Mysql（5）Order By优化

最新推荐文章于 2024-07-25 23:26:22 发布

java编程小帅

最新推荐文章于 2024-07-25 23:26:22 发布

阅读量1.5k

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 database 后端 java

本文链接：https://blog.csdn.net/qq_37107851/article/details/122715259

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

使用索引来满足 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;

在接下来的两个查询中，将 key_part1 与一个常量进行比较。如果 WHERE 子句的选择性足以使索引范围扫描比表扫描便宜，则使用索引：
```
SELECT * FROM t1
  WHERE key_part1 > constant
  ORDER BY key_part1 ASC;

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

在某些情况下，MySQL 不能使用索引来解析 ORDER BY，尽管它仍然可以使用索引来查找与 WHERE 子句匹配的行。例子：

在不同的索引上使用 ORDER BY的查询：
```
SELECT * FROM t1 ORDER BY key1, key2;
```

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

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

混合了 ASC 和 DESC的查询：

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

用于获取行的索引与 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 输出中第一个没有 const 连接类型的表。）
该查询具有不同的 ORDER BY 和 GROUP BY 表达式。
仅在 ORDER BY 子句中命名的列的前缀上存在索引。在这种情况下，索引不能用于完全解析排序顺序。例如，如果仅索引 CHAR(20) 列的前 10 个字节，则索引无法区分超过第 10 个字节的值，因此需要进行文件排序。
索引不按顺序存储行。例如，这适用于 MEMORY 表中的 HASH 索引。

用于排序的索引的可用性可能会受到使用列别名的影响。假设列 t1.a 已编入索引。在此语句中，选择列表中列的名称是 a。它引用 t1.a，就像 ORDER BY 中对 a 的引用一样，因此可以使用 t1.a 上的索引：

SELECT a FROM t1 ORDER BY a;

在这条语句中，选择列表中列的名称也是a，但它是别名。它引用 ABS(a)，就像 ORDER BY 中对 a 的引用一样，因此不能使用 t1.a 上的索引：

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

默认情况下，MySQL 对 GROUP BY col1, col2, ... 查询进行排序，就好像我们在查询中还包括 ORDER BY col1, col2, ... 一样。如果包含一个包含相同列列表的显式 ORDER BY 子句，MySQL 会优化它而不会降低速度，尽管排序仍然会发生。

如果查询包含 GROUP BY 但我们想避免对结果进行排序的开销，则可以通过指定 ORDER BY NULL 来抑制排序。例如：

INSERT INTO foo
SELECT a, COUNT(*) FROM bar GROUP BY a ORDER BY NULL;

优化器仍然可以选择使用排序来实现分组操作。 ORDER BY NULL 禁止对结果进行排序，而不是通过分组操作来确定结果的先前排序。

GROUP BY 默认隐式排序（也就是说，在 GROUP BY 列没有 ASC 或 DESC 指示符的情况下）。但是，不推荐依赖隐式 GROUP BY 排序（即，在没有 ASC 或 DESC 指示符的情况下进行排序）或对 GROUP BY 进行显式排序（即，通过对 GROUP BY 列使用显式 ASC 或 DESC 指示符）。要生成给定的排序顺序，请提供 ORDER BY 子句。

使用文件排序（filesort）来满足 ORDER BY

如果不能使用索引来满足 ORDER BY 子句，MySQL 会执行文件排序操作，读取表行并对其进行排序。文件排序在查询执行中构成了一个额外的排序阶段。

为了获得用于文件排序操作的内存，优化器会预先分配固定数量的 sort_buffer_size 字节。各个会话可以根据需要更改此变量的会话值，以避免过度使用内存，或根据需要分配更多内存。

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

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

此类查询在仅显示较大结果集中的几行的 Web 应用程序中很常见。例子：

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 活动低的组合。）

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

增加 sort_buffer_size 变量值。理想情况下，该值应该足够大，以使整个结果集适合排序缓冲区（以避免写入磁盘和合并通道），但至少该值必须足够大以容纳 15 个元组。（最多可以合并 15 个临时磁盘文件，并且内存中必须有空间用于每个文件至少一个元组。）
考虑到存储在排序缓冲区中的列值的大小受 max_sort_length 系统变量值的影响。例如，如果元组存储长字符串列的值并且我们增加 max_sort_length 的值，排序缓冲区元组的大小也会增加，并且可能需要我们增加 sort_buffer_size。对于作为字符串表达式的结果计算的列值（例如调用字符串值函数的列值），文件排序算法无法确定表达式值的最大长度，因此它必须为每个元组分配 max_sort_length 个字节。

要监视合并通道的数量（以合并临时文件），请检查 Sort_merge_passes 状态变量。
增加 read_rnd_buffer_size 变量值，以便一次读取更多行。
更改 tmpdir 系统变量以指向具有大量可用空间的专用文件系统。变量值可以列出以循环方式使用的多个路径；我们可以使用此功能将负载分散到多个目录中。在 Unix 上用冒号 (:) 分隔路径，在 Windows 上用分号 (;) 分隔路径。路径应该命名位于不同物理磁盘上的文件系统中的目录，而不是同一磁盘上的不同分区。

ORDER BY 执行计划信息可用

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

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

此外，如果执行文件排序，优化器跟踪输出包括一个 filesort_summary 块。例如：

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

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