[关闭]
@xiaohaizi 2022-02-08T03:51:40.000000Z 字数 148111 阅读 513

前言

本MySQL系列文章首发于微信公众号“我们都是小青蛙”，小孩子希望它们能在各位小伙伴工作面试过程中起到一定作用，希望对大家有帮助。

作者介绍：小孩子，目前创作：
- 《MySQL是怎样使用的：快速入门MySQL》书籍
- 《MySQL是怎样运行的：从根儿上理解MySQL》书籍
- 《计算机是怎样运行的：从根儿上理解计算机》掘金小册

![image_1frbmlgonj5cdijp9f1aci1p4h9.png-413.4kB]（http://static.zybuluo.com/xiaohaizi/rztjfqgjttg5k1wxv1an3ll1/image_1frbmlgonj5cdijp9f1aci1p4h9.png）

MySQL索引是怎么推导出来的

公众号文章

写本文的起因

《MySQL是怎样运行的》于2020年11.1日发行至今近一年的时间，已经印刷近两万册，十分感谢各位小伙伴的捧场。不过最近在答疑群里一直有小伙伴说：“书看的时候很爽，以为自己啥都会了，不过过一段时间后就都忘了，面试一问好像都学过，但是就是想不起来具体的内容是个啥”。

小孩子在这里需要强调一下，这不是一本入门的书籍，不是随便翻翻就可以学会，需要大家拿出一个完整的时间，找一个笔记本好好记一下笔记才可以学会的。

另外，书里涉及大量的细节，比方说我们致力于将记录、页面、索引、表空间中涉及的结构的每一个字节都是干什么的给大家讲清楚，但记住这些细节并不是重点，细节是用来辅助大家理解脉络的。我们尝试过在没有细节的情况下去讲述MySQL是怎样执行查询的，但大家一定会如站在空中楼阁，表面看起来很光鲜，但时刻都有掉下去的危险。

本文来尝试以最简单的脉络来帮各位理出一条线，大家有了这条线作为主心骨，就不用怕淹没在细节的海洋中无法自拔了。

第1~2章

前两章非常简单，主要介绍如何启动MySQL服务器和客户端，以及启动选项和系统变量。没有什么难度，就不花篇幅唠叨了。

第3章

我们需要明白“字符”是面向人类的概念，计算机需要用一个二进制字节序列来表示字符，由于二进制字节序列和字符的映射关系谁都可以规定，所以市面上产生了各种各样的字符集。

数字之间可以比较大小，字符之间也可以比较大小，不过比较字符时取决于人的好恶。比方说有人觉得'A'和'a'是相等的，有人就觉得不相等。所以我们在比较字符前需要事先规定好一个规则来说明谁大谁小，这个规则就是所谓的比较规则。

MySQL支持若干种字符集，不同字符集也可以有不同的比较规则，我们可以通过一定语法来进行配置。

第4章

大家需要知道一条记录实际上是分两部分存储的：

重点需要知道在记录的额外信息中有一个两字节的称为next_record指针，通过该指针各条记录可以组成一个单向链表：

单向链表中的记录的主键值是按照从小到大的顺序排序的，也就是说这个单向链表是有序的。

第5章

大家需要知道记录是放在页里边的， InnoDB是以页为单位从磁盘上加载数据的。页面可以配置成4KB、8KB、16KB、32KB、64KB几种大小，不过默认是16KB的。

对于存储记录的数据页来说，它的结构如下图所示：

下边假设某个页中存储了16条用户插入的记录，该数据页的效果图如下所示：

其中Infimum记录和Supremum记录是InnoDB给我们自动生成的两个伪记录，并且规定：

如上图所示，各条记录之间按照主键值大小组成了一个单向链表。

接下来我们面临的问题是如何从一个按照主键值大小进行排序的单向链表中快速定位到指定的主键值在哪里。很遗憾，链表做不到呀~

为了解决快速搜索的问题，设计InnoDB的大叔引入了一个称作页目录的东西。具体的做法就是将单向链表中的记录分成若干个组，如下图所示：

然后将把每个组最大的那条记录在页面中的地址（就是距离页面第0个字节处的偏移量）取出来单独放到页面中的一个部分。每个地址占用2个字节，多个地址就可以组成一个数组结构，如下图所示：

可见，页目录本质上就是一个指针数组，指针指向的记录是有序的，我们就可以针对这个页目录进行二分搜索。比方说我们想找主键值为6的记录，那就可以通过页目录先进行二分查找，定位到主键值6其实是在第3组里，然后再遍历第3组中的记录，就可以定位到具体的主键值6的记录在哪里了。

稍微总结一下，通过第5章的学习，我们应该知道如何在单个页面中快速定位某个主键值的记录了，大致分两步：

第6章

现在大家已经知道如何在单个页面中通过页目录来快速定位某个主键值对应的记录了。

一个页里可以放置若干条记录，如果记录太多，就得分散到不同的页中。

页中存储的除了记录以外，还有若干额外信息，其中包括两个指针（这两个指针在上图中的File Header中）：

这样多个页之间也可以通过双向链表连接起来，而且InnoDB会保证每个页中存储记录的主键值肯定不大于下一个页中存储记录的主键值，也就是说由页面组成的双向链表也是按照它们中存储记录的主键值大小进行排序的。

现在我们做一个大胆的假设：假设一个页里只能存储4条记录，然后有16条记录分散在4个页里，页之间按照它们中存储的主键值进行排序为双向链表，如下图所示：

现在如果我们想查找主键值为6的记录该怎么办呢？很遗憾，我们不知道主键值为6的记录在哪个页里，只能从第一个页开始，一个页一个页的找。

遍历很低效，InnoDB采取的方案就是将每个页的主键值最小的用户插入的记录的主键值拿出来，然后和该页的页号拼接成一个新的记录，为了和用户插入的记录做区分，我们把用户插入的记录称作用户记录，把这个新拼接成的记录称作目录项记录。

目录项记录也按照主键值排序成单向链表，也可以通过页目录来快速定位主键值等于某个值的目录项记录。

引入了目录项记录以及存储目录项记录的页之后，我们再查找主键值为6的记录就分为2步了：

表中的记录是可以不断扩充的，每增加一个存储用户记录的页，就需要在存储目录项记录的页中增加一条目录项记录，而页的大小是有限的（默认是16KB），所以存储目录项记录的页也可能被填满，这时候就需要再申请新的存储目录项记录的页，如下图所示：

多个存储目录项记录的页之间也是用双向链表连接的，而且它们也是按照各自页中目录项记录的主键值进行排序的。但是对于多个存储目录项记录的页来说，我们怎么知道我们要找的记录应该在哪个存储目录项记录的页中呢？

这时设计InnoDB的大叔开始了套娃操作，即再将各个存储目录项记录的页中最小的主键值给提取出来，与相应的页号组成更高一层的目录项记录，再填充到新的页里，如下图所示：

更高一层的目录项记录也按照主键值大小连成了单向链表，这些更高一层的目录项记录所在的页中也有页目录结构。

这样的话各个页面就分为了3层。设计InnoDB的大叔把最下边的存放用户记录的一层称作第0层，第0层往上是第1层，再往上是第2层。

现在如果我们想查找主键值为6的记录的话，那么查找过程就分为了3步：

设计InnoDB的大叔给上边的结构给了个名：B+树（因为像一颗倒过来的树）。InnoDB中的B+树还有另一个别名：索引。B+树的第0层，也就是最下边那层的页面称作叶子节点，其余的页面被称作非叶子节点或者内节点。其中最高层的节点也被称作根节点或者根页面。

至此，我们了解了索引结构是如何诞生的。

上边介绍按照主键进行排序的B+树索引结构被称作聚簇索引，聚簇索引的叶子节点存放着完整的用户记录（即各个列的值）。我们也可以给自己感兴趣的列或者列组合建立B+树索引，不过此时B+树中就会按照我们给定的列或列组合进行排序（这里指的是每一层页面的排序以及页面中记录的排序），我们把这种给自己感兴趣的列建立的索引称作二级索引或者辅助索引。二级索引的叶子节点中只存放建立索引时指定的列的值以及该记录对应的主键值。

小结一下

下边的小结并不仅仅针对聚簇索引，也针对二级索引。我们将用键值来表示聚簇索引中的主键值或者二级索引中的二级索引列值。

  1. 记录是按照键值大小组成一个单向链表的。

  2. 记录是被存放在页面中的，页面中维护着一个页目录结构，通过页目录可以对键值进行二分查找，从而加快在单个页面中的查询速度。

  3. 多个页面可以按照键值大小组成双向链表，为了快速定位到需查找的键值在哪个页面中，我们引入了目录项记录以及存储目录项记录的页。

  4. 存储目录项记录的页大小也有限，我们引入了更高层次的目录项记录，从而形成了套娃结构。我们把这个套娃结构称作B+树，也就是InnoDB中的索引。

好了，前6章的整体脉络就是上边这些了，大家抓住这些主线，然后进行针对性的阅读，补充更多的细节。一旦理解了这个脉络，之后想忘也忘不掉了。

MySQL的server层和存储引擎层是如何交互的

标签： 公众号文章

SQL的全称是Structured Query Language，翻译成中国话就是结构化查询语言。这是一种声明式的语法，何为声明式？可以联想一下我们生活中的老板，老板在布置任务的时候会告诉你：小王啊，今天把这些砖从A地搬到B地啊，然后就没然后了。老板并不关心你是用手抬，还是用车拉，老板只关心结果：你把砖搬过去就好了。我们之于数据库而言，就是一个老板，SQL语句就是我们给数据库下达的任务，至于具体数据库怎么执行我们并不关心，我们只关心最后数据库给我们返回的结果。

对于设计数据库的人而言，语句怎么执行就得好好考虑了，老板不操心，事儿总还得干。设计MySQL的大叔人为的把MySQL分为server层和存储引擎层，但是什么操作是在server层做的，什么操作是在存储引擎层做的大家可能有些迷糊。本文将以一个实例来展示它们二者各自负责的事情。

准备工作

为了故事的顺利发展，我们先创建一个表：

  1. CREATE TABLE hero (
  2.     id INT,
  3.     name VARCHAR(100),
  4.     country varchar(100),
  5.     PRIMARY KEY (id),
  6.     KEY idx_name (name)
  7. ) Engine=InnoDB CHARSET=utf8;

我们为hero表的id列创建了聚簇索引，为name列创建了一个二级索引。这个hero表主要是为了存储三国时的一些英雄，我们向表中插入一些记录：

  1. INSERT INTO hero VALUES
  2.     (1, 'l刘备', '蜀'),
  3.     (3, 'z诸葛亮', '蜀'),
  4.     (8, 'c曹操', '魏'),
  5.     (15, 'x荀彧', '魏'),
  6.     (20, 's孙权', '吴');

现在表中的数据就是这样的：

  1. mysql> SELECT * FROM hero;
  2. +----+------------+---------+
  3. | id | name       | country |
  4. +----+------------+---------+
  5. |  1 | l刘备      | 蜀      |
  6. |  3 | z诸葛亮    | 蜀      |
  7. |  8 | c曹操      | 魏      |
  8. | 15 | x荀彧      | 魏      |
  9. | 20 | s孙权      | 吴      |
  10. +----+------------+---------+
  11. 5 rows in set (0.00 sec)

准备工作就做完了。

正文

一条语句在执行之前需要生成所谓的执行计划，也就是该语句将采用什么方式来执行（使用什么索引，采用什么连接顺序等等），我们可以通过Explain语句来查看这个执行计划，比方说对于下边语句来说：

  1. mysql> EXPLAIN SELECT * FROM hero WHERE name < 's孙权' AND country = '蜀';
  2. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
  3. | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                              |
  4. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
  5. |  1 | SIMPLE      | hero  | NULL       | range | idx_name      | idx_name | 303     | NULL |    2 |    20.00 | Using index condition; Using where |
  6. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+------------------------------------+
  7. 1 row in set, 1 warning (0.03 sec)

输出结果的key列值为idx_name，type列的值为range，表明会针对idx_name二级索引进行一个范围查询。很多同学在这里有一个疑惑：到底是一次性把所有符合条件的二级索引都取出来之后再统一进行回表操作，还是每从二级索引中取出一条符合条件的记录就进行回表一次？其实server层和存储引擎层的交互是以记录为单位的，上边这个语句的完整执行过程就是这样的：

  1. server层第一次开始执行查询，把条件name < 's孙权'交给存储引擎，让存储引擎定位符合条件的第一条记录。

  2. 存储引擎在二级索引idx_name中定位name < 's孙权'的第一条记录，很显然第一条符合该条件的二级索引记录的name列的值为'c曹操'。然后需要注意，我们看到EXPLAIN语句的输出结果的Extra列有一个Using index condition的提示，这表明会将有关idx_name二级索引的查询条件放在存储引擎层判断一下，这个特性就是所谓的索引条件下推（Index Condition Pushdown，简称ICP）。很显然这里的ICP条件就是name < 's孙权'。有的同学可能会问这不就是脱了裤子放屁么，name值为'c曹操'的这条记录就是通过name < 's孙权'这个条件定位的，为啥还要再判断一次？这就是设计MySQL 的大叔的粗暴设计，十分简单，没有为啥～

    1. 小贴士：
    3. 对于使用二级索引进行等值查询的情况有些许不同，比方说上边的条件换成`name = 's孙权'`，对于等值查询的这种情况，设计MySQL的大叔在InnoDB存储引擎层有特殊的处理方案，是不作为ICP条件进行处理的。

    然后拿着该二级索引记录中的主键值去回表，把完整的用户记录都取到之后返回给server层（也就是说得到一条二级索引记录后立即去回表，而不是把所有的二级索引记录都拿到后统一去回表）。

  3. 我们的执行计划输出的Extra列有一个Using Where的提示，意味着server层在接收到存储引擎层返回的记录之后，接着就要判断其余的WHERE条件是否成立（就是再判断一下country = '蜀'是否成立）。如果成立的话，就直接发送给客户端。

    1. 小贴士：
    3. 什么？发现一条记录符合条件就发送给了客户端？那为什么我的客户端不是一条一条的显示查询结果，而是一下子全部展示呢？这是客户端软件的鬼，人家规定在接收完全部的记录之后再展示而已。

    如果不成立的话，就跳过该条记录。

  4. 接着server层向存储引擎层要求继续读刚才那条记录的下一条记录。

  5. 因为每条记录的头信息中都有next_record的这个属性，所以可以快速定位到下一条记录的位置，然后继续判断ICP条件，然后进行回表操作，存储引擎把下一条记录取出后就将其返回给server层。

  6. 然后重复第3步的过程，直到存储引擎层遇到了不符合name < 's孙权'的记录，然后向server层返回了读取完毕的信息，这时server层将结束查询。

这个过程用语言描述还是有点儿啰嗦，我们写一个超级简化版的伪代码来瞅瞅（注意，是超级简化版）：

  2. first_read = true;  //是否是第一次读取
  3. while (true) {
  5.     if (first_read) {
  6.         first_read = false;
  7.         err = index_read(...);  //调用存储引擎接口，定位到第一条符合条件的记录;
  8.     } else {
  9.         err = index_next(...); //调用存储引擎接口，读取下一条记录
  10.     }
  12.     if (err = 存储引擎的查询完毕信息) {
  13.         break;  //结束查询
  14.     }
  16.     if (是否符合WHERE条件) {
  17.         send_data();    //将该记录发送给客户端;
  18.     } else {
  19.         //跳过本记录
  20.     }
  21. }

上述的伪代码虽然很粗糙，但也基本表明了意思哈～ 之后有机会我们再唠叨唠叨使用临时表的情况已经使用filesort的情况是怎么执行的。

MySQL查询成本和扫描区间(MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！)

标签： 公众号文章

前情回顾

经过前面的学习我们知道了对于InnoDB存储引擎来说，表中的数据都存储在所谓的B+树中，我们每多建立一个索引，就相当于多建立一棵B+树。

我们向表中存储了多少条记录，每一棵B+树的叶子节点中就包含多少条记录（注意是“每一棵”，包括聚簇索引对应的B+树以及二级索引对应的B+树）。

示例

我们举个例子：

  1. CREATE TABLE t (
  2.     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  3.     key1 INT,
  4.     common_field VARCHAR(100),
  5.     PRIMARY KEY (id),
  6.     KEY idx_key1 (key1)
  7. ) Engine=InnoDB CHARSET=utf8;

这个表就包含2个索引（也就是2棵B+树）：

我们向表中插入一些记录：

  1. INSERT INTO t VALUES
  2.     (1, 30, 'b'),
  3.     (2, 80, 'b'),
  4.     (3, 23, 'b'),
  5.     (4, NULL, 'b'),
  6.     (5, 11, 'b'),
  7.     (6, 53, 'b'),
  8.     (7, 63, 'b'),
  9.     (8, NULL, 'b'),
  10.     (9, 99, 'b'),
  11.     (10, 12, 'b'),
  12.     (11, 66, 'b'),
  13.     (12, NULL, 'b'),
  14.     (13, 66, 'b'),
  15.     (14, 30, 'b'),
  16.     (15, 11, 'b'),
  17.     (16, 90, 'b');

所以现在s1表的聚簇索引示意图就是这样：

s1表的二级索引示意图就是这样：

从图中可以看出，值为NULL的二级索引记录都被放到了B+树的最左边，这是因为设计InnoDB的大叔们有规定：

We define the SQL null to be the smallest possible value of a field.

也就是认为NULL值是最小的。

小贴士：


原谅我们把B+树的结构做了一个如此这般的简化，我们省略了页面的结构，省略了所有的内节点（只画了了三角形替代），省略了记录之间的链表，因为这些不是本文的重点，画成如果所示的样子只是为了突出叶子节点处的记录是按照给定索引的键值进行排序的。

查询是怎么执行的

比方说我们现在执行下边这个查询语句：

  1. SELECT * FROM t WHERE key1 = 53;

那么语句的执行过程就如下图所示：

用文字描述一下这个过程也就是：

小贴士：


B+树叶子节点中的记录都是按照键值按照从小到大的顺序排好序的，通过B+树索引定位到叶子节点中的一条记录是非常快速的。

像下边这个查询：

  1. SELECT * FROM t WHERE key1 > 20 AND key1 < 50;

它的执行示意图就是这样：

用文字表述就是这样：

从上边的步骤中也可以看出来：需要扫描的二级索引记录越多，需要执行的回表操作也就越多。如果需要扫描的二级索引记录占全部记录的比例达到某个范围，那优化器就可能选择使用全表扫描的方式执行查询（一个极端的例子就是扫描全部的二级索引记录，那么将对所有的二级索引记录执行回表操作，显然还不如直接全表扫描）。

所以现在的结论就是：判定某个查询是否可以使用索引的条件就是需要扫描的二级索引记录占全部记录的比例是否比较低，较低的话说明成本较低，那就可以使用二级索引来执行查询，否则要采用全表扫描。

扫描区间和边界条件

对于下边这个查询来说：

  1. SELECT * FROM t WHERE key1 > 20 AND key1 < 50;

如果我们使用idx_key1执行该查询的话，那么就需要扫描key1值在(20, 50)这个区间中的所有二级索引记录，我们就把(20, 50)称作使用idx_key1执行上述查询时的扫描区间，把key1 > 20 AND key1 < 50称作形成该扫描区间的边界条件。

只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=或者LIKE操作符连接起来，就可以产生所谓的扫描区间。这里头有一些比较容易让人忽略的扫描区间：

其实对于任何查询语句来说，优化器都会按照下边的思路去判断该使用何种方式执行查询：

  1. 分析使用不同索引执行查询时对应的扫描区间都是什么。

  2. 采用某些手段来分析以下在使用扫描不同索引的扫描区间时对应的成本分别是多少。

    小贴士：


    我们这里定性的分析成本，而不是定量分析，定量分析可以到书中具体查看。大家粗略的认为扫描区间中的记录越多，成本就越高就好了。

  3. 比较使用不同索引执行查询的成本以及全表扫描的成本哪个更低，选择成本最低的那个方案去执行查询，这个方案就是所谓的执行计划。

具体的查询条件分析

我们分别看一下WHERE子句中出现IS NULL、IS NOT NULL、!=这些条件时优化器是怎么做决策的。

IS NULL的情况

比方说这个查询：

  1. SELECT * FROM t WHERE key1 IS NULL;

优化器在真正执行查询前，会首先少量的访问一下索引，调查一下key1在[NULL, NULL]这个区间的记录有多少条：

小贴士：


[NULL, NULL]这个区间代表区间里只有一个NULL值。

优化器经过调查得知，需要扫描的二级索引记录占总记录条数的比例是3/16，它觉得这个查询使用二级索引来执行比较靠谱，所以在执行计划中就显示使用这个idx_key1来执行查询：

IS NOT NULL的情况

比方说这个查询：

  1. SELECT * FROM t WHERE key1 IS NOT NULL;

优化器在真正执行查询前，会首先少量的访问一下索引，调查一下key1在(NULL, +∞)这个区间内记录有多少条：

小贴士：


我们这里把NULL当作是最小值对待，你可以认为它比-∞都小。另外注意区间(NULL, +∞)是开区间，也就意味这不包括NULL值。

优化器经过调查得知，需要扫描的二级索引记录占总记录条数的比例是13/16，跟显然这个比例已经非常大了，所以优化器决定使用全表扫描的方式来执行查询：

那怎么才能让使用IS NOT NULL条件的查询使用到二级索引呢？这还不简单，让表中符合IS NOT NULL条件的记录少不就行了，我们可以执行一下：

  1. UPDATE t SET key1 = NULL WHERE key1 < 80;

这样再去执行这个查询：

  1. SELECT * FROM t WHERE key1 IS NOT NULL;

优化器在真正执行查询前，会首先少量的访问一下索引，调查一下key1在(NULL, +∞)这个区间内记录有多少条：：

优化器经过调查得知，需要扫描的二级索引记录占总记录条数的比例是3/16，它觉得这个查询使用二级索引来执行比较靠谱，所以在执行计划中就显示使用这个idx_key1来执行查询：

!= 的情况

比方说这个查询：

  1. SELECT * FROM t WHERE key1 != 80;

优化器在真正执行查询前，会首先少量的访问一下索引，调查一下key1在(NULL, 80)和(80, +∞)这两个区间内记录有多少条：

优化器经过调查得知，需要扫描的二级索引记录占总记录条数的比例是2/16，它觉得这个查询使用二级索引来执行比较靠谱，所以在执行计划中就显示使用这个idx_key1来执行查询：

且慢！为啥执行计划的rows列的值为3呢？？？这是个什么鬼，明明只有2条记录符合条件嘛。哈哈，我们罗列一下每个区间找到的符合条件的记录数量：

可是设计优化器的大叔在这里有个规定：当某个扫描区间符合给定条件的记录数量为0时，硬生生的把它掰成1。也就是说实际优化器认为在(NULL, 80)这个扫描区间中有1条记录符合条件key1 != 80。所以执行计划的rows列才显示了3。

小贴士：


下边是设计优化器的大叔自己对当某个扫描区间符合给定条件的记录数量为0时硬生生的把它掰成1的解释（能看懂的就看，看不懂赶紧跳过）：
The MySQL optimizer seems to believe an estimate of 0 rows is always accurate and may return the result 'Empty set' based on that. The accuracy is not guaranteed, and even if it were, for a locking read we should anyway perform the search to set the next-key lock. Add 1 to the value to make sure MySQL does not make the assumption!

总结

至此，我们分别分析了拥有IS NULL、IS NOT NULL、!=这三个条件的查询是在什么情况下使用二级索引来执行的，核心结论就是：成本决定执行计划，跟使用什么查询条件并没有什么关系。优化器会首先针对可能使用到的二级索引划分几个扫描区间，然后分别调查这些区间内有多少条记录，在这些扫描区间内的二级索引记录的总和占总共的记录数量的比例达到某个值时，优化器将放弃使用二级索引执行查询，转而采用全表扫描。

小贴士：


其实扫描区间划分的太多也会影响优化器的决策，比方说IN条件中有太多个参数，将会降低优化器决定使用二级索引执行查询的几率。

另外，优化器调查在某个扫描区间内的索引记录的条数的方式有两种，一种是所谓的index dive（这种方式在数据少的时候是精确的，在数据多时会有些偏差），一种是依赖index statistics，也就是统计数据来做调查（这种方式的统计是很不精确的，有时候偏差是超级巨大的），反正不论采用哪种方式，优化器都会将各个扫描区间中的索引记录数量给计算出来。关于这两种调查方式在《MySQL是怎样运行：从根儿上理解MySQL》中都给出了详细的算法，当然都占用了相当大的篇幅，写在公众号文章里就有点杀鸡用牛刀了。

听说有一个最左原则？这回终于讲清楚了

标签： 公众号文章

准备工作

为了故事的顺利发展，我们需要先建立一个表：

  1. CREATE TABLE single_table (
  2.     id INT NOT NULL AUTO_INCREMENT,
  3.     key1 VARCHAR(100),
  4.     key2 INT,
  5.     key3 VARCHAR(100),
  6.     key_part1 VARCHAR(100),
  7.     key_part2 VARCHAR(100),
  8.     key_part3 VARCHAR(100),
  9.     common_field VARCHAR(100),
  10.     PRIMARY KEY (id),
  11.     KEY idx_key1 (key1),
  12.     UNIQUE KEY uk_key2 (key2),
  13.     KEY idx_key3 (key3),
  14.     KEY idx_key_part(key_part1, key_part2, key_part3)
  15. ) Engine=InnoDB CHARSET=utf8;

我们为这个single_table表建立了1个聚簇索引和4个二级索引，分别是：

然后我们需要为这个表插入10000行记录，除id列外其余的列都插入随机值就好了，具体的插入语句我就不写了，自己写个程序插入吧（id列是自增主键列，不需要我们手动插入）。

我们画一下single_table表的聚簇索引的示意图：

如图所示，我们把聚簇索引对应的复杂的B+树结构搞了一个极度精简版。可以看到，我们忽略掉了页的结构，直接把所有的叶子节点中的记录都放在一起展示，为了方便，我们之后就把聚簇索引叶子节点中的记录称为聚簇索引记录。虽然这个图很简陋，但是我们还是突出了聚簇索引一个非常重要的特点：聚簇索引记录是按照主键值由小到大的顺序排序的。当然，追求视觉上极致简洁的我们觉得图中的“其他列”也可以被略去，只需要保留id列即可，再次简化的B+树示意图就如下所示：

好了，再不能简化了，再简化就要把id列也删去了，就剩一个三角形了，那就真尴尬了。

通过聚簇索引对应的B+树，我们可以很容易的定位到主键值等于某个值的聚簇索引记录，比方说我们想通过这个B+树定位到id值为1438的记录，那么示意图就如下所示：

下边以二级索引idx_key1为例，画一下二级索引简化后的B+树示意图：

如图所示，我们在二级索引idx_key1对应的B+树中保留了叶子节点的记录，这些记录包括key1列以及id列，这些记录是按照key1列的值由小到大的顺序排序的，如果key1列的值相同，则按照id列的值进行排序。为了方便，我们之后就把二级索引叶子节点中的记录称为二级索引记录。

如果我们想查找key1值等于某个值的二级索引记录，那么可以通过idx_key1对应的B+树，很容易的定位到第一条key1列的值等于某个值的二级索引记录，然后沿着记录所在单向链表向后扫描即可。比方说我们想通过这个B+树定位到第一条key1值为'abc'的记录，那么示意图就如下所示：

扫描区间和边界条件

