专栏首页洁癖是一只狗Mysql如何使用order by工作

Mysql如何使用order by工作

日常开发中,我们经常要进行字段的排序,但是我们大多不知道排序是如何执行的,今天我们就说说order by 的执行逻辑,

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`city` varchar(16) NOT NULL,
`name` varchar(16) NOT NULL,
`age` int(11) NOT NULL,
`addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
KEY `city` (`city`)
) ENGINE=InnoDB

如果我们执行下面语句是如何进行排序的呢

select city,name,age from t where city='杭州' order by name limit 1000  ;

全字段排序

之前我们说过,为了避免全表扫描,我们在city字段上加索引,现在我使用explain命令查看这个语句的执行情况

我们发现extra这个子弹中的Using filesort 表是要进行排序,Mysql为每一个线程分配一块内存用于排序,这个叫sort_buffer.

如图所示,通常情况下,这个语句的流程如下

  1. 初始化sort_buffer,确定放入name,city,age这三个字段
  2. 从索引中找到第一个杭州的主键id
  3. 然后到主键id取出整行(name,age,city),存入sort_buffer中,
  4. 从索引字段中去下一个记录的id
  5. 重复3,4步骤,直到不满足条件
  6. 对sort_buffer中的name字段进行排序
  7. 按照排序结果取前1000条返回给客户端

我们把上面的排序叫全字段排序,执行流程如下

图中nama的排序有可能在内存中完成,也就可能使用外部排序,这个取决于所需的内存和参数sort_buffer_size

sort_buffer_size,就是Mysql为排序开辟的内存的大小,如果排序的数据量小于sort_buffer_size,排序就在内存中排序,如果大于内存大小,就会使用磁盘的临时文件辅助排序,

我们可以使用下面方法,来确定一个排序语句是否使用了临时文件

/* 打开optimizer_trace,只对本线程有效 */
SET optimizer_trace='enabled=on';

/* @a保存Innodb_rows_read的初始值 */
select VARIABLE_VALUE into @a from  performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 执行语句 */
select city, name,age from t where city='杭州' order by name limit 1000;

/* 查看 OPTIMIZER_TRACE 输出 */
SELECT * FROM `information_schema`.`OPTIMIZER_TRACE`\G

/* @b保存Innodb_rows_read的当前值 */
select VARIABLE_VALUE into @b from performance_schema.session_status where variable_name = 'Innodb_rows_read';

/* 计算Innodb_rows_read差值 */
select @b-@a;

这个方法是通过查询OPTIMIZER_TRACE结果来确认,你可以从number_of_tmp_files看到是否使用了临时文件,

number_of_tmp_files表示使用的临时文件数,我们可以理解为mysql在排序的时候把数据分成了12份,每一份单独排序后存在这些临时文件中,然后把12有序文件再合并一个有序的大文件。

如果number_of_tmp_files=0,说明使用的是内存进行排序,我们看到examined_rows=4000.说明参与排序的行数是4000.sort_mode里面packed_additional_fields的意思是说,排序过程对字段进行了紧凑处理,如果字段定义为varchar(16),实际上排序过程中也就是按照字段的实际长度进行排序。

注意的是这里我们设置internal_tmp_disk_storage_engin设置成MyiSAM,否则select @a-@b的结果会显示4001,这是因为如果是innodb,数据从临时表取出也会进行加1操作。

rowid排序

我们可以看到如果查询的字段很多的话,那么sort_buffer存放的字段数太多,就会使用临时文件进行排序,因此造成了很大的浪费,此时mysql任务排序的单行长度会怎么做呢,

首先我要知道如何判断单行长度太大,如下参数

SET max_length_for_sort_data = 16;

我们看到city,name,age总长度为36,远远大于16,因此我们判定单行长度过大,Mysql就会使用另外一种算法进行排序.

新的算法放入sort_buffer的字段,只需要排序的列和主键id,但是这个时候,并不能直接返回排序的结果,而是要回表查询整行。

  1. 初始化sort_buffer,确定放入两个字段,即name和id
  2. 从索引city中找到第一个满足的条件主键id
  3. 再到主键id索引中获取整行,取出name,id两个字段,存入sort_buffer
  4. 在从索引city中到下一个记录id
  5. 重复3,4步骤,知道不满足条件位置
  6. 对sort_buffer进行name排序
  7. 遍历排序结果,取出前1000条记录, 并按照id再到原表获取city,name,age字段返回给客户端

上面的排序算法叫做rowid排序,对比之前的流程,我们发现不同于之前的是多了一次访问表T的步骤,我们可根据上图的步骤,想一下select @b-@a,结果是啥,

首先,图中examined_rows的值还是4000,表示用于排序的数据是4000行,但是select@b-@a这个语句的值变成5000.

因为这个时候除了排序过程外,在排序完成后,还要根据id取原表取值,由于语句是limit 1000,因此会多读1000行。

我们也可以发现sort_mode=sort_key,rowid,表示参与排序只要name和id两个字段.

numner_of_tmp_files=10,那是因为参与排序的行数虽然仍然是4000行,但是每一个行都变小了,因此需要排序的总数量变小了,需要的临时文件相应变少了。

全字段排序和rowid排序

如果msyql实在是担心内存太小,会影响排序效率,才会采取rowid算法,这样排序过程中一次可以排序更多行,但是需要回表取数据。

如果任务内存足够大,会优先选择全字段排序,把需要的字段放入到sort_buffer,这样就会直接从内存里面返回查询结果,不再回表查询数据,

对于innodb来说,rowid排序要求回表造成磁盘读,因此不会优先选择,

看到这里,是不是所有的order by都要进行排序操作,如果不排序就不能获取正确的数据呢,其实,并不是多有的order by 语句,都需要排序,MySQL之所以要使用临时文件排序,是因为原来的数据都是无序的,因此如果本身的从city索引获取的数据就是按照name进行排序的,是不是就可以不用再进行排序呢.

实时上,确实是这样的,我们现在来建立一个(city,name)联合索引,对应的sql语句如下

alter table add index city_user(city,name)

从上面索引看出,我们依然可以用树索引定位到第一个city='杭州'的数据,并且额外确保了,接下来按顺序去下一条记录,只要city=杭州,name就是有序的

  1. 从索引(city,name)找到一个满足city=杭州条件的主键id
  2. 到主键id取到整行,取name,age ,city,作为结果的一部分直接返回
  3. 从索引(city,name)取下一个主键id
  4. 重复2,3步骤,直达查询到1000记录,或者不满足条件循环结束

可以看到这个查询过程不需要临时表,也不需要排序,接下来,我们用explain结果验证一下

发现extra的字段中没有using filesort,也就是不用排序,而且由于(city,name)索引本身就是索引有序,所以这个查询不需要查询4000行数据,只要找到前1000条数据就可以了。

到这里,我是不是还可以进行优化呢,当然是可以的,我们可以使用覆盖索引,覆盖索引是指,索引上的信息足够满足查询请求,不需要再回到主键索引上取数据,

我们按照覆盖索引的概念,建立(city,name.age)联合索引,

alter table add index (city,name,age)

这里如果city相等,还是按照name字段进行递增进行排序的,此时查询语句也就不需要排序了,

  1. 从索引(city,name,age)中找到满足city=杭州的记录,取出city,name,age这三个字段的值,作为结果集的一部分返回
  2. 从索引(city,name,age)取下一个记录,同样取出三个字段的值,作为结果返回
  3. 重复2步骤,直到查到1000记录,或者不满足city=杭州的条件结束循环

然后我们再看explain

可以看到Extra字段里面多了Using index ,表示使用了覆盖索引,性能上会快很多.

本文分享自微信公众号 - 洁癖是一只狗(rookie-dog),作者:洁癖汪

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-10-19

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 面试Mybatis之#和$

    使用 #{} 格式的语法会导致 MyBatis 创建 PreparedStatement 参数占位符并

    小土豆Yuki
  • Mysql如何随机获取表中的数呢rand()

    随机获取数据的业务场景,想必大家都有遇到过,今天我们分析一下如何正确的显示随机消息.

    小土豆Yuki
  • 【红帽认证系列笔记之RHCE】RHCE 认证考试题库(上)

    RHCE的考试相对RHCSA来说要难很多,主要考网络配置,各种文件系统的挂载,web的配置,防火墙策略,数据库查找,脚本编写等等。一共20道题左右。rhce考试...

    小土豆Yuki
  • Mysql中orderby底层执行流程

    爱撒谎的男孩
  • MySQL order by 是怎么工作的?

    这个排序过程叫做全字段排序,因为需要返回的字段都放入了 sort_buffer 参与排序过程。

    dys
  • 在mysql中order by是怎样工作的?

    还有一种就是通过rowId 排序(这种情况是当一行数据过大的时候) 直接上 流程图 :

    居士
  • 定时器篇:定时任务入门小案例

  • JAVA构建高并发商城秒杀系统——架构分析

    我们打算组织一个并发一万人的秒杀活动,1元秒杀100个二手元牙刷,你给我说说解决方案。

    良月柒
  • 为什么要用私钥加签,公钥加密

    之所以用发送方的私钥加签,是因为,即便信息被黑客拦截,黑客修改了信息,但是加签需要用发送方的私钥,黑客没有发送方的私钥,所以也无法生成正确的签名,接收方验签就不...

    yaphetsfang
  • RHEL7.X系列及周边Linux发行版中,关于MBR与GPT的选择一些思考与建议

    存储的选型、规划与管理等工作一直以来都是日常系统运维工作中的重点。MBR与GPT两种类型的分区表的选择与使用则是在磁盘管理中需要根据应用场景来注或考虑的要点。结...

    耕耘实录

扫码关注云+社区

领取腾讯云代金券