MySQL Optimization

Stone大约 114 分钟

MySQL Optimization

注意：
此文档对应的 MySQL 版本为 8.0.32 社区版。

Optimization Overview

数据库性能取决于两个方面：

软件层面：操作系统的配置，数据库的配置，存储引擎的选择，表的设置，SQL 的写法等。
硬件层面：CPU，磁盘，内存，网络等。

Optimizing at the Database Level

在数据库级别需要考虑：

存储引擎是否合适，一般情况下都使用 InnoDB 存储引擎。
表的设计是否合理，是否指定了主键，是否使用了正确的数据类型，是否使用了适当的行格式。
是否有正确的索引来提高查询效率。
事务隔离级别是否合适，是否使用了合适的锁。
是否合理配置了内存参数和 I/O 参数。

Optimizing at the Hardware Level

在硬件级别需要考虑：

磁盘
CPU
内存

Understanding the Query Execution Plan

根据表，列，索引的详细信息以及 WHERE 子句中的条件，MySQL 优化器会使用许多技术来高效地执行SQL查询。优化器选择用于执行的最高效操作集称为执行计划，也称为 EXPLAIN 计划。通过了解 EXPLAIN 计划，并学习 SQL 语法和索引，以便调优 SQL。

Optimizing Queries with EXPLAIN

使用 EXPLAIN 语句查看 MySQL 如何执行 SQL 语句：

EXPLAIN 可用于 SELECT，DELETE，INSERT，REPLACE 和 UPDATE 语句。
当使用 EXPLAIN 时，MySQL 从优化器获取语句执行计划信息。
当 EXPLAIN 与 FOR CONNECTION connection_id 一起使用时，显示的时某个连接正在执行语句的执行计划。
对于 SELECT 语句，EXPLAIN 后还可使用 SHOW WARNINGS 显示额外执行计划信息。
EXPLAIN 可用于检查包含分区表的查询。
可以使用 FORMAT 选项指定输出格式，默认为 TRADITIONAL，输出为表格格式，可以指定为 JSON 输出 JSON 格式。

通过使用 EXPLAIN 语句获取执行计划查看 MySQL 如何执行 SQL 语句，可以定位到性能瓶颈，测试是否它可以通过增加索引或者调整表连接顺序以提高性能。EXPLAIN 语句并不会真的去执行相关的语句。

[(none)]> EXPLAIN SELECT USER,HOST FROM MYSQL.USER;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | USER  | NULL       | index | NULL          | PRIMARY | 351     | NULL |   13 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

如果遇到应该使用索引时未使用索引的问题，请运行 ANALYZE TABLE 以更新表统计信息。

注意：

还可以使用 EXPLAIN 获取表字段的信息。

[(none)]> EXPLAIN information_schema.OPTIMIZER_TRACE;
+-----------------------------------+----------------+------+-----+---------+-------+
| Field                             | Type           | Null | Key | Default | Extra |
+-----------------------------------+----------------+------+-----+---------+-------+
| QUERY                             | varchar(65535) | NO   |     |         |       |
| TRACE                             | varchar(65535) | NO   |     |         |       |
| MISSING_BYTES_BEYOND_MAX_MEM_SIZE | int            | NO   |     |         |       |
| INSUFFICIENT_PRIVILEGES           | tinyint(1)     | NO   |     |         |       |
+-----------------------------------+----------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

EXPLAIN Output Format

使用 EXPLAIN 语句查看 MySQL 如何执行 SQL 语句，可用于 SELECT，DELETE，INSERT，REPLACE 和 UPDATE 语句。

EXPLAIN Output Columns

EXPLAIN 的每一行输出对应一个表的信息，每一行的字段如下表：

Column	JSON Name	Meaning
`id`open in new window	`select_id`	The `SELECT` identifier
`select_type`open in new window	None	The `SELECT` type
`table`open in new window	`table_name`	The table for the output row
`partitions`open in new window	`partitions`	The matching partitions
`type`open in new window	`access_type`	The join type
`possible_keys`open in new window	`possible_keys`	The possible indexes to choose
`key`open in new window	`key`	The index actually chosen
`key_len`open in new window	`key_length`	The length of the chosen key
`ref`open in new window	`ref`	The columns compared to the index
`rows`open in new window	`rows`	Estimate of rows to be examined
`filtered`open in new window	`filtered`	Percentage of rows filtered by table condition
`Extra`open in new window	None	Additional information

id：SELECT 标识符，如果此行是 UNION 的结果，则为 NULL。当 id 相同时，执行顺序由上向下；当 id 不同时，值越大，优先级越高，越先执行。
select_type：SELECT 类型，有如下类型：

`select_type` Value	JSON Name	Meaning
`SIMPLE`	None	Simple `SELECT`open in new window (not using `UNION`open in new window or subqueries)
`PRIMARY`	None	Outermost `SELECT`open in new window
`UNION`open in new window	None	Second or later `SELECT`open in new window statement in a `UNION`open in new window
`DEPENDENT UNION`	`dependent` (`true`)	Second or later `SELECT`open in new window statement in a `UNION`open in new window, dependent on outer query
`UNION RESULT`	`union_result`	Result of a `UNION`open in new window.
`SUBQUERY`open in new window	None	First `SELECT`open in new window in subquery
`DEPENDENT SUBQUERY`	`dependent` (`true`)	First `SELECT`open in new window in subquery, dependent on outer query
`DERIVED`	None	Derived table
`DEPENDENT DERIVED`	`dependent` (`true`)	Derived table dependent on another table
`MATERIALIZED`	`materialized_from_subquery`	Materialized subquery
`UNCACHEABLE SUBQUERY`	`cacheable` (`false`)	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query
`UNCACHEABLE UNION`	`cacheable` (`false`)	The second or later select in a `UNION`open in new window that belongs to an uncacheable subquery (see `UNCACHEABLE SUBQUERY`)

DEPENDENT 一般表示关联子查询。

table：表名，也可以为以下值：
- <unionM,N>：本行引用了 id 为 M 和 N 的行的 UNION 结果。
- <derivedN>：本行引用了 id 为 N 的行所产生的的派生表结果。派生表有可能产生自 FROM 语句中的子查询。
- <subqueryN>：本行引用了 id 为 N 的行所产生的的物化子查询结果。
partitions：查询匹配的分区。对于非分区表，该值为 NULL 。
type：访问类型。参考：EXPLAIN Join Typesopen in new window
possible_keys：可能用到的索引。如果为 NULL ，表示没有可能用到的索引。
key：实际使用的索引。如果为 NULL，表示未用到索引。
key_len：实际使用的索引的长度；用于确定在使用多列索引时实际用了哪些列。如果 key 列为 NULL ，则 key_len 列也为 NULL 。计算方法如下：
- 对于 VARCHAR(N) DEFAULT NULL：N * (CHARACTER SET：UTF8=3,GBK=2,LATIN1=1)+1(NULL)+2(变长字段)
- 对于 VARCHAR(N) NOT NULL：N * (CHARACTER SET：UTF8=3,GBK=2,LATIN1=1)+2(变长字段)
- 对于 CHAR(N) DEFAULT NULL：N * (CHARACTER SET：UTF8=3,GBK=2,LATIN1=1)+1(NULL)
- 对于 CHAR(N) NOT NULL：N * (CHARACTER SET：UTF8=3,GBK=2,LATIN1=1)
ref：与 key 列中索引进行比较的字段或常量。如果为 func，在 EXPLAIN 后使用 SHOW WARNINGS 查看函数。
rows：行数。对于 InnoDB，为估计值。
filtered：按条件进行过滤的表行的估计百分比，使用 rows × filtered 获取用于后续连接的行数。
Extra：额外的信息。参考：EXPLAIN Extra Informationopen in new window

EXPLAIN Join Types

EXPLAIN 的 type 字段表示访问类型，从性能最佳到最差为：

system：表只有一行（也就是 System Table），是 const 类型的特例。
const：当使用 PRIMARY KEY 或 UNIQUE 索引的所有字段进行等值比较时，由于最多只有一行匹配，故将此行中列值视为常量。在查询开始时读取，只读取这一次。
eq_ref：当使用 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：使用非唯一索引进行等值比较（ = 或 <=> ）时，使用此类型。例如：

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;

fulltext：使用 FULLTEXT 索引。
ref_or_null：类似于 ref ，但包含了对 NULL 的处理。例如：

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

index_merge：表示使用了索引合并（Index Mergeopen in new window）优化。此时 key 为所用到的索引列表，key_len 为所用索引的长度列表。
unique_subquery：将 eq_ref 替换为 IN 子查询：

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 。当使用 = ，<> ，> ，>= ，< ，<= ，IS NULL ，<=> ，BETWEEN ，LIKE 或 IN() 运算符与常量进行比较时，使用 range：

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：扫描索引，用于仅使用属于单个索引的列进行查询，有两种方式：
- 如果为覆盖索引，则仅扫描索引就可以获取所需数据， Extra 列为 Using index。
- 使用从索引读取来执行全表扫描，以按索引顺序查找数据行。 Uses index 不显示在 Extra 列中。
ALL：全表扫描。

EXPLAIN Extra Information

EXPLAIN 输出的 Extra 列包含有关 MySQL 如何解析查询的额外信息。可能出现的值有：

Backward index scan：优化器能够在 InnoDB 表上使用降序索引。
const row not found ：对于类似 SELECT ... FROM tbl_name 查询，表为空。
Distinct：查找不同值，对于一个值有多行的情况，找到第一个就停止。
FirstMatch(tbl_name)：FirstMatchopen in new window 是半连接子查询（Semi-join Subqueries）的一种执行策略。对外部表 tbl_name 的行，只要能在子查询表中找到 1 条匹配即可。
Full scan on NULL key：优化器无法使用索引。
Impossible HAVING：HAVING 子句总是为 False，不会选择任何行。
Impossible WHERE ：WHERE 子句总是为 False，不会选择任何行。
Impossible WHERE noticed after reading const tables：MySQL 已经读取了所有 const（或 system）表，WHERE 子句始终为 False。
LooseScan(m..n)：LooseScanopen in new window 是半连接子查询（Semi-join Subqueries）的一种执行策略。先使用子查询表的索引从重复记录中选择一条记录，再与外部表进行关联。
No matching min/max row：对于 SELECT MIN(...) FROM ... WHERE condition，没有行满足条件。
no matching row in const table：对于关联查询，其中一个表为空表，或者没有行满足唯一索引条件。
No matching rows after partition pruning：对于 DELETE 和 UPDATE 语句，在分区裁剪后，优化器找不到可以删除或更新的行，类似于 Impossible WHERE 。
No tables used：查询没有 FROM 语句，或者是 FROM DUAL 语句。对于 INSERT 和 REPLACE 语句，如果不包含 SELECT 语句，则也会显示此值。
Not exists：对 LEFT JOIN 的优化。
Plan isn't ready yet：当使用 EXPLAIN FOR CONNECTION 时，如果优化器还没有为语句创建完成执行计划，则会显示此值。
Range checked for each record (index map: N)：在没有找到合适索引的情况下，MySQL 检查是否可以使用 range （Range Optimizationopen in new window）或 index_merge （Index Mergeopen in new window）访问方法来检索行。通常在字段类型或者排序规则不一致时会出现此值。其中 N 为位掩码值，表示哪些索引为候选索引，例如值 0x19 （二进制 11001）表示考虑索引 1、4 和 5，对应 SHOW INDEX FROM tbl_name 显示的索引顺序。
Recursive：表示递归查询。
Scanned N databases：在查询 INFORMATION_SCHEMA 的表时，会扫描多少个目录，N 可以是 0，1 或者 all。
Skip_open_table， Open_frm_only，Open_full_table：在查询 INFORMATION_SCHEMA 的表时，采取的方式：
- Skip_open_table：无需打开表文件，信息已从数据字典获取。
- Open_frm_only：仅需要读取数据字典以获取表信息。
- Open_full_table：表信息必须从数据字典以及表文件中读取。
Start temporary，End temporary：当半连接子查询（Semi-join Subqueries）使用 DuplicateWeedoutopen in new window 策略时，会使用到临时表。
unique row not found：对于 SELECT ... FROM tbl_name ，没有行能够满足唯一索引或主键查询的条件。
Using filesort：当需要排序，而且无法利用索引完成排序时，优化器不得不选择相应的排序算法来实现。数据较少时在内存排序，否则在磁盘排序。
Using index：如果为覆盖索引，则仅扫描索引就可以获取所需数据。
Using index condition：使用索引条件下推（Index Condition Pushdownopen in new window）进行优化。
Using index for group-by：与 Using index 类似，通过索引就可以获取 GROUP BY 或 DISTINCT 查询的结果。
Using index for skip scan：跳过多列索引的前面字段进行扫描（Skip Scan Range Accessopen in new window）。
Using join buffer (Block Nested Loop)，Using join buffer (Batched Key Access)，Using join buffer (hash join)：将表部分数据读入连接缓冲区（Join Buffer），然后从缓冲区使用其行执行与当前表的联接。 (Block Nested Loop) 表示使用 Block Nested-Loop 算法（从 MySQL 8.0.20 开始不再支持该算法），(Batched Key Access) 表示使用 Batched Key Access 算法，(hash join) 表示使用 Hash Joinopen in new window 算法。
Using MRR：使用 Multi-Range Readopen in new window 优化策略。
Using sort_union(...)，Using union(...)，Using intersect(...) ：对于 index_merge 访问方法，表示使用了哪种索引合并（Index Mergeopen in new window）算法。
Using temporary：表示使用到了临时表。
Using where：表示使用 WHERE 条件过滤了数据。
Zero limit：查询有 LIMIT 0 子句，不会选择任何行。

EXPLAIN Output Interpretation

为避免性能问题，用于表连接的字段需要：

类型一致。
长度一致。
字符集和排序规则一致。

Extended EXPLAIN Output Format

EXPLAIN 可产生额外的扩展信息，可通过在 EXPLAIN 语句后紧跟一条 SHOW WARNINGS 语句查看扩展信息。

SHOW WARNINGS 输出中的 Message 字段值显示优化器如何在 SELECT 语句中限定表名和列名，查询重写和优化后的 SELECT 语句形式，以及可能有关优化过程的其他注释。

示例如下：

