mysql是怎样运行的

---

前言

互联网行业的数据层经历了几个阶段，分别是从早期使用mysql作为数据层的载体，到后面大规模使用商业数据库（orcale）作为载体，再又到近些年来返璞归真使用回mysql。

从数据层的演变路径来看，就能知晓学好mysql对程序员来说是多么重要，所以本文让笔者带大家一起从innodb底层存储结构、binlog、redolog、undolog、事务隔离、mvcc、锁这几方面深入浅出学习Mysql数据库

---

1、Mysql存储引擎

mysql中，最常提起的存储引擎有三种，分别是：

1. MyISAM：在Mysql5.5及之前默认的存储引擎。不支持InnoDB的大部分功能，唯一的优点是支持全文索引。
2. InnoDB：在Mysql5.5之后默认的存储引擎，相较于MyISAM来说支持事务、外键、行级锁、MVCC、在线热备份等功能。
3. Memory：从名字就能看出来会将数据存在内存中。不常用

因为日常使用中基本上只会使用innodb引擎，所以本文接下来所有的知识点默认针对InnoDB进行讲解

1.1、InnoDB底层存储结构

数据存储有行优先和列优先两种方式，行优先就是每一行数据挨着，列优先就是每一列数据优先，这两种排列方式各有各的好处，有兴趣的可以自行查阅，本文就不展开说明了。

众所周知，Mysql是一个行式数据库，本文就不提列式数据库了，只说说Mysql这种行式数据，Mysql中，每一行数据称作为一条记录，一页可以存储16KB数据，一区数据可以存储64个页，数据最小组织单位是页，磁盘分配最小单元是区。

1.1.1、行数据

InnoDB存储引擎到目前位置有4中不同类型的行格式，分别是COMPACT、REDUNDANT、DYNAMIC和COMPRESSED。这些行格式在原理上大体都是相同的。下面以COMPACT格式为例介绍。

COMPACT格式分为两部分，一部分可以理解为java中的对象头。记录这行数据的额外信息。另一部分则记录真实的数据，也就是每一列的值。

额外信息包括变长字段长度列表、Null值列表、记录头信息

1. 变长字段长度列表：这个很好理解，因为像Varchar、Text这种变长字段，Mysql也不知道它的边界在哪，所以要在记录头中记录，方便Mysql读这列的数据，也方便知道下列从哪开始读。
2. Null值列表：Compact格式Null值不记录到真实数据这一部分的，所以要提前记录在这个列表中表示这个值为Null
3. 记录头信息：它记录的数据比较多，我们这里只讲几个可能用到的进行讲解
   1. 指向下一条记录的指针：将每条数据使用单链表的方式串起来。方便从某条数据顺序查找到下一条数据。
   2. deleted标：指这条数据是否被删除了，在删除数据时，因为事务还没结束，Mysql不知道这条数据会不会真的被删除。所以会先进行标记。
   3. 溢出页指针：一行数据不能太长，最长是700多个字节，如果过长会把溢出的部分放到溢出页，然后用指针指过去，可能存在多个溢出页。

1.1.2、页数据

每一行数据是一条记录，每一条记录是通过页的方式管理起来的，一页是16KB，一页中存放多行数据。数据放在图中User Records部分中。

每一页由File Header、Page Header、Infimum + Supremum、User Records、Free Space、Page Directory和File Tailer组成，下面分别介绍一下这些内容

1. File Header：存放页的一些通用信息，页与页之间通过双链表的方式组织在一起，而这两个指针就存放在File Hader中。
2. Page Header：数据页专有的一些信息，因为有些页可能不存放数据，就没有这一部分，这部分包含index_id(当前页属于哪个索引)，B+树层级等信息。
3. Infimum + Supremum：页面中的最小记录和最大记录，两条虚拟的记录，不存放数据，类似于链表中的头结点和尾结点
4. User Records：已经存放的数据内容
5. Free Space：可用存放数据内容的空间
6. Page Directory：在一页中，通过链表查询数据的效率也比较低，所以就将中间的数据以4条为一个分组、Infimum单独作为一个分组、Supremum和往前的最多8条数据作为一个分组将数据分割。每一组最后一条数据的记录头中n_owned字段记录了该组一共多少条数据，也就是组长，Page Directory中存储每个分组最后一条数据。通过这个目录，在查询当前分页的数据时就可以实现二分查找。
7. File Tailer：文件尾、校验页是否完整。

