查看“MySQL 优化：优化 SQL 语句：优化 SELECT 语句”的源代码

[[category:MySQL文档]]

== 关于 ==
查询以“SELECT”语句的形式执行数据库中的所有查找操作。
* 除了“SELECT”语句外，查询的调整技术还适用于：“CREATE TABLE...AS SELECT”，“INSERT INTO...SELECT”和“DELETE”语句中的“WHERE”子句。这些语句还有其他性能方面的考虑，因为它们将写操作与面向读取的查询操作结合在一起。
* NDB Cluster 支持 join 下推优化，将符合条件的 join 整体发送到 NDB Cluster 数据节点，在这些节点之间可以分布并并行执行。【？？？】


优化查询的主要注意事项是：
# 要使慢速“SELECT ... WHERE”查询更快，首先要检查的是是否可以添加'''index'''。在“WHERE”子句中使用的列上设置索引，以加快评估，过滤和最终检索结果的速度。为了避免浪费磁盘空间，请构造一小组索引，以加快应用程序中使用的许多相关查询的速度。
#: 对于使用“joins”和“foreign keys”之类的功能引用不同表的查询，索引尤为重要。您可以使用“'''EXPLAIN'''”语句来确定 SELECT 使用哪些索引。
# 隔离并调整查询中花费时间过多的任何部分，例如函数调用。根据查询的结构方式，可以对结果集中的每一行调用一次函数，甚至可以对表中的每一行调用一次函数，从而极大地提高了效率。
# 最小化查询中的'''全表扫描'''数量，尤其是对于大表。
# 通过定期使用“'''ANALYZE TABLE'''”语句来使表统计信息保持最新，因此优化器具有构造有效执行计划所需的信息。
# 了解每个表的'''存储引擎'''特定的调整技术，索引技术和配置参数。InnoDB 和 MyISAM 都有一套准则，用于启用和维持查询的高性能。
# 避免以难以理解的方式转换查询，尤其是在优化程序自动执行某些相同转换的情况下。【？】
# 如果使用基本准则之一不能轻松解决性能问题，请通过阅读“EXPLAIN”计划并调整'''索引，WHERE 子句，join 子句'''等来调查特定查询的内部详细信息。
#* （当您达到一定的专业水平时，阅读 EXPLAIN 计划可能是每个查询的第一步。）【！】
# 调整 MySQL 用于缓存的内存区域的大小和属性。通过有效地使用'''InnoDB buffer pool（缓冲池），MyISAM 键高速缓存和 MySQL 查询高速缓存'''，重复查询的运行速度更快，因为第二次及以后都从内存中检索结果。
# 即使对于使用缓存区域快速运行的查询，您也可能会进一步优化，以使它们需要更少的缓存，从而使您的应用程序更具可伸缩性。可伸缩性意味着您的应用程序可以处理更多的并发用户，更大的请求等，而不会导致性能大幅下降。
# 处理'''锁'''问题，其中其他会话同时访问表可能会影响查询速度。

== WHERE 子句优化【适用于“SELECT”、“DELETE”和“UPDATE”语句中的“WHERE”子句】==
您可能会试图重写查询以加快算术运算，同时牺牲可读性。
* 因为'''MySQL会自动进行类似的优化'''，所以通常可以避免这项工作，并以更易于理解和维护的形式保留查询。