mysql> EXPLAIN
       SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: t2
         type: index
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a` AS `a`,
         <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in
         ( <materialize> (/* select#2 */ select `test`.`t2`.`a`
         from `test`.`t2` where 1 having 1 ),
         <primary_index_lookup>(`test`.`t1`.`a` in
         <temporary table> on <auto_key>
         where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `t1.a
         IN (SELECT t2.a FROM t2)` from `test`.`t1`
1 row in set (0.00 sec)

Obtaining Execution Plan Information for a Named Connection

使用下面语句获取某个连接正在执行的 SQL 的执行计划：

EXPLAIN [options] FOR CONNECTION connection_id;

特别适合某个会话的 SQL 需要很长时间才能执行完成时，从其他会话使用 EXPLAIN FOR CONNECTION 查看该 SQL 的性能问题。

可以通过 SHOW PROCESSLIST 命令和查询 INFORMATION_SCHEMA.PROCESSLIST 表获取 connection_id。

Optimizing SQL Statements

应用程序都是通过 SQL 语句与数据库进行交互，因此 SQL 语句的性能决定了应用和数据库的性能。

Optimizing SELECT Statements

优化查询需要注意：

要提高 SELECT ... WHERE 语句性能，首先确认是否可以在 WHERE 子句的字段上添加合适的索引。可以使用 EXPLAIN 语句来确定使用了哪些索引。
避免对查询中的每一行数据调用函数进行处理。
尽量减少查询中全表扫描的次数。
定期使用 ANALYZE TABLE 语句更新统计信息，以便优化器具有构造高效执行计划所需的信息。
合理配置存储引擎相关参数以获取最佳性能。
使用 EXPLAIN 获取执行计划，定位 SQL 的性能瓶颈。

WHERE Clause Optimization

对于 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)

常量（Constant）等价改写：

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

如果没有使用 GROUP BY 或聚合函数，则 HAVING 与 WHERE 合并。
对于表连接，应使用简单 WHERE 子句快速过滤掉尽可能多的数据。
会先读取常量表（Constant Table），常量表为：
- 空表或只有一行的表。
- WHERE 子句的字段上有 PRIMARY KEY 或 UNIQUE 索引的表。

以下的表都是常量表：

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，则会使用内存临时表。
根据表大小，行数，I/O 块大小等因素决定是否使用索引。
使用覆盖索引获取结果而无需回表。
在输出每一行之前，将跳过与 HAVING 子句不匹配的行。

Range Optimization

range 访问方法使用索引获取范围结果集。

Range Access Method for Single-Part Indexes

单列索引的范围条件（Range Condition）说明如下：

对于 B-Tree 和 HASH 索引，当索引列和常量值（Constant Value）之间使用 =，<=>，IN()，IS NULL 或者 IS NOT NULL 运算符时，即为范围条件。
另外，对于 B-Tree 索引，当索引列和常量值之间使用 >，<，>=，<=，BETWEEN，!= 或者 <> 运算符时，也为范围条件。
对于所有索引类型，使用 OR 或者 AND 组合多个范围条件也是范围条件。

上述的常量值为：

字符串
const 或者 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')

使用 TRUE 替换不能用于范围扫描（Range Scan）的 nonkey = 4 和 key1 LIKE '%b' ：

(key1 < 'abc' AND (key1 LIKE 'abcde%' OR TRUE)) OR
(key1 < 'bar' AND TRUE) OR
(key1 < 'uux' AND key1 > 'z')

进行逻辑合并，例如：

(key1 LIKE 'abcde%' OR TRUE) 始终为 TRUE
(key1 < 'uux' AND key1 > 'z') 始终为 FALSE

合并后：

(key1 < 'abc' AND TRUE) OR (key1 < 'bar' AND TRUE) OR (FALSE)

然后删除不必要的 TRUE 和 FALSE 常量：

(key1 < 'abc') OR (key1 < 'bar')

合并重叠的范围，最终的范围扫描的条件为：

(key1 < 'bar')

Range Access Method for Multiple-Part Indexes

多列索引的范围条件是单列索引的范围条件的扩展。

例如，多列索引的定义为 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)

这个范围覆盖了上面的 3-6 行索引数据，可用于范围扫描。

相比之下，条件 key_part3 = 'abc' 无法定位连续的范围，不能用于范围扫描。

多列索引的范围条件（Range Condition）说明如下：

对于 HASH 索引，当每一个索引列和常量值（Constant Value）之间使用 =，<=> 或者 IS NULL 比较运算符时，即为多列索引的范围条件。例如，以下 3 列 HASH 索引的范围条件：

key_part1 = 1 AND key_part2 IS NULL AND key_part3 = 'foo'

对于 B-Tree 索引，当索引列和常量值（Constant Value）之间使用 =，<=>，IS NULL，>，<，>=，<=，!=， <>，BETWEEN 或者 LIKE 'pattern'（'pattern' 不以通配符开头）运算符作为 WHERE 子句的一个条件时，使用 AND 将每个索引列的条件组合起来，只要可以确定索引记录与条件匹配，即为多列索引的范围条件。如果第一个索引列为 =，<=> 或者 IS NULL 运算符，则优化器还会使用第二个索引来确定范围，如果第二个索引列也为 =，<=> 或者 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 运算符合并，则使用交集合并为一个范围条件。例如：

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

Equality Range Optimization of Many-Valued Comparisons

考虑以下表达式，其中 col_name 为索引字段：

col_name IN(val1, ..., valN)
col_name = val1 OR ... OR col_name = valN

对于这种字段与多个值进行相等比较的条件，优化器估计成本的方式如下：

如果 col_name 上的索引是唯一索引，则每个值的行估计值为 1。
如果 col_name 上的索引是非唯一索引，优化器会使用索引下钻（Index Dives）或者索引统计信息（Index Statistics）来评估每个值对应的行数。

使用索引下钻，优化器更准确的估算出每个值（或者范围）对应的行数。但随着表达式中比较值数量的增加，优化器需要花费更长的时间来估算行数，此时可以使用不那么准确的索引统计信息来估算行数。

使用参数 eq_range_index_dive_limit 指定一个值以便让优化器选择估计行的策略，如果值为 N，则等值比较的数量少于 N 个才会使用索引下钻，否则就使用索引统计信息。如果值为 0，则始终使用索引下钻。默认为 200。

[(none)]> SHOW VARIABLES LIKE 'eq_range_index_dive_limit';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| eq_range_index_dive_limit | 200   |
+---------------------------+-------+
1 row in set (0.10 sec)

在 MySQL 8.0 之前，只能通过配置参数 eq_range_index_dive_limit 来避免使用索引下钻，在 MySQL 8.0 中，在以下条件可能不会使用索引下钻：

单表查询。
对单个索引使用 FORCE INDEX Hint。
索引是非唯一索引，且不是全文索引。
没有子查询。
没有 DISTINCT 、 GROUP BY 或 ORDER BY 子句。

对于 EXPLAIN FOR CONNECTION，如果没有使用索引下钻，则输出有如下改变：

对于传统输出， rows 和 filtered 的值为 NULL。
对于 JSON 输出，不会出现 rows_examined_per_scan 和 rows_produced_per_join， skip_index_dive_due_to_force 为 true，且成本估算不准确。

如果不使用 FOR CONNECTION，EXPLAIN 输出不做改变。

在执行了没有使用索引下钻的查询后，在表 INFORMATION_SCHEMA.OPTIMIZER_TRACE 的相应行包含 skipped_due_to_force_index ：index_dives_for_range_access。

Skip Scan Range Access Method

考虑如下情况：

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

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

在 MySQL 8.0.13 之前，虽然索引范围扫描比索引全扫描更高效，但由于这儿没有针对索引第一个字段 f1 的条件，故无法使用索引范围扫描。从 MySQL 8.0.13 开始，优化器可以使用称为跳跃扫描（Skip Scan）的方式进行多范围扫描，即对于索引的第一个字段（f1）每个值，对索引剩余字段（f2）进行范围扫描，找到满足条件 f2 > 40 的记录。

具体的操作步骤如下：

获取索引第一个字段的第一个不同值（f1 = 1）。
基于第一个字段的第一个值和第二个字段构造范围（f1 = 1 AND f2 > 40）。
执行范围扫描。
获取索引第一个字段的第二个不同值（f1 = 2）。
基于第一个字段的第二个值和第二个字段构造范围（f1 = 2 AND f2 > 40）。
执行范围扫描。

可以看到，使用跳跃扫描（Skip Scan）减少了访问的行数，其适用于以下环境：

表必须有一个多列索引。
查询语句只有 1 个表。
查询语句没有 GROUP BY 或者 DISTINCT。
查询语句只涉及该索引的字段。
第一个字段的谓词条件必须是与常量进行相等比较，包括 IN() 操作符。

使用跳跃扫描（Skip Scan）时，EXPLAIN 输出如下：

Extra 字段显示 Using index for skip scan。
possible_keys 字段显示使用的索引。

使用跳跃扫描（Skip Scan）时，Optimizer Trace 输出如下：

"skip_scan_range": {
  "type": "skip_scan",
  "index": index_used_for_skip_scan,
  "key_parts_used_for_access": [key_parts_used_for_access],
  "range": [range]
}

Range Optimization of Row Constructor Expressions

优化器可以对以下查询执行范围扫描：

SELECT ... FROM t1 WHERE ( col_1, col_2 ) IN (( 'a', 'b' ), ( 'c', 'd' ));

查询需要满足以下条件，优化器才能使用范围扫描：

可以使用 IN()，不能使用 NOT IN()。
在 IN() 左侧，只能是列。
在 IN() 右侧，只能是常量。

Limiting Memory Use for Range Optimization

使用参数 range_optimizer_max_mem_size 指定优化范围访问所使用内存的最大值。

设置为 0 表示不做限制。
如果设置为大于 0，优化器在考虑使用范围访问时会跟踪内存使用情况，如果超过了设定值，则会放弃范围访问改为其他访问方式，比如全表扫描，此时会有如下警告：

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 默认值为 8 MB，根据实际情况增大参数值以提高性能。

[(none)]> SHOW VARIABLES LIKE 'range_optimizer_max_mem_size';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| range_optimizer_max_mem_size | 8388608 |
+------------------------------+---------+
1 row in set (0.01 sec)

使用以下方法估算处理范围访问所需内存：

对于以下使用 OR 简单查询，每个谓词使用 230 bytes 内存。

SELECT COUNT(*) FROM t
WHERE a=1 OR a=2 OR a=3 OR .. . a=N;

对于以下使用 AND 简单查询，每个谓词使用 125 bytes 内存。

SELECT COUNT(*) FROM t
WHERE a=1 AND b=1 AND c=1 ... N;

对于使用 IN() 简单查询，等价于使用多个 OR。如果有 2 个 IN()，则使用 OR 组合的谓词数量为 M × N。

SELECT COUNT(*) FROM t
WHERE a IN (1,2, ..., M) AND b IN (1,2, ..., N);

Index Merge Optimization

索引合并（Index Merge）访问方法从单个表的多个索引范围扫描中获取数据进行合并。

例如：

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

注意：
索引合并优化算法有以下限制：
如果 WHERE 子句有多层 AND / OR 嵌套，并且 MySQL 未选择最佳执行计划，尝试进行如下改写：
(x AND y) OR z => (x OR z) AND (y OR z)
(x OR y) AND z => (x AND z) OR (y AND z)
索引合并不适用于全文索引。

在 EXPLAIN 输出中，索引合并方法在 type 字段显示为 index_merge，key 为所用到的索引列表，key_len 为所用索引的长度列表。

在 EXPLAIN 的 Extra 字段显示索引合并访问方法使用的算法：

Using intersect(...)
Using union(...)
Using sort_union(...)

Index Merge Intersection Access Algorithm

索引合并交集访问算法适用于在 WHERE 语句中使用 AND 合并多个不同索引上的范围条件。范围条件可以是：

包含多列索引的所有列：

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 表主键上的条件，则它不用于行检索，而是用于筛选使用其他条件获取的行。

Index Merge Union Access Algorithm

索引合并并集访问算法适用于在 WHERE 语句中使用 OR 合并多个不同索引上的范围条件。范围条件可以是：

包含多列索引的所有列：

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;

Index Merge Sort-Union Access Algorithm

与索引合并并集访问算法不同，索引合并排序并集访问算法在返回任何行之前，必给先获取所有记录的行 ID 并排序。

例如：

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;

Influencing Index Merge Optimization

索引合并的使用受制于 optimizer_switch 参数的 index_merge ，index_merge_intersection ，index_merge_union 和 index_merge_sort_union 标志的值。默认情况下，所有这些标志均为 on 。要仅启用某些算法，将 index_merge 设置为 off ，将允许的算法设置为 on。参考：Switchable Optimizations

除了在会话级别使用 optimizer_switch 参数来控制优化器使用索引合并算法之外，还可以在语句级别使用优化器提示来控制。

Hash Join Optimization

从 MySQL 8.0.18 开始，对于等值连接条件且连接字段都没有索引时，可以使用哈希连接，替代了块嵌套循环连接（Block Nested-Loop Join），例如：

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);

SELECT *
    FROM t1
    JOIN t2
        ON t1.c1=t2.c1;

使用 EXPLAIN 查看使用了哈希连接：

mysql> EXPLAIN
    -> SELECT * FROM t1
    ->     JOIN t2 ON t1.c1=t2.c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using join buffer (hash join)

在 MySQL 8.0.20 之前，对于多个表的连接，每对表至少需要一个等值连接条件才能使用哈希连接。

当有一个或多个索引可用于单表谓词条件时，也可以使用哈希联接。

任何不是等值连接的额外条件在执行连接后都会用于过滤。

mysql> EXPLAIN FORMAT=TREE
    -> SELECT *
    ->     FROM t1
    ->     JOIN t2
    ->         ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
    ->     JOIN t3
    ->         ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: 
-> Inner hash join (t3.c1 = t1.c1)  (cost=1.05 rows=1)
    -> Table scan on t3  (cost=0.35 rows=1)
    -> Hash
        -> Filter: (t1.c2 < t2.c2)  (cost=0.70 rows=1)
            -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
                -> Table scan on t2  (cost=0.35 rows=1)
                -> Hash
                    -> Table scan on t1  (cost=0.35 rows=1)

从 MySQL 8.0.20 开始，对于多个表的连接，即使没有等值连接条件也可使用哈希连接。

例如：

Inner non-equi-join

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 JOIN t2 ON t1.c1 < t2.c1\G
*************************** 1. row ***************************
EXPLAIN: 
-> Filter: (t1.c1 < t2.c1)  (cost=4.70 rows=12)
    -> Inner hash join (no condition)  (cost=4.70 rows=12)
        -> Table scan on t2  (cost=0.08 rows=6)
        -> Hash
            -> Table scan on t1  (cost=0.85 rows=6)

Semijoin

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 
    ->     WHERE t1.c1 IN (SELECT t2.c2 FROM t2)\G
*************************** 1. row ***************************
EXPLAIN: 
-> Hash semijoin (t2.c2 = t1.c1)  (cost=0.70 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t2  (cost=0.35 rows=1)

Antijoin

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t2 
    ->     WHERE NOT EXISTS (SELECT * FROM t1 WHERE t1.c1 = t2.c1)\G
*************************** 1. row ***************************
EXPLAIN: 
-> Hash antijoin (t1.c1 = t2.c1)  (cost=0.70 rows=1)
    -> Table scan on t2  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t1  (cost=0.35 rows=1)

1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1276
Message: Field or reference 't3.t2.c1' of SELECT #2 was resolved in SELECT #1

Left outer join

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1\G
*************************** 1. row ***************************
EXPLAIN: 
-> Left hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
    -> Table scan on t1  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t2  (cost=0.35 rows=1)

Right outer join（MySQL 重写所有右外连接为左外连接）

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 RIGHT JOIN t2 ON t1.c1 = t2.c1\G
*************************** 1. row ***************************
EXPLAIN: 
-> Left hash join (t1.c1 = t2.c1)  (cost=0.70 rows=1)
    -> Table scan on t2  (cost=0.35 rows=1)
    -> Hash
        -> Table scan on t1  (cost=0.35 rows=1)

从 MySQL 8.0.18 开始，可以使用 BNL 和 NO_BNL 优化器 Hint 控制优化器是否使用哈希连接。

使用参数 join_buffer_size 指定哈希连接的内存最大值，超过该值后，将使用磁盘文件来处理。如果文件数量超过 open_files_limit 设置值，则连接失败。为避免此问题，可以：

增大 join_buffer_size 的值。
增大 open_files_limit 的值。

Index Condition Pushdown Optimization

在 MySQL 使用索引从表中检索记录时，可以使用索引条件下推（Index Condition Pushdown ICP）进行优化。如果不使用 ICP，存储引擎会遍历索引以查找记录，将记录返回到 MySQL Server 层并使用 WHERE 条件对其进行判断。

index-access-2phases

启用 ICP 后，如果部分 WHERE 条件仅通过使用索引字段就可以进行判断，MySQL 服务器会将这部分 WHERE 条件向下推送到存储引擎。然后，存储引擎通过使用索引条目来评估下推的索引条件，只有在满足该条件时，才从表中读取记录。ICP 可以减少存储引擎必须访问表的次数和 MySQL Server 层必须访问存储引擎的次数。

index-access-with-icp

索引条件下推优化的使用条件和限制如下：

当需要访问表的所有记录时，ICP 可用于 range，ref，eq_ref 和 ref_or_null 访问方法。
ICP 可用于 InnoDB 和 MyISAM 表，包括 InnoDB 和 MyISAM 分区表。
对于 InnoDB 表，ICP 只能用于二级索引，减少记录读取次数，从而减少 I/O 操作。
ICP 不支持虚拟列上的二级索引。
有子查询的条件不能下推。
有存储函数的条件不能下推。
触发的条件不能下推。
不能将条件下推到包含对系统变量的引用的派生表。

在不使用索引条件下推时索引扫描以如下方式进行：

获取下一行，首先读取索引数据，然后使用索引数据来定位和读取表中的整行数据（回表）。
然后根据 WHERE 条件判断接受或拒绝该行数据。

在使用索引条件下推时索引扫描以如下方式进行：

获取下一行的索引数据。
对 WHERE 条件中可以使用此索引数据进行判断的条件进行判断。如果不满足条件，则处理下一行索引数据。
如果满足条件，则使用索引数据来定位和读取表中的整行数据。
判断 WHERE 条件的其他部分，然后根据结果接受或拒绝该行数据。

在使用索引条件下推时 EXPLAIN 的 Extra 列显示 Using index condition 。

假设某个表包含人员及其地址的信息，并且该表的索引定义为 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';

Nested-Loop Join Algorithm

嵌套循环连接（NLJ）算法从外部驱动表中依次读取一行数据，将其传递到内部被驱动表中进行比较和关联。

假定使用以下访问类型访问 t1 、 t2 和 t3 表：

Table   Join Type
t1      range
t2      ref
t3      ALL

使用嵌套循环连接算法进行连接时，处理方式如下：

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

Nested Join Optimization

例如有如下表：

表 t1 的字段 a 有值 1，2 两行。
表 t2 的字段 a，b 有值 1，101 一行。
表 t3 的字段 b 有值 101 一行。

[db1]> create table t1(a int primary key);
Query OK, 0 rows affected (0.02 sec)

[db1]> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)

[db1]> insert into t1 values(2);
Query OK, 1 row affected (0.01 sec)

[db1]> create table t2(a int primary key,b int);
Query OK, 0 rows affected (0.02 sec)

[db1]> insert into t2 values(1,101);
Query OK, 1 row affected (0.00 sec)

[db1]> create table t3(b int primary key);
Query OK, 0 rows affected (0.02 sec)

[db1]> insert into t3 values(101);
Query OK, 1 row affected (0.01 sec)

[db1]> select * from t1;
+---+
| a |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

[db1]> select * from t2;
+---+------+
| a | b    |
+---+------+
| 1 |  101 |
+---+------+
1 row in set (0.00 sec)

[db1]> select * from t3;
+-----+
| b   |
+-----+
| 101 |
+-----+
1 row in set (0.00 sec)

通常，仅在使用内连接的关联查询中可以忽略对关联表上的括号。

[db1]> SELECT *
       FROM t1
            JOIN
            (t2 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 |
+---+---+------+-----+
1 row in set (0.00 sec)

[db1]> EXPLAIN FORMAT=TREE 
       SELECT *
       FROM t1
            JOIN
            (t2 JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
            ON t1.a=t2.a\G
*************************** 1. row ***************************
EXPLAIN: 
-> Nested loop inner join  (cost=1.05 rows=1)
    -> Filter: ((t3.b = t2.b) or (t2.b is null))  (cost=0.70 rows=1)
        -> Inner hash join (no condition)  (cost=0.70 rows=1)
            -> Index scan on t3 using PRIMARY  (cost=0.35 rows=1)
            -> Hash
                -> Table scan on t2  (cost=0.35 rows=1)
    -> Single-row covering index lookup on t1 using PRIMARY (a=t2.a)  (cost=0.35 rows=1)

1 row in set (0.00 sec)


[db1]> SELECT *
       FROM (t1 JOIN t2 ON t1.a=t2.a)
            JOIN t3
            ON t2.b=t3.b OR t2.b IS NULL;
+---+---+------+-----+
| a | a | b    | b   |
+---+---+------+-----+
| 1 | 1 |  101 | 101 |
+---+---+------+-----+
1 row in set (0.00 sec)

[db1]> EXPLAIN FORMAT=TREE  
       SELECT *
       FROM (t1 JOIN t2 ON t1.a=t2.a)
            JOIN t3
            ON t2.b=t3.b OR t2.b IS NULL\G
*************************** 1. row ***************************
EXPLAIN: 
-> Nested loop inner join  (cost=1.05 rows=1)
    -> Filter: ((t3.b = t2.b) or (t2.b is null))  (cost=0.70 rows=1)
        -> Inner hash join (no condition)  (cost=0.70 rows=1)
            -> Index scan on t3 using PRIMARY  (cost=0.35 rows=1)
            -> Hash
                -> Table scan on t2  (cost=0.35 rows=1)
    -> Single-row covering index lookup on t1 using PRIMARY (a=t2.a)  (cost=0.35 rows=1)

1 row in set (0.00 sec)

但对于外连接，则不能忽略括号。

以下两个外连接，如果忽略括号，则结果和执行计划是不同的。

[db1]> 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 |
+---+------+------+------+
2 rows in set (0.00 sec)

[db1]> EXPLAIN FORMAT=TREE
       SELECT *
       FROM t1
            LEFT JOIN
            (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
            ON t1.a=t2.a\G
*************************** 1. row ***************************
EXPLAIN: 
-> Left hash join (t2.a = t1.a)  (cost=0.98 rows=2)
    -> Index scan on t1 using PRIMARY  (cost=0.45 rows=2)
    -> Hash
        -> Left hash join (no condition), extra conditions: ((t3.b = t2.b) or (t2.b is null))  (cost=0.53 rows=1)
            -> Table scan on t2  (cost=0.18 rows=1)
            -> Hash
                -> Index scan on t3 using PRIMARY  (cost=0.18 rows=1)

1 row in set (0.00 sec)


[db1]> 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 |
+---+------+------+------+
2 rows in set (0.00 sec)


[db1]> EXPLAIN FORMAT=TREE
       SELECT *
       FROM (t1 LEFT JOIN t2 ON t1.a=t2.a)
            LEFT JOIN t3
            ON t2.b=t3.b OR t2.b IS NULL\G
*************************** 1. row ***************************
EXPLAIN: 
-> Left hash join (no condition), extra conditions: ((t3.b = t2.b) or (t2.b is null))  (cost=0.63 rows=2)
    -> Left hash join (t2.a = t1.a)  (cost=0.63 rows=2)
        -> Index scan on t1 using PRIMARY  (cost=0.45 rows=2)
        -> Hash
            -> Table scan on t2  (cost=0.18 rows=1)
    -> Hash
        -> Index scan on t3 using PRIMARY  (cost=0.18 rows=1)

1 row in set (0.00 sec)

从前面的示例可以得出：

对于只有内连接的查询，括号不影响计算结果。
对于存在外连接的查询，括号会影响计算结果。

对于嵌套循环连接算法，假设 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) 和 P2(T3,T3) 是连接条件，P(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 表示通过连接表 t1 ，t2 和 t3 行的列而构造的行。

如果外连接的表列没有数据，则为 NULL，例如 t1||t2||NULL 表示通过连接表 t1 ，t2 和 t3 行的列而构造的行，此时 t3 表对应的列没有数据。

例如对于以下形式的外连接：

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

其中，对每一层循环使用一个标志来判断是否有匹配的数据。

对于内连接，优化器可以选择不同的嵌套循环顺序：

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

而对于外连接，始终是外部表的循环先于内部表的循环，只有这一个嵌套循环顺序。例如对于以下查询：

SELECT * T1 LEFT JOIN (T2,T3) ON P1(T1,T2) AND P2(T1,T3)
  WHERE P(T1,T2,T3)

此处 T1 与 (T2,T3) 进行左外连接。 T1 为外部表，用于外部连接，必须在外部循环中处理。(T2,T3) 为内部表，用于内连接，必须在内部循环中处理。

由于 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 条件。假设 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;
      }
    }
  }
}

将每一个条件推送到可以进行判断的最外层循环，尽可能减少传递到内层循环的行数，缩短执行时间。

对于外连接，只有在外部表的当前行在内部表有匹配行后，才判断 WHERE 条件，因此需要将匹配标志与 WHERE 条件一起使用来进行判断：

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

Outer Join Optimization

外连接包括左连接（LEFT JOIN）和右连接（RIGHT JOIN）。

MySQL 中 A LEFT JOIN B 的连接规范如下：

表 B 设置为依赖于表 A 及表 A 所依赖的所有表。
表 A 设置为依赖于在 LEFT JOIN 条件中使用的所有表（表 B 除外）。
LEFT JOIN 条件用于决定如何从表 B 中检索行。
执行所有标准 JOIN 优化，根据依赖顺序确定表的连接顺序，如果存在循环依赖，则抛出错误。
执行所有标准 WHERE 优化。
如果表 A 中有一行与 WHERE 子句匹配，但表 B 中没有在 ON 条件上与表 A 这一行匹配的行，则表 B 会生成一个所有列都为 NULL 的行与表 A 这一行匹配。
如果使用 LEFT JOIN 查找某个表中不存在的行，例如在 WHERE 子句有 col_name IS NULL，其中 col_name 为 NOT NULL 字段，则 MySQL 在找到一行数据与 LEFT JOIN 条件匹配后停止检索。

LEFT JOIN 反过来就是 RIGHT JOIN，故 RIGHT JOIN 会被转换为等效的 LEFT JOIN。

优化器将根据连接条件和 WHERE 条件判断是否可以将外连接转换为内连接，以便使用不同的连接顺序，来获取性能更好的执行计划。

Outer Join Simplification

在解析阶段，会将右连接转换为左连接。具体如下：

右连接：

(T1, ...) RIGHT JOIN (T2, ...) ON P(T1, ..., T2, ...)

转换为左连接：

(T2, ...) LEFT JOIN (T1, ...) ON P(T1, ..., T2, ...)

以下形式的内连接：

T1 INNER JOIN T2 ON P(T1,T2)

会转换为：

T1,T2 WHERE P(T1,T2)

外连接的执行计划固定为：先访问外部表，再访问内部表。例如以下查询：

SELECT * FROM T1
  LEFT JOIN T2 ON P1(T1,T2)
  WHERE P(T1,T2) AND R(T2)

其中，表 T2 中与 R(T2) 条件匹配的行很少。按照这种外连接写法的方式执行，优化器只能先访问结果集较大的表 T1，再访问结果集较小的表 T2，这可能会导致非常低效的执行计划。

此时如果 WHERE 子句有内部表的某个字段不能为 NULL 的条件（称之为 null-rejected），则可以将外连接转换为内连接。

例如对于以下外连接：

T1 LEFT JOIN T2 ON T1.A=T2.A

加上以下 null-rejected 条件，即可转换为内连接：

T2.B IS NOT NULL
T2.B > 3
T2.C <= T1.C
T2.B < 2 OR T2.C > 1

加上以下非 null-rejected 条件，则不能转换为内连接：

T2.B IS NULL
T1.B < 3 OR T2.B IS NOT NULL
T1.B < 3 OR T2.B > 3

对于 3 个表及以上的外连接，则需要注意 WHERE 子句的 null-rejected 条件对应的是哪一个表。

例如以下查询中 WHERE 子句的 null-rejected 条件对应的是 T3 表。

SELECT * FROM T1 LEFT JOIN T2 ON T2.A=T1.A
                 LEFT JOIN T3 ON T3.B=T1.B
  WHERE T3.C > 0

就可以将对 T3 表的外连接转换为内连接：

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 是 null-rejected 的，故可以将外连接转换为内连接：

SELECT * FROM (T1 INNER JOIN T2 ON T2.A=T1.A), T3
  WHERE T3.C > 0 AND T3.B=T2.B

Multi-Range Read Optimization

当表很大，不能放到存储引擎的缓存中，使用 range 扫描二级索引后，回表获取数据会导致大量随机磁盘访问。

no-mrr-access-pattern

通过使用 MRR，对从二级索引中获取的数据按照行 ID 进行排序，然后按照行 ID 顺序回表获取数据，将对磁盘的随机 I/O 尽量转换为顺序 I/O，提高了读取性能。

mrr-access-pattern

使用 MRR 时，在 EXPLAIN 的 Extra 字段显示 Using MRR。

使用参数 optimizer_switch 的 mrr 标志控制是否启用 MRR。如果 mrr 标志设置为 on，当 mrr_cost_based 标志设置为 on 时，优化器尝试基于成本决定是否使用 MRR，当 mrr_cost_based 标志设置为 off 时，尽可能使用 MRR。

对于 MRR，存储引擎使用参数 read_rnd_buffer_size 的值作为其可以使用的内存最大值。

Condition Filtering

在 MySQL 中，处理表连接时，将外部表的行传递给内部表进行处理，外部表这些行称为前缀行（Prefix Rows）。条件过滤（Condition Filtering）让优化器尽可能使用 WHERE 子句中的条件过滤外部表，使前缀行的数量尽可能小。

在 EXPLAIN 输出中， rows 字段表示所选访问方法的行估计值， filtered 字段表示条件过滤的效果，以百分比表示，如果为 100，表示未对行进行过滤。使用 rows × filtered 获取前缀行数。

假设有如下查询：

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 = 'John' 条件。
有 150 行满足 employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01' 条件。
有 1 行同时满足以上两个条件。

不使用条件过滤，执行计划如下：

+----+------------+--------+------------------+---------+---------+------+----------+
| 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 表，通过访问 first_name 字段上的索引 name （key 字段）选取与名称 'John' 匹配的 8 行（rows 字段），filtered 字段为 100 表示未进行过滤。因此前缀行数为 rows × filtered = 8 × 100% = 8。

使用条件过滤，优化器还会考虑访问方法未考虑的 WHERE 子句中的条件，执行计划如下：

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

其中，filtered 字段为 16.31 表示优化器使用启发式方法评估 employee.hire_date 的 BETWEEN 条件的过滤效果为 16.31%。现在前缀行数为 rows × filtered = 8 × 16.31% = 1.3。

使用参数 optimizer_switch 的 condition_fanout_filter 标志控制优化器是否考虑额外的过滤条件，默认启用。

如果优化器高估了条件过滤的效果，则性能可能比不使用条件过滤时差。此时可以：

为字段增加索引。
为字段增加直方图。
更改连接顺序。
禁用会话的条件筛选：

SET optimizer_switch = 'condition_fanout_filter=off';

禁用语句的条件筛选：

SELECT /*+ SET_VAR(optimizer_switch = 'condition_fanout_filter=off') */ ...

ORDER BY Optimization

本节介绍当有 ORDER BY 语句时，是使用索引进行排序，还是使用 filesort 操作进行排序。

Use of Indexes to Satisfy ORDER BY

假设有索引创建在字段 (key_part1, key_part2) 上：

根据成本决定以下语句是否使用索引进行排序：

SELECT * FROM t1
  ORDER BY 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;

排序时字段的方向与创建索引时字段的方向要么都一致，要么都相反才能使用索引：

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

以下查询中 key_part1 与常量进行对比，根据成本决定是否使用索引：

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

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

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;

以下 ORDER BY 无法使用索引：

排序的字段在不同的索引上面：

SELECT * FROM t1 ORDER BY key1, key2;

排序的字段在索引上不连续：

SELECT * FROM t1 WHERE key2=constant ORDER BY key1_part1, key1_part3;

WHERE 子句使用的索引与 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 和 GROUP BY 不一致。
ORDER BY 子句的字段位于前缀索引上。
非排序索引，例如 MEMORY 表中的 HASH 索引。

Use of filesort to Satisfy ORDER BY

如果 ORDER BY 子句无法使用索引进行排序，则需要执行 filesort 操作读取表行进行排序。

对于 filesort 操作，从 MySQL 8.0.12 开始，优化器根据需要为其分配内存，最大为参数 sort_buffer_size 指定的值。如果内存不够，则需要使用临时磁盘文件。

Influencing ORDER BY Optimization

当无法使用索引进行排序时，为提高排序性能，可以：

增大参数 sort_buffer_size。

增大参数 read_rnd_buffer_size。

指定参数 tmpdir 为有足够空间的物理磁盘位置。

ORDER BY Execution Plan Information Available

使用 EXPLAIN 查看 ORDER BY 子句是否使用索引：

如果 Extra 字段没有 Using filesort，则使用了索引，无需执行 filesort 操作。
如果 Extra 字段有 Using filesort，则没有使用索引，需要执行 filesort 操作。

另外，如果执行了 filesort 操作，Optimizer Trace 的输出包含 filesort_summary：

"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "peak_memory_used": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

其中：

peak_memory_used：为排序过程中使用的最大内存值。

GROUP BY Optimization

对于 GROUP BY 子句，如果不能使用索引，则将扫描整个表并创建临时表来处理分组。如果 GROUP BY 子句的所有字段都属于一个索引，则就有可能使用索引来提高性能。

有以下两种索引访问方式可以用于执行 GROUP BY 查询。

Loose Index Scan

使用松散索引扫描（Loose Index Scan）读取的索引记录数量与分组数量一样多，无需扫描整个索引，但需要满足以下条件：

只能用于单表查询。
GROUP BY 子句上的字段只能是索引的最左前缀字段。例如对于创建在 (c1,c2,c3) 字段上的索引，当查询有 GROUP BY c1, c2 子句时，适用于松散索引扫描，当查询有 GROUP BY c2, c3 或者 GROUP BY c1, c2, c4 时，则不适用。
对于 DISTINCT 后面的字段，也必须是索引的最左前缀字段。
只支持 MIN() 和 MAX() 组函数，参数必须是索引中的相同字段，且需为 GROUP BY 子句字段的后一个字段。
除了 MIN() 和 MAX() 参数外，索引中不在 GROUP BY 子句中的字段，如果使用了，则必须是常量。
不支持使用字段前缀创建的索引。

如果使用了松散索引扫描，则 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 子句及 MIN() 和 MAX() 中的字段，不为常量（c3）：

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

如果以上查询包含 WHERE c3 = const ，则可以使用松散索引扫描。

松散索引扫描访问方法还可以用于以下形式的组函数：

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;

Tight Index Scan

根据查询条件，紧密索引扫描（Tight Index Scan）可以是完全索引扫描，也可以是范围索引扫描。读取由 WHERE 子句指定的每个范围条件内的所有索引数据，如果没有范围条件，则扫描整个索引。

要使 GROUP BY 子句使用紧密索引扫描，需要不在 GROUP BY 子句的字段为常量。

假设表 t1(c1,c2,c3,c4) 上有一个索引 idx(c1,c2,c3) ，以下查询适用于紧密索引扫描访问方法。

GROUP BY 子句中的字段不是连续的索引字段，但是缺少的字段有常量条件。

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 Optimization

在大多数情况下，DISTINCT 与 ORDER BY 联用需要用到临时表。

在大多数情况下，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。具体参考：GROUP BY Optimizationopen in new window

LIMIT Query Optimization

可以使用 LIMIT 子句获取指定数量的记录。

对于有 LIMIT 子句且没有 HAVING 子句的查询，MySQL 进行如下优化：

如果使用 LIMIT 获取少量记录，MySQL 更倾向于使用全表扫描。
如果 SQL 语句中有 LIMIT row_count 子句和 ORDER BY 子句，只要找到排序结果中的前 row_count 行，就会停止排序，从而无需排序整个结果集。如果使用了索引，那么查询会很快。如果无法使用索引，将会排序大部分记录。
如果 SQL 语句中有 LIMIT row_count 子句和 DISTINCT，只要找到 row_count 行不同记录，就会停止查询，
基于 LIMIT 0 快速返回空结果集的特性，可以用于检查查询语句的有效性。
对于使用临时表的查询语句，MySQL 会使用 LIMIT row_count 来计算所需空间。

如果在 ORDER BY 列中相同的行值，则对于其他非排序列，其顺序是不确定的。

对于有 ORDER BY 子句的查询，是否有 LIMIT 子句会影响结果集的排序，考虑如下查询，以 category 字段排序：

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 后，可以看到 id 和 rating 字段的顺序发生了改变：

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 子句的影响，可以在 ORDER BY 子句中增加额外的排序字段：

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 BY 或 GROUP BY 子句的查询，如果有 LIMIT 子句，优化器默认会选择排好序的索引以加快查询速度。从 MySQL 8.0.21 开始，可以使用 optimizer_switch 参数的 prefer_ordering_index 标志来关闭此默认行为。

例如有以下表和参数配置：

# 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]

mysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+

对于以下包含 LIMIT 子句的查询，会使用主键：

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：

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

Function Call Optimization

MySQL 函数可分为以下两类：

结果确定函数：多次调用结果确定，比如 UPPER()，LOWER()。
结果不确定函数：多次调用结果会不同，比如 RAND()，UUID()。

结果不确定函数可能会影响性能，例如有如下表：

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

对于第 1 个查询，由于函数 POW(1,2) 结果确定，作为一个常量与主键进行相等比较，可以使用主键索引。
对于第 2 个查询，由于函数 RAND() 结果不确定，对于每一行都会有一个值，故查询需要读取表的所有行。

结果不确定函数同样会影响 UPDATE 语句：

UPDATE t SET col_a = some_expr WHERE id = FLOOR(1 + RAND() * 49);

根据 id 字段值和 RAND() 值，可能会更新 0 行，1 行或者多行。

综上，结果不确定函数对性能和复制的影响如下：

因为结果不确定函数无法产生固定结果，故可能会导致全表扫描。
会导致锁住更大范围记录。
在更新语句中使用结果不确定函数，对于复制来说是不安全的。

对于使用了结果不确定函数的语句，可以进行如下优化：

将包含结果不确定函数的表达式移到单独的语句中，将计算结果保存到一个变量中。在原始语句中，使用变量替换之前的表达式，这样优化器就可以将其视为常量值：

SET @keyval = FLOOR(1 + RAND() * 49);
UPDATE t SET col_a = some_expr WHERE id = @keyval;

将结果不确定函数的值赋予给派生表的变量，这会使得该变量在 WHERE 子句使用前只会被赋值一次。

UPDATE /*+ NO_MERGE(dt) */ t, (SELECT FLOOR(1 + RAND() * 49) AS r) AS dt
SET col_a = some_expr WHERE id = dt.r;

与确定的条件一起使用，减少结果不确定函数需要扫描的行数。

SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();

Avoiding Full Table Scans

当使用全表扫描时，EXPLAIN 输出的 type 字段显示 ALL。通常发生在以下情况：

表很小，通常少于 10 行。
在 ON 或 WHERE 子句没有索引字段。
使用全表扫描成本更低。

对于大表，可以通过以下方式避免全表扫描：

使用 ANALYZE TABLE tbl_name 更新表统计信息。
使用 FORCE INDEX Hint。

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

设置 SET max_seeks_for_key=1000。

Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions

MySQL 查询优化器针对不同的子查询（Subqueries）使用不同的优化策略：

对于使用 IN，= ANY 或者 EXISTS 谓词连接子查询，优化器可以：
- Semijoin
- Materialization
- EXISTS strategy
对于使用 NOT IN，<> ALL 或者 NOT EXISTS 谓词连接子查询，优化器可以：
- Materialization
- EXISTS strategy

对于派生表（Derived Tables），视图引用（View References）和通用表表达式（Common Table Expressions），优化器可以：

将派生表合并到外部查询块
将派生表物化为内部临时表

注意：
使用子查询修改单个表的 UPDATE 和 DELETE 语句时，优化器不会使用 Semijoin 或 Materialization 优化。解决方法是尝试将其重写为使用多表连接的 UPDATE 和 DELETE 语句。

Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations

对于评估外部查询的字段值是否存在于子查询中，可以使用半连接（Semijoin）或者反连接（Antijoin）来进行优化，但子查询需要满足以下条件：

子查询前面的谓词为 IN，= ANY 或者 EXISTS。从 MySQL 8.0.17 开始，可以为 NOT IN，<> ALL 或者 NOT EXISTS。
不能有 UNION。
不能有 HAVING 子句。
不能包含任何组函数。
不能有 LIMIT 子句。
在外部查询中不能使用 STRAIGHT_JOIN。

对于半连接和反连接，可以采用的优化策略有：

可以使用 optimizer_switch 参数的标志来启用或者禁用半连接及其优化策略（默认都是启用的）：

使用 semijoin 标志控制是否使用半连接，从 MySQL 8.0.17 开始，也适用于反连接。
如果启用 semijoin，使用 duplicateweedout，firstmatch，loosescan 或 materialization 标志启用或禁用对应的半连接策略。
如果禁用了 duplicateweedout，只有在其他策略都被禁用时才会使用。
如果禁用了 duplicateweedout，可以通过设置 optimizer_prune_level=0 避免优化器生成性能过差的执行计划。

使用半连接的 EXPLAIN 输出如下：

在 SHOW WARNINGS 的输出中查看重写的语句。
如果使用了 DuplicateWeedout 优化策略，则 Extra 字段显示 Start temporary 和 End temporary，表示使用了临时表。
如果使用了 FirstMatch 优化策略，则 Extra 字段显示 FirstMatch(tbl_name)。
如果使用了 LooseScan 优化策略，则 Extra 字段显示 LooseScan(m..n)。
如果使用了 Materialize 优化策略，则 select_type 字段为 MATERIALIZED， table 字段为 <subqueryN> 的行表示用于物化的临时表。

从 MySQL 8.0.21 开始，对于没有使用 ORDER BY 或者 LIMIT，使用了 [NOT] IN 或者 [NOT] EXISTS 子查询谓词的单表 UPDATE 或者 DELETE 语句，可以使用半连接转换。

DuplicateWeedout Strategy

DuplicateWeedout 是半连接子查询的一种优化策略，将半连接当作普通的内连接运行，然后使用临时表消除重复记录。

假设执行如下 SQL：

select * 
from Country 
where 
   Country.code IN (select City.Country
                    from City 
                    where 
                      City.Population > 0.33 * Country.Population and 
                      City.Population > 1*1000*1000);

首先对表 City 和 Country 运行普通的内连接：

duplicate-weedout-inner-join

可以看到内连接产生了重复的记录，使用 DuplicateWeedout：

duplicate-weedout-diagram

可以看到，使用带主键的临时表避免了重复的记录。

可以在 EXPLAIN 输出中看到上面的 Start temporary 和 End temporary：

explain select * from Country where Country.code IN 
  (select City.Country from City where City.Population > 0.33 * Country.Population 
   and City.Population > 1*1000*1000)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: City
         type: range
possible_keys: Population,Country
          key: Population
      key_len: 4
          ref: NULL
         rows: 238
        Extra: Using index condition; Start temporary
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: Country
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 3
          ref: world.City.Country
         rows: 1
        Extra: Using where; End temporary
2 rows in set (0.00 sec)

FirstMatch Strategy

FirstMatch 是半连接子查询的一种优化策略。

假设执行如下 SQL：

select * from Country 
where Country.code IN (select City.Country 
                       from City 
                       where City.Population > 1*1000*1000)
      and Country.continent='Europe'

假设执行计划是先找到欧洲的国家，然后对每个找到的国家，检查是否有人口超过 100 万的大城市。常规的内连接执行如下所示：

firstmatch-inner-join

因为德国有 2 个大城市，可以看到出现了两条相同的记录。此时使用 FirstMatch 策略在找到第一个真正的匹配记录后，通过停止检索子查询中的表（Short-Cutting）来避免产生重复结果：

firstmatch-firstmatch

注意，只有在 Using where 后才进行 Short-Cutting 操作。

以上查询的 EXPLAIN 输出如下：

MariaDB [world]> explain select * from Country where Country.code IN 
  (select City.Country from City where City.Population > 1*1000*1000)
    and Country.continent='Europe';
+----+-------------+---------+------+--------------------+-----------+---------+--------------------+------+----------------------------------+
| id | select_type | table   | type | possible_keys      | key       | key_len | ref                | rows | Extra                            |
+----+-------------+---------+------+--------------------+-----------+---------+--------------------+------+----------------------------------+
|  1 | PRIMARY     | Country | ref  | PRIMARY,continent  | continent | 17      | const              |   60 | Using index condition            |
|  1 | PRIMARY     | City    | ref  | Population,Country | Country   | 3       | world.Country.Code |   18 | Using where; FirstMatch(Country) |
+----+-------------+---------+------+--------------------+-----------+---------+--------------------+------+----------------------------------+
2 rows in set (0.00 sec)

其中 Extra 列中的 FirstMatch(Country) 表示，一旦生成了一个匹配的记录组合，就可以 Short-Cutting 并跳转回 Country 表。

LooseScan Strategy

LooseScan 是半连接子查询的一种优化策略。

假设执行如下 SQL 查找有卫星的国家：

select * from Country  
where 
  Country.code in (select country_code from Satellite)

在 Satellite.country_code 字段上有索引，如果使用该索引，将按卫星所属国家的顺序得到结果：

loosescan-satellites-ordered-r2

LooseScan 策略无需排序，需要的是分组。在上图中，卫星是根据国家分组的，便于从一组中找出一个卫星，将其与对应的国家进行连接，获得一个没有重复记录的国家列表：

loosescan-diagram-no-where

以上查询的 EXPLAIN 输出如下：

MariaDB [world]> explain select * from Country where Country.code in 
  (select country_code from Satellite);
+----+-------------+-----------+--------+---------------+--------------+---------+------------------------------+------+-------------------------------------+
| id | select_type | table     | type   | possible_keys | key          | key_len | ref                          | rows | Extra                               |
+----+-------------+-----------+--------+---------------+--------------+---------+------------------------------+------+-------------------------------------+
|  1 | PRIMARY     | Satellite | index  | country_code  | country_code | 9       | NULL                         |  932 | Using where; Using index; LooseScan |
|  1 | PRIMARY     | Country   | eq_ref | PRIMARY       | PRIMARY      | 3       | world.Satellite.country_code |    1 | Using index condition               |
+----+-------------+-----------+--------+---------------+--------------+---------+------------------------------+------+-------------------------------------+

LooseScan 先通过索引检索子查询从多条重复记录中选择一条记录，因此适用于 LooseScan 的子查询形式为：

expr IN (SELECT tbl.keypart1 FROM tbl ...)

或者：

expr IN (SELECT tbl.keypart2 FROM tbl WHERE tbl.keypart1=const AND ...)

Semi-join Materialization Strategy

Semi-join Materialization 是半连接子查询的一种优化策略。

考虑如下 SQL，查询欧洲有大城市的国家（城市人口超过 7 百万）：

select * from Country 
where Country.code IN (select City.Country 
                       from City 
                       where City.Population > 7*1000*1000)
      and Country.continent='Europe'

Semi-join Materialization 使用子查询的结果集创建临时表，然后再与外部查询的结果集进行连接。

sj-materialization1

根据物化临时表的连接顺序，可以分为：

Materialization-Scan：先全表扫描物化表，再关联外部查询的结果集。
Materialization-Lookup：对物化临时表执行索引查找。

Materialization-Scan

如果查找人口超过 7 百万的城市，优化器会使用 Materialization-Scan：

MariaDB [world]> explain select * from Country where Country.code IN 
  (select City.Country from City where  City.Population > 7*1000*1000);
+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+
| id | select_type  | table       | type   | possible_keys      | key        | key_len | ref                | rows | Extra                 |
+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+
|  1 | PRIMARY      | <subquery2> | ALL    | distinct_key       | NULL       | NULL    | NULL               |   15 |                       |
|  1 | PRIMARY      | Country     | eq_ref | PRIMARY            | PRIMARY    | 3       | world.City.Country |    1 |                       |
|  2 | MATERIALIZED | City        | range  | Population,Country | Population | 4       | NULL               |   15 | Using index condition |
+----+--------------+-------------+--------+--------------------+------------+---------+--------------------+------+-----------------------+
3 rows in set (0.01 sec)

其中：

id=2，select_type=MATERIALIZED，表示根据子查询的结果集创建带唯一键的临时表。
id=1，table=<subquery2>，表示从 id=2 获取的物化临时表。type=ALL 表示对物化临时表进行全表扫描。

Materialization-Lookup

如果查找人口超过 1 百万的城市，优化器会使用 Materialization-Lookup：

MariaDB [world]> explain select * from Country where Country.code IN 
  (select City.Country from City where  City.Population > 1*1000*1000) ;
+----+--------------+-------------+--------+--------------------+--------------+---------+------+------+-----------------------+
| id | select_type  | table       | type   | possible_keys      | key          | key_len | ref  | rows | Extra                 |
+----+--------------+-------------+--------+--------------------+--------------+---------+------+------+-----------------------+
|  1 | PRIMARY      | Country     | ALL    | PRIMARY            | NULL         | NULL    | NULL |  239 |                       |
|  1 | PRIMARY      | <subquery2> | eq_ref | distinct_key       | distinct_key | 3       | func |    1 |                       |
|  2 | MATERIALIZED | City        | range  | Population,Country | Population   | 4       | NULL |  238 | Using index condition |
+----+--------------+-------------+--------+--------------------+--------------+---------+------+------+-----------------------+
3 rows in set (0.00 sec)

其中：

id=1，table=<subquery2> 的访问类型为 type=eq_ref，使用了名称为 distinct_key 的索引。

这意味着优化器计划对物化临时表执行索引查找，即使用 Materialization-Lookup 策略。

Optimizing Subqueries with Materialization

优化器可以使用物化（Materialization）来优化子查询。在第一次需要使用子查询的结果时，会将该结果物化到内存临时表中，后续只需要访问该临时表即可。还可以为该临时表创建 HASH 索引以提高查询速度。

如果不使用物化，则优化器有可能将以下子查询：

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

重写为性能较差的关联子查询：

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

可以使用 optimizer_switch 参数的 materialization 标志控制是否启用子查询物化（包括前面的半连接物化），默认为 on。

对于子查询物化有以下限制：

外部和内部表达式的类型必须匹配。
内部表达式的类型不能是 BLOB。

使用子查询物化时，EXPLAIN 输出如下：

select_type 为 SUBQUERY，而不是 DEPENDENT SUBQUERY。
SHOW WARNINGS 包含 materialize 和 materialized-subquery。

Optimizing Subqueries with the EXISTS Strategy

对于以下子查询：

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

执行顺序为从外到内，也就是先获取外部表达式 outer_expr 的值，然后执行子查询，需要重复执行多次子查询。

可以通过将相等条件 outer_expr=inner_expr 下推到子查询的 WHERE 子句中来进行优化：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

对于以下多个表达式与子查询进行比较的 SQL：

(oe_1, ..., oe_N) IN
  (SELECT ie_1, ..., ie_N FROM ... WHERE subquery_where)

也可以将多个相等条件下推到子查询中：

EXISTS (SELECT 1 FROM ... WHERE subquery_where
                          AND oe_1 = ie_1
                          AND ...
                          AND oe_N = ie_N)

下推策略需要满足：

outer_expr 和 inner_expr 不能为 NULL。
子查询中至少有一条记录满足条件 outer_expr=inner_expr 。

如果 outer_expr 为非 NULL，没有满足 outer_expr=inner_expr 的记录，则 outer_expr IN (SELECT ...) 结果如下：

如果子查询在 inner_expr 为 NULL 时返回任意行，则为 NULL。
如果子查询返回非空值或无返回值，则为 FALSE。

在这种情况下，当查找 outer_expr = inner_expr 没有结果时，还需要查找 inner_expr is NULL，因此，子查询会被转换为如下形式：

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND
        (outer_expr=inner_expr OR inner_expr IS NULL))

MySQL 使用 ref_or_null 访问方法处理额外的 IS NULL 条件：

mysql> EXPLAIN
       SELECT outer_expr IN (SELECT t2.maybe_null_key
                             FROM t2, t3 WHERE ...)
       FROM t1;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
...
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: ref_or_null
possible_keys: maybe_null_key
          key: maybe_null_key
      key_len: 5
          ref: func
         rows: 2
        Extra: Using where; Using index
...

如果 outer_expr 为 NULL，则无法将 outer_expr = inner_expr 下推到子查询，子查询会很慢。在实际工作中应避免这种情况。

综上，为让优化器更好的优化子查询，建议：

定义列为 NOT NULL，并指定默认值。

可以使用 optimizer_switch 参数的 subquery_materialization_cost_based 标志控制在子查询物化和 IN-to-EXISTS 子查询转换这两种优化方式间如何选择。默认为 on，表示基于成本进行选择。如果为 off，则优先选择子查询物化。

Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization

对于派生表（Derived Tables），视图引用（View References）和通用表表达式（Common Table Expressions），优化器可以：

将派生表合并到外部查询块
将派生表物化为内部临时表

例如对于以下查询：

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

合并派生表 derived_t1：

SELECT * FROM t1;

例如对于以下查询：

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

合并派生表 derived_t2：

SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;

优化器以相同的方式处理派生表，视图引用和通用表表达式：尽可能避免不必要的物化，从而可以将条件从外部查询向下推送到派生表，并生成更高效的执行计划。

如果满足以下条件，优化器可以将派生表或视图引用中的 ORDER BY 子句传播到外部查询块：

外部查询没有分组或聚合。
外部查询未指定 DISTINCT、HAVING 或 ORDER BY。
外部查询将此派生表或视图引用作为 FROM 子句中的唯一来源。

否则优化器将忽略派生表或视图引用中的 ORDER BY 子句。

决定优化器是否将派生表、视图引用和通用表表达式合并到外部查询块的因素有：

是否使用了 MERGE 或 NO_MERGE 优化器 Hint。
是否启用了 optimizer_switch 参数的 derived_merge 标志，默认启用。
在派生表、视图引用和通用表表达式中有以下子句将禁用合并：
- 组函数或窗口函数
- DISTINCT
- GROUP BY
- HAVING
- LIMIT
- UNION 或者 UNION ALL
- 在 SELECT 子句中有子查询
- 对用户变量赋值。

如果优化器不能将派生表合并到外部查询块，则只能将派生表物化为内部临时表，处理如下：

只有在需要派生表内容时才会将其物化，这样可以避免不必要的物化。
优化器可以给派生表添加索引以提高访问效率。

Derived Condition Pushdown Optimization

从 MySQL 8.0.22 开始，支持对符合条件的子查询进行派生条件下推。

类似于以下查询：

SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > constant;

可以把外部的 WHERE 条件下推到派生表中：

SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt;

当派生表不能被合并到外部查询时（例如派生表使用了聚合函数），把外部的 WHERE 条件下推到派生表中可以减少需要处理的行数，从而提升查询速度。

在以下情况，外部 WHERE 条件可以被下推到派生的物化表中：

派生表没有使用组函数或窗口函数，外部 WHERE 条件可以直接下推。

例如，对于查询：

SELECT * FROM (SELECT f1, f2 FROM t1) AS dt WHERE f1 < 3 AND f2 > 11;

可以重写为：

SELECT f1, f2 FROM (SELECT f1, f2 FROM t1 WHERE f1 < 3 AND f2 > 11) AS dt;

派生表使用了 GROUP BY 子句，没有使用窗口函数，外部 WHERE 条件引用的字段不在 GROUP BY 子句中，则可以将其下推到派生表的 HAVING 子句中。

例如，对于查询：

SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j) AS dt WHERE sum > 100;

可以重写为：

SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j HAVING sum > 100) AS dt;

派生表使用了 GROUP BY 子句，外部 WHERE 条件的字段是 GROUP BY 子句中的字段，则可以将其下推到派生表的 WHERE 子句中。

例如，对于查询：

SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10;

可以重写为：

SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i,j) AS dt;

综合以上两种情况，对于查询：

SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10 AND sum > 100;

可以重写为：

SELECT * FROM (
    SELECT i, j, SUM(k) AS sum FROM t1
        WHERE i > 10
        GROUP BY i, j
        HAVING sum > 100
    ) AS dt;

可以使用 optimizer_switch 参数的 derived_condition_pushdown 标志控制派生条件下推，默认启用。也可以使用 DERIVED_CONDITION_PUSHDOWN 优化器 Hint 为指定查询启用，使用 NO_DERIVED_CONDITION_PUSHDOWN 优化器 Hint 为指定查询禁用。

派生条件下推有以下限制：

在 MySQL 8.0.29 之前，派生表定义不能包含 UNION。从 MySQL 8.0.29 开始，支持包含 UNION 的派生表。例如：

CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  c1 INT, 
  KEY i1 (c1)
);

CREATE TABLE t2 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  c1 INT, 
  KEY i1 (c1)
);

CREATE OR REPLACE VIEW v AS
     SELECT id, c1 FROM t1
     UNION ALL
     SELECT id, c1 FROM t2;
     
mysql> EXPLAIN FORMAT=TREE SELECT * FROM v WHERE c1 = 12\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on v  (cost=1.26..2.52 rows=2)
    -> Union materialize  (cost=2.16..3.42 rows=2)
        -> Covering index lookup on t1 using i1 (c1=12)  (cost=0.35 rows=1)
        -> Covering index lookup on t2 using i1 (c1=12)  (cost=0.35 rows=1)

1 row in set (0.00 sec)

派生表定义不能使用 LIMIT 子句。
含有子查询的条件不能被下推。
如果派生表是外连接中的内部表，则不能使用此优化。
如果物化的派生表是一个通用表表达式，则被引用多次，那么条件不能下推至此派生表中。

Optimizing INFORMATION_SCHEMA Queries

通过查询 INFORMATION_SCHEMA 获取 MySQL 数据库的元数据。

为获得最佳性能，建议：

只查询基于数据字典表创建的视图。
只查询静态元数据。

可以查询基于数据字典表创建的如下视图，也就是从数据字典检索信息：

CHARACTER_SETS
CHECK_CONSTRAINTS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
EVENTS
FILES
INNODB_COLUMNS
INNODB_DATAFILES
INNODB_FIELDS
INNODB_FOREIGN
INNODB_FOREIGN_COLS
INNODB_INDEXES
INNODB_TABLES
INNODB_TABLESPACES
INNODB_TABLESPACES_BRIEF
INNODB_TABLESTATS
KEY_COLUMN_USAGE
PARAMETERS
PARTITIONS
REFERENTIAL_CONSTRAINTS
RESOURCE_GROUPS
ROUTINES
SCHEMATA
STATISTICS
TABLES
TABLE_CONSTRAINTS
TRIGGERS
VIEWS
VIEW_ROUTINE_USAGE
VIEW_TABLE_USAGE

以下表字段包含表的统计信息，表示动态元数据：

STATISTICS.CARDINALITY
TABLES.AUTO_INCREMENT
TABLES.AVG_ROW_LENGTH
TABLES.CHECKSUM
TABLES.CHECK_TIME
TABLES.CREATE_TIME
TABLES.DATA_FREE
TABLES.DATA_LENGTH
TABLES.INDEX_LENGTH
TABLES.MAX_DATA_LENGTH
TABLES.TABLE_ROWS
TABLES.UPDATE_TIME

默认情况下，当查询以上字段时，会从 mysql.index_stats 和 mysql.table_stats 数据字典表检索数据。相比从存储引擎直接检索统计数据，会更加高效。如果以上数据不存在或者过期，则只能从存储引擎检索最新的统计数据并写入到 mysql.index_stats 和 mysql.table_stats 数据字典表中。后续的查询就只需要访问 mysql.index_stats 和 mysql.table_stats 数据字典表即可，直到这些数据再次过期。重启服务器或者第一次访问 mysql.index_stats 和 mysql.table_stats 数据字典表不会自动更新其数据。

使用参数 information_schema_stats_expiry 指定以上统计数据过期时间，默认为 86400 秒（24 小时）。

可以使用 ANALYZE TABLE 语句更新表的统计信息。

在以下情况查询上面的字段，不会更新 mysql.index_stats 和 mysql.table_stats 数据字典表：

统计信息没有过期。
参数 information_schema_stats_expiry 设置为 0，表示始终从存储引擎检索最新统计信息。
MySQL 处于 read_only，super_read_only，transaction_read_only 或者 innodb_read_only 模式。
当查询同时获取 performance_schema 数据。

使用 EXPLAIN 和 SHOW WARNING 查看执行计划和实际的 SQL，例如对于语句：

mysql> SELECT COLLATION_NAME
       FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
       WHERE CHARACTER_SET_NAME = 'utf8mb4';
+----------------------------+
| COLLATION_NAME             |
+----------------------------+
| utf8mb4_general_ci         |
| utf8mb4_bin                |
| utf8mb4_unicode_ci         |
| utf8mb4_icelandic_ci       |
| utf8mb4_latvian_ci         |
| utf8mb4_romanian_ci        |
| utf8mb4_slovenian_ci       |
...

执行计划为：

mysql> EXPLAIN SELECT COLLATION_NAME
       FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
       WHERE CHARACTER_SET_NAME = 'utf8mb4'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cs
   partitions: NULL
         type: const
possible_keys: PRIMARY,name
          key: name
      key_len: 194
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: col
   partitions: NULL
         type: ref
possible_keys: character_set_id
          key: character_set_id
      key_len: 8
          ref: const
         rows: 68
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.01 sec)

实际的 SQL：

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `mysql`.`col`.`name` AS `COLLATION_NAME`
         from `mysql`.`character_sets` `cs`
         join `mysql`.`collations` `col`
         where ((`mysql`.`col`.`character_set_id` = '45')
         and ('utf8mb4' = 'utf8mb4'))

Optimizing Performance Schema Queries

通过查询 performance_schema 监控 MySQL 数据库运行情况。

建议基于索引检索 performance_schema 表，使用 SHOW INDEX 或者 SHOW CREATE TABLE 查看索引及表定义：

mysql> SHOW INDEX FROM performance_schema.accounts\G
*************************** 1. row ***************************
        Table: accounts
   Non_unique: 0
     Key_name: ACCOUNT
 Seq_in_index: 1
  Column_name: USER
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: HASH
      Comment:
Index_comment:
      Visible: YES
*************************** 2. row ***************************
        Table: accounts
   Non_unique: 0
     Key_name: ACCOUNT
 Seq_in_index: 2
  Column_name: HOST
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: HASH
      Comment:
Index_comment:
      Visible: YES

mysql> SHOW CREATE TABLE performance_schema.rwlock_instances\G
*************************** 1. row ***************************
       Table: rwlock_instances
Create Table: CREATE TABLE `rwlock_instances` (
  `NAME` varchar(128) NOT NULL,
  `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,
  `WRITE_LOCKED_BY_THREAD_ID` bigint(20) unsigned DEFAULT NULL,
  `READ_LOCKED_BY_COUNT` int(10) unsigned NOT NULL,
  PRIMARY KEY (`OBJECT_INSTANCE_BEGIN`),
  KEY `NAME` (`NAME`),
  KEY `WRITE_LOCKED_BY_THREAD_ID` (`WRITE_LOCKED_BY_THREAD_ID`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

使用 EXPLAIN 查看执行计划：

mysql> EXPLAIN SELECT * FROM performance_schema.accounts
       WHERE (USER,HOST) = ('root','localhost')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: accounts
   partitions: NULL
         type: const
possible_keys: ACCOUNT
          key: ACCOUNT
      key_len: 278
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL

在 performance_schema 中的索引是预定义的虚拟索引，不占用内存或磁盘，避免了频繁更新表所需的维护成本，不能删除、添加或更改。

类似于 HASH 索引：

无序。
只能使用 = 或者 <=> 操作符进行相等比较。

Optimizing Data Change Statements

本节介绍如何提高 INSERT，UPDATE，DELETE 语句性能。

Optimizing INSERT Statements

插入一行数据涉及到的操作及时间占比大概如下：

连接：（3）
发送语句到服务器：（2）
解析语句：（2）
插入行：（1 × 行大小）
插入索引：（1 × 索引数量）
关闭连接：（1）

可以使用以下方式进行优化：

如果一次插入多行，可以在 VALUES 子句指定多个行值。例如：

INSERT INTO tbl_name (a,b,c)
    VALUES(1,2,3), (4,5,6), (7,8,9);

使用 LOAD DATA 从文件加载数据，比用 INSERT 快 20 倍。
为字段指定默认值，可以减少解析，提升性能。

Optimizing UPDATE Statements

UPDATE 语句的优化类似于 SELECT，只是多了一个写入操作，写入的速度取决于要更新的数据量和要更新的索引的个数。

和 INSERT 类似，一次性更新多行可以提高性能。

Optimizing DELETE Statements

如果要删除表所有记录，使用 TRUNCATE TABLE tbl_name 要比 DELETE FROM tbl_name 快。

Optimizing Database Privileges

权限越复杂，对于 SQL 语句的开销就越大，简化权限可以减少客户端执行语句时的权限检查开销。例如，如果没有授予表级或列级的权限，则在执行语句时就无需检查 tables_priv 和 columns_priv 表；如果没有对用户账户设置资源限制，也就不需要执行资源计数。因此在高负载情况下，建议简化权限配置。

Other Optimization Tips

不要将文件或图片存储在数据库中，而是存储其路径。
将查询路由到备库，以降低主库的负载。
在备库进行备份，避免对主库的影响。

Optimization and Indexes

提高 SELECT 语句性能的最佳方式就是创建索引，但是索引也是有成本的，除了占用存储空间外，在增删改时还都需要维护索引，因此我们应该使用尽可能少的索引实现最佳的查询性能。

How MySQL Uses Indexes

索引用于快速查找具有特定列值的行。如果没有索引，MySQL 必须从表的第一行开始进行全表扫描，随着表越来越大，所需 I/O 和时间越来越多，成本就会越来越高。如果表有相关列的索引，则可以快速定位所需数据，即使表越来越大，所需 I/O 和时间也是相对固定的。

MySQL 大多数索引（PRIMARY KEY， UNIQUE，INDEX 和 FULLTEXT）为 B-Treeopen in new window 索引。除此之外，还有用于空间数据类型的 R-trees，用于内存表的 Hash 索引。

对于 B+Trees 索引，使用规范如下：

用于匹配 WHERE 子句，字段与比较的值的类型需一致，不能对字段进行运算或使用函数。
有多个索引时，使用不同值较多，可选择性好的索引。
对于多列索引，遵循最左匹配原则。
对于表连接字段，需要类型，大小与排序规则都一致才能使用索引，但 CHAR 与 VARCHAR 类型可以匹配。
对于某个字段的 MIN() 或 MAX()，如果该字段属于多列索引中的一个字段，则需要该索引中，这个字段之前的字段在 WHERE 子句中有常量匹配条件，此时才能使用索引。例如：

SELECT MIN(key_part2),MAX(key_part2)
  FROM tbl_name WHERE key_part1=10;

如果可以使用覆盖索引，则无需回表。

Primary Key Optimization

每个表都应该有一个主键，通常创建在自增字段，对应一个唯一索引，用于提高查询性能。

Column Indexes

单列索引是最常见的索引类型。

Index Prefixes

使用 col_name(N) 语法，以字段的前 N 个字符作为前缀，创建索引，以便缩小索引的大小。对于 BLOB 和 TEXT 字段，为其创建索引时必须指定前缀长度，例如：

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

对于使用 REDUNDANT 或者 COMPACT 行格式的 InnoDB 表，前缀最大为 767 字节；对于使用 DYNAMIC 或者 COMPRESSED 行格式的 InnoDB 表，前缀最大为 3072 字节。

如果搜索词超过索引前缀长度，则使用该索引排除不匹配的行，再检查剩余行是否匹配。

Indexes in the MEMORY Storage Engine

MEMORY 存储引擎默认使用 HASH 索引，但也支持 B-Tree 索引。

Multiple-Column Indexes

MySQL 可以为多个字段创建一个索引，即多列索引，最多 16 个字段。

假设有如下表定义：

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 的查询或者有 last_name 和 first_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';

不能用于以下查询：

SELECT * FROM test WHERE first_name='John';

SELECT * FROM test
  WHERE last_name='Jones' OR first_name='John';

假设有如下查询：

SELECT * FROM tbl_name
  WHERE col1=val1 AND col2=val2;

如果在 col1 和 col2 上面有创建多列索引，则会直接获取满足条件的记录。如果在 col1 和 col2 上面分别创建的是单列索引，则优化器会使用 Index Merge Optimizationopen in new window，或者使用不同值较多，可选择性好的索引。

多列索引遵循最左匹配原则。假如索引创建在 (col1, col2, col3) 上面，如果 WHERE 子句中有 (col1)，(col1, col2) 或者 (col1, col2, col3) 才能使用索引。例如以下 SQL：

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;

只有第 1，2 查询可以使用索引，第 3，4 查询不能使用索引。

Verifying Index Usage

使用 EXPLAIN 查看 SQL 是否使用了索引，参考：Optimizing Queries with EXPLAINopen in new window。

InnoDB Index Statistics Collection

存储引擎会为优化器收集表的统计信息。表统计信息基于值组，一个值组也就是具有相同键前缀值的一组行。值组中的行数越多，表明重复值越多，则使用索引的可能性就越小。

使用 SHOW INDEX 语句查看索引的 Cardinality，此即为值组的数量，计算公式为 N/S，其中 N 为表的行数，S 为值组中的行数。例如，对于唯一索引，一个值组中只有一行，则 S 为 1，那么 Cardinality 就等于表的行数。

使用全局参数 innodb_stats_method 控制 InnoDB 如何搜集表统计信息，可以是以下值：

nulls_equal：默认值，所有 NULL 值相等，当成一个值组。此时 Cardinality 变小，优化器可能不会使用 ref 访问。
nulls_unequal：所有 NULL 值不相等，每个 NULL 值形成大小为 1 的单独值组。此时 Cardinality 变大，优化器倾向于使用 ref 访问。
nulls_ignored：忽略 NULL 值。

Comparison of B-Tree and Hash Indexes

B-Tree Index Characteristics

B-Tree 索引适用于字段以 =，>，>=，<，<= 或者 BETWEEN 操作符进行比较。

对于 LIKE 操作符，常量参数不能以通配符开始才能使用索引。

例如，以下查询可以使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%';

以下查询不能使用索引：

SELECT * FROM tbl_name WHERE key_col LIKE '%Patrick%';
SELECT * FROM tbl_name WHERE key_col LIKE other_col;

如果使用 LIKE '%string%' 且 string 的长度不超过 3 个字符，则会使用 Turbo Boyer-Moore 算法。

如果 col_name 字段有索引，则 col_name IS NULL 会使用索引。

优化器会根据成本决定是否使用索引，但如果有 LIMIT 子句，会始终使用索引。

Hash Index Characteristics

HASH 索引：

只能用于 = 或者 <=> 操作符的相等比较。
优化器无法使用 HASH 索引加快 ORDER BY 操作。
无法确认两个值之间大概有多少行。
与 B-Tree 索引可以最左匹配不同，HASH 索引必须全部匹配。

Use of Index Extensions

对于每个二级索引，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) 上创建了主键。在字段 (d) 上创建了二级索引 k_d ，InnoDB 会扩展该索引到字段 (d, i1, i2) 上。

