数据库调优

本文由 简悦 SimpRead 转码， 原文地址 mp.weixin.qq.com

正文

我们所谓的调优也就是在，执行器执行之前的分析器，优化器阶段完成的，那我们开发工作中怎么去调优的呢？

帅丙一般在开发涉及 SQL 的业务都会去本地环境跑一遍 SQL，用 explain 去看一下执行计划，看看分析的结果是否符合自己的预期，用没用到相关的索引，然后再去线上环境跑一下看看执行时间（这里只有查询语句，修改语句也无法在线上执行）。

遇 SQL 不决 explain，但是这里就要说到第一个坑了。

排除缓存干扰

因为在 MySQL8.0 之前我们的数据库是存在缓存这样的情况的，我之前就被坑过，因为存在缓存，我发现我 sql 怎么执行都是很快，当然第一次其实不快但是我没注意到，以至于上线后因为缓存经常失效，导致 rt（Response time）时高时低。

后面就发现了是缓存的问题，我们在执行 SQL 的时候，记得加上 SQL NoCache 去跑 SQL，这样跑出来的时间就是真实的查询时间了。

我说一下为什么缓存会失效，而且是经常失效。

如果我们当前的 MySQL 版本支持缓存而且我们又开启了缓存，那每次请求的查询语句和结果都会以 key-value 的形式缓存在内存中的，大家也看到我们的结构图了，一个请求会先去看缓存是否存在，不存在才会走解析器。

缓存失效比较频繁的原因就是，只要我们一对表进行更新，那这个表所有的缓存都会被清空，其实我们很少存在不更新的表，特别是我之前的电商场景，可能静态表可以用到缓存，但是我们都走大数据离线分析，缓存也就没用了。

大家如果是 8.0 以上的版本就不用担心这个问题，如果是 8.0 之下的版本，记得排除缓存的干扰。

Explain

最开始提到了用执行计划去分析，我想 explain 是大家 SQL 调优都会回答到的吧。

因为这基本上是写 SQL 的必备操作，那我现在问大家一个我去阿里面试被问过的一个问题：explain 你记得哪些字段，分别有什么含义？

那我再问大家一下，你们认为统计这个统计的行数就是完全对的么？索引一定会走到最优索引么？

当然我都这么问了，你们肯定也知道结果了，行数只是一个接近的数字，不是完全正确的，索引也不一定就是走最优的，是可能走错的。

我的总行数大概有 10W 行，但是我去用 explain 去分析 sql 的时候，就会发现只得到了 9.4W，为啥行数只是个近视值呢？

看过基础章节的小伙伴都知道，MySQL 中数据的单位都是页，MySQL 又采用了采样统计的方法，采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。

我们数据是一直在变的，所以索引的统计信息也是会变的，会根据一个阈值，重新做统计。

至于 MySQL 索引可能走错也很好理解，如果走 A 索引要扫描 100 行，B 所有只要 20 行，但是他可能选择走 A 索引，你可能会想 MySQL 是不是有病啊，其实不是的。

一般走错都是因为优化器在选择的时候发现，走 A 索引没有额外的代价，比如走 B 索引并不能直接拿到我们的值，还需要回到主键索引才可以拿到，多了一次回表的过程，这个也是会被优化器考虑进去的。

他发现走 A 索引不需要回表，没有额外的开销，所有他选错了。

如果是上面的统计信息错了，那简单，我们用 analyze table tablename 就可以重新统计索引信息了，所以在实践中，如果你发现 explain 的结果预估的 rows 值跟实际情况差距比较大，可以采用这个方法来处理。

还有一个方法就是 force index 强制走正确的索引，或者优化 SQL，最后实在不行，可以新建索引，或者删掉错误的索引。

覆盖索引

上面我提到了，可能需要回表这样的操作，那我们怎么能做到不回表呢？在自己的索引上就查到自己想要的，不要去主键索引查了。

覆盖索引

