SQL高级之order by优化

简介

  • MySQL支持两种方式的排序,FileSort和Index,其中Index的效率较高
  • 他是指MySQL扫描索引本身完成排序。FileSort方式效率较低

使用Index

一般情况下ORDER BY满足两种情况会使用索引排序

  1. ORDER BY语句使用索引最左前列
  2. 使用where子句与order by 子句条件列组合满足索引最左前列
    • where子句中如果出现索引的范围查询(explain中出现range)会导致order by 索引失效

建表

CREATE TABLE tblA(
  id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
  age INT,
  birth TIMESTAMP NOT NULL,
  NAME VARCHAR(200)
);
 
INSERT INTO tblA(age,birth,NAME) VALUES(22,NOW(),'abc');
INSERT INTO tblA(age,birth,NAME) VALUES(23,NOW(),'bcd');
INSERT INTO tblA(age,birth,NAME) VALUES(24,NOW(),'def');
 
CREATE INDEX idx_A_ageBirth ON tblA(age,birth,NAME);
 
SELECT * FROM tblA; 

查询如下
在这里插入图片描述

  • 我们在age、birth、name上面建立了索引

例子

最左前列

EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age;

在这里插入图片描述

EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age,birth;

在这里插入图片描述

EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth;

在这里插入图片描述

EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth,age;

在这里插入图片描述
经过上面的四个例子,我们会发现只要用到了最左前列的索引,那么他就会不会用到filesort排序(前两个)

总结

假设 KEY a_b_c(a,b,c)

order by 能使用索引最左前缀
  • order by a
  • order by a,b
  • order by a,b,c
  • order by a desc ,b desc,c desc
where使用最左前缀

where使用最左前缀定义为常量,则order by能使用索引

  • where a = const order by b,c
  • where a = const and b = const order by c
  • where a = const order by b,c
  • where a = const and b > const order by b,c
不能使用索引排序
  1. order by a asc , b desc , c desc : 排序不一致
  2. where g = const order by b,c:丢失索引a
  3. where a = const order by c:丢失索引b
  4. where a = const order by a,d:d不是索引的一部分
  5. where a in (…) order by b,c:对于排序来说,多个相等的条件也是范围查询

filesort

如果不在索引列上,通常,filesort有两种算法:双路排序和单路排序

双路排序

  1. MYSQL4.1之前是使用的双路排序,字面意思就是两次扫描磁盘,最终得到数据
  2. 去读行指针和order by列,对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中兑取对应的数据输入
  3. 从磁盘中取排序字段,在buffer进行排序,在从磁盘去其他字段

取一批数据,要对磁盘进行两次扫描,总所周知,I/O是非常耗时的,所以在mysql4.1之后,出现了第二种改进的算法,就是单路排序

单路排序

  1. 从磁盘读取查询需要的所有列,按照order by列在buffer对他们进行排序,然后扫描排序后的列表进行输出
  2. 他的效率更快一些,因为避免了第二次读取数据。并且把随机的IO变成了顺序的IO,但是他会使用更多的空间,因为他把每一行都保存在了内存中。

对比

  1. 总体而言,单路好过双路
  2. 但也存在着某种问题,单路排序要比多路排序需要更多的内存空间,有可能超出了sort_buffer的容量,导致了每次只能去sort_buffer容量大小的数据,进行排序(创建tmp文件,多路合并),排完再去sort_buffer容量大小,再排…从而导致多次IO

问题解决

为了解决单路排序的问题,我们可以用下面的措施进行解决

  1. 增大sort_buffer_size参数设置(他是专门用于单路排序内存大小的)
    • 不管用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的
  2. 增大max_length_for_sort_data参数的设置(专门用于单词排序字段大小)
    • 提高这个参数, 会增加用改进算法的概率。但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/O活动和低的处理器使用率.
  3. 去掉select后面不必要的字段,如果select后面的字段多了,排序的时候也会跟着一起,很占内存,去掉没有用的
    • 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时,会用改进后的算法——单路排序, 否则用老算法——多路排序。
    • 两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次I/O,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值