查询优化--ORDER BY查询优化

Mysql 系列文章主页 

===============

ORDER BY 子句,尽量使用 Index 查询,避免使用 FileSort 排序

尽可能在索引列上完成排序操作,遵照索引的最佳左前缀原则

1 准备数据

1.1 建表

DROP TABLE IF EXISTS employee;
CREATE TABLE IF NOT EXISTS employee (
    id INT PRIMARY KEY auto_increment,
    age INT,
    birth TIMESTAMP
);

1.2 插入数据

INSERT INTO employee(age, birth) VALUES(22, NOW());
INSERT INTO employee(age, birth) VALUES(23, NOW());
INSERT INTO employee(age, birth) VALUES(24, NOW());

2 测试&Explain分析

2.1 创建索引

CREATE INDEX idx_AgeBirth on employee(age, birth);

2.2 测试

Case#1:

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

Case#2:

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

Case#3:

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

结果:出现了 Using Filesort

Case#4:

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

结果:出现了 Using Filesort

Case#5:

EXPLAIN SELECT * FROM employee ORDER BY birth;

结果:出现了 Using Filesort

Case#6:

EXPLAIN SELECT * FROM employee WHERE birth > '2018-01-01 00:00:00' ORDER BY birth;

结果:出现了 Using Filesort

Case#7:

EXPLAIN SELECT * FROM employee WHERE birth > '2018-01-01 00:00:00' ORDER BY age;

Case#8:

EXPLAIN SELECT * FROM employee ORDER BY age ASC, birth DESC;

结果:出现了 Using Filesort

2.3 示例性总结

Mysql支持两种方式的排序,Index和Filesort,Index效率高。它指Mysql扫描索引本身完成排序。Filesort效率较低。

Order By 满足两情况,会使用Index方式排序:

  • Order By 语句使用索引最左前列
  • 使用 Where 子句与 Order By 子句条件列组合满足索引最左前列

2.4 Filesort的两种排序算法

2.4.1 双路排序

Mysql 4.1 之前使用的,两次扫描磁盘

2.4.2 单路排序

是对双路排序的改进算法。

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

但有可能出现“偷鸡不成蚀把米”的问题(类似于 Concurrent Mode Failure),这与 Sort Buffer 有很大关系(两个参数:sort_buffer_size & max_length_for_sort_data)

2.5 Group By

Group By 实质是先排序后进行分组,遵照索引的最佳左前缀原则

3 总结

Order By 要尽量使用 Index 排序,避免 Filesort 排序

posted @ 2018-04-24 15:13  cyhbyw  阅读(397)  评论(0编辑  收藏  举报