MySQL 优化：优化 SQL 语句：优化 SELECT 语句
关于
查询以“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 执行的一些优化如下：
- 删除不必要的括号：
((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)
- 常数折叠：
(a<b AND b=c) AND a=5 -> b>5 AND b=c AND a=5
- 常数条件移除：
(b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=7 AND 5=6) -> b=5 OR b=6
- 索引使用的常量表达式仅计算一次。
- “COUNT(*)”：在没有“WHERE”单表上，是直接从 MyISAM 和 MEMORY 表的表信息中检索的。
- 当其仅与一个表一起使用时，对于任何“NOT NULL”表达式也将执行此操作。
- 早期检测无效常量表达式。MySQL很快检测到一些“SELECT”语句是不可能的，并且不返回任何行。
- 如果不使用“GROUP BY”或集合函数（“COUNT()”、“MIN()”等），“HAVING”将与“WHERE”合并。
- 对于联接中的每个表，构造一个更简单的“WHERE”来快速计算表的“WHERE”，并尽快跳过行。
- 在查询中的所有其他表之前，首先读取所有常量表。常量表可以是以下任意一个：【？？？】
- 空表或具有一行的表。
- 在“主键”或“唯一索引”上使用“WHERE”子句的表，其中所有索引部分都与常量表达式进行比较，并定义为“NOT 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”和“GROUP BY”子句中的所有列都来自同一表，则在联接时优先使用该表。
- 如果有一个“ORDER BY”子句和另一个“GROUP BY”子句，或者“ORDER BY”或“GROUP BY”包含联接队列中第一个表以外的表中的列，则会创建一个临时表。【？？？】
- 如果使用“SQL_SMALL_RESULT”修饰符，则 MySQL 使用内存中的临时表。【？？？】
- 查询每个表索引，并使用最佳索引，除非优化程序认为使用全表扫描更有效。同时，使用扫描是基于最佳索引是否跨越了表的 30％以上，但是固定百分比不再决定使用索引还是扫描。现在，优化器更加复杂，其评估基于其他因素，例如表大小，行数和 I/O 块大小。
- 在输出每一行之前，将跳过与“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 key_part2 FROM tbl_name GROUP BY key_part1;
以下查询使用索引来按排序 Sequences 检索行，而无需单独的排序遍历：
SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... ;
SELECT ... FROM tbl_name
ORDER BY key_part1 DESC, key_part2 DESC, ... ;
范围优化【？？？？？？】
range访问方法使用单个索引来检索包含在一个或几个索引值间隔内的表行的子集。它可以用于单部分或多部份索引。
单部分索引的范围访问方法【？？？】
对于单部分索引，索引值间隔可以方便地由“WHERE”子句中的相应条件表示，表示为范围条件，而不是“间隔”。【？】
单部分索引的范围条件的定义如下：
- 对于 B树 和 HASH 索引，使用=，<=>，IN()，IS NULL或不为空运算符时，将关键部分与常量值进行比较是一个范围条件。
- 此外，对于 B树 索引，使用>，<，>=，<=，BETWEEN，!=或<>运算符时，将关键部分与常量值进行比较是一个范围条件；
- 如果LIKE的参数为常量字符串，则进行 LIKE 比较不能以通配符开头。
- 对于所有索引类型，将多个范围条件与OR或AND组合在一起可形成范围条件。
前面的描述中的“常量值”表示以下之一：
- 查询字符串中的常量；
- 来自同一联接的常量或system表中的列；
- 不相关子查询的结果；
- 任何完全由上述类型的子表达式组成的表达式；
以下是“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 < 'abc' AND (key1 LIKE 'abcde%' OR key1 LIKE '%b')) OR (key1 < 'bar' AND nonkey = 4) OR (key1 < 'uux' AND key1 > 'z')
- 删除“nonkey = 4”【B树索引“=”结果不为范围】和“key1 LIKE '%b'”【“LIKE”以通配符开始结果不为范围】，因为它们不能用于范围扫描。删除它们的正确方法是将它们替换为“TRUE”，这样我们在进行范围扫描时就不会丢失任何匹配的行。用 TRUE 替换它们会产生：
(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR (key1 < 'bar' AND TRUE) OR (key1 < 'uux' AND key1 > 'z')
- 折叠始终为 true 或 false 的条件：
- (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访问方法合并多个范围。要解决此限制，可以将“UNION”与相同的“SELECT”语句一起使用，除了将每个空间谓词放在不同的“SELECT”中。
多部分索引的范围访问方法【？？？】
多部分索引的范围条件是单部分索引的范围条件的扩展。
Multipart 索引的范围条件是单部分索引的范围条件的扩展。Multipart 索引上的范围条件将索引行限制在一个或几个键 Tuples 间隔内。使用从索引开始的 Sequences，在一组键 Tuples 上定义键 Tuples 间隔。
例如，考虑定义为“key1(key_part1, key_part2, key_part3)”的 Multipart 索引，并按键 Sequences 列出以下一组键 Tuples：
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 个 Tuples，并且可以由范围访问方法使用。
相反，条件“key_part3 = 'abc'”并未定义单个间隔，并且不能被范围访问方法使用。
关于范围条件如何作用于 Multipart 索引：【？？？】
- 对于 HASH 索引，可以使用每个包含相同值的间隔。【？？？】
- 这意味着只能针对以下形式的条件生成间隔：
key_part1 cmp const1 AND key_part2 cmp const2 AND ... AND key_partN cmp constN;
- 这里，const1，const2，…是常量，cmp 是 =，<=> 或 IS NULL 比较运算符之一，并且条件涵盖所有索引部分。（也就是说，存在 N 条件， N-part 索引的每个部分都有一个条件）例如，以下是由三部分组成的 HASH索引 的范围条件：
key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'
- 对于 B树 索引，间隔可用于与“AND”组合的条件，其中每个条件使用 =，<=>，IS NULL，>，<，>=，<=，!=，<>，BETWEEN 或 LIKE 'pattern' 将键部分与常数值进行比较（其中'pattern'不能以通配符开头）。只要可以确定包含所有与条件匹配的行的单个键 Tuples，就可以使用一个间隔（如果使用 <> 或 !=，则可以使用两个间隔)。【？？？】
- 只要比较运算符是 =，<=> 或 IS NULL，优化器就会尝试使用其他关键部分来确定间隔。如果运算符是 >，<，>=，<=，!=，<>，BETWEEN 或 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)
- 在此示例中，第一行的间隔使用一个关键部分作为左边界，使用两个关键部分作为右边界。第二行的间隔仅使用一个关键部分。 EXPLAIN 输出中的 key_len 列表示所使用的键前缀的最大长度。
在某些情况下，key_len 可能表明已使用了关键部件，但这可能不是您期望的。【？？？】
- 假设 key_part1 和 key_part2 可以是 NULL。然后 key_len 列显示以下条件的两个关键 Component 长度：
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 等于多个值中的任何一个，则每个表达式为 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”子句。
这些下潜跳过条件仅适用于单表查询。对于多表查询（联接），不会跳过索引下潜。
行构造函数表达式的范围优化【？？？】
优化程序可以将范围扫描访问方法应用于以下形式的查询：
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()”谓词的右侧，行构造函数仅包含运行时常量，这些常量是在执行期间绑定到常量的 Literals 或本地列引用。
- 在“IN()”谓词的右侧，有多个行构造器。
限制内存用于范围优化【？？？】
要控制范围优化器可用的内存，请使用 range_optimizer_max_mem_size 系统变量：
- 值 0 表示“无限制”。
- 值大于 0 时，优化程序将在考虑范围访问方法时跟踪消耗的内存。如果将要超出指定的限制，则将放弃范围访问方法，而改为考虑其他方法，包括全表扫描。这可能不太理想。如果发生这种情况，则会发生以下警告（其中 N 是当前的 range_optimizer_max_mem_size 值）：
Warning 3170 Memory capacity of N bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.
- 对于“UPDATE”和“DELETE”语句，如果优化器退回到全表扫描并且启用了 sql_safe_updates 系统变量，则会发生错误而不是警告，因为实际上，没有键用于确定要修改的行。
- 对于超出可用范围优化内存的单个查询，并且对于该查询，优化器后退到次优计划，增大 range_optimizer_max_mem_size 值可以提高性能。
若要估计处理范围表达式所需的内存量，请使用以下准则：【？】
- 对于如下所示的简单查询，其中存在一个用于范围访问方法的候选键，每个与“OR”谓词的组合大约使用 230 个字节：
SELECT COUNT(*) FROM t WHERE a=1 OR a=2 OR a=3 OR .. . a=N;
- 同样，对于以下查询，每个与“AND”谓词的组合大约使用 125 个字节：
SELECT COUNT(*) FROM t WHERE a=1 AND b=1 AND c=1 ... N;
- 对于带有“IN()”谓词的查询：
SELECT COUNT(*) FROM t WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);
- “IN()”列表中的每个 Literals 值都算作与“OR”组合的谓词。如果有两个“IN()”列表，则谓词与“OR”组合的数量是每个列表中 Literals 值数量的乘积。因此，在前一种情况下与OR组合的谓词数为“M × N”。
- 在 5.7.11 之前，每个谓词与“OR”相结合的字节数更高，大约为 700 个字节。