工作笔记

用户表: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的存储过程来写,

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