优化器会考虑扩展二级索引的主键字段，评估其是否会产生更高效的执行计划和更好的性能。

优化器可以将扩展二级索引用于 ref，range 和 index merge 访问方式，松散索引扫描（Loose Index Scan），连接和排序，以及 MIN()/MAX() 优化。

假如有以下表：

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 只有字段 (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 表明只使用了二级索引，但是后面的执行计划：

key_len 从 4 字节变为 8 字节，表明使用了 d 和 i1 字段。
ref 从 const 变为 const,const，表明使用了两个字段。
rows 从 5 变为 1，表明使用索引扩展后，只需要查找更少的行。
Extra 从 Using where; Using index 变为 Using index，表明只需要访问索引，无需回表即可获得结果。

使用参数 optimizer_switch 的 use_index_extensions 标志控制优化器是否使用索引扩展，默认启用。

同时需要注意，索引扩展会受到最大列数限制（16）和最大长度限制（3072 字节）。

Optimizer Use of Generated Column Indexes

MySQL 支持在 Generated Columns 上创建索引。例如：

CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));

其中 gc 字段为 Generated Column，定义为 f1 + 1，优化器在生成执行计划时会考虑其上的索引。例如以下查询：

SELECT * FROM t1 WHERE gc > 9;

即使在 WHERE，ORDER BY 或者 GROUP BY 子句中没有使用 gc 字段，但是如果有表达式与 gc 字段的定义匹配，则也会考虑使用 gc 字段上面的索引。例如以下查询：

