一条慢查询会造成什么后果？之前我觉得不就是返回资料会慢一些嘛，使用者体验变差一点而已？

其实远远不止，我经历过几次线上事故，有一次就是由一条 SQL 慢查询导致的。
那次是一条 SQL 查询耗时达到 2–3 秒「没有命中索引，导致全表扫描」，由于是高频查询，併发一起来很快就把 DB 执行绪池打满了，导致大量查询请求堆积，DB 伺服器 CPU 长时间 100%+，大量请求 timeout…
最终系统崩溃，老闆登场!
可见，团队如果对慢查询不引起足够的重视，风险是很大的。
经过那次事故我们老闆就说：谁的程式码再出现类似事故，开发和部门主管一起走人，吓唬得一大堆主管心里慌慌，赶紧招了两位 DBA 同事。

慢查询，顾名思义，执行很慢的查询。有多慢？超过 long_query_time 引数设定的时间阈值(预设 10s)，就被认为是慢的，是需要改善的。慢查询被记录在慢查询日誌里。

慢查询日誌预设是不开启的，如果你需要改善 SQL 语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要改善的(想想一个 SQL 要 10s 就可怕)。好了，下面我们就一起来看看怎么处理慢查询。

慢查询配置

开启慢查询

MySQL 支援透过以下方式开启慢查询：
输入指令开启慢查询(临时)，在 MySQL 服务重启后会自动关闭。
配置 my.cnf(Windows 是 my.ini)系统档案开启，修改配置档案是持久化开启慢查询的方式。

方式一：透过指令开启慢查询

步骤 1：查询 slow_query_log 检视是否已开启慢查询日誌：

show variables like '%slow_query_log%';mysql> show variables like '%slow_query_log%';+---------------------+-----------------------------------+| Variable_name       | Value                             |+---------------------+-----------------------------------+| slow_query_log      | OFF                               || slow_query_log_file | /var/lib/mysql/localhost-slow.log |+---------------------+-----------------------------------+2 rows in set (0.01 sec)

步骤 2：开启慢查询指令：

set global slow_query_log='ON';

步骤 3：指定记录慢查询日誌 SQL 执行时间得阈值(long_query_time 单位：秒，预设 10 秒)。
如下我设定成了 1 秒，执行时间超过 1 秒的 SQL 将记录到慢查询日誌中：

set global long_query_time=1;

步骤 4：查询 “慢查询日誌档案存放位置”。

show variables like '%slow_query_log_file%';mysql> show variables like '%slow_query_log_file%'; +---------------------+-----------------------------------+ | Variable_name       | Value                             | +---------------------+-----------------------------------+ | slow_query_log_file | /var/lib/mysql/localhost-slow.log | +---------------------+-----------------------------------+ 1 row in set (0.01 sec)

slow_query_log_file 指定慢查询日誌的储存路径及档案(预设和资料档案放一起)。
步骤 5：核对慢查询开启状态，需要退出当前 MySQL 终端，重新登入即可重新整理。
配置了慢查询后，它会记录以下符合条件的 SQL：

查询语句资料修改语句已经回滚的 SQL

方式二：透过配置 my.cnf(Windows 是 my.ini)系统档案开启

(版本：MySQL 5.5 及以上)。
在 my.cnf 档案的 [mysqld] 下增加如下配置开启慢查询，如下图：

# 开启慢查询功能 slow_query_log=ON # 指定记录慢查询日誌SQL执行时间得阈值 long_query_time=1 # 选填，预设资料档案路径 # slow_query_log_file=/var/lib/mysql/localhost-slow.log

重启资料库后即持久化开启慢查询，查询验证如下：
慢查询日誌介绍

如上图，是执行时间超过 1 秒的 SQL 语句(测试)：
Row 1：记录时间。
Row 2：使用者名称 、使用者的 IP 资讯、执行绪 ID 号。
Row 3：执行花费的时间【单位：秒】、执行获得锁的时间、获得的结果列数、扫描的资料列数。
Row 4：这 SQL 执行的时间戳。
Row 5：具体的 SQL 语句。

Explain 分析慢查询 SQL

分析 MySQL 慢查询日誌，利用 Explain 关键字可以模拟改善器执行 SQL 查询语句，来分析 SQL 慢查询语句。
下面我们的测试表是一张 137万资料的 app 资讯表，我们来举例分析一下。
SQL 示例如下：

-- 1.185s SELECT * from vio_basic_domain_info where app_name like '%翻译%' ;

这是一条普通的模糊查询语句，查询耗时：1.185s，查到了 148笔资料。
我们用 Explain 分析结果如下表，根据表资讯可知：该 SQL 没有用到栏位 app_name 上的索引，查询型别是全表扫描，扫描资料 137笔。

