从根上理解Mysql

服务器处理客户端请求

连接管理

• 客户端可以采用TCP/IP、命名管道或者共享内存、Unix域套接字这几种方式之一来与服务器进程建立连接
• 每有一个客户端连接服务器时，服务器进程都会创建一个线程专门来处理这个客户端的交互
• 当客户端断开连接时，服务器并不会立即销毁这个线程，而是把它缓存起来，分配给新来的客户端

• Mysql服务器接收到的请求只是一个文本消息，该消息要经过各种处理

  解析与优化

  查询缓存

• Mysql会把查询的结果缓存到内存中，但是如果两个查询请求在任何字符上的不同都会导致缓存不会命中

• 如果查询请求中包含某些系统函数、用户自定义变量和函数，那这个请求不会被缓存
• Mysql缓存系统会监测每张表使用了Insert、Update、Delete等操作语句，表的缓存查询都将失效并被删除
• 虽然缓存有可能会提升系统性能，但维护开销大，从Mysql5.7.20开始，不推荐使用查询缓存，并在Mysql8.0中删除

  语法解析

  客户端发送过来的是一段文本，Mysql服务器会对这段文本做分析，判断语法是否正确，从文本中将要查询的表各种条件都提取出来放到Mysql服务器内部使用的一些数据结构上

  查询优化

  Mysql会对语句进行优化，如外连接转换为内连接、表达式简化等，优化的结果就是生成一个执行计划，这个执行计划表名了应该使用哪些索引进行查询，表之间点的连接顺序是什么样的

字符集和排序规则

简介

字符集：Mysql中通过字符集把字符映射成二进制
比较规则：比较这两个字符对应的二进制数据
重要字符集：utf8字符集表示一个字符需要使用1~4个字节，但我们常用的一些字符使用1~3个字节就可以表示
utf8mb3：阉割过的utf8字符集，只使用1~3个字节
utf8mb4：正宗的utf8字符集，使用1~4个字节
重要的比较规则：查看utf8的比较规则 SHOW COLLATION LIKE ‘utf8_%’;
各级别字符集和比较规则：服务器级别、数据库级别、表级别、列级别

客户端和服务器通信中的字符集

如果客户端在读取数据时解码的字符集和数据库编码的字符集不同就会出现乱码的现象

mysql中字符集转换

客户端发往服务器的请求本质上就是一个字符串，服务器向客户端返回的结果本质上也是一个字符串

InnoDB记录结构

InnoDB把记录以页的形式存储下来，页的大小为16kb，一般情况下，一次最少从磁盘读取16kb的内从到内存中，一次至少把内存中的16kb内容刷新到磁盘中

InnoDB行格式

InnoDB目前为止一共设计了4种不同类型的行模式：Compact、Redundant、Dynamic、Compressed

Compact行格式

变长字段长度列表

• MySQL支持一些变长的数据类型，如VARCHAR(M)，变长字段中粗出多少字节的数据是不固定的，所以要把这些字段的长度也存储下来
• 各个变长字段数据占用的字节数按照顺序逆序存放
• 存储占用字节数用1~2个字节来表示

  假设某个字符集表示一个字符最多要用字节数为W，对于变长类型VARCHAR(M)来说最多个存储M个字符，所以这个类型能表示的字符串最多占用的字节数是MW；假设它实际存储的字符串占用的字节数是L 所以确定使用1个还是2个字节表示真正的字符串占用的字节规则是： 如果MN <=255，那么使用一个字节来表示真正字符串占用的字节数 如果M*N >255，那么分两种情况，L<=127，则用1个字节来表示字节数，L>127则用2个字节表示字节数 如何区分当前字段是一个单独的字段长度还是半个字段长度 使用字节的第一位表示，0则表示该字节就是一个单独的字段长度，1则表示该字节就是半个字段长度

• 变长字段长度列表只存储非Null的列内容占用的长度
• 如果列中没有变长的字段，则没有变长字段长度列表

NULL值列表

• 统计表中存储NULL的列有哪些，如果没有存储NULL的列，则NULL值列表也不存在
• 将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列，1代表该列的值为NULL，0代表该列的值不为NULL
• NULL值列表必须用整数个字节的位表示，如果不足则高位补充0，例如表中有9个允许为NULL的列，则需要2个字节表示

记录头信息

• 固定由5个字节组成，也就是40个二进制位（具体到InnoDB页再详细介绍）

记录的真实数据

除了真实的数据，MySQL还会为每个记录默认添加一些隐藏列
分别为DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR为了美观写成下面展示，row_id不一定会有

InnoDB表主键策略优先级为：用户自定义、选取一个Unique键、InnoDB默认添加名为DB_ROW_ID的隐藏列

CHAR(M)列的存储格式

• 对于CHAR类型当列采用的是定长字符集时(如ascii字符集)，该列占用的字节数不会被加到变长字段长度列表，而如果采用变长字符集时，该列占用的字节数也会被加到变长字段长度列表

• CHAR(M)要求至少占用M个字节

  Redundant行模式

  是MySQL5.0之前的行模式，和Compact行模式不同大概点是记录额外信息的格式不同，Compact行模式比Redundant节省20%的空间，增加了CPU的使用率

  行溢出数据

  

• MySQL对一条记录最多可以占用65535个字节(不包含隐藏列和记录头信息)

• MySQL一页的大小是16k也就是16384个字节，如果字段大于本页存储范围，就会在记录真实数据出用20个字节存储指向其他存储数据页的地址，这20个字节也包含在其他页中占用的字节数
• 行溢出临界点：MySQL规定一页至少存放两行记录，如果只存储一列，临界点大概在存储数据在大于8098个字节

Dynamic和Compressed行格式

Dynamic行模式和Compact行格式基本一直，就是行溢出处理不同，Dynamic只会存储溢出全部数据的页信息

Compressed行模式和Dynamic行模式不同的是，Compressed行格式会采用压缩算法以节省空间

InnoDB数据页结构

FileHeader

• FileHeader是专门针对数据页记录的各种信息，各个类型的页都通用，固定38个字节
• FIL_PAGE_SPACE_OR_CHKSUM：当前页的校验和，对长字符串通过算法变成短字符串，方便比较
• FIL_PAGE_OFFSET：唯一确定一个页的号
• FIL_PAGE_TYPE ：页的类型，比如索引页(也就是数据页)、事务系统数据页、Undo日志页等
• FIL_PAGE_PREV 和 FIL_PAGE_NEXT：记录本页上一页和下一页页号，并不是所有页类型都有这两个属性

PageHeader

• 记录存数据页中存储的记录信息，固定56个字节
• PAGE_DIRECTION：表示最后一条记录的插入方向，假如新插入的记录比上一条记录要大，插入方向就是向右，反之插入方向是向左
• PAGE_N_DIRECTION：同一个方向连续插入的数量，如果方向改变则从零重新统计

User Records

页刚开始没有UserRecords这部分，我们每插入一条记录，会从 Free Space中划分一部分到UserRecords中，直到Free Space用完表名本页已经用完

