Schema的优化和索引 - 高性能的索引策略 - 使用索引扫描来进行排序

MySQL有两种方法生成有序的结果：使用文件排序或者按顺序的扫描索引。你可以使用EXPLAIN来查看type列是否是index来得知MySQL是否计划的扫描索引。

 

扫描索引本身是非常快速的，因为它只是简单的需要从一个索引实体移动到下一个。然而，如果MySQL没有使用索引覆盖这个查询，它就会查找在索引中发现的每一行。这是个随机IO的过程，因此从索引顺序中读取数据比连续表的扫描要慢很多。

 

MySQL可以使用相同的索引来排序和查找行。如果可能，同时满足这两个任务是再好不过的事情了。

 

当索引的顺序和order by条件顺序相同并且所有的列都在同一个方向（升序或降序）时，通过索引排序才能好用。如果查询关联了多张表，所有order by 条件后的列必须是第一张表的情况下，才能使用索引排序。ORDER BY条件也和查询有相同的限制：它需要形成一个左端前缀的索引。在其他的情况下MySQL使用文件排序。

 

有一种情况就是，ORDER BY后的条件并不是最左端的索引前缀，如果WHERE条件或者一个JOIN条件也赋予了这些缺失索引的值，那么依然可以使用索引来排序。

 

比如，rental表有一个索引（rental_date, inventory_id, customer_id）。、

 

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

   ...

);

 

 

MySQL会使用rental_date索引去排序下列查询

 

mysql> EXPLAIN SELECT rental_id, staff_id FROM sakila.rental

    -> WHERE rental_date = '2005-05-25'

    -> ORDER BY inventory_id, customer_id\G

*************************** 1. row ***************************

         type: ref

possible_keys: rental_date

          key: rental_date

         rows: 1

        Extra: Using where

 

 

这是可以使用索引排序的，即使ORDER BY条件并不是最左边的索引前缀。那是因为我们已经在where条件后指定了第一列索引的值。

 

下面也是可以使用索引排序的一些查询。下面的一个可以使用索引，是因为查询已经使用了第一个索引列并且用order by指定了第二个索引列。一起来看，就是一个最左边的索引前缀。

... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;

 

下面的语句也可以，因为order by后的条件也是最左边的索引前缀。

... WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id;

 

下面都是不能使用索引排序的语句：

 

• 这个查询使用了排序的不同方向，但是这个索引列都是升序。
• ... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;

• 下面的语句，ORDER BY 后面的列并不是索引。
• ... WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;

• 下面的语句，并不是最左端的索引前缀。
• ... WHERE rental_date = '2005-05-25' ORDER BY customer_id;

• 这个语句对第一个字段使用了范围查询，所以MySQL就不会使用剩下的索引。
• ... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id;

• 对于inventory_id列有很多相同的条件。对于这种排序，和查询一个范围一样。
• ... WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2) ORDER BY customer_id;

• 下面的语句，从表面上看可以使用索引去排序，但是实际上却不能，因为语句优化器把film_actor表放到了第二的位置。以后再讲这是为什么。
• mysql> EXPLAIN SELECT actor_id, title FROM sakila.film_actor
•     -> INNER JOIN sakila.film USING(film_id) ORDER BY actor_id\G
• +------------+----------------------------------------------+
• | table      | Extra                                        |
• +------------+----------------------------------------------+
• | film       | Using index; Using temporary; Using filesort |
• | film_actor | Using index                                  |
• +------------+----------------------------------------------+

使用索引来排序其中最重要就是一个查询有ORDER BY和LIMIT。这个以后会详细说到。