mysql

本文从到到尾梳理mysql的知识点，主要用于面试的复习，内容主要分为以下几个部分：

• 1 存储引擎的对比
• 2 底层是如何组织存储的
• 3 索引
• 4 buffer Pool
• 5 bin log
• 6 redo log
• 7 undo log
• 8 事务的隔离级别
• 9 mvcc
• 10 锁
• 11 explain
• 12 多机

1 存储引擎的对比

2 底层是如何组织存储的

2.1 行结构

以compact格式为例：一行数据结构如上图，分为额外信息和真实的列值数据两部分，额外信息中需要记录变长列的长度，因为是变长的无法提前知道长度，所以需要随着每条记录做标记；null值列表主要是null不会在数据部分做任何存储，只在这里存储列名；记录头信息内容比较多，比如有指向下一条记录的指针，deleted标等。

一行数据不能太长，最长是700多个字节，超出的部分就算溢出，当前数据记录一个指针，超出的部分单独放到溢出页，指针指过去，可能有多个溢出的列。

2.2 页结构

每一行数据是一个record，record是通过页的方式管理起来的，一个页是16KB，一页中存放多行数据。这是页的组成，数据就是放在User Records部分。

需要简单介绍下:

• Infimum/Supremum，这两个分别是标志着这一页的最小和最大的记录，是虚拟的记录，并不真正存储数据，可以认为是dummy指针，Inf的next指向页中的第一条有效数据，而最后一条数据的next指向Sup。
• 页与页之间也通过FileHeader中的prev、next连接成双链表。所以页面的整体组织方式如下图。

• PageHeader则记录数据页内的元数据信息，非数据页就不需要这个字段，例如Index_id(当前页属于哪个索引)，B+树层级等等
• FileTailer是一个校验信息的位置
• PageDirectory是比较重要的数据，首先将Inf作为一个分组，Sup和往前最多8条数据为一个分组，中间的数据最多4条为一个分组，通过此方式将页内数据进行分组，每一组最后一条数据的记录头中n_owned字段记录了该组一共多少条数据，并且PageDirectory存储每个分组最后一条数据的地址，因为分组数取决于数据量，这部分的大小=地址长度x分组数，所以也是不确定大小的。通过这个目录，再查询当前分页的数据时就可以实现二分查找。

2.3 区与段

B+的最小组织单位就是页，但是如果按照页来组织整个磁盘的存储，仍有些不妥，尤其是读取大量连续数据的时候，可能会跨多个页，此时如果两个先后关系的页，物理上地址很远，IO也是比较慢的。例如下面索引这一节的图中，页34,35,36,40如果在磁盘上是相去甚远的不同地方，那么读取就会变慢。所以有了区。

一页是16k，64个连续的页成为一个区，也即是1M一个区。这样可以将连续的数据页放到一个区内，连续的目录页也可以放到一个区内。内存读取的最小单位是页，磁盘分配的最小单位是区，因为但分配一个页的代价比较大，且导致不连续性对性能是很大的损失。

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

2.4 数据更改时页的变化

先说insert，当插入数据的时候找到要插入的页。然后看页是否已经满了，如果没有满的话，则需要在PAGE_FREE也就是已删除的数据链表找最近一条已删除的记录，看空间够不够放下当前记录，够的话就用，不够则在当前页的FreeSpace中申请一部分插入当前数据，并通过next指针的修改，插入到当前页的数据链表中，注意这里不改变原来页内的数据。例如当前页有以下数据，并希望插入9这条数据，其他数据的物理位置不发生变化，只是在后面插入9，并把8的next指向9的地址，9的next指向10的地址即可，此外插入可能还会导致组的变化n_owned等字段，以及PageDirectory和PageHeader中一些元数据的修改。

而如果页面满了则有两种情况，一种就是下面会说的虽然满了，但是其实碎片空间是足以称下这条记录的，收拾收拾还能用；另一种就是实在装不下了，就只能新开新的页，这个过程又叫分裂。

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

