利用临时表,加快存储过程的执行速度。
背景:
一个博客网站访问流量表,每天有大约100万条数据插入,记录每个博客及网站的访问情况。表结构:
mysql> show fields from blog_track;
+—————-+————–+——+—–+———+—————-+
| Field | Type | Null | Key | Default | Extra |
+—————-+————–+——+—–+———+—————-+
| track_id | int(11) | NO | PRI | NULL | auto_increment |
| track_ip | char(15) | YES | | NULL | |
| track_date | datetime | NO | MUL | | |
| track_page | varchar(256) | YES | | NULL | |
| track_refferer | varchar(500) | YES | | NULL | |
| member_id | int(11) | NO | MUL | 0 | |
| visiter_id | int(11) | NO | | 0 | |
| track_type | tinyint(4) | NO | MUL | 0 | |
+—————-+————–+——+—–+———+—————-+
每隔2小时就会利用存储过程对这个表进行统计,包括统计当天的IP及PV。
CREATE DEFINER=`root`@`%` PROCEDURE `prc_anaylise_track`()
BEGIN
declare time_now datetime;
DROP TABLE IF EXISTS tmp_tables;
CREATE TEMPORARY TABLE tmp_tables (
blog_id int NOT NULL,
count int NOT NULL,
ip char(15) null
) engine = HEAP;
set time_now = now();
insert into tmp_tables(count,blog_id,ip) select count(*) as count,member_id as blog_id,track_ip as ip from blog_track where track_type=0 and track_date>=date_format(now(),’%Y-%m-%d 00:00:00′) and member_id>0 group by track_ip,member_id;
update blog_info set blog_info.today_ip = (select count(*) as count from tmp_tables where blog_info.member_id=tmp_tables.blog_id);
delete from tmp_tables;
select concat(‘count today ip count need time : ‘,(unix_timestamp(now()) – unix_timestamp(time_now))) as neetTime;
END
使用了一个临时表保存统计结果,执行时间超过15秒。
下面是使用了两个临时表的代码:
CREATE DEFINER=`root`@`%` PROCEDURE `prc_anaylise_user`()
BEGIN
declare time_now datetime;
DROP TABLE IF EXISTS tmp_tables_full;
CREATE TEMPORARY TABLE tmp_tables_full (
blog_id int not null,
count int not null default 0,
ip char(15) null,
index blog_id(blog_id)
)engine = HEAP;
DROP TABLE IF EXISTS tmp_tables;
CREATE TEMPORARY TABLE tmp_tables (
blog_id int NOT NULL,
count int NOT NULL,
ip char(15) null,
index blog_id(blog_id)
) engine = HEAP;
set time_now = now();
insert into tmp_tables_full(blog_id,ip) select member_id,track_ip from blog_track where track_type=0 and track_date>=date_format(now(),’%Y-%m-%d 00:00:00′) and member_id>0;
insert into tmp_tables(count,blog_id,ip) select count(*) as count,blog_id,ip from tmp_tables_full group by blog_id,ip;
update blog_info set blog_info.today_ip = (select count(*) as count from tmp_tables where blog_info.member_id=tmp_tables.blog_id);
delete from tmp_tables_full;
delete from tmp_tables;
select concat(‘count today ip count need time : ‘,(unix_timestamp(now()) – unix_timestamp(time_now))) as neetTime;
END
第一个临时表存放今天的访问记录,第一个临时表存放统计结果。这样做的目的是避免统计时间过长,锁表时间过长,导致网站访问不正常。执行时间为2秒。
进一步优化:
定义一个临时变量,
declare p_updatetime datetime;
建立一个tmpupdatetable表,保存上一次执行的时间;
select updatetime into p_updatetime from tmpupdatetable;
把最后执行的时间回到where条件里:
insert into tmp_tables_full(blog_id,ip) select member_id,track_ip from blog_track where track_type=0 and track_date>=date_format(now(),’%Y-%m-%d 00:00:00′) and track_date>=p_updatetime and member_id>0;
更新数据时的代码:
update blog_info set blog_info.today_ip =blog_info.today_ip+ (select count(*) as count from tmp_tables where blog_info.member_id=tmp_tables.blog_id);
经过样的修改,执行时间变了120ms左右。已经符合要求了,如果没有达到这个效果的话,请检查一个blog_track表的索引。