mysql mrr_cost_based_深入解析：从源码窥探MySQL优化器

(注:此图出自李海翔)

上图展示了优化器的大致执行过程，可以简单描述为：

1 根据语法树及统计统计，构建初始表访问数组(init_plan_arrays)

2 根据表访问数组，计算每个表的最佳访问路径(find_best_ref)，同时保存当前最优执行计划(COST最小)

3 如果找到更优的执行计划则更新最优执行计划，否则优化结束。

1 根据语法树及统计统计，构建初始表访问数组(init_plan_arrays)

2 根据表访问数组，计算每个表的最佳访问路径(find_best_ref)，同时保存当前最优执行计划(COST最小)

3 如果找到更优的执行计划则更新最优执行计划，否则优化结束。

从上述流程可以看出，执行计划的生成是一个“动态规划/贪心算法”的过程，动态规划公式可以表示为：Min(Cost(Tn+1)) = Min(Cost(T1))+Min(Cost(T2))+...Min(Cost(Tn-1))+Min(Cost(Tn)),其中Cost(Tn)表示访问表T1 T2一直到Tn的代价。如果优化器没有任何先验知识，则需要进行 A(n,n) 次循环，是一个全排列过程，很显然优化器是有先验知识的，如表大小，外连接，子查询等都会使得表的访问是部分有序的，可以理解为一个“被裁减”的动态规划，动态规则的核心函数为：Join::Best_extention_limited_search，在源码中有如下代码结构：

bool Optimize_table_order::best_extension_by_limited_search(

table_map remaining_tables,

uint idx,

uint current_search_depth)

{

for (JOIN_TAB **pos= join->best_ref + idx; *pos; pos++)

{

......

best_access_path(s, remaining_tables, idx, false,

idx ? (position-1)->prefix_rowcount : 1.0,

position);

......

if (best_extension_by_limited_search(remaining_tables_after,

idx + 1,

current_search_depth - 1))

......

backout_nj_state(remaining_tables, s);

......

}

}

bool Optimize_table_order::best_extension_by_limited_search(

table_map remaining_tables,

uint idx,

uint current_search_depth)

{

for (JOIN_TAB **pos= join->best_ref + idx; *pos; pos++)

{

......

best_access_path(s, remaining_tables, idx, false,

idx ? (position-1)->prefix_rowcount : 1.0,

position);

......

if (best_extension_by_limited_search(remaining_tables_after,

idx + 1,

current_search_depth - 1))

......

backout_nj_state(remaining_tables, s);

......

}

}

以上代码是在一个for循环中递归搜索，这是一个典型的全排列的算法。

02优化器参数

MySQL的优化器对于Oracle来说还显得比较幼稚。Oracle有着各种丰富的统计信息，比如系统统计信息，表统计信息，索引统计信息等，而MySQL则需要更多的常量，其中MySQL5.7提供了表mysql.server_cost和表mysql.engine_cost，可以供用户配置，使得用户能够调整优化器模型，下面就几个常见而又非常重要的参数进行介绍：

1 #define ROW_EVALUATE_COST 0.2f

计算符合条件的行的代价，行数越多，代价越大

2 #define IO_BLOCK_READ_COST 1.0f

从磁盘读取一个Page的代价

3 #define MEMORY_BLOCK_READ_COST 1.0f

从内存读取一个Page的代价，对于Innodb来说，表示从一个Buffer Pool读取一个Page的代价，因此读取内存页和磁盘页的默认代价是一样的

4 #define COND_FILTER_EQUALITY 0.1f

等值过滤条件默认值为0.1，例如name = ‘lily’, 表大小为100，则返回10行数据

5 #define COND_FILTER_INEQUALITY 0.3333f

非等值过滤条件的默认值是0.3333,例如col1>col2

6 #define COND_FILTER_BETWEEN 0.1111f

Between过滤的默认值是0.1111f，例如:col1 between a and b

......

1 #define ROW_EVALUATE_COST 0.2f

计算符合条件的行的代价，行数越多，代价越大

2 #define IO_BLOCK_READ_COST 1.0f

从磁盘读取一个Page的代价

3 #define MEMORY_BLOCK_READ_COST 1.0f

从内存读取一个Page的代价，对于Innodb来说，表示从一个Buffer Pool读取一个Page的代价，因此读取内存页和磁盘页的默认代价是一样的

4 #define COND_FILTER_EQUALITY 0.1f

等值过滤条件默认值为0.1，例如name = ‘lily’, 表大小为100，则返回10行数据

5 #define COND_FILTER_INEQUALITY 0.3333f

非等值过滤条件的默认值是0.3333,例如col1>col2

6 #define COND_FILTER_BETWEEN 0.1111f

Between过滤的默认值是0.1111f，例如:col1 between a and b

......

这样的常量很多，涉及到过滤条件、JOIN缓存、临时表等等各种代价，理解这些常量后，看到执行计划的Cost后，你会有种豁然开朗的感觉！

03 优化器选项

在MySQL中，执行select @@optimizer_trace, 得到如下参数：

index_merge=on,index_merge_union=off,index_merge_sort_union=off, index_merge_intersection=on, engine_condition_pushdown=on, index_condition_pushdown=on, mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on, use_index_extensions=on, condition