前言

在学习优化理论之前，有以下几点要首先明白：

• MySQL优化可以从配置、数据库、表、SQL、内存、CPU等各个层面入手，且与自己在项目中的角色（开发者、DBA）有关。
• 本文站在开发者的角度并基于MySQL8以及InnoDB进行讨论，基本局限在对SQL和索引的优化。
• 下文提到的所有优化方法都是指MySQL优化器在特定条件下对SQL的的优化，我们要做的仅仅是在编写SQL时尽量满足这些特定条件而尽量多的触发这些优化策略。
• MySQL优化器对SQL的优化是动态计算的，随着数据量的增多或减少都会影响优化器的选择。
• MySQL版本的差异和MySQL的不断发展都会导致优化方法和触发条件的变化。

概述

数据库性能取决于很多因素，包括表、查询和配置设置等。这些因素会在硬件层面导致 CPU 和 I/O 操作，我们需要尽可能地减少这些CPU和I/O操作，以此提高效率。一般用户的目标是从现有的软件和硬件配置中获得最佳的数据库性能。而高级用户则会改进 MySQL 软件本身，或者开发自己的存储引擎和硬件设备，以扩展 MySQL 生态系统。

查询优化

查询是指以SELECT语句的形式执行的所有查询数据库的操作，调优查询是最重要的。对查询的优化主要从以下几个方面考虑：

• 要让一个慢查询更快，首先要检查的是是否可以添加索引。在WHERE子句中使用的列上设置索引，以加快计算、过滤和最终检索结果的速度。为了避免浪费磁盘空间，可以构造一组索引来加速应用程序中使用的许多相关查询。索引对于使用连接和外键等特性引用不同表的查询尤其重要。可以使用EXPLAIN语句来确定SELECT使用了哪些索引。
• 隔离和调优查询中花费过多时间的任何部分，例如函数调用。根据查询的结构，可能对结果集中的每一行调用一次函数，甚至对表中的每一行调用一次函数，这极大地提高了效率。
• 尽可能的减少全表扫描的次数，尤其在查询大表的时候。
• 通过定期使用ANALYZE table语句使表统计信息保持最新，这样优化器就可以获得构建有效执行计划所需的信息。
• 了解特定于每个表的存储引擎的调优技术、索引技术和配置参数。InnoDB和MyISAM都有一套指导方针来实现和维持查询的高性能。
• 可以为InnoDB表优化单查询事务。
• 如果性能问题不能通过基本准则之一轻松解决，请通过阅读EXPLAIN计划和调整索引、WHERE子句、连接子句等来调查特定查询的内部细节。(当您达到一定的专业水平时，阅读EXPLAIN计划可能是您处理每个查询的第一步)
• 调节MySQL缓存的属性和大小。
• 锁优化，查询的速度可能会受到同时访问表的其他会话的影响。

此外，对查询语句的优化也可用于对诸如 CREATE TABLE...AS SELECT、INSERT INTO...SELECT和DELETE/UPDATE...WHERE的语句，但这些语句需要考虑的因素更多，因为它们结合了读写操作。

MySQL查询原理

MySQL查询的执行原理涉及多个关键步骤和组件，从语法解析到执行计划生成再到最终结果返回，每个步骤都对查询的性能和效率有重要影响。以下是MySQL查询的详细执行过程：

语法解析和语义分析

MySQL服务器首先接收到客户端发来的查询请求。服务器的解析器（Parser）会对查询语句进行以下处理：

• 语法解析：检查查询语句是否符合MySQL语法规范。
• 语义分析：验证查询中的表名、列名和函数是否存在，以及它们是否被正确使用。

解析器将查询语句转换为内部数据结构，通常是一个解析树（Parsing Tree）或查询树（Query Tree）。这颗树形结构反映了查询语句的结构和逻辑。

查询优化器的作用

一旦语法解析和语义分析完成，MySQL会将查询传递给查询优化器（Query Optimizer）。优化器的主要任务是选择执行查询的最佳方式，以最大限度地提高查询性能和效率。优化器会执行以下步骤：

• 选择最佳执行计划：优化器根据查询语句、表的结构、索引情况和统计信息选择最佳的执行计划。执行计划描述了如何访问表和索引、如何进行连接操作以及如何过滤数据。
• 成本估算：优化器评估每个可能的执行计划的成本，并选择成本最低的执行计划。成本通常包括CPU和I/O的开销估算。
• 优化策略：优化器可能会使用多种优化策略，如索引选择、表连接顺序优化、子查询优化、条件推导和简化等，以提高查询的效率。

执行计划生成

生成最佳执行计划后，MySQL将执行计划传递给执行引擎（Execution Engine）来执行查询。执行计划包括以下重要信息：