MySQL 执行的一些优化如下：
# 删除不必要的括号：
#: <syntaxhighlight lang="mysql">
((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)
</syntaxhighlight>
# 常数折叠：
#: <syntaxhighlight lang="mysql">
(a<b AND b=c) AND a=5
-> b>5 AND b=c AND a=5
</syntaxhighlight>
# 常数条件移除：
#: <syntaxhighlight lang="mysql">
(b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6)
-> b=5 OR b=6
</syntaxhighlight>
# 索引使用的常量表达式仅计算一次。
# '''“COUNT(*)”：在没有“WHERE”单表上，是直接从 MyISAM 和 MEMORY 表的表信息中检索的。'''
#* 当其仅与一个表一起使用时，对于任何“NOT NULL”表达式也将执行此操作。
# 早期检测无效常量表达式。MySQL很快检测到一些“SELECT”语句是不可能的，并且不返回任何行。
# '''如果不使用“GROUP BY”或集合函数（“COUNT()”、“MIN()”等），“HAVING”将与“WHERE”合并'''。
# 对于联接中的每个表，构造一个'''更简单的“WHERE”'''来快速计算表的“WHERE”，并尽快跳过行。
# 在查询中的所有其他表之前，首先读取所有'''常量表'''。常量表可以是以下任意一个：【？？？】
## 空表或具有一行的表。
## 在“主键”或“唯一索引”上使用“WHERE”子句的表，其中所有索引部分都与常量表达式进行比较，并定义为“NOT NULL”。【？】
#: 以下所有表均用作常量表：
#: <syntaxhighlight lang="mysql">
SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
  WHERE t1.primary_key=1 AND t2.primary_key=t1.id;
</syntaxhighlight>
# 通过尝试所有可能的方法，找到用于联接表的最佳联接组合。'''如果“ORDER BY”和“GROUP BY”子句中的所有列都来自同一表，则在联接时优先使用该表。'''
# 如果有一个“ORDER BY”子句和另一个“GROUP BY”子句，或者“ORDER BY”或“GROUP BY”包含联接队列中第一个表以外的表中的列，则会创建一个临时表。【？？？】
# 如果使用“'''SQL_SMALL_RESULT'''”修饰符，则 MySQL 使用内存中的临时表。【？？？】
# 查询每个表索引，并使用'''最佳索引'''，除非优化程序认为使用全表扫描更有效。同时，使用扫描是基于最佳索引是否跨越了表的 30％以上，但是固定百分比不再决定使用索引还是扫描。现在，优化器更加复杂，其评估基于其他因素，例如表大小，行数和 I/O 块大小。
# 在输出每一行之前，将跳过与“HAVING”子句不匹配的行。


快速查询示例：
<syntaxhighlight lang="mysql">
SELECT COUNT(*) FROM tbl_name;

SELECT MIN(key_part1),MAX(key_part1) FROM tbl_name;

SELECT MAX(key_part2) FROM tbl_name
  WHERE key_part1=constant;

SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... LIMIT 10;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... LIMIT 10;
</syntaxhighlight>
假设索引列是数字，MySQL 仅使用索引树来解析以下查询：
<syntaxhighlight lang="mysql">
SELECT key_part1,key_part2 FROM tbl_name WHERE key_part1=val;

SELECT COUNT(*) FROM tbl_name
  WHERE key_part1=val1 AND key_part2=val2;

SELECT key_part2 FROM tbl_name GROUP BY key_part1;
</syntaxhighlight>
以下查询使用索引来按排序 Sequences 检索行，而无需单独的排序遍历：
<syntaxhighlight lang="mysql">
SELECT ... FROM tbl_name
  ORDER BY key_part1,key_part2,... ;

SELECT ... FROM tbl_name
  ORDER BY key_part1 DESC, key_part2 DESC, ... ;
</syntaxhighlight>

== 范围优化【？？？？？？】 ==
range访问方法使用单个索引来检索包含在一个或几个索引值间隔内的表行的子集。它可以用于单部分或多部份索引。

=== 单部分索引的范围访问方法【？？？】 ===
对于单部分索引，索引值间隔可以方便地由“WHERE”子句中的相应条件表示，表示为范围条件，而不是“间隔”。【？】

单部分索引的范围条件的定义如下：
# 对于 B树 和 HASH 索引，使用'''='''，'''<=>'''，'''IN()'''，'''IS NULL'''或'''不为空'''运算符时，将关键部分与常量值进行比较是一个范围条件。
# 此外，对于 B树 索引，使用'''>'''，'''<'''，'''>='''，'''<='''，'''BETWEEN'''，'''!='''或'''<>'''运算符时，将关键部分与常量值进行比较是一个范围条件；
#* 如果'''LIKE'''的参数为常量字符串，则'''进行 LIKE 比较不能以通配符开头'''。
# 对于所有索引类型，将多个范围条件与'''OR'''或'''AND'''组合在一起可形成范围条件。

前面的描述中的“常量值”表示以下之一：
# 查询字符串中的常量；
# 来自同一联接的常量或system表中的列；
# 不相关子查询的结果；
# 任何完全由上述类型的子表达式组成的表达式；


以下是“WHERE”子句中具有范围条件的查询示例：
<syntaxhighlight lang="mysql">
SELECT * FROM t1
  WHERE key_col > 1
  AND key_col < 10;

SELECT * FROM t1
  WHERE key_col = 1
  OR key_col IN (15,18,20);

SELECT * FROM t1
  WHERE key_col LIKE 'ab%'
  OR key_col BETWEEN 'bar' AND 'foo';
</syntaxhighlight>
* 在优化程序常数传播阶段，某些非常数值可以转换为常数。


MySQL 尝试从“WHERE”子句中为每个可能的索引'''提取范围条件'''。在提取过程中，删除了不能用于构建范围条件的条件，合并了产生重叠范围的条件，并删除了产生空范围的条件。【？？？】


示例：【？？？？？？？？？】<br/>
请考虑以下语句，其中 key1 是索引列，而 nonkey 没有索引：
<syntaxhighlight lang="mysql">
SELECT * FROM t1 WHERE
  (key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
  (key1 < 'bar' AND nonkey = 4) OR
  (key1 < 'uux' AND key1 > 'z');
</syntaxhighlight>
密钥 key1 的提取过程如下：
# 从原始的 WHERE 子句开始：
#: <syntaxhighlight lang="mysql">
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR
(key1 < 'bar' AND nonkey = 4) OR
(key1 < 'uux' AND key1 > 'z')
</syntaxhighlight>
# 删除“nonkey = 4”【B树索引“=”结果不为范围】和“key1 LIKE '%b'”【“LIKE”以通配符开始结果不为范围】，因为它们不能用于范围扫描。删除它们的正确方法是将它们替换为“TRUE”，这样我们在进行范围扫描时就不会丢失任何匹配的行。用 TRUE 替换它们会产生：
#: <syntaxhighlight lang="mysql">
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')
</syntaxhighlight>
# 折叠始终为 true 或 false 的条件：
#* (key1 LIKE 'abcde%' OR TRUE) 始终为真
#* (key1 < 'uux' AND key1 > 'z') 始终为假
# 用常量替换这些条件将产生：
#: <syntaxhighlight lang="mysql">
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
</syntaxhighlight>
# 删除不必要的 TRUE 和 FALSE 常量将产生：
#: <syntaxhighlight lang="mysql">
(key1 < 'abc') OR (key1 < 'bar')
</syntaxhighlight>
# 将重叠的间隔合并为一个会产生用于范围扫描的最终条件：
#: <syntaxhighlight lang="mysql">
(key1 < 'bar')
</syntaxhighlight>


一般而言(如前面的示例所示)，范围扫描所使用的条件比“WHERE”子句的限制要少。 MySQL 执行附加检查以过滤出 满足范围条件但不完整的“WHERE”子句的行。【？】

范围条件提取算法可以处理任意深度的嵌套 AND / OR 结构，并且其输出不取决于条件在 WHERE 子句中出现的顺序。

MySQL 不支持为空间索引的range访问方法合并多个范围。要解决此限制，可以将“UNION”与相同的“SELECT”语句一起使用，除了将每个空间谓词放在不同的“SELECT”中。

=== 多部分索引的范围访问方法【？？？】 ===
多部分索引的范围条件是单部分索引的范围条件的扩展。

Multipart 索引的范围条件是单部分索引的范围条件的扩展。Multipart 索引上的范围条件将索引行限制在一个或几个键 Tuples 间隔内。使用从索引开始的 Sequences，在一组键 Tuples 上定义键 Tuples 间隔。


例如，考虑定义为“key1(key_part1, key_part2, key_part3)”的 Multipart 索引，并按键 Sequences 列出以下一组键 Tuples：
<syntaxhighlight lang="mysql">
key_part1  key_part2  key_part3
  NULL       1          'abc'
  NULL       1          'xyz'
  NULL       2          'foo'
   1         1          'abc'
   1         1          'xyz'
   1         2          'abc'
   2         1          'aaa'
</syntaxhighlight>
条件“key_part1 = 1”定义了此间隔：
<syntaxhighlight lang="mysql">
(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)
</syntaxhighlight>
该间隔涵盖了先前数据集中的第 4，第 5 和第 6 个 Tuples，并且可以由范围访问方法使用。

相反，条件“key_part3 = 'abc'”并未定义单个间隔，并且不能被范围访问方法使用。


关于范围条件如何作用于 Multipart 索引：【？？？】
# 对于 HASH 索引，可以使用每个包含相同值的间隔。【？？？】
#: 这意味着只能针对以下形式的条件生成间隔：
#: <syntaxhighlight lang="mysql">
key_part1 cmp const1
AND key_part2 cmp const2
AND ...
AND key_partN cmp constN;
</syntaxhighlight>
#: 这里，const1，const2，…是常量，cmp 是 =，<=> 或 IS NULL 比较运算符之一，并且条件涵盖所有索引部分。（也就是说，存在 N 条件， N-part 索引的每个部分都有一个条件）例如，以下是由三部分组成的 HASH索引 的范围条件：
#: <syntaxhighlight lang="mysql">
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
</syntaxhighlight>
# 对于 B树 索引，间隔可用于与“AND”组合的条件，其中每个条件使用 =，<=>，IS NULL，>，<，>=，<=，!=，<>，BETWEEN 或 LIKE 'pattern' 将键部分与常数值进行比较（其中'pattern'不能以通配符开头）。只要可以确定包含所有与条件匹配的行的单个键 Tuples，就可以使用一个间隔（如果使用 <> 或 !=，则可以使用两个间隔)。【？？？】
#: 只要比较运算符是 =，<=> 或 IS NULL，优化器就会尝试使用其他关键部分来确定间隔。如果运算符是 >，<，>=，<=，!=，<>，BETWEEN 或 LIKE，则优化器将使用它，但不再考虑其他关键部分。对于以下表达式，优化器使用第一个比较中的 =。它还从第二次比较中使用 >=，但不考虑其他关键部分，并且不将第三次比较用于间隔构造：
#: <syntaxhighlight lang="mysql">
key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
</syntaxhighlight>
#: 单个间隔为：
#: <syntaxhighlight lang="mysql">
('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)
</syntaxhighlight>
#* 创建的间隔可能包含比初始条件更多的行。例如，前面的时间间隔包含不满足原始条件的值('foo', 11, 0)。
# 如果将覆盖间隔中包含的行集合的条件与“OR”组合，则它们将形成覆盖间隔中的并集内包含的行集合的条件。如果条件与“AND”组合，则它们将形成一个条件，该条件覆盖其间隔的交点内包含的一组行。例如，对于由两部分组成的索引的这种情况：
#: <syntaxhighlight lang="mysql">
(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
</syntaxhighlight>
#: 间隔为：
#: <syntaxhighlight lang="mysql">
(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)
</syntaxhighlight>
#: 在此示例中，第一行的间隔使用一个关键部分作为左边界，使用两个关键部分作为右边界。第二行的间隔仅使用一个关键部分。 EXPLAIN 输出中的 key_len 列表示所使用的键前缀的最大长度。


在某些情况下，key_len 可能表明已使用了关键部件，但这可能不是您期望的。【？？？】
: 假设 key_part1 和 key_part2 可以是 NULL。然后 key_len 列显示以下条件的两个关键 Component 长度：
: <syntaxhighlight lang="mysql">
key_part1 >= 1 AND key_part2 < 2
</syntaxhighlight>
: 但是，实际上，条件已转换为：
: <syntaxhighlight lang="mysql">
key_part1 >= 1 AND key_part2 IS NOT NULL
</syntaxhighlight>

=== 多值比较的等距范围优化【？？？】 ===
考虑以下表达式，其中“col_name”是索引列：
<syntaxhighlight lang="mysql">
col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN
</syntaxhighlight>
如果 col_name 等于多个值中的任何一个，则每个表达式为 true。这些比较是相等范围比较（其中“范围”是单个值）。优化器估算读取相等行以进行相等范围比较的成本，如下所示：
# 如果 col_name 上有唯一索引，则每个范围的行估计为 1，因为最多一行可以具有给定值。
# 否则，col_name 上的任何索引都是唯一的，优化器可以使用对索引或索引统计数据的深入估算来估计每个范围的行数。【？？？】


使用'''索引下潜'''时，优化器在范围的每个末端进行下潜，并将范围中的行数用作估计值。例如，表达式“col_name IN (10, 20, 30)”具有三个相等范围，并且优化程序对每个范围进行两次下潜以生成行估计。每对下潜都会得出具有给定值的行数的估计值。【？？？】
* 索引下潜可提供准确的行估计，但是随着表达式中比较值的数量增加，优化器将花费更长的时间来生成行估计。使用索引统计信息的准确性不如使用索引统计法准确，但允许对大型值列表进行更快的行估计。


'''eq_range_index_dive_limit'''系统变量使您可以配置优化器从一种行估计策略切换到另一种行估计策略的值的数量：
# 要允许索引下潜用于最多 N 个相等范围的比较，请将 eq_range_index_dive_limit 设置为 N + 1。
# 要禁用统计信息的使用，并且无论 N 始终使用索引潜水，请将 eq_range_index_dive_limit 设置为 0。


要更新表索引统计信息以获得最佳估计值，请使用“'''ANALYZE TABLE'''”。


即使在本应使用索引下潜的条件下，对于满足所有这些条件的查询也将跳过它们：
# 存在单索引“FORCE INDEX”索引提示。其思想是，如果强制使用索引，那么执行下潜索引的额外开销将无济于事。
# 索引不是唯一的，不是“FULLTEXT”索引。
# 没有子查询。
# 没有“DISTINCT”，“GROUP BY”或“ORDER BY”子句。
这些下潜跳过条件仅适用于单表查询。对于多表查询（联接），不会跳过索引下潜。

=== 行构造函数表达式的范围优化【？？？】 ===
优化程序可以将范围扫描访问方法应用于以下形式的查询：
'''<syntaxhighlight lang="mysql">
SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));
</syntaxhighlight>'''
* 以前，要使用范围扫描，必须将查询编写为：
*: <syntaxhighlight lang="mysql">
SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );
</syntaxhighlight>


为了使优化器使用范围扫描，查询必须满足以下条件：
# '''仅使用“IN()”谓词，而不使用“NOT IN()”'''。
# 在“IN()”谓词的左侧，行构造函数仅包含列引用。
# 在“IN()”谓词的右侧，行构造函数仅包含运行时常量，这些常量是在执行期间绑定到常量的 Literals 或本地列引用。
# 在“IN()”谓词的右侧，有多个行构造器。

=== 限制内存用于范围优化【？？？】 ===
要控制范围优化器可用的内存，请使用 '''range_optimizer_max_mem_size''' 系统变量：
# 值 0 表示“无限制”。
# 值大于 0 时，优化程序将在考虑范围访问方法时跟踪消耗的内存。如果将要超出指定的限制，则将放弃范围访问方法，而改为考虑其他方法，包括全表扫描。这可能不太理想。如果发生这种情况，则会发生以下警告（其中 N 是当前的 range_optimizer_max_mem_size 值）：
#: <syntaxhighlight lang="mysql">
Warning    3170    Memory capacity of N bytes for
                   'range_optimizer_max_mem_size' exceeded. Range
                   optimization was not done for this query.
</syntaxhighlight>
* 对于“UPDATE”和“DELETE”语句，如果优化器退回到全表扫描并且启用了 sql_safe_updates 系统变量，则会发生错误而不是警告，因为实际上，没有键用于确定要修改的行。
* 对于超出可用范围优化内存的单个查询，并且对于该查询，优化器后退到次优计划，增大 range_optimizer_max_mem_size 值可以提高性能。


若要估计处理范围表达式所需的内存量，请使用以下准则：【？】
# 对于如下所示的简单查询，其中存在一个用于范围访问方法的候选键，每个与“'''OR'''”谓词的组合大约使用 230 个字节：
#: <syntaxhighlight lang="mysql">
SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
</syntaxhighlight>
# 同样，对于以下查询，每个与“'''AND'''”谓词的组合大约使用 125 个字节：
#: <syntaxhighlight lang="mysql">
SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;
</syntaxhighlight>
# 对于带有“'''IN()'''”谓词的查询：
#: <syntaxhighlight lang="mysql">
SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
</syntaxhighlight>
#* “IN()”列表中的每个 Literals 值都算作与“OR”组合的谓词。如果有两个“IN()”列表，则谓词与“OR”组合的数量是每个列表中 Literals 值数量的乘积。因此，在前一种情况下与OR组合的谓词数为“M × N”。
#* 在 5.7.11 之前，每个谓词与“OR”相结合的字节数更高，大约为 700 个字节。

== '''索引合并'''优化 ==
索引合并访问方法'''检索具有多个范围扫描的行，并将其结果合并到一个范围扫描中'''。
* 此访问方法仅合并来自'''单表'''的索引扫描，而不合并跨多个表的扫描。
* 合并可以生成其基础扫描的并集、交集或交集的并集。


可能使用索引合并的示例查询：【？？？】
<syntaxhighlight lang="mysql">
SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20;

SELECT * FROM tbl_name
  WHERE (key1 = 10 OR key2 = 20) AND non_key = 30;

SELECT * FROM t1, t2
  WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%')
  AND t2.key1 = t1.some_col;

SELECT * FROM t1, t2
  WHERE t1.key1 = 1
  AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);
</syntaxhighlight>


'''Note：'''<br/>
索引合并优化算法的已知限制：
# 如果您的查询有一个带有深度“AND”/“OR”嵌套的复杂“WHERE”子句，并且 MySQL 没有选择最佳方案，请尝试使用以下标识转换分发术语：【？】
#: <syntaxhighlight lang="mysql">
(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)
</syntaxhighlight>
# 索引合并不适用于全文索引。


在 '''EXPLAIN''' 输出中，索引合并方法在“type”列中显示为“index_merge”。 在这种情况下，“key”列包含使用的索引列表，而“key_len”包含这些索引的最长键部分的列表。
: 索引合并访问方法有几个算法，这些算法显示在 EXPLAIN 输出中的“Extra”字段：
* Using intersect(...)
* Using union(...)
* Using sort_union(...)
索引合并的使用取决于“optimizer_switch”系统变量的“index_merge”，“index_merge_intersection”，“index_merge_union”和“index_merge_sort_union”标志的值。
: 默认情况下，所有这些标志均为 on。要仅启用某些算法，请将 index_merge 设置为 off，并仅启用应允许的其他算法。

=== 索引合并“交叉”访问算法 ===
当表的“WHERE”子句转换为不同键上的多个范围条件并用“'''AND'''”组合在一起，并且每个条件是以下之一时，此访问算法适用：
# 这种形式的 N 部分表达式，其中索引正好有 N 部分（即，所有索引部分都包含在内）：
#: <syntaxhighlight lang="mysql">
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
</syntaxhighlight>
# InnoDB 表主键上的任何范围条件。
示例：
<syntaxhighlight lang="mysql">
SELECT * FROM innodb_table
  WHERE primary_key < 10 AND key_col1 = 20;

SELECT * FROM tbl_name
  WHERE key1_part1 = 1 AND key1_part2 = 2 AND key2 = 2;
</syntaxhighlight>


索引合并交集算法对所有使用的索引执行'''同时扫描'''，并生成从合并索引扫描接收到的行序列的'''交集'''。

# 如果查询中使用的所有列都被使用的索引覆盖，则不会检索完整的表行（在这种情况下，“EXPLAIN”在“Extra”字段输出中包含“Using index”）。这是此类查询的示例：
#: <syntaxhighlight lang="mysql">
SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;
</syntaxhighlight>
# 如果使用的索引未覆盖查询中使用的所有列，则仅在满足所有使用的键的范围条件时才检索完整行。【？】
如果合并的条件之一是 InnoDB 表的主键上的条件，则该条件不用于行检索，而是用于过滤出使用其他条件检索的行。【？】

=== 索引合并“联合”访问算法 ===
（似于索引“合并”交集算法的标准）<br/>
当表的“WHERE”子句转换为不同键上的多个范围条件并用“'''OR'''”组合在一起，并且每个条件是以下条件之一时，该算法适用：
# 这种形式的 N 部分表达式，其中索引正好有 N 部分（即，所有索引部分都包含在内）：
#: <syntaxhighlight lang="mysql">
key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN
</syntaxhighlight>
# InnoDB 表主键上的任何范围条件。
# 索引“合并”交集算法适用的条件。
示例：
<syntaxhighlight lang="mysql">
SELECT * FROM t1
  WHERE key1 = 1 OR key2 = 2 OR key3 = 3;

SELECT * FROM innodb_table
  WHERE (key1 = 1 AND key2 = 2)
     OR (key3 = 'foo' AND key4 = 'bar') AND key5 = 5;
</syntaxhighlight>

=== 索引合并“排序联合”访问算法 ===
当“WHERE”子句转换为“'''OR'''”组合的多个范围条件，但不适用于索引合并“联合”算法时，此访问算法适用。
示例：
<syntaxhighlight lang="mysql">
SELECT * FROM tbl_name
  WHERE key_col1 < 10 OR key_col2 < 20;

SELECT * FROM tbl_name
  WHERE (key_col1 > 10 OR key_col2 = 20) AND nonkey_col = 30;
</syntaxhighlight>


“排序联合”算法和“联合”算法的区别在于：“排序联合”算法必须首先获取所有行的行 id，并在返回任何行之前对它们进行排序。【？？？】

== 引擎条件“下推”优化【“非索引列”和“常量”之间直接比较】 ==
这种优化提高了'''“非索引列”和“常量”之间直接比较'''的效率。在这种情况下，条件将“推入”存储引擎进行评估。
* 此优化只能由'''NDB'''存储引擎使用。


对于 NDB Cluster，这种优化可以消除在集群的数据节点和发出查询的 MySQL 服务器之间通过网络发送不匹配行的需要。并且相对于未使用条件下推的案例，可以提高 5 到 10 倍的查询速度。【！】


示例：<br/>
假设 NDB 群集表定义如下：
<syntaxhighlight lang="mysql">
CREATE TABLE t1 (
    a INT,
    b INT,
    KEY(a)
) ENGINE=NDB;
</syntaxhighlight>
条件下推可以用于查询，例如此处显示的查询，其中包括'''非索引列和常量之间的比较'''：【包括使用“'''>'''”或“'''<'''”运算符】
<syntaxhighlight lang="mysql">
SELECT a, b FROM t1 WHERE b = 10;
SELECT a, b FROM t1 WHERE a < 2;
</syntaxhighlight>
在'''EXPLAIN'''的输出中可以看到条件下推的使用：
<syntaxhighlight lang="mysql" highlight="6,12">
mysql> EXPLAIN SELECT a,b FROM t1 WHERE b = 10\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 10
        Extra: Using where with pushed condition
</syntaxhighlight>
<syntaxhighlight lang="mysql" highlight="6,12">
mysql> EXPLAIN SELECT a, b FROM t1 WHERE a < 2\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 2
        Extra: Using where with pushed condition
</syntaxhighlight>


但条件下推与以下两个查询'''不能一起使用'''：【！！！】
<syntaxhighlight lang="mysql">
SELECT a,b FROM t1 WHERE a = 10;
SELECT a,b FROM t1 WHERE b + 1 = 10;
</syntaxhighlight>
# '''列上存在索引'''；【索引访问方法将更有效，因此将优先于条件下推】
#: <syntaxhighlight lang="mysql">
SELECT a,b FROM t1 WHERE a = 10;
</syntaxhighlight>
# '''涉及的非索引列是间接的'''；
#: <syntaxhighlight lang="mysql">
SELECT a,b FROM t1 WHERE b + 1 = 10;
</syntaxhighlight>


条件下推支持的其他比较包括：
# column [NOT] '''LIKE''' pattern
#* pattern 必须是包含要匹配的模式的字符串 Literals；
# column '''IS''' [NOT] '''NULL'''
# column '''IN''' (value_list)
#* value_list 中的每个项目都必须是恒定的 Literals 值。
# column '''BETWEEN''' constant1 '''AND''' constant2
#* constant1 和 constant2 必须分别是一个常量 Literals 值。
在上述列表中的所有情况下，都有可能将条件转换为列与常量之间的一个或多个直接比较的形式。


默认情况下，引擎条件下推处于启用状态。要在服务器启动时禁用它，请设置“optimizer_switch”系统变量。例如，在“my.cnf”文件中，使用以下几行：
<syntaxhighlight lang="xml">
[mysqld]
optimizer_switch=engine_condition_pushdown=off
</syntaxhighlight>
或，在运行时，禁用条件下推，如下所示：
<syntaxhighlight lang="mysql">
SET optimizer_switch='engine_condition_pushdown=off';
</syntaxhighlight>


引擎条件下推的'''限制'''：
# 条件下推仅受'''NDB'''存储引擎支持。
# 列只能与常量进行比较；但是，这包括计算结果为常数的表达式。
# 比较中使用的列不能是任何“BLOB”或“TEXT”类型。此排除范围也扩展到“JSON”，“BIT”和“ENUM”列。
# 要与列进行比较的字符串值必须使用与列相同的排序规则。
# 不直接支持联接。涉及多表的条件将在可能的情况下分别推送。使用扩展的“EXPLAIN”输出来确定实际推低哪些条件。

== 索引条件“下推”优化【将部分“WHERE”条件下推到存储引擎】 ==
索引条件下推（'''ICP'''）是针对 MySQL 使用索引从表中检索行的情况的优化。
# 如果没有 ICP，则存储引擎将遍历索引以在基表中定位行，并将其返回给 MySQL 服务器，该 MySQL 服务器将评估这些行的“WHERE”条件。【“存储引擎”：定位查找】
#* 不使用“索引条件下推”的情况下，如何进行索引扫描：
#*# 获取下一行，首先读取索引元组，然后使用索引元组查找并读取整个表行。
#*# 测试适用于此表的“WHERE”条件的一部分。根据测试结果接受或拒绝该行。
# 启用 ICP 后，如果“WHERE”条件的某些部分'''可以仅使用索引中的列进行评估'''，则 MySQL 服务器会将这部分“WHERE”条件下推到存储引擎。然后，存储引擎通过使用索引项来评估推送的索引条件，并且只有在满足此条件的情况下，才从表中读取行。 【“存储引擎”：先评估，再定位查找】
#* 使用“索引条件下推”的情况下，如何进行索引扫描：
#*# 获取下一行的索引元组（而不是整个表行）。
#*# 测试适用于此表、可仅使用索引列检查的“WHERE”条件部分。如果不满足条件，则转到下一行的索引元组。
#*# 如果满足条件，请使用索引元组来定位和读取整个表行。
#*# 测试适用于此表的“WHERE”条件的其余部分。根据测试结果接受或拒绝行。
#* 减少了：
#*# 存储引擎必须访问基表的次数；
#*# MySQL 服务器必须访问存储引擎的次数；


索引条件下推优化的适用性取决于以下条件：
# 当需要访问完整表行时，ICP 用于“range”，“ref”，“eq_ref”和“ref_or_null”访问方法。
# ICP 可用于 InnoDB 和 MyISAM 表，包括分区的 InnoDB 和 MyISAM 表。
# 对于 InnoDB 表，ICP 仅用于'''辅助索引'''。
#* ICP 的目标是减少全行读取的次数，从而减少 I/O 操作。对于 InnoDB 聚集索引，完整的记录已被读取到 InnoDB 缓冲区中。在这种情况下使用 ICP 不会减少 I/O。
# 在虚拟生成的列上创建的二级索引不支持 ICP。 InnoDB 支持虚拟生成的列上的二级索引。
# '''引用子查询的条件不能下推'''。
# 涉及存储函数的条件不能下推。存储引擎无法调用存储的函数。
# 触发条件不能下推。


* 当使用“索引条件下推”时，“'''EXPLAIN'''”输出在“Extra”列中显示“'''Using index condition'''”。 
** 它不会显示“Using index”，因为在必须读取整个表行时，该方法不适用。
* 默认情况下，索引条件下推处于启用状态。可以通过使用“optimizer_switch”系统变量设置“index_condition_pushdown”标志来进行控制：
*: <syntaxhighlight lang="mysql">
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';
</syntaxhighlight>


'''示例：'''<br/>
假设一个表包含有关人员及其地址的信息，并且该表的索引定义为 INDEX (zipcode, lastname, firstname)。如果我们知道一个人的 zipcode 值，但不确定姓氏，可以这样搜索：
<syntaxhighlight lang="mysql">
SELECT * FROM people
  WHERE zipcode='95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%';
</syntaxhighlight>
MySQL可以使用索引来扫描“zipcode='95054'”的人。
# 第二部分（lastname，如“%etrunia%”）不能用于限制必须扫描的行数【不使用索引】，因此如果没有“索引条件”下推，此查询必须检索“zipcode='95054'”的所有人的完整表行。
# 通过索引条件下推，MySQL '''在读取整个表行之前检查“lastname LIKE '%etrunia%'”部分。这避免了读取与匹配“zipcode”条件但不匹配“lastname”条件的索引元组相对应的完整行'''。

== “'''嵌套循环连接'''”算法 ==
MySQL 使用嵌套循环算法或其上的变体在表之间执行联接。

=== “嵌套循环连接”算法（NLJ） ===
一个简单的嵌套循环联接（NLJ）算法：一次从一个循环中的第一个表中读取行，然后将每一行传递给一个嵌套循环，该循环处理联接中的下一个表。重复此过程的次数与要连接的表的次数相同。


'''示例：'''<br/>
假设要使用以下联接类型执行三个表 t1，t2 和 t3 之间的联接：【？】
<syntaxhighlight lang="mysql">
Table   Join Type
t1      range
t2      ref
t3      ALL
</syntaxhighlight>
如果使用简单的 NLJ 算法，则按以下方式处理联接：
<syntaxhighlight lang="mysql">
for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}
</syntaxhighlight>

因为 NLJ 算法一次将行从外循环传递到内循环，所以它通常会'''多次读取在内循环中处理的表'''。

=== “块嵌套循环连接”算法（BNL） ===
块嵌套循环（BNL）连接算法：'''使用外部循环中读取的行的缓冲，来减少必须读取内部循环中的表的次数'''。
: 例如，如果将 10 行读入一个缓冲区并将该缓冲区传递给下一个内部循环，则可以将内部循环中读取的每一行与缓冲区中的所有10行进行比较。这将使必须读取内部表的次数减少一个数量级。


MySQL 连接缓冲具有以下 Feature：
# 当联接的类型为“ALL”或“index”（换句话说，当无法使用任何可能的键，并且分别对数据行或索引行进行完全扫描时）或“range”时，可以使用联接缓冲。【？？？】
#* 缓冲的使用也适用于外部联接。
# 连接缓冲区永远不会为第一个非恒定表分配，即使其类型为“ALL”或“index”。
# 联接中只有感兴趣的列存储在其联接缓冲区中，而不是整个行。
# join_buffer_size 系统变量确定用于处理查询的每个连接缓冲区的大小。
# 为每个可以缓冲的连接分配一个缓冲区，因此可以使用多个连接缓冲区来处理给定查询。
# '''在执行连接之前分配一个连接缓冲区，并在查询完成后释放连接缓冲区'''。


'''示例：'''<br/>
对于先前为 NLJ 算法描述的示例连接（不带缓冲），使用连接缓冲按如下方式进行连接：
<syntaxhighlight lang="mysql" highlight="">
for each row in t1 matching range {
  for each row in t2 matching reference key {
    store used columns from t1, t2 in join buffer
    if buffer is full {
      for each row in t3 {
        for each t1, t2 combination in join buffer {
          if row satisfies join conditions, send to client
        }
      }
      empty join buffer
    }
  }
}

if buffer is not empty {
  for each row in t3 {
    for each t1, t2 combination in join buffer {
      if row satisfies join conditions, send to client
    }
  }
}
</syntaxhighlight>
如果 S 是连接缓冲区中每个已存储的 t1，t2 组合的大小，并且 C 是缓冲区中的组合数目，则对表 t3 进行扫描的次数为：
<syntaxhighlight lang="mysql">
(S * C)/join_buffer_size + 1
</syntaxhighlight>
t3 的扫描次数随着 join_buffer_size 的值增加而减少，直到 join_buffer_size 足够大以容纳所有先前的行组合为止。那时，通过增大它无法获得任何速度。

== 嵌套联接优化 ==

== Outer Join 优化【？？？】 ==
外部联接包括“'''LEFT JOIN'''”和“'''RIGHT JOIN'''”。


MySQL 实现了“A LEFT JOIN B join_specification”（左连接规范），如下所示：【在说屁】
# 表 B 设置为取决于表 A 以及 A 所依赖的所有表。
# 表 A 设置为依赖于“LEFT JOIN”条件中使用的所有表（除 B 外）。
# “LEFT JOIN”条件用于决定如何从表 B 中检索行。（换句话说，不使用“WHERE”子句中的任何条件）
# 执行所有标准的连接优化，不同之处在于'''始终在 表所依赖的所有表 之后读取该表'''。
#* 如果存在循环依赖关系，则会发生错误。
# 执行所有标准的“WHERE”优化。
# 如果 A 中存在与“WHERE”子句匹配的行，但 B 中没有与“ON”条件匹配的行，则会生成多余的 B 行，并将所有列都设置为 NULL。【？】
# 如果您使用“LEFT JOIN”查找某个表中不存在的行，并进行以下测试：在“WHERE”中使用“col_name IS NULL”，而 col_name 是声明为 NOT NULL 的列，则在找到符合“LEFT JOIN”条件的一行之后 MySQL 停止搜索更多行（对于特定行组合键）。【？】
* “RIGHT JOIN”的实现类似于“LEFT JOIN”的实现，但表角色相反。


对于“LEFT JOIN”，'''如果“WHERE”条件对于生成的“NULL”行始终为 false，则“LEFT JOIN”更改为内部联接'''。【？】
: 例如，如果 t2.column1 是 NULL，则在以下查询中 WHERE 子句为 false：
:<syntaxhighlight lang="mysql">
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
</syntaxhighlight>
:因此，将查询转换为内部联接是安全的：
:<syntaxhighlight lang="mysql">
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
</syntaxhighlight>


现在，优化程序可以在表 t1 之前使用表 t2，如果这样做会导致更好的查询计划。
* 要提供有关表连接顺序的提示，请使用“'''STRAIGHT_JOIN'''”。但是，STRAIGHT_JOIN 可能会禁止使用索引，因为它禁用了半联接转换。【'''“STRAIGHT_JOIN”强制优化器按照“FROM”子句中列出的顺序连接表'''】

== Outer Join 简化 ==

== 多范围读取优化【？】 ==
问题：当表较大且未存储在存储引擎的高速缓存中时，在辅助索引上使用范围扫描来读取行会导致对表的许多随机磁盘访问。

通过磁盘扫描“多范围读取”（Multi-Range Read，'''MRR'''）优化：
# MySQL 尝试通过首先仅扫描索引并收集相关行的键来减少用于范围扫描的随机磁盘访问次数。
# 然后对键进行排序，最后使用主键的顺序从基表中检索行。
磁盘扫描 MRR 的动机是减少随机磁盘访问的次数，并对基表数据进行更有序的扫描。


多范围读取优化具有以下优点：
# MRR 使基于索引元组的数据行可以顺序访问，而不是以随机顺序访问。服务器获取一组满足查询条件的索引元组，并根据数据行 ID 顺序对它们进行排序，然后使用排序后的元组按顺序检索数据行。这使得数据访问更加高效且成本更低。
# 对于需要通过索引元组访问数据行的操作（例如范围索引扫描和使用索引作为联接属性的等联接），MRR 支持对键访问请求的批处理。 MRR 在一系列索引范围内进行迭代以获得合格的索引元组。随着这些结果的累积，它们将用于访问相应的数据行，而在开始读取数据行之前不必获取所有索引元组。


在虚拟生成的列上创建的二级索引不支持 MRR 优化。【InnoDB 支持虚拟生成的列上的二级索引】


以下方案说明了 MRR 优化何时可以发挥优势：
# 方案 A：MRR 可用于 InnoDB 和 MyISAM 表，以进行索引范围扫描和等联接操作。
## 索引元组的一部分累积在缓冲区中。
## 缓冲区中的元组按其数据行 ID 排序。
## 根据排序的索引元组序列访问数据行。
# 方案 B：MRR 可用于 '''NDB''' 表以进行多范围索引扫描，或在通过属性执行均等联接时使用。【？？？】
## 一部分范围（可能是单键范围）累积在提交查询的中心节点上的缓冲区中。【？】
## 范围被发送到访问数据行的执行节点。【？】
## 被访问的行被打包到程序包中并发送回中心节点。【？】
## 收到的带有数据行的数据包将放置在缓冲区中。【？】
## 从缓冲区读取数据行。【？】


使用 MRR 时，“EXPLAIN”输出中的“Extra”列显示“Using MRR”。


NOTE：
* 如果不需要访问完整表行以产生查询结果，则 InnoDB 和 MyISAM 不使用 MRR。
*: 如果可以完全基于索引元组中的信息（通过covering index）产生结果，则这种情况 MRR 没有任何好处。
* 两个“optimizer_switch”系统变量标志提供了使用 MRR 优化的接口：'''mrr'''标志控制是否启用 MRR。
*: 如果启用了mrr(on)，则'''mrr_cost_based'''标志控制优化器是尝试在使用还是不使用 MRR（on）之间做出基于成本的选择，还是在可能的情况下（off）使用 MRR。
** 默认情况下，mrr 是 on，mrr_cost_based 是 on。
* 对于 MRR，存储引擎使用“read_rnd_buffer_size”系统变量的值作为可为其缓冲区分配的内存大小。引擎最多使用 read_rnd_buffer_size 个字节，并确定一次处理要处理的范围数。

== 阻止嵌套循环和批量key访问联接 ==

== 条件过滤【？？？】 ==
在联接处理中，'''前缀行'''是“从联接中的一个表传递到下一个表的那些行”。通常，优化程序会尝试在连接顺序的早期放置前缀计数较低的表，以防止行组合的数量迅速增加。
* 在某种程度上，优化器可以使用有关从一个表中选择并传递到下一个表的行的条件的信息，它可以更准确地计算行估计并选择最佳执行计划。
关于“'''条件过滤'''”：
# 在没有“条件过滤”的情况下，表的前缀行计数基于：“WHERE”子句根据“优化器选择的访问方法”所选择的估计行数。【？】
# 通过“条件过滤”，优化器可以使用“WHERE”子句中访问方法未考虑的其他相关条件，从而改善其前缀行数估计。【？】
#: 例如，即使可以使用基于索引的访问方法从联接中的当前表中选择行，“WHERE”子句中的表也可能有其他条件，可以筛选（进一步限制）传递到下一个表的限定行的估值。【？？】


仅在以下情况下，'''条件'''才有助于过滤估计：【？？？】
# 它引用当前表。
# 它取决于连接序列中一个或多个常量值。
# 访问方法尚未考虑它。【？？？】


在“'''EXPLAIN'''”输出中：“rows”列指示所选访问方法的行估计，而“filtered”列反映条件过滤的效果。 filtered值表示为百分比。最大值为 100，表示没有行过滤发生。值从 100 减小表示过滤量增加。


前缀行数（估计从当前表通过联接传递到下一个表的行数）是 rows 和 filtered 值的乘积。即，前缀行数是估计的行数，该估计的行数由于估计的滤波效果而减少。
: 例如，如果 rows 为 1000 且 filtered 为 20％，则条件过滤会将估算的行数 1000 减少为前缀行数 1000×20％= 1000×0.2 = 200.


'''示例：'''【？？？？？？】<br/>
考虑以下查询：
<syntaxhighlight lang="mysql">
SELECT *
  FROM employee JOIN department ON employee.dept_no = department.dept_no
  WHERE employee.first_name = 'John'
  AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01';
</syntaxhighlight>
假设数据集具有以下 Feature：
# employee 表具有 1024 行。
# department 表有 12 行。
# 两个表在 dept_no 上都有一个索引。
# employee 表在 first_name 上具有索引。
# 8 个行在 employee.first_name 上满足此条件：
#: <syntaxhighlight lang="mysql">
employee.first_name = 'John'
</syntaxhighlight>
# 150 个行在employee.hire_date上满足此条件：
#: <syntaxhighlight lang="mysql">
employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'
</syntaxhighlight>
# 1 行满足以下两个条件：
#: <syntaxhighlight lang="mysql">
employee.first_name = 'John'
AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'
</syntaxhighlight>
没有条件过滤，EXPLAIN 会产生如下输出：
<syntaxhighlight lang="mysql">
+----+------------+--------+------------------+---------+---------+------+----------+
| id | table      | type   | possible_keys    | key     | ref     | rows | filtered |
+----+------------+--------+------------------+---------+---------+------+----------+
| 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 100.00   |
| 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   |
+----+------------+--------+------------------+---------+---------+------+----------+
</syntaxhighlight>
: 对于employee，在name索引上的访问方法将拾取与名称'John'匹配的 8 行。没有进行任何过滤(filtered为 100％)，因此所有行都是下一张表的前缀行：前缀行计数为 rows×filtered = 8×100％= 8。
通过条件过滤，优化器还考虑了 WHERE 子句中的条件，而访问方法并未考虑这些条件。在这种情况下，优化器使用'''试探法'''【？？？】来估计 employee.hire_date 上的 BETWEEN 条件的过滤效果为 16.31％。结果，EXPLAIN 产生如下输出：
<syntaxhighlight lang="mysql">
+----+------------+--------+------------------+---------+---------+------+----------+
| id | table      | type   | possible_keys    | key     | ref     | rows | filtered |
+----+------------+--------+------------------+---------+---------+------+----------+
| 1  | employee   | ref    | name,h_date,dept | name    | const   | 8    | 16.31    |
| 1  | department | eq_ref | PRIMARY          | PRIMARY | dept_no | 1    | 100.00   |
+----+------------+--------+------------------+---------+---------+------+----------+
</syntaxhighlight>
: 现在，前缀行计数为rows×filtered = 8×16.31％= 1.3，它更紧密地反映了实际数据集。


通常，优化器不会为最后一个联接表计算条件过滤效果（前缀行数减少），因为没有下一个表可以将行传递给该表。 EXPLAIN 发生异常：为了提供更多信息，将为所有联接的表（包括最后一个表）计算过滤效果。


要控制优化器是否考虑其他过滤条件，请使用“'''optimizer_switch'''”系统变量的“'''condition_fanout_filter'''”标志。
* 默认情况下，此标志是启用的，但可以禁用它以抑制条件过滤（例如，如果发现特定查询不使用它会产生更好的性能）。


如果优化器高估了条件过滤的效果，则性能可能会比不使用条件过滤的情况差。在这种情况下，这些技术可能会帮助：
# 如果未对列进行'''索引'''，请对其进行索引，以便优化程序获得有关列值分布的一些信息，并可以改善其行估计。
# 更改'''联接顺序'''。完成此操作的方法包括连接顺序优化器提示，紧跟在 SELECT 之后的 STRAIGHT_JOIN 以及 STRAIGHT_JOIN 连接运算符。
# 禁用会话的条件过滤：
#: <syntaxhighlight lang="mysql">
SET optimizer_switch = 'condition_fanout_filter=off';
</syntaxhighlight>

== IS NULL 优化 ==
MySQL 可以对“col_name IS NULL”执行与“col_name = constant_value”相同的优化。
: 例如，MySQL 可以使用索引和范围来搜索带有“IS NULL”的 NULL。
: 示例：
: <syntaxhighlight lang="mysql">
SELECT * FROM tbl_name WHERE key_col IS NULL;

SELECT * FROM tbl_name WHERE key_col <=> NULL;

SELECT * FROM tbl_name WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;
</syntaxhighlight>


* 如果“WHERE”子句包含声明为“NOT NULL”的列的“col_name IS NULL”条件，则该表达式将被优化。
** 在该列无论如何都会产生 NULL 的情况下（例如，如果它来自“LEFT JOIN”右侧的表），则不会进行此优化。
* MySQL 还可以优化组合“col_name = expr OR col_name IS NULL”，这种形式在已解析的子查询中很常见。使用此优化时，EXPLAIN 显示“ref_or_null”。
* 此优化可以为任何关键部分处理一个“IS NULL”。


假设在表 t2 的列 a 和 b 上有索引，则对查询进行一些优化的示例：
<syntaxhighlight lang="mysql">
SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;

SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;

SELECT * FROM t1, t2
  WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
  OR (t1.a=t2.a AND t2.a IS NULL AND ...);
</syntaxhighlight>


* “ref_or_null”的工作方式是先读取参考键，然后单独搜索具有 NULL 键值的行。【？？？】
* '''优化只能处理一个“IS NULL”级别'''。
*: 在以下查询中，MySQL 仅在表达式“(t1.a=t2.a AND t2.a IS NULL)”上使用键查找，而不能在 b 上使用键部分：
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL)
  OR (t1.b=t2.b AND t2.b IS NULL);
