15.ORDER BY 优化

在某些情况下，MySQL可以使用索引来满足ORDER BY子句，而不需要进行任何额外的排序。

--oracle 也是一样，通过索引的有序性完成desc或asc的数据排序。

即使ORDER BY与索引不精确匹配，只要索引的所有未使用部分和所有额外的ORDER BY列都是WHERE子句中的常量，索引也可以使用。以下查询使用索引来解决ORDER BY部分：

SELECT * FROM t1

ORDER BY key_part1,key_part2,... ;

SELECT * FROM t1

WHERE key_part1 = constant

ORDER BY key_part2;

SELECT * FROM t1

ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1

WHERE key_part1 = 1

ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1

WHERE key_part1 > constant

ORDER BY key_part1 ASC;

SELECT * FROM t1

WHERE key_part1 < constant

ORDER BY key_part1 DESC;你在

SELECT * FROM t1

WHERE key_part1 = constant1 AND key_part2 > constant2

ORDER BY key_part2;

--上述情况都是在一个索引key 中，只是包含多个part

在某些情况下，MySQL不能使用索引来解析ORDER BY，虽然它仍然使用索引来查找匹配WHERE子句的行。这些情况包括以下内容：

你在不同key上面使用 order by

SELECT * FROM t1 ORDER BY key1, key2;

你在不连续的key part上使用order by

SELECT * FROM t1 WHERE key=constant ORDER BY key_part2;

--索引key part 必须连续

ASC和DESC混合使用

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

用于提取行的键不同于在ORDER BY中使用的键：

SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

您使用ORDER BY的表达式包含除键列名以外的其他术语：

SELECT * FROM t1 ORDER BY ABS(key);

SELECT * FROM t1 ORDER BY -key;

您正在连接多个表，并且ORDER BY中的列不是全部来自用于检索行的第一个非常数表。（这是EXPLAIN输出中没有const连接类型的第一个表。）
你有不同的order by 和 group by 表达式
您只索引在ORDER BY子句中命名的列的前缀。在这种情况下，索引不能用于完全解决排序顺序。例如，如果您有一个CHAR（20）列，但仅索引前10个字节，则索引无法区分超过第10个字节的值，并且将需要一个filesort。
使用的表索引的类型不按顺序存储行。例如，对于MEMORY表中的HASH索引

用于排序的索引的可用性可能受到使用列别名的影响。假设列t1.a被索引。在这个语句中，select列表中的列名是a。它引用了t1.a，所以对于ORDER BY中的一个引用，可以使用索引：

SELECT a FROM t1 ORDER BY a;

在这个语句中，select 列表中的列名也是一个，但它是别名。它指的是ABS（a），所以对于引用ORDER BY中的a，索引不能被使用：

SELECT ABS(a) AS a FROM t1 ORDER BY a;

--oracle 中这类场景叫字段上使用函数，需要使用函数索引

在以下语句中，ORDER BY引用的名称不是选择列表中列的名称。但是在t1中有一个列名为a，所以ORDER BY使用它，并且可以使用索引。（当然，得到的排序顺序可能与ABS（a）的顺序完全不同。）

SELECT ABS(a) AS b FROM t1 ORDER BY a;

--这个可能逻辑就不对了，这个是按照a 来排序

默认情况下，MySQL对所有GROUP BY col1，col2，...查询进行排序，就像在查询中指定ORDER BY col1，col2 ...一样。如果包含一个显式ORDER BY子句，它包含相同的列列表，MySQL会优化它，而不会受到任何速度的损失，尽管排序仍然存在。如果查询包含GROUP BY，但您希望避免排序结果的开销，则可以通过指定ORDER BY NULL来禁止排序。例如：

INSERT INTO foo

SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

--mysql 会对 group by 操作默认进行排序，oracle 中不会这样做

注意：

在MySQL 5.7中，隐式GROUP BY排序已被弃用。要实现分组结果的特定排序顺序，最好使用显式的ORDER BY子句。 GROUP BY排序是一个MySQL扩展，可能会在将来的版本中更改; 例如，使优化器可以以任何它认为最有效的方式排序分组，并避免排序开销。

使用EXPLAIN SELECT ... ORDER BY，您可以检查MySQL是否可以使用索引来解析查询。如果您在Extra列中看到Using filesort，则不是。Filesort 使用类似于MEMORY存储引擎所使用的固定长度的行存储格式。可变长度类型（如VARCHAR）使用固定长度进行存储。

MySQL有两种用于排序和检索结果的文件算法。原始方法只使用ORDER BY列。修改的方法不仅使用ORDER BY列，而且使用查询引用的所有列。

优化器选择要使用的filesort算法。它通常使用被修改的算法，除了涉及到BLOB或TEXT列时。在这种情况下，它使用原始算法。对于这两种算法，排序缓冲区大小都是sort_buffer_size系统变量的值。

原始filesort算法的工作原理如下：