• 访问方法：描述如何访问表和索引，包括全表扫描、索引扫描或范围扫描等。
• 连接方法：描述多表连接的顺序和方法，例如嵌套循环连接、哈希连接或排序合并连接。
• 过滤和排序：描述如何应用WHERE条件、排序操作和聚合操作。

执行查询

执行引擎根据生成的执行计划执行查询，这包括以下步骤：

• 数据访问：根据执行计划从磁盘或内存中读取数据块。
• 应用过滤条件：执行引擎根据WHERE子句中的条件过滤数据，筛选出符合条件的数据行。
• 执行连接：如果查询涉及多个表，执行引擎会根据连接方法执行表连接操作，将相关数据行组合在一起。
• 排序和聚合：如果查询包含ORDER BY子句或聚合函数（如SUM、COUNT等），执行引擎会对数据进行排序和聚合操作。

结果返回

执行引擎生成最终的结果集，并将结果返回给客户端应用程序。客户端可以进一步处理结果集或直接呈现给最终用户。

查询执行计划

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

EXPLAIN语句

DESCRIBE和EXPLAIN语句是同义词。在实践中，DESCRIBE关键字更常用于获取有关表结构的信息，而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
}

获取表结构信息

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 语句。
• 功能说明:
  • 使用 EXPLAIN 可以显示优化器关于语句执行计划的信息。即，MySQL 解释了它如何处理该语句，包括如何连接表以及连接的顺序。
  • 当 EXPLAIN 与 FOR CONNECTION connection_id 一起使用时，它显示正在指定连接中执行的语句的执行计划。
  • 对于 SELECT 语句，EXPLAIN 生成的额外执行计划信息可以通过 SHOW WARNINGS 显示。
• 优化提示:
  • 使用 EXPLAIN 可以查看在哪些表上添加索引，以使语句通过使用索引查找行从而更快地执行。还可以使用 EXPLAIN 检查优化器是否以最佳顺序连接表。为了提示优化器使用与 SELECT 语句中表名顺序相对应的连接顺序，可以以 SELECT STRAIGHT_JOIN 开头，而不是仅仅使用 SELECT。但请注意，STRAIGHT_JOIN 可能会阻止索引的使用，因为它禁用了半连接转换。
• 更新统计信息:
  • 如果你发现某些索引没有被使用，而你认为它们应该被使用，可以运行 ANALYZE TABLE 来更新表统计信息，例如键的基数，这会影响优化器的选择。

EXPLAIN 输出格式

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

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

• id：表示查询中 SELECT 语句的序号。如果该行引用其他行的联合结果，则该值可以为NULL。在这种情况下，表名列显示类似<unionM,N>的值，表示该行引用id值为M和N的行的联合结果（这里的行指的是EXPLAIN输出结果的行，不是具体数据源的表行）。
• select_type：查询类型，可以是以下表格中的任何类型之一：

查询类型	说明
SIMPLE	简单查询（即普通的SQL查询和简单的关联查询，不使用联合查询和子查询）
PRIMARY	当有子查询或联合查询出现时的最外层查询（即没有其它查询嵌套在这个查询内）
UNION	联合查询中的第二个或之后的SELECT语句（这个联合查询不依赖于外部查询）
DEPENDENT UNION	联合查询中的第二个或之后的SELECT语句（这个联合查询依赖于外部查询）
UNION RESULT	是联合查询去重使用的临时表，用于存储联合查询的结果
SUBQUERY	子查询中的第一个SELECT语句（这个子查询不依赖于外部查询）
DEPENDENT SUBQUERY	子查询中的第一个SELECT（这个子查询依赖于外部查询）
DERIVED	派生表
DEPENDENT DERIVED	依赖于另一个表的派生表
MATERIALIZED	物化子查询
UNCACHEABLE SUBQUERY	不能为其缓存结果并且必须为外部查询的每一行重新求值的子查询
UNCACHEABLE UNION	联合查询中属于不可缓存子查询的第二次或之后的SELECT语句

• table：输出行所指向的表的名称。这也可以是以下值之一：
  • <unionM,N>：该行是id值为M和N的行的并集。
  • <derivedN>：该行引用id值为N的行的派生表结果。
  • <subqueryN>：该行引用id值为N的行的物化子查询的结果。
• partitions： 该查询匹配记录的分区。对于非分区表，该值为 NULL。
• type：连接类型。
• 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如何解析查询的附加信息。

访问类型

在 MySQL 的 EXPLAIN 输出中，type 列描述了访问表的方式（大部分文档解释称连接类型，这实际上是有歧义的）。以下列表按从最优到最差的顺序描述了访问类型：

• system：表示表只有一行（即系统表）。这是 const 类型的特殊情况。
• const：表示表最多只有一行匹配。这种类型非常快，因为只读一次。const 类型用于将主键或唯一索引的所有部分与常量值进行比较。

SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

• eq_ref：表示每个前面表的行组合从此表中读取一行。除了 system 和 const 类型，这是最佳的连接类型。用于连接使用所有索引部分，并且索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引。

SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

• ref：表示对于前面表的每个行组合，从此表中读取所有具有匹配索引值的行。用于连接仅使用键的左边前缀或键不是 PRIMARY KEY 或 UNIQUE 索引的情况。如果使用的键仅匹配少量行，这是一个好的连接类型。

SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

• ref_or_null：这种连接类型类似于 ref，但增加了对包含 NULL 值的行的额外搜索。最常用于解决子查询。

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

• index_merge：指示使用了 Index Merge 优化。在这种情况下，输出行中的 key 列包含使用的索引列表，key_len 列包含使用的最长键部分列表。
• unique_subquery：替代某些 IN 子查询的 eq_ref，提高效率。形式如下：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

• index_subquery：类似于 unique_subquery，但适用于子查询中非唯一索引。形式如下：

value IN (SELECT key_column FROM single_table WHERE some_expr)

• range：使用索引选择行，只检索在给定范围内的行。key 列指示使用的索引，key_len 列包含使用的最长键部分。ref 列对于这种类型为 NULL。

SELECT * FROM tbl_name WHERE key_column = 10;
SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

• index：index 连接类型与 ALL 相同，只是扫描索引树。

  • 如果索引是查询的覆盖索引，并且可以用于满足从表中获取的所有数据，则仅扫描索引树。这种情况下，Extra 列显示 Using index。
  • 另一种情况是，使用索引从表中查找数据行时执行全表扫描。这时 Extra 列不显示 Using index。

• ALL：对每个前面表的行组合进行全表扫描。如果此表是第一个没有标记为 const 的表，通常效果不好；在所有其他情况下，通常非常糟糕。通常可以通过添加索引来避免 ALL，以使表行检索基于常量值或前面表的列值。

额外的输出信息

在 MySQL 的 EXPLAIN 输出中，Extra 列包含了关于 MySQL 如何解析查询的附加信息。以下是大部分可能出现在 Extra 列中的值及其解释。在优化查询时，需要特别注意 Extra 列中的 Using filesort 和 Using temporary 值。

• Backward index scan：优化器能够在 InnoDB 表上使用降序索引。通常与 Using index 一起显示。
• LooseScan(m…n)：使用了半连接 LooseScan 策略。m和n是键部分编号。
• No tables used：查询没有 FROM 子句，或者有一个 FROM DUAL 子句。
• Start temporary, End temporary：指示使用了半连接策略的临时表。
• Using filesort：MySQL 必须进行额外的遍历以找出如何按排序顺序检索行。该排序通过遍历所有行并存储排序键和指向行的指针来完成。
• Using index：列信息仅从索引树中检索，无需进行额外的查找来读取实际行。
• Using index condition：通过首先访问索引元组并测试它们来读取表，这样可以推迟读取完整表行，除非必要。
• Using index for group-by：表示 MySQL 找到了一个索引，可以用来检索 GROUP BY 或 DISTINCT 查询的所有列，而无需对实际表进行额外的磁盘访问。
• Using index for skip scan：指示使用了 Skip Scan 访问方法。
• Using join buffer (Block Nested Loop), Using join buffer (Batched Key Access), Using join buffer (hash join)：早期连接中的表被读入连接缓冲区，然后从缓冲区中使用它们的行来执行与当前表的连接。
• Using MRR：表示使用了多范围读优化策略。
• Using temporary：为了解决查询，MySQL 需要创建一个临时表来保存结果。
• Using where：使用 WHERE 子句来限制匹配下一表或发送给客户端的行。
• Zero limit：查询有一个 LIMIT 0 子句，无法选择任何行。

通过理解 EXPLAIN 输出中 Extra 列的这些值，可以更好地分析查询的执行情况，并采取措施优化查询性能。

如何使用EXPLAIN进行优化

通过使用 EXPLAIN 命令，可以获得关于 MySQL 查询执行计划的详细信息。这些信息有助于优化查询性能。以下是一个示例，演示了如何基于 EXPLAIN 提供的信息逐步优化多表连接查询。假设有如下的 SELECT 语句：

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	CHAR(10)
tt	AssignedPC	CHAR(10)
tt	ClientID	CHAR(10)
et	EMPLOYID	CHAR(15)
do	CUSTNMBR	CHAR(15)

索引信息如下：

表	索引
tt	ActualPC
tt	AssignedPC
tt	ClientID
et	EMPLOYID (主键)
do	CUSTNMBR (主键)

最初的 EXPLAIN 输出如下：

表	类型	可能的键	键	键长度	引用	行数	额外信息
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	Range checked for each record (index map: 0x23)
		ClientID,
		ActualPC

