MySQL

基础

 

执行一条select语句，期间发生了什么？

MySQL执行流程是怎样的？

客户端-连接器-查询缓存-词法分析-语法分析-与处理器-优化器-执行计划-执行器

MySQL的架构共分为两层：Server层和存储引擎层

Server层负责建立连接、分析和执行SQL。My SQL大多数的核心功能模块都在这实现，主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有夸存储引擎的功能（如存储过程、触发器、视图等）都在Server层实现。

存储引擎层负责数据的存储和提取。支持InnoDB\MySAM\Memory等多个存储引擎，不同的存储引擎共用一个Server层。现在最常用的存储引擎是InnoDB ,从MySQL5.5版本开始，InnoDB成为了MySQL 的默认存储引擎。我们常说的索引数据结构，就是有存储引擎层实现的，不同的存储引擎支持的索引类型也不同，比如InnoDB支持索引是B+ 树，且是默认使用，也就是说在数据表中创建的主键索引和二级索引默认使用的是B+树索引。

每个功能模块的作用：

第一步：连接器

如果你在Linux操作系统里要使用MySQL,那你第一步肯定是要先连接MySQL服务，然后才能执行SQL语句，普遍我们都是使用下面这条命令进行连接：

//连接

连接mysql服务（TCP三次握手）

执行sql

断开mysql服务（TCP四次握手）

//长连接

连接mysql服务（TCP三次握手）

执行sql

执行SQL

执行SQL

。。。

断开mysql服务（TCP四次握手）

可以看到，使用长脸的好处是可以减少建立连接和断开连接的过程，所以一般是推荐使用长连接。

但是，使用长连接后可能会占用内存增多，因为mysql在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开是才会释放。如果长连接累计很多，将导致mysql服务占用内存太大，有可能会被系统强制杀掉，这样会发生mysql服务器异常重启的现象。

怎么解决长连接占用内存的问题呢？

有两种解决方式。

1、定期断开长连接。然后断开连接后就会释放连接占用的内存资源。

2、客户端主动重置连接。mysql5.7版本实现了mysql_reset_conection()函数接口，注意这是借口函数不是命令，那么当客户执行了很大的操作后，在代码里调用mysql_reset_connection函数来重置连接，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完的状态。

至此，连接器的工作做完了，简单总结一下：

与客户端进行TCP三次握手连接；

校验客户端的用户名和密码，如果用户名或密码不对，则会报错；

如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读到的权限；

第二步：查询缓存

连接器得到工作完成后，客户端就可以向mysql服务器发送Sql语句了，mysql服务收到SQL语句后，就会解析出SQL语句的第一个字段，看看是什么类型的语句。

如果SQL是查询语句（select语句），mysql就会先去查询缓存（query Cache)里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以key-value形式保存在内存中的，key为SQL查询语句，value为SQL语句查询的结果。

如果查询的语句中查询中语句中命中查询缓存，那么就会直接返回value给客户端。如果查询的语句中没有命中查询缓存中，那么就要往下继续执行，等执行完后，查询的结果就会被存入查询缓存中。

这么看，查询缓存还挺有用，但是其实查询缓存挺鸡肋的。

对于更新比较频繁的表，查询缓存的命中率很低的，因为要一个表有更新操作，那么这个表的查询缓存就会被清空。如果刚缓存了一个查询结果很大的数据，还没被使用的时候，刚好这个表有更新操作，查询缓存就被清空了，相当于缓存了个寂寞。所以

mysql8.0版本直接将查询缓存删掉了，也就是说这个版本开始执行一条SQL查询语句，不会咋走到查询缓存这个阶段了。

对于mysql8.0之前的版本，如果想关闭查询缓存，我们可以通过参数query_cache_type设置成memand。

第三步：解析SQL

在正式执行SQL查询语句之前，mysql会先对sql语句做解析，这个工作交由解析起来完成。

解析器

解析器会做如下两件事情。

第一件事情，词法分析，mysql会根据你输入的字符串识别出关键字来，构建出SQL语法树，这样方便后面模块获取SQL类型、表明、字段名、where条件等。

第二件类型，语法分析。根据词法分析的结果，语法解析器会根据语法规则，判断你输入的这个SQL语句是否满足mysql语法。

如果我们输入的SQL语句语法不对，就会在解析器这个阶段报错。

注意：表不存在或字段不存在，不是在解析器里做的，解析器只负责构建语法书和检查语法。

判断表不存在或字段不存在是在预处理器做的、

不过，对于 MySQL 5.7 判断表或字段是否存在的工作，是在词法分析&语法分析之后，prepare 阶段之前做的。结论都一样，不是在解析器里做的。

第四步：执行SQL

经过解析器后，接着就要进入执行SQL查询语句的流程了，每条select查询语句流程主要分为下面这三个阶段：

prepare阶段，预处理

optimize,优化阶段；

execute阶段，也就是执行阶段；

预处理器

预处理阶段：

检查SQL查询语句中的表或字段是否存在；

将select*中的*符号，扩展为表上的所有列；

判断表或子段是否存在的工作在prepare阶段。

优化器

优化器主要负责将SQL查询语句的执行方案确定下来，比如在表里有多个索引的时候，优化器会基于查询成本的考虑，来决定选则是用那个索引。

主键索引：primary

覆盖索引：Using index

执行器

负责执行语句，执行过程中执行器是与存储引擎进行交互的，交互以记录为单位。

执行器与存储引擎的交互过程：

1、主键索引查询

执行器第一次查询会调用 red first record函数指针指向的函数，因为优化器选择的访问类型为const，这个函数指针被指向为InnoDB引擎索引查询接口，把条件id=1交给存储引擎，让存储引擎定位符合条件的第一条记录。

