SQL 优化实例：初阶必看，巧用 explain 分析执行计划
@[toc]
---
案例来源：https://sourl.cn/P2VEq6，一个很好的 SQL 优化的实战案例，很有参考价值，在原文基础上我做了点行文和格式上的润色，让大伙读起来更顺畅。
## 场景
用的数据库是 MySQL5.6，下面简单的介绍下场景：
- 课程表（数据 100 条）
```sql
create table Course(
c_id int PRIMARY KEY,
name varchar(10)
)
```
- 学生表（数据 7w 条）
```sql
create table Student(
id int PRIMARY KEY,
name varchar(10)
)
```
- 学生成绩表（数据 70w 条）
```sql
CREATE table SC (
sc_id int PRIMARY KEY,
s_id int,
c_id int,
score int
)
```
需求是查找语文考 100分 的考生，很自然的可以写出下述查询语句：
```sql
select s.*
from Student s
where s.s_id in (
select s_id from SC sc
where sc.c_id = 0 and sc.score = 100
)
```
但是运行之后执行时间达到了 `30248.271s`，这太离谱了，无法接受
## 加索引
立马 `explain` 查看查询计划
可以发现，**`type` 全是 `ALL`，意味着没有用到索引，那么首先想到的就是为 `where` 条件中的字段建立索引**。
先给`sc` 表的 `c_id` 和 `score` 建个索引：
```sql
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
```
再次执行上述查询语句，时间为: `1.054s`
**只是简单加了两个索引，查询速度就快了 3w 多倍**，可见索引的必要性！
不过 1s 的时间还是有点长了，还能进行优化吗？
## 查看 select 的执行效果
按照我们的很自然的想法，对于这条 SQL：
```sql
select s.*
from Student s
where s.s_id in (
select s_id from SC sc
where sc.c_id = 0 and sc.score = 100
)
```
应该是先执行内层查询：
```sql
select s_id from SC sc where sc.c_id = 0 and sc.score = 100
```
这条语句耗时：0.001s
得到如下结果：
然后拿着内层查询的结果，去执行外层查询：
```sql
select s.* from Student s where s.s_id in(7, 29, 5000)
```
耗时：0.001s
那按照这个想法，远远是不需要 1s 的，那为啥 MySQL 执行这条语句要用 1s 呢？
因为 **MySQL 底层是会做查询优化的**，并不是完全按照你想的顺序和方式来执行，仔细看执行计划：
> 由上至下：在执行计划中一般含有多个节点，相同级别(或并列)的节点，靠上的优先执行，靠下的后执行
出现了 `DEPENDENT SUBQUERY`（嵌套子查询）！！！
我们可以利用 `explain extended` 在原本 explain 的基础上获得额外的一些查询优化的信息：
```sql
SELECT
`YSB`.`s`.`s_id` AS `s_id`,
`YSB`.`s`.`name` AS `name`
FROM
`YSB`.`Student` `s`
WHERE
< in_optimizer > (
`YSB`.`s`.`s_id` ,< EXISTS > (
SELECT
1
FROM
`YSB`.`SC` `sc`
WHERE
(
(`YSB`.`sc`.`c_id` = 0)
AND (`YSB`.`sc`.`score` = 100)
AND (
< CACHE > (`YSB`.`s`.`s_id`) = `YSB`.`sc`.`s_id`
)
)
)
)
```
可以看到，**MySQL 是将我们原本这条 SQL 优化成了 `exists` 子句，并出现了嵌套子查询**
也就是说，MySQL 是先执行外层查询，再执行内层的查询，这样就要循环 70007 * 8 次。
## 改用连接查询
既然子查询不好使，那我们尝试下改成连接（join）
```sql
SELECT s.* from Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=0 and sc.score=100
```
这里为了重新分析连接查询的情况，先暂时删除索引 `sc_c_id_index`，`sc_score_index`
执行时间是：`0.057s`
效率有所提高，看看执行计划：
这里有连表的情况出现，我猜想是不是要给 sc 表的 `s_id` 建立个索引
```sql
CREATE index sc_s_id_index on SC(s_id);
```
再执行下连接查询试试：
时间: 1.076s，竟然时间还变长了，什么原因？查看执行计划：
具体的 select 查询优化信息如下：
```sql
SELECT
`YSB`.`s`.`s_id` AS `s_id`,
`YSB`.`s`.`name` AS `name`
FROM
`YSB`.`Student` `s`
JOIN `YSB`.`SC` `sc`
WHERE
(
(
`YSB`.`sc`.`s_id` = `YSB`.`s`.`s_id`
)
AND (`YSB`.`sc`.`score` = 100)
AND (`YSB`.`sc`.`c_id` = 0)
)
```
从执行计划可以看出来是先做的连接查询，再进行的 where 条件过滤
回到前面的执行计划：
这里是先做的 where 条件过滤，再做连表，执行计划还不是固定的，那么我们先看下标准的 SQL 执行顺序：
正常情况下是先 `join` 再进行 `where` 过滤，但是我们这里的情况，如果先 `join`，将会有 70w 条数据发送 join 操作，因此先执行 `where` 过滤是明智方案，**现在为了排除 MySQL 的查询优化，我自己写一条优化后的 SQL**：
```sql
SELECT
s.*
FROM
(
SELECT
*
FROM
SC sc
WHERE
sc.c_id = 0
AND sc.score = 100
) t
INNER JOIN Student s ON t.s_id = s.s_id
```
即先执行 sc 表的过滤，再进行表连接，执行时间为：0.054s，和之前没有建 s_id 索引的时间差不多
查看执行计划：
先提取 sc 再连表，这样效率就高多了，现在的问题是提取 sc 的时候出现了扫描表，那么现在可以明确需要建立相关索引：
```sql
CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);
```
执行时间为：0.001s，这个时间相当靠谱，快了 50 倍
执行计划：
我们会看到，先提取 sc，再连表，都用到了索引。
确定好了索引后，那么再来执行下一开始连接查询的 SQL：
```sql
SELECT s.* from Student s
INNER JOIN SC sc
on sc.s_id = s.s_id
where sc.c_id=0 and sc.score=100
```
执行时间 0.001s
执行计划：
MySQL 对这条语句进行了查询优化，先执行了 where 过滤，再执行连接操作，且都用到了索引。