mysql> EXPLAIN SELECT * from vio_basic_domain_info where app_name like '%翻译%' ; +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ | id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ |  1 | SIMPLE      | vio_basic_domain_info | NULL    | ALL  | NULL     | NULL | NULL    | NULL | 1377809 |    11.11 | Using where | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

当这条 SQL 使用到索引时，SQL 如下：查询耗时：0.156s，查到 141 条资料：

-- 0.156s SELECT * from vio_basic_domain_info where app_name like '翻译%' ;Explain 分析结果如下表;根据表资讯可知：该 SQL 用到了idx_app_name索引，查询型别是索引範围查询，扫描资料 141 笔。由于查询的栏位不全在索引中(select *)，因此回表了一次，取了其他栏位的资料。mysql> EXPLAIN SELECT * from vio_basic_domain_info where app_name like '翻译%' ; +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ | id | select_type | table                 | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ |  1 | SIMPLE      | vio_basic_domain_info | NULL       | range | idx_app_name  | idx_app_name | 515     | NULL |  141 |   100.00 | Using index condition | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)

当这条 SQL 使用到覆盖索引时，SQL 如下：查询耗时：0.091s，查到 141 条资料。

-- 0.091s SELECT app_name from vio_basic_domain_info where app_name like '翻译%' ;

Explain 分析结果如下表;根据表资讯可知：和上面的 SQL 一样使用到了索引，由于查询栏位就包含在索引栏位中，又省去了 0.06s 的回表时间。

mysql> EXPLAIN SELECT app_name from vio_basic_domain_info where app_name like '翻译%' ; +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ | id | select_type | table                 | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ |  1 | SIMPLE      | vio_basic_domain_info | NULL       | range | idx_app_name  | idx_app_name | 515     | NULL |  141 |   100.00 | Using where; Using index | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec)

那么是如何透过 EXPLAIN 解析结果分析 SQL 的呢?各栏位属性又代表着什么?一起往下看。
各栏位属性的简介
各栏位属性的简介如下：
id：SELECT 的查询序列号，体现执行优先顺序，如果是子查询，id的序号会递增，id 值越大优先顺序越高，越先被执行。
select_type：表示查询的型别。
table：输出结果集的表，如设定了别名，也会显示。
partitions：匹配的分割槽。
type：对錶的访问方式。
possible_keys：表示查询时，可能使用的索引。
key：表示实际使用的索引。
key_len：索引栏位的长度。
ref：栏位与索引的比较。
rows：扫描出的列数(估算的列数)。
filtered：按表条件过滤的列百分比。
Extra：执行情况的描述和说明。

慢查询分析常用到的属性

type
对表访问方式，表示 MySQL 在表中找到所需资料的方式，又称“访问类型”。
存在的类型有：ALL、index、range、ref、eq_ref、const、system、NULL(从左到右，性能从低到高)。
介绍三个我们常见到的：
ALL：(Full Table Scan)MySQL 将遍历全表以找到匹配的资料，常说的全表扫描。
Index：(Full Index Scan)Index 与 ALL 区别为 Index 型别只遍历索引树。
Range：只检索给定範围的列，使用一个索引来选择列。key
key 显示了 SQL 实际使用索引，通常是 possible_keys 中的索引之一，MySQL 优化器一般会透过计算扫描列数来选择更适合的索引，如果没有选择索引，则返回 NULL。
当然，MySQL 优化器存在选择索引错误的情况，可以透过修改 SQL 强制MySQL“使用或忽视某个索引”：
强制使用一个索引：FORCE INDEX (index_name)、USE INDEX (index_name)。
强制忽略一个索引：IGNORE INDEX (index_name)。rows
rows 是 MySQL 估计为了找到所需的资料而要读取(扫描)的列数，可能不精确。Extra
显示一些额外资讯，很重要。
Using index：查询的栏位被索引覆盖，并且 where 筛选条件是索引的是前导栏位，Extra 中为 Using index。意味着透过索引查询就能直接找到符合条件的资料，无须回表。
注：前导栏位一般指联合索引中的第一个栏位或前几个栏位，以及单栏位索引的情况。这里为了方便理解我统称为前导栏位。
Using where：说明 MySQL 伺服器将在储存引擎检索列后再进列过滤，即没有用到索引，回表查询。
可能的原因：查询的栏位未被索引覆盖。where 筛选条件非索引的前导栏位或无法正确使用到索引。

