MySQL十部曲之九：MySQL优化理论

文章目录

前言
概述
查询优化
- 查询执行计划
- - EXPLAIN
  - - 获取表结构信息
    - 获取执行计划信息
  - EXPLAIN 输出格式
  - 如何使用EXPLAIN进行优化
- 范围访问优化
- - 单列索引的范围访问
  - 多列索引的范围访问
- 索引合并优化
- - 索引合并交叉访问算法
  - 索引合并联合访问算法
  - 索引合并排序联合访问算法
- 索引下推优化
- 连接优化
- - 连接查询原理
  - - 嵌套循环连接算法
    - 块嵌套循环连接算法
  - 嵌套连接优化
  - 外连接优化
- 多范围读取优化
- ORDER BY优化
- - 使用索引来满足ORDER BY
- GROUP BY优化
- - 松散索引扫描
  - 紧凑索引扫描
- DISTINCT优化
- LIMIT优化
- 避免全表扫描
- 子查询优化
- - 利用半连接转换优化IN和EXISTS子查询
  - 用物化优化子查询
  - 使用EXISTS策略优化子查询
表结构优化
- 优化数据大小
- 优化数据类型
- MySQL内部临时表的使用
- - 内部临时表存储引擎
  - 内存内部临时表的存储引擎
  - 磁盘上内部临时表的存储引擎
  - 内部临时表存储格式
  - 监视内部临时表的创建
实战经验

前言

在学习优化理论之前，应该先明白：下文提到的所有优化方法是指MySQL优化器在特定条件下对SQL的的优化，我们要做的仅仅是在编写SQL时尽量满足这些特定条件而尽量多的触发MySQL优化器对SQL的优化。此外，MySQL版本的差异和MySQL的不断发展都会导致优化方法和触发条件的变化，本文基于MySQL8以及InnoDB进行讨论。

PS：还不全，近期没时间弄，好久没发了，先发一下，过几天会补全。

概述

数据库性能取决于几个因素，包括表、查询和配置设置等。这些因素会在硬件层面导致 CPU 和 I/O 操作，我们需要尽可能地减少并提高效率。在优化数据库性能时，首先要了解一些高级规则和指南，然后随着您的专业知识的增长，您会更深入地了解内部运作，并开始测量诸如 CPU 周期和 I/O 操作等更具体的内容。

一般用户的目标是从现有的软件和硬件配置中获得最佳的数据库性能。而高级用户则会改进 MySQL 软件本身，或者开发自己的存储引擎和硬件设备，以扩展 MySQL 生态系统。

查询优化

查询是以SELECT语句的形式执行数据库中的所有查找操作。调优这些语句是最重要的。

查询执行计划

根据表、列、索引和WHERE子句中的条件的细节，MySQL优化器会考虑许多技术来高效地执行SQL查询，优化器选择执行最有效查询的操作集称为查询执行计划。

EXPLAIN

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    explainable_stmt

explain_type: {
    FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
  | TREE
}

