查询性能优化(三)

MySQL 查询优化器的局限性

MySQL 的万能 "嵌套循环" 并不是对每种查询都是最优的。
不过还好，MySQL 查询优化器只对少部分查询不适用，而且我们往往可以通过改写查询让 MySQL 高效地完成工作。
还有一个好消息，MySQL 5.6 版本正式发布后，会消除很多 MySQL 原本的限制，让更多的查询能够以尽可能高的效率完成

关联子查询

MySQL 的子查询实现得非常糟糕。最糟糕的一类查询是 WHERE 条件中包含 IN() 的子查询语句。
例如，我们希望找到 Sakila 数据库中，演员 Peneplope Guiness(他的 actor_id 为 1)参演过的所有影片信息。很自然的，我们会按照下面的方式用子查询实现:

SELECT * FROM sakila.film
WHERE film_id IN (
    SELECT film_id FROM sakila.film_actor WHERE actor_id = 1
);

因为 MySQL 对 IN() 列表中的选项有专门的优化策略，一般会认为 MySQL 会先执行子查询返回所有包含 actor_id 为 1 的 film_id。
一般来说，IN() 列表查询速度很快，所以我们会认为上面的查询会这样执行:

-- SELECT GROUP_CONCAT(film_id) FROM sakila.film_actor WHERE actor_id = 1;
-- Result: 1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980
SELECT * FROM sakil.film
WHERE film_id
IN(1,23,25,106,140,166,277,361,438,499,506,509,605,635,749,832,939,970,980);

很不幸，MySQL 不是这样做的。MySQL 会将相关的外层表压到子查询中，它认为这样可以更高效率地查找到数据行。也就是说，MySQL 会将查询改写成下面的样子:

SELECT * FROM sakila.film
WHERE EXISTS (
    SELECT * FROM sakila.film_actor WHERE actor_id = 1
    AND film_actor.film_id = film.film_id
);

这时，子查询需要根据 film_id 来关联外部表 film，因为需要 film_id 字段，所以 MySQL 认为无法先执行这个子查询。
通过 EXPLAIN 我们可以看到子查询是一个相关子查询(DEPENDENT SUBQUERY)(可以使用 EXPLAIN EXTENDED 来查看这个查询被改写成了什么样子):

explain子查询1

根据 EXPLAIN 的输出我们可以看到，MySQL 先选择对 film 表进行全表扫描，然后根据返回的 film_id 逐个执行子查询。
如果是一个很小的表，这个查询糟糕的性能可能还不会引起注意，但是如果外层的表是一个非常大的表，那么这个查询的性能会非常糟糕。
当然我们很容易用下面的办法来重写这个查询:

1
2
3

SELECT film.* FROM sakila.film
INNER JOIN sakila.film_actor USING(film_id)
WHERE actor_id = 1;

另一个优化的方法是使用函数 GROUP_CONCAT() 在 IN() 中构造一个由逗号分隔的列表。有时这比上面的使用关联改写更快。
因为使用 IN() 加子查询，性能经常会非常糟，所以通常建议使用 EXISTS() 等效的改写查询来获取更好的效率。

如何用好关联子查询

并不是所有关联子查询的性能都会很差。如果有人跟你说: "别用关联子查询"，那么不要理他。先测试，然后做出自己的判断。
很多时候，关联子查询是一种非常合理、自然，甚至是性能最好的写法。我们看看下面的例子:

EXPLAIN SELECT film_id, language_id FROM sakila.film
WHERE NOT EXISTS (
    SELECT * FROM sakila.film_actor
    WHERE film_actor.film_id = film.film_id
)\G

id: 1
select_type: PRIMARY
table: film
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra: Using where

id: 2
select_type: DEPENDENT SUBQUERY
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: film.film_id
rows: 2
Extra: Using where; Using index

一般会建议使用左外连接(LEFT OUTER JOIN)重写该查询，以代替子查询。理论上，改写后 MySQL 的执行计划完全不会改变。我们来看这个例子

EXPLAIN SELECT film.film_id, film.language_id
FROM sakila.film
LEFT OUTER JOIN sakila.film_actor USING(film_id)
WHERE film_actor.film_id IS NULL\G

id: 1
select_type: SIMPLE
table: film
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 951
Extra:

id: 1
select_type: SIMPLE
table: film_actor
type: ref
possible_keys: idx_fk_film_id
key: idx_fk_film_id
key_len: 2
ref: sakila.film.film_id
rows: 2
Extra: Using where; Using index; Not exists

可以看到，这里的执行计划基本上一样，下面是一些微小的区别:

表 film_actor 的访问类型一个是 DEPENDENT SUBQUERY，而另一个是 SIMPLE。这个不同是由于语句的写法不同导致的，一个是普通查询，一个是子查询。这对底层存储引擎接口来说，没有任何不同
对 film 表，第二个查询的 Extra 中没有 "Using where", 但这不重要，第二个查询的 USING 子句和第一个查询的 WHERE 子句实际上是完全一样的。
第二个表 film_actor 的执行计划的 Extra 列有 "Not exists"。MySQL 通过使用 "Not exists" 优化来避免在表 film_actor 的索引中读取任何额外的行。这完全等效于直接编写 NOT EXISTS 子查询，这个执行计划中也是一样，一旦匹配到一行数据，就立刻停止扫描

所以，从理论上讲，MySQL 将使用完全相同的执行计划来完成这个查询。现实世界中，我们建议通过一些测试来判断使用哪种写法速度会更快。