查询性能的优化 - 语句执行的基础 - 查询优化的过程 (一)

 

在语句生命周期的下一步就是把一个SQL查询放入一个可执行的计划中。这个步骤有许多子步骤：解析，预处理并且优化。在这个过程中任意一点抛出错误（比如语法错误）。在这我们不会列出MySQL内部机制。因此我们可以更自由的说一些别的，如单独的描述其中的一些步骤，虽然它们可能有效地全部或者部分的组合在一起。我们的目标就是帮助你理解MySQL执行语句的过程，这样能写出更好的语句。

 

解析和预处理

开始MySQL解析器会把一个语句分成一些token，并创建一个“解析树”。解析器使用MySQL语法来解释和校验这个语句。它必须确保这些token有效并且顺序正确，还有它会检查一些如字符串双引号没有结束的一些错误。

 

接下来预处理器就检查这个结果解析树额外的一些信息，这些信息解析器是解析不了的。举个例子，它会校验表和列是否存在，并且决定了命名和别名，这样保证列的引用不会有歧义。

 

然后，预处理校验权限。这个过程是非常快的，除非你的服务端使用了大量的权限。

 

查询语句优化器

解析树已经是有效的并且开始准备用优化器把它放入到执行计划中。可以用不同的方法来执行查询语句并且得到的结果相同。优化器的工作就是找到最好的方法。

 

MySQL使用了基于消耗的优化器。意思就是会尝试多种不同的方法去执行语句，选择一个效率更高的。这种消耗的单元是一个单独的随机4kb数据页读取。你可以查看到优化器执行一个查询语句，通过命令查看Last_query_cost变量。

mysql> SELECT SQL_NO_CACHE COUNT(*) FROM sakila.film_actor;
+----------+
| count(*) |
+----------+
| 5462 |
+----------+
mysql> SHOW STATUS LIKE 'last_query_cost';
+-----------------+-------------+
| Variable_name | Value |
+-----------------+-------------+
| Last_query_cost | 1040.599000 |
+-----------------+-------------+

 

这个结果的意思是，优化器评估它需要大学1040随机数据读取来执行这个语句。他评估标准基于如下的统计：每张表或索引的页，索引的基数（不同值的数目），行或键的长度，以及键的分布情况。在它的评估中，优化器不包含任意缓存的效果-它的前提是每次都以硬盘IO的方式来获取数据。

 

优化器并不是每一次都选择最优的方式，原因如下：

 

• 统计可能是错的。服务端依靠的是存储引擎提供的统计，这些统计可能从非常精确到不精确都有。比如，在一张表中innoDB引擎不会维护表的行数，这是由于它的MVCC架构。
• 消耗这个度量也不等同于这个语句实际所消耗的量。因此即使当统计数据是准确的，这个语句的执行开销也或多或少于MySQL的近似值。一个读取过多页的执行计划可能在某些情形下会读的少些。比如当读是连续的，因此硬盘IO会更快一些，或者当页已经被内存所缓存了。
• MySQL优化的方法可能不符合你的期望。你可能期望更快的执行时间，但是MySQL并不知道什么叫快速。它只明白消耗。就像前面我们所看到的，决定消耗的量也并非那么科学。
• MySQL并不会考虑其他正在并发执行的语句。这也影响一个语句的运行。
• MySQL也不会总做基于消耗的优化。有的时候，它仅仅遵循以下的规则，比如“如果有个全文匹配的MATCH条件，如果FULLTEXT索引存在，就使用它”。这样的话即使其他索引方式更快也不会去使用。
• 优化器不能计算账户内部操作的一些消耗。比如存储函数或者用户自定义的函数就没办法计算了。
• 在以后会说到，优化器不能评估每个可能执行的计划。因此会错失一个最佳的执行计划。

 