根据键或表扫描读取所有行。跳过与WHERE子句不匹配的行。
对于每一行，在排序缓冲区中存储一对值（排序键值和行ID）。
如果所有对都适合排序缓冲区，则不会创建临时文件。否则，当排序缓冲区变满时，在内存中运行一个qsort（快速排序）并将其写入临时文件。保存一个指向已排序块的指针。--这里有点像oracle 中临时表空间的作用
重复前面的步骤，直到所有行都被读取。
将MERGEBUFF（7）区域进行多个合并到另一个临时文件中的一个块。重复，直到第一个文件中的所有块都在第二个文件中。
重复以下操作，比MERGEBUFF2（15）块还少。
在最后的多重合并中，只有行ID（值对的最后一部分）被写入结果文件。
使用结果文件中的行ID按排序顺序读取行。为了优化这个，读入大量块的行ID，对它们进行排序，然后用它们按排序顺序将行读入行缓冲区。行缓冲区大小是read_rnd_buffer_size系统变量值。此步骤的代码位于sql/http://records.cc源文件中。

这种方法的一个问题是它读取行两次：WHERE子句评估过程中的一次，在对value对排序之后进行第二次。即使第一次连续访问行（例如，如果进行表扫描），但是第二次随机访问。（排序键是有序的，但行的位置不是。）

修改后的filesort算法包含一个优化，以避免两次读取行：它记录排序键值，而不是行ID，它记录查询引用的列。修改的filesort算法是这样工作的：

1.读取匹配WHERE子句的行

2.对于每一行，记录由排序键值和查询引用的列组成的值的元组（存了排序列和查询列）。

3.当排序缓冲区变满时，通过内存中的排序键值对元组进行排序，并将其写入临时文件。

4.合并排序临时文件后，以排序顺序检索行，但直接从排序的元组中读取所需的列，而不是第二次访问表。

使用修改的文件排列算法，元组比在原始方法中使用的更长，并且更少的元素适合排序缓冲区。因此，额外的I / O会使修改的方法变得更慢，而不是更快。为了避免减速，只有在排序元组中的额外列的总大小不超过max_length_for_sort_data系统变量的值时，优化器才使用修改的算法。（设置这个变量的值太高的症状是高磁盘活动和低CPU活动的组合。）

从MySQL 5.7.3开始，修改后的filesort算法包含一个额外的优化，可以使更多的元组适合排序缓冲区：对于CHAR或VARCHAR类型的其他列或任何可为null的固定长度数据类型，这些值是打包的。例如，如果没有打包，则只包含3个字符的VARCHAR（255）列值在排序缓冲区中占用255个字符。通过打包，该值只需要3个字符加上一个两字节的长度指示符。 NULL值只需要一个位掩码。

对于包含比最大列长度短的或多个NULL值可压缩字符串的数据，更多记录可以存放在排序缓冲区。这改进了排序缓冲区的内存排序和基于磁盘的临时文件合并排序的性能。

--oracle 对内存排序的算法将的很少。只知道是在pga进行，不够才会使用临时表空间。

在极端情况下，打包可能是不利的：如果可打包字符串是最大列长度或NULL值很少，则长度指示符所需的空间减少了适合排序缓冲区的记录数，并且减缓内存和磁盘排序。

如果文件夹已经完成，EXPLAIN输出包括Extra列中的Using filesort。另外，优化器跟踪输出还包含一个filesort_summary块。例如：

"filesort_summary": {

"rows": 100,

"examined_rows": 100,

"number_of_tmp_files": 0,

"sort_buffer_size": 25192,

"sort_mode": "<sort_key, packed_additional_fields>"

}

sort_mode值提供有关使用的算法和排序缓冲区中的元组内容的信息：

<sort_key, rowid>:

排序缓冲区元组包含原始表行的排序键值和行标识符。按排序键值对排序行进行排序，并使用行标识符从表中读取行。

<sort_key, additional_fields>:

排序缓冲区元组包含排序键值和查询引用的列。元组按排序键值排序，列值直接从元组中读取。

<sort_key, packed_additional_fields>:

排序缓冲区元组包含排序键值和打包的查询引用的列。元组按排序键值排序，列值直接从元组中读取。

假设一个表t1有四个VARCHAR列a，b，c和d，并且优化器使用这个查询的filesort：

SELECT * FROM t1 ORDER BY a, b;

查询按a和b排序，但返回所有列，所以查询引用的列是a，b，c和d。根据优化器选择哪种文件排列算法，查询执行如下：

对于原始算法，排序 buffer 元组有下列内容：

(fixed size a value, fixed size b value,

row ID into t1)

优化器根据固定大小的值进行排序。排序后，优化器按顺序读取元组，并使用每个元组中的行ID从t1中读取行以获取选择列表列值。

对于没有打包的修改算法，排序缓冲区元组具有以下内容：

(fixed size a value, fixed size b value,

a value, b value, c value, d value)

优化器根据固定大小的值进行排序。排序后，优化器按顺序读取元组，并使用a，b，c和d的值来获取select列表值，而不再读取t1。

对于带有打包的修改算法，排序缓冲区元组具有以下内容：

(fixed size a value, fixed size b value,

a length, packed a value, b length, packed b value,

c length, packed c value, d length, packed d value)

--类似进行了数据压缩的意思

如果a，b，c或d中的任何一个为NULL，则除了位掩码之外，它们在排序缓冲区中不占用空间。