explainable_stmt: {
    SELECT statement
  | TABLE statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

和EXPLAIN语句是同义词。在实践中，DESCRIBE关键字更常用于获取有关表结构的信息，而EXPLAIN则用于获取查询执行计划信息。

下面的讨论根据这些用途使用了DESCRIBE和EXPLAIN关键字，但是MySQL解析器将它们视为完全同义的。

获取表结构信息

DESCRIBE提供关于表中列的信息：

mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

默认情况下，DESCRIBE显示表中所有列的信息。如果给定col_name，则表示表中某个列的名称。在这种情况下，语句仅显示指定列的信息。wild表示一个模式字符串。它可以包含%和_通配符。如果给定wild，该语句仅显示名称与字符串匹配的列的输出。

获取执行计划信息

EXPLAIN语句提供了MySQL如何执行语句的信息：

EXPLAIN与SELECT、DELETE、INSERT、 REPLACE和UPDATE语句一起工作。在MySQL 8.0.19及更高版本中，它还可以与TABLE语句一起工作。
当EXPLAIN与可解释语句一起使用时，MySQL将显示来自优化器的关于语句执行计划的信息。也就是说，MySQL解释了它将如何处理语句，包括关于表如何连接以及以何种顺序连接的信息
FORMAT选项可用于选择输出格式。TRADITIONAL以表格格式显示输出。如果没有FORMAT选项，这是默认值。JSON格式以JSON格式显示信息。在MySQL 8.0.16及以后的版本中，TREE提供了类似树的输出，比传统格式更精确地描述了查询处理；它是唯一显示散列连接使用情况的格式，并且总是用于EXPLAIN ANALYZE。

在EXPLAIN的帮助下，您可以看到应该在哪里向表添加索引，以便通过使用索引查找行来加快语句的执行速度。还可以使用EXPLAIN检查优化器是否以最优顺序连接表。为了提示优化器使用与SELECT语句中表的命名顺序相对应的连接顺序，可以使用SELECT直连而不是SELECT来开始语句。

EXPLAIN 输出格式

对于SELECT语句，EXPLAIN会为每个使用的表返回一行信息。它在输出中按照MySQL在处理语句时读取它们的顺序列出这些表。这意味着MySQL首先从第一个表中读取一行，然后在第二个表中找到匹配的行，接着在第三个表中查找，依此类推。当所有表都被处理完毕时，MySQL会输出所选的列，并在表列表中回溯，然后从该表中读取下一行，并继续处理下一个表。

列名	JSON属性名	含义
id	select_id	查询标识符
select_type	None	查询类型
table	table_name	输出行的表
partitions	partitions	匹配的分区
type	access_type	连接类型
possible_keys	possible_keys	可能使用的索引
key	key	实际使用的索引
key_len	key_lenght	索引长度
ref	ref	与索引比较的列
rows	rows	估计要检查的行数
filtered	filtered	按表条件筛选的行百分比
Extra	None	附加信息

id：这是查询中SELECT语句的顺序号。如果行引用其他行的联合结果，则该值可以为NULL。在这种情况下，表列显示类似<unionM,N>的值，表示该行引用id值为M和N的行的联合结果。
select_type：查询类型，可以是以下表格中的任何类型之一：

查询类型	说明
SIMPLE	简单查询（不使用UNION或子查询）
PRIMARY	外层查询
UNION	UNION中的第二个或之后的SELECT语句
DEPENDENT UNION	UNION中的第二个或更高的SELECT语句，依赖于外部查询
UNION RESULT	UNION的结果
SUBQUERY	子查询中的第一个SELECT
DEPENDENT SUBQUERY	子查询中的第一个SELECT，依赖于外部查询
DERIVED	派生表
DEPENDENT DERIVED	依赖于另一个表的派生表
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	不能为其缓存结果并且必须为外部查询的每一行重新求值的子查询
UNCACHEABLE UNION	UNION中属于不可缓存子查询的第二次或之后的SELECT语句

table：输出行所指向的表的名称。这也可以是以下值之一：
- <unionM,N>：该行是id值为M和N的行的并集。
- <derivedN>：该行引用id值为N的行的派生表结果，例如，派生表可能来自FROM子句中的子查询。
- <subqueryN>：该行引用id值为N的行的物化子查询的结果。
partitions：该查询匹配记录的分区。对于非分区表，该值为 NULL。
type：连接类型，下面的列表描述了连接类型，从最佳类型到最差类型排序：
- system：该表只有一行，这是const连接类型的一种特殊情况。
- const：表最多只有一行匹配。
- eq_ref：在连接操作中，MySQL 使用了一个索引，该索引的所有部分都被用来与另一个表进行匹配。
- ref：当连接操作中使用的索引仅使用了索引的最左前缀，或者索引不是主键或唯一索引
- ref_or_null：这种连接类型与 ref 类似，但额外搜索了包含 NULL 值的行。
- index_merge：这种连接类型表示使用了索引合并优化。在这种情况下，输出行中的 key 列包含使用的索引列表，而 key_len 包含所使用的索引的最长键部分的列表。
- unique_subquery：
- index_subquery：
- range：只检索给定范围内的行，使用索引选择行。输出行中的键列指示使用哪个索引。key_len包含使用的最长键部分。这种类型的ref列为NULL。
- index：index 连接类型与 ALL 类型相同，但索引树被扫描。这有两种情况：
  - 如果索引可以满足查询所需的所有数据，并且可以用来满足表的所有数据，那么只扫描索引树。在这种情况下，Extra 列会显示 Using index。索引扫描通常比 ALL 更快，因为索引的大小通常比表数据小。
  - 使用索引从索引中按顺序查找数据行执行全表扫描。在 Extra 列中不会显示 Uses index。
- ALL：对于前面表格的每一行组合，都会对表进行全表扫描。

当查询只使用单个索引的列时，MySQL 可以使用此连接类型。

possible_keys ：possible_keys 列指示 MySQL 可以选择用来查找该表中行的索引。
key：MySQL 实际决定使用的索引。
key_len：表示MySQL决定使用的索引的长度。key_len的值使您能够确定MySQL实际使用的多列索引的多少部分。
ref：显示哪些列或常量与索引列中指定的索引进行比较，以便从表中选择行。如果值是func，则使用的值是某个函数的结果。
rows：表示MySQL认为执行查询必须检查的行数。对于InnoDB表，这个数字是一个估计值，可能并不总是准确的。
filtered：表示按表条件筛选的表行的估计百分比。最大值是100，这意味着没有对行进行过滤。从100开始递减的值表示过滤量在增加。Rows显示检查的估计行数，Rows × filtered显示与下表连接的行数。
Extra：关于MySQL如何解析查询的附加信息。

如何使用EXPLAIN进行优化

当您使用 EXPLAIN 命令查看查询执行计划时，可以关注其中的 rows 列。这一列显示了每个步骤中估计要检查的行数。如果您关心查询的性能，可以通过将每个步骤中的行数相乘来估算整个查询将检查的行数。这个结果越小，查询执行得越快。

您可以使用 EXPLAIN 命令来查看这个 SELECT 语句的执行计划，以了解 MySQL 是如何处理这个查询的。通过分析 EXPLAIN 的输出，您可以了解 MySQL 是否有效地利用了索引和优化了查询。接下来，我可以帮您解释如何解读 EXPLAIN 输出，以及如何根据这些信息来调整查询以获得更好的性能。

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;

对于这个例子，做以下假设：

要比较的列声明如下：
表有以下索引：
tt.ActualPC的值并不均匀

最初，在执行任何优化之前，EXPLAIN语句产生以下信息：

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      Range checked for each record (index map: 0x23)

由于每个表的 type 都是 ALL，这个输出表明 MySQL 正在生成所有表的笛卡尔积；也就是说，要检查每个表行的组合。这需要很长的时间，因为必须检查每个表中行数的乘积。在这个情况下，这个乘积是 74 × 2135 × 74 × 3872 = 45,268,558,720 行。如果表更大，你可以想象需要多长时间。

这里的一个问题是，如果列声明为相同类型和大小，MySQL 可以更有效地使用列上的索引。在这个上下文中，如果它们声明为相同的大小，VARCHAR 和 CHAR 被视为相同。tt.ActualPC 声明为 CHAR(10)，而 et.EMPLOYID 是 CHAR(15)，所以存在长度不匹配。

为了解决列长度不匹配的问题，使用 ALTER TABLE 将 ActualPC 的长度从 10 个字符延长到 15 个字符：

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

现在 tt.ActualPC 和 et.EMPLOYID 都是 VARCHAR(15)。再次执行 EXPLAIN 语句产生了以下结果：

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      Range checked for each record (index map: 0x1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      Range checked for each record (index map: 0x1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

这还不完美，但好多了：行值的乘积减少了 74 倍。这个版本执行时间只需要几秒钟。

第二次修改可以消除 tt.AssignedPC = et_1.EMPLOYID 和 tt.ClientID = do.CUSTNMBR 比较的列长度不匹配：

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

修改后，EXPLAIN产生如下所示的输出：

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

此时，查询已经优化得几乎尽善尽美。剩下的问题是，默认情况下，MySQL假设tt.ActualPC列中的值是均匀分布的，而这在tt表中并不是这样。幸运的是，可以很容易地告诉MySQL分析键的分布情况：

mysql> ANALYZE TABLE tt;

在额外的索引信息下，连接完美无缺，EXPLAIN生成如下结果：

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

范围访问优化

范围访问优化利用一个索引来检索表中包含在一个或多个索引值区间内的行。它可用于单列索引或多列索引。

单列索引的范围访问

对于单列索引，索引值区间可以通过 WHERE 子句中相应的条件方便地表示，这些条件被称为范围条件。单列索引构造范围条件的规则如下：

当使用=、<=>、IN()、is NULL或is NOT NULL操作符时，索引列与常量值的比较是一个范围条件。
当使用>、<、>=、<=、BETWEEN、!=或<>操作符时，索引列与常量值的比较是一个范围条件，或者如果LIKE比较的参数是不以通配符字符开头的常量字符串，则是一个范围条件。
多个索引值区间与OR或AND组合形成一个范围条件。

上文提到的常量值是以下几种之一：

查询字符串中的常量
来自同一联接的const表或系统表的一列
不相关子查询的结果
任何完全由上述类型的子表达式组成的表达式

多列索引的范围访问

多列索引的索引值区间是对键的范围条件的扩展，并且必须满足最左前缀原则，对于一个多列索引key1(key_part1, key_part2, key_part3)，条件 key_part1 = 1 定义以下索引值区间：

(1,-inf,-inf) <= (key_part1,key_part2,key_part3) < (1,+inf,+inf)

相比之下，条件 key_part3 = 'abc' 并不定义一个索引值区间，因此不能被范围访问优化使用。

当范围条件与 AND 结合时，如果比较运算符是 =、<=> 或 IS NULL，优化器会尝试使用额外的键部分来确定区间。例如，对于以下表达式，优化器会使用前两个范围条件来构建索引值区间，而不会使用第三个：

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)，这个值不满足原始条件。

索引合并优化

索引合并优化通过多个范围扫描检索行，并将它们的结果合并为一个。这种访问方法仅合并来自单个表的索引扫描，而不是跨多个表的扫描。合并可以生成底层扫描的并集、交集或并集的交集。

可能使用索引合并的示例查询：

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);

在 EXPLAIN 输出中，索引合并方法显示为 type 列中的 index_merge。在这种情况下，key 列包含使用的索引列表，key_len 包含这些索引的最长键部分列表。

索引合并访问方法有几种算法，这些算法显示在 EXPLAIN 输出的 Extra 字段中：

Using intersect(…)
Using union(…)
Using sort_union(…)

以下部分详细描述了这些算法。优化器根据各种可用选项的成本估算，选择不同可能的索引合并算法和其他访问方法之间的选择。

索引合并交叉访问算法

这种访问算法适用于当 WHERE 子句转换为多个在不同键上结合使用 AND 的范围条件时，每个条件是以下之一时：

如果索引恰好具有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）。以下是这样一个查询的示例：

SELECT COUNT(*) FROM t1 WHERE key1 = 1 AND key2 = 1;

如果使用的索引没有覆盖查询中使用的所有列，则仅当所有使用的键的范围条件都满足时才检索完整的行。

如果合并的条件之一是针对InnoDB表的主键的条件，则它不用于检索行，而是用于过滤使用其他条件检索的行。

索引合并联合访问算法

这种算法的条件与索引合并交集算法类似。当表的 WHERE 子句被转换为在不同键上使用 OR 连接的多个范围条件时，并且每个条件是以下之一时，该算法适用：

当索引正好有N个部分时（即所有索引部分都被包含），这种形式的N部分表达式为：

key_part1 = const1 OR key_part2 = const2 ... OR 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 连接的多个范围条件，但不适用于索引合并联合算法时，就可以使用这种访问算法。

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，并在返回任何行之前对它们进行排序。

索引下推优化

Index Condition Pushdown (ICP) 是 MySQL 中的一种优化技术，用于在使用索引检索表行时提高性能。在没有启用 ICP 的情况下，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，然后 MySQL 服务器对这些行的 WHERE 条件进行评估。启用 ICP 后，如果 WHERE 条件的某些部分可以仅通过索引列来评估，MySQL 服务器会将这部分 WHERE 条件下推给存储引擎。存储引擎然后通过使用索引条目来评估推送的索引条件，只有在满足条件时才会从表中读取行。ICP 可以减少存储引擎必须访问基表的次数，以及 MySQL 服务器必须访问存储引擎的次数。

连接优化

连接查询原理

MySQL使用嵌套循环算法或其变体来执行表之间的连接。

嵌套循环连接算法

一个简单的嵌套循环连接（NLJ）算法会逐行从第一个表中读取数据，然后将每一行传递给嵌套循环，处理连接中的下一个表。这个过程会重复执行，直到所有需要连接的表都被处理完毕。

假设要使用以下连接类型执行三个表 t1、t2 和 t3 之间的连接：

Table   Join Type
t1      range
t2      ref
t3      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 8.0.18 之前，当无法使用索引时，该算法用于等值连接；在 MySQL 8.0.18 及更高版本中，哈希连接优化在这种情况下被使用。从 MySQL 8.0.20 开始，MySQL 不再使用块嵌套循环，而是在以前使用块嵌套循环的所有情况下都使用哈希连接。请参阅第 10.2.1.4 节“哈希连接优化”。

MySQL的连接缓冲具有以下特点：

当连接的类型为ALL或index时（即无法使用可能的键，而是进行完整扫描，分别是数据或索引行），或者为range时，可以使用连接缓冲。连接缓冲也适用于外连接，如第 10.2.1.12 节“块嵌套循环和批量键访问连接”中所述。
即使第一个非常量表的类型为ALL或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
    }
  }
}

