MYSQL优化,或者其他的解决方案

amu1433 2015-09-13 09:18:12

备注mar_user表（会员信息）$Cookies0为会员ID （数据30万）
备注mar_sum表（统计会员每天的点击数）
备注mar_user_log表（会员点击一次,写入数据库一次,数据700W）
原因:mar_user_log每天大概能写入80万左右的数据...,上了700W左右,磁盘的I/O不行..PHP-cgi容易出错..

数据库中的关联键
mar_user(id)=mar_sum(sum_uid)=mar_user_log(nl_uid);

第一种方案:
改成了以下方案...让用户去触发..将不是今天的数据统计后,添加到mar_sum表,然后删除除过今天的数据..只保留今天的...

mar_user_log表的增长速度有所下降...但是还是达不到要求!

缺点:并不是每个会员都会每天登录..去触发这个统计..



mysql_query("SET AUTOCOMMIT=0");//设置为不自动提交，因为MYSQL默认立即执行

mysql_query("BEGIN");//开始事务定义



$sql_m="INSERT INTO mar_sum(sum_uid,sum_fen,sum_type,sum_time) SELECT nl_uid,SUM(nl_fen) as q,nl_ew,DATE_FORMAT(nl_time,'%Y-%m-%d') AS OrderDay FROM mar_user_log  where   nl_uid=".$Cookies0."  and nl_ew=3  and date_format(nl_time,'%Y-%m-%d')<curdate() GROUP by OrderDay ";



if(!mysql_query($sql_m)){

mysql_query('rollback');//判断当执行失败时回滚

exit('打不开?请在公共平台截图你的问题,');

}



$sql_n="Delete FROM mar_user_log WHERE nl_uid=".$Cookies0." and nl_ew=3  and  date_format(nl_time,'%Y-%m-%d')<curdate() ";



if(!mysql_query($sql_n)){

mysql_query('rollback');//判断当执行失败时回滚

}



mysql_query('commit') or exit(mysql_error());//执行事务

mysql_query("SET AUTOCOMMIT=1");



}

第二种方案:
循环每个会员ID定时去触发这个统计,然后删除mar_user_log表的数据..
备注mar_user用户表大概有30万数据



$ip_sql="select id from mar_user  ";

$res9=_query($ip_sql);

$i=0;

while (!!$rs1=_fetch_array($res9)){

$Cookies0      = $rs1['id'];



$sql_m="INSERT INTO mar_sum(sum_uid,sum_fen,sum_type,sum_time) SELECT nl_uid,SUM(nl_fen) as q,nl_ew,DATE_FORMAT(nl_time,'%Y-%m-%d') AS OrderDay FROM mar_user_log  where   nl_uid=".$Cookies0."  and nl_ew=3  and date_format(nl_time,'%Y-%m-%d')<curdate() GROUP by OrderDay ";

_query($sql_m);



$sql_n="Delete FROM mar_user_log WHERE nl_uid=".$Cookies0." and nl_ew=3  and  date_format(nl_time,'%Y-%m-%d')<curdate() ";

_query($sql_n);



}

缺点:PHP执行不了这么大的数据..基本就是卡死了..或者超时了...

对于储存过程能否解决我的问题..或者还有其他方法,可以解决mar_user_log的点击暴增问题...

我的运行环境:window 2008 R2 （mysql5.6）
window下的定时任务..真的不怎么好使,mysql有定时任务？
备注:储存过程，了解甚少,请写全代码,要不看不懂...在线等..希望今晚解决了..我晚上后半夜就可以开始..白天在线人数多.还不敢怎么太动数据..影响用户...

...全文

125 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

LongRui888 2015-09-15

打赏
举报

引用 8 楼 amu1433 的回复:

[quote=引用 7 楼 yupeigu 的回复:] 没有太看懂楼主的描述。但是我觉得是否可以这样，一个汇总表里就是30万条数据，记录的是每个会员的点击次数，然后log表记录的是今天的数据，当然考虑到数据的准确性，可以先暂时保留最近一个月的数据，而其他的数据，如果还需要就迁移到历史表中，如果不需要就直接都删除掉。每天晚上凌晨，都把昨天客户点击的次数汇总后增加到汇总表里，如果要实时查询，那么只需要把当天的数据汇总，加到汇总表里。

