mysql子查询(in)的实现

In子查询的原理

1.    in原理

此调研的背景是同样的select结果为什么使用子查询会比不使用子查询慢。我们使用的数据库还是mysql官方的employees。进行的实验操作为下面两个语句:

方法一:explain select sql_no_cache t.emp_no,t.title,t.from_date,t.to_datefrom titles t straight_join employees e on (e.emp_no=t.emp_no) straight_joinsalaries s on (s.emp_no=e.emp_no) where e.gender='F' and s.salary=90930;


图1直接使用join

方法二:explain select sql_no_cache * from titles t where t.emp_no in (select s.emp_no from salaries s, employees e where s.emp_no=e.emp_no ande.gender='F' and s.salary=90930);


图2使用in的子查询

在下面的讨论中我们直接使用直接join和in的称呼来代表两种不同的情况。(注:在我们的实验中第一种情况use 3.5s;第二种情况use 5.7s)

首先我们来解释一下图2的dependent subquery是什么意思:手册上的解释是,子查询中的第一个select,取决于外面的查询。就这么一句话,其实它表达的意思是(以我们图2的表来说明)子查询(e join t)的第一个表(e)的查询方式依赖于外部(t表)的查询。换句话说就是e表的检索方式依赖于t表的数据,如这里t表得到的记录t.emp_no(where t.emp_no in)刚好可以被e表作为eq_ref方式来获得它的相应的记录;换种写法如果此时t表扫描第一条记录得到的t.emp_no为10001的话,那么后面子查询的语句就类似于这样的语句:

select s.emp_no from salaries s, employeese where s.emp_no=e.emp_no and e.gender='F' and s.salary=90930 ands.emp_no=10001。此时这个语句就会被优化拿来优化,变成了上面的子查询的执行计划。

通过这个解释我们可以知道:对于上面的两种方式,它们使用的索引及读取数据的过程及方法是一样的,全表扫描t表,将t的每条记录传递给e表,e表通过eq_ref索引方式来获得记录判断自身的条件,然后再传递给s给,s表使用ref方式来获得记录,再判断自身的条件是否也得到满足,如果也满足的话,则找到一个满足此查询的语句。那么为什么这两种情况会有性能上的差距了?

首先我们通过bt上看一下,两的具体执行流程,看它们的区别在哪里?

#0  evaluate_join_record (join=0x6fe0f10, join_tab=0x6fe2b28, error=0) at sql_select.cc:11414
#1  0x00000000005e41e8 in sub_select (join=0x6fe0f10, join_tab=0x6fe2b28, end_of_records=<value optimized out>) at sql_select.cc:11384   【s表】

#2  0x00000000005e3f5a in evaluate_join_record (join=0x6fe0f10, join_tab=0x6fe28d0, error=<value optimized out>) at sql_select.cc:11511
#3  0x00000000005e41e8 in sub_select (join=0x6fe0f10, join_tab=0x6fe28d0, end_of_records=<value optimized out>) at sql_select.cc:11384       【e表】

#4  0x00000000005e3f5a in evaluate_join_record (join=0x6fe0f10, join_tab=0x6fe2678, error=<value optimized out>) at sql_select.cc:11511
#5  0x00000000005e4215 in sub_select (join=0x6fe0f10, join_tab=0x6fe2678, end_of_records=<value optimized out>) at sql_select.cc:11391       【t表】

#6  0x0000000000601d30 in do_select (join=0x6fe0f10, fields=0x6f819a0, table=0x0, procedure=0x0) at sql_select.cc:11140
#7  0x000000000060a479 in JOIN::exec (this=0x6fe0f10) at sql_select.cc:2314
#8  0x000000000060ae0f in mysql_select (thd=0x6f7f980, rref_pointer_array=0x6f81a68, tables=0x6fd5198, wild_num=0, fields=@0x6f819a0, conds=0x6fdd218,    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x6fdd398, unit=0x6f81470, select_lex=0x6f81898)    at sql_select.cc:2509
#9  0x000000000060b481 in handle_select (thd=0x6f7f980, lex=0x6f813d0, result=0x6fdd398, setup_tables_done_option=0) at sql_select.cc:269
#10 0x000000000054c71a in execute_sqlcom_select (thd=0x6f7f980, all_tables=0x6fd5198) at sql_parse.cc:5075
#11 0x000000000055538c in mysql_execute_command (thd=0x6f7f980) at sql_parse.cc:2271
#12 0x000000000055ebd3 in mysql_parse (thd=0x6f7f980,