嵌套连接优化

连接语法允许嵌套连接。在描述连接语法的部分中，table_factor 的语法相对于 SQL 标准有所扩展。SQL 标准只接受 table_reference，而不是将它们放在一对括号中的列表。这种扩展保守地将表引用项列表中的每个逗号视为等同于一个内连接。例如：

外连接优化

多范围读取优化

ORDER BY优化

使用索引来满足ORDER BY

在某些情况下，MySQL 可能会使用索引来满足 ORDER BY 子句，从而避免执行额外的文件排序操作。

即使 ORDER BY 子句与索引不完全匹配，只要索引的所有未使用部分和所有额外的 ORDER BY 列在 WHERE 子句中都是常量，索引也可能会被使用。如果索引不包含查询访问的所有列，那么只有在索引访问比其他访问方法更便宜时才会使用索引。

假设有一个索引 (key_part1, key_part2)，下面的查询可能会使用索引来解析 ORDER BY 部分。优化器是否实际执行此操作取决于如果还必须读取不在索引中的列，那么读取索引是否比表扫描更有效。

在这个查询中，索引 (key_part1, key_part2) 使优化器能够避免排序：

SELECT * FROM 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 的顺序排列的，如果 WHERE 子句具有足够的选择性，使得索引范围扫描比表扫描更便宜，那么索引 (key_part1, key_part2) 可以避免排序：

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

在接下来的两个查询中，索引的使用情况与之前显示的相同的查询（不带 DESC）类似：

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

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2 DESC;

ORDER BY 中的两列可以以相同方向排序（都是 ASC 或都是 DESC），也可以以相反方向排序（一个 ASC，一个 DESC）。索引使用的条件是索引必须具有相同的一致性，但实际方向不需要相同。如果查询混合了 ASC 和 DESC，如果索引也使用相应的混合升序和降序列，优化器可以使用这些列的索引：

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

优化器可以在 key_part1 降序和 key_part2 升序时使用 (key_part1, key_part2) 的索引。它也可以在 key_part1 升序和 key_part2 降序时使用这些列的索引（通过反向扫描）。

在接下来的两个查询中，key_part1 与一个常量进行了比较。如果 WHERE 子句足够具有选择性，使得索引范围扫描比表扫描更便宜，那么索引就会被使用：

SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

SELECT * FROM t1
  WHERE key_part1 < constant
  ORDER BY key_part1 DESC;

在某些情况下，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;

用于获取行的索引与ORDER BY中使用的索引不同：

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

该查询使用ORDER BY和包含索引列名以外的术语的表达式：

SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;

查询连接了许多表，并且 ORDER BY 中的列并非都来自于检索行的第一个非常量表。
查询具有不同的 ORDER BY 和 GROUP BY 表达式。
ORDER BY 子句中只有列的前缀有索引。在这种情况下，索引无法完全解决排序顺序。

使用列别名可能会影响用于排序的索引的可用性。假设列 t1.a 已经建立了索引。在以下语句中，选择列表中的列名是 a。它指的是 t1.a，ORDER BY 中对 a 的引用也是如此，因此可以使用 t1.a 上的索引：

SELECT a FROM t1 ORDER BY a;

在这个语句中，选择列表中的列名也是 a，但它是别名。它指的是 ABS(a)，ORDER BY 中对 a 的引用也是如此，因此无法使用 t1.a 上的索引：

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

在下面的语句中，ORDER BY 引用了一个不是选择列表中列的名称。但是在 t1 中有一个名为 a 的列，因此 ORDER BY 引用了 t1.a，可以使用 t1.a 上的索引。（当然，生成的排序顺序可能与 ABS(a) 的顺序完全不同。）

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

如果无法使用索引满足 ORDER BY 子句，MySQL 将执行一个 filesort 操作，该操作读取表行并对它们进行排序。filesort 在查询执行中构成了一个额外的排序阶段。

GROUP BY优化

对于 GROUP BY 子句，最一般的满足方法是扫描整个表，创建一个新的临时表，其中每个组的所有行都是连续的，然后使用该临时表来确定组并应用聚合函数（如果有）。但在某些情况下，MySQL可以通过使用索引来避免创建临时表。

使用索引来执行 GROUP BY 的最重要的前提是所有 GROUP BY 列引用同一个索引，并且该索引按顺序存储其键值（例如 BTREE 索引）。是否可以通过索引访问来替代临时表也取决于查询中使用的索引的部分以及为这些部分指定的条件和所选的聚合函数。

有两种方法可以通过索引访问来执行 GROUP BY 查询，具体如下：

第一种方法将分组操作与所有范围谓词（如果有）一起应用。
第二种方法首先执行范围扫描，然后对结果元组进行分组。

松散索引扫描

处理 GROUP BY 的最有效方法是使用索引直接检索分组列。使用此访问方法时，MySQL利用了某些索引类型的属性，即键是有序的（例如，BTREE）。这个属性使得可以在索引中查找组，而不必考虑满足所有 WHERE 条件的所有键。这种访问方法只考虑索引中的一小部分键，因此被称为宽松索引扫描。当没有 WHERE 子句时，宽松索引扫描读取的键数与组数相同，这可能远小于所有键的数量。如果 WHERE 子句包含范围谓词（参见第 10.8.1 节，“使用 EXPLAIN 优化查询”中的范围连接类型讨论），宽松索引扫描会查找满足范围条件的每个组的第一个键，并且再次读取尽可能少的键。这种情况下可能会出现：