记录头

在行的记录头中存储着一行记录的各种信息

• delete_mask：标记当前行是否被删除，删除置为1，因为移除记录其他记录在磁盘上重新排列非常消耗资源，所以删除记录先被打上标记进入一个所谓的垃圾链表，这部分空间会被重利用，之后有新插入的数据会覆盖掉这部分空间；delete_mask被置为1，和进入垃圾列表是两个步骤
• min_rec_mask：是否是B+树非叶子节点中最小的记录
• head_no：表示记录在页中的位置，插入的数据head_no是从2开始，列是按照主键从小到大排列的，head_no为0和1的记录是Infimum和Supremum，这两条数据存放在Infimum + Supremum区域

• record_type：表示当前记录的类型，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录
• next_record ：表示当前记录到下条记录的偏移量，形成一个单向链表，假如删除第二条记录，它的next_record会被置为0；next_record指向记录头和真实数据之间的位置，这样是方便向左读取数据信息，向右读取真实的数据，这也是之前所说变长字段长度列表、NULL值列表中的信息都是逆序存放的原因，是为了读取数据的方便

Page Directory

• 所有的记录包括最大最小列(不包括被删除的列)被分成几组
• 每组最大的那条记录的头信息n_owned表示本组有多少条记录
• PageDirectory存储着每组最后一条记录的偏移量，这些偏移量被称为槽(slot)
• 规定最下记录只存储所在分组只有一条记录，也就是它本身，最大记录的组有1~8条记录，其他组有4~8条记录
• 每次插入数据，会从页目录中找出槽中比插入数据主键并且差值最小的槽，向组中插入直到组的记录数达到8
• 等插入数据达到8时，再向中间插入数据，会被拆分成两个槽，一个槽4条数据，一个槽5条数据
• 在数据页中查找主键，先通过二分查找在页目录中找到主键所在的槽，通过槽中最小的记录向后遍历直到找到主键

File Trailer

• 操作页时会把页加载到内存中操作，操作完成再同步到磁盘中，为了检验数据写入是否完整，加入了FileTrailer进行校验，FileTrailer总共8个字节，和FileHeader一样，每个类型的页通用
• 前4个字节代表校验和，和FileHeader中的校验和一个意思，同步数据的时候先同步FileHeader中的校验和，等所有数据都同步完成同步FileTrailer中的校验和，如果两者不一致说明同步出了问题
• 后4个字节代表页最后被修改时对应的日志序列位置（LSN），也是为了校验页的完整性

B+树索引

索引

• 页与页之间通过指针相互连起来，物理上并不连续，页的编号也不连续
• 假如一个页只能容纳3数据，当当前页数据已经达到3条，又有数据插入的时候，就会产生页分裂
• 页之间的主键保持顺序排放

• 针对数据页做一个目录项，包含页号和这个页的最小主键，这个目录就称之为索引

InnoDB索引

• 目录项和用户数据相似，只不过只有两个列页号和主键，他们用的数据页类型都是一样的，结构也一样
• 用记录头中的record_type属性来区分用户记录和目录项记录

• 如果一页的目录项太多不够存储，就会再生成一个目录页，但新生成的目录页物理上不连续，所以会再生成一个更高级的页来记录这两个目录页

不同的索引

聚簇索引

• 主键索引也就是所谓的聚簇索引，用户所有的数据都存放在叶子节点上，聚簇索引就是InnoDB存储数据的方式，也就是所谓的索引即数据，数据即索引

  二级索引

• 除主键之外的其他列创建的索引称为二级索引，二级索引叶子节点只有索引列和主键两列，在二级索引上查找到所对应的主键，再到聚簇索引中进行回表查询

  联合索引

  比如我对c2和c3列建立一个联合索引，各记录页会先按照c2的数据进行排序，当c2记录相同时再比较c3
  叶子节点中存储着c2、c3和主键，非叶子节点存储着c2、c3和页号

InnoDB索引注意事项

根页面万年不动

• 每当创建索引时，会为这个索引创建一个根页面，当没有数据的时候根页面也没有任何的数据
• 当用户向表中插入数据，会先在根节点中插入数据，当根节点的空间用完时，会把根节点中的所有数据都复制到另一个页a中，后续如果a页空间不足，对a页进行页分裂生成页b，而根节点升级为存储记录项的目录页
• 一个索引的根节点从创建起就不会更改位置，根节点页号会被记录到一个地方从而来访问这个索引

内节点目录项的唯一性

• B+数索引的内节点的目录项为索引列+页号，如果二级索引中只有索引列+页号，当新插入一条数据，索引项相同时，怎么确定向哪个数据页插入数据，所以要把主键也放到内节点目录项中，这样在比较索引列相同后可以比较主键，决定放到哪个数据页中

索引的使用

索引的代价

• 空间代价：每建一个索引都会为其建立一课B+树，每个数据页都会占用16k的空间
• 时间代价：每次对表的增删改都要对索引数进行维护

适用条件

假设建立一个idx_name_birthday_phone_number的索引

全值匹配

• 下面这个查询语句都是全值匹配，所以可以使用到索引

  SELECT * FROM person_info WHERE name = ‘Ashburn’ AND birthday = ‘1990-09-27’ AND phone_num

  ber = '15123983239'; 

• 下面这个查询虽然和索引的顺序不同，但是会被mysql的优化器进行调整，也会用到索引

  SELECT * FROM person_info WHERE birthday = ‘1990-09-27’ AND phone_number = ‘15123983239’ A ND name = ‘Ashburn’;

匹配最左侧列

• 下面的查询只能用到name索引，用不到phone_number索引，但如果包含name列则用不到索引

  SELECT * FROM person_info WHERE name = ‘Ashburn’ AND phone_number = ‘15123983239’;

匹配列前缀

• 按照字符串排列的时候会一个字符串一个字符串比较，所以当用到下属查询可以用到索引

  SELECT * FROM person_info WHERE name LIKE ‘As%’;

• 但当没有符合匹配列前缀规则时，就用不到索引

  SELECT * FROM person_info WHERE name LIKE ‘%As%’;

匹配范围值

• 当对索引中最左侧的列进行范围查询时可以用到索引

  SELECT * FROM person_info WHERE name > ‘Asa’ AND name < ‘Barlow’;

• 但如果用到多个列，只能对索引最左侧的列用到索引排序，剩下的列用不到索引

  SELECT * FROM person_info WHERE name > ‘Asa’ AND name < ‘Barlow’ AND birthday > ‘1980-01-01’

精确匹配某一列并范围匹配另一列

• 当对联合索引中最左侧列精准查找，右侧列范围查找也可用到索引，下面查询语句name和birthday用到了索引，但是phone_number没有用到索引

  SELECT * FROM person_info WHERE name = ‘Ashburn’ AND birthday > ‘1980-01-01’ AND birthday < ‘2000-12-31’ AND phone_number > ‘15100000000’;

用于排序

