利用临时表，加快存储过程的执行速度。
背景：
一个博客网站访问流量表，每天有大约１００万条数据插入，记录每个博客及网站的访问情况。表结构：
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 | |
+—————-+————–+——+—–+———+—————-+
每隔２小时就会利用存储过程对这个表进行统计，包括统计当天的ＩＰ及ＰＶ。
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
使用了一个临时表保存统计结果，执行时间超过１５秒。
下面是使用了两个临时表的代码：
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表的索引。