MySQL——掘金小册《MySQL 是怎样运行的：从根儿上理解 MySQL》学习笔记

InnoDB 记录存储结构

1. 页是MySQL中磁盘和内存交互的基本单位，也是MySQL是管理存储空间的基本单位。

2. 指定和修改行格式的语法如下：

   CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
   
   ALTER TABLE 表名 ROW_FORMAT=行格式名称
   

3. InnoDB目前定义了4种行格式

   • COMPACT行格式

     具体组成如图：

     img
     • 变长字段长度列表存放的是每个变长字段存储的字节数，通过字符数*每个字符占用的字节来记录
     • null值列表存的是该条记录哪几列为null（逆序），比如第三第四列为空，主键列NOT NULL，第二列标记为NOTNULL，则这个标志位为000001(c4)1(c3)0（0x06)

• Redundant行格式

  具体组成如图：

  img

• Dynamic和Compressed行格式

  这两种行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储字符串的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。

  另外，Compressed行格式会采用压缩算法对页面进行压缩。

• 一个页一般是16KB，当记录中的数据太多，当前页放不下的时候，会把多余的数据存储到其他页中，这种现象称为行溢出。如果某个字段长度大于了16KB，该记录在单个页面中无法存储时，InnoDB会把一部分数据存放到所谓的溢出页中。记录的真实数据处只会存储该列的前768个字节的数据和一个指向其他页的地址，然后把剩下的数据存放到其他页中，这个过程也叫做行溢出，存储超出768字节的那些页面也被称为溢出页

每一条数据的结构：

img

这些二进制位代表的详细信息如下表：

页

一个InnoDB数据页的存储空间大致被划分成了7个部分，有的部分占用的字节数是确定的，有的部分占用的字节数是不确定的。

记录在页中的存储

在页的7个组成部分中，我们自己存储的记录会按照我们指定的行格式存储到User Records部分。但是在一开始生成页的时候，其实并没有User Records这个部分，每当我们插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下：

img

被删除的记录只是会标记为被删除，而并不会从磁盘中删除调，因为重新排列需要性能小号，会在新的记录插入的时候占用到被删除的记录的空间（可重用空间）

​ 每个页中的数据会被划分成几个组，每个组的最后一条记录的头信息中的n_owned属性表示该记录拥有多少条记录也就是这个组又多少条记录，且他的地址偏移量单独提取出来按顺序存储到靠近页的尾部的地方（页目录），页目录中的这些偏移量被称为槽。

img

​ 最小记录和最大记录分别是头尾节点，并不是真实的数据记录。在一个组中的记录数等于8个后再插入一条记录时，会将组中的记录拆分成两个组，一个组中4条记录，另一个5条记录。这个过程会在页目录中新增一个槽来记录这个新增分组中最大的那条记录的偏移量。

​ InnoDB可能不可以一次性为这么多数据分配一个非常大的存储空间，如果分散到多个不连续的页中存储的话需要把这些页关联起来，FIL_PAGE_PREV和FIL_PAGE_NEXT就分别代表本页的上一个和下一个页的页号。这样通过建立一个双向链表把许许多多的页就都串联起来了，而无需这些页在物理上真正连着。需要注意的是，并不是所有类型的页都有上一个和下一个页的属性，不过我们本集中唠叨的数据页（也就是类型为FIL_PAGE_INDEX的页）是有这两个属性的，所以所有的数据页其实是一个双链表，就像这样

img

​ InnoDB存储引擎会把数据存储到磁盘上，但是磁盘速度太慢，需要以页为单位把数据加载到内存中处理，如果该页中的数据在内存中被修改了，那么在修改后的某个时间需要把数据同步到磁盘中。

单页内查询：

根据主键查找的时候，用的是二分法，先计算中间槽的位置对应记录的主键值（这个槽的最后一条记录），用二分法找到所在的槽，然后找到该槽所在分组中主键最小的那条记录（通过找到上一个槽对应的记录的下一个节点），从那条记录开始遍历该槽所在的组中的各个记录。

多页查询：

1、定位记录所在的页

2、在该页用单页的方法查询

索引

平衡二叉树：左右节点的层级相差不大于1、左节点小于本节点，本节点小于右节点，最多拥有两个子节点

B树：

• 枝节点的关键字数量大于等于ceil(m/2)-1个且小于等于M-1个（注：ceil()是个朝正无穷方向取整的函数 如ceil(1.1)结果为2);
• 子节点数可以大于2，每个节点包含的关键字多了，减少了层级。

B+树：

• 非叶子节点不存关键字数据，所以每个非叶子节点存储的关键字数更多，树的层级更少（磁盘每4k为一块）
• 所有的关键字数据地址都存在叶子节点上，所以每次查找的次数都相同，更稳定。
• 叶子节点数据构成了一个有序链表，在查询大小区间的数据时候更方便，紧密型更高
• 全节点遍历更快，有利于全表扫描

如何定位记录所在的页