最后对于delete，则是先把deleted标识改为true(事务执行中的中间状态)，然后事务提交后，把这条从链表中拆下来，扔到PAGE_FREE这个已删除记录的链表中（该过程为purge），将可能用于insert新纪录去复用。PAGE_FREE链表在特定的时机也会被后台线程清理。

这里有一些碎片问题，例如删除的数据记录都很长，新插入的都很短，导致复用的记录空间是比较大的利用率比较低。所以有个兜底逻辑就是对应上面insert里说的虽然满了，但是记录的PAGE_GARBAGE中有碎片的大小信息，发现是可以承载当前要插入的record，那么就把当前重新紧凑排列，规整一下，插入这一record，具体方法是create一个临时页，按顺序排排坐，然后排好了再copy回来，删掉临时页。

3 索引

通过B+树的方式组织索引，上面介绍的页结构是数据页的结构，在页内可以使用二分查找，但是如何找到数据页就需要用到索引。B+树是一种多叉搜索树，可以有效的降低查询复杂度。树的每个节点是一个页，其中叶子节点是真正存储数据的，又叫数据页，而其他节点只存储索引值+页号，叫做目录页，数据页和目录页都是索引页index类型的页。下图中每条记录的类型用数字0123表示了，0就是真实的数据记录，1就是只有索引+页号的记录，2是Inf记录，3是Sup记录。

3.1 聚簇索引与二级索引

聚簇索引又叫主键索引，上面图中的就是聚簇索引，他的叶子节点记录的数据是完整的数据。但是table除了主键还可能加其他列作为索引，这些就是二级索引，二级索引也会创建B+树，只不过叶子节点不再需要存储所有列的内容，只需要存储主键和索引列的值两项即可，找到主键后再回到聚簇索引查对应的其他列数据即可，这个过程叫回表，此外目录节点也需要存储主键+索引列，而不是只存索隐列，这样提前命中就可以更早的去回表，如下是二级索引的树结构。橘色是二级索引列，深蓝是主键值。

3.2 联合索引

联合索引是将多个列同时作为索引，底层也是建立一个B+数，只不过需要同时存储多个列和主键的值，B+节点的大小排序是先按照联合的第一列排序，如果第一列一致，就按照第二列排序，第二列一致，就按照第三列，以此类推。

因而联合索引可以按照最左匹配来起到索引的效果，例如a、b两列的联合索引，就可以被where a=1 and b=2这种查询命中，也可以被where a=1这种命中，但是where b=2就不能使用这个联合索引。而对于range条件where a>1 and a<10 and b=1这个查询就可以使用该索引，因为可以感觉a的条件进行索引找，b=1不能用该索引，需要a>1 a<10拿出数据挨着比较b是否=1。

4 Buffer Pool

读数据的最小单位是16k的页，如果同时要从一个页读两条不同的数据，那就可以把这页缓存到内存中，减少IO，基于这样的想法就有了BufferPool这个内存额定大小的空间，来缓存数据，BufferPool简称BP吧，他由两部分组成，控制块和缓冲页，缓冲页是存储内存页数据的只不过结构上稍有变化，并且缓冲页大小也是16k。控制块则是记录很多元信息的。

4.1 free与flush链表

把空闲的缓冲页的控制节点串成一个链表，就是free链表，主要用来用的时候可以从这个链表分配，而当分配后如果有写操作，就会修改缓冲页中的值，导致和磁盘页不一致，称为脏页，脏页的控制节点也串成一个链表，叫flush链表，下面是两个链表示意图，一个控制块要么是free要么flush，也可能都不是，都不是的就是普通的读缓存节点，没有update操作过。另外如何根据物理磁盘页信息找到内存中的缓冲页呢，因为是个链表，O(n)显然不可取，所以有个专门的HashTable维护了表空间-页号和buffer页的对应关系。

4.2 LRU链表

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