优化器根据固定大小的值进行排序。排序后，优化器按顺序读取元组，并使用a，b，c和d的值来获取选择列表值，而不再读取t1。

要提高ORDER BY速度，请检查是否可以让MySQL使用索引而不是额外的排序阶段。如果这是不可能的，你可以尝试下面的策略：

增加sort_buffer_size 变量值
增加read_rnd_buffer_size 变量值
通过声明列的大小，每行使用更少的RAM，因为它们需要保存存储在其中的值。例如，如果值不超过16个字符，则CHAR（16）比CHAR（200）更好。
将tmpdir系统变量更改为指向具有大量可用空间的专用文件系统。变量值可以列出以循环方式使用的多个路径; 您可以使用此功能将负载分散到多个目录中。在UNIX上，路径应该用冒号（“：”）分隔，在Windows上应该用分号（“;”）分隔。路径应该命名位于不同物理磁盘上的文件系统中的目录，而不是同一磁盘上的不同分区上的目录。

--这个基本上没什么意义，纯粹优化临时表空间I/O

如果一个索引不用于ORDER BY，但是LIMIT子句也存在，优化器可以避免使用一个合并文件并对内存中的行进行排序。

16 group by 优化

满足GROUP BY子句的最通用的方法是扫描整个表并创建一个新的临时表，其中每个组中的所有行都是连续的，然后使用此临时表来发现组并应用聚合函数（如果有）。在某些情况下，MySQL可以做得比这更好，并使用索引访问来避免创建临时表。

使用GROUP BY索引的最重要的前提条件是，所有GROUP BY列引用来自同一索引的属性，并且索引按顺序存储其键（例如，是BTREE索引而不是HASH索引）。是否可以通过索引访问来替换临时表还取决于查询中使用索引的哪些部分，为这些部分指定的条件以及选定的聚合函数。

有两种方法可以通过索引访问来执行GROUP BY查询，详见以下章节。在第一种方法中，分组操作与所有范围谓词（如果有的话）一起应用。第二种方法首先执行范围扫描，然后对得到的元组进行分组。

在MySQL中，GROUP BY用于排序（默认），因此服务器也可以将ORDER BY优化应用于分组。

疏松 index scan

处理GROUP BY最有效的方法是使用索引直接检索分组列。通过这种访问方法，MySQL使用了一些索引类型的属性（例如，BTREE key是有序的）。这个属性允许在索引中使用查找组，而不必考虑索引中所有满足所有WHERE条件的所有键。这种访问方法只考虑索引中的一小部分键，因此称为松散索引扫描。当没有WHERE子句时，松散的索引扫描会读取与组数相同数量的键，这可能比所有键的数量少得多。如果WHERE子句包含范围谓词，松散索引扫描会查找每个组中满足范围条件的第一个键，并再次读取尽可能少的键。在以下情况下可能：

查询只在单独的表上
GROUP BY仅命名构成索引最左边前缀的列，而不是其他列。（例如，如果表t1在（c1，c2，c3）上有一个索引，松散索引扫描适用于查询具有GROUP BY c1，c2 ,. 如果查询具有GROUP BY c2，c3（列不是最左边的前缀）或GROUP BY c1，c2，c4（c4不在索引中），则不适用。
选择列表中使用的唯一聚合函数（如果有的话）是MIN（）和MAX（），并且它们全部引用同一列。该列必须位于索引中，并且必须遵循GROUP BY中的列。

除了MIN（）或MAX（）函数的参数以外，索引中除查询中引用的GROUP BY以外的任何其他部分都必须是常量（即必须与常量等价地引用它们）。
对于索引中的列，必须对全列值进行索引，而不仅仅是前缀。例如，对于c1 VARCHAR（20），INDEX（c1（10）），索引不能用于松散索引扫描。

如果松散索引扫描适用于查询，则EXPLAIN输出会在Extra列中显示Using index for group-by。

假定表t1（c1，c2，c3，c4）上有一个索引idx（c1，c2，c3）。松散的索引扫描访问方法可用于以下查询：

SELECT c1, c2 FROM t1 GROUP BY c1, c2;

SELECT DISTINCT c1, c2 FROM t1;

SELECT c1, MIN(c2) FROM t1 GROUP BY c1;

SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;

SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;

SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;

下面的查询不能用这个快速选择方法执行，原因如下：

除了MIN（）或MAX（）之外的聚合函数：

SELECT c1, SUM(c2) FROM t1 GROUP BY c1;

GROUP BY子句中的列不构成索引的最左边的前缀：

SELECT c1, c2 FROM t1 GROUP BY c2, c3;

查询引用GROUP BY部分之后的键的一部分，并且不是常量：

SELECT c1, c3 FROM t1 GROUP BY c1, c2;

如果查询包含WHERE c3 = const，则可以使用松散索引扫描。

松散索引扫描访问方法除了已经支持MIN（）和MAX（）引用之外，还可以应用于选择列表中其他形式的聚合函数引用：