1.1.3、区数据

一页是16KB，64个连续的页成为一个区，即1M大小。这样可以将连续的数据页放到一个区内，连续的目录页也可以放到一个区内。因为只分配一页的代价较大，且不连续性对性能是很大的损失。

1.1.4. 段数据

段是逻辑意义上的区分，例如聚簇索引中所有的叶子结点是叶子结点段，其他是非叶子结点段。段是纯逻辑概念，他并不一定由一个或多个完整的区组成，因为区是物理概念，两者没有交集。只能说段至少有一页组成。

1.2. 数据插入时的逻辑

目前数据的组织方式是页内有序，用单链表链接起来，然后中间以四条为一组、头单独一组、尾往前数8个为一组的方式记录在Page Directory中，方便查找。

那么插入的过程就是，先从页的PAGE_FREE也就是已删除的数据链表找最近的一条已删除的记录，看空间够不够放下当前记录，够的话直接复用，不够则向Free Space空间中申请一段磁盘空间供这条记录存储。然后这一段就会被标记成User Records。然后修改插入数据处的前后指针。

如果在插入的过程中，发现页满了，存不下新数据了，则会开新的页，这个过程叫做分裂

1.3. 数据修改时的逻辑

如果更新后的记录长度没有变化，就可以复用当前record的磁盘空间，就地更新，如果不一样大，则会先删除再创建，也就是先删除原来的记录，然后添加一条新的记录。所以这种情况一句update会产生两个undolog的日志。

1.4. 数据删除时的逻辑

先把delete标识改为true，然后事务提交后，把这条数据从链表拆下来，扔到PAGE_FREE这个已删除记录的链表中（该过程为purge），PAGE_FREE链表在特定的时机也会被后台线程清理。

二、索引

mysql底层通过B+树的方式组织索引，上面介绍的的页结构式数据页的结构，在页内可以使用二分查找，但是如何找到数据页就需要用到索引。

B+树是一种多叉搜索树，可以有效的降低查询复杂度。每个结点是一个页，这颗树有两种类型的结点：

1. 数据页：真正存储数据的结点，一般来说是叶子结点
2. 目录页：只存储索引值+页号，方便查询到真实数据

这两种类型的页都是index类型的页，在index页中有四种编号类型的记录：

1. 0：表示真实的数据记录用0表示
2. 1：表示索引+页号的记录用1表示
3. 2：表示Inf记录2表示
4. 3：表示Sup记录3表示

2.1. 为什么要用B+树作为索引存储结构：

1. 自平衡的多路搜索树，查询效率高
2. 多路 -> 层数少，跨磁盘扇区操作就少
3. 同层的节点有指针链接，减少跨扇区
4. 叶子结点存储数据，减少一次IO

2.2. 聚簇索引

聚簇索引又叫主键索引，上面图中的就是聚簇索引，他的叶子节点记录的数据是完整的数据。

上图中，浅蓝色的结点表示的是结点类型和指针，橙色表示的是主键值，绿色表示的是页号值，由这两个数据就可以表示id多少到多少的数据存储在多少页中，然后再去把那一页的数据从磁盘加载到内存中使用。剩下的其他颜色的结点则是这个主键的其他字段存储的数据。

2.3. 二级索引

二级索引指的是除了主键外，添加的其他列作为索引，二级索引也会创建B+树，只不过叶子结点不再需要存储所有列的内容。只需要存储逐渐和索引列的值这两项即可。

从图中也可以看出来叶子结点只存储了那一条数据的主键列，那么找到主键后，再回到聚簇索引查对应的其他数据，这个过程叫回表。

此外目录结点也需要存储主键+索引列，这样提前命中就可以更早的去回表。

如果刚好在二级索引中可以获得所有查询的数据字段，则不需要进行回表查询，这种情况称作：索引覆盖

2.4. 联合索引

联合索引是一种特殊的二级索引，他与普通的二级索引最大的不同在于他的每一个目录结点中不止存储单个索引列和一个页号，而是多个索引列和页号，如下图展示的两列联合索引，则会在目录页中存储两列的值。

B+结点的大小排序是按照联合的第一列排序，如果第一列一致，就按照第二列排序，第二列一致，就按照第三列，以此类推。

因而联合索引可以按照最左匹配来起到索引的效果，通俗来讲就是条件按照索引顺序来写。