mysql的LRU有很多改进，例如为了减少热点数据频繁在链表中移来移去，退出了前3/4个节点是young区，后1/4是old区，young区节点被访问后需要先判断距离上次被访问的时间是否大于一个阈值时间，如果没有就不移动了，因为young短时间也不会有被驱逐的危险，老年人才会被优先淘汰，这样减少了LRU的频繁操作。

4.3 脏页落盘

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

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

5 bin log

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

文件记录模式有STATEMENT、ROW和MIXED三种。

STATEMENT记录每次执行的sql，日志小，但是有些sql有毒比如now()，可能导致恢复的时候与之前数据不一致。针对这种函数也有解决方案，如下是一个binlog截图，先设置当前时间戳然后，再运行now的这句sql。

ROW记录每一行被改的数据，能还原所有的细节，但是如果有加一列这种操作，可能导致全表都得生成binlog。

MIXED则是两者结合。

实操：

首先binlog需要在my.ini配置文件中打开，对应的是log-bin=mysql-bin这一行配置，打开后进行数据写操作就会产生mysql-bin.000001这个文件，后缀是递增的一个号。

该文件不能直接打开，可以通过登录mysql后，执行show binlog events来查看对应的sql。

也可以通过mysqlbinlog指令来将其转换成sql：

mysqlbinlog --no-defaults mysql/data/mysql-bin.000001 > output.sql
Copy

可以添加--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。

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

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

6 redo log

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

redolog vs binlog

binlog是逻辑日志，记写操作的sql；redolog是数据页的变动情况，物理日志，记录的是一些二进制数据；binlog会不断累积，redolog会循环利用文件组覆盖；binlog的目的是记录所有的写sql，用于归档，可以恢复到任意时间，redolog的目的是故障恢复，所以只需要保证记录了所有脏页即可。

update数据的过程是，读取出要改的数据页，扔到上文提到的BufferPool中，然后对页进行更改，当事务提交后，对应的缓冲页会扔到flush链，我们上文说系统会定期刷新flush链的内容。不过漏掉了细节，就是对页进行更改后，需要将更改的内容以redolog的形式进行持久化。redolog的生成过程，也需要现将生成的内容以redo log block的形式放到LogBuffer中，然后等LogBuffer刷盘，有两个时机是必须刷盘的：

• 1 事务提交的时候，必须刷盘，虽然事务提交BufferPool中的flush链表可以不立即刷盘，但LogBuffer必须立即同步刷盘。
• 2 flush链表中脏页要刷新到磁盘之前，脏页如果要刷新到磁盘，必须保证他的LogBuffer中对应的redolog日志先刷盘完成。

当然刷盘时机还有其他，比如做checkpoint时，每秒定期等。不过我们至少是能保证，当事务提交的时候，也就是内存中的页成为了脏页，那么redolog就一定要刷盘，这是最基本的保证，这样才能保证redolog是保存了所有的脏页数据的。

binlog为什么不能用来做故障恢复？

因为故障恢复本身是要把产生线上影响的数据恢复回来，这是mysql先写内存后异步刷盘导致的问题。如果写内存后，没有刷盘就崩溃了，此时需要将写入内存的脏页恢复到磁盘上，这是因为 脏页虽然在内存中，但是可能已经提供服务出去了。因而故障恢复本质就是脏页的恢复。redolog能保证事务提交的时候已经刷盘到日志文件，专门就用来恢复脏页。binlog并不做这个保证，并且记录的是全量的写sql日志，如果用其做故障恢复，要全量删库然后运行binlog，过于兴师动众。

为什么不直接在事务提交时刷盘到ibd文件？

上面说故障恢复本质是先内存，然后异步刷盘ibd数据文件导致的，redolog保证了刷内存的同时也写入redolog文件，为什么不直接写入ibd文件不就可以了吗。换句话说，内存和ibd文件一直保持强一致，不就可以了。毕竟写redolog文件也是写文件，直接写ibd文件也是写文件。这是因为ibd文件写入的话，可能涉及到多个不同位置的页，磁盘是随机写，速度慢，而redolog是连续写速度快，对正常sql的性能影响小很多很多。

