登录/ 注册
 找回密码
 立即注册
关闭

CSDN热搜

程序园 精品问答 技术交流 资源下载
返回列表 发新帖
首页 › 业界区 › 安全 ›MySQL派生表查询大数据量无结果问题分析与解决 ...

MySQL派生表查询大数据量无结果问题分析与解决

蔺蓉城 2025-6-20 08:11:21
MySQL派生表查询大数据量无结果问题分析与解决

一、问题发现

在客户现场的一次问题报告中发现某个带有派生表进行查询的时候，数据量少的时候有结果，但是数据量大的时候返回无记录。看下面例子：
1、准备表
  1. CREATE TABLE `cmdb_item` (
  2.   `cm_item_id` varchar(350) NOT NULL,
  3.   `cm_model_id` varchar(350) NOT NULL,
  4.   PRIMARY KEY (`cm_item_id`,`cm_model_id`)
  5. );
  6. CREATE TABLE `sys_auth_role_cmdb` (
  7.   `id` varchar(60) NOT NULL,
  8.   `auth_type_id` varchar(60) DEFAULT NULL,
  9.   `cmdb_item_id` varchar(60) DEFAULT NULL,
  10.   `cmdb_model_id` varchar(60) DEFAULT NULL,
  11.   `role_id` varchar(60) DEFAULT NULL,
  12.   PRIMARY KEY (`id`)
  13. );
复制代码
2、一条数据场合
如下所示插入一条数据并且进行派生表查询，可以发现结果有一条数据，符合预期。
  1. greatsql> SELECT b.* FROM( SELECT cmdb_item_id FROM sys_auth_role_cmdb where1=0 union SELECT cm_item_id FROM cmdb_item) a LEFT JOIN cmdb_item b ON a.cmdb_item_id = b.cm_item_id WHERE b.cm_model_id='PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq' and
  2. cm_item_id='8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J';
  4. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | cm_item_id                                                                                                                                                                                                                                                       | cm_model_id                                                                                                                                                                                                                                                      |
  6. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  7. | 8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J | PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq |
  8. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  9. 1 row in set (3.50 sec)
复制代码
3、插入大量数据的场合
  1. -- 首先设置tmp_table_size参数为合适的值
  2. SET LOCAL tmp_table_size=2000000;
  4. set sql_mode="oracle";
  5. delimiter $$
  6. CREATE or replace PROCEDURE p1() as
  7. BEGIN
  8.   for i in 1 .. 5000 loop
  9.     insert into cmdb_item values('8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J'|| i,'PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq' || i);
  10.   end loop;
  11. END$$
  12. delimiter ;
  13. --插入5000条数据
  14. call p1();
  16. --执行跟上面一样的查询，可以发现结果为空，此处为问题
  17. greatsql> SELECT b.* FROM( SELECT cmdb_item_id FROM sys_auth_role_cmdb where1=0 union SELECT cm_item_id FROM cmdb_item) a LEFT JOIN cmdb_item b ON a.cmdb_item_id = b.cm_item_id WHERE b.cm_model_id='PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq' and cm_item_id='8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J';
  18. Empty set (0.00 sec)
复制代码
4、tmp_table_size值最小的场合
  1. -- 设置tmp_table_size参数为最小值
  2. SET LOCAL tmp_table_size=1024;
  4. -- 执行跟上面一样的查询，可以发现结果跟只有一条数据时候一样，出现一条数据
  5. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  6. | cm_item_id                                                                                                                                                                                                                                                       | cm_model_id                                                                                                                                                                                                                                                      |
  7. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  8. | 8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J | PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq |
  9. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  10. 1 row in set (3.50 sec)
复制代码
5、对比执行计划
查看上面几个场合的执行计划，结果如下：
数据量为1的场合执行计划：
  1. greatsql> explain analyze SELECT b.* FROM( SELECT cmdb_item_id FROM sys_auth_role_cmdb where1=0 union SELECT cm_item_id FROM cmdb_item) a LEFT JOIN cmdb_item b ON a.cmdb_item_id = b.cm_item_id WHERE b.cm_model_id='PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq' and cm_item_id='8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J';
  2. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
  4. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | -> Constant row from a  (cost=1207.97..1207.97 rows=1) (actual time=1107.026..1107.027 rows=1 loops=1)
  6.     -> Union materialize with deduplication  (cost=1207.62..1207.62 rows=4553) (actual time=1106.831..1106.831 rows=5001 loops=1)
  7.         -> Zero rows (Impossible WHERE)  (cost=0.00..0.00 rows=0) (actual time=0.000..0.000 rows=0 loops=1)
  8.         -> Covering index scan on cmdb_item using PRIMARY  (cost=752.32 rows=4553) (actual time=0.723..69.643 rows=5001 loops=1)
  9. |
  10. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 1 row in set (1.11 sec)
