MySQL的查询优化器是一个非常复杂的部件,它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单地分为两种,一种是静态优化,一种是动态优化。静态优化可以直接对解析树进行分析,并完成优化。例如,优化器可以通过一些简单的代数变换将WHERE条件转换成另一种等价形式。静态优化不依赖于特别的数值,如WHERE条件中带入的一些常数等。静态优化在第一次完成后就一直有效,即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种"编译优化"。
相反,动态优化则和查询的上下文有关,也可能和很多其他因素有关,例如WHERE条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估,可以认为这是"运行时优化"
mysql> EXPLAIN SELECT film.film_id, film_actor.actor_id
FROM film
INNER JOIN film_actor USING(film_id)
WHERE film_id=1;
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | film | NULL | const | PRIMARY | PRIMARY | 2 | const | 1 | 100.00 | Using index |
| 1 | SIMPLE | film_actor | NULL | ref | idx_fk_film_id | idx_fk_film_id | 2 | const | 10 | 100.00 | Using index |
+----+-------------+------------+------------+-------+----------------+----------------+---------+-------+------+----------+-------------+
2 rows in set (0.04 sec)
MySQL分成两步来执行这个查询,也就是上面执行计划的两行输出。第一步先从film表找到需要的行。因为在film_id字段上有主键索引,所以MySQL优化器直到这只会返回一行数据,优化器在生成执行计划的时候,就已经通过索引信息直到将返回多少行数据。因为优化器已经明确直到有多少个值(WHERE条件中的值)需要做索引查询,所以这里的表访问类型是const.
在执行计划的第二步,MySQL将第一步返回的film_id列当作一个已知取值的列来处理。因为优化器清除在第一步执行完成后,该值就会是明确的了。注意到正如第一步中一样,使用film_actor字段对表的访问类型也是const.另一种会看到常数条件的情况是通过等式将常数值从一个表传到另一个表,这可以通过WHERE、USING或者ON语句来限制某列取值为常数。在上面的例子中,因为使用了USING子句,优化器直到这也限制了film_id在整个查询中都始终是一个常量——因为它必须等于WHERE子句中的那个值
mysql> EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id = -1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
1 row in set (0.05 sec)
从这个例子看到查询在优化阶段就已经终止。除此之外,MySQL在执行过程中,如果发现某些特殊的条件,则会提前终止查询。当存储引擎需要检索"不同取值"或者判断存在性的时候,MySQL都可以使用这类优化。例如,我们现在需要找到没有演员的所有电影
mysql> SELECT film.film_id
-> FROM sakila.film
-> LEFT OUTER JOIN sakila.film_actor USING(film_id)
-> WHERE film_actor.film_id IS NULL;
+---------+
| film_id |
+---------+
| 257 |
| 323 |
| 803 |
+---------+
3 rows in set (0.05 sec)
这个查询将会过滤掉所有有演员的电影。每一部电影可能会有很多的演员,但是上面的查询一旦找到任何一个,就会停止并立刻判断下一部电影,因为只要有一名演员,那么WHERE条件则会过滤掉这类电影。类似这种"不同值/不存在"的优化一般可用于DISTINCT、NOT EXIST()或者LEFT JOIN类型的查询