情景描述:数据存储在两个表中,其中表内容直接有少量重复数据及异同数据,可以理解为大表中存在大批量数据,小表中存在少量的重复数据及异同数据。
有以下方案可供参考:
1)union
2)union all+distinct
3)union all+group by
4)union all+开窗函数
其实优化前就使用的union 因为两个表结构是一致的,使用union 自动去重,方便而且对于数据量不大的情况 使用union是最好的选择。写法简单而且速度与union all 并不会有很大的区别
但是问题出现了:当数据量增加之后 查询 大表 union 小表 明显比只查询大表 慢很多。(ps:百万级千万级的分页查询)
以下是优化的思路,并不涉及到代码语句
union | 从两个已进行相应排序的输入表中,使用其排序顺序对行进行匹配 |
union al | 追加多个输入表以组成输出表 |
查看问题首先要知道是什么导致的慢,根据查询执行计划来看,order排序占用很大一部分资源(先集合 后排序)。
当然集合之前是有各自的索引的 但是集合之后 进行排序这个就没有用到。
如果不适用union的话 只能是union all了。
关于上述2,3,4点 均已测试 其实 union all+distinct 与union all+group by 区别不大。
忘记了看到谁写的一点 开窗函数over() 对大数据量 的速度有提升。经测试
union all+ROW_NUMBER() over(PARTITION BY A ORDER BY A) 查询当页数据 速度可以缩短到原先的1/10倍
有兴趣的可以尝试下 但是前提是数据量够大。
ps:优化之后的速度在100ms左右 优化前的速度1000ms左右。