从一个案例出发，入门 MySQL 的性能优化

本文将从案例出发，带着实际问题带领读者入门 MySQL 的性能优化，涉及到的内容主要是入门知识，面向对 MySQL 不甚了解的读者。如果能让任何人有那么一点点收获，那我不胜荣幸。

话不多说，先介绍一下这个案例。

案例

起

事情还要从四个月前说起，刚开始后端实习的我接到了我的第一个需求，给落地页的管理端加一个 page 列表的功能。这活我熟啊，做前端的时候没少跟这种需求打交道，分页、检索一气呵成，交差上线。结果几天后有问题反馈过来了，说是有一个接口超时，赶紧拿过logid来查查看，结果让我倒吸一口凉气。

承

根据 log 来看，是一条 SQL 语句出现慢查询了，花费了 17s 之多才返回结果，可是当初测试的时候并没有出现这种情况啊，事情有点蹊跷，继续查下去我才发现自己掉入了一个大坑中。

背景：落地页这边有 template 表和 page 表，两张表根据 template_id 字段关联，其中 template 表中有 template_name 字段，跟 template_id 是 1 对多的关系，template_id 与 page 是 1 对多的关系。落地页的管理端需要根据 template_name 字段查 page list，需要分页，所以需要查 count 总数。

方案：先根据 template_name 查 template id 列表，然后根据 where template_id in (template_id...) 查 page list

结果就是出现了两条耗时差异巨大的查询语句：

	SQL A：	SQL B
Duration	17.7s	~400ms
param	template_name = "xxxxx"	template_name = "yyyyy"
SQL	SELECT SQL_NO_CACHE count(page_id) FROM `page` WHERE (template_id IN ( /* many ids*/ ) ) AND (is_del = '0' )	SELECT SQL_NO_CACHE count(page_id) FROM `page` WHERE (template_id IN ( /* many ids but less than SQL A*/ ) ) AND (is_del = '0' )
explain

转

两个 SQL 结构、字段一致，只有 IN 语句中限定的值范围不一样，就导致一个正常应用了索引(type 为 range)，一个没有(type 为 all)，仔细观察 explain 的结果可以看到 SQL A 中没有用到索引，而且 explain 还告诉我们一个信息：对于 SQL A possible_keys 跟 SQL B 一样，都是 idx_template_id，但是 type、key、extra 等几个字段中都告诉我们，索引没生效。 What? Why? How?

查了一下资料，我选择尝试添加 FORCE INDEX (idx_template_id) ，耗时变成了 ～700ms，问题解决了，explain 可以看到强制索引生效了，但是也多了一个奇怪的参数：Using MRR。

explain 的用法

explain 是排查 MySQL 性能问题最常用的工具了，用法也很简单，只需要在你执行的语句前面加上 expalin 关键词，MySQL 就会返回执行这条语句的执行计划。执行计划会告诉我们很多信息，在我们这个 case 中，type 字段为 all 表示执行了全表扫描，type 为 range 表示用到了索引执行范围查找，这是因为我们用到了 in 操作符。当用到索引的时候，key 字段会告诉我们用到了什么索引，而 possible_keys 的含义则是该语句可用的索引有哪些，但是实际用到的索引要看 key 字段。

另一个非常重要的字段是 extra，在我们这个 case 中 有两个有趣的值：Using Index Condition 和 Using MRR。我们在下面会简单介绍一下

关于 explain，网上有很多文章，但是最清晰的还是官方文档：dev.mysql.com/doc/refman/…

** MRR**

MRR，全称「Multi-Range Read Optimization」。

简单说 MRR 把「随机磁盘读」，转化为「顺序磁盘读」，主要原理是在从索引中读出记录后，查磁盘前先按照记录在磁盘中的顺序排序，然后再去读磁盘，这样可以减少磁盘的机械运动次数，另外也可以充分利用磁盘预读(磁盘预读是指在一次磁盘读取中，将要读取的数据附近的数据放进数据缓存区，这样做的原因是局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用)。

可以读一下这篇文章 zhuanlan.zhihu.com/p/110154066

合

How 的问题解决了，但是 Why 还是不理解，为什么相同的语句，A 和 B 会差异这么大呢？这时候只好再去恶补一下 MySQL 的原理了。那么接下来，就是为大家揭开真相的时刻～

一条 SQL 语句是如何执行的

