问题解析

为什么这些SQL语句逻辑相同，性能却差异巨大？在MySQL中，有很多看上去逻辑相同，但性能却差异巨大的SQL语句。

对这些语句使用不当的话，就会不经意间导致整个数据库的压力变大。

我今天挑选了三个这样的案例和你分享。

希望再遇到相似的问题时，你可以做到举一反三、快速解决问题。

案例一：条件字段函数操作假设你现在维护了一个交易系统，其中交易记录表tradelog包含交易流水号（tradeid）、交易员id（operator）、交易时间（t_modified）等字段。

为了便于描述，我们先忽略其他字段。

这个表的建表语句如下：

假设，现在已经记录了从2016年初到2018年底的所有数据，运营部门有一个需求是，要统计发生在所有年份中7月份的交易记录总数。

这个逻辑看上去并不复杂，你的SQL语句可能会这么写：

由于t_modified字段上有索引，于是你就很放心地在生产库中执行了这条语句，但却发现执行了特别久，才返回了结果。

如果你问DBA同事为什么会出现这样的情况，他大概会告诉你：如果对字段做了函数计算，就用不上索引了，这是MySQL的规定。

现在你已经学过了InnoDB的索引结构了，可以再追问一句为什么？为什么条件是wheret_modified=’2018-7-1’的时候可以用上索引，而改成where month(t_modified)=7的时候就不行了？下面是这个t_modified索引的示意图。

方框上面的数字就是month()函数对应的值。

mysql> CREATE TABLE t̀radelog ̀( ìd ̀int(11) NOT NULL, t̀radeid ̀varchar(32) DEFAULT NULL, `operator̀ int(11) DEFAULT NULL, t̀_modified ̀datetime DEFAULT NULL, PRIMARY KEY (̀ id )̀, KEY t̀radeid ̀(̀ tradeid )̀, KEY t̀_modified ̀(̀ t_modified )̀) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;mysql> select count(*) from tradelog where month(t_modified)=7;图1 t_modified索引示意图如果你的SQL语句条件用的是where t_modified=’2018-7-1’的话，引擎就会按照上面绿色箭头的路线，快速定位到 t_modified=’2018-7-1’需要的结果。

实际上，B+树提供的这个快速定位能力，来源于同一层兄弟节点的有序性。

但是，如果计算month()函数的话，你会看到传入7的时候，在树的第一层就不知道该怎么办了。

也就是说，对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

需要注意的是，优化器并不是要放弃使用这个索引。

在这个例子里，放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引t_modified，优化器对比索引大小后发现，索引t_modified更小，遍历这个索引比遍历主键索引来得更快。

因此最终还是会选择索引t_modified。

接下来，我们使用explain命令，查看一下这条SQL语句的执行结果。

图2 explain 结果key=”t_modified”表示的是，使用了t_modified这个索引；我在测试表数据中插入了10万行数据，rows=100335，说明这条语句扫描了整个索引的所有值；Extra字段的Using index，表示的是使用了覆盖索引。

也就是说，由于在t_modified字段加了month()函数操作，导致了全索引扫描。

为了能够用上索引的快速定位能力，我们就要把SQL语句改成基于字段本身的范围查询。

按照下面这个写法，优化器就能按照我们预期的，用上t_modified索引的快速定位能力了。

当然，如果你的系统上线时间更早，或者后面又插入了之后年份的数据的话，你就需要再把其他年份补齐。

到这里我给你说明了，由于加了month()函数操作，MySQL无法再使用索引快速定位功能，而只能使用全索引扫描。

不过优化器在个问题上确实有“偷懒”行为，即使是对于不改变有序性的函数，也不会考虑使用索引。

比如，对于select * from tradelog where id + 1 = 10000这个SQL语句，这个加1操作并不会改变有序性，但是MySQL优化器还是不能用id索引快速定位到9999这一行。

所以，需要你在写SQL语句的时候，手动改写成 where id = 10000 -1才可以。

案例二：隐式类型转换接下来我再跟你说一说，另一个经常让程序员掉坑里的例子。

我们一起看一下这条SQL语句：

交易编号tradeid这个字段上，本来就有索引，但是explain的结果却显示，这条语句需要走全表扫描。

你可能也发现了，tradeid的字段类型是varchar(32)，而输入的参数却是整型，所以需要做类型转换。

那么，现在这里就有两个问题：

mysql> select count(*) from tradelog where -> (t_modified >= ‘2016-7-1’ and t_modified<’2016-8-1’) or -> (t_modified >= ‘2017-7-1’ and t_modified<’2017-8-1’) or -> (t_modified >= ‘2018-7-1’ and t_modified<’2018-8-1’);mysql> select * from tradelog where tradeid=110717;1. 数据类型转换的规则是什么？2. 为什么有数据类型转换，就需要走全索引扫描？先来看第一个问题，你可能会说，数据库里面类型这么多，这种数据类型转换规则更多，我记不住，应该怎么办呢？这里有一个简单的方法，看 select “10” > 9的结果：

1. 如果规则是“将字符串转成数字”，那么就是做数字比较，结果应该是1；

2. 如果规则是“将数字转成字符串”，那么就是做字符串比较，结果应该是0。

验证结果如图3所示。

图3 MySQL中字符串和数字转换的效果示意图从图中可知，select “10” > 9返回的是1，所以你就能确认MySQL里的转换规则了：在MySQL中，字符串和数字做比较的话，是将字符串转换成数字。

这时，你再看这个全表扫描的语句：

就知道对于优化器来说，这个语句相当于：

也就是说，这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

现在，我留给你一个小问题，id的类型是int，如果执行下面这个语句，是否会导致全表扫描呢？mysql> select * from tradelog where tradeid=110717;mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;select * from tradelog where id=”83126”;你可以先自己分析一下，再到数据库里面去验证确认。

接下来，我们再来看一个稍微复杂点的例子。

案例三：隐式字符编码转换假设系统里还有另外一个表trade_detail，用于记录交易的操作细节。

为了便于量化分析和复现，我往交易日志表tradelog和交易详情表trade_detail这两个表里插入一些数据。

这时候，如果要查询id=2的交易的所有操作步骤信息，SQL语句可以这么写：

mysql> CREATE TABLE t̀rade_detail ̀( ìd ̀int(11) NOT NULL, t̀radeid ̀varchar(32) DEFAULT NULL, t̀rade_step ̀int(11) DEFAULT NULL, /操作步骤/ `step_info ̀varchar(32) DEFAULT NULL, /步骤信息/ PRIMARY KEY (̀ id )̀, KEY t̀radeid ̀(̀ tradeid )̀) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into tradelog values(1, ‘aaaaaaaa’, 1000, now());insert into tradelog values(2, ‘aaaaaaab’, 1000, now());insert into tradelog values(3, ‘aaaaaaac’, 1000, now());insert into trade_detail values(1, ‘aaaaaaaa’, 1, ‘add’);insert into trade_detail values(2, ‘aaaaaaaa’, 2, ‘update’);insert into trade_detail values(3, ‘aaaaaaaa’, 3, ‘commit’);insert into trade_detail values(4, ‘aaaaaaab’, 1, ‘add’);insert into trade_detail values(5, ‘aaaaaaab’, 2, ‘update’);insert into trade_detail values(6, ‘aaaaaaab’, 3, ‘update again’);insert into trade_detail values(7, ‘aaaaaaab’, 4, ‘commit’);insert into trade_detail values(8, ‘aaaaaaac’, 1, ‘add’);insert into trade_detail values(9, ‘aaaaaaac’, 2, ‘update’);insert into trade_detail values(10, ‘aaaaaaac’, 3, ‘update again’);insert into trade_detail values(11, ‘aaaaaaac’, 4, ‘commit’);mysql> select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2; /语句Q1/图4 语句Q1的explain 结果我们一起来看下这个结果：

1. 第一行显示优化器会先在交易记录表tradelog上查到id=2的行，这个步骤用上了主键索引，rows=1表示只扫描一行；

2. 第二行key=NULL，表示没有用上交易详情表trade_detail上的tradeid索引，进行了全表扫描。

在这个执行计划里，是从tradelog表中取tradeid字段，再去trade_detail表里查询匹配字段。

因此，我们把tradelog称为驱动表，把trade_detail称为被驱动表，把tradeid称为关联字段。

接下来，我们看下这个explain结果表示的执行流程：

图5 语句Q1的执行过程图中：

第1步，是根据id在tradelog表里找到L2这一行；

第2步，是从L2中取出tradeid字段的值；

第3步，是根据tradeid值到trade_detail表中查找条件匹配的行。

explain的结果里面第二行的key=NULL表示的就是，这个过程是通过遍历主键索引的方式，一个一个地判断tradeid的值是否匹配。

进行到这里，你会发现第3步不符合我们的预期。

因为表trade_detail里tradeid字段上是有索引的，我们本来是希望通过使用tradeid索引能够快速定位到等值的行。

但，这里并没有。

如果你去问DBA同学，他们可能会告诉你，因为这两个表的字符集不同，一个是utf8，一个是utf8mb4，所以做表连接查询的时候用不上关联字段的索引。

这个回答，也是通常你搜索这个问题时会得到的答案。

但是你应该再追问一下，为什么字符集不同就用不上索引呢？我们说问题是出在执行步骤的第3步，如果单独把这一步改成SQL语句的话，那就是：

其中，$L2.tradeid.value的字符集是utf8mb4。

参照前面的两个例子，你肯定就想到了，字符集utf8mb4是utf8的超集，所以当这两个类型的字符串在做比较的时候，MySQL内部的操作是，先把utf8字符串转成utf8mb4字符集，再做比较。

因此， 在执行上面这个语句的时候，需要将被驱动数据表里的字段一个个地转换成utf8mb4，再跟L2做比较。

也就是说，实际上这个语句等同于下面这个写法：

CONVERT()函数，在这里的意思是把输入的字符串转成utf8mb4字符集。

这就再次触发了我们上面说到的原则：对索引字段做函数操作，优化器会放弃走树搜索功能。

到这里，你终于明确了，字符集不同只是条件之一，连接过程中要求在被驱动表的索引字段上加函数操作，是直接导致对被驱动表做全表扫描的原因。

mysql> select * from trade_detail where tradeid=$L2.tradeid.value; 这个设定很好理解，utf8mb4是utf8的超集。

类似地，在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。

select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value; 作为对比验证，我给你提另外一个需求，“查找trade_detail表里id=4的操作，对应的操作者是谁”，再来看下这个语句和它的执行计划。

图6 explain 结果这个语句里trade_detail 表成了驱动表，但是explain结果的第二行显示，这次的查询操作用上了被驱动表tradelog里的索引(tradeid)，扫描行数是1。

这也是两个tradeid字段的join操作，为什么这次能用上被驱动表的tradeid索引呢？我们来分析一下。

假设驱动表trade_detail里id=4的行记为R4，那么在连接的时候（图5的第3步），被驱动表tradelog上执行的就是类似这样的SQL 语句：

这时候$R4.tradeid.value的字符集是utf8, 按照字符集转换规则，要转成utf8mb4，所以这个过程就被改写成：

你看，这里的CONVERT函数是加在输入参数上的，这样就可以用上被驱动表的traideid索引。

理解了原理以后，就可以用来指导操作了。

如果要优化语句的执行过程，有两种做法：

比较常见的优化方法是，把trade_detail表上的tradeid字段的字符集也改成utf8mb4，这样就没有字符集转换的问题了。

mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;select operator from tradelog where traideid =$R4.tradeid.value; select operator from tradelog where traideid =CONVERT($R4.tradeid.value USING utf8mb4); select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;如果能够修改字段的字符集的话，是最好不过了。

但如果数据量比较大， 或者业务上暂时不能做这个DDL的话，那就只能采用修改SQL语句的方法了。

图7 SQL语句优化后的explain结果这里，我主动把 l.tradeid转成utf8，就避免了被驱动表上的字符编码转换，从explain结果可以看到，这次索引走对了。

小结今天我给你举了三个例子，其实是在说同一件事儿，即：对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

第二个例子是隐式类型转换，第三个例子是隐式字符编码转换，它们都跟第一个例子一样，因为要求在索引字段上做函数操作而导致了全索引扫描。

MySQL的优化器确实有“偷懒”的嫌疑，即使简单地把where id+1=1000改写成where id=1000-1就能够用上索引快速查找，也不会主动做这个语句重写。

因此，每次你的业务代码升级时，把可能出现的、新的SQL语句explain一下，是一个很好的习惯。

最后，又到了思考题时间。

今天我留给你的课后问题是，你遇到过别的、类似今天我们提到的性能问题吗？你认为原因是什么，又是怎么解决的呢？你可以把你经历和分析写在留言区里，我会在下一篇文章的末尾选取有趣的评论跟大家一起分享和分析。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间我在上篇文章的最后，留给你的问题是：我们文章中最后的一个方案是，通过三次limit Y,1 来得alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;mysql> select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2; 到需要的数据，你觉得有没有进一步的优化方法。

这里我给出一种方法，取Y1、Y2和Y3里面最大的一个数，记为M，最小的一个数记为N，然后执行下面这条SQL语句：

再加上取整个表总行数的C行，这个方案的扫描行数总共只需要C+M+1行。

当然也可以先取回id值，在应用中确定了三个id值以后，再执行三次where id=X的语句也是可以的。

@倪大人 同学在评论区就提到了这个方法。

这次评论区出现了很多很棒的留言：

老杨同志  20感谢老师鼓励，我本人工作时间比较长，有一定的基础，听老师的课还是收获很大。

每次公司mysql> select * from t limit N, M-N+1;@老杨同志 提出了重新整理的方法、@雪中鼠[悠闲] 提到了用rowid的方法，是类似的思路，就是让表里面保存一个无空洞的自增值，这样就可以用我们的随机算法1来实现；

@吴宇晨 提到了拿到第一个值以后，用id迭代往下找的方案，利用了主键索引的有序性。

精选留言内部有技术分享，我都去听课，但是多数情况，一两个小时的分享，就只有一两句话受益。

老师的每篇文章都能命中我的知识盲点，感觉太别爽。

对应今天的隐式类型转换问题也踩过坑。

我们有个任务表记录待执行任务，表结构简化后如下：

CREATE TABLE task (task_id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘自增主键’,task_type int(11) DEFAULT NULL COMMENT ‘任务类型id’,task_rfid varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT ‘关联外键1’,PRIMARY KEY (task_id)) ENGINE=InnoDB AUTO_INCREMENT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT=’任务表’;task_rfid 是业务主键，当然都是数字，查询时使用sql：

select * from task where task_rfid =123;其实这个语句也有隐式转换问题，但是待执行任务只有几千条记录，并没有什么感觉。

这个表还有个对应的历史表，数据有几千万忽然有一天，想查一下历史记录，执行语句select * from task_history where task_rfid =99;直接就等待很长时间后超时报错了。

如果仔细看，其实我的表没有task_rfid 索引，写成task_rfid =‘99’也一样是全表扫描。

运维时的套路是，猜测主键task_id的范围，怎么猜，我原表有creat_time字段，我会先查select max(task_id) from task_history 然后再看看 select * from task_history where task_id = maxId - 10000的时间，估计出大概的id范围。

然后语句变成select * from task_history where task_rfid =99 and id between ？ and ？;2018-12-24 作者回复你最后这个id预估，加上between ，有种神来之笔的感觉感觉隐约里面有二分法的思想2018-12-24可凡不凡  11.老师好2.如果在用一个 MySQL 关键字做字段,并且字段上索引,当我用这个索引作为唯一查询条件的时候 ,会 造 成隐式的转换吗? 例如:SELECT * FROM b_side_order WHERE CODE = 332924 ; (code 上有索引)3. mysql5.6 code 上有索引 intime 上没有索引语句一:SELECT * FROM b_side_order WHERE CODE = 332924 ;语句二;UPDATE b_side_order SET in_time = ‘2018-08-04 08:34:44’ WHERE 1=2 or CODE = 332924;这两个语句 执行计划走 select 走了索引,update 没有走索引 是执行计划的bug 吗??2018-12-25 作者回复1. 你好2. CODE不是关键字呀， 另外优化器选择跟关键字无关哈，关键字的话，要用 反‘ 括起来3. 不是bug, update如果把 or 改成 and , 就能走索引2018-12-25冠超  0非常感谢老师分享的内容，实打实地学到了。

这里提个建议，希望老师能介绍一下设计表的时候要怎么考虑这方面的知识哈2019-01-28 作者回复是这样的，其实我们整个专栏大部分的文章，最后都是为了说明 “怎么设计表”、“怎么考虑优化SQL语句”但是因为这个不是一成不变的，很多是需要考虑现实的情况，所以这个专栏就是想把对应的原理说一下，这样大家在应对不同场景的时候，可以组合来考虑。

也就是说没有一段话可以把“怎么设计表”讲清楚（或者说硬写出来很可能就是一些general的没有什么针对性作用的描述）你可以把你的业务背景抽象说下，我们来具体讨论吧2019-01-28700  0老师您好，有个问题恳请指教。

背景如下，我长话短说：

