数据量大的表的分表方案 以及 跨(同类型的)表查询遇上分组时需要注意的点

最近着手对数据量比较大的表的改善工作。当一张表的数据量很大,并且在定时或实时的增加数据时,这时候就需要考虑表的容量,因为一张表的数据不可能无限大,所以考虑分表就迫在眉睫~~

解决方案:

1)结合表内数据的Insert频率,大概计算下表数据的大小。规划每张表最大的数据量,考虑按年或月或天分表,表名基本相同,以时间字符串作为后缀(如:表名_yyyy,表名_yyyyMM,表名_yyyyMMdd等);

2)假如最小维度的表的每条记录是按分钟入的(总之不是天),时间字段为“yyyyMMdd”格式(即“天”)。那么要求出一段时间的(哪天到哪天)按天分组的统计结果,直接查询该最小维度的表即可。

若该表是按天分开存储,需考虑跨表的情形,关于跨表的思想见我前一篇文章;

3)假如需要查询一段时间(哪月到哪月)的按“月”分组统计的情况,则不再建议直接查询最小维度的表,因为既要考虑分表,而且union all的表的数量太多,造成卡死,效率上不太现实。

——解决:定时任务每月初去统计上一个月的总体情况,时间字段为"yyyyMM"格式(即“月”),如最小维度的表是按月存储的,则读取上一个月的汇总情况,成为一条记录,该条记录即是上一个月的总体情况,专门存入一张如“表名_month”的表中,此时,不必实时去最小维度的表汇总,直接读取该month表就行啦;

4)假如需要查询一段时间(哪年到哪年)的按“年”分组统计的情况,同上,可在"表名_month"表中,在月维度的数据基础上汇总,存储在"表名_year"表中,此时这张year的表的每一条记录代表一个“年”的总体情况,时间字段为"yyyy"格式(即“年”),此时直接读取"表名_year"表即可。

5)在按月和按年定时汇总数据到相应维度的表的同时,也要建立下一个月的月表或下一年的年表。

接下来要讲的是同类型的表遇上分组时需要注意的地方,现假设有2张表itm_test和itm_test2,他们的时间字段为app,按探讨的背景,这2张表的app字段的值肯定是不会重复的。。

这2张表的数据情况如下:

itm_test表:


数据量大的表的分表方案  以及  跨(同类型的)表查询遇上分组时需要注意的点
 itm_test2表:


数据量大的表的分表方案  以及  跨(同类型的)表查询遇上分组时需要注意的点
 (现在假设app为时间字段。。这2张表的时间字段的值是不会重复的……)

情形一:按单个字段分组,这个字段非时间字段app。

写法一(2张表都group by之后再union all,并且查询条件分散在每张表):

(SELECT src,SUM(cnt) FROM itm_test WHERE app>'app2' GROUP BY src ORDER BY src) 
UNION ALL (SELECT src,SUM(cnt) FROM itm_test2 WHERE app<'app7' GROUP BY src ORDER BY src)

 查询结果:


数据量大的表的分表方案  以及  跨(同类型的)表查询遇上分组时需要注意的点
 写法二(2张表先union all之后再group by,并且查询条件分散在每张表):

SELECT src, SUM(cnt) FROM ((SELECT * FROM itm_test WHERE app>'app2') UNION ALL (SELECT * FROM itm_test2 WHERE app<'app7')) tmp GROUP BY src ORDER BY src

 查询结果:


数据量大的表的分表方案  以及  跨(同类型的)表查询遇上分组时需要注意的点
  

写法三(2张表先union all之后再group by,并且查询条件在union all之后的临时表里):

SELECT src,SUM(cnt) FROM((SELECT  * FROM itm_test) UNION ALL (SELECT * FROM itm_test2 ) tmp 
  WHERE app > 'app2' AND app < 'app7' GROUP BY src ORDER BY src

 查询结果:


数据量大的表的分表方案  以及  跨(同类型的)表查询遇上分组时需要注意的点
 

综上:写法一是错误的,因为原意是要统计一段时间内按src的cnt的汇总,但是写法一却有2条nbk5,即2个src相同的记录,这是违背初衷的。

情形二:按多个字段分组,其中一个字段包含时间字段app。

写法一(2张表都group by之后再union all,并且查询条件分散在每张表):

(SELECT app,src,SUM(cnt) FROM itm_test WHERE app>'app2' GROUP BY app,src ORDER BY app,src ) 
UNION ALL (SELECT app,src,SUM(cnt) FROM itm_test2 WHERE app<'app7' GROUP BY app,src ORDER BY app,src )

 写法二(2张表先union all之后再group by,并且查询条件分散在每张表):

SELECT app,src, SUM(cnt) FROM ((SELECT * FROM itm_test WHERE app>'app2') UNION ALL (SELECT * FROM itm_test2 WHERE app<'app7')) tmp GROUP BY app,src ORDER BY app,src

 写法三(2张表先union all之后再group by,并且查询条件在union all之后的临时表里):

SELECT app,src,SUM(cnt) FROM((SELECT  * FROM itm_test) UNION ALL (SELECT * FROM itm_test2 ) tmp 
  WHERE app > 'app2' AND app < 'app7' GROUP BY src ORDER BY app,src

 三种写法都是同样的查询结果:


数据量大的表的分表方案  以及  跨(同类型的)表查询遇上分组时需要注意的点
 综上,此时是按多个字段分组统计的,并且其中有个字段是app(时间字段),三种写法都是等效的。

所以,当跨同类型的表查询遇上分组统计时,需要看时间字段是否在group by的条件当中:如果在,三种写法都是等效的,如果不在,写法一是错误的。查询条件不管是分散在每张表中还是集中在临时表中,都是一样的。临时表需要加上表别名,否则报错:

Every derived table must have its own alias

相关推荐