查询只涉及一个表。
GROUP BY 只命名了索引的最左前缀，并且没有其他列。如果查询中有 DISTINCT 子句，那么所有不同的属性都是指向形成索引最左前缀的列。例如，如果表 t1 上有一个索引（c1，c2，c3），则在查询中有 GROUP BY c1，c2 时适用宽松索引扫描。如果查询有 GROUP BY c2，c3（列不是最左前缀）或 GROUP BY c1，c2，c4（c4 不在索引中）时则不适用。
在 select 列表中使用的任何聚合函数（如果有）只能是 MIN() 和 MAX()，并且它们都引用同一列。该列必须在索引中，并且必须紧跟 GROUP BY 中的列。
查询中引用的除了 GROUP BY 中的索引部分之外的任何索引部分必须是常量（即，它们必须在与常量的等式中引用），除了 MIN() 或 MAX() 函数的参数。
对于索引中的列，必须索引完整的列值，而不仅仅是前缀。例如，对于 c1 VARCHAR(20)，INDEX (c1(10))，索引只使用 c1 值的前缀，无法用于宽松索引扫描。

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

假设在表 t1(c1,c2,c3,c4) 上有一个索引 idx(c1,c2,c3)，则宽松索引扫描访问方法可用于以下查询：

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

以下查询无法使用这种快速选择方法执行，原因如下：

存在除 MIN() 或 MAX() 之外的聚合函数。

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

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

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

查询引用了在 GROUP BY 部分之后的键部分，并且没有与常数的相等性，如果查询包含WHERE c3 = const，则可以使用松散索引扫描：

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

紧凑索引扫描

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

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

为使此方法有效，只需要查询中所有列的一个常量等式条件引用键的部分，在 GROUP BY 键的部分之前或之间。等式条件中的常量填充了搜索键中的任何“间隙”，使得可以形成索引的完整前缀。然后，这些索引前缀可以用于索引查找。如果 GROUP BY 结果需要排序，并且可以形成索引的前缀搜索键，则 MySQL 还避免了额外的排序操作，因为使用有序索引的前缀搜索已经按顺序检索了所有键。

假设在表 t1(c1,c2,c3,c4) 上有索引 idx(c1,c2,c3)，则以下查询不适用于先前描述的 Loose Index Scan 访问方法，但仍适用于 Tight Index Scan 访问方法。

在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;

DISTINCT优化

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

SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const;

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

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

当将 LIMIT row_count 与 DISTINCT 结合使用时，MySQL 会在找到 row_count 个唯一行后立即停止。

如果在查询中没有使用所有表中命名的列，MySQL 会在找到第一个匹配项后立即停止扫描任何未使用的表。在下面的情况中，假设在 t2 之前使用了 t1，当在 t1 的任何特定行中找到 t2 的第一行时，MySQL 会停止从 t2 中读取：

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

LIMIT优化

MySQL 有时会对带有 LIMIT 行数子句而没有 HAVING 子句的查询进行优化：

如果你用 LIMIT 选择了只有几行，MySQL 在某些情况下会使用索引，而不是通常情况下的全表扫描。
如果将 LIMIT row_count 与 ORDER BY 结合起来，MySQL 在找到排序后的前 row_count 行后就停止排序，而不是对整个结果进行排序。如果使用索引进行排序，这是非常快的。如果必须进行文件排序，那么在找到不带 LIMIT 子句的查询匹配的所有行之后，大多数或所有这些行都会被选择并排序，然后才会找到前 row_count 行。在找到初始行后，MySQL 不会对结果集的任何其余部分进行排序。
如果将 LIMIT row_count 与 DISTINCT 结合使用，MySQL 会在找到 row_count 个唯一行后立即停止。
在某些情况下，可以通过按顺序读取索引（或对索引进行排序），然后在索引值更改之前计算摘要来解析 GROUP BY。在这种情况下，LIMIT row_count 不会计算任何不必要的 GROUP BY 值。
LIMIT 0 会快速返回一个空集。这对于检查查询的有效性很有用。它还可用于在使用使结果集元数据可用的 MySQL API 的应用程序中获取结果列的类型。使用 mysql 客户端程序，你可以使用 --column-type-info 选项显示结果列类型。
如果服务器使用临时表来解析查询，则它会使用 LIMIT row_count 子句来计算需要多少空间。
如果未使用索引进行 ORDER BY，但也存在 LIMIT 子句，则优化器可能能够避免使用合并文件，并在内存中使用内存文件排序操作对行进行排序。

如果 ORDER BY 列中有多行具有相同的值，服务器可以以任何顺序返回这些行，并且根据整体执行计划的不同可能会以不同的方式返回。换句话说，这些行的排序顺序与非排序列是不确定的。

影响执行计划的一个因素是 LIMIT，因此带有和不带有 LIMIT 的 ORDER BY 查询可能以不同的顺序返回行。考虑以下查询，它按 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 可能会影响每个类别值内行的顺序。例如，以下是一个有效的查询结果：

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 |
+----+----------+--------+

如果确保在有 LIMIT 和没有 LIMIT 的情况下保持相同的行顺序很重要，请在 ORDER BY 子句中包含其他列，以使顺序确定。例如，如果 id 值是唯一的，可以通过以下方式对行进行排序，使得给定类别值的行按 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 |
+----+----------+--------+

避免全表扫描

当MySQL使用全表扫描来解析查询时，EXPLAIN 输出中的 type 列显示 ALL。这通常在以下情况下发生：

表非常小，执行表扫描比进行关键字查找更快。这在行数少于 10 行且行长度较短的表中很常见。
在 ON 或 WHERE 子句中没有对索引列进行可用的限制。
您正在将索引列与常量值进行比较，MySQL 根据索引树计算出常量覆盖了表的太大部分，并且进行表扫描会更快。
通过另一列使用具有低基数（匹配键值的行数很多）的键。在这种情况下，MySQL 假定通过使用该键可能需要进行许多键查找，并且进行表扫描会更快。

对于小表来说，表扫描通常是适当的，性能影响可以忽略不计。对于大表，请尝试以下技术以避免优化器错误地选择表扫描：

要更新被扫描表的键分布，请使用 ANALYZE TABLE tbl_name。
要告诉 MySQL 表扫描相比使用给定索引非常昂贵，请为被扫描表使用 FORCE INDEX：

SELECT * FROM t1, t2 FORCE INDEX (index_for_column)
  WHERE t1.col_name=t2.col_name;

子查询优化

利用半连接转换优化IN和EXISTS子查询

半连接是一种在准备阶段进行的转换，它可以启用多种执行策略，例如表拉出、重复消除、第一个匹配、宽松扫描和材料化。优化器使用半连接策略来改善子查询的执行，如本节所述。

对于两个表之间的内连接，连接会根据另一个表中的匹配次数返回一张表的行。但是对于某些查询来说，重要的只是是否存在匹配，而不是匹配的数量。假设有名为 class 和 roster 的表，分别列出课程大纲中的课程和课程花名册（每个课程中注册的学生），要列出实际有学生注册的课程，您可以使用以下连接：

SELECT class.class_num, class.class_name
    FROM class
    INNER JOIN roster
    WHERE class.class_num = roster.class_num;

然而，结果对于每个注册的学生都会将每个课程列出一次。对于所提出的问题，这是信息的不必要重复。

