MySQL派生表处理大数据量无结果的探究与应对
一、问题的出现
在客户现场的一次问题反馈中，发现使用带有派生表的查询时，数据量少时能得到结果，但数据量大时却无记录返回。以下是具体示例：
1、表的创建
CREATE TABLE `cmdb_item` (
`cm_item_id` varchar(350) NOT NULL,
`cm_model_id` varchar(350) NOT NULL,
PRIMARY KEY (`cm_item_id`,`cm_model_id`)
);
CREATE TABLE `sys_auth_role_cmdb` (
`id` varchar(60) NOT NULL,
`auth_type_id` varchar(60) DEFAULT NULL,
`cmdb_item_id` varchar(60) DEFAULT NULL,
`cmdb_model_id` varchar(60) DEFAULT NULL,
`role_id` varchar(60) DEFAULT NULL,
PRIMARY KEY (`id`)
);
2、少量数据场景
插入一条数据后进行派生表查询，能得到符合预期的结果。
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='PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq' and
cm_item_id='8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cm_item_id | cm_model_id |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J | PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (3.50 sec)
3、大量数据场景
设置tmp_table_size参数后，插入5000条数据，再执行相同查询时无结果。
-- 首先设置tmp_table_size参数为合适的值
SET LOCAL tmp_table_size=2000000;
set sql_mode="oracle";
delimiter $$
CREATE or replace PROCEDURE p1() as
BEGIN
for i in 1 .. 5000 loop
insert into cmdb_item values('8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J'|| i,'PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq' || i);
end loop;
END$$
delimiter ;
--插入5000条数据
call p1();
--执行跟上面一样的查询，可以发现结果为空，此处为问题
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='PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq' and cm_item_id='8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J';
Empty set (0.00 sec)
4、tmp_table_size最小值场景
将tmp_table_size设为最小值后，执行查询又能得到结果。
-- 设置tmp_table_size参数为最小值
SET LOCAL tmp_table_size=1024;
-- 执行跟上面一样的查询，可以发现结果跟只有一条数据时候一样，出现一条数据
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cm_item_id | cm_model_id |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J | PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (3.50 sec)
5、执行计划对比
查看不同场景的执行计划，数据量为1时和5001时执行计划有差异，主要体现在Constant row from a部分的行数不同。
数据量为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='PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq' and cm_item_id='8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Constant row from a (cost=1207.97..1207.97 rows=1) (actual time=1107.026..1107.027 rows=1 loops=1)
-> Union materialize with deduplication (cost=1207.62..1207.62 rows=4553) (actual time=1106.831..1106.831 rows=5001 loops=1)
-> Zero rows (Impossible WHERE) (cost=0.00..0.00 rows=0) (actual time=0.000..0.000 rows=0 loops=1)
-> Covering index scan on cmdb_item using PRIMARY (cost=752.32 rows=4553) (actual time=0.723..69.643 rows=5001 loops=1)
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (1.11 sec)
数据量为5001的场合执行计划：
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='PmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmqPmqECfANKxmbpgt3XBWaxzF6dx32dLmq' AND cm_item_id='8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J8b0zIlHsiarv8Ls6NejWji6Cu4h3iN2J';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Constant row from a (cost=1053.17..1053.17 rows=1) (actual time=1118.948..1118.948 rows=0 loops=1)这个地方rows=0说明问题在这里
-> Union materialize with deduplication (cost=1052.82..1052.82 rows=4553) (actual time=1118.946..1118.946 rows=5001 loops=1)
-> Zero rows (Impossible WHERE) (cost=0.00..0.00 rows=0) (actual time=0.001..0.001 rows=0 loops=1)
-> Covering index scan on cmdb_item using PRIMARY (cost=597.52 rows=4553) (actual time=2.315..70.077 rows=5001 loops=1)
|
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (1.13 sec)
二、问题探究过程
带有派生表的查询会创建临时表存储中间数据，观察不同场景下临时表情况：
| tmp_table_size值 | 数据量 | 查询开始时临时表情况 | 查询过程中临时表情况 | 说明 |
© 版权声明
文章版权归作者所有，未经允许请勿转载。
相关文章
没有相关内容!
暂无评论...
