深入理解MySQL之SQL调优

前言

结合MySQL中B+树的索引结构和Explain关键子给出的查询计划，我们就可以有针对性的对一些慢SQL进行优化，这篇博客就来总结一下优化思路。
先声明一下表结构：

CREATE TABLE `s1` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`key1` VARCHAR(100),
	`key2` VARCHAR(100),
	`key3` VARCHAR(100),
	`key_part1` VARCHAR(100),
	`key_part2` VARCHAR(100),
	`key_part3` VARCHAR(100),
	`common_field` VARCHAR(100),
	PRIMARY KEY (`id`),
	KEY idx_key1 (`key1`),
	UNIQUE KEY idx_key2 (`key2`),
	KEY idx_key3 (`key3`),
	KEY idx_key_part(`key_part1`, `key_part2`, `key_part3`)
) Engine=InnoDB CHARSET=utf8;

CREATE TABLE `s2` (
	`id` INT NOT NULL AUTO_INCREMENT,
	`key1` VARCHAR(100),
	`key2` VARCHAR(100),
	`key3` VARCHAR(100),
	`key_part1` VARCHAR(100),
	`key_part2` VARCHAR(100),
	`key_part3` VARCHAR(100),
	`common_field` VARCHAR(100),
	PRIMARY KEY (`id`),
	KEY idx_key1 (`key1`),
	UNIQUE KEY idx_key2 (`key2`),
	KEY idx_key3 (`key3`),
	KEY idx_key_part(`key_part1`, `key_part2`, `key_part3`)
) Engine=InnoDB CHARSET=utf8;

两张表除了表名外结构一样，除了id主键索引外，还为key1和idx_key3建了普通索引，为key2建了唯一索引，为key_part1和key_part2和key_part3建了联合索引。s1表中有1000条数据，s2表中有10000条数据。

一般查询优化

常见的 type（访问类型）从最优到最差分别为：system > const > eq_ref > ref > ref_or_null > range > index > ALL，一般来说，得保证查询达到 range 级别，最好达到 ref，如果没有达到就需要考虑优化了。

避免全字段查询

尽量在查询的时候，指定查询需要的字段，减少SELECT *的使用，在可能使用索引覆盖的场景下，不仅可以节省MySQL查询优化器对于是否回表的判断开销，还可以减少MySQL回表的次数，扫描索引树就能获得结果。比如：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 = "bb";

等值匹配

这种情况的优化方式一般是尽量让搜索条件能用上索引，注意联合索引尽量不要出现中间断开的场景，比如：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 = "bb" AND key_part3 = "aa";

从 key 长度为 303 可以得知只用到了联合索引的第一个列。

模糊匹配

模糊查询可以使用右模糊查询，尽量不要使用左模糊查询，比如：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 LIKE "bb%"

右模糊的查询类型是 range 级别，再看一下左模糊：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 LIKE "%bb"

此时查询类型就变成 index 级别了。

分页查询优化

一般的分页语句：

EXPLAIN SELECT * FROM s2 LIMIT 9000, 5;

在表记录连续不会物理删除的情况下，即主键连续自增的情况下，可以这样优化：

EXPLAIN SELECT * FROM s2 WHERE id > 9000 LIMIT 5;

显然优化后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高。但是，优化后的 SQL 在很多场景并不适用，因为表中可能某些记录被删后，主键空缺，导致结果不一致。
所以这种优化得满足以下两个条件：

• 主键自增且连续
• 结果是按照主键排序的

一般的分页排序语句：

EXPLAIN SELECT * FROM s2 ORDER BY key_part1 LIMIT 9000, 5;

可以看到并没有使用到联合索引，这是因为扫描整个索引树并查找到没索引的行（可能要遍历多个索引树）的成本比扫描全表的成本更高，所以优化器放弃使用索引。

优化思路是让排序时返回的字段尽可能少，可以让排序和分页操作先查出主键，然后根据主键查到对应的记录，SQL 改写如下：

