子查询 - 非官方 MySQL 8.0 优化指南 - 学习笔记
优化器有许多优化子查询的执行策略，包括重写成连接、半连接、临时表。这种策略会根据子查询的类型和布置来使用。
标量子查询
标量子查询是只返回一行结果的子查询，在执行过程中还可以被优化和缓存。
在例子13中，我们可以通过标量子查询，找到 多伦多 的 CountryCode。
关键的一点是，优化器把它视作两个查询，花费分别是 1.00 和 4213.00 。
第二个查询（select_id:2）没有可用的索引，因此进行了全表扫描。因为条件查询的列attached_condition (`City`.`Name`)没有被索引。
EXPLAIN FORMAT=JSON
SELECT * FROM Country WHERE Code = (
SELECT CountryCode FROM City WHERE name='Toronto'
);
例子13 标量子查询.png
在为其添加索引后，这个查询就得到优化了。
例子14 添加索引，优化标量子查询.png
IN 子查询 （唯一）
例子15展示了返回主键的子查询，结果是唯一的。因此这种子查询可以安全地转换为内连接查询，并返回相同结果。
EXPLAIN FORMAT=JSON
SELECT * FROM City WHERE CountryCode IN (
SELECT Code FROM Country WHERE Continent = 'Asia'
);
例子15 可转换的 IN 子查询.png
这种子查询是比较高效的。我们可以看出先查询了 Country 表（使用了索引），对于每个匹配行，再通过 CountryCode 索引来查出 City 表里的行。
IN 子查询（非唯一）
在例子15中，子查询被重写成内连接，原因是它已经返回不重复的结果了。
当子查询不是不重复的，MySQL 优化器就不得不采用其他策略。
在例子16中，子查询要找到使用至少一种官方语言的国家。因为有多个国家使用超过一种官方语言，所以子查询结果不是唯一的。
EXPLAIN FORMAT=JSON
SELECT * FROM Country WHERE Code IN (
SELECT CountryCode FROM CountryLanguage WHERE isOfficial=1
);
例子16 不能重写成内连接的子查询.png
例子17的 EXPLAIN 结果 OPTIMIZER_TRACE 可以看出优化器指出该查询不能重写成连接查询，而是“半连接”。优化器有几种策略来执行半连接：首次匹配、查临时表、去重。在这个例子中，优化器采取了（代价最低的）临时表策略来查询。
SET OPTIMIZER_TRACE="enabled=on";
SET optimizer_trace_max_mem_size = 1024 * 1024;
SELECT * FROM Country WHERE Code IN (
SELECT CountryCode FROM CountryLanguage WHERE isOfficial=1
);
SELECT * FROM information_schema.optimizer_trace;
例子17 子查询的半连接策略.png
NOT IN 子查询
一个 NOT IN 子查询无法使用临时表或其他策略来优化。为了说明两种方式的区别，考虑如下例子：
SELECT * FROM City WHERE CountryCode NOT IN (SELECT code FROM Country);SELECT * FROM City WHERE CountryCode NOT IN (SELECT code FROM Country WHERE continent IN ('Asia', 'Europe', 'North America'));
在第一个查询中，其子查询或多或少是其最理想的形式。code 列是 Country 的主键， 而通过索引扫描就可以构建一个不重复集。非要说的话，唯一的不足就是主键因为持有行值的原因比较宽（每个分页能容纳的记录就比较少）。
在第二个查询中，附加了一个条件：continent IN ('Asia', 'Europe', 'North America'))。考虑到 City 表的每一行都需要对照判断NOT IN，创建一个临时表去储存匹配到条件的行是合理的，这样就不必对 City 表每一行都去检查附加条件。
EXPLAIN FORMAT=JSON
SELECT * FROM City WHERE CountryCode NOT IN (
SELECT code FROM Country WHERE continent IN ('Asia', 'Europe', 'North America')
);
例子18 采用临时表的 NOT IN 子查询.png
派生表
SELECT查询的FROM后跟着的子查询产生的表就是派生表。这种子查询不需要产生临时表，MySQL通常可以把它“合并”回来。
EXPLAIN FORMAT=JSON
SELECT * FROM Country, (SELECT * FROM City WHERE CountryCode ='CAN' ) as CityTmp
WHERE Country.code=CityTmp.CountryCode AND CityTmp.name ='Toronto';
例子19 派生表被“合并”回来.png
潜在的问题是，这种“合并”会让一些查询不再合法。如果你升级版本后看到了语法警告，你可以选择关闭
derived_merge优化，这会导致查询代价提升，因为产生临时表的代价比较高。
