工作笔记-优快云博客

本文链接：https://blog.youkuaiyun.com/ShadowSniper/article/details/1889284

本文介绍了一个复杂的MySQL存储过程，用于统计特定时间段内高点击量文章的独立IP访问量，并探讨了其执行效率的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

用户表:user

+-----------+------------------+------+-----+---------------------+----------------+

¦ Field ¦ Type ¦ Null ¦ Key ¦ Default ¦ Extra ¦

+-----------+------------------+------+-----+---------------------+----------------+

¦ ID ¦ int(10) unsigned ¦ NO ¦ PRI ¦ NULL ¦ auto_increment ¦

¦ userid ¦ varchar(30) ¦ NO ¦ ¦ ¦ ¦

+-----------+------------------+------+-----+---------------------+----------------+

ID是用户id,userid是用户名

文章表:archives

+-------------+------------------+------+-----+---------+----------------+

¦ Field ¦ Type ¦ Null ¦ Key ¦ Default ¦ Extra ¦

+-------------+------------------+------+-----+---------+----------------+

¦ ID ¦ int(11) unsigned ¦ NO ¦ PRI ¦ NULL ¦ auto_increment ¦

¦ click ¦ int(11) unsigned ¦ NO ¦ ¦ 0 ¦ ¦

¦ title ¦ varchar(80) ¦ NO ¦ MUL ¦ ¦ ¦

¦ pubdate ¦ int(11) ¦ NO ¦ ¦ 0 ¦ ¦

¦ adminID ¦ int(11) ¦ NO ¦ ¦ 0 ¦ ¦

¦ maxpage ¦ int(4) ¦ YES ¦ ¦ 1 ¦ ¦

+-------------+------------------+------+-----+---------+----------------+

ID是文章的id,自增,click是总点击量,title是文章标题,pubdate是发表日期,adminID是发表此文章的用户id。maxpage是文章的分页数量，对应user表的ID字段

访问记录表:access

+-----------+--------------------+------+-----+---------+----------------+

¦ Field ¦ Type ¦ Null ¦ Key ¦ Default ¦ Extra ¦

+-----------+--------------------+------+-----+---------+----------------+

¦ id ¦ int(4) unsigned ¦ NO ¦ PRI ¦ NULL ¦ auto_increment ¦

¦ aid ¦ int(4) unsigned ¦ NO ¦ ¦ ¦ ¦

¦ ipaddress ¦ varchar(15) ¦ YES ¦ MUL ¦ NULL ¦ ¦

¦ time ¦ bigint(4) unsigned ¦ NO ¦ ¦ ¦ ¦

+-----------+--------------------+------+-----+---------+----------------+

id是此表的自动编号,自增,aid是文章id,对应文章表的ID,ipaddress是此次访问的ip地址，time为访问时间，类型是unix时间戳。

这三张表的关系为:user表的ID对应archives表的adminId，archives的ID对应access表的aid。

我现在写了个存储过程，用来计算从:
2007年9月24日到2007年11月15日(共52天)
所有用户(大概20个)
所发的所有点击量> =100的文章(共3831篇)
每篇文章每一天所带来的独立ip数量
把上述结果集插入到一张新表里面。

新表结构如下:

editor_record:

+----------+--------------------+------+-----+---------+-------+

¦ Field ¦ Type ¦ Null ¦ Key ¦ Default ¦ Extra ¦

+----------+--------------------+------+-----+---------+-------+

¦ uid ¦ int(10) ¦ NO ¦ ¦ ¦ ¦

¦ username ¦ varchar(30) ¦ NO ¦ ¦ ¦ ¦

¦ aid ¦ int(10) unsigned ¦ NO ¦ ¦ ¦ ¦

¦ title ¦ varchar(80) ¦ NO ¦ ¦ ¦ ¦

¦ pubdate ¦ int(11) ¦ NO ¦ ¦ 0 ¦ ¦

¦ maxpage ¦ int(4) ¦ YES ¦ ¦ NULL ¦ ¦

¦ click ¦ int(11) unsigned ¦ NO ¦ ¦ 0 ¦ ¦

¦ union_ip ¦ int(11) unsigned ¦ NO ¦ ¦ 0 ¦ ¦

¦ time ¦ bigint(4) unsigned ¦ NO ¦ ¦ ¦ ¦

+----------+--------------------+------+-----+---------+-------+