6.1 redolog文件组

redo log是指定的文件个数的，比如设置了3个文件，那么就是xx.0 xx.1 xx.2这三个文件，每个文件大小也是指定的，当.2文件写满的时候，就会覆盖.0文件。redo log是用来崩溃恢复的，所以对于已经存到db磁盘的数据的redolog就不再需要了，因而可以循环利用文件组。但是需要保证确实被覆盖的部分数据，确实已经刷盘到DB了，所以有了checkpoint的概念，简单讲他是专门检查当前已经刷到ibd文件的日志序号（LSN）。

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

6.2 MTR的原子性

在进行事务操作的时候，改动都是先写Buffer Pool，然后由redolog记录，redolog被分为很多个不可分割的”组”，例如某个insert操作中，主键的max值自增是一个组，插入b+树数据是一个组。而b+树插入可能有很多步骤，尤其产生分裂的时候，如何保证这个组不可分割，那就在redo中以一个特殊标志位标识是组的最后一个log完成。这样就能在恢复的时候，不至于只恢复半个组，导致数据不一致。我们把只包含一个分组的这个操作叫做(MTR)最小事务，一个MTR对应不可分割的redolog组。

7 undo log

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

一行数据3个隐藏字段row_id，trx_id，roll_pointer。其中事务id就是当前的事务的id，roll_pointer则是指向上一次事务修改的数据，这个数据就在undo log中。因而当事务中修改数据的时候，需要将老的数据扔到undo_log的数据页中新的数据通过指针指向老的数据。这样如果事务回滚的话，只需将老的数据恢复回来即可。

7.1 undo页

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

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

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

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

insert undo链表 中只存储类型为 TRX_UNDO_INSERT_REC 的 undo日志 ，这种类型的 undo日志 在事务提交之后就没用了，就可以被清除掉。所以在某个事务提交后，重用这个事务的 insert undo链表 （这个链表中只有一个页面）时，可以直接把之前事务写入的一组 undo日志 覆盖掉，从头开始写入新事务的一组 undo日志 ，如下图所示

但是update链就稍微特殊，因为事务提交后，也不代表undolog可以删除，因为mvcc追溯版本可能还会追溯到提交后的历史事务中来，所以page可以重复利用空闲的部分，但是原来的数据不能改。

7.2 undo段（回滚段）

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

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

8 事务的隔离级别

• read uncommited: 有脏读问题，即事务A写的数据，还没提交事务B就读到了，A回滚后，B使用的是脏数据。
• read commited: 有不可重复读问题，即事务A查到一条数据，事务B修改了这条并提交，事务A再次查询发现，同一条数据前后两次读出来的结果不一样了。
• repeatable read: 有幻读问题，即事务A按照条件P查出了一批数据，而事务B插入了一批符合P的数据并提交。此时事务A又用P去查数据，发现条数比之前多了。
• serializable: 无任何并发问题，因为不支持并发，串行执行事务。

9 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。

9.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说明不会被任何事务所依赖了，那这部分就可以被清理了。有个专门的线程来做清理工作。

9.2 快照读与当前读

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

update user set age=20 where age=19;
Copy

上面这句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条)
提交
Copy

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

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

10 锁

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

10.1 行锁

行锁主要有5种： 记录锁，间隙锁，临键锁(next-key)，插入意向锁和隐式锁。

记录锁就是锁住当前条目，例如update xx where id=1会把id=1这一条锁住防止其他事务对其修改。

间隙锁(gap)是锁住B+树中当前条目和前一条之间的缝隙，例如update xx where id<1假如找到0条数据，并且存在id=1这条，那么就需要锁住(-无穷，1)，防止其他事务在这个范围插入了数据，导致后续当前读读取的条目增加。

next-key是记录锁+间隙锁，也就是左开右闭区间的锁，例如update xx where id<=1假如找到1条id=1这条数据，那么就需要锁住(-无穷,1]这个范围，因为1也不能被修改，这就是next-key lock。

