作者：手辨
实为吾之愚见，望诸君酌之!闻过则喜，与君共勉
环境
version | 5.6.24-debug |
| version_comment | Source distribution |
| version_compile_machine | x86_64 |
| version_compile_os | Linux |
SQL
该SQL是一个subquery SQL
SELECT h_1.*, o.S FROM h h_1, p o WHERE o.id = h_1.T AND h_1.id IN ( SELECT substring_index(GROUP_CONCAT(h_11.id ORDER BY h_11.C DESC), ',', 1) FROM h h_11, p o1 WHERE h_11.HI = 90 AND h_11.F = 81 AND o1.id = h_11.T GROUP BY T )
问题
subquery内的单独的SQL耗时0.01S，合并起来后，整个SQL耗时4min20S，耗时非常长
执行时间与执行计划对比
整个SQL的执行时间与执行计划：
SELECT h_1.*, o.S FROM h h_1, p o WHERE o.id = h_1.T AND h_1.id IN ( SELECT substring_index(GROUP_CONCAT(h_11.id ORDER BY h_11.C DESC), ',', 1) FROM h h_11, p o1 WHERE h_11.HI = 90 AND h_11.F = 81 AND o1.id = h_11.T GROUP BY T )
7 rows in set (4 min 20.57 sec)
| id | select_T | table | T | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | PRIMARY | o | ALL | PRIMARY | NULL | NULL | NULL | 150 | NULL |
| 1 | PRIMARY | h_1 | ref | idx_T | idx_T | 5 | alitest.o.id | 278 | Using where |
| 2 | DEPENDENT SUBQUERY | h_11 | index_merge | index_HI,idx_T,idx_F | idx_F,index_HI | 5,5 | NULL | 6 | Using intersect(idx_F,index_HI); Using where; Using filesort |
| 2 | DEPENDENT SUBQUERY | o1 | eq_ref | PRIMARY | PRIMARY | 4 | alitest.h_11.T | 1 | Using index |
SQL拆分执行时间如下：
subquery SQL:
SELECT substring_index(GROUP_CONCAT(h_11.id ORDER BY h_11.C DESC), ',', 1) FROM h h_11, p o1 WHERE h_11.HI = 90 AND h_11.F = 81 AND o1.id = h_11.T GROUP BY T
7 rows in set (0.01 sec)
| id | select_T | table | T | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | h_11 | index_merge | index_HI,idx_T,idx_F | idx_F,index_HI | 5,5 | NULL | 6 | Using intersect(idx_F,index_HI); Using where; Using filesort |
| 1 | SIMPLE | o1 | eq_ref | PRIMARY | PRIMARY | 4 | alitest.h_11.T | 1 | Using index |
外层SQL:
SELECT h_1.*, o.S FROM h h_1, p o WHERE o.id = h_1.T
60000 rows in set (1.38 sec)
| id | select_T | table | T | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | o | ALL | PRIMARY | NULL | NULL | NULL | 150 | NULL |
| 1 | SIMPLE | h_1 | ref | idx_T | idx_T | 5 | alitest.o.id | 278 | NULL |
问题分析
分析方法
借助GDB调试MYSQL，确认问题
耗时环节代码
该SQL整体执行时，代码的主要执行部分分为2部分，这两部分构成了MYSQL的nested loop算法，分别如下：
代码1
sub_select (join=0x7fbe78005808, join_tab=0x7fbe78006738, end_of_records=false) at /opt/mysql-5.6.24/sql/sql_executor.cc:1203
主要代码块：该代码块以while进行循环，获取多表关联时第一个表的数据（取决于执行计划的执行顺序）循环读取并进行比较判断，while循环结束的前提是error<0，也就是数据取完
while (rc == NESTED_LOOP_OK && join->return_tab >= join_tab){
<span class="hljs-keyword">int</span> error;
<span class="hljs-keyword">if</span> (in_first_read)
{
in_first_read= <span class="hljs-literal">false</span>;
<span class="hljs-comment">//表的read first record记录</span>
error= (*join_tab->read_first_record)(join_tab);
}
<span class="hljs-keyword">else</span>
<span class="hljs-comment">////取出表的下一行记录直到最后一条记录</span>
error= info->read_record(info);
DBUG_EXECUTE_IF(<span class="hljs-string">"bug13822652_1"</span>, join->thd->killed= THD::KILL_QUERY;);
<span class="hljs-keyword">if</span> (error > <span class="hljs-number">0</span> || (join->thd->is_error())) <span class="hljs-comment">// Fatal error</span>
rc= NESTED_LOOP_ERROR;
<span class="hljs-keyword">else</span> <span class="hljs-keyword">if</span> (error < <span class="hljs-number">0</span>)
<span class="hljs-comment">//以error状态判断数据是否取完，取完后循环在此终止</span>
<span class="hljs-keyword">break</span>;
<span class="hljs-keyword">else</span> <span class="hljs-keyword">if</span> (join->thd->killed) <span class="hljs-comment">// Aborted by user</span>
{
join->thd->send_kill_message();
rc= NESTED_LOOP_KILLED;
}
<span class="hljs-keyword">else</span>
{
<span class="hljs-keyword">if</span> (join_tab->keep_current_rowid)
join_tab->table->file->position(join_tab->table->record[<span class="hljs-number">0</span>]);
<span class="hljs-comment">//对获取到的行记录，进行比较，该函数内部可能会继续调用sub select，产生nest loop</span>
rc= evaluate_join_record(join, join_tab);
}
}
代码2
evaluate_join_record (join=0x7fbe64005478, join_tab=0x7fbe640063a8) at /opt/mysql-5.6.24/sql/sql_executor.cc:1449
主要代码块：
@@1部分主要对拿到的数据进行判断，确认是否符合where后的条件，以该SQL为例，如果从表h h_1里拿到了一行数据，因为该表where后有判断条件，条件为：
h_1.id IN ( SELECT substring_index(GROUP_CONCAT(h_11.id ORDER BY h_11.C DESC), ',', 1) FROM h h_11, p o1 WHERE h_11.HI = 90 AND h_11.F = 81 AND o1.id = h_11.T GROUP BY T )
则该代码块（@@1）会对这个subquery进行调用（相当于重新执行一次这个subquery，gdb跟踪时可以跟踪到最终调用JOIN::exec->do_select->sub_select->evaluate_join_record），所以没取一次数据，就要对其进行判断，故这个subquery每一次都要重新执行，它并不是只执行一次拿到数据然后对比。
@@2 部分的*join_tab->next_select会重新调用sub_select，进入循环部分，获取下一个关联表的数据，并再次进入evaluate_join_record 进行一系列判断，直至数据取完
@@1
if (condition){
found= MY_TEST(condition->val_int());
<span class="hljs-keyword">if</span> (join->thd->killed)
{
join->thd->send_kill_message();
DBUG_RETURN(NESTED_LOOP_KILLED);
}
<span class="hljs-comment">/* check for errors evaluating the condition */</span>
<span class="hljs-keyword">if</span> (join->thd->is_error())
DBUG_RETURN(NESTED_LOOP_ERROR);
}
@@2
enum enum_nested_loop_state rc; <span class="hljs-comment">/* A match from join_tab is found for the current partial join. */</span>
rc= (*join_tab->next_select)(join, join_tab+<span class="hljs-number">1</span>, <span class="hljs-number">0</span>);
join->thd->get_stmt_da()->inc_current_row_for_warning();
<span class="hljs-keyword">if</span> (rc != NESTED_LOOP_OK)
DBUG_RETURN(rc);</code></pre>推测和结论
从代码调试的结果看，subquery并不是执行一次就结束，mysql针对这个查询，会先执行外层查询（while循环，具体循环次数取决于记录数），然后每一次都要调用evaluate_join_record 进行判断（无论是p o表还是h h_1表），当取h h_1表时，每一次读取都会对subquery进行一次编译，循环往复，直至数据取完，所以在这个过程中，subquery的SQL会被执行很多次，造成耗时增加。
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information
For DEPENDENT SUBQUERY, the subquery is re-evaluated only once for each set of different values of the variables from its outer context.
解决办法
改写为join查询：
SELECT h_1.*, o.S FROM h h_1, p o, ( SELECT SUBSTRING_INDEX(GROUP_CONCAT(h_11.id ORDER BY h_11.C DESC), ',', 1) AS ceshi FROM h h_11, p o1 WHERE h_11.HI = 90 AND h_11.F = 81 AND o1.id = h_11.T GROUP BY T ) alitest WHERE o.id = h_1.T AND h_1.id = alitest.ceshi
