MySQL进阶难度知识点分析

以下为本人在阅读《MySQL是怎样运行的：从根儿上理解MySQL》这本书时对一些难度和重点的笔记，主要用于个人学习使用，内容可能存在出入，望理性食用~

1. sql执行流程

一条sql的执行流程大致可分为客户端获取与数据库服务器的连接，然后查询缓存（MySQL 8.0之后移除），解析和优化，最后交给存储引擎进行具体数据的读取与写入。

其中解析是指语法分析、词法分析、语义分析等，解析一条sql语句是否有语法错误，确定其查询的是哪个表，条件是什么，是否有索引等，然后优化器会进行优化，选择最优的执行计划，通过explain可以查看执行计划。

2. MySQL的内存结构

存储引擎与硬盘的I/O通过中间的内存进行交互，页是内存与磁盘进行交互的基本单位，一个页中包含多干条记录，InnoDB中页的大小一般为16KB。存储引擎中有不同的页，有的页存放表结构，有的页存放Insert Buffer、有的页存放undo日志等，其中存放记录的页称为索引页，也可称为数据页。

16KB的页中包含不同的分区，每个分区有不同的功能，其中存放实际记录的分区称为User Records，未插入数据时为空，当插入数据时会从Free Space（未使用空间）中分一部分空间给User Recods来存放数据，当Free Space为0，仍有数据插入时，则插入到其他页中。

页中还包含存放表信息的File Headers和存放页信息的Page Headers，具体分区情况如下：

一条记录中，不仅包含实际的字段数据，还包含其他数据，比如记录的头信息、真实数据中的NULL值情况等，同时还要三个隐藏列：row_id、transaction_id和roll_pointer。其中row_id是指当表中没有指定主键且不包含唯一索引时，会自动根据记录的物理存储顺序创建一个row_id用于记录的排序使用，transaction_id是事务id，roll_pointer是回滚指针。

16KB的页中存储若干条记录项，这些记录根据主键id排序，每条记录之间使用单链表进行连接（next_record字段记录下一条记录的地址偏移量，用于查找下一条记录），为便于查询每条记录，不用每次查询都遍历所有的记录，将所有记录项按块进行分组，每个分组对于一个槽，这些槽（页面目录中的这些地址偏移量被称为槽）记录了该分组中主键id最大的位置，页中的所有槽构成了页目录，通过二分法查找页目录可以快速定位到所查记录项所在分组，然后通过next_record遍历该槽中的各个记录查找即可，以此来提高查询效率。具体来说，在一个完整的页中，每隔6条数据就会有一个Slot。

同时记录项中有个delete_mask属性，用于逻辑删除使用，即值为0表示未删除，为1表示已删除，因为没从页中实际删除一条记录，移除该数据后需要从磁盘中重新进行I/O读取数据，效率较低，因此设置逻辑删除属性。

3. 为什么要有索引？

每个数据页中的记录会按照主键的大小通过单链表进行连接，各个数据页通过双向链表进行连接。因为页的大小有限，一张表的数据量较大时，需要很多页来存储，当没有使用索引的情况下，每次查找一条记录时，只能从头遍历所有的页，来确定该记录存在于哪个页中，然后在页中二分查找来找到对应的记录，时间复杂度很高。

参照AVL平衡二叉树的查找方式，MySQL的索引使用B+树进行降低查找的时间复杂度。在B+树中，非叶子结点存储了每个页中的最小主键值和页号，通过最小键值可以判断要查找的键值应该存在于哪个页中，通过页号可以直接定位到所要查找的页，使得每次查找只需从根节点比较主键的值，然后依次比较每个结点的键值，最后找到该记录存在于哪个叶子节点中，在该叶子节点中使用二分查找即可。

4. 页之间为什么要用双链表进行连接？

因为当频繁的插入或删除数据时，页内的数据量会进行变化，当页内存储的数据量太小或者太大导致需要增加新叶或删除当前页时，为保证记录按照主键有序排列，需要进行页分裂，因此需要知道当前页的前后页的位置，故需要使用双向链表进行连接。

5. 回表的缺点