对于以上三种锁，每种又分互斥锁(X锁)和共享锁(S锁)，X锁加锁后的数据不能被其他事务读或写，S锁加锁后的数据不能被其他事务写，但是可以被读，update、delete等写sql和select for update都是加X锁，而select in share mode是S锁。

插入意向锁是指前面gap或者next-key锁住一个范围之后，如果另一个事务想要在这个范围插入数据会被阻止，并且会分配一把锁给这个事务，只不过是waiting等锁的状态，等gap释放就可以插入了。

隐式锁，是针对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 唯一索引]
Copy

查看锁

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

加锁的原则就是防止其他事务影响这句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，原因不明
Copy

10.2 表锁

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

LOCK TABLES user READ;
LOCK TABLES user WRITE;
Copy

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

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

10.3 MDL

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

10.4 死锁

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

11 explain

mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)
Copy

结果中各个列的含义：

• id: 一个select对应一个id，如果是一个select的join操作，会有两个相同id。如果有子查询id会增加，如果能转join的子查询会优化成join。
• select_type: select的类型，比如是普通的还是union还是子查询等等，类型很多，用于判断是sql中哪一部分。
• table: 表名，如果sql写了别名，就显示别名
• type: 查询的类型很重要
  • const唯一索引等值查询
  • eq_ref指join时on条件中有一个表的唯一非null索引
  • ref对普通的二级索引等值查询，或join时on条件有一个表的普通二级索引
  • ref_or_null普通二级索引等值查询，该二级索引可以是null
  • index_merge索引合并，比较少见
  • unique_subquery/index_subquery.
  • range是索引使用in 或者大小于。
  • ALL全表
• possible_keys: 可能用到的key，key则是实际用到的
• key_len: key的最大长度(字节)
• ref: 当type是const,eq_ref,ref,ref_or_null,unique_subquery,index_subquery的时候，ref才有效，代表等号右边是什么。
• extra: 一些额外的辅助信息。

11.1 单表查询的例子

例子：

type=NULL，选择一些跟表没有直接关系的变量。

mysql> explain select @@version;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
Copy

type=const，通过主键或唯一索引等值插叙

mysql> explain select * from user where id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
Copy

type=ref，通过二级普通索引等值查询

mysql> explain select * from test where t2=2;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | t2,t2_2       | t2   | 5       | const |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
Copy

type=range，通过索引进行范围查询(in 或 大小于)，注意in如果只1个元素会退化成=。

mysql> explain select * from user where name in ('lily', 'zara');
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | name          | name | 1022    | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
Copy

type=ALL，全表查询，例如非索引条件

mysql> explain select * from user where age>1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |    33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
Copy

11.2 连表查询的例子

建立一个两个新的表test和test2结构完全一样如下，主键id，唯一索引t1，普通索引t2，联合索引t2_t3，普通列t4，t5，t6.

create table test
(
    id INT not null primary key, 
    t1 INT not null, 
    t2 INT, 
    t3 INT, 
    t4 INT, 
    t5 INT, 
    t6 varchar(255),
    unique index(t1), 
    index(t2), 
    index(t2, t3)
);
Copy

先来看个联合索引的例子，当空表查询时会使用key是t2，而当出入了数据的时候发现t2t3联合的效率更高，就用t2_2了。

mysql> explain select * from test where t2=1 and t3=1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ref  | t2,t2_2       | t2   | 5       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> insert into test (id, t1, t2, t3, t4, t5) values
    -> (1,1,1,1,1,1),
    -> (2,2,1,1,2,2),
    -> (3,3,1,2,4,4);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> explain select * from test where t2=1 and t3=1;
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | t2,t2_2       | t2_2 | 10      | const,const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+-------------+------+----------+-------+
Copy

type=eq_ref，当我们将test和test2联合查询，并且有使用其中一个表的唯一索引作为连接条件，如下test.id=test2.t5，此时查询方式是，先全表扫test2拿出所有的test2.t5数据，然后作为id索引条件到test中去查询。所以有如下执行计划，eq_ref就是指用唯一索引查询，但是不是等于常量而是来自test2的全表结果，ref列的值是test库test2表t5列。

