Mysql索引扫描排序

使用索引扫描来做排序

生成有序结果

Mysql有两种操作可以用来生成有序结果:
+ 排序操作: 将查找出来的结果使用排序算法进行排序
+ 按索引顺序扫描ORDER BY语句后跟着一个被索引的列,如此一来索引的顺序就是索引对应记录的顺序,这样直接顺着索引一直往下读取记录即可得到有序的结果。

建立用于排序扫描的索引

按照索引顺序扫描的好处是不言而喻的,因为查找出来的结果就是有序结果而无需执行额外的排序操作,这样执行的速度就会相对较快。但是,不是什么时候按照索引扫描的执行速都会是最快的。虽然扫描索引的速度是非常快的,但是如果索引不能覆盖到查询所需要的所有数据列的话,这种情况下每扫描一个索引就必须相对应地回表一次,这样的IO几乎是随机IO,如此一来虽然索引扫描无需执行一次排序算法,但是随机IO操作会大大拖慢执行速度,导致按照索引扫描的执行速度反而要比排序操作要慢。因此,在考虑使用按照索引扫描的方式去获得有序结果,那么设计索引时必须要考虑索引覆盖的情况

什么时候Mysql会使用索引排序?

  • 索引列的顺序与 ORDER BY字句的顺序一致时,并且所有列的排列顺序一样。
    因为多列索引的索引值是综合了多个列计算而得的,且计算的结果与列的顺序有关。例如下面就是一个例子:

    //伪代码
    index_t hash(string x, string y, string z){
       return x.append(y).append(z);
    }

    当执行ORDER BY x, y, z时,实际上就是ORDER BY hash(x, y, z),这样必须保证x, y, z的顺序与建立索引时一致,索引顺序对应的记录顺序才与排序的顺序一致,这样才可以使用索引扫描进行排序。

  • 当查询关联多张表时,则只有当ORDER BY中引用的字段全为第一张表中的字段,才可能使用索引排序。
  • 有一种例外,ORDER BY后跟的字段可以不满足最左前缀原则:当前导量为常量的时候

几个例子

  1. 有一张表rental如下所示:
CREATE TABLE rental{
    ...
    PRIMARY KEY(rental_id),
    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),
    ...
};

执行语句:

EXPLAIN SELECT rental_id, staff_id FROM sakila.rental 
-> WHERE rental_date = '2005-05-5'
-> ORDER BY inventory_id, customer_id\G

输出为:

possible_keys: rental_date
          key: rental_date
         rows: 1
        extra: using where

这里看到extra: using where而不是extra: using filesort表明此次查找并没有排序操作,而是直接通过扫描索引获得有序结果。因为这在这里,虽然ORDER BY语句的字段并没有满足最左前缀的原则,但是由于前导列只定了一个常量,所以可以使用索引扫描进行排序。

  1. 将上述查询改为以下语句也没问题:
EXPLAIN SELECT rental_id, staff_id FROM sakila.rental 
-> WHERE rental_date > '2005-05-5'
-> ORDER BY rental_date, inventory_id, customer_id\G

因为这里虽然没有将前导列指定为常量,但是这里ORDER BY的语句满足了最左前缀原则。

  1. 下列语句不能使用扫描索引得到有序结果:
    • ...WHERE rental_date > '2005-05-5' ORDER BY rental_date DESC, inventory_id ASC
      因为排序方向不一致。
    • ...WHERE rental_date = '2005-05-5' ORDER BY rental_date , staff_id
      因为使用了一个不在索引中的列。
    • ...WHERE rental_date > '2005-05-5' ORDER BY inventory_id
      因为不满足最左前缀索引
阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/u013298300/article/details/52415751
文章标签: mysql 索引
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