对于某个查询来说，最粗暴的执行方案就是扫描表中的所有记录，针对每一条记录都判断一下该记录是否符合搜索条件，如果符合的话就将其发送到客户端，否则就跳过该记录。这种执行方案也被称为全表扫描。对于使用InnoDB存储引擎的表来说，全表扫描意味着从聚簇索引第一个叶子节点的第一条记录开始，沿着记录所在的单向链表向后扫描，直到最后一个叶子节点的最后一条记录为止。虽然全表扫描是一种很笨的执行方案，但却是一种万能的执行方案，所有的查询都可以使用这种方案来执行。

我们之前介绍了利用B+树查找索引列值等于某个值的记录，这样可以明显减少需要扫描的记录数量。其实由于B+树的叶子节点中的记录是按照索引列值由小到大的顺序排序的，所以我们只扫描在某个区间或者某些区间中的记录也可以明显减少需要扫描的记录数量。比方说对于下边这个查询语句来说：

  1. SELECT * FROM single_table WHERE id >= 2 AND id <= 100;

这个语句其实是想查找所有id值在[2, 100]这个区间中的聚簇索引记录，那么我们就可以通过聚簇索引对应的B+树快速地定位到id值为2的那条聚簇索引记录，然后沿着记录所在的单向链表向后扫描，直到某条聚簇索引记录的id值不在[2, 100]这个区间中为止（其实也就是直到id值不符合id<=100这个条件为止）。与扫描全部的聚簇索引记录相比，扫描id值在[2, 100]这个区间中的记录已经很大程度的减少了需要扫描的记录数量，所以提升了查询效率。为简便起见，我们把这个例子中需要扫描的记录的id值所在的区间称为扫描区间，把形成这个扫描区间的查询条件，也就是id >= 2 AND id <= 100称为形成这个扫描区间的边界条件。

  1. 小贴士：
  3. 其实对于全表扫描来说，相当于我们需要扫描id值在(-∞, +∞)这个区间中的记录，也就是说全表扫描对应的扫描区间就是(-∞, +∞)。

对于下边这个查询：

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

我们当然可以直接使用全表扫描的方式执行该查询，但是观察到该查询的搜索条件涉及到了key2列，而我们又正好为key2列建立了uk_key2索引，如果我们使用uk_key2索引执行这个查询的话，那么相当于从下边的三个扫描区间中获取二级索引记录：

这些扫描区间对应到数轴上的示意图就如下图所示：

为方便起见，我们把像[1438, 1438]、[6328, 6328]这样只包含一个值的扫描区间称为单点扫描区间，把[38, 79]这样包含多个值的扫描区间称为范围扫描区间。另外，由于我们的查询列表是*，也就是需要读取完整的用户记录，所以从上述的扫描区间中每获取一条二级索引记录时，就需要根据该二级索引记录的id列的值执行回表操作，也就是到聚簇索引中找到相应的聚簇索引记录。

  1. 小贴士：
  3. 其实我们不仅仅可以使用uk_key2执行上述查询，使用idx_key1、idx_key3、idx_keypart都可以执行上述查询。以idx_key1为例，很显然我们无法通过搜索条件形成合适的扫描区间来减少需要扫描的idx_key二级索引记录数量，只能扫描idx_key1的全部二级索引记录。针对获取到的每一条二级索引记录，都需要执行回表操作来获取完整的用户记录。我们也可以说此时使用idx_key1执行查询时对应的扫描区间就是(-∞, +∞)。
  5. 这样子虽然行得通，但我们图啥呢？最粗暴的全表扫描方式已经要扫描全部的聚簇索引记录了，你这里除了要访问全部的聚簇索引记录，还要扫描全部的idx_key1二级索引记录，这不是费力不讨好么。在这个过程中没有减少需要扫描的记录数量，反而效率比全表扫描更差，所以如果我们想使用某个索引来执行查询，但是又无法通过搜索条件形成合适的扫描区间来减少需要扫描的记录数量时，那么我们是不考虑使用这个索引执行查询的。

并不是所有的搜索条件都可以成为边界条件，比方说下边这个查询：

  1. SELECT * FROM single_table WHERE key1 < 'a' AND key3 > 'z' AND common_field = 'abc';

那么：

使用联合索引执行查询时对应的扫描区间

联合索引的索引列包含多个列，B+树每一层页面以及每个页面中的记录采用的排序规则较为复杂，以single_table表的idx_key_part联合索引为例，它采用的排序规则如下所示：

我们画一下idx_key_part索引的示意图：

对于下边这个查询Q1来说：

  1. Q1：SELECT * FROM single_table WHERE key_part1 = 'a';

由于二级索引记录是先按照key_part1列的值进行排序的，所以所有符合key_part1 = 'a'条件的记录肯定是相邻的，我们可以定位到第一条符合key_part1 = 'a'条件的记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合key_part1 = 'a'条件为止（当然，对于获取到的每一条二级索引记录都要执行回表操作，我们这里就不展示回表操作了），如下图所示。

也就是说，如果我们使用idx_key_part索引执行查询Q1时，对应的扫描区间就是['a', 'a']，形成这个扫描区间的条件就是key_part1 = 'a'。

对于下边这个查询Q2来说：

  1. Q2：SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b';

由于二级索引记录是先按照key_part1列的值进行排序的；在key_part1列的值相等的情况下，再按照key_part2列进行排序。所以符合key_part1 = 'a' AND key_part2 = 'b'条件的二级索引记录肯定是相邻的，我们可以定位到第一条符合key_part1='a' AND key_part2='b'条件的记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合key_part1='a'条件或者key_part2='b'条件为止（当然，对于获取到的每一条二级索引记录都要执行回表操作，我们这里就不展示回表操作了），如下图所示。

也就是说，如果我们使用idx_key_part索引执行查询Q2时，可以形成扫描区间[('a', 'b'), ('a', 'b')]，形成这个扫描区间的条件就是key_part1 = 'a' AND key_part2 = 'b'。

对于下边这个查询Q3来说：

  1. Q3：SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c';

由于二级索引记录是先按照key_part1列的值进行排序的；在keypart1列的值相等的情况下，再按照key_part2列进行排序；在key_part1和key_part2列的值都相等的情况下，再按照key_part3列进行排序。所以符合key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'条件的二级索引记录肯定是相邻的，我们可以定位到第一条符合key_part1='a' AND key_part2='b' AND key_part3='c'条件的记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合key_part1='a'条件或者key_part2='b'条件或者key_part3='c'条件为止（当然，对于获取到的每一条二级索引记录都要执行回表操作），我们就不画示意图了。如果我们使用idx_key_part索引执行查询Q3时，可以形成扫描区间[('a', 'b', 'c'), ('a', 'b', 'c')]，形成这个扫描区间的条件就是key_part1 = 'a' AND key_part2 = 'b' AND key_part3 = 'c'。

对于下边这个查询Q4来说：

  1. Q4：SELECT * FROM single_table WHERE key_part1 < 'a';

由于二级索引记录是先按照key_part1列的值进行排序的，所以所有符合key_part1 < 'a'条件的记录肯定是相邻的，我们可以定位到第一条符合key_part1 < 'a'条件的记录（其实就是idx_key_part索引第一个叶子节点的第一条记录），然后沿着记录所在的单向链表向后扫描，直到某条记录不符合key_part1 < 'a'为止（当然，对于获取到的每一条二级索引记录都要执行回表操作，我们这里就不展示回表操作了），如下图所示。

也就是说，如果我们使用idx_key_part索引执行查询Q4时，可以形成扫描区间(-∞, 'a')，形成这个扫描区间的条件就是key_part1 < 'a'。

对于下边这个查询Q5来说：

  1. Q5：SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part2 > 'a' AND key_part2 < 'd';

由于二级索引记录是先按照key_part1列的值进行排序的；在key_part1列的值相等的情况下，再按照key_part2列进行排序。也就是说在符合key_part1 = 'a'条件的二级索引记录中，是按照key_part2列的值进行排序的，那么此时符合key_part1 = 'a' AND key_part2 > 'a' AND key_part2 < 'd'条件的二级索引记录肯定是相邻的。我们可以定位到第一条符合key_part1='a' AND key_part2 > 'a' AND key_part2 < 'c'条件的记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合key_part1='a'条件或者key_part2 > 'a'条件或者key_part2 < 'd'条件为止（当然，对于获取到的每一条二级索引记录都要执行回表操作，我们这里就不展示回表操作了），如下图所示。

也就是说，如果我们使用idx_key_part索引执行查询Q5时，可以形成扫描区间(('a', 'a'), ('a', 'd'))，形成这个扫描区间的条件就是key_part1 = 'a' AND key_part2 > 'a' AND key_part2 < 'd'。

对于下边这个查询Q6来说：

  1. Q6：SELECT * FROM single_table WHERE key_part2 = 'a';

由于二级索引记录不是直接按照key_part2列的值排序的，所以符合key_part2 = 'a'的二级索引记录可能并不相邻，也就意味着我们不能通过这个key_part2 = 'a'搜索条件来减少需要扫描的记录数量。在这种情况下，我们是不会使用idx_key_part索引执行查询的。

对于下边这个查询Q7来说：

  1. Q7：SELECT * FROM single_table WHERE key_part1 = 'a' AND key_part3 = 'c';

由于二级索引记录是先按照key_part1列的值进行排序的，所以符合key_part1 = 'a'条件的二级索引记录肯定是相邻的，但是对于符合key_part1 = 'a'条件的二级索引记录来说，并不是直接按照key_part3列进行排序的，也就是说我们不能根据搜索条件key_part3 = 'c'来进一步减少需要扫描的记录数量。那么如果我们使用idx_key_part索引执行查询的话，可以定位到第一条符合key_part1='a'条件的记录，然后沿着记录所在的单向链表向后扫描，直到某条记录不符合key_part1 = 'a'条件为止。所以在使用idx_key_part索引执行查询Q7的过程中，对应的扫描区间其实是['a', 'a']，形成该扫描区间的搜索条件是key_part1 = 'a'，与key_part3 = 'c'无关。

  1. 小贴士：
  3. 针对获取到的每一条二级索引记录，如果没有开启索引条件下推特性的话，则必须先进行回表操作，获取到完整的用户记录后再判断key_part3 = 'c'这个条件是否成立；如果开启了索引条件下推特性的话，可以立即判断该二级索引记录是否符合key_part3 = 'c'这个条件，如果符合则再进行回表操作，如果不符合则不进行回表操作，直接跳到下一条二级索引记录。索引条件下推特性是在MySQL 5.6中引入的，默认是开启的。

对于下边这个查询Q8来说：

  1. Q8：SELECT * FROM single_table WHERE key_part1 < 'b' AND key_part2 = 'a';

由于二级索引记录是先按照key_part1列的值进行排序的，所以符合key_part1 < 'b'条件的二级索引记录肯定是相邻的，但是对于符合key_part1 < 'b'条件的二级索引记录来说，并不是直接按照key_part2列进行排序的，也就是说我们不能根据搜索条件key_part2 = 'a'来进一步减少需要扫描的记录数量。那么如果我们使用idx_key_part索引执行查询的话，可以定位到第一条符合key_part1<'b'条件的记录（其实就是idx_key_part索引第一个叶子节点的第一条记录），然后沿着记录所在的单向链表向后扫描，直到某条记录不符合key_part1 < 'b'条件为止，如下图所示。

所以在使用idx_key_part索引执行查询Q8的过程中，对应的扫描区间其实是[-∞, 'b')，形成该扫描区间的搜索条件是key_part1 < 'b'，与key_part2 = 'a'无关。

对于下边这个查询Q9来说：

  1. Q9：SELECT * FROM single_table WHERE key_part1 <= 'b' AND key_part2 = 'a';

很显然Q8和Q9长得非常像，只不过在涉及key_part1的条件中，Q8中的条件是key_part1 < 'b'，Q9中的条件是key_part1 <= 'b'。很显然符合key_part1 <= 'b'条件的二级索引记录是相邻的，但是对于符合key_part1 <= 'b'条件的二级索引记录来说，并不是直接按照key_part2列进行排序的。但是，我这里说但是哈，对于符合key_part1 = 'b'的二级索引记录来说，是按照key_part2列的值进行排序的。那么我们在确定需要扫描的二级索引记录的范围时，当二级索引记录的key_part1列值为'b'时，我们也可以通过key_part2 = 'a'这个条件来减少需要扫描的二级索引记录范围，也就是说当我们扫描到第一条不符合 key_part1 = 'b' AND key_part2 = 'a'条件的记录时，就可以结束扫描，而不需要将所有key_part1列值为'b'的记录扫描完，示意图如下：

也就是说，如果我们使用idx_key_part索引执行查询Q9时，可以形成扫描区间((-∞, -∞), ('b', 'a'))，形成这个扫描区间的条件就是key_part1 <= 'b' AND key_part2 = 'a'。对比查询Q8，我们必须将所有符合key_part1 < 'b'的记录都扫描完，key_part2 = 'a'这个条件在查询Q8中并不能起到减少需要扫描的二级索引记录范围的作用。

可能将查询Q9转换为下边的这个形式后更容易理解使用idx_key_part索引执行它时对应的扫描区间以及形成扫描区间的条件：

  1. SELECT * FROM single_table WHERE (key_part1 < 'b' AND key_part2 = 'a') OR (key_part1 = 'b' AND key_part2 = 'a');

设计MySQL的大叔为何偏爱ref

标签（空格分隔）： 公众号文章

回忆一下查询成本

对于一个查询来说，有时候可以通过不同的索引或者全表扫描来执行它，MySQL优化器会通过事先生成的统计数据，或者少量访问B+树索引的方式来分析使用各个索引时都需要扫描多少条记录，然后计算使用不同索引的查询成本，最后选择成本最低的那个来执行查询。

  1. 小贴士：
  3. 我们之前称那种通过少量访问B+树索引来分析需要扫描的记录数量的方式称为index dive，不知道大家还有没有印象。

一个很简单的思想就是：使用某个索引执行查询时，需要扫描的记录越少，就越可能使用这个索引来执行查询。

创建场景

假如我们现在有一个表t，它的表结构如下所示：

  1. CREATE TABLE t (
  2.     id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.     key1 VARCHAR(100),
  4.     common_field VARCHAR(100),
  5.     INDEX idx_key1 (key1)
  6. ) ENGINE=InnoDB CHARSET=utf8;

这个表包含3个列：

现在该表中共有10000条记录：

  1. mysql> SELECT COUNT(*) FROM t;
  2. +----------+
  3. | COUNT(*) |
  4. +----------+
  5. |    10000 |
  6. +----------+
  7. 1 row in set (2.65 sec)

其中key1列为'a'的记录有2310条：

  1. mysql> SELECT COUNT(*) FROM t WHERE key1 = 'a';
  2. +----------+
  3. | COUNT(*) |
  4. +----------+
  5. |     2310 |
  6. +----------+
  7. 1 row in set (0.83 sec)

key1列在'a'到'i'之间的记录也有2310条：

  1. mysql> SELECT COUNT(*) FROM t WHERE key1 > 'a' AND key1 < 'i';
  2. +----------+
  3. | COUNT(*) |
  4. +----------+
  5. |     2310 |
  6. +----------+
  7. 1 row in set (1.31 sec)

现在我们有如下两个查询：

  1. 查询1：SELECT * FROM t WHERE key1 = 'a';
  3. 查询2：SELECT * FROM t WHERE key1 > 'a' AND key1 < 'i';

按理说上边两个查询需要扫描的记录数量是一样的，MySQL查询优化器对待它们的态度也应该是一样的，也就是要么都使用二级索引idx_key1执行它们，要么都使用全表扫描的方式来执行它们。不过现实是貌似查询优化器更喜欢查询1，而比较讨厌查询2。查询1的执行计划如下所示：

  1. # 查询1的执行计划
  2. mysql> EXPLAIN SELECT * FROM t WHERE key1 = 'a'\G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: t
  7.    partitions: NULL
  8.          type: ref
  9. possible_keys: idx_key1
  10.           key: idx_key1
  11.       key_len: 303
  12.           ref: const
  13.          rows: 2310
  14.      filtered: 100.00
  15.         Extra: NULL
  16. 1 row in set, 1 warning (0.04 sec)

查询2的执行计划如下所示：

  1. # 查询2的执行计划
  2. mysql> EXPLAIN SELECT * FROM t WHERE key1 > 'a' AND key1 < 'i'\G
  3. *************************** 1. row ***************************
  4.            id: 1
  5.   select_type: SIMPLE
  6.         table: t
  7.    partitions: NULL
  8.          type: ALL
  9. possible_keys: idx_key1
  10.           key: NULL
  11.       key_len: NULL
  12.           ref: NULL
  13.          rows: 9912
  14.      filtered: 23.31
  15.         Extra: Using where
  16. 1 row in set, 1 warning (0.03 sec)

很显然，查询优化器决定使用idx_key1二级索引执行查询1，而使用全表扫描来执行查询2。

为什么？凭什么？同样是扫描相同数量的记录，凭什么我range访问方法就要比你ref低一头？设计MySQL的大叔，你为何这么偏心...

解密偏心原因

世界上没有无缘无故的爱，也没有无缘无故的恨。这事儿还得从索引结构说起。比方说idx_key1二级索引结构长这样：

原谅我们把索引对应的B+树结构弄了一个极度精简版，我们忽略掉了页的结构，只保留了叶子节点的记录。虽然极度精简，但是我们还是保留了一个极其重要的特性：B+树叶子节点中的记录是按照索引列的值从小到大排序的。对于二级索引idx_key1来说：

也就是说，对于所有key1值为'a'的二级索引记录来说，它们都是按照id列的值进行排序的。对于查询1：

  1. 查询1: SELECT * FROM t WHERE key1 = 'a';

由于查询列表是* ，也就是说我们需要通过读取到的二级索引记录的id值执行回表操作，到聚簇索引中找到完整的用户记录（为了去获取common_field列的值）后才可以将记录发送到客户端。对于所有key1列值等于'a'的二级索引记录，由于它们是按照id列的值排序的，所以：

综上所属，执行语句1时，回表操作带来的性能开销较小。

而对于查询2来说：

  1. 查询2: SELECT * FROM t WHERE key1 > 'a' AND key1 < 'i';

由于需要扫描的二级索引记录对应的id值是无序的，所以执行回表操作时，需要访问的聚簇索引记录所在的数据页很大可能就是无序的，这样会造成很多随机I/O。所以如果使用idx_key1来执行查询1和查询2，执行查询1的成本很显然会比查询2低，这也是设计MySQL的大叔更钟情于ref而不是range的原因。

MySQL的内部实现

MySQL优化器在计算回表的成本时，在使用二级索引执行查询并且需要回表的情境下，对于ref和range是很明显的区别对待的：

  1. 小贴士：
  3. 在成本分析的代码中，range和index、all是被分到一类里的，ref是亲儿子，单独分析了一波。不过我们也可以看到，设计MySQL的大叔在计算range访问方法的代价时，直接认为每次回表都需要进行一次页面I/O，这是十分粗暴的，何况我们的实际聚簇索引总共才97个页面，它却将回表成本计算为2310，这也是很不精确的。当然，由于目前的算法无法预测哪些页面在内存中，哪些不在，所以也就将就将就用吧～

MySQL中NULL值引起的小锅

标签： 公众号文章

这一系列文章主要说明了一个道理：MySQL查询优化器决策是否使用某个索引执行查询时的依据是使用该索引的成本是否足够低，而成本很大程度上取决于需要扫描的二级索引记录数量占表中所有记录数量的比例。

innodb_stats_method的作用

我们知道索引列不重复的值的数量这个统计数据对于MySQL查询优化器十分重要，因为通过它可以计算出在索引列中平均一个值重复多少行，它的应用场景主要有两个：

在统计索引列不重复的值的数量时，有一个比较烦的问题就是索引列中出现NULL值怎么办，比方说某个索引列的内容是这样：

  1. +------+
  2. | col  |
  3. +------+
  4. |    1 |
  5. |    2 |
  6. | NULL |
  7. | NULL |
  8. +------+

此时计算这个col列中不重复的值的数量就有下边的分歧：

设计MySQL的大叔蛮贴心的，他们提供了一个名为innodb_stats_method的系统变量，相当于在计算某个索引列不重复值的数量时如何对待NULL值这个锅甩给了用户，这个系统变量有三个候选值：

反正这个锅是甩给用户了，当你选定了innodb_stats_method值之后，优化器即使选择了不是最优的执行计划，那也跟设计MySQL的大叔们没关系了哈～ 当然对于用户的我们来说，最好不在索引列中存放NULL值才是正解。

两种不同的统计数据存储方式

InnoDB提供了两种存储统计数据的方式：

设计MySQL的大叔们给我们提供了系统变量innodb_stats_persistent来控制到底采用哪种方式去存储统计数据。在MySQL 5.6.6之前，innodb_stats_persistent的值默认是OFF，也就是说InnoDB的统计数据默认是存储到内存的，之后的版本中innodb_stats_persistent的值默认是ON，也就是统计数据默认被存储到磁盘中。

不过InnoDB默认是以表为单位来收集和存储统计数据的，也就是说我们可以把某些表的统计数据（以及该表的索引统计数据）存储在磁盘上，把另一些表的统计数据存储在内存中。怎么做到的呢？我们可以在创建和修改表的时候通过指定STATS_PERSISTENT属性来指明该表的统计数据存储方式：

  1. CREATE TABLE 表名 (...) Engine=InnoDB, STATS_PERSISTENT = (1|0);
  3. ALTER TABLE 表名 Engine=InnoDB, STATS_PERSISTENT = (1|0);

当STATS_PERSISTENT=1时，表明我们想把该表的统计数据永久的存储到磁盘上，当STATS_PERSISTENT=0时，表明我们想把该表的统计数据临时的存储到内存中。如果我们在创建表时未指定STATS_PERSISTENT属性，那默认采用系统变量innodb_stats_persistent的值作为该属性的值。

问题

有同学在小册群中反应在使用基于磁盘的统计数据时，将innodb_stats_method系统变量设置成不同的值，但是发现对应的统计数据却并未发生预想的变化（可以通过SHOW INDEX FROM tbl_name或者查看mysql数据库下的innodb_index_stats表），这到底是因为啥呢？

原因

我一开始也对这个现象有点儿疑惑🤔，于是不得不再次打开看吐了的源码来看。

  1. 比较两条记录是否相同的函数是cmp_rec_rec_with_match，如下图所示：

    其中的nulls_unequal参数是用来区别是否将两个null值认为是相等的。

  2. 在计算基于磁盘的统计数据时，是这样调用该函数的：

    可以看到nulls_unequal参数是硬编码为FALSE。

  3. 在计算基于内存的统计数据时，是这样调用该函数的：

    可以看到这种调用的方式就是正常的。

从实践来看，在计算基于内存的统计数据时，改变系统变量innodb_stats_method的值是起作用的，但是在计算基于磁盘的统计数据时，改变该系统变量的值是无效的。我也并不知道设计InnoDB的大叔为什么这么写，翻了翻代码也没看见这么写有什么特别的注释，之后还特意去看了MySQL文档中关于统计数据收集的相关章节，也没发现有特别声明这两者的区别。可能是一个bug?或者有啥深层次的含义？有知道的同学可以留言哈～

MySQL使用索引执行IN子句

标签： 公众号文章

对于开发小伙伴来说，对MySQL中的包含IN子句的语句肯定熟悉的不能再熟悉了，几乎天天用，时时用。可是很多小伙伴不知道包含IN子句的语句是怎样执行的，在一些查询优化的场景中就开始找不着北了，本篇文章就来唠叨一下MySQL中的IN语句是怎样执行的（以MySQL 5.7的InnoDB存储引擎为例）。

准备工作

为了故事的顺利发展，我们先创建一个表：

  1. CREATE TABLE t (
  2.     id INT NOT NULL AUTO_INCREMENT,
  3.     key1 VARCHAR(100),
  4.     common_field VARCHAR(100),
  5.     PRIMARY KEY (id),
  6.     KEY idx_key1 (key1)
  7. ) Engine=InnoDB CHARSET=utf8;

可以看到表t中包含两个索引：

这个表里边现在有10000条数据：

  1. mysql> SELECT COUNT(*) FROM t;
  2. +----------+
  3. | COUNT(*) |
  4. +----------+
  5. |    10000 |
  6. +----------+
  7. 1 row in set (0.00 sec)

从B+树中定位记录

我们现在想执行下边这个语句：

  1. SELECT * FROM t WHERE 
  2.     key1 >= 'b' AND key1 <= 'c';

假设优化器选择使用二级索引来执行查询，那么查询语句的执行示意图就如下图所示：

  1. 小贴士：原谅我把索引对应的复杂的B+树结构搞了一个极度精简版，为了突出重点，我们忽略掉了页的结构，直接把所有的叶子节点的记录都放在一起展示。我们想突出的重点就是：B+树叶子节点中的记录是按照索引列值大小排序的，对于的聚簇索引来说，它对应的B+树叶子节点中的记录就是按照id列排序的，对于idx_key1二级索引来说，它对应的B+树叶子节点中的记录就是按照key1列排序的。

我们想查询key1列的值在['b', 'c']这个区间中的记录，那么就需要：

上述过程就是通过B+树查找一个键值在某一个范围区间的记录的过程。

包含IN子句的执行过程

如果我们想执行下边这个语句：

  1. SELECT * FROM t WHERE 
  2.     key1 IN ('b', 'c');

如果优化器选择使用二级索引执行上述语句，那它是如何执行的呢？

优化器会将IN子句中的条件看成是2个范围区间（虽然这两个区间中都仅仅包含一个值）：

那么在语句执行过程中就需要通过B+树去定位两次记录所在的位置：

所以如果你写的IN语句中的参数越多，意味着需要通过B+树定位记录的次数就越多。

IN子句中参数值重复的情况

比方说下边这条语句：

  1. SELECT * FROM t WHERE 
  2.     key1 IN ('b', 'b', 'b', 'b', 'b', 'b', 'b', 'b', 'b');

虽然IN子句中包含好多个参数，但MySQL在语法解析的时候只会为其生成一个范围区间，那就是：['b', 'b']。

IN子句的参数顺序问题

比方说下边这条语句：

  1. SELECT * FROM t WHERE key1 IN ('c', 'b');

IN ('c', 'b')和IN ('b', 'c')有啥差别么？也就是存储引擎在对待IN ('c', 'b')子句时，会先去找key1 = 'c'的记录，再去找key1 = 'b'的记录么？如果是这样的话，下边两条语句岂不是可能发生死锁：

  1. 事务T1中的语句一：
  2. SELECT * FROM t WHERE 
  3.     key1 IN ('b', 'c') FOR UPDATE;
  5. 事务T2中的语句二：
  6. SELECT * FROM t WHERE 
  7.     key1 IN ('c', 'b') FOR UPDATE;

放心，在生成范围区间的时候，自然是将范围区间排了序，也就是即使条件是IN ('c', 'b')，那优化器也会先让存储引擎去找键值在['b', 'b']这个范围区间中的记录，然后再去找键值在['c', 'c']这个范围区间中的记录。

系统变量eq_range_index_dive_limit对IN子句的影响

大家一定要记着：MySQL优化器决定使用某个索引执行查询的仅仅是因为：使用该索引时的成本足够低。也就是说即使我们有下边的语句：

  1. SELECT * FROM t WHERE 
  2.     key1 IN ('b', 'c');

MySQL优化器需要去分析一下如果使用二级索引idx_key1执行查询的话，键值在['b', 'b']和['c', 'c']这两个范围区间的记录共有多少条，然后通过一定方式计算出成本，与全表扫描的成本相对比，选取成本更低的那种方式执行查询。

在计算查询成本的这一步骤中大家需要注意，对于包含IN子句条件的查询来说，需要依次分析一下每一个范围区间中的记录数量是多少。MySQL优化器针对IN子句对应的范围区间的多少而指定了不同的策略：

