mysql通过订单量排序_mysql8 参考手册--通过排序优化

最新推荐文章于 2023-03-30 15:06:24 发布

向天再借十厘米

最新推荐文章于 2023-03-30 15:06:24 发布

阅读量276

点赞数

文章标签： mysql通过订单量排序

本文链接：https://blog.csdn.net/weixin_29734285/article/details/113956914

版权

本文详细介绍了MySQL在哪些情况下可以使用索引优化ORDER BY子句，避免文件排序操作。当索引与ORDER BY子句匹配，或者WHERE子句中有常量时，MySQL可能使用索引。优化器会考虑是否读取索引比全表扫描更经济。如果不能使用索引，MySQL会执行文件排序，可能导致额外的磁盘操作。文章还提供了优化ORDER BY性能的建议，包括调整系统变量和检查查询执行计划。

摘要由CSDN通过智能技术生成

本节描述了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;

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

如果查询混合使用ASC和 DESC，并且索引还使用相应的混合升序和降序列，则优化器可以在列上使用索引：

SELECT * FROM t1

ORDER BY key_part1 DESC, key_part2 ASC;

如果降序和升序key_part1，优化器可以在(key_part2) 上使用索引。如果它是升序还是降序，它也可以在这些列上使用索引(向后扫描)。请参见第8.3.13节“降序索引”key_part1key_part2key_part1key_part2

在接下来的两个查询中， key_part1将其与常量进行比较。如果 WHERE子句的选择性足以使索引范围扫描比表扫描便宜，那么将使用索引：

SELECT * FROM t1

WHERE key_part1 > constant

ORDER BY key_part1 ASC;

SELECT * FROM t1

WHERE key_part1 < constant

ORDER BY key_part1 DESC;

在下一个查询中，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输出中没有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 BYRefers t1.a和on上的索引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执行filesort读取表行并对它们进行排序的操作。A filesort构成查询执行中的额外排序阶段。

为了获得用于filesort操作的内存，从MySQL 8.0.12开始，优化器根据需要增量分配内存缓冲区，直到sort_buffer_size系统变量指示的大小为止，而不是sort_buffer_size像MySQL 8.0之前那样预先分配固定数量的字节。 .12。这使用户可以设置sort_buffer_size较大的值来加快较大的排序速度，而不必担心小排序会占用过多的内存。(对于具有弱多线程的Windows上的多个并发排序，可能不会产生此好处malloc。)

一个filesort操作使用临时磁盘文件作为必要的，如果结果集是太大，无法在内存中。某些类型的查询特别适合完全内存filesort操作。例如，优化器可用于 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查询 filesort，请尝试将max_length_for_sort_data 系统变量降低为适合触发的值 filesort。(将此变量的值设置得太高的症状是高磁盘活动和低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子句：

如果输出的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,

"peak_memory_used": 25192,

"sort_mode": ""

}

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值提供有关排序缓冲区中元组内容的信息：

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

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

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

EXPLAIN不区分优化器是否filesort在内存中执行。filesort在优化器跟踪输出中可以看到内存的使用。寻找 filesort_priority_queue_optimization。有关优化程序跟踪的信息，请参见 MySQL内部：跟踪优化程序。