mysql的sort by学习

ztyzly00 2020-07-18

前沿

学习了mysql的next-key-lock后,现在正式进入sort by的学习阶段。有时在项目里会用到sort by语句。我也听说sort by有可能会对mysql造成压力,所以要学习一下sort by的过程,从而才能深刻的了解自己的sort by语句对mysql的压力,避免线上性能低下或者事故。

场景 

有这么一个场景,要对杭州市民按照姓名排序,取前1000个人。

给city加索引,表设计如下:

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;

然后执行:

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

全字段排序

那接下来引擎层是如何执行的呢?大致过程如下:

1、初始化该sql线程的sort buffer,其大小是sort buffer size决定的。

2、city索引上查找第一个city=‘杭州‘的记录,取出id

3、到主键索引取出select所需的三个字段:city、name和age,插入到sort buffer中;

4、然后继续在city索引上查找下一个记录,重复2、3步,直到city不等于‘杭州‘;

5、在sort buffer中按照name进行排序

6、取出前1000的记录,返回给用户

这边有一个问题,就是如果数据量太大,那么sort buffer不一定能放下。那此时就要借助磁盘辅助排序。mysql在这边做了优化,是将数据放到若干个小的临时文件中,先各自排序,然后合并成一个大的临时文件,应该是类似于归并排序吧。

上述排序的好处是读完原表中数据后只需借助sort buffer和临时文件排序就行了。

row id排序

还有一个问题,就是要返回的当行数据过大时,会导致sort buffer中放不了多少行数据,就不得不借助临时文件排序,影响性能。row id排序可以缓解这种情况。通过如下配置,告知mysql单行超过多大需要使用row id排序。

假设city, name, age三个字段有32字节,而我们设置超过16个字节就采用row id排序。

1 SET max_length_for_sort_data = 16;

row id排序过程大致如下:

1、初始化sort buffer,确定放入两个字段:name和id;

2、从索引 city 找到第一个满足 city=‘杭州’条件的主键 id

3、用主键id在主键索引中查找name字段

4、将id和name放入sort buffer中

5、索引中继续查找下一个满足条件的主键id,重复3、4步骤,直到遇到第一个不满足条件的记录。

6、在sort buffer中按name排序,取前1000个

7、回表,获取这1000个id对应的city和age,返回给用户。

这样做的好处是,同样大小的sort buffer可以放更多的记录,尽可能的在内存中完成操作,代价是会多一次回表取其余字段。

借助联合索引

如果选出来的结果是天然有序的,那么我们就不用再做额外排序了。因为索引是天然有序的,所以可以创建city和name的联合索引,这样选出来的结果自然是按照name排序的了

alter table t add index city_user(city, name);
select name, age, city from t where city=‘杭州‘ limit 1000;

具体的执行过程就不分析了。如果想避免一次回表去读取age字段,也可以讲索引改成如下,这就是覆盖索引。

1 alter table t add index city_user(city, name, age);

相关推荐

whats / 0评论 2011-04-10