• 一般情况下排序要把数据加载到内存中，再利用排序算法进行排序，如果数据量过大，就把数据放到磁盘中进行排序，在Mysql中把这种排序称之为文件排序(filesort)

• 联合索引排序必须符合最左原则才能用到索引

  SELECT * FROM person_info ORDER BY name, birthday, phone_number LIMIT 10;

• 当排序ASC和DESC混合使用的时候不能使用到索引

  SELECT * FROM person_info ORDER BY name, birthday DESC LIMIT 10;

• where子句中出现非索引字段时不能使用索引

  SELECT * FROM person_info WHERE country = ‘China’ ORDER BY name LIMIT 10;

• 排序列包含非索引列不能使用索引

  SELECT * FROM person_info ORDER BY name, country LIMIT 10;

• 排序字段如果使用函数也不能使用索引

  SELECT * FROM person_info ORDER BY UPPER(name) LIMIT 10;

用于分组

• 和排序类似，必须符合最左原则才能使用到索引

  SELECT name, birthday, phone_number, COUNT(*) FROM person_info GROUP BY name, birthday, phone_number

回表的代价

• 查询时在二级索引中查出数据，由于索引中的数据是排好序的，所以数据会在一页或者连续多页中，IO读取属于顺序IO，但在回表的时候id值并不一定相连，所以回表的时候是随机IO
• 回表的次数越多二级索引的效率就越低，假如二级索引查出90%以上的数据，还不如直接去扫描全表
• MySQL会对查询进行优化，如果二级索引查询效率过低，就会直接扫描全表

覆盖索引

• 如果查询的字段覆盖住索引的列，则不用回表查询，提高查询效率

  SELECT name, birthday, phone_number FROM person_info WHERE name > ‘Asa’ AND name < ‘Barlow’

MySQL的数据目录

• 存储引擎其实就是把数据存储在文件系统上

• MySQL运行中产生的数据会放到数据目录中

  数据库在文件系统中表示

• 当我们创建一个数据库的时候其MySQL会在数据目录下创建一个子目录，同时会在子目录中创建db.opt文件，文件中包含了该数据库的各种属性，比如说字符集

  表在文件系统中表示

• 表结构的定义：会在库的目录下创建一个表名.frm的二进制文件，用于描述表结构

• 表中的数据：数据是存储在页中的，页是存储在表空间中，表空间是一个抽象的概念，其实就是对应文件系统上一个或多个真实文件
• 系统表空间：在数据目录下会创建一个名为idbatal的文件，初始大小为12M，会自动扩展，从MySQL5.5.7到MySQL5.6.6之间的各个版本中，表中的数据都是默认被存储到系统表空间中
• 独立表空间：在MySQL5.6.6之后会为每一个表建立一个独立表空间，会在数据库对应的目录下创建一个文件名.ibd的文件用来存储表数据

  视图在文件系统中的表示

  视图其实是虚拟的表，其实是某个查询语句的别名，存储视图的时候不需要存储真实的数据，只需要把它的存储结构存储起来，只会在所属数据库的目录下创建一个视图名.frm的文件

文件系统对数据库的影响

• 数据库名称和表名称不得超过文件系统所允许的最大长度
• MySQL会把数据库名和表名中所有除数字和拉丁字母以外的所有字符在文件系统中映射成@+编码值的形式
• 文件长度受文件系统最大长度限制

MySQL系统库简介

• mysql：核心库，存储着MySQL的用户账户和权限信息，一些存储过程、事件的定义，一些日志等信息
• information_schema：存储着MySQL所维护的其他数据库的信息，比如哪些表、哪些视图、哪些触发器、哪些列、哪些索引等，这些信息并不是用户的真实数据，只是一些描述信息
• performance_schema：主要保存了MySQL服务器运行过程中的一些状态信息，算是一个性能监控
• sys：这个库主要是通过视图形式把information_schema和performance_schema结合起来

InnoDB表空间

表空间是一个抽象的概念，对于每个独立的表空间对应文件系统中的一个.ibd文件，表空间可以看做存储页的大池子

区的概念

• 对于16k的页来说，连续64个页就是一个区，也就是区默认占用1M的空间
• 对于少量的数据确实没有必要引入区的概念，但是当数据量多的时候，范围查询虽然可以用叶子节点的链表，但如果物理存储空间不在一起，会产生随机IO，所以为了尽量让页之间物理位置也相邻，提出了区的概念
• 当数据量大的时候，就以区为单位分配空间

每256个区划分成一组，其中第一组最开始的三个页是固定的，分别是

• FSP_HDR类型：用来登记表空间的一些整体属性，还有0~255区中的属性
• IBUF_BITMAP类型：本组中所有区中页关于INSERT BUFFER的信息
• INODE类型：存储了称为INODE的数据结构

后面的组的前两个区是固定的：

• XDES类型：全称是 extent descripto，用于存储本组中区的属性，FSP_HDR和他类型，只不过FSP_HDR还存储了表空间的一些属性
• IBUF_BITMAP类型：本组中所有区中页关于INSERT BUFFER的信息

段的概念

• 当数据量大的时候，如果不区分叶子节点和非叶子节点，范围查询的时候效果大打折扣，所以InnoDB把叶子节点和非叶子节点分到了不同的区，这些存放同种类型的区就成为一个段，也就是一个索引会生成两个段，一个叶子节点段和一个非叶子节点段
• 对于数据量少的表，如果也生成两个段对空间有很大的浪费，所以提出了碎片区的概念，碎片区中的页并不是存储同一个段的数据，碎片区直属于表空间，不属于任何段
• 一个表数据刚进来的时候，在碎片区以一个页为单位分配给段，当一个段在碎片区占用32个页后，就以区为单位分配

区分类

区分为四个类型，也分别对应着四个状态：FREE、FREE_FRAG、FULL_FRAG、FSEG
其中FREE、FREE_FRAG、FULL_FRAG直属于表空间，FSEG直属于某个段

XDES Entry

XDES Entry全称Extent Descriptor Entry，用于管理区，每个区都对应着一个XDES Entry结构，里面记录了区的一些属性，XDES Entry结构如下：

• Segment ID：每一个段都有的一个唯一ID
• List Node：可以将若干个XDES Entry关联成一个链表，只需要指定的页号和在该页中的偏移量

• State：表明该区状态

• Page State Bitmap：共有16个字节也就是128个比特位，每2个比特位代表一个页，其中第一个比特位代表页是否空闲，第二个比特位暂时还未使用

  XDES Entry链表

• 表空间为了管理区，把区按照不同状态划分成三个XDES Entry链表，分别为FREE链表、FREE_FRAG链表、FULL_FRAG链表，当段数据不多时，插入数据先从FREE_FRAG链表中取碎片区中的页，当FREE_FRAG没有空闲时，从FREE链表中取空闲页存储数据，并改变状态

• 当段使用超过32个零散页后，就要申请完整的区，每个段维护了三个XDES Entry链表，分别为FREE链表、NOT_FULL链表、FULL链表

链表基节点