支持AVG（DISTINCT），SUM（DISTINCT）和COUNT（DISTINCT）。 AVG（DISTINCT）和SUM（DISTINCT）只有一个参数.COUNT（DISTINCT）可以有多个列参数。
查询中不得有GROUP BY或DISTINCT子句
之前描述的松散扫描限制仍然适用。

假定表t1（c1，c2，c3，c4）上有一个索引idx（c1，c2，c3）。松散的索引扫描访问方法可用于以下查询：

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

松散索引扫描不适用于以下查询：

SELECT DISTINCT COUNT(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1) FROM t1 GROUP BY c1;

Tight 索引扫描

严格的索引扫描可能是全索引扫描或范围索引扫描，具体取决于查询条件。

当没有满足松散索引扫描的条件时，仍然可以避免为GROUP BY查询创建临时表。如果WHERE子句中有范围条件，则此方法只读取满足这些条件的键。否则，它执行索引扫描。因为此方法读取WHERE子句定义的每个范围内的所有键，或者在没有范围条件的情况下扫描整个索引，所以我们称其为紧索引扫描。在紧密索引扫描的情况下，分组操作仅在所有键满足范围条件已被发现时被执行。

为了使这个方法起作用，查询中的所有列都有一个常量相等条件就足够了，这个条件引用了GROUP BY键之前或之间的键的部分。来自相等条件的常量填充搜索关键字中的任何“间隙”，从而可以形成索引的完整前缀。这些索引前缀可以用于索引查找。如果我们需要对GROUP BY结果进行排序，并且可以形成作为索引前缀的搜索关键字，那么MySQL也会避免额外的排序操作，因为使用有序索引中的前缀搜索已经按顺序检索所有关键字。

假定表t1（c1，c2，c3，c4）上有一个索引idx（c1，c2，c3）。以下查询不适用于前面描述的松散索引扫描访问方法，但仍然适用于紧凑的索引扫描访问方法。

GROUP BY中有一个缺口，但是它是由条件c2 ='a'覆盖的：

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

--where 条件中的 C2 把索引前缀衔接起来了。

GROUP BY不是从键的第一部分开始的，但有一个条件为该部分提供了一个常量：

SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

--这个也是衔接的意思

17 DISTINCT 优化

与ORDER BY结合的DISTINCT在很多情况下都需要临时表。

在大多数情况下，DISTINCT子句可以被认为是GROUP BY的特例。例如，以下两个查询是等价的：

SELECT DISTINCT c1, c2, c3 FROM t1

WHERE c1 > const;

SELECT c1, c2, c3 FROM t1

WHERE c1 > const GROUP BY c1, c2, c3;

--mysql 里面可以直接group by，oracle 里面必须聚合之后才能分组。

由于这种等价性，适用于GROUP BY查询的优化也可以应用于具有DISTINCT子句的查询。

如果您不使用查询中命名的所有表中的列，则MySQL一找到第一个匹配就立即停止扫描任何未使用的表。在下面的例子中，假设在t2之前使用了t1（你可以使用EXPLAIN来检查），当它在t2中找到第一行时，MySQL将停止从t2（对于t1中的任何特定行）读取：

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

--一旦在t2中发现存在了 t1.a就不会再继续扫描t2了。

18 子查询优化

MySQL查询优化器有不同的策略来评估子查询。对于IN（或= ANY）子查询，优化器有以下选择：

• Semi-join

• Materialization

• EXISTS strategy

对于NOT IN（或<> ALL）子查询，优化器具有以下选择：

• Materialization

• EXISTS strategy

半连接转换优化子查询

优化程序使用半连接策略来改善子查询的执行，如本节所述。

对于两个表之间的inner联接，联接从一个表中返回一个行，尽可能的与另一个表匹配。但是对于一些问题，唯一重要的信息是是否有匹配，而不是匹配的数量。假设有一些名为class 和花roster 的表，分别列出课程中的class 和class rosters

（每个class中注册的学生）。要列出真正有学生注册的class，您可以使用以下连接：

SELECT class.class_num, class.class_name

FROM class INNER JOIN roster

WHERE class.class_num = roster.class_num;

但是，结果会为每个已注册的学生列出每个班级一次。对于被问到的问题，这是不必要的重复信息。

假设class_num是类表中的主键，可以通过使用SELECT DISTINCT来实现重复的抑制，但是先生成所有匹配的行只是为了稍后消除重复项，这是低效的。

使用子查询可以获得相同的无重复结果：

SELECT class_num, class_name

FROM class

WHERE class_num IN (SELECT class_num FROM roster);

在这里，优化器可以识别IN子句要求子查询从名单表中只返回每个类号的一个实例。在这种情况下，查询可以作为一个半连接来执行，也就是说，只返回与名单中的行匹配的类中每行的一个实例。

外连接和内连接语法在外部查询规范中是允许的，并且表引用可以是基表或视图。

在MySQL中，子查询必须满足这些条件才能作为半连接进行处理：

它必须是出现在WHERE或ON子句顶层的IN（或= ANY）子查询，可能是AND表达式中的一个术语。例如：

SELECT ...

FROM ot1, ...

WHERE (oe1, ...) IN (SELECT ie1, ... FROM it1, ... WHERE ...);

