「Mysql索引原理（八）」使用索引扫描做排序

源码之路

发布于 2020-09-04 10:12:13

1.2K1

发布于 2020-09-04 10:12:13

MySQL有两种方式可以生成有序的结果：通过排序操作；或者按索引顺序扫描；如果explain出来的type列的值为index，则说明MySQL使用了索引扫描来做排序。

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那就不得不每扫描一条索引记录就得回表查询一次对应的行了。这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的工作负载时。 MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两个任务，这样是最好的。只有当索引的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向都一样时，MySQL才能使用索引结果来做排序。如果查询需要关联多张表，则只有当ORDER BU子句引用的字段全部为第一个表时，才能使用索引做排序。ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求；否则，MySQL都需要执行排序操作，而无法利用索引排序。有一种情况ORDER BY子句可以不满足索引的最左前缀的要求，就是前导列为常量的时候。如果WHERE子句或者JOIN子句中对这些列指定了常量，就可以弥补索引的不足。如，

CREATE TABLE `sakila` (
  `rental_id` int(10) NOT NULL AUTO_INCREMENT,
  `rental_date` date DEFAULT NULL,
  `inventory_id` int(10) DEFAULT NULL,
  `customer_id` int(10) DEFAULT NULL,
  `staff_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`) USING   BTREE,
  KEY `inventory` (`inventory_id`) USING BTREE,
  KEY `customer` (`customer_id`) USING BTREE,
  KEY `staff` (`staff_id`) USING BTREE
) ENGINE=InnoDB ;

MySQL可以使用rental_date索引为下面的查询做排序，从explain中可以看到有没有出现文件排序操作：

explain select rental_id,staff_id from sakila where rental_date='2019-10-10' order by inventory_id,customer_id

即使order by子句不满足索引的最前左缀的要求，也可以哟用于查询排序，这是因为索引的第一列被指定为一个常数。还有更多可以使用索引做排序的查询示例。下面这个查询可以利用索引排序，是因为查询为索引的第一列提供了常量条件，而是用第二列进行排序，将两列组合在一起，就形成了索引的最左前缀：

explain select rental_id,staff_id from sakila where rental_date='2019-10-10' order by inventory_id desc

explain select rental_id,staff_id from sakila where rental_date > '2019-10-10' order by rental_date,inventory_id

这列查询则不能使用索引排序：

1、排序方向不同，而索引列是正序排序
... where rental_date='2019-10-10' order by inventory_id desc,customer_id asc

2、order by子句引用了一个不再索引中的列
... where rental_date='2019-10-10' order by inventory_id desc,staff_id

3、where和order by中的列无法组合成索引的最左前缀
... where rental_date='2019-10-10' order by customer_id asc

4、wehre子句在索引列的第一列上市范围条件，所以MySQL无法使用索引的其余列
... where rental_date>'2019-10-10' order by inventory_id desc,customer_id

5、在inventory_id列上有多个等于条件。对于排序来说，这也是一种范围查询
 ... where rental_date='2019-10-10' and inventory_id in(1,2) order by customer_id

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

数据库