如果记录不存在，则会向执行器上报找不到的错误，然后查询结束。如果记录存在，就会将记录返回给执行器。

执行器从存储引擎读到记录后，接着判断是否符合查询条件，如果符合则发送给客户端，如果不符合，则跳过该记录。

执行查询的过程是一个while循环，所以还会再查一次，但是这次因为不是第一次查询了，所以会调用read record 函数指针指向的函数，因为优化器选择的访问类型为const，这个函数指针被指向为一个永远返回-1的函数，所以当调用该函数的时候，执行器就退出循环，也就是结束查询了。

至此，这个语句就执行完成了。

2、全表扫描

select * from product where name = 'iphone';

这条查询语句的查询条件没有用到索引，所以优化器决定选用访问类型为ALL进行查询，也就是全表扫描的方式查询，那么这是执行器与存储引擎的执行流程是这样的；

执行器第一次查询，会调用read_first_record函数指针指向的函数，因为优化器选择访问类型为all，这个函数指针被指向为InnoDB引擎全扫描的接口，让存储引擎读取表中的第一条记录；

执行器会判断读到的这条记录的name是不是iPhone，如果不是则跳过；如果是则将记录发给客户（使得没错，Sever层没从存储引擎读到一条记录就会发送给客户端，之所以客户端显示的时候是直接显示所有记录的，试音客户端是等查询语句查询碗好吃呢过后，才会显示出所有记录。

执行器查询的过程是一个while循环，所以还会再查一次，会调用read_record函数指针指向的函数，因为优化器选择的访问类型为all， read_record函数指针指向的还是InnoDB引擎全扫描的接口，所以接着向存储引擎要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器（server层），执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；

一直重复上诉过程，知道存储引擎吧表中的所有记录读完，然后向执行器返回了读取完毕的信息。

3、索引下推

MySQL 5.6推出的查询优化策略

索引下推能够减少二级索引在查询时回表操作，提高查询效率，因为它将server层底部负责的事情交给存储引擎层去处理了。

联合索引当玉带范围查询（>、<）就会停止匹配，也就是age字段能用到联合索引但是reward字段则无法利用到索引。

那么，不实用索引下推时，执行器与存储器的执行流程是这样的：

Server层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到age>20的第一条记录；

存储引擎根据二级索引的B+树快速定位到这条记录后，获取主键值，然后进行徽标操作，将完整的记录返回给server层；

sever再判断该记录的reward是否等于100000，如果成立则将其发送给客户端；否则跳过该记录；

接着，继续向存储引擎所要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回给server层；

如此往复，直到存储引擎吧表中的所有记录读完。

可以看到，没有索引下推的时候，没查询到一条二级记录，都要进行徽标操作，然后将记录返回给server，接着server再判断该记录的reward是否等于100000.

而使用索引下推后，判断记录的reward是否等于100000的工作交给了存储引擎层

过程如下：

• server层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到age>20的第一条记录；存储引擎定位到二级索引后，先不执行徽标操作，而是先判断一下该索引中包含的列reward列的条件（reward是否等于100000）是否成立。如果条件不成立，则直接跳过该二级索引。如果成立，则执行徽标操作，将完成记录返回给server层。

总结

执行一条SQL查询语句，期间发生了什么？

连接器：建立连接、管理连接、校验用户身份

查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL8.0已删掉该模块。

解析SQL：通过解析器进行词法分析、语法分析，然后构建语法树，方面后续模块读取表名、字段、语句类型；

执行SQL：

执行分为三个阶段：

• 预处理阶段：检查表或字段是否存在，将select*的*扩展为表上的所有列。
• 优化阶段：基于查询成本考虑，选择查询成本最小的执行计划；
• 执行阶段：根据执行计划执行SQL查询语句，从存储引擎读取记录，返回给客户端。

MySQL一行记录是怎么存储的？

MySQL的数据存放在哪个文件？

每个数据库里面都会有三个默认生成的文件：

• db.opt:用来存储当前数据库的默认字符集和字符校验规则。
• xxx.frm：xxx的表结构会保存在这个文件。
• xxx.ibd：xxx的表数据会保存在这个文件。

xxx为表名字，一张数据库表的数据是保存在「表名字.ibd」的文件里的，也称为独占空间文件。从 MySQL 5.6.6 版本开始。

表空间文件的结构是怎么样的？

表空间（tablespace）由段（segment）、区（extent）、页（page）、行（row）组成。

1、行

数据库表中的记录是按行存放的。

2、页

按行读取一次就会进行一次I/O操作，效率很低；InnoDB 的数据是按页来读写的。默认每页大小16KB。

页类型：数据页、undo日志页、溢出页等。

3、区

InnoDB存储引擎用B+树来组织数据。

B+树每层通过双向链表连接，为防止太多I/O次数，为索引分配空间的时候按照区为单位分配。连续的64个页会被分为一个区，这样就使得链表中相邻的页物理位置也相邻，就能使用顺序I/O了。

4、段

表空间由多个段组成，段又由多个区组成。

段分类：

• 索引段：存放B+树的非叶子结点的区的集合；
• 数据段：存放B+树的叶子结点的区的集合；
• 回滚段：存放的是回滚数据的区的集合；MVCC利用回滚段实现了多版本查询数据。

InnoDB行格式有哪些？

行格式（row_format），就是一条记录的存储数据。

InnoDB 提供了四种行格式：

• redundant；
• compact：紧凑的行格式，为了让数据页存放更多的记录，从5.1版本开始时。
• dynamic和compressed：都是紧凑的行格式，5.7版本之后默认使用dynamic格式。

COMPACT行格式长什么样？

