8.1 优化概述
数据库性能取决于数据库级别的几个因素，例如表、查询和配置设置。这些软件结构会导致硬件级别的 CPU 和 I/O 操作，您必须将其最小化并尽可能提高效率。在处理数据库性能时，首先要学习软件方面的高级规则和指南，并使用挂钟时间来衡量性能。当您成为专家时，您会更多地了解内部发生的事情，并开始测量诸如 CPU 周期和 I/O 操作之类的东西。
典型用户的目标是从他们现有的软件和硬件配置中获得最佳的数据库性能。高级用户寻找机会改进 MySQL 软件本身，或开发自己的存储引擎和硬件设备以扩展 MySQL 生态系统。
使数据库应用程序快速运行的最重要因素是其基本设计：
- 表格的结构是否正确？特别是，列是否具有正确的数据类型，每个表是否具有适合工作类型的列？例如，执行频繁更新的应用程序通常有很多表和很少的列，而分析大量数据的应用程序通常有很少的表和很多列。
- 是否有正确的 索引来提高查询效率？
- 您是否为每个表使用了适当的存储引擎，并利用了您使用的每个存储引擎的优势和特性？特别是，选择事务性存储引擎（例如事务性存储引擎） InnoDB 或非 事务性存储引擎（例如）MyISAM 对于性能和可伸缩性可能非常重要。
笔记
InnoDB是新表的默认存储引擎。在实践中，高级 InnoDB性能特性意味着 InnoDB表通常优于简单MyISAM表，尤其是对于繁忙的数据库。
- 每个表是否使用适当的行格式？此选择还取决于用于表的存储引擎。特别是，压缩表使用更少的磁盘空间，因此需要更少的磁盘 I/O 来读取和写入数据。压缩适用于所有类型的带有 InnoDB表的工作负载，也适用于只读 MyISAM表。
- 应用程序是否使用了适当的 锁定策略？例如，尽可能允许共享访问，以便数据库操作可以并发运行，并在适当时请求独占访问，以便关键操作获得最高优先级。同样，存储引擎的选择很重要。存储引擎无需您参与即可处理大多数锁定问题，从而提高数据库的InnoDB并发性并减少代码的试验和调整量。
- 用于缓存的 所有内存区域的大小是否正确？也就是说，大到足以容纳经常访问的数据，但又不会大到使物理内存过载并导致分页。要配置的主要内存区域是InnoDB缓冲池、MyISAM键缓存和 MySQL 查询缓存。
随着数据库变得越来越繁忙，任何数据库应用程序最终都会遇到硬件限制。DBA 必须评估是否可以调整应用程序或重新配置服务器以避免这些 瓶颈，或者是否需要更多的硬件资源。系统瓶颈通常来自以下来源：
- 磁盘寻道。磁盘找到一条数据需要时间。对于现代磁盘，平均时间通常低于 10 毫秒，因此理论上我们每秒可以进行大约 100 次寻道。这个时间随着新磁盘的增加而缓慢改善，并且很难针对单个表进行优化。优化寻道时间的方法是将数据分布到多个磁盘上。
- 磁盘读写。当磁盘在正确的位置时，我们需要读取或写入数据。使用现代磁盘，一个磁盘可提供至少 10–20MB/s 的吞吐量。这比查找更容易优化，因为您可以从多个磁盘并行读取。
- CPU 周期。当数据在主存中时，我们必须对其进行处理以获得我们的结果。与内存量相比，拥有大表是最常见的限制因素。但是对于小桌子，速度通常不是问题。
- 内存带宽。当 CPU 需要的数据超出 CPU 高速缓存的容量时，主内存带宽就会成为瓶颈。对于大多数系统来说，这是一个不常见的瓶颈，但需要注意。
要在可移植的 MySQL 程序中使用面向性能的 SQL 扩展，您可以将 MySQL 特定的关键字包装在/*! */注释分隔符内的语句中。其他 SQL 服务器忽略注释的关键字。有关撰写评论的信息，请参阅第 9.6 节，“评论”。
8.2 优化 SQL 语句
8.2.1.1 WHERE 子句优化
本节讨论可以对处理WHERE子句进行的优化。这些示例使用 SELECT语句，但相同的优化适用于和 语句 中的WHERE子句 。DELETEUPDATE
笔记
由于 MySQL 优化器的工作正在进行中，因此此处并未记录 MySQL 执行的所有优化。
您可能很想重写查询以使算术运算更快，同时牺牲可读性。因为 MySQL 会自动进行类似的优化，所以您通常可以避免这项工作，并将查询保留在更易于理解和维护的形式中。MySQL执行的一些优化如下：
- 删除不必要的括号：
aANDb)cORaANDb)cANDd))))
-> (a AND b AND c) OR (a AND b AND c AND d)
- 恒定折叠：
a<bANDb=c)a=5
-> b>5 AND b=c AND a=5
- 恒定条件去除：
b>=5b=5)b=6b=7
-> b=5 OR b=6
- 索引使用的常量表达式只计算一次。
- COUNT(*)在没有 a 的单个表上
WHERE直接从表信息中检索MyISAM和MEMORY表。NOT NULL当仅与一个表一起使用时， 这也适用于任何表达式。 - 早期检测无效的常量表达式。MySQL 快速检测到某些 SELECT语句是不可能的并且不返回任何行。
HAVINGWHERE如果您不使用GROUP BY或聚合函数（COUNT()、 等），则与 MIN()。- 对于连接中的每个表，
WHERE构造一个更简单的方法来快速WHERE评估表并尽快跳过行。 - 在查询中的任何其他表之前首先读取所有常量表。常量表是以下任何一种：
- 空表或只有一行的表。
- 与 a或 索引
WHERE上的子句 一起使用的表，其中所有索引部分都与常量表达式进行比较并定义为.PRIMARY KEYUNIQUENOT NULL
以下所有表都用作常量表：
SELECT * FROM t WHERE primary_key=1;
SELECT * FROM t1,t2
WHERE t1.primary_key=1 AND t2.primary_key=t1.id;- 通过尝试所有可能性来找到用于连接表的最佳连接组合。
ORDER BY如果and子句中的所有列GROUP BY都来自同一个表，则在加入时首先首选该表。 - 如果有一个
ORDER BY子句和一个不同的GROUP BY子句，或者如果ORDER BYorGROUP BY包含来自连接队列中第一个表以外的表的列，则会创建一个临时表。 - 如果使用
SQL_SMALL_RESULT修饰符，MySQL 使用内存中的临时表。 - 查询每个表索引，并使用最佳索引，除非优化器认为使用表扫描更有效。曾经，根据最佳索引是否跨越超过 30% 的表来使用扫描，但固定百分比不再决定使用索引还是扫描之间的选择。优化器现在更加复杂，它的估计基于其他因素，例如表大小、行数和 I/O 块大小。
- 在某些情况下，MySQL 甚至可以在不查阅数据文件的情况下从索引中读取行。如果索引中使用的所有列都是数字，则仅使用索引树来解析查询。
- 在每一行输出之前，那些不匹配
HAVING子句的被跳过。
一些非常快的查询示例：
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;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 MAX(key_part2) FROM tbl_name GROUP BY key_part1;
以下查询使用索引以排序顺序检索行，而无需单独的排序传递：
SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... ;SELECT ... FROM tbl_name
ORDER BY key_part1 DESC, key_part2 DESC, ... ;8.2.1.2 范围优化
访问方法使用range单个索引来检索包含在一个或多个索引值区间内的表行子集。它可用于单部分或多部分索引。以下部分描述了优化器使用范围访问的条件。
对于单部分索引，索引值区间可以方便地用 WHERE子句中对应的条件来表示，表示为 范围条件 而不是“区间”。”
单部分索引的范围条件定义如下：
- 对于
BTREE和 索引，在使用、 、 、或运算符HASH时，将关键部分与常数值进行比较是范围条件 。 =<=>IN()IS NULLIS NOT NULL - 此外，对于索引，在使用、 、 、 、 、 或 运算符
BTREE时，将键部分与常量值进行比较是范围条件 ，或者 如果参数 to 是不以通配符开头的常量字符串，则进行比较。 ><>=<=BETWEEN!=<>LIKELIKE - 对于所有索引类型，多个范围条件结合OR或 AND形成一个范围条件。
上述描述中的 “常数值”是指以下之一：
WHERE以下是子句 中具有范围条件的一些查询示例：
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';在优化器常量传播阶段，一些非常量值可能会转换为常量。
MySQL 尝试从 WHERE子句中为每个可能的索引提取范围条件。在提取过程中，丢弃不能用于构造范围条件的条件，合并产生重叠范围的条件，并去除产生空范围的条件。
考虑以下语句，其中 key1是索引列 nonkey且未编制索引：
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');密钥的提取过程key1如下：
- 从原始
WHERE条款开始：
- (
key1<key1LIKEkey1LIKE - (
key1<nonkey=
(key1 < 'uux' AND key1 > 'z')
- 删除
nonkey = 4并且key1 LIKE '%b'因为它们不能用于范围扫描。删除它们的正确方法是用 替换它们TRUE，这样我们在进行范围扫描时就不会错过任何匹配的行。TRUE用产量 代替它们：
- (
key1<key1LIKE - (
key1<
(key1 < 'uux' AND key1 > 'z')
- 始终为真或假的折叠条件：
(key1 LIKE 'abcde%' OR TRUE)永远是真的(key1 < 'uux' AND key1 > 'z')总是假的
用常数替换这些条件会产生：
(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)
删除不必要TRUE的 FALSE常量产生：
(key1 < 'abc') OR (key1 < 'bar')
- 将重叠的间隔组合成一个产生用于范围扫描的最终条件：
(key1 < 'bar')
通常（如前面的示例所示），用于范围扫描的条件没有WHERE子句那么严格。MySQL 执行额外的检查以过滤掉满足范围条件但不满足完整WHERE子句的行。
范围条件提取算法可以处理任意深度的嵌套 AND/OR 构造，其输出不依赖于条件在 WHERE子句中出现的顺序。
MySQL 不支持 range空间索引的访问方式合并多个范围。要解决此限制，您可以使用UNIONwith 相同 的SELECT语句，除了将每个空间谓词放在不同的 SELECT.
多部分索引的范围条件是单部分索引的范围条件的扩展。多部分索引上的范围条件将索引行限制在一个或多个键元组间隔内。键元组区间是在一组键元组上定义的，使用索引中的排序。
例如，考虑定义为 的多部分索引 ，以及按键顺序列出的以下键元组集： key1(key_part1, key_part2, key_part3)
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'条件key_part1 = 1定义了这个间隔：
(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)
区间覆盖了前面数据集中的第 4、5、6 个元组，可以被范围访问方法使用。
相比之下，该条件 key_part3 = 'abc'没有定义单个区间，并且不能由范围访问方法使用。
以下描述更详细地说明了范围条件如何适用于多部分索引。
- 对于
HASH索引，可以使用包含相同值的每个区间。这意味着只能针对以下形式的条件生成区间：
- AND
- AND
AND key_partN cmp constN;
这里, const1, const2, ... 是常数，cmp是 =, <=>, 或IS NULL比较运算符之一，条件涵盖所有索引部分。（即，N 有条件，对于 -part 索引的每个部分都有一个 N条件。）例如，以下是三部分 HASH索引的范围条件：
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
有关被视为常量的定义，请参阅 单部分索引的范围访问方法。
- 对于
BTREE索引，间隔可能可用于与 结合的条件 AND，其中每个条件使用 =, <=>, IS NULL, >, <, >=, <=, !=, <>, BETWEEN或 (其中 LIKE 'pattern''pattern'不以通配符开头）。只要可以确定包含所有匹配条件的行的单个键元组，就可以使用间隔（如果 使用<> 或，!= 则使用两个间隔）。
只要比较运算符是 、 或 ，优化器就会尝试使用其他关键部分来 =确定 <=>区间IS NULL。如果运算符是 >, <, >=, <=, !=, <>, BETWEEN, or LIKE，优化器使用它但不再考虑关键部分。对于以下表达式，优化器使用 =第一次比较。它还使用 >= 来自第二个比较，但不考虑其他关键部分，并且不使用第三个比较进行区间构造：
key_part1 = 'foo' AND key_part2 >= 10 AND key_part3 > 10
单个区间为：
('foo',10,-inf) < (key_part1,key_part2,key_part3) < ('foo',+inf,+inf)
创建的间隔可能包含比初始条件更多的行。例如，前面的区间包含('foo', 11, 0)不满足原始条件的值。
- 如果覆盖区间内包含的行集的条件与 组合 OR，则它们形成一个条件，覆盖区间内并集内包含的行集。如果条件与 结合 AND，则它们形成一个条件，该条件涵盖包含在其区间交集内的一组行。例如，对于两部分索引的这种情况：
(key_part1 = 1 AND key_part2 < 2) OR (key_part1 > 5)
间隔是：
(1,-inf) < (key_part1,key_part2) < (1,2)
(5,-inf) < (key_part1,key_part2)
在此示例中，第一行的间隔使用一个关键部分作为左边界，两个关键部分作为右边界。第二行的间隔只使用了一个关键部分。输出中的key_len列EXPLAIN指示使用的键前缀的最大长度。
在某些情况下，key_len可能表明使用了关键部件，但这可能不是您所期望的。假设 key_part1和 key_part2可以是 NULL。然后该 key_len列显示以下条件的两个关键部分长度：
key_part1 >= 1 AND key_part2 < 2
但是，实际上，条件转换为：
key_part1 >= 1 AND key_part2 IS NOT NULL
有关如何执行优化以组合或消除单部分索引的范围条件间隔的描述，请参阅单部分索引的 范围访问方法。对多部分索引的范围条件执行类似的步骤。
考虑这些表达式，其中 col_name是索引列：
col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN
col_name如果等于多个值中的任何一个， 则每个表达式都为真 。这些比较是相等范围比较（其中“范围”是单个值）。优化器估计读取符合条件的行以进行相等范围比较的成本如下：
- 如果 上存在唯一索引
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, orORDER BY子句。
这些跳水条件仅适用于单表查询。多表查询（连接）不会跳过索引潜水。
优化器能够将范围扫描访问方法应用于这种形式的查询：
SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));
以前，要使用范围扫描，必须将查询编写为：
SELECT ... FROM t1 WHERE ( col_1 = 'a' AND col_2 = 'b' )
OR ( col_1 = 'c' AND col_2 = 'd' );
为了让优化器使用范围扫描，查询必须满足以下条件：
- 只IN()使用谓词，不使用NOT IN().
- 在谓词的左侧 IN()，行构造函数仅包含列引用。
- 在谓词的右侧 IN()，行构造函数仅包含运行时常量，它们是在执行期间绑定到常量的文字或本地列引用。
- 在谓词的右侧 IN()，有不止一个行构造函数。
有关优化器和行构造函数的更多信息，请参阅 第 8.2.1.19 节，“行构造函数表达式优化”
要控制范围优化器可用的内存，请使用 range_optimizer_max_mem_size 系统变量：
- 值 0 表示“没有限制”。”
- 使用大于 0 的值，优化器在考虑范围访问方法时跟踪消耗的内存。如果即将超过指定的限制，则放弃范围访问方法，并考虑其他方法，包括全表扫描。这可能不太理想。如果发生这种情况，则会出现以下警告（
N当前 range_optimizer_max_mem_size 值在哪里）：
Warning 3170 Memory capacity ofNbytes for'range_optimizer_max_mem_size' exceeded. Range
optimization was not done for this query.- 对于UPDATE和 DELETE语句，如果优化器回退到全表扫描并且 sql_safe_updates启用了系统变量，则会发生错误而不是警告，因为实际上没有使用任何键来确定要修改哪些行。有关详细信息，请参阅 使用安全更新模式 (--safe-updates)。
对于超出可用范围优化内存并且优化器回退到不太优化的计划的单个查询，增加该 range_optimizer_max_mem_size 值可能会提高性能。
要估计处理范围表达式所需的内存量，请使用以下准则：
- 对于像下面这样的简单查询，其中有一个范围访问方法的候选键，每个谓词组合OR 使用大约 230 个字节：
- SELECT
t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
- 类似地，对于如下查询，每个谓词组合AND 使用大约 125 个字节：
- SELECT
t
WHERE a=1 AND b=1 AND c=1 ... N;
- 对于带有IN() 谓词的查询：
- SELECT
t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
列表中的每个文字值都 IN()算作一个与 . 组合的谓词OR。如果有两个IN() 列表，则结合的谓词 OR数量是每个列表中文字值数量的乘积。OR因此，在前一种情况下 结合的谓词数量 为M× N。
在 5.7.11 之前，每个谓词结合的字节数OR更高，约为 700 字节。
8.2.1.3 索引合并优化
Index Merge访问方法检索具有多个扫描的行 并将range其结果合并为一个。此访问方法仅合并来自单个表的索引扫描，而不是跨多个表的扫描。合并可以生成其底层扫描的并集、交集或交集并集。
可以使用索引合并的示例查询：
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);笔记
索引合并优化算法具有以下已知限制：
- (x
(x OR y) AND z => (x AND z) OR (y AND z)
- 索引合并不适用于全文索引。
在EXPLAIN输出中，索引合并方法显示为 index_merge列中 type。在这种情况下，该 key列包含使用的索引列表，并key_len包含这些索引的最长键部分的列表。
Index Merge 访问方法有几种算法，显示在输出Extra字段中 EXPLAIN：
Using intersect(...)Using union(...)Using sort_union(...)
以下部分更详细地描述了这些算法。优化器根据各种可用选项的成本估计在不同可能的索引合并算法和其他访问方法之间进行选择。
索引合并的使用取决于 系统变量的index_merge、 index_merge_intersection、 index_merge_union和 index_merge_sort_union标志的值。optimizer_switch请参阅 第 8.9.2 节，“可切换的优化”。默认情况下，所有这些标志都是on. 要仅启用某些算法，请设置index_merge 为off，并仅启用应允许的其他算法。
此访问算法适用于将 WHERE子句转换为不同键上的多个范围条件与 结合 AND，并且每个条件是以下之一：
- 这种形式的
N-part 表达式，其中索引具有精确的N部分（即，所有索引部分都被覆盖）：
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
- 表主键上的任何范围条件
InnoDB。
例子：
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;索引合并交集算法对所有使用的索引执行同时扫描，并生成它从合并索引扫描接收的行序列的交集。
如果查询中使用的所有列都被使用的索引覆盖，则不会检索完整的表行（在这种情况下，EXPLAIN输出包含 Using index在Extra 字段中）。以下是此类查询的示例：
SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;
如果使用的索引未覆盖查询中使用的所有列，则仅当满足所有使用的键的范围条件时才检索完整的行。
如果其中一个合并条件是对InnoDB表的主键的条件，则它不用于行检索，而是用于过滤掉使用其他条件检索到的行。
此算法的标准类似于索引合并交集算法的标准。该算法适用于将表的WHERE 子句转换为不同键上的多个范围条件与 结合OR，并且每个条件是以下之一：
- 这种形式的
N-part 表达式，其中索引具有精确的N部分（即，所有索引部分都被覆盖）：
key_part1 = const1 AND key_part2 = const2 ... AND key_partN = constN
- 表的主键上的任何范围条件
InnoDB。 - 索引合并交集算法适用的条件。
例子：
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;该访问算法适用于 WHERE子句转换为由 组合的多个范围条件时 OR，但不适用Index Merge union 算法。
例子：
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;排序联合算法和联合算法之间的区别在于排序联合算法必须首先获取所有行的行 ID 并在返回任何行之前对它们进行排序。
8.2.1.4 发动机工况下推优化
这种优化提高了非索引列和常量之间直接比较的效率。在这种情况下，条件被“下推”到存储引擎进行评估。此优化只能由NDB存储引擎使用。
对于 NDB Cluster，这种优化可以消除通过网络在集群的数据节点和发出查询的 MySQL 服务器之间发送不匹配行的需要，并且可以将使用它的查询速度提高 5 到 10 倍。条件下推可以但不使用的地方。
假设 NDB Cluster 表定义如下：
CREATE TABLE t1 (
a INT, b INT, KEY(a)) ENGINE=NDB;
引擎条件下推可用于如下所示的查询，其中包括非索引列和常量之间的比较：
SELECT a, b FROM t1 WHERE b = 10;
引擎状态下推的使用可以在以下输出中看到EXPLAIN：
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
但是，引擎条件下推不能 与以下两个查询中的任何一个一起使用：
SELECT a,b FROM t1 WHERE a = 10;
SELECT a,b FROM t1 WHERE b + 1 = 10;
引擎条件下推不适用于第一个查询，因为 column 上存在索引a。（索引访问方法会更有效，因此会优先选择条件下推。）引擎条件下推不能用于第二个查询，因为涉及非索引列的比较 b是间接的。（但是，如果您要在 子句 中减少b + 1 = 10到，则可以应用发动机条件下推。）b = 9WHERE
>当使用or运算符 将索引列与常量进行比较时，也可以使用引擎条件下推 <：
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
其他受支持的发动机状态下推比较包括：
column[NOT] LIKEpattern
pattern必须是包含要匹配的模式的字符串文字；有关语法，请参阅第 12.8.1 节，“字符串比较函数和运算符”。
columnIS [NOT] NULLcolumnIN (value_list)
中的每一项都value_list 必须是一个常量的文字值。
columnBETWEENconstant1ANDconstant2
constant1并且 constant2每个都必须是一个常量，文字值。
在前面列表中的所有情况下，条件都可以转换为列和常数之间的一个或多个直接比较的形式。
默认情况下启用引擎状态下推。要在服务器启动时禁用它，请将 optimizer_switch系统变量的 engine_condition_pushdown 标志设置为off. 例如，在一个 my.cnf文件中，使用这些行：
[mysqld]
optimizer_switch=engine_condition_pushdown=off
在运行时，禁用条件下推，如下所示：
SET optimizer_switch='engine_condition_pushdown=off';
限制。 发动机状态下推受到以下限制：
- 引擎条件下推仅由 NDB存储引擎支持。
- 列只能与常数进行比较；但是，这包括计算为常数值的表达式。
- 比较中使用的列不能是任何 BLOB或 TEXT类型。此排除也扩展到JSON、 BIT和 ENUM列。
- 要与列进行比较的字符串值必须使用与列相同的排序规则。
- 不直接支持连接；涉及多个表的条件尽可能单独推送。使用扩展EXPLAIN输出来确定哪些条件实际上被下推。请参阅 第 8.8.3 节，“扩展 EXPLAIN 输出格式”。
8.2.1.5 索引条件下推优化
索引条件下推 (ICP) 是针对 MySQL 使用索引从表中检索行的情况的优化。如果没有 ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，由 MySQL 服务器评估WHERE行的条件。启用 ICP 后，如果部分 WHERE条件可以仅使用索引中的列进行评估，则 MySQL 服务器会推送这部分WHERE条件下降到存储引擎。然后，存储引擎通过使用索引条目来评估推送的索引条件，并且只有在满足这一条件时才从表中读取行。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。
索引条件下推优化的适用性受以下条件限制：
- 当需要访问整个表行时 ， ICP 用于 range、 ref、 eq_ref和 访问方法。ref_or_null
- ICP可以用于InnoDB andMyISAM表，包括分区表
InnoDB和MyISAM表。 - 对于
InnoDB表，ICP 仅用于二级索引。ICP 的目标是减少全行读取次数，从而减少 I/O 操作。对于InnoDB聚集索引，完整的记录已经读入InnoDB缓冲区。在这种情况下使用 ICP 不会减少 I/O。 - 在虚拟生成列上创建的二级索引不支持 ICP。
InnoDB支持虚拟生成列的二级索引。 - 引用子查询的条件不能下推。
- 引用存储功能的条件不能被按下。存储引擎不能调用存储的函数。
- 触发条件不能下推。（有关触发条件的信息，请参阅 第 8.2.2.3 节，“使用 EXISTS 策略优化子查询”。）
要了解这种优化是如何工作的，首先要考虑在不使用索引条件下推时索引扫描是如何进行的：
- 获取下一行，首先通过读取索引元组，然后使用索引元组定位并读取全表行。
- 测试
WHERE适用于该表的条件部分。根据测试结果接受或拒绝该行。
使用索引条件下推，扫描会像这样进行：
- 获取下一行的索引元组（但不是整个表行）。
- 测试
WHERE适用于该表的条件部分，并且只能使用索引列进行检查。如果条件不满足，则继续下一行的索引元组。 - 如果满足条件，则使用索引元组定位并读取全表行。
WHERE测试适用于该表 的条件的其余部分。根据测试结果接受或拒绝该行。
EXPLAIN当使用索引条件下推时，输出显示 Using index condition在 Extra列中。它没有显示Using index ，因为这不适用于必须读取完整表行时。
假设一个表包含有关人员及其地址的信息，并且该表具有定义为 的索引 INDEX (zipcode, lastname, firstname)。如果我们知道一个人的zipcode价值但不确定姓氏，我们可以这样搜索：
SELECT * FROM people
WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';MySQL 可以使用索引来扫描具有 zipcode='95054'. 第二部分 ( lastname LIKE '%etrunia%') 不能用于限制必须扫描的行数，因此如果没有索引条件下推，此查询必须检索所有拥有 zipcode='95054'.
使用索引条件下推，MySQL lastname LIKE '%etrunia%'在读取整个表行之前检查该部分。zipcode这避免了读取与匹配条件但不 匹配条件的索引元组对应的完整行 lastname。
默认情况下启用索引条件下推。可以 通过设置 标志 用optimizer_switch系统变量 控制：index_condition_pushdown
SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';
8.2.1.6 嵌套循环连接算法
一个简单的嵌套循环连接 (NLJ) 算法一次从循环中的第一个表读取行，将每一行传递给处理连接中的下一个表的嵌套循环。只要还有要连接的表，这个过程就会重复多次。
假设 要使用以下连接类型执行 三个表t1、t2和 之间的连接：t3
Table Join Typet1 ranget2 reft3 ALL如果使用简单的 NLJ 算法，则连接处理如下：
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 } }}
因为 NLJ 算法一次将一行从外循环传递到内循环，所以它通常会多次读取在内循环中处理的表。
块嵌套循环 (BNL) 连接算法使用在外部循环中读取的行的缓冲来减少必须读取内部循环中的表的次数。例如，如果将 10 行读入缓冲区并将缓冲区传递到下一个内部循环，则可以将内部循环中读取的每一行与缓冲区中的所有 10 行进行比较。这将必须读取内部表的次数减少了一个数量级。
MySQL 连接缓冲具有以下特点：
- ALL当连接类型为或 时，可以使用连接缓冲 index（换句话说，当没有可能的键可用时，并且分别对数据行或索引行进行了完整扫描），或 range。缓冲的使用也适用于外连接，如第 8.2.1.11 节“阻止嵌套循环和批量键访问连接”中所述。
- 永远不会为第一个非常量表分配连接缓冲区，即使它的类型是 ALLor index。
- 只有对连接感兴趣的列存储在其连接缓冲区中，而不是整行。
- join_buffer_size 系统变量确定用于处理查询的每个连接缓冲区的大小 。
- 为每个可以缓冲的连接分配一个缓冲区，因此可以使用多个连接缓冲区处理给定的查询。
- 连接缓冲区在执行连接之前分配，并在查询完成后释放。
对于前面描述的用于 NLJ 算法（无缓冲）的示例连接，连接是使用连接缓冲完成的，如下所示：
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 } }}
如果S是连接缓冲区中每个存储 t1、t2组合的大小，C是缓冲区中组合的数量，t3则扫描表的次数为：
(S * C)/join_buffer_size + 1
扫描次数t3随着 值的join_buffer_size 增加而减少，直到 join_buffer_size大到足以容纳所有先前的行组合。在这一点上，通过增大它不会获得速度。
8.2.1.7 嵌套连接优化
表达连接的语法允许嵌套连接。以下讨论涉及 第 13.2.9.2 节，“JOIN 子句”中描述的连接语法。
的语法table_factor与 SQL 标准相比得到了扩展。后者只接受table_reference，而不是一对括号内的列表。table_reference如果我们认为项目列表中的每个逗号都等效于内部连接，则这是一个保守的扩展 。例如：
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)相当于：
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)在 MySQL 中，CROSS JOIN在语法上等价于INNER JOIN; 他们可以互相替换。在标准 SQL 中，它们是不等价的。 INNER JOIN与 ON子句一起使用；CROSS JOIN以其他方式使用。
通常，在仅包含内连接操作的连接表达式中可以忽略括号。考虑这个连接表达式：
t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ON t1.a=t2.a在删除括号并将操作分组到左侧之后，该连接表达式转换为以下表达式：
(t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3
ON t2.b=t3.b OR t2.b IS NULL然而，这两种表达方式并不等价。要查看这一点，假设表t1、 t2和t3具有以下状态：
- 表
t1包含行(1)，(2) - 表
t2包含行(1,101) - 表
t3包含行(101)
在这种情况下，第一个表达式返回一个包含行(1,1,101,101), 的结果集(2,NULL,NULL,NULL)，而第二个表达式返回行(1,1,101,101), (2,NULL,NULL,101)：
mysql> SELECT *
FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL) ON t1.a=t2.a;+------+------+------+------+
| a | a | b | b |
+------+------+------+------+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | NULL |
+------+------+------+------+
mysql> SELECT *
FROM (t1 LEFT JOIN t2 ON t1.a=t2.a) LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL;+------+------+------+------+
| a | a | b | b |
+------+------+------+------+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | 101 |
+------+------+------+------+
在以下示例中，外连接操作与内连接操作一起使用：
t1 LEFT JOIN (t2, t3) ON t1.a=t2.a该表达式不能转换为以下表达式：
t1 LEFT JOIN t2 ON t1.a=t2.a, t3对于给定的表状态，两个表达式返回不同的行集：
mysql> SELECT *
FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a;+------+------+------+------+
| a | a | b | b |
+------+------+------+------+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | NULL |
+------+------+------+------+
mysql> SELECT *
FROM t1 LEFT JOIN t2 ON t1.a=t2.a, t3;+------+------+------+------+
| a | a | b | b |
+------+------+------+------+
| 1 | 1 | 101 | 101 |
| 2 | NULL | NULL | 101 |
+------+------+------+------+
因此，如果我们在带有外连接运算符的连接表达式中省略括号，我们可能会更改原始表达式的结果集。
更准确地说，我们不能忽略左外连接操作的右操作数和右连接操作的左操作数中的括号。换句话说，我们不能忽略外连接操作的内表表达式的括号。可以忽略其他操作数（外部表的操作数）的括号。
以下表达式：
(t1,t2) LEFT JOIN t3 ON P(t2.b,t3.b)
对于任何表和属性上的t1,t2,t3任何条件 ， 都等效于此表达式 和： Pt2.bt3.b
t1, t2 LEFT JOIN t3 ON P(t2.b,t3.b)每当连接表达式 ( ) 中连接操作的执行顺序joined_table不是从左到右时，我们就讨论嵌套连接。考虑以下查询：
SELECT * FROM t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b) ON t1.a=t2.a
WHERE t1.a > 1SELECT * FROM t1 LEFT JOIN (t2, t3) ON t1.a=t2.a
WHERE (t2.b=t3.b OR t2.b IS NULL) AND t1.a > 1这些查询被认为包含这些嵌套连接：
t2 LEFT JOIN t3 ON t2.b=t3.bt2, t3在第一个查询中，嵌套连接由左连接操作构成。在第二个查询中，它是通过内部连接操作形成的。
在第一个查询中，括号可以省略：连接表达式的语法结构规定了连接操作的相同执行顺序。对于第二个查询，括号不能省略，尽管此处的连接表达式可以在没有它们的情况下明确解释。在我们的扩展语法中，(t2, t3)第二个查询中的括号是必需的，尽管理论上可以在没有它们的情况下解析查询：我们仍然会为查询提供明确的句法结构，因为LEFT JOIN并且ON 扮演表达式的左右分隔符的角色(t2,t3)。
前面的例子说明了以下几点：
- 对于只涉及内连接（而不是外连接）的连接表达式，可以删除括号并从左到右计算连接。事实上，表格可以按任何顺序进行评估。
- 通常，对于外部联接或外部联接与内部联接混合，情况并非如此。删除括号可能会改变结果。
具有嵌套外部联接的查询以与具有内部联接的查询相同的管道方式执行。更准确地说，利用了嵌套循环连接算法的变体。回忆一下嵌套循环连接执行查询的算法（参见第 8.2.1.6 节，“嵌套循环连接算法”）。假设对 3 个表的连接查询T1,T2,T3具有以下形式：
SELECT * FROM T1 INNER JOIN T2 ON P1(T1,T2)
INNER JOIN T3 ON P2(T2,T3) WHERE P(T1,T2,T3)在这里，P1(T1,T2)and P2(T3,T3)是一些连接条件（关于表达式），whileP(T1,T2,T3)是表列的条件T1,T2,T3。
嵌套循环连接算法将以下列方式执行此查询：
FOR each row t1 in T1 { FOR each row t2 in T2 such that P1(t1,t2) { FOR each row t3 in T3 such that P2(t2,t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } } }}
该符号表示通过连接行、和 t1||t2||t3的列构造的行 。在以下某些示例中， 出现表名的位置表示用于该表的每一列的行。例如，表示通过连接行和 的列构造的行，并且 对于 的每一列 。这样的行被称为是 补码的。 t1t2t3NULLNULLt1||t2||NULLt1t2NULLt3NULL
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON P2(T2,T3)) ON P1(T1,T2) WHERE P(T1,T2,T3)对于此查询，修改嵌套循环模式以获得：
FOR each row t1 in T1 { BOOL f1:=FALSE; FOR each row t2 in T2 such that P1(t1,t2) { BOOL f2:=FALSE; FOR each row t3 in T3 such that P2(t2,t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } f2=TRUE; f1=TRUE; } IF (!f2) { IF P(t1,t2,NULL) { t:=t1||t2||NULL; OUTPUT t; } f1=TRUE; } } IF (!f1) { IF P(t1,NULL,NULL) { t:=t1||NULL||NULL; OUTPUT t; } }}
通常，对于外连接操作中第一个内表的任何嵌套循环，都会引入一个标志，该标志在循环之前关闭并在循环之后进行检查。当外部表中的当前行与表示内部操作数的表匹配时，该标志被打开。如果在循环周期结束时标志仍然关闭，则没有为外部表的当前行找到匹配项。NULL在这种情况下，该行由内部表的列的值补充 。结果行被传递给输出的最终检查或下一个嵌套循环，但前提是该行满足所有嵌入式外部连接的连接条件。
在示例中，嵌入了由以下表达式表示的外连接表：
(T2 LEFT JOIN T3 ON P2(T2,T3))
对于带有内连接的查询，优化器可以选择不同的嵌套循环顺序，例如：
FOR each row t3 in T3 { FOR each row t2 in T2 such that P2(t2,t3) { FOR each row t1 in T1 such that P1(t1,t2) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } } }}
对于带有外部连接的查询，优化器只能选择外部表循环在内部表循环之前的顺序。因此，对于我们的外连接查询，只有一种嵌套顺序是可能的。对于以下查询，优化器评估两个不同的嵌套。在这两种嵌套中， T1必须在外循环中处理，因为它用于外连接。T2并且 T3用于内部连接，因此必须在内部循环中处理连接。但是，因为连接是内部连接，T2并且 T3可以按任意顺序处理。
SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)
WHERE P(T1,T2,T3)一个嵌套计算T2，然后 T3：
FOR each row t1 in T1 { BOOL f1:=FALSE; FOR each row t2 in T2 such that P1(t1,t2) { FOR each row t3 in T3 such that P2(t1,t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } f1:=TRUE } } IF (!f1) { IF P(t1,NULL,NULL) { t:=t1||NULL||NULL; OUTPUT t; } }}
另一个嵌套计算T3，然后 T2：
FOR each row t1 in T1 { BOOL f1:=FALSE; FOR each row t3 in T3 such that P2(t1,t3) { FOR each row t2 in T2 such that P1(t1,t2) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } f1:=TRUE } } IF (!f1) { IF P(t1,NULL,NULL) { t:=t1||NULL||NULL; OUTPUT t; } }}
在讨论内连接的嵌套循环算法时，我们省略了一些对查询执行性能影响可能很大的细节。我们没有提到所谓的 “下推”条件。假设我们的 WHERE条件 P(T1,T2,T3)可以用一个合取公式表示：
P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3).
在这种情况下，MySQL 实际上使用以下嵌套循环算法来执行带有内连接的查询：
FOR each row t1 in T1 such that C1(t1) { FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2) { FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } } }}
您会看到每个连词C1(T1), C2(T2),C3(T3)都被推出最内层循环到最外层循环，在那里可以对其进行评估。如果C1(T1)是一个非常严格的条件，这个条件下推可能会大大减少从表T1 传递到内部循环的行数。因此，查询的执行时间可能会大大提高。
对于具有外部连接的查询，WHERE 只有在发现外部表中的当前行与内部表中的匹配项后，才检查条件。因此，从内部嵌套循环中推出条件的优化不能直接应用于具有外部连接的查询。在这里，我们必须引入条件下推谓词，这些谓词由遇到匹配时打开的标志保护。
回想一下这个带有外连接的例子：
P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3)
例如，使用受保护的下推条件的嵌套循环算法如下所示：
FOR each row t1 in T1 such that C1(t1) { BOOL f1:=FALSE; FOR each row t2 in T2 such that P1(t1,t2) AND (f1?C2(t2):TRUE) { BOOL f2:=FALSE; FOR each row t3 in T3 such that P2(t2,t3) AND (f1&&f2?C3(t3):TRUE) { IF (f1&&f2?TRUE:(C2(t2) AND C3(t3))) { t:=t1||t2||t3; OUTPUT t; } f2=TRUE; f1=TRUE; } IF (!f2) { IF (f1?TRUE:C2(t2) && P(t1,t2,NULL)) { t:=t1||t2||NULL; OUTPUT t; } f1=TRUE; } } IF (!f1 && P(t1,NULL,NULL)) { t:=t1||NULL||NULL; OUTPUT t; }}
一般来说，下推谓词可以从连接条件中提取，例如P1(T1,T2)和 P(T2,T3)。在这种情况下，下推谓词也由一个标志保护，该标志防止检查谓词以查找NULL由相应外连接操作生成的补码行。
如果它是由WHERE条件中的谓词诱导的，则禁止在同一个嵌套连接中从一个内部表到另一个内部表的键访问。
8.2.1.8 外连接优化
MySQL 实现如下： A LEFT JOIN B join_specification
- 表
B设置为依赖表A和依赖的所有表A。 - 表
A设置为依赖于条件B中使用的所有表（除了 ）LEFT JOIN。 - 该
LEFT JOIN条件用于决定如何从表中检索行B。（换句话说，WHERE不使用子句中的任何条件。） - 执行所有标准连接优化，除了一个表总是在它所依赖的所有表之后被读取。如果存在循环依赖，则会发生错误。
- 执行所有标准
WHERE优化。 - 如果存在与子句
A匹配的行WHERE，但没有与条件B匹配的 行，则会生成ON一个额外的 行，所有列都设置为。BNULL - 如果您使用
LEFT JOIN查找某些表中不存在的行并且您有以下测试：col_nameIS NULL在声明为的列中WHERE， MySQL 在找到后停止搜索更多行（对于特定的组合键）符合条件 的一行。col_nameNOT NULLLEFT JOIN
该RIGHT JOIN实现类似于LEFT JOIN颠倒表角色的实现。如第 8.2.1.9 节“外部连接简化”中所述，右连接将转换为等效的左连接。
对于 a LEFT JOIN，如果 生成的行的WHERE条件始终为 false ，则将更改为内部联接。例如， 以下查询中的子句 if are 为false ： NULLLEFT JOINWHEREt2.column1NULL
SELECT * FROM t1 LEFT JOIN t2 ON (column1) WHERE t2.column2=5;
因此，将查询转换为内连接是安全的：
SELECT * FROM t1, t2 WHERE t2.column2=5 AND t1.column1=t2.column1;
现在优化器可以在表t2前使用表，t1如果这样做会产生更好的查询计划。要提供有关表连接顺序的提示，请使用STRAIGHT_JOIN; 请参阅 第 13.2.9 节，“SELECT 语句”。但是， STRAIGHT_JOIN可能会阻止使用索引，因为它禁用了半连接转换；请参阅 第 8.2.2.1 节，“使用半连接转换优化子查询、派生表和视图引用”。
8.2.1.9 外连接简化
FROM在许多情况下，查询子句中的 表表达式被简化了。
在解析器阶段，具有右外连接操作的查询被转换为仅包含左连接操作的等效查询。在一般情况下，执行转换使得该右连接：
(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)
变成这个等价的左连接：
(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)
表单的所有内部连接表达式T1 INNER JOIN T2 ON P(T1,T2)都被替换为 list T1,T2，P(T1,T2)作为WHERE条件的联合体（或嵌入连接的连接条件，如果有的话）。
当优化器评估外部连接操作的计划时，它只考虑对于每个这样的操作，外部表在内部表之前被访问的计划。优化器的选择是有限的，因为只有这样的计划才能使用嵌套循环算法执行外连接。
考虑这种形式的查询，其中R(T2) 大大缩小了 table 匹配行的数量 T2：
SELECT * T1 FROM T1
LEFT JOIN T2 ON P1(T1,T2) WHERE P(T1,T2) AND R(T2)如果查询是按写入执行的，优化器别无选择，只能先访问限制较少的表 T1，然后再访问限制较多的表 T2，这可能会产生非常低效的执行计划。
WHERE相反，如果条件被拒绝为空 ，MySQL 会将查询转换为没有外连接操作的查询。（也就是说，它将外连接转换为内连接。）如果条件求值为 FALSE或UNKNOWN为操作生成的任何 NULL补码行，则称该条件对于外连接操作是空拒绝的。
T1 LEFT JOIN T2 ON T1.A=T2.A诸如此类的条件被拒绝为空，因为它们对于任何NULL补码行（ T2列设置为NULL）都不能为真：
T2.B IS NOT NULLT2.B > 3T2.C <= T1.CT2.B < 2 OR T2.C > 1诸如此类的条件不会被 null 拒绝，因为它们可能对于NULL-complemented 行是正确的：
T2.B IS NULLT1.B < 3 OR T2.B IS NOT NULLT1.B < 3 OR T2.B > 3检查外连接操作的条件是否为空拒绝的一般规则很简单：
- 它的形式是
A IS NOT NULL，其中A是任何内部表的属性 - 它是一个谓词，包含对内部表的引用，该内部表评估
UNKNOWN其参数之一何时为NULL - 它是一个包含一个 null 拒绝条件作为连词的连词
- 它是 null 拒绝条件的析取
对于查询中的一个外连接操作，条件可以为空拒绝，而对另一个条件不能为空拒绝。在此查询中，WHERE第二个外部联接操作的条件为 null 拒绝，但第一个外部连接操作的条件不为 null 拒绝：
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
LEFT JOIN T3 ON T3.B=T1.B WHERE T3.C > 0如果WHERE查询中的外连接操作条件为空拒绝，则外连接操作被内连接操作替换。
例如，在前面的查询中，第二个外连接是 null 拒绝的，可以用内连接代替：
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
INNER JOIN T3 ON T3.B=T1.B WHERE T3.C > 0对于原始查询，优化器只评估与单表访问顺序兼容的计划 T1,T2,T3。对于重写的查询，它还考虑了访问顺序 T3,T1,T2。
一个外连接操作的转换可能会触发另一个外连接操作的转换。因此，查询：
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
LEFT JOIN T3 ON T3.B=T2.B WHERE T3.C > 0首先转换为查询：
SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
INNER JOIN T3 ON T3.B=T2.B WHERE T3.C > 0这相当于查询：
SELECT * FROM (T1 LEFT JOIN T2 ON T2.A=T1.A), T3
WHERE T3.C > 0 AND T3.B=T2.B剩余的外连接操作也可以用内连接代替，因为条件T3.B=T2.B 被拒绝为空。这会导致查询完全没有外部连接：
SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3
WHERE T3.C > 0 AND T3.B=T2.B有时优化器成功替换了嵌入外连接操作，但无法转换嵌入外连接。以下查询：
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON T3.B=T2.B) ON T2.A=T1.A WHERE T3.C > 0转换为：
SELECT * FROM T1 LEFT JOIN
(T2 INNER JOIN T3 ON T3.B=T2.B) ON T2.A=T1.A WHERE T3.C > 0只能将其重写为仍包含嵌入外连接操作的表单：
SELECT * FROM T1 LEFT JOIN
(T2,T3) ON (T2.A=T1.A AND T3.B=T2.B) WHERE T3.C > 0任何在查询中转换嵌入外连接操作的尝试都必须考虑嵌入外连接的连接条件和 WHERE条件。在这个查询中，嵌入外连接的 WHERE条件不是拒绝空值，但是嵌入外连接的连接条件T2.A=T1.A AND T3.C=T1.C是拒绝空值：
SELECT * FROM T1 LEFT JOIN
(T2 LEFT JOIN T3 ON T3.B=T2.B) ON T2.A=T1.A AND T3.C=T1.C WHERE T3.D > 0 OR T1.D > 0因此，查询可以转换为：
SELECT * FROM T1 LEFT JOIN
(T2, T3) ON T2.A=T1.A AND T3.C=T1.C AND T3.B=T2.B WHERE T3.D > 0 OR T1.D > 08.2.1.10 多范围读取优化
当表很大并且未存储在存储引擎的缓存中时，使用二级索引上的范围扫描读取行可能会导致对基表的许多随机磁盘访问。借助磁盘扫描多范围读取 (MRR) 优化，MySQL 尝试通过首先仅扫描索引并收集相关行的键来减少范围扫描的随机磁盘访问次数。然后对键进行排序，最后使用主键的顺序从基表中检索行。Disk-sweep MRR 的动机是减少随机磁盘访问的次数，而是实现对基表数据的更顺序扫描。
多范围读取优化提供了以下好处：
- MRR 允许基于索引元组按顺序访问数据行，而不是按随机顺序访问。服务器获取一组满足查询条件的索引元组，按照数据行ID顺序进行排序，并使用排序后的元组按顺序检索数据行。这使得数据访问更高效且成本更低。
- MRR 支持对需要通过索引元组访问数据行的操作的键访问请求进行批处理，例如范围索引扫描和使用索引作为连接属性的等连接。MRR 遍历一系列索引范围以获得合格的索引元组。随着这些结果的累积，它们用于访问相应的数据行。在开始读取数据行之前不必获取所有索引元组。
在虚拟生成列上创建的二级索引不支持 MRR 优化。 InnoDB支持虚拟生成列的二级索引。
以下场景说明了 MRR 优化何时可以发挥优势：
场景 A：MRR 可用于索引范围扫描和等值连接操作的表 InnoDB。 MyISAM
- 索引元组的一部分在缓冲区中累积。
- 缓冲区中的元组按其数据行 ID 排序。
- 根据排序的索引元组序列访问数据行。
场景 B：MRR 可用于 NDB表进行多范围索引扫描或通过属性执行 equi-join。
- 范围的一部分，可能是单键范围，累积在提交查询的中央节点上的缓冲区中。
- 范围被发送到访问数据行的执行节点。
- 访问的行被打包成包并发回中心节点。
- 接收到的带有数据行的包被放置在缓冲区中。
- 从缓冲区中读取数据行。
使用 MRR 时Extra，输出中的列 EXPLAIN显示 Using MRR。
InnoDBMyISAM如果不需要访问全表行来生成查询结果，则不要使用 MRR。如果结果可以完全基于索引元组中的信息（通过覆盖索引）产生，就是这种情况；MRR 没有任何好处。
两个optimizer_switch系统变量标志提供了使用 MRR 优化的接口。该mrr标志控制是否启用 MRR。如果 mrr启用 ( on)，则该 mrr_cost_based标志控制优化器是尝试在使用和不使用 MRR ( on) 之间做出基于成本的选择，还是尽可能使用 MRR ( off)。默认情况下，mrr是 on和 mrr_cost_based是 on。请参阅 第 8.9.2 节，“可切换的优化”。
对于 MRR，存储引擎使用 read_rnd_buffer_size系统变量的值作为它可以为其缓冲区分配多少内存的指导。引擎最多使用 read_rnd_buffer_size字节并确定一次处理中要处理的范围数。
8.2.1.11 阻止嵌套循环和批量密钥访问连接
在 MySQL 中，可以使用批处理键访问 (BKA) 连接算法，该算法使用对连接表的索引访问和连接缓冲区。BKA 算法支持内连接、外连接和半连接操作，包括嵌套外连接。BKA 的好处包括由于更有效的表扫描而提高了连接性能。此外，以前仅用于内部联接的块嵌套循环 (BNL) 联接算法得到扩展，可用于外部联接和半联接操作，包括嵌套外部联接。
以下部分讨论作为原始 BNL 算法、扩展 BNL 算法和 BKA 算法扩展基础的连接缓冲区管理。有关半连接策略的信息，请参阅第 8.2.2.1 节，“使用半连接转换优化子查询、派生表和视图引用”
MySQL 不仅可以使用连接缓冲区来执行没有索引访问内表的内连接，还可以执行子查询展平后出现的外连接和半连接。此外，当对内部表进行索引访问时，可以有效地使用连接缓冲区。
连接缓冲区管理代码在存储感兴趣的行列的值时更有效地利用连接缓冲区空间：如果行列的值为 ，则不会在缓冲区中分配额外 NULL的字节，并且为任何值分配最小字节数VARCHAR类型 。
该代码支持两种类型的缓冲区，常规的和增量的。假设使用连接缓冲区B1 连接表t1， t2并且此操作的结果t3使用连接缓冲区 与表连接B2：
- 常规连接缓冲区包含来自每个连接操作数的列。如果
B2是常规连接缓冲区，则r放入B2的每一行都由r1from 的一行的列和 tableB1的匹配行的有趣列组成。r2t3 - 增量连接缓冲区仅包含第二个连接操作数生成的表行中的列。也就是说，它从第一个操作数缓冲区递增到一行。如果
B2是一个增量连接缓冲区，它包含该行的有趣列以及r2指向该行的r1链接B1。
增量连接缓冲区始终相对于先前连接操作的连接缓冲区是增量的，因此第一个连接操作的缓冲区始终是常规缓冲区。在刚刚给出的示例中，B1 用于连接表t1的 缓冲区t2必须是常规缓冲区。
用于连接操作的增量缓冲区的每一行仅包含要连接的表中一行中感兴趣的列。这些列通过对第一个连接操作数生成的表中匹配行的感兴趣列的引用进行扩充。增量缓冲区中的几行可以引用同一行 r，其列存储在先前的连接缓冲区中，只要所有这些行都与 row 匹配 r。
增量缓冲区可以减少从用于先前连接操作的缓冲区中复制列的频率。这节省了缓冲区空间，因为在一般情况下，第一个连接操作数产生的行可以与第二个连接操作数产生的几行匹配。没有必要从第一个操作数复制几行。由于复制时间的减少，增量缓冲区还可以节省处理时间。
系统变量的block_nested_loop和 batched_key_access标志optimizer_switch 控制优化器如何使用 Block Nested-Loop 和 Batched Key Access 连接算法。默认情况下，block_nested_loop 是on和 batched_key_access是 off。请参阅 第 8.9.2 节，“可切换的优化”。也可以应用优化器提示；请参阅 块嵌套循环和批量密钥访问算法的优化器提示。
有关半连接策略的信息，请参阅 第 8.2.2.1 节，“使用半连接转换优化子查询、派生表和视图引用”
MySQL BNL 算法的原始实现被扩展为支持外连接和半连接操作。
当使用连接缓冲区执行这些操作时，放入缓冲区的每一行都提供一个匹配标志。
如果使用连接缓冲区执行外部连接操作，则检查由第二个操作数生成的表的每一行是否与连接缓冲区中的每一行匹配。当找到匹配项时，将形成一个新的扩展行（原始行加上来自第二个操作数的列）并发送给剩余的连接操作进一步扩展。此外，启用缓冲区中匹配行的匹配标志。在检查了要连接的表的所有行之后，扫描连接缓冲区。缓冲区中未启用匹配标志的每一行都由NULL补码扩展 (NULL第二个操作数中每一列的值）并由剩余的连接操作发送以供进一步扩展。
系统变量的block_nested_loop标志optimizer_switch 控制优化器如何使用块嵌套循环算法。默认情况下， block_nested_loop是 on. 请参阅 第 8.9.2 节，“可切换的优化”。也可以应用优化器提示；请参阅 块嵌套循环和批量密钥访问算法的优化器提示。
在输出中，当 值包含且值为 、 或 EXPLAIN时，表示对表使用 BNL 。 ExtraUsing join buffer (Block Nested Loop)typeALLindexrange
一些涉及一个或多个子查询与一个或多个左连接的组合的情况，尤其是那些返回许多行的情况，可能会使用 BNL，即使在这种情况下它并不理想。这是一个在 MySQL 8.0 中修复的已知问题。如果升级 MySQL 对您来说不是立即可行，您可能希望同时禁用 BNL，方法是设置 optimizer_switch='block_nested_loop=off' 或使用NO_BNL 优化器提示让优化器选择更好的计划，使用一个或多个索引提示（请参阅 第 8.9.4 节，“索引提示”）或这些的某种组合，以提高此类查询的性能。
有关半连接策略的信息，请参阅 第 8.2.2.1 节，“使用半连接转换优化子查询、派生表和视图引用”
MySQL 实现了一种连接表的方法，称为批量密钥访问 (BKA) 连接算法。当对第二个连接操作数生成的表进行索引访问时，可以应用 BKA。与 BNL 连接算法一样，BKA 连接算法使用连接缓冲区来累积连接操作的第一个操作数产生的行的感兴趣列。然后 BKA 算法为缓冲区中的所有行构建键来访问要连接的表，并将这些键批量提交给数据库引擎进行索引查找。密钥通过多范围读取 (MRR) 接口提交给引擎（请参阅 第 8.2.1.10 节，“多范围读取优化”）。提交键后，MRR 引擎函数以最佳方式在索引中执行查找，获取通过这些键找到的连接表的行，并开始为 BKA 连接算法提供匹配的行。每个匹配的行都与连接缓冲区中的行的引用相结合。
使用 BKA 时， 的值 join_buffer_size定义了对存储引擎的每个请求中密钥批次的大小。缓冲区越大，对连接操作的右侧表进行的顺序访问就越多，这可以显着提高性能。
要使用 BKA，系统变量的 batched_key_access标志optimizer_switch必须设置为on。BKA 使用 MRR，所以mrr标志也必须是on. 目前，MRR的成本估算过于悲观。因此，也有必要 mrr_cost_based使用 offBKA。以下设置启用 BKA：
mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
MRR 函数有两种执行情况：
- 第一种场景用于传统的基于磁盘的存储引擎， InnoDB例如 MyISAM. 对于这些引擎，通常来自连接缓冲区的所有行的键都会立即提交给 MRR 接口。特定于引擎的 MRR 函数对提交的键执行索引查找，从中获取行 ID（或主键），然后根据 BKA 算法的请求一一获取所有这些选定行 ID 的行。每行都返回一个关联引用，该引用允许访问连接缓冲区中的匹配行。MRR 函数以最佳方式获取行：它们按行 ID（主键）顺序获取。这提高了性能，因为读取是按磁盘顺序而不是随机顺序进行的。
- 第二种场景用于远程存储引擎，例如NDB. 来自连接缓冲区的部分行的键包及其关联由 MySQL 服务器（SQL 节点）发送到 NDB Cluster 数据节点。作为回报，SQL 节点接收一个（或多个）匹配行以及相应关联的包。BKA 连接算法采用这些行并构建新的连接行。然后将一组新的键发送到数据节点，返回的包中的行用于构建新的连接行。该过程继续进行，直到连接缓冲区中的最后一个键被发送到数据节点，并且 SQL 节点已经接收并连接了与这些键匹配的所有行。
在第一种情况下，保留一部分连接缓冲区来存储由索引查找选择的行 ID（主键），并作为参数传递给 MRR 函数。
没有特殊的缓冲区来存储为连接缓冲区中的行构建的键。相反，为缓冲区中的下一行构建键的函数作为参数传递给 MRR 函数。
在输出中，当 值包含且值为 or EXPLAIN时，表示对表使用 BKA 。 ExtraUsing join buffer (Batched Key Access)typerefeq_ref
除了使用 optimizer_switch系统变量来控制优化器在会话范围内使用 BNL 和 BKA 算法之外，MySQL 还支持优化器提示以在每个语句的基础上影响优化器。请参阅 第 8.9.3 节，“优化器提示”。
要使用 BNL 或 BKA 提示为外连接的任何内表启用连接缓冲，必须为外连接的所有内表启用连接缓冲。
8.2.1.12 条件过滤
在连接处理中，前缀行是从连接中的一个表传递到下一个表的那些行。通常，优化器会尝试将具有低前缀计数的表放在连接顺序的早期，以防止行组合的数量快速增加。如果优化器可以使用有关从一个表中选择并传递到下一个表的行的条件信息，它可以更准确地计算行估计并选择最佳执行计划。
在没有条件过滤的情况下，表的前缀行数基于 WHERE子句根据优化器选择的访问方法选择的估计行数。条件过滤使优化器能够使用 WHERE访问方法未考虑的子句中的其他相关条件，从而改进其前缀行计数估计。例如，即使可能有一种基于索引的访问方法可用于从连接中的当前表中选择行，但在连接中的表也可能存在附加条件。WHERE 可以过滤（进一步限制）传递给下一个表的合格行的估计值的子句。
只有在以下情况下，条件才有助于过滤估计：
- 它指的是当前表。
- 它取决于连接序列中早期表中的一个或多个常量值。
- 访问方法尚未考虑到它。
在EXPLAIN输出中，该 rows列指示所选访问方法的行估计，该filtered 列反映条件过滤的效果。 filtered值以百分比表示。最大值为 100，这意味着没有过滤行。从 100 开始减小的值表示过滤量增加。
前缀行数（估计从连接中的当前表传递到下一个表的行数）是rows和 filtered值的乘积。也就是说，前缀行数是估计的行数，减去估计的过滤效果。例如，如果rows为 1000 且filtered为 20%，则条件过滤将估计的 1000 行数减少为 1000 × 20% = 1000 × .2 = 200 的前缀行数。
考虑以下查询：
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';假设数据集具有以下特征：
- 该
employee表有 1024 行。 - 该
department表有 12 行。 - 两个表都有一个索引
dept_no。 - 该
employee表在 上有一个索引first_name。 - 8 行满足以下条件
employee.first_name：
employee.first_name = 'John'- 150 行满足以下条件
employee.hire_date：
employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'- 1 行满足两个条件：
employee.first_name=
AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'
如果没有条件过滤， EXPLAIN会产生如下输出：
+----+------------+--------+------------------+---------+---------+------+----------+| 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 |+----+------------+--------+------------------+---------+---------+------+----------+对于employee，索引上的访问方法会 name选择与名称匹配的 8 行'John'。未进行过滤（filtered为 100%），因此所有行都是下一个表的前缀行：前缀行数为 rows× filtered= 8 × 100% = 8。
使用条件过滤，优化器还考虑WHERE 访问方法未考虑的子句中的条件。BETWEEN 在这种情况下，优化器使用启发式方法来估计条件 on的过滤效果为 16.31% employee.hire_date。结果，EXPLAIN产生如下输出：
+----+------------+--------+------------------+---------+---------+------+----------+| 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 |+----+------------+--------+------------------+---------+---------+------+----------+现在前缀行数是rows× filtered= 8 × 16.31% = 1.3，更接近真实的数据集。
通常，优化器不会为最后一个连接的表计算条件过滤效果（前缀行数减少），因为没有下一个表可以传递行。出现异常 EXPLAIN：为了提供更多信息，过滤效果是针对所有连接表计算的，包括最后一个。
要控制优化器是否考虑其他过滤条件，请使用 系统变量 的condition_fanout_filter标志（请参阅第 8.9.2 节，“可切换优化”）。默认情况下启用此标志，但可以禁用以抑制条件过滤（例如，如果发现特定查询在没有它的情况下会产生更好的性能）。 optimizer_switch
如果优化器高估了条件过滤的效果，性能可能会比不使用条件过滤的情况更差。在这种情况下，这些技术可能会有所帮助：
- 如果一个列没有被索引，那么索引它以便优化器有一些关于列值分布的信息并且可以改进它的行估计。
- 更改连接顺序。实现这一点的方法包括连接顺序优化器提示（参见 第 8.9.3 节，“优化器提示”），
STRAIGHT_JOIN紧跟在SELECT, 和STRAIGHT_JOIN连接运算符之后。 - 禁用会话条件过滤：
SET optimizer_switch = 'condition_fanout_filter=off';
8.2.1.13 IS NULL 优化
MySQL 可以对其 用于 . 例如，MySQL 可以使用索引和范围来搜索 with 。 col_name IS NULLcol_name = constant_valueNULLIS NULL
例子：
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;如果WHERE子句包含 声明为 的列的条件，则 该表达式将被优化掉。这种优化不会发生在列可能无论如何都会产生的情况下（例如，如果它来自 a 右侧的表 ）。 col_name IS NULLNOT NULLNULLLEFT JOIN
MySQL 还可以优化组合 ，这是已解析子查询中常见的一种形式。 显示 何时使用此优化。 col_name = expr OR col_name IS NULLEXPLAINref_or_null
这种优化可以处理IS NULL任何关键部分。
一些经过优化的查询示例，假设列a和 b表有索引t2：
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 ...);ref_or_null首先读取引用键，然后单独搜索具有NULL键值的行。
优化只能处理一个IS NULL级别。在以下查询中，MySQL 仅在表达式上使用键查找，(t1.a=t2.a AND t2.a IS NULL)而不能在 上使用键部分 b：
SELECT * FROM t1, t2
WHERE (t1.a=t2.a AND t2.a IS NULL) OR (t1.b=t2.b AND t2.b IS NULL);8.2.1.14 ORDER BY 优化
本节介绍 MySQL 何时可以使用索引来满足ORDER BY子句，无法使用索引时使用的 filesort操作，以及优化器提供的关于ORDER BY.
如第 8.2.1.17 节“限制查询优化”中所讨论 的，ORDER BY有和无 LIMIT可能以不同的顺序返回行。
在某些情况下，MySQL 可能会使用索引来满足 ORDER BY子句并避免执行filesort 操作时涉及的额外排序。
即使ORDER BY索引不完全匹配索引，也可以使用索引，只要索引的所有未使用部分和所有额外 ORDER BY列都是 WHERE子句中的常量。如果索引不包含查询访问的所有列，则仅当索引访问比其他访问方法便宜时才使用索引。
假设 上有一个索引 ，以下查询可能会使用该索引来解析该 部分。优化器是否真的这样做取决于如果还必须读取不在索引中的列，则读取索引是否比表扫描更有效。 (key_part1, key_part2)ORDER BY
- 在此查询中，索引 on 使优化器能够避免排序：
(key_part1,key_part2)
- SELECT
t1
ORDER BY key_part1, key_part2;但是，查询使用SELECT *，它可能选择比 key_part1和 更多的列key_part2。在这种情况下，扫描整个索引并查找表行以查找不在索引中的列可能比扫描表和排序结果更昂贵。如果是这样，优化器不太可能使用索引。如果 SELECT *仅选择索引列，则使用索引并避免排序。
如果t1是InnoDB 表，则表的主键隐含地是索引的一部分，并且该索引可用于解析 ORDER BY此查询：
SELECT pk, key_part1, key_part2 FROM t1
ORDER BY key_part1, key_part2;- 在这个查询中，
key_part1是常数，所以通过索引访问的所有行都是key_part2有序的，如果子句的选择性足以使索引范围扫描比表扫描便宜， 则索引避免排序：(key_part1,key_part2)WHERE
- SELECT
t1
ORDER BY key_part2;- 在接下来的两个查询中，是否使用索引类似于
DESC前面没有显示的相同查询：
- SELECT
t1 - SELECT
t1
ORDER BY key_part2 DESC;- 在接下来的两个查询中，
key_part1将与一个常数进行比较。如果WHERE子句的选择性足以使索引范围扫描比表扫描便宜，则使用索引：
- SELECT
t1 - SELECT
t1
ORDER BY key_part1 DESC;- 在下一个查询中，
ORDER BY没有 namekey_part1，但所有选择的行都有一个常key_part1量值，因此仍然可以使用索引：
- SELECT
t1
ORDER BY key_part2;在某些情况下，MySQL不能使用索引来解析ORDER BY，尽管它仍然可以使用索引来查找与 WHERE子句匹配的行。例子：
- 查询
ORDER BY在不同的索引上使用：
SELECT * FROM t1 ORDER BY key1, key2;
- 该查询用于
ORDER BY索引的非连续部分：
SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;
- 查询混合
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
t1ORDER
SELECT * FROM t1 ORDER BY -key;
- 该查询连接了许多表，其中的列
ORDER BY并非全部来自用于检索行的第一个非常量表。（这是 EXPLAIN输出中第一个没有const连接类型的表。） - 查询有不同的
ORDER BY和GROUP BY表达式。 ORDER BY仅在子句 中命名的列的前缀上存在索引。在这种情况下，索引不能用于完全解析排序顺序。例如，如果仅对 CHAR(20)列的前 10 个字节进行索引，则索引无法区分超过第 10 个字节的值，filesort因此需要 a。- 索引不按顺序存储行。例如，这适用
HASH于表中的索引MEMORY。
用于排序的索引的可用性可能会受到使用列别名的影响。假设该列 t1.a已编入索引。在此语句中，选择列表中列的名称是 a。它指的是t1.a，就像 中的 引用一样a， 所以可以使用 ORDER BY索引 on ：t1.a
SELECT a FROM t1 ORDER BY a;
在这条语句中，选择列表中列的名称也是a，但它是别名。它指的是ABS(a)，就像 中的 引用一样a，因此不能使用 ORDER BY索引 on ：t1.a
SELECT ABS(a) AS a FROM t1 ORDER BY a;
在以下语句中，ORDER BY 引用的名称不是选择列表中列的名称。t1 但是named中有一个列a，所以可以使用ORDER BY引用t1.a和索引。t1.a（当然，生成的排序顺序可能与 的顺序完全不同 ABS(a)。）
SELECT ABS(a) AS b FROM t1 ORDER BY a;
默认情况下，MySQL对查询进行排序，就好像您也包含在查询中一样。如果你包含一个 包含相同列列表的显式子句，MySQL 会优化它而不会降低速度，尽管排序仍然会发生。 GROUP BY col1, col2, ...ORDER BY col1, col2, ...ORDER BY
如果查询包含GROUP BY但您想避免对结果进行排序的开销，则可以通过指定来抑制排序ORDER BY NULL。例如：
INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;
优化器仍然可以选择使用排序来实现分组操作。ORDER BY NULL 禁止对结果进行排序，而不是通过分组操作来确定结果的先前排序。
GROUP BY默认情况下隐式排序（即，在没有列ASC或 DESC指定符的情况下GROUP BY）。但是，不推荐依赖隐式 GROUP BY排序（即，在没有ASC或 DESC指示符的情况下进行排序）或显式排序GROUP BY（即，对列使用显式ASC或DESC 指示符GROUP BY）。要生成给定的排序顺序，请提供一个 ORDER BY子句。
如果不能使用索引来满足ORDER BY子句，MySQL 会执行 filesort读取表行并对其进行排序的操作。Afilesort在查询执行中构成一个额外的排序阶段。
为了获得filesort操作内存，优化器会预先分配固定数量的 sort_buffer_size字节。各个会话可以根据需要更改此变量的会话值，以避免过度使用内存，或根据需要分配更多内存。
如果filesort结果集太大而无法放入内存，则操作会根据需要使用临时磁盘文件。某些类型的查询特别适合完全在内存中的filesort操作。例如，优化器可以使用 filesort在内存中有效地处理ORDER BY 以下形式的查询（和子查询）的操作，而无需临时文件：
SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;
此类查询在仅显示较大结果集中的几行的 Web 应用程序中很常见。例子：
SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;
对于不使用的 慢ORDER BY查询 ，尝试将 系统变量降低到适合触发 . （将此变量的值设置得太高的症状是磁盘活动高和 CPU 活动低的组合。） filesortmax_length_for_sort_datafilesort
要提高ORDER BY速度，请检查是否可以让 MySQL 使用索引而不是额外的排序阶段。如果这是不可能的，请尝试以下策略：
- 增加 sort_buffer_size 变量值。理想情况下，该值应该足够大，以使整个结果集适合排序缓冲区（以避免写入磁盘和合并通道），但至少该值必须足够大以容纳 15 个元组。（最多可以合并 15 个临时磁盘文件，并且内存中必须有空间用于每个文件至少一个元组。）
考虑到存储在排序缓冲区中的列值的大小受 max_sort_length系统变量值的影响。例如，如果元组存储长字符串列的值并且您增加 的值 max_sort_length，则排序缓冲区元组的大小也会增加，并且可能需要您增加 sort_buffer_size。对于作为字符串表达式的结果计算的列值（例如调用字符串值函数的列值），filesort算法无法确定表达式值的最大长度，因此它必须分配 max_sort_length每个元组的字节数。
要监控合并通道的数量（合并临时文件），请检查 Sort_merge_passes 状态变量。
- 增加 read_rnd_buffer_size 变量值，以便一次读取更多行。
- 更改tmpdir 系统变量以指向具有大量可用空间的专用文件系统。变量值可以列出以循环方式使用的多个路径；您可以使用此功能将负载分散到多个目录中。
:在 Unix 上用冒号 ( ) 分隔路径，;在 Windows 上用分号 ( ) 分隔路径。路径应该命名位于不同 物理磁盘上的文件系统中的目录，而不是同一磁盘上的不同分区。
使用 EXPLAIN （参见第 8.8.1 节，“使用 EXPLAIN 优化查询”），您可以检查 MySQL 是否可以使用索引来解析ORDER BY子句：
- 如果输出的
Extra列 EXPLAIN不包含Using filesort，则使用索引并且filesort不执行 a。 - 如果输出的
Extra列 EXPLAIN包含Using filesort，则不使用索引并filesort执行 a。
此外，如果filesort执行 a，优化器跟踪输出包括一个 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>：这表示排序缓冲区元组是包含原始表行的排序键值和行 ID 的对。元组按排序键值排序，行 ID 用于从表中读取行。<sort_key, additional_fields>：这表示排序缓冲区元组包含排序键值和查询引用的列。元组按排序键值排序，列值直接从元组中读取。<sort_key, packed_additional_fields>: 与前面的变体一样，但附加的列紧密地打包在一起，而不是使用固定长度的编码。
EXPLAIN不区分优化器是否 filesort在内存中执行 a。filesort在优化器跟踪输出中可以看到内存中的使用 。寻找 filesort_priority_queue_optimization。有关优化器跟踪的信息，请参阅 MySQL 内部：跟踪优化器。
8.2.1.15 GROUP BY 优化
满足GROUP BY 子句的最通用方法是扫描整个表并创建一个新的临时表，其中每个组的所有行都是连续的，然后使用此临时表来发现组并应用聚合函数（如果有）。在某些情况下，MySQL 能够做得比这更好，并通过使用索引访问来避免创建临时表。
使用索引的最重要的先决条件 GROUP BY是所有GROUP BY列都引用来自同一索引的属性，并且索引按顺序存储其键（例如，对于BTREE索引，而不是 HASH索引）。临时表的使用是否可以被索引访问替代还取决于查询中使用了索引的哪些部分、为这些部分指定的条件以及所选的聚合函数。
有两种方法可以GROUP BY 通过索引访问来执行查询，如以下部分所述。第一种方法将分组操作与所有范围谓词（如果有）一起应用。第二种方法首先执行范围扫描，然后对结果元组进行分组。
在 MySQL 中，GROUP BY用于排序，因此服务器也可以ORDER BY 对分组进行优化。但是，不推荐使用隐式或显式GROUP BY排序。请参见第 8.2.1.14 节，“按优化排序”。
最有效的处理方式GROUP BY是使用索引直接检索分组列。通过这种访问方法，MySQL 使用了一些索引类型的属性，即键是有序的（例如，BTREE）。此属性允许在索引中使用查找组，而无需考虑索引中满足所有 WHERE条件的所有键。这种访问方法只考虑索引中的一小部分键，因此称为松散索引扫描。当没有WHERE 子句时，Loose Index Scan 读取与组数一样多的键，这可能比所有键的数量小得多。如果WHERE子句包含范围谓词（参见 第 8.8.1 节“使用 EXPLAIN 优化查询”range中对连接类型 的讨论），松散索引扫描查找满足范围条件的每个组的第一个键，并再次读取最小的可能键的数量。这在以下条件下是可能的：
- 查询针对单个表。
- 该
GROUP BY唯一名称是构成该指数并没有其他列的最左边的前缀列。（如果相反的GROUP BY，查询有一个DISTINCT子句，所有不同的属性指的是列形式的最左前缀索引的。）例如，如果一个表t1对的索引(c1,c2,c3)，松散索引扫描是适用如果查询GROUP BY c1, c2。如果查询是不适用GROUP BY c2, c3（列不是最左前缀）或GROUP BY c1, c2, c4（c4不在索引）。 - 选择列表（如果有）中使用的唯一聚合函数是MIN()and MAX()，它们都引用同一列。该列必须在索引中，并且必须紧跟在
GROUP BY. - 除了查询中引用的索引之外，索引的任何其他部分都
GROUP BY必须是常量（即，它们必须以与常量相等的方式引用），除了 MIN()or MAX()函数的参数。 - 对于索引中的列，必须索引完整的列值，而不仅仅是前缀。例如，使用 时
c1 VARCHAR(20), INDEX (c1(10))，索引仅使用值的前缀，c1不能用于松散索引扫描。
如果松散索引扫描适用于查询，则 EXPLAIN输出显示 Using index for group-by在 Extra列中。
假设 idx(c1,c2,c3)table 上 有一个索引t1(c1,c2,c3,c4)。松散索引扫描访问方法可用于以下查询：
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;
由于给出的原因，无法使用此快速选择方法执行以下查询：
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
- The columns in the
GROUP BYclause do not form a leftmost prefix of the index:
SELECT c1, c2 FROM t1 GROUP BY c2, c3;
- The query refers to a part of a key that comes after the
GROUP BYpart, and for which there is no equality with a constant:
SELECT c1, c3 FROM t1 GROUP BY c1, c2;
Were the query to include WHERE c3 = const, Loose Index Scan could be used.
Loose Index Scan 访问方法可以应用于选择列表中其他形式的聚合函数引用，除了已经支持 的MIN()和 引用：MAX()
- AVG(DISTINCT)、 SUM(DISTINCT)和 COUNT(DISTINCT)受支持。AVG(DISTINCT) 并SUM(DISTINCT)采取一个论点。 COUNT(DISTINCT)可以有多个列参数。
- 查询中不能有
GROUP BYorDISTINCT子句。 - 前面描述的松散索引扫描限制仍然适用。
假设 idx(c1,c2,c3)table 上 有一个索引t1(c1,c2,c3,c4)。松散索引扫描访问方法可用于以下查询：
SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;
Tight Index Scan 可以是全索引扫描，也可以是范围索引扫描，具体取决于查询条件。
当不满足松散索引扫描的条件时，仍然可以避免为GROUP BY查询创建临时表。如果子句中有范围条件，则WHERE该方法只读取满足这些条件的键。否则，它将执行索引扫描。因为此方法读取 WHERE子句定义的每个范围内的所有键，或者如果没有范围条件则扫描整个索引，因此称为 紧密索引扫描。使用紧密索引扫描，只有在找到所有满足范围条件的键之后才执行分组操作。
要使此方法起作用，查询中的所有列有一个恒定的相等条件就足够了，该条件引用位于键的部分之前或之间的部分GROUP BY键。来自相等条件的常量填充搜索键中的任何“间隙” ，以便可以形成索引的完整前缀。然后这些索引前缀可用于索引查找。如果GROUP BY结果需要排序，并且可以形成作为索引前缀的搜索键，MySQL也避免了额外的排序操作，因为在有序索引中使用前缀搜索已经按顺序检索了所有键。
假设 idx(c1,c2,c3)table 上 有一个索引t1(c1,c2,c3,c4)。以下查询不适用于前面描述的松散索引扫描访问方法，但仍适用于紧密索引扫描访问方法。
- 中有一个间隙
GROUP BY，但它被条件覆盖c2 = 'a'：
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
- 不是从键的第一部分开始的
GROUP BY，但是有一个条件为该部分提供了一个常数：
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
8.2.1.16 DISTINCT 优化
DISTINCT结合ORDER BY在很多情况下需要一个临时表。
因为DISTINCT可能会使用GROUP BY，了解 MySQL 如何处理 不属于所选列的ORDER BY或子句中的列。HAVING请参阅 第 12.20.3 节，“GROUP BY 的 MySQL 处理”。
在大多数情况下，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;
由于这种等价性，适用于 GROUP BY查询的优化也可以应用于带有DISTINCT子句的查询。因此，有关 DISTINCT查询优化可能性的更多详细信息，请参阅 第 8.2.1.15 节，“GROUP BY 优化”。
与结合使用时 ，MySQL 会在找到唯一行 后立即停止 。LIMIT row_countDISTINCTrow_count
如果您不使用查询中命名的所有表中的列，MySQL 会在找到第一个匹配项后立即停止扫描任何未使用的表。在以下情况下，假设 t1之前使用过t2 （您可以使用 进行检查 ），当 MySQL 在 中找到第一行时，它会 EXPLAIN停止读取t2（对于 中的任何特定行 ） ： t1t2
SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;
8.2.1.17 LIMIT 查询优化
如果您只需要结果集中指定数量的行，LIMIT请在查询中使用子句，而不是获取整个结果集并丢弃额外的数据。
MySQL 有时会优化有子句和无 子句的查询： LIMIT row_countHAVING
- 如果您只选择了几行
LIMIT，MySQL 在某些情况下会使用索引，而通常它更愿意进行全表扫描。 - 如果与 结合使用 ，MySQL 会在找到排序结果的第一行后立即停止排序 ，而不是对整个结果进行排序。如果使用索引进行排序，则速度非常快。如果必须进行文件排序，则选择与不带子句的查询匹配的所有行，并且在找到第一行之前对它们中的大部分或全部进行排序 。在找到初始行之后，MySQL 不对结果集的任何剩余部分进行排序。
LIMITrow_countORDER BYrow_countLIMITrow_count
这种行为的一种表现是， ORDER BY有和没有的查询 LIMIT可能会以不同的顺序返回行，如本节后面所述。
- 如果与 结合使用 ，MySQL 会在找到唯一行 后立即停止。
LIMITrow_countDISTINCTrow_count - 在某些情况下，
GROUP BY可以通过按顺序读取索引（或对索引进行排序），然后计算摘要直到索引值发生变化来解决。在这种情况下，不计算任何不必要 的值。LIMITrow_countGROUP BY - 一旦 MySQL 向客户端发送了所需数量的行，它就会中止查询，除非您使用
SQL_CALC_FOUND_ROWS. 在这种情况下，可以使用 检索行数SELECT FOUND_ROWS()。请参阅 第 12.16 节，“信息功能”。 LIMIT 0快速返回一个空集。这对于检查查询的有效性很有用。它还可用于获取使用 MySQL API 的应用程序中结果列的类型，该 API 使结果集元数据可用。使用 mysql客户端程序，您可以使用 --column-type-info选项显示结果列类型。- 如果服务器使用临时表来解析查询，它会使用该子句来计算需要多少空间。
LIMITrow_count - 如果未使用索引
ORDER BY但LIMIT也存在子句，则优化器可能能够避免使用合并文件并使用内存filesort操作对内存中的行进行排序。
如果多行在列中具有相同的值ORDER BY，则服务器可以自由地以任何顺序返回这些行，并且可能会根据整体执行计划以不同的方式返回。换句话说，这些行的排序顺序对于无序的列是不确定的。
影响执行计划的一个因素是 LIMIT，因此ORDER BY 带有和不带有的查询LIMIT可能会以不同的顺序返回行。考虑这个查询，它按category列排序，但对于id和 rating列是不确定的：
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 |
+----+----------+--------+
包含LIMIT可能会影响每个category值内的行顺序。例如，这是一个有效的查询结果：
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 |
+----+----------+--------+
在每种情况下，行都按ORDER BY列排序，这是 SQL 标准所要求的。
如果确保使用和不使用 的行顺序相同很重要，请LIMIT在子句中包含其他列ORDER BY以使顺序具有确定性。例如，如果id值是唯一的，您可以通过如下排序使给定 category值的行按 id顺序显示：
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 |
+----+----------+--------+
对于带有ORDER BYor GROUP BY和LIMIT 子句的查询，优化器会在默认情况下尝试选择有序索引，因为这样做会加快查询执行速度。在 MySQL 5.7.33 之前，无法覆盖此行为，即使在使用其他优化可能更快的情况下也是如此。从 MySQL 5.7.33 开始，可以通过将 optimizer_switch系统变量的 prefer_ordering_index标志设置为off.
示例：首先我们创建并填充一个表t，如下所示：
# Create and populate a table t:
mysql> CREATE TABLE t (
-> id1 BIGINT NOT NULL,
-> id2 BIGINT NOT NULL,
-> c1 VARCHAR(50) NOT NULL,
-> c2 VARCHAR(50) NOT NULL,
-> PRIMARY KEY (id1),
-> INDEX i (id2, c1)
-> );
# [Insert some rows into table t - not shown]
验证 prefer_ordering_index标志是否已启用：
mysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
| 1 |
+------------------------------------------------------+
由于以下查询有一个LIMIT 子句，我们希望它尽可能使用有序索引。在这种情况下，正如我们从 EXPLAIN输出中看到的那样，它使用了表的主键。
mysql> EXPLAIN SELECT c2 FROM t
-> WHERE id2 > 3
-> ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: index
possible_keys: i
key: PRIMARY
key_len: 8
ref: NULL
rows: 2
filtered: 70.00
Extra: Using where
现在我们禁用该 prefer_ordering_index标志，并重新运行相同的查询；这次它使用索引 i（包括 子句id2中使用的列 WHERE）和文件排序：
mysql> SET optimizer_switch = "prefer_ordering_index=off";
mysql> EXPLAIN SELECT c2 FROM t
-> WHERE id2 > 3
-> ORDER BY id1 ASC LIMIT 2\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: range
possible_keys: i
key: i
key_len: 8
ref: NULL
rows: 14
filtered: 100.00
Extra: Using index condition; Using filesort
另请参见第 8.9.2 节，“可切换的优化”。
8.2.1.18 函数调用优化
MySQL 函数在内部被标记为确定性或非确定性。一个函数是不确定的，如果给定其参数的固定值，它可以为不同的调用返回不同的结果。非确定性函数的示例： RAND(), UUID().
WHERE如果一个函数被标记为非确定性的，则对每一行（从一个表中选择时）或行组合（从多表连接中选择时）评估子句中 对它的引用。
MySQL 还根据参数类型确定何时评估函数，参数是表列还是常量值。每当该列更改值时，必须评估将表列作为参数的确定性函数。
非确定性函数可能会影响查询性能。例如，某些优化可能不可用，或者可能需要更多锁定。以下讨论使用 RAND()但也适用于其他非确定性函数。
假设一个表t有这个定义：
CREATE TABLE t (id INT NOT NULL PRIMARY KEY, col_a VARCHAR(100));
考虑以下两个查询：
SELECT * FROM t WHERE id = POW(1,2);
SELECT * FROM t WHERE id = FLOOR(1 + RAND() * 49);
由于与主键的相等性比较，这两个查询似乎都使用主键查找，但这仅适用于它们中的第一个：
- 第一个查询总是最多产生一行，因为POW()常量参数是一个常量值，用于索引查找。
- 第二个查询包含一个使用非确定性函数的表达式，该函数 RAND()在查询中不是常量，但实际上对于表的每一行都有一个新值
t。因此，查询读取表的每一行，评估每一行的谓词，并输出主键与随机值匹配的所有行。这可能是零、一或多行，具体取决于id列值和 RAND()序列中的值。
不确定性的影响不仅限于 SELECT陈述。此 UPDATE语句使用非确定性函数来选择要修改的行：
UPDATE t SET col_a = some_expr WHERE id = FLOOR(1 + RAND() * 49);
据推测，目的是最多更新主键与表达式匹配的单行。但是，它可能会更新零、一或多行，具体取决于 id列值和 RAND()序列中的值。
刚才描述的行为对性能和复制有影响：
- 由于非确定性函数不会产生常量值，因此优化器不能使用其他可能适用的策略，例如索引查找。结果可能是表扫描。
InnoDB可能会升级为范围键锁，而不是为匹配的行获取单行锁。- 不确定性执行的更新对于复制来说是不安全的。
困难源于这样一个事实，即 RAND()对表的每一行都对函数进行一次评估。为避免多重功能评估，请使用以下技术之一：
- 将包含不确定函数的表达式移至单独的语句，将值保存在变量中。在原始语句中，将表达式替换为对变量的引用，优化器可以将其视为常量值：
- SET
UPDATE t SET col_a = some_expr WHERE id = @keyval;
- 将随机值分配给派生表中的变量。
WHERE此技术导致在子句 中的比较中使用变量之前，为变量赋值一次 ：
- SET
optimizer_switch= - UPDATE
t,dt
SET col_a = some_expr WHERE id = @keyval;
如前所述， WHERE子句中的非确定性表达式可能会阻止优化并导致表扫描。但是，如果其他表达式是确定性的，则可以部分优化WHERE子句。例如：
SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();
如果优化器可以partial_key用来减少所选行的集合， RAND()则执行次数会更少，从而减少不确定性对优化的影响。
8.2.1.19 行构造函数表达式优化
行构造函数允许同时比较多个值。例如，这两个语句在语义上是等价的：
SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;
此外，优化器以相同的方式处理这两个表达式。
如果行构造函数列不覆盖索引的前缀，则优化器不太可能使用可用索引。考虑下表，它有一个主键 (c1, c2, c3)：
CREATE TABLE t1 (
c1 INT, c2 INT, c3 INT, c4 CHAR(100), PRIMARY KEY(c1,c2,c3));
在此查询中，WHERE子句使用索引中的所有列。但是，行构造函数本身不包含索引前缀，因此优化器仅使用c1( key_len=4, 的大小c1)：
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
在这种情况下，使用等效的非构造函数表达式重写行构造函数表达式可能会导致更完整的索引使用。对于给定的查询，行构造函数和等效的非构造函数表达式是：
(c2,c3) > (1,1)
c2 > 1 OR ((c2 = 1) AND (c3 > 1))重写查询以使用非构造函数表达式会导致优化器使用索引 ( key_len=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
因此，为了获得更好的结果，请避免将行构造函数与 AND/OR 表达式混合。使用其中一种。
在某些条件下，优化器可以将范围访问方法应用于IN()具有行构造函数参数的表达式。请参阅 行构造函数表达式的范围优化。
8.2.1.20 避免全表扫描
当 MySQL 使用 全表扫描来解析查询时， 列中EXPLAIN显示 的输出。这通常发生在以下情况下： ALLtype
- 该表是如此之小，以至于执行表扫描比费心查找键要快。这对于少于 10 行且行长度较短的表很常见。
- 索引列 的
ONor子句 中没有可用的限制 。WHERE - 您正在将索引列与常量值进行比较，并且 MySQL 已经计算（基于索引树）常量覆盖了表的太大部分，并且表扫描会更快。请参阅 第 8.2.1.1 节，“WHERE 子句优化”。
- 您正在通过另一列使用具有低基数的键（许多行与键值匹配）。在这种情况下，MySQL 假设通过使用该键可能会执行许多键查找，并且表扫描会更快。
对于小型表，表扫描通常是合适的，性能影响可以忽略不计。对于大型表，请尝试以下技术以避免优化器错误地选择表扫描：
- 用于更新已扫描表的键分布。请参阅 第 13.7.2.1 节，“ANALYZE TABLE 语句”。
ANALYZE TABLEtbl_name - 用于
FORCE INDEX已扫描的表告诉 MySQL 与使用给定索引相比，表扫描非常昂贵：
- SELECT
t1,t2FORCE
WHERE t1.col_name=t2.col_name;请参阅第 8.9.4 节，“索引提示”。
- 使用 选项 启动mysqld--max-seeks-for-key=1000 或用于
SET max_seeks_for_key=1000告诉优化器假设没有键扫描会导致超过 1,000 次键搜索。请参阅第 5.1.7 节，“服务器系统变量”。
查询以SELECT 语句的形式执行数据库中的所有查找操作。调整这些语句是重中之重，无论是实现动态网页的亚秒级响应时间，还是缩短生成大量隔夜报告的时间。
除了SELECT语句之外，查询的调优技术也适用于语句中的结构，如 CREATE TABLE...AS SELECT、 INSERT INTO...SELECT和WHERE子句 DELETE。这些语句有额外的性能考虑，因为它们结合了写操作和面向读的查询操作。
NDB Cluster 支持连接下推优化，其中合格的连接被完整地发送到 NDB Cluster 数据节点，在那里它可以分布在它们之间并并行执行。有关此优化的更多信息，请参阅 NDB 下推连接的条件。
优化查询的主要考虑因素是：
- 要让慢SELECT ... WHERE查询更快，首先要检查是否可以添加 索引。在子句中使用的列上设置索引WHERE，以加快评估、过滤和最终检索结果的速度。为避免浪费磁盘空间，请构建一小组索引来加速应用程序中使用的许多相关查询。
索引对于使用连接和 外键 等功能引用不同表的查询尤其重要 。您可以使用该EXPLAIN语句来确定哪些索引用于 SELECT. 请参阅 第 8.3.1 节，“MySQL 如何使用索引”和 第 8.8.1 节，“使用 EXPLAIN 优化查询”。
- 隔离和调整查询的任何部分，例如函数调用，这会花费过多时间。根据查询的结构，可以为结果集中的每一行调用一次函数，甚至为表中的每一行调用一次函数，这极大地放大了任何低效率。
- 尽量减少查询中的 全表扫描次数 ，尤其是对于大表。
- 通过定期使用该语句使表统计信息保持最新 ANALYZE TABLE，因此优化器拥有构建有效执行计划所需的信息。
- 了解特定于每个表的存储引擎的调优技术、索引技术和配置参数。两者InnoDB都有 MyISAM一套指导方针，用于启用和维持查询的高性能。有关详细信息，请参阅第 8.5.6 节，“优化 InnoDB 查询”和 第 8.6.1 节，“优化 MyISAM 查询”。
- 您可以使用第 8.5.3 节“优化 InnoDB 只读事务”InnoDB中的技术 来优化表的单查询事务 。
- 避免以难以理解的方式转换查询，尤其是在优化器自动执行某些相同的转换时。
- 如果性能问题无法通过基本准则之一轻松解决，请通过阅读 EXPLAIN计划并调整索引、WHERE子句、连接子句等来调查特定查询的内部细节。（当您达到一定的专业水平时，阅读 EXPLAIN计划可能是您每次查询的第一步。）
- 调整 MySQL 用于缓存的内存区域的大小和属性。通过有效使用 InnoDB 缓冲池、 MyISAM键缓存和 MySQL 查询缓存，重复查询运行得更快，因为结果是从内存中检索的第二次和后续时间。
- 即使对于使用缓存内存区域快速运行的查询，您仍然可以进一步优化，以便它们需要更少的缓存内存，从而使您的应用程序更具可扩展性。可扩展性意味着您的应用程序可以处理更多的并发用户、更大的请求等，而不会出现性能大幅下降。
- 处理锁定问题，您的查询速度可能会受到同时访问表的其他会话的影响。
8.2.3 优化 INFORMATION_SCHEMA 查询
数据库应用程序的核心逻辑是通过 SQL 语句执行的，无论是通过解释器直接发出还是通过 API 在后台提交。本节中的调优指南有助于加速各种 MySQL 应用程序。该指南涵盖了读取和写入数据的 SQL 操作、一般 SQL 操作的幕后开销以及在特定场景中使用的操作，例如数据库监控。
8.3 优化和索引
8.3.1 MySQL 如何使用索引
索引用于快速查找具有特定列值的行。如果没有索引，MySQL 必须从第一行开始，然后读取整个表以查找相关行。桌子越大，成本越高。如果表有相关列的索引，MySQL 可以快速确定要在数据文件中间查找的位置，而无需查看所有数据。这比顺序读取每一行要快得多。
大多数 MySQL 索引（PRIMARY KEY、 UNIQUE、INDEX和 FULLTEXT）都存储在 B-trees中。例外：空间数据类型的索引使用 R-trees；MEMORY 表也支持散列索引；InnoDB使用倒排列表作为FULLTEXT索引。
一般来说，索引的使用如以下讨论中所述。第 8.3.8 节“B-Tree 和哈希索引的比较”中描述了哈希索引的特定特征（如 MEMORY表中所用） 。
MySQL 对这些操作使用索引：
- 快速找到匹配
WHERE子句的行。 - 从考虑中消除行。如果在多个索引之间进行选择，MySQL 通常使用找到最少行数的索引（最具 选择性的索引）。
- 如果表有一个多列索引，优化器可以使用索引的任何最左边的前缀来查找行。例如，如果您在 上具有三列索引 ，则您在、 和
(col1, col2, col3)上具有索引搜索功能。有关更多信息，请参阅 第 8.3.5 节，“多列索引”。(col1)(col1, col2)(col1, col2, col3) - 在执行连接时从其他表中检索行。如果将列声明为相同的类型和大小，MySQL 可以更有效地使用列上的索引。在这种情况下， 如果它们被声明为相同的大小，则认为它们是相同的VARCHAR。 CHAR例如，
VARCHAR(10)和CHAR(10)大小相同，但VARCHAR(10)和CHAR(15)不同。
对于非二进制字符串列之间的比较，两列应使用相同的字符集。例如，将utf8列与 latin1列进行比较会排除使用索引。
如果值不能在没有转换的情况下直接比较，则比较不同的列（例如，将字符串列与时间或数字列进行比较）可能会阻止使用索引。对于给定值（例如1 数字列），它可能与字符串列中的任意数量的值（例如 '1'、' 1'、 '00001'或）进行比较'01.e1'。这排除了对字符串列使用任何索引。
- 查找特定索引列的MIN()或 值。这由预处理器优化，该预处理器检查您是否正在使用 索引中之前出现的所有关键部分。在这种情况下，MySQL 为每个or 表达式执行单个键查找并将其替换为常量。如果所有表达式都替换为常量，则查询立即返回。例如： MAX()
key_colWHEREkey_part_N=constantkey_colMIN()MAX()
- SELECT
FROM tbl_name WHERE key_part1=10;- 如果排序或分组是在可用索引的最左前缀（例如， ）上完成的，则对表进行排序或分组。如果所有关键部分后跟，则以相反的顺序读取密钥。请参阅 第 8.2.1.14 节，“ORDER BY 优化”和 第 8.2.1.15 节，“GROUP BY 优化”。
ORDER BYkey_part1,key_part2DESC - 在某些情况下，可以优化查询以在不查阅数据行的情况下检索值。（为查询提供所有必要结果的索引称为 覆盖索引。）如果查询仅使用表中包含在某个索引中的列，则可以从索引树中检索所选值以获得更快的速度：
- SELECT
WHERE key_part1=1对于小表或报表查询处理大部分或所有行的大表的查询，索引不太重要。当查询需要访问大部分行时，顺序读取比通过索引更快。即使查询不需要所有行，顺序读取也可以最大限度地减少磁盘寻道。有关详细信息，请参见第 8.2.1.20 节，“避免全表扫描”。
8.3.2 主键优化
表的主键表示您在最重要的查询中使用的列或列集。它有一个关联的索引，用于快速查询性能。查询性能受益于NOT NULL优化，因为它不能包含任何NULL值。使用InnoDB存储引擎，表数据在物理上被组织起来，可以根据一个或多个主键列进行超快速的查找和排序。
如果您的表很大且很重要，但没有明显的列或列集用作主键，您可以创建一个具有自动递增值的单独列以用作主键。当您使用外键连接表时，这些唯一 ID 可以用作指向其他表中相应行的指针。
8.3.3 外键优化
如果一个表有很多列，并且您查询了许多不同的列组合，那么将不常用的数据拆分为每个包含几列的单独表，然后通过复制数字 ID 将它们关联回主表可能会很有效主表中的列。这样，每个小表都可以有一个主键来快速查找其数据，并且您可以使用连接操作仅查询所需的列集。根据数据的分布方式，查询可能会执行较少的 I/O 并占用较少的高速缓存，因为相关的列在磁盘上打包在一起。（为了最大化性能，查询尝试从磁盘读取尽可能少的数据块；
8.3.4 列索引
最常见的索引类型涉及单个列，将来自该列的值的副本存储在数据结构中，从而允许快速查找具有相应列值的行。B树数据结构让索引可以快速找到一个特定的值，一组值，或者一个范围内的值，对应于子句 中的运算符，如=, >, ≤, BETWEEN,IN等。WHERE
每个表的最大索引数和最大索引长度由每个存储引擎定义。请参阅 第 14 章，InnoDB 存储引擎和 第 15 章，替代存储引擎。所有存储引擎都支持每个表至少有 16 个索引，总索引长度至少为 256 字节。大多数存储引擎都有更高的限制。
有关列索引的更多信息，请参阅 第 13.1.14 节，“CREATE INDEX 语句”。
使用 字符串列的索引规范中的语法，您可以创建仅使用 列的第一个字符的索引。以这种方式仅索引列值的前缀可以使索引文件更小。当你索引一个 或 列时，你 必须为索引指定一个前缀长度。例如： col_name(N)NBLOBTEXT
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
前缀最长可达 1000 字节（ InnoDB表为 767 字节，除非您已 innodb_large_prefix设置）。
笔记
前缀限制以字节为单位，而 , 和 语句中的前缀长度CREATE TABLE被 ALTER TABLE解释 CREATE INDEX为非二进制字符串类型 ( , , ) 的字符数和二进制字符串类型 ( , , CHAR) VARCHAR的 TEXT字节数。在为使用多字节字符集的非二进制字符串列指定前缀长度时，请考虑到这一点。 BINARYVARBINARYBLOB
如果搜索词超过索引前缀长度，则使用索引排除不匹配的行，并检查剩余的行是否可能匹配。
有关索引前缀的更多信息，请参阅 第 13.1.14 节，“CREATE INDEX 语句”。
FULLTEXT索引用于全文搜索。只有InnoDB和 MyISAM存储引擎支持 FULLTEXT索引，并且仅 支持CHAR、 VARCHAR和 TEXT列。索引始终在整个列上进行，并且不支持列前缀索引。有关详细信息，请参阅 第 12.10 节，“全文搜索功能”。
优化适用于 FULLTEXT针对单个 InnoDB表的某些类型的查询。具有这些特征的查询特别有效：
FULLTEXT只返回文档 ID 或文档 ID 和搜索排名的查询。FULLTEXT按分数降序对匹配行进行排序并应用LIMIT子句以获取前 N 个匹配行的查询。要应用此优化，必须没有WHERE子句，并且只有一个ORDER BY按降序排列的子句。FULLTEXT仅检索COUNT(*)与搜索词匹配的行的值的查询，没有附加WHERE子句。WHERE将子句 编码为，不带任何比较运算符。WHERE MATCH(text) AGAINST ('other_text')> 0
对于包含全文表达式的查询，MySQL 在查询执行的优化阶段评估这些表达式。优化器不只是查看全文表达式并进行估计，它实际上是在制定执行计划的过程中评估它们。
这种行为的一个含义是， EXPLAIN全文查询通常比在优化阶段不进行表达式评估的非全文查询慢。
EXPLAIN由于在优化过程中发生匹配，全文查询可能会显示Select tables optimized away在列中；Extra在这种情况下，在以后的执行过程中不需要访问表。
您可以为空间数据类型创建索引。 MyISAM并InnoDB 支持空间类型的 R-tree 索引。其他存储引擎使用 B 树来索引空间类型（ ARCHIVE不支持空间类型索引的 除外）。
MEMORY存储引擎默认使用 索引HASH，但也支持 BTREE索引。
8.3.5 多列索引
MySQL 可以创建复合索引（即多列上的索引）。一个索引最多可以包含 16 列。对于某些数据类型，您可以索引列的前缀（请参阅 第 8.3.4 节，“列索引”）。
MySQL 可以将多列索引用于测试索引中所有列的查询，或者只测试第一列、前两列、前三列等的查询。如果在索引定义中以正确的顺序指定列，则单个复合索引可以加快对同一张表的多种查询。
多列索引可以被认为是一个排序数组，其中的行包含通过连接索引列的值创建的值。
笔记
作为复合索引的替代方案，您可以引入基于来自其他列的信息“散列”的列。如果此列很短、相当唯一且已编入索引，则它可能比在许多列上的“宽”索引要快。在 MySQL 中，很容易使用这个额外的列：
SELECT * FROM tbl_name
WHERE hash_col=MD5(CONCAT(val1,val2))
AND col1=val1 AND col2=val2;
假设一个表具有以下规范：
CREATE TABLE test (
id INT NOT NULL,
last_name CHAR(30) NOT NULL,
first_name CHAR(30) NOT NULL,
PRIMARY KEY (id),
INDEX name (last_name,first_name)
);
name索引是对 last_name和first_name 列 的索引。该索引可用于查询中的查找，这些查询指定已知范围内的值组合 last_name和first_name 值。它也可以用于只指定一个 last_name值的查询，因为该列是索引的最左边的前缀（如本节后面所述）。因此，name索引用于以下查询中的查找：
SELECT * FROM test WHERE last_name='Jones';
SELECT * FROM test
WHERE last_name='Jones' AND first_name='John';
SELECT * FROM test
WHERE last_name='Jones'
AND (first_name='John' OR first_name='Jon');
SELECT * FROM test
WHERE last_name='Jones'
AND first_name >='M' AND first_name < 'N';
但是，该name索引 不用于以下查询中的查找：
SELECT * FROM test WHERE first_name='John';
SELECT * FROM test
WHERE last_name='Jones' OR first_name='John';
假设您发出以下 SELECT语句：
SELECT * FROM tbl_name
WHERE col1=val1 AND col2=val2;
col1如果和 上存在多列索引col2，则可以直接获取相应的行。col1如果和上存在单独的单列索引 col2，优化器会尝试使用索引合并优化（请参阅 第 8.2.1.3 节，“索引合并优化”），或者尝试通过确定哪个索引排除更多行并使用该索引以获取行。
如果表有一个多列索引，优化器可以使用索引的任何最左边的前缀来查找行。例如，如果您在 上具有三列索引，则您在、和 (col1, col2, col3)上具有索引搜索功能 。 (col1)(col1, col2)(col1, col2, col3)
如果列不构成索引的最左前缀，则 MySQL 无法使用索引执行查找。假设您有SELECT此处显示的语句：
SELECT * FROM tbl_name WHERE col1=val1;
SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2;
SELECT * FROM tbl_name WHERE col2=val2;
SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;
如果 上存在索引(col1, col2, col3)，则只有前两个查询使用该索引。第三和第四个查询确实涉及索引列，但不使用索引来执行查找，因为(col2)and (col2, col3)不是 (col1, col2, col3).
8.3.6 验证索引使用情况
始终检查您的所有查询是否真的使用您在表中创建的索引。使用 第 8.8.1 节“使用 EXPLAIN 优化查询”EXPLAIN中所述的语句。
8.3.7 InnoDB 和 MyISAM 索引统计收集
存储引擎收集有关表的统计信息以供优化器使用。表统计基于值组，其中值组是一组具有相同键前缀值的行。出于优化器的目的，一个重要的统计数据是平均值组大小。
MySQL 通过以下方式使用平均值组大小：
- ref估计每次访问 必须读取多少行
- 估计部分连接产生多少行；也就是说，这种形式的操作产生的行数：
(...) JOIN tbl_name ON tbl_name.key = expr
随着索引的平均值组大小的增加，该索引对这两个目的的用处不大，因为每次查找的平均行数会增加：为了使索引有利于优化目的，最好每个索引值都针对一个小的表中的行数。当给定的索引值产生大量行时，索引的用处不大，MySQL 不太可能使用它。
平均值组大小与表基数有关，表基数是值组的数量。该 SHOW INDEX语句显示基于 的基数值N/S，其中 N是表中的行数，S是平均值组大小。该比率在表中产生近似数量的值组。
对于基于<=>比较运算符的连接，NULL与任何其他值没有区别对待：NULL <=> NULL，就像任何其他 。 N <=> NN
但是，对于基于=运算符的连接， NULL与非NULL值不同： 当或 （或两者） 为时不为真 。这会影响 表单比较的访问：如果当前值为is ，则 MySQL 不会访问该表 ，因为比较不可能为真。 expr1 = expr2expr1expr2NULLreftbl_name.key = exprexprNULL
对于=比较，表中有多少NULL值并不重要。出于优化目的，相关值是非NULL价值组的平均大小。但是，MySQL 当前不支持收集或使用该平均大小。
对于InnoDB和表，您可以分别通过和 系统变量MyISAM 来控制表统计信息的收集 。这些变量具有三个可能的值，它们的区别如下： innodb_stats_methodmyisam_stats_method
- 当变量设置为 时
nulls_equal，所有NULL值都被视为相同（即，它们都形成一个值组）。
如果NULL价值组大小远高于平均非NULL价值组大小，则此方法会向上倾斜平均价值组大小。这使得索引对优化器来说似乎不如查找非NULL值的连接实际有用。因此，该 nulls_equal方法可能会导致优化器不使用索引进行 ref访问。
- 当变量设置为 时
nulls_unequal，NULL值不被视为相同。相反，每个NULL值形成一个大小为 1 的单独值组。
如果您有许多NULL值，则此方法会向下倾斜平均值组大小。如果平均非NULL值组大小很大，则将每个值计数NULL为大小为 1 的组会导致优化器高估查找非值的连接的索引NULL 值。因此，当其他方法可能更好时 ，该nulls_unequal 方法可能会导致优化器使用此索引进行 查找。ref
- 当变量设置为 时
nulls_ignored，NULL值将被忽略。
如果您倾向于使用许多使用 <=>而不是的连接=， NULL则值在比较中并不特殊，并且一个NULL等于另一个。在这种情况下，nulls_equal是适当的统计方法。
系统innodb_stats_method变量具有全局值；系统 myisam_stats_method变量具有全局值和会话值。设置全局值会影响来自相应存储引擎的表的统计信息收集。设置会话值仅影响当前客户端连接的统计信息收集。这意味着您可以通过设置会话值来强制使用给定方法重新生成表的统计信息，而不会影响其他客户端 myisam_stats_method。
要重新生成MyISAM表统计信息，您可以使用以下任何一种方法：
- 执行myisamchk --stats_method= method_name --analyze
- 更改表使其统计数据过时（例如插入一行然后删除），然后设置 myisam_stats_method并发出ANALYZE TABLE 语句
关于使用 innodb_stats_methodand 的一些注意事项myisam_stats_method：
- 如前所述，您可以强制明确收集表统计信息。但是，MySQL 也可以自动收集统计信息。例如，如果在对表执行语句的过程中，其中一些语句修改了表，MySQL 可能会收集统计信息。（例如，批量插入或删除或某些 ALTER TABLE语句可能会发生这种情况。）如果发生这种情况，将使用任何值 innodb_stats_method或 myisam_stats_method当时有。因此，如果您使用一种方法收集统计信息，但稍后自动收集表的统计信息时将系统变量设置为另一种方法，则使用另一种方法。
- 无法判断使用哪种方法为给定表生成统计信息。
- 这些变量仅适用于
InnoDB和MyISAM表。其他存储引擎只有一种收集表统计信息的方法。通常它更接近nulls_equal方法。
8.3.8 B-Tree和Hash索引的比较
了解 B-tree 和 hash 数据结构可以帮助预测在索引中使用这些数据结构的不同存储引擎上的不同查询如何执行，特别是对于MEMORY允许您选择 B-tree 或 hash 索引的存储引擎。
B 树索引可用于使用 =、 >、 >=、 <、 <=或BETWEEN运算符的表达式中的列比较。LIKE 如果参数 to LIKE是不以通配符开头的常量字符串，则索引也可用于比较。例如，以下SELECT语句使用索引：
SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';
在第一个语句中，仅考虑具有的行。在第二个语句中，仅考虑具有的行。 'Patrick' <= key_col < 'Patricl''Pat' <= key_col < 'Pau'
以下SELECT语句不使用索引：
SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;
在第一个语句中，LIKE 值以通配符开头。在第二个语句中，该LIKE值不是一个常数。
如果使用and 长度超过三个字符，MySQL 使用Turbo Boyer-Moore 算法来初始化字符串的模式，然后使用该模式更快地执行搜索。 ... LIKE '%string%'string
col_name IS NULL如果被索引，则使用索引 的搜索col_name。
不跨越子句中所有 AND级别的 任何索引都WHERE不会用于优化查询。换句话说，为了能够使用索引，必须在每个 AND组中使用索引的前缀。
以下WHERE子句使用索引：
... WHERE index_part1=1 AND index_part2=2 AND other_column=3
/* index = 1 OR index = 2 */... WHERE index=1 OR A=10 AND index=2
/* optimized like "index_part1='hello'" */... WHERE index_part1='hello' AND index_part3=5
/* Can use index on index1 but not on index2 or index3 */... WHERE index1=1 AND index2=2 OR index1=3 AND index3=3;
这些WHERE子句 不使用索引：
/* index_part1 is not used */... WHERE index_part2=1 AND index_part3=2
/* Index is not used in both parts of the WHERE clause */... WHERE index=1 OR A=10
/* No index spans all rows */... WHERE index_part1=1 OR index_part2=10
有时 MySQL 不使用索引，即使索引可用。发生这种情况的一种情况是优化器估计使用索引将需要 MySQL 访问表中很大比例的行。（在这种情况下，表扫描可能会快得多，因为它需要更少的查找。）但是，如果这样的查询LIMIT仅用于检索某些行，那么 MySQL 无论如何都会使用索引，因为它可以更快地找到在结果中返回几行。
- 它们仅用于使用
=or<=>运算符（但速度非常快）的相等比较。它们不用于比较运算符，例如<查找值范围。依赖这种类型的单值查找的系统被称为“键值存储”；要将 MySQL 用于此类应用程序，请尽可能使用哈希索引。 - 优化器不能使用哈希索引来加速
ORDER BY操作。（这种类型的索引不能用于按顺序搜索下一个条目。） - MySQL 无法确定两个值之间大约有多少行（范围优化器使用它来决定使用哪个索引）。如果将
MyISAMorInnoDB表更改为散列索引MEMORY表，这可能会影响某些查询。 - 只能使用整个键来搜索行。（使用 B 树索引，键的任何最左边的前缀都可用于查找行。）
8.3.9 索引扩展的使用
InnoDB通过将主键列附加到它来自动扩展每个二级索引。考虑这个表定义：
CREATE TABLE t1 (
i1 INT NOT NULL DEFAULT 0, i2 INT NOT NULL DEFAULT 0, d DATE DEFAULT NULL, PRIMARY KEY (i1, i2), INDEX k_d (d)) ENGINE = InnoDB;
此表定义列上的主键(i1, i2)。它还 k_d在列上定义了一个二级索引(d)，但在内部InnoDB扩展了该索引并将其视为列(d, i1, i2)。
优化器在确定如何以及是否使用该索引时会考虑扩展二级索引的主键列。这可以产生更有效的查询执行计划和更好的性能。
优化器可以将扩展二级索引用于 ref、range和 index_merge索引访问，用于松散索引扫描访问，用于连接和排序优化，以及用于 MIN()/MAX() 优化。
以下示例显示优化器是否使用扩展二级索引如何影响执行计划。假设t1填充了这些行：
INSERT INTO t1 VALUES
(1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
(1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
(1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
(2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
(2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
(3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
(3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
(3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
(4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
(4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
(5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
(5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
(5, 5, '2002-01-01');
现在考虑这个查询：
EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'
执行计划取决于是否使用扩展索引。
当优化器不考虑索引扩展时，它会将索引k_d视为 only (d)。 EXPLAIN查询会产生以下结果：
mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: PRIMARY,k_d
key: k_d
key_len: 4
ref: const
rows: 5
Extra: Using where; Using index
当优化器考虑索引扩展时，它k_d会将(d, i1, i2). 在这种情况下，它可以使用最左边的索引前缀(d, i1)来生成更好的执行计划：
mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: ref
possible_keys: PRIMARY,k_d
key: k_d
key_len: 8
ref: const,const
rows: 1
Extra: Using index
在这两种情况下，都key表示优化器使用二级索引k_d，但 EXPLAIN输出显示使用扩展索引的这些改进：
key_len从 4 个字节变为 8 个字节，表示键查找使用列d和i1，而不仅仅是d.- 该
ref值从const变为 ，const,const因为键查找使用两个键部分，而不是一个。 - 计数从5
rows减少到 1，表明InnoDB应该需要检查更少的行来产生结果。 Extra值 从Using where; Using index变为Using index。这意味着可以仅使用索引读取行，而无需查阅数据行中的列。
使用扩展索引的优化器行为的差异也可以通过以下方式看到SHOW STATUS：
FLUSH TABLE t1;
FLUSH STATUS;
SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
SHOW STATUS LIKE 'handler_read%'
前面的语句包括 刷新表缓存和清除状态计数器 FLUSH TABLES。FLUSH STATUS
如果没有索引扩展，SHOW STATUS将产生以下结果：
+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Handler_read_first | 0 || Handler_read_key | 1 || Handler_read_last | 0 || Handler_read_next | 5 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 0 |+-----------------------+-------+使用索引扩展，SHOW STATUS会产生这个结果。该 Handler_read_next值从 5 减少到 1，表示更有效地使用索引：
+-----------------------+-------+| Variable_name | Value |+-----------------------+-------+| Handler_read_first | 0 || Handler_read_key | 1 || Handler_read_last | 0 || Handler_read_next | 1 || Handler_read_prev | 0 || Handler_read_rnd | 0 || Handler_read_rnd_next | 0 |+-----------------------+-------+系统变量的use_index_extensions标志允许控制优化器在确定如何使用表的二级索引optimizer_switch时是否考虑主键列 。InnoDB默认情况下，use_index_extensions 已启用。要检查禁用索引扩展是否可以提高性能，请使用以下语句：
SET optimizer_switch = 'use_index_extensions=off';
优化器对索引扩展的使用受到索引中键部分的数量（16）和最大键长度（3072 字节）的通常限制。
8.3.10 优化器使用生成的列索引
MySQL 支持生成列的索引。例如：
CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));
生成的列 ,gc定义为表达式f1 + 1。该列也被索引，优化器可以在执行计划构建期间考虑该索引。在以下查询中， WHERE子句引用gc 并且优化器考虑该列上的索引是否产生更有效的计划：
SELECT * FROM t1 WHERE gc > 9;
优化器可以使用生成列上的索引来生成执行计划，即使在查询中没有按名称直接引用这些列的情况下也是如此。如果 WHERE, ORDER BY, or GROUP BY子句引用了与某些索引生成列的定义匹配的表达式，则会发生这种情况。以下查询不直接引用gc 但确实使用了与 的定义匹配的表达式 gc：
SELECT * FROM t1 WHERE f1 + 1 > 9;
优化器识别出表达式与索引f1 + 1的定义匹配，因此它在执行计划构建期间考虑该索引。你可以看到这个使用 ： gcgcEXPLAIN
mysql> EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: range
possible_keys: gc
key: gc
key_len: 5
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
实际上，优化器已将表达式替换为与表达式f1 + 1匹配的生成列的名称。EXPLAIN 这在以下显示的扩展信息中可用的重写查询中也很明显SHOW WARNINGS：
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc`
AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` > 9)
以下限制和条件适用于优化器使用生成的列索引：
- 对于要匹配生成的列定义的查询表达式，表达式必须相同并且必须具有相同的结果类型。例如，如果生成的列表达式是，如果查询使用，或者如果 （整数表达式）与字符串进行比较
f1 + 1，优化器将无法识别匹配 。1 + f1f1 + 1 - 优化适用于以下运算符： =、 <、 <=、 >、 >=、 BETWEEN、 和 IN()。
BETWEEN对于and 以外的运算符 IN()，任一操作数都可以替换为匹配的生成列。对于 BETWEENand IN()，只有第一个参数可以替换为匹配的生成列，其他参数必须具有相同的结果类型。 BETWEEN并且 IN()尚不支持涉及 JSON 值的比较。
- 生成的列必须定义为至少包含一个函数调用或上一项中提到的运算符之一的表达式。表达式不能包含对另一列的简单引用。例如，
gc INT AS (f1) STORED仅包含列引用，因此gc不考虑索引。 - 为了将字符串与索引生成的列进行比较，这些列从返回带引号的字符串的 JSON 函数计算值，JSON_UNQUOTE()需要在列定义中从函数值中删除额外的引号。（对于字符串与函数结果的直接比较，JSON 比较器处理引号删除，但索引查找不会发生这种情况。）例如，不要像这样编写列定义：
doc_name TEXT AS (JSON_EXTRACT(jdoc, '$.name')) STORED像这样写：
doc_name TEXT AS (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) STORED使用后一个定义，优化器可以检测到这两个比较的匹配：
... WHERE JSON_EXTRACT(jdoc, '$.name') = 'some_string' ...
... WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = 'some_string' ...
如果不在JSON_UNQUOTE()列定义中，优化器仅检测第一个比较的匹配项。
- 如果优化器未能选择所需的索引，则可以使用索引提示来强制优化器做出不同的选择。
8.3.11 TIMESTAMP 列的索引查找
时间值作为 UTC 值存储在 TIMESTAMP列中，插入到列中和从 TIMESTAMP列中检索的值在会话时区和 UTC 之间进行转换。（这与 CONVERT_TZ()函数执行的转换类型相同。如果会话时区是 UTC，则实际上没有时区转换。）
由于夏令时 (DST) 等本地时区更改的约定，UTC 和非 UTC 时区之间的转换在两个方向上不是一对一的。不同的 UTC 值在另一个时区可能不同。以下示例显示了在非 UTC 时区中变得相同的不同 UTC 值：
mysql> CREATE TABLE tstable (ts TIMESTAMP);
mysql> SET time_zone = 'UTC'; -- insert UTC values
mysql> INSERT INTO tstable VALUES
('2018-10-28 00:30:00'), ('2018-10-28 01:30:00');mysql> SELECT ts FROM tstable;
+---------------------+
| ts |
+---------------------+
| 2018-10-28 00:30:00 |
| 2018-10-28 01:30:00 |
+---------------------+
mysql> SET time_zone = 'MET'; -- retrieve non-UTC values
mysql> SELECT ts FROM tstable;
+---------------------+
| ts |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+
笔记
要使用命名时区，例如'MET'或 'Europe/Amsterdam'，时区表必须正确设置。有关说明，请参阅 第 5.1.13 节，“MySQL 服务器时区支持”。
您可以看到两个不同的 UTC 值在转换'MET'为时区时是相同的。这种现象可能导致给定 TIMESTAMP列查询的不同结果，具体取决于优化器是否使用索引来执行查询。
假设查询从前面显示的表中选择值，使用WHERE子句在 ts列中搜索单个特定值，例如用户提供的时间戳文字：
SELECT ts FROM tstable
WHERE ts = 'literal';
进一步假设查询在以下条件下执行：
- 会话时区不是 UTC 并且有 DST 偏移。例如：
SET time_zone = 'MET';
- TIMESTAMP由于 DST 变化， 存储在该列中的唯一 UTC 值在 会话时区中不是唯一的。（前面显示的示例说明了这是如何发生的。）
- 该查询指定在会话时区进入 DST 小时内的搜索值。
在这些条件下， WHERE子句中的比较对于非索引和索引查找以不同的方式发生，并导致不同的结果：
- 如果没有索引或优化器无法使用它，则在会话时区进行比较。优化器执行表扫描，在其中检索每个
ts列值，将其从 UTC 转换为会话时区，并将其与搜索值（也在会话时区中解释）进行比较：
- mysql>
tsFROMtstable ts=- +---------------------+
- | ts |
- +---------------------+
- | 2018-10-28 02:30:00 |
- | 2018-10-28 02:30:00 |
+---------------------+
因为存储的ts值被转换为会话时区，所以查询可能返回两个时间戳值，它们与 UTC 值不同但在会话时区中相等：一个值发生在时钟更改时 DST 偏移之前，以及在 DST 转变之后出现的一个值。
- 如果有可用索引，则以 UTC 进行比较。优化器执行索引扫描，首先将搜索值从会话时区转换为 UTC，然后将结果与 UTC 索引条目进行比较：
- mysql>
tstableADDts); - mysql>
tsFROMtstable ts=- +---------------------+
- | ts |
- +---------------------+
- | 2018-10-28 02:30:00 |
+---------------------+
在这种情况下，（转换后的）搜索值仅与索引条目匹配，并且由于不同存储的 UTC 值的索引条目也是不同的，因此搜索值只能匹配其中一个。
由于非索引和索引查找的优化器操作不同，查询在每种情况下都会产生不同的结果。非索引查找的结果返回会话时区中匹配的所有值。索引查找不能这样做：
- 它在只知道 UTC 值的存储引擎中执行。
- 对于映射到相同 UTC 值的两个不同会话时区值，索引查找仅匹配相应的 UTC 索引条目并仅返回单行。
在前面的讨论中，存储的数据集 tstable恰好由不同的 UTC 值组成。在这种情况下，所有显示形式的索引使用查询最多匹配一个索引条目。
如果索引不是UNIQUE，则表（和索引）可以存储给定 UTC 值的多个实例。例如，该ts列可能包含 UTC 值的多个实例 '2018-10-28 00:30:00'。在这种情况下，使用索引的查询将返回它们中的每一个（转换为'2018-10-28 02:30:00'结果集中的 MET 值）。确实，使用索引的查询将转换后的搜索值与 UTC 索引条目中的单个值匹配，而不是匹配转换为会话时区中搜索值的多个 UTC 值。
如果返回ts会话时区中匹配的所有值很重要，解决方法是禁止使用带有IGNORE INDEX提示的索引：
mysql> SELECT ts FROM tstable
IGNORE INDEX (ts) WHERE ts = '2018-10-28 02:30:00';+---------------------+
| ts |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+
在其他上下文中，同样缺少双向时区转换的一对一映射，例如使用 FROM_UNIXTIME()and UNIX_TIMESTAMP()函数执行的转换。请参见 第 12.7 节，“日期和时间函数”。
提高操作性能的最佳方法 SELECT是在查询中测试的一个或多个列上创建索引。索引条目的作用类似于指向表行的指针，允许查询快速确定哪些行与WHERE子句中的条件匹配，并检索这些行的其他列值。所有 MySQL 数据类型都可以被索引。
尽管为查询中使用的每个可能的列创建索引可能很诱人，但不必要的索引会浪费空间和时间，让 MySQL 确定要使用哪些索引。索引还会增加插入、更新和删除的成本，因为每个索引都必须更新。您必须找到适当的平衡，以使用最佳索引集实现快速查询。
144
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
