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

MySql 码拜 4年前 (2016-02-21) 411次浏览
备注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有定时任务?
备注:储存过程,了解甚少,请写全代码,要不看不懂…在线等..希望今晚解决了..本人晚上后半夜就可以开始..白天在线人数多.还不敢怎么太动数据..影响用户…

解决方案

10

你的第二种方案是遍历每一个用户,这个效率上欠佳,改造你的SQL,统一删除 。

20

引用 2 楼 amu1433 的回复:
Quote: 引用 1 楼 wmxcn2000 的回复:

做个操作系统上的  job ,每天晚上执行一次数据清理的操作。

你说的是本人的第二种方案
循环30万数据  然后查询700W数据  insert 后再从700万数据中删除…
基本耗时太大…PHP 直接超时..或无法响应..执行不了
有没有其他的方法….

他的意思是用window下的定时任务。你也可以使用mysql的event。
循环30万数据  然后查询700W数据  insert 后再从700万数据中删除.是这样的,首先用游标取出30W,然后查询700W数据  insert 后再从700万数据中删除,假如你在这里有索引,那就是30/700,操作还是挺快的,每晚定时去更新。
不知道你的mar_sum表的设计,可以的话是可以不用游标的。

20

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

20

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

30

引用 8 楼 amu1433 的回复:
Quote: 引用 7 楼 yupeigu 的回复:

没有太看懂LZ的描述。
但是本人觉得 能否可以这样,一个汇总表里就是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)

本人觉得能否可以这样,一开始就有30万用户,然后汇总之前的数据到一个临时表,然后通过update语句直接更新 汇总表的记录,例如:
update 汇总表 a, 临时表 b set a.xx= a.xx+b.xx where a.userid=b.userid


CodeBye 版权所有丨如未注明 , 均为原创丨本网站采用BY-NC-SA协议进行授权 , 转载请注明MYSQL优化,或其他的解决方案
喜欢 (0)
[1034331897@qq.com]
分享 (0)