那什么时候采用index dive的统计方式，什么时候采用index statistic的统计方式呢？这就取决于系统变量eq_range_index_dive_limit的值了，我们看一下在我的机器上该系统变量的值：

  1. mysql> SHOW VARIABLES LIKE 'eq_range_index_dive_limit';
  2. +---------------------------+-------+
  3. | Variable_name             | Value |
  4. +---------------------------+-------+
  5. | eq_range_index_dive_limit | 200   |
  6. +---------------------------+-------+
  7. 1 row in set (0.20 sec)

可以看到它的默认值是200，这也就意味着当范围区间个数小于200时，将采用index dive的统计方式，否则将采用index statistic的统计方式。

不过这里需要大家特别注意，在MySQL 5.7.3以及之前的版本中，eq_range_index_dive_limit的默认值为10。所以如果大家采用的是5.7.3以及之前的版本的话，很容易采用索引统计数据而不是index dive的方式来计算查询成本。当你的查询中使用到了IN查询，但是却实际没有用到索引，就应该考虑一下是不是由于 eq_range_index_dive_limit 值太小导致的。

MySQL的COUNT语句是怎么执行的

标签： 公众号文章

众多开发小伙伴在写业务逻辑的时候都会有一个统计行数的需求，此时COUNT函数便成为了首选。但是大家发现，随着数据量的增长，COUNT执行的越来越慢，本文从源码的角度来帮助小伙伴们分析一下MySQL中的COUNT函数是如何执行的。

需要声明一点，本文所使用的MySQL源码版本是5.7.22，并且只针对InnoDB存储引擎。在深入介绍之前需要大家具有一些前置知识，才可以顺利讨论。

前置知识1——InnoDB的B+树索引

为了故事的顺利发展，我们引入一个表：

  1. CREATE TABLE t (
  2.     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  3.     key1 INT,
  4.     common_field VARCHAR(100),
  5.     PRIMARY KEY (id),
  6.     KEY idx_key1 (key1)
  7. ) Engine=InnoDB CHARSET=utf8;

这个表就包含2个索引（也就是2棵B+树）：

我们向表中插入一些记录：

  1. INSERT INTO t VALUES
  2.     (1, 30, 'b'),
  3.     (2, 80, 'b'),
  4.     (3, 23, 'b'),
  5.     (4, NULL, 'b'),
  6.     (5, 11, 'b'),
  7.     (6, 53, 'b'),
  8.     (7, 63, 'b'),
  9.     (8, NULL, 'b'),
  10.     (9, 99, 'b'),
  11.     (10, 12, 'b'),
  12.     (11, 66, 'b'),
  13.     (12, NULL, 'b'),
  14.     (13, 66, 'b'),
  15.     (14, 30, 'b'),
  16.     (15, 11, 'b'),
  17.     (16, 90, 'b');

所以现在t表的聚簇索引示意图就是这样：

t表的二级索引示意图就是这样：

小贴士：


原谅我们画了一个极度简化版的B+树：我们省略了B+树中的页面节点，省略了每层页面节点之间的双向链表，省略了页面中记录的单向链表，省略了页面中的页目录结构，还有省略了好多东西。但是我们保留B+树最为核心的一个特点：记录是按照键值大小进行排序的。即对于聚簇索引来说，记录是按照id列进行排序的；对于二级索引idx_key1来说，记录是按照key1列进行排序的，在key1列相同时再按照id列进行排序。

从上边聚簇索引和二级索引的结构中大家可以发现：每一条聚簇索引记录都可以在二级索引中找到唯一的一条二级索引记录与其相对应。

前置知识2——server层和存储引擎的交互

以下边这个查询为例：

  1. SELECT * FROM t WHERE key1 > 70 AND common_field != 'a';

假设优化器认为通过扫描二级索引idx_key1中key1值在(70, +∞)这个区间中的二级索引记录的成本更小，那么查询将以下述方式执行：

小贴士：

此处将记录发送给客户端其实是发送到本地的网络缓冲区，缓冲区大小由net_buffer_length控制，默认是16KB大小。等缓冲区满了才真正发送网络包到客户端。

可见，一般情况下server层和存储引擎层是以记录为单位进行交互的。

我们看一下源码中读取一条记录的函数调用栈：

其中的handler::ha_index_next便是server层向存储引擎要下一条记录的接口。

其中的row_search_mvcc是读取一条记录最重要的函数，这个函数长的吓人，有一千多行：

每读取一条记录，都要做非常多的工作，诸如进行多版本的可见性判断，要不要对记录进行加锁的判断，要是加锁的话加什么锁的选择，完成记录从InnoDB的存储格式到server层存储格式的转换等等等等十分繁杂的工作。

小贴士：

不知道你们公司有没有写这么长函数的同学，如果有的话你想不想打他。

前置知识3——COUNT是个啥

COUNT是一个汇总函数（聚集函数），它接收1个表达式作为参数：

  1. COUNT(expr)

COUNT函数用于统计在符合搜索条件的记录中，指定的表达式expr不为NULL的行数有多少。这里需要特别注意的是，expr不仅仅可以是列名，其他任意表达式都是可以的。

比方说：

  1. SELECT COUNT(key1) FROM t;

这个语句是用于统计在single_table表的所有记录中，key1列不为NULL的行数是多少。

再看这个：

  1. SELECT COUNT('abc') FROM t;

这个语句是用于统计在single_table表的所有记录中，'abc'这个表达式不为NULL的行数是多少。很显然，'abc'这个表达式永远不是NULL，所以上述语句其实就是统计single_table表里有多少条记录。

再看这个：

  1. SELECT COUNT(*) FROM t;

这个语句就是直接统计single_table表有多少条记录。

总结+注意：COUNT函数的参数可以是任意表达式，该函数用于统计在符合搜索条件的记录中，指定的表达式不为NULL的行数有多少。

MySQL中COUNT是怎样执行的

做了那么多铺垫，终于到了MySQL中COUNT是怎样执行的了。

以下边这个语句为例：

  1. SELECT COUNT(*) FROM t;

这个语句是要去查询表t中共包含多少条记录。由于聚簇索引和二级索引中的记录是一一对应的，而二级索引记录中包含的列是少于聚簇索引记录的，所以同样数量的二级索引记录可以比聚簇索引记录占用更少的存储空间。如果我们使用二级索引执行上述查询，即数一下idx_key1中共有多少条二级索引记录，是比直接数聚簇索引中共有多少聚簇索引记录可以节省很多I/O成本。所以优化器会决定使用idx_key1执行上述查询：

  1. mysql> EXPLAIN SELECT COUNT(*) FROM t;
  2. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
  3. | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
  4. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
  5. |  1 | SIMPLE      | t     | NULL       | index | NULL          | idx_key1 | 5       | NULL |   16 |   100.00 | Using index |
  6. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
  7. 1 row in set, 1 warning (0.02 sec)

在执行上述查询时，server层会维护一个名叫count的变量，然后：

我们看一下源码里给count变量加1的代码是怎么写的：

其大意就是判断一下COUNT里的表达式是不是NULL，如果不是NULL的话就给count变量加1。

我们再来看一下arg_is_null的实现：

其中最重要的是我们标蓝的那一行，item[i]表示的就是COUNT函数中的参数，我们调试一下对于COUNT(*)来说，表达式*的值是什么：

可以看到，*表达式的类型其实是Item_int，这表示MySQL其实会把*当作一个整数处理，它的值是0（见图中箭头）。也就是说我们在判断表达式*是不是为NULL，也就是在判断整数0是不是为NULL，很显然不为NULL。

那COUNT(1)，COUNT(id)，COUNT(非主键列)呢？

那在执行COUNT(1)呢？比方说下边这个语句：

  1. SELECT COUNT(1) FROM t;

我们看一下：

可以看到，常数1对应的类型其实是PTI_num_literal_num，它其实是Item_int的一个包装类型，本质上还是代表一个整数，它的值是1（见图中箭头）。也就是说我们其实是在判断表达式1是不是为NULL，很显然不为NULL。

再看一下COUNT(id)：

  1. SELECT COUNT(id) FROM t;

我们看一下：

可以看到，id对应的类型是Item_field，代表一个字段。

对于COUNT(*)、COUNT(1)或者任意的COUNT(常数)来说，读取哪个索引的记录其实并不重要，因为server层只关心存储引擎是否读到了记录，而并不需要从记录中提取指定的字段来判断是否为NULL。所以优化器会使用占用存储空间最小的那个索引来执行查询。

对于COUNT(id)来说，由于id是主键，不论是聚簇索引记录，还是任意一个二级索引记录中都会包含主键字段，所以其实读取任意一个索引中的记录都可以获取到id字段，此时优化器也会选择占用存储空间最小的那个索引来执行查询。

而对于COUNT(非主键列)来说，我们指定的列可能并不会包含在每一个索引中。这样优化器只能选择包含我们指定的列的索引去执行查询，这就可能导致优化器选择的索引并不是最小的那个。

总结一下

对于COUNT(*)、COUNT(常数)、COUNT(主键)形式的COUNT函数来说，优化器可以选择最小的索引执行查询，从而提升效率，它们的执行过程是一样的，只不过在判断表达式是否为NULL时选择不同的判断方式，这个判断为NULL的过程的代价可以忽略不计，所以我们可以认为COUNT(*)、COUNT(常数)、COUNT(主键)所需要的代价是相同的。

而对于COUNT(非主键列)来说，server层必须要从InnoDB中读到包含非主键列的记录，所以优化器并不能随心所欲的选择最小的索引去执行。

我想改进一下？

我们知道，InnoDB的记录都是存储在数据页中的（页面大小默认为16KB），而每个数据页的Page Header部分都有一个统计当前页面中记录数量的属性PAGE_N_RECS。那有的同学说了：在执行COUNT函数的时候直接去把各个页面的这个PAGE_N_RECS属性加起来不就好了么？

答案是：行不通的！对于普通的SELECT语句来说，每次查询都要从记录的版本链上找到可见的版本才算是读到了记录；对于加了FOR UPDATE或LOCK IN SHARE MODE后缀的SELECT语句来说，每次查询都要给记录添加合适的锁。所以这个读取每一条记录的过程（就是上边给出的row_search_mvcc函数）在InnoDB的目前实现中是无法跳过的，InnoDB还是得老老实实的读一条记录，返给server层一条记录。

那如果我的业务中有COUNT需求，但是由于数据量太大导致即使优化器即使通过扫描二级索引记录的方式也还是太慢怎么办？既然业务上有需求，当然还是业务第一喽，我们可以在另一个地方存储一份待统计数据的行数，每次增删改记录都维护一下。

这样的解决方案显著增加了开发小伙伴的工作量，部分开发小伙伴肯定不太乐意，那就去怼你的产品经理吧：这么大数据量要TM什么精确值，你来告诉我这么大数据量要TM什么的TM的精确值？

MySQL的LIMIT这么差劲的吗

标签： 公众号文章

最近有多个小伙伴在答疑群里问了小孩子关于LIMIT的一个问题，下边我来大致描述一下这个问题。

问题

为了故事的顺利发展，我们得先有个表：

  1. CREATE TABLE t (
  2.     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  3.     key1 VARCHAR(100),
  4.     common_field VARCHAR(100),
  5.     PRIMARY KEY (id),
  6.     KEY idx_key1 (key1)
  7. ) Engine=InnoDB CHARSET=utf8;

表t包含3个列，id列是主键，key1列是二级索引列。表中包含1万条记录。

当我们执行下边这个语句的时候，是使用二级索引idx_key1的：

  1. mysql>  EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 1;
  2. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
  3. | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra |
  4. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
  5. |  1 | SIMPLE      | t     | NULL       | index | NULL          | idx_key1 | 303     | NULL |    1 |   100.00 | NULL  |
  6. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------+
  7. 1 row in set, 1 warning (0.00 sec)

这个很好理解，因为在二级索引idx_key1中，key1列是有序的。而查询是要取按照key1列排序的第1条记录，那MySQL只需要从idx_key1中获取到第一条二级索引记录，然后直接回表取得完整的记录即可。

但是如果我们把上边语句的LIMIT 1换成LIMIT 5000, 1，则却需要进行全表扫描，并进行filesort，执行计划如下：

  1. mysql>  EXPLAIN SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;
  2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
  3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
  4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
  5. |  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9966 |   100.00 | Using filesort |
  6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
  7. 1 row in set, 1 warning (0.00 sec)

有的同学就很不理解了：LIMIT 5000, 1也可以使用二级索引idx_key1呀，我们可以先扫描到第5001条二级索引记录，对第5001条二级索引记录进行回表操作不就好了么，这样的代价肯定比全表扫描+filesort强呀。

很遗憾的告诉各位，由于MySQL实现上的缺陷，不会出现上述的理想情况，它只会笨笨的去执行全表扫描+filesort，下边我们唠叨一下到底是咋回事儿。

server层和存储引擎层

大家都知道，MySQL内部其实是分为server层和存储引擎层的：

MySQL中一条SQL语句的执行是通过server层和存储引擎层的多次交互才能得到最终结果的。比方说下边这个查询：

  1. SELECT * FROM t WHERE key1 > 'a' AND key1 < 'b' AND common_field != 'a';

server层会分析到上述语句可以使用下边两种方案执行：

server层会分析上述两个方案哪个成本更低，然后选取成本更低的那个方案作为执行计划。然后就调用存储引擎提供的接口来真正的执行查询了。

这里假设采用方案二，也就是使用二级索引idx_key1执行上述查询。那么server层和存储引擎层的对话可以如下所示：

server层：“hey，麻烦去查查idx_key1二级索引的('a', 'b')区间的第一条记录，然后把回表后把完整的记录返给我哈”

InnoDB：“收到，这就去查”，然后InnoDB就通过idx_key1二级索引对应的B+树，快速定位到扫描区间('a', 'b')的第一条二级索引记录，然后进行回表，得到完整的聚簇索引记录返回给server层。

server层收到完整的聚簇索引记录后，继续判断common_field!='a'条件是否成立，如果不成立则舍弃该记录，否则将该记录发送到客户端。然后对存储引擎说：“请把下一条记录给我哈”

小贴士：


此处将记录发送给客户端其实是发送到本地的网络缓冲区，缓冲区大小由net_buffer_length控制，默认是16KB大小。等缓冲区满了才真正发送网络包到客户端。

InnoDB：“收到，这就去查”。InnoDB根据记录的next_record属性找到idx_key1的('a', 'b')区间的下一条二级索引记录，然后进行回表操作，将得到的完整的聚簇索引记录返回给server层。

小贴士：


不论是聚簇索引记录还是二级索引记录，都包含一个称作next_record的属性，各个记录根据next_record连成了一个链表，并且链表中的记录是按照键值排序的（对于聚簇索引来说，键值指的是主键的值，对于二级索引记录来说，键值指的是二级索引列的值）。

server层收到完整的聚簇索引记录后，继续判断common_field!='a'条件是否成立，如果不成立则舍弃该记录，否则将该记录发送到客户端。然后对存储引擎说：“请把下一条记录给我哈”

... 然后就不停的重复上述过程。

直到：

也就是直到InnoDB发现根据二级索引记录的next_record获取到的下一条二级索引记录不在('a', 'b')区间中，就跟server层说：“好了，('a', 'b')区间没有下一条记录了”

server层收到InnoDB说的没有下一条记录的消息，就结束查询。

现在大家就知道了server层和存储引擎层的基本交互过程了。

那LIMIT是什么鬼？

说出来大家可能有点儿惊讶，MySQL是在server层准备向客户端发送记录的时候才会去处理LIMIT子句中的内容。拿下边这个语句举例子：

  1. SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;

如果使用idx_key1执行上述查询，那么MySQL会这样处理：

从上述过程中我们可以看到，由于MySQL中是在实际向客户端发送记录前才会去判断LIMIT子句是否符合要求，所以如果使用二级索引执行上述查询的话，意味着要进行5001次回表操作。server层在进行执行计划分析的时候会觉得执行这么多次回表的成本太大了，还不如直接全表扫描+filesort快呢，所以就选择了后者执行查询。

怎么办？

由于MySQL实现LIMIT子句的局限性，在处理诸如LIMIT 5000, 1这样的语句时就无法通过使用二级索引来加快查询速度了么？其实也不是，只要把上述语句改写成：

  1. SELECT * FROM t, (SELECT id FROM t ORDER BY key1 LIMIT 5000, 1) AS d
  2.     WHERE t.id = d.id;

这样，SELECT id FROM t ORDER BY key1 LIMIT 5000, 1作为一个子查询单独存在，由于该子查询的查询列表只有一个id列，MySQL可以通过仅扫描二级索引idx_key1执行该子查询，然后再根据子查询中获得到的主键值去表t中进行查找。

这样就省去了前5000条记录的回表操作，从而大大提升了查询效率！

吐个槽

设计MySQL的大叔啥时候能改改LIMIT子句的这种超笨的实现呢？还得用户手动想欺骗优化器的方案才能提升查询效率~

MySQL：为什么查询列表中多了它，GROUP BY语句就会报错呢？

标签： 公众号文章

事前准备