如果在我们建立的索引上就已经有我们需要的字段，就不需要回表了，在电商里面也是很常见的，我们需要去商品表通过各种信息查询到商品 id，id 一般都是主键，可能 sql 类似这样：

select itemId from itemCenter where size between 1 and 6

因为商品 id itemId 一般都是主键，在 size 索引上肯定会有我们这个值，这个时候就不需要回主键表去查询 id 信息了。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

联合索引

还是商品表举例，我们需要根据他的名称，去查他的库存，假设这是一个很高频的查询请求，你会怎么建立索引呢？

大家可以思考上面的回表的消耗对 SQL 进行优化。

是的建立一个，名称和库存的联合索引，这样名称查出来就可以看到库存了，不需要查出 id 之后去回表再查询库存了，联合索引在我们开发过程中也是常见的，但是并不是可以一直建立的，大家要思考索引占据的空间。

刚才我举的例子其实有点生硬，正常通过商品名称去查询库存的请求是不多的，但是也不代表没有哈，真来了，难道我们去全表扫描？

最左匹配原则

大家在写 sql 的时候，最好能利用到现有的 SQL 最大化利用，像上面的场景，如果利用一个模糊查询 itemname like ’敖丙 %‘，这样还是能利用到这个索引的，而且如果有这样的联合索引，大家也没必要去新建一个商品名称单独的索引了。

很多时候我们索引可能没建对，那你调整一下顺序，可能就可以优化到整个 SQL 了。

索引下推

你已经知道了前缀索引规则，那我就说一个官方帮我们优化的东西，索引下推。

select * from itemcenter where name like '敖%' and size=22 and age = 20;

所以这个语句在搜索索引树的时候，只能用 “敖”，找到第一个满足条件的记录 ID1，当然，这还不错，总比全表扫描要好。

然后呢？

当然是判断其他条件是否满足，比如 size。

在 MySQL 5.6 之前，只能从 ID1 开始一个个回表，到主键索引上找出数据行，再对比字段值。

而 MySQL 5.6 引入的索引下推优化（index condition pushdown)， 可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。

唯一索引普通索引选择难题

这个在我的面试视频里面其实问了好几次了，核心是需要回答到 change buffer，那 change buffer 又是个什么东西呢？

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InooDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。

在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作，通过这种方式就能保证这个数据逻辑的正确性。

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。

将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。

除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率

那么，什么条件下可以使用 change buffer 呢？

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。要判断表中是否存在这个数据，而这必须要将数据页读入内存才能判断，如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大，change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置，这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一，change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。

change buffer 的使用场景

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好，这种业务模型常见的就是账单类、日志类的系统。

反过来，假设一个业务的更新模式是写入之后马上会做查询，那么即使满足了条件，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价，所以，对于这种业务模式来说，change buffer 反而起到了副作用。

前缀索引

我们存在邮箱作为用户名的情况，每个人的邮箱都是不一样的，那我们是不是可以在邮箱上建立索引，但是邮箱这么长，我们怎么去建立索引呢？

MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

我们是否可以建立一个区分度很高的前缀索引，达到优化和节约空间的目的呢？

使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本。

上面说过覆盖索引了，覆盖索引是不需要回表的，但是前缀索引，即使你的联合索引已经包涵了相关信息，他还是会回表，因为他不确定你到底是不是一个完整的信息，就算你是 www.aobing@mogu.com 一个完整的邮箱去查询，他还是不知道你是否是完整的，所以他需要回表去判断一下。

下面这个也是我在阿里面试面试官问过我的，很长的字段，想做索引我们怎么去优化他呢？

因为存在一个磁盘占用的问题，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

我当时就回答了一个 hash，把字段 hash 为另外一个字段存起来，每次校验 hash 就好了，hash 的索引也不大。

我们都知道只要区分度过高，都可以，那我们可以采用倒序，或者删减没区分的字符串这样的情况去建立我们自己的区分度，不过大家需要注意的是，调用函数也是一次开销哟，这点当时没注意。

