阿里巴巴DBA,原去哪儿网DBA。专注于MySQL源码研究、DBA运维、CGroup虚拟化及Linux Kernel源码研究等。 github:https://github.com/HengWang/ Email:king_wangheng@163.com 微博 :@王恒-Henry QQ :506437736
分类: Mysql/postgreSQL
2012-08-25 14:26:22
紧接着之前的《MySQL 查询优化器实验分析(一)》中的内容,以下对测试过程进行详细分析。
测试
1、普通查询
普通查询主要是单表查询,从测试的SQL语句可以看出,主要针对不同的查询条件进行测试,研究查询优化器的处理逻辑和生成查询计划。以下根据测试的SQL语句,给出测试的目的,重点分析处理逻辑和生成查询计划。此外,主要对关键的处理逻辑函数进行分析,具体详细内容可以参考《MySQL查询优化器分析》或者MySQL数据库源代码(本文基于MySQL 5.5.20)。
1.1 “*“处理
逻辑处理过程如下所示:
JOIN:prepare阶段 setup_tables():对查询涉及的表(student表),查看是否存在,设置变量相应的值,为查询准备。(sql\sql_base.cc:7963) setup_wild():将查询中所有“*“展开为对应数据表的字段。主要调用insert_fields()实现。(sql\sql_base.cc:7739) insert_fields():将“*”替换,展开为数据表的字段。(sql\sql_base.cc:8151) set_fields():检查所有字段是否在数据表中。(个人观点:对于替换了“*”的字段,可以省去检查字段的操作,或者对于“*”处理过程可以在set_fields()过程中处理)。(sql\sql_base.cc:7825) JOIN:optimize阶段 make_join_statistics():计算每个表查询匹配记录数和读取时间。均估计值,并非实际的值。(sql\sql_select.cc:2651) choose_plan():查找最优的查询计划。实际处理过程,调用greedy_search()贪婪查找算法实现。由于对一个表进行查询,并且没有任何查询的过滤条件和分组排序等条件,因此整个过程处理一次就结束。(sql\sql_select.cc:4913) greedy_search():贪婪查找最优执行计划的算法。具体处理过程调用递归函数best_extension_by_limited_search()实现。(sql\sql_select.cc:5219) best_extension_by_limited_search():对当前的查询计划进行递归查找更优的计划,由于该sql查询需要对全表进行扫描,因此执行一遍操作即终止。(sql\sql_select.cc:5425) JOIN:exec阶段 do_select():执行查询,并将查询结果通过socket进行写操作。(sql\sql_select.cc:11431) sub_select():调用evaluate_join_record()逐条取出查询的每条记录。(sql\sql_select.cc:11705) evaluate_join_record():取出每条记录。(sql\sql_select.cc:11758) |
对应的查询计划如下所示:
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
1 |
SIMPLE |
student |
ALL |
NULL |
NULL |
NULL |
NULL |
26 |
|
由以上处理过程可以知道,对于没有过滤条件以及分组排序条件的情况下,查询优化器处理仅对“*”替换为对应表的字段,并且还对字段进行了再次验证是否在查询的表中。因此,在写sql查询中,无论任何情况下,避免使用“*”,而是直接添加各个字段。并且,仅将需要的字段名进行查询并输出。
对于全表查询来说,查询优化器没有任何的优化策略。
1.2 查询字段
查询表中的字段,进行全表查询时,处理逻辑与1.1类似,唯一区别在于,省去了setup_wild()和insert_fields()处理过程。1.2测试与1.1进行对比,从而深入理解,查询优化器是如何工作。从而更加验证一点,为什么不建议使用“*”查询(以前在性能优化书籍中,建议不使用“*”,但始终不知道具体的原因。)。
对应的查询计划如下所示:
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
1 |
SIMPLE |
student |
ALL |
NULL |
NULL |
NULL |
NULL |
26 |
|
1.3 Distinct条件
Distinct的逻辑处理过程如下所示:
JOIN:prepare阶段 setup_tables():同1.1测试。 set_fields():同1.1测试。 JOIN:optimize阶段 make_join_statistics():计算每个表查询匹配记录数和读取时间。在该测试中,该过程有不同的操作,主要针对distinct条件。其中,调用add_group_and_distinct_keys()函数查找distinct可以使用的索引。(sql\sql_select.cc:2651) add_group_and_distinct_keys():查找distinct可以使用的索引。此外,通过调用Item_field::collect_item_field_processor()(sql\sql_item.cc:683)获取所有查询字段,存储到查询列表中。并且合并所有字段的(sql\sql_select.cc:4265) choose_plan():同1.1测试。 greedy_search():同1.1测试。 best_extension_by_limited_search():同1.1测试。 list_contains_unique_index():查看查询的字段中,是否有唯一索引。如果有查询字段上有唯一索引,那么处理过程将被转化为指定字段的全表查询。(sql\sql_select.cc:13576) create_distinct_group():根据查询的字段创建一个group,order顺序为给定字段的排列顺序。为将distinct转化为group 条件。(sql\sql_select.cc:15168) calc_group_buffer():根据查询的字段,计算group by的所需的buffer。(sql\sql_select.cc) create_tmp_table():根据查询字段,创建临时表。(sql\sql_select.cc:10229) JOIN:exec阶段 do_select():类似1.1测试,但是当前查询是将查询的结果写到临时表中。 sub_select():类似1.1测试,将查询字段的每条记录取出放到临时表中。 evaluate_join_record():同1.1测试。 change_to_use_tmp_fields():将操作切换到临时表操作。(sql\sql_select.cc:15828) JOIN::make_simple_join():初始化join。(sql\sql_select.cc:6075) calc_group_buffer():同上。 do_select():类似1.1测试,不同之处在于,该查询在临时表中进行。 sub_select():。类似1.1测试,不同之处在于,记录查询从临时表中获取,并从中过滤掉不符合查询条件的记录。 evaluate_join_record():同1.1测试。 |
对应的查询计划如下所示:
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
1 |
SIMPLE |
student |
ALL |
NULL |
NULL |
NULL |
NULL |
26 |
Using temporary; Using filesort |
由以上处理逻辑可以看出,distinct的优化方式是将distinct转化为group by条件来执行,并且会用到临时表操作。因此,在需要distinct操作时,可以转化为group by操作,来避免转化过程。
将distinct语句转化,转化结果如下所示:
SELECT std_spec, std_***, std_age FROM student GROUP BY std_spec, std_***, std_age; |
转化后的SQL执行处理逻辑如下所示:
JOIN:prepare阶段 setup_tables():通1.1测试。 set_fields():同1.1测试。 setup_group():初始化group by列表,并调用find_order_in_list()函数,检查group by 的字段是否在select的字段中(调用find_item_in_list(),(sql\sql_base.cc:6835)),并查找group by列表中的字段是否在数据表中 (调用find_item_in_tables(),(sql\sql_base.cc:6602))。(sql\sql_select.cc:15037) JOIN:optimize阶段 make_join_statistics():同1.3测试。 add_group_and_distinct_keys():类似1.3测试,区别是,这里处理的group条件,而不是distinct条件。 choose_plan():同1.3测试。 greedy_search():同1.3测试。 best_extension_by_limited_search():同1.3测试。 list_contains_unique_index():省去。 create_distinct_group(): 省去。 calc_group_buffer():同1.3测试。 create_tmp_table():同1.3测试。 JOIN:exec阶段 所有操作同1.3测试 |
对应的查询计划如下所示:
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
1 |
SIMPLE |
student |
ALL |
NULL |
NULL |
NULL |
NULL |
26 |
Using temporary; Using filesort |
通过转化后的测试可以验证,将distinct转化为group by处理过程,可以避免通过程序根据查询字段,转换group by的过程。
1.4 Primary key条件
主键查询的逻辑处理过程如下所示:
JOIN:prepare阶段 setup_tables():通1.1测试。 setup_fields():同1.1测试。 setup_conds():检查查询的where条件中字段是否存在。(sql_base.cc:8379) JOIN:optimize阶段 optimize_cond():优化查询的where条件,对等值条件调用build_equal_items()(sql\sql_select.cc:8273)函数进行优化,例如(a=b AND c=d AND (b=c OR d=5)转化为等值(a,b,c,d) OR (a=b AND b=c AND d=5));调用propagate_cond_constants()(sql\sql_select.cc:8763)函数将字段等值转换为常量,例如(a=b AND c=d AND d=5转化为a=b AND c=5 AND d=5);调用remove_eq_conds()函数去除常量等值,例如(1=1)(sql\sql_select.cc:9405)。 make_join_statistics():与1.1测试不同,由于where条件查询的字段是主键,可以使用索引。通过调用update_ref_and_keys()(sql\sql_select.cc:3967)函数,查找是否有使用索引的字段。调用create_ref_for_key()(sql\sql_select.cc:5848)函数为索引创建索引空间。调用join_read_const_table()(sql\sql_select.cc:12109)函数查找索引获得查找的记录。这里因为使用了主键索引,所以只有一行匹配内容,记录数和读取时间均设置为1。由于使用主键索引对单个数据表进行查询,因此不需要对现有的查询进行继续优化,。(sql\sql_select.cc:2651)。 join_read_const_table():通过查找索引获取查询的记录。其中调用join_read_const()(sql\sql_select.cc:12222)函数用于取出数据。 JOIN:exec阶段 do_select():与之前操作不同,该过程将取出的数据直接发送。 |
主键的查询性能较高,仅有一条查询记录,查询计划为const类型。与unique key索引的查询优化器执行过程类似,不在举例赘述。
对应的查询计划如下所示:
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
1 |
SIMPLE |
student |
const |
PRIMARY |
PRIMARY |
4 |
const |
1 |
|
1.5普通条件查询
普通where查询条件的逻辑处理过程如下所示:
JOIN:prepare阶段 setup_tables():通1.1测试。 setup_fields():同1.1测试。 setup_conds():同1.4测试。 JOIN:optimize阶段 optimize_cond():同1.4测试。 make_join_statistics():与1.4测试类似,不同之处在于,由于where条件字段没有索引,因此在查找索引时没有匹配的索引可以使用。所以该函数会调用update_ref_and_keys()函数,查找是否有使用索引的字段,但是不调用create_ref_for_key()和join_read_const_table()。也因为不能通过索引查找,接下来的操作跟1.1测试的全表查询类似。 choose_plan():以下操作同1.1测试。 JOIN:exec阶段 以下操作同1.1测试。 |
对普通字段进行where条件查询时,如果查询字段没有索引,将进行全表查询,查询计划类型为all。
对应的查询计划如下所示:
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
1 |
SIMPLE |
student |
ALL |
NULL |
NULL |
NULL |
NULL |
26 |
Using where |
为查询字段添加索引后,进行同样的操作,查看添加索引后,查询优化器的处理逻辑和查询计划的改变情况。
添加索引操作:
ALTER TABLE student ADD INDEX idx_student_name (std_name); |
添加索引后,查询优化器的处理逻辑如下所示:
JOIN:prepare阶段 setup_tables():通1.1测试。 setup_fields():同1.1测试。 setup_conds():同1.4测试。 JOIN:optimize阶段 optimize_cond():同1.4测试。 make_join_statistics():与1.4测试不同,由于where条件查询的字段是非主键,但是可以使用索引。首先通过调用update_ref_and_keys()(sql\sql_select.cc:3967)函数,查找是否有使用索引的字段。在查到可以使用索引后,并不会调用create_ref_for_key()和join_read_const_table()读取记录。而是调用get_quick_record_count()函数估计需要读取的记录数和读取时间,主要处理逻辑调用SQL_SELECT::test_quick_select()函数实现,具体处理逻辑如下所示。 SQL_SELECT::test_quick_select():该函数调用get_mm_tree()(sql\opt_range.cc:5518)函数获取查询树,调用get_key_scans_params()函数获取最优的查找范围,并估计查询的记录数,具体处理逻辑如下。(sql\sql_select.cc:2651) get_key_scans_params():该函数调用check_quick_select()(sql\opt_range.cc:7519)函数,核心处理逻辑调用check_quick_keys()(sql\opt_range.cc:7628)函数,递归估计通过索引查找的记录的行数,通过调用ha_innodb.cc::records_in_range()(storage\innobase\handler\ha_innodb.cc:7505)估计该范围内索引查找的记录数。(sql\opt_range.cc:4923) choose_plan():同1.3测试。 greedy_search():同1.3测试。 best_extension_by_limited_search():与之前不同,因为查询可以使用索引,因此该函数调用best_access_path()(sql\sql_select.cc:4365)函数根据查询计划查找最佳路径。 JOIN:exec阶段 以下操作同1.3测试。 |
当查询字段上有索引的情况下,在调用make_join_statistics()函数对查询代价进行估计时,使用索引树进行查找,从而避免了全表查询,并可以在执行阶段提高执行效率。从查询计划的类型来看,使用ref索引类型。
对应的查询计划如下所示:
id |
select_type |
table |
type |
possible_keys |
key |
key_len |
ref |
rows |
Extra |
1 |
SIMPLE |
student |
ref |
idx_student_name |
idx_student_name |
62 |
const |
1 |
Using where |
如果当前where条件查询的结果是离散分布的,并且查询结果占总记录数的很小部分的查询情况,根据业务需求,建议使用索引,可以有效提高查询的效率。
接下来的测试将在接下来的《MySQL 查询优化器实验分析(三)》文档中给出。