Using temporary：这意味着 MySQL 在对查询结果排序时会使用一个临时表。
Using filesort：说明 MySQL 会对结果使用一个外部索引排序，而不是按索引次序从表里读取列。
Using index condition：查询的栏位不全在索引中，where 条件中是一个前导栏位的範围。
Using where;Using index：查询的栏位被索引覆盖，并且 where 筛选条件是索引栏位之一，但不是索引的前导栏位或出现了其他影响直接使用索引的情况(如存在範围筛选条件等)，Extra 中为 Using where;Using index，意味着无法直接透过索引查询来查询到符合条件的资料，影响并不大。

一些慢查询改善经验分享

改善 LIMIT 分页
在系统中需要分页的操作通常会使用 limit 加上偏移量的方法实现，同时加上合适的 order by 子句。
如果有对应的索引，通常效率会不错，否则 MySQL 需要做大量的档案排序操作。
一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是 limit 1000000，10 这样的查询。
这是 MySQL 需要查询 1000000 条然后只返回最后 10 条，前面的 1000000 条记录都将被捨弃，这样的代价很高，会造成慢查询。
改善此类查询的一个最简单的方法是儘可能的使用索引覆盖扫描，而不是查询所有的栏位。
然后根据需要做一次关联操作再返回所需的栏位。对于偏移量很大的时候这样做的效率会得到很大提升。
对于下面的查询：

- 执行耗时：1.379s SELECT * from vio_basic_domain_info LIMIT 1000000,10;

Explain 分析结果：

mysql> EXPLAIN SELECT * from vio_basic_domain_info LIMIT 1000000,10; +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+ | id | select_type | table                 | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+ |  1 | SIMPLE      | vio_basic_domain_info | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1377809 |   100.00 | NULL  | +----+-------------+-----------------------+------------+------+---------------+------+---------+------+---------+----------+-------+ 1 row in set, 1 warning (0.00 sec)

该语句存在的最大问题在于 limit M，N 中偏移量 M 太大，导致每次查询都要先从整个表中找到满足条件的前 M 条记录，之后捨弃这 M 条记录并从第 M+1 条记录开始再依次找到 N 条满足条件的记录。
如果表非常大，且筛选栏位没有合适的索引，且 M 特别大那么这样的代价是非常高的。
那么如果我们下一次的查询能从前一次查询结束后标记的位置开始查询，找到满足条件的 10 条记录，并记下下一次查询应该开始的位置，以便于下一次查询能直接从该位置开始。
这样就不必每次查询都先从整个表中先找到满足条件的前 M 条记录，捨弃掉，再从 M+1 开始再找到 10 条满足条件的记录了。
处理分页慢查询的方式一般有以下几种：
思路一：构造覆盖索引
透过修改 SQL，使用上覆盖索引，比如我需要只查询表中的 app_name、createTime 等少量栏位，那么我秩序在 app_name、createTime 栏位设定联合索引，即可实现覆盖索引，无需全表扫描。
适用于查询栏位较少的场景，查询栏位数过多的不推荐，耗时：0.390s。

mysql> EXPLAIN SELECT app_name,createTime from vio_basic_domain_info LIMIT 1000000,10; +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+ | id | select_type | table | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+ |  1 | SIMPLE      | vio_basic_domain_info | NULL       | index | NULL          | idx_app_name | 515     | NULL | 1377809 |   100.00 | Using index | +----+-------------+-----------------------+------------+-------+---------------+--------------+---------+------+---------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)

思路二：优化 offset
无法用上覆盖索引，那么重点是想办法快速过滤掉前 100w 笔资料。我们可以利用自增主键有序的条件，先查询出第 1000001 笔资料的 id 值，再往后查 10 笔资料。
适用于主键 id 自增的场景，耗时：0.471s。

SELECT * from vio_basic_domain_info whereid >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10;

原理：先基于索引查询出第 1000001 笔资料对应的主键 id 的值，然后直接透过该 id 的值直接查询该 id 后面的 10 笔资料。
下方 EXPLAIN 分析结果中大家可以看到这条 SQL 的两步执行流程：

mysql> EXPLAIN SELECT * from vio_basic_domain_info where id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10; +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ | id | select_type | table                 | partitions | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra       | +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ |  1 | PRIMARY     | vio_basic_domain_info | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |      10 |   100.00 | Using where | |  2 | SUBQUERY    | vio_basic_domain_info | NULL       | index | NULL          | PRIMARY | 8       | NULL | 1000001 |   100.00 | Using index | +----+-------------+-----------------------+------------+-------+---------------+---------+---------+------+---------+----------+-------------+ 2 rows in set, 1 warning (0.40 sec)

方法三：延迟关联
耗时：0.439s，延迟关联适用于数量级较大的表。
SQL 如下：

SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id);