uid为发文章的用户id，username为发文章的用户名称，aid是文章id，title是文章标题，pubdate是文章发表时间，maxpage是文章的分页数量，click为文章的总点击量，union_ip是独立ip数量，time是日期。

这么做的目的是为了我前台一个php页面能够单表很迅速的查出我想要的数据，这个表有几个字段是冗余的，但我为了php可以快速进行单表查询，所以冗余就冗余吧。

计算一篇文章独立ip的方法为:

select access.aid,access.time,count(distinct(FROM_UNIXTIME(time,'%Y-%m-%d'))) as cnt from `access` where aid =文章id

group by c.aid

独立ip的算法是:如果一个ip地址在同一天内出现两次，只记一次，否则记它出现的次数。这个sql执行后，再sum一下cnt就是独立ip的数量。这张access的表有大概一百万条记录。

整个程序执行过程我们写了个存储过程，代码如下:

DELIMITER $$

DROP PROCEDURE IF EXISTS `mztest`.`proc_test1`$$

CREATE PROCEDURE `mztest`.`proc_test1` ()

BEGIN

DECLARE a int(11);

DECLARE b INT;

DECLARE days int(11);

DECLARE date int(11);

DECLARE cur_1 CURSOR FOR Select aid from tmp_table;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;

CREATE TEMPORARY TABLE tmp_table (

uid int(10) NOT NULL,

username varchar(30) NOT NULL,

aid int(10) unsigned NOT NULL,

title varchar(80) NOT NULL,

pubdate int(11) NOT NULL default 0,

maxpage int(4),

click int(11) unsigned NOT NULL default 0

)TYPE=HEAP DEFAULT CHARSET=gbk;

SET days=ABS(DATEDIFF(FROM_UNIXTIME("1190563200","%Y-%m-%d %H:%i:%s"),FROM_UNIXTIME("1195142399","%Y-%m-%d %H:%i:%s")));

SET date=1190563200;

insert into tmp_table Select b1.ID as uid,b1.userid,a1.ID as aid,a1.title,a1.pubdate,a1.maxpage,a1.click From archives a1,

user b1 Where a1.adminid=b1.ID And a1.click >= 100 ;

WHILE days > 0 DO

SET b=2;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

IF b=2 THEN

insert into editor_record(uid,username,aid,title,pubdate,maxpage,click,union_ip,time) select t.uid,t.username,c.aid,t.title,t.pubdate,t.maxpage,t.click,sum(cnt) as union_ip,date from (select access.aid,access.time,count(distinct(FROM_UNIXTIME(time,'%Y-%m-%d'))) as cnt from `access` where aid =a And time >= date And time <= (date+86399) group by ipaddress) c,tmp_table t where c.aid=t.aid and t.uid =1 group by c.aid;

END IF;

UNTIL b = 1

END REPEAT;

CLOSE cur_1;

SET date=date+86400;

SET days = days-1;

END WHILE;

END;$$

DELIMITER ;

之前,我是用PHP直接计算这些结果的,选出两个日期,和一个编辑的名称,计算这段时间段内的薪水,速度及其的慢,
而且这个表在我们网站的服务器上,查的次数多了,对网站的速度影响都是很大,领导斥令我把这个统计程序暂时不对兼职编辑开放.让我尽快采取优化方案.

于是,我想到了使用linux下的计划任务,定时在每天12点自动执行一段脚本,计算出当天的独立ip数量和编辑的薪水.第一次执行的时候我想先计算出9月24日那天到今天这段时间的数据,以后每一天计算当天的数据就可以了.把最终的结果插入到一个新表里面,我的php在前台单表查询就可以了,这样肯定会很快.其实流程就是:
把复杂的数据计算过程完全交给mysql来做,php用来把结果显示出来.

可是...虽然运行结果是正确的。但是执行速度非常之慢，预计要执行几天吧.也太长久了...
算了一下，大概要执行1000000*3831*52次！！！

一开始打算用perl写的,最近在学这个语言,和php比较像,用起来应该会比较顺手,想正好可以拿来练练.但是我在linux配了一天的DBI(DBD是perl连接mysql接口,DBI是mysql的驱动),始终就是配置不成功.身边也没有有相关经验的人.后来想到用linux的shell来写,但shell语言对复杂数据的处理能力有限,而且在我感觉语法有些怪异.于是就用mysql的存储过程来写,

目前一直在琢磨优化代码的方案,我的压力很大啊,一堆兼职编辑在等着我这个程序给人家结算薪水呢,数据有错或者不能尽快给人家结果的话,我良心过不去啊,唉