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

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

 

解决方案：

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