mysql>select @@version;5.6.30-logCREATE TABLE t1 ( id int(11) unsigned NOT NULL AUTO_INCREMENT,user_id int(11) NOT NULL, plan_id int(11) NOT NULL DEFAULT ‘0’ , PRIMARY KEY (id),KEY userid (user_id) USING BTREE, KEY idx_planid (plan_id)) ENGINE=InnoDB DEFAULT CHARSET=gb2312;CREATE TABLE t3 (id int(11) NOT NULL AUTO_INCREMENT,status int(4) NOT NULL DEFAULT ‘0’,ootime varchar(11) DEFAULT NULL,PRIMARY KEY (id),KEY idx_xxoo (status,ootime)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;t1 和 t3 表的字符集不一样sql 执行计划如下：

explainSELECT t1.id, t1.user_idFROM t1, t3WHERE t1.plan_id = t3.idAND t3.ootime < UNIX_TIMESTAMP(‘2022-01-18’)+—-+————-+——-+——-+—————+————–+———+————–+——-+—————————————-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+——-+——-+—————+————–+———+————–+——-+—————————————-+| 1 | SIMPLE | t3 | index | PRIMARY | idx_xxoo | 51 | NULL | 39106 | Using where; Using index|| 1 | SIMPLE | t1 | ref | idx_planid | idx_planid | 4 | t3.id | 401 | Using join buffer (Batched Key Access) |+—-+————-+——-+——-+—————+————–+———+————–+——-+—————————————-+我的疑惑是1)t3 的 status 没出现在 where 条件中，但执行计划为什么用到了 idex_xxoo 索引？2)为什么 t3.ootime 也用到索引了，从 key_len 看出。

t3.ootime 是 varchar 类型的，而 UNIX_TIMESTAMP(‘2022-01-18’) 是数值，不是发生了隐式转换吗？请老师指点。

2019-01-18 作者回复这个查询语句会对t3做全索引扫描，是使用了索引的，只是没有用上快速搜索功能2019-01-19赖阿甘  0“mysql>select l.operator from tradelog l , trade_detail d where d.tradeid=l.tradeid and d.id=4;”图6上面那句sql是不是写错了。

d.tradeid=l.tradeid是不是该写成l.tradeid = d.tradeid？不然函数会作用在索引字段上，就只能全表扫描了2018-12-24 作者回复这个问题不是等号顺序决定的哈好问题2018-12-24Leon  16索引字段不能进行函数操作，但是索引字段的参数可以玩函数，一言以蔽之2018-12-24 作者回复精辟2018-12-24探索无止境  5多表连接时，mysql是怎么选择驱动表和被驱动表的？这个很重要，希望老师可以讲讲2018-12-25可凡不凡  51.老师对于多表联合查询中,MySQL 对索引的选择 以后会详细介绍吗?2018-12-24 作者回复额，你是第三个提这个问题的了，我得好好考虑下安排2018-12-24某、人  4SQL逻辑相同,性能差异较大的,通过老师所讲学习到的,和平时碰到的,大概有以下几类:一.字段发生了转换,导致本该使用索引而没有用到索引1.条件字段函数操作2.隐式类型转换3.隐式字符编码转换(如果驱动表的字符集比被驱动表得字符集小，关联列就能用到索引,如果更大,需要发生隐式编码转换,则不能用到索引,latin<gbk<utf8<utf8mb4)二.嵌套循环,驱动表与被驱动表选择错误1.连接列上没有索引,导致大表驱动小表,或者小表驱动大表(但是大表走的是全表扫描) –连接列上建立索引2.连接列上虽然有索引,但是驱动表任然选择错误。

–通过straight_join强制选择关联表顺序3.子查询导致先执行外表在执行子查询,也是驱动表与被驱动表选择错误。

–可以考虑把子查询改写为内连接,或者改写内联视图(子查询放在from后组成一个临时表,在于其他表进行关联)4.只需要内连接的语句,但是写成了左连接或者右连接。

比如select * from t left join b on t.id=b.idwhere b.name=’abc’驱动表被固定,大概率会扫描更多的行,导致效率降低. –根据业务情况或sql情况,把左连接或者右连接改写为内连接三.索引选择不同,造成性能差异较大1.select * from t where aid= and create_name>’’ order by id limit 1;选择走id索引或者选择走(aid,create_time)索引,性能差异较大.结果集都有可能不一致–这个可以通过where条件过滤的值多少来大概判断,该走哪个索引四.其它一些因素1.比如之前学习到的是否有MDL X锁2.innodb_buffer_pool设置得太小,innodb_io_capacity设置得太小,刷脏速度跟不上3.是否是对表做了DML语句之后,马上做select,导致change buffer收益不高4.是否有数据空洞5.select选取的数据是否在buffer_pool中6.硬件原因,资源抢占原因多种多样,还需要慢慢补充。

老师我问一个问题:连接列上一个是int一个是bigint或者一个是char一个varchar,为什么被驱动表上会出现(using index condition)?2018-12-24Destroy、  2老师，对于最后回答上一课的问题：mysql> select * from t limit N, M-N+1;这个语句也不是取3条记录。

没理解。

2018-12-27 作者回复取其中三条…2018-12-27风轨  2刚试了文中穿插得思考题:当主键是整数类型条件是字符串时，会走索引。

文中提到了当字符串和数字比较时会把字符串转化为数字，所以隐式转换不会应用到字段上，所以可以走索引。

另外，select ‘a’ = 0 ; 的结果是1，说明无法转换成数字的字符串都被转换成0来处理了。

2018-12-24 作者回复2018-12-24匿名的朋友  1丁奇老师，我有个疑问，就是sql语句执行时那些order by group by limit 以及where条件，有执行的先后顺序吗？2019-01-05 作者回复有，先where ,再order by 最后limit2019-01-05大坤  1之前遇到过按时间范围查询大表不走索引的情况，如果缩小时间范围，又会走索引，记得在一些文章中看到过结果数据超过全表的30%就会走全表扫描，但是前面说的时间范围查询大表，这个时间范围绝对是小于30%的情况，想请教下老师，这个优化器都是在什么情况下会放弃索引呢？2018-12-25 作者回复总体来说就是判断哪种方式消耗更小，选哪种2018-12-25Leon  1老师，经常面试被问到工作中做了什么优化，有没有好的业务表的设计，请问老师课程结束后能不能给我们一个提纲挈领的大纲套路，让我们有个脉络和思路来应付这种面试套路2018-12-25 作者回复有没有好的业务表的设计，这类问题我第一次听到，能不能展开一下，这样说不要清楚面试官的考核点是啥…2018-12-25果然如此  1我想问一个上期的问题，随机算法2虽然效率高，但是还是有个瑕疵，比如我们的随机出题算法无法直接应用，因为每次随机一个试题id，多次随机没有关联，会产生重复id，有没有更好的解决方法？2018-12-25 作者回复内存里准备个set这样的数据结构，重读的不算，这样可以不2018-12-25长杰  1这里我给出一种方法，取 Y1、Y2 和 Y3 里面最大的一个数，记为 M，最小的一个数记为 N，然后执行下面这条 SQL 语句：

mysql> select * from t limit N, M-N+1;再加上取整个表总行数的 C 行，这个方案的扫描行数总共只需要 C+M 行。

优化后的方案应该是C+M+1行吧？2018-12-24 作者回复你说的对，我改下2018-12-25asdf100  1在这个例子里，放弃了树搜索功能，优化器可以选择遍历主键索引，也可以选择遍历索引 t_modified，优化器对比索引大小后发现，索引 t_modified 更小，遍历这个索引比遍历主键索引来得更快。

优化器如何对比的，根据参与字段字段类型占用空间大小吗？2018-12-24 作者回复优化器信息是引擎给的，引擎是这么判断的2018-12-24约书亚  1谁是驱动表谁是被驱动表，是否大多数情况看where条件就可以了？这是否本质上涉及到mysql底层决定用什么算法进行级联查询的问题？后面会有课程详细说明嘛？2018-12-24 作者回复可以简单看where之后剩下的行数（预判不一定准哈）2018-12-24Lukia  0老师好，之前看了《数据索引与优化》，提到表之间的连接操作可以有嵌套循环连接（本文中提到的驱动表和被驱动表）和合并扫描连接（先在临时表中针对谓词作排序）还有哈希连接。

请问MySQL中是否存在后面两种方式的连接，如果有的话优化器会在什么情况下选择呢？谢谢！2019-01-29 作者回复第34、35两篇就会说到了，今晚关注下2019-01-29涛哥哥  0老师，您好！我是做后端开发的。

想问一下 mysql in关键字 的内部原理，能抽一点点篇幅讲一下吗？比如：select * from T where id in (a,b,d,c,,e,f); id是主键。

1、为什么查询出来的结果集会按照id排一次序呢（是跟去重有关系么）？2、如果 in 里面的值较多的时候，就会比较慢啊（是还不如全表扫描么）？问我们公司很多后端的，都不太清楚，问我们DBA，他说默认就是这样（这不跟没说一样吗）。

希望老师可以帮忙解惑。

祝老师身体健康！微笑~2019-01-26 作者回复1. 优化器会排个序，目的是如果这几个记录对应的数据都不在内存里，可以触发顺序读盘，后面文章我们介绍到join的时候，会提到MRR，你关注下2. in里面值多就是多次执行树搜索，跟全表扫描的速度对比，就看in里面的数据个数的比例了。

