Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1731658
  • 博文数量: 107
  • 博客积分: 1715
  • 博客等级: 上尉
  • 技术积分: 3168
  • 用 户 组: 普通用户
  • 注册时间: 2012-04-18 18:42
个人简介

阿里巴巴DBA,原去哪儿网DBA。专注于MySQL源码研究、DBA运维、CGroup虚拟化及Linux Kernel源码研究等。 github:https://github.com/HengWang/ Email:king_wangheng@163.com 微博 :@王恒-Henry QQ :506437736

文章分类

全部博文(107)

文章存档

2014年(2)

2013年(38)

2012年(67)

分类: 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()根据查询的字段创建一个grouporder顺序为给定字段的排列顺序。为将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 查询优化器实验分析(三)》文档中给出。


阅读(5298) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~