变长字段长度列表｜NULL值列表｜记录头信息｜ row_id | trx_id | roll_ptr| 列1值｜列2值｜列3值

                        记录的额外信息                                                记录的真实数据

记录的额外信息

1、变长字段长度列表

变长字段世纪存储的数据的长度不固定。char是定长的、varchar是非定常的。

变长字段的真实数据占用的字节数是以十六进制的形式逆序存放的。

NULL不会存放在行格式记录中的真实数据里面，所以不需要保存值为null的变长字段的长度。

为什么变长字段列表的信息要按照逆序存放？

使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。

「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，比较方便。

每个数据库的行格式都有变长字段字节数列表吗？

不是必须的，当数据表没有变长字段的时候，比如全部都是int类型的字段。可以节省空间。

变长字段长度列表只出现在数据表有变长字段的时候。

2、null值列表

null值当道记录的真实数据中会比较浪费空间，所以compact行格式把这些值为NULL的列存储到NULL值列表中。null值的每个列对应一个二进制位，二进制位按照逆序排列。

• 二进制位的值为1，则该列值为null
• 值为0，则不为NULL
• null值必须用整数个字节的位表示，不够在高位补0.

每个数据表的行格式都有NULL值列表吗？

NULL值也不是必须的。

当数据表的字段都定义成NOT NULL的时候，就不会有NULL值列表了

NULL值列表是固定一字节空间吗？如果这样的话，一条记录由9个字段都是NULL，这时候怎么表示？

NULL值列表的空间不是固定1字节的

当一条记录有九个字段都是NULL，那么就会创建2字节空间的NULL值列表，以此类推。

3、记录头信息

记录头信息包含的内容很多。

• delete_mask：标识此条数据是否被删除。
• next_record:下一条记录的位置。
• record_type:表示当前的记录类型，0比奥什普通记录，1表示B+树非叶子结点记录，2表示最小记录，3表示最大记录。

记录的真实数据

记录真实数据部分出了我们定义的字段，还有三个隐藏字段

1、row_id   行标识符

用于在表中标识一行数据，行标识可用于快速定位和访问特定行数据。占用6字节

2、trx_id

事务id，表示这个数据是由那个事务上呢过程呢过的，这是必须的，占用6字节。

3、roll_poiter

记录上一个版本的指针，这也是必须的，占用7字节。

varchar（n)中的n最大取值为多少？

mysql规定除了TEXT、BLOBS这种大对象类型之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。注意是一行不是一列。

varchar（n）字段类型的n代表的是最多存储的字符数量，不是字节大小。

varchar（n）最大能允许存储的字节数，还要看数据库表的字腹肌，因为字腹肌代表着，1字符要占用多少字节，比如ASCII字腹肌，1字符占用1字节，那么varchar（100）以为喝最大能允许存储100字节的数据。

单字段情况

多字段情况

行溢出后，MySQL是怎么处理的？

总结

索引常见面试题

索引底层使用了什么数据结构和算法？

索引是数据的目录。

索引的分类

• 按数据结构分类：B+tree索引、hash索引、Full-Text索引。
• 按物理存储分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按字段特性分类：主键索引、唯一索引、普通索引、前缀索引。
• 按字段个数分类：单列索引、联合索引。

按数据结构分类

B+Tree 索引、HASH 索引、Full-Text 索引。

InnoDB引擎：支持B+Tree 索引、Full-Text 索引。内存结构有一个自适应的hash索引。

MyISAM引擎：支持B+Tree 索引、Full-Text 索引。

Memory引擎：B+tree索引、hash索引。

为什么MySQL InnoDb选择B+tree作为索引的数据结构？

怎样的索引的数据结构是好的？

MySQL的数据是持久化的，意味着数据（索引+记录）是保存到磁盘上的，因为这样即使设备断电了，数据也不会丢失。

磁盘读写的最少单位是山区，扇区的大小只有512B大小，操作系统一次会读写多个扇区，所以操作系统最小读写单位是块（BLOCK）。Linux中的额块大小为4KB，也就是一次磁盘I/O操作会直接读写8个扇区。

由于数据库的索引是保存到磁盘上的，因此当我们通过索引查找某航数据的时候，就需要先从磁盘读取索引到内存，再痛哟索引从磁盘中找到某行数据，然后读入到内存，也就是说查询过程会发生多次磁盘I/O，而磁盘I/O次数越多，所消耗的时间也就越大。

另外，mysql是支持范围查找的，所以索引的数据结构不仅能高效的查询某一记录，而且也要能高效的执行范围查找。

1、尽可能少的I/O

2、高效的范围查找

什么是二分查找？

索引数据最好能按顺序排列，这样就可以用二分查找高效定位数据。

假如用数组来存储索引，如果从头开始遍历查找的话，时间复杂度是O（n）效率不高。

数据是有序的，我们就可以用二分查找法：

每次都以中间节点开始对比，每次对比都会把查询的范围减半，这样时间复杂度就降到了O（logn).但是每次查找都需要不断计算中间位置。

什么是二分查找树？

1、用数组来实现线性排序当然好用，但是插入新元素的时候性能太低。

因为插入一个新元素，都要把这个元素之后的所有元素后移一位。

2、用有序的数组进行二分查找的时候，每次查找都要不断计算中间的位置。

二分查找树：把每次二分查找所有用到的中间节点用指针练起来，并将最中间的节点作为根结点。就变成了二叉查找树。

特点：一个节点的左子树的所有节点都小于这个节点，右子树的所有节点都大于这个节点。

这样的好处就是，我们在查询数据的时候，不需要计算中间节点的位置了，只需要将查找的数据与节点的数据进行比较。

二叉查找树解决了插入新节点的问题，二叉查找树是一个跳跃结构，不必连续排列。这样在插入的时候可以放在任何位置，不会像线性结构那样每插入一个元素所有元素都需要向后排列。