例如a、b两列的联合索引，就可以被where a = 1 and b = 2 这种查询命中，也可以被where a = 1这种命中。

但是where b = 2 就不能使用这个联合索引，因为where b = 2 不满足最左匹配的原则。

而对于range条件 where a > 1 and a < 10 and b = 1 这个查询就可以使用该索引，因为a的条件进行索引找，b = 1 不能使用该索引，需要拿a > 1到 a < 10这部分中的数据出来 再找出b = 1的部分，这里会有两种找的方式。

1. 先拿到这部分数据回表查询，然后再根据结果排除不需要的部分数据
2. 先过滤不需要的部分数据，再回表查询结果。

显然第二种方式更优，因为回表查询的数据少。第二种方式也叫做索引下推。

三、Buffer Pool

读取数据的最小单位是16k的页，如果同时要从一个页读两条不同的数据，那就可以把这页缓存到内存中，减少IO，基于这样的想法就有了BufferPool这个内存额定大小的空间，来缓存数据。

BufferPool简称BP吧。它由两部分组成，控制块和缓冲页，缓冲页是存储内存页数据的只不过结构上稍有变化，并且缓冲页大小也是16k。控制块则是记录很多元信息的，可以实现逻辑上的串联操作。

3.1. 控制块作用描述

一个控制块可能是free，可能是flush，也可能都不是，都不是的话就是没有update操作过的普通读缓存结点。

另外如何根据物理磁盘页信息找到内存中的缓冲页呢，因为是链表，O(n)显然不可取，所以有个专门的HashTable维护了表空间-页号和buffer页的对应关系。

3.1.1. free链表

把空闲的缓冲页的控制节点串成一个链表，就是free链表，如果要载入新数据时，可以从这个链表分配。

3.1.2. flush链表

当分配后如果有写操作，就会修改缓冲页中的值，导致和磁盘页不一致，称为脏页，脏页的控制节点也串成一个链表，叫flush链表。

3.2. LRU链表

从磁盘读到内存中的页，都会放到LRU链中，也就是LRU链中的结点是由flush链表的所有结点和另一部分只读没写的结点组成的。

mysql的LRU有很多改进，例如为了减少热点数据频繁在链表中移来移去，退出了3/4个结点是young区，后1/4是old区。

young区结点被访问后需要先判断距离上次被访问的时间是否大于一个阈值，如果没有就不移动了，因为young短时间也不会有被驱逐的危险，老年人才会被优先淘汰，这样减少了LRU的频繁操作。

这里就不对LRU算法做展开赘述了。有兴趣的可以自行查询百度。

3.3. 脏页落盘

数据刷新到磁盘，是一个后台线程定期执行的，有两种刷新方式：

• 从LRU的old中刷脏页（跳过不是脏页的old）
• 从flush链表中刷一部分

四、binlog、redolog、undolog

binlog是server级别的，而redolog和undolog是存储引擎级别的，也就是说其他的存储引擎就没有redolog和undolog了

4.1. binlog

Binlog是记录所有数据库表结构变更以及表数据修改的二进制日志，不会记录SELECT和SHOW这类操作，主要用于数据恢复和主从同步。写入时机是事务提交的时候。

默认是关闭的，需要在mysql的配置文件中打开log-bin=mysql-bin开启，开启后执行写入操作就会产生mysql-bin.000001这个日志文件，后缀是递增的序号。然后就可以在mysql客户端中输入show binlog events来展示所有的binlog操作。

mysql-bin.000001这个文件存储的目录在设置数据目录的根目录中，因为这个文件是二进制文件，直接打开这个文件会不可读，可以通过来生成对应的sql文件。

# 将mysql-bin.000001文件恢复成sql文件。
mysqlbinlog --no-defaults mysql/data/mysql-bin.000001 > output.sql 

可以添加–start-datetime=“2023-06-30 00:00:00” --stop-datetime="2023-06-30 08:00:00"这种参数来圈定固定的时间范围，或者使用–start-position=190 --stop-position=888来确定位置，position就是上面sql截图中每一句sql都有的一个偏移量。–database=test则可以指定某个db。

它的文件记录模式有三种：

1. STATEMENT：记录每次执行的SQL，日志小，但有些sql有毒比如now()，可能导致恢复的时候与之前的数据不一致。针对这种函数也有解决方案，如下是一个binlog截图，先设置时间戳，再运行now的这句sql。
   
