麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践
1860
2023-05-15
MySQL数据库ORDER BY优化总结(为排序使用索引)
在使用order by时,经常出现Using filesort,所以对于此类sql语句我们需要去尽力优化,使其尽量使用Using index。
那么,我们对于这类型的语句我们怎么去做优化呢?因为这一块还是比较容易混淆的,所以我弄了个实验,相信大家跟我一起做下实验就都能理解了~
1. 环境准备
drop table if exists test; create table test( id int primary key auto_increment, c1 varchar(10), c2 varchar(10), c3 varchar(10), c4 varchar(10), c5 varchar(10) ) ENGINE=INNODB default CHARSET=utf8; insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5'); insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5'); insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5'); insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5'); insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');
2. 创建btree索引
create index idx_c1234 on test(c1,c2,c3,c4); show index from test;
3. 范围扫导致全表扫描
explain select * from test where c1>'a1' order by c1;
分析:
在c1,c2,c3,c4上创建了索引,直接在c1上使用范围,导致了索引失效,全表扫描:type=ALL,ref=Null。因为此时c1主要用于排序,并不是查询。使用c1进行排序,出现了Using filesort。解决方法:使用覆盖索引。
4、覆盖索引--》优化
explain select c1 from testwhere c1>'a1' order by c1;
分析:
使用了覆盖索引,不走全扫,走索引范围扫描排序时按照索引的顺序,所以不会出现Using filesort。
这里不懂没关系,后面我会分享索引的八大法则,保证看得懂...
5. 没有按最左列索引排序
explain select c1 from testwhere c1>'a1' order by c2;
分析:
这里出现了Using filesort,是因为排序用的c2,与索引的创建顺序(c1,c2,c3,c4)不一致。
6. 排序索引列与索引创建的顺序相反
explain select c1 from testwhere c1>'a1' order by c2,c1;
分析:
这里出现了Using filesort。因为排序索引列(c2,c1)与索引创建的顺序(c1,c2)相反,从而产生了重排,也就出现了Using filesort。
7. order by索引列排序不一致
explain select c1 from testwhere c1>'a1' order by c1 asc,c2 desc;
分析:
虽然排序的字段列与索引顺序一样,且order by默认升序,这里c2 desc变成了降序,导致与索引的排序方式不同,从而产生Using filesort。如果是order by c1 asc,c2 asc或者order by c1 desc,c2 desc就会是using index了。
实验总结
1. MySQL支持两种方式的排序filesort和index
Using index是指MySQL扫描索引本身完成排序。index效率高,filesort效率低。
2. 为排序使用索引
假设KEY test(a,b,c)
(1) order by 能使用索引最左前缀
-order by a -order by a,b -order by a,b,c -order by a asc,b asc,c asc -order by a desc,b desc,c desc
(2) 如果where使用索引最左前缀定位为常量,则order by可以使用索引
-where a= const order by b,c -where a= const and b= const order by c -where a= const and b> consst order by b,c
(3) 不能使用索引进行排序
-order by a asc,b desc, c desc /*排序不一致*/ -where g=const order by b,c /*丢失a索引*/ -where a=const order by c /*丢失b索引*/ -where a=const order by a,d /*d不是索引一部分*/ -where a in (....) order by b,c /*对于排序来说,多个相等条件也是范围查询*/
3. filesort有两种排序算法:双路排序和单路排序
双路排序:在MySQL4.1之前使用双路排序,就是两次磁盘扫描,得到最终数据。读取行指针和order by列,对他们进行排序,然后扫描已经排好序的列表,按照列表中的值重新从列表中读取对应的数据输出。即从磁盘读取排序字段,在buffer进行排序,再从磁盘取其他字段。如果使用双路排序,取一批数据要对磁盘进行两次扫描,众所周知,I/O操作是很耗时的,因此在MySQL4.1以后,出现了改进的算法:单路排序。
单路排序:从磁盘中查询所需的列,按照order by列在buffer中对它们进行排序,然后扫描排序后的列表进行输出。它的效率更高一些,避免了第二次读取数据,并且把随机I/O变成了顺序I/O,但是会使用更多的空间,因为它把每一行都保存在内存中了。但当读取数据超过sort_buffer的容量时,就会导致多次读取数据,并创建临时表,最后多路合并,产生多次I/O,反而增加其I/O运算。
解决方式:
增加sort_buffer_size参数的设置。增大max_length_for_sort_data参数的设置。
4. 提升order by速度
在使用order by时,不要用select *,只查询所需的字段。因为当查询字段过多时,会导致sort_buffer不够,从而使用多路排序或进行多次I/O操作。增加sort_buffer_size。增加max_length_for_sort_data。
5. 优化group by
group by与order by很类似,其实质是先排序后分组,遵照索引创建顺序的最佳左前缀法则。当无法使用索引列的时候,也要对sort_buffer_size和max_length_for_sort_data参数进行调整。注意where高于having,能写在where中的限定条件就不要去having限定了。
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。