一条慢查询会造成什么后果？之前我一直觉得不就是返回数据会慢一些么，用户体验变差？其实远远不止，我经历过几次线上事故，有一次就是由一条 SQL 慢查询导致的。
那次是一条 SQL 查询耗时达到 2-3 秒，没有命中索引，导致全表扫描，由于是高频查询，并发一起来很快就把 DB 线程池打满了，导致大量查询请求堆积，DB 服务器 CPU 长时间 100%+，大量请求 timeout...
最终系统崩溃，老板登场！可见，团队如果对慢查询不引起足够的重视，风险是很大的。
经过那次事故我们老板就说了：谁的代码再出现类似事故，开发和部门领导一起走人，吓得一大堆领导心发慌，赶紧招了两位 DBA 同事。
慢查询，顾名思义，执行很慢的查询。有多慢？超过 long_query_time 参数设定的时间阈值（默认 10s），就被认为是慢的，是需要优化的。慢查询被记录在慢查询日志里。
慢查询日志默认是不开启的，如果你需要优化 SQL 语句，就可以开启这个功能，它可以让你很容易地知道哪些语句是需要优化的（想想一个 SQL 要 10s 就可怕）。好了，下面我们就一起来看看怎么处理慢查询。
MySQL 支持通过以下方式开启慢查询：
方式一：通过命令开启慢查询
步骤 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：
方式二：通过配置 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
重启数据库后即持久化开启慢查询，查询验证如下：
mysql> show variables like '%_query_%';
+------------------------------+-----------------------------------+
| Variable_name | Value |
+------------------------------+-----------------------------------+
| have_query_cache | YES |
| long_query_time | 1.000000 |
| slow_query_log | ON |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+------------------------------+-----------------------------------+
6 rows in set (0.01 sec)
如上图，是执行时间超过 1 秒的 SQL 语句（测试）：
分析 MySQL 慢查询日志，利用 Explain 关键字可以模拟优化器执行 SQL 查询语句，来分析 SQL 慢查询语句。
下面我们的测试表是一张 137w 数据的 app 信息表，我们来举例分析一下。
SQL 示例如下：
-- 1.185s
SELECT * from vio_basic_domain_info where app_name like '%翻译%' ;
这是一条普通的模糊查询语句，查询耗时：1.185s，查到了 148 条数据。
我们用 Explain 分析结果如下表，根据表信息可知：该 SQL 没有用到字段 app_name 上的索引，查询类型是全表扫描，扫描行数 137w。
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：执行情况的描述和说明。
以上标星的几类是我们优化慢查询时常用到的。
1）type
对表访问方式，表示 MySQL 在表中找到所需行的方式，又称“访问类型”。
存在的类型有：ALL、index、range、ref、eq_ref、const、system、NULL（从左到右，性能从低到高）。
介绍三个咱们天天见到的：
ALL：（Full Table Scan）MySQL 将遍历全表以找到匹配的行，常说的全表扫描；
Index：（Full Index Scan）Index 与 ALL 区别为 Index 类型只遍历索引树；
Range：只检索给定范围的行，使用一个索引来选择行。
2）key
key 列显示了 SQL 实际使用索引，通常是 possible_keys 列中的索引之一，MySQL 优化器一般会通过计算扫描行数来选择更适合的索引，如果没有选择索引，则返回 NULL。
当然，MySQL 优化器存在选择索引错误的情况，可以通过修改 SQL 强制MySQL“使用或忽视某个索引”：
3）rows
rows 是 MySQL 估计为了找到所需的行而要读取（扫描）的行数，可能不精确。
4）Extra
这一列显示一些额外信息，很重要。
Using index：查询的列被索引覆盖，并且 where 筛选条件是索引的是前导列，Extra 中为 Using index。意味着通过索引查找就能直接找到符合条件的数据，无须回表。
注：前导列一般指联合索引中的第一列或“前几列”，以及单列索引的情况；这里为了方便理解我统称为前导列。
Using where：说明 MySQL 服务器将在存储引擎检索行后再进行过滤；即没有用到索引，回表查询。
可能的原因：
Using temporary：这意味着 MySQL 在对查询结果排序时会使用一个临时表。
Using filesort：说明 MySQL 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。
Using index condition：查询的列不全在索引中，where 条件中是一个前导列的范围。
Using where；Using index：查询的列被索引覆盖，并且 where 筛选条件是索引列之一，但不是索引的前导列或出现了其他影响直接使用索引的情况（如存在范围筛选条件等），Extra 中为 Using where；Using index，意味着无法直接通过索引查找来查询到符合条件的数据，影响并不大。
在系统中需要分页的操作通常会使用 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 where
id >=(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 | | 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 |
