使用索引扫描来做排序

生成有序结果

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
    因为不满足最左前缀索引