MySQL的语句优化器一个软件高度复杂的地方，并且它使用许多优化方法去把一个语句转为一个可执行的计划。有两个基本的优化类型，我们叫做静态或动态的。静态优化表现为的形式是，查看解析树。举一个例子，通过一个代数的规则，优化器可以把WHERE子句转为一个相等形式。静态优化是独立的值，比如WHERE子句不变的值。它们被执行一次，然后总是有效的。你可以把静态优化想象成”编译时的优化“。

 

相比较之下，动态优化基于上下文并且依赖于很多因素。比如在WHERE子句的值或者在一个索引中有多少行。每一次语句的执行，它们都必须重写进行评估。你可以把动态优化想象成“运行期的优化”。

 

在执行预处理语句或存储过程时，这些不同尤为重要。MySQL会进行一次静态优化，但是每次语句的执行都要进行动态优化。MySQL有的时候甚至会对一个执行语句重写优化。

 

 

下面是一些MySQL知道怎样去做的优化类型。

重写排序连接(join)

在一个查询语句中，表并不总是在你指定的顺序下进行连接。决定最佳连接顺序是一个重要的优化：我们会在Join优化器详细说到

 

把外连接转为内连接

一个外连接没有必要做为一个“外连接”去执行。由于一些源于，如WHERE条件和表的schema都可能导致一个外连接等于与内连接。MySQL可以发现这点并重新连接。并且以最适当的顺序进行连接。

 

 

 应用代数等价规则

MySQL应用代数转换使表达式简单化和规范化。 这样可以减少一些约束。评估可能的约束和常数的条件。比如，(5=5 AND a>5)就会缩减为a>5.同样的。(a<b AND b=c) AND a=5 就会变为b>5 AND b=c AND a=5.这些规则对条件查询非常有用。在这章的后面会详细说到。

 

COUNT(),MIN(),MAX()优化

 索引或者列为空的特性可以帮助MySQL优化这些语句。例如，去查找一个B-Tree索引最左边的列的最小值，MySQL仅仅需要请求索引的第一行。在优化查询语句的情形下同样有效，并且对于剩下的查询语句把这个值看做常量。同样的查找B-TREE索引的最大值，服务端会读取最后一行。如果服务端使用了这种优化方式，你会在EXPLAN看到““Select
tables optimized away”。意思就是优化器已经从语句执行计划中移除了这个表，并且以常量来替代它。

 

同样的 COUNT(*) 查询没有WHERE子句的情况下，也是这样被优化的。优化COUNT()会在以后说到。

 

 

评估和简化常量表达式

 当MySQL发现一个表达式可以简化为一个常量的时候，就会做这个优化。比如，如果发现在查询中一个用户自定义的变量并不会改变，就会把它转为常量。运算表达式是另一个例子了。

 

也许令人惊讶的是，在优化的过程中， 你可能认为一个查询可以简化为一个常数。其中一个例子为在索引上的MIN()函数。

在主键或唯一索引上查找，这就被扩展为一个常数。 如果一个WHERE子句给索引附上一个常量的条件，那么优化器就会了解MySQL可以在这个查询的开端来查找这个值。在查询剩下的语句中，就会把这个值看做一个常量。这有个例子。

 

mysql> EXPLAIN SELECT film.film_id, film_actor.actor_id
-> FROM sakila.film
-> INNER JOIN sakila.film_actor USING(film_id)
-> WHERE film.film_id = 1;
+----+-------------+------------+-------+----------------+-------+------+
| id | select_type | table | type | key | ref | rows |
+----+-------------+------------+-------+----------------+-------+------+
| 1 | SIMPLE | film | const | PRIMARY | const | 1 |
| 1 | SIMPLE | film_actor | ref | idx_fk_film_id | const | 10 |
+----+-------------+------------+-------+----------------+-------+------+

 

MySQL用两步来执行这个SQL. 看下上面的输出就可以知道了。第一步在film表中查找所需要的行。 MySQL的优化器知道只有一行，因为film_id是主键，并且在查询优化阶段查看将要查询的行数的时候，已经考虑到索引了。这个表的ref是const那是因为优化器已经知道了在这个查找过程中将用到的行数。

 

 在第二步的时候，MySQL把从第一步中找到的film_id做为一个已知的数量。那是因为当到达第二步的时候优化器已经知道这些了，它会知道所有来自第一步的值。注意film_actor表的ref也是const的。和表film是一样的。

 