为了故事的顺利发展，我们先得建一个表：

  1. CREATE TABLE student_score (
  2.   number INT(11) NOT NULL,
  3.   name VARCHAR(30) NOT NULL,
  4.   subject VARCHAR(30) NOT NULL,
  5.   score TINYINT(4) DEFAULT NULL,
  6.   PRIMARY KEY (number,subject)
  7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这个student_score表是用来存储学生成绩的，我们为这个条填充一些数据，填充后的效果就像这样：

  1. mysql> SELECT * FROM student_score;
  2. +----------+-----------+-----------------------------+-------+
  3. | number   | name      | subject                     | score |
  4. +----------+-----------+-----------------------------+-------+
  5. | 20180101 | 杜子腾    | 母猪的产后护理              |    78 |
  6. | 20180101 | 杜子腾    | 论萨达姆的战争准备          |    88 |
  7. | 20180102 | 杜琦燕    | 母猪的产后护理              |   100 |
  8. | 20180102 | 杜琦燕    | 论萨达姆的战争准备          |    98 |
  9. | 20180103 | 范统      | 母猪的产后护理              |    59 |
  10. | 20180103 | 范统      | 论萨达姆的战争准备          |    61 |
  11. | 20180104 | 史珍香    | 母猪的产后护理              |    55 |
  12. | 20180104 | 史珍香    | 论萨达姆的战争准备          |    46 |
  13. +----------+-----------+-----------------------------+-------+
  14. 8 rows in set (0.00 sec)

GROUP BY是在干什么？

我们知道MySQL提供了一系列的聚集函数，诸如：

比方说我们想查看一下student_score表中所有人成绩的平均数就可以这么写：

  1. mysql> SELECT AVG(score) FROM student_score;
  2. +------------+
  3. | AVG(score) |
  4. +------------+
  5. |    73.1250 |
  6. +------------+
  7. 1 row in set (0.00 sec)

如果我们只想查看《母猪的产后护理》这个科目的平均成绩，那加个WHERE子句就好了：

  1. mysql> SELECT AVG(score) FROM student_score WHERE subject = '母猪的产后护理';
  2. +------------+
  3. | AVG(score) |
  4. +------------+
  5. |    73.0000 |
  6. +------------+
  7. 1 row in set (0.00 sec)

同理，我们也可以单独查看《论萨达姆的战争准备》这门课程的平均成绩：

  1. mysql> SELECT AVG(score) FROM student_score WHERE subject = '论萨达姆的战争准备';
  2. +------------+
  3. | AVG(score) |
  4. +------------+
  5. |    73.2500 |
  6. +------------+
  7. 1 row in set (0.00 sec)

这时候问题来了，如果这个student_score表中存储了20门科目的成绩信息，那我们怎么单独的得到这20门课程的平均成绩呢？单独写20个查询语句？那要是有100门课呢？

很显然，不能傻兮兮的写一百个语句，设计MySQL的大叔给我们提供了分组的概念。我们可以按照某个列将表中的数据进行分组，比方说我们现在按照subject列对表中数据进行分组，那么所有的记录就会被分成2组，如图所示：

让MySQL产生这样子的分组的语句就是GROUP BY子句，我们只要在GROUP BY后边把需要分组的列写上就好，然后在查询列表处就可以针对每一个分组来写相应的聚集函数去统计该分组，就像这样：

  1. mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject;
  2. +-----------------------------+------------+
  3. | subject                     | AVG(score) |
  4. +-----------------------------+------------+
  5. | 母猪的产后护理              |    73.0000 |
  6. | 论萨达姆的战争准备          |    73.2500 |
  7. +-----------------------------+------------+
  8. 2 rows in set (0.00 sec)

报错

可以从上边带有GROUP BY子句的查询语句中看出来，我们只在查询列表处放了分组列subject以及对该分组中的记录调用的聚集函数AVG，那如果我们把不是分组列的字段也放到查询列表中会出现啥情况：

  1. mysql> SELECT subject, name, AVG(score) FROM student_score GROUP BY subject;
  3. ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dahaizi.student_score.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
  5. mysql>

可以看到报错了，为啥会报错呢？回想一下我们使用GROUP BY子句的初衷，我们只是想把记录分为若干组，然后再对各个组分别调用聚集函数去做一些统计工作。本例中的查询列表处放置了既非分组列、又非聚集函数的name列，那我们想表达啥意思呢？从各个分组中的记录中取一个记录的name列？该取哪条记录为好呢？比方说对于'母猪的产后护理'这个分组中的记录来说，name列的值应该取杜子腾，还是杜琦燕，还是范统，还是史珍香呢？这个我们也不知道，所以把非分组列放到查询列表中会引起争议，导致结果不确定，所以设计MySQL的大叔才会为上述语句报错。

不过有的同学会说，假如分组后的某个分组的某个非分组列的值都一样，那我把该非分组列加入到查询列表中也没啥问题呀。比方说按照subject列进行分组后，假如在'母猪的产后护理'的分组中各条记录的name列的值都相同，在'论萨达姆的战争准备'的分组中各条记录的name列的值也都相同，那么我们把name列放在查询列表中也没啥问题。可能设计MySQL的大叔觉得这种说法也有点儿道理，他们竟然同意在一些情况下把非分组列也放到查询列表中，这就设计到一个称之为sql_mode的系统变量，我们先看一下在我的电脑上这个系统变量的值：

  1. mysql> SHOW VARIABLES LIKE 'sql_mode';
  2. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
  3. | Variable_name | Value                                                                                                                                     |
  4. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
  5. | sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
  6. +---------------+-------------------------------------------------------------------------------------------------------------------------------------------+
  7. 1 row in set (0.02 sec)

哇唔，好长的一段。不过大家不必在意，我们只关心其中一个称之为ONLY_FULL_GROUP_BY的家伙。只要sql_mode的值里边有这个东东，MySQL服务器就“比较正常”（也就是不允许非分组列放到查询列表中），但是如果我们把这个东东从sql_mode系统变量中移除（移除这个东东只要重新设置一下这个系统变量，把这个东东从值里边去除掉就好，我们现在不必要关心值里边儿后边那一坨东西是干嘛的，照着抄下来就好）：

  1. mysql> set sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
  3. Query OK, 0 rows affected (0.00 sec)

然后再执行上边那个曾经报错的语句：

  1. mysql> SELECT subject, name, AVG(score) FROM student_score GROUP BY subject;
  2. +-----------------------------+-----------+------------+
  3. | subject                     | name      | AVG(score) |
  4. +-----------------------------+-----------+------------+
  5. | 母猪的产后护理              | 杜子腾    |    73.0000 |
  6. | 论萨达姆的战争准备          | 杜子腾    |    73.2500 |
  7. +-----------------------------+-----------+------------+
  8. 2 rows in set (0.00 sec)

看，这回就不会报错了。但这是个好事儿么？个人觉得不是，因为MySQL服务器也不能保证结果集中的name列的值到底是分组中的哪条记录的。大家在日常工作中，也希望尽量不要用这个投机取巧的功能，没啥乱用，而且容易产生错误。

  1. 小贴士：
  3. 不同MySQL版本中sql_mode的值可能默认包含ONLY_FULL_GROUP_BY这个家伙，也可能不包含ONLY_FULL_GROUP_BY这个家伙，也就是说不同MySQL版本中可能默认不支持查询列表中包含非分组列，也可能默认支持查询列表中包含非分组列。

MySQL冷知识：t1.id=t2.id=t3.id看着咋这么怪呢？

标签： 公众号文章

为了故事的顺利发展，我们先创建几个表：

  1. CREATE TABLE t1 (id INT);
  2. CREATE TABLE t2 (id INT);
  3. CREATE TABLE t3 (id INT);

然后往这些表里插入一些数据（具体的插入语句就不写了），达到的效果就是这样：

  1. mysql> SELECT * FROM t1;
  2. +----+
  3. | id |
  4. +----+
  5. |  1 |
  6. |  2 |
  7. |  3 |
  8. +----+
  9. 3 rows in set (0.00 sec)
  11. mysql> SELECT * FROM t2;
  12. +----+
  13. | id |
  14. +----+
  15. |  1 |
  16. |  2 |
  17. |  3 |
  18. +----+
  19. 3 rows in set (0.00 sec)
  21. mysql> SELECT * FROM t3;
  22. +----+
  23. | id |
  24. +----+
  25. |  1 |
  26. |  2 |
  27. |  3 |
  28. +----+
  29. 3 rows in set (0.00 sec)

如果我们想对这三个表执行连接操作，把三个表中id列相同的记录都拿出来，有的同学可能会这样写：

  1. SELECT t1.id AS t1_id, t2.id AS t2_id, t3.id AS t3_id 
  2.     FROM t1, t2, t3 
  3.     WHERE t1.id = t2.id = t3.id;

这样写会得到啥效果呢？我们看一下：

  1. mysql> SELECT t1.id AS t1_id, t2.id AS t2_id, t3.id AS t3_id
  2.     ->     FROM t1, t2, t3
  3.     ->     WHERE t1.id = t2.id = t3.id;
  4. +-------+-------+-------+
  5. | t1_id | t2_id | t3_id |
  6. +-------+-------+-------+
  7. |     1 |     1 |     1 |
  8. |     2 |     2 |     1 |
  9. |     3 |     3 |     1 |
  10. +-------+-------+-------+
  11. 3 rows in set (0.00 sec)
  13. mysql>

噫，发生了奇怪的事情，结果集中的第一条记录是符合我们预期的，但是剩下两条记录不符合我们预期，其中t3.id的值和t1.id、t2.id是不一样的，这是什么鬼呢？

哈哈，其实条件t1.id = t2.id = t3.id的真实含义是这样的：

  1. (t1.id = t2.id) = t3.id

也就是说我们需要分两步理解这个表达式：

让我们再分析一下上边的例子：

噫，原来t1.id = t2.id = t3.id是这个意思呀，那我们想要实现把三个表中id列值相同的记录取出来的效果该咋写？这么写：

  1. SELECT t1.id AS t1_id, t2.id AS t2_id, t3.id AS t3_id 
  2.     FROM t1, t2, t3 
  3.     WHERE t1.id = t2.id AND t1.id = t3.id;

我们看下效果：

  1. mysql> SELECT t1.id AS t1_id, t2.id AS t2_id, t3.id AS t3_id
  2.     ->     FROM t1, t2, t3
  3.     ->     WHERE t1.id = t2.id AND t1.id = t3.id;
  4. +-------+-------+-------+
  5. | t1_id | t2_id | t3_id |
  6. +-------+-------+-------+
  7. |     1 |     1 |     1 |
  8. |     2 |     2 |     2 |
  9. |     3 |     3 |     3 |
  10. +-------+-------+-------+
  11. 3 rows in set (0.00 sec)

这样的话，MySQL才会把三个表中id列值相同的记录取出来呢～ 一定要记住这个知识点喔，小心哪天写三表连接的时候写错了还找不到原因呢～

  1. 小贴士：
  3. 本文的这个问题是有《MySQL是怎样运行的：从根儿上理解MySQL》的微信讨论群里昵称为“白衬衫老胡同”的同学提问的，说实话我也把这个知识点忘掉了，在此感谢他的问题～

MySQL乱码

标签： 公众号文章

字符集转换概述

我们有必要说明一下，字符其实是面向人类的一个概念，计算机可并不关心字符是什么，它只关心这个字符对应的字节编码是什么。对于一个字节序列，计算机怎么知道它是使用什么字符集编码的呢？计算机不知道，所以其实在计算机中表示一个字符串时，都需要附带上它对应的字符集是什么，就像这样（以C++语言为例）：

  1. class String {
  2.     byte* content;
  3.     CHARSET_INFO* charset;
  4. }

比方说我们现在有一个以utf8字符集编码的汉字'我'，那么意味着计算机中不仅仅要存储'我'的utf8编码0xE68891，还需要存储它是使用什么字符集编码的信息，就像这样：

  1. {
  2.     content: 0xE68891;
  3.     charset: utf8;
  4. }

计算机内部包含将一种字符集转换成另一种字符集的函数库，也就是某个字符在某种字符集下的编码可以很顺利的转换为另一种字符集的编码，我们将这个过程称之为字符集转换。比方说我们可以将上述采用utf8字符集编码的字符'我'，转换成gbk字符集编码的形式，就变成了这样：

  1. {
  2.     content: 0xCED2;
  3.     charset: gbk;
  4. }
  1. 小贴士：
  3. 我们上边所说的'编码'可以当作动词，也可以当作名词来理解。当作动词的话意味着将一个字符映射到一个字节序列的过程，当作名词的话意味着一个字符对应的字节序列。大家根据上下文理解'编码'的含义。

MySQL客户端和服务器是怎么通信的

MySQL客户端发送给服务器的请求以及服务器发送给客户端的响应其实都是遵从一定格式的，我们把它们通信过程中事先规定好的数据格式称之为MySQL通信协议，这个协议是公开的，我们可以简单的使用wireshark等截包软件十分方便的分析这个通信协议。在了解了这个通信协议之后，我们甚至可以动手制作自己的客户端软件。市面上的MySQL客户端软件多种多样，我们并不想各个都分析一下，现在只选取在MySQL安装目录的bin目录下自带的mysql程序（此处的mysql程序指的是名字叫做mysql的一个可执行文件），如图所示：

我们在计算机的黑框框中执行该可执行文件，就相当于启动了一个客户端，就像这样： 

  1. 小贴士：
  3. 我们这里的'黑框框'指的是Windows操作系统中的cmd.exe或者UNIX系统中的Shell。

我们通常是按照下述步骤使用MySQL的：

  1. 启动客户端并连接到服务器
  2. 客户端发送请求。
  3. 服务器接收到请求
  4. 服务器处理请求
  5. 服务器处理请求完毕生成对该客户端的响应
  6. 客户端接收到响应

下边我们就详细分析一下每个步骤中都影响到了哪些字符集。

启动客户端并连接到服务器过程

每个MySQL客户端都维护者一个客户端默认字符集，这个默认字符集按照下边的套路进行取值：

在确认了MySQL客户端默认字符集之后，客户端就会向服务器发起登陆请求，传输一些诸如用户名、密码等信息，在这个请求里就会包含客户端使用的默认字符集是什么的信息，服务器收到后就明白了稍后客户端即将发送过来的请求是采用什么字符集编码的，自己生成的响应应该以什么字符集编码了（剧透一下：其实服务器在明白了客户端使用的默认字符集之后，就会将character_set_client、character_set_connection以及character_set_result这几个系统变量均设置为该值）。

客户端发送请求

登陆成功之后，我们就可以使用键盘在黑框框中键入我们想要输入的MySQL语句，输入完了之后就可以点击回车键将该语句当作请求发送到服务器，可是客户端发送的语句（本质是个字符串）到底是采用什么字符集编码的呢？这其实涉及到应用程序和操作系统之间的交互，我们的MySQL客户端程序其实是一个应用程序，它从黑框框中读取数据其实是要调用操作系统提供的读取接口。在不同的操作系统中，调用的读取接口其实是不同的，我们还得分情况讨论一下：

服务器接收请求

服务器接收到到的请求本质上就是一个字节序列，服务器将其看作是采用系统变量character_set_client代表的字符集进行编码的字节序列。character_set_client是一个SESSION级别的系统变量，也就是说每个客户端和服务器建立连接后，服务器都会为该客户端维护一个单独的character_set_client变量，每个客户端在登录服务器的时候都会将客户端的默认字符集通知给服务器，然后服务器设置该客户端专属的character_set_client。

我们可以使用SET命令单独修改character_set_client对应的值，就像这样：

  1. SET character_set_client=gbk;

需要注意的是，character_set_client对应的字符集一定要包含请求中的字符，比方说我们把character_set_client设置成ascii，而请求中发送了一个汉字'我'，将会发生这样的事情：

  1. mysql> SET character_set_client=ascii;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> SHOW VARIABLES LIKE 'character%';
  5. +--------------------------+------------------------------------------------------+
  6. | Variable_name            | Value                                                |
  7. +--------------------------+------------------------------------------------------+
  8. | character_set_client     | ascii                                                |
  9. | character_set_connection | utf8                                                 |
  10. | character_set_database   | utf8                                                 |
  11. | character_set_filesystem | binary                                               |
  12. | character_set_results    | utf8                                                 |
  13. | character_set_server     | utf8                                                 |
  14. | character_set_system     | utf8                                                 |
  15. | character_sets_dir       | /usr/local/Cellar/mysql/5.7.21/share/mysql/charsets/ |
  16. +--------------------------+------------------------------------------------------+
  17. 8 rows in set (0.00 sec)
  19. mysql> SELECT '我';
  20. +-----+
  21. | ??? |
  22. +-----+
  23. | ??? |
  24. +-----+
  25. 1 row in set, 1 warning (0.00 sec)
  27. mysql> SHOW WARNINGS\G
  28. *************************** 1. row ***************************
  29.   Level: Warning
  30.    Code: 1300
  31. Message: Invalid ascii character string: '\xE6\x88\x91'
  32. 1 row in set (0.00 sec)

如图所示，最后提示了'E6、88、91'并不是正确的ascii字符。

  1. 小贴士：
  3. 可以将character_set_client设置为latin1，看看还会不会报告WARNINGS，以及为什么～

服务器处理请求

服务器在处理请求时会将请求中的字符再次转换为一种特定的字符集，该字符集由系统变量character_set_connection表示，该系统变量也是SESSION级别的。每个客户端在登录服务器的时候都会将客户端的默认字符集通知给服务器，然后服务器设置该客户端专属的character_set_connection。

不过我们之后可以通过SET命令单独修改这个character_set_connection系统变量。比方说客户端发送给服务器的请求中包含字节序列0xE68891，然后服务器针对该客户端的系统变量character_set_client为utf8，那么此时服务器就知道该字节序列其实是代表汉字'我'，如果此时服务器针对该客户端的系统变量character_set_connection为gbk，那么在计算机内部还需要将该字符转换为采用gbk字符集编码的形式，也就是0xCED2。

有同学可能会想这一步有点儿像脱了裤子放屁的意思，但是大家请考虑下边这个查询语句：

  1. mysql> SELECT 'a' = 'A';

请问大家这个查询语句的返回结果应该是TRUE还是FALSE？其实结果是不确定。这是因为我们并不知道比较两个字符串的大小到底比的是什么！我们应该从两个方面考虑：

MySQL中支持若干种字符集，我们可以使用SHOW CHARSET命令查看，如下图所示（太多了，只展示几种，具体自己运行一下该命令）：

  1. mysql> SHOW CHARSET;
  2. +----------+---------------------------------+---------------------+--------+
  3. | Charset  | Description                     | Default collation   | Maxlen |
  4. +----------+---------------------------------+---------------------+--------+
  5. | big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
  6. | latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
  7. | latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
  8. | ascii    | US ASCII                        | ascii_general_ci    |      1 |
  9. | gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
  10. | gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
  11. | utf8     | UTF-8 Unicode                   | utf8_general_ci     |      3 |
  12. | utf8mb4  | UTF-8 Unicode                   | utf8mb4_general_ci  |      4 |
  13. | utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
  14. | utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
  15. | utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
  16. | binary   | Binary pseudo charset           | binary              |      1 |
  17. | gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
  18. +----------+---------------------------------+---------------------+--------+
  19. 41 rows in set (0.04 sec)

其中每一种字符集又对应着若干种比较规则，我们以utf8字符集为例（太多了，也只展示几个）：

  1. mysql> SHOW COLLATION WHERE Charset='utf8';
  2. +--------------------------+---------+-----+---------+----------+---------+
  3. | Collation                | Charset | Id  | Default | Compiled | Sortlen |
  4. +--------------------------+---------+-----+---------+----------+---------+
  5. | utf8_general_ci          | utf8    |  33 | Yes     | Yes      |       1 |
  6. | utf8_bin                 | utf8    |  83 |         | Yes      |       1 |
  7. | utf8_unicode_ci          | utf8    | 192 |         | Yes      |       8 |
  8. | utf8_icelandic_ci        | utf8    | 193 |         | Yes      |       8 |
  9. | utf8_latvian_ci          | utf8    | 194 |         | Yes      |       8 |
  10. | utf8_romanian_ci         | utf8    | 195 |         | Yes      |       8 |
  11. +--------------------------+---------+-----+---------+----------+---------+
  12. 27 rows in set (0.00 sec)

其中utf8_general_ci是utf8字符集默认的比较规则，在这种比较规则下是不区分大小写的，不过utf8_bin这种比较规则就是区分大小写的。

在我们将请求中的字节序列转换为character_set_connection对应的字符集编码的字节序列后，也要配套一个对应的比较规则，这个比较规则就由collation_connection系统变量来指定。我们现在通过SET命令来修改一下
和collation_connection的值分别设置为utf8和utf8_general_ci，然后比较一下'a'和'A'：

  1. mysql> SET character_set_connection=utf8;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> SET collation_connection=utf8_general_ci;
  5. Query OK, 0 rows affected (0.00 sec)
  7. mysql> SELECT 'a' = 'A';
  8. +-----------+
  9. | 'a' = 'A' |
  10. +-----------+
  11. |         1 |
  12. +-----------+
  13. 1 row in set (0.00 sec)

可以看到在这种情况下这两个字符串就是相等的。

我们现在通过SET命令来修改一下
和collation_connection的值分别设置为utf8和utf8_bin，然后比较一下'a'和'A'：

  1. mysql> SET character_set_connection=utf8;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> SET collation_connection=utf8_bin;
  5. Query OK, 0 rows affected (0.00 sec)
  7. mysql> SELECT 'a' = 'A';
  8. +-----------+
  9. | 'a' = 'A' |
  10. +-----------+
  11. |         0 |
  12. +-----------+
  13. 1 row in set (0.00 sec)

可以看到在这种情况下这两个字符串就是不相等的。

当然，如果我们并不需要单独指定将请求中的字符串采用何种字符集以及比较规则的话，并不用太关心character_set_connection和collation_connection设置成啥，不过需要注意一点，就是character_set_connection对应的字符集必须包含请求中的字符。

服务器处理请求完毕生成对该客户端的响应

为了故事的顺利发展，我们先创建一个表：

  1. CREATE TABLE t (
  2.     c VARCHAR(100)
  3. ) ENGINE=INNODB CHARSET=utf8;

然后向这个表插入一条记录：

  1. INSERT INTO t VALUE('我');

现在这个表中的数据就如下所示：

  1. mysql> SELECT * FROM t;
  2. +------+
  3. | c    |
  4. +------+
  5. | 我   |
  6. +------+
  7. 1 row in set (0.00 sec)

我们可以看到该表中的字段其实是使用utf8字符集编码的，所以底层存放格式是：0xE68891，将它读出后需要发送到客户端，是不是直接将0xE68891发送到客户端呢？这可不一定，这个取决于character_set_result系统变量的值，该系统变量也是一个SESSION级别的变量。服务器会将该响应转换为character_set_result系统变量对应的字符集编码后的字节序列发送给客户端。每个客户端在登录服务器的时候都会将客户端的默认字符集通知给服务器，然后服务器设置该客户端专属的character_set_result。

我们也可以使用SET命令来设置character_set_result的值。不过也需要注意，character_set_result对应的字符集应该包含响应中的字符。

这里再强调一遍，character_set_client、character_set_connection和character_set_result这三个系统变量是服务器的系统变量，每个客户端在与服务器建立连接后，服务器都会为这个连接维护这三个变量，如图所示（我们假设连接1的这三个变量均为utf8，连接1的这三个变量均为gbk，连接1的这三个变量均为ascii，）：

一般情况下character_set_client、character_set_connection和character_set_result这三个系统变量应该和客户端的默认字符集相同，SET names命令可以一次性修改这三个系统变量：

  1. SET NAMES 'charset_name'

该语句和下边三个语句等效：

  1. SET character_set_client = charset_name;
  2. SET character_set_results = charset_name;
  3. SET character_set_connection = charset_name;

不过这里需要大家特别注意，SET names语句并不会改变客户端的默认字符集！

客户端接收到响应

客户端收到的响应其实仍然是一个字节序列。客户端是如何将这个字节序列写到黑框框中的呢，这又涉及到应用程序和操作系统之间的一次交互。

乱码问题应该如何分析

好了，介绍了各个步骤中涉及到的各种字符集，大家估计也看的眼花缭乱了，下边总结一下我们遇到乱码的时候应该如何分析，而不是胡子眉毛一把抓，随便百度一篇文章，然后修改某个参数，运气好修改了之后改对了，运气不好改了一天也改不好。知其然也要知其所以然，在学习了本篇文章后，大家一定要有节奏的去分析乱码问题：

请认真分析上述的每一个步骤，然后发出惊呼：小样，不就是个乱码嘛，还治不了个你！

字符从UTF-8转成GBK发生了什么？

标签： 公众号文章

字符是什么

字符是面向人类的概念，大致可分为两种，一种叫可见字符，一种叫不可见字符。

顾名思义，可见字符就是打印出来后能看见的字符。比如a、b、我这样的人眼能看见的单个国家文字、标点符号、图形符号、数字等这样的东东，我们就叫做一个可见字符。

不可见字符也好理解，就是之前打印机或者在黑框框里打印字符的时候有时候需要换行，打个制表符啥的，或者在输出某个字符的时候就发出嘟地一声，这种我们看不到，只是为了控制输出效果的字符叫做不可见字符。

注意，字符都是单个的喔！。把字符连起来叫做字符串，比如abc，就是由a、b、c三个字符连起来的一个字符串。

计算机怎么表示字符

计算机只能处理二进制数据，它并不认识字符。为了让计算机能处理字符，人们人为地在字符和二进制数字之间建立起了映射关系，映射的过程可以被称作编码，字符和二进制数字的映射关系也可以被称作编码方案。由于谁都可以制作编码方案，不同地人制作出了不同地编码方案。制作一种编码方案说清楚两个事就可以：

虽然说谁都可以制作编码方案，但随着时间的流逝，只有为数不多的编码方案流行起来，比方说：

由于我们之前详细唠叨过不同编码方案是如何编码字符的，所以本文就不再赘述了。本文来唠叨一下不同字符编码方案之间是如何相互转换的。

字符编码方案的转换

对于字符'我'来说：

  1. 11100110 10001000 10010001

共3个字节，写成十六进制的形式就是：0xE68891。

  1. 11001110 11010010

共2字节，写成十六进制的形式就是：0xCED2

在某个需要将字符串的字符编码方案从UTF-8转成GBK的场景中，怎么把0xE68891转换成0xCED2呢？

解决这个问题其实很简单，我们可以制作一个大型数组， 数组大小就是源编码方案中包含的字符数量，这样在源编码方案中的每个字符的编码值都对应数组的一个下标。这样每个数组下标都对应一个字符，我们只需要将相应字符的目标编码方案的编码值填入到该下标对应的数组元素中。比方说：

也就是说对于某个字符来说，数组下标就是源编码方案的编码值，数组元素值就是目标编码方案的编码值。这样就可以很轻松的完成某个字符的编码方案转换功能。

这个方案是有很大缺点的，因为UTF-8中包含的字符数量是远超GBK的，这就导致我们申请的数组的存储空间有绝大部分是被浪费掉的。其实数组里只需要把GBK编码方案中的字符编码都存储上即可，这样可以显著减小数组大小，但是由于我们又要要求根据字符的UTF-8编码值作为下标找到对应的GBK编码值，这时候就有点儿犯难。。。

其实GBK编码方案中包含的字符只会被包含在UTF-8编码方案的几部分中：

如上图所示，画圈部分的UTF-8编码值对应的字符就已经可以覆盖GBK编码方案中的字符了。当然，画圈部分的UTF-8编码值对应的某些字符也可能GBK编码方案并不包含，但这并不会有什么大问题，只是在申请数组的存储空间的时候浪费掉一些而已。

这样针对每一个圈，我们都可以建立一个数组，数组大小就是圈中UTF-8编码值的数量，每个圈对应数组的下标0都对应该圈包含的第1个UTF-8编码值，数组元素值就是相应下标对应的UTF-8编码值对应字符的GBK编码值。这样就可以极大程度减少数组占用的存储空间大小了。

那如果是UTF-16转GBK呢？

简单，再仿造上述步骤建立从UTF-16的编码值映射到GBK编码值的数组呗！

那如果是BIG5转GBK呢？

简单，再仿造上述步骤建立从BIG5的编码值映射到GBK编码值的数组呗！

那如果是UTF-16转BIG5呢？

还得建立相应的数组...

好像很烦噢，字符编码方案多种多样，想实现任意两个编码方案都可以相互转换的话，那我们得建立多少数组呀！如下图所示：

而且编码方案也可以随时增加，没新增一种编码方案都要考虑到与其他编码方案如何相互转换的问题实在太繁琐了。有没有什么好的方案呢？

有！比方有5个人分别会说汉语、英语、法语、俄语、阿拉伯语，如果想让他们之间任意两个人可以相互沟通，其实也没必要让每个人都学会其他4种语言，只需要规定大家都会同一门语言，比方说汉语！这样大家只需要学习一门外语即可相互沟通（会汉语的甚至都不用再学一遍外语了）！

在将某个字符从一种编码方案转换成另一种编码方案时，我们不必单独维护从源编码方案到目标编码方案的转换数组，只需要先将源编码方案转换成一种中间编码方案，再将中间编码方案转换成目标编码方案。这样对于任何一种编码方案来说，仅需维护它与中间编码方案的转换数组即可。这个中间编码方案指的就是Unicode！如下图所示：

这样在将UTF-8编码值转换为GBK编码值时，需完成两步：

MySQL的实现

不像我们应用程序员直接调用某个库的进行字符编码转换的函数，MySQL为了尽量减少依赖，自己实现了各种字符编码方案以及它们之间的转换。我们下边以'我'字为例，看一下它是怎么实现从UTF-8编码方案转换成GBK编码方案的。

从UTF-8编码值获取对应的Unicode值

my_utf8_uni是用于获取UTF-8编码字符对应的Unicode值的函数，红色箭头指向的是实际操作过程。比方说字符'我'的UTF-8编码值是0xE68891，那就需要做如下操作：

  1. ((0xE6 & 0x0f) << 12) |
  2. ((0x88 ^ 0x80) << 6) |
  3. (0x91 ^ 0x80)

得到的结果是十进制的25105，这个25105就是字符'我'对应的Unicode值

从Unicode值获取对应GBK编码值

func_uni_gbk_onechar是根据Unicode值获取对应的GBK编码值的函数。从该函数的实现中可以看到，设计MySQL的大叔把包含在GBK编码方案中的字符对应的Unicode值分成了9个组，分别是tab_uni_gbk0～tab_uni_gbk8，当然，这些组中也包含了一些不属于GBK编码方案的字符对应的Unicode值，比方说第一个组tab_uni_gbk0:

其中值为0的元素对应的字符就是不包含在GBK编码方案中的。

如果让每一个组中仅包含GBK字符的话，这会导致组划分的过多。出于在组的数量和浪费的存储空间方面做出取舍，就形成了现在这种划分了9个组的方案。

由于字符'我'对应的Unicode值25105其实是在tab_uni_gbk6中的，我们看一下数组tab_uni_gbk6的下标为25105-0x4E00，也就是5137的元素的值是什么：

是他！是他！就是他！就是我们的0xCED2!

MySQL的定点数类型

标签： 公众号文章

上一篇文章我们唠叨了浮点数，知道了浮点数存储小数是不精确的。本篇继续唠叨一下MySQL中的另一种存储小数的方式 —— 定点数。

定点数类型

正因为用浮点数表示小数可能会有不精确的情况，在一些情况下我们必须保证小数是精确的，所以设计MySQL的大叔们提出一种称之为定点数的数据类型，它也是存储小数的一种方式：

类型 占用的存储空间（单位：字节） 取值范围
DECIMAL(M, D) 取决于M和D 取决于M和D

其中：

举个例子看一下，设置了M和D的单精度浮点数的取值范围的变化：

类型 取值范围
DECIMAL(4, 1) -999.9~999.9
DECIMAL(5, 1) -9999.9~9999.9
DECIMAL(6, 1) -99999.9~99999.9
DECIMAL(4, 0) -9999~9999
DECIMAL(4, 1) -999.9~999.9
DECIMAL(4, 2) -99.99~99.99

可以看到，在D相同的情况下，M越大，该类型的取值范围越大；在M相同的情况下，D越大，该类型的取值范围越小。当然，M和D的取值也不是无限大的，M的取值范围是1~255，D的取值范围是0~30，而且D的值必须不大于M。M和D都是可选的，如果我们省略了它们，那它们的值按照机器支持的最大值来存储。

我们说定点数是一种精确的小数，为了达到精确的目的我们就不能把它转换成二进制小数之后再存储(因为有很多十进制小数转为二进制小数后需要进行舍入操作，导致二进制小数表示的数值是不精确的)。其实转念一想，所谓的小数只是把两个十进制整数用小数点分割开来而已，我们只要把小数点左右的两个十进制整数给存储起来，那不就是精确的了么。比方说对于十进制小数2.38来说，我们可以把这个小数的小数点左右的两个整数，也就是2和38分别保存起来，那么不就相当于保存了一个精确的小数么，这波操作是不是很6。

当然事情并没有这么简单，对于给定M、D值的DECIMAL(M, D)类型，比如DEMCIMAL(16, 4)来说：

这些步骤看的有一丢丢懵逼吧，别着急，举个例子就都清楚了。比方说我们使用定点数类型DECIMAL(16, 4)来存储十进制小数1234567890.1234，这个小数会被划分成3个部分：

  1. 1 234567890 1234

也就是：

然后将每一组中的十进制数字转换成对应的二进制数字：

所以将这些十六进制数字连起来之后就是：

  1. 0x00010DFB38D204D2

最后还要将这个结果的最高位设置为1，所以最终十进制小数1234567890.1234使用定点数类型DECIMAL(16, 4)存储时共占用8个字节，具体内容为：

  1. 0x80010DFB38D204D2

有的同学会问，如果我们想使用定点数类型DECIMAL(16, 4)存储一个负数怎么办，比方说-1234567890.1234，这时只需要将0x80010DFB38D204D2中的每一个比特位都执行一个取反操作就好，也就是得到下边这个结果：

  1. 0x7FFEF204C72DFB2D

从上边的叙述中我们可以知道，对于DECIMAL(M, D)类型来说，给定的M和D的值不同，所需的存储空间大小也不同。可以看到，与浮点数相比，定点数需要更多的空间来存储数据，所以如果不是在某些需要存储精确小数的场景下，一般的小数用浮点数表示就足够了。

对于定点数类型DECIMAL(M, D)来说，M和D都是可选的，默认的M的值是10，默认的D的值是0，也就是说下列等式是成立的：

  1. DECIMAL = DECIMAL(10) = DECIMAL(10, 0)
  2. DECIMAL(n) = DECIMAL(n, 0)

另外M的范围是1~65，D的范围是0~30，且D的值不能超过M。

Innodb到底是怎么加锁的

标签： 公众号文章

上一篇文章中缺失一点儿东西，再补全一下。

学完本文后：妈妈再也不用担心我不知道InnoDB是怎么加锁的了!

流传较广，但是错误的一个观点

不知道从什么时候开始，下边这个错误的观点开始被广泛的流传：

在使用加锁读的方式读取使用InnoDB存储引擎的表时，当在执行查询时没有使用到索引时，行锁会被转换为表锁。

这里强调一点，对于任何INSERT、DELETE、UPDATE、SELECT ... LOCK IN SHARE MODE、SELECT ... FOR UPDATE语句来说，InnoDB存储引擎都不会加表级别的S锁或者X锁（我们这里不讨论表级意向锁的添加），只会加行级锁。所以即使对于全表扫描的加锁读语句来说，也只会对表中的记录进行加锁，而不是直接加一个表锁。

另外，很多小伙伴都会问：“这个语句加什么锁”，其实这是一个伪命题，因为一个语句需要加什么锁受到很多方面的影响，如果有人问你某某语句会加什么锁，那你可以直接回怼：真不专业！

我们稍后给大家详细分析一下影响加锁的因素都有哪些，以及从源码的角度看一下InnoDB到底是如何加锁的，希望小伙伴看完后会惊呼：真tm的简单！

不过在进行讨论前我们需要申明一下，我们讨论的只是InnoDB加的事务锁，即为了避免脏写、脏读、不可重复读、幻读这些现象带来的一致性问题而加的锁，并不是为了在多线程访问共享内存区域时而加的锁（比方说两个不同事务所在的线程想读写同一个页面时，需要进行加锁保护），也不包括server层添加的MDL锁。

本文所参考的源码版本为5.7.22。

事务锁到底是什么

锁是一个内存结构，InnoDB中用lock_t这个结构来定义：

不论是行锁，还是表锁都用这个结构来表示。我们给大家画个图：

其中的type_mode是用于区分这个锁结构到底是行锁还是表锁，如果是表锁的话是意向锁、直接对表加锁、还是AUTO-INC锁，如果是行锁的话，具体是正经记录锁、gap锁还是next-key锁。

小贴士：

在InnoDB的实现中，InnoDB的行锁是与记录一一对应的。即使是对于gap锁来说，在实现上也是为某条记录生成一个锁结构，然后该锁结构的类型是gap锁而已，并不是专门为某个区间生成一个锁结构。该gap锁的功能就是每当有别的事务插入记录时，会检查一下待插入记录的下一条记录上是否已经有一个gap锁的锁结构，如果有的话就进入阻塞状态。

我们平时所说的加锁就是在内存中生成这样的一个锁结构（除了生成锁结构，还有一种称作隐式锁的加锁方式，不用生成锁结构）。当然，如果为1条记录加锁就要生成一个锁结构，那岂不是太浪费了！设计InnoDB的大叔提出了一种优化方案，即同一个事务，在同一个页面上加的相同类型的锁都放在同一个锁结构里。

各种类型的锁是如果通过type_mode区分、各种锁都有什么作用，以及如何减少生成锁结构的细节我们这里就不展开了，那又要花费超长的篇幅，大家可以到《MySQL是怎样运行的：从根儿上理解MySQL》书籍中查看，我们下边来看具体的加锁细节。

准备工作

为了故事的顺利发展，我们先创建一个表hero：

  1. CREATE TABLE hero (
  2.     number INT,
  3.     name VARCHAR(100),
  4.     country varchar(100),
  5.     PRIMARY KEY (number),
  6.     KEY idx_name (name)
  7. ) Engine=InnoDB CHARSET=utf8;

然后向这个表里插入几条记录：

  1. INSERT INTO hero VALUES
  2.     (1, 'l刘备', '蜀'),
  3.     (3, 'z诸葛亮', '蜀'),
  4.     (8, 'c曹操', '魏'),
  5.     (15, 'x荀彧', '魏'),
  6.     (20, 's孙权', '吴');

然后现在hero表就有了两个索引（一个二级索引，一个聚簇索引），示意图如下：

加锁受哪些因素影响

一条语句加什么锁受多种因素影响，如果你不能确认下边这些因素的时候，最好不要抢先发言说"XXX语句对XXX记录加了什么锁"：

这里边有几个概念大家可能不是很清楚，我们先解释一下。

扫描区间

比方说下边这个查询：

  1. SELECT * FROM hero WHERE name <=  'l刘备' AND country = '魏';

MySQL可以使用下边两种方式来执行上述查询：

优化器会计算上述二种方式哪个成本更低，选用成本更低的那种来执行查询。

当优化器使用二级索引执行查询时，我们把(-∞, 'l刘备']称作扫描区间，意味着需要扫描name列值在这个区间中的所有二级索引记录，我们也可以把形成这个扫描区间的条件name <= 'l刘备'称作是形成这个扫描区间的边界条件；当优化器使用全表扫描执行查询时，我们把(-∞, +∞)称作扫描区间，意味着需要扫描number值在这个区间中的所有聚簇索引记录。

在执行一个查询的过程中，可能会用到多个扫描区间，如下所示：

  1. SELECT * FROM hero WHERE name < 'l刘备' OR name > 'x荀彧';

如果优化器采用二级索引idx_name执行上述查询时，那么对应的扫描区间就是(-∞, l刘备)以及('x荀彧', +∞)，即需要扫描name值在上述两个扫描区间中的记录。

每当InnoDB需要扫描一个扫描区间中的记录时，都需要分两步：

也就是说在扫描某个扫描区间的记录时，只有定位第1条记录的时候稍微麻烦点儿，其他记录只需要顺着链表（单个页面中的记录连成一个单向链表，不同的页面之间是双向链表）扫描即可。

精确匹配

对于形成扫描区间的边界条件来说，如果是等值匹配的条件，我们就把对这个扫描区间的匹配模式称作精确匹配。比方说：

  1. SELECT * FROM hero WHERE name = 'l刘备' AND country = '魏';

如果使用二级索引idx_name执行上述查询时，扫描区间就是['l刘备', 'l刘备']，形成这个扫描区间的边界条件就是name = 'l刘备'。我们就把在使用二级索引idx_name执行上述查询时的匹配模式称作精确匹配。

而对于下边这个查询来说

  1. SELECT * FROM hero WHERE name <=  'l刘备' AND country = '魏';

显然就不是精确匹配了。

唯一性搜索

如果在扫描某个扫描区间的记录前，就能事先确定该扫描区间最多只包含1条记录的话，那么就把这种情况称作唯一性搜索。我们看一下代码中判定扫描某个扫描区间的记录是否是唯一性搜索的代码是怎么写的：

其中：

  1. 匹配模式是精确匹配
  2. 使用的索引是聚簇索引或唯一二级索引
  3. 如果索引中包含多个列，则每个列在生成扫描区间时都应该被用到
  4. 如果使用的索引是唯一二级索引，那么在搜索时不能搜索某个索引列为NULL的记录（因为对于唯一二级索引来说，是可以存储多个值为NULL的记录的）。

上边几点都比较好理解，我们稍微解释一下第3点。比方说我们为某个表的a、b两列建立了一个唯一二级索引uk_a_b(a, b)，那么对于搜索条件a=1形成的扫描区间来说，不能保证该扫描区间最多只包含一条记录；对于搜索条件a=1 AND b= 1形成的扫描区间来说，才可以保证该扫描区间中仅包含1条记录（不包括记录的delete_flag=1的记录）。

row_search_mvcc

我们知道MySQL其实是分成server层和存储引擎层两部分，每当执行一个查询时，server层负责生成执行计划，即选取即将使用的索引以及对应的扫描区间。我们这里以InnoDB为例，针对每一个扫描区间，都会：

小贴士：


此处将记录发送给客户端其实是发送到本地的网络缓冲区，缓冲区大小由net_buffer_length控制，默认是16KB大小。等缓冲区满了才真正发送网络包到客户端。

可见一般情况下，server层和存储引擎层是以记录为单位进行通信的，而InnoDB读取一条记录最重要的函数就是row_search_mvcc：

可以看到这个函数长到吓人，有一千多行。

小贴士：

不知道你们公司有没有在一个函数中把业务逻辑写到一千多行的同事，如果有的话你想不想打他。

在row_search_mvcc里，对一条记录进行诸如多版本的可见性判断，要不要对记录进行加锁的判断，要是加锁的话加什么锁的选择，完成记录从InnoDB的存储格式到server层存储格式的转换等等等等十分繁杂的工作。

其实对于UPDATE、DELETE语句来说，执行它们前都需要先在B+树中定位到相应的记录，所以它们也会调用row_search_mvcc。

InnoDB对记录的加锁操作主要是在row_search_mvcc中的，像SELECT ... LOCK IN SHARE MODE、SELECT ... FOR UPDATE、UPDATE、DELETE这样的语句都会调用row_search_mvcc完成加锁操作。SELECT ... LOCK IN SHARE MODE会为记录添加S型锁，SELECT ... FOR UPDATE、UPDATE、DELETE会为记录添加X型锁。

InnoDB每当读取一条记录时，都会调用一次row_search_mvcc，在做了足够长的铺垫之后，我们终于可以看一下在row_search_mvcc函数中是怎么对某条记录进行加锁的。

语句到底是怎么加锁的

首先看一个十分重要的变量：

set_also_gap_locks表示是否要给记录添加gap锁（next-key锁可以看成是正经记录锁和gap锁的组合），它的默认值是TRUE，表示默认会给记录添加gap锁。

set_also_gap_locks可能会在下边这个地方发生变化：

即如果当前执行的是SELECT ... LOCK IN SHARE MODE或者SELECT ... FOR UPDATE这样的加锁读语句（非DELETE或UPDATE语句），并且隔离级别不大于READ COMMITTED 时，将set_also_gap_locks设置为FALSE。

其中prebuilt->select_lock_type表示加锁的类型，LOCK_NONE表示不加锁，LOCK_S表示加S锁（比方说执行SELECT ... LOCK IN SHARE MODE时），LOCK_X表示加X锁（比方说执行SELECT ... FOR UPDATE、DELETE、UPDATE时）。

对普通的SELECT的处理和意向锁的添加

再往后看：

其中：

小贴士：


具体的讲就是对于Repeatable Read隔离级别来说，只在首次执行SELECT语句时生成Readview，之后的SELECT语句都复用这个ReadView；对于Read Committed隔离级别来说，每次执行SELECT语句时都会生成一个ReadView。这一点并不是在上边截图中的代码里实现的。

下边是真正处理记录并给记录加锁的流程，我们给这些流程编个号。

1. 定位扫描区间的第一条记录

下边开始通过B+树定位某个扫描区间中的第一条记录了（对于一个扫描区间来说，只执行一次下述函数，因为只要定位到扫描区间的第一条记录之后，就可以沿着记录所在的单向链表进行查询了）：

其中btr_pcur_open_with_no_init是用于定位扫描区间中的第一条记录的函数。

2. 对于ORDER BY ... DESC条件形成的扫描区间的第一条记录的处理

在B+树的每层节点中，记录是按照键值从小到大的方式进行排序的。对于某个扫描区间来说，InnoDB通常是定位到扫描区间中最左边的那条记录，也就是键值最小的那条记录，然后沿着从左往右的方式向后扫描。

但是对于下边这个查询来说：

  1. SELECT * FROM hero WHERE name < 's孙权' AND country = '魏' ORDER BY name DESC FOR UPDATE ;

如果优化器决定使用二级索引idx_name执行上述查询的话，那么对应的扫描区间就是(-∞, 's孙权')。由于上述查询要求记录是按照从大到小的顺序返回给用户，所以InnoDB定位到扫描区间中的第一条记录应该是该扫描区间中最右边的那条记录，也就是键值最大的那条记录（在执行btr_pcur_open_with_no_init时就定位到最右边的那条记录），我们看一下idx_name二级索引示意图：

很显然，name值为'l刘备'的二级索引记录是扫描区间(-∞, 's孙权')中最右边的记录。

对于从右向左扫描扫描区间中记录的情况，针对从扫描区间中定位到的最右边的那条记录，需要做如下处理：

其中sel_set_rec_lock就是对一条记录进行加锁的函数。

可以看到，对于加锁读来说，在隔离级别不小于REPEATABLE READ并且也没有开启innodb_locks_unsafe_for_binlog系统变量的情况下，会对扫描区间中最右边的那条记录的下一条记录加一个类型为LOCK_GAP的锁，这个类型为LOCK_GAP的锁其实就是gap锁。

在本例中，假设事务的隔离级别是REPATABLE　READ。扫描区间(-∞, 's孙权')中最右边的那条记录就是name值为'l刘备'的二级索引记录，接下来就应该为该记录的下一条记录，也就是name值为's孙权'的二级索引记录加一个gap锁。

小贴士：


大家可以读一下上述代码的注释，其实这样加锁主要是为了阻止幻读。另外，这一步骤的加锁仅仅针对从右向左的扫描区间中的最右边的那条记录，之后扫描该扫描区间中的其他记录时就不做这一步的操作了。

3. 真正的加锁流程才开始——对Infimum和Supremum记录的处理

步骤1是用来定位扫描区间中的第一条记录，针对一个扫描区间只执行1次。

步骤2是针对从右向左扫描的扫描区间中最右边的那条记录的下一条记录进行加锁，针对一个扫描区间也执行1次。

从第3步骤开始以及往后的步骤，扫描区间中的每一条记录都要经历。

先看一下如果当前记录是Infimum记录或者Supremum记录时的处理：

从上边的代码中可以看出，如果当前读取的记录是Infimum记录，则啥也不做，直接去读下一条记录。

如果当前读取的记录是Supremum记录，则在下边这些条件成立的时候就会为记录添加一个类型为LOCK_ORDINARY的锁，其实也就是next-key锁：

其实由于Supremum记录本身是一条伪记录，别的事务并不会更新或删除它，所以给它添加next-key锁起到的效果和给它添加gap锁是一样的。

小贴士：


Infimum记录和Supremum记录是InnoDB自动为B+树中的每个页面都添加的两条虚拟记录，也可以被称作伪记录。Infimum记录和Supremum记录分别占用13字节的存储空间，被放置在页面中固定的位置。其中Infimum记录被看作最小的记录，Supremum记录被看作最大的记录，Infimum记录属于页面中的记录单向链表的头节点，Supremum记录属于页面中的记录单向链表的尾节点。更多关于页面结构的内容小伙伴们可以参考《MySQL是怎样运行的：从根儿上理解MySQL》书籍哈～

4. 真正的加锁流程才开始——对精确匹配的特殊处理

如果当前记录不是Infimum记录或者Supremum记录，下边进入对匹配模式是精确匹配的一个特殊处理：

可以看到，对于匹配模式是精确匹配的扫描区间来说，如果执行本次row_search_mvcc获取到的记录不在扫描区间中（0 != cmp_dtuple_rec(search_tuple, rec, offsets)），则需要进行一些特殊处理，即：

对于加锁读来说，如果事务的隔离级别不小于Repeatable Read并且未开启innodb_locks_unsafe_for_binlog系统变量，那么就对该记录加一个gap锁，并且直接返回（代码中直接跳转到normal_return处），就不进行后续的加锁操作了。

我们举一个例子，比方说当前事务的隔离级别为Repeatable Read，执行如下语句：

  1. SELECT * FROM hero WHERE name = 's孙权' FOR UPDATE;

如果使用二级索引idx_name执行上述查询，那么对应的扫描区间就是['s孙权', 's孙权']。该语句会首先对name值是's孙权'的记录进行加锁，不过该记录是在扫描区间中的，上述代码并不处理这种正常情况，关于正常情况的加锁我们稍后分析。

当读取完's孙权'的记录后，InnoDB会根据记录的next_record属性找到下一条二级索引记录，即name值为'x荀彧'的二级索引记录，该记录不在扫描区间['s孙权', 's孙权']中，即符合 0 != cmp_dtuple_rec(search_tuple, rec, offsets)条件，那么就执行上述代码的加锁流程 —— 对name值为'x荀彧'的二级索引记录加一个gap锁。另外，err被赋值为DB_RECORD_NOT_FOUND，这意味着向server层报告当前扫描区间的记录都已经扫描完了，server层在收到这个信息后就会停止向Innodb索要下一条记录的请求，即结束本扫描区间的查询。

小贴士：


这一步骤是对精确匹配的扫描区间的一个特殊处理，即当server层收到InnoDB返回的扫描区间的最后一条记录，server层仍会向InnoDB索要下一条记录。InnoDB仍会沿着记录所在的链表向后读取，此次读取到的记录就不在扫描区间中了，如例子中的name值为'x荀彧'的二级索引记录就不在扫描区间['s孙权', 's孙权']中。如果这是一个精确匹配的扫描区间，那么就进行如步骤4所示的特殊处理，如果不是的话，就继续执行第5步，也就是走正常的加锁流程。

5. 真正的加锁流程才开始——这回真的开始了

我们在代码中画了2个红框，这两个红框是对记录是不对记录加gap锁的场景。我们具体看一下。

对于1号红框来说：

也就是说只要上边任意一个条件成立，该记录就不应该被加gap锁，而应该添加正经记录锁。其余情况就应该加next-key锁（gap锁和正经记录锁的合体）了。

紧接着2号红框就又叙述了一个不加gap锁的场景：

对于>= 主键的这种边界条件来说，如果当前记录恰好是开始边界，就仅需对该记录加正经记录锁，而不需添加gap锁。

1号红框的内容比较好理解，我们举个例子看一下2号红框是在说什么。比方说下边这个查询：

  1. SELCT * FROM hero WHERE number >= 8 FOR UPDATE;

我们假设这个语句在隔离级别为REPEATABLE READ。

很显然，优化器会扫描[8, +∞)的聚簇索引记录。首先要通过B+树定位到扫描区间[8, +∞)的第一条记录，也就是number值为8的聚簇索引记录，这条记录就是扫描区间[8, +∞)的开始边界记录。按理说在REPEATABLE READ隔离级别下应该添加next-key锁，但由于2号红框中代码的存在，仅会给number值为8的聚簇索引记录添加正经记录锁。

小贴士：


2号方框的优化主要是基于“主键值是唯一的”这条约束，在一个事务执行了上述查询之后，其他事务是不能插入number值为8的记录的，这也用不着gap锁了。

除了1号方框和2号方框的场景，其余场景都给记录加next-key锁就好喽～

6. 判断索引条件下推的条件是否成立

如果是使用二级索引执行查询，并且有索引条件下推（Index Condition Pushdown，简称ICP）的条件的话，判断下推的条件是否成立：

这里大家特别注意一下，在使用二级索引执行查询，对于非精确匹配的扫描区间来说，形成扫描区间的边界条件也会被当作ICP条件下推到存储引擎判断，比方说下边这个查询：

  1. mysql> EXPLAIN SELECT * FROM hero WHERE name > 's孙权' AND name < 'z诸葛亮' FOR UPDATE;
  2. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
  3. | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
  4. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
  5. |  1 | SIMPLE      | hero  | NULL       | range | idx_name      | idx_name | 303     | NULL |    1 |   100.00 | Using index condition |
  6. +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
  7. 1 row in set, 1 warning (0.03 sec)

可以看到优化器决定使用idx_name执行上述查询，对应的扫描区间就是('s孙权', 'z诸葛亮')，形成这个扫描区间的边界条件就是name > 's孙权' AND name < 'z诸葛亮'。在执行计划的Extra列中出现了Using index condition，表明将边界条件name > 's孙权' AND name < 'z诸葛亮'作为ICP条件下推到了存储引擎。

不下推不要紧，一下推的话row_search_idx_cond_check就会判断当前记录是否已经不在扫描区间中了，如果不在扫描区间中的话，该函数就会返回ICP_OUT_OF_RANGE。这样的话，err被赋值为DB_RECORD_NOT_FOUND，这意味着向server层报告当前扫描区间的记录都已经扫描完了，server层在收到这个信息后就会停止向Innodb索要下一条记录的请求，即结束本扫描区间的查询。

当然，如果本次查询没有ICP条件，row_search_idx_cond_check直接返回ICP_MATCH，那就没有上述的麻烦事儿，继续向下走。

7. 回表对记录加锁

如果row_search_mvcc读取的是二级索引记录，则还需进行回表，找到相应的聚簇索引记录后需对该聚簇索引记录加一个正经记录锁：

其中，row_sel_get_clust_rec_for_mysql便是用于回表的函数，对聚簇索引进行加锁的逻辑在该函数中实现，我们这里就不展开了。

需要注意的是，即使是对于覆盖索引的场景下，如果我们想对记录加X型锁（也就是使用SELECT ... FOR UPDATE、DELETE、UPDATE语句时）时，也需要对二级索引记录执行回表操作，并给相应的聚簇索引记录添加正经记录锁。

8. row_search_mvcc返回，判断是否已经到达边界

每当处理完一条记录后，还需要判断一下这条记录还在不在扫描区间中，判断的代码如下：

如果当前记录还在扫描区间中，就给server层正常返回，如果不在了，就给server层返回一个HA_ERR_END_OF_FILE信息，表示当前扫描区间的记录都已经扫描完了，server层在收到这个信息后就会停止向Innodb索要下一条记录的请求，即结束本扫描区间的查询。

小贴士：

之前在处理精确匹配以及ICP条件时可能把err变量赋值为DB_RECORD_NOT_FOUND，其实后续代码会将这种情况也转换为给server层返回HA_ERR_END_OF_FILE信息。

9. 然后，再处理下一条记录

server层收到InnoDB的一条记录后，如果收到InnoDB通知的本扫描区间已经扫描完毕的信息，则结束本扫描区间的查询；否则继续向InnoDB要下一条记录，也就是需要继续执行一遍row_search_mvcc函数了。

不过此时并不是定位扫描区间中的第一条记录，而是根据记录所在的链表去取下一条记录即可，所以直接从步骤3开始执行就好了，又开始了新的一条记录的加锁流程。。。

循环往复，直到server层收到本扫描区间所有记录都扫描完了的信息为止。

还有一些释放锁的场景

大家在步骤8判断当前记录是否已经不再扫描区间中时可以看到，如果当前记录不在扫描区间中，会执行一个unlock_row的函数，这个函数主要是用于在隔离级别不大于READ COMMITTED时释放当前记录上的锁（如果是二级索引记录还要释放相应的聚簇索引记录上的锁）。

释放锁的场景并不是只有这么一个，在row_search_mvcc中也有几处释放锁的场景，我们这里就不多唠叨了。

总结一下

其实大家再回头看row_search_mvcc里的关于加锁的代码就会发现，其实流程还是很简单的：

好了，到现在为止大家应该明白为什么最开始说的即使是全表扫描的加锁读，加的也是行锁而不是表锁了。在使用InnoDB存储引擎时，当进行全表扫描时，其实就是相当于扫描主键值在(-∞, +∞)这个扫描区间中的聚簇索引记录，针对每一条聚簇索引记录，都需要执行一次row_search_mvcc函数，都需要进行如上所述的各种判断，最后决定给扫描的记录加什么锁。

语句加锁分析

标签： MySQL是怎样运行的

事前准备

建立一个存储三国英雄的hero表：

  1. CREATE TABLE hero (
  2.     number INT,
  3.     name VARCHAR(100),
  4.     country varchar(100),
  5.     PRIMARY KEY (number),
  6.     KEY idx_name (name)
  7. ) Engine=InnoDB CHARSET=utf8;

然后向这个表里插入几条记录：

  1. INSERT INTO hero VALUES
  2.     (1, 'l刘备', '蜀'),
  3.     (3, 'z诸葛亮', '蜀'),
  4.     (8, 'c曹操', '魏'),
  5.     (15, 'x荀彧', '魏'),
  6.     (20, 's孙权', '吴');

语句加锁分析

说了这么久，还是没说一条语句该加什么锁（心里是不是有点mmp啊[坏笑]）。在进一步分析之前，我们先给hero表的name列建一个索引：

  1. ALTER TABLE hero ADD INDEX idx_name (name);

然后现在hero表就有了两个索引（一个二级索引，一个聚簇索引），示意图如下：

其实啊，“XXX语句该加什么锁”本身就是个伪命题，一条语句需要加的锁受到很多条件制约，比方说：

在继续详细分析语句的加锁过程前，大家一定要有一个全局概念：加锁只是解决并发事务执行过程中引起的脏写、脏读、不可重复读、幻读这些问题的一种解决方案（MVCC算是一种解决脏读、不可重复读、幻读这些问题的一种解决方案），一定要意识到加锁的出发点是为了解决这些问题，不同情景下要解决的问题不一样，才导致加的锁不一样，千万不要为了加锁而加锁，容易把自己绕进去。当然，有时候因为MySQL具体的实现而导致一些情景下的加锁有些不太好理解，这就得我们死记硬背了～

我们这里把语句分为3种大类：普通的SELECT语句、锁定读的语句、INSERT语句，我们分别看一下。

普通的SELECT语句

普通的SELECT语句在：

锁定读的语句

我们把下边四种语句放到一起讨论：

我们说语句一和语句二是MySQL中规定的两种锁定读的语法格式，而语句三和语句四由于在执行过程需要首先定位到被改动的记录并给记录加锁，也可以被认为是一种锁定读。

READ UNCOMMITTED/READ COMMITTED隔离级别下

在READ UNCOMMITTED下语句的加锁方式和READ COMMITTED隔离级别下语句的加锁方式基本一致，所以就放到一块儿说了。值得注意的是，采用加锁方式解决并发事务带来的问题时，其实脏读和不可重复读在任何一个隔离级别下都不会发生（因为读-写操作需要排队进行）。

对于使用主键进行等值查询的情况
对于使用主键进行范围查询的情况
对于使用二级索引进行等值查询的情况
  1. 小贴士：
  3. 在READ UNCOMMITTED和READ COMMITTED隔离级别下，使用普通的二级索引和唯一二级索引进行加锁的过程是一样的，所以我们也就不分开讨论了。
对于使用二级索引进行范围查询的情况
全表扫描的情况

比方说：

  1. SELECT * FROM hero WHERE country  = '魏' LOCK IN SHARE MODE;

由于country列上未建索引，所以只能采用全表扫描的方式来执行这条查询语句，存储引擎每读取一条聚簇索引记录，就会为这条记录加锁一个S型正常记录锁，然后返回给server层，如果server层判断country = '魏'这个条件是否成立，如果成立则将其发送给客户端，否则会释放掉该记录上的锁，画个图就像这样：

使用SELECT ... FOR UPDATE进行加锁的情况与上边类似，只不过加的是X型正经记录锁，就不赘述了。

对于UPDATE ...和DELETE ...的语句来说，在遍历聚簇索引中的记录，都会为该聚簇索引记录加上X型正经记录锁，然后：

REPEATABLE READ隔离级别下

REPEATABLE READ隔离级别与READ UNCOMMITTED和READ COMMITTED这两个隔离级别相比，最主要的就是要解决幻读问题，幻读的解决还得靠我们上边讲过的gap锁。

对于使用主键进行等值查询的情况

如果在READ UNCOMMITTED／READ COMMITTED隔离级别下一样查询了一条主键值不存在的记录，那么什么锁也不需要加，因为在READ UNCOMMITTED／READ COMMITTED隔离级别下，并不需要禁止幻读问题。

其余语句的使用主键进行等值查询的情况与READ UNCOMMITTED／READ COMMITTED隔离级别类似，这里就不赘述了。

对于使用主键进行范围查询的情况
对于使用唯一二级索引进行等值查询的情况

由于hero表并没有唯一二级索引，我们把原先的idx_name修改为一个唯一二级索引：

  1. ALTER TABLE hero DROP INDEX idx_name, ADD UNIQUE KEY uk_name (name);
对于使用唯一二级索引进行范围查询的情况

INSERT语句

前边说INSERT语句一般情况下不加锁，不过如果即将插入的间隙已经被其他事务加了gap锁，那么本次INSERT操作会阻塞，并且当前事务会在该间隙上加一个插入意向锁。除此之外，在下边两种特殊情况下也会进行加锁操作：

其他的锁

系统锁和事务锁

死锁简介

有一点大家需要注意一下，我们平时所说的“DELETE表中的一条记录”其实意味着对聚簇索引和所有的二级索引中对应的记录做DELETE操作；“UPDATE表中的一条记录”其实意味着对聚簇索引和所有受影响的二级索引中对应的记录做DELETE操作（有时候UPDATE语句中不更新）；

“DELETE或者UPDATE表中的一条记录”其实意味着对聚簇索引和所有的二级索引中对应的记录做DELETE或者UPDATE操作，“向表中INSERT一条记录”其实意味着向聚簇索引和所有的二级索引中都插入一条记录。以DELETE操作为例，在执行“删除表中的一条记录”操作时，需要先获取到对应的聚簇索引记录和对应的二级索引记录上的X锁，换句话说，平时所说的“删除表中的一条记录”其实要对好多条记录进行加锁。

MySQL如何查看事务加锁情况

标签： 公众号文章

  1. 小贴士：
  3. 本篇文章算是回答一些同学的提问，以MySQL 5.7为例。

我们都知道MySQL的InnoDB存储引擎是支持事务功能的，不过在很多同学的潜意识中，只有把若干语句包含在BEGIN/START TRANSACTION、COMMIT中间，才算是开启一个事务，就像这样：

  1. BEGIN;
  2. 语句1;
  3. 语句2;
  4. ...
  5. 语句n;
  6. COMMIT;

其实不是这样的，每个涉及到使用InnoDB作为存储引擎的表的语句，都会在事务中执行。我们稍后详细看一下。

自动提交与手动提交

设计MySQL的大叔提供了一个称之为autocommit的系统变量，如下所示：

  1. mysql> SHOW VARIABLES LIKE 'autocommit';
  2. +---------------+-------+
  3. | Variable_name | Value |
  4. +---------------+-------+
  5. | autocommit    | ON    |
  6. +---------------+-------+
  7. 1 row in set (0.15 sec)

该系统变量含义如下：

如果我们当前会话的系统变量autocommit的值为ON，意味着开启了自动提交。此时如果我们想把若干个语句放到一个事务中执行，那就需要显式地写出BEGIN或者START TRANSACTION语句来禁用自动提交。

查看事务加锁的几种方法

有同学会使用information_schema数据库下的一些表来观察当前数据库中的一些事务和锁的情况，诸如：innodb_locks、innodb_lock_wait。但是千万要记住：在使用它们之前一定先得知道它们是干啥的，然后再去使用。不要在连这些表是干啥的情况下就去贸然使用，最后还能得出一个结论～。下边我们看看这几个表都是干嘛使的：

不过我们看到，在查询innodb_locks和innodb_lock_wait表的时候都伴随着一个warning，我们看一下系统在警告神马：

  1. mysql> SHOW WARNINGS\G
  2. *************************** 1. row ***************************
  3.   Level: Warning
  4.    Code: 1681
  5. Message: 'INFORMATION_SCHEMA.INNODB_LOCKS' is deprecated and will be removed in a future release.
  6. 1 row in set (0.01 sec)

其实是因为innodb_locks和innodb_lock_wait在我目前使用的版本（MySQL 5.7.21）中被标记为过时的，并且提示在未来的版本中可能被移除，其实也就是不鼓励我们使用这两个表来获取相关的锁信息。

另外，我们还可以使用SHOW ENGINE INNODB STATUS这个命令来查看当前系统中每个事务都加了哪些锁：

  1. mysql> SHOW ENGINE INNODB STATUS\G
  2. ...此处省略很多信息
  3. ------------
  4. TRANSACTIONS
  5. ------------
  6. Trx id counter 38944
  7. Purge done for trx's n:o < 38452 undo n:o < 0 state: running but idle
  8. History list length 262
  9. Total number of lock structs in row lock hash table 2
  10. LIST OF TRANSACTIONS FOR EACH SESSION:
  11. ---TRANSACTION 281479631937824, not started
  12. 0 lock struct(s), heap size 1160, 0 row lock(s)
  13. ---TRANSACTION 38938, ACTIVE 1875 sec
  14. 2 lock struct(s), heap size 1160, 1 row lock(s)
  15. MySQL thread id 29, OS thread handle 123145576628224, query id 690 localhost 127.0.0.1 root
  16. Trx read view will not see trx with id >= 38938, sees < 38938
  18. ...此处省略很多信息

由于输出的内容太多，为方便起见，我们只保留了关于TRANSACTIONS的相关信息，这里列出了每个事务获取锁的情况。如果我们想看到更详细的加锁情况，可以开启innodb_status_output_locks：

  1. mysql> SET GLOBAL innodb_status_output_locks = ON;
  2. Query OK, 0 rows affected (0.01 sec)

那每个锁的详细情况就被列出来了：

  1. mysql> SHOW ENGINE INNODB STATUS\G
  2. ...此处省略很多信息
  4. ------------
  5. TRANSACTIONS
  6. ------------
  7. Trx id counter 38945
  8. Purge done for trx's n:o < 38452 undo n:o < 0 state: running but idle
  9. History list length 262
  10. Total number of lock structs in row lock hash table 2
  11. LIST OF TRANSACTIONS FOR EACH SESSION:
  12. ---TRANSACTION 281479631937824, not started
  13. 0 lock struct(s), heap size 1160, 0 row lock(s)
  14. ---TRANSACTION 38938, ACTIVE 2122 sec
  15. 2 lock struct(s), heap size 1160, 1 row lock(s)
  16. MySQL thread id 29, OS thread handle 123145576628224, query id 690 localhost 127.0.0.1 root
  17. Trx read view will not see trx with id >= 38938, sees < 38938
  18. TABLE LOCK table `xiaohaizi`.`hero` trx id 38938 lock mode IX
  19. RECORD LOCKS space id 272 page no 3 n bits 80 index PRIMARY of table `xiaohaizi`.`hero` trx id 38938 lock_mode X locks rec but not gap
  20. Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
  21.  0: len 4; hex 80000001; asc     ;;
  22.  1: len 6; hex 000000009624; asc      $;;
  23.  2: len 7; hex 80000001d00110; asc        ;;
  24.  3: len 7; hex 6ce58898e5a487; asc l      ;;
  25.  4: len 3; hex e89c80; asc    ;;
  27. ...此处省略很多信息

当然，我们现在并不准备唠叨输出内容中的每个字段都是啥意思，之后有时间再撰文描述吧～

MySQL介于普通读和锁定读的加锁方式—— semi-consistent read

标签： 公众号文章

事前准备

为了故事的顺利发展，我们先建一个表，并向表中插入一些记录，下边是SQL语句：

  1. CREATE TABLE hero (
  2.     number INT,
  3.     name VARCHAR(100),
  4.     country varchar(100),
  5.     PRIMARY KEY (number),
  6.     KEY idx_name (name)
  7. ) Engine=InnoDB CHARSET=utf8;
  9. INSERT INTO hero VALUES
  10.     (1, 'l刘备', '蜀'),
  11.     (3, 'z诸葛亮', '蜀'),
  12.     (8, 'c曹操', '魏'),
  13.     (15, 'x荀彧', '魏'),
  14.     (20, 's孙权', '吴');

现在hero表中的记录情况就如下所示：

  1. mysql> SELECT * FROM hero;
  2. +--------+------------+---------+
  3. | number | name       | country |
  4. +--------+------------+---------+
  5. |      1 | l刘备      | 蜀      |
  6. |      3 | z诸葛亮    | 蜀      |
  7. |      8 | c曹操      | 魏      |
  8. |     15 | x荀彧      | 魏      |
  9. |     20 | s孙权      | 吴      |
  10. +--------+------------+---------+
  11. 5 rows in set (0.01 sec)

现象

在小册答疑群里有一位同学提了一个问题：说是在READ COMMITTED隔离级别下发生了一件百思不得其解的事儿。好的，首先构造环境，将当前会话默认的隔离级别设置成READ COMMITTED：

  1. mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  2. Query OK, 0 rows affected (0.00 sec)

事务T1先执行：

  1. # T1中，隔离级别为READ COMMITTED
  2. mysql> BEGIN;
  3. Query OK, 0 rows affected (0.00 sec)
  5. mysql> SELECT * FROM hero WHERE country = '魏' FOR UPDATE;
  6. +--------+---------+---------+
  7. | number | name    | country |
  8. +--------+---------+---------+
  9. |      8 | c曹操   | 魏      |
  10. |     15 | x荀彧   | 魏      |
  11. +--------+---------+---------+
  12. 2 rows in set (0.01 sec)

country列并不是索引列，所以本条语句执行时肯定是使用扫描聚簇索引的全表扫描方式来执行，EXPLAIN语句也证明了我们的想法：

  1. mysql> EXPLAIN SELECT * FROM hero WHERE country = '魏' FOR UPDATE;
  2. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  3. | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  4. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  5. |  1 | SIMPLE      | hero  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where |
  6. +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  7. 1 row in set, 1 warning (0.02 sec)

我们之前学过MySQL语句的加锁分析，知道在READ COMMITTED隔离级别下，如果采用全表扫描的方式执行查询语句时，InnoDB存储引擎将依次对每条记录加正经记录锁，在server层测试该记录是否符合WHERE条件，如果不符合则将加在该记录上的锁释放掉。本例中使用FOR UPDATE语句，肯定加的是X型正经记录锁。只有两条记录符合WHERE条件，所以最终其实只对这两条符合条件的记录加了X型正经记录锁（就是number列值为8和15的两条记录）。当然，我们可以使用SHOW ENGINE INNODB STATUS命令证明我们的分析：

  1. mysql> SHOW ENGINE INNODB STATUS\G
  2. ... 省略了很多内容
  4. ------------
  5. TRANSACTIONS
  6. ------------
  7. Trx id counter 39764
  8. Purge done for trx's n:o < 39763 undo n:o < 0 state: running but idle
  9. History list length 36
  10. Total number of lock structs in row lock hash table 1
  11. LIST OF TRANSACTIONS FOR EACH SESSION:
  12. ---TRANSACTION 281479653009568, not started
  13. 0 lock struct(s), heap size 1160, 0 row lock(s)
  14. ---TRANSACTION 281479653012832, not started
  15. 0 lock struct(s), heap size 1160, 0 row lock(s)
  16. ---TRANSACTION 39763, ACTIVE 468 sec
  17. 2 lock struct(s), heap size 1160, 2 row lock(s)
  18. MySQL thread id 19, OS thread handle 123145470611456, query id 586 localhost 127.0.0.1 root
  19. TABLE LOCK table `xiaohaizi`.`hero` trx id 39763 lock mode IX
  20. RECORD LOCKS space id 287 page no 3 n bits 72 index PRIMARY of table `xiaohaizi`.`hero` trx id 39763 lock_mode X locks rec but not gap
  21. Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
  22.  0: len 4; hex 80000008; asc     ;;
  23.  1: len 6; hex 000000009b4a; asc      J;;
  24.  2: len 7; hex 80000001d3012a; asc       *;;
  25.  3: len 7; hex 63e69bb9e6938d; asc c      ;;
  26.  4: len 3; hex e9ad8f; asc    ;;
  28. Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
  29.  0: len 4; hex 8000000f; asc     ;;
  30.  1: len 6; hex 000000009b4a; asc      J;;
  31.  2: len 7; hex 80000001d30137; asc       7;;
  32.  3: len 7; hex 78e88d80e5bda7; asc x      ;;
  33.  4: len 3; hex e9ad8f; asc    ;;
  35.  ... 省略了很多内容

其中id为39763的事务就是指T1，可以看出它为heap no值为4和5的两条记录加了X型正经记录锁（lock_mode X locks rec but not gap）。

然后再开启一个隔离级别也为READ COMMITTED的事务T2，在其中执行：

  1. # T2中，隔离级别为READ COMMITTED
  2. mysql> BEGIN;
  3. Query OK, 0 rows affected (0.00 sec)
  5. mysql> SELECT * FROM hero WHERE country = '吴' FOR UPDATE;
  6. (进入阻塞状态)

很显然，这条语句也会采用全表扫描的方式来执行，会依次去获取每一条聚簇索引记录的锁。不过因为number值为8的记录已经被T1加了X型正经记录锁，T2想得却得不到，只能眼巴巴的进行阻塞状态，此时的SHOW ENGINE INNODB STATUS也能证明我们的猜想（只截取了一部分）：

  1. ---TRANSACTION 39764, ACTIVE 34 sec fetching rows
  2. mysql tables in use 1, locked 1
  3. LOCK WAIT 3 lock struct(s), heap size 1160, 1 row lock(s)
  4. MySQL thread id 20, OS thread handle 123145471168512, query id 590 localhost 127.0.0.1 root Sending data
  5. SELECT * FROM hero WHERE country = '吴' FOR UPDATE
  6. ------- TRX HAS BEEN WAITING 34 SEC FOR THIS LOCK TO BE GRANTED:
  7. RECORD LOCKS space id 287 page no 3 n bits 72 index PRIMARY of table `xiaohaizi`.`hero` trx id 39764 lock_mode X locks rec but not gap waiting
  8. Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
  9.  0: len 4; hex 80000008; asc     ;;
  10.  1: len 6; hex 000000009b4a; asc      J;;
  11.  2: len 7; hex 80000001d3012a; asc       *;;
  12.  3: len 7; hex 63e69bb9e6938d; asc c      ;;
  13.  4: len 3; hex e9ad8f; asc    ;;

可以看到T2正在等待获取heap no为4的记录上的X型正经记录锁（lock_mode X locks rec but not gap waiting）。

以上是很正常的阻塞逻辑，我们都可以分析出来，不过如果在T2中执行下边的UPDATE语句：

  1. # T2中，隔离级别为READ COMMITTED
  2. mysql> BEGIN;
  3. Query OK, 0 rows affected (0.00 sec)
  5. mysql> UPDATE hero SET name = 'xxx' WHERE country = '吴';
  6. Query OK, 1 row affected (0.02 sec)
  7. Rows matched: 1  Changed: 1  Warnings: 0

WTF? 竟然没有阻塞，就这么随意地执行成功了？同样的WHERE条件，同样的执行计划，怎么SELECT ... FOR UPDATE和UPDATE语句的加锁情况不一样？

原因

哈哈，是的，的确不一样。其实MySQL支持3种类型的读语句：

很显然，我们上边所唠叨的例子中是因为事务T2执行UPDATE语句时使用了半一致性读，判断number列值为8和15这两条记录的最新提交版本的country列值均不为UPDATE语句中WHERE条件中的'吴'，所以直接就跳过它们，不对它们加锁了。

本知识点容易被忽略，各位同学在工作过程中分析的时候别忘记考虑一下Semi-Consistent Read喔，码字不易，有帮助帮着转发喔，么么哒～

死锁分析

标签： 公众号文章

如果我们的业务处在一个非常初级的阶段，并发程度比较低，那么我们可以几年都遇不到一次死锁问题的发生，反之，我们业务的并发程度非常高，那么时不时爆出的死锁问题肯定让我们非常挠头。不过在死锁问题发生时，很多没有经验的同学的第一反应就是成为一直鸵鸟：这玩意儿很高深，我也看不懂，听天由命吧，又不是一直发生。其实如果大家认真研读了我们之前写了3篇关于MySQL中语句加锁分析的文章，加上本篇关于死锁日志的分析，那么解决死锁问题应该也不是那么摸不着头脑的事情了。

准备工作

为了故事的顺利发展，我们需要建一个表：

  1. CREATE TABLE hero (
  2.     id INT,
  3.     name VARCHAR(100),
  4.     country varchar(100),
  5.     PRIMARY KEY (id),
  6.     KEY idx_name (name)
  7. ) Engine=InnoDB CHARSET=utf8;

我们为hero表的id列创建了聚簇索引，为name列创建了一个二级索引。这个hero表主要是为了存储三国时的一些英雄，我们向表中插入一些记录：

  1. INSERT INTO hero VALUES
  2.     (1, 'l刘备', '蜀'),
  3.     (3, 'z诸葛亮', '蜀'),
  4.     (8, 'c曹操', '魏'),
  5.     (15, 'x荀彧', '魏'),
  6.     (20, 's孙权', '吴');

现在表中的数据就是这样的：

  1. mysql> SELECT * FROM hero;
  2. +----+------------+---------+
  3. | id | name       | country |
  4. +----+------------+---------+
  5. |  1 | l刘备      | 蜀      |
  6. |  3 | z诸葛亮    | 蜀      |
  7. |  8 | c曹操      | 魏      |
  8. | 15 | x荀彧      | 魏      |
  9. | 20 | s孙权      | 吴      |
  10. +----+------------+---------+
  11. 5 rows in set (0.00 sec)

准备工作就做完了。

创建死锁情景

我们先创建一个发生死锁的情景，在Session A和Session B中分别执行两个事务，具体情况如下：

我们分析一下：

以上是我们从语句加了什么锁的角度出发来进行死锁情况分析的，但是实际应用中我们可能压根儿不知道到底是哪几条语句产生了死锁，我们需要根据MySQL在死锁发生时产生的死锁日志来逆向定位一下到底是什么语句产生了死锁，从而再优化我们的业务。

查看死锁日志

设计InnoDB的大叔给我们提供了SHOW ENGINE INNODB STATUS命令来查看关于InnoDB存储引擎的一些状态信息，其中就包括了系统最近一次发生死锁时的加锁情况。在上边例子中的死锁发生时，我们运行一下这个命令：

  1. mysql> SHOW ENGINE INNODB STATUS\G
  2. ...省略了好多其他信息
  3. ------------------------
  4. LATEST DETECTED DEADLOCK
  5. ------------------------
  6. 2019-06-20 13:39:19 0x70000697e000
  7. *** (1) TRANSACTION:
  8. TRANSACTION 30477, ACTIVE 10 sec starting index read
  9. mysql tables in use 1, locked 1
  10. LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s)
  11. MySQL thread id 2, OS thread handle 123145412648960, query id 46 localhost 127.0.0.1 root statistics
  12. select * from hero where id = 3 for update
  13. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  14. RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30477 lock_mode X locks rec but not gap waiting
  15. Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
  16.  0: len 4; hex 80000003; asc     ;;
  17.  1: len 6; hex 000000007517; asc     u ;;
  18.  2: len 7; hex 80000001d0011d; asc        ;;
  19.  3: len 10; hex 7ae8afb8e8919be4baae; asc z         ;;
  20.  4: len 3; hex e89c80; asc    ;;
  22. *** (2) TRANSACTION:
  23. TRANSACTION 30478, ACTIVE 8 sec starting index read
  24. mysql tables in use 1, locked 1
  25. 3 lock struct(s), heap size 1160, 2 row lock(s)
  26. MySQL thread id 3, OS thread handle 123145412927488, query id 47 localhost 127.0.0.1 root statistics
  27. select * from hero where id = 1 for update
  28. *** (2) HOLDS THE LOCK(S):
  29. RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap
  30. Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
  31.  0: len 4; hex 80000003; asc     ;;
  32.  1: len 6; hex 000000007517; asc     u ;;
  33.  2: len 7; hex 80000001d0011d; asc        ;;
  34.  3: len 10; hex 7ae8afb8e8919be4baae; asc z         ;;
  35.  4: len 3; hex e89c80; asc    ;;
  37. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  38. RECORD LOCKS space id 171 page no 3 n bits 72 index PRIMARY of table `dahaizi`.`hero` trx id 30478 lock_mode X locks rec but not gap waiting
  39. Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
  40.  0: len 4; hex 80000001; asc     ;;
  41.  1: len 6; hex 000000007517; asc     u ;;
  42.  2: len 7; hex 80000001d00110; asc        ;;
  43.  3: len 7; hex 6ce58898e5a487; asc l      ;;
  44.  4: len 3; hex e89c80; asc    ;;
  46. *** WE ROLL BACK TRANSACTION (2)
  47. ------------
  48. ...省略了好多其他信息