• 目录项记录页，该页中存放的数据只有（主键）索引的起始值和对应子节点目录（数据）页的页号信息
• innodb采用了b+树来做索引，所有的叶子节点是所有的用户数据页，也就是真正存数据的页，而所有的非叶子节点都是目录项记录页，当数据量越来越大时，目录项记录页也会越来越多，此时需要一个更上层的节点来保存该层所有目录项记录页的索引。最终顶层是一个根节点。如下图：
• img
• 当进行索引查询的时候，根据索引项，从根节点（单个目录项记录页）通过二分法找到对应的子节点的目录项记录页，在再这个页中再通过二分法找到下一个节点的目录项记录页，直到找到叶子节点，即数据页，再通过二分法找到找到对应的槽，再在槽里遍历找到该数据。牛逼！

聚簇索引（主键索引，叶子节点存放所有的数据）

1. 存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。页内的记录是按照主键的大小顺序排成一个单向链表。

2. B+树的叶子节点存储的是完整的用户记录。

   所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列）。

我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句去创建（后边会介绍索引相关的语句），InnoDB存储引擎会自动的为我们创建聚簇索引。另外有趣的一点是，在InnoDB存储引擎中，聚簇索引就是数据的存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

二级索引（非聚簇索引也叫辅助索引）

• 使用其他列字段的大小作为排序依据（作为索引）
• 叶子节点存储的不是完整的用户记录，而是索引列+主键
• 目录页记录的不是主键+页号，而是索引列+主键+页号
• 查找到用户记录后，根据查找到的主键再去聚簇索引中查找一遍完整的用户记录也就是回表，需要2棵b+树

联合索引

如果根据c2和c3来建索引，则按照c2和c3的大小进行排序

• 先把各记录的页按照c2进行排序
• 如果c2相同则根据c3排序
• img
• 叶子节点由c2、c3、主键组成
• 如果要查询的数据刚好都在索引列里面，则不会回表，直接从二级索引的叶子节点中取数据。
• 如果是ab联合索引，查询条件中b在前，a在后，那mysql会通过查询优化器进行优化，先用a来搜索。但是如果只用b来进行查询，则不会走索引，因为没办法通过b的大小来二分查找。

注意事项

• 每当为某个表创建一个B+树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录。
• 随后向表中插入用户记录时，先把用户记录存储到这个根节点中。
• 当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页a中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页b。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页a或者页b中，而根节点便升级为存储目录项记录的页。
• 一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引

MyISAN中的索引方案

MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储：

• 将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多少记录就往这个文件中塞多少记录就成了。我们可以通过行号而快速访问到一条记录。MyISAM 记录也需要记录头信息来存储一些额外数据，我们以index_demo表为例，看一下这个表中的记录使用MyISAM作为存储引擎在存储空间中的表示：

• image-20200916203440781

• 由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找。

• 使用MyISAM存储引擎的表会把索引信息另外存储到一个称为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户记录，而是主键值 + 行号的组合。也就是先通过索引找到对应的行号，再通过行号去找对应的记录！

CREATE TALBE 表名 (
    各种列的信息 ··· , 
    [KEY|INDEX] 索引名 (需要被索引的单个列或多个列)
)
ALTER TABLE 表名 ADD [INDEX|KEY] 索引名 (需要被索引的单个列或多个列);
ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

索引的代价

• 空间上的代价

  这个是显而易见的，每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，那可是很大的一片存储空间呢。

• 时间上的代价

  每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且我们讲过，B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收啥的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，这还能不给性能拖后腿么？

匹配左边的列

最好不要跨过中间索引查询前后索引，这样只会用到最左侧的连续索引。

匹配列前缀

• 字符串排序，根据第一个字符的大小排序，如果相同则根据第二个，依次类推。
• 最好使用a = 'test%' 而不是a = '%test' 或者 a= '%test%'
• 如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引，因为第一个列查出来的是范围，只有在第一列相同的情况下，第二列才做了排序，所以用不到这个索引。
• 如果最左边的列是精确，第二列是范围，是可以用到索引，如果再有第三列是范围，则只能遍历了

排序

• order by后的列的顺序也必须按照索引列的顺序给出，不然用不了索引。且排序方式必须一致，不能一个desc一个asc
• 如果包含了非同一个索引的列，也不能使用索引
• 不能使用别的函数，比如UPPER或者a*2=4 可以改成a=4/2

分组

如果是索引字段按顺序进行分组，天然就是排好序的，可以直接分组。

回表

• 根据索引查数据时，由于数据记录再磁盘中的存储是项链的，顺序IO速度很快。而他们所对应的主键往往是不连续的，所以用这些不连续的id值到聚簇索引中去查完整记录时，大概率分布在不同的数据页，就变成随机IO了
• 需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。比方说name值在Asa～Barlow之间的用户记录数量占全部记录数量90%以上，那么如果使用idx_name_birthday_phone_number索引的话，有90%多的id值需要回表，这不是吃力不讨好么，还不如直接去扫描聚簇索引（也就是全表扫描）。回表的记录越少，性能提升就越高，越倾向二级索引+回表