表1 join方式的bt

通过该bt我们也可以清楚的看到三层nest-loop的过程;注:通过在每一层的sub_select处查看join_tab->table->alias变量我们可以此时具体操作的表。

#0  evaluate_join_record (join=0x6fe11d8, join_tab=0x6fe5148, error=0) at sql_select.cc:11414
#1  0x00000000005e41e8 in sub_select (join=0x6fe11d8, join_tab=0x6fe5148, end_of_records=<value optimized out>) at sql_select.cc:11384     【s表】

#2  0x00000000005e3f5a in evaluate_join_record (join=0x6fe11d8, join_tab=0x6fe4ef0, error=<value optimized out>) at sql_select.cc:11511
#3  0x00000000005e41e8 in sub_select (join=0x6fe11d8, join_tab=0x6fe4ef0, end_of_records=<value optimized out>) at sql_select.cc:11384      【e表】

#4  0x0000000000601d30 in do_select (join=0x6fe11d8, fields=0x6fd5300, table=0x0, procedure=0x0) at sql_select.cc:11140
#5  0x000000000060a479 in JOIN::exec (this=0x6fe11d8) at sql_select.cc:2314
#6  0x00000000004d5102 in subselect_single_select_engine::exec (this=0x6fdcda0) at item_subselect.cc:1987
#7  0x00000000004d26b1 in Item_subselect::exec (this=0x6fdccb0) at item_subselect.cc:280
#8  0x00000000004d1aaa in Item_in_subselect::val_bool (this=0x6fe11d8) at item_subselect.cc:880
#9  0x0000000000438821 in Item::val_bool_result (this=0x6fe11d8) at item.h:745
#10 0x0000000000476941 in Item_in_optimizer::val_int (this=0x6fe2a58) at item_cmpfunc.cc:1833
#11 0x00000000005e3d9a in evaluate_join_record (join=0x6fdce98, join_tab=0x6fe3538, error=<value optimized out>) at sql_select.cc:11434
#12 0x00000000005e4215 in sub_select (join=0x6fdce98, join_tab=0x6fe3538, end_of_records=<value optimized out>) at sql_select.cc:11391      【t表】

#13 0x0000000000601d30 in do_select (join=0x6fdce98, fields=0x6f819a0, table=0x0, procedure=0x0) at sql_select.cc:11140
#14 0x000000000060a479 in JOIN::exec (this=0x6fdce98) at sql_select.cc:2314
#15 0x000000000060ae0f in mysql_select (thd=0x6f7f980, rref_pointer_array=0x6f81a68, tables=0x6fd4d90, wild_num=1, fields=@0x6f819a0, conds=0x6fdccb0,    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x6fdce78, unit=0x6f81470, select_lex=0x6f81898)    at sql_select.cc:2509
#16 0x000000000060b481 in handle_select (thd=0x6f7f980, lex=0x6f813d0, result=0x6fdce78, setup_tables_done_option=0) at sql_select.cc:269
#17 0x000000000054c71a in execute_sqlcom_select (thd=0x6f7f980, all_tables=0x6fd4d90) at sql_parse.cc:5075
#18 0x000000000055538c in mysql_execute_command (thd=0x6f7f980) at sql_parse.cc:2271
#19 0x000000000055ebd3 in mysql_parse (thd=0x6f7f980,

表2 in方式的bt

