这篇主要介绍对慢 SQL 优化的一些手段，而在讲解具体的优化措施之前，我想先对 EXPLAIN 进行介绍，它是我们在分析查询时必要的操作，理解了它输出结果的内容更有利于我们优化 SQL。为了方便大家的阅读，在下文中规定类似 key1 的表示二级索引，key_part1 表示联合索引的第一部分，unique_key1 则表示唯一二级索引，primary_key 表示主键索引。

1. Explain 详解

Explain 是我们在对慢 SQL 进行优化前常用语句，它能分析具体的查询计划，从而让我们有目的地去进行优化。本节则主要是让大家看懂 Explain 查询结果的每一列是干啥用的，我们先简要的来看一下各个列的作用：

列名 描述
id 在一个大的查询语句中，每个 SELECT 关键字都对应一个唯一的 id。在连接查询中，记录的 id 值都是相同的；在多个 SELECT 关键字的查询中，查询优化器可能会对子查询进行优化，使得多条 SELECT 记录的 id 值相同
select_type 查询类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际使用的索引
key_len 实际使用的索引长度
ref 当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 针对预估的需要读取的记录，经过搜索条件过滤后剩余记录条数的百分比。在单表查询中没什么意义，在连表查询中可以计算出在驱动表执行完查询后，还需要对被驱动表执行多少次查询
Extra 额外的备注信息

其中大部分列在描述信息中已经解释的足够清楚，下面我们主要对一些必要的列进行详述：

1.1 select_type

1.2 type

1.3 ref

当访问方法是 const、ref、ref_or_null、eq_ref、unique_subquery 和 index_subquery 其中之一时，ref 列展示的是与索引列进行 等值匹配 的东西是啥：

1.4 Extra

2. 优化考虑点

基于访问类型优化

在前文中我们已经详细介绍了 EXPLAIN 语句中的访问类型（type），如果一个查询的访问类型并不是我们预期的，那么最简单直接的解决办法是为搜索条件列 增加合适的索引。

减少扫描行数的优化

在有些情况下，简单地增加索引并不能解决问题，比如执行如下 SQL：

select name, count(name) from specific_table group by key1;

这条 SQL 执行完毕后可能只返回几行数据，但是因为有 COUNT 聚合函数，需要扫描的数据可能会有成千上万行，这取决于表中数据量总数。对于这种 扫描大量数据却只返回少数行 的情况，通常可以通过 增加单独的汇总表 进行优化，当然这需要在应用层增加相应的逻辑对汇总表的数据进行维护。

除此之外，还可以通过 重写复杂查询 的方式来优化，下面我们对重写查询时需要考虑的方向进行介绍：

一个复杂查询还是多个简单查询？

这是一个值得考虑的问题。将复杂查询拆成多个简单查询，尽可能地减少数据库的工作，并将一些处理逻辑拿到应用层处理，因为 MySQL 处理简单查询很高效，所以通常情况下这么做能够提高效率。

切分处理

在实际工作中，对数据量较大的数据库表进行结转（或删除）时通常会采用 切分处理 的方法，将一个大查询分成小查询，每个查询的作用是一样的，只不过操作的数据量不同，各个小查询执行完毕后，大查询的任务也就处理完成了。

一次性结转大量数据可能会锁住很多数据、占满整个事务日志、耗尽系统资源和阻塞很多小的查询等，为了避免这种情况，通常在一次数据结转任务中只操作 一万条左右 数据，这样对服务器影响最小，而且可以在每次结转完成时，都 暂停一会儿 再去执行下一次任务，这样做可以将压力分散到一个比较长的时间段中，大大降低对服务器的影响和减少持有锁的时间。

优化联结查询

阿里巴巴开发手册中提到过一点，联表查询时联表的数量不超过 3 个。如果联表过多，我们需要将其拆成多个查询或多个单表查询（单表查询的 缓存效率会更高），查询被分解后，查询间的锁竞争会减少。除此之外，联表查询还需要注意以下两点：

IN() 条件与 OR 条件

