MySQL之查询性能优化三

本文先简略地继续介绍mysql优化器的两种重要的优化特性：关联查询优化器，排序优化。

 

关联查询优化器：

　　mysql优化器最重要的一部分就是关联查询优化。它决定了多表关联时的顺序。

　　通常多表关联的时候，可以有很多不同的关联顺序来获得相同的执行结果。关联查询优化器则通过
　　不同顺序时的成本来选择一个带最小的关联顺序。

　　

mysql> SELECT film.film_id, film.title, film.release_year, actor.actor_id,
　　 -> actor.first_name, actor.last_name
　　 -> FROM sakila.film
　　 -> INNER JOIN sakila.film_actor USING(film_id)
　　 -> INNER JOIN sakila.actor USING(actor_id);

　　很容易看出，可以通过一些不同的执行计划来完成上面的查询。例如，mysql可以从film表开始，使用
　　film_actor表的索引film_id来查找对应的actor_id值，然后根据actor表的主键找到对应的记录。
　　优化器会选择一个最优的顺序来生成一个执行计划。

　　当然我们可以使用straight join关键字，固定关联顺序。

 

　

排序优化：　　

　　无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免
　　对大量数据进行排序。

　　在前面的文章内我们已经看到了mysql如何通过索引进行排序。当不能使用索引生成排序结果的时候，
　　mysql需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，不过mysql
　　将这个过程统一称为文件排序（filesort），即使完全是内存排序不需要任何磁盘文件也是如此。

　　如果排序的数据量小于"排序缓冲区"，mysql使用内存进行"快速排序"操作。如果内存不够排序，
　　那么mysql会先将数据分块，对每个独立的块使用"快速排序"进行排序，并将各个块的排序结果存放
　　在磁盘上，然后将各个排好序的块进行合并（merge），最后返回排序结果。

　　mysql有如下两种排序算法：
　　　　两次传输排序(旧版本使用)

　　　　单次传输排序（新版本使用）

 

查询执行引擎：

　　在查询经优化器优化以后，下一步就是执行生成的执行计划。这里就涉及到查询执行引擎了，

　　让我们来浅谈一下查询执行引擎：

　　在解析和优化阶段，mysql将生成查询对应的执行计划，mysql的查询执行引擎则根据这个执行
　　计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多的关系型数据库那样会生成
　　对应的字节码。

　　相对于查询优化阶段，查询执行阶段不是那么复杂：mysql只是简单地根据执行计划给出的指令
　　逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过条用存储引擎实现的接口
　　来完成，这些接口也就是我们称为"handler API"的接口。查询中的每一个表由一个handler实例
　　表示。前面我们有意忽略了这点，实际上，mysql在优化阶段就为每个表创建了一个handler实例，
　　优化器根据这些实例的接口可以获取表的相关信息，包括表的所有列名，索引统计信息，等等。

　　存储引擎接口有着非常丰富的功能，但是底层接口却只有几十个，这些接口像"搭积木"一样能够
　　完成查询的大部分操作。例如，有一个查询某个索引的第一行的接口，再有一个查询某个索引条目
　　的下一个条目的功能，有了这两个功能我们就可以完成全索引扫描的操作。这种简单的接口模式，
　　让mysql的存储引擎插件式架构成为可能，但是正如前面的讨论，也给优化器带来了一定的限制。

　　为了执行查询，mysql只需要重复执行计划中的各个操作，直到完成所有的数据查询。

 

返回结果给客户端：

　　查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端，mysql仍然会
　　返回这个查询的一些信息，如该查询影响的行数。

　　如果查询可以被缓存，那么mysql在这个阶段也会将结果存放在查询缓存中。

　　mysql将结果集返回客户端是一个增量，逐步返回的过程。例如，我们回头看看前面的关联操作，
　　一旦服务器处理完最后一个关联表，开始生成第一条结果时，mysql就可以开始向客户端逐步返回结果集了。

　　这样处理有两个好处，服务器端无须存储太多的结果，也就不会因为要返回太多结果而消耗太多内存。
　　另外，这样处理也让mysql客户端第一是获得返回的结果。