浅谈MySQL慢查询 | 滚~韬的博客

慢查询是什么

MySQL的慢查询，全名是慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中响应时间超过阀值的语句。

具体环境中，运行时间超过long_query_time值的SQL语句，则会被记录到慢查询日志中。

long_query_time的默认值为10，意思是记录运行10秒以上的语句。

默认情况下，MySQL数据库并不启动慢查询日志，需要手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

慢查询日志支持将日志记录写入文件和数据库表。

慢查询配置

mysql并不启动慢查询日志，需要我们手动开启

1、输入命令开启慢查询（临时），在MySQL服务重启后会自动关闭；

2、配置my.cnf（windows是my.ini）系统文件开启，修改配置文件是持久化开启慢查询的方式

命令开启
查询慢查询是否开启
1
show variables like '%slow_query_log%';
开启慢查询命令
1
set global slow_query_log='ON'; 
指定记录慢查询日志SQL执行时间得阈值（long_query_time 单位：秒，默认10秒）
1
set global long_query_time=1; 
查询 “慢查询日志文件存放位置”
1
show variables like '%slow_query_log_file%';
配置文件开启
1
2
3
4
5
6
# 开启慢查询功能
slow_query_log=ON
# 指定记录慢查询日志SQL执行时间得阈值
long_query_time=1
# 选填，默认数据文件路径
# slow_query_log_file=/var/lib/mysql/localhost-slow.log

慢查询经验

LIMIT分页

优化LIMIT分页
在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。

一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是limit 1000000,10这样的查询，这是mysql需要查询1000000条然后只返回最后10条，前面的1000000条记录都将被舍弃，这样的代价很高，会造成慢查询。

优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

对于下面的查询：
1
2
-- 执行耗时：1.379s
SELECT * from vio_basic_domain_info LIMIT 1000000,10;
该语句存在的最大问题在于limit M,N中偏移量M太大，导致每次查询都要先从整个表中找到满足条件的前M条记录，之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。

那么如果我们下一次的查询能从前一次查询结束后标记的位置开始查找，找到满足条件的10条记录，并记下下一次查询应该开始的位置，以便于下一次查询能直接从该位置开始，这样就不必每次查询都先从整个表中先找到满足条件的前M条记录，舍弃掉，再从M+1开始再找到10条满足条件的记录了。

思路一：构造覆盖索引
通过修改SQL，使用上覆盖索引，比如我需要只查询表中的app_name、createTime等少量字段，那么我秩序在app_name、createTime字段设置联合索引，即可实现覆盖索引，无需全表扫描。适用于查询列较少的场景，查询列数过多的不推荐。

耗时：0.390s
1
SELECT app_name,createTime from vio_basic_domain_info LIMIT 1000000,10;
思路二：优化offset
无法用上覆盖索引，那么重点是想办法快速过滤掉前100w条数据。我们可以利用自增主键有序的条件，先查询出第1000001条数据的id值，再往后查10行；适用于主键id自增的场景。
耗时：0.471s
1
2
3
SELECT * from vio_basic_domain_info where 
  id >=(SELECT id from vio_basic_domain_info ORDER BY id limit 1000000,1) limit 10;
方法三：“延迟关联”
耗时：0.439s
延迟关联适用于数量级较大的表，SQL如下；
1
2
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拿到表中通过主键索引二次查询。（表数据增速快的情况对该方法影响较小。）

索引没起作用

1.模糊查询尽量避免用通配符’%’开头，会导致数据库引擎放弃索引进行全表扫描。如下：
1
2
SELECT * FROM t WHERE username LIKE '%MIKE%' #不推荐
SELECT * FROM t WHERE username LIKE 'MIKE%' #推荐
如果需求是要在前面使用模糊查询，

使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置。
使用FullText全文索引，用match against 检索
数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级
当表数据量较少（几千条儿那种），别整花里胡哨的，直接用like ‘%xx%’。
但不得不说，MySQL模糊匹配大字段是硬伤，毕竟保证事务的ACID特性耗费了太多性能，因此，如果实际场景中有类似业务需求，建议果断更换大数据存储引擎如ElasticSearch、Hbase等。

2.尽量避免使用 not in，会导致引擎走全表扫描。建议用 not exists 代替，如下：
1
2
3
4
5
-- 不走索引
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);
3.尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下：
1
2
3
4
5
6
7
SELECT * FROM t WHERE id = 1 OR id = 3


优化方式：可以用union代替or。如下：
SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3
4.尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：
1
2
3
4
5
SELECT * FROM t WHERE score IS NULL

优化方式：可以给字段添加默认值0，对0值进行判断。如下：