一般情况下我们认为 IN() 完全等价于多个 OR 条件，但是在 MySQL 中这两者是有区别的。MySQL 在处理 IN() 条件时，会将列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个时间复杂度为 O(logn) 的操作，如果等价地转换成 OR 查询，它的时间复杂度为 O(n)，所以在 IN() 条件中有大量取值时，MySQL 的处理速度会更快。

查询时索引是否失效

排序时索引是否失效

索引列不为空的优化

当需要 Min() 和 Max() 操作时，索引列不为空可以让它们更高效。比如要找到某一列的最小值，只需要查询对应 B-Tree 索引的最左端记录，查询优化器会将这个表达式看做一个常数对待，而且能够在 ESPLAIN 结果的 Extra 列中发现 “Select tables optimized away”。

重复索引和冗余索引

重复索引指的是在相同的列上按照相同顺序创建的相同类型的索引，如下 SQL 所示：

create table specific_table (
    id int not null primary key,
    unique key(id)
)engine=InnoDB;

它在 id 列上创建了两个相同的索引，需要将其中的唯一索引移除。

冗余索引通常发生在为表添加新的索引时，比如在已有索引（column_a），再添加一个索引（column_a, column_b），这就是发生了冗余索引的情况，因为第二个联合索引能够发挥和单列索引一样的作用。

大多数情况下都不需要冗余索引，我们应该尽量扩展已有的索引而不是创建新的索引。

是否存在索引合并

在多列上独立地创建多个单列索引，大部分情况下并不能提高 MySQL 的查询性能。

MySQL 中有一种 “索引合并” 的策略，它可以 使用表中的多个单列索引 来定位指定的数据行，并将扫描结果进行合并。索引合并的策略有时候非常不错，但更多的时候，它说明了表中的 索引建的比较糟糕：

通常来说，我们需要考虑 重建索引 或者 使用 UNION 改写查询。除此之外，可以通过修改 optimizer_switch 参数来关闭索引合并功能，如下 SQL：

SELECT @@optimizer_switch;

-- 改成 index_merge=off 
set optimizer_switch = 'index_merge=off, ...';

还可以使用 IGNORE INDEX 语法让优化器来忽略到某些索引，从而避免优化器使用包含该索引的索引合并执行计划：

select * from specific_table ignore index(index_name)
where column_name = #{value};

除了在发生索引合并时考虑忽略索引，也需要在执行查询时因无法形成合适的扫描区间，达不到减少扫描记录的数量的目的时，考虑忽略索引而使用全表扫描。

下面我们介绍三种索引合并的类型，让大家对索引合并有一个更加充分的了解：它们分别是 Intersection 索引合并、Union 索引合并 和 Sort-Union 索引合并。

Intersection 索引合并

我们看如下查询：

select * from specific_table where key1 = 'a' and key2 = 'b';

我们都能清楚的是：在索引列值相同的情况下，二级索引记录是按照主键值的大小排序的，那么可以将 key1 筛选出的主键值和 key2 筛选出的主键值 取交集，根据结果再去执行回表操作，这相比于分别对 key1 和 key2 筛选出的主键值都去做回表的开销要低，这种情况使用的是 Intersection 索引合并策略。

Union 索引合并

我们看如下查询：

select * from specific_table where key1 = 'a' or key2 = 'b';

将 key1 筛选出的主键值和 key2 筛选出的主键值 取并集，再根据结果去做回表操作，这种做法被称为 Union 索引合并，它可能相比于直接做全表扫描的开销要低。需要注意的是：Union 索引合并要求二级索引筛选出的主键值是有序的，如果主键值无序则需要考虑 Sort-Union 索引合并。

Sort-Union 索引合并

有如下查询：

select * from specific_table where key1 < 'a' or key2 > 'b';

我们将上述查询条件更改成了范围查询条件，现在各索引筛选出的主键值是无序的，所以无法使用 Union 索引合并，而 Sort-Union 索引合并正是在 Union 索引合并的基础上添加了排序操作：将 key1 筛选出的主键值和 key2 筛选出的主键值 进行排序，这样就能够继续使用 Union 索引合并了。