这里，ot_i和it_i表示查询的外部和内部部分中的表，并且oe_i和ie_i表示引用外部和内部表中的列的表达式。

它必须是一个没有UNION结构的SELECT。
它不得包含GROUP BY或HAVING子句或聚合函数。
在order by 中不能有limit
在outer 查询中不能有STRAIGHT_JOIN
外部和内部表的数量必须小于连接中允许的最大表格数量。

子查询可以是相关的或不相关的。 DISTINCT是允许的，作为LIMIT，除非ORDER BY也被使用

如果子查询符合上述标准，则MySQL将其转换为半连接，并根据这些策略进行基于成本的选择：

将子查询转换为连接，或使用表拉出，并将查询作为子查询表和外部表之间的内连接运行。表拉出从子查询中拉出一个表到外部查询。

Duplicate Weedout：运行半连接，就像它是一个连接一样，并使用临时表删除重复的记录。
FirstMatch:当扫描行组合的内表，并且有多个给定值组的实例时，选择一个而不是全部返回它们。这个“快捷方式”扫描并消除了不必要的行的产生。
LooseScan：使用从每个子查询的值组中选择单个值的索引来扫描子查询表。
将子查询实体化到带有索引的临时表中，并使用临时表执行连接。该索引用于删除重复项。在将外部表连接到临时表时，该索引也可以稍后用于查找; 如果没有，则扫描表。

---sybbjs一句话描述该过程：子查询构造临时表，临时表去重，然后外部表来匹配临时表的值，一旦找到就不继续匹配，临时表上也可以使用索引。

可以使用optimizer_switch系统变量启用或禁用每个策略。 semijoin标志控制是否使用半连接。如果它设置为on，则可以使用firstmatch，loosescan，duplicateweedout（在MySQL 5.7.8中添加）和materialization标志来更好地控制允许的半连接策略。

如果duplicateweed的半连接策略被禁用，则除非所有其他适用的策略也被禁用，否则不会使用它。

如果duplicateweedout被禁用，有时优化器可能会生成一个远不是最优的查询计划。这发生在贪婪搜索期间的启发式修剪，可以通过设置optimizer_prune_level = 0来避免。

从MySQL 5.7.6开始，处理FROM子句中的视图和子查询的差异被最小化。这会影响具有STRAIGHT_JOIN修饰符的查询以及具有可以转换为半连接的IN子查询的视图。以下查询说明了这一点，因为处理中的更改会导致转换中的更改，从而执行不同的执行策略：

CREATE VIEW v AS

SELECT *

FROM t1

WHERE a IN (SELECT b

FROM t2);

SELECT STRAIGHT_JOIN *

FROM t3 JOIN v ON t3.x = v.a;

在5.7.6之前，优化器首先将视图v合并到外部查询中。当决定是否将IN子查询转换为半连接时，会注意到STRAIGHT_JOIN并拒绝转换。

--STRAIGHT_JOIN 类似带hint 的inner join 一样，提示左边表为驱动表，先读取

从5.7.6开始，优化器首先查看视图并将IN子查询转换为半连接，然后检查是否可以将视图合并到外部查询中。由于外部查询中的STRAIGHT_JOIN修饰符会阻止半连接，因此优化程序会拒绝合并，从而导致使用物化表评估派生表。

EXPLAIN输出中显示了半连接策略的使用，如下所示：

半连接表显示在外部选择。 EXPLAIN EXTENDED plus SHOW WARNINGS显示重写的查询，显示半连接结构。从这里你可以知道哪些表是从半连接中拉出来的。如果子查询被转换为半连接，您将看到子查询谓词消失，其表和WHERE子句被合并到外部查询连接列表和WHERE子句中。
临时表格用于Duplicate Weedout，在Extra列中由Start temporary和End temporary表示。如果表未被拉出并处于“Start temporary”和“End temporary ”范围的行的rowid 将在临时表中
Extra列中的FirstMatch（tbl_name）表示join快捷方式。
Extra 列中的LooseScan（m..n）表示使用LooseScan策略。 m和n是key part 编号。
用于materialization的临时表由具有select_type值为MATERIALIZED的行和具有<subqueryN>表值的行表示。

子查询物化中的子查询优化

优化器使用子查询实现作为一种策略，可以实现更高效的子查询处理

如果没有使用物化，则优化程序有时会将不相关的子查询重写为相关的子查询。例如，以下IN子查询是不相关的（where_condition只涉及t2而不是t1的列）：

SELECT * FROM t1

WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

优化器可能会将其重写为EXISTS相关的子查询：

SELECT * FROM t1

WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

使用临时表的子查询materialization避免了这样的重写，并且使得可以仅对外部查询的行只执行一次子查询。 materialization通过生成子查询结果作为临时表（通常在内存中）来加快查询的执行速度。 MySQL首次需要子查询结果时，将结果转化为临时表。随后的任何时候都需要结果，MySQL再次引用临时表。该表使用散列索引进行索引，以便快速且便宜地进行查找。该索引是唯一的，这使得表更小，因为它没有重复。

子查询实现尝试尽可能使用内存中的临时表，如果表变得太大，则会回退到虚拟磁盘存储。