所以二叉查找树解决了连续节后插入新元素开销很大的问题，同时又保持着天然的二分结构。

但是二叉查找树存在着一个极端情况！

当每次插入的元素都是二叉查找树中的最大元素，二叉查找数就会退化成了一条链表，查找数据的时间复杂度变成了O（n）。

由于树是存储在磁盘中的，访问每个节点都对应一次磁盘I/O操作，树的高度就会与每次查询数据时磁盘IO操作次数成正比，树的高度越高就会影响查询性能。

什么是B树？

为了解决降低树的高度问题，就有了B树，它不再限制一个腺点就只能有两个节点，而是允许M个节点，从而降低树的高度。

B树的每个节点最多可以包括M个子节点，M称为B树的阶，所以B树就是一个多叉树。

B树的每个节点单最多有M-1个数据和最多有M个子节点，超过这些要求的话，就会分裂节点。

B树的查询过程：

        首先与根节点的索引范围进行比较，比根结点范围小，就往左子树走，介于之间就往中间子树走，然后进行索引范围的比较。

每进行一层树高的查询就会发生一次I/O操作。所以在同样节点数的情况下和平衡二叉树比，树高会比B树高，那样的话，B树的I/O次数会更少，数据查询效率也就更高。

但是B树的每个几点都包含数据（索引+记录），而用户的记录数据的大小很有可能远远超过了索引数据，这就需要花费更多的磁盘I/O操作次数来读到有用的索引数据。

再加上不能进行范围查询，所以不适合作为数据库的索引结构。

什么是自平衡二叉树？

为了解决二叉查找树会在极端情况下退化成链表问题，有了平衡二叉树（AVL树）。

主要是在二叉查找树的基础上增加了一些条件约束：每个节点的左子树和右子树的高度不能超过

1.这样查询操作的时间复杂度就会一直维持在O(logn).

每次插入的元素都是平衡二叉查找树中最大的元素，他会维持自平衡。

除了平衡二叉查找树，还有很多自平衡的二叉树，比如红黑树，也是通过一些约束条件来达到自平衡，不过红黑树约束条件比较复杂。

这两种平衡二叉树都会随着插入的元素增多，从而导致树的高度变高，这就意味着磁盘I/o次数多。

根本原因是因为他们都是二叉树，也就是每个节点只能保存两个子节点。

什么是B+树？

为了解决树的高度问题，后来就有了B树，它不再限制一个节点就只能有两个子节点，而是允许M个子节点，从而降低树的高度。

B树的每一个节点最多可以剥落M个子节点M称为B树的阶，所以B树就是一个多叉树。

B树的非叶子结点里的记录数据会从磁盘加载到内存，但是这些记录数据是没有用的，B树的

B+树与B树的差异：

1、叶子结点才会存放实际数据（索引+记录）非叶子结点只会存放索引。

2、所有索引都会在叶子及诶单出现，叶子节点之间构成一个有序链表

3、非叶子结点的索引也会同时存在子节点中，并且是在子节点中所有索引的最大/最小。

4、非叶子结点中有多少个子节点，就有多少个索引

B树进行单个索引查询时，最快可以在O（1）的时间代价内就查到，而从平均时间代价来看，会比B+树稍快一些。

但是B树的查询波动会比较大，因为每个节点既存索引又存记录，所以有时候访问到了非叶子结点就可以找到做音，而有时需要访问到叶子结点才能找到索引。

B+树的非叶子结点不存放实际的记录数据，仅存放索引，因此数量相同的情况下，相比存储索引又存记录的B树，B+树的非叶子结点可以存放更多的索引，因此B+树可以比B树更矮胖，查询底层节点的磁盘I/O次数会更少。

插入和删除效率：

B+树有大量的冗余节点，这样使得删除一个节点的时候，可以直接从叶子结点删除，甚至可以不动非叶子结点，不会设计复杂的树的变形，这样删除非常快。

插入也是一样，有冗余节点，插入有可能存在结点的分裂，如果节点饱和，但是最多只涉及树的一条路径。B+树会自动平衡不需要向更多复杂的算法，类似红河哦树的旋转操作。

范围查询：

B+树所有叶子结点还有一个链表进行连接，这种设计对范围查找非常有帮助。利用链表就可以实现叶子结点向左遍历。而B树额密友将所有叶子结点用链表串联起来的结构，因此只能通过树的遍历来完成范围查询，这会涉及多个节点的磁盘I/O操作，范围查询效率不如B+树。

因此，在大范围检索的场景，设施使用B= 树，比如数据库。对于大量的单个索引查询的场景可以考虑B树，比如nosql的mongoDB .

MySQL中的B+树

mysql最常用的InnoDB 存储引擎，采用了B+树座位了索引的数据结构。

B+树的叶子结点之间使用双向链表进行连接，这样的好处是既能向右遍历也能向左遍历。

B+树接待您内容是数据页，数据页里存放了用户的记录以及各种信息，每个数据页的默认大小是16KB。

InnoDBs是如何存储数据的？

记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取也就是一次I/O操作只能处理一行数据，效率非常低。

因此InnoDB的数据是按数据页为单位来读写的，也就是说，当需要读一条记录的时候并不是讲这个记录本身从磁盘读取出来，而是以页为单位，将其整体读入内存。innodb数据页的默认大小是16KB。

数据页结构包括：文件头、页头、行记录（最大最小记录用户记录）、空闲空间、页目录、文件尾。

在文件头file header中有两个指针，分别指向上一个数据页和下一个数据页。数据页中的记录按照主键顺序组成单向链表，单向链表的特点是插入、删除非常方便，但是检索效率不高，最差情路昂下需要遍历链表上的所有节点才能完成检索。

