mysql排序怎么优化,mysql 排序慢

MySQL 排序优化

2.1 排序方式

数据量小则在内存排序, 数据量大则使用磁盘排序

内存排序 : 直接使用"快速排序"

磁盘排序 : 先将数据分块, 对每个独立的块使用"快速排序", 并将各个块的排序结果存在磁盘上, 然后将各个排好序的块进行合并(merge), 最后返回排序结果

2.2 排序算法

3. 注意点 :

Mysql using filesort 优化

1、利用有序索引获取有序数据： using index

2、文件排序: using filesort

如果索引不能用于满足 ORDER BY 子句，MySQL 将执行 filesort 读取表行并对它们进行排序的操作。内存大小根据 sort_buffer_size 系统变量来控制(每个thread独享的，同一时刻在mysql可能存在多个sort buffer区域)，如果结果集太大无法在内存中，就会使用临时磁盘文件

1、使用索引并且只选择索引列

如果还必须读取不在索引中的列 select * ，全表扫描快就不走索引了

例子：game_order_info建立复合索引（game_id,source_id）

2、 key_part1 是常量，所以通过索引访问的所有行都是 key_part2 有序的，如果子句有足够的选择性使得索引范围扫描比表扫描便宜，则索引可以避免排序： ( key_part1 , key_part2 )

例子：

3、 key_part1 将与一个常量进行比较。如果 WHERE 子句的选择性足以使索引范围扫描比表扫描的行少，则使用索引

4、 ORDER BY 没有 key_part1 ，但所有选定的行都有一个常 key_part1 量值，因此仍然可以使用索引：

MySQL 5.7及以下版本， GROUP BY 在某些条件下隐式排序。在 MySQL 8.0 中，这种情况不再发生，因此 ORDER BY NULL 不再需要在末尾指定抑制隐式排序。但是，查询结果可能与以前的 MySQL 版本不同。要生成给定的排序顺序，需要 ORDER BY 子句

1、 order by 后的字段使用索引，查询少量的数据，使得在内存中进行排序

2、增加 sort_buffer_size 变量值，结果集排序缓冲区

3、增加 read_rnd_buffer_size 变量值，以便一次读取多行

4、将 tmpdir 系统变量更改为指向具有大量可用空间的专用文件系统

参考：

MySQL 查询优化（六）： MySQL 的查询优化排序优化机制

对结果进行排序操作的代价可能很高，因此可以通过避免排序或让参与排序的数据行更少来优化查询性能。

当 MySQL 不能使用索引产生有序结果时，它必须对数据行进行排序。这有可能是在内存中进行也可能是在磁盘进行，但 MySQL 始终将这个过程称之为 filesort，即便实际上并没有使用一个文件。

如果用于排序的值可以一次性放入排序缓存中，MySQL 可以在内存中使用快排算法进行排序。如果 MySQL 不能在内存中进行排序，则会在磁盘中按块逐块排序。它对每个块使用快排算法，然后在将这些排序好的块合并到结果中。

有两个文件排序（filesort）算法：

很难说哪种算法更有效，对每个算法来说都会有最优和最坏案例。MySQL 在数据表全部列加上用于排序的列的大小不超过 max_length_for_sort_data 时会使用单次遍历算法。可以通过修改这个参数影响排序算法的选择。

需要注意的是，MySQL 的 filesort使用的临时存储空间可能会超出你的预期，这是因为它对每个排序元素都分配了固定大小的存储空间。这些存储空间要足够大以便容下存储最大的元素，而且 VARCHAR这类字段使用的是对应的最大长度。而且，如果使用的是 UTF-8字符集，MuSQL 会对每个字符分配3个字节。结果是，我们会发现那些没怎么优化的查询会导致磁盘上的临时存储空间是数据表自身存储空间的好几倍。

而在对联合查询进行排序时，MySQL 可能会在查询执行过程中执行两次文件排序。如果 ORDER BY 子句只是引用联合查询的第一张表，MySQL 可以先对这个表进行文件排序，然后再处理联合查询。如果是这种情况，在 EXPLAIN 时会在 Extra 字段显示“Using filesort”。而对于其他的排序情况——例如排序不是针对第一张表，或者是 ORDER BY 使用的列对应了不止一个数据表，MySQL 必须使用临时表缓存查询结果，然而在联合查询完成后，再对临时表进行文件排序。在这种情况下，EXPLAIN 会在 Extra 字段显示“Using temorary; Using filesort”。如果包含 LIMIT 约束的话，会发生在文件排序后，因此临时表和文件排序的存储空间可能非常大。

MySQL 5.6在只需要对数据行的子集（例如 LIMIT）进行排序时，引入了一个重大改进。相对于对整个结果集进行排序再返回部分数据，MySQL 有时候会在排序的时候直接丢弃掉不需要的数据行来提高效率。不管怎么样，排序也需要小心使用，很可能会导致存储占用的飙升最终导致系统负荷过大。