mysql order by 排序优化

order by在实际开发环境中使用时还是有很多禁忌的,比如字段的长度,索引的使用,select尽量别用*等,我们来看下order by在MySQL里经历了哪些过程吧,并且学会合理的优化MySQL。

场景

表结构

1
2
3
4
5
6
CREATE TABLE `words` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`class_name` varchar(255) NOT NULL COMMENT '分类名称',
`pid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='行业词库'

根据主键排序,可以看到extra 字段为空

1
explain select *from words order by id

explain1

根据字段排序,extra 的值为 Using filesort

1
explain select * from words order by pid

explain2

下面我们来优化这一条SQL

1
select pid from words where pid = 5  order by class_name

运行流程

上图可见未使用到索引的字段 extra 的值为 Using filesort 是使用到排序了的,索引采用 btree的存储数据自带顺序。在Using filesort的时候MySQL会为每个线程分配一个sort_buffer内存或者文件来进行排序。

排序

进行order by语句的时候,MySQL会优先把数据放进内存分配sort_buffer进行排序。

sort_buffer_size 为sort_buffer使用的内存值,超过会使用临时文件辅佐排序,每个临时文件的大小为sort_buffer_size。

max_length_for_sort_data (列长度值)决定了排序采用的算法是全字段还是rowid排序

查看

1
2
show variables like 'sort_buffer_size';
show variables like 'max_length_for_sort_data';

sort_buffer_size 太大会在并发场景较大的环境下容易造成资源耗尽的情况,建议2kb-2MB之间5.6默认是 848KB

全字段

这种排序方式是把需要用到的数据全部放到sort_buffer中,提升排序速度

eg.

1
select pid from words where pid = 5  order by class_name
  1. 初始化一个sort_buffer 确定放入 class_name,pid两个字段
  2. 找到 “pid = 5” 满足条件的数据的主键ID
  3. 回表主键ID索引取出整行,把class_name,pid放入 sort_buffer
  4. 取出下一个记录的主键ID
  5. 重复3-4
  6. sort_buffer按照class_name字段进行排序
  7. 返回数据给客户端

rowid排序

这种排序方式是应对字段较长,全部放入sort_buffer占用大量内存的情况

eg.

1
select pid from words where pid = 5  order by class_name
  1. 初始化 sort_buffer 确定投入 id,class_name
  2. 找到 “pid = 5” 满足条件的数据的主键ID
  3. 主键ID回表,把id,class_name放入sort_buffer
  4. 取出下一个记录的主键ID
  5. 重复3-4
  6. sort_buffer按照class_name字段进行排序
  7. 拿到 sort_buffer 中的主键ID回表取出 pid返回数据

总结

排序方法 优点 缺点
全字段 减少回表 sort_buffer占用量大
rowid sort_buffer体积小 需要去主键回表

优化

1
select pid from words where pid = 5 order by class_name
  1. 给class_name加上索引,让它变为顺序结构。
  2. 查询中没有class_name,用不到索引。
  3. 加上(pid,class_name)联合索引

联合索引

Q:为什么加class_name字段索引无法用到排序
A:

  1. 根据查找排序步骤,排序会在sort_buffer中进行
  2. 索引简化排序是需要再进入sort_buffer前就阻断掉。
  3. 因此就要把排序值放入where条件中,让MySQL知道需要排序字段的值而不是再去回表取获取
  • 版权声明: 本博客所有文章除特别声明外,著作权归作者所有。转载请注明出处!

请我喝杯咖啡吧~