MySQL排序原理与优化方法（9/16）

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排序？

1. 全字段排序：
   • 当使用order by对查询结果进行排序时，MySQL会使用sort_buffer进行内存排序。
   • 如果排序数据量小于sort_buffer_size参数设置的内存大小，则排序在内存中完成；如果数据量过大，则需要使用外部排序，可能涉及到磁盘临时文件。
2. 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方法来进行分析，但可能会导致日志膨胀。