MySQL 8.0 参考手册——8.2优化 SQL 语句(二)

8.2.1.13 条件过滤

在连接处理中，前缀行是从连接中的一个表传递到下一个表的行。一般来说，优化程序会尝试将前缀数较少的表放在连接顺序的前面，以防止行组合数迅速增加。如果优化器能使用从一个表中选择并传递到下一个表的行的条件信息，就能更准确地计算行估计值并选择最佳执行计划。

如果不使用条件过滤，那么表的前缀行数将基于 WHERE 子句根据优化器选择的访问方法估计行数。条件过滤使优化器能够在访问方法未考虑到的WHERE子句中使用其他相关条件，从而改进其前缀行行数估计值。例如，即使有一种基于索引的访问方法可用于在连接中从当前表中选择行，但在 WHERE 子句中也可能存在表的其他条件，可以过滤（进一步限制）传递到下一个表的合格行的估计值。

只有在以下情况下，条件才会对过滤估计有所帮助：

它指的是当前表格。
它取决于一个常量值或来自连接序列中早期表的值。
访问方法尚未将其考虑在内。

在 EXPLAIN 输出中，行列表示所选访问方法的行估计值，过滤列反映条件过滤的效果。最大值为 100，表示没有过滤行。从 100 开始递减的值表示过滤量在增加。

前缀行数（在连接中从当前表传递到下一个表的估计行数）是行数与过滤值的乘积。也就是说，前缀行数是估计的行数，再减去估计的过滤效果。例如，如果行数为 1000，过滤值为 20%，则条件过滤会将估计行数 1000 减少为前缀行数 1000 × 20% = 1000 × 0.2 = 200。

请看下面的查询：

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

假设该数据集具有以下特征：

雇员表有 1024 行。
部门表有 12 行。
两个表都有一个关于 dept_no 的索引。
雇员表在 first_name 上有一个索引。
有 8 条记录满足 employee.first_name 上的条件：

employee.first_name = 'John'

有 150 条记录满足 employee.hire_date 的这一条件：

employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'

有 1 行同时满足这两个条件：

employee.first_name = 'John'
AND employee.hire_date BETWEEN '2018-01-01' AND '2018-06-01'

如果不进行条件过滤，EXPLAIN 会产生这样的输出结果：

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

就雇员而言，名称索引的访问方法会获取与名称 "John "匹配的 8 条记录。没有进行过滤（过滤率为 100%），因此所有记录都是下一个表的前缀行：前缀行计数为行 × 过滤 = 8 × 100% = 8。

通过条件过滤，优化器会额外考虑访问方法未考虑的 WHERE 子句中的条件。在本例中，优化器使用启发式方法估算了 employee.hire_date 的 BETWEEN 条件的过滤效果为 16.31%。因此，EXPLAIN 的输出结果如下：

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

现在，前缀行数为行 × 筛选 = 8 × 16.31% = 1.3，更接近实际数据集。

通常情况下，优化器不会计算最后一个连接表的条件过滤效果（前缀行数减少），因为没有下一个表可以传递行。但 EXPLAIN 的情况例外：为了提供更多信息，会计算所有连接表（包括最后一个表）的过滤效果。

要控制优化器是否考虑额外的过滤条件，可以使用 optimizer_switch 系统变量的 condition_fanout_filter 标志（参见第 8.9.2 节 "可切换优化"）。默认情况下启用该标志，但也可以禁用该标志来抑制条件过滤（例如，如果发现某个查询在不使用条件过滤的情况下性能更好）。

如果优化器高估了条件过滤的效果，性能可能会比不使用条件过滤时更差。在这种情况下，这些技术可能会有所帮助：

如果未对某列进行索引，则对其进行索引，这样优化器就能获得一些关于列值分布的信息，从而改进对行的估计。
同样，如果没有列直方图信息，可以生成直方图（参见第 8.9.6 节 "优化器统计"）。
更改连接顺序。实现这一点的方法包括连接顺序优化提示（参见第 8.9.3 节 "优化提示"）、紧跟 SELECT 的 STRAIGHT_JOIN 以及 STRAIGHT_JOIN 连接操作符。
禁用会话的条件筛选：