你的in里面一般多少个value呀2019-01-26```

问题解析

如何正确地显示随机消息？我在上一篇文章，为你讲解完order by语句的几种执行模式后，就想到了之前一个做英语学习App的朋友碰到过的一个性能问题。

今天这篇文章，我就从这个性能问题说起，和你说说MySQL中的另外一种排序需求，希望能够加深你对MySQL排序逻辑的理解。

这个英语学习App首页有一个随机显示单词的功能，也就是根据每个用户的级别有一个单词表，然后这个用户每次访问首页的时候，都会随机滚动显示三个单词。

他们发现随着单词表变大，选单词这个逻辑变得越来越慢，甚至影响到了首页的打开速度。

现在，如果让你来设计这个SQL语句，你会怎么写呢？为了便于理解，我对这个例子进行了简化：去掉每个级别的用户都有一个对应的单词表这个逻辑，直接就是从一个单词表中随机选出三个单词。

这个表的建表语句和初始数据的命令如下：

为了便于量化说明，我在这个表里面插入了10000行记录。

接下来，我们就一起看看要随机选择3个单词，有什么方法实现，存在什么问题以及如何改进。

内存临时表首先，你会想到用order by rand()来实现这个逻辑。

这个语句的意思很直白，随机排序取前3个。

虽然这个SQL语句写法很简单，但执行流程却有点复杂的。

我们先用explain命令来看看这个语句的执行情况。

mysql> CREATE TABLE words ( id int(11) NOT NULL AUTO_INCREMENT, word varchar(64) DEFAULT NULL, PRIMARY KEY (id)) ENGINE=InnoDB;delimiter ;;create procedure idata()begin declare i int; set i=0; while i<10000 do insert into words(word) values(concat(char(97+(i div 1000)), char(97+(i % 1000 div 100)), char(97+(i % 100 div 10)), char(97+(i % 10)))); set i=i+1; end while;end;;delimiter ;call idata();mysql> select word from words order by rand() limit 3;图1 使用explain命令查看语句的执行情况Extra字段显示Using temporary，表示的是需要使用临时表；Using filesort，表示的是需要执行排序操作。

因此这个Extra的意思就是，需要临时表，并且需要在临时表上排序。

这里，你可以先回顾一下上一篇文章中全字段排序和rowid排序的内容。

我把上一篇文章的两个流程图贴过来，方便你复习。

图2 全字段排序图3 rowid排序然后，我再问你一个问题，你觉得对于临时内存表的排序来说，它会选择哪一种算法呢？回顾一下上一篇文章的一个结论：对于InnoDB表来说，执行全字段排序会减少磁盘访问，因此会被优先选择。

我强调了“InnoDB表”，你肯定想到了，对于内存表，回表过程只是简单地根据数据行的位置，直接访问内存得到数据，根本不会导致多访问磁盘。

优化器没有了这一层顾虑，那么它会优先考虑的，就是用于排序的行越少越好了，所以，MySQL这时就会选择rowid排序。

理解了这个算法选择的逻辑，我们再来看看语句的执行流程。

同时，通过今天的这个例子，我们来尝试分析一下语句的扫描行数。

这条语句的执行流程是这样的：

1. 创建一个临时表。

这个临时表使用的是memory引擎，表里有两个字段，第一个字段是double类型，为了后面描述方便，记为字段R，第二个字段是varchar(64)类型，记为字段W。

并且，这个表没有建索引。

2. 从words表中，按主键顺序取出所有的word值。

对于每一个word值，调用rand()函数生成一个大于0小于1的随机小数，并把这个随机小数和word分别存入临时表的R和W字段中，到此，扫描行数是10000。

3. 现在临时表有10000行数据了，接下来你要在这个没有索引的内存临时表上，按照字段R排序。

4. 初始化 sort_buffer。

sort_buffer中有两个字段，一个是double类型，另一个是整型。

5. 从内存临时表中一行一行地取出R值和位置信息（我后面会和你解释这里为什么是“位置信息”），分别存入sort_buffer中的两个字段里。

这个过程要对内存临时表做全表扫描，此时扫描行数增加10000，变成了20000。

6. 在sort_buffer中根据R的值进行排序。

注意，这个过程没有涉及到表操作，所以不会增加扫描行数。

7. 排序完成后，取出前三个结果的位置信息，依次到内存临时表中取出word值，返回给客户端。

这个过程中，访问了表的三行数据，总扫描行数变成了20003。

接下来，我们通过慢查询日志（slow log）来验证一下我们分析得到的扫描行数是否正确。

其中，Rows_examined：20003就表示这个语句执行过程中扫描了20003行，也就验证了我们分析得出的结论。

这里插一句题外话，在平时学习概念的过程中，你可以经常这样做，先通过原理分析算出扫描行数，然后再通过查看慢查询日志，来验证自己的结论。

我自己就是经常这么做，这个过程很有趣，分析对了开心，分析错了但是弄清楚了也很开心。

现在，我来把完整的排序执行流程图画出来。

Query_time: 0.900376 Lock_time: 0.000347 Rows_sent: 3 Rows_examined: 20003SET timestamp=1541402277;select word from words order by rand() limit 3;图4 随机排序完整流程图1图中的pos就是位置信息，你可能会觉得奇怪，这里的“位置信息”是个什么概念？在上一篇文章中，我们对InnoDB表排序的时候，明明用的还是ID字段。

这时候，我们就要回到一个基本概念：MySQL的表是用什么方法来定位“一行数据”的。

在前面第4和第5篇介绍索引的文章中，有几位同学问到，如果把一个InnoDB表的主键删掉，是不是就没有主键，就没办法回表了？其实不是的。

如果你创建的表没有主键，或者把一个表的主键删掉了，那么InnoDB会自己生成一个长度为6字节的rowid来作为主键。

这也就是排序模式里面，rowid名字的来历。

实际上它表示的是：每个引擎用来唯一标识数据行的信息。

对于有主键的InnoDB表来说，这个rowid就是主键ID；

对于没有主键的InnoDB表来说，这个rowid就是由系统生成的；

MEMORY引擎不是索引组织表。

在这个例子里面，你可以认为它就是一个数组。

因此，这个rowid其实就是数组的下标。

到这里，我来稍微小结一下：order by rand()使用了内存临时表，内存临时表排序的时候使用了rowid排序方法。

磁盘临时表那么，是不是所有的临时表都是内存表呢？其实不是的。

tmp_table_size这个配置限制了内存临时表的大小，默认值是16M。

如果临时表大小超过了tmp_table_size，那么内存临时表就会转成磁盘临时表。

磁盘临时表使用的引擎默认是InnoDB，是由参数internal_tmp_disk_storage_engine控制的。

当使用磁盘临时表的时候，对应的就是一个没有显式索引的InnoDB表的排序过程。

为了复现这个过程，我把tmp_table_size设置成1024，把sort_buffer_size设置成 32768, 把max_length_for_sort_data 设置成16。

set tmp_table_size=1024;set sort_buffer_size=32768;set max_length_for_sort_data=16;/* 打开 optimizer_trace，只对本线程有效 /SET optimizer_trace=’enabled=on’; / 执行语句 /select word from words order by rand() limit 3;/ 查看 OPTIMIZER_TRACE 输出 */SELECT * FROM information_schema.OPTIMIZER_TRACE\G图5 OPTIMIZER_TRACE部分结果然后，我们来看一下这次OPTIMIZER_TRACE的结果。

因为将max_length_for_sort_data设置成16，小于word字段的长度定义，所以我们看到sort_mode里面显示的是rowid排序，这个是符合预期的，参与排序的是随机值R字段和rowid字段组成的行。

这时候你可能心算了一下，发现不对。

R字段存放的随机值就8个字节，rowid是6个字节（至于为什么是6字节，就留给你课后思考吧），数据总行数是10000，这样算出来就有140000字节，超过了sort_buffer_size 定义的 32768字节了。

但是，number_of_tmp_files的值居然是0，难道不需要用临时文件吗？这个SQL语句的排序确实没有用到临时文件，采用是MySQL 5.6版本引入的一个新的排序算法，即：优先队列排序算法。

接下来，我们就看看为什么没有使用临时文件的算法，也就是归并排序算法，而是采用了优先队列排序算法。

其实，我们现在的SQL语句，只需要取R值最小的3个rowid。

但是，如果使用归并排序算法的话，虽然最终也能得到前3个值，但是这个算法结束后，已经将10000行数据都排好序了。

也就是说，后面的9997行也是有序的了。

但，我们的查询并不需要这些数据是有序的。

所以，想一下就明白了，这浪费了非常多的计算量。

而优先队列算法，就可以精确地只得到三个最小值，执行流程如下：

1. 对于这10000个准备排序的(R,rowid)，先取前三行，构造成一个堆；

（对数据结构印象模糊的同学，可以先设想成这是一个由三个元素组成的数组）1. 取下一个行(R’,rowid’)，跟当前堆里面最大的R比较，如果R’小于R，把这个(R,rowid)从堆中去掉，换成(R’,rowid’)；

2. 重复第2步，直到第10000个(R’,rowid’)完成比较。

这里我简单画了一个优先队列排序过程的示意图。

图6 优先队列排序算法示例图6是模拟6个(R,rowid)行，通过优先队列排序找到最小的三个R值的行的过程。

整个排序过程中，为了最快地拿到当前堆的最大值，总是保持最大值在堆顶，因此这是一个最大堆。

图5的OPTIMIZER_TRACE结果中，filesort_priority_queue_optimization这个部分的chosen=true，就表示使用了优先队列排序算法，这个过程不需要临时文件，因此对应的number_of_tmp_files是0。

这个流程结束后，我们构造的堆里面，就是这个10000行里面R值最小的三行。

然后，依次把它们的rowid取出来，去临时表里面拿到word字段，这个过程就跟上一篇文章的rowid排序的过程一样了。

我们再看一下上面一篇文章的SQL查询语句：

你可能会问，这里也用到了limit，为什么没用优先队列排序算法呢？原因是，这条SQL语句是limit 1000，如果使用优先队列算法的话，需要维护的堆的大小就是1000行的(name,rowid)，超过了我设置的sort_buffer_size大小，所以只能使用归并排序算法。

总之，不论是使用哪种类型的临时表，order by rand()这种写法都会让计算过程非常复杂，需要大量的扫描行数，因此排序过程的资源消耗也会很大。

再回到我们文章开头的问题，怎么正确地随机排序呢？随机排序方法我们先把问题简化一下，如果只随机选择1个word值，可以怎么做呢？思路上是这样的：

1. 取得这个表的主键id的最大值M和最小值N;2. 用随机函数生成一个最大值到最小值之间的数 X = (M-N)*rand() + N;3. 取不小于X的第一个ID的行。

我们把这个算法，暂时称作随机算法1。

这里，我直接给你贴一下执行语句的序列:这个方法效率很高，因为取max(id)和min(id)都是不需要扫描索引的，而第三步的select也可以用索引快速定位，可以认为就只扫描了3行。

但实际上，这个算法本身并不严格满足题目的随机要求，因为ID中间可能有空洞，因此选择不同行的概率不一样，不是真正的随机。

select city,name,age from t where city=’杭州’ order by name limit 1000 ;mysql> select max(id),min(id) into @M,@N from t ;set @X= floor((@M-@N+1)*rand() + @N);select * from t where id >= @X limit 1;比如你有4个id，分别是1、2、4、5，如果按照上面的方法，那么取到 id=4的这一行的概率是取得其他行概率的两倍。

如果这四行的id分别是1、2、40000、40001呢？这个算法基本就能当bug来看待了。

所以，为了得到严格随机的结果，你可以用下面这个流程:1. 取得整个表的行数，并记为C。

2. 取得 Y = floor(C * rand())。

floor函数在这里的作用，就是取整数部分。

3. 再用limit Y,1 取得一行。

我们把这个算法，称为随机算法2。

下面这段代码，就是上面流程的执行语句的序列。

由于limit 后面的参数不能直接跟变量，所以我在上面的代码中使用了prepare+execute的方法。

你也可以把拼接SQL语句的方法写在应用程序中，会更简单些。

这个随机算法2，解决了算法1里面明显的概率不均匀问题。

MySQL处理limit Y,1 的做法就是按顺序一个一个地读出来，丢掉前Y个，然后把下一个记录作为返回结果，因此这一步需要扫描Y+1行。

再加上，第一步扫描的C行，总共需要扫描C+Y+1行，执行代价比随机算法1的代价要高。

当然，随机算法2跟直接order by rand()比起来，执行代价还是小很多的。

你可能问了，如果按照这个表有10000行来计算的话，C=10000，要是随机到比较大的Y值，那扫描行数也跟20000差不多了，接近order by rand()的扫描行数，为什么说随机算法2的代价要小很多呢？我就把这个问题留给你去课后思考吧。

现在，我们再看看，如果我们按照随机算法2的思路，要随机取3个word值呢？你可以这么做：

1. 取得整个表的行数，记为C；

2. 根据相同的随机方法得到Y1、Y2、Y3；

mysql> select count(*) into @C from t;set @Y = floor(@C * rand());set @sql = concat(“select * from t limit “, @Y, “,1”);prepare stmt from @sql;execute stmt;DEALLOCATE prepare stmt;3. 再执行三个limit Y, 1语句得到三行数据。

我们把这个算法，称作随机算法3。

下面这段代码，就是上面流程的执行语句的序列。

小结今天这篇文章，我是借着随机排序的需求，跟你介绍了MySQL对临时表排序的执行过程。

如果你直接使用order by rand()，这个语句需要Using temporary 和 Using filesort，查询的执行代价往往是比较大的。

所以，在设计的时候你要量避开这种写法。

今天的例子里面，我们不是仅仅在数据库内部解决问题，还会让应用代码配合拼接SQL语句。

在实际应用的过程中，比较规范的用法就是：尽量将业务逻辑写在业务代码中，让数据库只做“读写数据”的事情。

因此，这类方法的应用还是比较广泛的。

最后，我给你留下一个思考题吧。

上面的随机算法3的总扫描行数是 C+(Y1+1)+(Y2+1)+(Y3+1)，实际上它还是可以继续优化，来进一步减少扫描行数的。

我的问题是，如果你是这个需求的开发人员，你会怎么做，来减少扫描行数呢？说说你的方案，并说明你的方案需要的扫描行数。

你可以把你的设计和结论写在留言区里，我会在下一篇文章的末尾和你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间我在上一篇文章最后留给你的问题是，select * from t where city in (“杭州”,” 苏州 “) order byname limit 100;这个SQL语句是否需要排序？有什么方案可以避免排序？虽然有(city,name)联合索引，对于单个city内部，name是递增的。

但是由于这条SQL语句不是要单独地查一个city的值，而是同时查了”杭州”和” 苏州 “两个城市，因此所有满足条件的name就不是递增的了。

也就是说，这条SQL语句需要排序。

mysql> select count(*) into @C from t;set @Y1 = floor(@C * rand());set @Y2 = floor(@C * rand());set @Y3 = floor(@C * rand());select * from t limit @Y1，1； //在应用代码里面取Y1、Y2、Y3值，拼出SQL后执行select * from t limit @Y2，1；

select * from t limit @Y3，1；

那怎么避免排序呢？这里，我们要用到(city,name)联合索引的特性，把这一条语句拆成两条语句，执行流程如下：

1. 执行select * from t where city=“杭州” order by name limit 100; 这个语句是不需要排序的，客户端用一个长度为100的内存数组A保存结果。

2. 执行select * from t where city=“苏州” order by name limit 100; 用相同的方法，假设结果被存进了内存数组B。

3. 现在A和B是两个有序数组，然后你可以用归并排序的思想，得到name最小的前100值，就是我们需要的结果了。

如果把这条SQL语句里“limit 100”改成“limit 10000,100”的话，处理方式其实也差不多，即：要把上面的两条语句改成写：

和这时候数据量较大，可以同时起两个连接一行行读结果，用归并排序算法拿到这两个结果集里，按顺序取第10001~10100的name值，就是需要的结果了。

当然这个方案有一个明显的损失，就是从数据库返回给客户端的数据量变大了。

所以，如果数据的单行比较大的话，可以考虑把这两条SQL语句改成下面这种写法：

和然后，再用归并排序的方法取得按name顺序第10001~10100的name、id的值，然后拿着这100个id到数据库中去查出所有记录。

上面这些方法，需要你根据性能需求和开发的复杂度做出权衡。

select * from t where city=”杭州” order by name limit 10100; select * from t where city=”苏州” order by name limit 10100。

select id,name from t where city=”杭州” order by name limit 10100; select id,name from t where city=”苏州” order by name limit 10100。

问题解析

order by执行原理在你开发应用的时候，一定会经常碰到需要根据指定的字段排序来显示结果的需求。

还是以我们前面举例用过的市民表为例，假设你要查询城市是“杭州”的所有人名字，并且按照姓名排序返回前1000个人的姓名、年龄。

假设这个表的部分定义是这样的：
这时，你的SQL语句可以这么写：
CREATE TABLE t ( id int(11) NOT NULL, city varchar(16) NOT NULL, name varchar(16) NOT NULL, age int(11) NOT NULL, addr varchar(128) DEFAULT NULL, PRIMARY KEY (id), KEY city (city)) ENGINE=InnoDB;select city,name,age from t where city=’杭州’ order by name limit 1000 ;这个语句看上去逻辑很清晰，但是你了解它的执行流程吗？今天，我就和你聊聊这个语句是怎么执行的，以及有什么参数会影响执行的行为。

全字段排序前面我们介绍过索引，所以你现在就很清楚了，为避免全表扫描，我们需要在city字段加上索引。

在city字段上创建索引之后，我们用explain命令来看看这个语句的执行情况。

图1 使用explain命令查看语句的执行情况Extra这个字段中的“Using filesort”表示的就是需要排序，MySQL会给每个线程分配一块内存用于排序，称为sort_buffer。

为了说明这个SQL查询语句的执行过程，我们先来看一下city这个索引的示意图。

图2 city字段的索引示意图从图中可以看到，满足city=’杭州’条件的行，是从ID_X到ID_(X+N)的这些记录。

通常情况下，这个语句执行流程如下所示 ：

1. 初始化sort_buffer，确定放入name、city、age这三个字段；
2. 从索引city找到第一个满足city=’杭州’条件的主键id，也就是图中的ID_X；
3. 到主键id索引取出整行，取name、city、age三个字段的值，存入sort_buffer中；
4. 从索引city取下一个记录的主键id；
5. 重复步骤3、4直到city的值不满足查询条件为止，对应的主键id也就是图中的ID_Y；
6. 对sort_buffer中的数据按照字段name做快速排序；
7. 按照排序结果取前1000行返回给客户端。

我们暂且把这个排序过程，称为全字段排序，执行流程的示意图如下所示，下一篇文章中我们还会用到这个排序。

图3 全字段排序图中“按name排序”这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数sort_buffer_size。

sort_buffer_size，就是MySQL为排序开辟的内存（sort_buffer）的大小。

如果要排序的数据量小于sort_buffer_size，排序就在内存中完成。

但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

你可以用下面介绍的方法，来确定一个排序语句是否使用了临时文件。

这个方法是通过查看 OPTIMIZER_TRACE 的结果来确认的，你可以从 number_of_tmp_files中看到是否使用了临时文件。

/* 打开optimizer_trace，只对本线程有效 /SET optimizer_trace=’enabled=on’; / @a保存Innodb_rows_read的初始值 /select VARIABLE_VALUE into @a from performance_schema.session_status where variable_name = ‘Innodb_rows_read’;/ 执行语句 /select city, name,age from t where city=’杭州’ order by name limit 1000; / 查看 OPTIMIZER_TRACE 输出 /SELECT * FROM information_schema.OPTIMIZER_TRACE\G/ @b保存Innodb_rows_read的当前值 /select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = ‘Innodb_rows_read’;/ 计算Innodb_rows_read差值 */select @b-@a;图4 全排序的OPTIMIZER_TRACE部分结果number_of_tmp_files表示的是，排序过程中使用的临时文件数。

你一定奇怪，为什么需要12个文件？内存放不下时，就需要使用外部排序，外部排序一般使用归并排序算法。

可以这么简单理解，MySQL将需要排序的数据分成12份，每一份单独排序后存在这些临时文件中。

然后把这12个有序文件再合并成一个有序的大文件。

如果sort_buffer_size超过了需要排序的数据量的大小，number_of_tmp_files就是0，表示排序可以直接在内存中完成。

否则就需要放在临时文件中排序。

sort_buffer_size越小，需要分成的份数越多，number_of_tmp_files的值就越大。

接下来，我再和你解释一下图4中其他两个值的意思。

我们的示例表中有4000条满足city=’杭州’的记录，所以你可以看到 examined_rows=4000，表示参与排序的行数是4000行。

sort_mode 里面的packed_additional_fields的意思是，排序过程对字符串做了“紧凑”处理。

即使name字段的定义是varchar(16)，在排序过程中还是要按照实际长度来分配空间的。

同时，最后一个查询语句select @b-@a 的返回结果是4000，表示整个执行过程只扫描了4000行。

这里需要注意的是，为了避免对结论造成干扰，我把internal_tmp_disk_storage_engine设置成MyISAM。

否则，select @b-@a的结果会显示为4001。

这是因为查询OPTIMIZER_TRACE这个表时，需要用到临时表，而internal_tmp_disk_storage_engine的默认值是InnoDB。

如果使用的是InnoDB引擎的话，把数据从临时表取出来的时候，会让Innodb_rows_read的值加1。

rowid排序在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在sort_buffer和临时文件中执行的。

但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么sort_buffer里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

所以如果单行很大，这个方法效率不够好。

那么，如果MySQL认为排序的单行长度太大会怎么做呢？接下来，我来修改一个参数，让MySQL采用另外一种算法。

max_length_for_sort_data，是MySQL中专门控制用于排序的行数据的长度的一个参数。

它的意思是，如果单行的长度超过这个值，MySQL就认为单行太大，要换一个算法。

city、name、age 这三个字段的定义总长度是36，我把max_length_for_sort_data设置为16，我们再来看看计算过程有什么改变。

新的算法放入sort_buffer的字段，只有要排序的列（即name字段）和主键id。

但这时，排序的结果就因为少了city和age字段的值，不能直接返回了，整个执行流程就变成如下所示的样子：

1. 初始化sort_buffer，确定放入两个字段，即name和id；
2. 从索引city找到第一个满足city=’杭州’条件的主键id，也就是图中的ID_X；
3. 到主键id索引取出整行，取name、id这两个字段，存入sort_buffer中；
4. 从索引city取下一个记录的主键id；
5. 重复步骤3、4直到不满足city=’杭州’条件为止，也就是图中的ID_Y；
6. 对sort_buffer中的数据按照字段name进行排序；
7. 遍历排序结果，取前1000行，并按照id的值回到原表中取出city、name和age三个字段返回给客户端。

这个执行流程的示意图如下，我把它称为rowid排序。

SET max_length_for_sort_data = 16;图5 rowid排序对比图3的全字段排序流程图你会发现，rowid排序多访问了一次表t的主键索引，就是步骤7。

需要说明的是，最后的“结果集”是一个逻辑概念，实际上MySQL服务端从排序后的sort_buffer中依次取出id，然后到原表查到city、name和age这三个字段的结果，不需要在服务端再耗费内存存储结果，是直接返回给客户端的。

根据这个说明过程和图示，你可以想一下，这个时候执行select @b-@a，结果会是多少呢？现在，我们就来看看结果有什么不同。

首先，图中的examined_rows的值还是4000，表示用于排序的数据是4000行。

但是select @b-@a这个语句的值变成5000了。

因为这时候除了排序过程外，在排序完成后，还要根据id去原表取值。

由于语句是limit 1000，因此会多读1000行。

图6 rowid排序的OPTIMIZER_TRACE部分输出从OPTIMIZER_TRACE的结果中，你还能看到另外两个信息也变了。

sort_mode变成了<sort_key, rowid>，表示参与排序的只有name和id这两个字段。

number_of_tmp_files变成10了，是因为这时候参与排序的行数虽然仍然是4000行，但是每一行都变小了，因此需要排序的总数据量就变小了，需要的临时文件也相应地变少了。

全字段排序 VS rowid排序我们来分析一下，从这两个执行流程里，还能得出什么结论。

如果MySQL实在是担心排序内存太小，会影响排序效率，才会采用rowid排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。

如果MySQL认为内存足够大，会优先选择全字段排序，把需要的字段都放到sort_buffer中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。

这也就体现了MySQL的一个设计思想：如果内存够，就要多利用内存，尽量减少磁盘访问。

对于InnoDB表来说，rowid排序会要求回表多造成磁盘读，因此不会被优先选择。

这个结论看上去有点废话的感觉，但是你要记住它，下一篇文章我们就会用到。

看到这里，你就了解了，MySQL做排序是一个成本比较高的操作。

那么你会问，是不是所有的order by都需要排序操作呢？如果不排序就能得到正确的结果，那对系统的消耗会小很多，语句的执行时间也会变得更短。

其实，并不是所有的order by语句，都需要排序操作的。

从上面分析的执行过程，我们可以看到，MySQL之所以需要生成临时表，并且在临时表上做排序操作，其原因是原来的数据都是无序的。

你可以设想下，如果能够保证从city这个索引上取出来的行，天然就是按照name递增排序的话，是不是就可以不用再排序了呢？确实是这样的。

所以，我们可以在这个市民表上创建一个city和name的联合索引，对应的SQL语句是：
作为与city索引的对比，我们来看看这个索引的示意图。

图7 city和name联合索引示意图在这个索引里面，我们依然可以用树搜索的方式定位到第一个满足city=’杭州’的记录，并且额外确保了，接下来按顺序取“下一条记录”的遍历过程中，只要city的值是杭州，name的值就一定是有序的。

这样整个查询过程的流程就变成了：

1. 从索引(city,name)找到第一个满足city=’杭州’条件的主键id；
2. 到主键id索引取出整行，取name、city、age三个字段的值，作为结果集的一部分直接返回；
3. 从索引(city,name)取下一个记录主键id；
   alter table t add index city_user(city, name);4. 重复步骤2、3，直到查到第1000条记录，或者是不满足city=’杭州’条件时循环结束。

图8 引入(city,name)联合索引后，查询语句的执行计划可以看到，这个查询过程不需要临时表，也不需要排序。

接下来，我们用explain的结果来印证一下。

图9 引入(city,name)联合索引后，查询语句的执行计划从图中可以看到，Extra字段中没有Using filesort了，也就是不需要排序了。

而且由于(city,name)这个联合索引本身有序，所以这个查询也不用把4000行全都读一遍，只要找到满足条件的前1000条记录就可以退出了。

也就是说，在我们这个例子里，只需要扫描1000次。

既然说到这里了，我们再往前讨论，这个语句的执行流程有没有可能进一步简化呢？不知道你还记不记得，我在第5篇文章《 深入浅出索引（下）》中，和你介绍的覆盖索引。

这里我们可以再稍微复习一下。

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键索引上去取数据。

按照覆盖索引的概念，我们可以再优化一下这个查询语句的执行流程。

针对这个查询，我们可以创建一个city、name和age的联合索引，对应的SQL语句就是：
这时，对于city字段的值相同的行来说，还是按照name字段的值递增排序的，此时的查询语句也就不再需要排序了。

这样整个查询语句的执行流程就变成了：

1. 从索引(city,name,age)找到第一个满足city=’杭州’条件的记录，取出其中的city、name和age这三个字段的值，作为结果集的一部分直接返回；
2. 从索引(city,name,age)取下一个记录，同样取出这三个字段的值，作为结果集的一部分直接返回；
3. 重复执行步骤2，直到查到第1000条记录，或者是不满足city=’杭州’条件时循环结束。

图10 引入(city,name,age)联合索引后，查询语句的执行流程然后，我们再来看看explain的结果。

alter table t add index city_user_age(city, name, age);图11 引入(city,name,age)联合索引后，查询语句的执行计划可以看到，Extra字段里面多了“Using index”，表示的就是使用了覆盖索引，性能上会快很多。

当然，这里并不是说要为了每个查询能用上覆盖索引，就要把语句中涉及的字段都建上联合索引，毕竟索引还是有维护代价的。

这是一个需要权衡的决定。

小结今天这篇文章，我和你介绍了MySQL里面order by语句的几种算法流程。

在开发系统的时候，你总是不可避免地会使用到order by语句。

你心里要清楚每个语句的排序逻辑是怎么实现的，还要能够分析出在最坏情况下，每个语句的执行对系统资源的消耗，这样才能做到下笔如有神，不犯低级错误。

最后，我给你留下一个思考题吧。

假设你的表里面已经有了city_name(city, name)这个联合索引，然后你要查杭州和苏州两个城市中所有的市民的姓名，并且按名字排序，显示前100条记录。

如果SQL查询语句是这么写的 ：
那么，这个语句执行的时候会有排序过程吗，为什么？如果业务端代码由你来开发，需要实现一个在数据库端不需要排序的方案，你会怎么实现呢？进一步地，如果有分页需求，要显示第101页，也就是说语句最后要改成 “limit 10000,100”， 你的实现方法又会是什么呢？你可以把你的思考和观点写在留言区里，我会在下一篇文章的末尾和你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间上期的问题是，当MySQL去更新一行，但是要修改的值跟原来的值是相同的，这时候MySQL会真的去执行一次修改吗？还是看到值相同就直接返回呢？这是第一次我们课后问题的三个选项都有同学选的，所以我要和你需要详细说明一下。

第一个选项是，MySQL读出数据，发现值与原来相同，不更新，直接返回，执行结束。

这里我们可以用一个锁实验来确认。

mysql> select * from t where city in (‘杭州’,”苏州”) order by name limit 100;假设，当前表t里的值是(1,2)。

图12 锁验证方式session B的update 语句被blocked了，加锁这个动作是InnoDB才能做的，所以排除选项1。

第二个选项是，MySQL调用了InnoDB引擎提供的接口，但是引擎发现值与原来相同，不更新，直接返回。

有没有这种可能呢？这里我用一个可见性实验来确认。

假设当前表里的值是(1,2)。

图13 可见性验证方式session A的第二个select 语句是一致性读（快照读)，它是不能看见session B的更新的。

现在它返回的是(1,3)，表示它看见了某个新的版本，这个版本只能是session A自己的update语句做更新的时候生成。

（如果你对这个逻辑有疑惑的话，可以回顾下第8篇文章《事务到底是隔离的还是不隔离的？》中的相关内容）所以，我们上期思考题的答案应该是选项3，即：InnoDB认真执行了“把这个值修改成(1,2)”这个操作，该加锁的加锁，该更新的更新。

然后你会说，MySQL怎么这么笨，就不会更新前判断一下值是不是相同吗？如果判断一下，不就不用浪费InnoDB操作，多去更新一次了？其实MySQL是确认了的。

只是在这个语句里面，MySQL认为读出来的值，只有一个确定的(id=1), 而要写的是(a=3)，只从这两个信息是看不出来“不需要修改”的。

问题解析

答疑文章（一）：日志和索引相关问题
在今天这篇答疑文章更新前，MySQL实战这个专栏已经更新了14篇。

在这些文章中，大家在评论区留下了很多高质量的留言。

现在，每篇文章的评论区都有热心的同学帮忙总结文章知识点，也有不少同学提出了很多高质量的问题，更有一些同学帮忙解答其他同学提出的问题。

在浏览这些留言并回复的过程中，我倍受鼓舞，也尽我所知地帮助你解决问题、和你讨论。

可以说，你们的留言活跃了整个专栏的氛围、提升了整个专栏的质量，谢谢你们。

评论区的大多数留言我都直接回复了，对于需要展开说明的问题，我都拿出小本子记了下来。

这些被记下来的问题，就是我们今天这篇答疑文章的素材了。

到目前为止，我已经收集了47个问题，很难通过今天这一篇文章全部展开。

所以，我就先从中找了几个联系非常紧密的问题，串了起来，希望可以帮你解决关于日志和索引的一些疑惑。

而其他问题，我们就留着后面慢慢展开吧。

日志相关问题我在第2篇文章《日志系统：一条SQL更新语句是如何执行的？》中，和你讲到binlog（归档日志）和redo log（重做日志）配合崩溃恢复的时候，用的是反证法，说明了如果没有两阶段提交，会导致MySQL出现主备数据不一致等问题。

在这篇文章下面，很多同学在问，在两阶段提交的不同瞬间，MySQL如果发生异常重启，是怎么保证数据完整性的？现在，我们就从这个问题开始吧。

我再放一次两阶段提交的图，方便你学习下面的内容。

图1 两阶段提交示意图这里，我要先和你解释一个误会式的问题。

有同学在评论区问到，这个图不是一个update语句的执行流程吗，怎么还会调用commit语句？他产生这个疑问的原因，是把两个“commit”的概念混淆了：

他说的“commit语句”，是指MySQL语法中，用于提交一个事务的命令。

一般跟begin/starttransaction 配对使用。

而我们图中用到的这个“commit步骤”，指的是事务提交过程中的一个小步骤，也是最后一步。

当这个步骤执行完成后，这个事务就提交完成了。

“commit语句”执行的时候，会包含“commit 步骤”。

而我们这个例子里面，没有显式地开启事务，因此这个update语句自己就是一个事务，在执行完成后提交事务时，就会用到这个“commit步骤“。

接下来，我们就一起分析一下在两阶段提交的不同时刻，MySQL异常重启会出现什么现象。

如果在图中时刻A的地方，也就是写入redo log 处于prepare阶段之后、写binlog之前，发生了崩溃（crash），由于此时binlog还没写，redo log也还没提交，所以崩溃恢复的时候，这个事务会回滚。

这时候，binlog还没写，所以也不会传到备库。

到这里，大家都可以理解。

大家出现问题的地方，主要集中在时刻B，也就是binlog写完，redo log还没commit前发生crash，那崩溃恢复的时候MySQL会怎么处理？我们先来看一下崩溃恢复时的判断规则。

1. 如果redo log里面的事务是完整的，也就是已经有了commit标识，则直接提交；

2. 如果redo log里面的事务只有完整的prepare，则判断对应的事务binlog是否存在并完整：

a. 如果是，则提交事务；

b. 否则，回滚事务。

这里，时刻B发生crash对应的就是2(a)的情况，崩溃恢复过程中事务会被提交。

现在，我们继续延展一下这个问题。

追问1：MySQL怎么知道binlog是完整的?回答：一个事务的binlog是有完整格式的：

statement格式的binlog，最后会有COMMIT；

row格式的binlog，最后会有一个XID event。

另外，在MySQL 5.6.2版本以后，还引入了binlog-checksum参数，用来验证binlog内容的正确性。

对于binlog日志由于磁盘原因，可能会在日志中间出错的情况，MySQL可以通过校验checksum的结果来发现。

所以，MySQL还是有办法验证事务binlog的完整性的。

追问2：redo log 和 binlog是怎么关联起来的?回答：它们有一个共同的数据字段，叫XID。

崩溃恢复的时候，会按顺序扫描redo log：

如果碰到既有prepare、又有commit的redo log，就直接提交；

如果碰到只有parepare、而没有commit的redo log，就拿着XID去binlog找对应的事务。

追问3：处于prepare阶段的redo log加上完整binlog，重启就能恢复，MySQL为什么要这么设计?回答：其实，这个问题还是跟我们在反证法中说到的数据与备份的一致性有关。

在时刻B，也就是binlog写完以后MySQL发生崩溃，这时候binlog已经写入了，之后就会被从库（或者用这个binlog恢复出来的库）使用。

所以，在主库上也要提交这个事务。

采用这个策略，主库和备库的数据就保证了一致性。

追问4：如果这样的话，为什么还要两阶段提交呢？干脆先redo log写完，再写binlog。

崩溃恢复的时候，必须得两个日志都完整才可以。

是不是一样的逻辑？回答：其实，两阶段提交是经典的分布式系统问题，并不是MySQL独有的。

如果必须要举一个场景，来说明这么做的必要性的话，那就是事务的持久性问题。

对于InnoDB引擎来说，如果redo log提交完成了，事务就不能回滚（如果这还允许回滚，就可能覆盖掉别的事务的更新）。

而如果redo log直接提交，然后binlog写入的时候失败，InnoDB又回滚不了，数据和binlog日志又不一致了。

两阶段提交就是为了给所有人一个机会，当每个人都说“我ok”的时候，再一起提交。

追问5：不引入两个日志，也就没有两阶段提交的必要了。

只用binlog来支持崩溃恢复，又能支持归档，不就可以了？回答：这位同学的意思是，只保留binlog，然后可以把提交流程改成这样：… -> “数据更新到内存” -> “写 binlog” -> “提交事务”，是不是也可以提供崩溃恢复的能力？答案是不可以。

如果说历史原因的话，那就是InnoDB并不是MySQL的原生存储引擎。

MySQL的原生引擎是MyISAM，设计之初就有没有支持崩溃恢复。

InnoDB在作为MySQL的插件加入MySQL引擎家族之前，就已经是一个提供了崩溃恢复和事务支持的引擎了。

InnoDB接入了MySQL后，发现既然binlog没有崩溃恢复的能力，那就用InnoDB原有的redo log好了。

而如果说实现上的原因的话，就有很多了。

就按照问题中说的，只用binlog来实现崩溃恢复的流程，我画了一张示意图，这里就没有redo log了。

图2 只用binlog支持崩溃恢复这样的流程下，binlog还是不能支持崩溃恢复的。

我说一个不支持的点吧：binlog没有能力恢复“数据页”。

如果在图中标的位置，也就是binlog2写完了，但是整个事务还没有commit的时候，MySQL发生了crash。

重启后，引擎内部事务2会回滚，然后应用binlog2可以补回来；但是对于事务1来说，系统已经认为提交完成了，不会再应用一次binlog1。

但是，InnoDB引擎使用的是WAL技术，执行事务的时候，写完内存和日志，事务就算完成了。

如果之后崩溃，要依赖于日志来恢复数据页。

也就是说在图中这个位置发生崩溃的话，事务1也是可能丢失了的，而且是数据页级的丢失。

此时，binlog里面并没有记录数据页的更新细节，是补不回来的。

你如果要说，那我优化一下binlog的内容，让它来记录数据页的更改可以吗？但，这其实就是又做了一个redo log出来。

所以，至少现在的binlog能力，还不能支持崩溃恢复。

追问6：那能不能反过来，只用redo log，不要binlog？回答：如果只从崩溃恢复的角度来讲是可以的。

你可以把binlog关掉，这样就没有两阶段提交了，但系统依然是crash-safe的。

但是，如果你了解一下业界各个公司的使用场景的话，就会发现在正式的生产库上，binlog都是开着的。

因为binlog有着redo log无法替代的功能。

一个是归档。

redo log是循环写，写到末尾是要回到开头继续写的。

这样历史日志没法保留，redo log也就起不到归档的作用。

一个就是MySQL系统依赖于binlog。

binlog作为MySQL一开始就有的功能，被用在了很多地方。

其中，MySQL系统高可用的基础，就是binlog复制。

还有很多公司有异构系统（比如一些数据分析系统），这些系统就靠消费MySQL的binlog来更新自己的数据。

关掉binlog的话，这些下游系统就没法输入了。

总之，由于现在包括MySQL高可用在内的很多系统机制都依赖于binlog，所以“鸠占鹊巢”redolog还做不到。

你看，发展生态是多么重要。

追问7：redo log一般设置多大？回答：redo log太小的话，会导致很快就被写满，然后不得不强行刷redo log，这样WAL机制的能力就发挥不出来了。

所以，如果是现在常见的几个TB的磁盘的话，就不要太小气了，直接将redo log设置为4个文件、每个文件1GB吧。

追问8：正常运行中的实例，数据写入后的最终落盘，是从redo log更新过来的还是从buffer pool更新过来的呢？回答：这个问题其实问得非常好。

这里涉及到了，“redo log里面到底是什么”的问题。

实际上，redo log并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由redo log更新过去”的情况。

1. 如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。

最终数据落盘，就是把内存中的数据页写盘。

这个过程，甚至与redo log毫无关系。

2. 在崩溃恢复场景中，InnoDB如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让redo log更新内存内容。

更新完成后，内存页变成脏页，就回到了第一种情况的状态。

追问9：redo log buffer是什么？是先修改内存，还是先写redo log文件？回答：这两个问题可以一起回答。

在一个事务的更新过程中，日志是要写多次的。

比如下面这个事务：

这个事务要往两个表中插入记录，插入数据的过程中，生成的日志都得先保存起来，但又不能在还没commit的时候就直接写到redo log文件里。

所以，redo log buffer就是一块内存，用来先存redo日志的。

也就是说，在执行第一个insert的时候，数据的内存被修改了，redo log buffer也写入了日志。

但是，真正把日志写到redo log文件（文件名是 ib_logfile+数字），是在执行commit语句的时候做的。

（这里说的是事务执行过程中不会“主动去刷盘”，以减少不必要的IO消耗。

但是可能会出现“被动写入磁盘”，比如内存不够、其他事务提交等情况。

这个问题我们会在后面第22篇文章《MySQL有哪些“饮鸩止渴”的提高性能的方法？》中再详细展开）。

单独执行一个更新语句的时候，InnoDB会自己启动一个事务，在语句执行完成的时候提交。

过程跟上面是一样的，只不过是“压缩”到了一个语句里面完成。

以上这些问题，就是把大家提过的关于redo log和binlog的问题串起来，做的一次集中回答。

如果你还有问题，可以在评论区继续留言补充。

业务设计问题接下来，我再和你分享@ithunter 同学在第8篇文章《事务到底是隔离的还是不隔离的？》的评论区提到的跟索引相关的一个问题。

我觉得这个问题挺有趣、也挺实用的，其他同学也可能会碰上这样的场景，在这里解答和分享一下。

问题是这样的（我文字上稍微做了点修改，方便大家理解）：

begin;insert into t1 …insert into t2 …commit;业务上有这样的需求，A、B两个用户，如果互相关注，则成为好友。

设计上是有两张表，一个是like表，一个是friend表，like表有user_id、liker_id两个字段，我设置为复合唯一索引即首先，我要先赞一下这样的提问方式。

虽然极客时间现在的评论区还不能追加评论，但如果大家能够一次留言就把问题讲清楚的话，其实影响也不大。

所以，我希望你在留言提问的时候，也能借鉴这种方式。

接下来，我把@ithunter 同学说的表模拟出来，方便我们讨论。

虽然这个题干中，并没有说到friend表的索引结构。

但我猜测friend_1_id和friend_2_id也有索uk_user_id_liker_id。

语句执行逻辑是这样的：

以A关注B为例：

第一步，先查询对方有没有关注自己（B有没有关注A）select * from like where user_id = B and liker_id = A;如果有，则成为好友insert into friend;没有，则只是单向关注关系insert into like;但是如果A、B同时关注对方，会出现不会成为好友的情况。

因为上面第1步，双方都没关注对方。

第1步即使使用了排他锁也不行，因为记录不存在，行锁无法生效。

请问这种情况，在MySQL锁层面有没有办法处理？CREATE TABLE like ( id int(11) NOT NULL AUTO_INCREMENT, user_id int(11) NOT NULL, liker_id int(11) NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_user_id_liker_id (user_id,liker_id)) ENGINE=InnoDB;CREATE TABLE friend ( idint(11) NOT NULL AUTO_INCREMENT, friend_1_idint(11) NOT NULL, firned_2_idint(11) NOT NULL, UNIQUE KEYuk_friend (friend_1_id,firned_2_id) PRIMARY KEY (id`)) ENGINE=InnoDB;引，为便于描述，我给加上唯一索引。