因此数据页中有一个页目录，起到记录的索引作用。数据页中的页目录就是为了能快速找到记录。

聚集索引和二级索引

1、聚集索引的叶子结点存放的是实际数据，所有完整的用户记录都存放在聚集索引的叶子节点。

2、二级索引的叶子结点存放的是主键值，而不是实际数据。

因为表的数据都是存放在聚集索引的叶子结点里，所以InnoDB存储引擎一定会为表创建一个聚集索引，由于苏剧在物理上只会保存一份，所以聚簇索引只能有一个。

inndb在创建聚簇索引时，会根据不同的场景选择不同的列作为索引：

1、如果有主键，默认会使用逐渐作为聚簇索引的索引键。

2、如果没有主键，就选择第一个不包含NULL值的唯一列作为聚簇索引的索引键。

3、在上面两个都没有的情况下，innoDB 将自动生成一个隐式自增id列作为聚簇索引的索引键。

为了实现非主键字段的快速搜索，就引出了二级索引，二级索引的叶子结点存放的是主键值，而不是实际数据。

如果叶子结点存储的是实际数据就是聚簇索引，一个表只能有一个聚簇索引；

如果叶子结点存储的不是实际数据，二是主键值则就是二级索引，一个表中可以有多个二级索引。

回表：

如果查询的数据不在二级索引里，就需要现在二级索引找到主键值，需要再去聚簇索引中获得数据行。

索引覆盖：

在使用二级索引进行查找数据时，如果查询的数据能在二级索引找到，那么就是索引覆盖操作。

索引失效有哪些

什么时候使用索引？

什么不时候不需要创建索引？

什么情况下索引会失效？

有什么优化索引的方法？

MySQL 使用 like “%x“，索引一定会失效吗？

count(*)和count(1)有什么区别？哪个性能最好？

事务

事务隔离级别是怎么实现的？

MySQL可重复读隔离界别，完全解决幻读了吗？

锁

MySQL有哪些锁？

MySQL是怎么加锁的？

MySQL是怎么加行级锁的？

加锁的对象是索引，加锁的基本单位是next-key lock,它是由记录锁和间隙锁组合而成的，next-key lock是前开后闭区间，而间隙锁是前开后开区间。

但是next-key lock 在一些场景下会退化成记录锁或间隙锁。

在能使用记录锁或间隙锁就能避免幻读现象的场景下，next-key lock就会退化成记录锁或间隙锁。

唯一索引等值查询

当我们用唯一索引进行等值查询的时候，查询的记录存不存在，加锁的规则也会不同：

当查询的记录是存在的，在索引树上定位到这一条记录后，将该记录的索引中的next-key lock会退化成记录锁。

当查询的记录是不存在的，在索引树找到第一条大于该查询记录的记录后，将该记录的索引中的next-key lock会退化成间隙锁。

如果是二级索引，进行“锁定读”查询的时候，还会对查询到的记录的主键索引项上加记录锁。

1、记录存在的情况

事务执行查询语句，如果记录存在于表中，该事务会为该记录加上X型的记录锁。

接下来，其他事务对该记录的操作都会被阻塞。

有什么命令可以分析加了什么锁？

select*from performance_schema.data_locks\G;

从上图可以看到，共加了两个锁，分别是：

• 表锁：X 类型的意向锁；
• 行锁：X 类型的记录锁；

图中的LOCK_TYPE 的RECORD表示行级锁，不是记录锁的意思。

通过lock_mode可以确认是next-key锁，还是间隙锁，还是记录锁：

lock_mode为x，说明是next_key锁，

lock_mode为x，REC_NOT_GAP说明是记录锁；

lock_mode为x，GAP说明是间隙锁

即所得随想是针对索引，，因为这里查询语句扫描的B+树是聚簇索引树，即主键索引树，所以是对主键索引加锁。

为什么唯一索引等值查询并且查询记录存在的场景下，该记录的索引中的next-key lock会退化成记录锁？

因为在唯一索引等值查询并且查询记录存在的场景下，仅靠记录锁也能避免幻读的问题。

幻读：当一个事务前后两次查询的结果集，不相同时，即为幻读。

由于主键具有唯一性，所以其他事务插入id=1的时候，会因为主键冲突，导致误差插入id=1的新纪录。这样事务A在多次查询id=1的记录的时候，不会出现前后两次查询的结果集不同，也就避免了幻读的问题。

由于对id=1加了记录锁，其他事务无法删除该记录，这样事务A在多次查询id=1的记录的时候，不会出现前后两次查询的结果集不同，也就避免了幻读问题。

2、记录不存在的情况

​​​​​​​

当事务在id=5记录的主键索引上加的是间隙锁，锁住的范围是（1，5）时，当有其他事务插入id值为2、3、4这些记录的话，这些插入语句都会发生阻塞。

但是id=1，5不会发生阻塞，而是报主键冲突的错误，因为表中已经存在id=1和id=5的记录了。

表锁：x的意向锁

行锁：x的间隙锁

间隙锁的范围（1，5）是怎么确定的？

lock_DATA表示锁范围-右边界，锁范围-做辩解为右边界的上一条记录。
为什么唯一索引等值查询并且查询记录不存在的场景下，在索引树找到第一条大于该查询记录的记录后，要将该记录的索引中的next-key lock会退化成间隙锁？

原因就是在唯一索引等值查询并且查询记录不存在的场景下，仅靠间隙锁就能避免幻读的问题。

唯一索引范围查询

当唯一索引进行范围查询时，会对每一个扫描到的索引加next-key锁，然后如果遇到下面这些情况，会退化成记录锁或者间隙锁：