就是你的意思... 但是怎么加进去啊...他是按会员的ID 跟时间汇总的.... $sql_m="INSERT INTO mar_sum(sum_uid,sum_fen,sum_type,sum_time) SELECT nl_uid,SUM(nl_fen) as q,nl_ew,DATE_FORMAT(nl_time,'%Y-%m-%d') AS OrderDay FROM mar_user_log where nl_uid=".$Cookies0." and nl_ew=3 and date_format(nl_time,'%Y-%m-%d')<curdate() GROUP by OrderDay "; 下面这个nl_uid有30万.. 不循环怎么实现汇总....循环的话 limit最多只能达到5000 超过就卡死了 where nl_uid=".$Cookies0." 备注mar_user表（会员信息）$Cookies0为会员ID （数据30万）备注mar_sum表（统计会员每天的点击数）备注mar_user_log表（会员点击一次,写入数据库一次,数据700W） [/quote] 我觉得是否可以这样，一开始就有30万用户，然后汇总之前的数据到一个临时表，然后通过update语句直接更新汇总表的记录，比如： update 汇总表 a, 临时表 b set a.xx= a.xx+b.xx where a.userid=b.userid

amu1433 2015-09-15

打赏
举报

引用 7 楼 yupeigu 的回复:

没有太看懂楼主的描述。但是我觉得是否可以这样，一个汇总表里就是30万条数据，记录的是每个会员的点击次数，然后log表记录的是今天的数据，当然考虑到数据的准确性，可以先暂时保留最近一个月的数据，而其他的数据，如果还需要就迁移到历史表中，如果不需要就直接都删除掉。每天晚上凌晨，都把昨天客户点击的次数汇总后增加到汇总表里，如果要实时查询，那么只需要把当天的数据汇总，加到汇总表里。

zhu19774279 2015-09-14

打赏
举报

能否引入内存数据库，把部分压力转移到内存里。在主从集群的情况下，除非机房整体断电，否则主从同时崩溃的情况很少。

amu1433 2015-09-14

打赏
举报

引用 4 楼 u011575570 的回复:

[quote=引用 2 楼 amu1433 的回复:] [quote=引用 1 楼 wmxcn2000 的回复:] 做个操作系统上的 job ，每天晚上执行一次数据清理的操作。

你说的是我的第二种方案循环30万数据然后查询700W数据 insert 后再从700万数据中删除... 基本耗时太大...PHP 直接超时..或者无法响应..执行不了有没有其他的方法....[/quote] 他的意思是用window下的定时任务。你也可以使用mysql的event。循环30万数据然后查询700W数据 insert 后再从700万数据中删除.是这样的，首先用游标取出30W,然后查询700W数据 insert 后再从700万数据中删除，如果你在这里有索引，那就是30/700，操作还是挺快的，每晚定时去更新。不知道你的mar_sum表的设计，可以的话是可以不用游标的。[/quote] 能否给出具体代码....


$ip_sql="select id from mar_user  ";
$res9=_query($ip_sql);
$i=0;
while (!!$rs1=_fetch_array($res9)){
$Cookies0      = $rs1['id'];

$sql_m="INSERT INTO mar_sum(sum_uid,sum_fen,sum_type,sum_time) SELECT nl_uid,SUM(nl_fen) as q,nl_ew,DATE_FORMAT(nl_time,'%Y-%m-%d') AS OrderDay FROM mar_user_log  where   nl_uid=".$Cookies0."  and nl_ew=3  and date_format(nl_time,'%Y-%m-%d')<curdate() GROUP by OrderDay ";
_query($sql_m);

$sql_n="Delete FROM mar_user_log WHERE nl_uid=".$Cookies0." and nl_ew=3  and  date_format(nl_time,'%Y-%m-%d')<curdate() ";
_query($sql_n);

}

能否给出具体代码.... 这样删除的话...需要添加limit 而且我测试了下...最多只能循环5000,再多就超时了...

LongRui888 2015-09-14

打赏
举报

没有太看懂楼主的描述。但是我觉得是否可以这样，一个汇总表里就是30万条数据，记录的是每个会员的点击次数，然后log表记录的是今天的数据，当然考虑到数据的准确性，可以先暂时保留最近一个月的数据，而其他的数据，如果还需要就迁移到历史表中，如果不需要就直接都删除掉。每天晚上凌晨，都把昨天客户点击的次数汇总后增加到汇总表里，如果要实时查询，那么只需要把当天的数据汇总，加到汇总表里。