wp建站系统,华为网站建设建议,广州做网站价格,用wordpress 部署引言
本文翻译自MySQL 官网#xff1a;ORDER BY Optimization#xff0c;MySQL 版本#xff1a;5.7。
这一部分描述了MySQL何时会使用索引来满足order by子句#xff0c;filesort 操作会在索引不能生效的时候被用到#xff0c;以及优化器对order by的执行计划信息。
or…引言
本文翻译自MySQL 官网ORDER BY OptimizationMySQL 版本5.7。
这一部分描述了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 的部分。优化器是否真会这么做取决于如果有一个字段必须查询而它又不在索引中的情况下读取索引是否比全表扫描更有效。
在这个查询中两个字段的复合索引可以使优化器避免排序。
SELECT * FROM t1ORDER BY key_part1, key_part2;
但是查询使用的是select *这可能会查询出比索引列(key_part1 , key_part2)更多的字段。这种情况扫描整个索引并在表中记录里查找哪些不是索引列的开销可能要比扫描整个表然后对结果集排序还要大。如果这样优化器通常就不会使用索引。如果select * 只查询了索引列那么就会用到索引并避免排序操作。
如果表是一个InnoDB 表那么表的主键默认也是索引的一部分那么下面的查询就可以使用索引来解决order by
SELECT pk, key_part1, key_part2 FROM t1ORDER BY key_part1, key_part2;
在这个查询中key_part1是常量所有通过索引访问到的记录都会按照key_part2 来排序并且如果where子句有足够的选择性使得索引范围扫描比全表扫描开销更小的话那么覆盖了(key_part1, key_part2)的复合索引就可以避免排序操作。
SELECT * FROM t1WHERE key_part1 constantORDER BY key_part2;
下面的两个查询和之前没有desc的同一查询索引的使用情况类似。
SELECT * FROM t1ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1WHERE key_part1 constantORDER BY key_part2 DESC;
下面的两个查询key_part1 和一个常量进行比较。如果 WHERE 子句有足够的可选择性来使索引区间扫描比全表扫描更优那么索引就会被使用。
SELECT * FROM t1WHERE key_part1 constantORDER BY key_part1 ASC;SELECT * FROM t1WHERE key_part1 constantORDER BY key_part1 DESC;
下面的查询 ORDER BY 没有使用 key_part1,但是所有被查询的行都有一个常量的 key_part1 的值因此依然会使用索引
SELECT * FROM t1WHERE key_part1 constant1 AND key_part2 constant2ORDER BY key_part2;
某些情况MySQL 不会使用索引来处理 ORDER BY即便仍然会在 WHERE 子句进行匹配操作时用到索引。例如下面这些
查询中的 ORDER BY 使用了不同的 索引
SELECT * FROM t1 ORDER BY key1, key2;
查询中的 ORDER BY 使用了不连续的索引部分
SELECT * FROM t1 WHERE key2constant ORDER BY key1_part1, key1_part3;
查询中的 ORDER BY 混用了 ASC 和 DESC
SELECT * FROM t1 WHERE key2constant ORDER BY key1_part1, key1_part3;
用于查询记录的索引与 ORDER BY 中的索引不是同一个
SELECT * FROM t1 WHERE key2constant ORDER BY key1;
查询中的 ORDER BY 包含了一个表达式这个表达式包含索引列名之外的其他项
SELECT * FROM t1 ORDER BY ABS(key);
SELECT * FROM t1 ORDER BY -key;
查询连接了多张表并且 ORDER BY 中的字段并不都来自于第一个非常量数据表博主如果所有的 ORDER BY 和 GROUP BY子句中的字段都来自同一个表那么这个表在连接时就作为第一张表。这里的意思是说如果 ORDER BY 中的字段来自于不同的表那么就不会用到索引查询中包含不同的 ORDER BY 和 GROUP BY 表达式。ORDER BY 子句中的字段只有字段的前缀有索引。这种情况索引就不会完全参与排序操作。例如有一个列声明为 CHAR(20)但是只有前10个字节建立了索引那么索引就无法区分博主排序最本质的工作就是比较大小这里的区分就是比较的意思剩余的10个字节这时就会使用 filesort 。索引没有以一定的顺序存储记录。例如memory 存储引擎中用到的 HASH 索引就是这种不会对记录进行排序的索引。
另外用于排序的索引究竟可不可用还会受到字段别名的影响。假设 t1 表的 t1.a 字段建立了索引。下面的语句中查询列表中有这个字段 a 。它代表 t1.a 因为 a 已经建立了索引所以下面的语句就会用到索引
SELECT a FROM t1 ORDER BY a;
下面的语句中查询列表依然有 a 列但此 a 非彼 a这个 a 是一个别名。它代表的是 ABS(a)因为 索引是建立在 a 列上所以 t1.a 上的索引不会生效
SELECT ABS(a) AS a FROM t1 ORDER BY a;
下面的语句中 ORDER BY 对 a 进行排序而在查询列表中并没有叫 a 的列。但是 t1 表中有一个列叫做 a因此 ORDER BY 指向的是 t1.a t1.a 上的索引就会生效。当然排序的结果可能与 ORDER BY ABS(a) 的排序完全不同
SELECT ABS(a) AS b 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 排序或显式 GROUP BY 即使用声明的 ASC 或 DESC 来对 GROUP BY 的字段排序排序都是不推荐的。要排序请用 ORDER BY 子句。 二、 使用 filesort 来满足 ORDER BY
如果索引已经无法满足 ORDER BY 子句MySQL 会执行 filesort 操作它的意思是——读取表中数据然后排序。filesort 会在查询执行的时候有额外的排序时间。
为了获取用于 filesort 操作的内存优化器会预先分配一个固定大小为 sort_buffer_size 个字节。每一个session 会话可以通过改变这个值来避免过度的内存消耗或者在必要时分配更多内存。
如果结果集真的大到内存已经无法装下那么 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 活动
为了加快 ORDER BY 的速度检查一下你是否可以使用索引而不是额外的排序时间博主即使用 filesort。如果无法做到尝试下面的优化策略
调大 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 列不包含 Using filesort 那么索引排序生效filesort 不会执行。如果在输出的执行计划中 Extra 列包含 Using filesort那么排序就没有用到索引filesort 就会执行。
另外如果 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 值提供了一些关于排序缓冲区中的元组的信息。
sort_key, rowid 它表示排序缓冲区元组是包含原表行的排序键值和行ID的对。元组被排序键排序记录 ID 被用于从表中读取数据。sort_key, additional_fields它表示排序缓冲区元组包含排序键值和查询涉及到的字段。元组被排序键排序字段值会直接从元组中读取。sort_key, packed_additional_fields与前面的变体类似但是附加的列是紧密地打包在一起的而不是使用固定长度的编码。
EXPLAIN 不会分辨优化器是否会执行内存中的 filesort 。内存 filesort 的使用可以在优化器的跟踪报告中看到。
总结
关于 ORDER BY 的优化部分的确是非常复杂其中比较重要的是关于 ORDER BY 如何利用索引的具体条件。在某些场合下ORDER BY 是不会使用到索引的。
