问题描述
RDS和官方MySQL 8.0.25，通过union 联合查询，在特殊场景下无法获取到数据。
如图所示，使用 union 查询不到结果（union all 正常），将查询数据量降低后又能查到结果了。
原因
知识点
参数：tmp_table_size
当需要使用内存临时表时，若所需的 size 小于当前配置值时，将使用内存存储引擎。
若所需size 大于此值时，将转换为磁盘临时表，将使用innodb 存储引擎。
原因
union 查询会使用临时表空间，当临时表空间大于 tmp_table_size 时，需要转换成磁盘临时表空间。在转化后，有个变量不符合预期，导致磁盘临时表中的数据全部被忽略，查不到数据。如前面截图中语句，limit 就是控制临时表空间大小的，所以当使用不同的数值，就会有出现不同的返回值。
测试复现流程
1. 建表
droptable if exists t1;createtable t1(id varchar(50));
2. 创建存储过程
delimiter $$ drop PROCEDURE if exists bulk_insert1;CREATE PROCEDURE bulk_insert1(INcountINTEGER)BEGINdeclare i int default 1;declare s1 varchar(50);declare s2 varchar(50);declare s3 varchar(50);declare s4 varchar(50);declare s5 varchar(50);declare str varchar(50);START TRANSACTION;while i<=count DO set s1 = substring(md5(rand()),1,10);set s2 = substring(md5(rand()),1,4);set s3 = substring(md5(rand()),1,4);set s4 = substring(md5(rand()),1,4);set s5 = substring(md5(rand()),1,10);set str = concat(s1,'-',s2,'-',s3,'-',s4,'-',s5);insertinto t1(id)values(str);set i=i+1;end while;COMMIT;END$$ delimiter ;
3. 插入数据
insertinto t1(id)values('1234567890-1234-1234-1234567890');call bulk_insert1(11999);
4. 测试结果
执行计划
5. 修改 tmp_table_size 从 2MB 增加到 16MB，同样的 SQL 符合预期
- 自建实例，可以通过命令直接修改。
set session internal_tmp_mem_storage_engine='memory';set session tmp_table_size=2097152; # 2*1024*1024;set session tmp_table_size=16777216; # 16*1024*1024;
- RDS 实例不支持通过命令修改global参考，需要通过控制台进行修改，但可以修改session 级别参数
控制台截图
6. 测试验证
selectcount(*)from((select id from t1 limit1000)unionselect1)as t where id='1234567890-1234-1234-1234567890';selectcount(*)from((select id from t1 limit12000)unionselect1)as t where id='1234567890-1234-1234-1234567890';selectcount(*)from((select id from t1 limit12000)union all select1)as t where id='1234567890-1234-1234-1234567890';
7. 修改后，验证union 可以获取到正常记录
结论：
- 查询少量数据时正常，可以使用内存中的临时表空间正常的，但超过就不适合预期了。
- 场景是tmp_table_size过小，临时表需要转到innodb，转化后，有个变量不符合预期，导致innodb临时表中的数据全部被忽略，查不到数据。
- 最终确定为MySQL 官方bug。
解决方案
推荐union all，因为这样能彻底改变执行计划，不会生成临时表空间，所以不会有此问题。
相关文档
此问题已经提交MySQL BUG：https://bugs.mysql.com/bug.php?id=108315
临时表相关文档：https://dev.mysql.com/doc/refman/8.0/en/internal-temporary-tables.html