这里我们利用到了覆盖索引+延迟关联查询，相当于先只查询 id 栏位，利用覆盖索引快速查到该页的 10 笔资料 id，然后再把返回的 10 笔 id 拿到表中透过主键索引二次查询。(表资料增速快的情况对该方法影响较小)

mysql> EXPLAIN SELECT * from vio_basic_domain_info inner join (select id from vio_basic_domain_info order by id limit 1000000,10) as myNew using(id); +----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+ | id | select_type | table                 | partitions | type   | possible_keys | key     | key_len | ref      | rows    | filtered | Extra       | +----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+ |  1 | PRIMARY     | <derived2>            | NULL       | ALL    | NULL          | NULL    | NULL    | NULL     | 1000010 |   100.00 | NULL        | |  1 | PRIMARY     | vio_basic_domain_info | NULL       | eq_ref | PRIMARY       | PRIMARY | 8       | myNew.id |       1 |   100.00 | NULL        | |  2 | DERIVED     | vio_basic_domain_info | NULL       | index  | NULL          | PRIMARY | 8       | NULL     | 1000010 |   100.00 | Using index | +----+-------------+-----------------------+------------+--------+---------------+---------+---------+----------+---------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec)

排查索引没起作用的情况
①模糊查询儘量避免用万用字元’%’开头，会导致资料库引擎放弃索引进行全表扫描
如下：

SELECT * FROM t WHERE username LIKE '%陈%'

改善方式：儘量在栏位后面使用模糊查询。如下：

SELECT * FROM t WHERE username LIKE '陈%'

如果需求是要在前面使用模糊查询：
使用 MySQL 内建函式 INSTR(str，substr)来匹配，作用类似于 Java 中的 indexOf()，查询字串出现的角标位置。
使用 FullText 全文索引，用 match against 检索。
资料量较大的情况，建议引用 ElasticSearch、Solr，亿级资料量检索速度秒级。
当表资料量较少(几千笔那种)，就不必做的太複杂了，直接用 like ‘%xx%’。
②儘量避免使用 not in，会导致引擎走全表扫描。建议用 not exists 代替
如下：

-- 不走索引SELECT * FROM t WHERE name not IN ('提莫','队长'); -- 走索引 select * from t as t1 where not exists (select * from t as t2 where name IN ('提莫','队长') and t1.id = t2.id);

③儘量避免使用 or，会导致资料库引擎放弃索引进行全表扫描
如下：

SELECT * FROM t WHERE id = 1 OR id = 3

改善方式：可以用 union 代替 or。如下：

SELECT * FROM t WHERE id = 1    UNION SELECT * FROM t WHERE id = 3

④儘量避免进行 null 值的判断，会导致资料库引擎放弃索引进行全表扫描
如下：

SELECT * FROM t WHERE score IS NULL

改善方式：可以给栏位新增预设值 0，对 0 值进行判断。如下：

SELECT * FROM t WHERE score = 0

⑤儘量避免在 where 条件中等号的左侧进行表示式、函式操作，会导致资料库引擎放弃索引进行全表扫描
可以将表示式、函式操作移动到等号右侧。如下：

-- 全表扫描 SELECT * FROM T WHERE score/10 = 9 -- 走索引 SELECT * FROM T WHERE score = 10*9

⑥当资料量大时，避免使用 where 1=1 的条件。通常为了方便拼装查询条件，我们会预设使用该条件，资料库引擎会放弃索引进行全表扫描
如下：

SELECT username, age, sex FROM T WHERE 1=1

改善方式：用程式码拼装 SQL 时进行判断，没 where 条件就去掉 where，有 where 条件就加 and。
⑦查询条件不能用 <> 或者 !=
使用索引栏位作为条件进行查询时，需要避免使用<>或者!=等判断条件。
如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此栏位上建立索引，改由查询条件中其他索引栏位代替。
⑧where 条件仅包含複合索引非前导栏位
如：複合(联合)索引包含 key_part1，key_part2，key_part3 三栏，但 SQL 语句没有包含索引前置栏位”key_part1"，按照 MySQL 联合索引的最左匹配原则，不会走联合索引。

-- 不走索引 select col1 from table where key_part2=1 and key_part3=2 -- 走索引 select col1 from table where key_part1 =1 and key_part2=1 and key_part3=2

⑨隐式型别转换造成不使用索引
如下 SQL 语句由于索引对栏位型别为 varchar，但给定的值为数值，涉及隐式型别转换，造成不能正确走索引。
select col1 from table where col_varchar=123;

结语
好了，透过这篇文章，希望你 Get 到了一些分析 MySQL 慢查询的方法和心得，如果你觉得这篇文章不错，欢迎分享给朋友或同事。
原文连结： 什么？我写的一条SQL让公司网站瘫痪了…SQL慢查询改善方案