我们只关心最近发生的死锁信息，所以就把以LATEST DETECTED DEADLOCK这一部分给单独提出来分析一下。下边我们就逐行看一下这个输出的死锁日志都是什么意思：

思索分析的思路

  1. 查看死锁日志时，首先看一下发生死锁的事务等待获取锁的语句都是啥。

    本例中，发现SESSION A发生阻塞的语句是：

    1. select * from hero where id = 3 for update

    SESSION B发生阻塞的语句是：

    1. select * from hero where id = 1 for update

    然后切记：到自己的业务代码中找出这两条语句所在事务的其他语句。

  2. 找到发生死锁的事务中所有的语句之后，对照着事务获取到的锁和正在等待的锁的信息来分析死锁发生过程。

    从死锁日志中可以看出来，SESSION A获取了hero表聚簇索引id值为1的记录的X型正经记录锁（这其实是从SESSION B正在等待的锁中获取的），查看SESSION A中的语句，发现是下边这个语句造成的（对照着语句加锁分析那三篇文章）:

    1. select * from hero where id = 1 for update;

    还有SESSION B获取了hero表聚簇索引id值为3的记录的X型正经记录锁，查看SESSION B中的语句，发现是下边这个语句造成的（对照着语句加锁分析那三篇文章）:

    1. select * from hero where id = 3 for update;

    然后看SESSION A正在等待hero表聚簇索引id值为3的记录的X型正经记录锁，这个是由于下边这个语句造成的：

    1. select * from hero where id = 3 for update;

    然后看SESSION B正在等待hero表聚簇索引id值为1的记录的X型正经记录锁，这个是由于下边这个语句造成的：

    1. select * from hero where id = 1 for update;

    然后整个死锁形成过程就根据死锁日志给还原出来了。

