MySQL知识点总结

SQL语句注意

• sql语句的执行顺序：from—where–group by—having—select—order by

  • https://blog.csdn.net/u014044812/article/details/51004754

• 字段名和表名要用反引号`

• 关键词 DISTINCT 用于返回唯一不同的值。

  • SELECT DISTINCT 列名称 FROM 表名称
    

• in，允许我们在 WHERE 子句中规定多个值。

  • SELECT column_name(s)
    FROM table_name
    WHERE column_name IN (value1,value2,...)
    

• 顺序排列是order by asc/desc

• Round(字段名,保留的位数)：保存小数点四舍五入的函数

• upper（字段名）：是将字段的内容转换成大写进行返回

  • 注意：字段的数据类型必须是字符串类型

• substring(字符串，起始位置，截取字符数）：字符串的截取，起始是从1开始

• concat(字符串1，字符串2，字符串3,…)：字符串的拼接

• sum()：SUM 函数返回数值列的总数（总额）。

  • SELECT SUM(column_name) FROM table_name
    

• 返回一列中的最大值 SELECT MAX(column_name) FROM table_name

  • 聚合函数
  • 最大值—max()
  • 最小值—min()
  • 平均值—avg()
  • 总值 —sum()
  • 总数 —count()

• group by分组

  • 按照哪个字段进行分组，或者是哪几个字段进行分组
  • https://blog.csdn.net/u014717572/article/details/80687042?ops

• having子句：在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

  • 它往往与 GROUP BY 配合使用，为聚合操作指定条件

  • 说到指定条件，我们最先想到的往往是 WHERE 子句，但 WHERE 子句只能指定行的条件，而不能指定组的条件，因此就有了 HAVING 子句，它用来指定组的条件

• 关于连接https://blog.csdn.net/Li_Jian_Hui_/article/details/105801454

• 自然连接。

• 对于自然连接而言，连接两个table之后，两个table共用的属性就会合并在一起。

  如果连个table没有共有的属性，则进行笛卡尔乘积，也就是进行两两相乘，如果table 1有3行，table 2有4行，自然连接后就有12行。

  自然连接的语法如下：

  select *
  from table_a natural join table_b;
  

SQL注入

什么是SQL注入

在web应用程序中用户输入数据时填写SQL关键字来使得数据库执行代码的过程。像你在表单里填写有关;DROP TABLE students这样子的语句，会使得数据库被删库。这个过程就是SQL注入

怎么防止SQL注入

• 尽量避免使用常见的数据库名和结构。

• 使用正则表达式等过滤手段限制数据项的格式、字符数目

• 将数据项以参数的方式与SQL执行语句分离开，可以避免SQL注入问题

  这个在mybatis中体现在#{}里，它会将传入的参数作为一个字符串来处理，将sql中的#{}替换为？

  然后调用preparestatement的set方法来赋值

  ${}是直接将传入的参数拼接到sql上去执行，是直接的字符串替换，容易发生sql注入

• 权限限制，限制用户的操作权限

• 做好备份

数据库三大范式

第一范式：确保数据库字段的原子性，每一列都不可以再拆分，不能是数组、集合等非原子数据项

第二范式：首先要满足第一范式，表必须要有一个主键，非主键完全依赖于主键，而不是依赖主键的一部分

第三范式：在第二范式的基础上，非主键只依赖于主键，不能依赖于其他非主键，不存在传递依赖

（事实上我们经常会为了性能而妥协数据库的设计。）

MySQL的数据类型

数据类型

• 整数：TINYINT SMALLINT MEDIUMINT INT BIGINT 表示1、2、3、4、8字节的整数
• 实数：FLOAT DOUBLE DECIMAL(m,n)m代表多少位有效数字，n表示小数点后有多少位
• 字符串：VARCHAR CHAR TEXT BLOB char比varchar要好，不容易产生碎片，但要注意分配的空间。尽量避免使用TEXT/BLOB类型，查询时会用到临时表，会有严重的性能开销

Float和double的区别

• float占四个字节，double占八个字节

char和varchar的区别

• 定长和变长：char是定长的，长度固定，若插入长度小于定义长度时，则用空格填充。varchar是变长的，当存入长度小于定义长度时，还是按实际长度存储，插入多长就存多长。所以相比之下varchar更加省空间
• 因为char长度固定，不需要计算插入数据段的长度。而varchar的存储结构前面是一个1到2个字节的空间存储数据长度，需要计算，后面才是存储实际数据，取的时候都要先读长度，再根据长度去读数据，所以char的存取速度总要比varchar快，但是会占多余空间
• 存储容量不同：char最大是255个字节，varchar最大是6w(65535)个字节
• char插入时尾部有空格会被忽略，varchar插入时不会忽略，但查询时会忽略

MySQL引擎

MySQL有什么存储引擎

• MySQL中常用的四种存储引擎分别是： MyISAM、InnoDB、MEMORY、ARCHIVE。
• MySQL 5.5版本后默认的存储引擎为InnoDB。

innodb和myisam的区别

mysql5.5开始innodb就是默认的表存储引擎

• InnoDB支持事务，MyISAM不支持事务
• InnoDB支持表、行级锁，而MyISAM支持表级锁。
• InnoDB支持外键，MyISAM不支持外键
• InnoDB 支持 MVCC(多版本并发控制)，MyISAM 不支持
• select count(*) from table时，MyISAM更快，因为它有一个变量保存了整个表的总行数，可以直接读取，InnoDB就需要全表扫描。
• Innodb不支持全文索引，而MyISAM支持全文索引（5.7以后的InnoDB也支持全文索引）
• InnoDB表必须有主键，而MyISAM可以没有主键

引擎应用场景

一般是用innodb

SQL的约束

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK

MySQL的索引

什么是索引

• 让mysql高效获取数据的数据结构，好比一本书的目录，能加快数据库的查询速度
• 索引往往是存放于磁盘上
• 我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使用B+树结构组织（多路搜索树，并不一定是二叉的）的索引。

索引的优缺点

优点：

• 可以提高检索效率，降低I/O成本
• 可以通过索引对数据进行排序，降低数据的排序成本

缺点：

• 索引会占据磁盘空间
• 索引虽然会提高查询效率，但是会降低更新表的效率，因为增删查改不仅操作数据，也会操作对应的索引

索引的使用场景

索引是以空间换时间的，某字段如果频繁作为查询条件，建议建立索引

创建索引的原则

• 最左适配原则，组合索引的原则，mysql会一直向右匹配直到遇到范围查询(<、>、between、like)就会停止匹配
• 对频繁作为查询条件的字段才去创建索引
• 频繁更新的字段不适合作为索引
• 不能有效区分数据的列不适合做索引，如性别，就男女两种，区分度太低，100w行数据用索引可能还要搜寻50w行
• 尽量的扩展索引，不要新建索引。创建新索引耗费更多的性能，比如表中有a索引，现在要加（a,b）索引，那么只需要修改原来的索引即可
• 定义外键的数据列一定要建立索引，不然会出现死锁
• 对于查询中很少涉及的列，重复值比较多的列不适合建立索引
• 对定义text、blob类型的数据不要使用索引

Mysql有哪些索引

按数据结构分

• B+索引（主键索引和辅助索引）
• hash索引（自适应哈希索引，时间复杂度O(1)，但是失去了有序性，无法进行范围查找）
• 空间索引（R树，根据维度去索引数据）

按索引类型分

普通索引（INDEX）

最基本的索引，没有什么限制，可以是空值和重复的值

CREATE TABLE 表名(
	字段名 数据类型;
	......
	INDEX[索引名](字段名[长度])
);

CREATE INDEX 索引名 ON 表名(字段名[长度]);

//例子
CREATE INDEX ID ON Student(stu_id);

唯一性索引（UNIQUE）

索引列中的值必须是唯一的，但允许是空值

CREATE UNIQUE INDEX 索引名 ON 表名(字段名[长度]);

//例子
CREATUE UNIQUE INDEX name on subject(sub_name);

主键索引（PRIMARY KEY）

唯一性索引，每个表只能有一个主键，且不能为空

CREATE TABLE Persons(
	Id_P int not null,
	Primary key (Id_P)
    //第二种写法
    CONSTRAINT Person_id PRIMARY KEY (Id_P)
)

ALTER TABLE Persons
ADD PRIMARY KEY (Id_P)

ALTER TABLE Persons
DROP PRIMARY KEY

全文索引（FULLTEXT）

当数据量较大时，用like模糊查询效率会下降。可以用全文索引来提高效率，全文索引只支持在char、varchar、text类型上创建（MyISAM 存储引擎支持全文索引）

CREATE TABLE FULLTEST_test(
	id int(11) not null,
    content test not null,
    tag varchar(255),
    FULLTEXT KEY content_tag_fulltext(content,tag)
)

组合索引

组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

索引失效的情况

1、like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。索引的排列顺序是根据比较字符串的首字母排序的

（%叫做通配符，代表任何字符任意数量
如’%a%'就代表一个字符串只要包含字母a就符合条件
'%a’则代表一个字符串需要以字母a结尾才符合条件
'a%'就是以a开头的字符串了）

2、or语句前后都没有使用索引，或只有一个是索引，则索引失效，必须为前后两个条件都为索引时索引才生效。

3、组合索引，不是使用第一列索引，违背最左适配原则也会失效。

4、数据类型出现隐式转换，则索引失效。如varchar类型忘记加单引号，将转化为int，产生全表扫描，索引失效。

5、在索引上使用not、!=、<>是不会使用索引的，将进行全表扫描（不等操作符）

6、对索引字段进行计算操作或者函数操作会导致索引失效

7、全表扫描比索引更快时，mysql会启动全表扫描，索引失效。

最左适配原则（组合索引）

https://blog.csdn.net/sinat_41917109/article/details/88944290

• 顾名思义，就是最左优先，在创建组合索引时，根据业务需求，要把查询最频繁的一列放在最左边
• mysql会一直向右匹配直到遇到范围查询（<、>、between、like）就停止匹配
• =和in可以乱序，mysql会自动优化查询顺序

联合索引也是一颗B+树（m阶多路查找树），只不过联合索引的键值有多个，而B+是根据一个键值来构造，因此数据库依据索引的最左字段来构建B+树

联合索引(a,b)

• a的值是顺序的，但是b是无序的所以b=2这种查询条件是没办法利用索引的，因为组合索引最先是按最左的排序的。
• 但是在a值相同的情况下，b就是有序的，是相对的，所以a=1 and b>2是可以用到索引的，绑定了a之后就变得有序
• a>1 and b=2就不能，因为当a值是一个范围时，b值是无序的，索引用不上索引

（a,b,c）这些方法靠底层B+的规则就可以判断

1、全值匹配时用到了索引

a/b顺序不会影响索引使用，因为mysql会自动优化查询顺序

select * from table_name where a = '1' and b = '2' and c = '3' 
select * from table_name where b = '2' and a = '1' and c = '3' 
select * from table_name where c = '3' and b = '2' and a = '1' 
......

2、匹配左边的列时

select * from table_name where a = '1' 
select * from table_name where a = '1' and b = '2'  
select * from table_name where a = '1' and b = '2' and c = '3'

如果直接是b=‘1’，用的是全表扫描

如果不连续，只用到了a的索引，b用不到

3、匹配列前缀

如果a是字符类型，那么前缀匹配时用的是索引，后缀和中缀就是全表扫描了

select * from table_name where a like 'As%'; //前缀都是排好序的，走索引查询
select * from table_name where  a like '%As'//全表查询
select * from table_name where  a like '%As%'//全表查询

4、匹配范围值

如果只对最左边进行范围查询，会用到索引

select * from table_name where  a > 1 and a < 3

多个列查询时只有a用到

select * from table_name where  a > 1 and a < 3 and b > 1;

5、精确匹配某一列并范围匹配另外一列

如果a是精确查找，那么b是有序的，走的是联合索引

select * from table_name where  a = 1 and b > 3;

6、排序

order by的子句后面的顺序也必须按照索引列的顺序排出，颠倒顺序就没有用到索引

select * from table_name order by a,b,c limit 10;

select * from table_name order by b,c,a limit 10;

什么是聚簇索引、非聚簇索引

• 聚簇索引：聚簇索引是使用B+树构建，也叫主键索引，叶子结点存储的数据是整行记录，非叶子结点存储的是索引
• 非聚簇索引：在聚簇索引之上创建的索引称为非聚簇索引，也叫辅助索引。访问数据总需要二次查找，叶子结点存储的是主键值，通过主键值再到聚簇索引上找到行数据

什么是覆盖索引

• 覆盖索引是一种数据查询方式，不是索引类型
• 覆盖索引一般针对的是辅助索引，整个査询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取。
• 查询的字段被使用到的索引树全部覆盖到，不需要主键回表查询，就叫做覆盖索引
  

什么是回表

根据辅助索引树中获取的主键id，到主键索引树检索数据二次查询的过程称为回表查询

非聚簇索引一定会回表吗

不一定，这得看查询的字段是否就是索引的字段。如在学生的age上建立索引，那么进行select age from Student where age<20时索引就在叶子上，不会再进行回表查询

如何避免回表

使用覆盖索引，在查询字段上建立索引，就可以不需要回表。

MySQL的索引结构

Hash表

以键值对的方式去存取数据

B+树

• 叶子节点保存了父节点的所有键值（索引）和键值对应的数据（行数据），每个叶子节点的关键字从小到大通过链指针链接，形成有序的双向链表
• 非叶子节点上只存储key值信息（索引）和孩子指针p。不会存实际的键值对应的数据（行数据），这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度
  

B+树和B树的区别

• B树：非叶子结点和叶子结点都会存数据
• B+树：只有叶子结点才会存储数据，非叶子结点只存储键值。叶子结点之间使用双指针连接形成一个双向有序链表
• B+树的叶子结点是双向链表，B树的叶子结点各自独立

Hash和B+的区别

• hash索引底层是hash表，通过键值去查询数据。B+是多路搜索树，每次查询都是从根节点出发到叶子结点结束，然后根据查询判断是否需要回表
• hash的等值查询更快，但是因为hash是无序的所以进行不了范围查询
• hash不支持模糊查询和最左适配原则
• hash的查询速度不稳定，hash碰撞会导致其不稳定

innodb为什么索引要用B+ tree

1. B+树的空间利用率更高，可以减少I/O次数，磁盘读写代价更低，查询速度更快：B+树的非叶子节点上只存储key(索引)，这样可以大大增加每个节点存储的key值数量，降低B+Tree的高度，减少I/O次数，所以磁盘读写代价更低，查询速度也更快

2. B+树全表扫描更快，方便扫库：B+树遍历整棵树只需要遍历所有的叶子节点即可，而不需要像B树一样需要对每一层进行遍历，这有利于数据库做全表扫描。

3. B+树查询速度更稳定：B+树的key对应的数据都存在叶子节点上，每次查询都要从根节点走到叶子结点，所以查询速度更加稳定

4. B+树的增删效率更高，因为B+树的叶子结点包含所有关键字，并且链式有序存储，增删效率高

5. B+树范围查询更方便：B+树所有的叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便

B+树存100w条数据有几层

b+树一个节点一页，一页16k，如果一行数据1k，那一页能存16条数据。对于非叶子节点，key如果使用bigint，为8个字节，指针在mysql6个字节，一共14个字节，则16k能存16*1024/14 = 1170个索引指针。由此算出高度为2的b+树，有1170个叶子节点存储数据，每个叶子节点16条，也就是1170 * 16 = 18720条数据；对于高度为3的b+树，可以存1170 * 1170 * 16 = 21902400条

为什么减少树高能降低IO

• 操作系统一般将内存和磁盘分为固定大小的块，每一块称为一页，内存与磁盘以页为单位交换数据。

• 数据库将索引一个结点的大小设置为页的大小，使得一次IO就能完全载入结点

关于树

二叉树

只有两个分支的树，如果树高位n，树的最大节点数为2^n-1

二叉搜索树

左子树的结点小于根节点的值，右子树的结点大于根节点的值

二叉平衡树

任意结点子树的高度差为

红黑树

• 不严格的平衡二叉树，增加和删除结点的效率比AVL高
• 根节点为黑色，相邻父子结点不能都为红色

MyISam索引实现

主键索引

MyISam的数据文件和索引文件是分开存储的。

MyISam使用B+树构建索引树，叶子结点存储的key是索引列的值，data为索引所在行的磁盘地址

根据主键等值查询数据

磁盘IO次数：3次索引检索+1次记录数据检索。

select * from user where id = 28;

根据主键范围查询数据：

磁盘IO次数：4次索引检索+记录数据检索。

辅助索引（结构一样，键值对可重复）

在 MyISAM 中,辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

查询数据时，由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

Innodb索引的实现

主键索引（聚簇索引）

每个Innodb表都有一个聚簇索引，聚簇索引使用B+树创建，叶子结点存储的是整行的数据。

当一个表没有创建主键索引时，innodb会自动创建一个rowid字段来创建聚簇索引。创建规则如下：

• 在表上定义主键PRIMARY KEY，InnoDB将主键索引用作聚簇索引。
• 如果表没有定义主键，InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。
• 如果以上两个都没有，InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。

等值查询数据：

• 先在主键树中从根节点开始检索，将根节点加载到内存，比较28<75，走左路。（1次磁盘IO）

• 将左子树节点加载到内存中，比较16<28<47，向下检索。（1次磁盘IO）

• 检索到叶节点，将节点加载到内存中遍历，比较16<28，18<28，28=28。查找到值等于28的索引项，直接可以获取整行数据。将改记录返回给客户端。（1次磁盘IO）

磁盘IO数量：3次。

辅助索引（非聚簇索引）

除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB，辅助索引中的叶子节点存储的数据（data）是该行的主键的值。 在检索时，InnoDB使用此主键值在聚簇索引中搜索行记录。

等值查询数据

select * from t_user_innodb where age=19;

事务

什么是的事务

事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

比方说A向B转700块钱（读取A账户->A-700->读取B账户->B+700) 这组操作集就是事务

ACID

1.原子性

事务被视为不可分割的最小单元，事务的所有操作要么全部提交成功，要么全部失败回滚。

2.一致性

一致性是指事务执行前后能够保证事务从一个正确的状态转移到另一个正确的状态。用例子来解释就是A有100块，B有0块。这是一个正确的状态。A向B转账50块钱，A账户50，B账户50.这是另一个正确状态。这样就是一致性，不一致性就类似于A转了50，但是在B+50的过程中宕机了，导致了B还是0。这就是不一致

3.隔离性

并发访问数据库时，一个用户的事务不被其他事务干扰，各并发事务之间数据库是独立的

4.持久性

一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。

mysql如何实现acid

• 原子性：是使用 undo log来实现的，如果事务执行过程中出错或者用户执行了rollback，系统通过undo log日志返回事务开始的状态。
• 持久性：使用 redo log来实现，只要redo log日志持久化了，当系统崩溃，即可通过redo log把数据恢复。
• 隔离性：通过锁以及MVCC,使事务相互隔离开。
• 一致性：通过回滚和并发情况下的隔离性实现（原子性和隔离性）

为什么innodb支持事务

因为innodb有两种日志redo log和undo log

redo log是来记录数据修改后的记录，顺序记录，用来保证事务的持久性

undo log是来记录数据修改前的记录，若出现异常可以用undo log来回滚，实现事务的原子性、一致性。

bin log是记录数据库中表的更改

事务在修改数据之前，要先把旧版本的数据记到undo log，然后修改数据，再将更新数据写入redo log。redo log和undo log要比数据先持久化到磁盘。

并发一致性问题

• 丢失修改：1、某个事务的回滚导致另外一个事务的更新操作丢失 2、某个事务的提交覆盖了另一个事务的更新
• 读脏数据：当前事务读到其他事务未提交的数据
• 不可重复读：某个事务对同一个数据前后读取的结果不一致（在该事务未提交时，其他事务修改了第一次读的值，导致第二次读就不一样了）
• 幻读：某个事务对同一个表前后查询的结果行数不一致。这是因为第二次这个事务查到了其他事务对数据所做的更改。（范围）比如说查询age=20的用户数量，起先是1个，后面事务2再添加age为20的用户，第二次查询就为2了

可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

产生并发不一致性问题的主要原因是破坏了事务的隔离性，解决方法是通过并发控制来保证隔离性。并发控制可以通过封锁来实现，但是封锁操作需要用户自己控制，相当复杂。数据库管理系统提供了事务的隔离级别，让用户以一种更轻松的方式处理并发一致性问题。

事务隔离级别

• 读未提交：事务中的修改，即使没提交，也能被其他事务看到（防止读未提交）
• 读已提交：只能读到已经提交事务的修改。（避免脏读）
• 可重复读：保证某个事务对同个数据多次读取结果一致（避免脏读、不可重复读）
• 可串行化：强制事务串行执行

mysql默认是使用RR，事务隔离级别是基于锁机制和并发调度实现，其中并发调度是通过MVCC来实现，通过保存修改的旧版本信息来实现并发一致性读和回滚等特性

RR可以避免幻读吗

• 如果只是查询，是可以的，因为属于快照读

生产环境下一般使用什么隔离级别

生产环境下一般使用RC

• 在RR隔离级别下，存在间隙锁，导致出现死锁的几率比RC大的多

• 在RR隔离级别下，条件列未命中索引会锁表，而在RC隔离级别下，只锁行。相比较之下RR性能低于RC

• 大部分场景下，不可重复读问题是可以接受的。毕竟数据都已经提交了，读出来本身就没有太大问题

mysql的锁

锁粒度

• 表级锁

优点：实现逻辑简单，可以有效避免死锁问题

缺点：粒度大导致并发量会减少

• 行级锁

优点：粒度小，并发量大

缺点：容易发生死锁，锁的多所以系统开销大（锁一个大门和锁很多个房间门）

行锁适用于高并发环境下更新多的系统；表锁适用于并发性不高，以查询为主，更新少

锁类型

• 共享锁

也称读锁，如果一个事务对数据加了读锁，只有获得了读锁才可以读取数据，但不能更新。其他事务可以对它加s锁但是不能加x锁

• 排它锁

也称写锁，如果一个事务对数据加了写锁，只有获得了写锁才可以被读取和更新，同时其他事务不能再对该数据加任何锁。

• 意向共享锁
• 意向排它锁

一个事务在获得某个数据行对象的 S 锁之前，必须先获得表的 IS 锁或者更强的锁；

一个事务在获得某个数据行对象的 X 锁之前，必须先获得表的 IX 锁。

意向锁是表锁，意向锁是用来判断即将添加的锁是否兼容，无需将表从头到尾扫一遍看看是否有冲突的

事务在向表中添加共享锁时，会自动添加意向共享锁。

事务在向表中添加排它锁时，会自动添加意向排它锁。

什么是间隙锁

• 间隙锁锁定的是索引B+树叶子节点的next指针

比方说有id为1/2/5的数据，现在在RR级别下想搜寻id<4的数据，这个时候间隙锁就会锁住(2,5)这个间隙，防止其他事务来插入数据，防止出现幻读现象

• 间隙锁主要用于解决可重复读事务隔离级别中的幻读问题。
• 但是间隙锁会产生死锁，当两个事务同时进行返回查询时比如刚刚的<4，都会锁住(2,5)这个间隙，间隙锁互相不会冲突。这就导致了A事务想在这个区间插入数据时遭到了B事务的间隙锁，只能等待，而B事务要添加也只能等待A事务的间隙锁。这就造成了死锁

隔离级别与锁的关系

RU：一级封锁协议，要求事务T修改数据时要加x锁，事务T结束后才释放锁。可以解决丢失修改问题，保证写操作时只有一个事务在操作

RC：二级封锁协议，一级封锁的基础上，要求读数据时必须要加S锁，读完就立马释放锁。因为一级对修改数据时要加X锁，所以在修改数据期间是没法加S锁的也就读不到数据，解决了读脏数据的问题。

RR：三级封锁协议，在二级的基础上，要求读取数据时必须要加S锁，并且是事务结束后才释放。因为在事务获得S锁期间就没法加X锁了，也就没法对数据进行修改。解决了不可重复读问题。

可串行化是通过间隙锁实现。

死锁

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象

解决死锁的方法

• 同一个事务中，尽可能一次性锁定所需要的资源，减少死锁的概率
• 对于非常容易出产生死锁的部分，可以尝试升级锁的粒度，通过表级锁来减少死锁的概率

MVCC

什么是MVCC

• MVCC是指在并发访问数据库时，通过对数据做多版本管理，采用无锁的形式避免读写冲突问题。实现RC和RR这两种隔离级别。

• 读操作是快照读，读的是该事务开始前的数据库的快照，实现不加锁的非阻塞读

• 写操作是当前读，读取的是最新版本的数据，读取时使用锁来保证其他事务不会修改当前数据

MVCC的好处

• 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
• 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题

MVCC实现原理

原理依赖3个隐式字段、undo日志、Read View来实现

3个隐式字段

DB_TRX_ID：当前事务ID：通过事务id的大小判断事务的时间顺序

DB_ROLL_PTR：回滚指针，指向当前行记录的上一个版本，通过这个指针将数据的多个版本连接在一起构成undo log版本链。

DB_ROW_ID：隐藏主键，如果数据表没有主键，InnoDB 会自动生成一个主键

undo日志

insert undo log

代表事务在 insert 新记录时产生的 undo log, 只在事务回滚时需要，并且在事务提交后可以被立即丢弃

update undo log

事务在进行 update 或 delete 时产生的 undo log ; 不仅在事务回滚时需要，在快照读时也需要；所以不能随便删除

不同事务或者相同事务的对同一记录的修改，会导致该记录的undo log成为一条记录版本线性表，既链表，undo log 的链首就是最新的旧记录，链尾就是最早的旧记录

（对MVCC有实质帮助）

Read View

读视图，用来记录发生快照读那一刻所有的记录，当你下次就算有执行新的事务记录改变了，read view没变，读出来的数据依然是不变的。

总结

InnoDB 的MVCC是通过 read view 和版本链实现的，版本链保存有历史版本记录存在undo log中，通过read view 判断当前版本的数据是否可见，如果不可见，再从版本链中找到上一个版本，继续进行判断，直到找到一个可见的版本。

MVCC中的RC和RR

而隔离级别中的RR（可重复读）、和RC（提交读）不同就是差在快照读时

RR同一个事务中的第一个快照读才会创建 Read View, 之后的快照读获取的都是同一个 Read View，所以其他事务修改数据对他是不可见的、解决了不可重复读问题。

RC则是每次快照读时都会生成并获取最新的 Read View、所以就会产生不可重复读问题。

视图

视图是什么

• 是从一个或多个表导出的虚拟表，其内容由查询定义，具有普通表的结构，但是不实现数据存储
• 单表视图一般用于查询和修改，会改变基本表数据
• 多表视图一般用于查询，不会改变基本表数据

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

视图的优点

• 简化用户操作
• 提高数据安全性，用户只能查询和修改看得到的数据
• 逻辑独立性，屏蔽了真实表结构带来的影响

视图的缺点

• 性能差，数据库必须把对视图的查询转化为对基本表的查询，如果视图是由一个复杂的多表查询所定义，查询会需要一定的时间
• 修改限制，对于简单的视图来说修改很方便，但是对于复杂的视图，可能是不可修改的

视图适用的场景

• 需要一直复用sql语句，可以用视图
• 简化复杂的sql操作
• 保护数据，只想对外公开一部分数据

什么是游标

游标有点像是指针，可以定在查询出来的结果集中的特定行，检索结果集的一行或者多行

存储过程

什么是存储过程

一组预先编译好的sql语句集

create procedure 存储过程名(参数列表)
begin
//....存储过程体
end;

存储过程的优点

• 存储过程是预编译过的，执行效率高
• 存储过程可以重复使用，减少冗余

存储过程的缺点

如果程序中大量使用存储过程，若因为需求增加而导致数据库结果发生改变，后期要维护该系统将会变得异常艰难

触发器

什么是触发器

触发器是用户定义在关系表上的一类由事件驱动的特殊的存储过程，当触发某个事件时，会自动执行这段代码。

触发器使用场景

• 复杂的安全检查
• 数据的确认
• 数据库的备份与同步

mysql的主从复制

1、什么是主从复制

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库

2、主从复制原理

• 主库的更新事件（update、insert、delete）被写到binlog中

• 从库生成两个线程，一个I/O线程，一个SQL线程

• 主库会生成log dump线程，用来给从库I/O线程读取binlog

• 从库的I/O线程去请求主库的binlog，并将binlog日志写入到relay log文件中

• 从库的SQL线程读取relay log文件中的日志，并解析成具体操作，实现主从操作一致。

  

3、主从复制的好处

1、可以在从服务器上进行查询操作，主库写，从库读，实现读写分离，降低服务器的压力

2、从库可作为后备数据库，主数据库服务器故障后。可切换到从数据库继续工作，避免数据的丢失。

4、主从同步延时、数据丢失问题

• 在高并发场景下，从库获取到数据会比主库慢一些，是有延时的。所以经常出现，刚写入主库的数据可能是读不到的，要过几十毫秒，甚至几百毫秒才能读取到。
• 主库宕机后，数据可能会丢失

并行复制

解决主从同步延时问题

从库开启多个线程，并行读取relay log中不同库的日志，然后并行重放不同库的日志，这是库级别的并行

（把relay log并行读取，分成不同库的日志读，然后同时进行，省时）

半同步复制

解决主库数据丢失问题

指的是主库写入binlog日志之后，就会将此时的数据强制同步到从库中，从库将日志写入到本地的relay log之后，接着会返回一个ack给主库，主库接收到至少一个ack之后才会commit提交给客户端。

MySQL日志

https://blog.csdn.net/qq_44331428/article/details/123090779

https://blog.csdn.net/weixin_39406430/article/details/115841383

binlog二进制日志作用：

binlog的主要作用是记录数据库中表的更改，它只记录改变数据的sql，不改变数据的sql不会写入，比如select语句一般不会被记录，因为他们不会对数据产生任何改动。

relay-log作用：

relay-log中继日志记录的是从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器

undo回滚日志作用：

用于存放数据修改被修改前的值。如果这个修改出现异常，可以使用undo日志来实现回滚操作，保证事务的原子性、一致性。

redo重做日志作用：

用于记录数据修改后的记录，顺序记录。假设修改 tba 表中 id=2的行数据，把Name=’B’ 修改为Name = ‘B2’ ，那么redo日志就会用来存放Name=’B2’的记录，如果这个修改在flush 到磁盘文件时出现异常，可以使用redo log实现重做操作，保证事务的持久性。

explain

id：select查询的序列号，id相同，执行顺序自上而下，id不同，id越大越快执行

select_type:查询类型，有简单查询、联合查询、子查询等

table：指的是当前表

type：对表的访问方式

• All：遍历全表，性能最差
• index：全索引扫描，和ALL的区别是index只遍历索引树
• range：索引范围扫描，一般是where语句出现between、<，>，in等查询
• ref：非唯一性索引扫描，返回匹配某个值的所有行
• eq_ref：类似ref，区别在于使用的是唯一索引，对于每个索引键，表中只有一条记录与之匹配。常见于主键索引和唯一索引
• const：针对主键或唯一索引的等值查询，最多只返回一行数据
• system：该表中只有一行，system是const类型的实例

row：扫描的行数

key：实际上使用到的索引

为什么要尽量设置主键

可以更快的进行增删查改

主键使用自增ID还是UUID

推荐使用自增

主键是聚簇索引，而B+树的叶子结点是有序的双向链表，添加只需从后面加进去即可。如果是UUID可能会造成多数据的移动，导致插入性能下降

为什么字段最好要not null

因为null会占更多的字节（1个字节）

mysql数据库优化的思路和方法

查询性能优化

使用 Explain 进行分析

Explain 用来分析 SELECT 查询语句，开发人员可以通过分析 Explain 结果来优化查询语句。

比较重要的字段有：

• select_type : 查询类型，有简单查询、联合查询、子查询等
• key : 使用的索引
• rows : 扫描的行数

优化数据访问

1. 减少请求的数据量

• 只返回必要的列：最好不要使用 SELECT * 语句。执行SQL时优化器需要将 * 转成具体的列；每次查询都要回表，不能走覆盖索引。
• 只返回必要的行：使用 LIMIT 语句来限制返回的数据。
• 缓存重复查询的数据：使用缓存可以避免在数据库中进行查询，特别在要查询的数据经常被重复查询时，缓存带来的查询性能提升将会是非常明显的。

2. 减少服务器端扫描的行数

最有效的方式是使用索引来覆盖查询。

重构查询方式

1. 切分大查询

一个大查询如果一次性执行的话，可能一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但重要的查询。

DELETE FROM messages WHERE create < DATE_SUB(NOW(), INTERVAL 3 MONTH);
rows_affected = 0
do {
    rows_affected = do_query(
    "DELETE FROM messages WHERE create  < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
} while rows_affected > 0

2. 分解大连接查询

将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

• 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用。
• 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余记录的查询。
• 减少锁竞争；
• 在应用层进行连接，可以更容易对数据库进行拆分，从而更容易做到高性能和可伸缩。
• 查询本身效率也可能会有所提升。例如下面的例子中，使用 IN() 代替连接查询，可以让 MySQL 按照 ID 顺序进行查询，这可能比随机的连接要更高效。

SELECT * FROM tag
JOIN tag_post ON tag_post.tag_id=tag.id
JOIN post ON tag_post.post_id=post.id
WHERE tag.tag='mysql';
SELECT * FROM tag WHERE tag='mysql';
SELECT * FROM tag_post WHERE tag_id=1234;
SELECT * FROM post WHERE post.id IN (123,456,567,9098,8904);

一条查询语句在数据库的处理过程

mysql的逻辑架构

客户端、sql层和存储引擎层

语句执行过程

1、客户端先与连接器进行连接、权限验证

mysql mysql -h主机地址 -u用户名 －p

2、连接建立后，查询缓存，查看是否执行过这条语句，若有则直接命中并返回结果。（mysql8.0把查询缓存给删了）

3、若缓存未命中，则走解析器，进行语法解析，判断是否有语法错误

4、将解析后的结果放进优化器中处理，决定使用哪个索引，生成执行计划

5、将执行计划交给操作引擎执行，返回结果

6、将结果交给存储引擎进行存储