前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Mysql如何使用order by工作

Mysql如何使用order by工作

作者头像
小土豆Yuki
发布2020-11-03 11:34:03
1K0
发布2020-11-03 11:34:03
举报
文章被收录于专栏:洁癖是一只狗

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

代码语言:javascript
复制
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

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

代码语言:javascript
复制
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,排序就在内存中排序,如果大于内存大小,就会使用磁盘的临时文件辅助排序,

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

代码语言:javascript
复制
/* 打开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任务排序的单行长度会怎么做呢,

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

代码语言:javascript
复制
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语句如下

代码语言:javascript
复制
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)联合索引,

代码语言:javascript
复制
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 ,表示使用了覆盖索引,性能上会快很多.

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2020-10-19,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 洁癖是一只狗 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档