两条一样的INSERT语句竟然引发了死锁？

标签： 公众号文章

两条一样的INSERT语句竟然引发了死锁，这究竟是人性的扭曲，还是道德的沦丧，让我们不禁感叹一句：卧槽！这也能死锁，然后眼中含着悲催的泪水无奈的改起了业务代码。

好的，在深入分析为啥两条一样的INSERT语句也会产生死锁之前，我们先介绍一些基础知识。

准备一下环境

为了故事的顺利发展，我们新建一个用了无数次的hero表：

  1. CREATE TABLE hero (
  2.     number INT AUTO_INCREMENT,
  3.     name VARCHAR(100),
  4.     country varchar(100),
  5.     PRIMARY KEY (number),
  6.     UNIQUE KEY uk_name (name)
  7. ) Engine=InnoDB CHARSET=utf8;

然后向这个表里插入几条记录：

  1. INSERT INTO hero VALUES
  2.     (1, 'l刘备', '蜀'),
  3.     (3, 'z诸葛亮', '蜀'),
  4.     (8, 'c曹操', '魏'),
  5.     (15, 'x荀彧', '魏'),
  6.     (20, 's孙权', '吴');

现在hero表就有了两个索引（一个唯一二级索引，一个聚簇索引），示意图如下：

INSERT语句如何加锁

读过《MySQL是怎样运行的：从根儿上理解MySQL》的小伙伴肯定知道，INSERT语句在正常执行时是不会生成锁结构的，它是靠聚簇索引记录自带的trx_id隐藏列来作为隐式锁来保护记录的。

但是在一些特殊场景下，INSERT语句还是会生成锁结构的，我们列举一下：

1. 待插入记录的下一条记录上已经被其他事务加了gap锁时

每插入一条新记录，都需要看一下待插入记录的下一条记录上是否已经被加了gap锁，如果已加gap锁，那INSERT语句应该被阻塞，并生成一个插入意向锁。

比方说对于hero表来说，事务T1运行在REPEATABLE READ（后续简称为RR，后续也会把READ COMMITTED简称为RC）隔离级别中，执行了下边的语句：

  1. # 事务T1
  2. mysql> BEGIN;
  3. Query OK, 0 rows affected (0.00 sec)
  5. mysql> SELECT * FROM hero WHERE number < 8 FOR UPDATE;
  6. +--------+------------+---------+
  7. | number | name       | country |
  8. +--------+------------+---------+
  9. |      1 | l刘备      | 蜀      |
  10. |      3 | z诸葛亮    | 蜀      |
  11. +--------+------------+---------+
  12. 2 rows in set (0.02 sec)

这条语句会对主键值为1、3、8的这3条记录都添加X型next-key锁，不信的话我们使用SHOW ENGINE INNODB STATUS语句看一下加锁情况，图中箭头指向的记录就是number值为8的记录：

小贴士：


至于SELECT、DELETE、UPDATE语句如何加锁，我们已经在之前的文章中分析过了，这里就不再赘述了。

此时事务T2想插入一条主键值为4的聚簇索引记录，那么T2在插入记录前，首先要定位一下主键值为4的聚簇索引记录在页面中的位置，发现主键值为4的下一条记录的主键值是8，而主键值是8的聚簇索引记录已经被添加了gap锁（next-key锁包含了正经记录锁和gap锁），那么事务T2就需要进入阻塞状态，并生成一个类型为插入意向锁的锁结构。

我们在事务T2中执行一下INSERT语句验证一下：

  1. mysql> BEGIN;
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> INSERT INTO hero VALUES(4, 'g关羽', '蜀');

此时T2进入阻塞状态，我们再使用SHOW ENGINE INNODB STATUS看一下加锁情况：

可见T2对主键值为8的聚簇索引记录加了一个插入意向锁（就是箭头处指向的lock_mode X locks gap before rec insert intention），并且处在waiting状态。

好了，验证过之后，我们再来看看代码里是如何实现的：

lock_rec_insert_check_and_lock函数用于看一下别的事务是否阻止本次INSERT插入，如果是，那么本事务就给被别的事务添加了gap锁的记录生成一个插入意向锁，具体过程如下：

小贴士：


lock_rec_other_has_conflicting函数用于检测本次要获取的锁和记录上已有的锁是否有冲突，有兴趣的同学可以看一下。

2. 遇到重复键时

如果在插入新记录时，发现页面中已有的记录的主键或者唯一二级索引列与待插入记录的主键或者唯一二级索引列值相同（不过可以有多条记录的唯一二级索引列的值同时为NULL，这里不考虑这种情况了），此时插入新记录的事务会获取页面中已存在的键值相同的记录的锁。

如果是主键值重复，那么：

如果是唯一二级索引列重复，那不论是哪个隔离级别，插入新记录的事务都会给已存在的二级索引列值重复的二级索引记录添加S型next-key锁，再强调一遍，加的是next-key锁！加的是next-key锁！加的是next-key锁！这是rc隔离级别中为数不多的给记录添加gap锁的场景。

小贴士：


本来设计InnoDB的大叔并不想在RC隔离级别引入gap锁，但是由于某些原因，如果不添加gap锁的话，会让唯一二级索引中出现多条唯一二级索引列值相同的记录，这就违背了UNIQUE约束。所以后来设计InnoDB的大叔就很不情愿的在RC隔离级别也引入了gap锁。

我们也来做一个实验，现在假设上边的T1和T2都回滚了，现在将隔离级别调至RC，重新开启事务进行测试。

  1. mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  2. Query OK, 0 rows affected (0.01 sec)
  4. # 事务T1
  5. mysql> BEGIN;
  6. Query OK, 0 rows affected (0.00 sec)
  8. mysql> INSERT INTO hero VALUES(30, 'x荀彧', '魏');
  9. ERROR 1062 (23000): Duplicate entry 'x荀彧' for key 'uk_name'

然后执行SHOW ENGINE INNODB STATUS语句看一下T1加了什么锁：

可以看到即使现在T1的隔离级别为RC，T1仍然给name列值为'x荀彧'的二级索引记录添加了S型next-key锁（图中红框中的lock mode S）。

如果我们的INSERT语句还带有ON DUPLICATE KEY... 这样的子句，如果遇到主键值或者唯一二级索引列值重复的情况，会对B+树中已存在的相同键值的记录加X型锁，而不是S型锁（不过具体锁的具体类型是和前面描述一样的）。

好了，又到了看代码求证时间了，我们看一下吧：

row_ins_scan_sec_index_for_duplicate是检测唯一二级索引列值是否重复的函数，具体加锁的代码如下所示：

如上图所示，在遇到唯一二级索引列重复的情况时：

不过不论是那种情况，添加的lock_typed的值都是LOCK_ORDINARY，表示next-key锁。

在主键重复时INSERT语句的加锁代码我们就不列举了。

3. 外键检查时

当我们向子表中插入记录时，我们分两种情况讨论：

由于外键不太常用，例子和代码就都不举例了，有兴趣的小伙伴可以打开《MySQL是怎样运行的：从根儿上理解MySQL》查看例子。

死锁要出场了

好了，基础知识预习完了，该死锁出场了。

看下边这个平平无奇的INSERT语句：

  1. INSERT INTO hero(name, country) VALUES('g关羽', '蜀'), ('d邓艾', '魏');

这个语句用来插入两条记录，不论是在RC，还是RR隔离级别，如果两个事务并发执行它们是有一定几率触发死锁的。为了稳定复现这个死锁，我们把上边一条语句拆分成两条语句：

  1. INSERT INTO hero(name, country) VALUES('g关羽', '蜀');
  2. INSERT INTO hero(name, country) VALUES('d邓艾', '魏');

拆分前和拆分后起到的作用是相同的，只不过拆分后我们可以人为的控制插入记录的时机。如果T1和T2的执行顺序是这样的：

也就是：

可见，T1持有的name值为g关羽的隐式锁已经被转换为显式锁（X型正经记录锁，lock_mode X locks rec but not gap）；T2正在等待获取一个S型next-key锁（lock mode S waiting）。

很显然，name值为'd邓艾'的二级索引记录所在位置的下一条二级索引记录的name值应该是'g关羽'（按照汉语拼音排序）。那么在T1插入name值为d邓艾的二级索引记录时，就需要看一下name值为'g关羽'的二级索引记录上有没有被别的事务加gap锁。

有同学想说：目前只有T2想在name值为'g关羽'的二级索引记录上添加S型next-key锁（next-key锁包含gap锁），但是T2并没有获取到锁呀，目前正在等待状态。那么T1不是能顺利插入name值为'g关羽'的二级索引记录么？

我们看一下执行结果：

  1. # 事务T2
  2. mysql> INSERT INTO hero(name, country) VALUES('g关羽', '蜀');
  3. ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

很显然，触发了一个死锁，T2被InnoDB回滚了。

这是为啥呢？T2明明没有获取到name值为'g关羽'的二级索引记录上的S型next-key锁，为啥T1还不能插入入name值为d邓艾的二级索引记录呢？

这我们还得回到代码上来，看一下插入新记录时是如何判断锁是否冲突的：