因为非聚族索引的叶子节点只存放索引的键值和主键的值，不存放具体的数据，因此在使用非聚族索引如二级索引查数据时，会先根据二级索引查到该数据的主键值，然后到聚族索引中根据该主键值找到具体的数据信息，这种二次查表的过程就是回表，回表会导致查询效率降低，需要额外再查一次表。

其次回表会导致随机IO，索引的叶子节点中的记录项是根据顺序依次排放的，它们在磁盘中的存储是相连的，因此根据查询的索引键值可以集中的将查询结果从磁盘中读取出来，这种读取方式称为顺序IO，效率高。而在回表查询时，每个索引键值对应的主键值是随机分布的，根据主键值再去聚族索引中查找不同的页中的记录项，这种读取方式称为随机IO，导致与磁盘IO次数增大，效率降低。

6. 使用索引的注意事项

对于联合索引进行查询时，查询条件要满足最左匹配原则。
用于排序的情况只有同升或同降才能适用索引，一升一降无法使用索引。
右模糊匹配适用索引，左模糊和全模糊不适用。
只对查询、排序或分组字段建立索引，而不是根据查询的结果建立索引。
索引列的类型尽可能的小，页的大小一定，主键值越小存储的记录项就越多。
为了尽可能少的让聚族索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
使用到索引的搜索条件和没有使用该索引的搜索条件使用OR连接起来后是无法使用该索引的。

7. Buffer Pool缓存区

通过前面的知识可以知道InnoDB是以页为单位来存储和读取数据的，数据存储在磁盘中，当需要查询页中的某条数据时，会从磁盘中将相应的整页加载到内存当中，然后在内存中对该数据进行读写操作。而操作完成后并不会立即将该页对应的内存空间释放掉，而是将其缓存起来，以便下次查询到相同的数据时直接进行读取，省去了磁盘IO的开销。

这个用于缓存磁盘中的页的区域被称为Buffer Pool（中文名是缓冲池），它是MySQL向操作系统申请的一片连续的空间，在MySQL服务器启动时进行初始化，默认空间大小为128M，同时为了更好的管理Buffer Pool中的缓存页，每个缓存页都有其对应的控制信息，这些控制信息包括该页所属的表空间编号、页号、缓存页在Buffer Pool中的地址、链表节点信息等，每个缓存页对应的控制信息占用的内存大小是固定的，将每个页对应的控制信息所占用的一块内存称为控制块，故一个页对应一个控制块，Buffer Pool对应的内存空间如下：

刚开始时Buffer Pool内不存放任何数据，只是有空闲的一个个控制块和对应的缓存页构成，随着程序的运行不断有磁盘上的页被加载进该内存空间，那如何来确定Buffer Pool中哪些缓存页是空闲的，哪些已经被使用了呢？因此需要使用free链表来进行管理，其记录了所有空间缓存页的情况（将其对应的控制块放入到链表中），刚开始时所有缓存页均空闲，其对应的控制块都在free链表上，每当需要从磁盘中加载一个页到Buffer Pool中，就从free链表中取一个空闲的缓存页对应的控制块，并将其对应的控制块信息填上，然后从free链表中移除该控制块，表示当前缓存页已被使用。

如果当我们访问页时，该页已经存在Buffer Pool中，如何确定其已存在呢？如何定位它的位置？

最高效的方式就是在O(1)的时间复杂度内查询到，故可以使用哈希表，通过key快速找到其对应的value，表空间号 + 页号作为key，缓存页作为value创建一个哈希表，在需要访问某个页的数据时，先从哈希表中根据表空间号 + 页号看看有没有对应的缓存页，如果有，直接使用该缓存页就好，如果没有，那就从free链表中选一个空闲的缓存页，然后把磁盘中对应的页加载到该缓存页的位置。

再来考虑，当我们如何修改了Buffer Pool中的缓存页内的数据时，如何将其对应的页同步到更新到磁盘中去，防止出现脏页，MySQL使用flush链表来存储脏页，将凡是修改过的缓存页对应的控制块都会作为一个节点加入到一个链表中，待之后的某个时刻同步到磁盘上。

那么如果当Buffer Pool中的空间满了，依然有数据要从磁盘加载到该空间中该怎么办，一个很好的思路是将最近最少使用的缓存页释放掉，然后放入新的缓存页，因此根据该规则需再创建一个LRU链表，当我们需要访问某个页时，可以这样处理LRU链表：