SET optimizer_switch = 'condition_fanout_filter=off';

或者，对于给定查询，使用优化器提示：

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

8.2.1.14 常数折叠优化

常数折叠（Constant folding）以及常数传播（constant propagation）都是编译器最佳化技术，他们被使用在现代的编译器中。进阶的常数传播形式，或称之为稀疏有条件的常量传播（sparse conditional constant propagation），可以更精确地传播常数及无缝的移除无用的程式码。（提前把值算出来）

在常量和列值之间进行比较时，如果常量值超出范围或相对于列类型而言类型错误，现在会在查询优化过程中逐行处理，而不是在执行过程中处理。以这种方式处理的比较有 >、>=、<、<=、<>/!=、= 和 <=>。

请看下面语句创建的表格：

CREATE TABLE t (c TINYINT UNSIGNED NOT NULL);

在查询 SELECT * FROM t WHERE c < 256 中的 WHERE 条件包含整数常数 256，这超出了 TINYINT UNSIGNED 列的范围。以前的处理方法是将两个操作数都视为较大的类型，但现在，由于 c 的任何允许值都小于常数，WHERE 表达式可以折叠为 WHERE 1，这样查询就改写为 SELECT * FROM t WHERE 1。

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

SELECT * FROM t WHERE ti IS NOT NULL

与支持的 MySQL 列类型相比，常量的折叠方法如下：

Integer column type 整数类型与以下类型的常量进行比较，如此处所述：

Integer value.如果常量超出列类型的范围，比较结果将折叠为 1 或 IS NOT NULL，如前所述。如果常量是范围边界，则比较结果将折叠为 =：

mysql> EXPLAIN SELECT * FROM t WHERE c >= 255;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 20.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`t`.`ti` AS `ti` from `test`.`t` where (`test`.`t`.`ti` = 255)
1 row in set (0.00 sec)

Floating- or fixed-point value.

如果常数是十进制类型（如 DECIMAL、REAL、DOUBLE 或 FLOAT）之一，且小数部分不为零，则不可能相等；因此要进行相应的折叠。对于其他比较，根据符号向上或向下舍入为整数值，然后执行范围检查，并按照前面所述的整数-整数比较进行处理。

如果一个 REAL 值太小，无法用十进制表示，则会根据符号四舍五入为 .01 或 -.01，然后按十进制处理。

String types.

尝试将字符串值解释为整数类型，然后在整数值之间进行比较。如果失败，则尝试将值作为 REAL 处理。

DECIMAL or REAL column. 十进制类型与以下类型的常量进行比较，如此处所述：

Integer value. 对列值的整数部分进行范围检查。如果没有折叠结果，则将常数转换为小数位数，小数位数与列值相同，然后将其作为小数位数进行检查（见下一步）。

DECIMAL or REAL value. 检查是否有溢出（即常数整数部分的位数是否超过列的十进制类型所允许的位数）。如果是，则折叠。

String value. 如果数值可以解释为整数类型，则按整数类型处理。否则，尽量按 REAL 类型处理。

FLOAT or DOUBLE column.

FLOAT(m,n) 或 DOUBLE(m,n) 值与常量相比的处理方法如下：

如果数值超出列的范围，则折叠。

如果数值超过 n 个小数，则截断，在折叠过程中进行补偿。对于 = 和 <> 比较，如前所述，折叠为 TRUE、FALSE 或 IS [NOT] NULL；对于其他操作符，调整操作符。

如果数值的整数位数超过 m，则折叠。

限制以下情况不能使用此优化：

使用 BETWEEN 或 IN 进行比较。

使用 BIT 列或使用日期或时间类型的列。

在准备语句的准备阶段，尽管可以在优化阶段实际执行准备语句时使用。这是因为在语句准备期间，常量的值尚未确定。

8.2.1.15 IS NULL 优化

MySQL 可以对 col_name IS NULL 执行与 col_name = constant_value 相同的优化。例如，MySQL 可以使用索引和范围来搜索 IS NULL 中的 NULL。