</syntaxhighlight>

== ORDER BY 优化【？】 ==
=== 使用索引满足 ORDER BY ===
在某些情况下，MySQL 可以使用索引来满足“ORDER BY”子句，并避免执行 filesort 操作【？？？】时涉及的额外排序。


即使“ORDER BY”与索引不完全匹配，也可以使用索引：
# 只要索引的所有未使用部分和所有额外“ORDER BY”列都是“WHERE”子句中的常量。
# 如果索引不包含查询访问的所有列，则仅当索引访问比其他访问方法便宜时才使用索引。


'''示例1：'''使用索引来解析 ORDER BY<br/>
假设“(key_part1, key_part2)”上有一个索引，以下查询可以使用该索引来解析“ORDER BY”部分。【'''优化器是否实际上这样做取决于如果还必须读取索引中没有的列，则读取索引是否比表扫描更有效'''。】
* 在此查询中，(key_part1, key_part2) 上的索引使优化程序避免排序：
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1
  ORDER BY key_part1, key_part2;
</syntaxhighlight>
*: 但是，查询使用“SELECT *”，它选择的列可能多于“key_part1”和“key_part2”。在这种情况下，扫描整个索引并查找表行以查找索引中未包含的列可能比扫描表并排序结果要昂贵。如果是这样，优化器可能不会使用该索引。【如果“SELECT *”仅选择索引列，则将使用索引并避免排序。】
*: 如果 t1 是 InnoDB 表，则表主键隐式属于索引的一部分，并且该索引可用于解析此查询的 ORDER BY：
*: <syntaxhighlight lang="mysql">
SELECT pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;
</syntaxhighlight>
* 在此查询中，key_part1 是常量，因此通过索引访问的所有行都按 key_part2 顺序，并且如果 WHERE 子句的选择性足以使索引范围扫描比表扫描便宜，则 (key_part1, key_part2) 上的索引可以避免排序：
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;
</syntaxhighlight>
* 在接下来的两个查询中，是否使用索引类似于前面没有显示“DESC”的相同查询：
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1
  ORDER BY key_part1 DESC, key_part2 DESC;

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2 DESC;
</syntaxhighlight>
* 在接下来的两个查询中，将 key_part1 与常量进行比较。如果 WHERE 子句的选择性足以使索引范围扫描比表扫描便宜，那么将使用索引：【！！！】
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;
</syntaxhighlight>
* 在下一个查询中，ORDER BY 不命名 key_part1，但是所有选择的行都有一个常量 key_part1 值，因此仍可以使用索引：【？？？】
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;
</syntaxhighlight>


