MySQL之查询性能优化(五)

查询性能优化

查询优化器

MySQL的查询优化器是一个非常复杂的部件，它使用了很多优化策略来生成一个最优的执行计划。优化策略可以简单地分为两种，一种是静态优化，一种是动态优化。静态优化可以直接对解析树进行分析，并完成优化。例如，优化器可以通过一些简单的代数变换将WHERE条件转换成另一种等价形式。静态优化不依赖于特别的数值，如WHERE条件中带入的一些常数等。静态优化在第一次完成后就一直有效，即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种"编译优化"。
相反，动态优化则和查询的上下文有关，也可能和很多其他因素有关，例如WHERE条件中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估，可以认为这是"运行时优化"

下面是一些MySQL能够处理的优化类型:

• 1.重新定义关联表的顺序
  数据表的关联并不总是按照在查询中指定的顺序进行。决定关联的顺序是优化器很重要的一部分功能
• 2.将外连接转化为内连接(外连接:左、右连接)
  并不是所有的OUTER JOIN语句都必须以外连接的方式执行。诸多因素，例如WHERE条件、库表结构都可能会让外连接等价于一个内连接。MySQL能够识别这点并重写查询，让其可以调整关联顺序
• 3.使用等价变化规则
  MySQL可以使用一些等价变化来简化并规范表达式。它可以合并和减少一些比较，还可以移除一些恒成立和一些恒不成立的判断。例如(5=5 AND a > 5) 将被改写为a>5。类似的，如果有( a < b AND b = c)AND a =5则会改写为 b>5 AND b =c AND a =5.这些规则对于我们编写条件语句很有用
• 4.优化COUNT()、MIN()和MAX()
  索引和列是否可为空通常可以帮助MySQL优化这类表达式。例如，要找到某一列的最小值，只需要查询对应B-Tree索引最左端的记录，MySQL可以直接获取索引的第一行记录。在优化器生成执行计划的时候可以利用这一点，在B-Tree索引中，优化器会将这个表达式作为一个常数对待。类似的，如果要查找一个最大值，也只需读取B-Tree索引的最后一条记录。如果MySQL使用了这种类型的优化，那么在EXPLAIN中就可以看到"Select tables optimized away".从字面意思可以看出，它表示优化器已经从执行计划中移除了该表，并以一个常数取而代之。类似的，没有任何WHERE条件的COUNT(*)查询通常也可以使用存储引擎提供的一些优化(例如，MyISAM维护了一个变量来存放数据表的行数)
• 5.预估并转化为常数表达式
  当MySQL检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理。例如，一个用户自定义变量在查询中没有发生变化时就可以转换为一个常数。数学表达式则是另外一种典型的例子。让人惊讶的是，在优化阶段，有时候甚至一个查询也能够转化为一个常数。一个例子是在索引列上执行MIN()函数。甚至是主键或者唯一键查找语句也可以转换为常数表达式。如果WHEREE子句中使用了该类索引的常数条件，MySQL可以在查询开始阶段就先查找这些值，这样优化器就能够直到并转换为常数表达式，下面是一个例子:

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)
1
2
3
4
5
6
7
8
9
10
11

MySQL分成两步来执行这个查询，也就是上面执行计划的两行输出。第一步先从film表找到需要的行。因为在film_id字段上有主键索引，所以MySQL优化器直到这只会返回一行数据，优化器在生成执行计划的时候，就已经通过索引信息直到将返回多少行数据。因为优化器已经明确直到有多少个值(WHERE条件中的值)需要做索引查询，所以这里的表访问类型是const.
在执行计划的第二步，MySQL将第一步返回的film_id列当作一个已知取值的列来处理。因为优化器清除在第一步执行完成后，该值就会是明确的了。注意到正如第一步中一样，使用film_actor字段对表的访问类型也是const.另一种会看到常数条件的情况是通过等式将常数值从一个表传到另一个表，这可以通过WHERE、USING或者ON语句来限制某列取值为常数。在上面的例子中，因为使用了USING子句，优化器直到这也限制了film_id在整个查询中都始终是一个常量——因为它必须等于WHERE子句中的那个值

• 6.覆盖索引扫描
  当索引中的列包含所有查询中需要使用的列的时候，MySQL就可以使用索引返回需要的数据，而无须查询对应的数据行
• 7.子查询优化
  MySQL在某些情况下可以将子查询转换一种效率更高的形式，从而减少多个查询多次对数据进行访问
• 8.提前终止查询
  在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询。一个典型的例子就是当使用了LIMIT子句的时候，除此之外，MySQL还有几类情况也会提前终止查询，例如发现了一个不成立的条件，这时MySQL可以立刻返回一个空结果。从下面的例子可以看到这一点:

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)
1
2
3
4
5
6
7

从这个例子看到查询在优化阶段就已经终止。除此之外，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)
1
2
3
4
5
6
7
8
9
10
11
12

这个查询将会过滤掉所有有演员的电影。每一部电影可能会有很多的演员，但是上面的查询一旦找到任何一个，就会停止并立刻判断下一部电影，因为只要有一名演员，那么WHERE条件则会过滤掉这类电影。类似这种"不同值/不存在"的优化一般可用于DISTINCT、NOT EXIST()或者LEFT JOIN类型的查询