mysql的索引扫描排序与排序操作

 

目录

一、排序方式

二、判断

三、规则

四、举例

---

一、排序方式

mysql有两种方式可以生产有序的结果：
1、通过排序操作；
2、按索引顺序扫描；

二、判断

通过explain出来的type列的值为“index”，则说明mysql使用了索引扫描来做排序。
扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就都回表查询一次对应的行，这基本上都是随机I/O.

三、规则

1）只有当索引的列顺序和order by 子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，mysql才能够使用索引来对结果做排序；
2）如果查询需要关联多张表，则只有当order by 子句引用的字段全部为第一个表时，才能使用索引做排序；
3）order by 子句需要满足索引的最左前缀的要求

四、举例

建表

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)
);

搜索排序：

select rental_id, staff_id from rental
wherer rental_date = '2005-05-25' order by inventory_id, customer_id;

即使order by 子句不满足索引的最左前缀的要求，也可以用于查询排序，这是因为索引的第一列被指定为一个常数。
可以视为将查询列和排序列组合在一起，形成索引的最左前缀。

wherer rental_date = '2005-05-25' order by inventory_id desc;

下面也符合，order by 使用的两列是索引的最左前缀。

wherer rental_date > '2005-05-25' order by rental_date, inventory_id;

下面试一些不能使用索引做排序的查询：

wherer rental_date = '2005-05-25' order by inventory_id desc， customer_id asc;(两种不同的排序方向)
wherer rental_date = '2005-05-25' order by inventory_id, staff_id;（order by引用一个不再索引中的列）
wherer rental_date = '2005-05-25' order by customer_id;(where和order by中的列无法组合成索引的最左前缀)
wherer rental_date > '2005-05-25' order by inventory_id, customer_id;(查询索引是范围条件)
wherer rental_date = '2005-05-25' and inventory_id in(1, 2) order by customer_id;
(inventory_id 列上有多个等于条件，对于排序来说，这也是一种范围查询)