就比如本来是 www.aobing@qq,com 其实前面的www.基本上是没任何区分度的，所有人的邮箱都是这么开头的，你一搜一大堆出来，放在索引还浪费内存，你可以 substring() 函数截取掉前面的，然后建立索引。

我们所有人的身份证都是区域开头的，同区域的人很多，那怎么做良好的区分呢？REVERSE（）函数翻转一下，区分度可能就高了。

这些操作都用到了函数，我就说一下函数的坑。

条件字段函数操作

日常开发过程中，大家经常对很多字段进行函数操作，如果对日期字段操作，浮点字符操作等等，大家需要注意的是，如果对字段做了函数计算，就用不上索引了，这是 MySQL 的规定。

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

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

这个时候大家可以用一些取巧的方法，比如 select from tradelog where id + 1 = 10000 就走不上索引，select from tradelog where id = 9999 就可以。

隐式类型转换

select * from t where id = 1

如果 id 是字符类型的，1 是数字类型的，你用 explain 会发现走了全表扫描，根本用不上索引，为啥呢？

因为 MySQL 底层会对你的比较进行转换，相当于加了 CAST(id AS signed int) 这样的一个函数，上面说过函数会导致走不上索引。

隐式字符编码转换

还是一样的问题，如果两个表的字符集不一样，一个是 utf8mb4，一个是 utf8，因为 utf8mb4 是 utf8 的超集，所以一旦两个字符比较，就会转换为 utf8mb4 再比较。

转换的过程相当于加了 CONVERT(id USING utf8mb4) 函数，那又回到上面的问题了，用到函数就用不上索引了。

还有大家一会可能会遇到 mysql 突然卡顿的情况，那可能是 MySQLflush 了。

flush

redo log 大家都知道，也就是我们对数据库操作的日志，他是在内存中的，每次操作一旦写了 redo log 就会立马返回结果，但是这个 redo log 总会找个时间去更新到磁盘，这个操作就是 flush。

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

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

那什么时候会 flush 呢？

1. InnoDB 的 redo log 写满了，这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写。
2. 系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是 “脏页”，就要先将脏页写到磁盘。

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

这里其实是从性能考虑的，如果刷脏页一定会写盘，就保证了每个数据页有两种状态：

• 一种是内存里存在，内存里就肯定是正确的结果，直接返回；
• 另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。这样的效率最高。

1. MySQL 认为系统 “空闲” 的时候，只要有机会就刷一点“脏页”。
2. MySQL 正常关闭，这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

那我们怎么做才能把握 flush 的时机呢？

Innodb 刷脏页控制策略，我们每个电脑主机的 io 能力是不一样的，你要正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。

这就要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力，这个值建议设置成磁盘的 IOPS，磁盘的 IOPS 可以通过 fio 这个工具来测试。

正确地设置 innodb_io_capacity 参数，可以有效的解决这个问题。

这中间有个有意思的点，刷脏页的时候，旁边如果也是脏页，会一起刷掉的，并且如果周围还有脏页，这个连带责任制会一直蔓延，这种情况其实在机械硬盘时代比较好，一次 IO 就解决了所有问题，

但是现在都是固态硬盘了，innodb_flush_neighbors=0 这个参数可以不产生连带制，在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了。

资料参考：《MySQL 实战》、《高性能 MySQL》、《丁奇 MySQL47 讲》

总结

在本文中我提到了以下知识点：

应该还不算全，行锁、表锁、间隙锁、同步场景等等都没怎么提到，因为他们的场景比较复杂，每种都可以单独开一篇了，丁奇的 MySQL 里面算是很全了，还有就是高性能 MySQL 大家可以展开看看，要是懒也可以等我总结。

每个点我也没多仔细的讲解，主要是篇幅原因，其实每个点在 MySQL 相关书籍都是很多篇幅才介绍完的，我就做个总结，对具体的概念不了解可以用搜索引擎查询相关概念，不过我想我说得还算通俗易懂。