复制代码
数据量为5001的场合执行计划，可以看到下面的执行计划跟上面是一样的，不同点在于最后一步Constant row from a 这段的rows=0，说明执行计划没问题，但是执行阶段有问题。
  1. greatdb> explain analyze SELECT b.* FROM ( SELECT cmdb_item_id FROM sys_auth_role_cmdb WHERE 1=0 UNION SELECT cm_item_id FROM cmdb_item) a LEFT JOIN cmdb_item b ON a.cmdb_item_id = b.cm_item_id WHERE b.cm_model_id='PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq' AND cm_item_id='8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J';
  2. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  3. | EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
  4. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  5. | -> Constant row from a  (cost=1053.17..1053.17 rows=1) (actual time=1118.948..1118.948 rows=0 loops=1)这个地方rows=0说明问题在这里
  6.     -> Union materialize with deduplication  (cost=1052.82..1052.82 rows=4553) (actual time=1118.946..1118.946 rows=5001 loops=1)
  7.         -> Zero rows (Impossible WHERE)  (cost=0.00..0.00 rows=0) (actual time=0.001..0.001 rows=0 loops=1)
  8.         -> Covering index scan on cmdb_item using PRIMARY  (cost=597.52 rows=4553) (actual time=2.315..70.077 rows=5001 loops=1)
  9. |
  10. +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  11. 1 row in set (1.13 sec)
复制代码
二、问题调查过程

查询带有派生表的时候内部会创建临时表用于保存临时数据，因此先看一下上面几个场合的临时表情况：
tmp_table_size值数据量查询开始时候临时表情况查询过程中临时表情况说明20000001条数据内存表内存表没问题20000005001条数据内存表转为落盘表问题10245001条数据落盘表落盘表没问题先看一下读取数据的代码：
  1. int ConstIterator::Read() {
  2.   if (!m_first_record_since_init) {
  3.     return -1;
  4.   }
  5.   m_first_record_since_init = false;
  6.   // 这里打印err结果为-1，跳进read_const看一下为何没有数据返回
  7.   int err = read_const(table(), m_ref);
  8.   if (err == 0 && m_examined_rows != nullptr) {
  9.     ++*m_examined_rows;
  10.   }
  11.   table()->const_table = true;
  12.   return err;
  13. }
  15. int read_const(TABLE *table, Index_lookup *ref) {
  16.   int error;
  17.   DBUG_TRACE;
  18.   // 数据为一条的时候会进入这个if线正常读数据，但是数据为5001条的时候却没进入这个if线，可见问题在table的这个状态。
  19.   // 这里要求table->m_status = STATUS_NOT_STARTED才会进入
  20.   if (!table->is_started())  // If first read
  21.     这里面读取表数据
  22.   } else if (table->has_row() && table->is_nullable()) {}
  23.   return table->has_row() ? 0 : -1;
  24. }
复制代码
考虑上面的现象，数据量少和数据量多的场合结果不一样，这里面的区别在于union在查询阶段会创建临时物化表用于储存union结果，数据量少的时候在内存就可以创建物化表出结果了，但是如果数据量大的话，因为内存不够放，因此需要重新创建落盘表用于储存查询结果。
又考虑到上面这节，把内存表值改小结果又正常，因此可以推测是内存临时表改为落盘表后table的is_started()状态值改变了导致结果为空。这里可以从创建落盘表入手。找到创建落盘表的函数create_ondisk_from_heap()并且打个断点，看看堆栈：
  1. #0  create_ondisk_from_heap (
  2.     thd=0x55555accb1a0 <native_rw_unlock(native_rw_lock_t*)+24>, wtable=0x7fffc83eddc0,
  3.     error=0, insert_last_record=false, ignore_last_dup=false, is_duplicate=0x0)
  4.     at sql/sql_tmp_table.cc:2777
  5. #1  0x0000555559b98fb2 in MaterializeIterator<DummyIteratorProfiler>::MaterializeQueryBlock (this=0x7fff2023f390, query_block=..., stored_rows=0x7fffc83eef10)
  6.     at sql/iterators/composite_iterators.cc:1433
  7. #2  0x0000555559b9649e in MaterializeIterator<DummyIteratorProfiler>::Init (
  8.     this=0x7fff2023f390)
  9.     at sql/iterators/composite_iterators.cc:975
  10. #3  0x00005555597dae49 in Query_expression::ExecuteIteratorQuery (this=0x7fff2026d310,
  11.     thd=0x7fff20001050)
  12.     at sql/sql_union.cc:1814
  13. #4  0x00005555597db287 in Query_expression::execute (this=0x7fff2026d310,
  14.     thd=0x7fff20001050)
  15.     at sql/sql_union.cc:1877
  16. #5  0x0000555559700334 in Sql_cmd_dml::execute_inner (this=0x7fff20224010,
  17.     thd=0x7fff20001050)
  18.     at sql/sql_select.cc:872
  19. #6  0x00005555596ff502 in Sql_cmd_dml::execute (this=0x7fff20224010, thd=0x7fff20001050)
  20.     at sql/sql_select.cc:612
  21. #7  0x00005555596621f0 in mysql_execute_command (thd=0x7fff20001050, first_level=true)
  22.     at sql/sql_parse.cc:5227
  23. #8  0x0000555559664838 in dispatch_sql_command (thd=0x7fff20001050,
  24.     parser_state=0x7fffc83f0760, update_userstat=false)
  25.     at sql/sql_parse.cc:5905
  26. #9  0x0000555559658b03 in dispatch_command (thd=0x7fff20001050, com_data=0x7fffc83f1210,
  27.     command=COM_QUERY)