SELECT * FROM t1 WHERE f1 + 1 > 9;

EXPLAIN 查看其执行计划如下：

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

从 SHOW WARNINGS 中可以看到，优化器对该 SQL 进行了改写，使用 gc 替换了表达式 f1 + 1：

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)

优化器使用 Generated Columns 上的索引有以下限制：

对于匹配的表达式，必须与 Generated Column 的定义一致，包括类型和顺序。假设定义为 f1 + 1，如果查询中的表达式为 1 + f1，则不匹配。
支持 =，<，<=，>，>=，BETWEEN 和 IN()。对于 BETWEEN 和 IN()，只有第一个参数可以替换为匹配的 Generated Column。
Generated Column 至少包含一个函数或者一个操作符，不能只是对其他列的简单引用。例如 gc INT AS (f1) STORED 只包含了对一个字段的引用，故不会考虑在 gc 上的索引。

Invisible Indexes

MySQL 支持不可见索引，优化器不会考虑使用不可见索引，适用于主键以外的索引。

可以在 CREATE TABLE，CREATE INDEX 和 ALTER TABLE 时使用 VISIBLE 或者 INVISIBLE 关键字指定索引是否可见，如果不指定，则默认可见。

CREATE TABLE t1 (
  i INT,
  j INT,
  k INT,
  INDEX i_idx (i) INVISIBLE
) ENGINE = InnoDB;
CREATE INDEX j_idx ON t1 (j) INVISIBLE;
ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