假设 class_num 是 class 表中的主键，通过使用 SELECT DISTINCT 可以进行重复消除，但是首先生成所有匹配行，然后再消除重复是低效的。

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

SELECT class_num, class_name
    FROM class
    WHERE class_num IN
        (SELECT class_num FROM roster);

在这里，优化器可以识别到 IN 子句要求子查询从 roster 表中只返回每个课程编号的一个实例。在这种情况下，查询可以使用半连接；也就是说，只返回 roster 表中与 class 表中的行匹配的每行 class 中的一个实例。

下面的语句包含一个 EXISTS 子查询谓词，它等价于前面包含一个 IN 子查询谓词的语句：

SELECT class_num, class_name
    FROM class
    WHERE EXISTS
        (SELECT * FROM roster WHERE class.class_num = roster.class_num);

从 MySQL 8.0.16 开始，任何带有 EXISTS 子查询谓词的语句都会被转换为与带有等效 IN 子查询谓词的语句相同的半连接转换。

从 MySQL 8.0.17 开始，以下子查询会被转换为反连接：

NOT IN (SELECT … FROM …)
NOT EXISTS (SELECT … FROM …)
IN (SELECT … FROM …) IS NOT TRUE
EXISTS (SELECT … FROM …) IS NOT TRUE
IN (SELECT … FROM …) IS FALSE
EXISTS (SELECT … FROM …) IS FALSE

简而言之，对形式为 IN (SELECT … FROM …) 或 EXISTS (SELECT … FROM …) 的子查询的任何否定都会被转换为反连接。

反连接是一种仅返回没有匹配的行的操作。考虑下面的查询：

SELECT class_num, class_name
    FROM class
    WHERE class_num NOT IN
        (SELECT class_num FROM roster);

这个查询在内部被重写为反连接的 SELECT class_num, class_name FROM class ANTIJOIN roster ON class_num，它返回 class 中每一行的一个实例，这些行在 roster 中没有任何匹配行。这意味着对于 class 中的每一行，一旦在 roster 中找到匹配项，就可以丢弃 class 中的行。

在大多数情况下，如果比较的表达式是可空的，则无法应用反连接转换。一个例外是 (… NOT IN (SELECT …)) IS NOT FALSE 及其等效形式 (… IN (SELECT …)) IS NOT TRUE 可以被转换为反连接。

在外部查询规范中允许使用外连接和内连接语法，并且表引用可以是基本表、派生表、视图引用或公共表达式。

在MySQL中，子查询必须满足以下条件才能被处理为半连接（或在MySQL 8.0.17及更高版本中，如果 NOT 修改子查询，则为反连接）：

它必须作为出现在 WHERE 或 ON 子句顶层的 IN、= ANY 或 EXISTS 谓词的一部分，可能作为 AND 表达式中的一个项。例如：

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

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

在 MySQL 8.0.17 及更高版本中，子查询也可以是由 NOT、IS [NOT] TRUE 或 IS [NOT] FALSE 修改的表达式的参数。

它必须是一个单独的 SELECT，不包含 UNION 结构。
它不能包含 HAVING 子句。
它不能包含任何聚合函数（无论是显式还是隐式地分组）。
它不能有 LIMIT 子句。
语句在外部查询中不能使用 STRAIGHT_JOIN 连接类型。
STRAIGHT_JOIN 修饰符不能出现。
外部和内部表的数量总和必须少于联接中允许的最大表数。
子查询可以是相关的或无关的。在 MySQL 8.0.16 及更高版本中，装饰化（decorrelation）查看作为 EXISTS 参数的子查询中 WHERE 子句中的平凡相关谓词，并使其能够像在 IN (SELECT b FROM …) 中使用一样进行优化。平凡相关指的是谓词是一个相等谓词，在 WHERE 子句中是唯一的谓词（或与 AND 结合），其中一个操作数来自子查询中引用的表，另一个操作数来自外部查询块。
允许使用 DISTINCT 关键字，但会被忽略。半连接策略会自动处理重复项的去除。
允许使用 GROUP BY 子句，但会被忽略，除非子查询还包含一个或多个聚合函数。
允许使用 ORDER BY 子句，但会被忽略，因为排序与半连接策略的评估无关。

如果一个子查询符合上述条件，MySQL 将根据成本从以下策略中进行选择：

将子查询转换为连接，或者使用表拉出（table pullout）并在子查询表和外部表之间运行内连接。表拉出将一个表从子查询中拉出到外部查询中。
重复消除（Duplicate Weedout）：将半连接视为连接运行，并使用临时表删除重复记录。
首次匹配（FirstMatch）：在扫描内部表以获取行组合时，如果某个值组有多个实例，则选择一个而不是返回它们所有。这个“快捷方式”扫描并消除了不必要的行的生成。
宽松扫描（LooseScan）：使用索引扫描子查询表，该索引可以从每个子查询的值组中选择一个值。
将子查询材料化为带索引的临时表，用于执行连接，其中索引用于删除重复项。在将临时表与外部表连接时，索引也可能稍后用于查找；如果不使用，则会扫描该表。有关材料化的更多信息，请参阅“使用材料化优化子查询”（Section 10.2.2.2）。

每种策略都可以使用以下 optimizer_switch 系统变量标志进行启用或禁用：

semijoin 标志控制是否使用半连接。从 MySQL 8.0.17 开始，这也适用于反连接。
如果启用了 semijoin，则 firstmatch、loosescan、duplicateweedout 和 materialization 标志可以更精细地控制允许的半连接策略。
如果禁用了 duplicateweedout 半连接策略，则除非所有其他适用的策略也被禁用，否则它不会被使用。
如果禁用了 duplicateweedout，则偶尔优化器可能生成远非最佳的查询计划。这是由于贪婪搜索中的启发式剪枝造成的，可以通过设置 optimizer_prune_level=0 来避免这种情况。

这些标志默认情况下是启用的。请参阅“可切换的优化”（Section 10.9.2）。

优化器尽量减少视图和派生表处理的差异。这会影响使用 STRAIGHT_JOIN 修饰符和具有 IN 子查询的视图的查询，后者可以转换为半连接。以下查询说明了这一点，因为处理方式的变化导致了转换的变化，从而产生了不同的执行策略：

CREATE VIEW v AS
SELECT *
FROM t1
WHERE a IN (SELECT b
           FROM t2);

SELECT STRAIGHT_JOIN *
FROM t3 JOIN v ON t3.x = v.a;

优化器首先查看视图，并将 IN 子查询转换为半连接，然后检查是否可能将视图合并到外部查询中。由于外部查询中的 STRAIGHT_JOIN 修饰符阻止了半连接，优化器拒绝了合并，导致使用一个材料化表来评估派生表。

EXPLAIN 输出指示了使用半连接策略的情况如下：

在扩展的 EXPLAIN 输出中，后续 SHOW WARNINGS 显示的文本显示了重写后的查询，其中显示了半连接结构。从中可以了解到哪些表是从半连接中拉出来的。如果子查询被转换为半连接，则应该看到子查询谓词消失了，并且其表和 WHERE 子句被合并到外部查询的连接列表和 WHERE 子句中。

在 Extra 列中，通过 Start temporary 和 End temporary 表示使用临时表进行重复消除。未被拉出并且在 Start temporary 和 End temporary 覆盖的 EXPLAIN 输出行范围内的表，在临时表中具有其行 ID。

Extra 列中的 FirstMatch(tbl_name) 表示连接的“首次匹配”。

Extra 列中的 LooseScan(m…n) 表示使用宽松扫描策略。m 和 n 是关键部分号。

使用材料化的临时表由具有 MATERIALIZED 选择类型值的行和具有表值的行指示。

