MySQL ORDER BY 背后的秘密：全字段排序 vs. Rowid 排序

Aesthetis
107 阅读4分钟

我们使用mysql时，一定有个要根据字段排序后的结果返回。这时一定会用到关键字，order by，我们看看这个过程发生了什么，怎么让order by的性能更好，花费mysql资源更少。

以表为例：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

order by发生了什么？

order by的字段，分成两种，非索引字段，以及索引字段

非索引字段

非索引字段，性能差，排序速度慢；使用磁盘文件排序，增加磁盘I/O。

数据排序方式：
  • 全表扫描：MySQL 会对全量数据进行扫描，读取表中的每一行数据。扫描完所有数据后，再根据 ORDER BY 子句里指定的列进行排序操作。
  • 使用临时文件：当数据量较大时，MySQL 无法把所有待排序的数据都存于内存，就会把部分数据存储到磁盘临时文件中，之后再对这些数据进行排序。

索引字段

一般order by会配合where使用，会结合where字段一起创建复合索引。mysql使用索引也有几种不同的方式：全字段排序，rowid排序。

tips: 可以用explain命令，查看这个sql执行过程。 如果有filesort，说明用了文件排序

全字段排序

mysql会分配给每个线程一块空间，用于排序 例子：

select * from t where city = '杭州';

  1. 检查索引覆盖排序：

    • MySQL 首先检查查询中的 ORDER BY 子句是否可以通过现有索引直接满足。如果可以通过索引顺序直接返回排序结果，则不使用 sort_buffer 或 filesort。

  2. 准备 sort_buffer：

    • 如果没有合适的索引来优化排序，MySQL 为每个查询线程分配一块内存空间，称为 sort_buffer，用于存放需要排序的数据。其大小由 sort_buffer_size 参数决定。

  3. 加载数据：

    • MySQL 从表中扫描符合 WHERE 子句条件的记录，将这些记录加载到 sort_buffer 中。

  4. 检查内存是否足够：

    • MySQL 判断 sort_buffer 是否足够容纳所有待排序的数据。
    • 如果 sort_buffer 足够大，可以容纳所有数据，MySQL 将在内存中直接完成排序。

  5. 内存排序：

    • 当所有待排序数据都在 sort_buffer 中时，MySQL 直接在内存中执行排序操作。此时不需要借助磁盘临时文件。

  6. 使用临时文件 (filesort) ：

    • 如果 sort_buffer 无法容纳所有数据，MySQL 会将 sort_buffer 中的数据排序后写入到临时文件中。
    • 这一步可能会重复多次，直到所有数据都被处理完。

  7. 多路归并排序：

    • MySQL 使用多路归并排序算法将所有临时文件合并成一个有序的结果集。这个过程会尽量减少磁盘 I/O 操作。

  8. 结果输出：

    • 排序完成后，MySQL 根据查询要求返回排序后的结果集。

  9. 清理临时文件：

    • 查询执行完成后，MySQL 自动删除所有临时文件以释放磁盘空间。
rowid排序

全字段排序，当sort buffer上字段多了，再做排序，性能会很差：

  1. cpu资源花费多：要做很多swap操作，交换字段
  2. 导致内存竞争：多余字段在内存里
  3. 导致文件排序，性能差：sort buffer空间占用大，只能装得下更少的数据行；用临时文件，排序性能差；间接导致数据局部性原理没办法被利用

对此，mysql的解决方案是：排序后回表，sort buffer只存储索引列和主键id，排序后再到聚簇索引去查询。

全字段排序和 rowid排序的使用场景

排序内存小，影响排序效率，才会采用 rowid 排序算法。这样排序过程中一次可以排序更多行，但需要回表。

内存大，会优先选择全字段排序。排序后会从内存里面返回查询结果了，不用回表。

但特殊的：满足覆盖索引，索引上的信息足够满足查询请求，就不需要再回表。

avatar
文章
阅读
粉丝