MySQL 排序

发表于

开发中经常会遇到根据指定字段排序的需求，通常使用 order by 实现，这篇文章结合极客时间专栏《MySQL 实战 45 讲》中相关的几讲，聊聊 order by 的执行过程。

CREATE TABLE person (
    `id` BIGINT NOT NULL AUTO_INCREMENT, 
    `name` VARCHAR(32) NOT NULL,
    `age` INT NOT NULL,
    `city` VARCHAR(32) NOT NULL,
    PRIMARY KEY (`id`)
    KEY `city` (`city`)
) ENGINE=InnoDB;

INSERT INTO person (name, age, city) VALUES ('Alice', 14, 'Beijing'), ('Bob', 15, 'Beijing'), ('Eve', 9, 'Shanghai');

首先创建表 person 并插入三行数据，后面的测试都在这张表上进行。

全字段排序

EXPLAIN SELECT name, age, city FROM person WHERE city = 'Beijing' ORDER BY name;

id            | 1
select_type   | SIMPLE
table         | person
partitions    | <null>
type          | ref
possible_keys | city
key           | city
key_len       | 130
ref           | const
rows          | 2
filtered      | 100.0
Extra         | Using filesort

从执行计划的 Extra 的值为 Using filesort 可知，上面的语句是需要排序的。MySQL 会给每个线程分配一块内存 sort_buffer 用于排序，上面语句的大概执行流程是：初始化 sort_buffer，确定要放入 name、age、city 三个字段；从 city 索引树搜索到第一个满足条件的 id，回表取出 name、age、city 存入 sort_buffer，继续在 city 索引树搜索直到不满足条件；最后对 sort_buffer 中的数据按照字段 name 做快速排序并返回给客户端。因为把所有需要返回给客户端的字段都放入了 sort_buffer，所以称为「全字段排序」。

当要排序的数据量太大，sort_buffer 放不下时，MySQL 就需要利用临时文件做外部排序了，sort_buffer 的大小由参数 sort_buffer_size 控制，按照下面的方法可以查看排序的详细情况，如果使用了临时文件应该会在 number_of_tmp_files 字段中看到使用的文件数量，这里没有使用临时文件。值得注意的是 sort_mode 字段，其中 packed_additional_fields 表示排序过程对字符串做了「紧凑」处理，即使 name 字段的定义是 VARCHAR(32)，在排序过程中还是要按照实际长度分配内存空间。

-- 打开 optimizer_trace
SET optimizer_trace = 'enabled=on';

-- 执行语句
SELECT name, age, city FROM person WHERE city = 'Beijing' ORDER BY name;

-- 查看 optimizer_trace
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

// 截取的 filesort_summary 结果
{
    "memory_available": 262144,
    "key_size": 64,
    "row_size": 336,
    "max_rows_per_buffer": 762,
    "num_rows_estimate": 963,
    "num_rows_found": 2,
    "num_initial_chunks_spilled_to_disk": 0,
    "peak_memory_used": 32784,
    "sort_algorithm": "std::sort",
    "sort_mode": "<fixed_sort_key, packed_additional_fields>"
}

rowid 排序

上面的算法从表中读出一次数据后，其他操作都在 sort_buffer 和临时文件中进行，但是如果查询要返回的字段很多，sort_buffer 中能放入的行很有限，可能需要分成多个临时文件，这时排序的性能会很差。针对这种参与排序的单行长度太大的情况，MySQL 会采用另外一种算法，它其实是根据参数 max_length_for_sort_data 来判断行是不是太大，把这个参数调小就让 MySQL 使用这种算法了：

SET optimizer_trace = 'enabled=on';
SET max_length_for_sort_data = 16;

SELECT name, age, city FROM person WHERE city = 'Beijing' ORDER BY name;
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

{
    "memory_available": 262144,
    "key_size": 72,
    "row_size": 72,
    "max_rows_per_buffer": 963,
    "num_rows_estimate": 963,
    "num_rows_found": 2,
    "num_initial_chunks_spilled_to_disk": 0,
    "peak_memory_used": 32784,
    "sort_algorithm": "std::sort",
    "unpacked_addon_fields": "max_length_for_sort_data",
    "sort_mode": "<fixed_sort_key, rowid>"
}

可以看到 sort_mode 中原来的 packed_additional_fields 变为 rowid，即新算法只会把要排序的列 name 和主键 id 放入 sort_buffer 中，所以回表操作延迟到将 sort_buffer 中的数据按照 name 排序之后：从 sort_buffer 中依次取出 id 到主键索引树中查到 name、age、city 三个字段的值，这时不需要服务端再耗费内存存储结果，直接返回给客户端即可。

rowid 排序开销相对大一些，因为 sort_buffer 在 server 层，全字段排序只需要读一次数据，即只与存储引擎层交互一下，而 rowid 需要查询一次获取 name 和 id 之后，还要根据 id 再取 name、age、city 的数据，造成更多的磁盘读操作，因此不会被优先选择。

不需要排序

MySQL 排序其实是成本比较高的操作，尤其是使用外部文件时，其实也并是不所有 order by 查询都需要排序的。上面的查询需要排序是因为数据是无序的，如果创建一个 city 和 name 的联合索引，保证同一个 city 下的数据按照 name 有序，理论上之前的查询就不需要排序了，验证一下：

ALTER TABLE person ADD INDEX idx_city_name (city, name);

EXPLAIN SELECT name, age, city FROM person WHERE city = 'Beijing' ORDER BY name;

id            | 1
select_type   | SIMPLE
table         | person
partitions    | <null>
type          | ref
possible_keys | city,idx_city_name
key           | idx_city_name
key_len       | 130
ref           | const
rows          | 2
filtered      | 100.0
Extra         | <null>

从执行计划中可以看出，查询不再需要排序了，上面的查询还需要回表，可以进一步使用覆盖索引进行优化：添加 city、name 和 age 的联合索引，从执行计划的 Extra 中的 Using index 可以看出使用了覆盖索引。

ALTER TABLE person ADD INDEX idx_city_name_city (city, name, age);

EXPLAIN SELECT name, age, city FROM person WHERE city = 'Beijing' ORDER BY name;

id            | 1
select_type   | SIMPLE
table         | person
partitions    | <null>
type          | ref
possible_keys | city,idx_city_name,idx_city_name_city
key           | idx_city_name_city
key_len       | 130
ref           | const
rows          | 2
filtered      | 100.0
Extra         | Using index
0%