深入理解SQL慢查询优化 – 底层原理、explain分析 和 案例实操
在数据库的日常运维和开发中，SQL查询的性能问题一直是开发者们关注的焦点。因此，深入理解SQL慢查询优化的底层原理，掌握如何优化SQL查询，对于提升系统的整体性能具有重要意义。
本文将从实现高性能查询的核心策略出发，详细剖析增量查询和索引加速的原理及其在各类基本操作中的应用，帮助读者深入理解SQL慢查询优化的底层逻辑，从而能够在实际工作中有效地进行SQL查询优化。接下来，让我们一同探讨如何实现这一目标。
首先我们需要理解实现高性能查询的核心策略：增量查询和索引加速，再结合核心策略分析每种基本操作，如where / join / order by 等。
一、核心策略 – 增量查询 & 索引加速
以下面的SQL为例进行说明
-- MySQL 自带的测试库
-- 如果没有，可以从这里下载 https://github.com/datacharmer/test_db
use employees;
SELECT *
FROM departments JOIN dept_emp ON departments.dept_no = dept_emp.dept_no
WHERE departments.dept_name = 'Marketing'
LIMIT 10;上面这条语句包括3个部分：join + where + limit
如果你是MySQL，对这样一条语句，你会如何执行？
- 最简单直接的策略
public List naiveQuery() {
// 先加载2张表的全量数据
List departmentsList = loadAllFromDisk("departments");
List deptEmpList = loadAllFromDisk("dept_emp");
// 再做通过2重for循环做join
List joinedList = new LinkedList();
for(Department department in departmentsList) {
for(DeptEmp deptEmp in deptEmpList) {
if(department.dept_no == deptEmp.dept_no) {
joinedList.add(new JoinedRow(deptEmp, department));
}
}
}
}
// 最后遍历join结果集，做where + limit
List result = new LinkedList();
for(JoinedRow joinedRow in joinedList) {
if(joinedRow["dept_name"].equals("Marketing")) {
result.add(joinedRow);
if(result.size() == 10) {
return result;
}
}
}
return result;
}
// 我们为了拿到10条结果，对两张表做了全量的join计算
// 如果每张表有10万条记录，就要计算10万 * 10万 = 100亿次，显然是非常低效的2. 增量查询策略
public List incrementalQuery() {
List result = new LinkedList();
Cursor departmentsCursor = openCursor("departments")
while(departmentsCursor.hasMoreRows()) {
// 外层表（外层循环使用的表），每次只读取1行
Department department = departmentsCursor.readNext();
// 判断where条件
if(department["dept_name"].equals("Marketing")) {
// 遍历内层表做join
Cursor deptEmpCursor = openCursor("dept_emp");
while(deptEmpCursor.hasMoreRows()){
DeptEmp deptEmp = deptEmpCursor.readNext();
if(department.dept_no == deptEmp.dept_no) {
result.add(new JoinedRow(deptEmp, department));
if(result.size() == 10) {
return result;
}
}
}
}
}
return result;
}
// 不再做全量的数据加载和计算，而是增量地做
// 只要收集到了10条满足条件的记录，就直接提前返回，节省掉不必要的计算
// 但是对外层表的每条记录，还是需要遍历内层表做join
// 如果运气好，外层表的第一条数据就可以匹配到10条满足条件的记录, 那就会很快
// 但如果运气不好，外层表要到第10001条数据才能匹配到10条满足条件的记录，那内层表就要被全表扫描10000次
// 或者满足条件的记录加起来也不到10条，增量查询又会退化成全量计算了3. 索引加速
按照顺序存储数据，查询时就能利用顺序速定位到所需要的行。
public List incrementalQueryWithIndex() {
List result = new LinkedList();
// 通过索引快速定位符合where条件的数据
Cursor departmentsCursor = searchIndex("dept_name", "Marketing");
while(departmentsCursor.hasNext()) {
Department department = departmentsCursor.readNext();
// 利用索引快速定位到所需要的行，只对这些行做join，提高join的效率
Cursor deptEmpCursor = searchIndex("dept_no", department.deptNo);
while(deptEmpCursor.hasNext()) {
DeptEmp deptEmp = deptEmpCursor.readNext();
result.add(new JoinedRow(deptEmp, department));
if(result.size() == 10) {
return result;
}
}
}
return result;
}
// 查询外层表时，利用索引快速筛选出符合where条件的记录
// 内层循环同样利用索引快速定位到匹配的行，都避免了做遍历
// 比如：同样是外层表的第10001条数据才能匹配到10条满足条件的记录，
// 我们不需要把内层表遍历10000次，而只是查10000次内层表的索引，开销会低很多
// 最差的情况下（符合条件的记录数不足10条），计算量也是 (lgm * lgn)，远好于(m * n)MySQL高性能查询的核心策略：
-
增量查询：只要收集到了足够的结果，就立刻返回，避免全量计算，减少工作量。
-
索引加速：在数据存储时做预先的排序，在查询时利用有序性快速定位到所需的行，最大发挥增量查询的优势。
二、核心策略在基本操作上的应用
SELECT *
FROM departments JOIN dept_emp ON departments.dept_no = dept_emp.dept_no
WHERE departments.dept_name = 'Marketing'
LIMIT 10;一个完整的SQL是由若干基本操作组成的，比如：上面这条语句包含join, where和limit 3个基本操作。
要理解一条完整的SQL是怎么执行的，先要理解每种基本操作是怎么执行的。
- order by
顺序是一个全局属性。如果order by的列上没有索引，就必须把整个表都加载进内存进行排序，非常耗时。如果有索引，MySQL可以通过索引直接按顺序读取行，非常高效。
-- salary列上没有索引，耗时400ms
select * from salaries order by salary limit 10;
-- emp_no列上有索引，耗时2ms
select * from salaries order by emp_no limit 10;慢查询信号 (explain)：Using filesort
- join
前面已经说过，join是通过两重循环实现的。如果内层表的join列上没有索引，那对于外层表的每一行数据，内层表都要做一次全表扫描。
如果有索引，内层表直接用索引就可以快速定位到匹配的记录，配合limit就可以做增量查询，非常高效。
-- birth_date和from_date上都没有索引，耗时2500ms
-- birth_date和from_date只要有一个有索引就够了
select * from employees join dept_emp
on employees.birth_date = dept_emp.from_date
limit 10;
-- emp_no列上有索引，耗时2ms
select * from employees join dept_emp
on employees.emp_no = dept_emp.emp_no
limit 10;慢查询信号 (explain)：
Using join buffer (hash join)
Using join buffer (block nested join)
- where
没有索引，只能扫表；有索引直接定位到满足条件的记录
-- salary列上没有索引，耗时300ms
select * from salaries where salary = 1171046 limit 10;
-- emp_no列上有索引，耗时2ms
select * from salaries where emp_no = 12000 limit 10;- group by
分组操作的实现一般有2种方案：hash和排序，MySQL用的是排序方案。
-
新建临时表；
-
扫原始数据表，并按顺序插入临时表；
这里的顺序就是按照group by的列进行排序。这样就可以保证group by列的值相同的行排在一起，方便统计。
- 在临时表上做处理，如统计（min/max/count/sum/avg）
如果group by的列，已经有索引了，那前2步的开销就省掉了。
-- emp_no上有主键索引，耗时2ms
select emp_no, max(salary) from salaries
group by emp_no limit 10;
-- from_date上没有索引，耗时700ms
select from_date, max(salary) from salaries
group by from_date limit 10;PS: 直觉上hash方案更高效，但考虑到group by以后可能还有order by等操作。排序方案通用性更好，而且有机会利用到已有的B+树索引，所以MySQL还是选择排序方案。
慢查询信号 (explain)：Using temporary
- distinct
distinct可以理解为一种简单的group by，比如：下面2条语句等价
select first_name from employees group by first_name;
select distinct first_name from employees;- max / min
如果有索引，直接取就可以；没索引就要扫全表。
-- salary上没有索引，耗时300ms
select max(salary) from salaries;
-- emp_no上有索引，耗时2ms
select max(emp_no) from salaries;- avg / sum / count
需要做全量扫描，如果有索引，可以扫索引。索引文件(仅包含索引列和主键列)相比主数据(全部列)通常要小一些，速度会稍微快一点。
如果表的列也很多，行也很多，索引会明显小于主数据文件，效果会更明显
-- 扫主数据，耗时300ms
select sum(salary) from salaries;
-- 扫索引，耗时270ms
select sum(emp_no) from salaries;- in
-- in查询，需要对给出的值做遍历对比
-- 如果当前值已经等于10005，就没必要再去和后面的值比较了
-- 循环可以提前退出
select * from salaries
where emp_no in (10005, 10006, 10007);
-- 当in出现在子查询时，提前退出循环的意义更大
-- 因为可以避免把内层表匹配的记录全部查出来
select * from salaries
where emp_no in
(select emp_no from employees);
-- MySQL把in子查询提前退出循环的优化称为semijoin
select * from salaries
where exists
(select 1 from employees
where employees.emp_no = salaries.emp_no);- not in
-- not in查询，需要对给出的值做遍历对比
-- 如果当前值已经等于10005，就没必要再去和后面的值比较了
-- 循环可以提前退出
select * from salaries
where emp_no not in (10005, 10006, 10007);
-- 当not in出现在子查询时，提前退出循环的意义更大
-- 因为可以避免把内层表匹配的记录全部查出来
select * from salaries
where emp_no not in
(select emp_no from employees);
-- MySQL把not in子查询提前退出循环的优化称为antijoin
select * from salaries
where not exists
(select 1 from employees
where employees.emp_no = salaries.emp_no);为什么MySQL能采用增量查询的优化策略？
使用场景决定优化策略——MySQL之所以能用增量策略，是因为实际业务场景允许。
- 以之前的SQL为例，如果没有limit语句，我就想要全量数据，这时候做增量查询意义就不大了。
- 在MySQL实际使用场景中，一般一次查询只会有很少的数据符合条件，可以很好地适用增量策略。如果符合条件的数据确实很多，我们会通过分页限制返回条数，使增量策略能继续适用。
- 当然有些业务场景就是需要做全量的操作。比如报表类的业务，就是要在大量数据中频繁的做全量的统计计算 这时候就不适合用MySQL这样的关系型数据库，而是选择其他更适合的技术方案，比如Map – Reduce
二、explain 工具分析 SQL
学习explain，到底是要学啥？explain本质上是一个工具，我们使用它是为了辅助理解给定SQL的底层执行策略。执行策略这块，在上两个标题中已经熟练掌握了，我们不熟悉的只是 explain 描述执行策略的方式。
学习explain，主要是熟悉适应Mysql描述执行策略的方式。
在介绍explain之前，我们先来思考一个问题：
抛开explain，你会如何描述执行策略？以下面这条SQL为例，我们试着描述一下它的执行策略。
-- 查询编号大于10005的员工信息，按工资升序排列
SELECT *
FROM
employees JOIN salaries
ON employees.emp_no = salaries.emp_no AND salaries.from_date = '1992-08-04'
WHERE
employees.emp_no > 10005
AND salaries.salary > 70000
ORDER BY
salaries.salary;
-- employees: PRIMARY KEY(emp_no)
-- salaries: PRIMARY KEY(emp_no, from_date)-
根据employees表的主键，直接把主键游标定位到emp_no > 10005的位置；
-
开始读取employees表的记录，针对每条读到的记录（employees[i]）；
-
根据employees[i].emp_no和from_date=’1992-08-04’去查salaries表；
-
这里刚好可以根据salaries表的主键(emp_no, from_date)，唯一定位到一条记录salaries[j]；
-
如果salaries[j].salary > 70000，则把JoinedRow(employees[i], salaries[j])加入join结果；
-
拿到join结果集后，根据salaries.salary排序；
explain是如何描述执行策略的？
–
这里，我们看一下同样的内容（执行策略），explain的描述方式是怎样的？
-
极简版本
explain format=tree
SELECT * FROM employees JOIN salaries
ON employees.emp_no = salaries.emp_no AND salaries.from_date = '1992-08-04'
WHERE employees.emp_no > 10005 AND salaries.salary > 70000
ORDER BY salaries.salary;
-- employees: PRIMARY KEY(emp_no)
-- salaries: PRIMARY KEY(emp_no, from_date)
-> Sort: salaries.salary
-> Stream results (cost=82354 rows=49884)
-> Nested loop inner join (cost=82354 rows=49884)
-> Filter: (employees.emp_no > 10005) (cost=29971 rows=149667)
-> Index range scan on employees using PRIMARY over (10005 < emp_no) (cost=29971 rows=149667)
-> Filter: (salaries.salary > 70000) (cost=0.25 rows=0.333)
-> Single-row index lookup on salaries using PRIMARY (emp_no=employees.emp_no, from_date=DATE'1992-08-04') (cost=0.25 rows=1)可以看到执行计划的整体结构，和我们上面说的完全一致。
MySQL官方的工具MySQL Workbench是有可视化explain功能的。
-
默认版本
SELECT * FROM employees JOIN salaries ON employees.emp_no = salaries.emp_no
AND salaries.from_date = '1992-08-04'
WHERE employees.emp_no > 10005 AND salaries.salary > 70000
ORDER BY salaries.salary;
-- employees: PRIMARY KEY(emp_no)
-- salaries: PRIMARY KEY(emp_no, from_date)
-- 分析结果如下图所示。分析结果如下图所示。
-
输出结果有2行，分别对应emplyees表和salaries表，这个也可以从table这一列看出来；
-
查询employees表时，用了主键索引
-
explain预估通过主键
emp_no > 10005会过滤出149667行记录；employees表总共有约30W行记录（30W这个数据不是通过explain看出来的，而是直接select count(1)查出来的）；这一步过滤，减少了后续需要和salaries做join的记录数，提升了性能（Using where）； -
过滤出的149667行记录，因为没有其他where条件了，所以100%都符合条件；
-
Using temporary和Using filesort其实是排序操作用到了；filesort这个名字不准确，它只是强调需要额外做排序，不一定涉及IO操作，也可能是内存排序；
-
查询salaries表时，也用了主键索引
-
用来和主键比较的有2个值，外层循环传进来的emp_no和一个常量值’1992-08-04’；
-
emp_no是int类型占4个字节，from_date占3个字节；这里emp_no和from_date都用到了，所以key_len是7。如果join条件只有emp_no，key_len会是4；
-
PRIMARY，idx_emp_no都是能用的，最终选了PRIMARY（避免回表的开销）；
-
通过主键比对，最多只会选出1条记录；
-
这条记录还要满足
salaries.salary > 70000，explain预估满足这个条件的概率是1/3；
再强调一遍，一定要把重点放在理解底层执行逻辑上。
一个典型的误区，就是去研究type=range和type=eq_ref有啥区别，这完全是舍本逐末。
-
详细版本
通过指定format=json可以拿到更详细的信息。
explain format=json
SELECT * FROM employees JOIN salaries ON employees.emp_no = salaries.emp_no
AND salaries.from_date = '1992-08-04'
WHERE employees.emp_no > 10005 AND salaries.salary > 70000
ORDER BY salaries.salary;
-- employees: PRIMARY KEY(emp_no)
-- salaries: PRIMARY KEY(emp_no, from_date)
{
"query_block": {
"select_id": 1,
"cost_info": { "query_cost": "244600.58" },
"ordering_operation": {
"using_temporary_table": true,
"using_filesort": true,
"cost_info": { "sort_cost": "49884.01" },
"nested_loop": [
{
"table": {
"table_name": "employees",
"access_type": "range",
"possible_keys": [ "PRIMARY" ],
"key": "PRIMARY",
"used_key_parts": [ "emp_no" ],
"key_length": "4",
"rows_examined_per_scan": 149667,
"rows_produced_per_join": 149667,
"filtered": "100.00",
"cost_info": { "read_cost": "15116.17", "eval_cost": "14966.70", "prefix_cost": "30082.87", "data_read_per_join": "19M" },
"used_columns": [ "emp_no", "birth_date", "first_name", "last_name", "gender", "hire_date" ],
"attached_condition": "(`employees`.`employees`.`emp_no` > 10005)"
}
},
{
"table": {
"table_name": "salaries",
"access_type": "eq_ref",
"possible_keys": [ "PRIMARY", "idx_emp_no" ],
"key": "PRIMARY",
"used_key_parts": [ "emp_no", "from_date" ],
"key_length": "7",
"ref": [ "employees.employees.emp_no", "const" ],
"rows_examined_per_scan": 1,
"rows_produced_per_join": 49884,
"filtered": "33.33",
"cost_info": { "read_cost": "149667.00", "eval_cost": "4988.40", "prefix_cost": "194716.57", "data_read_per_join": "779K" },
"used_columns": [ "emp_no", "salary", "from_date", "to_date" ],
"attached_condition": "(`employees`.`salaries`.`salary` > 70000)"
}
}
]
}
}
}这里可以看到一些更细节的东西，比如：
- 外层表employees经过
emp_no > 10005筛选出149667条记录； - 和内层表salaries做join时，因为用到了组合主键的全部列，主键又是唯一的，最多只会有149667条；
- 再经过
salaries.salary > 70000筛选出约33.33%的记录，也就是149667 * 33.33% = 49884； - 所以最终参与排序的记录数就是49884；
需要注意的是:
- 关于explain输出的cost是如何计算的，官方也没有很明确的文档，感兴趣的同学可以参考 https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-2.html https://yunche.pro/blog/?tags=48
- 有时MySQL的优化器会重写SQL，可以通过
show warnings查看重写后的SQL，比如：
explain select * from employees where emp_no in (select emp_no from salaries);
show warnings;
/* select#1 */ select `employees`.`employees`.`emp_no` AS `emp_no`,`employees`.`employees`.`birth_date` AS `birth_date`,`employees`.`employees`.`first_name` AS `first_name`,`employees`.`employees`.`last_name` AS `last_name`,`employees`.`employees`.`gender` AS `gender`,`employees`.`employees`.`hire_date` AS `hire_date`
from `employees`.`employees` semi join (`employees`.`salaries`)
where (`employees`.`salaries`.`emp_no` = `employees`.`employees`.`emp_no`)–
结果如下：
–
总结
- 一定要把重点放在理解底层执行逻辑上，不要舍本逐末，沉溺explain细节，无法自拔；
- 可以结合tree、表格和json 3种格式一起看，帮助理解；
- 如果感觉执行计划很奇怪，有可能是MySQL优化器重写了SQL，可以执行
show warnings，查看重写后的SQL；
三、案例实操
这里我们通过一个问题和2个示例，帮大家加深对慢SQL优化的理解。
如何构造一条高质量的慢SQL？数据库实现高效查询的核心原则是：增量查询和索引加速。
其中，索引加速在很大程度上，也是在为增量查询服务。
要构造一个慢SQL，就要针对性地攻击“增量查询”。
通过上面的分析，可以看到数据库的增量策略，其实是相当成功的，想要破坏掉它（造出一条高质量的慢SQL），并不容易。
实际的数据库使用场景中，最常见的查询有2类：
- 列表页 一般是分页查询，通常适用按顺序增量搜索；
- 详情页 通常有非常精准的查询条件，适合利用where索引，精准定位。当然，也会有统计类的查询，比如 sum/avg等；
这个在第一个标题就讲过，数据库不适合处理这类业务，因为统计类查询天然是和增量策略冲突的。
下面是2个慢SQL优化的案例。
示例1
-- cost 364ms
SELECT * FROM employees JOIN salaries ON employees.emp_no = salaries.emp_no
AND salaries.from_date = '1992-08-04'
WHERE employees.emp_no > 10005 AND salaries.salary > 70000
ORDER BY salaries.salary
LIMIT 10;
-- employees: PRIMARY KEY(emp_no)
-- salaries: PRIMARY KEY(emp_no, from_date)
-> Limit: 10 row(s)
-> Sort: salaries.salary, limit input to 10 row(s) per chunk
-> Stream results (cost=95578 rows=74834)
-> Nested loop inner join (cost=95578 rows=74834)
-> Filter: (employees.emp_no > 10005) (cost=29971 rows=149667)
-> Index range scan on employees using PRIMARY over
(10005 < emp_no) (cost=29971 rows=149667)
-> Filter: (salaries.salary > 70000) (cost=0.338 rows=0.5)
-> Single-row index lookup on salaries using PRIMARY
(emp_no=employees.emp_no, from_date=DATE'1992-08-04')
(cost=0.338 rows=1)看这个图，cost的大头在join的部分，占了184738.27 / 234622.28 ≈ 80%。
但是这里的join已经用上了主键索引，不可能更快了。
那就没有办法优化了吗？
其实，这里优化的关键在于帮MySQL用上增量策略。
对于这条SQL，我们其实只需要10条结果，但是目前的执行策略是：
先查出所有符合条件的结果，再排序取前10条 — 没有用增量策略。
找到了问题，优化的方案也就简单了，只需要在排序字段salary上加索引。
create index idx_salary on salaries(salary);加索引后，耗时33ms，相对364ms有很大提升；对应的执行计划如下：
-> Limit: 10 row(s) (cost=443853 rows=10)
-> Nested loop inner join (cost=443853 rows=1.42e+6)
-> Index range scan on salaries using idx_salary over (70000 < salary), with index condition:
((salaries.from_date = DATE'1992-08-04') and (salaries.emp_no > 10005) and (salaries.salary > 70000))
(cost=284192 rows=1.42e+6)
-> Single-row index lookup on employees using PRIMARY (emp_no=salaries.emp_no) (cost=0.25 rows=1)
<br></br>salaries表总共有141W条记录，预估有5%的记录符合条件，也就是7万多条。
但是我们并不需要把7万多条全部找出来，只需要按顺序，收集到10条满足条件的，就可以返回了。
注意：
- 这里耗时大幅降低，不是因为节省了排序的开销，而是因为用上了增量策略；
- where条件一般不会影响增量策略的使用，因为一条数据是否满足where条件不受其他数据行的影响；
- 但order by不同，顺序是一个全局属性；一条数据排在第几位是由所有符合条件的数据决定的；
- 如果where和order by用到的索引不同，就涉及索引选择问题了；
-
hints
MySQL的优化器并不总能选出最优策略，有时候需要我们给它一点提示（hints）。
比如，上面加了索引以后，MySQL可能还是选了老的全量执行策略
-- 这里JOIN_ORDER就是给MySQL的提示
SELECT /*+ JOIN_ORDER(salaries, employees) */ *
FROM employees JOIN salaries
ON employees.emp_no = salaries.emp_no AND salaries.from_date = '1992-08-04'
WHERE employees.emp_no > 10005 AND salaries.salary > 70000
ORDER BY salaries.salary limit 10;示例2
再来看另外一个例子
-- 耗时4200ms
select * from employees
left join salaries on employees.emp_no = salaries.emp_no
left join dept_emp on dept_emp.emp_no = employees.emp_no
where dept_emp.dept_no not in (select dept_no from departments where dept_name > 'AAA')
order by employees.hire_date
limit 10;
-- employees: PRIMARY KEY (emp_no)
-- salaries: PRIMARY KEY (emp_no, from_date)
-- dept_emp: PRIMARY KEY (emp_no,dept_no)
-- departments: UNIQUE KEY (dept_name)尝试1：使用增量策略 — 4500ms
给employees.hire_date加索引
`create index idx_hire_date on employees(hire_date);`-> Limit: 10 row(s) (cost=851920 rows=10)
-> Filter: <in_optimizer>(dept_emp.dept_no,dept_emp.dept_no in (select #2) is false) (cost=851920 rows=10.4)
-> Nested loop left join (cost=851920 rows=10.4)
-> Nested loop left join (cost=135044 rows=9.42)
-> Index scan on employees using idx_hire_date (cost=776e-6 rows=1)
-> Index lookup on salaries using PRIMARY (emp_no=employees.emp_no) (cost=0.451 rows=9.42)
-> Index lookup on dept_emp using PRIMARY (emp_no=employees.emp_no) (cost=0.254 rows=1.1)
-> Select #2 (subquery in condition; run only once)
-> Filter: ((dept_emp.dept_no = `<materialized_subquery>`.dept_no)) (cost=2.9..2.9 rows=1)
-> Limit: 1 row(s) (cost=2.8..2.8 rows=1)
-> Index lookup on <materialized_subquery> using <auto_distinct_key> (dept_no=dept_emp.dept_no)
-> Materialize with deduplication (cost=2.8..2.8 rows=9)
-> Filter: (departments.dept_name > 'AAA') (cost=1.9 rows=9)
-> Covering index scan on departments using dept_name (cost=1.9 rows=9)可以看到索引生效了，但是耗时变成了4500ms，不仅没减少还略有增加。
实际执行一下这条SQL，可以知道数据库里一条符合条件的记录都没有。
这种情况下，增量和全量没有区别，走了hire_date上的索引，反而会带来额外的回表开销。
这也是一个很好的例子：走索引不一定就快，还是要具体分析底层的执行逻辑。
尝试2：拆分SQL — 5ms
可以看到查询dept_emp表开销最大，另外观察到departments表很小，总共只有9条记录，考虑拆分SQL
-- 耗时2ms，返回 'd009','d005','d002','d003','d001','d004','d006','d008','d007'
select dept_no from departments where dept_name > 'AAA';
-- 把第一步返回的结果直接做为第二步查询的条件，耗时3ms
select * from employees
left join salaries on employees.emp_no = salaries.emp_no
left join dept_emp on dept_emp.emp_no = employees.emp_no
where dept_emp.dept_no not in ('d009','d005','d002','d003','d001','d004','d006','d008','d007')
order by employees.hire_date
limit 10;
-- employees: PRIMARY KEY (emp_no)
-- salaries: PRIMARY KEY (emp_no, from_date)
-- dept_emp: PRIMARY KEY (emp_no,dept_no)
-- departments: UNIQUE KEY (dept_name)这里驱动表变成了dept_emp，而且explain预估只会有10条记录符合条件，显然是非常好的策略。
dept_emp位于left join的右侧，为什么可以做驱动表?
因为在当前这个SQL中，left join 和 inner join等价，而inner join，左右两边都可以做驱动表。
下面我们证明这里的left join等价于inner join：
-
join的结果有3种情况：能匹配上的(inner join的结果)，左边多出来的(右边为null)，右边多出来的(左边为null)。
left join = 能匹配上的 + 左边多出来的(右边为null)。
只要证明左边多出来的(右边为null)的这部分不符合条件，就可以证明这里的left join等价于inner join。
-
要满足where dept_emp.dept_no not in (‘d009’,…)，dept_emp.dept_no就不能为null。
对SQL来说，null 只能做is (not) null判断，null not in一定是false。
- 也就是left join中，右边为null的部分都不符合条件。
四、常用优化技巧
本文转载自：time-tell-the-truth
原文链接：
https://v0etqjz8nkv.feishu.cn/docx/MWYVdprXZoiBV9xDk7PcjSW9nEb
https://v0etqjz8nkv.feishu.cn/docx/FQJOdrDOtoTlC7x2NHPczzUenGg
https://v0etqjz8nkv.feishu.cn/docx/IyaQdJcnDoBxJsxdCLicW02ln9c