由于每个表的 type 都是 ALL，这意味着 MySQL 生成了所有表的笛卡尔积。为了减少检查的行数，我们需要确保表之间的连接条件使用索引。

• 首先，解决列长度不匹配的问题，将 tt.ActualPC 长度从 10 字符更改为 15 字符：

ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

再次执行 EXPLAIN，结果如下：

表	类型	可能的键	键	键长度	引用	行数	额外信息
tt	ALL	AssignedPC,	NULL	NULL	NULL	3872	Using where
		ClientID,
		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

• 继续修正其他列长度不匹配的问题：

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

再次执行 EXPLAIN，结果如下：

表	类型	可能的键	键	键长度	引用	行数	额外信息
et	ALL	PRIMARY	NULL	NULL	NULL	74
tt	ref	AssignedPC,	ActualPC	15	et.EMPLOYID	52	Using where
		ClientID,
		ActualPC
et_1	eq_ref	PRIMARY	PRIMARY	15	tt.AssignedPC	1
do	eq_ref	PRIMARY	PRIMARY	15	tt.ClientID	1

• 最后，通过分析键分布来告知 MySQL 实际的数据分布情况：

ANALYZE TABLE tt;

最终的 EXPLAIN 输出如下：

表	类型	可能的键	键	键长度	引用	行数	额外信息
tt	ALL	AssignedPC,	NULL	NULL	NULL	3872	Using where
		ClientID,
		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

此时，查询已经优化得非常接近最佳状态。可以检查 rows 列的值是否与实际返回的行数接近，如果差距较大，可以尝试使用 STRAIGHT_JOIN 并更改 FROM 子句中表的顺序来进一步优化查询。

避免全表扫描

EXPLAIN输出中的类型列显示为ALL时，表示MySQL使用全表扫描来解析查询。通常出现这种情况的条件包括：

• 表非常小，执行全表扫描比使用索引查找更快。这在行数少于10行且行长度较短的表中很常见。
• ON或WHERE子句中没有可用的限制条件来使用索引列。
• 比较索引列与常量值，并且MySQL根据索引树计算，认为这些常量覆盖了表的大部分，因此执行全表扫描会更快。
• 当使用具有低基数的索引键值（即很多行具有相同的键值）进行查询时，MySQL会假设使用这个索引键值可能需要执行大量的索引查找操作。由于这种情况下每次查找都需要花费时间，MySQL可能会认为直接执行全表扫描会更快速有效。

对于小表，全表扫描通常是合适的，并且性能影响可以忽略不计。对于大表，应该尽可能的尝试使用下文提到的技术来避免优化器错误地选择全表扫描。

WHERE子句优化

不必优化以下算术操作，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

• 索引使用的常量表达式只使用一次
• 检查具有常量值的数值类型列的比较，并对无效或超出范围的值进行折叠或删除

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

• 对于连接中的每个表，将构造一个更简单的WHERE，以便对表进行快速的WHERE计算，并尽可能快地跳过行。
• 在查询中，所有常数表都是在其他表之前读取的。常量表是下列任意一种：
  • 空表或只有一行的表
  • 与PRIMARY KEY或UNIQUE索引上的WHERE子句一起使用的表，其中所有索引部分都与常量表达式进行比较，并定义为NOT NULL。
• 最佳连接组合是通过尝试所有可能的方式来确定的。如果ORDER BY和GROUP BY子句中的所有列都来自同一个表，则在连接时优先选择该表。
• 如果有ORDER BY子句和不同的GROUP BY子句，或者ORDER BY或GROUP BY包含来自其他表的列，则会创建一个临时表。
• 每个表索引都会被优化器查询，并使用最佳索引，除非优化器认为使用表扫描更高效。优化器会基于表大小、行数和I/O块大小等附加因素进行估计。
• 在某些情况下，MySQL可以从索引读取行而不需要访问数据文件。如果使用的索引列全是数值类型，则仅使用索引树来解析查询。

range优化

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

单列索引的range优化

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

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

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

• 查询字符串中的常量
• 来自同一连接的常量表或系统表的一列
• 不相关子查询（查询结果不依赖外部查询结果的子查询）的结果
• 任何完全由上述类型的子表达式组成的表达式

MySQL尝试从WHERE子句中为每个可能的索引提取范围条件。在提取过程中，不能用于构造范围条件的条件将被删除，产生重叠范围的条件将被合并，产生空范围的条件将被删除。所以通常用于范围扫描的条件比WHERE子句的条件更宽松。因此MySQL会执行额外的检查，以过滤出那些满足范围条件但不满足完整WHERE子句的行。

多列索引的range优化

