MySQL使用索引排序

前言

过去我们只知道使用ORDER BY来做排序，那么本章将来介绍MySQL使用索引进行排序，MySQL实现排序共有两种手段，第一种就是通过排序操作，第二种就是通过索引来排序。(关于查询语句中有没有使用带索引排序我们可以通过EXPLAIN执行计划得到type列的值为index，则说明使用到了索引来排序)

支持

MySQL可以使用同一个索引满足排序(ORDER BY)和查询(WHERE)操作,因此如果可以，设计索引的时候尽可能考虑同时满足两种任务

可能更慢

扫描索引本身就很快，因为只需要从一条索引记录移动到紧挨着的下一条记录，(注意：但如果索引不能覆盖查询所需的全部列，那么就不得不没扫描一条索引记录就要回表查询一次对应的行,这里也就是如果不能使用覆盖索引的情况)，因为从索引上回表读取行的记录基本上都是随机I/O，那么这种情况按照索引的顺序随机I/O回表读取行记录，通常比全表扫描更慢，尤其是在I/O密集型的工作负载时

索引排序前提

注意不是瞎鸡巴建的索引都能在排序的时候产生好的效果

• 只有当索引列顺序和ORDER BY的列顺序完全一致，并且排序方式都是一样的情况时才能使用索引对结果做排序
• 如果存在表关联，则只有当ORDER BY引用的字段全部为第一个表是，才能使用索引排序
• 同时ORDER BY使用索引排序的时候是符合和查询语句一样的最左前缀法则的，否则MySQL还是需要执行排序操作，无法利用索引排序

例外

有一种特殊的情况即使不满足最左法则，MySQL也能通过索引来实现排序，如果WHERE或者JOIN中对索引中某些列指定为常量，那么可以弥补这个最左法则！示例如下

如现在有名为rental_date的索引，该索引包含列(rental_date,inventory_id,customer_id),那么MySQL可以使用rental_date索引为下面SQL的查询做排序

EXPLAIN SELECT rental_id,staff_id FROM xxxtab
WHERE rental_date='1999-05-05'
ORDER BY inventory_id,customer_id

即使ORDER BY中不满足最左法则，但是也可以使用索引排序，这是因为索引(rental_date,inventory_id,customer_id)中的第一个列rental_date被指定为常量

其他SQL也是同样的道理

WHERE rental_date='1999-05-05'
ORDER BY inventory_id

WHERE rental_date>'1999-05-05'
ORDER BY rental_date,inventory_id

其他形式则不行

WHERE rental_date='1999-05-05'
ORDER BY inventory_id DESC,customer_id ASC

WHERE rental_date='1999-05-05'
ORDER BY inventory_id ,其他字段

#排序中又不满足最左法则了
WHERE rental_date='1999-05-05'
ORDER BY customer_id 

#rental_date使用了范围查询，索引失效，间接破坏索引
WHERE rental_date>'1999-05-05'
ORDER BY inventory_id,customer_id 

#inventory_id 使用了范围查询，索引失效，间接破坏索引
WHERE rental_date='1999-05-05' AND inventory_id in (xx,xx)
ORDER BY customer_id 

关联查询
下面这个理论上看起来是可以通过索引进行关联排序的，但是由于优化器在优化时将film_actor表当做关联的第第二张表索引实际上是无法使用索引排序的

EXPLAIN SELECT actor_id,title FROM film_actor
INNER JOIN film USING(film_id) ORDER BY actor_id

重要用法

使用索引做排序一个重要的用法就是当查询时同有ORDER BY 和LIMIT的时候！