'''示例2：'''不能使用索引来解析 ORDER BY，尽管它仍可以使用索引来查找与 WHERE 子句匹配的行。<br/>
* 该查询对不同的索引使用 ORDER BY：
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1 ORDER BY key1, key2;
</syntaxhighlight>
* 该查询在索引的非连续部分上使用 ORDER BY：
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;
</syntaxhighlight>
* 该查询混合了“ASC”和“DESC”：【！！！】
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;
</syntaxhighlight>
* 用于获取行的索引与在 ORDER BY 中使用的索引不同：
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1 WHERE key2=constant ORDER BY key1;
</syntaxhighlight>
* 该查询使用 ORDER BY，且表达式中包含除索引列名称以外的术语：【！！！】
*: <syntaxhighlight lang="mysql">
SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;
</syntaxhighlight>
* 该查询联接了许多表，并且 ORDER BY 中的列并非全部来自用于检索行的第一个非恒定表。
*: <syntaxhighlight lang="mysql">

</syntaxhighlight>
* 该查询具有不同的“ORDER BY”和“GROUP BY”表达式。【！！！】
*: <syntaxhighlight lang="mysql">

</syntaxhighlight>
* 仅在“ORDER BY”子句中命名的列的前缀上存在索引。在这种情况下，索引不能用于完全解析排序顺序。【！！！】
*: 例如，如果仅对“CHAR(20)”列的前 10 个字节进行索引，则索引无法区分第 10 个字节之后的值，因此需要'''filesort'''。
*: <syntaxhighlight lang="mysql">

