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

灰太狼 2023-10-07 15:53 13阅读 0赞

> 志在巅峰的攀登者，不会陶醉在沿途的某个脚印之中，在码农的世界里，优美的应用体验，来源于程序员对细节的处理以及自我要求的境界，年轻人也是忙忙碌碌的码农中一员，每天、每周，都会留下一些脚印，就是这些创作的内容，有一种执着，就是不知为什么，如果你迷茫，不妨来瞅瞅码农的轨迹。

*  [优美的音乐节奏带你浏览这个效果的编码过程][Link 1]
 *  [坚持每一天，是每个有理想青年的追求][Link 2]
 *  [追寻年轻人的脚步，也许你的答案就在这里][Link 3]
 *  [如果你迷茫 不妨来瞅瞅这里][Link 4]

--------------------

#### 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 是怎么操作的吗？

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70]

#### 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 行返回给客户端

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 1]  
在上述的这个排序过程，我们可以称为 **全字段排序** 。

参数 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行

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 2]  
**对比全字段排序与 rowid 排序，rowid 排序要比 全字段排序多一次回表查询操作，所以 对于 InnoDB 表来说，rowid 排序会要求回表多造成磁盘读，因此不会被优先选择。**

#### 4 explain 命令 ####

用 explain 命令来查看上述查询语句的执行情况  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 3]  
Extra 这个字段中的“Using filesort”表示的就是需要排序。

--------------------

完毕

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

以小编的性格，肯定是要录制一套视频的，随后会上传

有兴趣 你可以关注一下 [西瓜视频 — 早起的年轻人][Link 5]

![在这里插入图片描述][20201031094959816.gif_pic_center]

[Link 1]: https://www.bilibili.com/video/BV1QX4y1G7Sh/
[Link 2]: https://www.zhihu.com/zvideo/1348960604003180544
[Link 3]: https://www.ixigua.com/6933784858019758595
[Link 4]: https://biglead.blog.csdn.net/article/details/93532582
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20210315181530268.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20210315183641628.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210315190325202.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20210315181809425.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3psMTg2MDM1NDM1NzI=,size_16,color_FFFFFF,t_70
[Link 5]: https://www.toutiao.com/c/user/token/MS4wLjABAAAAYMrKikomuQJ4d-cPaeBqtAK2cQY697Pv9xIyyDhtwIM/
[20201031094959816.gif_pic_center]: https://img-blog.csdnimg.cn/20201031094959816.gif#pic_center