SQL统计某张表的数据问题,表内数据平均一天1千多万[高分,分不够可以加,只要能把问题解决]

[x03570227]

先把现在的情况说下,表名什么的都是虚拟的
数据库中有表:
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

[x03570227]

补充一点,数据库是MYSQL5.0,并且xxx_log表已经使用了分表技术

[reality]

用临时表是不是会快一些？
每个月的记录单独放到一个表中。

[fenlin1985]

可以做一个后台程序专门跑这个东西呀,每天晚上3点钟跑一次,然后把东西都记录下来,

然后用户只能查询 当前之前的数据..

[reality]

实时插入实时统计？？

[chensiping]

用mysql capi来做 每月一号在服务器上后台凌晨执行,执行的结果保存,java只查询保存结果

[yyz0832]

试试mysql分区
我的测试

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)
效果比较明显

[yyz0832]

根据实际情况，制定一个好的分区条件很重要。

[chensiping]

分区的目的是在执行sql的时候避免扫描无关的行,楼主的符合条件的行很多,再分区也没用.

看看连接设置的时长是多少,如果非要用java来实现的话,把时间设置长一点

[WWWWA]

select sum(amount) from consume_log where type in(2001,1001) and
recordtime between 1212163200 and 1209571200
建立TYPE、recordtime的复合索试试

[x03570227]

谢谢大家,我的问题现在变掉了,听说过段时间表结构要改,改成各种类别不同的表,总算是有救了,现在的问题只要能够撑到那个时间就OK了
早上试过between and,效率比原来好一点,不过还是不理想,再想想别的办法吧,谢谢大家了!~

[x03570227]

首先谢谢各位网友

可能没有阐述很清楚

这个统计其实是一个任务,每天的早上7点执行,执行的时候查询月初到任务执行时间的那段数据,结果被插入到另一个数据库的表中,便于查询,假如15号执行了一次任务,那么中间(1-14号)执行的结果其实都没有用的(旧数据会被清掉)

其实那表里记录的就是哪些用户在哪些时间里消费了多少,而统计的是各个用户在特定时间段里一共消费了多少,总消费了多少,总人数等

另外,XXX_LOG表我没办法修改,那张表专门有人定期维护.

我昨天还做了个测试,把< >=换成between and 执行时间比原来是少了一点,但效果不明显,以后数据量再大的话仍然会出现现在这个问题.

希望大家能再帮帮忙

[reality]

java程序在7点开始执行？
都放到存储过程中去处理 脱离程序

[wwwwb]

select sum(amount) from consume_log where type in(2001,1001) and
recordtime between 1212163200 and 1209571200