把某个链表的基节点放到表空间的固定位置，这样就能找到链表了

段结构

段只是一个逻辑上的概念，由一些分散的页和一些完整的区组成，InnoDB用INODE Entry结构来记录段中的属性

• Segment ID：段id
• NOT_FULL_N_USED：NOT_FULL链表中使用了多少页，当需要使用页的时候不需要从表头遍历
• 三个类型的链表基节点
• Magic Number：标识INODE Entry是否被初始化
• Fragment Array Entry：对应着零散页的页号

各个类型的页

上面所提到的XDES Entry和INODE Entry还有对应的链表，这些都存储在什么地方如下

FSP_HDR类型

存储表空间的一些整体属性和第一个组的XDES Entry


其中File Space Header中存储了表空间ID、表空间占用页面数、三种链表的基节点、INODE FREE和FULL链表等信息

XDES类型

和FSP_HDR中XDES区域类似

INODE类型

所有段的INODE Entry都存放在统一的页中


一个页只能存储85个INODE Entry，如果表空间中超过85段，需要额外的INODE页来存储，其中就用List Node for INODE Page List来存储上一个页和下个页

系统表空间

除了extent0记录系统属性，extent 1 和 extent 2 这两个区也就是128个页面被称为 Doublewrite buffer ，也就是双写缓冲区，上述大多都涉及事务

InnoDB数据字典

为了更好的管理数据而存储着元数据，InnoDB定义了一些内部系统表，都以B+数的形式保存在系统表空间下

单表访问

下面讲述围绕着一个表举例，表中包含索引如下

为 id 列建立的聚簇索引。
为 key1 列建立的 idx_key1 二级索引。
为 key2 列建立的 idx_key2 二级索引，而且该索引是唯一二级索引。
为 key3 列建立的 idx_key3 二级索引。
为 key_part1 、 key_part2 、 key_part3 列建立的 idx_key_part 二级索引，这也是一个联合索引。

访问方法

CONST

通过主键或者唯一索引来定位一条记录称之为CONST，意思是常熟级别，代价可以忽略不计

ref

SELECT * FROM single_table WHERE key1 = ‘abc’;

一个二级索引匹配到多列，回表的代价不是很大，速度也就比CONST差一点，这种称之为ref
但是需要注意联合索引，必须符合最左原则，并且都为等值查询，否则无法使用ref，如下

SELECT * FROM single_table WHERE key_part1 = ‘god like’ AND key_part2 > ‘legendary’;

ref_or_null

SELECT * FROM single_demo WHERE key1 = ‘abc’ OR key1 IS NULL;

查询时不仅有索引列的等值查询，还是索引列的null值查询，这种称之为ref_or_null

range

SELECT * FROM single_table WHERE key2 IN (1438, 6328) OR (key2 >= 38 AND key2 <= 79) ;

使用索引(包含聚簇索引)的时候进行范围多值的匹配，称之为range

index

SELECT key_part1, key_part2, key_part3 FROM single_table WHERE key_part2 = ‘abc’;

虽然key_part2并不符合联合索引最左原则，但是查询项都属于联合索引的列，所以只需扫描索引即可，不用回表，这种方式称之为index

all

直接扫描聚簇索引的方式称之为all

注意事项

当出现不能使用索引的列时，就把这个条件变为true

SELECT FROM single_table WHERE key2 > 100 AND common_field = ‘abc’; 变换后为 SELECT FROM single_table WHERE key2 > 100 AND true SELECT * FROM single_table WHERE key2 > 100

上面这个例子就可以使用索引

SELECT FROM single_table WHERE key2 > 100 OR common_field = ‘abc’; 变换后为 SELECT FROM single_table WHERE key2 > 100 OR true SELECT * FROM single_table WHERE true

上面这个例子就变成扫描全表，就使用不了索引

索引合并

Intersection合并

SELECT * FROM single_table WHERE key1 = ‘a’ AND key3 = ‘b’;

从两个二级索引中取出具体的id值并做交集再回表进行查询，这就进行了索引的Intersection

• 可以只读一个索引，再回表查询出数据，再进行进一步的筛选
• 虽然多读一个索引比只读一个索引有消耗，但是索引是顺序IO，而回表是随机IO，减少了回表的IO，总体上还是减少了消耗

MySQL只会在特定情况下使用Intersection合并

• 二级索引出现等值匹配，因为只有等值匹配查出的数据id是顺序排好的，这样取交集更快
• 主键可以使用范围匹配

就算符合上述两个条件，也不一定会有Intersection合并，要优化器去自己判断是否需要

Union合并

SELECT * FROM single_table WHERE key1 = ‘a’ OR key3 = ‘b’ ;

和Intersection相反，使用的是两个索引的并集
MySQL只会在特定情况下使用Union合并

• 二级索引出现等值匹配
• 主键可以使用范围匹配
• 使用Intersection合并，比如把两个Intersection合并的结果再取并集

就算符合上述三个条件，也不一定会有Union合并，要优化器去自己判断是否需要

Sort-Union

Union合并必须是等值查询，因为查出的id是顺序的，但如果使用到索引的范围查询，如下

SELECT * FROM single_table WHERE key1 < ‘a’ OR key3 > ‘z’

可以先分别根据索引查询出相应的数据，然后进行排序，再取并集，这种称为Sort-Union

连接原理

当使用连接查询时，会先确定好一个驱动表，会先查一次驱动表，查询出相应的值后再查询被驱动表，驱动表只会被查询一次，被驱动表会被查询好多次

嵌套循环连接

• 连接会先查一次驱动表，再多次查询被驱动表，如果有3个表连接，则第二表称为驱动表，再重复上述过程
• 这种执行单表查询后再取查被驱动表的方式称之为嵌套循环连接
• 可以使用索引来提高连接查询的效率

基于块的嵌套循环连接

• 扫描表实际上是先把表的数据加载到内存中，再进行比较看是否符合要求，如果数据量太大，会先加载一部分，比较完成后释放，再加载后续数据，如果嵌套循环连接，会把被驱动表的数据反复加载到内存中
• 由于上述过程会有大量的IO操作，所以有了join buffer，把驱动表查出的结果集放到join buffer中，批量和被驱动表中的数据进行匹配，这种方式称之为基于快的嵌套循环连接

Mysql基于规则的优化

• Having子句和where子句的合并
  • 如果查询语句中没有出现如SUM、MAX等聚集函数以及GROUP BY子句，优化器会把Having子句和where 子句合并起来
• 外连接消除
  • 如果where中有对被驱动表不为null或者指定值的条件，称之为空值拒绝，这样外连接就能变成一个内连接。这种好处就是优化器可以根据表的信息选出查询成本最低的连接顺序

子查询优化

• 子查询出现在select后、from后where或on语句中
• 出现在from后的子查询，就好像又生成了一张表一样，mysql称之为派生表
  • 对于派生表会先看能否与外层表查询合并，如果可以合并成一个查询
  • 不能合并查询则进行物化后再查询
