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

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_idG
*************************** 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_idG
+------------+----------------------------------------------+
| table 　 　 　| Extra 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　|
+------------+----------------------------------------------+
| film 　 　 　 | Using index; Using temporary; Using filesort |
| film_actor | Using index 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　 　|
+------------+----------------------------------------------+

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