Skip to content

ORDER BY工作原理

一、 问题背景

  • 带有 ORDER BY 的查询语句，其执行效率如何？MySQL 内部是如何处理排序的？
  • 示例语句: SELECT city, name, age FROM t WHERE city='杭州' ORDER BY name LIMIT 1000; (假设 city 字段有索引)

二、 ORDER BY 的执行流程 (无合适索引时)

  • 当无法利用索引天然有序性来满足 ORDER BY 条件时，MySQL 需要进行显式的排序操作，通常在 EXPLAIN 的 Extra 字段显示 Using filesort。

  • MySQL 为每个线程分配排序内存，由参数 sort_buffer_size 控制。

  • 全字段排序 (Full Field Sort)

    • 流程：
      1. 初始化 sort_buffer，确定放入查询所需的所有字段 (示例中为 city, name, age)。
      2. 通过 city 索引找到满足 city='杭州' 的记录的主键 ID。
      3. 回表到主键索引，取出所有需要的字段 (city, name, age)，存入 sort_buffer。
      4. 重复 2、3 步，直到所有满足条件的行都放入 sort_buffer。
      5. 在 sort_buffer 中按 name 字段进行快速排序。
      6. 取出排序结果的前 1000 行返回给客户端。
    • 内存与磁盘：
      • 如果所有待排序数据能放入 sort_buffer，则在内存中完成排序。
      • 如果数据量超过 sort_buffer_size，则需要使用外部排序：将数据分块存入磁盘临时文件 (Using temporary; Using filesort)，各块排序后，再通过归并排序合并结果。
    • 性能特点：只访问原表一次（回表取数据），后续排序在内存或临时文件进行。但如果查询字段多、行很大，sort_buffer 能容纳的行数少，可能导致大量临时文件，效率下降。
    • OPTIMIZER_TRACE 关键信息：number_of_tmp_files (临时文件数), examined_rows (参与排序行数), sort_mode (如 packed_additional_fields 表示紧凑存储)。

  • rowid 排序 (RowID Sort)

    • 触发条件：当 MySQL 判断单行数据过大（长度超过 max_length_for_sort_data 参数值）时，为了在有限的 sort_buffer 中放入更多行以提高排序效率，会切换到此算法。
    • 流程：
      1. 初始化 sort_buffer，确定只放入排序字段 (name) 和主键 ID (id)。
      2. 通过 city 索引找到满足 city='杭州' 的记录的主键 ID。
      3. 回表到主键索引，取出排序字段 (name) 和主键 ID (id)，存入 sort_buffer。
      4. 重复 2、3 步，直到所有满足条件的行都放入 sort_buffer。
      5. 在 sort_buffer 中按 name 字段进行排序。
      6. 遍历排序结果的前 1000 行。
      7. 对这 1000 行的每一个 id，再次回表到主键索引，取出查询所需的完整字段 (city, name, age) 返回给客户端。
    • 性能特点：排序阶段 sort_buffer 效率更高（行更小，可放更多行，临时文件可能更少），但排序完成后需要额外的回表操作，增加了磁盘 I/O。
    • OPTIMIZER_TRACE 关键信息：sort_mode 变为 <sort_key, rowid>，number_of_tmp_files 可能减少，但查询 Innodb_rows_read 会发现总读取行数增加（多了最后的回表）。

  • MySQL 的选择倾向：

    • 优先全字段排序：如果内存足够 (sort_buffer_size 大，且行不太大)，尽量使用全字段排序，因为它避免了排序后的二次回表，体现了“尽量利用内存，减少磁盘访问”的设计思想。
    • rowid 排序是备选：当行过大时，为了保证排序阶段的内存效率而采用。

三、 优化 ORDER BY：利用索引避免排序

  • 核心思想：如果索引的结构能保证取出的数据天然有序，且顺序与 ORDER BY 要求一致，MySQL 就可以避免 filesort。

  • 创建联合索引 (city, name)

    • 原理：索引先按 city 排序，city 相同再按 name 排序。
    • 流程：
      1. 通过 (city, name) 索引定位到第一个 city='杭州' 的记录。
      2. 由于索引保证了后续记录在 city='杭州' 条件下按 name 有序，直接按顺序遍历索引。
      3. 对遍历到的每条记录，回表到主键索引取 city, name, age。
      4. 取满 1000 条或 city 不再是 '杭州' 时停止。
    • 效果：EXPLAIN 中无 Using filesort。扫描行数减少（只需扫描满足条件的 1000 行，而非所有满足 city='杭州' 的行）。

  • 创建覆盖索引 (city, name, age)

    • 原理：联合索引包含了查询所需的所有字段 (city, name, age)。
    • 流程：
      1. 通过 (city, name, age) 索引定位到第一个 city='杭州' 的记录。
      2. 由于索引本身按 name 有序，且包含了所有需要查询的字段，直接从索引中提取 city, name, age 返回。
      3. 按顺序遍历索引，重复步骤 2。
      4. 取满 1000 条或 city 不再是 '杭州' 时停止。
    • 效果：EXPLAIN 中无 Using filesort，且有 Using index (表示使用了覆盖索引)。无需回表，性能最优。
    • 权衡：覆盖索引性能好，但增加了索引维护成本和存储空间，需根据业务需求权衡。

四、 总结

  • ORDER BY 可能触发 filesort，涉及内存 (sort_buffer) 甚至磁盘临时文件，成本较高。
  • MySQL 有全字段排序和 rowid 排序两种算法，根据行大小和内存决定，优先选择避免二次回表的全字段排序。
  • 优化关键：通过创建合适的联合索引，使数据能按 ORDER BY 的要求从索引中有序地获取，从而避免 filesort。
  • 若能使用覆盖索引，则可进一步避免回表，达到最佳性能。
  • 理解 ORDER BY 的内部机制有助于分析 SQL 性能瓶颈并进行针对性优化。