order by 是怎样排序

Sam_lhs
157 阅读2分钟

参考 juejin.cn/post/704743…

表结构

CREATE TABLE `order` (
id INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT '主键',
user_code VARCHAR ( 16 ) NOT NULL COMMENT '用户编号',
goods_name VARCHAR ( 64 ) NOT NULL COMMENT '商品名称',
order_date TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP COMMENT '下单时间',
city VARCHAR ( 16 ) DEFAULT NULL COMMENT '下单城市',
order_num INT ( 10 ) NOT NULL COMMENT '订单号数量',
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 100 DEFAULT CHARSET = utf8 COMMENT = '商品订单表';

全字段排序

将所有的字段写入buffer中排序后返回

select city, order_num, user_code from `order` where city='广州' 
order by order_num limit 1000;
  • explain 这个语句 , extra 会出现 Using filesort, 这个 filesort 并不是说通过磁盘文件进行排序，而只是告诉我们进行了一个排序操作而已

出现order by关键字 MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

  • 执行过程

  1. 初始化 sort_buffer，放入 city、order_num、user_code 这三个字段；
  2. 从索引 city 找到第一个满足 city=' 广州’条件的主键 id，也就是图中的 ID_3；
  3. 到主键 id 索引取出整行，取 city、order_num、user_code 三个字段的值，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到 city 的值不满足查询条件为止
  6. 对 sort_buffer 中的数据按照字段 order_num 做快速排序；
  7. 按照排序结果取前 1000 行返回给客户端。

排序的操作(第6步)可能在内存中完成, 也可能在文件中完成(外部排序),取决于参数

sort_buffer_size //也就是mysql开辟的sort_buffer大小

MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存顶不住，就得磁盘临时文件辅助排序。

sort_buffer_size尽量调大,尽量减少磁盘临时文件排序可能

rowid 排序

将主键排序后,回表取数据返回

我们需要查询的字段都要放到 sort_buffer 中，如果查询的字段多了起来，内存占用升高，就会很容易打满 sort_buffer 。这时，就要用很多的临时文件辅助排序，导致性能降低。

我们思考的方向应该是降低排序的 单行长度，哪有没有方法能做到呢？

MySQL 之所以走全字段排序是由 max_length_for_sort_data 控制的，它的 默认值是 1024。

show variables like 'max_length_for_sort_data';

max_length_for_sort_data 尽量调大,减少回表次数,rowid排序需要额外回表查询数据

  • 进行rowid排序 本文示例中 city,order_num,user_code 长度 = 16+4+16 =36 < 1024, 所以走的是全字段排序。我们来改下这个参数，改小一点，
SET max_length_for_sort_data = 16;

当单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。原来 city、user_code、order_num 占用的长度是 36，显然放不下全部查询字段了。

这时就要换算法：sort_buffer 只存 order_num 和 id 字段。

  1. 初始化 sort_buffer，确定放入两个字段，即 order_num 和 id；
  2. 从索引 city 找到第一个满足 city=' 广州’条件的主键 id，也就是图中的 ID_3
  3. 回表，取 order_num、id 这两个字段，存入 sort_buffer 中；
  4. 从索引 city 取下一个记录的主键 id；
  5. 重复步骤 3、4 直到不满足 city=' 广州’条件为止，也就是图中的 ID_X；
  6. 对 sort_buffer 中的数据按照字段 order_num 进行排序；
  7. 遍历排序结果，取前 1000 行，再次回表取出 city、order_num 和 user_code
  8. 三个字段返回给客户端。

可以发现:比全字段排序多了1次回表操作,但是sort_buffer不大,不用进行临时文件排序

全字段, rowid排序对比

  1. rowid 排序中，排序过程一次可以排序更多行，但是需要回表取数据。
  2. 对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

索引优化排序

增加符合索引

alter table `order` add index city_order_num_index(city, order_num);
  1. 从索引 (city,order_num) 找到第一个满足 city=' 广州’条件的主键 id；
  2. 回表，取 city、order_num、user_code 三个字段的值，作为结果集的一部分直接返回
  3. 从索引 (city,order_num) 取下一个记录主键 id；
  4. 重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city='广州'条件时循环结束。

因为order_num字段本身就是有序的,所以不需要排序,只需要一次回表：

继续优化

查询字段，加到组合索引中呀，对应到这张表，就是把 user_code 也加到组合索引中：

alter table `order` add index city_order_num_user_code_index(city, order_num, user_code);

因为索引覆盖,不用回表取值,速度最快

没有 where 条件，order by 字段需要加索引吗?

select * from student order by create_time;

无条件查询的话，即使 create_time 上有索引，也不会使用到。因为 MySQL 优化器认为走普通二级索引，再去回表成本比全表扫描排序更高。所以选择走全表扫描，然后根据全字段排序或者 rowid 排序来进行。

avatar
文章
阅读
粉丝