• 结果集区分子查询
  • 标量子查询：只返回一个值
  • 列子查询：返回一列，不一定是多值
  • 表子查询：返回包含多条多列
• 按外层查询关系分类：如果不依赖外层查询值则为不相关子查询，反之则称为相关子查询

标量或列子查询方式

• 不相关标量子查询或列子查询：先执行子查询，再执行外层查询
• 相关标量子查询或列子查询：例如 SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s2.key3 LIMIT 1);
  • 会先从外层取一条数据，然后作为条件带入子查询
  • 带入后看查询是否符合条件，符合就录入到结果集中，否则丢弃，循环这个步骤

in子查询

• 物化表：将查询结果存放到一个临时表中并去重，会为表建立索引，如果数据不多则放到内存中建立一个hash索引，如果数据太多则落到磁盘中，建立一个B+索引。把这种存储子查询数据的临时表称之为物化表
• 物化表连接：子查询物化后，会和外部查询进行内连接继续查询，mysql优化器再选出谁作为驱动表来进行查询
• 由于相关子查询并不是一个独立的查询，所以不能转换为物化表来执行查询。
• semi-join：对于in子查询例如
  SELECT FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = ‘a’);
  可以转变成一个内连接
  SELECT s1. FROM s1 INNER JOIN s2 ON s1.key1 = s2.common_field WHERE s2.key3 = ‘a’;
  只不过，s1一条数据可能对应条件 s1.key1 = s2.common_field存在多条s2的数据，所以不能完全看做是一个内连接，可以称之为半连接（semi-join）
  • Table pullout (子查询中的表上拉)：如果子查询的列为主键或者唯一索引，就可以变为内连接
  • DuplicateWeedout execution strategy (重复值消除)：s1中对应s2中多条数据，避免重复添加到结果集中，就会建立一张临时表存储结果集的id，如果向临时表中插入失败，则代表结果集中存在数据
  • LooseScan execution strategy (松散索引扫描)：如果子查询用到索引，则从索引中取出不重复的数据，再到外部查询
• semi-join使用条件：不是所用的in操作都可以转化为半连接，如下
  • 外层操作包含其他查询条件和in子查询用OR连接
  • 使用not in而不是in
  • select中的in子查询
  • 子查询中包含group by、having
  • 子查询中包含union

Explain详解

字段详解

• table：一个表对应一条执行计划，table就代表着表名
• id：每对应一个select关键字，都会出现一行，并对应赋值一个id
  • 对于连表查询id相同，出现在前面的表为驱动表
  • 子查询id会赋值不同，但是查询优化器有可能将子查询重写优化成连接查询
  • union例如（EXPLAIN SELECT FROM s1 UNION SELECT FROM s2;）
    执行计划会出现3条，因为union会把查询结果去重，会建立一张临时表
• select_type：表示查询类型
  • simple：查询语句中不包含union或子查询的都算simple类型，连接查询也算simple查询
  • primary：对于包含union、union all或者子查询的大查询，它是由好多个子查询组成，最左侧的查询的select_type则为primary
  • union：对于包含union或者union all的查询，除了最左侧的查询，其余都是union类型
  • union result：mysql使用临时表对union语句进行去重
  • subquery：如果不相关子查询不能使用semi-join，但子查询进行了物化，则子查询的类型为subquery
  • dependent subquery：如果子查询为相关子查询，子查询第一个select类型为dependent subquery，可能会被执行多次
  • dependent union：包含union或union all的大查询中，子查询依赖外层查询，则除最左侧查询外其余都为dependent union
  • derived：对于采用物化方式包含派生表的查询，派生表对应的子查询为derived
  • materialized：将子查询优化和外层查询进行连接查询时，子查询对应的查询类型为materialized
• type：表示了查询的访问方式
  • system：表中只有一条记录，并且该表使用的存储引擎的统计数据是准确的
  • const：根据主键或者唯一索引进行常数比较时
  • eq_ref：连接查询时，被驱动表通过主键或者唯一索引列连接
  • ref：通过普通二级索引和常量进行等值比较，访问方式可能为ref
  • ref_or_null：对于普通二级索引，索引列允许为null，访问方式可能为ref_or_null
  • index_merge：使用索引合并的方式进行查询
  • range：使用索引获取范围记录，可能用到range访问方式
  • index：可以使用索引覆盖， 但要扫描全部的索引记录，例如联合索引一个在select中一个在where中
  • all：全表扫描
• possible_keys和key：possible_keys表示可能用到的索引，key表示实际用到的索引，当type为index时，possible_keys为空，possible_keys并不是越多越好，多的话可能增加优化器选择时间
• key_len：使用索引列所记录的最大长度
• ref：当使用索引进行等值查询时，ref展示等值比较的常数(const)或者列或者函数
• rows：预计查询需要扫描的行数
• filtered：查询优化器预测有百分之多少的列符合查询条件，主要用于计算在里连表查询时被驱动表还要进行多少次查询
• extra：额外信息，以下举个别例子
  • Using index：当查询列表或条件只包含索引列时
  • Using index condition：搜索条件中虽然出现了索引列，但没有用到索引
  • Using where：使用全表扫描，并且有where条件

更详细查看

• 使用EXPLAIN FORMAT=JSON可以查询具体的查询成本
• 在mysql5.6之后使用optimizer_trace可以查看优化器优化过程，可以通过SET optimizer_trace=”enabled=on”;来设置optimizer_trace开启

InnoDB Buffer

我们哪怕只访问一个页的一条数据，也会把整个页加载到内存中

Buffer pool

• Mysql启动时会向操作系统申请一片连续的内存空间叫做buffer pool，可以设置innodb_buffer_pool_size来设置大小

控制块：为了更好的管理数据页，会为每个页创建存储页信息的控制块，存储着表空间编号、页号、缓存页地 址、锁和LSN等信息，每个控制块的大小是固定的，控制块和缓存页是一一对应的，控制块放到buffer pool的前面，缓存页放到buffer pool的后面，每个控制块越占用缓存页大小的5%

free链表

用于存储buffer pool中空闲页信息的链表，如需加载页到缓存中，只需从free链表中取空闲页的信息，并从链表中移除

缓存页hash处理

用缓存页的表空间号+页号来作为哈希表的key，缓存页的地址作为value，这样就可以快速定位一个页是否在缓存中

flush链表

如果缓存页中的数据被修改，这个页就被称为脏页，脏页不会立即刷线到磁盘中，因为刷新到磁盘很影响性能，所以InnoDB维护了一个链表来存储脏页的信息，这个链表称之为flush链表

刷新脏页

后台有一个线程，会每隔一段时间就刷一下脏页到磁盘中，主要有两种刷新方法

• BUF_FLUSH_LRU：从LRU链表的冷区中刷新一部分页到磁盘中，从冷区尾部扫描innodb_lru_scan_depth的页，发现脏页就同步到磁盘中
• BUF_FLUSH_LIST：后台线程会定时从flush链表刷新一部分页到磁盘中，刷新时机取决于系统忙不忙

