MySQL排序原理与优化方法（9/16）_mysql 排序分组临时表磁盘io-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_33876553/article/details/137628592

order by排序优化

MySQL排序策略

内存临时表 or 磁盘临时表？

**内存临时表排序：**在MySQL中，使用InnoDB引擎执行排序操作时，当处理的数据量较小，可以在内存中完成排序时，MySQL会优先使用内存进行排序操作。在这种情况下，MySQL会创建一个临时内存表来存储排序结果，这样可以快速地对数据进行排序，提高查询效率。

**磁盘临时表排序：**如果排序操作需要处理的数据量超过了内存的限制，或者无法在内存中完成排序，MySQL会将数据溢写到磁盘上的临时表中进行排序。这种情况下，MySQL会创建一个磁盘临时表，并可能涉及到磁盘I/O操作，这会增加排序操作的时间。磁盘临时表的排序过程与内存临时表有所不同，可能会采用优先队列排序算法，减少了对临时文件的依赖。

MySQL有几个系统变量会影响排序操作是否使用内存临时表，例如sort_buffer_size和join_buffer_size。

sort_buffer_size指定了MySQL在排序操作中使用的内存缓冲区大小。如果排序操作需要的内存超过这个值，MySQL会使用磁盘临时表。
join_buffer_size用于连接操作的内存缓冲区大小，但在某些情况下，也可以用于排序操作。

MySQL的查询优化器会根据查询的复杂性、数据量和可用内存等因素来决定是否使用内存临时表进行排序。优化器会估算使用内存临时表和磁盘临时表的代价，并选择成本更低的方案。

全字段排序 or rowid排序？

全字段排序：
- 当使用order by对查询结果进行排序时，MySQL会使用sort_buffer进行内存排序。
- 如果排序数据量小于sort_buffer_size参数设置的内存大小，则排序在内存中完成；如果数据量过大，则需要使用外部排序，可能涉及到磁盘临时文件。
rowid排序：
- 当查询的单行数据量max_length_for_sort_data，MySQL会采用rowid排序算法，只将排序用的字段和主键id放入sort_buffer中进行排序，能够减少内存使用。
- 排序完成后，根据排序结果中的主键id回到原表中取出完整的行数据返回给客户端。这里因为涉及到回表过程，性能会下降。

常用优化方法

使用合适的索引：
- 创建合适的索引可以极大地提高排序速度。因为索引本身已经按照特定的顺序存储了数据。如果查询经常根据特定字段进行排序，考虑在这些字段上创建索引。
- 如果查询中的order by子句与某个索引的顺序一致，MySQL可以利用这个索引来避免额外的排序操作。
```
例如，如果有一个索引是(a, b)，那么ORDER BY a, b与索引顺序一致，而ORDER BY b, a则不一致。
```
- 使用覆盖索引，即一个包含所有查询字段的索引，这样可以避免回表查询，减少磁盘I/O操作。
- 尽量避免查询条件无法有效利用索引而导致的全表扫描，因为这会增加排序的数据量。
减少排序数据量：
- 仅选择需要的字段，使用LIMIT子句来限制结果集的大小，避免不必要的数据传输和处理。
调整sort_buffer_size：
- 增加sort_buffer_size参数的值可以增加MySQL在排序过程中使用的内存量，有助于减少磁盘I/O操作，尤其是在处理大量数据时。
调整max_length_for_sort_data参数：
- 如果预期排序的数据行长度较大，可以通过调整max_length_for_sort_data参数来控制MySQL使用rowid排序还是全字段排序。
- max_length_for_sort_data指定了单个数据行的最大长度（以字节为单位），如果单行数据的长度超过这个值，会转而使用rowid排序。
避免不必要的排序操作：
- 如果数据已经是排序好的，或者可以通过索引直接获取有序的数据，那么可以避免使用order by子句。
分析执行计划：
- 使用EXPLAIN命令来分析查询的执行计划，查看是否可以进行优化。
- 使用OPTIMIZER_TRACE方法来进行分析，但可能会导致日志膨胀。