数据库Order By语句优化

Order By语句优化最好的做法是： 利用索引避免排序

哪些情况下Order by字句能用索引

MySQL排序原理

Order by调优原则与技巧

Order by调优实战

 

1、哪些情况下Order by字句能用索引

下图是B+Tree

 

 利用索引本身的有序性，让MySQL跳过排序过程。

 

测试数据准备，如下图所示， employee有个索引index(first_name, last_name)

 

实例1

执行： explain select * from employees order by  first_name, last_name;

返回：

 

 type为All，全表扫描

Extra： using filesort 说明不能使用索引避免排序。

 

执行： explain select * from employees order by first_name, last_name limit 10;

返回：

 

 type为index，说明加了limit ，能够使用索引。

Extra：为空， 说明能使用索引避免排序。

 

总结：为什么select * from employees order by  first_name, last_name; 没有使用索引，因为MySQL优化器发现全表扫描开销更低时， 会直接用全表扫描。

 

实例2

执行： explain select  * from employees where first_name = 'Bader' order by last_name;

返回：

 

 type为ref 使用了索引

extra为Using index condition： 说明可以避免排序

 

执行： explain select  * from employees where first_name < 'Bader' order by first_name;

返回：

 

 type为range ， 说明查询时使用了索引，并且是一个范围查询。

extra为Using index condition： 表示先按条件过滤索引，可以避免排序

 

执行： explain select  * from employees where first_name = 'Bader' and last_name > 'Peng' order by last_name;

返回：

  type为range ， 说明查询时使用了索引，并且是一个范围查询。

extra为Using index condition： 表示先按条件过滤索引，可以避免排序

 

 

执行： explain select  * from employees  order by first_name, emp_no;

返回：

 

   type为All，全表扫描

执行： explain select * from employees order by first_name, emp_no limit 10;

返回：

 

    type为All，全表扫描。

总结： 无法利用索引避免排序 【排序字段存在于多个索引中】

first_name 存在于索引 index (first_name, last_name)

emp_no  存在于主键（索引）

 

执行：explain select  * from employees  order by first_name desc, last_name asc limit  10;

返回：

 

 无法使用索引避免排序 [升降序不一致]

 

无法使用索引避免排序【使用key_part1(first_name)范围查询，使用key_part2（last_name）排序】

执行： explain select  * from employees  where first_name < 'Bader' order by last_name;

返回： 

 

 type为range说明where条件能使用索引

Extra为Using index condition; Using filesort  说明无法使用索引避免排序

 

2、MySQL排序模式

排序模式1  rowid排序(常规排序)

1) 从表中获取满足where条件的记录

2) 对于每条记录，将记录的主键及排序键(id, order_columne)取出放入sort buffer(由sort_buffer_size控制， sort_buffer_size专门做排序的缓存)

3)如果sort buffer 能够存放所有满足条件的(id, order_columne)，则进行排序； 否则sort buffer满后，排序并写到临时文件

    排序算法： 快速排序算法。