2. ROW：记录每一行被改的数据，能还原所有的细节，但是如果有加一列这种操作，可能导致全表都得生成binlog。
3. MIXED：上述两种方式的结合。

其他知识，一般可以通过mysqldump和binlog相结合的方式来实现线上数据的备份，这样可以使得mysql数据可以快速恢复到任意时间点。例如每天执行一次mysqldump同时带上–flush-logs标志，这样每天会生成一份全量数据库的sql文件，同时切换binlog文件到一个新的文件，来打印新的一天的sql。

想要恢复到3号10点10分，那么可以执行先用3号0点的dump.sql快速恢复到0点，然后用3号的binlog通过指定–stop-datetime恢复到具体的10点10分这一瞬间。这样比纯用binlog的速度快。

4.2. redolog

redo log也是用来记录执行的数据变动的，但是他是innodb存储引擎级别的，并且存储的是数据页的变动，并且是顺序写，性能更好。

它与binlog的区别在于两个方面：

1. 记录的内容不同：
   • redolog是逻辑日志文件，记录写操作的sql，用于归档、可以恢复到任意时间，同时用于主从同步、备份等操作
   • redolog是物理日志文件，记录某一页数据的磁盘变动，比如页38的第多少个字节从什么改成了什么。用于故障恢复、所以只需要保存了所有的脏页即可。
2. 存储的逻辑不同
   • binlog会不断累计，mysql-bin.000001存满后，会开后缀为2的文件继续存。以此类推。
   • redolog会使用循环利用文件组来覆盖存储。

事务提交时，redolog必须刷盘。这样才能保证保存了所有的脏页数据。

为什么不用binlog来做故障恢复？因为故障恢复本身是要把产生线上影响的数据恢复回来，redolog能保证事务提交的时候已经刷盘到日志文件中，binlog并不做这个保证。并且记录的是全量的写sql日志，如果用其做故障恢复，要全量删除库然后运行binlog，过于兴师动众。

为什么不在事务提交时就直接刷盘到ibd文件？redolog保证了刷内存的同时也写入redolog文件，为什么不直接写入ibd文件？因为写ibd文件可能涉及到多个不同位置的页，磁盘是随机写，速度慢，而redolog是连续写，速度快，对正常sql的性能影响小很多很多。

4.2.1. redolog文件组

redolog是指定的文件个数，比如设置了3个文件，那么就是xx.0、xx.1、xx.2这三个文件，每个文件大小也是指定的，当最后一个文件写满时，就会开始覆盖第一个文件。redlog是用来崩溃恢复的，所以对于已经存到db磁盘的数据的redolog就不再需要了，因而可以循环利用文件组。

但是需要保证被覆盖的部分数据确实已经刷到DB了，所以有了checkpoint的概念。简单讲它是专门检查当前已经刷到db文件的日志序号（LSN）。

恢复的时候，就从checkpoint开始，往后读block，直到有个block的size不是512了，说明这里崩溃的，恢复之前的。因为cp保证了之前的操作都已经持久化到ibd文件了，而之后的则不一定，因为redolog记录的是页的变动信息，是幂等的。cp之后的可能有一部分是已经持久化到ibd文件了，也没有关系，幂等。

4.3. undolog

与binlog、redolog不同，undolog和db一样用页的方式组织的，有一种专门的page_type就是undolog类型。这是因为undolog是事务相关的，一个事务需要自己的undolog链，而redolog是物理页的改动记录，与逻辑层无关，所以可以无脑顺序写。

一行数据3个隐藏字段row_id，trx_id，roll_pointer。其中事务id就是当前的事务的id，roll_pointer则是指向上一次事务修改的数据，这个数据就在undo log中。

因而当事务中修改数据的时候，需要将老的数据扔到undo_log的数据页中新的数据通过指针指向老的数据。这样如果事务回滚的话，只需将老的数据恢复回来即可。

undolog与事务回滚和mvcc的版本链都有关系，mvcc我们后续介绍，undolog本质是为了记录对数据的操作历史记录。

undolog对不同的增删改操作生成的结构并不相同：

1. 对于新增操作需要记录新增的id即可
2. 对于修改操作则需要记录改动前的旧值（只记修改的列）
3. 而对于删除操作稍微不太一样，需要记录删除前的样子，并且还不能直接把主索引树中的记录删除，因为删了的话就没法找到undolog了，所以是把原来的记录打一个delete标记，然后把所有列值记录到undolog中。