情况一：针对>=的范围查询，因为存在等值查询的条件，那么如果等值查询的记录是存在于表中，那么该记录的索引中的next-key锁会退化成记录锁。

情况二：针对<=的范围查询，要看条件值的记录的记录是都存在表中：

不存在：<=:退化成间隙锁。

存在：<的范围：退化成间隙锁。<=:不会退化成间隙锁。

举例子1：查找id>15，针对大于的范围

 

1.最开始，由于不是等值查询，所以对主键索引加的是范围为（15，20]的next-key锁

2、由于是范围查找，会继续往后找，发现id=20是最后一条记录了（有一个特殊记录来标识最后一条记录supremum pseudo-record ），扫描到第二行的时候就会扫描到这个特殊的记录，会对该主键所欲加范围为（20，+∞]的next-key锁。

3、停止扫描。

可以得知，事务在主键索引上加了两个x型的next-key锁。

举例子2:针对大于等于的范围

1.开始找到第一行记录为等值查询，next-key退化成记录锁，仅锁住id=15这一行记录。无法更新删除15

2.由于是范围查找，继续完后找到第二行，于是对该主键索引加范围（15，20]的next-key锁。无法更新删除20，无法插入16，17，18，19.

3.扫描到第三行，扫描到了特殊记录superemum pseudo-record)于是对该主键索引加的是范围（20，+∞]next-key锁。无法插入>20

4.停止扫描

所以，事务在主键索引上加了三个x型的锁。

通过select*feom performance_schema.data_locks\G看看事务加了什么锁。

举例子3:针对小于的范围：id<6

 

1.最开始要找的第一行是id=1，于是该主键索引加的范围是（-∞，1]next-key锁。

2.由于是范围查找，继续往后找到id=5，（1，5]next-key锁

3.继续扫描

4.第三行id=10，不满足id<6，所以id=10这一行记录的锁会退化成间隙锁，于是对该主键索引加的是范围为（5，10）的间隙锁。

5.由于扫描到的第三行记录id=10，不满足id<6条件，达到了种植扫描的条件，于是停止扫描。

以上，加了三个x型锁。

举例子4:针对小于等于的范围：id<=5

1. 最开始要找的第一行是 id = 1，于是对该记录加的是范围为 (-∞, 1] 的 next-key 锁；
2. 由于是范围查找，就会继续往后找存在的记录，扫描到的第二行是 id = 5，于是对该记录加的是范围为 (1, 5] 的 next-key 锁。
3. 由于主键索引具有唯一性，不会存在两个 id = 5 的记录，所以不会再继续扫描，于是停止扫描。

因此，通过前面的三个实验，可以得知：

在针对小于或者小于等于的唯一索引（主键索引）范围查询时，存在着两种情况会将索引的next-key锁会退化成间隙锁的：

当条件值的记录不在表中时，那么不管是小于还是小于等于条件的范围查询，扫描到种植范围查询的记录是，该记录的主键索引中的next-key锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的主键索引上加next-key锁。

当条件值的记录在表中时：

如果是小于条件的范围查询，扫描到终止范围查询的记录时，该记录的主键索引中的next-key锁会退化成间隙锁，其他扫描到的记录，都是在这些记录的主键索引上加next-key锁。

非唯一索引等值查询

当使用非唯一索引进行等值查询的时候，因为存在两个索引，一个是主键索引，一个是非主键索引（二级索引），所以在加锁时，同时会对着两个索引都加锁，但是对主键索引加锁的时候，只有满足条件查询的记录才会对他们的主键索引加锁。

当查询的记录存在：

由于不是唯一索引，所以肯定存在索引值相同的记录，于是非唯一索引等值查询的过场是一个扫描的过程，知道扫描到第一个不符合条件的二级索引记录就停止到秒，然后再扫描的过程中，对扫描到的二级索引记录加的是next-key锁，而对于第一个不符合条件的二级索引记录，该二级索引的next-key锁会退化成间隙锁。同时，在符合查询条件的记录的主键索引上加伤记录锁。

当查询的记录不存在：

扫描到第一条不符合条件的二级索引记录，该二级索引的next-key锁会退化成间隙锁，因为不存在满足查询的条件记录，所以不会对主键索引加锁。

当有一个事务持有二级索引的间隙锁 (22, 39) 时，什么情况下，可以让其他事务的插入 age = 22 或者 age = 39 记录的语句成功？又是什么情况下，插入 age = 22 或者 age = 39 记录时的语句会被阻塞？

 

当一个事务持有二级索引的间隙锁（22，39）时，插入age=22或者age=39记录的语句是否可以执行成功，关键还要考虑插入记录的主键值，以为二级索引值（age列）+主键值（id列）才可以确定插入位置，确定了插入位置后，就要看插入的未知的下一条记录是否有间隙锁，如果有间隙锁就会发生阻塞，如果没有就会插入成功。

什么情况下插入语句会发生阻塞？

插入语句在插入一条记录之前，需要先定位到该记录在B+树的位置，如果插入的位置的下一条记录索引上有间隙锁，才会发生阻塞。

二级索引树是如何存放记录的？

二级索引树是按照二级索引值（age列）按顺序存放的，在相同的二级索引值情况下，再按主键id的顺序存放。

update没加索引会锁全表？

为什么会发生这种事故？

InnoDB存储引擎的默认事务隔离级别是可重复读，但是在这个隔离级别下，在多个事务并发的时候，会出现幻读的问题。所谓幻读，是指在同一事务下，连续执行两次同样的查询语句，第二次的查询语句可能会返回之前不存在的行。

因此innodb存储引擎自己实现了行锁，通过next-key锁（记录锁和间隙锁的组合）来锁住记录本身和记录之间的间隙，防止其他事务在这个记录之间插入新的记录，从而避免了幻读现象。

