欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2024-03-12 11:38:08
...

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

 

解决方案:

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

  • 数据量大的表的分表方案  以及  跨(同类型的)表查询遇上分组时需要注意的点
            
    
    博客分类: 数据库 同类型的表跨表查询时间字段分表 
  • 大小: 6.9 KB
  • 数据量大的表的分表方案  以及  跨(同类型的)表查询遇上分组时需要注意的点
            
    
    博客分类: 数据库 同类型的表跨表查询时间字段分表 
  • 大小: 7.3 KB
  • 数据量大的表的分表方案  以及  跨(同类型的)表查询遇上分组时需要注意的点
            
    
    博客分类: 数据库 同类型的表跨表查询时间字段分表 
  • 大小: 3.6 KB
  • 数据量大的表的分表方案  以及  跨(同类型的)表查询遇上分组时需要注意的点
            
    
    博客分类: 数据库 同类型的表跨表查询时间字段分表 
  • 大小: 3.5 KB
  • 数据量大的表的分表方案  以及  跨(同类型的)表查询遇上分组时需要注意的点
            
    
    博客分类: 数据库 同类型的表跨表查询时间字段分表 
  • 大小: 3.7 KB
  • 数据量大的表的分表方案  以及  跨(同类型的)表查询遇上分组时需要注意的点
            
    
    博客分类: 数据库 同类型的表跨表查询时间字段分表 
  • 大小: 6.4 KB