可以在 ALTER TABLE ... ALTER INDEX 语句中使用 VISIBLE 或者 INVISIBLE 关键字修改现有索引的可见性。

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

可以查询 INFORMATION_SCHEMA 下的 STATISTICS 表或者使用 SHOW INDEX 查看索引是否可见：

mysql> SELECT INDEX_NAME, IS_VISIBLE
       FROM INFORMATION_SCHEMA.STATISTICS
       WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| i_idx      | YES        |
| j_idx      | NO         |
| k_idx      | NO         |
+------------+------------+

通过将索引配置为可见或者不可见，来测试其对性能的影响。

如果将索引设置为不可见后，出现以下情形：

使用了该索引的 Hint 的 SQL 执行时会报错。
查询的负载增加。
EXPLAIN 的执行计划发生改变。
查询变慢。

使用参数 optimizer_switch 的 use_index_extensions 标志控制优化器是否使用不可见索引，默认为 off，表示优化器不使用不可见索引。

使用 SET_VAR 优化器 Hint 更新参数 optimizer_switch，可以为单次查询启用不可见索引。例如：

mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
     >     i, j FROM t1 WHERE j >= 50\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: j_idx
          key: j_idx
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

mysql> EXPLAIN SELECT i, j FROM t1 WHERE j >= 50\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 33.33
        Extra: Using where

索引的可见性不影响系统对其进行维护，即使索引不可见，对表进行更改时，也需要对索引进行更新。

Descending Indexes

MySQL 支持使用 DESC 关键字创建降序索引。

例如：

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

以上定义的索引可以用于以下的排序而无需 filesort 操作：

ORDER BY c1 ASC, c2 ASC    -- optimizer can use idx1
ORDER BY c1 DESC, c2 DESC  -- optimizer can use idx4
ORDER BY c1 ASC, c2 DESC   -- optimizer can use idx2
ORDER BY c1 DESC, c2 ASC   -- optimizer can use idx3

使用降序索引时需注意：

降序索引只支持 InnoDB 存储引擎。
降序索引只支持 B-Tree 索引。

在 EXPLAIN 的 Extra 字段查看优化器是否使用了降序索引：

mysql> CREATE TABLE t1 (
    -> a INT, 
    -> b INT, 
    -> INDEX a_desc_b_asc (a DESC, b ASC)
    -> );

mysql> EXPLAIN SELECT * FROM t1 ORDER BY a ASC\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: NULL
          key: a_desc_b_asc
      key_len: 10
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Backward index scan; Using index

也可以在 EXPLAIN FORMAT=TREE 输出中查看：

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 ORDER BY a ASC\G 
*************************** 1. row ***************************
EXPLAIN: -> Index scan on t1 using a_desc_b_asc (reverse)  (cost=0.35 rows=1)

Optimizing Database Structure

好的数据库结构可以提高系统的整体性能。

Optimizing Data Size

一般情况下，建议使用 InnoDB 存储引擎。

Table Columns

字段使用尽可能小的数据类型。
如果可以，定义字段为 NOT NULL 。

Row Format

建议使用默认的 DYNAMIC 行格式，参考： DYNAMIC Row Formatopen in new window。
如果需要压缩表以解压磁盘空间，在创建 InnoDB 表时指定 ROW_FORMAT=COMPRESSED，参考：InnoDB Table Compressionopen in new window。

Indexes

建议为每个 InnoDB 表指定自增主键，以节约磁盘空间和提高性能。
只创建必要的索引。尽量用多列索引替代多个单列索引，多列索引中的第一列应该是使用最多的字段。
对于较长的字符类型字段，只需要选择合适的前缀字符创建索引，以节约磁盘空间和提高性能。

Joins

将经常变化的数据和很少变化的数据分别存放在不同的表中。
表连接的字段类型，长度，字符集和排序规则应一致。
表连接的字段名称应尽可能简单且相同。

Optimizing MySQL Data Types

Optimizing for Numeric Data

对于既可以使用字符类型，也可以使用数字类型的字段，首选数字类型，以节约磁盘空间和提高性能。

Optimizing for Character and String Types

对于字符类型字段，建议：