覆盖索引

• 最好返回结果只查索引值

列的基数

• 在记录行数一定的情况下，列的基数越大，该列中的值越分散，列的基数越小，该列中的值越集中。最好为那些列的基数大的列建立索引，为基数太小列的建立索引效果可能不好。

索引列的类型尽量小

• 数据类型越小，在查询时进行的比较操作越快（这是CPU层次）
• 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。

索引字符串值的前缀

• 由于字符串长的话占用的空间会比较多，如果整个字符串做索引，b+树占用的空间会很大，做字符串比较也会占用更多的时间，所以可以用前缀几个字符来做索引，比如前10个字符进行索引，则匹配的时候只查出前10个字符匹配上的数据，然后再回表查完整数据。减少比较时间，节约了空间。
• 索引列前缀的方式无法支持使用索引排序。

主键插入顺序

插入时不要忽大忽小，不然会造成页面分裂和记录位移，会造成性能损耗。最好是自增主键。

总结：

B+树索引适用于下边这些情况：

• 全值匹配
• 匹配左边的列
• 匹配范围值
• 精确匹配某一列并范围匹配另外一列
• 用于排序
• 用于分组

在使用索引时需要注意下边这些事项：

• 只为用于搜索、排序或分组的列创建索引
• 为列的基数大的列创建索引
• 索引列的类型尽量小
• 可以只对字符串值的前缀建立索引
• 只有索引列在比较表达式中单独出现才可以适用索引
• 为了尽可能少的让聚簇索引发生页面分裂和记录移位的情况，建议让主键拥有AUTO_INCREMENT属性。
• 定位并删除表中的重复和冗余索引
• 尽量使用覆盖索引进行查询，避免回表带来的性能损耗。

区（extent）

• 每16kb为一页，连续64个页就是一个区，默认占用1MB，每256个区被划分成一个组。
• 相邻页的物理位置可能离的很远，所以在进行读写的时候是随机IO，速度并不快
• 为了使用顺序IO，引入了区的概念，连续的64个区，在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位了，而是区。甚至数据非常多的时候可以一次性分配多个连续的区。
• 叶子节点和非叶子节点分别拥有各自的区，这些区的集合就是一个段，一个索引会有一个叶子节点段和一个非叶子节点段。
• 碎片区：由于直接分配一个区就是占用了1mb的内存，对于小表会造成浪费，所以提出了碎片区的概念，这个区中并不是所有的页都是同一个段的，可能是不同的段。碎片区直属于表空间，不属于任何一个段。只有当某个段已经占用了32个碎片区页面后，才会以完整的区为单位来分配存储空间。

访问方法

• const：主键或唯一约束索引的等值匹配，如果是多个列，则每一个列需要与常数进行等值比较
• ref：普通的二级索引，如果是key is null，不管是唯一二级索引还是普通二级索引，都是ref形式，因为NULL值的数量不限制.如果最左边的连续索引全都是等值比较,则还是ref,否则就不是了
• ref_or_null：普通的等值比较或者key is null则成为ref_or_null
• range：对二级索引进行等值或者某个范围的值
• index：如果可以直接通过遍历某个索引的子节点的记录来比较查询条件，且返回值包含在索引列中，不需要回表。把这种遍历二级索引记录成为index
• all：扫全表

索引合并：Intersection

MySQL在某些特定的情况下才可能会使用到Intersection索引合并：

• 情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只匹配部分列的情况。为什么？因为只有每个列全都等值匹配的情况下，才会对主键排序。只有在这种情况下根据二级索引查询出的结果集是按照主键值排序的。
• 情况二：主键列可以是范围匹配

按照有序的主键值去回表取记录有个专有名词儿，叫：Rowid Ordered Retrieval，简称ROR

Union合并

与Intersection索引合并类似，MySQL在某些特定的情况下才可能会使用到Union索引合并。其实就是union取并集，intersection取交集

• 情况一：二级索引列是等值匹配的情况，对于联合索引来说，在联合索引中的每个列都必须等值匹配，不能出现只出现匹配部分列的情况。
• 情况二：主键列可以是范围匹配
• 情况三：使用Intersection索引合并的搜索条件

Sort-Union合并

先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

连接的原理

嵌套连接

• 两表连接，驱动表（比如左连接的左表）

  • 步骤1：选取驱动表，使用与驱动表相关的过滤条件，选取代价最低的单表访问方法来执行对驱动表的单表查询。
  • 步骤2：对上一步骤中查询驱动表得到的结果集中每一条记录，都分别到被驱动表中查找匹配的记录。

• 可以对被驱动表加索引

• 有时候连接查询的查询列表和过滤条件中可能只涉及被驱动表的部分列，而这些列都是某个索引的一部分，这种情况下即使不能使用eq_ref、ref、ref_or_null或者range这些访问方法执行对被驱动表的查询的话，也可以使用索引扫描，也就是index的访问方法来查询被驱动表。所以我们建议在真实工作中最好不要使用*作为查询列表，最好把真实用到的列作为查询列表。

