《MySQL 实战45讲》–笔记–order by 实现

用 explain 命令查看执行计划的时候,Extra 列的值含有 “using filesort” 表示需要排序,可能是在内存里排序、也可能是磁盘文件排序。MySQL 给每个线程分配一块内存用于排序,称为 sort_buffer ,可以通过参数 sort_buffer_size 调整这个内存的大小。

如果要排序的数据都能放进 sort_buffer 则直接在内存里排序,否则需要借助磁盘临时文件进行辅助排序。

由于 InnoDB 是索引组织表,聚簇索引就是主键索引,下面的描述就用 rowid 替代主键值。

走索引全字段排序

  1. 初始化 sort_buffer,确定要放入 sort_buffer 的目标字段;
  2. 根据选择的索引查找满足条件的 rowid;
  3. 通过rowid到聚簇索引树获取整行数据,取目标字段放入sort_buffer;
  4. 从选择的索引取下一个记录的rowid;
  5. 重复步骤 3、4直到不满足条件;
  6. 对 sort_buffer 中的数据按照排序列进行排序。
  7. 遍历排序结果,把目标数据返回给客户端。

走索引 rowid 排序

如果 MySQL 认为要排序的单行数据太大,可以选择 rowid 排序:

  1. 初始化 sort_buffer,确定目标字段为 主键列 和 排序列。
  2. 从索引中找到第一个满足条件的rowid;
  3. 到主键索引取出整行数据,取出 rowid 和 排序列放入 sort_buffer ;
  4. 从索引去下一个 rowid;
  5. 重复步骤 3、4 直到不满足条件;
  6. 对 sort_buffer 中的数据按照 排序列进行排序;
  7. 遍历排序结果,取出要返回的 rowid,根据 rowid 回表取出 select 子句指定的列返回给客户端。

索引消除排序、需回表的情况

  1. 从索引找到第一个满足条件的 rowid;
  2. 回表取出整行数据,取 select 子句指定的列,作为结果集的一部分直接返回;
  3. 从索引取下一个满足条件的 rowid;
  4. 重复步骤2、3直到不满足条件。

索引覆盖的情况

  1. 从索引找出第一个满足条件的记录,取出 select 子句指定的列作为结果集的一部分直接返回;
  2. 从索引取下一条记录通用取出 select 子句指定的列的值作为结果集的一部分直接返回;
  3. 重复执行步骤2直到没有满足条件的。

查看排序相关的参数值

mysql> show variables like '%sort%';
+--------------------------------+--------------+
| Variable_name                  | Value        |
+--------------------------------+--------------+
| innodb_disable_sort_file_cache | OFF          |
| innodb_ft_sort_pll_degree      | 2            |
| innodb_sort_buffer_size        | 1048576      |
| max_length_for_sort_data       | 4096         |
| max_sort_length                | 1024         |
| myisam_max_sort_file_size      | 107374182400 |
| myisam_sort_buffer_size        | 84934656     |
| sort_buffer_size               | 262144       |
+--------------------------------+--------------+
8 rows in set, 1 warning (0.00 sec)

max_length_for_sort_data 决定排序的行数据的长度。


欢迎关注我的微信公众号: coderbee笔记,可以更及时回复你的讨论。

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据