</syntaxhighlight>
* 索引不按顺序存储行。例如，这对于内存表中的哈希索引是正确的。【！！！】


'''NOTE：'''<br/>
* 索引的可用性可能会受到'''列别名'''的使用的影响。假设对 t1.a 列进行了索引。在此语句中，选择列表中列的名称为 a。它引用 t1.a，就像在 ORDER BY 中引用 a 一样，因此可以使用 t1.a 上的索引：
*: <syntaxhighlight lang="mysql">
SELECT a FROM t1 ORDER BY a;
</syntaxhighlight>
*: 在此语句中，选择列表中列的名称也是 a，但这是别名。它引用“ABS(a)”，就像引用 ORDER BY 中的 a 一样，因此不能使用 t1.a 上的索引：【？？？】
*: <syntaxhighlight lang="mysql">
SELECT ABS(a) AS a FROM t1 ORDER BY a;
</syntaxhighlight>
*: 在下面的语句中，ORDER BY 引用的名称不是选择列表中列的名称。但是 t1 中有一列名为 a，因此 ORDER BY 指向 t1.a 且可以使用 t1.a 上的索引。（当然，生成的排序顺序可能与“ABS(a)”的顺序完全不同）
*: <syntaxhighlight lang="mysql">
SELECT ABS(a) AS b FROM t1 ORDER BY a;
</syntaxhighlight>
* 默认情况下，MySQL 对“'''GROUP BY''' col1, col2, ...”查询进行排序，就好像您在查询中也包含了“'''ORDER BY''' col1, col2, ...”一样。如果您包含一个显式的“ORDER BY”子句，该子句包含相同的列列表，则 MySQL 会对其进行优化，而不会造成任何速度损失，尽管排序仍然会发生。【！！！“GROUP BY”和“ORDER BY”】
** 如果查询包含“GROUP BY”，但您希望'''避免对结果进行排序的开销'''，则可以通过指定 '''ORDER BY NULL''' 来抑制排序。例如：
'''<syntaxhighlight lang="mysql">
INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
</syntaxhighlight>'''
** 优化器可能仍选择使用排序来实现分组操作。 “ORDER BY NULL”禁止对结果进行排序，而不是通过分组操作来确定结果的先前排序。 
** 【默认情况下，“GROUP BY”隐式排序（即“GROUP BY”列没有“ASC”或“DESC”指示符）。但是，不建议依靠“GROUP BY”排序（隐式或显示都不建议）。要产生给定的排序顺序，请提供“ORDER BY”子句。】

