慢查询 SQL 调优

调优第一步，开启 MySQL 慢查询日志，找出慢查询 SQL：

// 开启慢查询日志
set global slow_query_log = ON;
// 设置慢查询超时时间(单位秒)
set global long_query_time = 'xxx';
// 设置慢查询日志名称，默认名称：hostname-slow.log
set global log-slow-queries = 'xxx';
// 开启记录没有使用索引的 SQL
set global log_querise_not_using_indexes = ON;

接下来就可以分析慢查询日志了，这里可以直接通过 explain 关键字获取 sql 执行计划：

explain select x from table_name where 1 = 1;

执行结果如下图所示，这里我们逐个分析：

为了方便测试，我创建几张表用于验证，初始化 sql 如下：

create table `student` (
	`id` bigint(20) NOT NULL auto_increment,
	`name` varchar(20) NOT NULL,
	`class_id` bigint(20) NOT null,
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

create table `class` (
	`id` bigint(20) NOT NULL auto_increment,
	`name` varchar(20) NOT NULL,
	`is_win` bigint(20) NOT null,  # 是否优胜班级
	PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

insert into class values (1, '一班', 0);
insert into class values (2, '二班', 0);
insert into class values (3, '三班', 1);

insert into student values (1, '张三', 1);
insert into student values (2, '李四', 2);
insert into student values (3, '王五', 3);
insert into student values (4, '马六', 1);
insert into student values (5, '小白', 2);
insert into student values (6, '小红', 3);

id：select 查询的序列号，表示 select 查询子句或操作表的顺序，有三种情况：

• id 相同：从上向下加载表
• id 不同：id 越大，优先级越高，越先执行
• id 有相同也有不同：id 相同的可以看为一组，组内按从上到下顺序加载，组外规则和 id 不同类似

id 相同常出现于级联查询，如下所示：

// 查询优胜班级学生
explain select * from student left join class on student.class_id = class.id where class.is_win = 1;

从结果可以看出，先加载 class 表，找出优胜班级，再加载 student 表，两次加载过程优先级相同。

上面 sql 实际可以改一下，改成优先级不同的场景：

explain select * from student where class_id = (select id from class where is_win = 1);

从结果可以看出，此时先执行内查询，再执行外层查询。如果把上面 sql 中的 = 换为 in，id 又会相同，优先级又变为相同，关于这点我们在后面介绍。使用 = 需要注意保证内查询只能查到一个结果，否则会报错

select_type 表示查询类型，结果有：SIMPLE、PRIMARY、SUBQUERY、UNION、UNION RESULT、DEPENDENT UNION、DEPENDENT SUBQUERY、DERIVED

• SIMPLE：简单 SQL 语句，没有用到级联、UNION 或子查询
• PRIMARY：多表查询中外层 SELECT 或 UNION 操作中外层 SELECT
• SUBQUERY：多表查询中子查询
• UNION：UNION 操作中内层 SELECT
• UNION RESULT：UNION 的结果
• DEPENDENT UNION：UNION 中的第二个或随后的 select 查询，依赖于外部查询的结果集
• DEPENDENT SUBQUERY：UNION 子查询
• DERIVED：衍生表

下面我分别列出这几种情况：

explain select * from student;

explain select * from student where class_id = (select id from class where is_win = 1);

explain select * from student where id = 1 union select * from student where id = 2;

table 表示用到的表，Union 操作 id 为 NULL，table 是执行 Union 操作用到表的并集

partitions 表示使用的哪个分区，需要结合表分区才可以看到

type 是 SQL 调优的关键，通过它可以确定这条 select 的类型，结果包含以下几种：

• const：表中最多只有一个匹配行，常用于主键或唯一索引
• eq_ref：唯一性索引扫描，对于每一个来自前表的记录，从该表中读取唯一一行
• ref：非唯一性索引扫描，对于每一个来自前表的记录，找出所有匹配的行
• ref_or_null：类似 ref，可以搜索包含 null 的行
• index_merge：用到同一张表的多个索引的情况
• range：索引范围查询
• index：通过索引全表扫描
• ALL：全表所有，扫描物理表

从上到下，效率依次降低。当 type 为 ALL 时，一般就需要考虑优化了，因为此时没有加索引或索引失效。下面我挑几个常见类型验证：

// const
explain select * from student where id = 1;
// range
explain select * from student where id > 3;
// index
explain select id from student;
// ALL
explain select * from student where class_id = 1;

possible_keys 显示可能应用到该表上的索引，一个或多个，但实际不一定被查询使用

key：实际使用的索引，如果为 NULL 表示没有用到索引

key_len：索引字段最大可能长度，一般越小越好

ref：显示哪一列索引被使用了

rows：需要扫描的行数

filtered：返回结果行数占总扫描行数的百分比，越大说明索引命中率越高，整体性能越好

extra：额外信息说明，可忽略

至此关于 explain 执行计划所有属性都说明完毕

---

根据执行计划，我们就可以大致推断出 sql 执行慢的主要原因，下面我列出几种常见情况和解决办法：

1、没有创建索引或索引失效

这里首先说一下如何优雅的创建索引：

1. 表的主外键必须创建索引
2. 经常出现在 where 子句中的字段适合创建索引
3. 经常和别人表连接的字段适合创建索引
4. 索引适合创建在小字段上，大的文本字段不要创建索引
5. 不建议使用复合索引，使用单字段索引代替更好
6. 频繁执行增删改的表不建议创建索引
7. 数据量大的表应该创建有索引
8. 删除无效的索引，也会提高 sql 的性能，减少系统负载
9. 对于 index_a(a) 、index_a_b_c（a, b, c) 2个索引来说，idx_a 索引是没有意义的，可以直接删除

下面再说一下索引失效的场景：

1. or 语句可能导致索引失效，需判断 or 左右是否使用相同的索引
2. 负向查询可能导致索引失效，如 NOT、!=、<>、NOT IN、NOT LIKE 等
3. 条件判断含有运算符，如使用 +、-、*、/ 等
4. like ‘%xxx’ 当使用 like，并且以 % 作为第一个字符时，索引失效
5. 对于多列索引，必须满足最左匹配原则，否则索引失效

对此就可以根据实际情况创建索引或调整查询语句

2、表体量太大，查询效率低

这里表体量过大分两种情况：表本身字段过多或者频繁的联表查询导致拼装出的临时表过大。

• 对于表本身字段过多，可以采用拆分表的方式解决：分离使用频率低的字段到新表
• 对于经常需要联合查询的表，建立中间表提高查询效率

3、分解关联查询

对于多表的联合查询，合理的拆分可能效率过高，举个例子：

select * from a 
	left join b on a.id = b.a_id 
	left join c on b.id = c.b_id 
	where c.b_id = 10;
拆分为：
select * from c where c.b_id = 10;
select * from b where b.id = xx; // 上述查询结果的 b_id 集合
select * from a where a.id = xxx; // 上述查询结果的 a_id 集合

这里我们大概算一下，假如 a、b、c 三表都包含十万条数据，采用联表查询复杂度呈指数倍增加，此时分三次查询可能效率更高。

4、关于 in 操作优化

前文我们提到，对于下面这条 sql 语句，mysql 判断优先级是相同的：

explain select * from student where class_id in (select id from class where is_win = 1);

实际上这条 sql 会被转换为如下格式：

select * from student where exists(select * from class where student.class_id = class.id and class.is_win = 1);

exists 执行原理：循环 student 表中每一条记录，去 class 表中查询判断是否存在，存在就返回。

由于 exists 循环判断的是 student 表，因此 student 表的索引肯定用不到，只能根据 class 表中索引去判断。此时我们不能判断遍历 class 表，循环判断 student 表使用索引效率会不会更高。为了能够用到 student 表中索引，我们可以将 sql 修改为如下格式：

select * from student inner join class on student.class_id = class.id where class.is_win = 1

这里只能使用 inner join，因为 left join 和 right join 都包含顺序，和上述 exists 一样的原理，都只能判断一种情况，使用 Inner join 可以双向判断，既判断以 student 去 class 查询的效率，也判断使用 class 去 student 查询的效率，具体使用哪种情况，由 MySQL 自己判断，我们可以通过 explain 去查看最终用到哪列索引。

5、limit 分页优化

对于分页操作 limit a,b 常常需要先跳过 a 条查到的数据，再查询 b 条。当 a 值特别大时，扫描并抛弃的数据过多，效率很差。如 limit 10000,1 就需要扫描 10001 条数据，最后抛弃 10000 条返回第 10001 条数据。优化这类 sql 常用的方法有以下三种：

select id,name from student limit 10000,1

• 被查询字段加索引：直接在 name 字段加索引，添加基于该字段的判断语句
• 根据 id 字段查询：通过 id 字段跳过大量的应该抛弃的数据，优化后的 sql 如下：

  selct id,name from student where id >=(select id from student limit 10000,1) limit 1;
  

• 关联延迟查询：让 mysql 扫描尽可能少的页面，获取到需要的记录后再关联原数据，查到需要的列。优化后的sql 如下：

  select s1.id,s1.name from student as s1 inner join (select id from student limit 10000,1) as s2 on s1.id = s2.id;