基于块的嵌套循环连接

• 嵌套连接中被驱动表可能需要访问很多次，如果不能用索引或者数据量大，则IO压力很大，要尽量减少被驱动表的访问次数
• 每次访问驱动表会加载到内存，然后去和驱动表中的一条记录做匹配，然后又从内存中删掉，循环很多次。所以提出了join buffer的概念，就是执行廉洁查询前申请一块固定大小的内存，先把若干条驱动表结果集中的记录装在这个join buffer中，然后开始扫描被驱动表，每一条被驱动表的记录一次性和join buffer中的多条驱动表记录做匹配，且都是在内存中完成的，减少了很多IO操作。且只有查询列表和过滤条件中的列会放到join buffer中，所以尽可能不要用*，这样join buffer可以存放更多的记录。

查询优化

• 移除不必要的括号

• 常量传递： a=5 and b>a -> a=5 and b>5

• 等值传递

• 移除没用的条件

• HAVING和WHERE合并：如果没有sum、max等聚集函数以及group by，那么会把having和where合并。

• 优先执行常量表，即主键或唯一二级索引的等值匹配

• 外连接消除：内连接的驱动与被驱动表可以互相转换，而外连接不行。指定的WHERE子句中包含被驱动表中的列不为NULL值的条件称之为空值拒绝（英文名：reject-NULL）。在被驱动表的WHERE子句符合空值拒绝的条件后，外连接和内连接可以相互转换。这种转换带来的好处就是查询优化器可以通过评估表的不同连接顺序的成本，选出成本最低的那种连接顺序来执行查询。

• 子查询

  • 对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别独立的执行外层查询和子查询，就当作两个单表查询就好了。如：会先查询子查询， 然后用子查询的值当做常数值进行外层查询

    SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1);
    
    

  • 对于相关的标量子查询，对外层查询的表进行扫描，每次获取一条记录，取出子查询中涉及到的列的值进行子查询，依次循环。如：

    SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);
    
    

• IN子查询

  物化表：如果子查询的结果集非常的大，那父查询的每条记录都要判断查询条件，会很耗性能，所以mysql会把子查询的结果集写入一个临时表（采用Memory存储引擎）里，列就是子查询的列，且结果会去重，并对列建立哈希索引，这样判断某个操作值在不在子查询结果集里就会很快。或者如果子查询结果集太大了，则会变成基于磁盘保存，索引变为B+树。这个过程叫做物化表。转换之后其实就可以变成内连接了

  SELECT * FROM s1 
      WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a');
  等价于
  SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val;
  

• semi-join：如果想要不通过临时表，直接转换成连接查询，则会产生问题，如果子查询的结果集中有多条数据没有经过去重直接内连接会有多条记录，于是提出了semi-join半连接，对s1的某条记录，我们只关心在s2表中是否存在与之匹配的记录，不关心有多少条，最终的结果集中只保留s1表的记录。

  • 转成半连接的条件：
    • 该子查询必须是和IN语句组成的布尔表达式，并且在外层查询的WHERE或者ON子句中出现。
    • 外层查询也可以有其他的搜索条件，只不过和IN子查询的搜索条件必须使用AND连接起来。
    • 该子查询必须是一个单一的查询，不能是由若干查询由UNION连接起来的形式。
    • 该子查询不能包含GROUP BY或者HAVING语句或者聚集函数。
  • 对于不能转为半连接的查询有两种优化方法：
    • 1、先物化再查询
    • 2、IN转EXISTS

• 执行带有派生表的语句时，会延迟物化。或者把派生表和外层的表合并来消除派生表。

执行计划

EXPLAIN语句输出的各个列的作用：

• select

  • 每一个表会有一条记录，如果是同一个select，那每个表的id一样。出现在前的为驱动表，后的为被驱动表
  • 每一个select会有一个唯一的id（如果被优化成连接查询则另谈）
  • 如果是union，会内部去重，通过生成临时表，所以会有一条临时表的记录，而union all则不需要

• select_type

  • simple：不包含子查询或者union的都是simple类型，包括连接查询
  • primary：包含union、union all 或者子查询的外层查询或最左侧查询，则是primary
  • union：除了最左侧查询，其他的都是union
  • union_result：union去重的临时表查询
  • subquery：如果包含子查询的语句不能转为semi-join，且是不相关子查询，那子查询的第一个select就是subquery，会被物化所以只执行一遍
  • dependent_subquery：同上，如果是相关子查询，则是dependent_subquery，不会物化所以可能会执行多次
  • dependent union：union或者union all中如果各个小查询都依赖于外层的话，除了最左边的小查询之外，其余的小查询都是dependent union
  • derived：对于派生表被物化的，派生表对应的子查询就是derived
  • materialized：如果物化之后与外层进行连接查询时，属性就是materialized

• partitions：

