先把现在的情况说下,表名什么的都是虚拟的
数据库中有表:
xxx_log
涉及主要字段:userid 用户编号
amount 用户帐户变化情况(也就是用户的充值,消费等情况)
type 帐户变化类型(在线充值,汇款,消费类型1,消费类型2等),这个字段用来表示用户的帐户是做什么样的变化,当属于充值类的时候,consum的值是正数,当属于消费类的时候consum的数值是负数,另外还有其他的变化类型,可能是-1,0等等,这个暂时不重要
recordtime 发生变化的日期时间(以时间戳的形式存储,如:1214627387)三个字段都做了索引问题:
现有一条SQL语句(老的统计方法):
select sum(amount) from consume_log where (type =2001 or type=1001) and recordtime <1212163200 and recordtime>=1209571200
SQL语句用来查询出某个月的1号到执行任务的那天的用户消费情况(type =2001 or type=1001),即统计这段时间内用户在type类型下一花消费了多少多少钱.
统计完后,用JAVA代码来判断消费属于哪个档次(小额,还是大额,还是其他什么的),并且统计每种消费档次的人数,然后将统计结果存储到另一个库里
下面是一段当前算法的伪代码: ResultSet rs = con.executeQuery(); //就上面那条SQL语句拿来查
int type = 0; while (rs.next()) {
type = getType(Math.abs(rs.getInt("total")), sect); //getType方法用来区分用户属于哪个档次的消费用户
totalAmount += Math.abs(rs.getInt("total")); //totalAmount,totalPersons是两个全局的变量,用来记录消费总额,和总人数,最后两者相除可以得到一个平均数.
totalPersons++; if (map.containsKey(type)) {
map.put(type, map.get(type) + 1);
} else {
map.put(type, 1);
}
}
最后,也是最重要的,xxx_log表里的数据量非常大,每天的数据量在1300万左右,而且这个数值每天都在增长
现在用原来的统计方法,从一号到30号的那条SQL语句执行时间太长(我曾经做过测试,6月1号到30号,执行时间在300秒左右),导致JAVA应用程序中出现数据库连接中断的现象,任务没办法继续进行下去现在我同事给我的参考是,把计算过程移到外面,让JAVA来做运算,而让SQL语句只查数据,一时没什么思路了,JAVA做运算的话,取出来的数据也是超级庞大
我刚刚试了下,select amount,userId from consume_log where (type =2001 or type=1001) and recordtime <1212163200 and recordtime>=1209571200 (这个是查所有5月份消费情况),光这样查,用时半分钟,数据量是1200多万.如果把这一千两百多万数据放到JAVA里去运算的话也没有思路了.最终问题就是想办法统计出一个月内用户的消费情况(总共消费了多少,平均每用户消费多少,每个消费档次的用户有多少),但数据量非常大,光一条SQL语句不一定能够胜任,如果用其他语言来处理计算,那又该怎么查,怎么算?希望大家帮忙想想,这个问题困了我快一个月了,再不解决,7月份的数据又出不来了,555555
数据库中有表:
xxx_log
涉及主要字段:userid 用户编号
amount 用户帐户变化情况(也就是用户的充值,消费等情况)
type 帐户变化类型(在线充值,汇款,消费类型1,消费类型2等),这个字段用来表示用户的帐户是做什么样的变化,当属于充值类的时候,consum的值是正数,当属于消费类的时候consum的数值是负数,另外还有其他的变化类型,可能是-1,0等等,这个暂时不重要
recordtime 发生变化的日期时间(以时间戳的形式存储,如:1214627387)三个字段都做了索引问题:
现有一条SQL语句(老的统计方法):
select sum(amount) from consume_log where (type =2001 or type=1001) and recordtime <1212163200 and recordtime>=1209571200
SQL语句用来查询出某个月的1号到执行任务的那天的用户消费情况(type =2001 or type=1001),即统计这段时间内用户在type类型下一花消费了多少多少钱.
统计完后,用JAVA代码来判断消费属于哪个档次(小额,还是大额,还是其他什么的),并且统计每种消费档次的人数,然后将统计结果存储到另一个库里
下面是一段当前算法的伪代码: ResultSet rs = con.executeQuery(); //就上面那条SQL语句拿来查
int type = 0; while (rs.next()) {
type = getType(Math.abs(rs.getInt("total")), sect); //getType方法用来区分用户属于哪个档次的消费用户
totalAmount += Math.abs(rs.getInt("total")); //totalAmount,totalPersons是两个全局的变量,用来记录消费总额,和总人数,最后两者相除可以得到一个平均数.
totalPersons++; if (map.containsKey(type)) {
map.put(type, map.get(type) + 1);
} else {
map.put(type, 1);
}
}
最后,也是最重要的,xxx_log表里的数据量非常大,每天的数据量在1300万左右,而且这个数值每天都在增长
现在用原来的统计方法,从一号到30号的那条SQL语句执行时间太长(我曾经做过测试,6月1号到30号,执行时间在300秒左右),导致JAVA应用程序中出现数据库连接中断的现象,任务没办法继续进行下去现在我同事给我的参考是,把计算过程移到外面,让JAVA来做运算,而让SQL语句只查数据,一时没什么思路了,JAVA做运算的话,取出来的数据也是超级庞大
我刚刚试了下,select amount,userId from consume_log where (type =2001 or type=1001) and recordtime <1212163200 and recordtime>=1209571200 (这个是查所有5月份消费情况),光这样查,用时半分钟,数据量是1200多万.如果把这一千两百多万数据放到JAVA里去运算的话也没有思路了.最终问题就是想办法统计出一个月内用户的消费情况(总共消费了多少,平均每用户消费多少,每个消费档次的用户有多少),但数据量非常大,光一条SQL语句不一定能够胜任,如果用其他语言来处理计算,那又该怎么查,怎么算?希望大家帮忙想想,这个问题困了我快一个月了,再不解决,7月份的数据又出不来了,555555
我的测试CREATE TABLE aa(account int(10),
memberid int(10),
addtime int(10)
)
partition by list(memberid)
(
partition p0 values in(1),
partition p1 values in(2),
partition p2 values in(3),
partition p3 values in(4),
partition p4 values in(5),
partition p5 values in(6),
partition p6 values in(7),
partition p7 values in(8),
partition p8 values in(9),
partition p9 values in(10)
);aa表1600w条记录
不使用分区
select sum(account) from aa where memberid = 1;
+--------------+
| sum(account) |
+--------------+
| 170687744 |
+--------------+
1 row in set (16.34 sec)使用分区后
select sum(account) from aa where memberid = 1 or memberid = 4;
+--------------+
| sum(account) |
+--------------+
| 340063488 |
+--------------+
1 row in set (2.88 sec)
效果比较明显
都放到存储过程中去处理 脱离程序
recordtime between 1212163200 and 1209571200
recordtime between 1212163200 and 1209571200
建立TYPE、recordtime的复合索试试
早上试过between and,效率比原来好一点,不过还是不理想,再想想别的办法吧,谢谢大家了!~