看一下画红框的注释，意思是：只要别的事务生成了一个显式的gap锁的锁结构，不论那个事务已经获取到了该锁(granted)，还是正在等待获取（waiting），当前事务的INSERT操作都应该被阻塞。

回到我们的例子中来，就是T2已经在name值为'g关羽'的二级索引记录上生成了一个S型next-key锁的锁结构，虽然T2正在阻塞（尚未获取锁），但是T1仍然不能插入name值为d邓艾的二级索引记录。

这样也就解释了死锁产生的原因：

两个事务相互等待对方释放锁，这样死锁也就产生了。

怎么解决这个死锁问题？

两个方案：

为啥这两个方案可行？屏幕前的大脑瓜是不是也该转一下分析一波呗~

binlog那些事儿（一）

标签： 公众号文章

上一篇文章中有同学留言说想让小孩子写写MySQL的两阶段提交。

动手写的时候想到：是不是很多同学连XA事务是什么也不清楚，甚至很多同学连binlog是什么也不清楚～

好吧，这篇文章先来唠叨一下MySQL中的binlog到底是什么。

binlog的作用

binlog是binary log的缩写，即二进制日志。binlog中记载了数据库发生的变化，比方说新建了一个数据库或者表、表结构发生改变、表中的数据发生了变化时都会记录相应的binlog日志。

binlog主要用在下边两个方面：

现在人们张口闭口就是亿级并发，虽然是夸张，但单台物理机器所能同时处理的请求是有限的却是一个事实。为了提高并发处理请求的能力，一般将MySQL服务部署在多台物理机器中，这些服务器中维护相同的数据副本。

其中一个典型的部署方案就是一主多从，即一台主服务器（Master）和多台从服务器（Slave）。对于改变数据库状态的请求（DDL、DML等），就将它们发送给主服务器，对于单纯的查询（如SELECT语句）请求，就将它们发送给从服务器。为了让各个从服务器中存储的数据和主服务器中存储的数据一致，每当我们改变了主服务器中的数据后，就需要将改变的信息同步给各个从服务器。binlog日志中正好记录了数据库发生的各种改变的信息，从服务器读取主服务器产生的binlog日志，然后执行这些binlog日志中所记录的数据库变化语句，从而达到主从服务器数据一致的效果。

怎么配置binlog

MySQL服务器并不一定会生成binlog日志，我们可以通过查看log_bin系统变量来判断当前MySQL服务器是否生成binlog日志：

  1. mysql> show variables like 'log_bin';
  2. +---------------+-------+
  3. | Variable_name | Value |
  4. +---------------+-------+
  5. | log_bin       | ON    |
  6. +---------------+-------+
  7. 1 row in set, 1 warning (0.02 sec)

上例中bin_log系统变量的值为ON，表明当前服务器生成binlog，若为OFF表明当前服务器不生成binlog。

如果当前服务器不生成binlog，我们想开启binlog，那么就需要重启服务器，设置log-bin启动选项：

  1. --log-bin[=base_name]

binlog日志并不是仅写到一个文件中，而是写入一组文件中，这组文件的命名是这样的：

  1. basename.000001
  2. basename.000002
  3. basename.000003
  4. basename.000004
  5. ...

也就是这组日志文件名称都包含一个basename，然后以一个数字结尾。

启动选项log-bin[=base_name]中的base_name就是这组binlog日志文件名称都包含的部分。如果我们不指定base_name（即单纯的使用--log-bin），那MySQL服务器会默认将主机名-bin作为binlog日志文件的basename。

我们看一下例子。

如果启动服务器的命令是：

  1. mysqld --log-bin

表示开启binlog，并将binlog写入MySQL服务器的数据目录下。我的主机名是xiaohaizi，那MySQL服务器程序生成的binlog日志文件名就像是这样：

  1. xiaohaizi-bin.000001
  2. xiaohaizi-bin.000002
  3. xiaohaizi-bin.000003
  4. xiaohaizi-bin.000004
  5. ...

如果启动命令是：

  1. mysqld --log-bin=xx

表示开启binlog，并将binlog写入MySQL服务器的数据目录下，binlog日志文件名就像是这样：

  1. xx.000001
  2. xx.000002
  3. xx.000003
  4. xx.000004
  5. ...

我们可以在将启动选项log-bin[=base_name]的base_name指定为一个绝对路径，那么binlog日志就不会被放到默认的数据目录中，而是写到我们指定的绝对路径下了。比方说启动命令是：

  1. mysqld --log-bin=/Users/xiaohaizi/xx

这样binlog日志就会被写入/Users/xiaohaizi/路径下，binlog日志文件名就像是这样：

  1. xx.000001
  2. xx.000002
  3. xx.000003
  4. xx.000004
  5. ...

小贴士：


log-bin启动选项也可以放在配置文件中，我们这里就不赘述了。

binlog在文件系统中的内容

我们刚强调，binlog日志不是单个文件，而是一组包含共同basename的文件。比方说现在我的机器上有以下4个binlog文件：

  1. xiaohaizi-bin.000001
  2. xiaohaizi-bin.000002
  3. xiaohaizi-bin.000003
  4. xiaohaizi-bin.000004

这些binlog文件并不能直接被当作文本打开，毕竟人家的名字是binlog，存储的是二进制数据。

除了真正存储binlog日志的文件外，MySQL服务器还会在相同的路径下生成一个关于binlog的索引文件，在我的系统上它的名称就是：

  1. xiaohaizi-bin.index

这个索引文件是一个文本文件，我们可以直接打开：

  1. shell> cat xiaohaizi-bin.index
  2. ./xiaohaizi-bin.000001
  3. ./xiaohaizi-bin.000002
  4. ./xiaohaizi-bin.000003
  5. ./xiaohaizi-bin.000004

可以看到，这个索引文件只是简单的将各个binlog文件的路径存储了起来而已。

查看binlog的语句

binlog中记录数据库发生更改的各种事件（events），这些事件的种类非常多，完整的事件类型如下所示：

  1. enum Log_event_type { 
  2.   UNKNOWN_EVENT= 0, 
  3.   START_EVENT_V3= 1, 
  4.   QUERY_EVENT= 2, 
  5.   STOP_EVENT= 3, 
  6.   ROTATE_EVENT= 4, 
  7.   INTVAR_EVENT= 5, 
  8.   LOAD_EVENT= 6, 
  9.   SLAVE_EVENT= 7, 
  10.   CREATE_FILE_EVENT= 8, 
  11.   APPEND_BLOCK_EVENT= 9, 
  12.   EXEC_LOAD_EVENT= 10, 
  13.   DELETE_FILE_EVENT= 11, 
  14.   NEW_LOAD_EVENT= 12, 
  15.   RAND_EVENT= 13, 
  16.   USER_VAR_EVENT= 14, 
  17.   FORMAT_DESCRIPTION_EVENT= 15, 
  18.   XID_EVENT= 16, 
  19.   BEGIN_LOAD_QUERY_EVENT= 17, 
  20.   EXECUTE_LOAD_QUERY_EVENT= 18, 
  21.   TABLE_MAP_EVENT = 19, 
  22.   PRE_GA_WRITE_ROWS_EVENT = 20, 
  23.   PRE_GA_UPDATE_ROWS_EVENT = 21, 
  24.   PRE_GA_DELETE_ROWS_EVENT = 22, 
  25.   WRITE_ROWS_EVENT = 23, 
  26.   UPDATE_ROWS_EVENT = 24, 
  27.   DELETE_ROWS_EVENT = 25, 
  28.   INCIDENT_EVENT= 26, 
  29.   HEARTBEAT_LOG_EVENT= 27, 
  30.   IGNORABLE_LOG_EVENT= 28,
  31.   ROWS_QUERY_LOG_EVENT= 29,
  32.   WRITE_ROWS_EVENT = 30,
  33.   UPDATE_ROWS_EVENT = 31,
  34.   DELETE_ROWS_EVENT = 32,
  35.   GTID_LOG_EVENT= 33,
  36.   ANONYMOUS_GTID_LOG_EVENT= 34,
  37.   PREVIOUS_GTIDS_LOG_EVENT= 35, 
  38.   ENUM_END_EVENT 
  39.   /* end marker */ 
  40. };

其中的一些我们熟悉的事件：

像创建、修改数据库或者表结构这些语句也都可以找到对应类型的事件，我们这里就不一一展开了。

为了查看我们的binlog中包含了哪些事件，可以使用下边这个语句（带中括号[]的表示可以省略的语句）：

  1. SHOW BINLOG EVENTS
  2.    [IN 'log_name']
  3.    [FROM pos]
  4.    [LIMIT [offset,] row_count]

其中：

下边该做一下测试了。

如果我们直接执行SHOW BINLOG EVENTS，表示查看第1个binlog日志文件的内容，在我的机器上就是xiaohaizi-bin.000001这个文件的内容：

  1. mysql> SHOW BINLOG EVENTS;
  2. +----------------------+-----+----------------+-----------+-------------+---------------------------------------+
  3. | Log_name             | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
  4. +----------------------+-----+----------------+-----------+-------------+---------------------------------------+
  5. | xiaohaizi-bin.000001 |   4 | Format_desc    |         3 |         123 | Server ver: 5.7.21-log, Binlog ver: 4 |
  6. | xiaohaizi-bin.000001 | 123 | Previous_gtids |         3 |         154 |                                       |
  7. | xiaohaizi-bin.000001 | 154 | Anonymous_Gtid |         3 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
  8. | xiaohaizi-bin.000001 | 219 | Query          |         3 |         296 | BEGIN                                 |
  9. | xiaohaizi-bin.000001 | 296 | Table_map      |         3 |         367 | table_id: 138 (xiaohaizi.s1)          |
  10. | xiaohaizi-bin.000001 | 367 | Update_rows    |         3 |         634 | table_id: 138 flags: STMT_END_F       |
  11. | xiaohaizi-bin.000001 | 634 | Xid            |         3 |         665 | COMMIT /* xid=65 */                   |
  12. | xiaohaizi-bin.000001 | 665 | Stop           |         3 |         688 |                                       |
  13. +----------------------+-----+----------------+-----------+-------------+---------------------------------------+
  14. 8 rows in set (0.01 sec)

可以看到共输出了8个列，其中：

如果您对MySQL了解不多的话，那上边执行SHOW BINLOG EVENTS语句的输出的大部分事件可能都比较懵，可能能看懂两个事件：

好了现在先不细究各种类型的事件都代表什么，我们目前只需要知道binlog日志是由若干个事件组成的就好了。

如果我们想看其他binlog日志的详细情况，那就需要用到IN子句了：

  1. mysql> SHOW BINLOG EVENTS IN 'xiaohaizi-bin.000004';
  2. +----------------------+-----+----------------+-----------+-------------+-------------------------------------------+
  3. | Log_name             | Pos | Event_type     | Server_id | End_log_pos | Info                                      |
  4. +----------------------+-----+----------------+-----------+-------------+-------------------------------------------+
  5. | xiaohaizi-bin.000004 |   4 | Format_desc    |         3 |         123 | Server ver: 5.7.21-log, Binlog ver: 4     |
  6. | xiaohaizi-bin.000004 | 123 | Previous_gtids |         3 |         154 |                                           |
  7. | xiaohaizi-bin.000004 | 154 | Anonymous_Gtid |         3 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'      |
  8. | xiaohaizi-bin.000004 | 219 | Query          |         3 |         327 | use `xiaohaizi`; create table tt1 (c int) |
  9. | xiaohaizi-bin.000004 | 327 | Anonymous_Gtid |         3 |         392 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'      |
  10. | xiaohaizi-bin.000004 | 392 | Query          |         3 |         469 | BEGIN                                     |
  11. | xiaohaizi-bin.000004 | 469 | Table_map      |         3 |         520 | table_id: 167 (xiaohaizi.tt1)             |
  12. | xiaohaizi-bin.000004 | 520 | Write_rows     |         3 |         560 | table_id: 167 flags: STMT_END_F           |
  13. | xiaohaizi-bin.000004 | 560 | Xid            |         3 |         591 | COMMIT /* xid=71 */                       |
  14. | xiaohaizi-bin.000004 | 591 | Anonymous_Gtid |         3 |         656 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'      |
  15. | xiaohaizi-bin.000004 | 656 | Query          |         3 |         733 | BEGIN                                     |
  16. | xiaohaizi-bin.000004 | 733 | Table_map      |         3 |         784 | table_id: 167 (xiaohaizi.tt1)             |
  17. | xiaohaizi-bin.000004 | 784 | Update_rows    |         3 |         830 | table_id: 167 flags: STMT_END_F           |
  18. | xiaohaizi-bin.000004 | 830 | Xid            |         3 |         861 | COMMIT /* xid=73 */                       |
  19. +----------------------+-----+----------------+-----------+-------------+-------------------------------------------+
  20. 14 rows in set (0.00 sec)

这样我们就看到了xiaohaizi-bin.000004这个binlog日志文件中存储了哪些事件了。

大家可以自行测试一下FROM子句和LIMIT子句的用法，这里就不展示了。

mysqlbinlog工具的使用

由于binlog是二进制格式的，我们不能直接以文本的形式查看。使用SHOW BINLOG EVENTS又只能看到粗略的信息，如果我们想查看binlog日志文件的详细信息的话，就需要使用MySQL给我们提供的实用工具——mysqlbinlog。

像mysqld、mysql这些可执行文件一样，mysqlbinlog也被放在了MySQL安装目录下的bin目录下。

我们可以将想查看的binlog日志文件路径作为mysqlbinlog的参数，就能查看文本形式的事件详细信息了。比方说我们看一下xiaohaizi-bin.000001：

  1. shell> mysqlbinlog ./xiaohaizi-bin.000001
  2. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
  3. /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
  4. DELIMITER /*!*/;
  5. # at 4
  6. #211202 20:01:14 server id 3  end_log_pos 123 CRC32 0xa308715b  Start: binlog v 4, server v 5.7.21-log created 211202 20:01:14 at startup
  7. ROLLBACK/*!*/;
  8. BINLOG '
  9. irWoYQ8DAAAAdwAAAHsAAAAAAAQANS43LjIxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
  10. AAAAAAAAAAAAAAAAAACKtahhEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
  11. AVtxCKM=
  12. '/*!*/;
  13. # at 123
  14. #211202 20:01:14 server id 3  end_log_pos 154 CRC32 0x0d6a1ce6  Previous-GTIDs
  15. # [empty]
  16. # at 154
  17. #211202 20:07:07 server id 3  end_log_pos 219 CRC32 0xab157b64  Anonymous_GTID  last_committed=0    sequence_number=1   rbr_only=yes
  18. /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
  19. SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
  20. # at 219
  21. #211202 20:07:07 server id 3  end_log_pos 296 CRC32 0xedb6b609  Query   thread_id=2 exec_time=0 error_code=0
  22. SET TIMESTAMP=1638446827/*!*/;
  23. SET @@session.pseudo_thread_id=2/*!*/;
  24. SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
  25. SET @@session.sql_mode=1436549152/*!*/;
  26. SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
  27. /*!\C utf8 *//*!*/;
  28. SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
  29. SET @@session.lc_time_names=0/*!*/;
  30. SET @@session.collation_database=DEFAULT/*!*/;
  31. BEGIN
  32. /*!*/;
  33. # at 296
  34. #211202 20:07:07 server id 3  end_log_pos 367 CRC32 0x43cd57ee  Table_map: `xiaohaizi`.`s1` mapped to number 138
  35. # at 367
  36. #211202 20:07:07 server id 3  end_log_pos 634 CRC32 0xe2981d9e  Update_rows: table id 138 flags: STMT_END_F
  38. BINLOG '
  39. 67aoYRMDAAAARwAAAG8BAAAAAIoAAAAAAAEACXhpYW9oYWl6aQACczEACAMPDw8PDw8PDiwBLAEs
  40. ASwBLAEsASwB/u5XzUM=
  41. 67aoYR8DAAAACwEAAHoCAAAAAIoAAAAAAAEAAgAI//8ApAMAABgAZmt3YW91b2syY2sxY2RlMzA2
  42. bzZ2NHcxCQAxMzA4NzI2NzgTAHBqdHFxc2dsMngxMjd4MWZjdngBAG0MAHBycmp3bmtxbjV1aRoA
  43. NHN3cWJsNXEzd3V2bzUyZGdscmI1eWlmencJAGxzMjFoNHZhNwCkAwAAGABma3dhb3VvazJjazFj
  44. ZGUzMDZvNnY0dzEJADEzMDg3MjY3OBMAcGp0cXFzZ2wyeDEyN3gxZmN2eAEAbQwAcHJyandua3Fu
  45. NXVpGgA0c3dxYmw1cTN3dXZvNTJkZ2xyYjV5aWZ6dwIAeHieHZji
  46. '/*!*/;
  47. # at 634
  48. #211202 20:07:07 server id 3  end_log_pos 665 CRC32 0xe586ffeb  Xid = 65
  49. COMMIT/*!*/;
  50. # at 665
  51. #211202 20:07:19 server id 3  end_log_pos 688 CRC32 0x8c69bad2  Stop
  52. SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
  53. DELIMITER ;
  54. # End of log file
  55. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
  56. /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

哇唔，好大一片输出！

其中以# at xx开头的表示这是一个事件的开始，诸如：

...

具体的格式我们就先不展开了。

binlog的文件格式

本来我们还想唠叨一下binlog日志文件是怎么设计的，每一个事件是怎样表示的。可是回头一看好像已经写了很多内容了，小孩子太累了，之后抽时间再给大家写吧...

还有MySQL的主从复制是怎么跑起来的？XA事务是什么？为啥要两阶段提交？

再会～

binlog那些事儿（二）

标签： 公众号文章

读完上一篇文章，我们应该知道：

掌握了上述内容之后，我们可以继续展开了。

binlog日志版本

binlog是自MySQL 3.23.14版本开始诞生的，到现在为止，共经历了4个版本：

其中的v4版本从MySQL 5.0就开始使用，直到今天。

所以本文着重介绍v4版本的binlog格式，其他版本就不关注了。

binlog日志文件结构概览

废话少说，先看一下一个binlog日志文件的基本格式：

从上图中可以看出：

小贴士：


0xfe626963中的0x626963的ascii码是'bin'，0xfe626963也被称作魔数（magic number），如果一个文件不以0xfe626963开头，那这个文件肯定不算是一个binlog日志。很多软件都会在磁盘文件的某个地方添加一个类似的魔数来表明该文件是本软件处理的文件格式，比方说Intel处理器的BIOS会将磁盘上的第一个扇区加载到内存中，这个扇区的最后两个字节必须为魔数0x55aa，Java的class文件字节码的开头四个字节为魔数0xCAFEBABE。

其中，每个事件都可以被分成event header和event data两个部分，我们以上图的事件2为例展示一下：

其中：

event header结构

每个事件都会包括一个通用的event header，我们看一下这个event header的结构：

event header中包含了如下几部分内容：

event data

event data由2部分组成，分别是：

不过并不是所有事件都有这两个部分，有的事件可以仅有其中的一个部分或者两个部分都没有。

上一篇文章中唠叨过，MySQL中支持几十种binlog事件，不同事件具有不同的event data部分。

我们先看一下binlog的事件类型有多少（上一篇文章中引用MySQL internal文档中的内容，有点陈旧，所以这次直接从MySQL5.7.22的源码中获取Log_event_type结构）：

  1. enum Log_event_type
  2. {
  3.   /**
  4.     Every time you update this enum (when you add a type), you have to
  5.     fix Format_description_event::Format_description_event().
  6.   */
  7.   UNKNOWN_EVENT= 0,
  8.   START_EVENT_V3= 1,
  9.   QUERY_EVENT= 2,
  10.   STOP_EVENT= 3,
  11.   ROTATE_EVENT= 4,
  12.   INTVAR_EVENT= 5,
  13.   LOAD_EVENT= 6,
  14.   SLAVE_EVENT= 7,
  15.   CREATE_FILE_EVENT= 8,
  16.   APPEND_BLOCK_EVENT= 9,
  17.   EXEC_LOAD_EVENT= 10,
  18.   DELETE_FILE_EVENT= 11,
  19.   /**
  20.     NEW_LOAD_EVENT is like LOAD_EVENT except that it has a longer
  21.     sql_ex, allowing multibyte TERMINATED BY etc; both types share the
  22.     same class (Load_event)
  23.   */
  24.   NEW_LOAD_EVENT= 12,
  25.   RAND_EVENT= 13,
  26.   USER_VAR_EVENT= 14,
  27.   FORMAT_DESCRIPTION_EVENT= 15,
  28.   XID_EVENT= 16,
  29.   BEGIN_LOAD_QUERY_EVENT= 17,
  30.   EXECUTE_LOAD_QUERY_EVENT= 18,
  32.   TABLE_MAP_EVENT = 19,
  34.   /**
  35.     The PRE_GA event numbers were used for 5.1.0 to 5.1.15 and are
  36.     therefore obsolete.
  37.    */
  38.   PRE_GA_WRITE_ROWS_EVENT = 20,
  39.   PRE_GA_UPDATE_ROWS_EVENT = 21,
  40.   PRE_GA_DELETE_ROWS_EVENT = 22,
  42.   /**
  43.     The V1 event numbers are used from 5.1.16 until mysql-trunk-xx
  44.   */
  45.   WRITE_ROWS_EVENT_V1 = 23,
  46.   UPDATE_ROWS_EVENT_V1 = 24,
  47.   DELETE_ROWS_EVENT_V1 = 25,
  49.   /**
  50.     Something out of the ordinary happened on the master
  51.    */
  52.   INCIDENT_EVENT= 26,
  54.   /**
  55.     Heartbeat event to be send by master at its idle time
  56.     to ensure master's online status to slave
  57.   */
  58.   HEARTBEAT_LOG_EVENT= 27,
  60.   /**
  61.     In some situations, it is necessary to send over ignorable
  62.     data to the slave: data that a slave can handle in case there
  63.     is code for handling it, but which can be ignored if it is not
  64.     recognized.
  65.   */
  66.   IGNORABLE_LOG_EVENT= 28,
  67.   ROWS_QUERY_LOG_EVENT= 29,
  69.   /** Version 2 of the Row events */
  70.   WRITE_ROWS_EVENT = 30,
  71.   UPDATE_ROWS_EVENT = 31,
  72.   DELETE_ROWS_EVENT = 32,
  74.   GTID_LOG_EVENT= 33,
  75.   ANONYMOUS_GTID_LOG_EVENT= 34,
  77.   PREVIOUS_GTIDS_LOG_EVENT= 35,
  79.   TRANSACTION_CONTEXT_EVENT= 36,
  81.   VIEW_CHANGE_EVENT= 37,
  83.   /* Prepared XA transaction terminal event similar to Xid */
  84.   XA_PREPARE_LOG_EVENT= 38,
  85.   /**
  86.     Add new events here - right above this comment!
  87.     Existing events (except ENUM_END_EVENT) should never change their numbers
  88.   */
  89.   ENUM_END_EVENT /* end marker */
  90. };

可见在MySQL 5.7.22这个版本中，共支持38种不同的binlog事件类型。把每一种事件格式都唠叨清楚要花费很多篇幅，并且没有多大的必要，我们下边只举一个具体的例子进行描述。

举一个具体的例子——格式描述事件

每个binlog日志文件都以格式描述事件作为第一个事件，它对应的Log_event_type就是FORMAT_DESCRIPTION_EVENT。我们看一下这种事件的结构：

从图中我们可以知道，格式描述事件共占用119字节，是由event header和event data两部分构成的，其中event header是各个事件都有的部分，我们上边详细唠叨过event header中各个字段的含义，这里就不赘述了。另外，在event data部分，格式描述事件的event data中只有固定长度部分，没有可变长度部分，其中的各个字段含义如下：

唠叨了很多，大家真正打开一个binlog日志文件来看一下：

  1. 魔数：      FE62696E 
  3. timestamp:     8AB5A861  
  4. typecode:     0F          
  5. server_id:     03000000 
  6. event_length:     77000000 
  7. next_postion:     7B000000 
  8. flags:      0000     
  9. binlog_version:    0400   
  10. server_version:    352E37 2E32312D 6C6F6700 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 000000  
  11. create_timestamp:    8AB5A861 
  12. header_length:     13       
  13. post-header length(共38种): 380D0008 00120004 04040412 00005F00 041A0800 00000808 08020000 000A0A0A 2A2A0012 3400
  14. checksum_alg：    01 
  15. checksum：      5B7108A3

小贴士：


其他事件的event data部分大家可以参考一下MySQL internal文档。另外，也可以使用mysqlbinlog，配合--hexdump启动选项来直接分析binlog的二进制格式。

基于语句（Statement）和基于行(Row)的binlog

同一条SQL语句，随着启动选项binlog-format的不同，可能生成不同类型的binlog事件：

小贴士：


我们也可以通过修改会话级别的binlog_format系统变量的形式来修改只针对本客户端执行语句生成的binlog日志的格式。

基于语句的binlog

假如服务器启动时添加了--binlog-format=STATEMENT启动选项，我们执行如下语句：

  1. UPDATE s1 SET common_field = 'xx' WHERE id > 9990;

然后使用mysqlbinlog实用工具查看一下相应的binlog内容：

  1. mysqlbinlog --verbose xiaohaizi-bin.000007
  2. ...这里省略了很多内容
  3. # at 308
  4. #211207 21:00:27 server id 3  end_log_pos 440 CRC32 0x713f80ae  Query thread_id=2 exec_time=0 error_code=0
  5. use `xiaohaizi`/*!*/;
  6. SET TIMESTAMP=1638882027/*!*/;
  7. update s1 set common_field= 'xx' where id > 9990
  8. /*!*/;
  9. ...这里省略了很多内容

可见，基于语句的binlog只将更新语句是什么记录下来了。

基于行的binlog

假如服务器启动时添加了--binlog-format=ROW启动选项，我们执行如下语句：

  1. UPDATE s1 SET common_field = 'xxx' WHERE id > 9990;

然后使用mysqlbinlog实用工具查看一下相应的binlog内容：

  1. mysqlbinlog --verbose xiaohaizi-bin.000008
  2. ...这里省略了很多内容
  3. ### UPDATE `xiaohaizi`.`s1`
  4. ### WHERE
  5. ###   @1=9991
  6. ###   @2='7cgwfh14w6nql61pvult6ok0ccwe'
  7. ###   @3='799105223'
  8. ###   @4='c'
  9. ###   @5='gjjiwstjysv1lgx'
  10. ###   @6='zg1hsvqrtyw2pgxgg'
  11. ###   @7='y244x02'
  12. ###   @8='xx'
  13. ### SET
  14. ###   @1=9991
  15. ###   @2='7cgwfh14w6nql61pvult6ok0ccwe'
  16. ###   @3='799105223'
  17. ###   @4='c'
  18. ###   @5='gjjiwstjysv1lgx'
  19. ###   @6='zg1hsvqrtyw2pgxgg'
  20. ###   @7='y244x02'
  21. ###   @8='xxx'
  22. ### UPDATE `xiaohaizi`.`s1`
  23. ### WHERE
  24. ###   @1=9992
  25. ###   @2='2sfq3oftc'
  26. ###   @3='815047282'
  27. ###   @4='ub'
  28. ###   @5='73hw14kbaaoa'
  29. ###   @6='fxnqzef3rrpc7qzxcjsvt14nypep4rqi'
  30. ###   @7='10vapb6'
  31. ###   @8='xx'
  32. ### SET
  33. ###   @1=9992
  34. ###   @2='2sfq3oftc'
  35. ###   @3='815047282'
  36. ###   @4='ub'
  37. ###   @5='73hw14kbaaoa'
  38. ###   @6='fxnqzef3rrpc7qzxcjsvt14nypep4rqi'
  39. ###   @7='10vapb6'
  40. ###   @8='xxx'
  41. ...这里省略了很多内容

