利用临时表,加快存储过程的执行速度。

利用临时表,加快存储过程的执行速度。


背景:
  一个博客网站访问流量表,每天有大约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表的索引。


Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.