=== 使用 filesort 满足 ORDER BY 【？？？】===
如果不能使用索引来满足“ORDER BY”子句，则 MySQL 执行“filesort操作”以读取表行并对它们进行排序。'''filesort构成查询执行中的额外排序阶段'''。


为了获得filesort操作的内存，优化器会预先分配固定数量的“sort_buffer_size”字节。各个会话可以根据需要更改此变量的会话值，以避免过多的内存使用，或根据需要分配更多的内存。

如果结果集太大而无法容纳在内存中，则 filesort 操作会根据需要使用临时磁盘文件。某些类型的查询特别适合完全在内存中的filesort操作。
: 例如，优化器可以使用filesort来有效地在内存中处理“ORDER BY”操作，而无需使用临时文件，该“ORDER BY”操作用于以下形式的查询(和子查询)：【？？？】
'''<syntaxhighlight lang="mysql">
SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;
</syntaxhighlight>'''
此类查询在仅显示较大结果集中的几行的 Web 应用程序中很常见。
: 示例：【？？？】
<syntaxhighlight lang="mysql">
SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;
</syntaxhighlight>

=== 影响 ORDER BY 优化 ===
对于不使用filesort的慢“ORDER BY”查询，请尝试将“'''max_length_for_sort_data'''”系统变量降低为适合触发filesort的值。【将此变量的值设置得太高的症状是磁盘活动过多和 CPU 活动较低的组合】