多列索引的索引值区间是对每个组成多列索引的列（键）的范围条件的扩展，并且必须遵守最左前缀原则。举例来说，对于一个多列索引 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、>、<、>=、<=、!=、<>、BETWEEN 或 LIKE（但 LIKE 不能以通配符开头），并与常量值比较，那么这些条件可以用来构建范围条件。优化器会尝试确定一个包含所有匹配条件行的范围条件（如果使用 <> 或 !=，则可能会生成两个范围条件）。
• 如果条件作用于多个键，并且使用了 =、<=> 或 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)

如果存在多个索引值区间：

• 当条件用 OR 组合时，结果集合是各条件范围的并集。
• 当条件用 AND 组合时，结果集合是各条件范围的交集。

跳跃扫描

考虑以下场景：

CREATE TABLE t1 (
  f1 INT NOT NULL,
  f2 INT NOT NULL,
  PRIMARY KEY(f1, f2)
);

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40;

要执行此查询，MySQL 可以选择使用索引扫描来获取所有行（索引包含所有要选择的列），然后应用 WHERE 子句中的 f2 > 40 条件以生成最终结果集。

范围扫描比全索引扫描更高效，但在此情况下不能使用，因为没有关于第一个索引列 f1 的条件。优化器可以对每个 f1 的值执行多个范围扫描，使用一种类似于松散索引扫描的方法，称为跳跃扫描（Skip Scan）：

• 在第一个索引部分 f1 的不同值之间跳跃（索引前缀）。
• 在每个不同的前缀值上执行子范围扫描，以 f2 > 40 条件应用于剩余的索引部分。

对于前面显示的数据集，算法的操作如下：

• 获取第一个键部分的第一个不同值（f1 = 1）。
• 基于第一个和第二个键部分构造范围（f1 = 1 AND f2 > 40）。
• 执行范围扫描。
• 获取第一个键部分的下一个不同值（f1 = 2）。
• 基于第一个和第二个键部分构造范围（f1 = 2 AND f2 > 40）。
• 执行范围扫描。

使用这种策略减少了访问的行数，因为 MySQL 跳过了每个构造范围中不符合条件的行。跳跃扫描访问方法在以下条件下适用：

• 表 T 至少有一个复合索引，键部分的形式为 ([A_1, ..., A_k,] B_1, ..., B_m, C [, D_1, ..., D_n])。键部分 A 和 D 可以为空，但 B 和 C 必须非空。
• 查询仅引用一个表。
• 查询不使用 GROUP BY 或 DISTINCT。
• 查询仅引用索引中的列。
• 对 A_1, ..., A_k 的谓词必须是等值谓词，并且它们必须是常量。这包括 IN() 操作符。
• 查询必须是合取查询；即 AND 的 OR 条件：（cond1(key_part1) OR cond2(key_part1)）AND（cond1(key_part2) OR ...）AND ...
• C 上必须有范围条件。
• 允许对 D 列的条件。对 D 的条件必须与 C 的范围条件结合使用。

在 EXPLAIN 输出中，使用索引进行跳跃扫描的方法在 Extra 列中显示为 Using index for skip scan。

index_merge优化

index_merge优化通过多个range优化检索行，并将它们的结果合并为一个。这种优化只能作用于单表。index_merge优化有几种算法，这些算法显示在 EXPLAIN 输出的 Extra 字段中：

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

以下部分详细描述了这些算法。

索引合并交集算法

这个算法适用于当 WHERE 子句被转换为几个在不同索引上的范围条件并用 AND 组合在一起时，每个范围条件是以下之一：

• 这种形式的 N 部分表达式，其中索引恰好有 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 输出中的 Extra 字段包含 Using index）。例如：

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

如果使用的索引不覆盖查询中使用的所有列，则仅当满足所有使用的键的范围条件时才检索完整的行。如果合并条件之一是 InnoDB 表主键上的条件，则它不会用于行检索，但会用于过滤使用其他条件检索到的行。

索引合并并集算法

该算法适用于表的 WHERE 子句被转换为几个在不同索引上的范围条件并用 OR 组合在一起时，每个条件是以下之一：

1. 这种形式的 N 部分表达式，其中索引恰好有 N 部分（即覆盖所有索引部分）：

   key_part1 = const1 OR key_part2 = const2 ... OR key_partN = constN
   

2. InnoDB 表的主键上的任何范围条件。

3. 索引合并交集算法适用的条件。

例如：

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并进行排序，然后再返回任何行。排序是为了避免重复行、确保结果顺序、提高合并效率以及减少磁盘I/O操作。在涉及多个范围条件的查询中，通过对行ID进行排序，可以更高效地合并结果集并优化查询性能。

索引条件下推（ICP）优化