在 MySQL 8.0.21 及更高版本中，半连接转换也可以应用于使用 [NOT] IN 或 [NOT] EXISTS 子查询谓词的单表 UPDATE 或 DELETE 语句，前提是该语句不使用 ORDER BY 或 LIMIT，并且优化器提示或 optimizer_switch 设置允许半连接转换。

用物化优化子查询

优化器使用材料化来实现更高效的子查询处理。材料化通过将子查询结果生成为临时表（通常在内存中），加速了查询执行。当 MySQL 首次需要子查询结果时，它将结果材料化为临时表。在后续需要结果时，MySQL 再次引用该临时表。优化器可能会使用哈希索引对表进行索引，以使查找快速且廉价。索引包含唯一值，以消除重复项并使表变得更小。

子查询材料化在可能时使用内存临时表，如果表变得过大，则退回到磁盘存储。请参阅“MySQL 中的内部临时表使用”（Section 10.4.4）。

如果不使用材料化，优化器有时会将非相关子查询重写为相关子查询。例如，以下 IN 子查询是非相关的（where_condition 仅涉及 t2 的列而不涉及 t1）：

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

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

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

使用临时表进行子查询材料化可以避免这种重写，并且可以使得子查询只执行一次，而不是对外部查询的每一行都执行一次。

要在 MySQL 中使用子查询材料化，必须启用 optimizer_switch 系统变量中的 materialization 标志。（参见“可切换的优化”（Section 10.9.2）。）启用 materialization 标志后，材料化适用于出现在任何地方（select 列表、WHERE、ON、GROUP BY、HAVING 或 ORDER BY）的子查询谓词，对于任何符合以下用例之一的谓词：

当没有外部表达式 oe_i 或内部表达式 ie_i 是可空的时，谓词具有如下形式，其中 N 是 1 或更大的数。

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

当存在单个外部表达式 oe 和内部表达式 ie 时，谓词具有如下形式。这些表达式可以是可空的。

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

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

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

以下示例说明了UNKNOWN和FALSE谓词评估等效性要求如何影响是否可以使用子查询材料化。假设 where_condition 仅涉及 t2 的列而不涉及 t1，因此子查询是非相关的。

这个查询适用于材料化：

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

使用子查询材料化存在以下限制：

内部和外部表达式的类型必须匹配。例如，如果两个表达式都是整数或都是十进制，则优化器可能可以使用材料化，但如果一个表达式是整数而另一个是十进制，则不能使用。
内部表达式不能是 BLOB 类型。

使用 EXPLAIN 命令执行查询时，可以提供一些指示，表明优化器是否使用了子查询材料化：

与不使用材料化的查询执行相比，select_type 可能从 DEPENDENT SUBQUERY 更改为 SUBQUERY。这表明，对于每一行外部行执行一次的子查询，材料化使得子查询只需执行一次。
在扩展的 EXPLAIN 输出中，后续 SHOW WARNINGS 显示的文本包括 materialize 和 materialized-subquery。

在 MySQL 8.0.21 及更高版本中，MySQL 还可以将子查询材料化应用于使用 [NOT] IN 或 [NOT] EXISTS 子查询谓词的单表 UPDATE 或 DELETE 语句，前提是该语句不使用 ORDER BY 或 LIMIT，并且子查询材料化是由优化器提示或 optimizer_switch 设置允许的。

使用EXISTS策略优化子查询

表结构优化

优化数据大小

设计你的表以尽量减少它们在磁盘上的占用空间。这样做可以通过减少写入和从磁盘读取的数据量，从而带来巨大的改进。较小的表通常在查询执行过程中需要较少的主存储器。表数据的任何空间减少也会导致索引更小，从而可以更快地处理。

MySQL支持许多不同的存储引擎（表格类型）和行格式。对于每个表格，你可以决定使用哪种存储和索引方法。选择适当的表格式对你的应用程序可以带来很大的性能提升。

表列：
- 尽可能使用最高效（最小）的数据类型。MySQL拥有许多专门的类型，可以节省磁盘空间和内存。
- 如果可能的话，将列声明为NOT NULL。这样做可以使SQL操作更快，通过更好地利用索引和消除对每个值是否为NULL进行测试的开销。
行格式：
- 默认情况下，InnoDB表使用DYNAMIC行格式创建。要使用除DYNAMIC之外的行格式，请配置innodb_default_row_format，或在CREATE TABLE或ALTER TABLE语句中明确指定ROW_FORMAT选项。
- 紧凑型行格式系列，包括COMPACT、DYNAMIC和COMPRESSED，减少了行存储空间，但增加了一些操作的CPU使用量。如果你的工作负载是典型的，受缓存命中率和磁盘速度限制，那么使用它可能会更快。如果是一个罕见的情况，受CPU速度限制，可能会更慢。
- 紧凑型行格式系列还在使用可变长度字符集（如utf8mb3或utf8mb4）时优化了CHAR列的存储。在ROW_FORMAT=REDUNDANT的情况下，CHAR(N)占据N × 字符集的最大字节长度。许多语言主要使用单字节的utf8mb3或utf8mb4字符编写，因此固定的存储长度通常会浪费空间。通过紧凑型行格式，InnoDB为这些列分配可变数量的存储空间，范围是从N到N × 字符集的最大字节长度，通过去除尾随空格。最小存储长度是N个字节，以便在典型情况下进行原地更新。
索引：
- 表的主键索引应尽可能短。这样可以轻松有效地识别每一行。对于InnoDB表，主键列在每个二级索引中都会被复制，因此如果有许多二级索引，那么较短的主键将节省大量空间。
- 只创建需要的索引来提高查询性能。索引对于检索很有用，但会减慢插入和更新操作。如果你主要通过组合列进行搜索来访问表，那么创建一个单个的复合索引比为每个列创建单独的索引更好。索引的第一部分应该是最常使用的列。如果在从表中选择时总是使用许多列，那么索引中的第一列应该是具有最多重复项的列，以获得更好的索引压缩。
- 如果长字符串列很可能具有前几个字符的唯一前缀，最好只对这个前缀创建索引，使用MySQL支持的在列的最左侧部分创建索引的功能。较短的索引更快，不仅因为它们需要更少的磁盘空间，而且因为它们还可以在索引缓存中获得更多的命中，从而减少了磁盘寻址。
连接：
- 在某些情况下，如果经常扫描的表很多，将其拆分为两个表可能会有利。特别是如果它是一个动态格式表，并且可以使用较小的静态格式表来在扫描表时找到相关行。
- 将具有相同信息的列在不同表中声明为具有相同数据类型的列，以加快基于相应列的连接速度。
- 保持列名称简单，这样您可以在不同表之间使用相同的名称，并简化连接查询。例如，在名为customer的表中，使用列名为name而不是customer_name。为了使您的名称可移植到其他SQL服务器，考虑将它们保持在18个字符以下。
标准化：
- 通常情况下，尽量保持所有数据非冗余（遵循数据库理论中的第三范式）。而不是重复较长的值，如姓名和地址，给它们分配唯一的ID，在多个较小的表中根据需要重复这些ID，并在查询中通过引用连接子句中的ID来连接这些表。
- 如果速度比磁盘空间和保持多个数据副本的维护成本更重要，例如在商业智能场景中，您分析大表中的所有数据，您可以放松规范化规则，重复信息或创建摘要表以获得更快的速度。

优化数据类型