对于insert和非insert，有两个链表insert链和update链（虽然叫update其实也包含delete），而对于临时表和普通表也是分开的，所以一个事务对应的undolog会有0-4个redolog链。

为什么要分成insert链和update链，因为两个原因：

1. insert和update存储的结构不一样，新增的只有id、修改和删除则还存在具体列的值
2. 事务提交后insert语句的undolog就没用了。可以删除或复用，而update和delete因为mvcc的原理，可能在事务提交后仍然是有用的。所以不能删除，如下图所示。
   
   而update链只能利用空闲的部分空间，原来的数据不能覆盖。
   

4.3.1. undo段（回滚段）

回滚段是特殊的一页，这一页有1024个undo slot，每个slot指向first undo page也就是undo log链表的第一个节点，回滚段（Rollback Segment）是用于实现事务回滚操作的一种数据结构。它是一块用于存储已提交事务的旧版本数据的空间，以便在需要回滚事务时可以恢复到之前的状态。

一个回滚段可以指向1024个链，一个单表事务最多4个链，因而一个回滚段就能支持至少256事务并行，而mysql有128个回滚段。

五、事务

我们有多句sql语句，把他拼到一个事务里面去，多句sql的执行是原子性的，也就是说要么都成功，要么都失败。这就叫事务。

举例：A有十块钱，B也有十块钱。此时A向B转账五块钱，那么最终的结果应该是A变成五块钱，B变成十五块钱。转换成sql语句来看，应该是有两条sql语句。

1. update monery = monery - 5 where name = A # 给A减五块钱
2. update monery = monery + 5 where name = B # 给B加五块钱

这两句sql都执行成功了，我们才叫这个事务也成功了。如果这两句sql有一句出现了问题。那么我们就会进行统一的回滚，两方都会回滚成原来的十块钱。

如果没有事务，那么当第一句sql执行成功后，第二句sql执行之前，出现了问题导致程序崩溃，没有执行完成。那就会出现A-5已经操作了。B+5没有操作，最终会导致A已经扣除五块钱，B也没有增加五块钱，就出现了数据不一致的问题。这就体现了事务的必要性。

如果有事务的话，将两句sql放在一个事务里，如果在中间出现了异常，那么就会将A-5这个sql取消掉（数据回滚）。那么A与B的数据都没发生变化，就保证了数据的一致性。

5.1. 事务隔离

因为数据库在同一时刻可能有多个事务并发执行，那么这多个事务有可能会操作到同一条数据，这种并发就需要有一定的隔离。下图是两个事务对一个数据并发操作的流程图，在不同的事务隔离级别中，x和y的值会产生不同的结果。接下来我们一起来研究下。

1. read uncommited（读未提交）：即在事务二未提交时，事务一也能读到事务二修改的内容。

使用这个隔离级别时，x的值是10，y的值也是10。会出现脏读、不可重复读和幻读问题，即事务A写的数据，还没提交事务B就读到了，A回滚后，B使用的是脏数据。

2. read commited（读已提交）：即在事务二提交后，事务一才能读到事务二修改的内容

使用这个隔离级别时，x的值是5，y的值是10。读已提交可以解决脏读问题，但会出现不可重复读和幻读问题，即事务A查到一条数据，事务B修改了这条并提交，事务A再次查询发现，同一条数据前后两次读出来的结果不一样了。

例：有一张表存放了公司总人数，假设有100人，男女比例为1：1，此时需要按照性别，分别查询男生和女生人数。此时开启事务1查询男生时，返回结果为50人，然后有一个新事务2将一位男性的性别修改为了女性。此时事务1继续执行查询女性，得到的结果却为51，此时在整个查询结果中，有一位员工又为男性、又为女性，这就是不可重复读问题。也就是说它的性别修改了，但是在事务中还是使用的原来的值。

3. repeatable read（可重复读）：即在同一个事务中，多次读取的结果应该是一致的。

使用这个隔离级别时，x的值是5，y的值也是5。但会出现幻读问题，即事务A按照条件P查出了一批数据，而事务B插入了一批符合P的数据并提交。此时事务A又用P去查数据，发现条数比之前多了。

4. serializable（串行化sql）：无任何并发问题，因为不支持并发，串行执行事务。、