另一方面你会看到如果WHERE USING或者ON子句的约束的值相同，那些条件的值都为常量了。在这个例子中，优化器已经通过WHERE子句得知了film_id的值，就会认为这个语句的所有的film_id都为常量了。

 

覆盖索引

当一个索引覆盖了你所需要查询的列，MySQL有的时候使用这个索引来避免读取行数据。我们在覆盖索引那节已经详细说过了。

 

子查询优化

MySQL会把一些子查询转为更高效的形式，把它们转为索引查找来取代子查询。

 

提前结束

当查询满足了条件，MySQL会终止处理这个查询。显而易见的例子是LIMIT子句。但是还有很多提前结束操作的例子，看看如下的例子

 

mysql> EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id = -1;

+----+...+-----------------------------------------------------+

| id |...| Extra |

+----+...+-----------------------------------------------------+

| 1 |...| Impossible WHERE noticed after reading const tables |

+----+...+-----------------------------------------------------+

 

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;

 

这个查询排除掉了有演员的电影。每个电影可能有很多演员。但是只要它找到了一个演员，它就停止查询当前的电影了，而去寻找下一个电影是否有演员。因为它知道WHERE条件禁止查询出这个有演员的电影了。同样的如“Distinct/not-exists”优化适用于DISTINCT, NOT EXISTS( )，LEFT JOIN语句。

 

等价传播

当两个值在一个查询语句中相同，那么MySQL会进行识别的。举个例子，在JOIN条件中，就传播到WHERE子句所有相同的列。来看个示例。

mysql> SELECT film.film_id

-> FROM sakila.film

-> INNER JOIN sakila.film_actor USING(film_id)

-> WHERE film.film_id > 500;

 

MySQL知道WHERE子句的film_id不仅仅指的是film表，也指的是film_actor，因为USING语句使这两张表的film_id进行了匹配。

如果你使用了其他数据库，请不要那么做，你可以明确指出这两列的条件。语句如下

... WHERE film.film_id > 500 AND film_actor.film_id > 500

显然对于MySQL上面的语句是不需要的。这只会让你的维护更困难。

 

IN()列表的比较

在许多数据库中，IN()和许多OR组成的语句是一样的。因为这两个语句的逻辑上相同。在MySQL中，有些不同，对IN()里的值进行排序，并且用快速的二进制搜索来查找值是否存在。in的时间复杂度为O(log n) ，然而OR的为O(n) （也就是说，如果list很大会慢一些）。

 

上面所说的几点并不太全，MySQL所执行的优化策略超过了本章的内容了。但是能给你带来一些优化的思想。但是重要的一点是，不要试图比优化器更智能。结果就是你可能比它强或者使你的语句更复杂更难维护，我要说的就是，让优化器去做应该做的。

 

当然有的时候，优化器不能给我们带来高效的结果的使用，也要去做一些特定的优化。有的时候，因为逻辑的原因，可能有个条件一定会为true，但优化器却不知道。优化器也有些不具备的功能，比如优化hash索引等等。这样导致优化器对查询消耗评估错误，去执行了一个消耗更高的执行计划。

 

如果你知道优化器不能带来一个优化的结果，并且你知道原因，那么你就可以自己优化了。也有一些其他的方法，那就是重写这个语句，重写设计数据结构，或者添加索引。

 

 

评论

1 楼 315224416 2012-08-30  

我有几个疑问

1. 重写排序连接(join), 是根据什么原则来决定需要优先使用的表?

2. 把外连接转为内连接, 能不能举个例子, 不太明白

3. 子查询优化, 这个是怎么回事呢


希望你能帮忙解答一下爱. 这几点实在想不出来了,. 谢谢