查询性能的优化 - 查询慢的基础知识:优化数据访问

核心提示： EXPLAIN显示了MySQL仅仅要访问10行，换句话说，查询性能的优化 - 查询慢的基础知识:优化数据访问(4)，查询优化器知道选择合适的访问类型来满足这个高效的查询，如果没有合适的索引会怎样呢，一个例子就是使用汇总表， 重写这个复杂的查询，MySQL可能会使用一个性能差点的访问类型，让我们先

EXPLAIN显示了MySQL仅仅要访问10行。换句话说，查询优化器知道选择合适的访问类型来满足这个高效的查询。如果没有合适的索引会怎样呢。MySQL可能会使用一个性能差点的访问类型。让我们先删除索引，再执行这个查询。

mysql> ALTER TABLE sakila.film_actor DROP FOREIGN KEY fk_film_actor_film;

mysql> ALTER TABLE sakila.film_actor DROP KEY idx_fk_film_id;

mysql> EXPLAIN SELECT * FROM sakila.film_actor WHERE film_id = 1\G

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

　　 　 　 　 　 id: 1

　　select_type: SIMPLE

　　 　 　 　table: film_actor

　　 　 　 　 type: ALL

possible_keys: NULL

　　 　 　 　 　key: NULL

　　 　 　key_len: NULL

　　 　 　 　 　ref: NULL

　　 　 　 　 rows: 5073

　　 　 　 　Extra: Using where

和预想的一样，访问类型已经变为全表扫描了（All），并且MySQL检查了5073行来满足这个查询。“Using Where”的意思是，在存储引擎读取行之后，MySQL服务器使用了WHERE条件了过滤行。

一般情况下，MySQL以三种方式来应用一个WHERE，顺序为最佳到最差。

在索引查找操作上来应用条件，便于去掉不匹配的行。这个操作在存储引擎一层。

使用一个覆盖索引（在Extra列显示为“using index”）来避免行的访问，并且在从索引获取结果之后，再过滤掉不匹配的行。这发生在服务器层，但是它不需要从表中读取行。

从表中获取行，之后过滤掉不符合的行。（Extra为“Using Where”）。这发生在服务器层，并且需要服务器在过滤结果之前从表中读取行。

这个例子证明了有个好的索引设计是多么的重要。好的索引帮助你的查询有好的访问类型并且仅仅检查你需要的行。然而，添加一个索引，并不意味着MySQL访问和返回的行数相同。举个例子，一个查询使用了COUNT()聚合函数。

mysql> SELECT actor_id, COUNT(*) FROM sakila.film_actor GROUP BY actor_id;

这个查询仅仅返回了200行。但是它需要读取上千行来构建结果集。像这种查询语句，索引就不能降低要检查的行数了。

不幸的是，MySQL不会告诉你它访问了多少行用来构建结果集。它仅仅告诉你它所访问的行数。其中的一些行可能会被WHERE条件过滤并且不会对结果集有什么贡献。在上个例子中，在移除索引之后，这个查询访问了表中的每一行并且WHERE条件除了那10行外已经全部过滤掉。仅仅剩了10行来创建结果集。要明白服务器访问了多少行，还有有多少行对这个查询有用。

如果你发现有很多的行被检查而结果的行数却很少，你需要做如下的修补

使用覆盖索引，它们存储这你要的数据，因此存储引擎就没必获取全部的行。

改变数据库模型。一个例子就是使用汇总表。

重写这个复杂的查询，因此MySQL优化器能更好的进行优化。（以后会详细讨论）