要提高“ORDER BY”的速度，请检查是否可以使 MySQL 使用索引而不是额外的排序阶段。如果这不可能，请尝试以下策略：
# 增加“'''sort_buffer_size'''”变量值。理想情况下，该值应足够大以使整个结果集适合排序缓冲区（以避免写入磁盘和合并过程），但该值至少必须足够大以容纳 15 个元组。（最多合并 15 个临时磁盘文件，并且每个文件中至少必须有一个元组在内存中）
#: 考虑到存储在排序缓冲区中的列值的大小受“'''max_sort_length'''”系统变量值的影响。【例如，如果元组存储长字符串列的值，而您增加了“max_sort_length”的值，则排序缓冲区元组的大小也会增加，并且可能需要您增加“max_sort_length”。】对于由字符串表达式（例如调用字符串值函数的结果）计算出的列值，filesort算法无法确定表达式值的最大长度，因此必须为每个元组分配“max_sort_length”个字节。【？？？】
#: 要监视合并通过次数（以合并临时文件），请检查“Sort_merge_passes” status 变量。
# 增加“'''read_rnd_buffer_size'''”变量值，以便一次读取更多行。
# 更改'''tmpdir'''系统变量以指向具有大量可用空间的专用文件系统。变量值可以列出以循环方式使用的多个路径。
#* 您可以使用此功能将负载分散到多个目录中。在 Unix 上用冒号(:)和在 Windows 上用分号(;)分隔路径。路径应命名位于不同物理磁盘上的文件系统中的目录，而不是同一磁盘上的不同分区。

=== ORDER BY 执行计划信息可用 ===
使用'''EXPLAIN'''，可以检查 MySQL 是否可以使用索引来解析“ORDER BY”子句：
* 如果 EXPLAIN 输出的“Extra”列不包含“Using filesort”，则使用索引，而不执行filesort。
* 如果 EXPLAIN 输出的“Extra”列包含“'''Using filesort'''”，则不使用索引，而执行filesort。
此外，如果执行filesort，则优化器跟踪输出将包含 filesort_summary 块。例如：【？】
<syntaxhighlight lang="mysql">
"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "sort_buffer_size": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}
</syntaxhighlight>
其中，“sort_mode”值提供有关排序缓冲区中元组内容的信息：
* <sort_key, rowid>：这表明排序缓冲区是元组对，包含原始表行的“排序键值”和“行 ID”。元组按排序键值排序，并且行 ID 用于从表中读取行。
* <sort_key, additional_fields>：这表明排序缓冲区元组包含“排序键值”和“查询所引用的列”。元组通过排序键值进行排序，并且列值直接从元组中读取。
* <sort_key, packed_additional_fields>：与以前的变体一样，但其他列紧密地包装在一起，而不是使用固定长度的编码。


EXPLAIN 不能区分优化器是否在内存中执行filesort。在优化器跟踪输出中可以看到内存中filesort的使用。寻找filesort_priority_queue_optimization。【？？？】

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


为“GROUP BY”使用索引的最重要的先决条件是，所有“GROUP BY”列都引用同一索引中的属性，并且索引按顺序存储其键（例如，对于 B树 索引是这样，但对于 HASH 索引则不是这样）。
临时表的使用是否可以替换为索引访问，还取决于在查询中使用索引的哪些部分、为这些部分指定的条件以及所选的聚合函数。


有两种通过索引访问执行 GROUP BY 查询的方法，如以下各节所述。第一种方法将分组操作与所有范围谓词(如果有)一起应用。第二种方法首先执行范围扫描，然后对所得的 Tuples 进行分组。


在 MySQL 中，使用 GROUP BY 进行排序，因此服务器也可以将 ORDER BY 优化应用于分组。但是，不建议依赖隐式或显式 GROUP BY 排序。

=== 松散索引扫描 ===
处理 GROUP BY 的最有效方法是'''使用索引直接检索分组列'''。
: 通过这种访问方法，MySQL 使用键排序的某些索引类型的属性（例如 B树）。使用此属性，可以在索引中使用查找组，而不必考虑索引中满足所有 WHERE 条件的所有键。此访问方法仅考虑索引中的一部分键，因此称为“松散索引扫描”。
# 如果没有 WHERE 子句，则“松散索引扫描”将读取与组数一样多的键，该数目可能比所有键的数目小得多。
# 如果 WHERE 子句包含范围谓词，则“松散索引扫描”将查找满足范围条件的每个组的第一个键，并再次读取尽可能少的键数。


在以下情况下可以这样做：
# 查询是在单个表上。
# GROUP BY 仅命名构成索引最左前缀的列，不命名其他列。（如果查询具有 DISTINCT 子句，而不是 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))，索引仅使用 c1 值的前缀，不能用于宽松索引扫描。


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


示例：<br/>
假设在表 t1(c1,c2,c3,c4) 上有一个索引 idx(c1,c2,c3)。松散索引扫描访问方法可用于以下查询：
<syntaxhighlight lang="mysql">
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;
</syntaxhighlight>
无法使用此快速选择方法执行以下查询：
# 除 MIN() 或 MAX() 以外，还有其他聚合函数：
#: <syntaxhighlight lang="mysql">
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
</syntaxhighlight>
# GROUP BY 子句中的列不构成索引的最左前缀：
#: <syntaxhighlight lang="mysql">
SELECT c1, c2 FROM t1 GROUP BY c2, c3;
</syntaxhighlight>
# 该查询引用的是 GROUP BY 部分之后的键的一部分，并且该部分与常量不相等：
#: <syntaxhighlight lang="mysql">
SELECT c1, c3 FROM t1 GROUP BY c1, c2;
</syntaxhighlight>
#: 如果查询包含 WHERE c3 = const，则可以使用松散索引扫描。


除了已经支持的“MIN()”和“MAX()”引用之外，“松散索引扫描”访问方法还可以应用于选择列表中的其他形式的聚合函数引用：
# 支持“AVG(DISTINCT)”，“SUM(DISTINCT)”和“COUNT(DISTINCT)”。
#* “AVG(DISTINCT)”和“SUM(DISTINCT)”采用一个参数。 “COUNT(DISTINCT)”可以有多个列参数。
# 查询中不得包含“”GROUP BY或“”DISTINCT子句。
# 先前描述的松散索引扫描限制仍然适用。


示例：<br/>
假设在表 t1(c1,c2,c3,c4) 上有一个索引 idx(c1,c2,c3)。松散索引扫描访问方法可用于以下查询：
<syntaxhighlight lang="mysql">
SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;
</syntaxhighlight>

=== 紧密索引扫描 ===
紧密索引扫描可以是完全索引扫描，也可以是范围索引扫描，具体取决于查询条件。


当不满足松散索引扫描的条件时，仍然可以避免为 GROUP BY 查询创建临时表。如果 WHERE 子句中存在范围条件，则此方法仅读取满足这些条件的键。否则，它将执行索引扫描。由于此方法读取 WHERE 子句定义的每个范围内的所有键，或者如果没有范围条件，则扫描整个索引，因此称为紧密索引扫描。对于紧密索引扫描，只有在找到满足范围条件的所有键之后，才执行分组操作。


要使此方法起作用，查询中的所有列都有一个常量相等条件就足够了，该查询引用的部分键位于 GROUP BY 键的部分之前或部分之间。相等条件中的常量填充搜索键中的任何“空白”，以便可以形成索引的完整前缀。这些索引前缀可用于索引查找。如果 group by 结果需要排序，并且可以形成作为索引前缀的搜索键，MySQL还可以避免额外的排序操作，因为在有序索引中使用前缀进行搜索已经按顺序检索所有键。


示例：<br/>
假设在表 t1(c1,c2,c3,c4) 上有一个索引 idx(c1,c2,c3)。以下查询不适用于前面所述的“松散索引扫描”访问方法，但仍适用于“紧索引扫描”访问方法。
# GROUP BY 中有一个缝隙，但已被条件c2 = 'a'覆盖：
#: <syntaxhighlight lang="mysql">
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
</syntaxhighlight>
# GROUP BY并非以键的第一部分开头，但是存在为该部分提供常量的条件：
#: <syntaxhighlight lang="mysql">
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
</syntaxhighlight>

== DISTINCT 优化 ==
在许多情况下，'''“DISTINCT”和“ORDER BY”结合需要一个临时表'''。


在大多数情况下，'''“DISTINCT”子句可以视为“GROUP BY”的特例'''。例如，以下两个查询是等效的：
<syntaxhighlight lang="mysql">
SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;

SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;
</syntaxhighlight>
由于这种等效性，适用于“GROUP BY”查询的优化也可以应用于带有“DISTINCT”子句的查询。


* 当结合“LIMIT row_count”和“DISTINCT”时，MySQL 一旦找到 row_count 个唯一行，就会停止运行。
* 如果不使用查询中命名的所有表中的列，MySQL会在找到第一个匹配项后立即停止扫描任何未使用的表。
*: 在以下情况下，假设在 t2 之前使用 t1（您可以使用“EXPLAIN”进行检查），则当 MySQL 在 t2 中找到第一行时，它将停止从 t2（对于 t1 中的任何特定行）读取：
*: <syntaxhighlight lang="mysql">
SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;
</syntaxhighlight>

== LIMIT 查询优化 ==
【LIMIT：从结果集中只需要指定数量的行，而非整个结果集】<br/>