通过这两个表我们可以发现在表t与表[e,s]之前插入了一些其它的函数,并且这个插入的时机是在t表执行evaluate_join_record函数时调用select_cond_result= test(select_cond->val_int());判断它所拥有的条件是否满足时进入的。对于表1直接join的情况该过程是没有被执行的因为它没有自身的where cond。对于表2 in的方式,这个条件可能是由于在前面执行optimize时,确定外部查询的执行计划时确定的,这里我们不再去确认。正常的情况这个test如果有条件的话那么应该执行相应的条件判断如对于e表它最终调用的判断函数为int Arg_comparator::compare_int_signed();而在这里对于有子查询的它调用的方法是:Item_subselect::exec,而它最终又调用各自的engine->exec(),如这里它调用的是这个subselect_single_select_engine::exec方法,如果是第一次调用该函数的话那么就先执行一次join->optimize(),即对子查询(内部查询)进行优化,而在mysql_select时调用的join->optimize()只是对外部查询进行优化,它并不包括内部查询的优化(执行计划等,另外对于直接join的话没有所谓的内外部查询那么它的整个执行计划就是mysql_select完成),然后执行join->reinit(),最后再执行JOIN::exec;也就是说对于in这种情况t进行全表扫描,那么它总共有443310,那么这几个函数就要被调用443310多次(join->optimize()除外,它在第一次调用子查询确认执行计划之后就不再调用)。

我们可以通过下面的图来反应这两种过程:


图3 join方式的执行过程


图4 in方式的执行过程

上面就是in子查询的实现过程。下面我们将讨论为什么in方式与join方式性能差的原因?

2.    In比join慢的原因

首先我们通过oprofile来测试一下,两种情况各自的性能损耗在哪里?

Join

In

samples  %        symbol name                                                

444      11.2065  buf_calc_page_new_checksum                                 

337       8.5058  rec_get_offsets_func

326       8.2282  pthread_mutex_unlock                                       

245       6.1837  pthread_mutex_lock

240       6.0575  cmp_dtuple_rec_with_match                                  

226       5.7042  row_search_for_mysql                                        

218       5.5023  row_sel_store_mysql_rec

104       2.6249  code_state

103       2.5997  ha_insert_for_fold                                         

97        2.4483  page_cur_search_with_match                                 

83        2.0949  pthread_mutex_trylock                                      

79        1.9939  pthread_getspecific                                        

77        1.9435  memcpy

76        1.9182  _db_enter_                                                  

67        1.6911  btr_search_guess_on_hash

63        1.5901  evaluate_join_record(JOIN*, st_join_table*, int)           

62        1.5649  safe_mutex_lock

52        1.3125  DoTrace

51        1.2872  Arg_comparator::compare_int_signed()                       

51        1.2872  _db_return_

49        1.2367  btr_search_build_page_hash_index                           

46        1.1610  btr_cur_search_to_nth_level                                

46        1.1610  ha_innobase::general_fetch(unsigned char*, unsigned int, unsigned int)

37        0.9339  safe_mutex_unlock                                          

33        0.8329  ha_innobase::unlock_row()

32        0.8077  ha_remove_all_nodes_to_page

30        0.7572  btr_search_drop_page_hash_index                            

28        0.7067  _db_doprnt_

27        0.6815  _db_pargs_

26        0.6562  join_read_key(st_join_table*)                              

26        0.6562  lock_clust_rec_cons_read_sees                               

24        0.6058  cp_buffer_from_ref(THD*, st_table*, st_table_ref*)         

24        0.6058  row_get_rec_sys_field                                      

19        0.4796  Field_long::val_int()

19        0.4796  ha_delete_hash_node

19        0.4796  ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function)

19        0.4796  mtr_memo_slot_release                                      

19        0.4796  row_sel_convert_mysql_key_to_innobase                       

18        0.4543  Item_field::val_int()

samples  %        symbol name                                                

432       7.4922  pthread_mutex_unlock                                       

423       7.3361  buf_calc_page_new_checksum                                 

373       6.4690  rec_get_offsets_func                                       

283       4.9081  row_search_for_mysql                                       

256       4.4398  pthread_mutex_lock                                          

242       4.1970  _db_enter_                                                 

242       4.1970  cmp_dtuple_rec_with_match                                  

206       3.5727  row_sel_store_mysql_rec                                    

190       3.2952  code_state                                                 

181       3.1391  _db_return_                                                

151       2.6188  pthread_getspecific                                        

146       2.5321  DoTrace                                                    