因为幻读发生的概率小，而且mysql使用readview解决了大部分情况下的幻读问题，并且串行化效率低，所以mysql的事务隔离级别默认使用可重复读

六、mvcc

上面undo log中介绍了每一条数据在被事务修改后，历史版本的数据不会被立即删除，即使事务提交了也不会删除(insert的历史可以删除)，这样每个历史的版本都会保存下来到undo log页中，通过roll_pointer将一条数据的所有版本串联起来，这就是版本链。下面都是RR隔离级别为例，讲述MVCC工作流程。

trx_id是当事务有写操作时才会申请的自增id，如果纯读事务trx_id=0，RR的实现是在第一句select的时候创建ReadView，直到事务结束都使用这个视图实现的，而ReadView本质是记录当前所有已经提交和未提交的事务，RR读取数据的时候查看数据的trx_id，如果不是已经提交的事务，就顺着roll_pointer向前直到找到已经提交的版本。

RR和RC最大的区别就是RR是第一句select建立view，而RC是每一句select都重新创建view。

6.1. ReadView

ReadView能判断一个trx_id是已经提交的还是正在运行的，主要通过以下四个字段：

• max_trx_id 下一个要分配的trx_id
• min_trx_id 所有活跃的trx中最小的id
• m_ids 所有活跃的trx id列表
• creator_trx_id 当前事务的id（可能是0，因为当前可能没有写操作）
• trx_id拿来之后先判断是不是>=max_trx_id，如果是的话说明是未来提交的事务，不能用；

然后判断是不是<min_trx_id，如果是的话说明已经提交的事务，直接用；

如果在两者之间，需要判断是不是=creator_trx_id，如果是的话说明是自己改的，直接用；

如果不是的话，需要判断是不是in m_ids，如果是的话说明是未提交的事务，不能用，否则可以用。

ReadView是一个简单的数据结构，RR下每个有读操作的事务都会生成一个ReadView，RC下则可能有多个。ReadView以列表的形式存放在特殊的位置。当一个事务结束后，ReadView就会被删除，因而列表中存放的ReadView都是active的事务。从这个列表中，可以筛选出所有的min_trx_id中最小的，如果比这个id还小2个版本的undolog说明不会被任何事务所依赖了，那这部分就可以被清理了。有个专门的线程来做清理工作。

6.2. 快照读与当前读

依赖ReadView的读取又叫快照读、视图读、一致性读，但是并非所有的读都能用视图读。例如当我们执行写操作的时候

update user set age = 20 where age = 19;

上面这句sql执行的时候，如果采用快照读，根据age=19找到的数据可能是一个老版本undolog中复原出来的数据，那么此时如果要修改他的age，是要修改undolog中的节点，还是修改聚簇索引的数据节点呢。先排除undolog，因为undolog页记录没法修改，只能加一个版本用指针链接起来，那这个老版本就被两个节点指向，就不是单链表了。所以只能修改数据节点，数据节点就是当前最新版本了，所以写操作都不能用视图读，只能用当前读。

因而写操作用当前读，读操作用视图读，可以保证读的时候不会出现幻读，因为多次读使用的视图相同。但是写操作的当前读，另外锁定读select for update和select in share mode使用当前读。当前读就有可能出现幻读，例如

事务A                       事务B
开始                        开始
select for update(n条)          
                           insert一条符合条件的数据

                           提交
select for update(n+1条)
提交

因为insert和select for update都是当前读，所以都是最新数据，因而第二次就读出了比第一次多一条的数据。也就是产生了幻读。

为了解决当前读的幻读，mysql引入了锁机制。

七 、锁

锁主要有行锁和表锁两大类。其中行锁是解决当前读幻读的主要机制。通过对数据范围加锁，导致另一个事务无法进行数据的修改，例如上面例子中，行锁会锁住符合条件的范围，防止数据的插入，事务Binsert会被阻塞，直到A提交。

7.1. 行锁

行锁主要有5种：记录所、间隙锁、临建锁、插入意向锁、隐式锁。