EXPLAIN SELECT * FROM s2 s INNER JOIN (SELECT id FROM s2 ORDER BY key_part1 LIMIT 9000, 5) t ON t.id = s.id;

原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序，且分页和排序使用到了联合索引。

排序分组优化

MySQL支持两种方式的排序 filesort 和 index，Using index 是指 MySQL 扫描索引本身完成排序，filesort 是使用文件排序。index 效率高，filesort 效率低。
排序分组优化方式一般是尽量让排序和分组字段用上索引，比如：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 = "bb" ORDER BY key_part1;

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 = "bb" GROUP BY key_part1 ORDER BY NULL;

order by 满足两种情况会使用 Using index：

• order by 语句使用索引最左前列
• 使用 where 子句与 order by 子句条件列组合满足索引最左前列

比如：

EXPLAIN SELECT * FROM s1 WHERE key_part1 = "bb" ORDER BY key_part1, key_part3;

此时排序条件跳过了联合索引的中间列，导致需要使用文件排序。

需要注意的点：

• 如果是联合索引，不要出现中间断开的场景
• 不要出现倒序正序交替的场景，要么都是倒序，要么都是正序
• where 高于 having，能写在 where 中的限定条件就不要在 having 中限定

用于排序的联合索引中间断开的场景，比如：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 = "bb" ORDER BY key_part1, key_part3;

可以看到只用到了联合索引的第一个列。

出现倒序正序交替的场景，比如：

EXPLAIN SELECT key_part1, key_part2, key_part3 FROM s1 WHERE key_part1 = "bb" AND key_part2 = "aa" ORDER BY key_part1, key_part2;

此时都是正序排序，Extra 提示 Using index 扫描索引就可以完成排序，如果正序倒序交替，比如：

EXPLAIN SELECT * FROM s1 WHERE key_part1 = "bb" ORDER BY key_part1 ASC, key_part3 DESC;

此时 Extra 提示 Using filesort 需要用到文件排序。

限定条件用在 having 和 where 的比较：

EXPLAIN SELECT key_part1, COUNT(id) num FROM s1 GROUP BY key_part2 HAVING key_part1 = "aa";

EXPLAIN SELECT key_part1, COUNT(id) num FROM s1 WHERE key_part1 = "aa" GROUP BY key_part2;

关联查询优化

MySQL的表关联常见有两种算法：

• Nested-Loop Join（NLJ） 算法
• Block Nested-Loop Join（BNL） 算法

1、 嵌套循环连接 Nested-Loop Join（NLJ） 算法
循环从驱动表中读取行数据，在这行数据中获取到关联字段，根据关联字段在被驱动表里取出满足条件的行，然后取出两张表的结果合集。

EXPLAIN SELECT * FROM s2 INNER JOIN s1 ON s1.key3 = s2.key3;

从执行计划中可以看到这些信息：

• 驱动表是 s1，被驱动表是 s2。可以看到优化器一般会优先选择小表作为驱动表。所以使用 inner join 时，排在前面的表并不一定就是驱动表。
• 使用了 NLJ 算法。一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer 则表示使用的算法是 NLJ。

SQL的执行过程大致如下：

• 从表 s1 中读取一行数据
• 从取出的一行数据中取出关联字段 key3，到表 s2 中查找
• 取出表 s2 中满足条件的行，跟 s1 中获取到的结果合并，作为结果返回给客户端
• 重复上面步骤，直到连接条件不满足

整个过程会读取 s1 表的所有数据（扫描1000行），然后遍历每行数据中字段 key3 的值，根据 s1 表中 key3 的值索引扫描 s2 表中的对应行（扫描1000次 s2 表的索引，1次扫描可以认为最终只扫描 s2 表一行完整数据，也就是总共 s2 表也扫描了1000 行）。因此整个过程扫描了 2000 行。