当我们执行updata语句时，实际上是会对记录加上独占锁（X锁）的，如果其他事务对持有独占锁的记录进行修改是会被阻塞的。另外，这个锁并不是执行完update语句就会释放的，而是会等事务结束时才会释放。

在InnoDB事务中，对记录加锁基本单位是next-key锁，但是会因为一些条件退化为间隙锁或者记录锁。加锁的位置准确的说，锁是加在索引上而非行上。

比如，在update语句的where条件使用了唯一索引，那么next-key就会退化成记录锁，也就是只会给这一行记录加锁。（因为等值查询语句在记录存在的情况下，仅靠记录锁也能解决幻读问题）

事务的update语句中where是等值查询，并且查询id=1，id是唯一索引，所以只会对id=1这一条记录加锁，因此别的事物不会被阻塞。

但是，在update语句的where条件没有使用索引，就会全表扫描，于是就会对所有记录加上next-key锁（记录锁+间隙锁），相当于把整个表锁住了。

在扫描过程中是对全表进行扫描，对索引加锁，所有记录都会被枷锁。相当于锁住了全表。直到事务结束，。但这并不等于加了表锁，只是对表里的索引项都加锁。

那么update语句的where带上索引就能避免全表记录加锁了吗？

关机还的看这条语句在执行过程中，优化器最终选择的是索引扫描，还是全表扫描，如果走了全表扫描，就会对全表的记录加锁了。

如何避免这种事故发生？

大致意思是，当sql_safe_updates参数设置为1时。

update语句必须满足如下条件之一才能执行成功：

• 使用where，并且where条件中必须有索引列；
• 使用limit；
• 同时使用where和limit，此时where条件中可以没有索引列；

​​​​​​​delete语句必须满足条列条件才能执行成功：

同时使用where和limit，此时where条件可以没有索引列。

如果where条件带上了索引列，但是优化器最终扫描选择的是全表，而不是索引的话，我们可以使用force index([index_name])可以告诉优化器使用那个索引，以此避免有几率锁全表带来的隐患。

MySQL记录锁+间隙锁可以防止删除操作导致的幻读吗？

答案是可以的。

什么是幻读？

当同一个事务，在不同时间内执行同样的查询语句，但两次的查询结果不一样，比如第二次返回了第一次没有返回的行。

MySQL 是怎么解决幻读的？

mysql innodb殷勤的默认隔离级别虽然是可重复读，但是它很大程度上避免幻读现象。

针对快照读（普通select语句），是通过mvcc方式解决了幻读，因为可重复读隔离级别下，事务执行过程中看到的数据，一直跟这个事务启动时看到的数据是一致的，即使中途有其他事务插入了一条事务，是查不出来这条数据的，所以就很好地避免了幻读问题。

针对当前读（select...for update等语句），是通过next-key（记录锁+间隙锁）方式解决了幻读，因为当执行select...for update语句的时候，会加上next-key lock，如果有其他事务在next-key lock锁范围内插入了一条记录，那么这个插入语句就会被阻塞，无法成功插入，所以就很好的避免了幻读问题。

 

• 表锁（LOCK_TYPE: TABLE）：X 类型的意向锁；
• 行锁（LOCK_TYPE: RECORD）：X 类型的 next-key 锁；

• 这里我们重点关注「行锁」，图中 LOCK_TYPE 中的 RECORD 表示行级锁，而不是记录锁的意思：

• 如果 LOCK_MODE 为 X，说明是 next-key 锁；
• 如果 LOCK_MODE 为 X, REC_NOT_GAP，说明是记录锁；
• 如果 LOCK_MODE 为 X, GAP，说明是间隙锁；

如果 LOCK_MODE 是 next-key 锁或者间隙锁，那么 LOCK_DATA 就表示锁的范围最右值，而锁范围的最左值为 LOCK_DATA 的上一条记录的值。

此时事务 A 在主键索引（INDEX_NAME : PRIMARY）上加了 10 个 next-key 锁，如下：

• X 型的 next-key 锁，范围：(-∞, 1]
• X 型的 next-key 锁，范围：(1, 2]
• X 型的 next-key 锁，范围：(2, 3]
• X 型的 next-key 锁，范围：(3, 4]
• X 型的 next-key 锁，范围：(4, 5]
• X 型的 next-key 锁，范围：(5, 6]
• X 型的 next-key 锁，范围：(6, 7]
• X 型的 next-key 锁，范围：(7, 8]
• X 型的 next-key 锁，范围：(8, 9]
• X 型的 next-key 锁，范围：(9, +∞]

这相当于把整个表锁住了，其他事务在对该表进行增、删、改操作的时候都会被阻塞。

只有事务A提交了事务，事务A在执行过程中产生的锁才会被释放。

为什么只是查询年龄 20 岁以上行记录，而把整个表给锁住了呢？

这是因为事务A的这条查询语句是全表扫描，锁是在遍历索引的时候加上的，并不是针对输出的结果加锁。

 因此，在线上再执行update、delete、select...for update等具有加锁性质的语句，一定要检查语句时都走了索引，如果是去全表扫描的话，会对每一个索引加next-key锁，相当于把整个表锁住了。

如果对 age 建立索引，事务 A 这条查询会加什么锁呢？

对age字段建立了索引后，查询语句是索引查询，并不会全表扫描，因此不会把整张表给锁住。

主键索引会加如下的锁：

• X 型的记录锁，锁住 id = 2 的记录；
• X 型的记录锁，锁住 id = 3 的记录；
• X 型的记录锁，锁住 id = 5 的记录；
• X 型的记录锁，锁住 id = 6 的记录；
• X 型的记录锁，锁住 id = 7 的记录；
• X 型的记录锁，锁住 id = 8 的记录；