数值类型优化：
- 对于可以表示为字符串或数字的唯一ID或其他值，更倾向于使用数字列而不是字符串列。由于大数字值可以用比相应字符串更少的字节存储，因此传输和比较它们更快且占用更少的内存空间。
- 如果您使用数字数据，在许多情况下，从数据库（使用实时连接）访问信息比从文本文件访问更快。数据库中的信息可能以比文本文件更紧凑的格式存储，因此访问它涉及的磁盘访问较少。您还可以节省应用程序中的代码，因为您可以避免解析文本文件以找到行和列边界。
字符和字符串类型优化：
- 使用二进制排序规则进行比较和排序操作，当您不需要语言特定的排序功能时。您可以使用BINARY运算符在特定查询中使用二进制排序规则。
- 当比较来自不同列的值时，尽可能在声明这些列时使用相同的字符集和排序规则，以避免在运行查询时进行字符串转换。
- 对于大小不超过8KB的列值，请使用二进制VARCHAR而不是BLOB。GROUP BY和ORDER BY子句可能会生成临时表，这些临时表如果原始表不包含任何BLOB列，则可以使用MEMORY存储引擎。
- 如果表包含诸如姓名和地址之类的字符串列，但许多查询不检索这些列，请考虑将字符串列拆分到单独的表中，并在必要时使用外键进行连接查询。当MySQL检索行的任何值时，它会读取包含该行所有列（可能还有其他相邻行）的数据块。保持每行小，并且只包含最常用的列，可以使更多的行适合每个数据块。这样的紧凑表减少了常见查询的磁盘I/O和内存使用。
- 当您在InnoDB表中将随机生成的值用作主键时，请尽可能使用一个升序值，例如当前日期和时间的前缀。当连续的主键值物理存储在一起时，InnoDB可以更快地插入和检索它们。
BLOB类型优化：
- 当存储包含文本数据的大型 BLOB 时，请考虑首先对其进行压缩。当整个表由 InnoDB 或 MyISAM 压缩时，请不要使用此技术。
- 对于具有多个列的表，为了减少不使用 BLOB 列的查询的内存需求，请考虑将 BLOB 列拆分到单独的表中，并在需要时使用连接查询引用它。
- 由于检索和显示 BLOB 值的性能要求可能与其他数据类型非常不同，因此您可以将特定于 BLOB 的表放在不同的存储设备上，甚至放在单独的数据库实例中。例如，检索 BLOB 可能需要大量的顺序磁盘读取，这对传统硬盘比固态硬盘设备更合适。
- 与其针对非常长的文本字符串进行相等性测试，您可以将列值的哈希存储在单独的列中，对该列进行索引，并在查询中测试哈希值。（使用 MD5() 或 CRC32() 函数生成哈希值。）由于哈希函数可能为不同的输入产生重复的结果，因此您仍然在查询中包含一个 AND blob_column = long_string_value 子句来防止错误匹配；性能的好处来自于用于哈希值的较小、易于扫描的索引。

MySQL内部临时表的使用

在某些情况下，服务器在处理语句时会创建内部临时表。用户无法直接控制这种情况发生的时机。

服务器在以下情况下创建临时表：

对 UNION 语句进行评估，但有一些特殊情况除外。
对一些视图进行评估，比如使用 TEMPTABLE 算法、UNION 或聚合的视图。
对派生表进行评估。
对公共表达式进行评估。
为了子查询或半连接的材料化而创建的表。
对包含 ORDER BY 子句和不同的 GROUP BY 子句的语句进行评估，或者 ORDER BY 或 GROUP BY 子句包含来自连接队列中第一个表以外的表的列的语句。
对 DISTINCT 与 ORDER BY 结合使用可能需要一个临时表。
对于使用 SQL_SMALL_RESULT 修改器的查询，MySQL 使用内存中的临时表，除非查询还包含需要在磁盘上存储的元素。
为了评估 INSERT … SELECT 语句，该语句从同一表中进行选择和插入，MySQL 创建一个内部临时表来保存 SELECT 中的行，然后将这些行插入目标表。
对多表 UPDATE 语句进行评估。
对 GROUP_CONCAT() 或 COUNT(DISTINCT) 表达式进行评估。
使用窗口函数进行评估时，根据需要使用临时表。

要确定语句是否需要临时表，使用 EXPLAIN 并检查 Extra 列是否显示 Using temporary（请参阅使用 EXPLAIN 优化查询）。EXPLAIN 并不一定会为派生或材料化的临时表显示 Using temporary。对于使用窗口函数的语句，使用 FORMAT=JSON 的 EXPLAIN 始终提供关于窗口步骤的信息。如果窗口函数使用临时表，则会为每个步骤指示。

一些查询条件会阻止使用内存中的临时表，在这种情况下，服务器将使用磁盘上的临时表：

表中存在 BLOB 或 TEXT 列。但是，自 MySQL 8.0 起，默认的内存内部临时表存储引擎 TempTable 支持二进制大对象类型。请参阅内部临时表存储引擎。
如果使用 UNION 或 UNION ALL，则在 SELECT 列中存在任何字符串列的最大长度大于 512（对于二进制字符串为字节，对于非二进制字符串为字符）。
SHOW COLUMNS 和 DESCRIBE 语句使用 BLOB 作为某些列的类型，因此用于结果的临时表是一个磁盘上的表。

服务器不会对满足某些资格的 UNION 语句使用临时表。相反，它仅保留执行结果列类型转换所需的数据结构。该表没有完全实例化，也没有行被写入或读取；行直接发送到客户端。这样做的结果是减少内存和磁盘需求，并在发送第一行到客户端之前的延迟更小，因为服务器不需要等到执行最后一个查询块。EXPLAIN 和优化器跟踪输出反映了这种执行策略：UNION RESULT 查询块不存在，因为该块对应于从临时表读取的部分。

这些条件使 UNION 在不使用临时表进行评估时符合资格：

联合是 UNION ALL，而不是 UNION 或 UNION DISTINCT。
没有全局 ORDER BY 子句。
联合不是 {INSERT | REPLACE} … SELECT … 语句的顶级查询块。

内部临时表存储引擎

内部临时表可以由 TempTable 或 MEMORY 存储引擎保存在内存中并进行处理，也可以由 InnoDB 存储引擎存储在磁盘上。 TempTable 存储引擎是 MySQL 8.0 中默认用于内部临时表的内存存储引擎。而对于特定的条件或限制，临时表可能会由 InnoDB 存储引擎保存在磁盘上。

内存内部临时表的存储引擎

internal_tmp_mem_storage_engine 变量定义了用于内存中内部临时表的存储引擎。允许的值包括 TempTable（默认值）和 MEMORY。

TempTable 存储引擎提供了对 VARCHAR 和 VARBINARY 列以及其他二进制大对象类型（自 MySQL 8.0.13 起）的高效存储。
以下变量控制 TempTable 存储引擎的限制和行为：

tmp_table_size：从 MySQL 8.0.28 开始，tmp_table_size 定义了由 TempTable 存储引擎创建的任何单个内存中内部临时表的最大大小。当达到 tmp_table_size 限制时，MySQL 自动将内存中的内部临时表转换为 InnoDB 磁盘上的内部临时表。默认的 tmp_table_size 设置为 16777216 字节（16 MiB）。

tmp_table_size 限制旨在防止个别查询消耗过多的全局 TempTable 资源，这可能会影响需要 TempTable 资源的并发查询的性能。全局 TempTable 资源由 temptable_max_ram 和 temptable_max_mmap 设置控制。

如果 tmp_table_size 限制小于 temptable_max_ram 限制，则内存中临时表不可能包含超过 tmp_table_size 限制允许的数据量。如果 tmp_table_size 限制大于 temptable_max_ram 和 temptable_max_mmap 限制之和，则内存中临时表不可能包含超过 temptable_max_ram 和 temptable_max_mmap 限制之和的数据量。