顺便说明一下，“like”是关键字，我一般不建议使用关键字作为库名、表名、字段名或索引名。

我把他的疑问翻译一下，在并发场景下，同时有两个人，设置为关注对方，就可能导致无法成功加为朋友关系。

现在，我用你已经熟悉的时刻顺序表的形式，把这两个事务的执行语句列出来：

图3 并发“喜欢”逻辑操作顺序由于一开始A和B之间没有关注关系，所以两个事务里面的select语句查出来的结果都是空。

因此，session 1的逻辑就是“既然B没有关注A，那就只插入一个单向关注关系”。

session 2也同样是这个逻辑。

这个结果对业务来说就是bug了。

因为在业务设定里面，这两个逻辑都执行完成以后，是应该在friend表里面插入一行记录的。

如提问里面说的，“第1步即使使用了排他锁也不行，因为记录不存在，行锁无法生效”。

不过，我想到了另外一个方法，来解决这个问题。

首先，要给“like”表增加一个字段，比如叫作 relation_ship，并设为整型，取值1、2、3。

值是1的时候，表示user_id 关注 liker_id;值是2的时候，表示liker_id 关注 user_id;值是3的时候，表示互相关注。

然后，当 A关注B的时候，逻辑改成如下所示的样子：

应用代码里面，比较A和B的大小，如果A<B，就执行下面的逻辑如果A>B，则执行下面的逻辑这个设计里，让“like”表里的数据保证user_id < liker_id，这样不论是A关注B，还是B关注A，在操作“like”表的时候，如果反向的关系已经存在，就会出现行锁冲突。