ICP是MySQL中的一种优化技术，用于在使用索引检索表行时提升查询性能。当未启用ICP时，存储引擎通过遍历索引来定位基表中的行，并将它们返回给MySQL服务器进行WHERE条件的评估。启用ICP后，如果WHERE条件的部分可以仅通过索引列进行评估，MySQL服务器将这部分WHERE条件下推给存储引擎。存储引擎接着使用索引条目来评估下推的索引条件，只有满足条件时才从表中读取行。ICP能够减少存储引擎访问基表和MySQL服务器访问存储引擎的次数。

当使用索引下推时，EXPLAIN输出中的额外列显示为Using index condition，而不是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%'）无法限制必须扫描的行数，因此在没有ICP的情况下，此查询必须检索具有zipcode='95054'的所有人员的完整表行。

使用ICP时，MySQL在读取完整表行之前先检查lastname LIKE '%etrunia%'条件。这样可以避免读取与zipcode条件匹配但不符合lastname条件的行。

磁盘扫描多范围读取（MRR）优化

MRR优化旨在减少对二级索引进行范围扫描时的随机磁盘访问次数，特别适用于未完全缓存在存储引擎中的大型表。其工作原理如下：

• 索引扫描和键收集：MySQL首先扫描二级索引，收集符合查询条件的行的键。
• 键排序：收集到的键根据主键顺序进行排序。
• 顺序数据访问：利用排序后的键，MySQL按顺序从基表中检索数据行。这种顺序访问模式通过最小化随机磁盘寻址来提高效率。

常量折叠优化

对于MySQL中常数与列值之间的比较优化处理，当常数值超出列的数据类型范围或类型不匹配时，MySQL会在查询优化阶段而不是执行阶段将其进行折叠处理。这种处理方式适用于以下比较运算符：>、>=、<、<=、<>、!=、=和<=>。

考虑以下创建表格的语句：

CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);

在查询 SELECT * FROM t WHERE c < 256 中，WHERE条件中的整数常数256超出了TINYINT UNSIGNED列的范围。以前，MySQL会将两个操作数都视为较大的类型处理，但现在，由于任何c列的允许值都小于常数，因此WHERE表达式可以简化为 WHERE 1，因此查询被重写为 SELECT * FROM t WHERE 1。

这使得优化器可以完全删除WHERE表达式。如果列c是可空的（即仅定义为TINYINT UNSIGNED），则查询会被重写为：

SELECT * FROM t WHERE ti IS NOT NULL

常数与MySQL支持的列类型进行折叠处理的方式如下：

• 整数列类型：整数类型与整数值进行比较。如果常数超出了列类型的范围，比较会折叠为1或IS NOT NULL。如果常数是范围的边界值，则比较会被折叠为=。

• 浮点数和定点数值：对于浮点数（如DECIMAL, REAL, DOUBLE或FLOAT），如果常数具有非零小数部分且与列值类型不匹配，则不能相等，会相应地进行折叠处理。对于其他比较操作，会根据符号进行四舍五入，然后进行范围检查。

这种优化不能用于以下情况：

• 使用BETWEEN或IN的比较。
• 对BIT列或使用日期或时间类型的列的比较。
• 在准备语句的准备阶段期间，虽然可以在实际执行准备语句的优化阶段应用。

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 将执行一个 文件排序操作，该操作读取表行并对它们进行排序。文件排序在查询执行中构成了一个额外的排序阶段。

使用文件排序满足ORDER BY

如果无法使用索引满足 ORDER BY 子句，MySQL 将执行一个 文件排序操作，该操作读取表行并对它们进行排序。文件排序在查询执行中构成了一个额外的排序阶段。在MySQL中有以下两种文件排序：
是的，MySQL 的确分为磁盘文件排序和内存文件排序两种方式。这两种文件排序方式是优化器在处理需要排序的大数据集时所采用的不同策略。

• 内存文件排序：内存文件排序是指当需要排序的数据量较小时，MySQL 会在内存中完成排序操作。这种方式速度较快，因为内存访问速度远高于磁盘访问速度。
• 磁盘文件排序：磁盘文件排序是指当需要排序的数据量较大，超出了内存的承载能力时，MySQL 会将数据写入磁盘上的临时文件，进行排序并合并。这种方式会涉及频繁的磁盘 I/O 操作，因此速度较慢。

GROUP BY优化

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

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

松散索引扫描

处理GROUP BY的最高效方式是使用索引直接检索分组列。使用这种访问方法时，MySQL利用了一些索引类型（例如BTREE）的键是有序的特性。该特性使得在不必考虑满足所有WHERE条件的索引所有键的情况下，可以在索引中使用查找组。由于这种访问方法只考虑索引中的一部分键，因此称之为松散索引扫描。当没有WHERE子句时，松散索引扫描读取的键数与组数相同，这个数目通常远小于所有键的数量。如果WHERE子句包含范围谓词，松散索引扫描会查找每个满足范围条件的组的第一个键，并且同样读取最少数量的键。

