转载：https://blog.csdn.net/qq_39785418/article/details/116304662

8.2 优化SQL语句
数据库应用程序的核心逻辑是通过SQL语句执行完成的，这些语句无论是通过解释器直接发出，还是通过API在幕后提交。本节中的调优指南有助于加快各种MySQL应用程序的速度。这些准则涵盖了读写数据的SQL操作、一般SQL操作的幕后开销，以及特定场景中使用的操作，比如数据库监控。

8.2.1 优化 SELECT 语句
查询是在数据库中以SELECT语句的形式执行所有查找操作。优化这些语句是最优先考虑的，无论是为动态网页实现亚秒级的响应时间，还是为生成庞大的夜间报告而减少要花费的数小时时间。

除了SELECT语句之外，查询的调优技术也适用于诸如 CREATE TABLE…AS SELECT，INSERT INTO...SELECT和在 DELETE语句中的WHERE子句。这些语句都要考虑额外的性能因素，因为它们将写操作与面向读的查询操作结合在一起。

NDB 集群支持一种连接下推优化技术，通过这种优化，一个合格的连接将作为整体发送到NDB 集群中的数据节点，它可以被分布到这些节点中并行执行。有关此优化的更多信息，请参见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子句、join子句等来调查特定查询的内部细节（当您达到一定的专业水平时，阅读EXPLAIN计划可能是您进行每个查询的第一步。）

调整MySQL用于缓存的内存区域的大小和属性。通过有效地使用InnoDB缓冲池、MyISAM 键缓存和MySQL查询缓存，重复的查询会运行得更快，这是因为结果会在第二次和随后的时间从内存中检索。

即使对于已使用缓存区域快速运行的查询，您仍然可以进一步优化，使它们需要更少的缓存，从而使应用程序更具可伸缩性。可伸缩性意味着您的应用程序可以处理更多的并发用户、更大的请求等，而不会出现性能的大幅下降。

处理好锁的问题，您的查询速度可能受到同时访问这些表的其他会话影响。

8.2.1.1 WHERE 子句优化
本节讨论可用于处理WHERE子句的优化。这些示例使用SELECT语句，但DELETE和UPDATE语句中的WHERE子句也采用相同的优化。

注意：
因为MySQL优化器的工作正在进行中，这里并没有记录MySQL执行的所有优化。

您可能会试图重写查询语句以加快算术运算，这样也就牺牲了语句的可读性。因为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

在MySQL 8.0.14及更高版本中，这发生在准备阶段而不是优化阶段，这有助于简化连接。有关更多信息和示例，请参阅第8.2.1.9节“外部连接优化”。

索引所使用的常量表达式只计算一次。

从MySQL 8.0.16开始，数值类型的列与常量值的比较会被检查、合并或删除无效或超出范围的值：

# CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);
SELECT * FROM t WHERE c ≪ 256;
-≫ SELECT * FROM t WHERE 1;

有关更多信息，请参见8.2.1.14节“常数-合并优化”。

对于没有WHERE子句的单表上的 COUNT(*)个数的查询，会从MyISAM和MEMORY表的表信息中直接获取。当只用在单表中，并且语句中没有任何 NOT NULL表达式的，对于个数的查询也是一样直接获取。

早期就会检测无效常量表达式。MySQL会很快检测到一些SELECT语句是不可能的，并且不返回任何行。

如果不使用GROUP BY子句或聚合函数（COUNT()、MIN()等），HAVING中的条件将与WHERE的条件合并。

对于连接中的每个表，构造一个更简单的WHERE子句来快速计算表的WHERE条件，并尽快跳过不匹配的行。

在查询中，首先读取所有常量表，然后再读取任何其他表。常数表是下列任一项：

空表或只有一行的表。（译者：就是后面提及的system）

与建立在主键或唯一索引的WHERE子句一起使用的表，其中的所有索引都与常量表达式进行比较，并定义为NOT NULL。（译者：就是后面提及的const）

以下所有表都用作常量表：

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块大小。

在某些情况下，MySQL甚至不需要查阅数据文件就可以从索引中读取行（译者：覆盖索引）。如果索引中使用的所有列都是数字，则仅仅使用索引树就可以解析查询。

在输出每一行之前，将跳过那些与HAVING子句不匹配的行。

下面演示一些非常快速的查询示例：

SELECT COUNT(*) FROM tbl_name; # 没有WHERE子句的个数查询

SELECT MIN(key_part1), MAX(key_part1) FROM tbl_name; # 从单表中直接查询某字段的最大值和最小值

SELECT MAX(key_part2) FROM tbl_name
WHERE key_part1 = constant; # 条件是某个字段等于常量，计算字段2的最大值

SELECT ... FROM tbl_name
ORDER BY key_part1,key_part2,... LIMIT 10; # 按照同一个表的几个字段排序，并取前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;

以下查询使用索引来检索排序后的行，而没有经过单独的排序（译者：这是因为使用了索引覆盖，而索引本身是有次序的。如果要排序需要同方向，即要么升序，要么降序。）：

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

SELECT ... FROM tbl_name
ORDER BY key_part1 DESC, key_part2 DESC, ... ;