2、基于块的嵌套循环连接 Block Nested-Loop Join（BNL）算法
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行读取出来跟 join_buffer 中的数据做对比。如果被驱动表的关联字段没索引，MySQL会选择 Block Nested-Loop Join 算法。

EXPLAIN SELECT * FROM s2 INNER JOIN s1 ON s1.common_field = s2.common_field;

Extra 中的 Using join buffer （Block Nested Loop）说明该关联查询使用的是 BNL 算法。 上面SQL的执行过程大致如下：

• 把 s1 所有的数据放入到 join_buffer 中
• 把表 s2 中每一行数据取出来，跟 join_buffer 中的数据做对比
• 返回满足连接条件的数据

整个过程对表 s1 和 s2 都做了一次全表扫描，因此扫描的总行数为11000行（表 s1 的数据总量 1000 + 表 s2 的数据总量10000）。并且 join_buffer 里的数据是无序的，因此对表 s2 中的每一行，都要做 1000 次判断，所以内存中的判断次数是 1000 * 10000= 1000 万次。
可以看到这种算法的扫描行数为两张表的记录总和，并且还有两张表数据量乘积的内存比较操作，但是相比于磁盘扫描，内存比较操作要快得多。

被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 NLJ 呢？
如果第二条SQL使用 NLJ 算法，由于没有索引，所以只能遍历扫描全表，那么磁盘扫描表数据就达到了1000 * 10000= 1000 万次，而用 BNL 算法的磁盘扫描行数会少很多，相比于磁盘扫描，BNL 算法的内存计算会快得多。

因此MySQL对于被驱动表的关联字段没有索引的关联查询，一般为了减少磁盘扫描的 IO 开销，会倾向于使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL 算法性能更高。

对于关联查询的优化：

• 关联字段加索引，让MySQL做关联操作时尽量选择 NLJ 算法
• 小表驱动大表，写多表连接SQL时如果明确知道哪张表是小表可以用 straight_join 写法固定连接驱动方式，省去MySQL优化器优化的性能开销。

straight_join 功能同 join 类似，但能指定让左边的表来驱动右边的表，能修改表优化器对于联表查询的执行顺序。
比如可以用 NLJ 算法查询的SQL语句，使用了 straight_join 后换成了 BNL 算法：

EXPLAIN SELECT * FROM s2 STRAIGHT_JOIN s1 ON s1.common_field = s2.key1;

强制用 s2 表作为驱动表后，原来能用 NLJ 算法现在MySQL使用了 BNL 算法来执行查询。使用的时候注意两点：

• 只适用于 inner join，并不适用于 left join，right join。（因为left join，right join已经指定了表的执行顺序）
• 尽可能让优化器去判断，因为大部分情况下MySQL优化器是比人要聪明的。使用 straight_join 一定要慎重，部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

总结

• 为表建立合适的索引，但是也不要建立太多索引，索引有开销和维护的成本，不合适的索引反而适得其反
• 模糊查询可以使用右模糊查询，尽量不要使用左模糊查询
• 联合索引要遵守最左前缀法则，查询从索引的最左列开始并且不跳过索引中的列
• 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则
• 不在索引列上做任何操作（计算、函数、类型转换等），会导致索引失效而转向全表扫描
• 尽量使用覆盖索引（只访问索引包含的列的查询），减少SELECT * 语句
• MySQL在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描
• IS NULL，IS NOT NULL 无法使用索引，有的版本可以
• 字符串不加单引号会导致索引失效
• 少用 OR 或 IN，MySQL内部优化器会根据检索比例、 表大小等多种因素整体评估是否使用索引
• 范围查询优化，将大的查询范围拆成多个小的查询范围
• 关联字段加索引，让MySQL做关联操作时尽量选择 NLJ 算法
• 小表驱动大表，写多表连接SQL时如果明确知道哪张表是小表可以用 straight_join 写法固定连接驱动方式，省去MySQL优化器优化的性能开销。