复制代码
查看MaterializeIterator::MaterializeQueryBlock函数代码，追踪一下table->is_started()状态值：
  1. bool MaterializeIterator<Profiler>::MaterializeQueryBlock(
  2.     error = t->file->ha_write_row(t->record[0]);
  4.     if (error == 0) {
  5.       ++*stored_rows;
  6.       continue;
  7.     }
  8.     // create_ondisk_from_heap will generate error if needed.
  9.     if (!t->file->is_ignorable_error(error)) {
  10.       bool is_duplicate;
  11.       // 这里table->m_status = STATUS_NOT_STARTED | STATUS_NOT_FOUND，正常初始值，符合预期
  12.       if (create_ondisk_from_heap(thd(), t, error,
  13.                                   /*insert_last_record=*/true,
  14.                                   /*ignore_last_dup=*/true, &is_duplicate))
  15.         return true; /* purecov: inspected */
  16.       // 这里执行完create_ondisk_from_heap以后table->m_status = STATUS_NOT_FOUND，可见在create_ondisk_from_heap函数内部改变了这个m_status
  17. }
  18. 查看create_ondisk_from_heap代码，看一下哪个地方改变了table->m_status值。
  19. bool create_ondisk_from_heap(THD *thd, TABLE *wtable, int error,
  20.                              bool insert_last_record, bool ignore_last_dup,
  21.                              bool *is_duplicate) {
  22.         // 执行下面这句的时候因为调用了table->set_row_status_from_handler(result)改变了m_status值，先从3变为0最后变为2
  23.         // 状态迁移情况：STATUS_NOT_STARTED | STATUS_NOT_FOUND ==> 0 ==> STATUS_NOT_FOUND
  24.         while (!table->file->ha_rnd_next(new_table.record[1])) {
  25.           write_err = new_table.file->ha_write_row(new_table.record[1]);
  26.           DBUG_EXECUTE_IF("raise_error", write_err = HA_ERR_FOUND_DUPP_KEY;);
  27.           if (write_err) goto err_after_open;
  28.         }
  29. }
复制代码
发现果然是create_ondisk_from_heap函数改变了table->is_started()状态值，所以导致了结果为空。
三、问题解决

结合上面分析，我们可以把代码作如下修复，就可以解决这个问题了。
  1. bool MaterializeIterator<Profiler>::MaterializeQueryBlock(
  2.     error = t->file->ha_write_row(t->record[0]);
  4.     if (error == 0) {
  5.       ++*stored_rows;
  6.       continue;
  7.     }
  8.     // create_ondisk_from_heap will generate error if needed.
  9.     if (!t->file->is_ignorable_error(error)) {
  10.       bool is_duplicate;
  11.       if (create_ondisk_from_heap(thd(), t, error,
  12.                                   /*insert_last_record=*/true,
  13.                                   /*ignore_last_dup=*/true, &is_duplicate))
  14.         return true; /* purecov: inspected */
  15.       // 这里把table->is_started()状态复原即可，问题解决。
  16.       t->set_not_started();
  17. }
复制代码
接着执行上面的查询，发现可以查出结果了。
四、问题总结

通过以上分析我们可以发现，带有派生表的查询会在内部创建临时表用于储存中间数据，根据tmp_table_size值设置会影响临时表存放地方，如果tmp_table_size设置小了那么一开始就会创建落盘表，如果设置大了那么创建的就是内存表，随着数据的增加后面可能会转为落盘表，因此才有了本文章提及的bug。分析类似问题的时候，需要结合查询计划的执行和临时表创建情况分析，才能更好解决问题。

Enjoy GreatSQL
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

使用道具 举报
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册
发帖
蔺蓉城
2025-6-20 08:11:21

0

粉丝关注

16

主题发布

板块介绍填写区域，请于后台编辑
微信扫一扫