还有主动刷新的情况
BUF_FLUSH_SINGLE_PAGE：当缓存页不够用时，会从LRU尾部取出缓存页，如果缓存页是脏页，则会强制刷新脏页

LRU链表

• 当缓存页不够用的时候就需要淘汰旧的缓存页来让新的缓存页加入，维持一个LRU链表用来淘汰不常用的缓存页
• LRU链表分为冷区和热区，用来区分常用数据和不常用数据，可以通过设置innodb_old_blocks_pct来设置冷区占LRU的比例

LRU优化

但是会存在劣币驱逐良币的情况，比如

• 因为预读的机制，加载到缓存中的页的不一定被用到，可通过冷热区来防止

  预读：当读取当前页时，InnoDB认为你会读取当前页之后的数据，就会预先把一些页加载到缓存中

  • 线性预读：根据变量innodb_read_ahead_threshold，如果访问某个区的页面超过这个参数设置的值，就会通过异步的方式加载下个区所有的数据到缓存中
  • 随机预读：如果buffer pool中已经缓存了某个区的13个连续的页，并且在热区的钱1/4，会触发异步读取本区所有页到缓存中，通过innodb_random_read_ahead控制开启，默认是关闭的

• 大量读取频率较低的数据被加载到缓存中

  对一个页访问一条数据算访问一次，取出一个页中的全部数据相当于访问了好多次，在冷区对数据访问第一次会记录访问时间，当间隔时间不超过innodb_old_blocks_time(默认是1s)，就不会从冷区加载到热区，这样可以防止扫描全表对一个页进行大量的读取操作

进一步优化

被访问的数据位于热区的前1/4，则不会移动到LRU链表的头部，否则移动到LRU头部

Buffer Pool其他信息

• 可以设置多个buffer pool实例，每个实例单独用一个线程控制，可以提高系统的并发率

• 但是管理多个buffer pool实例也有开销，当buffer pool值小于1G的时候设置多个实例是无效的

  chunk

• 在Mysql5.7.5之前是不允许在mysql运行时修改buffer pool的大小，后续InnoDB将buffer pool以chunk划分，一个buffer pool由多个chunk组成，一个chunk是一片连续的空间

• 因为chunk的概念出现，可以在运行的时候以chunk为单位调整buffer pool的大小
• 通过innodb_buffer_pool_chunk_size来设置chunk的大小，运行期间不能修改

事务简介

原子性（Atomicity）：一个事务要么全部成功，要么全部不成功
一致性（Consistency）：事务执行从一个一致性状态转变为另一个一致性状态
隔离性（Isolation）：每个事务之间是隔离的，互不影响的
持久性（Durability）：事务对数据库的影响是持久性的

事务状态

• 活动的：事务正在操作数据库
• 部分提交的：当事务最后一个操作执行完成，但由于操作都是在内存中执行，并没有刷新到磁盘中，这时事务的状态为部分提交
• 失败的：当事务处于活动的或部分提交的状态，因为某些错误而无法继续执行，则状态为失败的
• 中止的：当位于失败的状态，回滚完成状态变为中止的
• 提交的：当事务处于部分提交的状态，数据被刷新到磁盘后变为提交的状态

redoLog

简介

• 想要提交的事务对数据库的影响永久生效，但每次刷磁盘太消耗性能，所以只记录每次修改了什么的日志就好，系统崩溃了按照以上日志重新做一边就好，所以这样的日志称之为redoLog；
• redoLog比直接刷新到磁盘有两个好处：redoLog占用空间小、redoLog是顺序写入磁盘的

日志格式

type：redoLog的类型（5.7.21中一共有53种类型）
space ID：表空间ID
page number：页号
data：redoLog具体内容

日志类型

• 简单的日志类型：包含很多种(例如自增id的维护)，一般记录在某个页某个偏移量修改了几个字节，具体修改了什么内容，这种日志称之为物理日志
• 复杂的日志类型：包含很多种(例如向表中插入一条新的数据)，需要修改一个或者多个页的很多地方，这种日志有两种方面的含义
  • 物理层面：指明对哪个表空间的哪个页进行修改
  • 逻辑层面：在系统恢复时，需要调用准备好的函数，把日志的内容作为入参才能恢复数据

Mini-transaction

以组形式写入redoLog

• 执行语句中产生的redoLog会被分成好几个不可分割的组，每个组都是一个小的事务，例如

  • 向聚簇索引对应 B+ 树的页面中插入一条记录时产生的 redo 日志是不可分割的。
  • 向某个二级索引对应 B+ 树的页面中插入一条记录时产生的 redo 日志是不可分割的。

• 乐观插入：如果向索引页中插入一条数据，如果页还有空间，则直接插入数据即可，只产生一条redoLog（特殊情况会产生多条，不考虑）

• 悲观插入：如果页空间不足，就会出现页分裂，会产生多条redoLog

• 在一组redoLog后加一个类型为MLOG_MULTI_REC_END的日志，表示一组redoLog的结束；如果解析redoLog发现没有MLOG_MULTI_REC_END日志，则前面的redoLog无效；
• 如果redoLog组只有一条日志，则会在redoLog的type第一位标识是否是单一日志；

Mini-transaction概念

对底层页中的访问称之为一次Mini-transaction，简称为mtr

redoLog写入过程

redoLog block

把mtr产生的redoLog日志放在在一个512b的页中，这个页称之为block

LOG_BLOCK_HDR_NO：唯一标识block
LOG_BLOCK_HDR_DATA_LEN：表示block用了多少个字节
LOG_BLOCK_FIRST_REC_GROUP：代表block中第一个mtr生成的redoLog组的偏移量
LOG_BLOCK_CHECKPOINT_NO：checkpoint序号
LOG_BLOCK_CHECKSUM：检验值

redoLog缓冲区

redoLog写入磁盘速度过慢，所以引入log buffer，log buffer写到哪了通过全局变量buf_free记录

redoLog刷盘时机

• log buffer空间不足：redoLog占用buffer一半以上就开始刷盘
• 事务提交时进行刷盘
• 后台有一个线程每隔1s左右就会刷一次盘
• 做checkpoint的时候进行刷盘

redoLog文件组

innodb_log_file_size指定文件组的大小，innodb_log_files_in_group指定文件组的个数(默认2，最大100)
写入方式是循环写入的过程，如果最后一个文件组写满就从第一个开始写

redoLog文件格式

redoLog文件前2048个字节是管理信息，后面是真正的日志内容也就是log buffer中的block镜像

redoLog文件前4个block存储的是管理信息，其中checkpoint在循环使用文件时有具体作用

Log Sequeue Number

InnoDB规定了一个日志编号简称为lsn，从8704开始开始，日志写入log buffer占用多少个字节(包含log block header和log block trailer)，就增加几个数，以下图为例，8916是上一个mtr_1的lsn，mtr_2占用1000个字节，再加上2个log block header和2个log block trailer生成最后的lsn

flushed_to_disk_lsn