MySQL 有时会优化具有“LIMIT row_count”子句而没有“HAVING”子句的查询：
# 如果只选择“LIMIT”的几行，MySQL在某些情况下会使用索引，而'''通常它会执行全表扫描'''。
# 如果将“LIMIT row_count”和“ORDER BY”结合使用，MySQL 会在找到排序结果前面的 row_count 个行后立即停止排序，而'''不是对整个结果进行排序'''。
#* 如果通过使用索引进行排序，这将非常快。
#* 如果必须执行“filesort”（文件排序？），则在找到前面的 row_count 个行之前，将选择所有匹配但不带有 LIMIT 子句的行，并对其中的大多数或全部进行排序。找到初始行后，MySQL 不会对结果集的其余部分进行排序。【？】
#*【此行为的一种体现是，具有和不具有 LIMIT 的 ORDER BY 查询可能以不同的 Sequences 返回行】
# 如果将“LIMIT row_count”和“DISTINCT”结合使用，MySQL 将在找到 row_count 唯一行后立即停止。
# 在某些情况下，可以通过按顺序读取索引（或对索引进行排序），然后计算汇总直到索引值更改来解决“GROUP BY”。在这种情况下，“LIMIT row_count”不会计算任何不必要的“GROUP BY”值。
# MySQL 一旦向 Client 端发送了所需的行数，它将立即中止查询，除非您使用“'''SQL_CALC_FOUND_ROWS'''”【见：“[http://wiki.eijux.com/MySQL_%E5%87%BD%E6%95%B0%E5%92%8C%E8%BF%90%E7%AE%97%E7%AC%A6%EF%BC%9A%E4%BF%A1%E6%81%AF%E5%87%BD%E6%95%B0 MySQL 函数和运算符：信息函数]”的“FOUND_ROWS()”部分，其用于“知道该语句在没有 LIMIT 的情况下将返回多少行”】。
#* 在这种情况下，可以使用“SELECT FOUND_ROWS()”检索行数。
# “LIMIT 0”快速返回一个空集。这对于检查查询的有效性很有用。
#* 它还可以用于获取使用 MySQL API 的应用程序中结果列元数据的类型的结果列的类型。通过mysql Client 端程序，您可以使用“--column-type-info”选项显示结果列类型。【？】
# 如果服务器使用临时表来解析查询，则它使用“LIMIT row_count”子句来计算所需的空间。【？】
# 如果没有为“ORDER BY”使用索引，但是也存在“LIMIT”子句，则优化器可能能够避免使用合并文件，并使用内存中“filesort”操作对内存中的行进行排序。【？】


如果“ORDER BY”列中的多行具有相同的值，服务器可以按任意顺序返回这些行，并且根据总体执行计划的不同，返回的顺序也可能不同。换句话说，这些行的排序顺序相对于非排序列是不确定的。
** 影响执行计划的一个因素是“LIMIT”，因此具有 LIMIT 和不具有 LIMIT 的“ORDER BY”查询可能以不同的顺序返回行。
**: <syntaxhighlight lang="mysql">
mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+
</syntaxhighlight>
** 要确保带有 LIMIT 和不带有 LIMIT 的行顺序相同，请在 ORDER BY 子句中包括其他列以使顺序确定。
**: <syntaxhighlight lang="mysql">
mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+
</syntaxhighlight>

== 函数调用优化 ==
MySQL 函数在内部被标记为确定性或不确定性。
* 不确定：如果给定参数固定值的函数可以'''为不同的调用返回不同的结果'''。如：“RAND()”，“UUID()”。

如果一个函数被标记为不确定的，则对 WHERE 子句中的每一行（从一个表中选择时）或行组合（从多表联接中选择时）的引用进行评估。

MySQL 还根据参数的类型（参数是表列还是常量值）确定何时评估函数。每当表列更改值时，都必须评估将表列作为参数的确定性函数。


'''非确定性函数可能会影响查询性能'''。例如，某些优化可能不可用，或者可能需要更多锁定。


示例：
假设表 t 具有以下定义：
<syntaxhighlight lang="mysql">
CREATE TABLE t (id INT NOT NULL PRIMARY KEY, col_a VARCHAR(100));
</syntaxhighlight>
考虑以下两个查询：
<syntaxhighlight lang="mysql">
SELECT * FROM t WHERE id = POW(1,2);
SELECT * FROM t WHERE id = FLOOR(1 + RAND() * 49);
</syntaxhighlight>
由于与主键的相等性比较，两个查询似乎都使用了主键查找，但这仅适用于第一个查询：
# 第一个查询始终最多产生一行，因为带有常量参数的“POW()”是常量值，用于索引查找。
# 第二个查询包含一个使用不确定函数“RAND()”的表达式，该函数在查询中不是常量，但实际上对于表 t 的每一行都有一个新值。因此，查询将读取表的每一行，评估每一行的谓词，并输出主键与随机值匹配的所有行。这可能是零行，一行或多行，具体取决于 id 列的值和 RAND() 序列中的值。


'''不确定性的影响不仅限于“SELECT”语句'''。此“UPDATE”语句使用非确定性函数来选择要修改的行：
<syntaxhighlight lang="mysql">
UPDATE t SET col_a = some_expr WHERE id = FLOOR(1 + RAND() * 49);
</syntaxhighlight>
大概的目的是最多更新主键与表达式匹配的一行。但是，它可能更新零，一或多个行，具体取决于 id 列的值和 RAND() 序列中的值。


刚刚描述的行为对性能和复制有影响：
* 由于不确定函数不会产生恒定值，因此优化器无法使用其他可能适用的策略，例如索引查找。结果可能是全表扫描。
* InnoDB 可能升级为范围键锁定，而不是为一个匹配的行获取单个行锁定。
* 无法确定执行的更新对于复制是不安全的。


困难源于对表的每一行都对“RAND()”函数进行一次评估的事实。为了避免进行多功能评估，请使用以下技术之一：
# 将包含不确定性函数的表达式移到单独的语句，将值保存在'''变量'''中。在原始语句中，'''将表达式替换为对变量的引用'''，优化器可以将该变量视为常量值：【！】
<syntaxhighlight lang="mysql">
SET @keyval = FLOOR(1 + RAND() * 49);
UPDATE t SET col_a = some_expr WHERE id = @keyval;
</syntaxhighlight>
# 将随机值分配给派生表中的'''变量'''。此技术使变量'''在 WHERE 子句中的比较中使用之前被分配一个值'''：
<syntaxhighlight lang="mysql">
SET optimizer_switch = 'derived_merge=off';
UPDATE t, (SELECT @keyval := FLOOR(1 + RAND() * 49)) AS dt
SET col_a = some_expr WHERE id = @keyval;
</syntaxhighlight>
#: 如前所述，WHERE 子句中的不确定性表达式可能会阻止优化并导致表扫描。但是，如果其他表达式是确定性的，则可以部分优化 WHERE 子句。例如：
#: <syntaxhighlight lang="mysql">
SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();
</syntaxhighlight>
#: 如果优化器可以使用“partial_key”来减少所选行的集合，则执行“RAND()”的次数将减少，从而减少了不确定性对优化的影响。

== 行构造函数表达式优化 ==
行构造函数【？？？就是用函数表示条件语句？？？】允许'''同时比较多个值'''。
: 例如，以下两个语句在语义上是等效的：
<syntaxhighlight lang="mysql">
SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
</syntaxhighlight>
: 另外，优化器以'''相同的方式处理两个表达式'''。


如果行构造器的列不覆盖索引的前缀，则优化器不太可能使用可用索引。

示例：表在(c1, c2, c3)上具有主键：
<syntaxhighlight lang="mysql">
CREATE TABLE t1 (
  c1 INT, c2 INT, c3 INT, c4 CHAR(100),
  PRIMARY KEY(c1,c2,c3)
);
</syntaxhighlight>
在此查询中，WHERE 子句使用索引中的所有列。但是，行构造器本身不包含索引前缀，因此优化器仅使用 c1：【“key_len=4”：c1的大小，而非联合主键的大小】
<syntaxhighlight lang="mysql" highlight="2,8,11,12">
mysql> EXPLAIN SELECT * FROM t1
       WHERE c1=1 AND (c2,c3) > (1,1)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 3
     filtered: 100.00
        Extra: Using where
</syntaxhighlight>
在这种情况下，使用等效的'''“非构造函数表达式”'''重写“行构造函数表达式”可能会导致更完整的索引使用。对于给定的查询，行构造函数 和等效的 非构造函数表达式 为：【？？？】
<syntaxhighlight lang="mysql">
(c2,c3) > (1,1)
c2 > 1 OR ((c2 = 1) AND (c3 > 1))
</syntaxhighlight>
使用索引中的所有三列重写查询以使用非构造函数表达式会在优化器中产生结果：【“key_len=12”：非联合主键的大小】
<syntaxhighlight lang="mysql" highlight="2,8,11,12">
mysql> EXPLAIN SELECT * FROM t1
       WHERE c1 = 1 AND (c2 > 1 OR ((c2 = 1) AND (c3 > 1)))\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 12
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
</syntaxhighlight>
因此，为了获得更好的结果，请'''避免将行构造函数与“AND”/“OR”表达式混合使用'''。


在某些情况下，优化器可以将范围访问方法应用于具有行构造函数参数的“IN()”表达式。

== '''避免全表扫描''' ==
当 MySQL 使用全表扫描解析查询时，'''EXPLAIN'''的输出在“type”列中显示“'''ALL'''”【即，使用了“全表扫描”】。


这通常在以下情况下发生：
# '''表太小'''了，执行表扫描比进行键查找要快。这对于行数少于10行且行长较短的表很常见。
# 对于索引列，'''“ON”或“WHERE”子句中没有可用的限制'''。
# 正将索引列与常量值进行比较，MySQL 已经计算出（基于索引树）'''常量覆盖了表的很大一部分，并且表扫描会更快'''。【？？？】
# 正在通过另一列使用'''基数低的键'''（许多行与键值匹配）。
#* 在这种情况下，MySQL 假定：通过使用键，它可能会执行许多键查找，而表扫描会更快。


对于小型表，表扫描通常是适当的，并且对性能的影响可以忽略不计。对于大型表，请尝试以下技术，以避免优化器错误地选择表扫描：
# 使用“'''ANALYZE TABLE tbl_name'''”更新扫描表的 key 分布。
# 对被扫描的表使用“'''FORCE INDEX'''”来告诉 MySQL：与使用给定索引相比，表扫描非常昂贵：
#: <syntaxhighlight lang="mysql">
SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
  WHERE t1.col_name=t2.col_name;
</syntaxhighlight>
# 使用“--max-seeks-for-key=1000”选项启动 mysqld，或使用“SET max_seeks_for_key=1000”告诉优化器假定没有键扫描会导致超过 1000 个键查找。