然后，insert … on duplicate语句，确保了在事务内部，执行了这个SQL语句后，就强行占住了这个行锁，之后的select 判断relation_ship这个逻辑时就确保了是在行锁保护下的读操作。

操作符 “|” 是按位或，连同最后一句insert语句里的ignore，是为了保证重复调用时的幂等性。

这样，即使在双方“同时”执行关注操作，最终数据库里的结果，也是like表里面有一条关于A和B的记录，而且relation_ship的值是3， 并且friend表里面也有了A和B的这条记录。

mysql> begin; /启动事务/insert into like(user_id, liker_id, relation_ship) values(A, B, 1) on duplicate key update relation_ship=relation_ship | 1;select relation_ship from like where user_id=A and liker_id=B;/*代码中判断返回的 relation_ship， 如果是1，事务结束，执行 commit 如果是3，则执行下面这两个语句：

*/insert ignore into friend(friend_1_id, friend_2_id) values(A,B);commit;mysql> begin; /启动事务/insert into like(user_id, liker_id, relation_ship) values(B, A, 2) on duplicate key update relation_ship=relation_ship | 2;select relation_ship from like where user_id=B and liker_id=A;/*代码中判断返回的 relation_ship， 如果是2，事务结束，执行 commit 如果是3，则执行下面这两个语句：

*/insert ignore into friend(friend_1_id, friend_2_id) values(B,A);commit;不知道你会不会吐槽：之前明明还说尽量不要使用唯一索引，结果这个例子一上来我就创建了两个。

这里我要再和你说明一下，之前文章我们讨论的，是在“业务开发保证不会插入重复记录”的情况下，着重要解决性能问题的时候，才建议尽量使用普通索引。

而像这个例子里，按照这个设计，业务根本就是保证“我一定会插入重复数据，数据库一定要要有唯一性约束”，这时就没啥好说的了，唯一索引建起来吧。

小结这是专栏的第一篇答疑文章。

我针对前14篇文章，大家在评论区中的留言，从中摘取了关于日志和索引的相关问题，串成了今天这篇文章。

这里我也要再和你说一声，有些我答应在答疑文章中进行扩展的话题，今天这篇文章没来得及扩展，后续我会再找机会为你解答。

所以，篇幅所限，评论区见吧。

最后，虽然这篇是答疑文章，但课后问题还是要有的。

我们创建了一个简单的表t，并插入一行，然后对这一行做修改。

这时候，表t里有唯一的一行数据(1,2)。

假设，我现在要执行：

你会看到这样的结果：

结果显示，匹配(rows matched)了一行，修改(Changed)了0行。

仅从现象上看，MySQL内部在处理这个命令的时候，可以有以下三种选择：

1. 更新都是先读后写的，MySQL读出数据，发现a的值本来就是2，不更新，直接返回，执行mysql> CREATE TABLE t (id int(11) NOT NULL primary key auto_increment,a int(11) DEFAULT NULL) ENGINE=InnoDB;insert into t values(1,2);mysql> update t set a=2 where id=1;结束；

2. MySQL调用了InnoDB引擎提供的“修改为(1,2)”这个接口，但是引擎发现值与原来相同，不更新，直接返回；

3. InnoDB认真执行了“把这个值修改成(1,2)”这个操作，该加锁的加锁，该更新的更新。

你觉得实际情况会是以上哪种呢？你可否用构造实验的方式，来证明你的结论？进一步地，可以思考一下，MySQL为什么要选择这种策略呢？你可以把你的验证方法和思考写在留言区里，我会在下一篇文章的末尾和你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间上期的问题是，用一个计数表记录一个业务表的总行数，在往业务表插入数据的时候，需要给计数值加1。

逻辑实现上是启动一个事务，执行两个语句：

1. insert into 数据表；

2. update 计数表，计数值加1。

从系统并发能力的角度考虑，怎么安排这两个语句的顺序。

这里，我直接复制 @阿建 的回答过来供你参考：

评论区有同学说，应该把update计数表放后面，因为这个计数表可能保存了多个业务表的计数值。

如果把update计数表放到事务的第一个语句，多个业务表同时插入数据的话，等待时间会更长。

这个答案的结论是对的，但是理解不太正确。

即使我们用一个计数表记录多个业务表的行数，也肯定会给表名字段加唯一索引。

类似于下面这样的表结构：

并发系统性能的角度考虑，应该先插入操作记录，再更新计数表。

知识点在《行锁功过：怎么减少行锁对性能的影响？》因为更新计数表涉及到行锁的竞争，先插入再更新能最大程度地减少事务之间的锁等待，提升并发度。

在更新计数表的时候，一定会传入where table_name=$table_name，使用主键索引，更新加行锁只会锁在一行上。

而在不同业务表插入数据，是更新不同的行，不会有行锁。

问题解析

count(*)语句实现方式

在不同的MySQL引擎中，count(*)有不同的实现方式。

MyISAM引擎

MyISAM引擎把一个表的总行数存在了磁盘上，执行count(*)的时候会直接返回这个数，效率很高； 加 where 条件后，无法直接得到结果，也需要过滤。

InnoDB引擎

InnoDB引执行count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

InnoDB不论是在事务支持、并发能力还是在数据安全方面，InnoDB都优于MyISAM。

当你的记录数越来越多的时候，计算一个表的总行数会越来越慢。

为什么InnoDB不跟MyISAM一样，也把数字存起来呢？因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

这里，我用一个算count(*)的例子来为你解释一下。

表“应该返回多少行”也是不确定的。

假设表t中现在有10000条记录，我们设计了三个用户并行的会话。

假设表t中现在有10000条记录，我们设计了三个用户并行的会话。

会话A先启动事务并查询一次表的总行数；

会话B启动事务，插入一行后记录后，查询表的总行数；

会话C先启动一个单独的语句，插入一行记录后，查询表的总行数。

会话C先启动一个单独的语句，插入一行记录后，查询表的总行数。

我们假设从上到下是按照时间顺序执行的，同一行语句是在同一时刻执行的。

我们假设从上到下是按照时间顺序执行的，同一行语句是在同一时刻执行的。

你会看到，在最后一个时刻，三个会话A、B、C会同时查询表t的总行数，但拿到的结果却不同。

这和InnoDB的事务设计有关系，可重复读是它默认的隔离级别，在代码上就是通过多版本并发控制，也就是MVCC来实现的。

每一行记录都要判断自己是否对这个会话可见，因此对于count(*)请求来说，InnoDB只好把数据一行一行地读出依次判断。

优化方法InnoDB是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。

所以，普通索引树比主键索引树小很多。

对于count(*)这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。

因此，MySQL优化器会找到最小的那棵树来遍历。

在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

如果你用过show table status 命令的话，就会发现这个命令的输出结果里面也有一个TABLE_ROWS用于显示这个表当前有多少行，这个命令执行挺快的，那这个TABLE_ROWS能代替count(*)吗？索引统计的值是通过采样来估算的。

实际上，TABLE_ROWS就是从这个采样估算得来的，因此它也很不准。

是通过采样来估算的。

有多不准呢，官方文档说误差可能达到40%到50%。

所以，show table status命令显示的行数也不能直接使用。

MyISAM表虽然count(*)很快，但是不支持事务；

show table status命令虽然返回很快，但是不准确；

InnoDB表直接count(*)会遍历全表，虽然结果准确，但会导致性能问题。

InnoDB表直接count(*)会遍历全表，虽然结果准确，但会导致性能问题。

如果你现在有一个页面经常要显示交易系统的操作记录总数，到底## 最佳实现自己计数### 用缓存系统保存计数对于更新很频繁的库来说，你可能会第一时间想到，用缓存系统来支持。

可以用一个Redis服务来保存这个表的总行数。

这个表每被插入一行Redis计数就加1，每被删除一行Redis计数就减1。

这种方式下，读和更新操作都很快，但你再想一下这种方式存在什么问题吗？没错，缓存系统可能会丢失更新。

Redis的数据不能永久地留在内存里，所以你会找一个地方把这个值定期地持久化存储起来。

但即使这样，仍然可能丢失更新。

试想如果刚刚在数据表中插入了一行，Redis中保存的值也加了1，然后Redis异常重启了，重启后你要从存储redis数据的地方把这个值读回来，而刚刚加1的这个计数操作却丢失了。

当然了，这还是有解的。

比如，Redis异常重启以后，到数据库里面单独执行一次count(*)获取真实的行数，再把这个值写回到Redis里就可以了。

异常重启毕竟不是经常出现的情况，这一次全表扫描的成本，还是可以接受的。

但实际上，将计数保存在缓存系统中的方式，还不只是丢失更新的问题。

即使Redis正常工作，这个值还是逻辑上不精确的。

你可以设想一下有这么一个页面，要显示操作记录的总数，同时还要显示最近操作的100条记录。

那么，这个页面的逻辑就需要先到Redis里面取出计数，再到数据表里面取数据记录，这个页面的逻辑就需要先到Redis里面取出计数，再到数据表里面取数据记录。

我们是这么定义不精确的：

1. 一种是，查到的100行结果里面有最新插入记录，而Redis的计数里还没加1；

2. 另一种是，查到的100行结果里没有最新插入的记录，而Redis的计数里已经加了1。

这两种情况，都是逻辑不一致的。

会话A是一个插入交易记录的逻辑，往数据表里插入一行R，然后Redis计数加1；会话B就是查询页面显示时需要的数据。

在图2的这个时序里，在T3时刻会话B来查询的时候，会显示出新插入的R这个记录，但是Redis的计数还没加1。

这时候，就会出现我们说的数据不一致。

你一定会说，这是因为我们执行新增记录逻辑时候，是先写数据表，再改Redis计数。

而读的时候是先读Redis，再读数据表，这个顺序是相反的。

那么，如果保持顺序一样的话，是不是就没问题了？我们现在把会话A的更新顺序换一下，再看看执行结果。

问题了？我们现在把会话A的更新顺序换一下，再看看执行结果。

调整顺序后，会话B在T3时刻查询的时候，Redis计数加了1了，但还查不到新插入的R这一行，也是数据不一致的情况。

在并发系统里面，我们是无法精确控制不同线程的执行时刻的，因为存在图中的这种操作序列，所以，我们说即使Redis正常工作，这个计数值还是逻辑上不精确的。

用数据库保存计数把这个计数直接放到数据库里单独的一张计数表C中，又会怎么样呢？首先，这解决了崩溃丢失的问题，InnoDB是支持崩溃恢复不丢数据的。

会话B的读操作仍然是在T3执行的，但是因为这时候更新事务还没有提交，所以计数值加1这个操作对会话B还不可见。

还没有提交，所以计数值加1这个操作对会话B还不可见。

因此，会话B看到的结果里， 查计数值和“最近100条记录”看到的结果，逻辑上就是一致的。

不同的count用法基于InnoDB引擎，count(*)、count(主键id)、count(字段)和count(1)等不同用法的性能，有哪些差别。

首先你要弄清楚count()的语义。

count()是一个聚合函数，对于返回的结果集，一行行地判断，如果count函数的参数不是NULL，累计值就加1，否则不加。

最后返回累计值。