在以下条件下，松散索引扫描是可行的：

• 查询仅涉及一个表。
• GROUP BY只包含形成索引左前缀的列，且不包含其他列。
• 选择列表中（如果有）的唯一聚合函数是MIN()和MAX()，并且所有这些函数都引用同一列。该列必须在索引中，并且必须紧跟在GROUP BY列之后。
• 在一个查询中，使用GROUP BY的部分之外，如果索引中的其他列被引用，那么这些列必须是常量（即，这些列必须在等式中与常量进行比较），唯一的例外是作为MIN()或MAX()函数参数的列。
• 索引中的列必须是完整的列值，而不仅仅是前缀。

除了已经支持的MIN()和MAX()引用，松散索引扫描访问方法还可以应用于选择列表中其他形式的聚合函数引用，：

• 支持AVG(DISTINCT)，SUM(DISTINCT)和COUNT(DISTINCT)。AVG(DISTINCT)和SUM(DISTINCT)接受单个参数。COUNT(DISTINCT)可以有多个列参数。
• 查询中不能有GROUP BY或DISTINCT子句。
• 之前描述的松散索引扫描限制仍然适用。

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

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

紧凑索引扫描

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

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

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

假设在表t1(c1,c2,c3,c4)上有一个索引idx(c1,c2,c3)。以下查询无法使用之前描述的松散索引扫描访问方法，但仍可以使用紧密索引扫描访问方法。

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会停止扫描任何未使用的表。在以下情况下，假设t1在t2之前被使用（可以通过EXPLAIN来检查），MySQL在找到t2中的第一行时（针对t1中的任何特定行）停止读取t2：

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

这种优化可以减少不必要的扫描和读取，从而提高查询的执行效率。

LIMIT优化

如果您只需要结果集中指定数量的行，请在查询中使用 LIMIT 子句，而不是获取整个结果集并丢弃多余的数据。

MySQL 有时会优化带有 LIMIT row_count 子句且没有 HAVING 子句的查询：

• 如果您仅选择几行数据，MySQL 在某些情况下会使用索引，而通常情况下它会更倾向于进行全表扫描。
• 如果将 LIMIT row_count 与 ORDER BY 结合使用，MySQL 在找到排序结果的前 row_count 行后就会停止排序，而不是对整个结果进行排序。如果必须进行文件排序，那么在找到前 row_count 行之前，会选择所有匹配查询但不含 LIMIT 子句的行，并对其中大部分或全部进行排序。在找到初始行后，MySQL 不会再对结果集的剩余部分进行排序。
• 如果将 LIMIT row_count 与 DISTINCT 结合使用，MySQL 会在找到 row_count 个唯一行后停止。
• 在某些情况下，可以通过按索引顺序读取或对索引进行排序来解决 GROUP BY。在这种情况下，LIMIT row_count 不会计算任何不必要的 GROUP BY 值。
• LIMIT 0 快速返回一个空集。这对于检查查询的有效性非常有用。
• 如果 ORDER BY 未使用索引但存在 LIMIT 子句，优化器可能会避免使用磁盘文件排序，而在内存中使用内存文件排序操作对行进行排序。
• 如果多个行在 ORDER BY 列中具有相同的值，服务器可以以任意顺序返回这些行，并且可能会根据整体执行计划以不同顺序返回这些行。换句话说，对于非排序列，这些行的排序顺序是不确定的。

连接查询优化

连接查询原理

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.20 开始，MySQL 不再使用块嵌套循环，而是在以前使用块嵌套循环的所有情况下都使用哈希连接。

• 哈希连接

哈希连接通过将一个表的列值映射到一个哈希表中，然后将另一个表的相应列值与哈希表中的值匹配来实现连接。在以下情况下可以使用哈希连接：

• 等值连接条件：当连接条件为等值连接（即使用等号=）时，如ON t1.c1 = t2.c1。
• 没有索引的连接条件：当连接条件没有索引可以使用时。
• 单表谓词：即使存在可以用于单表条件的索引，哈希连接也可能被使用。

内连接规范

内连接是 SQL 查询中最常用的连接类型，用于返回两个表中匹配的行。以下是 MySQL 实现 A INNER JOIN B 连接的规范：

1. 依赖关系设置：
   • 表 A 和 表 B 被设定为互相依赖。
   • 在内连接中，表 A 和表 B 的依赖关系是对等的，即它们在连接操作中互相依赖。
2. 连接条件使用：
   • 使用 INNER JOIN 条件来决定如何检索两个表中的匹配行。
   • 连接条件 ON 子句用于定义两个表中哪些列之间的关系是匹配的。
   • WHERE 子句可以用于进一步过滤结果，但不是决定连接的条件。
