最近着手对数据量比较大的表的改善工作。当一张表的数据量很大,并且在定时或实时的增加数据时,这时候就需要考虑表的容量,因为一张表的数据不可能无限大,所以考虑分表就迫在眉睫~~
解决方案:
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
相关推荐
资源里包含SQL文档:创建数据库和存储过程.sql 生成数据.sql 测试.sql 比较完善的大量数据分表功能代码,包含...数据按照每个月创建一个数据表,通过存储过程输入参数,按照指定日期跨表查询数据,并且可以分页查询。
一个简单的oracle数据库环境下的存储过程,介绍了如何从带年月人的分表取数据
利用python进行数据表分表,目的是将一个大的excel文本按照规则生成对应的小文件
- 产品上线以后,数据量越来越大,当一个表有几十万上百万条记录的时候,是时候考虑分表了。【超过几千万记录的话,这个分表估计不适合。】 - 怎么做 - 分表情况,1:hash分表,按照目标表的id的hash值,写入对应...
利用MybatisPlus来实现对表的水平分表,全自动,可以首先判断表是否存在,不存在创建表,然后设计有定时任务,可以每个月月末的时候,去创建下一个分表程序
① 创建数据库 ② 在创建的数据库中添加文件组 ③ 在文件组中添加新的文件 ④ 定义分区函数 ⑤ 定义分区架构 ⑥ 定义分区表 ⑦ 定义代理作业,自动添加分区分割点 ⑧ 测试数据
水平拆分数据分库和分表的核心问题是表的ID唯一,然后根据唯一的ID映射到一个物理存储位置,这个映射方案要考虑到满足数据量暴增线性扩展和业务上容易保持一致,本文主要讨论分库分表如何映射的问题。
kettle对数据分表插入 数据库Oracle etl工具 kettle 通过java脚本,hash算法,实现分表数据写入
大数据分表技术方案.pdf
主要介绍了超大数据量存储常用数据库分表分库算法总结,本文讲解了按自然时间来分表/分库、按数字类型hash分表/分库、按md5值来分表/分库三种方法,以及分表所带来的问题探讨,需要的朋友可以参考下
分表是一种数据库分割技术,用于将大表拆分成多个小表,以提高数据库的性能和可管理性。在MySQL中,可以使用多种方法进行分表,例如基于范围、哈希或列表等...下面将详细介绍MySQL如何分表以及分表后如何进行数据查询。
spring动态数据源整合mybatis实现的分库分表操作。 分库分表对业务代码是透明的,只需要在配置文件分配分库模版即可动态的实现分库分表的增删改查操作。
本文讲的是mysql大数据分库和分表 php解决方案。 mysql分库分表方案、mysql 分库方案、php实现mysql分库分表、mysql高并发解决方案。
分区表实际上是把逻辑上的一个大表分割成物理上的几小块,提供了很多好处,比如: 1、查询性能大幅提升 2、删除历史数据更快 3、可将不常用的历史数据使用表空间技术转移到低成本的存储介质上 那么什么时候该使用...
SQL,大数据分表,SQL千万级分表,删除百万数据
MySQL分区分表方案实践手册
我们可以将两个表连起来一起查询数据,我现在有两张表,一个是feedback表和member表,如图: 总目录: 上代码: $where = array(); $where['meiyepin_feedback.member_id'] = 1; $Model = M('feedback'); $a = ...
这是一份康盛数据分表的实现代码,支持自动分表,同时支持跨表跨范围定位数据。
通过使用sharding-jdbc分库分表,以及多数据源的切换
基于mybatis插件实现轻量级分库分表方案-亿级数据mysql存储解决方案-mybatis-sharding