一、基础概念
1. 回表查询
在过往介绍 MySQL 聚簇索引时曾提过，当执行查询时会先匹配索引，再通过回表的方式二次匹配具体记录值。
基于此特性，若想取得更优的查询效率则需减少查询回表次数，便可在查询时可为需返回字段的适当添加索引，减少回表从而提升性能。
如下述执行计划中查询用户表记录，可以看到通过主键索引正常匹配数据。
调整上述查询语句仅返回单个索引列，即结果列 id 在条件查询 id=1 时已完成匹配，无需回表查询其余信息，可看到此时 Extra=Using index 无需回表查询。
因此，两个例子中通过执行计划可以看到虽然都命中了主键索引，但第二个查询语句性能仍优于第一个。这也是常提到的查询语句中不建议采用 select * 的原因之一，通过减少返回的属性集从而尽可能减少回表的频次。
二、关联查询
1. 驱动表
在多表 join 关联查询中涉及一个核心概念：驱动表 (Drivering Table)，其对性能起着至关重要的作用。
所谓驱动表即为关联主表，在 left join 中左表一定为驱动表，在 right join 中则右表为驱动表，而 inner join 中驱动表的选择则由数据库动态决定。
对于 inner join 而言驱动表的选择遵循着一个质朴的原则：小表驱动大表，简而言之，即 inner join 中优化器会初步计算代价有限将小表作为驱动表。
如存在 tb_class 与 tb_student 两张表数据，其中 tb_student 表量级大于 tb_class，那么在 inner join 查询时无论谁作为主表最终优化器执行永远都是由 tb_class 作为驱动表。
即下述两个查询语句中驱动表都是 tb_class，可通过执行计划 ID 顺序验证结论。
select
*
from tb_class c
join tb_student s
on s.class_id = c.id
select
*
from tb_student s
join tb_class c
on c.id = s.class_id
那么为什么 MySQL 会采取这个策略呢？
在 MySQL 中多表 JOIN（尤其是 Nested Loop Join）有一个执行顺序：先由驱动表取一行，再由其从被驱动表根据连接条件查找匹配。从这个规则路径下可以看出，驱动表是循环次数的控制者。
按照此规则，可以轻易得到关联的 总成本 = 驱动表行数 * 被驱动扫描行数。而 join 条件通常能通过索引等手段提升性能，即降低驱动表行数从而降低整个查询过程的扫描次数，进而降低 IO 操作次数从而取得更优效率。
同时，小表作为驱动表的另一好处即提高了缓存命中率。由于 Buffer Pool 缓存池的存在，对于小表可完成被缓冲池缓存。当小表作为驱动表时，每次循环访问小表行都能命中缓冲池，无需频繁磁盘 IO。
因此，即便在 left join 与 right join 等固定驱动表查询下，也常考虑小表作为驱动表从而提升性能。
2. 驱动代价
从上述的描述可以知道表的记录数是确定确定表的一大关键因素，但其后背蕴含着完整的一套机制。
除了已经提到的行数指标外，驱动表的确定同时参考下述各项指标：
| 优化指标 | 含义 |
|---|---|
| 行数估算（rows） | 表或索引扫描后预计产生的行数。 |
| 过滤率（filtered） | 过滤后剩余比例。 |
| 连接代价（join_cost） | 当前表被扫描后与前面已驱动表连接的开销。 |
| 索引可用性 | 是否能用索引进行过滤。 |
| 排序或分组代价 | 若有 ORDER BY 或 GROUP BY，是否可顺序扫描。 |
以过滤率为例，优化器会根据查询条件预判断，通过过滤后匹配度越高的其作为驱动表的概率越大。
同样以上述的 tb_class 与 tb_student 为例，表 tb_student 量级大于 tb_class，按常理下在 join 关联查询时优化器将优先考虑 tb_class 作为驱动表。但若查询条件能够更精准的匹配被驱动表，此时情况将发生反转。
如下查询语句中，通过查询条件 student_id = 1 匹配后 tb_student 仅匹配单条记录，若作为驱动表只需再单次匹配 tb_class 即可完成查询。因此，此场景下 tb_student 便会升阶作为驱动表。
select
*
from tb_class c
join tb_student s
on s.class_id = c.id
where
s.student_id = 1
3. 驱动特例
虽然优化器有着一套完善的机制，但并非在所有情况下其都能够给出最优解。
在 join 查询确定驱动表时，将优先以小表作为驱动表进行关联查询。但若两张表差距过于悬殊时，大表驱动小表往往却能展现出更优的效率。
让我们以伪代码的角度来分析 join 的执行流程，在 InnoDB 的嵌套循环连接 (Nested Loop Join) 中，执行器以驱动表 outer_table 为关联遍历被驱动表 inner_table。
for each row in outer_table
find matching rows in inner_table (using index if possible)
按照小表驱动大表的规则，小表中每条记录都需通过索引循环匹配大表记录，频繁的索引定位及回表操作将严重拖慢查询效率。
反之这类场景下若以大表驱动小表，则只需大表执行一次全量扫描，对于小表的匹配关联在索引下执行效率反而更高。
故此，小表驱动大表并非万能铁律，优化的核心关键在于降低查询扫描次数，减少 IO 从而提升效率。
此类场景，若想跳过优化器的介入，则可通过 STRAIGHT_JOIN 强制优化器按照语句定义的顺序进行查询，即定义在前的永远的是驱动表。
调整刚刚的查询语句，如下示例中优化器将不再作任何操作，由于 tb_class 定义在前则以其作为驱动表执行查询。故此，对于上述提到的负优化，则可手动指定关联的驱动表。
select
*
from tb_class c
straight_join tb_student s
on s.class_id = c.id
三、查询优化
1. Semi优化
在索引失效的场景中，否定查询如 not in 或 != 等操作是非常经典的案例。
如下述查询语句中执行器只有在全表扫描后才能知道哪些具体的不包含的记录，而无法通过索引信息过滤。
-- 否定查询
SELECT
*
FROM
user
WHERE
id NOT IN (1, 2, 3);
对于此类场景，则可以通过 NOT EXISTS 语法进行改造，从而转化为 in 查询利用索引以取得最优效率。
SELECT
*
FROM
user a
WHERE
NOT EXISTS (
SELECT
1
FROM
user b
WHERE
a.id = b.id
and b.id IN (1, 2, 3)
)
同时，在执行计划层面 MySQL 8.0+ 对于 NOT EXISTS 会自动做一种称为半连接 (semi-join) 反半连接 (anti-semi-join) 的优化。
其核心在于当外层表中的一行只要在子查询结果中找到至少一条匹配行，就可以认定匹配成功，就不再继续查找。即会将子查询和外层循环改写为一种等价的 JOIN 结构，从而使用内表的索引避免真正的 N 次子查询执行。
仍以刚才改造后的 NOT EXISTS 为例，MySQL 可能改写为类似下面的逻辑：
SELECT
*
FROM
user a
left join user b
on
a.id = b.id and b.id IN (1, 2, 3)
where
b.id is null
2. 结果排序
在介绍确定驱动代价的时候提到了优化器同时会考虑索引可用性以及排序操作，那为什么排序会相对特殊呢？
这其中涉及一个相对隐晦的知识即 order by 只有针对驱动表时索引才会生效，也就是说当 order by 的字段来自被驱动表时，即便字段添加了索引也无法生效。
如下述查询命令中，通过 straight_join 指定了 tb_class 为驱动表，但在 order by 中却以被驱动表 s 的 create_time 作为排序依据，故索引也将无法生效。
此时通过执行计划查看，将会发现 Extra = Using filesort 表示无法使用索引，MySQL 需要将结果先读入临时表（如内存）进行排序，当结果集过大内存无法容纳时则需先写入文件，因此其所带来的性能损耗可想而知。
select
*
from tb_class c
straight_join tb_student s
on
s.class_id = c.id
order by
s.create_time
因此，当查询涉及排序时，若查询结果集记录不大时虽性能损耗不大，但若结果集较多时，为了获得更优的查询性能应尽量保证以驱动表字段作为排序依据。
倘若在默认优化器的 join 下无法实现最优解则同样可利用 straight_join 显式指定驱动表。
3. 分组优化
既然提到了 order by 排序，那肯定就逃不开 group by 分组操作。
与上述提到的排序类似，在 join 时 group by 索引生效的前提同样是索引来源表为驱动表，否则索引将失效。
同时，当混合执行 order by 与 group by 操作时，需保证二者使用字段一致且顺序也需保持一致，否则索引存在失效的情况。
如下述示例中为 tb_class 表字段 create_time 与 update_time 创建联合索引，虽然写法上二者都满足了最左前缀但索引仍可能失效。
select
*
from tb_class c
join tb_student s
on
s.class_id = c.id
group by
t.create_time
order by
t.create_time,
t.update_time
在执行 group by 分组操作时存在一个优化小技巧，即使没有显式使用 order by 对结果排序，MySQL 仍然会隐式对结果集排序。
因此，若对于返回的结果无顺序要求，可显式通过 order by null 关闭默认的排序行为，在执行计划中可以观察是否返回 Extra = Using filesort，关闭默认排序后则通常不会出现此结果。
select
c.name,
s.name,
max(s.age)
from tb_class c
join tb_student s
on
s.class_id = c.id
group by
c.name,
s.name,
order by null
