MYSQL SUBQUERY执行过程

2021-08-27 23:33:48

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

环境

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的执行时间与执行计划：

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)

  {

    int error;

    if (in_first_read)

    {

      in_first_read= false;
       //表的read first record记录
      error= (*join_tab->read_first_record)(join_tab);

    }

    else
       ////取出表的下一行记录直到最后一条记录
      error= info->read_record(info);

    DBUG_EXECUTE_IF("bug13822652_1", join->thd->killed= THD::KILL_QUERY;);

    if (error > 0 || (join->thd->is_error()))   // Fatal error

      rc= NESTED_LOOP_ERROR;

    else if (error < 0)
      //以error状态判断数据是否取完，取完后循环在此终止
      break;

    else if (join->thd->killed)     // Aborted by user

    {

      join->thd->send_kill_message();

      rc= NESTED_LOOP_KILLED;

    }

    else

    {

      if (join_tab->keep_current_rowid)

        join_tab->table->file->position(join_tab->table->record[0]);
       //对获取到的行记录，进行比较，该函数内部可能会继续调用sub select，产生nest loop
      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());

    if (join->thd->killed)

    {

      join->thd->send_kill_message();

      DBUG_RETURN(NESTED_LOOP_KILLED);

    }

    /* check for errors evaluating the condition */

    if (join->thd->is_error())

      DBUG_RETURN(NESTED_LOOP_ERROR);

  }

@@2

enum enum_nested_loop_state rc;

      /* A match from join_tab is found for the current partial join. */

      rc= (*join_tab->next_select)(join, join_tab+1, 0);

      join->thd->get_stmt_da()->inc_current_row_for_warning();

      if (rc != NESTED_LOOP_OK)

        DBUG_RETURN(rc);

推测和结论

从代码调试的结果看，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

id	select_T	table	T	possible_keys	key	key_len	ref	rows	filtered	Extra
1	PRIMARY		ALL	NULL	NULL	NULL	NULL	5	100.00	Using where
1	PRIMARY	h_1	eq_ref	PRIMARY,idx_T	PRIMARY	4	alitest.ceshi	1	100.00	Using where
1	PRIMARY	o	eq_ref	PRIMARY	PRIMARY	4	alitest.h_1.T	1	100.00	NULL
2	DERIVED	h_11	index_merge	index_HI,idx_T,idx_F	idx_F,index_HI	5,5	NULL	6	83.33	Using intersect(idx_F,index_HI); Using where; Using filesort
2	DERIVED	o1	eq_ref	PRIMARY	PRIMARY	4	alitest.h_11.T	1	100.00	Using index

码农公寓