mysql查询优化器

查询优化器是mysql中非常重要且复杂的部件，mysql优化器优化策略分为静态优化和动态优化：静态优化可以直接对解析树进行分析并完成优化，不依赖于特别的数值，依次完成后就一直有效，如通过简单的代数变换将where条件转换成另一种等价形式； 动态优化和查询的上下文有关，也可能和多种其他因素有关，如where条件的取值、索引中条目对应数据行数等，它需要每次在查询的时候进行评估，可以理解为 “运行时优化”。

mysql能处理到的优化类型：

1. 重新定义关联表顺序：数据表的关联不会总是按照在查询中指定顺序进行，关联的顺序是由关联查询优化器决定。关于关联查询、关联查询优化器，后面会详细介绍。
2. 外连接转化成内连接：mysql会把等价于一个内连接的外连接重写，让其可以调整关联顺序。
3. 使用等价变化规则：mysql可以使用一些等价变换来简化并规范表达式，它可以合并和减少一些比较，移除一些恒成立和恒不成立的判断。
4. 优化count()、min()、max()：索引和列是否可为空通常可以帮助mysql优化这类表达式。如，查询某一列的最小值，只要查询B-Tree索引的最左端的第一行记录；查询最大值，查询B-Tree索引最右端数据；查询没有where条件的count(*)，则可以通过使用存储引擎提供的一些优化，如MyISAM维护一个变量来存储数据表的行数。
5. 预估并转化为成熟表达式：mysql检测到一个表达式可以转化为常数的时候，会一直把该表达式作为常数进行优化。
6. 覆盖索引扫描：如果索引中的列包含查询中所有需要使用的列，mysql可以使用索引返回需要的数据，而无需查询对应行数据。
7. 子查询优化：mysql在一些情况下可以将子查询转换成一种更高效的形式，从而减少多个查询多次对数据进行访问。
8. 提前终止查询：发现满足查询条件的需求后，mysql会立即终止查询。如在使用limit子句，当查询到我们需要的数据行后，即停止访问其他行数据。
9. 等值传播：如果两个列的值通过等式关联，那么mysql能把其中一个列的where条件传递到另一列上，如：select f.film_id from film f  inner join film_actor fa USING (film_id) where f.film_id > 500;  这里使用film_id 进行等值关联，mysql知道where条件中的film_id 不仅适用于film表，还适用于film_actor 表，两张表都会使用film_id > 500 进行筛选。
10. 列表in() 的比较： in() 我们一般理解为多个or条件，而且两者确实是等价的。但是在mysql中这点并不成立，mysql将in() 列表中的数据先进行排序，然后通过二分查找的方式确定列表中的值是否满足条件，这是一个O(log n) 时间复杂度的操作，等价换成or条件的复杂度为 O(n) ， 对于in()列表中有大量取值的时候，mysql的处理速度相对更快。

上面列举的并非mysql优化器的全部，mysql还会做大量的其他优化。了解mysql优化器，可以帮助我们在编写高性能的SQL时少走一些弯路。但是优化器给出的结果有时候也并不是最优的结果，这需要我们在更加了解真实的数据后，对其进行逻辑调整。

数据和索引的统计信息：

在了解mysql执行基础 一文中，给出了mysql执行的流程图，在服务器层有查询的优化器，没有保存数据和索引的统计信息。统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息。mysql查询优化器在生成查询的执行计划时，需要向存储引擎获取响应的统计信息，包括：每个表或者索引有多少页面、每个表的每个索引基数是多少、数据行和索引长度、索引的分布信息等。

Mysql执行关联查询的过程：

Mysql的任何一次查询都是一次关联，并不是一个查询用到两个表匹配才叫关联。mysql对任何关联查询都执行嵌套循环关联操作，即先从一张表中循环取出单条数据，然后再嵌套寻循环找到下一个表中寻找匹配的行，依次执行直到直到所有表中匹配的行。用代码表示如下：

