MySQL 使用索引扫描进行排序

作者:临时营地 时间:2024-01-25 09:25:15 

目录
  • 安装sakila

  • 索引扫描排序

  • 表结构

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

    • 补足前导列

    • order by 中只包含一种排序

  • 无法使用索引扫描的情况

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

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

    • 无法组合最左前缀时

    • 第一列是查询范围时

    • where中有多个等于条件

  • 总结

    安装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这个表来进行讲解


    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 中只包含一种排序


    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'


    WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id

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

    MySQL 使用索引扫描进行排序

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


    SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id limit 0,10

    对应的执行计划

    MySQL 使用索引扫描进行排序

    可以看到使用了索引

    无法使用索引扫描的情况

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


    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 使用索引扫描进行排序

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


    SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date ='2005-08-23 21:01:09' ORDER BY  inventory_id ,staff_id

    MySQL 使用索引扫描进行排序

    无法组合最左前缀时


    SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date ='2005-08-23 21:01:09' ORDER BY  customer_id

    MySQL 使用索引扫描进行排序

    第一列是查询范围时


    SELECT rental_id, staff_id FROM sakila.rental WHERE rental_date > '2005-08-22' ORDER BY  inventory_id,customer_id

    MySQL 使用索引扫描进行排序

    where中有多个等于条件


    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中的索引扫描排序,明天我们还将继续介绍其他建立高性能索引的方法,敬请期待,下篇再见!

    来源:https://juejin.cn/post/6972047569107189773

    标签:MySQL,索引,扫描,排序
    0
    投稿

    猜你喜欢

  • Linux下通过python获取本机ip方法示例

    2023-02-18 05:56:15
  • Oracle数据库失效对象处理详情

    2023-07-13 16:42:39
  • python实现双向链表原理

    2023-08-11 11:42:11
  • PyCharm+Qt Designer+PyUIC安装配置教程详解

    2024-01-04 12:58:02
  • pyqt实现.ui文件批量转换为对应.py文件脚本

    2022-01-22 03:32:26
  • vue $mount 和 el的区别说明

    2024-04-28 09:20:24
  • python中的字符串切割 maxsplit

    2022-04-16 14:35:35
  • js实现炫酷的左右轮播图

    2024-04-22 22:31:40
  • 4行Python代码生成图像验证码(2种)

    2021-02-08 06:08:03
  • Python提取Word中图片的实现步骤

    2022-11-07 20:25:10
  • 在Golang中使用http.FileServer返回静态文件的操作

    2024-02-20 07:51:50
  • Oracle关于时间/日期的操作

    2009-02-26 10:37:00
  • python 写的一个爬虫程序源码

    2022-08-10 16:00:47
  • Python实现发票自动校核微信机器人的方法

    2023-05-01 07:11:09
  • vue文件树组件使用详解

    2024-05-09 09:53:52
  • Python 忽略文件名编码的方法

    2021-06-11 15:16:55
  • 使用Python的datetime库处理时间(RPA流程)

    2023-04-23 22:53:15
  • perl的格式化输出及chomp的重要性分析

    2022-03-22 11:03:00
  • Python实现Excel自动分组合并单元格

    2021-11-06 03:03:41
  • Python实现的圆形绘制(画圆)示例

    2023-01-09 12:41:36
  • asp之家 网络编程 m.aspxhome.com