本文探讨了MySQL中子查询使用IN操作符与直接JOIN在性能上的差异。通过对两种方法的执行计划和过程分析，揭示了IN子查询在全表扫描时会多次执行JOIN构造和析构，导致性能下降。主要原因是子查询的执行涉及到JOIN结构的重复构造和初始化，特别是在外部查询为全表扫描时，这个过程会被放大，增加了额外的性能开销。
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 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 |
表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 |
表 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 |
注：这里并没有显示这个函数名，但是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表】中间的部分。
扫一扫
1959
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