比较不同字段时，字段的字符集和排序规则应一致，避免出现转换。
对于小于 8 KB 的字段，使用二进制 VARCHAR 替代 BLOB。如果表中没有 BLOB 字段，可以将 GROUP BY 和 ORDER BY 子句生成的临时表并使用 MEMORY 存储引擎。

Optimizing for BLOB Types

当 BLOB 字段与非常长的字符串进行相等比较时，可以存储 BLOB 字段的哈希值（使用 MD5() 或者 CRC32() 函数）到单独的列，并创建索引，比较此哈希值。因为哈希值有可能重复，可以在查询中加上 AND blob_column = long_string_value 以防止错误的匹配。

Optimizing for Many Tables

How MySQL Opens and Closes Tables

执行 mysqladmin status 命令输入如下：

Uptime: 426 Running threads: 1 Questions: 11082
Reloads: 1 Open tables: 12

其中，Open tables: 12 表示打开的表数量为 12，是多个会话打开的表数量之和。

最大打开表数量由以下参数确定：

open_files_limit：指定 mysqldopen in new window 可以使用的文件描述符数量。从 MySQL 8.0.19 开始，等于操作系统的限制。

[root@s1 ~]# ulimit -n
65535

[(none)]> show variables like 'open_files_limit';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| open_files_limit | 65535 |
+------------------+-------+
1 row in set (0.00 sec)

max_connection：指定最大允许并发客户端连接数量，默认值为 151，最大值为 open_files_limit - 810。

[(none)]> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 5000  |
+-----------------+-------+
1 row in set (0.01 sec)

table_open_cache：所有线程打开表的数量，默认值为 4000。

[(none)]> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| table_open_cache | 4000  |
+------------------+-------+
1 row in set (0.03 sec)

有效值为：

MAX(
    (open_files_limit - 10 - max_connections) / 2,
    400
   )

通过查看 Opened_tables 状态变量来检查是否需要增大该参数。

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741  |
+---------------+-------+

如果 Opened_tables 很大，且没有使用 FLUSH TABLES，则需要增大 table_open_cache 参数。

Internal Temporary Table Use in MySQL

在以下场景，MySQL 可能会创建内部临时表以便处理 SQL 语句：

包含 UNION。
处理某些视图。
派生表。
通用表表达式。
子查询或半连接物化。
包含 ORDER BY 和 GROUP BY 子句，且字段不同。
多表关联时，ORDER BY 或 GROUP BY 子句的字段不来自于第一个表。
包含 DISTINCT 和 ORDER BY 子句。
使用了 SQL_SMALL_RESULT 修饰。
语句为 INSERT ... SELECT。
多表 UPDATE 语句。
包含 GROUP_CONCAT() 或者 COUNT(DISTINCT)。
包含窗口函数。

查看 EXPLIAN 的 Extra 字段，如果有 Using temporary，表明使用了临时表。

根据存放位置不同，内部临时表可以分为：

内存内部临时表。
磁盘内部临时表。

内存内部临时表的存储引擎由参数 internal_tmp_mem_storage_engine 控制，可以是：

TempTable，默认值。
MEMORY。

相比 MEMORY 存储引擎，TempTable 存储引擎可以支持变长类型和二进制大对象，节约了空间，提升了性能。TempTable 存储引擎可以配置的参数有：

tmp_table_size：指定单个内存内部临时表的最大值，默认为 16 MB。如果内存内部临时表超过该参数值，则会自动转换为磁盘内部临时表。
temptable_max_ram：指定 TempTable 存储引擎可以使用的最大内存，默认为 1 GB。
temptable_use_mmap：在达到 temptable_max_ram 时，指定内部临时表是使用内存映射文件还是使用磁盘内部临时表，默认为 ON，表示使用内存映射文件，文件创建在参数 tmpdir 指定的目录下。此参数从 MySQL 8.0.16 引入，在 MySQL 8.0.26 废弃。
temptable_max_mmap：从 MySQL 8.0.23 引入，指定内存映射文件最大值，默认为 1 GB。设置为 0 将不会使用内存映射文件。

从 MySQL 8.0.16 开始，磁盘内部临时表的存储引擎只能是 InnoDb。

监控内部临时表可以使用：

状态变量：
- Created_tmp_tables：创建了内存或磁盘内部临时表，会增加该状态变量值。
- Created_tmp_disk_tables：创建了磁盘内部临时表，会增加该状态变量值。不会计算位于内存映射文件中的临时表。
Instruments：
- memory/temptable/physical_ram：表示分配的内存总量。
- memory/temptable/physical_disk：表示内存映射文件的大小。

Optimizing for InnoDB Tables

本节介绍如何优化 InnoDB 表。

Optimizing Storage Layout for InnoDB Tables

使用 OPTIMIZE TABLE 语句重组表，拷贝表数据并重建索引，节约空间，减少 I/O，提高性能。
为每个 InnoDB 表指定自增主键，以节约磁盘空间和提高性能。
对于包含长度不确定的字符串或者多个空值的字段，使用 VARCHAR 类型。
对于大表，或包含很多重复数据的表，考虑使用 COMPRESSED 行格式。

Optimizing InnoDB Transaction Management

使用 START TRANSACTION 和 COMMIT 语句手动开启和提交事务，避免大量小事务和单个大事务。
避免回滚大事务，其会导致性能更加恶化。可以使用 innodb_force_recovery=3 进行重启以避免回滚。
设置 innodb_change_buffering=all，以便缓存更新和删除操作，可加快回滚。
如果可以容忍丢失少量数据，可以设置参数 innodb_flush_log_at_trx_commit 为 0，每秒刷新一次日志。

Optimizing InnoDB Redo Logging

增大重做日志文件，从 MySQL 8.0.30 开始，使用参数 innodb_redo_log_capacity 指定重做日志文件大小。参考：Configuring Redo Log Capacity (MySQL 8.0.30 or Higher)open in new window。
增大日志缓冲区，使用参数 innodb_log_buffer_size 指定重做日志文件大小。参考：Log Bufferopen in new window。
保持参数 innodb_log_write_ahead_size 为默认值 8192，如果配置太小，则会导致 “read-on-write" 问题。
对于高并发场景，建议保持参数 innodb_log_writer_threads 默认启用。

Bulk Data Loading for InnoDB Tables

导入数据到 InnoDB 表前，关闭自动提交以避免为每一条 INSERT 语句执行刷日志到磁盘的操作。

SET autocommit=0;
... SQL import statements ...
COMMIT;

可以将 mysqldumpopen in new window 创建 dump 文件快速导入的 InnoDB 表中，无需使用 SET autocommit=0 和 COMMIT。
可以临时关闭二级索引上的 UNIQUE 约束，加快表的导入操作。

SET unique_checks=0;
... SQL import statements ...
SET unique_checks=1;

使用多行 INSERT 语法，减少客户端和服务器之间的通信开销。

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

当批量插入的表有自增字段时，保持参数 innodb_autoinc_lock_mode 为默认值 2。
按照 PRIMARY KEY 顺序执行批量插入会更快。
如果加载数据到新的 MySQL 实例，可以考虑使用 ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG 语句禁用重做日志以提高加载速度。参考：Disabling Redo Loggingopen in new window。
使用 MySQL Shell 的并行导入工具 util.importTable() 和 util.loadDump() 。

Optimizing InnoDB Queries

每个 InnoDB 表都要有主键。
主键尽可能短，建议使用自增字段作为主键。
尽量创建多列索引替换多个单列索引。
字段尽量定义为 NOT NULL。

Optimizing InnoDB DDL Operations

可以在加载完数据后在线添加二级索引以提高数据加载速度和查询速度。
使用 TRUNCATE TABLE 清空表，而不要使用 DELETE FROM tbl_name。如果有外键约束，会导致 TRUNCATE 语句像 DELETE 语句一样工作，此时可以使用 DROP TABLE 和 CREATE TABLE。
修改主键会导致重组表，故应该在 CREATE TABLE 时指定主键。

Optimizing InnoDB Disk I/O

增大缓冲池。设置参数 innodb_buffer_pool_size 为系统内存的 50% 到 75%。参考：InnoDB Buffer Pool Configurationopen in new window。
调整刷盘方式。InnoDB 默认使用 fsync，如果遇到写性能问题，调整参数 innodb_flush_method 为 O_DSYNC 并进行测试。
使用参数 innodb_fsync_threshold （默认为 0）指定刷盘阈值，避免一次性将大量数据从操作系统缓存刷到磁盘。
从 MySQL 8.0.26 开始，可以启用 innodb_use_fdatasync（默认为 OFF），在支持 fdatasync() 的平台上，使用 fdatasync() 替换 fsync()，减少对文件元数据的刷盘。
保持参数 innodb_use_native_aio 为默认启用，以便 InnoDB 使用 Linux 上的异步 I/O 子系统（原生 AIO）来执行数据文件页的预读和写入请求，此时推荐使用 noop 和 deadline I/O 调度类型进行测试，以获取最佳 I/O 性能。参考：Using Asynchronous I/O on Linuxopen in new window。
使用 SSD，处理随机 I/O 较多的文件，包括独立表空间open in new window（File-Per-Table）文件，通用表空间文件，UNDO 表空间open in new window文件，临时表空间open in new window文件。可以使用 HDD，处理顺序 I/O 文件，包括系统表空间open in new window文件，二进制日志open in new window文件和重做日志open in new window文件。对于 SSD，需要注意以下参数：
- innodb_checksum_algorithm：保持默认值 crc32。
- innodb_flush_neighbors：用于优化 HDD 的 I/O，对于 SSD，保持默认值为禁用。
- innodb_io_capacity：指定 InnoDB 可用的 I/O 总容量，默认为 200 IOPS，对于高端存储可以设置为 1000 IOPS。参考： Configuring InnoDB I/O Capacityopen in new window。
- innodb_io_capacity_max：指定 IOPS 的最大值，默认为 2000 IOPS，对于高端存储可以设置为 2500 IOPS。
- innodb_idle_flush_pct：从 MySQL 8.0.18 引入，指定空闲时可以使用的 IOPS 百分比，默认为 100，表示 100% 使用 innodb_io_capacity 的 IOPS。通过设置小于 100 的值来限制空闲时间的刷新，以延长 SSD 的使用寿命。
- innodb_log_compressed_pages：如果重做日志位于 SSD，可以禁用此参数以减少日志。
- innodb_redo_log_capacity：从 MySQL 8.0.30 开始，使用此参数控制重做日志文件占用的磁盘空间。
- innodb_page_size：用于在初始化 MySQL 实例时指定 InnoDB 表空间页大小，默认是 16 KB，适用于大多数工作负载。
- binlog_row_image：如果二进制日志位于 SSD 且所有表都有主键，考虑设置该参数为 minimal 以减少日志。
当使用了 InnoDB 表压缩功能，在对压缩数据进行修改后，使用参数 innodb_log_compressed_pages 指定是否将重新压缩的页映像写入重做日志。默认启用，以防止在恢复过程中使用不同版本的 zlib 压缩算法可能发生的损坏。如果确定 zlib 版本不会更改，可以禁用 innodb_log_compressed_pages，以减少修改压缩数据的重做日志生成。参考： Compression for OLTP Workloadsopen in new window。

Optimizing InnoDB Configuration Variables

需要针对不同的负载进行配置：

使用参数 innodb_change_buffering 指定写缓冲适用的 DML 操作，默认为 all 。参考：Configuring Change Bufferingopen in new window。
使用参数 innodb_adaptive_hash_index 启用自适应哈希索引，默认为 ON。参考：Adaptive Hash Indexopen in new window。
如果出现上下文切换瓶颈，使用参数 innodb_thread_concurrency 指定并发线程的数量。参考：Configuring Thread Concurrency for InnoDBopen in new window。
控制预读数量，参考：Configuring InnoDB Buffer Pool Prefetching (Read-Ahead)open in new window。
根据硬件配置增加后台线程数量。参考：Configuring the Number of Background InnoDB I/O Threadsopen in new window。
控制 IOPS。参考：Configuring InnoDB I/O Capacityopen in new window。
控制何时执行后台写入。参考：Configuring Buffer Pool Flushingopen in new window。
利用多核处理器及其缓存配置，最大程度减少上下文切换的延迟。参考：Configuring Spin Lock Pollingopen in new window。
防止类似于全表扫描这种操作影响存储在 InnoDB 缓冲区缓存中的频繁访问的数据。参考：Making the Buffer Pool Scan Resistantopen in new window。
调整日志文件到合适大小。
配置 InnoDB 缓冲池的实例数量。参考：Configuring Multiple Buffer Pool Instancesopen in new window。
增加最大并发事务数。参考：Undo Logsopen in new window。
配置 Purge 操作。参考：Purge Configurationopen in new window。

要监控 InnoDB 的性能，参考：InnoDB Integration with MySQL Performance Schemaopen in new window。

Controlling the Query Optimizer

Controlling Query Plan Evaluation

优化器的任务是找到 SQL 的最佳执行计划，目标是在尽可能短的时间内找到最佳执行计划，特别是对于有多个表进行关联的 SQL。

可以使用以下两个参数控制优化器的行为：

optimizer_prune_level：默认值为 1，表示根据表的行数跳过某些执行计划，从而减少找到最佳执行计划时间。
optimizer_search_depth：指定优化器的搜索深度，默认值为 62，如果设置为 0，表示由优化器自动选择合适的值。

Switchable Optimizations

使用参数 optimizer_switch 控制优化器行为，其值为一系列标志（可设置为 on 或者 off ）。该参数可以在全局或者会话级别动态设置。

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on,firstmatch=on,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on,hypergraph_optimizer=off,
                    derived_condition_pushdown=on
1 row in set (0.00 sec)

修改标志的语法如下：

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

command 可以为以下值：

Command Syntax	Meaning
`default`	Reset every optimization to its default value
`opt_name=default`	Set the named optimization to its default value
`opt_name=off`	Disable the named optimization
`opt_name=on`	Enable the named optimization

按照优化策略分组，常用标志如下：

条件过滤标志：
- condition_fanout_filter，默认为 on。参考：Condition Filteringopen in new window。
派生条件下推标志：
- derived_condition_pushdown，默认为 on。参考：Derived Condition Pushdown Optimizationopen in new window。
派生表合并标志：
- derived_merge，默认为 on。参考：Optimizing Subqueries, Derived Tables, View References, and Common Table Expressionsopen in new window。
索引条件下推标志：
- index_condition_pushdown，默认为 on。参考：Index Condition Pushdown Optimizationopen in new window。
索引扩展标志：
- use_index_extensions，默认为 on。参考：Use of Index Extensionsopen in new window。
索引合并标志：
- index_merge，默认为 on。
- index_merge_intersection，默认为 on。
- index_merge_sort_union，默认为 on。
- index_merge_union，默认为 on。参考：Index Merge Optimizationopen in new window。
索引可见性标志：
- use_invisible_indexes，默认为 off。参考：Invisible Indexesopen in new window。
限制优化标志：
- prefer_ordering_index，默认为 on。参考：LIMIT Query Optimizationopen in new window。
多范围读标志：
- mrr，默认为 on。
- mrr_cost_based，默认为 on。参考：Multi-Range Read Optimizationopen in new window。
半连接标志：
- duplicateweedout，默认为 on。
- firstmatch，默认为 on。
- loosescan，默认为 on。
- semijoin，默认为 on。参考：Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformationsopen in new window。
跳跃扫描标志：
- skip_scan，默认为 on。参考：Skip Scan Range Access Methodopen in new window。
子查询物化标志：
- materialization，默认为 on。
- subquery_materialization_cost_based，默认为 on。参考：Optimizing Subqueries, Derived Tables, View References, and Common Table Expressionsopen in new window。
子查询转换标志：
- subquery_to_derived，默认为 off。主要用于测试。

修改标志的示例如下：

mysql> SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off';

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=off,
                    index_merge_sort_union=off,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on, firstmatch=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on

Optimizer Hints

可以使用优化器 Hint 控制单个语句的执行计划，优先级高于参数 optimizer_switch 中的标志。

例如：

SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
  FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);

Optimizer Hint Overview

按照作用范围，优化器 Hint 可以分为:

Global：作用于整个语句。
Query Block：作用于语句中的某个查询块。
Table-Level：作用于语句中的某个表。
Index-Level：作用于语句中的某个索引。

优化器 Hint 有：

Hint Name	Description	Applicable Scopes
`BKA`open in new window, `NO_BKA`open in new window	Affects Batched Key Access join processing	Query block, table
`BNL`open in new window, `NO_BNL`open in new window	Prior to MySQL 8.0.20: affects Block Nested-Loop join processing; MySQL 8.0.18 and later: also affects hash join optimization; MySQL 8.0.20 and later: affects hash join optimization only	Query block, table
`DERIVED_CONDITION_PUSHDOWN`open in new window, `NO_DERIVED_CONDITION_PUSHDOWN`open in new window	Use or ignore the derived condition pushdown optimization for materialized derived tables (Added in MySQL 8.0.22)	Query block, table
`GROUP_INDEX`open in new window, `NO_GROUP_INDEX`open in new window	Use or ignore the specified index or indexes for index scans in `GROUP BY` operations (Added in MySQL 8.0.20)	Index
`HASH_JOIN`open in new window, `NO_HASH_JOIN`open in new window	Affects Hash Join optimization (MySQL 8.0.18 only	Query block, table
`INDEX`open in new window, `NO_INDEX`open in new window	Acts as the combination of `JOIN_INDEX`open in new window, `GROUP_INDEX`open in new window, and `ORDER_INDEX`open in new window, or as the combination of `NO_JOIN_INDEX`open in new window, `NO_GROUP_INDEX`open in new window, and `NO_ORDER_INDEX`open in new window (Added in MySQL 8.0.20)	Index
`INDEX_MERGE`open in new window, `NO_INDEX_MERGE`open in new window	Affects Index Merge optimization	Table, index
`JOIN_FIXED_ORDER`open in new window	Use table order specified in `FROM` clause for join order	Query block
`JOIN_INDEX`open in new window, `NO_JOIN_INDEX`open in new window	Use or ignore the specified index or indexes for any access method (Added in MySQL 8.0.20)	Index
`JOIN_ORDER`open in new window	Use table order specified in hint for join order	Query block
`JOIN_PREFIX`open in new window	Use table order specified in hint for first tables of join order	Query block
`JOIN_SUFFIX`open in new window	Use table order specified in hint for last tables of join order	Query block
`MAX_EXECUTION_TIME`open in new window	Limits statement execution time	Global
`MERGE`open in new window, `NO_MERGE`open in new window	Affects derived table/view merging into outer query block	Table
`MRR`open in new window, `NO_MRR`open in new window	Affects Multi-Range Read optimization	Table, index
`NO_ICP`open in new window	Affects Index Condition Pushdown optimization	Table, index
`NO_RANGE_OPTIMIZATION`open in new window	Affects range optimization	Table, index
`ORDER_INDEX`open in new window, `NO_ORDER_INDEX`open in new window	Use or ignore the specified index or indexes for sorting rows (Added in MySQL 8.0.20)	Index
`QB_NAME`open in new window	Assigns name to query block	Query block
`RESOURCE_GROUP`open in new window	Set resource group during statement execution	Global
`SEMIJOIN`open in new window, `NO_SEMIJOIN`open in new window	Affects semijoin strategies; beginning with MySQL 8.0.17, this also applies to antijoins	Query block
`SKIP_SCAN`open in new window, `NO_SKIP_SCAN`open in new window	Affects Skip Scan optimization	Table, index
`SET_VAR`open in new window	Set variable during statement execution	Global
`SUBQUERY`open in new window	Affects materialization, `IN`-to-`EXISTS` subquery strategies	Query block

Optimizer Hint Syntax

优化器 Hint 位于 /*+ ... */ 中，例如：

/*+ BKA(t1) */
/*+ BNL(t1, t2) */
/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
/*+ QB_NAME(qb2) */

解析器可以识别在 SELECT，UPDATE，INSERT，REPLACE 和 DELETE 语句后的优化器 Hint：

SELECT /*+ ... */ ...
INSERT /*+ ... */ ...
REPLACE /*+ ... */ ...
UPDATE /*+ ... */ ...
DELETE /*+ ... */ ...

(SELECT /*+ ... */ ... )
(SELECT ... ) UNION (SELECT /*+ ... */ ... )
(SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )
UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
INSERT ... SELECT /*+ ... */ ...

EXPLAIN SELECT /*+ ... */ ...
EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)