分析 age 索引加锁的范围时，要先对 age 字段进行排序。 

age 索引加的锁：

• X 型的 next-key lock，锁住 age 范围 (19, 21] 的记录；
• X 型的 next-key lock，锁住 age 范围 (21, 21] 的记录；
• X 型的 next-key lock，锁住 age 范围 (21, 23] 的记录；
• X 型的 next-key lock，锁住 age 范围 (23, 23] 的记录；
• X 型的 next-key lock，锁住 age 范围 (23, 39] 的记录；
• X 型的 next-key lock，锁住 age 范围 (39, 43] 的记录；
• X 型的 next-key lock，锁住 age 范围 (43, +∞] 的记录；

化简一下，age 索引 next-key 锁的范围是 (19, +∞]。

 

总结一下，在对 age 字段建立索引后，事务 A 在执行下面这条查询语句后，主键索引和 age 索引会加下图中的锁。

 

 

MySQL死锁了，怎么办？

死锁的发生

在查询记录是否存在的时候，使用了 select ... for update 语句，目的为了防止事务执行的过程中，有其他事务插入了记录，而出现幻读的问题。

 

如果没有使用 select ... for update 语句，而使用了单纯的 select 语句，如果是两个订单号一样的请求同时进来，就会出现两个重复的订单，有可能出现幻读。

为什么会产生死锁？

innoDB引擎为了解决可重复读隔离级别下的幻读问题，就引出了next-key锁，它是记录锁和间隙锁的组合。

record lock，记录锁，锁的是记录本身；

gap lock，间隙锁，锁的就是两个值之间的空隙，以防止其他事务在这个空隙间插入新的数据，从而避免幻读现象。

普通的select语句是不会对记录加锁的，因为它是通过MVCC的机制实现的快照读，如果要在查询时对记录加行锁，可以使用下面这两个方式：

begin;
//对读取的记录加共享锁
select ... lock in share mode;
commit;//锁释放

begin;
//对读取的记录加排他锁
select ... for update
commit;//锁释放

 行锁的释放时机是在事务提交后（commit），锁就会释放，并不是一条语句执行完就释放锁。

插入意向锁于间隙锁是冲突的，所以当其他事务持有该间隙的间隙锁是，需要等待其他事务释放间隙锁之后，才能获取到插入意向锁。而间隙锁与间隙锁之间是兼容的，所以两个事务中的select...for update语句并不会相互影响。

案例中的事务A和事务B在执行完后select...for update语句后都持有范围为（1006，+∞]的next-key锁，而接下来的插入操作为了获取到插入意向锁，都在等待对方事务的间隙锁释放，于是就造成了循环等待，导致死锁。

为什么间隙锁之间是兼容的？

间隙锁的意义只在与阻止区间被插入，因袭是可以共存的，。一个事务获取的间隙锁不会阻止另一个事务获取同一个间隙范围的间隙锁，共享和排他的间隙锁是没有区别的，他们相互不冲突，且功能相同。

可以是两个间隙锁的区间一样；也可以是一个是另一个区间的子集。

但是有一点要注意，next-key lock是包含间隙锁+记录的，如果一个事务获取了x型的next-key lock ,那么醒一个事务在获取相同范围的x型的next-key lock时，是会被阻塞的。

虽然相同范围的间隙锁十多个事务相互兼容的，但对于记录锁，是要考虑x型和s型关系。x型的记录锁与x行的记录锁是冲突的，比如一个事务执行了select...where id=1 for update,后一个事务在执行这条语句的时候就会被阻塞。

对于这种范围为 (1006, +∞] 的 next-key lock，两个事务是可以同时持有的，不会冲突。因为 +∞ 并不是一个真实的记录，自然就不需要考虑 X 型与 S 型关系。

插入意向锁是什么？

插入意向锁不是意向锁，，它是一种特殊的间隙锁。该锁只用于并发插入操作。

如果说间隙锁锁住的是一个区间，那么插入意向锁锁住的就是一个点。

插入意向锁与间隙锁的另一个非常重要的差别是：尽管插入意向锁也属于间隙锁，但两个事务却不能在同一时间内，一个拥有意向锁，另一个拥有该间隙区间内的插入意向锁。（插入意向锁如果不在间隙区间内也是可以的）。

没插入一条新纪录，都需要看一下带插入记录的下一条记录上是否已经被加了间隙锁，如果已加间隙锁，此时会生成一个插入意向锁，然后锁的状态设置为等待状态。（PS：mysql加锁时，是先生成锁结构，然后设置锁的状态，如果锁状态是等待状态，并不是意味着事务成功获取到了锁，只有当锁状态为正常状态时，才代表事务成功获取到了锁。）

现象就是insert语句会被阻塞。

insert语句是怎么加行级锁的？

insert语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的trx_id隐藏列来作为隐式锁来保护记录的。

什么是隐式锁？

当事务需要加锁时，如果这个锁不可能发挥说呢过冲突，InnoDB回调国家锁环节，这种机制称为隐式锁。隐式锁是InnoDB实现的一种延迟加锁机制，其他电视只有在可能发生冲突时才加锁，从而减少了锁的数量，提高了系统整体性能。

隐式锁就是在insert过程中不加锁，只有在特殊情况下，才会将隐式锁转换为显示锁。

• 如果记录之间加有间隙锁，为了避免幻读，此时不能插入记录；
• 如果insert的记录和已有的记录存在唯一键冲突，此时也不能插入记录；

字节面试：加了什么锁，导致死锁的？

日志

MySQL日志：undo log、redolog、binlog有什么用？

内存

揭开Buffer Pool的面纱