深入理解SQL慢查询优化 - 底层原理、explain分析 和 案例实操
本文归于系列： 技术干货
在数据库的日常运维和开发中，SQL查询的性能问题一直是开发者们关注的焦点。因此，深入理解SQL慢查询优化的底层原理，掌握如何优化SQL查询，对于提升系统的整体性能具有重要意义。
本文将从实现高性能查询的核心策略出发，详细剖析增量查询和索引加速的原理及其在各类基本操作中的应用，帮助读者深入理解SQL慢查询优化的底层逻辑，从而能够在实际工作中有效地进行SQL查询优化。接下来，让我们一同探讨如何实现这一目标。
首先我们需要理解实现高性能查询的核心策略：增量查询和索引加速， 再结合核心策略分析每种基本操作，如where / join / order by 等。
一、核心策略 - 增量查询 & 索引加速
以下面的SQL为例进行说明
-- MySQL 自带的测试库-- 如果没有，可以从这里下载 https://github.com/datacharmer/test_dbuse employees;
SELECT *FROM departments JOIN dept_emp ON departments.dept_no = dept_emp.dept_noWHERE 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高性能查询的核心策略：
- 增量查询： 只要收集到了足够的结果，就立刻返回，避免全量计算，减少工作量。
2. 索引加速： 在数据存储时做预先的排序，在查询时利用有序性快速定位到所需的行，最大发挥增量查询的优势。
二、核心策略在基本操作上的应用
SELECT *FROM departments JOIN dept_emp ON departments.dept_no = dept_emp.dept_noWHERE departments.dept_name = 'Marketing'LIMIT 10;
一个完整的SQL是由若干基本操作组成的，比如：上面这条语句包含join, where和limit 3个基本操作。
要理解一条完整的SQL是怎么执行的，先要理解每种基本操作是怎么执行的。
- order by
顺序是一个全局属性。如果order
by的列上没有索引，就必须把整个表都加载进内存进行排序，非常耗时。如果有索引，MySQL可以通过索引直接按顺序读取行，非常高效。
-- salary列上没有索引，耗时400msselect * from salaries order by salary limit 10;
-- emp_no列上有索引，耗时2msselect * 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列上有索引，耗时2msselect * 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列上没有索引，耗时300msselect * from salaries where salary = 1171046 limit 10;-- emp_no列上有索引，耗时2msselect * from salaries where emp_no = 12000 limit 10;
- group by
分组操作的实现一般有2种方案：hash和排序，MySQL用的是排序方案。
1. 新建临时表；
2. 扫原始数据表，并按顺序插入临时表；
这里的顺序就是按照group by的列进行排序。这样就可以保证group by列的值相同的行排在一起，方便统计。
3. 在临时表上做处理，如统计（min/max/count/sum/avg）
如果group by的列，已经有索引了，那前2步的开销就省掉了。
-- emp_no上有主键索引，耗时2msselect emp_no, max(salary) from salaries group by emp_no limit 10;
-- from_date上没有索引，耗时700msselect 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上没有索引，耗时300msselect max(salary) from salaries;-- emp_no上有索引，耗时2msselect max(emp_no) from salaries;
- avg / sum / count
需要做全量扫描，如果有索引，可以扫索引。索引文件(仅包含索引列和主键列)相比主数据(全部列)通常要小一些，速度会稍微快一点。
如果表的列也很多，行也很多，索引会明显小于主数据文件，效果会更明显
-- 扫主数据，耗时300msselect sum(salary) from salaries;-- 扫索引，耗时270msselect 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子查询提前退出循环的优化称为semijoinselect * 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子查询提前退出循环的优化称为antijoinselect * 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]）；
1. 根据employees[i].emp_no和from_date='1992-08-04’去查salaries表；
2. 这里刚好可以根据salaries表的主键(emp_no, from_date)，唯一定位到一条记录salaries[j]；
3. 如果salaries[j].salary > 70000，则把JoinedRow(employees[i], salaries[j])加入join结果；
3. 拿到join结果集后，根据salaries.salary排序；
explain是如何描述执行策略的？
这里，我们看一下同样的内容（执行策略），explain的描述方式是怎样的？
-
极简版本
explain format=treeSELECT * 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功能的。
-
默认版本
explainSELECT * 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表时，用了主键索引
1. explain预估通过主键 ` emp_no > 10005 ` 会过滤出149667行记录；employees表总共有约30W行记录（30W这个数据不是通过explain看出来的，而是直接select count(1)查出来的）；这一步过滤，减少了后续需要和salaries做join的记录数，提升了性能（Using where）；
2. 过滤出的149667行记录，因为没有其他where条件了，所以100%都符合条件；
3. Using temporary和Using filesort其实是排序操作用到了；filesort这个名字不准确，它只是强调需要额外做排序，不一定涉及IO操作，也可能是内存排序；
3. 查询salaries表时，也用了主键索引
1. 用来和主键比较的有2个值，外层循环传进来的emp_no和一个常量值'1992-08-04'；
2. emp_no是int类型占4个字节，from_date占3个字节；这里emp_no和from_date都用到了，所以key_len是7。如果join条件只有emp_no，key_len会是4；
3. PRIMARY，idx_emp_no都是能用的，最终选了PRIMARY（避免回表的开销）；
4. 通过主键比对，最多只会选出1条记录；
5. 这条记录还要满足 ` salaries.salary > 70000 ` ，explain预估满足这个条件的概率是1/3；
再强调一遍，一定要把重点放在 _理解底层执行逻辑 _ 上。
一个典型的误区，就是去研究type=range和type=eq_ref有啥区别，这完全是舍本逐末。
-
详细版本
通过指定 format=json 可以拿到更详细的信息。
explain format=jsonSELECT * 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 364msSELECT * 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)
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
再来看另外一个例子
-- 耗时4200msselect * 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';
-- 把第一步返回的结果直接做为第二步查询的条件，耗时3msselect * 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。
2. 要满足where dept_emp.dept_no not in (‘d009’,…)，dept_emp.dept_no就不能为null。
对SQL来说，null 只能做is (not) null判断，null not in一定是false。
3. 也就是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
欢迎在评论区留言表达看法和问题，阿沛会一一作出回复。
如果本文对大家有帮助，麻烦大家动动小手点个免费的“赞”或“在看”，大家的鼓励就是阿沛持续更新的动力~
-- 往期精彩 –
面试题：什么是虚拟内存，它如何与物理内存映射？页面置换算法有哪些，优缺点如何？内存碎片是如何产生的，有哪些解决方法？
深入Redis系列 详解Redis的内存管理和缓存数据的淘汰机制
面试题：说说看你对数据库事务和ACID的理解？并发事务可能会产生哪些问题，该如何解决？什么是快照读和MVCC，解决了什么问题？
华为一面：说说看MySQL的索引类型及其作用？什么是聚簇索引和非聚簇索引？索引失效的情况有哪些？说说看回表、覆盖索引和索引下推？