sql - postgreSQL 中的汇总表以获得更好的性能
问题描述
我使用 postgreSQL 作为我的数据库。我有一个表 MASTER(A, B, C, D, N1, N2, N3, N4, N5, N6) 主键是 (A, B, C, D) 和 N1, N2, N3, N4, N5 , N6 是数字列。
我有一个如下查询来获取从 MASTERCOMB 中的每个列表中选择的每个 A 的汇总数据。
SELECT MASTERCOM.A
,STATS.sumn1
,STATS.sumn2
,STATS.sumn3
,STATS.sumn4
,STATS.sumn5
,STATS.sumn6
FROM (WITH
sum1 AS (SELECT A, SUM(N1) FROM MASTER WHERE B = $1 GROUP BY A ORDER BY SUM(N1) DESC LIMIT $2),
sum2 AS (SELECT A, SUM(N2) FROM MASTER WHERE B = $1 GROUP BY A ORDER BY SUM(N2) DESC LIMIT $2),
sum3 AS (SELECT A, SUM(N3) FROM MASTER WHERE B = $1 GROUP BY A ORDER BY SUM(N3) DESC LIMIT $2),
sum4 AS (SELECT A, SUM(N4) FROM MASTER WHERE B = $1 GROUP BY A ORDER BY SUM(N4) DESC LIMIT $2),
sum5 AS (SELECT A, SUM(N5) FROM MASTER WHERE B = $1 GROUP BY A ORDER BY SUM(N5) DESC LIMIT $2),
sum6 AS (SELECT A, SUM(N6) FROM MASTER WHERE B = $1 GROUP BY A ORDER BY SUM(N6) DESC LIMIT $2)
SELECT DISTINCT COALESCE(sum1.A, sum2.A, sum3.A, sum4.A, sum5.A, sum6.A) A
FROM sum1
FULL OUTER JOIN sum2 ON sum2.A = sum1.A
FULL OUTER JOIN sum3 ON sum3.A = sum1.A
FULL OUTER JOIN sum4 ON sum4.A = sum1.A
FULL OUTER JOIN sum5 ON sum5.A = sum1.A
FULL OUTER JOIN sum6 ON sum6.A = sum1.A) MASTERCOMB
LEFT JOIN (SELECT A
,SUM(N1) sumn1
,SUM(N2) sumn2
,SUM(N3) sumn3
,SUM(N4) sumn4
,SUM(N5) sumn5
,SUM(N6 sumn6)
FROM MASTER WHERE B = $1 GROUP BY A) AS STATS
ON STATS.A = MASTERCOMB.A
这只是 WHERE 子句中带有 B 的一种查询。我可能需要使用不同的组合进行查询,例如“WHERE C = $3”或“WHERE D = $4”。在极少数情况下,我可能必须同时查询 B、C 和 D 上的多个条件的组合;
随着表的增长,查询的性能可能会下降。所以我在想两个方法
方法#1:
- 创建汇总表 SMRY_A_B、SMRY_A_C、SMRY_A_D
- 在 MASTER 表的每个插入、更新和删除上,对值求和并插入/更新/删除相应的表
方法#2:
- 使用主键(A、B、C、D)创建汇总表 SMRY_A_B_C_D
- 在 MASTER 表的每次插入、更新和删除时,对值和插入/更新/删除 SMRY_A_B_C_D 表求和
SMRY_A_B_C_D 的可能值可以是
(valA, valB, 'N/A', 'N/A', sumn1, sumn2, sumn3, sumn4, sumn5, sumn6)
(valA, 'N/A, valC, 'N/A', sumn1, sumn2, sumn3, sumn4, sumn5, sumn6)
(valA, 'N/A, 'N/A', 'valD', sumn1, sumn2, sumn3, sumn4, sumn5, sumn6)
问题:
- 哪种方法更好?
- 我不应该同时考虑主表本身的方法和查询吗?如果是这样,我应该优化查询吗?
解决方案
推荐阅读
- python - Python sympy 产生了奇怪的数字
- ios - 线程 1:项目中的 EXC_BAD_INSTRUCTION(代码=EXC_I386_INVOP,子代码=0x0)
- sql - 如何在 SQL Server 中聚合 JSON?
- android - 带有 GridLayoutManager 的 recyclerview 中的不同视图类型
- ios - 更改子视图的约束后如何调整父视图的大小?
- file - 使用 python 从文件创建邻接矩阵并将输出文件保存为 .mat 格式
- apache-spark - 当 hdfs 卷为 100% 时回收损坏的表
- javascript - 单击 Jquery 中搜索结果的任何位置时隐藏搜索结果
- path - 我无法选择使用 Java 8 打开。它转到 Internet Explorer,我陷入了一个循环。我该怎么办?
- python - 功能未给出预期结果,单元测试失败