子查询实现尝试在可能的情况下使用内存中的临时表，如果表过大，则回退到磁盘存储。

为了在MySQL中使用子查询materialization，materialization标志optimizer_switch系统变量必须打开。实体化然后适用于出现在任何位置（在选择列表中，WHERE，ON，GROUP BY，HAVING或ORDER BY）的子查询谓词，用于落入任何这些用例的谓词：

谓词有这种形式，当没有外部表达式oe_i或内部表达式ie_i是可空的。 N可以是1或更大。

(oe_1, oe_2, ..., oe_N) [NOT] IN (SELECT ie_1, i_2, ..., ie_N ...)

谓词有这种形式，当有一个单独外部表达式oe和内部表达式ie。表达式可以是空的。

oe [NOT] IN (SELECT ie ...)

谓词是IN或NOT IN，UNKNOWN（NULL）的结果与FALSE的结果具有相同的含义。

以下示例说明UNKNOWN和FALSE谓词评估的等价条件如何影响是否可以使用子查询实现。假设where_condition只涉及t2而不是t1的列，所以子查询是不相关的

这个查询是实体化的：

SELECT * FROM t1

WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

这里，IN谓词是否返回UNKNOWN或FALSE并不重要。无论哪种方式，来自t1的行都不包含在查询结果中。

将不使用子查询实现的示例是以下查询，其中t2.b是可以为空的列。

SELECT * FROM t1

WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2

WHERE where_condition);

使用EXPLAIN和查询可以给出一些优化器是否使用子查询实现的指示。与不使用materialization的查询执行相比，select_type可能会从“DEPENDENT SUBQUERY”更改为“SUBQUERY”。这表明，对于每个外部行将执行一次子查询，materialization仅使子查询执行一次。另外，对于EXPLAIN EXTENDED，由以下SHOW WARNINGS显示的文本将包括materialize和materialized-subquery。

优化派生表和视图引用

优化器可以处理派生表（FROM子句中的子查询）和使用两种策略查看引用：将派生表或视图合并到外部查询块中，或将派生表或视图实体化为内部临时表。

例子 1：

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

通过合并，该查询执行类似于：

SELECT * FROM t1;

例子2：

SELECT *

FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1

WHERE t1.f1 > 0;

通过合并，该查询执行类似于：

SELECT t1.*, t2.f1

FROM t1 JOIN t2 ON t1.f2=t2.f1

WHERE t1.f1 > 0;

在materialization中，derived_t1和derived_t2在各自的查询中被视为一个单独的表。

从MySQL 5.7.6开始，优化器以相同的方式处理派生表和视图引用：它尽可能地避免了不必要的materialization ,这使得使用生成更有效的执行计划的推下条件成为可能。在MySQL 5.7.6之前，派生表总是被materialized，而等价的视图引用有时被materialized，有时被合并。对等值查询的这种不一致的处理可能会导致性能问题：不必要的派生表materialization需要时间，并阻止优化器将条件推送到派生表。

如果合并会导致引用多于61个基表的外部查询块，则优化器会选materialization。

从MySQL 5.7.6开始，优化器可以更好地处理派生表中的ORDER BY子句的传播或对外部查询块的视图引用。如果外部查询未分组或聚合，则优化器将传播ORDER BY子句; 不指定DISTINCT，HAVING或ORDER BY; 并将此派生表或视图引用作为FROM子句中的唯一源。否则，优化器将忽略ORDER BY子句。在MySQL 5.7.6之前，优化器总是传播ORDER BY，即使它不相关或导致无效查询。

对于诸如DELETE或UPDATE这样的修改表的语句，对于MySQL 5.7.6之前的派生表使用合并策略可能导致ER_UPDATE_TABLE_USED错误：

mysql> DELETE FROM t1

-> WHERE id IN (SELECT id

-> FROM (SELECT t1.id

-> FROM t1 INNER JOIN t2 USING (id)

-> WHERE t2.status = 0) AS t);

ERROR 1093 (HY000): You can't specify target table 't1'

for update in FROM clause

将派生表合并到外部查询块中时会发生错误，导致从中选择并修改表的语句（实现不会导致问题，因为实际上它会将派生表转换为单独的表）。避免这个错误，在执行语句之前禁用optimizer_switch系统变量的derived_merge标志：

mysql> SET optimizer_switch = 'derived_merge=off';

derived_merge标志控制优化程序是否尝试将派生表和视图合并到外部查询块中，假定没有其他规则会阻止合并。默认情况下，该标志是on以启用合并。将该标志设置为off可防止合并并避免刚描述的错误。（其他解决方法包括在子查询中使用SELECT DISTINCT或LIMIT，尽管这些在实现中的效果不明确）。如果对于使用与子查询等效的表达式的视图引用发生ER_UPDATE_TABLE_USED错误，可以将ALGORITHM=TEMPTABLE添加到视图的定义来防止合并，并优先于当前的derived_merge值。

如果优化器为派生表选择实现策略，则按如下方式处理查询：