例如

SELECT * FROM tbl_name WHERE key_col IS NULL;

SELECT * FROM tbl_name WHERE key_col <=> NULL;

SELECT * FROM tbl_name
  WHERE key_col=const1 OR key_col=const2 OR key_col IS NULL;

如果 WHERE 子句包含一个 col_name IS NULL 条件，而该列已声明为 NOT NULL，那么该表达式将被优化掉。如果列无论如何都可能产生 NULL（例如，如果列来自 LEFT JOIN 右侧的表），则不会进行这种优化。

MySQL 还可以优化 col_name = expr OR col_name IS NULL 组合，这种形式在解析子查询中很常见。使用这种优化时，EXPLAIN 会显示 ref_or_null。

这种优化可以处理任何键部分的一个 IS NULL。

假设表 t2 的 a 列和 b 列上有索引，优化后查询的一些示例：

SELECT * FROM t1 WHERE t1.a=expr OR t1.a IS NULL;

SELECT * FROM t1, t2 WHERE t1.a=t2.a OR t2.a IS NULL;

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a OR t2.a IS NULL) AND t2.b=t1.b;

SELECT * FROM t1, t2
  WHERE t1.a=t2.a AND (t2.b=t1.b OR t2.b IS NULL);

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL AND ...)
  OR (t1.a=t2.a AND t2.a IS NULL AND ...);

ref_or_null 的工作原理是，首先读取引用键，然后单独搜索键值为 NULL 的记录。

优化只能处理一个 IS NULL 级别。在下面的查询中，MySQL 只在表达式（t1.a=t2.a AND t2.a IS NULL）上使用键查找，而不能在 b 上使用键部分：

SELECT * FROM t1, t2
  WHERE (t1.a=t2.a AND t2.a IS NULL)
  OR (t1.b=t2.b AND t2.b IS NULL);

8.2.1.16 ORDER BY 优化

本节介绍了 MySQL 在什么情况下可以使用索引来满足 ORDER BY 子句、无法使用索引时使用的文件排序操作，以及优化器提供的有关 ORDER BY 的执行计划信息。

如第 8.2.1.19 节 "LIMIT 查询优化 "所述，带 LIMIT 和不带 LIMIT 的 ORDER BY 可能以不同的顺序返回记录。

使用索引满足 ORDER BY 的要求
使用文件排序来满足 ORDER BY
影响 ORDER BY 优化的因素
可用的 ORDER BY 执行计划信息

使用索引满足 ORDER BY 的要求

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

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

假设（key_part1, key_part2）上有一个索引，下面的查询可能会使用该索引来解决 ORDER BY 部分。优化器是否真的会这样做，取决于在必须同时读取不在索引中的列的情况下，读取索引是否比表扫描更有效。

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

SELECT * FROM t1
  ORDER BY key_part1, key_part2;

但是，查询使用了 SELECT *，可能会选择比 key_part1 和 key_part2 更多的列。在这种情况下，扫描整个索引并查找表行以找到索引中没有的列，可能比扫描表并对结果进行排序更昂贵。如果是这样，优化器可能不会使用索引。如果 SELECT * 只选择索引列，则会使用索引并避免排序。

如果 t1 是 InnoDB 表，则表的主键隐式地是索引的一部分，索引可用于解决此查询的 ORDER BY 问题：

SELECT pk, key_part1, key_part2 FROM t1
  ORDER BY key_part1, key_part2;

在此查询中，key_part1 是常数，因此通过索引访问的所有记录都按 key_part2 的顺序排列，如果 WHERE 子句有足够的选择性，使索引范围扫描比表扫描更省钱，那么 (key_part1, key_part2) 上的索引就可以避免排序：

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

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

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

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

ORDER BY 中的两列可以按相同方向排序（都是 ASC，或都是 DESC），也可以按相反方向排序（一个 ASC，一个 DESC）。使用索引的一个条件是，索引必须具有相同的同质性，但不需要具有相同的实际方向。

在下一个查询中，ORDER BY 没有命名 key_part1，但所有被选中的记录都有一个恒定的 key_part1 值，因此仍然可以使用索引：

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