可见，基于行的binlog将更新语句执行过程中每一条记录更新前后的值都记录下来了。

基于语句的binlog的问题

在有主从复制的场景中，使用基于语句的日志可能会造成主服务器和从服务器维护的数据不一致的情况。

比方说我们有一个表t：

  1. CREATE TABLE t (
  2.     id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  3.     c VARCHAR(100),
  4.     PRIMARY KEY(ID)
  5. );

如果我们执行如下语句：

  1. INSERT INTO t(c) SELECT c FROM other_table;

这个语句是想将other_table表中列c的值都插入到表t的列c中，而表t的id列是自增列，可以自动生成。

如果主库和从库的服务器执行SELECT c FROM other_table返回记录的顺序不同的话（不同服务器版本、不同的系统变量配置都可能导致同一条语句返回结果的顺序不同），那么针对表t相同id值的记录来说，列c就可能具有不同的值，这就会造成主从之间数据的不一致。

而如果将binlog的格式改为基于行的日志的话，由于主库在执行完语句后将该语句插入的每条完整的记录都写入binlog日志，就不会造成主从之间不一致了。

好像又写了很多枯燥的内容...
下次不写这么枯燥的了，我看着都有点儿烦~

redo、undo、buffer pool、binlog，谁先谁后，有点儿乱

标签： 公众号文章

这篇文章我们来讨论一下一条DML语句从客户端发出后，服务器都做了哪些处理。

小贴士：


虽然SELECT语句的处理也很复杂，但SELECT语句并不会修改数据库中的数据，也就不会记录诸如redo、undo、binlog这些日志，本文主要是想讨论redo、undo、binlog这些日志是在什么时候生成的，啥时候写到磁盘的。

为了增强文章的真实性（总是有一些小伙伴问小孩子为什么和CSDN上的某某文章陈述的不一样），我们会列举一些关键步骤的代码，本文用到的源码版本为MySQL 5.7.22。

另外，我们假设屏幕前的小伙伴已经知道什么是buffer pool，什么是redo日志，什么是undo日志，什么是binlog，以及MySQL为什么需要它们。我们不会再展开各种日志的格式、写入方式​等细节问题，有不清楚的小伙伴可以查看《MySQL是怎样运行的：从根儿上理解MySQL》，包教包会，不会来问小孩子。

预备知识

我们讨论的是基于InnoDB存储引擎的表，数据会被保存在硬盘上的表空间（文件系统中的一个或多个文件）中。

InnoDB会将磁盘中的数据看成是若干个页的集合，页的大小默认是16KB。其中某些页面用于存储关于系统的一些属性，某些页面用于存储undo日志，某些页面用于存储B+树的节点（也就是包含记录的页面），反正总共有十来种不同类型的页面。

不过不论是什么类型的页面，每当我们从页面中读取或写入数据时，都必须先将其从硬盘上加载到内存中的buffer pool中（也就是说内存中的页面其实就是硬盘中页面的一个副本），然后才能对内存中页面进行读取或写入。如果要修改内存中的页面，为了减少磁盘I/O，修改后的页面并不立即同步到磁盘，而是作为脏页继续呆在内存中，等待后续合适时机将其刷新到硬盘（一般是有后台线程异步刷新）。

准备工作

为了故事的顺利发展，我们先建立一个表：

  1. CREATE TABLE hero (
  2.     number INT,
  3.     name VARCHAR(100),
  4.     country varchar(100),
  5.     PRIMARY KEY (number),
  6.     KEY idx_name (name)
  7. ) Engine=InnoDB CHARSET=utf8;

然后向这个表里插入几条记录：

  1. INSERT INTO hero VALUES
  2.     (1, 'l刘备', '蜀'),
  3.     (3, 'z诸葛亮', '蜀'),
  4.     (8, 'c曹操', '魏'),
  5.     (15, 'x荀彧', '魏'),
  6.     (20, 's孙权', '吴');

然后现在hero表就有了两个索引（一个二级索引，一个聚簇索引），示意图如下：

执行计划的生成

假设我们想执行下边这条UPDATE语句：

  1. UPDATE hero SET country = '汉' WHERE name >= 'x荀彧';

MySQL优化器首先会分析一下使用不同索引执行查询的成本，然后选取成本最低的那个索引去执行查询。

对于上述语句来说，可选的执行方案有2种：

优化器会计算上述两种方案的成本，选取成本最低的方案作为最终的执行计划。

我们作为用户，可以通过EXPLAIN语句来看一下这个语句的执行计划（当然也可以通过MySQL提供的optimizer trace功能查看具体执行计划分析流程）：

  1. mysql> explain UPDATE hero SET country = '汉' WHERE name >= 'x荀彧';
  2. +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
  3. | id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
  4. +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
  5. |  1 | UPDATE      | hero  | NULL       | range | idx_name      | idx_name | 303     | const |    2 |   100.00 | Using where |
  6. +----+-------------+-------+------------+-------+---------------+----------+---------+-------+------+----------+-------------+
  7. 1 row in set, 1 warning (0.01 sec)

可以看到，MySQL优化器决定采用方案二，即扫描二级索引idx_name在['x荀彧', +∞)这个扫描区间种的记录。

真正开始执行

MySQL分为server层和存储引擎层，我们前边的多篇文章有唠叨这两层之间的关系。考虑到没有看过前边文章的小伙伴，我们再不厌其烦的唠叨一下在执行上述UPDATE语句时server层和InnoDB层之间是如何沟通的。优化器的执行计划中得到了若干个扫描区间（本例中只有1个扫描区间['x荀彧', +∞)），针对每个扫描区间，都会执行下边的步骤：

处理扫描区间的第一条记录

处理扫描区间的第二条记录

处理扫描区间的剩余记录

该扫描区间中的其他记录的处理就和第2条记录的处理过程是一样一样的了，这里就不赘述了。

详细的更新过程

MySQL使用mysql_update函数处理我们上述的更新语句：

最主要的处理流程写在了一个循环里：

上图所示的while循环就是依次处理各条记录的过程。

其中info.read_record是用于获取扫描区间的一条记录，读取到该记录后随后展开详细的更新操作。处理完了之后再回到这个while循环的起点，通过info.read_record获取下一条记录，然后进行详细的更新操作。

也就是说，其实处理每一条记录的过程都是类似的，只不过定位扫描区间的第一条记录会有点儿麻烦（需要从B+树根页面一层一层向下找）。

我们下边聚焦于一条记录的更新过程，看看这个过程都发生了什么。

将记录所在的页面加载到buffer pool

我们想更新一条记录，首先就得在B+树中定位这条记录——即进行一次加锁读（上图中的info.read_record函数用于调用Innodb读取记录的接口，关于对一条记录加锁的过程我们在之前的文章中分析过，这里就不赘述了。）。

如果该记录所在的页面已经在内存的buffer pool中，那就可以直接读取，否则还需要将该记录所在的页面读取到内存中的buffer pool中。

小贴士：


再一次强调，不论我们想读写任何页面，都需要先将该页面从硬盘加载到buffer pool中。在定位扫描区间的第一条记录时，我们首先要读取B+树根页面中的记录，所以首先需要先把B+树根页面加载到buffer pool中，然后再读取下一层的页面，然后再读取下下层的页面，直到叶子节点。每当要读取的页面不在buffer pool中，都得将其先加载到buffer pool后才能使用。

Innodb使用row_search_mvcc处理读取一条记录的过程（不论是加锁读还是一致性读都调用这个函数），在该函数内btr_pcur_open_with_no_init用于从B+树定位记录：

在定位记录时就需要将记录所在的页面加载到buffer pool，完成这个过程的函数是：

检测更新前后记录是否一样

在mysql_update函数中，当通过info.read_record读取到一条记录之后，就要分析一下这条记录更新前后是否发生变化：

上图中的compare_records用于比较记录更新前后是否一样。

如果更新前和更新后的记录是一样的，那就直接跳过该记录，否则继续向下处理。

调用存储引擎接口进行更新

上图中的ha_update_row就是要存储引擎去更新记录，其中的table->record[1]代表旧记录，table->record[0]代表新记录。

更新聚簇索引记录

InnoDB会首先更新聚簇索引记录，然后再更新二级索引记录。

我们先看更新聚簇索引记录时都发生了什么。更新聚簇索引的函数如下所示：

下边首先会尝试在同一个页面中更新记录，这被称作乐观更新，调用btr_cur_optimistic_update函数：

如果不能在本页面中完成更新（比方说更新后的记录非常大啊，本页面容纳不下），就会尝试悲观更新：

本例中使用乐观更新即可。

记录undo日志

更新记录前，首先要记录相应的undo日志，调用trx_undo_report_row_operation来记录undo日志：

首先我们要知道，MySQL的undo日志是要写到一种专门存储undo日志的页面中的。如果一个事务写入的undo日志非常多，需要占用多个Undo页面，那这些页面会被串联成一个链表，称作Undo页面链表。

trx_undo_page_report_modify函数用于真正的向Undo页面中写入undo日志。另外，由于我们这里是在修改页面，一个事务执行过程中凡是修改页面的地方，都需要记录相应的redo日志，所以在这个函数的末尾，有一个记录修改这个Undo页面的redo日志的函数trx_undof_page_add_undo_rec_log：

有同学在这里肯定会疑惑：是先将undo日志写入Undo页面，然后再记录修改该页面对应的redo日志吗？

先说答案：是的。

不过这里修改后的页面并没有加入buffer pool的flush链表，记录的redo日志也没有加入到redo log buffer。当这个函数执行完后，才会：

上述过程是在mtr_commit中完成的：

小贴士：


设计MySQL的大叔把对底层页面的一次原子修改称作一个Mini Trasaction，即MTR。一个MTR中包含若干条redo日志，在崩溃恢复时，要么全部恢复该MTR对应的redo日志，要么全部不恢复。

也就是说实际上虽然先修改Undo页面，后写redo日志，但是此时InnoDB并不认为Undo页面是脏页，就不会将其刷新到硬盘，redo日志也没有写入到redo log buffer，这些redo日志也不会被刷新到redo日志文件。只有当MTR提交时，才先将redo日志复制到redo log buffer，再将修改的Undo页面加入到flush链表。

所以我们可以粗略的认为修改Undo页面的redo日志是先写的，而修改页面的过程是后发生的。

小贴士：


有后台线程不断的将redo log buffer中的redo日志刷新到硬盘的redo日志文件，也有后台线程不断的将buffer pool里的脏页（只有加入到flush链表后的页面才能算作是脏页）刷新到硬盘中的表空间中。设计InnoDB的大叔规定，在刷新一个脏页到硬盘时，该脏页对应的redo日志应该被先刷新到redo日志文件。而redo日志是顺序刷新的，也就是说，在刷新redo log buffer的某条redo日志时，在它之前的redo日志也都应该被刷新到redo日志文件。

修改页面内容

上一步骤是先把undo日志写到Undo页面中以及记录相应的redo日志，接下来该真正修改聚簇索引记录了。

首先更新系统字段trx_id以及roll_pointer：

然后真正的修改记录内容：

小贴士：


由于本例中的更新语句更新前后的各个字段占用的存储空间大小是不变的，所以可以直接就地（in place）更新。

然后记录更新的redo日志：

像向Undo页面写入undo日志一样，InnoDB规定更新一个页面中的一条记录也属于一个MTR。在该MTR提交时，也是先将MTR中的redo日志复制到redo log buffer，然后再将修改的页面加入到flush链表。

所以我们也可以认为在这个过程中，先记录修改页面的redo日志，然后再真正的修改页面。

至此，一条聚簇索引记录就更新完毕了。

更新二级索引记录

更新二级索引记录的函数如下所示：

更新二级索引记录时不会再记录undo日志，但由于是在修改页面内容，会先记录相应的redo日志。

由于本例子中并不会更新二级索引记录，所以就跳过本步骤了。

记录binlog

在一条更新语句执行完成后（也就是将所有待更新记录都更新完了），就需要该语句对应的binlog日志了（下图中的thd->binlog_query函数）：

不过值得注意的是，此时记录的binlog日志并不会被写到binlog日志文件中，而是被暂时保存到内存的某个地方，等之后事务提交的时候才会真正将该事物执行过程中产生的所有binlog统一写入binlog日志文件。

提交事务的时候

终于要唠叨到所谓的两阶段提交（two phase commit）啦～

在事务提交时，binlog才会被真正刷新到binlog日志文件中，redo日志也会被刷新到redo日志文件中。不过由于这个部分涉及较多的知识点，所以我们本篇先不唠叨了，留在下一篇里吧

哈哈>_>

总结

本篇文章唠叨了执行一条UPDATE语句过程中都发生了什么事情。当优化器分析出成本最小的执行计划后，就开始对执行计划中的各个扫描扫描区间中的记录进行更新。具体更新一条记录的流程如下：

  1. 先在B+树中定位到该记录（这个过程也被称作加锁读），如果该记录所在的页面不在buffer pool里，先将其加载到buffer pool里再读取。
  2. 读取到记录后判断记录更新前后是否一样，一样的话就跳过该记录，否则进行后续步骤。
  3. 首先更新聚簇索引记录。
    更新聚簇索引记录时：
    ①先向Undo页面写undo日志。不过由于这是在更改页面，所以修改Undo页面前需要先记录一下相应的redo日志。
    ②真正的更新记录。不过在真正更新记录前也需要记录相应的redo日志。
  4. 更新其他的二级索引记录。

至此，一条记录就更新完了。

然后开始记录该语句对应的binlog日志，此时记录的binlog并没有刷新到硬盘上的binlog日志文件，在事务提交时才会统一将该事务运行过程中的所有binlog日志刷新到硬盘。

剩下的就是所谓的两阶段提交的事情了，我们下节再会～

XA事务与两阶段提交

标签： 公众号文章

什么是分布式事务

我们平常使用事务的时候，基本流程是这样的：

小贴士：


如果auto_commit系统变量值为1，并且我们未显式使用BEGIN/START TRANSACTION开启事务，那MySQL也会将单条语句当作是一个事务来执行。

我们知道MySQL分为server层和存储引擎层，而事务具体是在存储引擎层实现的。有的存储引擎支持事务，有的不支持。

对于支持事务的存储引擎来说，它们提供了相应的开启事务、提交事务的接口。server层只需要调用这些接口，来让存储引擎执行事务。

除了MySQL自带的支持事务的存储引擎InnoDB外，其他一些公司也为MySQL开发了一些支持事务的存储引擎，比方说阿里的XEngine，Facebook的Rocksdb等。

在书写包含在一个事务中的语句时，不同语句可能会涉及不同存储引擎的表，这时如果我们想保持整个事务要么全部执行，要么全部不执行的话，本质上就需要保证各个存储引擎的事务全部提交，或者全部回滚。不能存在某些存储引擎事务提交了，某些存储引擎事务回滚了的情况。

稍微总结一下就是：我们有一个大的事务，我们可以称其为全局事务，这个全局事务由若干的小的事务组成。要实现这个大的事务，就必须让它对应的若干个小的事务全部完成，或者全部回滚。我们也可以把这个大的全局事务称作分布式事务。

除了上述涉及多个存储引擎的全局事务之外，分布式事务还有更多的应用场景。比方说我们的数据分布在多个MySQL服务器中；甚至有的数据分布在MySQL服务器中，有的数据分布在Oracle服务器中；甚至有些服务器在中国，有些服务器在美国。我们想完成一个操作，这个操作会更新多个系统里的数据，此时如果我们想让这个操作具有原子性，就需要保证让各个系统中的小事务要么全部提交，要么全部回滚。这时的这个跨多个系统的操作也可以被称作分布式事务。

跨行转账是一个典型的分布式事务的实例。各个银行都有自己的服务，如果狗哥在招商银行存了10块钱，他想把这10块钱转给猫爷在建设银行的账户，那么招商银行先得给狗哥账户扣10块，然后建设银行给猫爷账户增10块。而招商银行和建设银行根本就不是一个系统，招商银行给狗哥扣钱的业务放到了自己的一个事务里，建设银行给猫爷加钱的业务放到了自己的一个事务里，这两个事务其实在各自的系统中并没有什么关系，完全有可能招商银行的事务提交了，而建设银行的事务由于系统宕机而失败了，这就导致狗哥扣了钱，却没有转给猫爷的惨剧发生。所以我们必须引入一些机制，来实现分布式事务。

XA规范

有一个名叫X/Open（这名儿听着就挺霸气）的组织提出了一个名为XA的规范。

小贴士：为节省同志们去搜索这份规范的宝贵时间，大家在“我们都是小青蛙”公众号输入“XA”即可下载该规范。

有人说XA的含义是Extended Architecture。令人迷惑的是，我竟然没在上述规范中找到XA到底是个啥意思（很尴尬😅），大家把它理解成一个名字就好了，其实叫成王尼玛也没啥问题。

这个XA规范提出了2个角色：

不论是事务协调器，还是资源管理器这样的名字念起来都比较拗口，有催眠功效，我们后续就把事务协调器或者资源管理器称作大哥了哈。

事务管理器念起来也比较拗口，我们就把它称作小弟了哈。

要提交一个全局事务，那么属于该全局事务的若干个小事务就应该全部提交，只要有任何一个小事务无法提交，那么整个全局事务就应该全部回滚。所以此时大哥不能让各个小弟逐个提交，因为不能保证后面提交的小弟是否可能发生错误。此时XA规范中指出，要提交一个全局事务，必须分为2步：

XA规范把上述全局事务提交时所经历的两个阶段称作两阶段提交。

小贴士：


如果一个全局事务仅包含一个小弟的话，那两阶段提交可以退化成1阶段提交。

大家可以看到，XA规范引入了一个在事务提交时的Prepare阶段，这个阶段就是让各个事务做好提交前的准备，具体就是把语句执行过程中产生的redo日志都刷盘。如果语句执行过程中的redo日志都刷盘了，那么即使之后系统崩溃，那么在重启的时候还是可以恢复到该事务各个语句都执行完的样子。

这样的话，在Prepare阶段结束后，即使某个小弟因为某些原因而崩溃，在之后重启恢复时，也可以把自己再次恢复成Prepare状态。在崩溃恢复结束后，大哥可以继续让小弟提交或者回滚。

以上就是XA规范的核心内容，下边可以来唠叨一下MySQL对上述XA规范的实现了。

MySQL中的XA事务

MySQL中的XA事务分为外部XA和内部XA，我们分别来看一下。

外部XA

在MySQL的外部XA实现中，MySQL服务器充当小弟，而连接服务器的客户端程序充当大哥。

与使用BEGIN语句开启，使用COMMIT提交的常规事务不同，如果我们想在MySQL中使用XA事务，需要一些特殊的语句：

在一台MySQL服务器上，每个XA事务都必须有一个唯一的id，被称作xid。这个xid是由发起XA事务的应用程序（客户端）自己指定的，只要我们自己保证它唯一就好了。

这个xid其实是由gtrid、bqual、formatID三个部分组成的：

  1. xid: gtrid [, bqual [, formatID ]]

其中gtrid（global transaction id）是指全局事务id，是一个字符串，bqual是指分支限定符，formatID是指gtrid和bqual所使用的格式。

不过我们这里并不打算详纠啥是个分支，还限定符，以及啥格式之类的，我们可以在指定xid的时候省略bqual和formatID的值，MySQL会使用默认的值（bqual默认是空字符串''，formatID默认是1）。也就是说我们文章后续内容指定xid时仅指定gtrid就好了，也就是指定一个字符串即可。

介绍了在MySQL中使用外部XA的一些语句，接下来可以画一个XA事务的状态转换图了：

哔哔了很多，赶紧来做个实验：

  1. mysql> XA START 'a';    //XA事务进入ACTIVE状态
  2. Query OK, 0 rows affected (0.00 sec)
  4. mysql> DELETE FROM x WHERE id = 1;  //XA事务中包含的语句
  5. Query OK, 1 row affected (0.00 sec)
  7. mysql> XA END 'a';      //XA事务进入IDEL状态
  8. Query OK, 0 rows affected (0.00 sec)
  10. mysql> XA PREPARE 'a';  //XA事务进入PREPARE状态
  11. Query OK, 0 rows affected (0.01 sec)
  13. mysql> XA COMMIT 'a';   //XA事务进入COMMIT状态
  14. Query OK, 0 rows affected (0.01 sec)

MySQL的外部XA除了被用于跨行转账这种经典的分布式事务应用场景，还被广泛应用于所谓的数据库中间件。

现在各个公司由于表中数据太多，这些数据会被分散在不通服务器中存储。由应用程序员分别和不同的MySQL服务器打交道实在费劲，所以有一种称作数据库中间件的东西开始问世。即应用程序只将SQL语句发送给数据库中间件，中间件分析一下该SQL访问的数据都在哪些不同的服务器中存储着，并且计算出不通服务器应该执行哪些SQL语句。然后就可以对不同的服务器分别开启XA事务，并且让把不同服务器需要执行的语句分别发送到不同的服务器中。等应用程序员告知中间件准备提交事务时，中间件先给各个服务器发送XA PREPARE语句，如果各个服务器都返回OK的话，接着就给各个服务器发送XA COMMIT语句来提交XA事务，等各个服务器把提交成功的消息返回给中间件，中间件就可以通知应用程序事务提交成功了。

内部XA

对于一台服务器来说，即使客户端使用BEGIN/START TRANSACTION语句开启的普通事务，该事务所包含的语句也有可能涉及多个存储引擎。此时MySQL内部采用XA规范来保证所有支持事务的存储引擎要么全部提交，要么全部回滚，这也被称作MySQL的内部XA。

另外有一点值得注意的是，内部XA除了解决这种设计多个存储引擎的事务之外，还解决保证binlog和存储引擎所做的修改是一致的问题。我们稍后重点展开一下这个问题。

在MySQL内部执行一个事务时，存储引擎会修改相应的数据，server层会记录语句对应的binlog。这是两个要么都完成，要么都步完成的事情。否则的话：

那我们需要保证：如果存储引擎提交了事务，server层的binlog日志必须也被写入到硬盘上；如果存储引擎回滚了事务，server层的binlog日志必须不能被写入到硬盘上。

MySQL采用内部XA来实现上述内容，下边以Innodb存储引擎为例，具体讨论一下Innodb事务的提交和binlog日志写入的过程。

有binlog参与的内部XA事务

小贴士：


后续会用到很多undo日志结构的内容，这些内容都在《MySQL是怎样运行的：从根儿上理解MySQL》书籍中有详细叙述，如果不了解的话，看起来可能会有点儿困难，建议先将undo日志章节内容看熟。

当客户端执行COMMIT语句或者在自动提交的情况下，MySQL内部开启一个XA事务，分两阶段来完成XA事务的提交：

binlog_prepare是在PREPARE阶段对binlog所做的一些操作：

binlog_prepare函数基本啥也没干，我们就不展开说了。

innobase_xa_prepare是InnoDB存储引擎实现的XA规范的prepare接口：

这个函数做了很多事情，我们得好好唠叨一下。

首先我们知道事务执行过程中需要写undo日志，这些undo日志被写到若干个页面中，这些页面也被称作Undo页面，这些页面会串成一个链表，称作Undo页面链表。在一个事务对应的Undo页面链表的首个页面中，记录了一些关于这个事务的一些属性，我们贴个图看一下：

我们先看一下其中的Undo Log Segment Header部分：

其中的TRX_UNDO_STATE字段就表明该事务目前处于什么状态。当处于Prepare阶段时，调用innobase_xa_prepare函数会将TRX_UNDO_STATE字段的值设置为TRX_UNDO_PREPARED（整数5），表明当前事务处在Prepare阶段。

我们再看一下Undo Log Header部分：

这个部分体现着这个Undo页面链表所属的事务的各种信息，包括事务id。其中两个属性和我们今天主题特别搭：

当处于Prepare阶段时，调用innobase_xa_prepare函数会将TRX_UNDO_XID_EXISTS设置为TRUE，并将本次内部XA事务的xid（这个xid是MySQL自己生成的）写入XID信息处。

小贴士：


再一次强调，修改Undo页面也是在修改页面，事务凡是修改页面就需要先记录相应的redo日志。

记录了关于该事务的各种属性之后，接下来该将到现在为止所产生的所有redo日志进行刷盘，刷盘的函数如下所示：

在将redo日志刷盘之后，即使之后系统崩溃，在重启恢复的时候也可以将处于Prepare状态的事务完全恢复。

小贴士：


在MySQL 5.7中，有一个称之为组提交（group commit）的优化。即设计InnoDB的大叔觉得各个事务分别刷自己的redo日志和binlog效率太低，他们把并发执行的多个事务所产生的redo日志和binlog在后续的Commit阶段集中起来统一刷新，这样可能提升效率，所以在MySQL 5.7以及之后的版本中，上述在Prepare阶段刷新redo日志的操作会被推迟到Commit阶段才真正执行。关于组提交的优化措施我们并不想过多展开，大家忽略这个优化就好了，这里就认为在Prepare阶段事务就已经将执行过程中产生的redo日志刷盘就OK。

将binlog刷盘的函数如下：

InnoDB存储引擎提交事务使用innobase_commit函数完成存储引擎层面的事务提交：

innobase_commit函数做了很多事情，我们挑一些重要的来说。

首先是更新Undo页面链表的状态，将我们上边说的Undo Log Segment Header部分的STATE字段更新一下。更新规则如下：

也就是说如果当前事务产生的undo日志比较少，那么就继续让别的事务复用该Undo页面链表，将STATE设置为TRX_UNDO_CACHED；如果Undo页面链表用于存储INSERT操作产生的undo日志，那么就将STATE设置为TRX_UNDO_TO_FREE，稍后会释放Undo页面链表占用的页面；如果Undo页面链表用于存储其他操作产生的undo日志，那么就将STATE设置为TRX_UNDO_TO_PURGE，等待purge线程后台回收该Undo页面链表。

小贴士：


UPDATE、DELETE操作产生的undo日志可能会用于其他事务的MVCC操作，所以不能立即删除。

对于存储UPDATE、DELETE操作产生的undo日志的Undo页面链表，还要将其加入所谓的History链表，关于这个History链表是啥，我们这里就不展开了。

每个Undo页面链表的首个页面的页号会被存储在表空间的某个地方，以便崩溃恢复的时候可以根据该页来进行恢复。如果此时在事务提交时，Undo页面链表的状态被设置为TRX_UNDO_CACHED，那存储Undo页面链表的首个页面的页号的地方也就不需要做改动；如果此时在事务提交时，Undo页面链表的状态被设置为TRX_UNDO_CACHED，那存储Undo页面链表的首个页面的页号的地方就得被设置为空，这样这个地方就可以被其他事务使用了。

至此，这个事务就算是提交完了。

崩溃恢复

每当系统重启时，都会先进入恢复过程。

此时首先按照已经刷新到磁盘的redo日志修改页面，把系统恢复到崩溃前的状态。

然后在表空间中找一下各个Undo页面链表的首个页面的页号，然后就可以读取该页面的各种信息。我们再把这个页面的内容给大家看一下：

通过这个页面，我们可以知道该Undo页面链表对应的事务状态是什么：

对于处于PREPARE状态的事务，存储引擎既可以提交，也可以回滚，这取决于目前该事务对应的binlog是否已经写入硬盘。这时就会读取最后一个binlog日志文件，从日志文件中找一下有没有该PREPARE事务对应的xid记录，如果有的话，就将该事务提交，否则就回滚好了。

最后

这一篇文章有点儿长，不点赞/在看/分享，真的好么～

推荐资料与书籍

一些资料

一些书籍

添加新批注
在作者公开此批注前，只有你和作者可见。
回复批注