temptable_max_ram：定义 TempTable 存储引擎在开始从内存映射文件分配空间或在 MySQL 开始使用 InnoDB 磁盘上的内部临时表之前可以使用的最大内存量，具体取决于您的配置。默认的 temptable_max_ram 设置为 1073741824 字节（1GiB）。
temptable_use_mmap：控制当超过 temptable_max_ram 限制时，TempTable 存储引擎是从内存映射文件分配空间，还是 MySQL 使用 InnoDB 磁盘上的内部临时表。默认设置为 temptable_use_mmap=ON。
temptable_max_mmap：在 MySQL 8.0.23 中引入。定义了在 MySQL 开始使用 InnoDB 磁盘上的内部临时表之前，TempTable 存储引擎允许从内存映射文件分配的最大内存量。默认设置为 1073741824 字节（1GiB）。该限制旨在解决内存映射文件在临时目录（tmpdir）中使用过多空间的风险。temptable_max_mmap=0 设置禁用了从内存映射文件中分配内存的使用，有效地禁用了它们的使用，无论 temptable_use_mmap 设置如何。

使用 TempTable 存储引擎的内存映射文件受以下规则控制：

临时文件在 tmpdir 变量定义的目录中创建。
临时文件在创建和打开后立即被删除，因此不会保留在 tmpdir 目录中。在临时文件打开时，操作系统会保留临时文件占用的空间。当临时文件被 TempTable 存储引擎关闭或 mysqld 进程关闭时，该空间会被释放。
数据永远不会在 RAM 和临时文件之间、RAM 内部或临时文件之间移动。
如果在 temptable_max_ram 定义的限制范围内有空间可用，则新数据存储在 RAM 中。否则，新数据存储在临时文件中。
如果在一些数据写入临时文件后，RAM 中有空间可用，剩余的表数据可以存储在 RAM 中。
当在内存中创建临时表时使用 MEMORY 存储引擎（internal_tmp_mem_storage_engine=MEMORY），如果临时表变得太大，MySQL 会自动将其转换为磁盘上的表。内存中临时表的最大大小由 tmp_table_size 或 max_heap_table_size 值中较小的值定义。这与使用 CREATE TABLE 显式创建的 MEMORY 表不同。对于这样的表，只有 max_heap_table_size 变量确定表可以增长到多大，而且不会转换为磁盘格式。

磁盘上内部临时表的存储引擎

在 MySQL 8.0.15 及更早版本中，internal_tmp_disk_storage_engine 变量定义了用于磁盘上内部临时表的存储引擎。支持的存储引擎包括 InnoDB 和 MyISAM。

从 MySQL 8.0.16 开始，MySQL 仅使用 InnoDB 存储引擎来创建磁盘上的内部临时表。不再支持 MYISAM 存储引擎用于此目的。

InnoDB 磁盘上的内部临时表默认创建在数据目录中的会话临时表空间中。有关更多信息，请参见 17.6.3.5 节，“临时表空间”。

在 MySQL 8.0.15 及更早版本中：

对于公共表达式（CTE），用于磁盘上内部临时表的存储引擎不能是 MyISAM。如果 internal_tmp_disk_storage_engine=MYISAM，则任何尝试使用磁盘临时表来实现 CTE 的材料化都会导致错误。

当使用 internal_tmp_disk_storage_engine=INNODB 时，生成超出 InnoDB 行或列限制的磁盘上内部临时表的查询将返回 Row size too large 或 Too many columns 错误。解决方法是将 internal_tmp_disk_storage_engine 设置为 MYISAM。

内部临时表存储格式

当由 TempTable 存储引擎管理内存中的内部临时表时，包含 VARCHAR 列、VARBINARY 列和其他二进制大对象类型列（从 MySQL 8.0.13 开始支持）的行在内存中由一个单元格数组表示，每个单元格包含一个 NULL 标志、数据长度和数据指针。列值依次放置在数组之后的连续内存区域中，不会填充。数组中的每个单元格使用 16 字节的存储空间。当 TempTable 存储引擎从内存映射文件中分配空间时，使用相同的存储格式。

当由 MEMORY 存储引擎管理内存中的内部临时表时，使用固定长度行格式。VARCHAR 和 VARBINARY 列值被填充到最大列长度，实际上将它们存储为 CHAR 和 BINARY 列。

在 MySQL 8.0.16 之前，磁盘上的内部临时表由 InnoDB 或 MyISAM 存储引擎管理（取决于 internal_tmp_disk_storage_engine 设置）。这两个引擎都使用动态宽度行格式存储内部临时表。列只占用所需的存储空间，这减少了与使用固定长度行的磁盘上表相比的磁盘 I/O、空间需求和处理时间。从 MySQL 8.0.16 开始，不再支持 internal_tmp_disk_storage_engine，并且磁盘上的内部临时表始终由 InnoDB 管理。

当使用 MEMORY 存储引擎时，语句可以首先创建一个内存中的内部临时表，然后如果表变得太大，则转换为磁盘上的表。在这种情况下，通过跳过转换并直接在磁盘上创建内部临时表可能会获得更好的性能。big_tables 变量可用于强制将内部临时表存储到磁盘中。

监视内部临时表的创建

当在内存或磁盘上创建内部临时表时，服务器会增加 Created_tmp_tables 的值。当在磁盘上创建内部临时表时，服务器会增加 Created_tmp_disk_tables 的值。如果在磁盘上创建了太多的内部临时表，请考虑调整内部临时表存储引擎中描述的特定于引擎的限制。可以使用 Performance Schema 中的 memory/temptable/physical_ram 和 memory/temptable/physical_disk 工具来监视从内存和磁盘分配 TempTable 空间。memory/temptable/physical_ram 报告分配的 RAM 量。memory/temptable/physical_disk 报告在使用内存映射文件作为 TempTable 溢出机制时从磁盘分配的空间量。如果 physical_disk 工具报告的值不为 0，并且使用内存映射文件作为 TempTable 溢出机制，则在某个时刻达到了 TempTable 内存限制。数据可以查询 Performance Schema 内存摘要表，如 memory_summary_global_by_event_name。请参阅第 29.12.20.10 节“内存摘要表”。

实战经验

1、批量查询的SQL做好强制分页
(出现过的坑: 批量查询没做分页，一开始数据量不多没啥感觉。随着业务发展数据量增多，每次查询返回的数据很多。最终会拖累应用和数据库)

2、数据库联表查询时，建议关联的表不超过两张，且要检查关联的字段是否可以缩小数据限定范围，尤其数据量大的表一定要检查关联的字段是否建立索引。
（出过的坑: 关联表时没有限定范围没有索引全表扫描，导致查询超时，数据库连接被占满不能释放，数据库奔溃）

3、sql脚本中尽量不要带业务逻辑，可读性极差，不易维护
（出过的坑: 代码可读性差，可维护性差）

4、表里的c_t,u_t要用数据库的时间
（出过的坑: 用java对象里的值写入或更新。如果没传入会记成0，并且java里计算的时间和真正执行sql的时间有差异。）

4、insert 的时候c_t,u_t要做好初始化

5、update 的时候 u_t 要更新为最新的时间

6、 1）更新较频繁的表，update语句确保用主键做条件，如果批量更新，可采用先查id再根据id更新，并且要按id排序做更新。
2）更新较频繁的表，如果确保不了用主键update，则至少要确保只有一种update语句，用相同的索引条件，更新顺序一致。
7、不同场景的事务会涉及多个相同表更新，一定要确保更新的表顺序一致。
例如场景1要更新A，B，C三个表，场景2要更新B，C两个表。要确保场景1 update A，update B，update C；场景2 update B，update C的顺序。不能场景1 update A，update B，update C；场景2 update C，update B。