可以在 EXPLAIN 之后执行 SHOW WARNINGS 语句查看使用了哪个优化器 Hint。

如果用到了多个优化器 Hint，需要放在一个 /*+ ... */ 中：

SELECT /*+ BNL(t1) BKA(t2) */ ...

如果多个优化器 Hint 出现重复或者冲突，则使用先出现的优化器 Hint。

优化器 Hint，查询块名称和策略名称不区分大小写。

Join-Order Optimizer Hints

使用 Join-Order Hint 调整表连接顺序。

语法：

hint_name([@query_block_name])
hint_name([@query_block_name] tbl_name [, tbl_name] ...)
hint_name(tbl_name[@query_block_name] [, tbl_name[@query_block_name]] ...)

其中：

hint_name：包括：
- JOIN_FIXED_ORDER：优化器严格按照 FROM 子句中表的顺序进行连接，与 SELECT STRAIGHT_JOIN 效果一致。
- JOIN_ORDER：优化器按照指定的顺序连接表。
- JOIN_PREFIX：指定第一个连接的表。
- JOIN_SUFFIX：指定最后一个连接的表。
tbl_name：表名。如果定义了表别名，则使用表别名。表名不能添加数据库名称前缀。
query_block_name：查询块名。

例如：

SELECT
/*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
    JOIN_ORDER(t4@subq1, t3)
    JOIN_SUFFIX(t1) */
COUNT(*) FROM t1 JOIN t2 JOIN t3
           WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
             AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);

优化器按照以下规则解析 Join-Order Hint：

对于多个 Hint，只能有一个 JOIN_PREFIX 和 JOIN_SUFFIX。例如下面的 JOIN_PREFIX(t2) 会被忽略。

/*+ JOIN_PREFIX(t1) JOIN_PREFIX(t2) */

对于多个 Hint，如果存在冲突，则使用先出现的 Hint，忽略后面的 Hint。例如下面的 JOIN_PREFIX(t2, t1) 会被忽略。

SELECT /*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */ ... FROM t1, t2;

对于多个 Hint，如果存在循环依赖，则会忽略后面的 Hint。例如下面的 JOIN_PREFIX(t2, t1) 会被忽略，不会出现在 EXPLAIN 后面的 SHOW WARNINGS 中。

/*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */

对于 const 表，优化器始终将其作为第一个表进行连接，不受 Hint 的影响。例如以下两个 Hint 等效：

JOIN_ORDER(t1, const_tbl, t2)
JOIN_ORDER(t1, t2)

对于 LEFT， RIGHT， INNER， CROSS 和 STRAIGHT_JOIN 连接类型，如果与 Hint 发生冲突，则会忽略 Hint。例如以下 Hint 中的连接顺序与 LEFT JOIN 连接顺序冲突，则 Hint 会被忽略：

SELECT /*+ JOIN_PREFIX(t1, t2) */FROM t2 LEFT JOIN t1;

Table-Level Optimizer Hints

Table-Level Hint 会影响：

Block Nested-Loop（BNL）和 Batched Key Access（BKA）连接处理算法的使用。
何时合并派生表，视图引用和通用表表达式到外部查询块，合适使用内部临时表进行物化。
使用派生表条件下推优化。参考：Derived Condition Pushdown Optimizationopen in new window。

Table-Level Hint 语法：

hint_name([@query_block_name] [tbl_name [, tbl_name] ...])
hint_name([tbl_name@query_block_name [, tbl_name@query_block_name] ...])

其中：

hint_name：包括：
- BKA，NO_BKA：为指定表启用或禁用 Batched Key Access。
- BNL，NO_BNL：为指定表启用或禁用 Block Nested-Loop。从 MySQL 8.0.18 开始，用于启用或禁用哈希连接。
注意：
从 MySQL 8.0.20 开始，不再支持 Block Nested-Loop 优化，但可以继续使用 BNL 和 NO_BNL 来启用和禁用哈希连接。
- DERIVED_CONDITION_PUSHDOWN，NO_DERIVED_CONDITION_PUSHDOWN：为指定表启用或禁用派生表条件下推优化。
- HASH_JOIN，NO_HASH_JOIN：只能用于 MySQL 8.0.18，为指定表启用或禁用哈希连接。
- MERGE，NO_MERGE：为指定表，视图引用或者通用表表达式启用合并，或者禁用合并使用物化。
tbl_name：表名。如果定义了表别名，则使用表别名。表名不能添加数据库名称前缀。
query_block_name：查询块名。

例如：

SELECT /*+ NO_BKA(t1, t2) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
SELECT /*+ NO_BNL() BKA(t1) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;

Table-Level Hint 作用于接收数据的表，而非发送数据的表。例如以下语句：

SELECT /*+ BNL(t2) */ FROM t1, t2;

如果优化器选择先处理 t1 表，那么在开始读取 t2 表前，通过缓存 t1 表记录，使用 Block Nested-Loop 与 t2 表进行连接。如果优化器选择先处理 t2 表，那么此时 t2 表为发送数据的表，则 Hint 不起作用。

Index-Level Optimizer Hints

Index-Level Hint 会影响优化器使用哪个索引优化策略，包括 Index Condition Pushdownopen in new window（ICP），Multi-Range Readopen in new window（MRR），Index Mergeopen in new window 以及 Range Optimizationopen in new window。

Index-Level Hint 语法：

hint_name([@query_block_name] tbl_name [index_name [, index_name] ...])
hint_name(tbl_name@query_block_name [index_name [, index_name] ...])

其中：

hint_name：包括：
- GROUP_INDEX，NO_GROUP_INDEX：对于 GROUP BY 操作启用或禁用指定索引。等价于 FORCE INDEX FOR GROUP BY，IGNORE INDEX FOR GROUP BY 索引 Hint。
- INDEX，NO_INDEX：INDEX 是 JOIN_INDEX，GROUP_INDEX 和 ORDER_INDEX 的组合，强制使用指定索引；NO_INDEX 是 NO_JOIN_INDEX，NO_GROUP_INDEX 和 NO_ORDER_INDEX 的组合，忽略指定索引。等价于 FORCE INDEX，IGNORE INDEX 索引 Hint。
- INDEX_MERGE，NO_INDEX_MERGE：为指定表或索引启用或禁用 Index Mergeopen in new window 访问方法。
- JOIN_INDEX，NO_JOIN_INDEX：是否使用某个索引，等价于 FORCE INDEX FOR JOIN，IGNORE INDEX FOR JOIN。
- MRR，NO_MRR：为指定表或索引启用或禁用 MRRopen in new window。
- NO_ICP：为指定表或索引禁用 ICPopen in new window。
- NO_RANGE_OPTIMIZATION：为指定表或索引禁用 Index Rangeopen in new window，Index Mergeopen in new window 和 Loose Index Scanopen in new window。
- ORDER_INDEX，NO_ORDER_INDEX：对于排序操作启用或禁用指定索引。等价于 FORCE INDEX FOR ORDER BY，IGNORE INDEX FOR ORDER BY。
- SKIP_SCAN，NO_SKIP_SCAN：为指定表或索引禁用 Skip Scanopen in new window 访问方法。
tbl_name：表名。如果定义了表别名，则使用表别名。表名不能添加数据库名称前缀。
index_name：索引名。如果是主键，则使用 PRIMARY。
query_block_name：查询块名。

例如：

SELECT /*+ INDEX_MERGE(t1 f3, PRIMARY) */ f2 FROM t1
  WHERE f1 = 'o' AND f2 = f3 AND f3 <= 4;
SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
  FROM t3 WHERE f1 > 30 AND f1 < 33;
INSERT INTO t3(f1, f2, f3)
  (SELECT /*+ NO_ICP(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
   WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1
   AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
SELECT /*+ SKIP_SCAN(t1 PRIMARY) */ f1, f2
  FROM t1 WHERE f2 > 40;

Subquery Optimizer Hints

Subquery Hint 会影响是否使用半联接转换以及允许哪些半联接策略，以及不使用半联接时，是使用子查询物化还是 IN-to-EXISTS 转换。

影响半连接策略的语法：

hint_name([@query_block_name] [strategy [, strategy] ...])

其中：

hint_name：包括：
- SEMIJOIN，NO_SEMIJOIN：启用或禁用半连接策略。
strategy：半连接策略，包括 DUPSWEEDOUT，FIRSTMATCH，LOOSESCAN 和 MATERIALIZATION。对于 SEMIJOIN Hint，如果没有指定策略，则使用参数 optimizer_switch 启用的策略，如果指定的策略不适合 SQL 语句，则使用 DUPSWEEDOUT。对于 NO_SEMIJOIN Hint，如果没有指定策略，则不使用半连接。

如果一个子查询嵌套在另一个子查询中，并且两个子查询都合并到外部查询的半连接中，则将忽略最内层查询的任何半连接策略。SEMIJOIN 和 NO_SEMIJOIN 仍可用于启用或禁用此类嵌套子查询的半连接转换。

如果禁用了 DUPSWEEDOUT，优化器可能会生成性能很差的查询计划，可以通过设置 optimizer_prune_level=0 来避免这种情况。

例如：

SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2
  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);

影响是使用子查询物化还是 IN-to-EXISTS 转换的 Hint 语法：

SUBQUERY([@query_block_name] strategy)

Hint 名称始终为 SUBQUERY。strategy 可以是 INTOEXISTS，MATERIALIZATION。

例如：

SELECT id, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1);

Statement Execution Time Optimizer Hints

对于 MAX_EXECUTION_TIME(N) Hint，只适用于 SELECT 语句，指定语句在超时终止前，可以执行的时间为 N 毫秒。如果 N 为 0 或不指定，则由参数 max_execution_time 指定，此参数默认为 0，表示不启用执行超时。

MAX_EXECUTION_TIME(N)

例如以下语句只能在 1 秒内完成：

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

此 Hint 有以下限制：

对于有多个 SELECT 关键字的语句，MAX_EXECUTION_TIME 作用于整个语句时需位于第一个 SELECT 之后。
只适用于只读 SELECT 语句。
不适用于存储函数中的 SELECT 语句。

Variable-Setting Hint Syntax

使用 SET_VAR Hint 为单个语句设置参数值，不能用在子查询。

例如：

SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ 1;

语法：

SET_VAR(var_name = value)

SET_VAR Hint 只在当前语句生效：

mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+
mysql> SELECT /*+ SET_VAR(unique_checks=OFF) */ @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               0 |
+-----------------+
mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+

不是所有的会话参数都可以用于 SET_VAR Hint，使用不支持的参数会发出警告：

mysql> SELECT /*+ SET_VAR(collation_server = 'utf8mb4') */ 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 4537
Message: Variable 'collation_server' cannot be set using SET_VAR hint.

SET_VAR Hint 只支持单个参数，可以使用多个SET_VAR Hint 配置多个参数：

SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off')
           SET_VAR(max_heap_table_size = 1G) */ 1;

多个SET_VAR Hint 配置了相同的参数，则只使用第一个 Hint，忽略其他并发出警告：

SELECT /*+ SET_VAR(max_heap_table_size = 1G)
           SET_VAR(max_heap_table_size = 3G) */ 1;

从库会忽略SET_VAR Hint。

Optimizer Hints for Naming Query Blocks

Table-Level，Index-Level 和 Subquery Hint 可以使用命名的查询块。使用 QB_NAME Hint 创建命名查询块：

QB_NAME(name)

例如以下语句：

SELECT ...
  FROM (SELECT ...
  FROM (SELECT ... FROM ...)) ...

使用 QB_NAME Hint 创建命名查询块：