3. 标准优化：
   • 执行所有标准的连接优化。
   • 内连接会尽可能高效地执行，通常使用哈希连接、嵌套循环连接等算法。
   • 所有的 WHERE 优化也会应用于内连接查询。
4. 行生成：
   • 只有当 表 A 和 表 B 中都有匹配的行时，才会在结果集中生成行。
   • 如果 表 A 和 表 B 中某一行没有匹配，结果集中将不会包含该行。
5. 特殊情况处理：
   • 内连接不会生成额外的行。它只返回符合连接条件的行。
   • 如果连接条件中有关于某一表的过滤条件，MySQL 会在连接之后应用这些条件。
   • 对于复杂的查询，优化器可能会重写查询以使用更有效的执行计划。例如，连接条件可以被转换或重新排序以提高查询性能。

外连接规范

外连接包括 LEFT JOIN 和 RIGHT JOIN。MySQL 实现了如下的 A LEFT JOIN B 连接规范：

1. 依赖关系设置：
   • 表 B 被设定为依赖于表 A 以及所有表 A 所依赖的表。
   • 表 A 被设定为依赖于 LEFT JOIN 条件中使用的所有表（除了 B）。
2. 连接条件使用：
   • 使用 LEFT JOIN 条件来决定如何检索表 B 中的行。（换句话说，WHERE 子句中的任何条件都不会被使用）
3. 标准优化：
   • 执行所有标准的连接优化，但有一个例外，即总是在读取一个表之后再读取它所依赖的所有表。如果存在循环依赖，则会发生错误。
   • 执行所有标准的 WHERE 优化。
4. 行生成：
   • 如果在 A 中有一行匹配 WHERE 子句，但在 B 中没有一行匹配 ON 条件，则会生成一个额外的 B 行，其中所有列都设置为 NULL。
5. 特殊情况处理：
   • 如果你使用 LEFT JOIN 查找某个表中不存在的行，并且在 WHERE 部分有如下测试：col_name IS NULL，其中 col_name 是声明为 NOT NULL 的列，那么 MySQL 在找到一行符合 LEFT JOIN 条件的行后会停止搜索更多的行（对于特定的键组合）。

RIGHT JOIN 的实现与 LEFT JOIN 类似，只是表的角色颠倒了。

子查询优化

用半连接和反连接转换优化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 将根据成本从以下策略中进行选择：

1. 将子查询转换为连接，或者使用表拉出（table pullout）并在子查询表和外部表之间运行内连接。表拉出将一个表从子查询中拉出到外部查询中。

2. 重复消除（Duplicate Weedout）：将半连接视为连接运行，并使用临时表删除重复记录。

3. 首次匹配（FirstMatch）：在扫描内部表以获取行组合时，如果某个值组有多个实例，则选择一个而不是返回它们所有。这个“快捷方式”扫描并消除了不必要的行的生成。

4. 宽松扫描（LooseScan）：使用索引扫描子查询表，该索引可以从每个子查询的值组中选择一个值。

5. 将子查询材料化为带索引的临时表，用于执行连接，其中索引用于删除重复项。在将临时表与外部表连接时，索引也可能稍后用于查找；如果不使用，则会扫描该表。有关材料化的更多信息，请参阅“使用材料化优化子查询”（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策略优化子查询

实战经验

• 批量查询的SQL做好强制分页。（出现过的坑，批量查询没做分页，一开始数据量不多没啥感觉。随着业务发展数据量增多，每次查询返回的数据很多。最终会拖累应用和数据库）。
• 数据库联表查询时，建议关联的表不超过两张，且要检查关联的字段是否可以缩小数据限定范围，尤其数据量大的表一定要检查关联的字段是否建立索引（出过的坑: 关联表时没有限定范围没有索引全表扫描，导致查询超时，数据库连接被占满不能释放，数据库奔溃）。
• SQL脚本中尽量不要带业务逻辑，可读性极差，不易维护。
• 表里的c_t、u_t要用数据库的时间（出过的坑: 用Java对象里的值写入或更新。如果没传入会记成0，并且Java里计算的时间和真正执行SQL的时间有差异）。
• insert的时候c_t、u_t要做好初始化
• update 的时候u_t要更新为最新的时间
• 更新较频繁的表：
  • update语句确保用主键做条件，如果批量更新，可采用先查id再根据id更新，并且要按id排序做更新。
  • 更新较频繁的表，如果确保不了用主键update，则至少要确保只有一种update语句，用相同的索引条件，更新顺序一致。
• 不同场景的事务会涉及多个相同表更新，一定要确保更新的表顺序一致。
  例如场景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。

数据库优化

数据分片

点击查看原文

分布式事务

点击查看原文

读写分离

点击查看原文

数据库网关

点击查看原文

流量治理

点击查看原文