我们首先过一遍一条 SQL 是如何执行的，我们在图中区分了 client 和 server，但是在很多文档中，会将存储引擎单独拆出去，因为在 MySQL 架构中，存储引擎是一个相对独立的模块，也有多种存储引擎可供选择，以适应不同的应用场景。大家在读文档时需要注意根据上下文区分。

如上图所示，数据库在处理一次查询时，首先会查询缓存，如果命中缓存则直接返回。这就是为什么我们在测试时需要添加参数 SQL_NO_CACHE ，目的是要强制本次查询不走缓存。

如果没有命中缓存就会进入解析器，解析器将查询语句解析为 AST 后，会在执行下一步之前执行鉴权相关的逻辑。

下一步查询优化器会根据数据库内部的统计信息和数据的分布情况，找到执行这一查询的最高效的方法，通常把执行查询的各种方式称之为执行计划。这里就是我们的案例中，第一条语句没有使用索引的原因(逆向优化了😭)。但是其实在大多数情况下，查询优化器都会帮我们找到更优的执行计划，甚至会帮我们优化我们写出来的有性能问题的语句。

比如说这样一条语句：

explain select SQL_NO_CACHE count(page_id) from page where library_id in (0);

理论上，library_id 列建索引的情况下in 操作会执行范围查找，也就是我们上文中所说的 type = range，但是实际上我们执行的结果却是 type = ref，为什么会这样呢？

我们可以在执行完 explain 之后，执行一下 SHOW WARNINGS 来看一下最终的执行计划：

/* select#1 */ select sql_no_cache count(`tetris_data_i18n_sg`.`page`.`page_id`) AS `count(page_id)` from `tetris_data_i18n_sg`.`page` where (`tetris_data_i18n_sg`.`page`.`library_id` = 0)

可以看到我们的 in 操作被优化成了 =。

在执行完 explain 语句后，执行 SHOW WARNINGS 可以查询 explain 的扩展信息，其中 message 字段中展示了查询优化器优化、改写之后的查询语句。

查询优化器选择出了“最优”执行计划后，这一计划就被送到执行引擎，执行引擎会负责把这一执行计划发往本地的存储引擎和其他节点的存储引擎，然后收集执行结果，并将这一结果返回。

“最优”计划的选择

那么优化器是如何生成最优计划的呢？

优化器主要由转换模块、计划生成模块、评估模块三个模块组成，其中最重要的是评估模块，它会负责决定不同执行计划的成本，作为最优计划的选择标准。

那么成本是如何决定的呢？有三个维度：Selectivity、Cardinality、Cost，这三个维度都是估算的，某些存储引擎会提供准确的统计信息，那估算就会更准确一些。

Selectivity 可以翻译成选择性，它是一个比例值，代表选择的行在所有行中的比例，分母可能是一张表、一个视图、或者 join 的结果。分子则是根据 where 条件、group等，这个值对外部是不可见的。但一般来说 selectivity 值越低越好，也就是说选择性越高越好。

Cardinality 直译的话是基数，但是这个词很难理解。实际上是指应用了筛选条件后会返回的行数，在 explain 中我们能看到 rows 一列就是这一估算值。比如说对于这一case：

我们可以看到由于这个查询没有索引可用，所以就会使用全表扫描的方式，而由于没有对列的统计信息，全表扫描返回的行数与左侧表信息中的行数是一致的。

但是如果我们执行的是有索引的语句，那 Cardinality 的范围就会大大减少：

不难理解，Selectivity 与 Cardinality 互为倒数关系，所以就有了这个公式：

cardinality = selectivity × total number of rows

至于 Cost 则指的是执行计划所需要的 CPU、内存占用、I/O操作等消耗。这个值也是内部值，我们无法查看。

这三个维度共同决定了最优计划的选择，但是由于这些数值依赖根据统计数据的估算，因此最终的结果可能并不会是实际上最优的计划。

参考文档

MySQL :: MySQL 8.0 Reference Manual :: 8.8.2 EXPLAIN Output Format

What does eq_ref and ref types mean in MySQL explain

MySQL 的 MRR 到底是什么？

高性能MySQL(第3版)

数据库系统内幕

cardinality翻译成基数很不好，误导科技工程人员! - Oracle数据库管理 - ITPUB论坛-专业的IT技术社区

What are cardinality statistics?