work hard work smart

专注于Java后端开发。 不断总结,举一反三。
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

数据库Order By语句优化

Posted on 2021-02-27 16:17  work hard work smart  阅读(155)  评论(0编辑  收藏  举报

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满后,排序并写到临时文件

    排序算法: 快速排序算法。