在某些情况下，MySQL 无法使用索引来解决 ORDER BY 问题，尽管它仍然可以使用索引来查找与 WHERE 子句相匹配的记录。示例：

查询在不同索引上使用 ORDER BY：

SELECT * FROM t1 ORDER BY key1, key2;

该查询对索引的非连续部分使用 ORDER BY：

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

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

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

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

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

查询连接了许多表，而 ORDER BY 中的列并不都来自第一个用于检索记录的非常量表。(这是 EXPLAIN 输出中第一个没有常数连接类型的表）。

查询有不同的 ORDER BY 和 GROUP BY 表达式。

仅在 ORDER BY 子句中指定的列的前缀上有一个索引。在这种情况下，索引不能用于完全解析排序顺序。例如，如果只对 CHAR(20) 列的前 10 个字节进行了索引，索引就无法区分第 10 个字节之后的值，因此需要进行文件排序。

索引不按顺序存储行。例如，这适用于 MEMORY 表中的 HASH 索引。

使用列别名可能会影响索引排序的可用性。假设列 t1.a 已被索引。在该语句中，选择列表中列的名称是 a。它指向 t1.a，ORDER BY 中对 a 的引用也指向 t1.a，因此可以使用 t1.a 上的索引：

SELECT a FROM t1 ORDER BY a;

在这条语句中，查询列表中的列名也是 a，但它是别名。它指的是 ABS(a)，ORDER BY 中对 a 的引用也是 ABS(a)，因此不能使用 t1.a 上的索引：

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

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

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

以前（MySQL 5.7 及更低版本），GROUP BY 在某些条件下会隐式排序。在 MySQL 8.0 中，这种情况不再发生，因此不再需要在末尾指定 ORDER BY NULL 来抑制隐式排序（以前的做法）。不过，查询结果可能与以前的 MySQL 版本不同。要产生给定的排序顺序，请提供 ORDER BY 子句。

使用文件排序满足 ORDER BY 的要求

如果索引无法满足 ORDER BY 子句的要求，MySQL 会执行文件排序操作，读取表行并对其进行排序。文件排序是查询执行过程中的一个额外排序阶段。

为了获取文件排序操作需要的内存，从MySQL 8.0.12开始，优化器会根据需要增量分配内存缓冲区，直到sort_buffer_size系统变量的最大值。而不是像 MySQL 8.0.12 之前那样，预先分配固定数量的 sort_buffer_size 字节。这样，用户就可以将 sort_buffer_size 设置为较大值，以加快大型排序的速度，而不必担心小型排序会占用过多内存。(在多线程 malloc 功能较弱的 Windows 上进行多个并发排序时，可能不会有这种好处）。

如果结果集过大，内存无法容纳，文件排序操作会根据需要使用临时磁盘文件。某些类型的查询特别适合完全在内存中进行文件排序操作。例如，优化器可以使用文件排序在内存中有效地处理下列形式的查询（和子查询）的 ORDER BY 操作，而无需使用临时文件：

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

这种查询在网络应用程序中很常见，它只显示较大结果集中的几行。例如：

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;
SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

影响 ORDER BY 优化

对于不使用文件排序的慢速 ORDER BY 查询，可尝试将 max_length_for_sort_data 系统变量降低到适合触发文件排序的值。(该变量值设置过高的一个症状是磁盘活动量大而 CPU 活动量小）。此技术仅适用于 MySQL 8.0.20 之前。从 8.0.20 起，由于优化器的更改，max_length_for_sort_data 已被弃用，不再有任何作用。

要提高 ORDER BY 的速度，请检查是否可以让 MySQL 使用索引而不是额外的排序。如果没有使用，请尝试以下策略：

增加 sort_buffer_size 变量的值。理想情况下，该值应足够大，以便整个结果集都能放入排序缓冲区（以避免写入磁盘和合并传递）。
请注意，存储在排序缓冲区中的列值的大小受 max_sort_length 系统变量值的影响。例如，如果元组存储的是长字符串列的值，并且增加了 max_sort_length 的值，那么排序缓冲区元组的大小也会增加，可能需要增加 sort_buffer_size。
要监控合并通过次数（合并临时文件），请检查 Sort_merge_passes 状态变量。
增加 read_rnd_buffer_size 变量值，以便一次读取更多记录。
更改 tmpdir 系统变量，使其指向具有大量可用空间的专用文件系统。变量值可以列出以循环方式使用的多个路径；你可以利用这一功能将负载分散到多个目录中。在 Unix 系统中，路径之间用冒号字符（:）分隔，在 Windows 系统中，路径之间用分号字符（;）分隔。路径应命名位于不同物理磁盘上的文件系统中的目录，而不是同一磁盘上的不同分区。

ORDER BY 可用的执行计划信息

使用 EXPLAIN（参见第 8.8.1 节 "使用 EXPLAIN 优化查询"），可以检查 MySQL 是否可以使用索引来处理 ORDER BY 子句：

如果 EXPLAIN 输出的 Extra 列不包含使用文件排序，则使用索引，不执行文件排序。
如果 EXPLAIN 输出的 Extra 列包含使用文件排序，则不使用索引，而是执行文件排序。

此外，如果执行了文件排序，优化器的跟踪输出会包含一个 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 表示在排序过程中任何一次使用的最大内存。该值不一定与 sort_buffer_size 系统变量的值一样大。在 MySQL 8.0.12 之前，输出显示的是 sort_buffer_size系统设置值，而不是使用sort_buffer_size 的大小。(在 MySQL 8.0.12 之前，优化器总是为排序缓冲区分配 sort_buffer_size 字节。从 8.0.12 开始，优化器将以增量方式分配排序缓冲区内存，从少量开始，根据需要增加，直至 sort_buffer_size 字节）。

sort_mode 值提供了有关排序缓冲区中元组内容的信息：

<sort_key，rowid>：表示排序缓冲区的元组是包含排序键值和原始表行的行 ID 的键值对。元组按排序键值排序，行 ID 用于从表中读取行。
<sort_key，additional_fields>：表示排序缓冲区元组包含排序键值和查询引用的列。元组按排序键值排序，列值直接从元组读取。
<sort_key，packed_additional_fields>：与前一个变体类似，但附加列不是使用固定长度编码，而是紧密地打包在一起。

EXPLAIN 不会区分优化器是否在内存中执行文件排序。在优化器的跟踪输出中可以看到内存中文件排序的使用。查找 filesort_priority_queue_optimization有关优化器跟踪的信息，请参阅 MySQL Internals：跟踪优化器。

8.2.1.17 GROUP BY 优化

满足 GROUP BY 子句的最一般方法是扫描整个表并创建一个新的临时表，在表中连续记录每个组的所有记录，然后使用该临时表来发现组并应用聚合函数（如果有的话）。在某些情况下，MySQL 可以做得更好，会通过使用索引访问来避免创建临时表。

在 GROUP BY 中使用索引的最重要的前提条件是，所有 GROUP BY 列都引用同一索引中的属性，并且索引按顺序存储其键（例如，BTREE 索引如此，而 HASH 索引则不然）。索引访问是否可以取代临时表的使用，还取决于查询中使用了索引的哪些部分、为这些部分指定的条件以及所选的聚合函数。

通过索引访问执行 GROUP BY 查询有两种方法，下文将详细介绍。第一种方法是将group by操作与所有范围扫描（如果有）一起执行。第二种方法是先执行范围扫描，然后对结果元组进行分组。

松散索引扫描
紧凑索引扫描

在某些情况下，如果没有 GROUP BY，也可以使用松散索引扫描。请参阅跳过扫描范围访问方法。

松散索引扫描

处理 GROUP BY 的最有效方法是使用索引直接检索分组的列。通过这种访问方法，MySQL 使用了某些索引类型的属性，即键是有序的（例如，BTREE）。该属性允许在索引中使用查找组，而无需考虑索引中满足所有 WHERE 条件的所有键。这种访问方法只考虑索引中的部分键，因此称为松散索引扫描。在没有 WHERE 子句的情况下，松散索引扫描读取的键的数量与组的数量相同，但可能比所有键的数量少得多。如果 WHERE 子句包含范围谓词（请参阅第 8.8.1 节 "使用 EXPLAIN 优化查询 "中关于范围连接类型的讨论），松散索引扫描会查找满足范围条件的每个组的第一个键，并再次读取尽可能少的键。在以下条件下可以做到这一点：

查询的是单表。

GROUP BY 只索引最左前缀的列，而不包含其他列。(如果查询不使用 GROUP BY，而是使用 DISTINCT 子句，那么所有不同属性都将指向构成索引最左前缀的列）。例如，如果表 t1 在 (c1,c2,c3)上有一个索引，那么如果查询有 GROUP BY c1, c2，松散索引扫描就适用。如果查询有 GROUP BY c2、c3（列不是最左前缀）或 GROUP BY c1、c2、c4（c4 不在索引中），则松散索引扫描不适用。

查询列表中使用的聚合函数（如果有）只有 MIN() 和 MAX()，而且所有函数都指向同一列。列必须在索引中，并且必须紧跟 GROUP BY 中的列。

除了查询中引用的 GROUP BY 索引外，索引的其他部分必须是常量（即必须在常量等式中引用），MIN() 或 MAX() 函数的参数除外。

对于索引中的列，必须索引完整的列值，而不仅仅是前缀。例如，使用 c1 VARCHAR(20), INDEX (c1(10))，索引只使用 c1 值的前缀，不能用于松散索引扫描。

如果松散索引扫描适用于查询，则 EXPLAIN 会在 Extra 列中显示 Using index for group-by。

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

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

由于上述原因，以下查询无法使用此快速选择方法执行：

除了 MIN() 或 MAX() 之外的其他聚合函数：

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

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

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

查询指向的是键的一部分，该部分位于 GROUP BY 部分之后，且与常量不相等：

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

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

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

支持 AVG(DISTINCT)、SUM(DISTINCT) 和 COUNT(DISTINCT)。AVG(DISTINCT)和 SUM(DISTINCT) 只有一个参数。COUNT(DISTINCT) 可以有多个列参数。
查询中必须没有 GROUP BY 或 DISTINCT 子句。
前面描述的松散索引扫描限制仍然适用。

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

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

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

紧凑索引扫描

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

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

要使用这种方法，只需在查询中的所有列中存在一个常量相等条件，该条件指的是在 GROUP BY 关键字之前或之间的关键字部分。相等条件中的常量会填补搜索键中的任何 "空白"，从而形成完整的索引前缀。这些索引前缀可用于索引查找。如果 GROUP BY 结果需要排序，并且有可能形成作为索引前缀的搜索键，MySQL 也会避免额外的排序操作，因为在有序索引中使用前缀搜索已经按顺序检索了所有键。

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

在 GROUP BY 中有一个空白，但它被条件 c2 = 'a' 所覆盖：

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;

GROUP BY 并非从键的第一部分开始，但有一个条件为该部分提供了一个常数：

SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

8.2.1.18 DISTINCT 优化

在许多情况下，DISTINCT 与 ORDER BY 结合使用时需要一个临时表。

由于 DISTINCT 可能使用 GROUP BY，因此请学习 MySQL 如何处理 ORDER BY 或 HAVING 子句中不属于所选列的列。请参见第 12.19.3 节 "MySQL 处理 GROUP 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 子句的查询。因此，有关 DISTINCT 查询优化可能性的详细信息，请参见第 8.2.1.17 节 "GROUP BY 优化"。

将 LIMIT row_count 与 DISTINCT 结合使用时，MySQL 会在发现 row_count 唯一行时立即停止。

如果不使用查询中指定的所有表中的列，MySQL 会在找到第一个匹配时停止扫描任何未使用的表。在下面的情况中，假设 t1 在 t2 之前使用（可以用 EXPLAIN 检查），MySQL 在找到 t2 中的第一行时，就会停止从 t2 读取（对于 t1 中的任何特定行）：

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

8.2.1.19 LIMIT 查询优化

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

MySQL 有时会优化有 LIMIT row_count 子句而无 HAVING 子句的查询：

如果使用 LIMIT 只选择几条记录，MySQL 会在某些情况下使用索引，而通常情况下它更愿意进行全表扫描。
如果将 LIMIT row_count 与 ORDER BY 结合使用，MySQL 会在找到排序结果中的第一个 row_count 行后立即停止排序，而不是对整个结果进行排序。如果使用索引进行排序，速度会非常快。如果必须进行文件排序，则会在找到第一个行数之前，选择所有与查询匹配且不带 LIMIT 子句的记录，并对其中的大部分或全部记录进行排序。找到初始行后，MySQL 不会对结果集的剩余部分进行排序。

这种行为的一种表现形式是，带 LIMIT 和不带 LIMIT 的 ORDER BY 查询可能以不同的顺序返回记录，本节稍后将对此进行说明。

如果将 LIMIT row_count 与 DISTINCT 结合使用，MySQL 会在发现 row_count 唯一行时立即停止。
在某些情况下，可以通过按顺序读取索引（或对索引进行排序）来解决 GROUP BY 问题，然后计算摘要，直到索引值发生变化。在这种情况下，LIMIT row_count 不会计算任何不必要的 GROUP BY 值。
一旦 MySQL 向客户端发送了所需的行数，它就会中止查询，除非使用的是 SQL_CALC_FOUND_ROWS。在这种情况下，可以使用 SELECT FOUND_ROWS() 来获取记录数。参见第 12.15 节 "信息函数"。
LIMIT 0 可以快速返回空集。这对检查查询的有效性非常有用。在使用可提供结果集元数据的 MySQL API 的应用程序中，也可以使用它来获取结果列的类型。在 mysql 客户端程序中，可以使用 --column-type-info 选项来显示结果列类型。
如果服务器使用临时表来解决查询，它会使用 LIMIT row_count 子句来计算所需空间。
如果 ORDER BY 未使用索引，但同时存在 LIMIT 子句，优化器可能会避免使用合并文件，而是使用内存中的文件排序操作在内存中对行进行排序。

如果多条记录的 ORDER BY 列中有相同的值，服务器可以自由地按任何顺序返回这些记录，并可能根据整体执行计划的不同而采取不同的方式。换句话说，相对于未排序列，这些记录的排序顺序是不确定的。

影响执行计划的一个因素是 LIMIT，因此带 LIMIT 和不带 LIMIT 的 ORDER BY 查询可能以不同的顺序返回记录。请看这条查询，它按类别列排序，但与 id 和评级列无关：

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

包含 LIMIT 可能会影响每个类别值中记录的顺序。例如，这是一个有效的查询结果：

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

在每种情况下，记录都按 ORDER BY 列排序，这也是 SQL 标准所要求的。

如果必须确保有 LIMIT 和无 LIMIT 时的行顺序相同，可以在 ORDER BY 子句中加入额外的列，使顺序具有确定性。例如，如果 id 值是唯一的，则可以通过以下排序方式使给定类别值的记录按 id 排序：

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+

对于带有 ORDER BY 或 GROUP BY 和 LIMIT 子句的查询，当优化器认为选择有序索引会加快查询执行速度时，会默认尝试选择有序索引。在 MySQL 8.0.21 之前，即使在使用其他优化可能更快的情况下，也无法覆盖这种行为。从 MySQL 8.0.21 开始，可以通过将 optimizer_switch 系统变量的 prefer_ordering_index 标志设置为 off 来关闭这种优化。

首先，我们创建并填充一个表 t，如图所示：

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

确认 prefer_ordering_index 标记已启用：

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

由于下面的查询有一个 LIMIT 子句，我们希望它尽可能使用有序索引。在这种情况下，我们可以从 EXPLAIN 输出中看到，它使用了表的主键。

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（其中包括 WHERE 子句中使用的 id2 列）和文件排序：

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

另请参见第 8.9.2 节 "可切换优化"。

8.2.1.20 函数调用优化

MySQL 函数内部标记为确定性或非确定性。如果给定参数的固定值，函数在不同的调用下会返回不同的结果，则该函数为非确定性函数。非确定性函数举例如下rand()、uuid()。

如果函数被标记为非确定性，WHERE 子句中对该函数的引用将针对每一行（从一个表中选择时）或每一行的组合（从多表连接中选择时）进行评估。

MySQL 还会根据参数类型（参数是表的列还是常量值）确定何时评估函数。将表的列作为参数的确定性函数必须在该列值发生变化时进行评估。

非确定性函数可能会影响查询性能。例如，某些优化可能不可用，或者可能需要更多锁定。下面的讨论将使用 RAND()，但也适用于其他非确定函数。

假设表格 t 有这样的定义：

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

这两个查询似乎都使用了主键查找，因为与主键进行了相等比较，但这只适用于第一个查询：

第一次查询最多只能生成一条记录，因为带有常量参数的 POW() 是一个常量值，用于索引查找。
第二个查询包含一个使用非确定函数 RAND() 的表达式，在查询中 RAND() 并非常量，而是对表 t 的每一行都有一个新值。因此，查询读取表中的每一行，对每一行的条件进行求值，并输出主键与随机值匹配的所有行。这可能是 0 行、1 行或多行，具体取决于 id 列值和 RAND() 序列中的值。

非确定性的影响不仅限于 SELECT 语句。这条 UPDATE 语句使用了一个非确定函数来选择要修改的记录：

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

其目的大概是最多更新主键与表达式匹配的一条记录。不过，根据 id 列值和 RAND() 序列中的值，它可能会更新零条、一条或多条记录。

上述行为对性能和复制都有影响：

由于非确定函数不会产生常数恒定值，因此优化器无法使用其他可能适用的策略，例如索引查找。造成的结果很可能是全表扫描。
InnoDB 可能会升级到范围键锁定，而不是对一条匹配记录进行单行锁定。
执行不确定的更新对复制不安全。

困难在于 RAND() 函数对表格的每一行都要计算一次。要避免多次函数求值，可使用以下技术之一：

将包含非确定函数的表达式移到单独的语句中，将值保存在变量中。在原始语句中，用变量引用替换表达式，优化器可将其视为常量值：

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

8.2.1.21 窗口函数优化

8.2.1.22 行构造表达式优化

8.2.1.23 避免全表扫描

posted @ 2023-08-17 19:44 喵喵2023 Views(130) Comments(0) Edit 收藏举报

刷新页面返回顶部

欢迎访问喵喵2023的博客

MySQL 8.0 参考手册——8.2优化 SQL 语句(二)

8.2.1.13 条件过滤

8.2.1.14 常数折叠优化

8.2.1.15 IS NULL 优化

8.2.1.16 ORDER BY 优化

使用索引满足 ORDER BY 的要求

使用文件排序满足 ORDER BY 的要求

影响 ORDER BY 优化

ORDER BY 可用的执行计划信息

8.2.1.17 GROUP BY 优化

松散索引扫描

紧凑索引扫描

8.2.1.18 DISTINCT 优化

8.2.1.19 LIMIT 查询优化

8.2.1.20 函数调用优化

8.2.1.21 窗口函数优化

8.2.1.22 行构造表达式优化

8.2.1.23 避免全表扫描

公告

欢迎访问喵喵2023的博客

MySQL 8.0 参考手册——8.2优化 SQL 语句(二)

8.2.1.13 条件过滤

8.2.1.14 常数折叠优化

8.2.1.15 IS NULL 优化

8.2.1.16 ORDER BY 优化

使用索引满足 ORDER BY 的要求

使用文件排序 满足 ORDER BY 的要求

影响 ORDER BY 优化

ORDER BY 可用的执行计划信息

8.2.1.17 GROUP BY 优化

松散索引扫描

紧凑索引扫描

8.2.1.18 DISTINCT 优化

8.2.1.19 LIMIT 查询优化

8.2.1.20 函数调用优化

8.2.1.21 窗口函数优化

8.2.1.22 行构造表达式优化

8.2.1.23 避免全表扫描

公告

使用文件排序满足 ORDER BY 的要求