InnoDB用全局变量buf_next_to_write来表示已经刷到磁盘的日志，也称之为flushed_to_disk_lsn，当flushed_to_disk_lsn和buf_free相同时，代表所有log都别刷到磁盘中

flush链表中的LSN

如果页有修改，就会把修改页也称之为脏页加入到flush链表的表头中，如果修改的页已经存在flush中，则不插入到头部，在假如flush链表中有两个参数
oldest_modification：某个页被加载到buffer pool进行第一次修改时的lsn
newest_modification：最后一次修改页所对应的lsn

checkpoint

对于已经刷新的脏页，redoLog已经没有存在的意义，所以系统维护了一个checkpoint_lsn来表示哪些redoLog可以被覆盖，每增加一次checkpoint_lsn的过程称之为checkpoint，checkpoint分为两步

• 计算当下系统中可以被覆盖的redoLog对应的lsn，只需把已经刷新脏页在flush链表中的控制块的oldest_modification取出，并赋值给checkpoint_lsn即可
• 将checkpoint_lsn所对应的redoLog在文件中的偏移量还有别的信息更新到redoLog文件组的文件头中

恢复数据

• 取出checkpoint_lsn以及在日志文件中的偏移量，找到具体的redoLog
• 根据空间id和页号建立一个hash表，把修改相同页的redoLog用链表串起来，这样可以避免重复的IO读取
• 如果在崩溃之前已经有些脏页刷线到磁盘，但未及时checkpoint，在页的File Header 里有一个称之为FIL_PAGE_LSN 的属性，记录了最近一次修改页时对应的lsn，如果FIL_PAGE_LSN大于checkpoint_lsn则表示此页已经把checkpoint_lsn对应的redoLog的操作刷新到了磁盘

undoLog

trx_id隐藏列

聚簇索引都会有几个隐藏列，trx_id表示事务id，roll_pointer是一个指针，指向记录对应的undoLog

undoLog日志格式

insert操作日志

其中记录了该列主键占用空间和真实值

delete操作日志

删除分为两个阶段，第一个阶段delete_mask阶段，仅仅将记录的delete_mask标识位设置成1

阶段二：当该删除语句所在的事务提交后，会有专门的线程来把记录从正常链表中移除，加入到垃圾链表中，然后再调整页面信息，这个阶段称之为purge

delete所对应的undoLog结构如下

其中记录了这条记录老的trx_id和老的roll_pointer；除了主键的信息还多了索引列的信息，在删除的第二阶段purge的时候使用

update操作日志

• 不更新主键的情况
  • 就地更新：如果更新的字段更新前后存储空间大小没有改变，那么就可以就地更新
  • 先删除旧记录再插入：在更新前后字段所占用的空间大小不一致时，需要先把这套旧记录从聚簇索引中删除，再根据更新后列的值创建一条新的插入，这里的删除不是delete mark操作，而是把这条记录直接从正常列表中移除到垃圾链表中，并且修改页面信息，这里做删除的线程不是purge操作时的线程，而是用户线程同步执行删除，删除完紧接着插入新的记录

不更新主键undoLog结构如下

其中大部分属性和delete的undoLog类似，n_updated表示有几个列被更新，后面跟着被更新列的信息
如果更新的列包含索引，还会记录索引的信息

• 更新主键的情况
  • 第一步：将旧记录进行delete mark操作
  • 第二步：根据更新后各列的值创建一条新的记录并插入聚簇索引

再次过程中，第一步产生一条delete的undoLog，第二步产生一条insert的undoLog

FIL_PAGE_UNDO_LOG页面

有一种页面称之为FIL_PAGE_UNDO_LOG类型的页面，用来专门存放undoLog

Undo页面链表

• 因为一个事务可能包含多个语句，一个语句可能产生多条修改，所以产生的undoLog一个页面放不下，这个时候就需要多个页面，这个多个页面形成一个a一个页称之为first undo page，其中记录了一些管理信息，
• 每一种类型的undoLog都会有一个页的链表，并且在一个事务中最多有4个undo页面组成链表，分别是普通和临时表的insert undo链表和update undo链表
• 在事务刚开始的时候不分配undo页面列表，只有真正产生undoLog时才分配真正的链表

undoLog写入过程

• 其实undoLog写入就是暴力的直接写入，每个undoLog紧挨着，这个页装不下了就申请新的页
• 每一个undoLog页链表都对应了一个段，在链表中的第一个节点包含了一个Undo Log Segment Header的部分，里面包含了一些管理信息
• 链表第一个节点还会有一个Undo Log Header，其中会存储本组undoLog的一些信息

重用Undo页面

有一些undo页面可以被重用，条件如下

• 该链表中只包含一个undo页面
• 该undo页面已经使用的空间小于整个页面空间的3/4

insert undo链表被重用

insert的undoLog在事务提交后就可以删除了，重用时可以直接覆盖之前的数据，然后再调整pageHeader等信息

update undo链表

update的undoLog在提交事务后不能立即删除(这些日志用于MVCC)，如果想重用这个页，不能覆盖之前的数据

回滚段

• 不同的事务会产生不同的undo页链表，为了更好的管理这些链表设计了一个称之为Rollback Segment Header的页面，这个页面中存放了各个undo页链表的first undo page页号，把这些页号称之为undo slot
• Rollback Segment Header中包含了链表占用页的数量，各个链表first undo page的页号集合等信息
• 每一个Rollback Segment Header都对应着一个段，这个段称之为Rollback Segment
• 当需要undo页面时，需向回滚段中申请

多个回滚段

• 每个Rollback Segment Header中只有1024个undo slot，所以InnoDB中定义了128个回滚段，InnDB在系统表空间的第5号页面的某个区域包含了128个8字节大小的格子，每个格子是一个指针，指向了128个Rollback Segment Header
• 普通表的回滚段和临时表的回滚段是分开的

MVCC

• InoDB的聚簇索引都会有两个隐藏的列，trx_id和roll_pointer
• 每次对记录进行改动，都会记录一条undoLog，每条undoLog也都有一个roll_pointer指向之前版本的undoLog，这就形成了版本链

• 在Read committed和Repeatable read隔离级别下，会生成ReadView，这个ReadView包含以下四个属性
  • m_ids：表示在生成ReadView时，当前系统中活跃的读写事务的事务id列表
  • min_trx_id：表示生成ReadView时，当前系统中活跃的事务中最小的事务id，也就是m_ids里的最小值
  • max_trx_id：生成ReadView时，系统中会分配改下个事务的id值
  • creator_trx_id：表示生成ReadView的事务id（在读事务的时候id默认为1）
• 用以上四个数据性可以判断版本中哪些记录可见，如果某个版本判断不可见就往版本链下一个版本判断
  • 如果被访问版本的trx_id属性值与ReadView中的creaor_trx_id值相同，意味着当前事务在访问自己修改过的记录，所以该版本可以被当前事务访问
  • 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明该版本的事务在当前事务生成ReadView前已提交，所以该版本可以被当前事务访问
  • 如果被访问版本的trx_id属性大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问
  • 如果被访问版本的trx_id属性在ReadView的min_trx_id和max_trx_id之间，那就需要判断下trx_id在不在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问，如果不在，说明创建ReadView时生成该版本的事务已被提交，该版本可以被访问