//遍历表1的数据行
while(table_1_row.next()!=-1)
  //遍历子表表2的数据行
  while(table_2_row.next()!=-1)
     if(符合连接条件)
            ......

 mysql基本上将所有的关联查询类型（单表查询、子查询、连接查询）都转换为这种嵌套形式（其实全表扫描是mysql最简单最暴力的一种方式，真实情况下还会选择更优的方式，例如索引扫描，但原理基本相通）。例如使用from+子查询的方式查询时，会先将内部查询结果保存到临时表中（可能是内存，也可能是磁盘中），然后将这个临时表作为普通表对待，然后使用上面方法进行查询。

我们看一下，下面这个查询语句的执行过程：

select tabl1.col1, tabl2.col2
from tabl1 inner join tabl2 using (col3)
where tabl1.col1 in (5,6);

mysql对它实行嵌套查询的伪代码为：

   outer_iter = iterator_over tbl1 where col1 in(3,4)
　　outer_row = outer_iter.next
 
　　while outer_row
 
　　　　inner_iter = iterator over tbl2 where col3=outer_row.col3
　　　　inner_row = inner_iter.next
 
　　　　　　while inner_row
 
　　　　　　　　output[outer_row.col1,inner_row.col2]
　　　　　　　　inner_row = inner_iter.next
 
　　　　　　end
　　　　out_row = outer_iter.next
　　end

但是，并非关联查询就要用到临时表。举个例子，比如有如下一个查询语句：

 select * from teacher inner join student using(teacher_id)；

在这个查询中，teacher_id是两个表的索引，mysql会通过索引找到student对应的数据行，而不是直接取出某一个表作为临时表。这样就大大提高了效率，所以合理的索引对于数据库是十分重要的。

关联查询优化器：

关联查询优化器是mysql优化器的重要组成部分，它决定了多个表关联时的顺序。上面我们说过，mysql执行关联查询是使用嵌套循环的方式执行的，嵌套循环的层级关系一定程度上决定了嵌套循环的执行次数。

关联插叙优化器会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树，但是入股有n个表的关联，它需要检查n的阶乘种关联顺序。而我们没增加一张表，那么可能出现的查询结果就会增加n+1倍，它的增长速度非常快，msyql要在众多中可能中计算出最优的策略成本就会很高。这时候mysql就会使用“贪婪”搜索的方式查找最优关联顺序，不会遍历全部的可能性。

所以在编写关联查询sql时，特别是关联表比较多时，表的关联顺序不能随意安排，这时关联优化器可以根据这些规则大大减少搜索空间，如左连接、相关子查询。因为后面的表的查询需要依赖于前面的表的查询结果，这种依赖关系通常可以帮助优化器减少需要扫描的执行计划数量。

排序优化：

排序操作是一个成本很高的操作，从性能角度考虑应该尽量避免排序或尽可能不对大量数据进行排序。特别是不能使用索引进行排序是，mysql需要自己进行排序，如果数据量小则在内存中进行排序，如果数据量大需要使用磁盘，mysql将这个过程统称为文件排序(filesort) 。

如果排序的数据量小于“排序缓冲区”，mysql使用内存进行“快速排序”；如果内存不够，mysql会先将数据分块，然后对每个独立的块使用“快速排序”进行排序，并将各个块的排序结果存放在磁盘上，然后将排序好的块进行合并。mysql排序时分配的临时空间要比磁盘上原表的数据大很多，它要为定长空间准备足够长的字符串，如varchar列要分配完整长度；UTF-8字符集，要为每个字符留三个字节。

在关联查询排序时，mysql会分两种情况来处理文件排序：如果Order by 子句中的所有列都来自关联表的第一个表，那么mysql在关联处理第一个表的时候就进行文件排序，在EXPLAIN中会看到Extra字段会有“Using filesort”；其它情况下，mysql会先将关联的结果存放到临时表中，然后再所有的关联都结束后，再进行文件排序。