优化器延迟FROM子句中子查询的materialization，直到查询执行期间需要其内容。这提高了性能，因为延迟materialization可能导致完全不必这样做。考虑一个将FROM子句中的子查询结果连接到另一个表的查询：如果优化器首先处理另一个表并发现它不返回任何行，则连接不需要进一步执行，优化器可以完全跳过子查询的materialization。
在查询执行过程中，优化器可能会将索引添加到派生表中以加快从其中检索行。

考虑以下EXPLAIN语句，在SELECT查询的FROM子句中为其出现子查询：

EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;

优化器通过延迟它来避免materialization子查询，直到SELECT执行期间需要结果为止。在这种情况下，查询不被执行，所以结果是不需要的。

即使对于执行的查询，延迟子查询materialization也可以允许优化器完全避免materialization。发生这种情况时，执行materialization所需的时间会更快。请考虑以下查询，它将FROM子句中的子查询的结果连接到另一个表：

SELECT *

FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1

WHERE t1.f1 > 0;

如果优化过程t1首先和WHERE子句产生一个空的结果，则连接必须是空的，子查询不需要materialization

对于派生表需要实例化的情况，优化器可以通过向物化表添加索引来加速对结果的访问。如果这样的索引允许ref访问表，那么可以大大减少查询执行期间必须读取的数据量。考虑以下查询：

SELECT *

FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2

ON t1.f1=derived_t2.f1;

优化器在derived_t2上的列f1上构造一个索引，如果这样做会允许为最低成本的执行计划使用ref访问。在添加索引之后，优化器可以将物化的派生表与具有索引的常规表一样对待，并且从生成的索引类似地受益。索引创建的开销与没有索引的查询执行的开销相比可以忽略不计。如果引用访问会导致比其他访问方法更高的成本，则不会创建索引，优化器也不会丢失任何内容。

使用EXISTS策略优化子查询

某些优化适用于使用IN运算符来测试子查询结果的比较（或使用= ANY，这是等效的）。本节讨论这些优化，特别是关于NULL值存在的挑战。讨论的最后部分包含有关如何帮助优化器的建议。

考虑下面的子查询比较：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL会从外部到内部评估查询。也就是说，它首先获取外部表达式outer_expr的值，然后运行子查询并捕获它产生的行。

一个非常有用的优化是“inform”子查询，唯一感兴趣的行是内部表达式inner_expr等于outer_expr的那些。这是通过向子查询的WHERE子句中按下适当的等式来完成的。也就是说，比较转换为：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

转换之后，MySQL可以使用下推的等式来限制评估子查询时必须检查的行数。

更一般地说，将N值与返回N-值行的子查询进行比较将受到相同的转换。如果oe_i和ie_i表示相应的外部和内部表达式值，则该子查询比较：

(oe_1, ..., oe_N) IN

(SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

变为：

EXISTS (SELECT 1 FROM ... WHERE subquery_where

AND oe_1 = ie_1

AND ...

AND oe_N = ie_N)

--oracle的优化器不会因为写法的不同而评估不同，优化器比mysql更强大。

为简单起见，下面的讨论假定单一的一对外部和内部表达值。

刚刚描述的转换有其局限性。只有我们忽略了可能的NULL值才有效。也就是说，只要这两个条件都是true，“下推”策略就可以工作：

outer_expr and inner_expr 不能为空
您不需要区分NULL和FALSE子查询结果。（如果子查询是WHERE子句中的OR或AND表达式的一部分，MySQL假定你不关心。）

当这些条件中的任何一个或两个都不成立时，优化就更为复杂。

假设outer_expr被称为非NULL值，但子查询不会产生一个如outer_expr = inner_expr的行。那么 outer_expr IN（SELECT ...）的计算如下：

NULL，如果SELECT产生inner_expr为NULL的任何行
FALSE，如果SELECT只产生非空值或不产生任何东西

在这种情况下，使用outer_expr = inner_expr查找行的方法不再有效。有必要查找这样的行，但是如果找不到，还要查找inner_expr为NULL的行。粗略地说，子查询可以转换为：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND

(outer_expr=inner_expr OR inner_expr IS NULL))

需要评估额外的IS NULL条件是为什么MySQL有ref_or_null访问方法：

mysql> EXPLAIN

-> SELECT outer_expr IN (SELECT t2.maybe_null_key

-> FROM t2, t3 WHERE ...)

-> FROM t1;

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: t1

...

*************************** 2. row ***************************

id: 2

select_type: DEPENDENT SUBQUERY

table: t2

type: ref_or_null

possible_keys: maybe_null_key

key: maybe_null_key

key_len: 5

ref: func

rows: 2

Extra: Using where; Using index

...

unique_subquery和index_subquery子查询特定的访问方法也具有“or NULL”变体。但是，在MySQL 5.7.3之前，它们在EXPLAIN输出中是不可见的，所以必须使用EXPLAIN EXTENDED，然后是SHOW WARNINGS（注意在警告消息中检查NULL）：

mysql> EXPLAIN EXTENDED

-> SELECT outer_expr IN (SELECT maybe_null_key FROM t2) FROM t1\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: t1

...

*************************** 2. row ***************************

id: 2

select_type: DEPENDENT SUBQUERY