• Read committed会在每次读取前生成ReadView，Repeatable read会在第一次读取时生成一个ReadView，之后查询不重复生成

  purge

  update的undoLog因为要支持MVCC所以不能立即删除，delete也只是打了一个删除标记，随着系统的运行，会有一个purge线程在合适的时机把update的undoLog和被打了删除标记的数据真正删除

锁

锁定读

• 共享锁：Shared Locks，简称S锁，S锁和S锁之间是互相兼容的
• 独占锁：Exclusive Locks，简称X锁，X锁和其他锁包括X锁都不兼容
• 读操作加S锁：select … Lock in Share mode;
• 读操作加X锁：select … for update;

写操作

• DELETE：在B+树中定位到这条记录，并获取记录的X锁，然后再进行delete mark操作
• UPDATE：
  • 如果没有更新主键，并且更新前后字段所占空间没有变化，先在B+树中获取记录的X锁，再在原先的记录上进行修改
  • 如果没有更新主键，但更新前后字段所占空间发生变化，现在B+树中获取记录的X锁，将记录彻底删除，并且INSERT一条新的记录，INSERT提供隐式锁
  • 如果更新主键，也相当于DELETE完后再INSERT
• INSERT：一般情况下insert一条记录不需要加锁，会有所谓的隐式锁来提供保护

表锁

• 除了行锁，还有表锁，表锁也包含S锁和X锁
• 意向共享锁：一个事务在加S行锁时，要先加一个表级别的意向锁，称为Intention Shared Lock ，简称为IS锁
• 意向独占锁：一个事务在加X行级锁时，要先加一个表级别的意向锁，称为Intention Exclusive Locks，简称为IX锁
• 在加表级别的S锁和X锁时，要先看下相关的意向锁IS和IX

InnoDB锁

表级锁

• S锁、X锁：在执行insert、delete、update、select操作时，不会对表加S锁和X锁，另外在执行alter table等DDL语句时，上述CRUD会发生阻塞，同理在执行CRUD时DDL语句也会发生阻塞，这个是在server层用一种称为元数据锁（Metadata Locks，简称MDL）来进行控制的；一般情况下，表级别的S锁和X锁只在一些特殊情况下使用，比如崩溃恢复过程中使用
• IS锁、IX锁：当在某些记录上加行级别的S锁和X锁时，就要先添加IS锁和IX锁

• AUTO-INC锁：在为表的某个字段加 AUTO_INCREMENT 属性，在插入时系统会自动递增赋值

  • 采用AUTO-INC锁：在执行插入语句时，会加一个表级别的AUTO-INC锁，等整个插入语句执行完才释放锁，这个AUTO-INC针对的是单条插入语句，并不是整个事务
  • 采用轻量级的锁：在为插入语句为AUTO_INCREMENT的列生成值后就释放锁，并不会等整个插入语句执行完才释放锁，避免锁表，提升插入性能
  • 可以使用系统属性innodb_autoinc_lock_mode 来控制使用上述哪种方式，设置成0则采用AUTO-INC锁，设置成1时则混合使用，确定插入条数使用轻量级锁，不确定插入条数使用AUTO-INC锁，设置成2是则使用轻量级锁模式

    行级锁

• Record Locks：记录锁，类型为LOCK_REC_NOT_GAP，分为S锁和X锁

• Gap Locks：间隙锁，类型为LOCK GAP，在可重复读级别下解决幻读的问题，在读的时候，给读取到的记录加上gap锁；
  • 如下为number为8的记录加了gap锁，也就意味着不允许之前的间隙插入新记录，就是不number在(3,8)的区间不允许插入新记录，当插入一个number为4的记录，它发现后一条记录number为8的记录有一个间隙锁，就会阻塞插入，知道gap锁所在的事务提交了才能插入，gap锁有共享gap锁和独占gap锁，但都是为了控制幻读

数据页存在两个伪记录，分别在头的Infimum和尾的Supremum，如果不允许数据在最后一条记录后添加新的数据，可以在Suprenum上添加gap锁，这样就可以阻止其他事务插入number在(20, +∞)这个区间

• Next-Key Locks：类型为LOCK_ORDINARY，本质是记录锁和间隙锁的合体，比如为number为8的记录加next-key锁，既保护了这条记录又组织其他事务向这条记录前插入新的值
• Insert Intention Locks：插入意向锁，类型为LOCK_INSERT_INTENTION，在等待gap锁的过程中，需要生成一个锁结构，表示处于gap锁的等待状态

• 隐式锁：假如一个事务插入了一条记录，此时并没有该记录的锁结构，如果其他事务要获取这个记录的S锁或者X锁，如果允许的话就会发生脏写和脏读的情况，所以这个时候会利用行记录中的trx_id事务id来构成隐式锁
  • 情况一：对于聚簇索引来说，有一个trx_id列来记录最后一次修改记录的事务id，想要为一个记录添加S锁或者X锁前，查看trx_id对应的事务是否在活跃，是的话就帮助当前事务创建一个X锁，然后自己进入等待状态
  • 情况二：对于二级索引来说，本身没有trx_id列，但索引页的Page header中会有PAGE_MAX_TRX_ID，表示对本页做出改动的最大trx_id，如果PAGE_MAX_TRX_ID小于当前事务id，说明事务提交了，否则就要找到回溯到聚簇索引执行情况一的操作
  • 所以对于Insert操作不会显示的加锁，但由于事务id的存在，相当于加了一个隐式锁

InnoDB锁内存结构

如果一个操作对1000条记录都加S锁，多少会有些许浪费，所以在满足以下条件时，就加入到一个锁结构中

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

锁内存结构

• 锁所在的事务信息：一个指针指向生成锁的事务
• 索引信息：记录加锁的是哪个索引
• 表锁/行锁信息：
  • 表锁：记载着对哪个表进行加锁和其他信息
  • 行锁：记录了三个重要信息，SpaceId、PageNumber、n_bits；其中n_bits一行记录就对应着一个比特位，用不同的比特位代表这个页中哪几个记录被加锁了，n_bits一般位数比目前页已有行数大，以防后面还有记录的插入
• type_mode：一个32位的数，被分为lock_mode、lock_type和rec_lock_type

• lock_mode：占低四位，锁的模式
• lock_type：占用5~8位，锁类型；LOCK_REC行级锁，LOCK_TABLE表级锁
• rec_lock_type：只有lock_type为LOCK_REC时，也就是行级锁时才会被细分
  • 其他信息：为了管理系统运行时生成的各种锁结构而设计了各种哈希表和链表
  • 一堆比特位：如果是行级锁，还在末尾放置了一堆比特位，比特位的数量是由上面提到的n_bits来控制的，放置了和记录头中heap_no的映射关系