SELECT /*+ QB_NAME(qb1) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

其他 Hint 使用命名查询块：

SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3 t1 idx1, id2) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

其中：

MRR(@qb1 t1) 应用于查询块 qb2 中的表 t1。
BKA(@qb2) 应用于查询块 qb2。
NO_MRR(@qb3 t1 idx1, id2) 应用于查询块 qb3 中表 t1 及索引 idx1 and idx2。

Index Hints

可以使用 Index Hint 控制优化器使用哪个索引。

语法：

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

其中：

USE INDEX (index_list)：表示使用其中一个索引。
IGNORE INDEX (index_list)：表示忽略指定索引。
FORCE INDEX：表示尽可能使用索引。

注意：
从 MySQL 8.0.20 开始，提供了 JOIN_INDEX，GROUP_INDEX，ORDER_INDEX 和 INDEX 这几个 Index-Level 优化器 Hint，等价于 FORCE INDEX；提供了NO_JOIN_INDEX，NO_GROUP_INDEX，NO_ORDER_INDEX 和 NO_INDEX 这几个 Index-Level 优化器 Hint，等价于 IGNORE INDEX。在后续版本会将 USE INDEX, FORCE INDEX 和 IGNORE INDEX 废弃和移除。

Optimizer Statistics

在 Other MySQL Documentationopen in new window 页面下载测试数据，解压并导入：

[root@s1 ~]# tar -xvzf menagerie-db.tar.gz 
[root@s1 ~]# cd menagerie-db/
[root@s1 menagerie-db]# mysql -e 'CREATE DATABASE menagerie;';
[root@s1 menagerie-db]# mysqlimport --local menagerie pet.txt
menagerie.pet: Records: 8  Deleted: 0  Skipped: 0  Warnings: 0
[root@s1 menagerie-db]# mysql menagerie < ins_puff_rec.sql
[root@s1 menagerie-db]# mysql menagerie < cr_event_tbl.sql
[root@s1 menagerie-db]# mysqlimport --local menagerie event.txt
menagerie.event: Records: 10  Deleted: 0  Skipped: 0  Warnings: 2

[root@s1 ~]# tar -xvzf test_db-1.0.7.tar.gz
[root@s1 ~]# cd test_db/
[root@s1 test_db]# mysql < employees.sql 
INFO
CREATING DATABASE STRUCTURE
INFO
storage engine: InnoDB
INFO
LOADING departments
INFO
LOADING employees
INFO
LOADING dept_emp
INFO
LOADING dept_manager
INFO
LOADING titles
INFO
LOADING salaries
data_load_time_diff
00:01:14

[root@s1 test_db]# mysql -t < test_employees_sha.sql
+----------------------+
| INFO                 |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+------------------------------------------+
| table_name   | expected_records | expected_crc                             |
+--------------+------------------+------------------------------------------+
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_emp     |           331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
+--------------+------------------+------------------------------------------+
+--------------+------------------+------------------------------------------+
| table_name   | found_records    | found_crc                                |
+--------------+------------------+------------------------------------------+
| departments  |                9 | 4b315afa0e35ca6649df897b958345bcb3d2b764 |
| dept_emp     |           331603 | d95ab9fe07df0865f592574b3b33b9c741d9fd1b |
| dept_manager |               24 | 9687a7d6f93ca8847388a42a6d8d93982a841c6c |
| employees    |           300024 | 4d4aa689914d8fd41db7e45c2168e7dcb9697359 |
| salaries     |          2844047 | b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f |
| titles       |           443308 | d12d5f746b88f07e69b9e36675b6067abb01b60e |
+--------------+------------------+------------------------------------------+
+--------------+---------------+-----------+
| table_name   | records_match | crc_match |
+--------------+---------------+-----------+
| departments  | OK            | ok        |
| dept_emp     | OK            | ok        |
| dept_manager | OK            | ok        |
| employees    | OK            | ok        |
| salaries     | OK            | ok        |
| titles       | OK            | ok        |
+--------------+---------------+-----------+
+------------------+
| computation_time |
+------------------+
| 00:01:25         |
+------------------+
+---------+--------+
| summary | result |
+---------+--------+
| CRC     | OK     |
| count   | OK     |
+---------+--------+

从 MySQL 8.0 开始，可以使用直方图（Histogram）对没有索引或者数据倾斜字段进行统计分析，以便优化器选择更优的执行计划。

使用 ANALYZE TABLE 语句创建和删除直方图。

语法：

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    UPDATE HISTOGRAM ON col_name [, col_name] ...
        [WITH N BUCKETS]

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] 
    TABLE tbl_name
    UPDATE HISTOGRAM ON col_name [USING DATA 'json_data']

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL]
    TABLE tbl_name
    DROP HISTOGRAM ON col_name [, col_name] ...

创建直方图：

mysql> ANALYZE TABLE t UPDATE HISTOGRAM ON c1;
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| mydb.t | histogram | status   | Histogram statistics created for column 'c1'. |
+--------+-----------+----------+-----------------------------------------------+

生成后可以在 INFORMATION_SCHEMA.COLUMN_STATISTICS 查看直方图信息：

mysql> TABLE information_schema.column_statistics\G
*************************** 1. row ***************************
SCHEMA_NAME: mydb
 TABLE_NAME: t
COLUMN_NAME: c1
  HISTOGRAM: {
                "buckets":[
                    [
                        206,
                        0.0625
                    ],
                    [
                        456,
                        0.125
                    ],
                    [
                        608,
                        0.1875
                    ]
                ],
                "data-type":"int",
                "null-values":0,
                "collation-id":8,
                "last-updated":"2022-10-11 16:13:14.563319",
                "sampling-rate":1,
                "histogram-type":"singleton",
                "number-of-buckets-specified":100
            }

创建直方图时指定 BUCKET 数量：

[employees]> ANALYZE TABLE salaries UPDATE HISTOGRAM ON salary WITH 16 BUCKETS;
+--------------------+-----------+----------+---------------------------------------------------+
| Table              | Op        | Msg_type | Msg_text                                          |
+--------------------+-----------+----------+---------------------------------------------------+
| employees.salaries | histogram | status   | Histogram statistics created for column 'salary'. |
+--------------------+-----------+----------+---------------------------------------------------+
1 row in set (2.87 sec)

[employees]> TABLE information_schema.column_statistics\G
*************************** 1. row ***************************
SCHEMA_NAME: employees
 TABLE_NAME: salaries
COLUMN_NAME: salary
  HISTOGRAM: {
                "buckets":[
                    [
                        38874,
                        41992,
                        0.06262269336474284,
                        2881
                    ],
                    [
                        41993,
                        45021,
                        0.12523531958160933,
                        3045
                    ],
                    [
                        45022,
                        47897,
                        0.18784291222453767,
                        2888
                    ],
                    [
                        47898,
                        50571,
                        0.2504580552283732,
                        2676
                    ],
                    [
                        50572,
                        53213,
                        0.31306816465827064,
                        2642
                    ],
                    [
                        53214,
                        55845,
                        0.37568582444907533,
                        2634
                    ],
                    [
                        55846,
                        58505,
                        0.4383085178138182,
                        2665
                    ],
                    [
                        58506,
                        61230,
                        0.5009186272437156,
                        2728
                    ],
                    [
                        61231,
                        64106,
                        0.5635186695257367,
                        2883
                    ],
                    [
                        64107,
                        67176,
                        0.6261413628904795,
                        3085
                    ],
                    [
                        67177,
                        70533,
                        0.6887690898291605,
                        3371
                    ],
                    [
                        70534,
                        74355,
                        0.7513892664069343,
                        3882
                    ],
                    [
                        74356,
                        78993,
                        0.814006926197739,
                        4812
                    ],
                    [
                        78994,
                        84866,
                        0.8766245859885435,
                        6384
                    ],
                    [
                        84867,
                        93601,
                        0.9392497961402555,
                        10705
                    ],
                    [
                        93602,
                        155513,
                        1,
                        30081
                    ]
                ],
                "data-type":"int",
                "null-values":0,
                "collation-id":8,
                "last-updated":"2023-07-17 03:34:33.649078",
                "sampling-rate":0.1356919562459659,
                "histogram-type":"equi-height",
                "number-of-buckets-specified":16
            }
  1 row in set (0.00 sec)

其中：

histogram-type：表示直方图类型，不能在创建时指定，由系统自动决定。有两种：
- singleton：等宽直方图，每个 BUCKET 保存一个值。
- equi-height：等高直方图，每个 BUCKET 保存多个值。
buckets：列出各个 BUCKET，如果是等宽直方图，每个 BUCKET 中包含每个字段的每个值及其累积占比，如果是等高直方图，每个 BUCKET 中包含此 BUCKET 中的最小值和最大值，累计占比以及不同值数量。
data-type：表示字段类型，可以是 int，uint (unsigned integer)，double，decimal，datetime 还或者 string 。
null-values：空值数量。
last-updated：上次更新时间。
sampling-rate：采样率。
number-of-buckets-specified：指定的 BUCKET 数量。
collation-id：对应 INFORMATION_SCHEMA.COLLATIONS 表的 ID 字段。

可以使用 JSON 运算符获取指定信息：

mysql> SELECT
         TABLE_NAME, COLUMN_NAME,
         HISTOGRAM->>'$."data-type"' AS 'data-type',
         JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS 'bucket-count'
       FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;
+-----------------+-------------+-----------+--------------+
| TABLE_NAME      | COLUMN_NAME | data-type | bucket-count |
+-----------------+-------------+-----------+--------------+
| country         | Population  | int       |          226 |
| city            | Population  | int       |         1024 |
| countrylanguage | Language    | string    |          457 |
+-----------------+-------------+-----------+--------------+

可以使用直方图的谓词形式有：

col_name = constant
col_name <> constant
col_name != constant
col_name > constant
col_name < constant
col_name >= constant
col_name <= constant
col_name IS NULL
col_name IS NOT NULL
col_name BETWEEN constant AND constant
col_name NOT BETWEEN constant AND constant
col_name IN (constant[, constant] ...)
col_name NOT IN (constant[, constant] ...)

例如：

SELECT * FROM orders WHERE amount BETWEEN 100.0 AND 300.0;
SELECT * FROM tbl WHERE col1 = 15 AND col2 > 100;

删除直方图：

mysql> ANALYZE TABLE t DROP HISTOGRAM ON c1;
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| mydb.t | histogram | status   | Histogram statistics removed for column 'c1'. |
+--------+-----------+----------+-----------------------------------------------+

mysql> TABLE information_schema.column_statistics\G
Empty set (0.00 sec)

还可以使用之前的 JSON 信息恢复直方图：

mysql> ANALYZE TABLE t UPDATE HISTOGRAM ON c1 
    ->     USING DATA '{"buckets": [[206, 0.0625], [456, 0.125], [608, 0.1875]],
    ->               "data-type": "int", "null-values": 0.0, "collation-id":
    ->               8, "last-updated": "2022-10-11 16:13:14.563319",
    ->               "sampling-rate": 1.0, "histogram-type": "singleton",
    ->               "number-of-buckets-specified": 100}';   
+--------+-----------+----------+-----------------------------------------------+
| Table  | Op        | Msg_type | Msg_text                                      |
+--------+-----------+----------+-----------------------------------------------+
| mydb.t | histogram | status   | Histogram statistics created for column 'c1'. |
+--------+-----------+----------+-----------------------------------------------+

mysql> TABLE information_schema.column_statistics\G
*************************** 1. row ***************************
SCHEMA_NAME: mydb
 TABLE_NAME: t
COLUMN_NAME: c1
  HISTOGRAM: {"buckets": [[206, 0.0625], [456, 0.125], [608, 0.1875]],
"data-type": "int", "null-values": 0.0, "collation-id": 8, "last-updated":
"2022-10-11 16:13:14.563319", "sampling-rate": 1.0, "histogram-type":
"singleton", "number-of-buckets-specified": 100}

直方图不支持以下几种情况：

加密表、临时表。
JSON数据类型、空间（spatial）数据类型。
已创建唯一索引的单列。

使用参数 histogram_generation_max_mem_size 指定创建直方图时可以使用的最大内存，默认为 20000000 字节，如果需要读取的数据量较大，超过了设置值，则会进行采用，采样率为 sampling-rate。

例如：

mysql> SET histogram_generation_max_mem_size = 2000000;

mysql> USE employees;

mysql> ANALYZE TABLE employees UPDATE HISTOGRAM ON birth_date WITH 16 BUCKETS\G
*************************** 1. row ***************************
   Table: employees.employees
      Op: histogram
Msg_type: status
Msg_text: Histogram statistics created for column 'birth_date'.

mysql> SELECT HISTOGRAM->>'$."sampling-rate"'
       FROM INFORMATION_SCHEMA.COLUMN_STATISTICS
       WHERE TABLE_NAME = "employees"
       AND COLUMN_NAME = "birth_date";
+---------------------------------+
| HISTOGRAM->>'$."sampling-rate"' |
+---------------------------------+
| 0.0491431208869665              |
+---------------------------------+

sampling-rate 为 0.0491431208869665 意味着创建直方图时只会读取 birth_date 字段 4.9% 的数据到内存中。

Buffering and Caching

InnoDB Buffer Pool Optimization

InnoDB 使用缓冲池缓存数据和索引到内存，参考：Buffer Poolopen in new window。

对于 InnoDB 缓冲池配置和调优，参考：

Caching of Prepared Statements and Stored Programs

使用参数 max_prepared_stmt_count 指定同一时间在所有会话中 Prepare Statement 的最大值，默认为 16382。

[(none)]> SHOW VARIABLES LIKE 'max_prepared_stmt_count';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| max_prepared_stmt_count | 16382 |
+-------------------------+-------+
1 row in set (0.00 sec)

根据使用情况调整该参数值：

[(none)]> SHOW GLOBAL STATUS LIKE 'com_stmt%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Com_stmt_execute        | 0     |
| Com_stmt_close          | 0     |
| Com_stmt_fetch          | 0     |
| Com_stmt_prepare        | 0     |
| Com_stmt_reset          | 0     |
| Com_stmt_send_long_data | 0     |
| Com_stmt_reprepare      | 0     |
+-------------------------+-------+
7 rows in set (0.04 sec)

Optimizing the MySQL Server

Optimizing Memory Use

Monitoring MySQL Memory Usage

本节介绍如何使用 information_schema 和 sys 监控 MySQL 内存使用。

大部分 information_schema 的内存 Instruments 都是关闭的，通过更新 setup_instruments 表的 ENABLED 字段启用 Instruments。Instruments 的名称形式为 memory/code_area/instrument_name，其中 code_area 可以是 sql 或者 innodb，instrument_name 为 Instrument 名称。

查询 performance_schema.setup_instruments 查看 MySQL 内存 Instruments：

mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%memory/innodb%';
+-------------------------------------------+---------+-------+
| NAME                                      | ENABLED | TIMED |
+-------------------------------------------+---------+-------+
| memory/innodb/adaptive hash index         | NO      | NO    |
| memory/innodb/buf_buf_pool                | NO      | NO    |
| memory/innodb/dict_stats_bg_recalc_pool_t | NO      | NO    |
| memory/innodb/dict_stats_index_map_t      | NO      | NO    |
| memory/innodb/dict_stats_n_diff_on_level  | NO      | NO    |
| memory/innodb/other                       | NO      | NO    |
| memory/innodb/row_log_buf                 | NO      | NO    |
| memory/innodb/row_merge_sort              | NO      | NO    |
| memory/innodb/std                         | NO      | NO    |
| memory/innodb/trx_sys_t::rw_trx_ids       | NO      | NO    |
...

增加 performance-schema-instrument 到参数文件中并重启 MySQL Server 以启用内存 Instruments：

performance-schema-instrument='memory/%=COUNTED'

重启后，ENABLED 字段变为 YES。

查询 performance_schema.memory_summary_global_by_event_name 获取内存 Instruments 数据：

mysql> SELECT * FROM performance_schema.memory_summary_global_by_event_name
       WHERE EVENT_NAME LIKE 'memory/innodb/buf_buf_pool'\G
                  EVENT_NAME: memory/innodb/buf_buf_pool
                 COUNT_ALLOC: 1
                  COUNT_FREE: 0
   SUM_NUMBER_OF_BYTES_ALLOC: 137428992
    SUM_NUMBER_OF_BYTES_FREE: 0
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 1
             HIGH_COUNT_USED: 1
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 137428992
   HIGH_NUMBER_OF_BYTES_USED: 137428992

也可以查询 sys.memory_global_by_current_bytes 显示当前内存使用情况：

mysql> SELECT * FROM sys.memory_global_by_current_bytes
       WHERE event_name LIKE 'memory/innodb/buf_buf_pool'\G
*************************** 1. row ***************************
       event_name: memory/innodb/buf_buf_pool
    current_count: 1
    current_alloc: 131.06 MiB
current_avg_alloc: 131.06 MiB
       high_count: 1
       high_alloc: 131.06 MiB
   high_avg_alloc: 131.06 MiB

对各种内存分组计算：

mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
       code_area, FORMAT_BYTES(SUM(current_alloc))
       AS current_alloc
       FROM sys.x$memory_global_by_current_bytes
       GROUP BY SUBSTRING_INDEX(event_name,'/',2)
       ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area                 | current_alloc |
+---------------------------+---------------+
| memory/innodb             | 843.24 MiB    |
| memory/performance_schema | 81.29 MiB     |
| memory/mysys              | 8.20 MiB      |
| memory/sql                | 2.47 MiB      |
| memory/memory             | 174.01 KiB    |
| memory/myisam             | 46.53 KiB     |
| memory/blackhole          | 512 bytes     |
| memory/federated          | 512 bytes     |
| memory/csv                | 512 bytes     |
| memory/vio                | 496 bytes     |
+---------------------------+---------------+

Enabling Large Page Support

在 MySQL 中，可以为 InnoDB 的缓冲池配置大页内存。

步骤如下：

使用 innodb_buffer_pool_size / Hugepagesize 计算得出大页数量 1024 * 1024 / 2048 = 512，其中 Hugepagesize 通过查看 /proc/meminfo 获取。

[root@s1 ~]# grep -i huge /proc/meminfo
AnonHugePages:    569344 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

使用 root 用户编辑文件 /etc/sysctl.conf，添加以下参数：

vm.nr_hugepages=512

其中 P 为上一步的计算结果。

运行以下命令使参数生效：

[root@s1 ~]# sysctl -p
vm.nr_hugepages = 512

然后查看 /proc/meminfo，检查是否生效：

[root@s1 ~]# grep -i huge /proc/meminfo
AnonHugePages:    573440 kB
HugePages_Total:     233
HugePages_Free:      233
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB

确保参数 innodb_buffer_pool_chunk_size 比大页的大小（2048 kB）大，默认为 128 MB。
MySQL Server 默认禁用大页，在参数文件中增加以下参数以启动大页：

large-pages=ON

重启 MySQL Server 后，再次查看 /proc/meminfo 验证是否使用了大页内存：

[root@s1 ~]# grep -i huge /proc/meminfo
AnonHugePages:    280576 kB
HugePages_Total:     233
HugePages_Free:      220
HugePages_Rsvd:      185
HugePages_Surp:        0
Hugepagesize:       2048 kB

MySQL Optimization

# MySQL Optimization

# Optimization Overview

# Optimizing at the Database Level

# Optimizing at the Hardware Level

# Understanding the Query Execution Plan

# Optimizing Queries with EXPLAIN

# EXPLAIN Output Format

# EXPLAIN Output Columns

# EXPLAIN Join Types

# EXPLAIN Extra Information

# EXPLAIN Output Interpretation

# Extended EXPLAIN Output Format

# Obtaining Execution Plan Information for a Named Connection

# Optimizing SQL Statements

# Optimizing SELECT Statements

# WHERE Clause Optimization

# Range Optimization

# Range Access Method for Single-Part Indexes

# Range Access Method for Multiple-Part Indexes

# Equality Range Optimization of Many-Valued Comparisons

# Skip Scan Range Access Method

# Range Optimization of Row Constructor Expressions

# Limiting Memory Use for Range Optimization

# Index Merge Optimization

# Index Merge Intersection Access Algorithm

# Index Merge Union Access Algorithm

# Index Merge Sort-Union Access Algorithm

# Influencing Index Merge Optimization

# Hash Join Optimization

# Index Condition Pushdown Optimization

# Nested-Loop Join Algorithm

# Nested Join Optimization

# Outer Join Optimization

# Outer Join Simplification

# Multi-Range Read Optimization

# Condition Filtering

# ORDER BY Optimization

# Use of Indexes to Satisfy ORDER BY

# Use of filesort to Satisfy ORDER BY

# Influencing ORDER BY Optimization

# ORDER BY Execution Plan Information Available

# GROUP BY Optimization

# Loose Index Scan

# Tight Index Scan

# DISTINCT Optimization

# LIMIT Query Optimization

# Function Call Optimization

# Avoiding Full Table Scans

# Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions

# Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations

# DuplicateWeedout Strategy

# FirstMatch Strategy

# LooseScan Strategy

# Semi-join Materialization Strategy

# Materialization-Scan

# Materialization-Lookup

# Optimizing Subqueries with Materialization

# Optimizing Subqueries with the EXISTS Strategy

# Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization

# Derived Condition Pushdown Optimization

# Optimizing INFORMATION_SCHEMA Queries

# Optimizing Performance Schema Queries

# Optimizing Data Change Statements

# Optimizing INSERT Statements

# Optimizing UPDATE Statements

# Optimizing DELETE Statements

# Optimizing Database Privileges

# Other Optimization Tips

# Optimization and Indexes

# How MySQL Uses Indexes

# Primary Key Optimization

# Column Indexes

# Index Prefixes

# Indexes in the MEMORY Storage Engine

# Multiple-Column Indexes

# Verifying Index Usage

# InnoDB Index Statistics Collection

# Comparison of B-Tree and Hash Indexes

# B-Tree Index Characteristics

MySQL Optimization

Optimization Overview

Optimizing at the Database Level

Optimizing at the Hardware Level

Understanding the Query Execution Plan

Optimizing Queries with EXPLAIN

EXPLAIN Output Format

EXPLAIN Output Columns

EXPLAIN Join Types

EXPLAIN Extra Information

EXPLAIN Output Interpretation

Extended EXPLAIN Output Format

Obtaining Execution Plan Information for a Named Connection

Optimizing SQL Statements

Optimizing SELECT Statements

WHERE Clause Optimization

Range Optimization

Range Access Method for Single-Part Indexes

Range Access Method for Multiple-Part Indexes

Equality Range Optimization of Many-Valued Comparisons

Skip Scan Range Access Method

Range Optimization of Row Constructor Expressions

Limiting Memory Use for Range Optimization

Index Merge Optimization

Index Merge Intersection Access Algorithm

Index Merge Union Access Algorithm

Index Merge Sort-Union Access Algorithm

Influencing Index Merge Optimization

Hash Join Optimization

Index Condition Pushdown Optimization

Nested-Loop Join Algorithm

Nested Join Optimization

Outer Join Optimization

Outer Join Simplification

Multi-Range Read Optimization

Condition Filtering

ORDER BY Optimization

Use of Indexes to Satisfy ORDER BY

Use of filesort to Satisfy ORDER BY

Influencing ORDER BY Optimization

ORDER BY Execution Plan Information Available

GROUP BY Optimization

Loose Index Scan

Tight Index Scan

DISTINCT Optimization

LIMIT Query Optimization

Function Call Optimization

Avoiding Full Table Scans

Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions

Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations

DuplicateWeedout Strategy

FirstMatch Strategy

LooseScan Strategy

Semi-join Materialization Strategy

Materialization-Scan

Materialization-Lookup

Optimizing Subqueries with Materialization

Optimizing Subqueries with the EXISTS Strategy

Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization

Derived Condition Pushdown Optimization

Optimizing INFORMATION_SCHEMA Queries

Optimizing Performance Schema Queries

Optimizing Data Change Statements

Optimizing INSERT Statements

Optimizing UPDATE Statements

Optimizing DELETE Statements

Optimizing Database Privileges

Other Optimization Tips

Optimization and Indexes

How MySQL Uses Indexes

Primary Key Optimization

Column Indexes

Index Prefixes

Indexes in the MEMORY Storage Engine

Multiple-Column Indexes

Verifying Index Usage

InnoDB Index Statistics Collection

Comparison of B-Tree and Hash Indexes

B-Tree Index Characteristics

Hash Index Characteristics