MySQL 使用索引扫描进行排序

时间:2022-04-29 19:17:04

安装sakila

我们将会使用mysql示例数据库sakila来进行sql的演示和讲解 dev.mysql.com/doc/sakila/…

索引扫描排序

mysql有两种方式可以生成有序的结果:通过排序操作﹔或者按索引顺序扫描﹔如果explain出来的type列的值为“index”,则说明mysql使用了索引扫描来做排序。
扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列,那就不得不每扫描一条索引记录就都回表查询一次对应的行。这基本上都是随机i/o,因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢,尤其是在io密集型的工作负载时。此时可能就会用全表扫描而不是按索引查找了。
如果可能,设计索引时应该尽可能地同时满足排序和查找行。
只有当索引的列顺序和0rder by子句的顺序完全一致,并且所有列的排序方向(倒序或正序)都一样时,mysql才能够使用索引来对结果做排序。如果查询需要关联多张表,则只有当order by子句引用的字段全部为第一个表时,才能使用索引做排序。order by子句和查找型查询的限制是一样的:需要满足索引的最左前缀的要求﹔否则,mysql都需要执行排序操作(filesort),而无法利用索引排序。

表结构

我们将使用rental这个表来进行讲解

?
1
2
3
4
5
6
7
8
create table `rental` (
  
  unique key `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  key `idx_fk_inventory_id` (`inventory_id`),
  key `idx_fk_customer_id` (`customer_id`),
  key `idx_fk_staff_id` (`staff_id`),
  
) engine=innodb auto_increment=16050 default charset=utf8mb4;

查看extra 中是否出现using filesort(mysql中无法利用索引完成的排序操作称为“文件排序”)当我们试图对一个没有索引的字段进行排序时,就是filesort。虽然里面有个file,但它跟文件没有任何关系,实际上是内部的一个快速排序

可以使用索引扫描来做排序的情况

补足前导列

有一种情况下order by子句可以不满足索引的最左前缀的要求,就是前导列为常量的时候。如果where子句或者join子句中对这些列指定了常量,就可以“弥补”索引的不足。 我们使用sakila数据库来测试一下

可以看到

MySQL 使用索引扫描进行排序

书上的extra写的是using where,而我执行的时候是using index condition ,原因是高性能mysql中使用的版本是5.5,5.6版本中的索引条件推送(index condition pushdown)还处于未正式发布阶段呢。这里没有filesort的原因是因为有个rental_date = '2005-05-25'的常量条件,相当于将索引的第一列补足了,这样就符合了索引的最左前缀要求。

order by 中只包含一种排序

?
1
select rental_id, staff_id from sakila.rental where rental_date = '2005-05-25' order by  inventory_id desc

可以看到

MySQL 使用索引扫描进行排序

需要注意这一条,在书中使用的的条件是rental_date>'2005-05-25'

?
1
where rental_date > '2005-05-25' order by rental_date, inventory_id

此时无法使用索引排序而是直接全表扫描做了个排序,原因是因为返回数据的条数过多,用索引查询此时已经不划算了

MySQL 使用索引扫描进行排序

需要注意这里的解释里面的rows并不准确,只是一个估算值,实际上按这个条件查询有16036条数据 要想解决这个问题,就需要加上limit

?
1
select rental_id, staff_id from sakila.rental where rental_date > '2005-05-25' order by rental_date, inventory_id limit 0,10

对应的执行计划

MySQL 使用索引扫描进行排序

可以看到使用了索引

无法使用索引扫描的情况

查询条件中包含不同排序方向

?
1
select rental_id, staff_id from sakila.rental where rental_date = '2005-05-25' order by  inventory_id desc,customer_id asc

索引中两列都是正序,现在order by 中一列正序一列倒序就得二次排序了。

MySQL 使用索引扫描进行排序

查询条件中引用不在索引中的列

?
1
select rental_id, staff_id from sakila.rental where rental_date ='2005-08-23 21:01:09' order by  inventory_id ,staff_id

MySQL 使用索引扫描进行排序

无法组合最左前缀时

?
1
select rental_id, staff_id from sakila.rental where rental_date ='2005-08-23 21:01:09' order by  customer_id

MySQL 使用索引扫描进行排序

第一列是查询范围时

?
1
select rental_id, staff_id from sakila.rental where rental_date > '2005-08-22' order by  inventory_id,customer_id

MySQL 使用索引扫描进行排序

where中有多个等于条件

?
1
select rental_id, staff_id from sakila.rental where rental_date ='2005-08-23 21:01:09' and inventory_id in(1,2)  order by  customer_id

简单来说就是不符合索引最左前缀的就会进行一次排序。

MySQL 使用索引扫描进行排序

总结

今天我们讲解了mysql中的索引扫描排序,明天我们还将继续介绍其他建立高性能索引的方法,敬请期待,下篇再见!

以上就是mysql 索引扫描的简单使用的详细内容,更多关于mysql 索引扫描排序的资料请关注服务器之家其它相关文章!

原文链接:https://juejin.cn/post/6972047569107189773