MySQL排序优化

2024-04-01T22:00:00

排序优化

1 排序优化

问题：在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？

回答：

在MySQL中，支持两种排序方式，分别是 FileSort 和 Index 排序。

  • Index 排序中，索引可以保证数据的有序性，不需要再进行排序， 效率更高。
  • FileSort 排序则一般在 内存中 进行排序，占用 CPU较多。如果待排结果较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率较低。

优化建议：

  1. SQL 中，可以在 WHERE 子句和 ORDER BY 子句中使用索引，目的是在 WHERE 子句中 避免全表扫描 ，在 ORDER BY 子句 避免使用 FileSort 排序 。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
  2. 尽量使用 Index 完成 ORDER BY 排序。如果 WHERE 和 ORDER BY 后面是相同的列就使用单索引列； 如果不同就使用联合索引。
  3. 无法使用 Index 时，需要对 FileSort 方式进行调优。

2 测试

删除student表和class表中已创建的索引。

 # 方式1
 DROP INDEX idx_monitor ON class;
 DROP INDEX idx_cid ON student;
 DROP INDEX idx_age ON student;
 DROP INDEX idx_name ON student;
 DROP INDEX idx_age_name_classId ON student;
 DROP INDEX idx_age_classId_name ON student;
 # 方式2
 call proc_drop_index('atguigudb2','student';)

以下是否能使用到索引， 能否去掉using filesort

过程一： 

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;

EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

过程二： order by 时不limit,索引失效 

#创建索引
CREATE INDEX idx_age_classid_name ON student (age,classid,NAME);

#不限制，索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid;

#增加limit过滤条件，使用上索引了。
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10;

过程三：order by 时顺序错误，索引失效 

#创建索引age,classid,stuno
CREATE INDEX idx_age_classid_stuno ON student (age,classid,stuno);

#以下哪些索引失效？
1：EXPLAIN SELECT * FROM student ORDER BY classid LIMIT 10;

2：EXPLAIN SELECT * FROM student ORDER BY classid,NAME LIMIT 10;

3：EXPLAIN SELECT * FROM student ORDER BY age,classid,stuno LIMIT 10;

4：EXPLAIN SELECT * FROM student ORDER BY age,classid LIMIT 10;

5：EXPLAIN SELECT * FROM student ORDER BY age LIMIT 10;

过程四：order by 时规则不一致，索引失效（顺序错，不索引；方向反，不索引）

根据最左前缀原则，第1,2sql索引失效。

结论：ORDER BY 子句，尽量使用 Index 方式排序，避免使用 FileSort 方式排序

过程五：无过滤，不索引 

EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid;

EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid,name;

EXPLAIN SELECT * FROM student WHERE classid=45 order by age;

EXPLAIN SELECT * FROM student WHERE classid=45 order by age limit 10;

小结:

INDEX 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使用索引的最左前缀定义为常量，则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

不能使用索引进行排序
- 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 案例实战

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。

执行案例前先清除student上的索引，只留主键：

 DROP INDEX idx_age ON student;
 DROP INDEX idx_age_classid_stuno ON student;
 DROP INDEX idx_age_classid_name ON student;
 #或者
call proc_drop_index('atguigudb2','student');

场景:查询年龄为30岁的，且学生编号小于101000的学生，按用户名称排序 

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 
ORDER BY NAME ;

结论：type 是 ALL，即最坏的情况。Extra 里还出现了 Using filesort,也是最坏的情况。可以进行改进。

方案一: 为了去掉filesort我们可以把索引建成 

# 创建索引
alter table student add index idx_age_name(age,name);

# 再次执行
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 
ORDER BY NAME;

方案二：尽量让where的过滤条件和排序使用上索引 

# 建一个三个字段的组合索引
alter table student add index idx_age_stuno_name(age,stuno,name);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 
ORDER BY NAME;

我们发现using filesort依然存在，所以name并没有用到索引，而且type还是range光看名字其实并不美好。原因是，因为stuno是一个范围过滤，所以索引后面的字段不会在使用索引了 。

运行sql发现，运行速度比没有 filesotr效率不差多少。

原因：

所有的排序都是在条件过滤之后才执行的。所以，如果条件过滤掉大部分数据的话，剩下几百几干条数据进行排

序其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。相对的stuo<101000这个条件，如

果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

结论：

1.两个索引同时存在，mysql自动选择最优的方案。（对于这个例子，mysqli选择

idx_age_stuno_name)。但是，随着数据量的变化，选择的索引也会随之变化的。

2.当【范围条件】和【group by或者order by】的字段出现二选一时，优先观察条件字段的

过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段

上。反之，亦然。

4 filesort算法：双路排序和单路排序

排序的字段若不在索引列上，则filesort会有两种算法：双路排序和单路排序

双路排序 （慢）

  • MySQL 4.1之前是使用双路排序 ，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和 order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对 应的数据输出
  • 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段 。

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种 改进的算法，就是单路排序。

单路排序 （快）

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

结论及引申出的问题

  • 由于单路是后出的，总体而言好过双路
  • 但是用单路有问题 在sort_buffer中，单路要比多路多占用很多空间，因为单路是把所有字段都取出，所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小 的数据，进行排序（创建tmp文件，多路合并），排完再取sort_buffer容量大小，再排...从而 多次I/O。
  • 单路本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

优化策略

1. 尝试提高 sort_buffer_size

不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高，因为这个参数是针对每个进程

(connection)的1M-8M之间调整。MySQL5.7,InnoDB存储擎默认值是1048576字节，1MB。

SHOW VARIABLES LIKE '%sort_buffer_size%';

2. 尝试提高 max_length_for_sort_data

提高这个参数，会加用改进算法的概率。

但是如果设的太高，数据总容量超出sort_buffer_size的慨率就增大，明显症状是高的磁盘1/O活动和低的处理器使用率。如果需要返回的列的总长度大于max_length._for_sort_data,使用双路算法，否则使用单路算法。1024-8192字节之间调整。

3. Order by 时select * 是一个大忌。最好只Query需要的字段。

  • 当Queryl的字段大小总和小于max_length_for_sort_data,而且排序字段不是TEXT|BLOB类型时，会用改进后的算法一一单路排序，否则用老算法一一多路排序。
  • 两种算法的数据都有可能超出sort_buffer_size的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O,但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。
当前页面是本站的「Baidu MIP」版。发表评论请点击：完整版 »

© 2018 MIP for Typecho v0.7.5 , Designed by Holmesian.