SELECT * FROM t WHERE score = 0
5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。可以将表达式、函数操作移动到等号右侧。如下：
1
2
3
4
-- 全表扫描
SELECT * FROM T WHERE score/10 = 9
-- 走索引
SELECT * FROM T WHERE score = 10*9
6.当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：
1
2
SELECT username, age, sex FROM T WHERE 1=1
优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。
7.查询条件不要用 <> 或者 !=
使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

8.where条件仅包含复合索引非前导列
如：复合（联合）索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列”key_part1”，按照MySQL联合索引的最左匹配原则，不会走联合索引。
1
2
3
4
-- 不走索引
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
9.隐式类型转换造成不使用索引
如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。
1
select col1 from table where col_varchar=123; 

面试题

你对MySQL的慢查询优化有了解吗

标准回答

慢查询优化的前提是定位到响应慢的SQL，这可以通过启用慢查询日志来实现。默认情况下，MySQL并不启用慢查询日志，我们需要手动开启这个参数。通过日志定位到慢查询的SQL之后，我们可以使用EXPLAIN语句来分析这个SQL，进而发现问题所在。导致慢查询的原因有很多，下面列举几种常见的原因，以及对应的解决方案：
向数据库请求了多余的数据：

很多时候，我们的SQL返回的结果会超出我们的需要，例如实际上它返回了更多的行，而我们只要其中的一部分。又或者我们要求返回所有的列，实际上却只有其中少数的列。对于这类问题，我们可以通过LIMIT控制返回的行数，尽量不用SELECT *避免查询到过多的列。

SQL复杂导致无法利用缓存：

处于业务的需要，我们经常会写出比较复杂的SQL，这自然包括复杂的关联查询。由于复杂SQL返回的结果涉及多张表、多个条件、甚至各种函数，这样的SQL每次返回的结果势必不同，所以很难利用到数据库的缓存。如果我们将复杂SQL进行拆分，变成若干简单的SQL，那么其中有些SQL由于条件不变，就可以利用到数据库的缓存了，从而让查询效率得以提升。
没有选择正确的索引：

我们都知道，创建索引是提高查询效率的一个常用手段，事实上我们也经常会这样做。但是，很多时候我们创建了索引，通过EXPLAIN查看会发现并没有走这个索引，最终导致SQL执行变慢。所以，不是把索引创建出来就算完成任务，还要分析索引的选择性，根据业务条件不断的优化索引，从而增加索引的命中率。

加分回答

除上述优化的方向之外，SQL中还有很多地方都有优化的空间，例如COUNT()、关联查询、子查询、GROUP BY、LIMIT、UNION等。总体来说，不同的情况要区别对待，但所有优化的背后是基于慢查询日志的定位。另外，为了能够发现问题的本质，还需要对MySQL执行查询的过程有所了解：

客户端发送一条查询SQL给服务器。

服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。

服务器进行SQL解析和预处理，再由优化器生成对应的执行计划。

服务器根据优化器生成的执行计划，调用存储引擎的API来执行查询。

将结果返回给客户端。

延伸阅读
B+树索引是基于B+树构建出来的有序结构，只有利用上它的有序性才能提高查询的效率。若不满足有序性这个前提，则在这个索引中的查询是离散的，其效率反而更低。查询优化器对索引的选择性，被称为最左前缀原则。

假设有如下一张表：

复制代码
1
2
3
4
5
6
CREATE TABLE t (
  a VARCHAR(100),
  b VARCHAR(100),
  c VARCHAR(100),
  KEY idx_union(a,b,c)
) ENGINE=INNODB;
假设idx_union的叶子节点数据如下：

复制代码
1
(1,3,2), (1,3,3), (1,3,9), (1,3,9), (1,7,4), (1,7,8), (2,1,5), (2,1,7), (2,5,1), (2,5,6)
该索引的选择性示例如下：

复制代码
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 匹配左前缀
select * from T where a='';    -- Y
select * from T where b='';    -- N
-- 匹配列前缀
select * from T where a like 'x%';    -- Y
select * from T where a like '%x';    -- N
select * from T where b like 'x%';    -- N
-- 全值匹配
select * from T where a='' and b='' and c='';    -- Y
select * from T where c='' and b='' and a='';    -- Y
-- 匹配范围值
select * from T where a between '' and '';    -- Y
select * from T where b between '' and '';    -- N
-- 全值匹配 + 范围匹配
select * from T where a='' and b between '' and '';    -- Y
select * from T where b='' and c between '' and '';    -- N
select * from T where a between '' and '' and b='';    -- N