mysql> explain select test.t5, test2.t6 from test2 join test on test.id = test2.t5;
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | ALL    | NULL          | NULL    | NULL    | NULL          |    1 |   100.00 | Using where |
|  1 | SIMPLE      | test  | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.test2.t5 |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------------+
Copy

如果我们把on的条件改成常量，就会变成const，因为只需要根据主键查询test，然后再全表扫test2

mysql> explain select test.t5, test2.t6 from test  join test2 on test.id = 1 and test.t3 =test2.t3;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  1 | SIMPLE      | test2 | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
Copy

type=ref，条件中的test2.t2是普通索引，而不是唯一非null，此时就会变成ref。

mysql> insert into test (id,t1, t2, t3) values (4,4,2,2), (5,5,2,2), (6,6,2,3);

mysql> explain select test.t5, test2.t6 from test  join test2 on test.t2 = test2.t5;
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | test2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL          |    1 |   100.00 | Using where |
|  1 | SIMPLE      | test  | NULL       | ref  | t2,t2_2       | t2   | 5       | test.test2.t5 |    3 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+------+---------+---------------+------+----------+-------------+
Copy

其他几个type暂时不列出来了，比较复杂。

12 多机

12.1 主从同步

主从同步的流程一般都是依赖主节点的binlog，从节点的io线程从主节点读binlog，看是否有新的数据，如果有的话就拉下来写到自己的relaylog中，然后再同步到数据库，主流程如下。

在同步过程中有多个策略可以选择：

• 异步复制，master不管slave，只管自己写binlog，slave异步去同步，这种新能最好，但是一致性最差。
• 半同步复制，事务提交要求n个slave收到日志才算提交成功，当n设置为全部slave节点就是同步复制了。

12.2 分库分表

不到万不得已不要分库分表。

数据量太大时，分库可以缓解单机压力，分表可以降低b+复杂度。

水平拆分是表结构完全一致，数据平均切分。垂直拆分例如分库就是部分表扔到另一个库，分表则是部分字段拆到另一个表。

分库分表就要用到分布式事务，两阶段/三阶段提交问题，自增id问题，查询时需要额外的外排等等。

12.3 分布式事务

在分库分表之后的事务就必须使用分布式事务了，此外对于更通用的场景，服务改不同的库表的一致性，也需要借助分布式事务。分布式事务常见的解决策略有2pc、3pc等。简单说一下这俩。

2pc：

• 第一阶段：协作者广播VOTE_REQUEST，等待commit或者abort
• 第二阶段：协作者根据收回的ack，广播一个全局commit或者abort

2pc比较简单，但是阻塞时间较长，比如第一阶段有个参与者就不能提交事务比如没有库存了，这时候其他参与者还在执行事务。有单点(协调器)故障问题，以及commit消息发送给部分机器后，协调器挂了，会导致不一致的问题。

3pc:

• CanCommit阶段其实和2PC的准备阶段很像。协调者向参与者发送commit请求，参与者如果可以提交就返回Yes响应，否则返回No响应。这时参与者并不执行事务，而只是说我是否准备好了。
• PreCommit操作。根据响应情况，有以下两种可能。假如协调者从所有的参与者获得的反馈都是Yes响应，那么就会执行事务的预执行。执行事务，但是不commit。
• Docommit.发送提交请求 协调接收到参与者发送的ACK响应，那么他将从预提交状态进入到提交状态。并向所有参与者发送doCommit请求。

3pc主要是为了解决2PC提交协议的阻塞问题。相对于2PC，3PC主要解决解决了2pc存在单点故障，导致节点持久阻塞的问题，降低了整个事务的阻塞时间和范围。3PC一旦参与者无法及时收到来自协调者的信息之后，他会默认执行commit，而2PC会一直持有事务资源并处于阻塞状态。缺点：但是这种实现就可能导致一致性的问题。