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

在这里插入图片描述
在上述的这个排序过程,我们可以称为 全字段排序

参数 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”表示的就是需要排序。


完毕

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

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

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

在这里插入图片描述

早起的年轻人 CSDN认证博客专家 移动开发 项目管理 Java
只要用心去做,每一件事情还是有可能成功的,当然成功是没有界限的,只不过是达到自己心里的那个目标,公众号:我的大前端生涯,一个爱喝茶的程序员,通常会搞搞SpringBoot 、Herbinate、Mybatiys、Android、iOS、Flutter、Vue、小程序等.
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页