124       2.1505  build_template(row_prebuilt_struct*, THD*, st_table*, unsigned int)    

108       1.8730  page_cur_search_with_match                                 

99        1.7170  ha_insert_for_fold

87        1.5088  btr_search_guess_on_hash                                   

83        1.4395  evaluate_join_record(JOIN*, st_join_table*, int)           

81        1.4048  pthread_mutex_trylock

80        1.3874  safe_mutex_lock

76        1.3181  memcpy

57        0.9886  JOIN::exec()                                                

56        0.9712  __errno_location                                           

54        0.9365  _db_doprnt_

50        0.8672  btr_search_build_page_hash_index                           

50        0.8672  ha_innobase::general_fetch(unsigned char*, unsigned int, unsigned int)

47        0.8151  dict_index_copy_types

46        0.7978  btr_cur_search_to_nth_level                                

41        0.7111  _my_thread_var

39        0.6764  Field_long::val_int()

38        0.6590  Arg_comparator::compare_int_signed()                       

36        0.6243  safe_mutex_unlock

34        0.5897  cp_buffer_from_ref(THD*, st_table*, st_table_ref*)         

33        0.5723  ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function)

32        0.5550  _db_pargs_

31        0.5376  JOIN::cleanup(bool)

31        0.5376  ha_innobase::unlock_row()                                   

29        0.5029  join_read_key(st_join_table*)                              

29        0.5029  sub_select(JOIN*, st_join_table*, bool)                     

通过对比我们发现在join出现的在in中也有,但是在in中占的采样比例比较高的几个在join中并没有(这里的没有并不是指join里没有调用它们,只是可能它们调用的次数太少导致在oprofile采样时没有获得它们的数据)。通过gdb我们发现两种方式都调用build_template,所以为了进一步查看它们的性能损耗,我们通过gcov比较两者的覆盖情况。通过它们的输出我们知道两者调用build_template的次数分别是join VS in:174 620786;而这个函数的内部有一个for循环用于拷贝,判断哪些字段是需要被保留的。对于in的方式这个for内部执行了3370262次,而join只执行了1036次。我们再来看一下这个函数的bt:

#0  build_template (prebuilt=0x2aaaabbc40b8, thd=0x6f7f980, table=0x6fd9780, templ_type=1) at handler/ha_innodb.cc:3564
#1  0x000000000083953b in ha_innobase::change_active_index (this=0x6fdad30, keynr=0) at handler/ha_innodb.cc:4834
#2  0x0000000000839ef7 in ha_innobase::index_init (this=0x6fdad30, keynr=0, sorted=<value optimized out>) at handler/ha_innodb.cc:4508
#3  0x00000000006005f8 in join_read_key (tab=0x6fe4ef0) at handler.h:1180
#4  0x00000000005e41d3 in sub_select (join=0x6fe11d8, join_tab=0x6f7f980, end_of_records=128) at sql_select.cc:11383
#5  0x0000000000601d30 in do_select (join=0x6fe11d8, fields=0x6fd5300, table=0x0, procedure=0x0) at sql_select.cc:11140
#6  0x000000000060a479 in JOIN::exec (this=0x6fe11d8) at sql_select.cc:2314
#7  0x00000000004d5102 in subselect_single_select_engine::exec (this=0x6fdcda0) at item_subselect.cc:1987
#8  0x00000000004d26b1 in Item_subselect::exec (this=0x6fdccb0) at item_subselect.cc:280
#9  0x00000000004d1aaa in Item_in_subselect::val_bool (this=0x2aaaabbc40b8) at item_subselect.cc:880
#10 0x0000000000438821 in Item::val_bool_result (this=0x2aaaabbc40b8) at item.h:745
#11 0x0000000000476941 in Item_in_optimizer::val_int (this=0x6fe2a58) at item_cmpfunc.cc:1833
#12 0x00000000005e3d9a in evaluate_join_record (join=0x6fdce98, join_tab=0x6fe3538, error=<value optimized out>) at sql_select.cc:11434
#13 0x00000000005e4215 in sub_select (join=0x6fdce98, join_tab=0x6fe3538, end_of_records=<value optimized out>) at sql_select.cc:11391
#14 0x0000000000601d30 in do_select (join=0x6fdce98, fields=0x6f819a0, table=0x0, procedure=0x0) at sql_select.cc:11140
#15 0x000000000060a479 in JOIN::exec (this=0x6fdce98) at sql_select.cc:2314
#16 0x000000000060ae0f in mysql_select (thd=0x6f7f980, rref_pointer_array=0x6f81a68, tables=0x6fd4d90, wild_num=1, fields=@0x6f819a0, conds=0x6fdccb0,    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x6fdce78, unit=0x6f81470, select_lex=0x6f81898)   at sql_select.cc:2509