• type：代表的执行时的访问方法法： system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL

  • system：当表中只有一条记录并且该表使用的存储引擎的统计数据是精确的，比如MyISAM、Memory，那么对该表的访问方法就是system

  • eq_ref：通过主键或者唯一二级索引列等值匹配的方式进行访问的（如果该主键或者唯一二级索引是联合索引的话，所有的索引列都必须进行等值比较）与const的区别就是一个常数一个非常数

  • index_merge：Intersection、union、sort-union

  • unique_subquery：包含IN的子查询，如果可以转换为EXISTS，且子查询可以使用主键进行等值匹配的话

    • EXPLAIN SELECT * FROM s1 WHERE key2 IN (SELECT id FROM s2 where s1.key1 = s2.key1) OR key3 = 'a';
      

  • index_subquery与unique_subquery类似，只是访问子查询中的表时使用的是普通索引

• possible_keys和key

  • possible_keys表示可能用到的索引，key表示实际用到的索引
  • 如果用的是index方式，则不会显示possible_keys，只会显示真正用到的key

• key_len：索引记录的最大长度

  • 如果是固定长度，则是固定值，如果是变长，则是字符长度*字符所占的字节
  • 如果可以存储NULL值，则key_len比不可以存储NULL值时多一个字节
  • 对于变长字段，都会有2个字节的空间来存储该变长列的实际长度

• ref：当访问方法是const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery其中之一时，ref列展示的就是与索引列作等值匹配的东东是个啥，比如只是一个常数或者是某个列。

• rows：如果要用全表时，rows代表预计需要扫描的行数，如果使用索引来查询，就代表预计扫描的索引记录行数

• filtered：被驱动表占驱动表条数的百分比

• extra：代表的是额外的信息

  • SELECT * FROM s1 WHERE key1 > 'z' AND key1 LIKE '%a';
    这个查询虽然用到了key1的索引，但是like的后缀匹配用不了索引，所以正常需要查出前面的条件后回表然后进行过滤，但其实可以根据前面的条件查出的记录进行判断是否满足后面的条件，如果不满足可以直接不回表，减少随机IO。这就叫索引条件下推——Using index condition
    

• 查看执行计划的成本：

   EXPLAIN FORMAT=JSON SELECT * FROM s1
  

  缓存

​ 当我们访问一条记录时，要把这个页的数据都加载到内存中并且缓存起来。在mysql启动时，就像操作系统申请了一篇连续的内存，缓冲池——buffer pool，默认128M。可以通过innodb_buffer_pool_size来修改。池中的缓存页默认也是16kb

​ 每一个缓存页会有对应的控制块，占用一块内存(约为808字节,5%)，存放了控制信息（表空间编号、页号、缓存页的地址、链表节点信息、锁和LSN信息)

img

free链表

​ 用来管理哪些缓存页还没有被占用,也就是空闲的,会把每一个空闲缓存页对应的控制块加入到free链表中.每个节点占40字节,是单独申请的一块内存空间.每次加载了一个数据页后，就从free链表中取出一个空闲的缓存页，并把该缓存页对应的控制块的信息填上，然后从free链表中移除该控制块节点。

缓存页的哈希处理

​ 查找buffer pool中的缓存页通过表空间号+页号作为key，缓存页是对应的value。来组成一个哈希表

flush链表

​ 如果一个缓存页的数据被更新了，说明他是脏页了，会把这些脏页对应的控制块加入到flush链表中去

LRU链表

​ 如果缓存页已经用满了，如何删除掉不用的缓存页呢。通过最近最少使用算法来淘汰（同redis），所以需要维护一个LUR链表，当刚加载到缓存池中时，会放到链表的头部。每一次访问就把对应的控制块移动到头部，这样尾部的节点就是最近最少使用的缓存页了。

​ 问题：

• 预读：可能判断会读，但其实没读

• 全表扫描，会对缓冲池进行好几轮换血

  解决：

  • 把LRU链表按照比例分成冷热两块，默认冷链占37%，3/8左右。
  • 这样预读就会放在old链的头部
  • 全表扫描：首次加载的会被放到old区的头部。且在首次加载后的一定时间间隔内，再次访问改页的数据，不会把他放到young区（热链）默认1秒。
  • 只有在热链区域的1/4后面，才会被移动到LRU链表的头部，就可以降低调整的频率。

刷新脏页到磁盘

​ 后台有线程专门把脏页刷新到磁盘：

• 从LRU链表的冷数据中刷新一部分页面到磁盘。从尾部开始如果发现脏页就刷新到磁盘
• 从flush中刷新一部分到磁盘

多个buffer pool实例

​ 如果多线程且并发高的情况下，单一的pool会因为多线程加锁而影响处理速度，如果pool比较大的时候（大于1g，如果小于1g设置多个是没用的），会拆分成若干个小pool，单独申请内存空间，这样多线程并发访问时可以提高并发能力。5.7.5之后可以动态调整pool的大小，这时，重新申请，再把旧的拷过来太耗时，所以会把连续申请改为chunk为单位申请，所以一个pool是有若干个chunk组成。包含了若干缓存页的chunk。如果pool size变化的时候就通过chunk增减来完成。不需要重新申请再复制

