4.12 优化排序

本节描述了MySQL何时可以使用索引满足ORDER BY子句，无法使用索引时使用的 filesort操作，以及有关优化程序的执行计划信息ORDER BY。

一个ORDER BY有和没有 LIMIT可能以不同的顺序返回行，

使用索引满足ORDER BY

在某些情况下，MySQL可以使用索引来满足 ORDER BY子句，并避免执行filesort 操作时涉及的额外排序。

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

假设在上有一个索引 ，以下查询可以使用该索引来解析 零件。优化程序是否实际这样做取决于如果还必须读取索引中没有的列，则读取索引是否比表扫描更有效。 (*key_part1*, *key_part2*)``ORDER BY

  • 在此查询中，索引on 使优化器避免排序： (*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*有序的，并且如果子句具有足够的选择性以使索引范围扫描比表扫描便宜，则索引on 可以避免排序： (*key_part1*, *key_part2*)``WHERE

    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;

  • 在下一个查询中，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;

  • 查询混合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个字节之后的 值，filesort因此需要a。

  • 索引不按顺序存储行。例如，对于表中的HASH索引来说 就是这样MEMORY。

排序别名的可用性可能会受到使用列别名的影响。假设该列 t1.a已建立索引。在此语句中，选择列表中列的名称为 a。它指的是t1.a，如同在参考a在 ORDER BY，所以上的索引 t1.a可用于：

SELECT a FROM t1 ORDER BY a;

在此语句中，选择列表中列的名称也为a，但它是别名。它指的是ABS(a)，如同在参考a在ORDER BY，所以上的索引t1.a不能使用：

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

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

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

默认情况下，MySQL对查询进行排序，就好像您也包含在查询中一样。如果您包含一个 包含相同列列表的显式子句，则MySQL会对其进行优化，而不会造成任何速度损失，尽管排序仍然会发生。 GROUP BY *col1*, *col2*, ...``ORDER BY *col1*, *col2*, ...``ORDER BY

如果查询包括GROUP BY但希望避免对结果进行排序的开销，则可以通过指定来禁止排序ORDER BY NULL。例如：

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

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

注意

GROUP BY默认情况下隐式排序（即在没有ASC或 列DESC指定GROUP BY符的情况下）。但是，不建议使用隐式GROUP BY排序（即在没有ASC或 DESC指示符的情况下进行排序）或显式排序GROUP BY（即 对列使用显式ASC或DESC指示符GROUP BY）的依赖 。要产生给定的排序顺序，请提供一个 ORDER BY子句。

使用文件排序满足ORDER BY

如果不能使用索引来满足ORDER BY子句，则MySQL执行filesort读取表行并对它们进行排序的 操作。A filesort构成查询执行中的额外排序阶段。

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

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

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

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

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

通过优化影响订单

对于未使用的慢ORDER BY查询 filesort，请尝试将max_length_for_sort_data 系统变量降低 为适合触发的值 filesort。（将此变量的值设置得太高的症状是磁盘活动过多和CPU活动较低的组合。）

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

  • 增加 sort_buffer_size变量值。理想情况下，该值应足够大以使整个结果集适合排序缓冲区（以避免写入磁盘和合并过程），但该值至少必须足够大以容纳15个元组。（最多合并15个临时磁盘文件，并且每个文件中至少必须有一个元组在内存中。）

    考虑到存储在排序缓冲区中的列值的大小受 max_sort_length系统变量值的影响。例如，如果元组存储长字符串列的值，而您增加的值 max_sort_length，则排序缓冲区元组的大小也会增加，并且可能需要您增加 sort_buffer_size。对于由字符串表达式（例如调用字符串值函数的结果）计算出的列值，该filesort算法无法确定表达式值的最大长度，因此必须分配 max_sort_length每个元组的字节数。

    要监视合并次数（合并临时文件），请检查 Sort_merge_passes状态变量。

  • 增加 read_rnd_buffer_size 变量值，以便一次读取更多行。

  • 更改tmpdir 系统变量，使其指向具有大量可用空间的专用文件系统。变量值可以列出以循环方式使用的多个路径。您可以使用此功能将负载分散到多个目录中。:在Unix上用冒号（）和;在Windows上用分号（）分隔路径。这些路径应命名位于不同物理磁盘上的文件系统中的目录 ，而不是同一磁盘上不同的分区。

ORDER BY执行计划信息可用

使用 EXPLAIN ，可以检查MySQL是否可以使用索引来解析ORDER BY子句：

  • 如果输出Extra列 EXPLAIN不包含Using filesort，则使用索引，filesort而不执行a。

  • 如果输出Extra列 EXPLAIN包含 Using filesort，则不使用索引并filesort执行a。

另外，如果filesort执行a，则优化器跟踪输出将包含一个 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值提供有关排序缓冲区中元组内容的信息：

  • <sort_key, rowid>：这表明排序缓冲区元组是对，包含原始表行的排序键值和行ID。元组按排序键值排序，并且行ID用于从表中读取行。

  • <sort_key, additional_fields>：这表明排序缓冲区元组包含排序键值和查询所引用的列。元组通过排序键值进行排序，并且列值直接从元组中读取。

  • <sort_key, packed_additional_fields>：与以前的变体一样，但其他列紧密地包装在一起，而不是使用固定长度的编码。

EXPLAIN不区分优化器是否filesort在内存中执行 。filesort在优化器跟踪输出中可以看到内存的使用 。寻找 filesort_priority_queue_optimization。

Previoussql优化Nextsql优化

Last updated

Was this helpful?