所以，count(*)、count(主键id)和count(1) 都表示返回满足条件的结果集的总行数；而count(字段），则表示返回满足条件的数据行里面，参数“字段”不为NULL的总个数。

至于分析性能差别的时候，你可以记住这么几个原则：

1. server层要什么就给什么；

2. InnoDB只给必要的值；

3. 现在的优化器只优化了count(*)的语义为“取行数”，其他“显而易见”的优化并没有做。

这是什么意思呢？接下来，我们就一个个地来看看。

这是什么意思呢？接下来，我们就一个个地来看看。

对于count(主键id)来说，InnoDB引擎会遍历整张表，把每一行的id值都取出来，返回给server层。

server层拿到id后，判断是不可能为空的，就按行累加。

对于count(1)来说，InnoDB引擎遍历整张表，但不取值。

server层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

单看这两个用法的差别的话，你能对比出来，count(1)执行得要比count(主键id)快。

因为从引擎返回id会涉及到解析数据行，以及拷贝字段值的操作。

返回id会涉及到解析数据行，以及拷贝字段值的操作。

对于count(字段)来说：

1. 如果这个“字段”是定义为not null的话，一行行地从记录里面读出这个字段，判断不能为null，按行累加；

2. 如果这个“字段”定义允许为null，那么执行的时候，判断到有可能是null，还要把值取出来再判断一下，不是null才累加。

也就是前面的第一条原则，server层要什么字段，InnoDB就返回什么字段。

但是count(*)是例外，并不会把全部字段取出来，而是专门做了优化，不取值。

count(*)肯定不是null，按行累加。

看到这里，你一定会说，优化器就不能自己判断一下吗，主键id肯定非空啊，为什么不能按照count(*)来处理，多么简单的优化啊。

当然，MySQL专门针对这个语句进行优化，也不是不可以。

但是这种需要专门优化的情况太多了，而且MySQL已经优化过count(*)了，你直接使用这种用法就可以了。

所以结论是：按照效率排序的话，count(字段)<count(主键id)<count(1)≈count(*)今天，我和你聊了聊MySQL中获得表行数的两种方法。

我们提到了在不同引擎中count(*)的实现
把计数放在Redis里面，不能够保证计数和MySQL表里的数据精确一致的原因，是这两个不同的存储构成的系统，不支持分布式事务，无法拿到精确一致的视图。

而把计数值也放在MySQL中，就解决了一致性视图的问题。

InnoDB引擎支持事务，我们利用好事务的原子性和隔离性，就可以简化在业务开发时的逻辑。

我们用事务来确保计数准确。

由于事务可以保证中间结果不被别的事务读到，因此修改计数值和插入新记录的顺序是不影响逻辑结果的。

但是，从并发系统性能的角度考虑，你觉得在这个事务序列里，应该先插入操作记录，还是应该先更新计数表呢？

问题解析

为什么表数据删掉一半，表文件大小不变？经常会有同学来问我，我的数据库占用空间太大，我把一个最大的表删掉了一半的数据，怎么表文件的大小还是没变？那么今天，我就和你聊聊数据库表的空间回收，看看如何解决这个问题。

这里，我们还是针对MySQL中应用最广泛的InnoDB引擎展开讨论。

一个InnoDB表包含两部分，即：表结构定义和数据。

在MySQL 8.0版本以前，表结构是存在以.frm为后缀的文件里。

而MySQL 8.0版本，则已经允许把表结构定义放在系统数据表中了。

因为表结构定义占用的空间很小，所以我们今天主要讨论的是表数据。

接下来，我会先和你说明为什么简单地删除表数据达不到表空间回收的效果，然后再和你介绍正确回收空间的方法。

参数innodb_file_per_table表数据既可以存在共享表空间里，也可以是单独的文件。

这个行为是由参数innodb_file_per_table控制的：

1. 这个参数设置为OFF表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；

2. 这个参数设置为ON表示的是，每个InnoDB表数据存储在一个以 .ibd为后缀的文件中。

从MySQL 5.6.6版本开始，它的默认值就是ON了。

我建议你不论使用MySQL的哪个版本，都将这个值设置为ON。

因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过drop table命令，系统就会直接删除这个文件。

而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

所以，将innodb_file_per_table设置为ON，是推荐做法，我们接下来的讨论都是基于这个设置展开的。

我们在删除整个表的时候，可以使用drop table命令回收表空间。

但是，我们遇到的更多的删除数据的场景是删除某些行，这时就遇到了我们文章开头的问题：表中的数据被删除了，但是表空间却没有被回收。

我们要彻底搞明白这个问题的话，就要从数据删除流程说起了。

数据删除流程我们先再来看一下InnoDB中一个索引的示意图。

在前面第4和第5篇文章中，我和你介绍索引时曾经提到过，InnoDB里的数据都是用B+树的结构组织的。

图1 B+树索引示意图假设，我们要删掉R4这个记录，InnoDB引擎只会把R4这个记录标记为删除。

如果之后要再插入一个ID在300和600之间的记录时，可能会复用这个位置。

但是，磁盘文件的大小并不会缩小。

现在，你已经知道了InnoDB的数据是按页存储的，那么如果我们删掉了一个数据页上的所有记录，会怎么样？答案是，整个数据页就可以被复用了。

但是，数据页的复用跟记录的复用是不同的。

记录的复用，只限于符合范围条件的数据。

比如上面的这个例子，R4这条记录被删除后，如果插入一个ID是400的行，可以直接复用这个空间。

但如果插入的是一个ID是800的行，就不能复用这个位置了。

而当整个页从B+树里面摘掉以后，可以复用到任何位置。

以图1为例，如果将数据页page A上的所有记录删除以后，page A会被标记为可复用。

这时候如果要插入一条ID=50的记录需要使用新页的时候，page A是可以被复用的。

如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。

进一步地，如果我们用delete命令把整个表的数据删除呢？结果就是，所有的数据页都会被标记为可复用。

但是磁盘上，文件不会变小。

你现在知道了，delete命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。

也就是说，通过delete命令是不能回收表空间的。

这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

实际上，不止是删除数据会造成空洞，插入数据也会。

如果数据是按照索引递增顺序插入的，那么索引是紧凑的。

但如果数据是随机插入的，就可能造成索引的数据页分裂。

假设图1中page A已经满了，这时我要再插入一行数据，会怎样呢？图2 插入数据导致页分裂可以看到，由于page A满了，再插入一个ID是550的数据时，就不得不再申请一个新的页面page B来保存数据了。

页分裂完成后，page A的末尾就留下了空洞（注意：实际上，可能不止1个记录的位置是空洞）。

另外，更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。

不难理解，这也是会造成空洞的。

也就是说，经过大量增删改的表，都是可能是存在空洞的。

所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。

而重建表，就可以达到这样的目的。

重建表试想一下，如果你现在有一个表A，需要做空间收缩，为了把表中存在的空洞去掉，你可以怎么做呢？你可以新建一个与表A结构相同的表B，然后按照主键ID递增的顺序，把数据一行一行地从表A里读出来再插入到表B中。

由于表B是新建的表，所以表A主键索引上的空洞，在表B中就都不存在了。

显然地，表B的主键索引更紧凑，数据页的利用率也更高。

如果我们把表B作为临时表，数据从表A导入表B的操作完成后，用表B替换A，从效果上看，就起到了收缩表A空间的作用。

这里，你可以使用alter table A engine=InnoDB命令来重建表。

在MySQL 5.5版本之前，这个命令的执行流程跟我们前面描述的差不多，区别只是这个临时表B不需要你自己创建，MySQL会自动完成转存数据、交换表名、删除旧表的操作。

图3 改锁表DDL显然，花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表A的话，就会造成数据丢失。

因此，在整个DDL过程中，表A中不能有更新。

也就是说，这个DDL不是Online的。

而在MySQL 5.6版本开始引入的Online DDL，对这个操作流程做了优化。

我给你简单描述一下引入了Online DDL之后，重建表的流程：

1. 建立一个临时文件，扫描表A主键的所有数据页；

2. 用数据页中表A的记录生成B+树，存储到临时文件中；

3. 生成临时文件的过程中，将所有对A的操作记录在一个日志文件（row log）中，对应的是图中state2的状态；

4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表A相同的数据文件，对应的就是图中state3的状态；

5. 用临时文件替换表A的数据文件。

图4 Online DDL可以看到，与图3过程的不同之处在于，由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表A做增删改操作。

这也就是Online DDL名字的来源。

我记得有同学在第6篇讲表锁的文章《全局锁和表锁 ：给表加个字段怎么索这么多阻碍？》的评论区留言说，DDL之前是要拿MDL写锁的，这样还能叫Online DDL吗？确实，图4的流程中，alter语句在启动的时候需要获取MDL写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。

为什么要退化呢？为了实现Online，MDL读锁不会阻塞增删改操作。

那为什么不干脆直接解锁呢？为了保护自己，禁止其他线程对这个表同时做DDL。

而对于一个大表来说，Online DDL最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。

所以，相对于整个DDL过程来说，锁的时间非常短。

对业务来说，就可以认为是Online的。

需要补充说明的是，上述的这些重建方法都会扫描原表数据和构建临时文件。

对于很大的表来说，这个操作是很消耗IO和CPU资源的。

因此，如果是线上服务，你要很小心地控制操作时间。

如果想要比较安全的操作的话，我推荐你使用GitHub开源的gh-ost来做。

Online 和 inplace说到Online，我还要再和你澄清一下它和另一个跟DDL有关的、容易混淆的概念inplace的区别。

你可能注意到了，在图3中，我们把表A中的数据导出来的存放位置叫作tmp_table。

这是一个临时表，是在server层创建的。

在图4中，根据表A重建出来的数据是放在“tmp_file”里的，这个临时文件是InnoDB在内部创建出来的。

整个DDL过程都在InnoDB内部完成。

对于server层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。

所以，我现在问你，如果你有一个1TB的表，现在磁盘间是1.2TB，能不能做一个inplace的DDL呢？答案是不能。

因为，tmp_file也是要占用临时空间的。

我们重建表的这个语句alter table t engine=InnoDB，其实隐含的意思是：

跟inplace对应的就是拷贝表的方式了，用法是：

当你使用ALGORITHM=copy的时候，表示的是强制拷贝表，对应的流程就是图3的操作过程。

但我这样说你可能会觉得，inplace跟Online是不是就是一个意思？其实不是的，只是在重建表这个逻辑中刚好是这样而已。

比如，如果我要给InnoDB表的一个字段加全文索引，写法是：

这个过程是inplace的，但会阻塞增删改操作，是非Online的。

如果说这两个逻辑之间的关系是什么的话，可以概括为：

1. DDL过程如果是Online的，就一定是inplace的；

alter table t engine=innodb,ALGORITHM=inplace;alter table t engine=innodb,ALGORITHM=copy;alter table t add FULLTEXT(field_name);2. 反过来未必，也就是说inplace的DDL，有可能不是Online的。

截止到MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引(SPATIAL index)就属于这种情况。

最后，我们再延伸一下。

在第10篇文章《MySQL为什么有时候会选错索引》的评论区中，有同学问到使用optimizetable、analyze table和alter table这三种方式重建表的区别。

这里，我顺便再简单和你解释一下。

从MySQL 5.6版本开始，alter table t engine = InnoDB（也就是recreate）默认的就是上面图4的流程了；

analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了MDL读锁；

optimize table t 等于recreate+analyze。

小结今天这篇文章，我和你讨论了数据库中收缩表空间的方法。

现在你已经知道了，如果要收缩一个表，只是delete掉表里面不用的数据的话，表文件的大小是不会变的，你还要通过alter table命令重建表，才能达到表文件变小的目的。

我跟你介绍了重建表的两种实现方式，Online DDL的方式是可以考虑在业务低峰期使用的，而MySQL 5.5及之前的版本，这个命令是会阻塞DML的，这个你需要特别小心。

最后，又到了我们的课后问题时间。

假设现在有人碰到了一个“想要收缩表空间，结果适得其反”的情况，看上去是这样的：

1. 一个表t文件大小为1TB；

2. 对这个表执行 alter table t engine=InnoDB；

3. 发现执行完成后，空间不仅没变小，还稍微大了一点儿，比如变成了1.01TB。

你觉得可能是什么原因呢 ？你可以把你觉得可能的原因写在留言区里，我会在下一篇文章的末尾把大家描述的合理的原因都列出来，以后其他同学就不用掉到这样的坑里了。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间在上期文章最后，我留给你的问题是，如果一个高配的机器，redo log设置太小，会发生什么情况。

每次事务提交都要写redo log，如果设置太小，很快就会被写满，也就是下面这个图的状态，这个“环”将很快被写满，write pos一直追着CP。

这时候系统不得不停止所有更新，去推进checkpoint。

这时，你看到的现象就是磁盘压力很小，但是数据库出现间歇性的性能下跌。

问题解析

为什么我的MySQL会“抖”一下平时的工作中，不知道你有没有遇到过这样的场景，一条SQL语句，正常执行的时候特别快，但是有时也不知道怎么回事，它就会变得特别慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短。

看上去，这就像是数据库“抖”了一下。

今天，我们就一起来看一看这是什么原因。

你的SQL语句为什么变“慢”了在前面第2篇文章《日志系统：一条SQL更新语句是如何执行的？》中，我为你介绍了WAL机制。

现在你知道了，InnoDB在处理更新语句的时候，只做了写日志这一个磁盘操作。

这个日志叫作redo log（重做日志），也就是《孔乙己》里咸亨酒店掌柜用来记账的粉板，在更新内存写完redo log后，就返回给客户端，本次更新成功。

做下类比的话，掌柜记账的账本是数据文件，记账用的粉板是日志文件（redo log），掌柜的记忆就是内存。

掌柜总要找时间把账本更新一下，这对应的就是把内存里的数据写入磁盘的过程，术语就是flush。

在这个flush操作执行之前，孔乙己的赊账总额，其实跟掌柜手中账本里面的记录是不一致的。

因为孔乙己今天的赊账金额还只在粉板上，而账本里的记录是老的，还没把今天的赊账算进去。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。

内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

不论是脏页还是干净页，都在内存中。

在这个例子里，内存对应的就是掌柜的记忆。

接下来，我们用一个示意图来展示一下“孔乙己赊账”的整个操作过程。

假设原来孔乙己欠账10文，这次又要赊9文。

图1 “孔乙己赊账”更新和flush过程回到文章开头的问题，你不难想象，平时执行很快的更新操作，其实就是在写内存和日志，而MySQL偶尔“抖”一下的那个瞬间，可能就是在刷脏页（flush）。

那么，什么情况会引发数据库的flush过程呢？我们还是继续用咸亨酒店掌柜的这个例子，想一想：掌柜在什么情况下会把粉板上的赊账记录改到账本上？第一种场景是，粉板满了，记不下了。

这时候如果再有人来赊账，掌柜就只得放下手里的活儿，将粉板上的记录擦掉一些，留出空位以便继续记账。

当然在擦掉之前，他必须先将正确的账目记录到账本中才行。

这个场景，对应的就是InnoDB的redo log写满了。

这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。

我在第二讲画了一个redo log的示意图，这里我改成环形，便于大家理解。

图2 redo log状态图checkpoint可不是随便往前修改一下位置就可以的。

比如图2中，把checkpoint位置从CP推进到CP’，就需要将两个点之间的日志（浅绿色部分），对应的所有脏页都flush到磁盘上。

之后，图中从write pos到CP’之间就是可以再写入的redo log的区域。

第二种场景是，这一天生意太好，要记住的事情太多，掌柜发现自己快记不住了，赶紧找出账本把孔乙己这笔账先加进去。

这种场景，对应的就是系统内存不足。

当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。

如果淘汰的是“脏页”，就要先将脏页写到磁盘。

你一定会说，这时候难道不能直接把内存淘汰掉，下次需要请求的时候，从磁盘读入数据页，然后拿redo log出来应用不就行了？这里其实是从性能考虑的。

如果刷脏页一定会写盘，就保证了每个数据页有两种状态：

一种是内存里存在，内存里就肯定是正确的结果，直接返回；

另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。

这样的效率最高。

第三种场景是，生意不忙的时候，或者打烊之后。

这时候柜台没事，掌柜闲着也是闲着，不如更新账本。

这种场景，对应的就是MySQL认为系统“空闲”的时候。

当然，MySQL“这家酒店”的生意好起来可是会很快就能把粉板记满的，所以“掌柜”要合理地安排时间，即使是“生意好”的时候，也要见缝插针地找时间，只要有机会就刷一点“脏页”。

第四种场景是，年底了咸亨酒店要关门几天，需要把账结清一下。

这时候掌柜要把所有账都记到账本上，这样过完年重新开张的时候，就能就着账本明确账目情况了。

这种场景，对应的就是MySQL正常关闭的情况。

这时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

接下来，你可以分析一下上面四种场景对性能的影响。

其中，第三种情况是属于MySQL空闲时的操作，这时系统没什么压力，而第四种场景是数据库本来就要关闭了。

这两种情况下，你不会太关注“性能”问题。

所以这里，我们主要来分析一下前两种场景下的性能问题。

第一种是“redo log写满了，要flush脏页”，这种情况是InnoDB要尽量避免的。

因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。

如果你从监控上看，这时候更新数会跌为0。

第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。

InnoDB用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

第一种是，还没有使用的；

第二种是，使用了并且是干净页；

第三种是，使用了并且是脏页。

InnoDB的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。

而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。

这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

1. 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；

2. 日志写满，更新全部堵住，写性能跌为0，这种情况对敏感业务来说，是不能接受的。

所以，InnoDB需要有控制脏页比例的机制，来尽量避免上面的这两种情况。

InnoDB刷脏页的控制策略接下来，我就来和你说说InnoDB脏页的控制策略，以及和这些策略相关的参数。

首先，你要正确地告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。

这就要用到innodb_io_capacity这个参数了，它会告诉InnoDB你的磁盘能力。

这个值我建议你设置成磁盘的IOPS。

磁盘的IOPS可以通过fio这个工具来测试，下面的语句是我用来测试磁盘随机读写的命令：

其实，因为没能正确地设置innodb_io_capacity参数，而导致的性能问题也比比皆是。

之前，就曾有其他公司的开发负责人找我看一个库的性能问题，说MySQL的写入速度很慢，TPS很低，但是数据库主机的IO压力并不大。

经过一番排查，发现罪魁祸首就是这个参数的设置出了问题。

他的主机磁盘用的是SSD，但是innodb_io_capacity的值设置的是300。

于是，InnoDB认为这个系统的能力就这么差，所以刷脏页刷得特别慢，甚至比脏页生成的速度还慢，这样就造成了脏页累积，影响了查询和更新性能。

虽然我们现在已经定义了“全力刷脏页”的行为，但平时总不能一直是全力刷吧？毕竟磁盘能力不能只用来刷脏页，还需要服务用户请求。

所以接下来，我们就一起看看InnoDB怎么控制引擎按照“全力”的百分比来刷脏页。

根据我前面提到的知识点，试想一下，如果你来设计策略控制刷脏页的速度，会参考哪些因素呢？这个问题可以这么想，如果刷太慢，会出现什么情况？首先是内存脏页太多，其次是redo log写满。

所以，InnoDB的刷盘速度就是要参考这两个因素：一个是脏页比例，一个是redo log写盘速度。

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest InnoDB会根据这两个因素先单独算出两个数字。

参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。

InnoDB会根据当前的脏页比例（假设为M），算出一个范围在0到100之间的数字，计算这个数字的伪代码类似这样：

InnoDB每次写入的日志都有一个序号，当前写入的序号跟checkpoint对应的序号之间的差值，我们假设为N。

InnoDB会根据这个N算出一个范围在0到100之间的数字，这个计算公式可以记为F2(N)。

F2(N)算法比较复杂，你只要知道N越大，算出来的值越大就好了。

然后，根据上述算得的F1(M)和F2(N)两个值，取其中较大的值记为R，之后引擎就可以按照innodb_io_capacity定义的能力乘以R%来控制刷脏页的速度。

上述的计算流程比较抽象，不容易理解，所以我画了一个简单的流程图。

图中的F1、F2就是上面我们通过脏页比例和redo log写入速度算出来的两个值。

F1(M){ if M>=innodb_max_dirty_pages_pct then return 100; return 100*M/innodb_max_dirty_pages_pct;}图3 InnoDB刷脏页速度策略现在你知道了，InnoDB会在后台刷脏页，而刷脏页的过程是要将内存页写入磁盘。

所以，无论是你的查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页的逻辑会占用IO资源并可能影响到了你的更新语句，都可能是造成你从业务端感知到MySQL“抖”了一下的原因。

要尽量避免这种情况，你就要合理地设置innodb_io_capacity的值，并且平时要多关注脏页比例，不要让它经常接近75%。

其中，脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total得到的，具体的命令参考下面的代码：

接下来，我们再看一个有趣的策略。

一旦一个查询请求需要在执行过程中先flush掉一个脏页时，这个查询就可能要比平时慢了。

而MySQL中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。

在InnoDB中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为1的时候会有上述的“连坐”机制，值为0时表示不找邻居，自己刷自己的。

找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机IO。

机械硬盘的随机IOPS一般只有几百，相同的逻辑操作减少随机IO就意味着系统性能的大幅度提升。

而如果使用的是SSD这类IOPS比较高的设备的话，我就建议你把innodb_flush_neighbors的值设置成0。

因为这时候IOPS往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少SQL语句响应时间。

在MySQL 8.0中，innodb_flush_neighbors参数的默认值已经是0了。

小结今天这篇文章，我延续第2篇中介绍的WAL的概念，和你解释了这个机制后续需要的刷脏页操作和执行时机。

利用WAL技术，数据库将随机写转换成了顺序写，大大提升了数据库的性能。

但是，由此也带来了内存脏页的问题。

脏页会被后台线程自动flush，也会由于数据页淘汰而触发flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。

在文章里，我也给你介绍了控制刷脏页的方法和对应的监控方式。

文章最后，我给你留下一个思考题吧。

mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = ‘Innodb_buffer_pool_pages_dirty’;select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = ‘Innodb_buffer_pool_pages_total’;select @a/@b;一个内存配置为128GB、innodb_io_capacity设置为20000的大规格实例，正常会建议你将redolog设置成4个1GB的文件。

但如果你在配置的时候不慎将redo log设置成了1个100M的文件，会发生什么情况呢？又为什么会出现这样的情况呢？你可以把你的分析结论写在留言区里，我会在下一篇文章的末尾和你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间上期我留给你的问题是，给一个学号字段创建索引，有哪些方法。

由于这个学号的规则，无论是正向还是反向的前缀索引，重复度都比较高。

因为维护的只是一个学校的，因此前面6位（其中，前三位是所在城市编号、第四到第六位是学校编号）其实是固定的，邮箱后缀都是@gamil.com，因此可以只存入学年份加顺序编号，它们的长度是9位。

而其实在此基础上，可以用数字类型来存这9位数字。

比如201100001，这样只需要占4个字节。

其实这个就是一种hash，只是它用了最简单的转换规则：字符串转数字的规则，而刚好我们设定的这个背景，可以保证这个转换后结果的唯一性。

评论区中，也有其他一些很不错的见解。

评论用户@封建的风 说，一个学校的总人数这种数据量，50年才100万学生，这个表肯定是小表。

为了业务简单，直接存原来的字符串。

这个答复里面包含了“优化成本和收益”的思想，我觉得值得at出来。

@小潘 同学提了另外一个极致的方向。

如果碰到表数据量特别大的场景，通过这种方式的收益是很不错的。

问题解析

怎么给字符串字段加索引？现在，几乎所有的系统都支持邮箱登录，如何在邮箱这样的字段上建立合理的索引，是我们今天要讨论的问题。

假设，你现在维护一个支持邮箱登录的系统，用户表是这么定义的：

由于要使用邮箱登录，所以业务代码中一定会出现类似于这样的语句：

从第4和第5篇讲解索引的文章中，我们可以知道，如果email这个字段上没有索引，那么这个语句就只能做全表扫描。

同时，MySQL是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。

默认地，mysql> create table SUser(ID bigint unsigned primary key,email varchar(64), … )engine=innodb; mysql> select f1, f2 from SUser where email=’xxx’;如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

比如，这两个在email字段上创建索引的语句：

第一个语句创建的index1索引里面，包含了每个记录的整个字符串；而第二个语句创建的index2索引里面，对于每个记录都是只取前6个字节。

那么，这两种不同的定义在数据结构和存储上有什么区别呢？如图2和3所示，就是这两个索引的示意图。

图1 email 索引结构mysql> alter table SUser add index index1(email);或mysql> alter table SUser add index index2(email(6));图2 email(6) 索引结构从图中你可以看到，由于email(6)这个索引结构中每个邮箱字段都只取前6个字节（即：

zhangs），所以占用的空间会更小，这就是使用前缀索引的优势。

但，这同时带来的损失是，可能会增加额外的记录扫描次数。

接下来，我们再看看下面这个语句，在这两个索引定义下分别是怎么执行的。

如果使用的是index1（即email整个字符串的索引结构），执行顺序是这样的：

1. 从index1索引树找到满足索引值是’zhangssxyz@xxx.com’的这条记录，取得ID2的值；

2. 到主键上查到主键值是ID2的行，判断email的值是正确的，将这行记录加入结果集；

3. 取index1索引树上刚刚查到的位置的下一条记录，发现已经不满足email=‘zhangssxyz@xxx.com’的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

select id,name,email from SUser where email=‘zhangssxyz@xxx.com‘;如果使用的是index2（即email(6)索引结构），执行顺序是这样的：

1. 从index2索引树找到满足索引值是’zhangs’的记录，找到的第一个是ID1；

2. 到主键上查到主键值是ID1的行，判断出email的值不是’zhangssxyz@xxx.com’，这行记录丢弃；

3. 取index2上刚刚查到的位置的下一条记录，发现仍然是’zhangs’，取出ID2，再到ID索引上取整行然后判断，这次值对了，将这行记录加入结果集；

4. 重复上一步，直到在idxe2上取到的值不是’zhangs’时，循环结束。

在这个过程中，要回主键索引取4次数据，也就是扫描了4行。

通过这个对比，你很容易就可以发现，使用前缀索引后，可能会导致查询语句读数据的次数变多。

但是，对于这个查询语句来说，如果你定义的index2不是email(6)而是email(7），也就是说取email字段的前7个字节来构建索引的话，即满足前缀’zhangss’的记录只有一个，也能够直接查到ID2，只扫描一行就结束了。

也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

于是，你就有个问题：当要给字符串创建前缀索引时，有什么方法能够确定我应该使用多长的前缀呢？实际上，我们在建立索引时关注的是区分度，区分度越高越好。

因为区分度越高，意味着重复的键值越少。

因此，我们可以通过统计索引上有多少个不同的值来判断要使用多长的前缀。

首先，你可以使用下面这个语句，算出这个列上有多少个不同的值：

然后，依次选取不同长度的前缀来看这个值，比如我们要看一下4~7个字节的前缀索引，可以用这个语句：

mysql> select count(distinct email) as L from SUser;当然，使用前缀索引很可能会损失区分度，所以你需要预先设定一个可以接受的损失比例，比如5%。

然后，在返回的L4~L7中，找出不小于 L * 95%的值，假设这里L6、L7都满足，你就可以选择前缀长度为6。

前缀索引对覆盖索引的影响前面我们说了使用前缀索引可能会增加扫描行数，这会影响到性能。

其实，前缀索引的影响不止如此，我们再看一下另外一个场景。

你先来看看这个SQL语句：

与前面例子中的SQL语句相比，这个语句只要求返回id和email字段。

所以，如果使用index1（即email整个字符串的索引结构）的话，可以利用覆盖索引，从index1查到结果后直接就返回了，不需要回到ID索引再去查一次。

而如果使用index2（即email(6)索引结构）的话，就不得不回到ID索引再去判断email字段的值。

即使你将index2的定义修改为email(18)的前缀索引，这时候虽然index2已经包含了所有的信息，但InnoDB还是要回到id索引再查一下，因为系统并不确定前缀索引的定义是否截断了完整信息。

也就是说，使用前缀索引就用不上覆盖索引对查询性能的优化了，这也是你在选择是否使用前缀索引时需要考虑的一个因素。

其他方式对于类似于邮箱这样的字段来说，使用前缀索引的效果可能还不错。

但是，遇到前缀的区分度不mysql> select count(distinct left(email,4)）as L4, count(distinct left(email,5)）as L5, count(distinct left(email,6)）as L6, count(distinct left(email,7)）as L7,from SUser;select id,email from SUser where email=‘zhangssxyz@xxx.com‘;select id,name,email from SUser where email=‘zhangssxyz@xxx.com‘;够好的情况时，我们要怎么办呢？比如，我们国家的身份证号，一共18位，其中前6位是地址码，所以同一个县的人的身份证号前6位一般会是相同的。

假设你维护的数据库是一个市的公民信息系统，这时候如果对身份证号做长度为6的前缀索引的话，这个索引的区分度就非常低了。

按照我们前面说的方法，可能你需要创建长度为12以上的前缀索引，才能够满足区分度要求。

但是，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

那么，如果我们能够确定业务需求里面只有按照身份证进行等值查询的需求，还有没有别的处理方法呢？这种方法，既可以占用更小的空间，也能达到相同的查询效率。

答案是，有的。

第一种方式是使用倒序存储。

如果你存储身份证号的时候把它倒过来存，每次查询的时候，你可以这么写：

由于身份证号的最后6位没有地址码这样的重复逻辑，所以最后这6位很可能就提供了足够的区分度。

当然了，实践中你不要忘记使用count(distinct)方法去做个验证。

第二种方式是使用hash字段。

你可以在表上再创建一个整数字段，来保存身份证的校验码，同时在这个字段上创建索引。

然后每次插入新记录的时候，都同时用crc32()这个函数得到校验码填到这个新字段。

由于校验码可能存在冲突，也就是说两个不同的身份证号通过crc32()函数得到的结果可能是相同的，所以你的查询语句where部分要判断id_card的值是否精确相同。

这样，索引的长度变成了4个字节，比原来小了很多。

mysql> select field_list from t where id_card = reverse(‘input_id_card_string’);mysql> alter table t add id_card_crc int unsigned, add index(id_card_crc);mysql> select field_list from t where id_card_crc=crc32(‘input_id_card_string’) and id_card=’input_id_card_string’接下来，我们再一起看看使用倒序存储和使用hash字段这两种方法的异同点。

首先，它们的相同点是，都不支持范围查询。

倒序存储的字段上创建的索引是按照倒序字符串的方式排序的，已经没有办法利用索引方式查出身份证号码在[ID_X, ID_Y]的所有市民了。

同样地，hash字段的方式也只能支持等值查询。

它们的区别，主要体现在以下三个方面：

1. 从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而hash字段方法需要增加一个字段。

当然，倒序存储方式使用4个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个hash字段也差不多抵消了。

2. 在CPU消耗方面，倒序方式每次写和读的时候，都需要额外调用一次reverse函数，而hash字段的方式需要额外调用一次crc32()函数。

如果只从这两个函数的计算复杂度来看的话，reverse函数额外消耗的CPU资源会更小些。

3. 从查询效率上看，使用hash字段方式的查询性能相对更稳定一些。

因为crc32算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近1。

而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

小结在今天这篇文章中，我跟你聊了聊字符串字段创建索引的场景。

我们来回顾一下，你可以使用的方式有：

1. 直接创建完整索引，这样可能比较占用空间；

2. 创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；

3. 倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；

4. 创建hash字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

在实际应用中，你要根据业务字段的特点选择使用哪种方式。

好了，又到了最后的问题时间。

如果你在维护一个学校的学生信息数据库，学生登录名的统一格式是”学号@gmail.com”, 而学号的规则是：十五位的数字，其中前三位是所在城市编号、第四到第六位是学校编号、第七位到第十位是入学年份、最后五位是顺序编号。

系统登录的时候都需要学生输入登录名和密码，验证正确后才能继续使用系统。

就只考虑登录验证这个行为的话，你会怎么设计这个登录名的索引呢？你可以把你的分析思路和设计结果写在留言区里，我会在下一篇文章的末尾和你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间上篇文章中的第一个例子，评论区有几位同学说没有复现，大家要检查一下隔离级别是不是RR（Repeatable Read，可重复读），创建的表t是不是InnoDB引擎。

我把复现过程做成了一个视频，供你参考。

在上一篇文章最后，我给你留的问题是，为什么经过这个操作序列，explain的结果就不对了？这里，我来为你分析一下原因。

delete 语句删掉了所有的数据，然后再通过call idata()插入了10万行数据，看上去是覆盖了原来的10万行。

但是，session A开启了事务并没有提交，所以之前插入的10万行数据是不能删除的。

这样，之前的数据每一行数据都有两个版本，旧版本是delete之前的数据，新版本是标记为deleted的数据。

这样，索引a上的数据其实就有两份。

然后你会说，不对啊，主键上的数据也不能删，那没有使用force index的语句，使用explain命令看到的扫描行数为什么还是100000左右？（潜台词，如果这个也翻倍，也许优化器还会认为选字段a作为索引更合适）是的，不过这个是主键，主键是直接按照表的行数来估计的。

而表的行数，优化器直接用的是show table status的值。

这个值的计算方法，我会在后面有文章为你详细讲解。

问题解析

MySQL为什么有时候会选错索引？

前面我们介绍过索引，你已经知道了在MySQL中一张表其实是可以支持多个索引的。

但是，你写SQL语句的时候，并没有主动指定使用哪个索引。

也就是说，使用哪个索引是由MySQL来确定的。

不知道你有没有碰到过这种情况，一条本来可以执行得很快的语句，却由于MySQL选错了索引，而导致执行速度变得很慢？我们一起来看一个例子吧。

我们先建一个简单的表，表里有a、b两个字段，并分别建上索引：
CREATE TABLE t ( id int(11) NOT NULL, a int(11) DEFAULT NULL, b int(11) DEFAULT NULL, PRIMARY KEY (id), KEY a (a), KEY b (b)) ENGINE=InnoDB；
然后，我们往表t中插入10万行记录，取值按整数递增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到(100000,100000,100000)。

我是用存储过程来插入数据的，这里我贴出来方便你复现：
接下来，我们分析一条SQL语句：
你一定会说，这个语句还用分析吗，很简单呀，a上有索引，肯定是要使用索引a的。

你说得没错，图1显示的就是使用explain命令看到的这条语句的执行情况。

图1 使用explain命令查看语句执行情况从图1看上去，这条查询语句的执行也确实符合预期，key这个字段值是’a’，表示优化器选择了索引a。

不过别急，这个案例不会这么简单。

在我们已经准备好的包含了10万行数据的表上，我们再做如下操作。

delimiter ;;create procedure idata()begin declare i int; set i=1; while(i<=100000)do insert into t values(i, i, i); set i=i+1; end while;end;;delimiter ;call idata();mysql> select * from t where a between 10000 and 20000;图2 session A和session B的执行流程这里，session A的操作你已经很熟悉了，它就是开启了一个事务。

随后，session B把数据都删除后，又调用了 idata这个存储过程，插入了10万行数据。

这时候，session B的查询语句select * from t where a between 10000 and 20000就不会再选择索引a了。

我们可以通过慢查询日志（slow log）来查看一下具体的执行情况。

为了说明优化器选择的结果是否正确，我增加了一个对照，即：使用force index(a)来让优化器强制使用索引a（这部分内容，我还会在这篇文章的后半部分中提到）。

下面的三条SQL语句，就是这个实验过程。

第一句，是将慢查询日志的阈值设置为0，表示这个线程接下来的语句都会被记录入慢查询日志中；
第二句，Q1是session B原来的查询；
第三句，Q2是加了force index(a)来和session B原来的查询语句执行情况对比。

如图3所示是这三条SQL语句执行完成后的慢查询日志。

set long_query_time=0;select * from t where a between 10000 and 20000; /Q1/select * from t force index(a) where a between 10000 and 20000;/Q2/图3 slow log结果可以看到，Q1扫描了10万行，显然是走了全表扫描，执行时间是40毫秒。

Q2扫描了10001行，执行了21毫秒。

也就是说，我们在没有使用force index的时候，MySQL用错了索引，导致了更长的执行时间。

这个例子对应的是我们平常不断地删除历史数据和新增数据的场景。

这时，MySQL竟然会选错索引，是不是有点奇怪呢？今天，我们就从这个奇怪的结果说起吧。

优化器的逻辑在第一篇文章中，我们就提到过，选择索引是优化器的工作。

而优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。

在数据库里面，扫描行数是影响执行代价的因素之一。

扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的CPU资源越少。

当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

我们这个简单的查询语句并没有涉及到临时表和排序，所以MySQL选错索引肯定是在判断扫描行数的时候出问题了。

那么，问题就是：扫描行数是怎么判断的？MySQL在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。

这个统计信息就是索引的“区分度”。

显然，一个索引上不同的值越多，这个索引的区分度就越好。

而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。

也就是说，这个基数越大，索引的区分度越好。

我们可以使用show index方法，看到一个索引的基数。

如图4所示，就是表t的show index 的结果。

虽然这个表的每一行的三个字段值都是一样的，但是在统计信息中，这三个索引的基数值并不同，而且其实都不准确。

图4 表t的show index 结果那么，MySQL是怎样得到索引的基数的呢？这里，我给你简单介绍一下MySQL采样统计的方法。

为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。

采样统计的时候，InnoDB默认会选择N个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

而数据表是会持续更新的，索引统计信息也不会固定不变。

所以，当变更的数据行数超过1/M的时候，会自动触发重新做一次索引统计。

在MySQL中，有两种存储索引统计的方式，可以通过设置参数innodb_stats_persistent的值来选择：
设置为on的时候，表示统计信息会持久化存储。

这时，默认的N是20，M是10。

设置为off的时候，表示统计信息只存储在内存中。

这时，默认的N是8，M是16。

由于是采样统计，所以不管N是20还是8，这个基数都是很容易不准的。

但，这还不是全部。

你可以从图4中看到，这次的索引统计值（cardinality列）虽然不够精确，但大体上还是差不多的，选错索引一定还有别的原因。

其实索引统计只是一个输入，对于一个具体的语句来说，优化器还要判断，执行这个语句本身要扫描多少行。

接下来，我们再一起看看优化器预估的，这两个语句的扫描行数是多少。

图5 意外的explain结果rows这个字段表示的是预计扫描行数。

其中，Q1的结果还是符合预期的，rows的值是104620；但是Q2的rows值是37116，偏差就大了。

而图1中我们用explain命令看到的rows是只有10001行，是这个偏差误导了优化器的判断。

到这里，可能你的第一个疑问不是为什么不准，而是优化器为什么放着扫描37000行的执行计划不用，却选择了扫描行数是100000的执行计划呢？这是因为，如果使用索引a，每次从索引a上拿到一个值，都要回到主键索引上查出整行数据，这个代价优化器也要算进去的。

而如果选择扫描10万行，是直接在主键索引上扫描的，没有额外的代价。

优化器会估算这两个选择的代价，从结果看来，优化器认为直接扫描主键索引更快。

当然，从执行时间看来，这个选择并不是最优的。

使用普通索引需要把回表的代价算进去，在图1执行explain的时候，也考虑了这个策略的代价 ，但图1的选择是对的。

也就是说，这个策略并没有问题。

所以冤有头债有主，MySQL选错索引，这件事儿还得归咎到没能准确地判断出扫描行数。

至于为什么会得到错误的扫描行数，这个原因就作为课后问题，留给你去分析了。

既然是统计信息不对，那就修正。

analyze table t 命令，可以用来重新统计索引信息。

我们来看一下执行效果。

图6 执行analyze table t 命令恢复的explain结果这回对了。

所以在实践中，如果你发现explain的结果预估的rows值跟实际情况差距比较大，可以采用这个方法来处理。

其实，如果只是索引统计不准确，通过analyze命令可以解决很多问题，但是前面我们说了，优化器可不止是看扫描行数。

依然是基于这个表t，我们看看另外一个语句：
从条件上看，这个查询没有符合条件的记录，因此会返回空集合。

在开始执行这条语句之前，你可以先设想一下，如果你来选择索引，会选择哪一个呢？为了便于分析，我们先来看一下a、b这两个索引的结构图。

图7 a、b索引的结构图如果使用索引a进行查询，那么就是扫描索引a的前1000个值，然后取到对应的id，再到主键索引上去查出每一行，然后根据字段b来过滤。

显然这样需要扫描1000行。

如果使用索引b进行查询，那么就是扫描索引b的最后50001个值，与上面的执行过程相同，也是需要回到主键索引上取值再判断，所以需要扫描50001行。

所以你一定会想，如果使用索引a的话，执行速度明显会快很多。

那么，下面我们就来看看到底是不是这么一回事儿。

mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;图8是执行explain的结果。

图8 使用explain方法查看执行计划 2可以看到，返回结果中key字段显示，这次优化器选择了索引b，而rows字段显示需要扫描的行数是50198。

从这个结果中，你可以得到两个结论：

1. 扫描行数的估计值依然不准确；
2. 这个例子里MySQL又选错了索引。

索引选择异常和处理其实大多数时候优化器都能找到正确的索引，但偶尔你还是会碰到我们上面举例的这两种情况：
原本可以执行得很快的SQL语句，执行速度却比你预期的慢很多，你应该怎么办呢？一种方法是，像我们第一个例子一样，采用force index强行选择一个索引。

MySQL会根据词法解析的结果分析出可能可以使用的索引作为候选项，然后在候选列表中依次判断每个索引需要扫描多少行。

如果force index指定的索引在候选索引列表中，就直接选择这个索引，不再评估其他索引的执行代价。

我们来看看第二个例子。

刚开始分析时，我们认为选择索引a会更好。

现在，我们就来看看执行效果：
图9 使用不同索引的语句执行耗时可以看到，原本语句需要执行2.23秒，而当你使用force index(a)的时候，只用了0.05秒，比优化器的选择快了40多倍。

也就是说，优化器没有选择正确的索引，force index起到了“矫正”的作用。

不过很多程序员不喜欢使用force index，一来这么写不优美，二来如果索引改了名字，这个语句mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;也得改，显得很麻烦。

而且如果以后迁移到别的数据库的话，这个语法还可能会不兼容。

但其实使用force index最主要的问题还是变更的及时性。

因为选错索引的情况还是比较少出现的，所以开发的时候通常不会先写上force index。

而是等到线上出现问题的时候，你才会再去修改SQL语句、加上force index。

但是修改之后还要测试和发布，对于生产系统来说，这个过程不够敏捷。

所以，数据库的问题最好还是在数据库内部来解决。

那么，在数据库里面该怎样解决呢？既然优化器放弃了使用索引a，说明a还不够合适，所以第二种方法就是，我们可以考虑修改语句，引导MySQL使用我们期望的索引。

比如，在这个例子里，显然把“order by b limit 1” 改成 “order by b,a limit 1” ，语义的逻辑是相同的。

我们来看看改之后的效果：
图10 order by b,a limit 1 执行结果之前优化器选择使用索引b，是因为它认为使用索引b可以避免排序（b本身是索引，已经是有序的了，如果选择索引b的话，不需要再做排序，只需要遍历），所以即使扫描行数多，也判定为代价更小。

现在order by b,a 这种写法，要求按照b,a排序，就意味着使用这两个索引都需要排序。

因此，扫描行数成了影响决策的主要条件，于是此时优化器选了只需要扫描1000行的索引a。

当然，这种修改并不是通用的优化手段，只是刚好在这个语句里面有limit 1，因此如果有满足条件的记录， order by b limit 1和order by b,a limit 1 都会返回b是最小的那一行，逻辑上一致，才可以这么做。

如果你觉得修改语义这件事儿不太好，这里还有一种改法，图11是执行效果。

图11 改写SQL的explain在这个例子里，我们用limit 100让优化器意识到，使用b索引代价是很高的。

其实是我们根据数mysql> select * from (select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 100)alias limit 1;据特征诱导了一下优化器，也不具备通用性。

第三种方法是，在有些场景下，我们可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。

不过，在这个例子中，我没有找到通过新增索引来改变优化器行为的方法。

这种情况其实比较少，尤其是经过DBA索引优化过的库，再碰到这个bug，找到一个更合适的索引一般比较难。

如果我说还有一个方法是删掉索引b，你可能会觉得好笑。

但实际上我碰到过两次这样的例子，最终是DBA跟业务开发沟通后，发现这个优化器错误选择的索引其实根本没有必要存在，于是就删掉了这个索引，优化器也就重新选择到了正确的索引。

小结今天我们一起聊了聊索引统计的更新机制，并提到了优化器存在选错索引的可能性。

对于由于索引统计信息不准确导致的问题，你可以用analyze table来解决。

而对于其他优化器误判的情况，你可以在应用端用force index来强行指定索引，也可以通过修改语句来引导优化器，还可以通过增加或者删除索引来绕过这个问题。

你可能会说，今天这篇文章后面的几个例子，怎么都没有展开说明其原理。

我要告诉你的是，今天的话题，我们面对的是MySQL的bug，每一个展开都必须深入到一行行代码去量化，实在不是我们在这里应该做的事情。

所以，我把我用过的解决方法跟你分享，希望你在碰到类似情况的时候，能够有一些思路。

你平时在处理MySQL优化器bug的时候有什么别的方法，也发到评论区分享一下吧。

最后，我给你留下一个思考题。

前面我们在构造第一个例子的过程中，通过session A的配合，让session B删除数据后又重新插入了一遍数据，然后就发现explain结果中，rows字段从10001变成37000多。

而如果没有session A的配合，只是单独执行delete from t 、call idata()、explain这三句话，会看到rows字段其实还是10000左右。

你可以自己验证一下这个结果。

这是什么原因呢？也请你分析一下吧。

你可以把你的分析结论写在留言区里，我会在下一篇文章的末尾和你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

上期问题时间我在上一篇文章最后留给你的问题是，如果某次写入使用了change buffer机制，之后主机异常重启，是否会丢失change buffer和数据。

这个问题的答案是不会丢失，留言区的很多同学都回答对了。

虽然是只更新内存，但是在事务提交的时候，我们把change buffer的操作也记录到redo log里了，所以崩溃恢复的时候，changebuffer也能找回来。

在评论区有同学问到，merge的过程是否会把数据直接写回磁盘，这是个好问题。

这里，我再为你分析一下。

merge的执行流程是这样的：

1. 从磁盘读入数据页到内存（老版本的数据页）；
2. 从change buffer里找出这个数据页的change buffer 记录(可能有多个），依次应用，得到新版数据页；
3. 写redo log。

这个redo log包含了数据的变更和change buffer的变更。

到这里merge过程就结束了。

这时候，数据页和内存中change buffer对应的磁盘位置都还没有修改，属于脏页，之后各自刷回自己的物理数据，就是另外一个过程了。

问题解析

普通索引和唯一索引，应该怎么选择今天的正文开始前，我要特意感谢一下评论区几位留下高质量留言的同学。

用户名是 @某、人 的同学，对文章的知识点做了梳理，然后提了关于事务可见性的问题，就是先启动但是后提交的事务，对数据可见性的影响。

@夏日雨同学也提到了这个问题，我在置顶评论中回复了，今天的文章末尾也会再展开说明。

@Justin和@倪大人两位同学提了两个好问题。

对于能够引发更深一步思考的问题，我会在回复的内容中写上“好问题”三个字，方便你搜索，你也可以去看看他们的留言。

非常感谢大家很细致地看文章，并且留下了那么多和很高质量的留言。

知道文章有给大家带来一些新理解，对我来说是一个很好的鼓励。

同时，也让其他认真看评论区的同学，有机会发现一些自己还没有意识到的、但可能还不清晰的知识点，这也在总体上提高了整个专栏的质量。

再次谢谢你们。

好了，现在就回到我们今天的正文内容。

在前面的基础篇文章中，我给你介绍过索引的基本概念，相信你已经了解了唯一索引和普通索引的区别。

今天我们就继续来谈谈，在不同的业务场景下，应该选择普通索引，还是唯一索引？假设你在维护一个市民系统，每个人都有一个唯一的身份证号，而且业务代码已经保证了不会写入两个重复的身份证号。

如果市民系统需要按照身份证号查姓名，就会执行类似这样的SQL语句：

所以，你一定会考虑在id_card字段上建索引。

由于身份证号字段比较大，我不建议你把身份证号当做主键，那么现在你有两个选择，要么给id_card字段创建唯一索引，要么创建一个普通索引。

如果业务代码已经保证了不会写入重复的身份证号，那么这两个选择逻辑上都是正确的。

现在我要问你的是，从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么呢？简单起见，我们还是用第4篇文章《深入浅出索引（上）》中的例子来说明，假设字段 k 上的值都不重复。

图1 InnoDB的索引组织结构接下来，我们就从这两种索引对查询语句和更新语句的性能影响来进行分析。

查询过程select name from CUser where id_card = ‘xxxxxxxyyyyyyzzzzz’;假设，执行查询的语句是 select id from T where k=5。

这个查询语句在索引树上查找的过程，先是通过B+树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

对于普通索引来说，查找到满足条件的第一个记录(5,500)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录。

对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微。

你知道的，InnoDB的数据是按数据页为单位来读写的。

也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。

在InnoDB中，每个数据页的大小默认是16KB。

因为引擎是按页读写的，所以说，当找到k=5的记录的时候，它所在的数据页就都在内存里了。

那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。

当然，如果k=5这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。

但是，我们之前计算过，对于整型字段，一个数据页可以放近千个key，因此出现这种情况的概率会很低。

所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的CPU来说可以忽略不计。

更新过程为了说明普通索引和唯一索引对更新语句性能的影响这个问题，我需要先跟你介绍一下changebuffer。

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB会将这些更新操作缓存在change buffer中，这样就不需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行change buffer中与这个页有关的操作。

通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作change buffer，实际上它是可以持久化的数据。

也就是说，change buffer在内存中有拷贝，也会被写入到磁盘上。

将change buffer中的操作应用到原数据页，得到最新结果的过程称为merge。

除了访问这个数据页会触发merge外，系统有后台线程会定期merge。

在数据库正常关闭（shutdown）的过程中，也会执行merge操作。

显然，如果能够将更新操作先记录在change buffer，减少读磁盘，语句的执行速度会得到明显的提升。

而且，数据读入内存是需要占用buffer pool的，所以这种方式还能够避免占用内存，提高内存利用率。

那么，什么条件下可以使用change buffer呢？对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。

比如，要插入(4,400)这个记录，就要先判断现在表中是否已经存在k=4的记录，而这必须要将数据页读入内存才能判断。

如果都已经读入到内存了，那直接更新内存会更快，就没必要使用change buffer了。

因此，唯一索引的更新就不能使用change buffer，实际上也只有普通索引可以使用。

change buffer用的是buffer pool里的内存，因此不能无限增大。

change buffer的大小，可以通过参数innodb_change_buffer_max_size来动态设置。

这个参数设置为50的时候，表示changebuffer的大小最多只能占用buffer pool的50%。

现在，你已经理解了change buffer的机制，那么我们再一起来看看如果要在这张表中插入一个新记录(4,400)的话，InnoDB的处理流程是怎样的。

第一种情况是，这个记录要更新的目标页在内存中。

这时，InnoDB的处理流程如下：

对于唯一索引来说，找到3和5之间的位置，判断到没有冲突，插入这个值，语句执行结束；

对于普通索引来说，找到3和5之间的位置，插入这个值，语句执行结束。

这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的CPU时间。

但，这不是我们关注的重点。

第二种情况是，这个记录要更新的目标页不在内存中。

这时，InnoDB的处理流程如下：

对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；

对于普通索引来说，则是将更新记录在change buffer，语句执行就结束了。

将数据从磁盘读入内存涉及随机IO的访问，是数据库里面成本最高的操作之一。

change buffer因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

之前我就碰到过一件事儿，有个DBA的同学跟我反馈说，他负责的某个业务的库内存命中率突然从99%降低到了75%，整个系统处于阻塞状态，更新语句全部堵住。

而探究其原因后，我发现这个业务有大量插入数据的操作，而他在前一天把其中的某个普通索引改成了唯一索引。

change buffer的使用场景通过上面的分析，你已经清楚了使用change buffer对更新过程的加速作用，也清楚了changebuffer只限于用在普通索引的场景下，而不适用于唯一索引。

那么，现在有一个问题就是：普通索引的所有场景，使用change buffer都可以起到加速作用吗？因为merge的时候是真正进行数据更新的时刻，而change buffer的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做merge之前，change buffer记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时changebuffer的使用效果最好。

这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在change buffer，但之后由于马上要访问这个数据页，会立即触发merge过程。

这样随机访问IO的次数不会减少，反而增加了change buffer的维护代价。

所以，对于这种业务模式来说，change buffer反而起到了副作用。

索引选择和实践回到我们文章开头的问题，普通索引和唯一索引应该怎么选择。

其实，这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。

所以，我建议你尽量选择普通索引。

如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭change buffer。

而在其他情况下，change buffer都能提升更新性能。

在实际使用中，你会发现，普通索引和change buffer的配合使用，对于数据量大的表的更新优化还是很明显的。

特别地，在使用机械硬盘时，change buffer这个机制的收效是非常显著的。

所以，当你有一个类似“历史数据”的库，并且出于成本考虑用的是机械硬盘时，那你应该特别关注这些表里的索引，尽量使用普通索引，然后把change buffer 尽量开大，以确保这个“历史数据”表的数据写入速度。

change buffer 和 redo log理解了change buffer的原理，你可能会联想到我在前面文章中和你介绍过的redo log和WAL。

在前面文章的评论中，我发现有同学混淆了redo log和change buffer。

WAL 提升性能的核心机制，也的确是尽量减少随机读写，这两个概念确实容易混淆。

所以，这里我把它们放到了同一个流程里来说明，便于你区分这两个概念。

现在，我们要在表上执行这个插入语句：

这里，我们假设当前k索引树的状态，查找到位置后，k1所在的数据页在内存(InnoDB bufferpool)中，k2所在的数据页不在内存中。

如图2所示是带change buffer的更新状态图。

图2 带change buffer的更新过程分析这条更新语句，你会发现它涉及了四个部分：内存、redo log（ib_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）。

这条更新语句做了如下的操作（按照图中的数字顺序）：

1. Page 1在内存中，直接更新内存；

2. Page 2没有在内存中，就在内存的change buffer区域，记录下“我要往Page 2插入一行”这个信息备注：这里，你可以再回顾下第2篇文章《日志系统：一条SQL更新语句是如何执行的？》中的相关内容。

mysql> insert into t(id,k) values(id1,k1),(id2,k2);3. 将上述两个动作记入redo log中（图中3和4）。

做完上面这些，事务就可以完成了。

所以，你会看到，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

同时，图中的两个虚线箭头，是后台操作，不影响更新的响应时间。

那在这之后的读请求，要怎么处理呢？比如，我们现在要执行 select * from t where k in (k1, k2)。

这里，我画了这两个读请求的流程图。

如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关了。

所以，我在图中就没画出这两部分。

图3 带change buffer的读过程从图中可以看到：

1. 读Page 1的时候，直接从内存返回。

有几位同学在前面文章的评论中问到，WAL之后如果读数据，是不是一定要读盘，是不是一定要从redo log里面把数据更新以后才可以返回？其实是不用的。

你可以看一下图3的这个状态，虽然磁盘上还是之前的数据，但是这里直接从内存返回结果，结果是正确的。

2. 要读Page 2的时候，需要把Page 2从磁盘读入内存中，然后应用change buffer里面的操作日志，生成一个正确的版本并返回结果。

可以看到，直到需要读Page 2的时候，这个数据页才会被读入内存。

所以，如果要简单地对比这两个机制在提升更新性能上的收益的话，redo log 主要节省的是随机写磁盘的IO消耗（转成顺序写），而change buffer主要节省的则是随机读磁盘的IO消耗。

小结今天，我从普通索引和唯一索引的选择开始，和你分享了数据的查询和更新过程，然后说明了change buffer的机制以及应用场景，最后讲到了索引选择的实践。

由于唯一索引用不上change buffer的优化机制，因此如果业务可以接受，从性能角度出发我建议你优先考虑非唯一索引。

最后，又到了思考题时间。

通过图2你可以看到，change buffer一开始是写内存的，那么如果这个时候机器掉电重启，会不会导致change buffer丢失呢？change buffer丢失可不是小事儿，再从磁盘读入数据可就没有了merge过程，就等于是数据丢失了。

会不会出现这种情况呢？你可以把你的思考和观点写在留言区里，我会在下一篇文章的末尾和你讨论这个问题。

感谢你的收听，也欢迎你把这篇文章分享给更多的朋友一起阅读。

补充：

评论区大家对“是否使用唯一索引”有比较多的讨论，主要是纠结在“业务可能无法确保”的情况。

这里，我再说明一下：

首先，业务正确性优先。

咱们这篇文章的前提是“业务代码已经保证不会写入重复数据”的情况下，讨论性能问题。

如果业务不能保证，或者业务就是要求数据库来做约束，那么没得选，必须创建唯一索引。

这种情况下，本篇文章的意义在于，如果碰上了大量插入数据慢、内存命中率低的时候，可以给你多提供一个排查思路。

然后，在一些“归档库”的场景，你是可以考虑使用唯一索引的。

比如，线上数据只需要保留半年，然后历史数据保存在归档库。

这时候，归档数据已经是确保没有唯一键冲突了。

要提高归档效率，可以考虑把表里面的唯一索引改成普通索引。

上期问题时间上期的问题是：如何构造一个“数据无法修改”的场景。

评论区里已经有不少同学给出了正确答案，这里我再描述一下。

这样，session A看到的就是我截图的效果了。

其实，还有另外一种场景，同学们在留言区都还没有提到。

这个操作序列跑出来，session A看的内容也是能够复现我截图的效果的。

这个session B’启动的事务比A要早，其实是上期我们描述事务版本的可见性规则时留的彩蛋，因为规则里还有一个“活跃事务的判断”，我是准备留到这里再补充的。

当我试图在这里讲述完整规则的时候，发现第8篇文章《事务到底是隔离的还是不隔离的？》中的解释引入了太多的概念，以致于分析起来非常复杂。

因此，我重写了第8篇，这样我们人工去判断可见性的时候，才会更方便。

【看到这里，我建议你能够再重新打开第8篇文章并认真学习一次。

如果学习的过程中，有任何问题，也欢迎你给我留言】用新的方式来分析session B’的更新为什么对session A不可见就是：在session A视图数组创建的瞬间，session B’是活跃的，属于“版本未提交，不可见”这种情况。

业务中如果要绕过这类问题，@约书亚提供了一个“乐观锁”的解法，大家可以去上一篇的留言区看一下。