redo 和undo

redo

redo日志会把事务在执行过程中对数据库所做的所有修改都记录下来，在之后系统崩溃重启后可以把事务所做的任何修改都恢复出来。

redo`日志占用的空间非常小且是顺序写入磁盘的；

• 存储表空间ID、页号、偏移量以及需要更新的值所需的存储空间是很小的
• 每执行一条语句就可能产生若干条redo日志

格式

img

• type：该条redo日志的类型。
• space ID：表空间ID。
• page number：页号。
• data：该条redo日志的具体内容。

​ 把一条记录插入到一个页面时需要更改的地方非常多，除了数据要修改以外，还有什么File Header、Page Header、Page Directory等等部分都需要修改。

​ 有的原子性操作会生成多条redo日志，需要分到同一个组内执行，会再最后一条redo日志后边加上一条特殊类型的redo日志，一组redo日志必须要以这个类型的redo日志为结尾。相当于分隔符

img

mtr：Mini-Transaction，向某个索引对应的B+树中插入一条记录的过程也算是一个Mini-Transaction

redo日志写入过程

block

​ 通过mtr生成的redo日志都放在了大小为512字节的页中叫redo log block

img

redo日志缓冲区

• 会申请一片redo log buffer，被划分成若干个连续的redo log block，写到log buffer中的redo日志是顺序的，先在前面的block中写，如果写满了，就往下一个写，所以有buf_free全局变量来标识redo应该写到哪个位置了。有可能会追尾，写到最早的lock里（写满了的话）
• 且写的时候往往是一个mtr的一组redo日志一起写。而且因为事务是可以并发执行的，所以不同事务的mtr可能是交替写入log buffer的

redo日志的刷盘

1. log buffer空间不足
2. 事务提交时
3. 后台线程定时刷新（1s）
4. checkpoint
5. 关闭

img

LSN（log sequence number）：初始为8704，表示redo日志所写到的位置，所以每一组mtr生成的redo都有唯一一个LSN，值越小说明redo日志越早

flushed_to_disk_lsn：已刷新到磁盘的redo log地址。

checkpoint：redo日志追尾！如果redo对应的脏页已经刷新到磁盘，则这一段redo已经没有用了，可以被后续的redo重用。checkpoint_lsn就是用来记录当前可以覆盖的redo对应的lsn值。

• 如果来不及从做checkpoint，则需要从flush链表中把那些最早修改的脏页刷新到磁盘。

redo日志的使用：崩溃恢复后，需要读取使用的redo日志可能很多，分布在不同的页，会把他们根据页号和空间号做hash放到hash表里，相同的key对应的redo日志放在同一个key的链表中，恢复的时候遍历哈希表，对每个页进行redo日志执行（且需要按照顺序）。

undo

事务id：只有在事务对表中的记录做改动时才会为这个事务分配一个唯一的事务id。

• INSERT：插入类型的undo日志主要记录主键信息，对应的删除该主键记录即可
• DELETE：如果是删除记录会分为两阶段（根据事务的进度）
  • 事务未提交，语句已执行，会把记录的delete_mask改为1，中间状态，此时还在正常记录的链表中
  • 事务提交后，把delete_mask标记为1的记录从正常记录链表中移除，加入到已删除链表的头部
  • 而事务提交后，不需要用到undo日志，所以其实只要保存第一阶段的undo日志即可。
• UPDATE：
  • 不更新主键
    • 就地更新：如果被更新的没格列，前后占用的存储空间一样大，可以直接在原纪录的基础上修改值
    • 先删旧记录，再插新记录：真正的删除记录，移到垃圾链表。如果新的记录占用的空间大小不超过旧的空间，则可以直接复用原来的空间，否则要在页面中新申请一段空间。如果页面没有新空间，需要进行页分裂。
  • 更新主键
    • 旧记录进行delete mark，因为如果这个事务没有提交，记录却删除了，则其他事务就无法根据主键找到该记录了。等到事务提交后再一到垃圾链表中，进行delete mark操作前会有一条undo日志
    • 重新插入新纪录。并产生一条undo日志

事务隔离级别

• 脏写：一个事务修改了另一个未提交事务修改过的数据（这里的未提交指的是该事务进行修改的时候，读到的数据还是未提交时候的数据而不是已提交完后的数据）
• 脏读：一个事务读到了另一个未提交事务修改过的数据，如果修改后回滚，读到了一个不存在的数据
• 不可重复读：一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值，简单来说就是一个事务内同一个查询可能返回不同的结果
• 幻读：如果一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来，那就意味着发生了幻读。幻读与不可重复读的区别重点是幻读强调了读取到之前没有读取到的记录。

严重程度：脏写 > 脏读 > 不可重复读 > 幻读

隔离级别：

也就是说：

• READ UNCOMMITTED隔离级别下，可能发生脏读、不可重复读和幻读问题。
• READ COMMITTED隔离级别下，可能发生不可重复读和幻读问题，但是不可以发生脏读问题。
• REPEATABLE READ隔离级别下，可能发生幻读问题，但是不可以发生脏读和不可重复读的问题。
• SERIALIZABLE隔离级别下，各种问题都不可以发生。
• 每种隔离级别都不允许脏写

​ MySQL默认是RR,但是互联网项目推荐RC。为什么默认是RR，因为在mysql5.0前，主从复制的binlog的格式为statement。顺序是先插后删，会造成先删后插的事务在master和slave中不一致，也就是主从不一致。

​ 在RR级别下，会存在间隙锁，出现死锁的概率高，且条件列未命中索引会锁表。而RC只锁行。

​ 在RC级别下的主从复制binlog要用row格式

MVCC(Multi-Version Concurrency Control)

版本链

• 每次对记录进行改动都会记录一条undo日志，每条undo日志会有一个roll_pointer属性（insert的没有，因为没有更早版本），可以将这些undo日志都连起来，串成一个链表，如：
  • img
  • 记录的每次更新都会把旧值放到一条undo日志中，随着更新次数增多，会形成一个链表，最新的会在链头。且会记录事务id

ReadView

核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。需要ReadView

• m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。

• min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。

• max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。

  小贴士： 注意max_trx_id并不是m_ids中的最大值，事务id是递增分配的。比方说现在有id为1，2，3这三个事务，之后id为3的事务提交了。那么一个新的读事务在生成ReadView时，m_ids就包括1和2，min_trx_id的值就是1，max_trx_id的值就是4。

• creator_trx_id：表示生成该ReadView的事务的事务id。

  小贴士： 我们前边说过，只有在对表中的记录做改动时（执行INSERT、DELETE、UPDATE这些语句时）才会为事务分配事务id，否则在一个只读事务中的事务id值都默认为0。

有了ReadView，就可以根据以下步骤判断记录的某个版本是否可见：

• 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
• 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
• 如果被访问版本的trx_id属性值大于或等于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启（对RC来说且肯定还未提交，否则此时生成的ReadView的max_trx_id应该会大于当前访问版本，对RR来说，在他之后开启的事务是不允许被读到的），所以该版本不可以被当前事务访问。
• 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

如果某个版本的数据对当前事务不可见，就顺着版本链找到下一个版本的数据。

READ COMMITTED —— 每次读取数据前都生成一个ReadView

所以每一次查询之间如果有新的事务提交，那根据上面的查找流程，每次都会找到最新一次提交的事务的记录

REPEATABLE READ —— 在第一次读取数据时生成一个ReadView

所以只有第一次查询的时候生成ReadView，之后每次查询不管是否有新的事务提交，都会查到相同的结果（第一次查询时候的结果）。

具体流程参考：https://juejin.im/book/6844733769996304392/section/6844733770071801870

MVCC小结

​ 从上边的描述中我们可以看出来，所谓的MVCC（Multi-Version Concurrency Control ，多版本并发控制）指的就是在使用READ COMMITTD、REPEATABLE READ这两种隔离级别的事务在执行普通的SELECT操作时访问记录的版本链的过程，这样子可以使不同事务的读-写、写-读操作并发执行，从而提升系统性能。READ COMMITTD、REPEATABLE READ这两个隔离级别的一个很大不同就是：生成ReadView的时机不同，READ COMMITTD在每一次进行普通SELECT操作前都会生成一个ReadView，而REPEATABLE READ只在第一次进行普通SELECT操作前生成一个ReadView，之后的查询操作都重复使用这个ReadView就好了。

小贴士： 我们之前说执行DELETE语句或者更新主键的UPDATE语句并不会立即把对应的记录完全从页面中删除，而是执行一个所谓的delete mark操作，相当于只是对记录打上了一个删除标志位，这主要就是为MVCC服务的。不然如果更新主键的记录直接删了，就没办法出现在版本链中，就没办法通过MVCC来找到对应的记录。

锁

一致性读

事务利用MVCC进行读取的操作叫做一致性读，或者一致性无锁读，也称为快照读。所有普通的SELECT在RC和RR下都是一致性读。不会加任何锁。

锁定读

• 共享锁：shared locks 简称s锁，在事务要读取一条记录时，首先获取该记录的s锁
• 独占锁：也成排他锁，exclusive locks，简称x锁，在事务要改动一条记录时，需要现获取该记录的X锁。

只有获取了S锁再获取S锁是可以的，而任何涉及到x锁的获取，都不能同时获取到x锁或者s锁：

对读取的记录加S锁：

SELECT ... LOCK IN SHARE MODE;

对读取的记录加X锁：

SELECT ... FOR UPDATE;

写操作

• DELETE：

  对一条记录做DELETE操作的过程其实是先在B+树中定位到这条记录的位置，然后获取一下这条记录的X锁，然后再执行delete mark操作。我们也可以把这个定位待删除记录在B+树中位置的过程看成是一个获取X锁的锁定读。

• 各个UPDATE：

  在对一条记录做UPDATE操作时分为三种情况：

  • 如果未修改该记录的键值并且被更新的列占用的存储空间在修改前后未发生变化，则先在B+树中定位到这条记录的位置，然后再获取一下记录的X锁，最后在原记录的位置进行修改操作。其实我们也可以把这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读。
  • 如果未修改该记录的键值并且至少有一个被更新的列占用的存储空间在修改前后发生变化，则先在B+树中定位到这条记录的位置，然后获取一下记录的X锁，将该记录彻底删除掉（就是把记录彻底移入垃圾链表），最后再插入一条新记录。这个定位待修改记录在B+树中位置的过程看成是一个获取X锁的锁定读，新插入的记录由INSERT操作提供的隐式锁进行保护。
  • 如果修改了该记录的键值，则相当于在原记录上做DELETE操作之后再来一次INSERT操作，加锁操作就需要按照DELETE和INSERT的规则进行了。

• INSERT：

  一般情况下，新插入一条记录的操作并不加锁，设计InnoDB的大叔通过一种称之为隐式锁的东东来保护这条新插入的记录在本事务提交前不被别的事务访问。

多粒度锁

表S锁：可以获取表S锁和行S锁，但是无法获取表X和行X锁

表X锁：无法获取任何维度的x和s锁。

在上表锁的时候，要确保行没有被加x锁，否则要等待，如何知道呢，通过意向锁（Intention Locks）

意向共享锁：（Intention Shared Lock）简称IS锁，当食物准备在某记录上加S锁的时候，需要先在表上加IS锁。

意向独占锁：（Intention Exclusive Lock），简称IX锁。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

• 如果要给表加S锁，要看表上有没有IX锁，如果有，则等待。

• 如果要给表加X锁，要看表上有没有IS或者IX锁，如果有，则等待

• IS、IX锁是表级锁，它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录，也就是说其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的。

  兼容性	X	IX	S	IS
  X	不兼容	不兼容	不兼容	不兼容
  IX	不兼容	兼容	不兼容	兼容
  S	不兼容	不兼容	兼容	兼容
  IS	不兼容	兼容	兼容	兼容

其他存储引擎中的锁

​ 对于MyISAM、MEMORY、MERGE这些存储引擎来说，它们只支持表级锁，而且这些引擎并不支持事务，所以使用这些存储引擎的锁一般都是针对当前会话来说的。比方说在Session 1中对一个表执行SELECT操作，就相当于为这个表加了一个表级别的S锁，如果在SELECT操作未完成时，Session 2中对这个表执行UPDATE操作，相当于要获取表的X锁，此操作会被阻塞，直到Session 1中的SELECT操作完成，释放掉表级别的S锁后，Session 2中对这个表执行UPDATE操作才能继续获取X锁，然后执行具体的更新语句。

InnoDB存储引擎中的锁

自增属性

• AUTO-INC锁：主要是给自增id用的，保证自增id的有序。插入执行完后才释放
• 采用一个轻量级的锁，（在生成完自增值后就释放）
• 也可以两种混着来，当知道要插入几条数据时用轻量，否则用auto-inc

行级锁

• Record Locks：记录锁（LOCK_REC_NOT_GAP），也就是前面讲的锁，分S和X锁。
• Gap Locks：SQL标准中，RR是会有幻读问题的，但是MySQL中解决了幻读。解决方案有两种——MVCC和锁。Gap锁是为了防止插入幻影记录(没法锁住将要加入的记录，会造成幻读)
  • 如果给id为8的记录加锁，那就不允许别的事务在id为8的记录前面的间隙插入新记录，也就是（3，8）这个区间的新纪录是不允许立即插入的。比如要插入4，他会先定位到该新纪录的下一条记录的值为8，有Gap锁，就会被阻塞。
• Next-Key Locks：相当于Gap和普通行锁的结合
• 插入操作在等待gap锁的情况下会生成一个锁结构，也就是插入意向锁，称为Insert Intention Locks。多个插入意向锁之间不会阻塞。
• 隐式锁：一个事务中的插入操作刚入完成后，并没有任何锁，此时另外的时候可以获取这个记录的X或者S锁。会先判断隐藏列中的事务id，如果该事务id活跃，则会帮助创建一个x锁，并自己进入等待。

InnoDB锁的内存结构

• 在同一个事务中进行加锁操作
• 被加锁的记录在同一个页面中
• 加锁的类型是一样的
• 等待状态是一样的

那么这些记录的锁就可以被放到一个锁结构中

img

行锁：记载了三个重要的信息：

• Space ID：记录所在表空间。
• Page Number：记录所在页号。
• n_bits：对于行锁来说，一条记录就对应着一个比特位，一个页面中包含很多记录，用不同的比特位来区分到底是哪一条记录加了锁。为此在行锁结构的末尾放置了一堆比特位，这个n_bits属性代表使用了多少比特位。