MySql 你知道 order by 是怎么回事吗？ MySql全字段排序与 rowid 排序

vlambda
2021-03-16

MySql 你知道 order by 是怎么回事吗？ MySql全字段排序与 rowid 排序

1 前言

如下我这里有一张抽题记录表，部分建表语句如下：

  
    
    
  
   
     
     
   CREATE TABLE `question_extracting` (
   
     
     
   
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
   
     
     
   
  `question_number` int(11) NOT NULL COMMENT '抽题数',
   
     
     
   
  `total_score` int(11) NOT NULL COMMENT '总分（乘以10以后的值）',
   
     
     
   
  `obtain_score` int(11) DEFAULT NULL COMMENT '得分（乘以10以后的值）',
   
     
     
   
  `user_id` bigint(20) NOT NULL COMMENT '抽取人',
   
     
     
   
  `create_time` datetime NOT NULL COMMENT '创建时间',
   
     
     
   
  PRIMARY KEY (`id`),
   
     
     
   
  KEY `user_id` (`user_id`)
   
     
     
   
) ENGINE=InnoDB AUTO_INCREMENT=4981687 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='抽题记录表';

目前我在这个表中保存了 4000 万左右的数句，如下我执行查询语句：

  
    
    
  
   
     
     
   	SELECT
   
     
     
   
		total_score ,
   
     
     
   
		question_number ,
   
     
     
   
		create_time
   
     
     
   
	FROM
   
     
     
   
		question_extracting
   
     
     
   
	WHERE
   
     
     
   
		user_id = 760
   
     
     
   
	ORDER BY
   
     
     
   
		total_score
   
     
     
   
	LIMIT 1000;

你知道这个过程 order by 是怎么操作的吗？

2 MySql 全字段排序

在这个 question_extracting 抽题表中，我们为 user_id 添加了普通索引，所在在执行上述这个查询时，会走索引查询，

在上述这个查询，MySql 需要对查询结果进行排序，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。

对于上述这个查询，它的查询过程如下：

第一步初始化 sort_buffer 区域，确定放入 total_score、question_number 、create_time 字段
第二步从索引 user_id 中找到第一个满足 user_id='760’ 条件的主键 id
第三步回表到主键 id 索引取出整行，取 total_score、question_number 、create_time 三个字段的值，存入 sort_buffer 中
第四步从索引 user_id 取下一个记录的主键 id
然后重复第三步和第四步直到 user_id 的值不满足查询条件为止
查询结束后，对 sort_buffer 中的数据按照字段 total_score 做快速排序
最后按照排序结果取前 1000 行返回给客户端

MySql 你知道 order by 是怎么回事吗？ MySql全字段排序与 rowid 排序

在上述的这个排序过程，我们可以称为全字段排序。

参数 sort_buffer_size ，MySQL 为排序开辟的内存(sort_buffer)的大小，如果将要排序的数据量小于 sort_buffer_size，排序就在内存中完成；如果排序数据量太大，内存放不下，就需要使用用磁盘临时文件辅助排序，可称为外部排序。

在外部排序中，MySQL 将需要排序的数据分成 N 份，使用参数 number_of_tmp_files 来表示，每一份单独排序后存在这些临时文件中，然后把这 N 个有序文件再合并成一个有序的大文件。

在内存排序中，number_of_tmp_files的值为0，在外部排序中，number_of_tmp_files的值大于0，sort_buffer_size 的值越小，需要排序的数据量就需要分成的份数越多，number_of_tmp_files的值就越大。

3 MySql rowid 排序

MySql rowid 排序应用于当查询要返回的字段很多的时候，这种情况下，使用全字段排序，如果单行很大，排序的数据量也会很大，排序的性能会很差。

参数 max_length_for_sort_data，在 MySQL 中控制用于排序的行数据的长度，如果单行的长度超过这个值，MySQL的排序算法就会将全字段排序切换为 rowid 排序。

SELECT * FROM question_extracting WHERE  user_id = 760 ORDER BY total_score LIMIT 1000;

如在这个查询中，在rowid 排序中，只有要排序的列 total_score 和主键 id 会放入到 sort_buffer 中，它的查询过程如下：

第一步初始化 sort_buffer 区域，确定放入两个字段 total_score、id字段
第二步从索引 user_id 中找到第一个满足 user_id='760’ 条件的主键 id
第三步回表到主键 id 索引取出整行，取 total_score、id两个字段的值，存入 sort_buffer 中
第四步从索引 user_id 取下一个记录的主键 id
然后重复第三步和第四步直到 user_id 的值不满足查询条件为止
查询结束后，对 sort_buffer 中的数据按照字段 total_score 做快速排序
然后从 sort_buffer 中取出排序好的 id ，依次回表查询获取前 1000行

对比全字段排序与 rowid 排序，rowid 排序要比全字段排序多一次回表查询操作，所以对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。

4 explain 命令

用 explain 命令来查看上述查询语句的执行情况

Extra 这个字段中的“Using filesort”表示的就是需要排序。

完毕

不局限于思维，不局限语言限制，才是编程的最高境界。

vlambda博客
学习文章列表