优化 COUNT()

当我们需要 统计有值的结果 时，需要在 COUNT() 条件内指定列名或 COUNT(0)；当我们需要 统计所有的行数 时，需要指定为 COUNT(*)，它会忽略所有列而直接统计所有行数。明白了这两点之后，我们做数据统计能够更清晰的传达意图。

通常来说，COUNT() 查询需要扫描大量的数据行才能获得精确的结果，所以比较难优化。如果业务场景不要求完全精确，我们可以 使用 EXPLAIN 估算的行数 rows 来代替；或者，我们去掉一些查询条件中的约束，删除 DISTINCT 来避免排序操作，这些做法都可能使统计查询性能提高。

优化 UNION 查询

在我们使用 UNION 查询时，如果不需要消除重复的行，一定要使用 UNION ALL，因为如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT，这会对数据做去重，代价比较高。此外，我们可以将 WHERE、LIMIT 和 ORDER BY 语句应用到每个查询中，这样能够让 MySQL 对它们更好地进行优化。

优化 OFFSET

在分页查询中，OFFSET 会导致 MySQL 扫描大量不需要的行然后再抛弃掉，比如 LIMIT 1000, 20 这个表达式，它会查询 1020 条数据然后将前 1000 条抛弃掉，这样做的代价非常高。

我们可以通过采用 书签 的方式记录上次读取数据的 “位置”，那么下次查询就能直接从该位置开始扫描，避免使用 OFFSET。比如说，每页展示 20 条数据，我们记录下来当前所在页面的数据 ID 值为 200，那么我们看下一页的数据时，查询 SQL 如下：

select * from specific_table
where id <= 180
limit 20;

不过，这种情况也有不足，它没有办法指定页码进行查询，比如说我现在想看第 5 页的数据，我们没办法计算对应页具体的 ID 值范围。除非我们能够保证 ID 值是单调递增且没有删除过数据的，这样的话， ID 值是连续的，我们就能轻易的计算出第 5 页的数据的 ID 值是从 120 开始的。这样做的好处是无论翻页到多么靠后，它的性能都很好。

使用 WITH ROLLUP 优化 GROUP BY

我们通常使用 GROUP BY 做分组聚合查询，如果还要对分组后的结果再次求和，可以使用 WITH ROLLUP 操作，但是更好的办法还是将 WITH ROLLUP 的处理拿到应用层去做。

OPTIMIZE TABLE

如果我们 删除了很多数据，或者在插入数据时，不是按照主键的递增顺序插入的，很可能会因此产生很多内存碎片，影响数据查询的效率。这是因为在删除数据时，MySQL 并不会立即将它们清除并整理空间，而是将它们标记为删除，通过 OPTIMIZE TABLE 可以将空间进行整理，减少内存碎片。

InnoDB 引擎并不支持 OPTIMIZE TABLE 操作，它会提示如下信息：

OPTIMIZE TABLE specific_table;

-- Table does not support optimize, doing recreate + analyze instead

我们可以通过不做任何操作的 ALTER 命令来重建表达到以上目的：

alter table specific_table engine=InnoDB;

执行完成后，我们通过如下 SQL 查看执行情况，如果 data_free 列为 0，说明我们空间碎片整理成功

show table status from specific_db like specific_table;

不过，多数情况下不需要执行该操作。

找到并修复损坏的表

可能因硬件问题、MySQL 本身的缺陷或者操作系统的问题导致索引损坏，当然这种问题非常少见，我们可以通过如下 SQL 来检查大多数表和索引的错误：

check table specific_table;

如果发现异常的话，可以通过如下 SQL 进行修复：

repair table specific_table;

-- 如果存储引擎不支持上述操作的话，也可通过表重建来完成
alter table specific_table engine=InnoDB;

巨人的肩膀

作者：京东物流 王奕龙

来源：京东云开发者社区 自猿其说 Tech 转载请注明来源


↙↙↙阅读原文可查看相关链接，并与作者交流