table: t2

type: index_subquery

possible_keys: maybe_null_key

key: maybe_null_key

key_len: 5

ref: func

rows: 2

Extra: Using index

mysql> SHOW WARNINGS\G

额外的OR ... IS NULL条件使得查询执行稍微复杂一些（并且子查询中的一些优化变得不适用），但通常这是可以容忍的。

当outer_expr可以为NULL时，情况会更糟糕。根据NULL的sql解释为“unknown value”，NULL IN（SELECT inner_expr ...）应该评估为：

NULL，如果SELECT产生任何行
FALSE，如果SELECT不产生行

为了正确评估，必须能够检查SELECT是否已经产生了任何行，所以outer_expr=inner_expr不能被下推到子查询中。这是一个问题，因为许多真是世界中的子查询变得非常缓慢，除非等式可以被下推。

实质上，根据outer_expr的值，必须有不同的方式来执行子查询。

优化器在速度上选择SQL合规性，因此它考虑到了outer_expr可能为NULL的可能性。

如果outer_expr为NULL，为了计算下面的表达式，有必要运行SELECT来确定它是否产生任何行：

NULL IN (SELECT inner_expr FROM ... WHERE subquery_where)

在这里运行原始的SELECT是必要的，没有任何前面提到的那种下推。

另一方面，当outer_expr不是NULL时，这个比较是绝对必要的：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

被转换为使用下推条件的这个表达式：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

--等式下推

没有这个转换，子查询将会很慢。为了解决是否推倒条件进入子查询的困境，条件被包含在“触发”功能中。因此，表达式如下：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

被转换为：

EXISTS (SELECT 1 FROM ... WHERE subquery_where

AND trigcond(outer_expr=inner_expr))

更一般地说，如果子查询比较基于几对外部和内部表达式，则转换将进行此比较：

(oe_1, ..., oe_N) IN (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

并将其转换为以下表达式：

EXISTS (SELECT 1 FROM ... WHERE subquery_where

AND trigcond(oe_1=ie_1)

AND ...

AND trigcond(oe_N=ie_N)

)

每个trigcond（X）是一个特殊的函数，它的计算结果如下：

当“链接的”外部表达式oe_i不为NULL时为X.
当“链接的”外部表达式oe_i为NULL时为TRUE.

注意：

这里的触发器函数不是create trigger创建的那种触发器

包装到trigcond（）函数中的等式不是查询优化器的first class谓词。大多数优化不能处理在查询执行时可能被打开和关闭的谓词，所以他们假设任何一个trigcond（X）是一个未知的函数并且忽略它。目前，这些优化可以使用触发的等式：

Reference optimizations：trigcond（X = Y [OR Y IS NULL]）可用于构造ref，eq_ref或ref_or_null表访问。
基于索引查找的子查询执行引擎：trigcond（X = Y）可用于构造unique_subquery 或 index_subquery 访问
表条件生成器：如果子查询是多个表的连接，则会尽快检查触发条件。

当优化器使用触发条件创建某种基于索引查找的访问（对于前面列表的前两项）时，必须针对被关闭条件的情况设置回退策略。这个回退策略总是相同的：做一个全表扫描。在EXPLAIN输出中，后备显示为额外列中的NULL键上的全面扫描：

mysql> EXPLAIN SELECT t1.col1,

-> t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G

*************************** 1. row ***************************

id: 1

select_type: PRIMARY

table: t1

...

*************************** 2. row ***************************

id: 2

select_type: DEPENDENT SUBQUERY

table: t2

type: index_subquery

possible_keys: key1

key: key1

key_len: 5

ref: func

rows: 2

Extra: Using where; Full scan on NULL key

如果运行EXPLAIN EXTENDED，然后显示SHOW WARNINGS，则可以看到触发条件

触发条件的使用有一些性能影响。现在NULL IN（SELECT ...）表达式可能会导致全表扫描（这是慢的），而以前没有。这是正确结果的效果（触发条件策略的目标是提高合规性而不是速度）。

对于多表子查询，NULL IN（SELECT ...）的执行将特别慢，因为连接优化器不针对外表达式为NULL的情况进行优化。它假定在左侧的NULL上进行子查询评估是非常罕见的，即使有统计数据表明否则。另一方面，如果外部表达式可能是NULL，但实际上从来没有，那么就没有性能问题。

为了帮助查询优化器更好地执行查询，请使用以下提示：

声明一个列为NOT NULL，如果它是真的。（这也通过简化列的条件测试来帮助优化器的其他方面。）
如果您不需要区分NULL和FALSE子查询结果，则可以轻松地避免执行缓慢的路径。替换看起来像这样的比较：

outer_expr IN (SELECT inner_expr FROM ...)

用这个表达式：

(outer_expr IS NOT NULL) AND (outer_expr IN (SELECT inner_expr FROM ...))

那么NULL IN（SELECT ...）永远不会被评估，因为一旦表达式结果清零 MySQL就停止评估AND部分。

subquery_materialization_cost_based可以控制子查询实现和IN-to-EXISTS子查询转换之间的选择。

ITPub博客

8.2 mysql 优化2

最新文章