MySQL5.7 union all 大数据量问题

最新推荐文章于 2023-12-22 22:07:18 发布

linfeng763

最新推荐文章于 2023-12-22 22:07:18 发布

阅读量2.1k

点赞数 1

分类专栏：开发文章标签： mysql

本文链接：https://blog.csdn.net/linfeng763/article/details/106123698

版权

开发专栏收录该内容

1 篇文章 0 订阅

订阅专栏

问题现象

数据库有3张表，如chinese_score、math_score、english_score，分别代表学生的语文成绩、数学成绩、英语成绩，每张表的表结构和数据量一致，200万数据量。表结构如下：

ID(主键)	code(学号)	name(姓名)	score(成绩)

执行的SQL（获得每个学生的总成绩）：

select code, name, sum(t.score) total_score
from
    (
        select code, name, score from chinese_score
        union all
        select code, name, score from math_score
        union all
        select code, name, score from english_score
    ) t
group by code,name

运行结果
MySQL5.6中运行，结果total_score合计正常
MySQL5.7中运行，结果total_score的值为语文的成绩，没有合计3个总成绩
即如结果中，查看张三的总成绩（语文90、数学85、英语100），5.6版本总成绩为275，5.7版本为90

这么坑，它也没有报错，直接数据不对

解决问题

网上查找资料，发现有篇文章说到MySQL5.7之后对union all有更新：

MySQL在以下几种情况会创建临时表：
UNION查询（MySQL 5.7起，执行UNION ALL不再产生临时表，除非需要额外排序。）；

本来union all查询是中间产生临时表的，但是MySQL 5.7不产生临时表了，估计数据在内存了，然后估计数据量超出其限制了，就丢弃后面数据了（只是猜测）

因为我测试过每个150万数据量没有问题，而两个160万就不对了

select code, name, sum(t.score) total_score
from
    (
        select code, name, score from chinese_score order by code limit 1500000
        union all
        select code, name, score from math_score order by code limit 1500000
        union all
        select code, name, score from english_score order by code limit 1500000
    ) t
group by code,name

select code, name, sum(t.score) total_score
from
    (
        select code, name, score from chinese_score order by code limit 1600000
        union all
        select code, name, score from math_score order by code limit 1600000
    ) t
group by code,name

所以最终解决方法就是修改SQL，添加排序

select code, name, sum(t.score) total_score
from
    (
        select code, name, score from chinese_score
        union all
        select code, name, score from math_score
        union all
        select code, name, score from english_score
        order by code
    ) t
group by code,name

添加了 order by code
这样5.6和5.7运行都是正确的