1. 记录锁：锁住当前条目，例如update xx where id=1会把id=1这一条锁住防止其他事务对其修改。
2. 间隙锁：锁住B+树中当前条目和前一条之间的缝隙，例如update xx where id<1假如找到0条数据，并且存在id=1这条，那么就需要锁住(-无穷，1)，防止其他事务在这个范围插入了数据，导致后续当前读读取的条目增加。
3. 临键锁：记录锁+间隙锁，也就是左开右闭区间的锁，例如update xx where id<=1假如找到1条id=1这条数据，那么就需要锁住(-无穷,1]这个范围，因为1也不能被修改，这就是next-key lock。
4. 插入意向锁：前面gap或者next-key锁住一个范围之后，如果另一个事务想要在这个范围插入数据会被阻止，并且会分配一把锁给这个事务，只不过是waiting等锁的状态，等gap释放就可以插入了。
5. 隐式锁：针对insert的，默认是不加锁的，减少开销，当另一个事务要访问对应的id的时候，会看这条数据的trx_id来判断是不是已经提交的事务，如果不是，那就需要给这条数据加锁，锁的持有trx是记录中的trx_id，同时给自己创建一把对这条记录的锁，waiting状态。隐式锁可以减少insert时锁的创建，只有发生竞争的时候由另一个事务惰性创建，借助了trx_id这个隐式的条件。

当然为了提高并发性能，我们发现读-读是不需要互斥的，所以就有了类似读写锁的：独占锁(X)和共享锁(S)，例如select in share mode就只需要给数据加共享锁，允许其他事务也进行select in share mode。而select for update和写操作就需要加独占锁，其他事务既不能写也不能读，因为读的话，可能因为这个事务的修改，而导致两次读取数据的不一致。

对行锁进行分析，准备数据如下。

CREATE TABLE user (
   id INT AUTO_INCREMENT PRIMARY KEY,
   name VARCHAR(255) NOT NULL UNIQUE,
   age INT
);
insert into user (id, name, age) values (1, 'lily',20),(3, 'sam',24), (5, 'kity', 33), (10, 'tim',  44);

id name age
1  lily 20
3  sam  24
5  kity 33
10 tim  44

[ id 主键]
[ name 唯一索引]

查看锁

SELECT * FROM performance_schema.data_locks;
// 8.0查看锁信息 注意8.0是加锁就会在该表有记录
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
// 5.7查看锁信息 注意如果是5.7的话，得真正发生竞争了表里才有数据，所以至少俩事务并发运行才行
// 否则该表是空

加锁的原则就是防止其他事务影响这句sql中where条件去select的再次运行，对于主键需要锁住聚簇索引，而对于二级索引需要锁住二级索引，并通过回表锁住聚簇索引，防止数据其他字段被修改。

select * from user where id = 1 for update;
-- 记录锁X锁，锁住聚簇索引中id=1的这条

update user set age = age+1 where name = 'lily';
-- 记录锁X锁，锁住二级索引中name='lily'这条，并且回表锁住id=1的这条
-- 如果name不是唯一索引，那么这句加next-key

select * from user where id>=3 for update;
-- 3记录锁，5 next-key,10 next-key,sup next-key

select * from user where age>1 for update;
-- 当前读没有命中索引，每一条记录都加next-key，性能很差

select * from user where name>='sam' for update;
-- 理论上是，二级索引中 sam 记录锁，tim next-key，sup next-key，聚簇索引中 id=3 记录锁，id=10 记录锁
-- 实际是sam也是next-key，原因不明

7.2. 表锁

表锁的效果等价于对每一条数据next-key锁，表锁的S锁和X锁使用非常少，代价较大，性能较差，以下方式在事务中获取表锁。

LOCK TABLES user READ;
LOCK TABLES user WRITE;

行锁和表锁存在一定的互斥关系，例如如果在这个表中有在用的行X锁，那想要获取这个表X锁，也是不行的，为了更快的判断这个情况，在进行加行X锁的时候(S锁类似)，需要先给表加IX锁。IX和IS又叫表的意向锁。

自增锁AUTO-INC，比较特殊是专门针对自增键的，比较简单，但是他的生效范围与其他不同，其他行锁都是事务范围的，也就是事务结束的时候，锁才释放，但是自增锁是insert这一句结束就释放锁。

7.3. MDL

当我们进行表结构修改DDL的时候使用的并不是表锁，而是server级别的元数据锁(Metadata Lock, MDL)，这就不是innodb引擎级别的锁了。

7.4. 死锁

当一个事务锁的顺序是id=2,id=3，另一个事务锁顺序反过来，并发运行时，就会出现死锁。死锁出现时，mysql会选择较小的事务进行回滚，并向上报错。