mysql排序的骚操作-索引扫描

mysql 的InnoDB引擎，使用的B+树索引结构，天生的就是有序的；由于这个特性，可以通过合理的使用索引，来避免额外排序操作带来的损耗。下面举例说明

create table rental
(
    rental_id    bigint   default 0 comment 'id',
    rental_date  datetime default now() comment '租赁日期',
    inventory_id bigint   default 0 comment '商品ID',
    customer_id  bigint   default 0 comment '顾客ID',
    staff_id     bigint   default 0 comment '员工ID',
    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)
);

我先来查询下5.25 进行租赁记录按照商品id排序

explain select *
from rental
where rental_date = '2020-05-25'
order by inventory_id;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	rental	null	ref	rental_date	rental_date	6	const	1	100	Using index condition; Using filesort

通过explain知道，查询命中索引rental_date，但是同时使用到 filesort。虽然命中了索引，但是在查询的总损耗中，排序造成了额外的性能消耗。
那么，如果使用索引扫描代替排序的过，我们可以先看下排序时命中索引的规则。

索引的列的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向(倒序或正序)完全一致时，MYSQL采用索引对结果进行排序。

根据上面的规则，从新梳理查询sql，如果想我的排序sql命中rental_date索引，那么我的sql应该是这样。

explain select *
from rental
where rental_date = '2020-05-25'
order by rental_date,inventory_id,customer_id;

执行结果

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	rental		ref	rental_date	rental_date	6	const	1	100	Using index condition

可以看到，此时执行，没有using filesort。也就是使用索引扫描代替了结果排序的动作。

有一个点需要注意，当前导列为常量时，order by 子句不满足最左匹配原则，仍然可以命中索引。仍然以 rental_date (rental_date, inventory_id, customer_id)索引为例。也就是把列rental_date前置为条件的前提下，order by 最左边不是rental_date，也可以认为rental_date在，会命中索引。

explain select *
from rental
where rental_date = '2005-05-25'
order by inventory_id,customer_id;

执行结果与上面相同

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	rental		ref	rental_date	rental_date	6	const	1	100	Using index condition

那么返回到第一条sql，如果sql不变，想让他走索引扫描代替排序操作，需要怎么做呢

CREATE INDEX rental_date_invent on rental(rental_date,inventory_id);