MYSQL SUBQUERY执行过程

2021-11-07 08:20:56

作者：手辨

实为吾之愚见，望诸君酌之!闻过则喜，与君共勉


环境


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


MYSQL SUBQUERY执行过程

上一篇：关于limits生效的案例一两只


下一篇：ImportDatabaseBetweenInstances使用实例