mysql order by 是怎么实现的？

在 MySQL 的 InnoDB 存储引擎中，`ORDER BY` 的执行方式主要取决于**优化器的选择**。一般有两种方式： 1. **索引排序（Index Sorting）**：利用索引的有序性直接获取排序结果，性能较高。 2. **文件排序（Filesort）**：如果不能使用索引排序，MySQL 会使用 **Filesort** 算法在内存或磁盘上排序。 ## **1. 索引排序（Index Sorting）** ### **1.1 利用索引的有序性** - **索引是天然有序的**，如果查询的数据能直接通过索引获取，则不需要额外的排序操作，效率最高。 - 只有满足 **以下条件**，优化器才可能选择索引排序： 1. **ORDER BY 的字段本身有索引**。 2. **查询条件匹配索引的最左前缀**。 3. **ORDER BY 顺序和索引顺序一致**（ASC/DESC）。 4. **如果是联合索引，必须保证 `ORDER BY` 使用的字段顺序和索引顺序匹配**。 ### **1.2 索引排序示例** #### ✅ **可以利用索引排序** ```sql CREATE INDEX idx_name_age ON users (name, age); -- 由于 name 是索引的最左前缀，age 也在索引中，优化器可以使用索引排序 SELECT name, age FROM users WHERE name = 'Alice' ORDER BY age; ``` #### ❌ **不能利用索引排序** ```sql SELECT name, age FROM users ORDER BY age; -- age 不是索引的最左前缀 ``` 此时 MySQL **不会走索引排序**，而是 **Filesort**。 ## **2. Filesort 排序** 当优化器发现 **无法利用索引排序**，或者索引排序的成本过高时，就会选择 **Filesort**。在 `EXPLAIN` 执行计划中，`Extra` 字段会显示： ``` Using filesort ``` ### **2.1 Filesort 的执行方式** - **MySQL 为每个线程分配一块内存（sort_buffer），用于排序**。 - **如果数据量较少**，排序可以直接在 **sort_buffer** 中完成。 - **如果数据量过大**，MySQL 会将数据写入临时文件，并采用**归并排序**。 ## **3. Filesort 的两种排序方式** ### **3.1 全字段排序（Full Field Sort）** - **所有需要查询的字段都放入 `sort_buffer`，然后在内存中直接排序**，排序后直接返回数据。 - **优点**：只需要回表一次。 - **缺点**：占用 `sort_buffer` 过多，如果数据量过大，可能会导致溢出，进而使用磁盘临时文件。 #### **示例** ```sql SELECT id, name, age FROM users WHERE name = 'Alice' ORDER BY age; ``` **执行过程** 1. 取出符合 `name='Alice'` 的数据，放入 `sort_buffer`。 2. 在 `sort_buffer` 里 **按 `age` 排序**。 3. 直接返回排序后的数据。 ✅ **适用于数据量小、字段长度短的情况**。 ### **3.2 row_id 排序（Row ID Sort）** - 由于全字段排序可能占用过多 `sort_buffer`，当数据行的字段**较长**时，MySQL 可能改用 **row_id 排序**： 1. **排序时只存储 `排序字段 + 主键 ID`**。 2. **排序完成后，再回表获取完整数据**。 #### **示例** ```sql SELECT id, name, long_text FROM users WHERE name = 'Alice' ORDER BY age; ``` **执行过程** 1. 先取出 `age` 和 `id`（主键），存入 `sort_buffer`。 2. 在 `sort_buffer` 中 **按 `age` 排序**。 3. **根据排序后的 ID 再次回表查询 `name, long_text`**。 4. 返回最终数据。 ✅ **适用于字段较长的情况，减少 `sort_buffer` 内存占用**。 ## **4. 影响排序方式的关键参数** ### **4.1 `sort_buffer_size`** - **控制 `sort_buffer` 大小**，默认值通常较小。 - **优化建议**： ```sql SET GLOBAL sort_buffer_size = 4M; -- 调大 `sort_buffer_size`，减少磁盘排序 ``` ### **4.2 `max_length_for_sort_data`** - **控制单行数据存入 `sort_buffer` 的最大长度**。 - **默认值**：1024 字节。 - **如果数据长度超出这个值，MySQL 会使用 `row_id` 排序**。 ## **5. `ORDER BY` 选择索引排序还是 Filesort？** | 查询方式 | 是否走索引排序 | 备注 | |-----------|--------------|------| | `ORDER BY` 索引列，符合最左前缀 | ✅ | 索引排序，效率最高 | | `ORDER BY` 非索引列 | ❌ | 使用 Filesort | | `ORDER BY` 多列，但索引顺序不匹配 | ❌ | 使用 Filesort | | `ORDER BY` 索引列，但 `LIMIT` 过大 | ❌ | 可能走 Filesort | | `ORDER BY` 和 `WHERE` 条件组合 | 可能 ✅ | 取决于优化器选择 | ## **6. `ORDER BY` 查询优化建议** ### ✅ **1. 使用索引排序** ```sql CREATE INDEX idx_name_age ON users(name, age); SELECT * FROM users WHERE name = 'Alice' ORDER BY age; -- 走索引排序 ``` ### ✅ **2. 避免 `ORDER BY` 额外字段** 如果 `SELECT` 查询了**非索引字段**，MySQL 可能会选择 **Filesort**： ```sql SELECT * FROM users ORDER BY age; -- age 没有索引，会走 Filesort ``` 优化方式： ```sql ALTER TABLE users ADD INDEX idx_age (age); -- 创建索引，可能走索引排序 ``` ### ✅ **3. `LIMIT` 结合 `ORDER BY`** `ORDER BY ... LIMIT` 可能导致排序大量数据后再截取部分结果： ```sql SELECT * FROM users ORDER BY age LIMIT 10000, 10; -- 可能导致 Filesort ``` 优化方式： ```sql 使用覆盖索引 + `WHERE` 限制范围，减少排序数据量。 ``` ### ✅ **4. 调整 `sort_buffer_size`** 如果 `ORDER BY` 频繁触发 `Filesort`，可**适当增大 `sort_buffer_size`**： ```sql SET GLOBAL sort_buffer_size = 4M; ``` ## **7. 结论** 1. **索引排序**：**如果 `ORDER BY` 的字段可以利用索引，MySQL 会直接使用索引排序，效率最高**。 2. **Filesort 排序**： - **全字段排序**（数据量小，直接在 `sort_buffer` 中排序）。 - **row_id 排序**（数据量大，先按 `id` 排序，再回表查询）。 3. **优化建议**： - **尽可能让 `ORDER BY` 使用索引**。 - **避免查询过多无关字段，减少 `sort_buffer` 压力**。 - **适当调整 `sort_buffer_size` 以减少磁盘排序**。 ✅ **正确使用索引和优化 `ORDER BY`，可以显著提高查询性能！**