表 3build_template的bt

通过它我们可以看到该函数是由index_init调用的,而index_init又是在sub_select每次读取第一条记录的时候执行的,对于eq_ref则是调用join_read_key函数(注:e表,对于s表则调用join_read_always_key,它也有类似的过程)

static int

join_read_key(JOIN_TAB *tab)

{

  int error;

  TABLE *table= tab->table;

  if (!table->file->inited)

  {

    table->file->ha_index_init(tab->ref.key, tab->sorted); //在函数内部把table->file->inited赋值为INDEX;

  }

 …

可以看到table->file->inited这个变量决定着index_init的执行。那么它在哪里重新被赋值为0?答案就是ha_index_end(),而这个函数的执行是在:(再次bt。。。哈哈)

#0  st_join_table::cleanup (this=0x6fe28d0) at handler.h:1186
#1  0x000000000060120b in JOIN::cleanup (this=0x6fe0f10, full=<value optimized out>) at sql_select.cc:6982
#2  0x0000000000601734 in JOIN::join_free (this=0x6fe0f10) at sql_select.cc:6905
#3  0x0000000000601db2 in do_select (join=0x6fe0f10, fields=0x6f819a0, table=0x0, procedure=0x0) at sql_select.cc:11161
#4  0x000000000060a479 in JOIN::exec (this=0x6fe0f10) at sql_select.cc:2314
#5  0x000000000060ae0f in mysql_select (thd=0x6f7f980, rref_pointer_array=0x6f81a68, tables=0x6fd5198, wild_num=0, fields=@0x6f819a0, conds=0x6fdd218,    og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x6fdd398, unit=0x6f81470, select_lex=0x6f81898)  at sql_select.cc:2509

注:这里并没有显示这个函数名,但是handler.h:1186这条语句就是在ha_index_end()。我们看到这个函数是在do_select进行清除操作的时候调用的,而我们前面也已经说明了do_select是nest-loop的入口及出口地方,那么就是说每执行一交nest-loop的话都要进行一次index_init操作。对于in的类型,图4我们已经说明了它有count(t)的nest-loop过程,所以它就要执行443308次,这个刚才就等于gcov输出的join_read_key:table->file->ha_index_init(tab->ref.key, tab->sorted);而join_read_always_key:table->file->ha_index_init(tab->ref.key,tab->sorted);总共执行了177224次,这个数字刚好是select count(*) from titles t straight_join employees e on(e.emp_no=t.emp_no)  where e.gender='F';即每条满足t与e join条件的记录。通过这两组数据,也验证了我们上面关于in的执行过程的描述。同样的这两个函数在join方式下都只执行了一次,因为它们只执行了一次nest-loop。

3.    总结

我们这里只是分析了占采样比例较高的build_template情况,对于其它的也是一样的分析方法,这里不再赘述。对于直接join方式与in子查询方式,两者select的时间复杂度是一样的(注:这里的select是指获得数据的方式,个数)。唯一不同的是对于in子查询它每次执行内部查询的时候都必须重新构造一个JOIN结构,完成相应的初始化操作,并且在这次内部查询结束之后,要完成相应的析构函数,如index_init,index_end,而当外部查询是全表扫描的时候这些操作的次数就是它的记录数,那么它们(构造,析构)所占用的性能也是显而易见的。简单一句话子查询的性能除了查询外,还消耗在JOIN的构造与析构过程,也就是上面表2的【t表】与【e表】中间的部分。

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页