求一些优化方案，分不够可以另开贴再加！

Question

http://blog.chinaunix.net/u/29134/showart_400939.html

Answer 1

我觉得可以把一堆操作拆开来做，这样数据库本身的优化器更能发挥作用。这就是我的想法。以下是代码，没有完全测试，如果有语法错误及时提出来：DELIMITER $$DROP PROCEDURE IF EXISTS `mztest`.`proc_test1`$$
CREATE PROCEDURE `mztest`.`proc_test1` ()-- 创建文章aid 与 总的点击次数click的映射表
create table archive_click
(
aid int PRIMARY KEY,
click int
);
insert into archive_click(aid,click)
select aid, count(*)
from access
group by aid
order by aid;-- 创建具有少量必要信息的临时表
-- tmp1_table大小约为1,000,000
-- 因为archive_click表中aid为主码，所以速度不会很慢
create table tmp1_table
select aid, FROM_UNIXTIME(time,"%Y-%m-%d") date, ipaddress
from access as A, archive_click as B
where A.aid = B.aid and B.click >= 100;-- 分组统计,得到union_ip
-- tmp2_table大小约为3831*52 = 200,000
-- 主要是一个排序过程，时间复杂度为O(nlogn) = 4,000,000
create table tmp2_table
select aid, date, count(distinct(ipadress)) union_ip
from tmp1_table
group by aid,date;-- 将所有信息融合到一个表中
-- 由于都是主码上的连接，所以速度也不会很慢
-- 如果慢的话，这里改起来也容易
create table editor_record
select U.ID uid, U.userid username, T2.aid aid, A.title title, A.pubdate pubdate, A.maxpage maxpage, T1.click click, T2.union_ip union_ip, T2.date time
from tmp2_table T2, tmp1_table T1, archives A, user U
where T2.aid = T1.aid and T2.aid = A.id and A.adminID = U.ID;drop table archive_click;drop table tmp1_table;drop table tmp2_table;END   $$DELIMITER ;

Answer 2

to qzy6:错误提示:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create table archive_click
(
aid int PRIMARY KEY,
click int
);
insert into archi' at line 2

Answer 3

我上面发的代码确实有些错误，昨晚有点晕，不好意思。
下面的代码是一系列sql查询，不需要建立procedure。
我觉得在效率上应当比较快，我想整个执行过程不会超过一分钟。 因为楼主是自己写循环，要执行很多次查询，代价是很大的。
如果没有错误，希望楼主把执行时间告诉我。以下代码在mysql 5.0上执行通过：
-- 选择数据库
use mztest;
-- 以下是创建楼主需要的表，用于测试
create table user
(
 ID int(10) PRIMARY KEY auto_increment   ,
 userid varchar(30)
);
create table archives
(
ID  int(11)   unsigned   PRIMARY KEY auto_increment ,
click  int(11)   unsigned ,
title   varchar(80) ,
pubdate int(11),
adminID int(11) ,
maxpage int(4)
);
create table access
 (
 id int(4) PRIMARY KEY auto_increment  ,
aid int(4),
ipaddress varchar(15),
time bigint(4)
);
-- 创建文章aid与总的点击次数click的映射表
create table archive_click
(
aid int(11) PRIMARY KEY,
click int(11)
);
create table tmp1_table
select A.aid, FROM_UNIXTIME(time,"%Y-%m-%d") date, ipaddress
from access as A, archive_click as B
where A.aid = B.aid and B.click >= 100;
-- 创建具有少量必要信息的临时表
-- 得到tmp1_table大小约为1,000,000
-- 执行过程中因为archive_click表中aid为主码，所以速度不会很慢
create table tmp2_table
select aid, date, count(*) click, count(distinct(ipaddress)) union_ip
from tmp1_table
group by aid,date;
-- 分组统计,得到单日的click和union_ip
-- 得到tmp2_table大小约为3831*52 = 200,000
-- 执行主要是对tmp1_table表中元组一个排序过程，时间复杂度为O(nlogn)=20,000,000 
create table editor_record
select U.ID uid, U.userid username, T.aid aid, A.title title, A.pubdate pubdate, A.maxpage maxpage, T.click click, T.union_ip union_ip, T.date time
from tmp2_table T, archives A, user U
where T.aid = A.id and A.adminID = U.ID;
-- 删除临时表
drop table tmp1_table;
drop table tmp2_table;
drop table archive_click;

Answer 4

晕，上面写错位了，再贴：-- 选择数据库
use mztest;
-- 以下是创建楼主需要的表，用于测试
create table user
(
 ID int(10) PRIMARY KEY auto_increment   ,
 userid varchar(30)
);
create table archives
(
ID  int(11)   unsigned   PRIMARY KEY auto_increment ,
click  int(11)   unsigned ,
title   varchar(80) ,
pubdate int(11),
adminID int(11) ,
maxpage int(4)
);
create table access
 (
 id int(4) PRIMARY KEY auto_increment  ,
aid int(4),
ipaddress varchar(15),
time bigint(4)
);
-- 创建文章aid与总的点击次数click的映射表
create table archive_click
(
aid int(11) PRIMARY KEY,
click int(11)
);
insert into archive_click(aid,click)
select aid, count(*)
from access
group by aid
order by aid;
-- 创建具有少量必要信息的临时表
-- 得到tmp1_table大小约为1,000,000
-- 执行过程中因为archive_click表中aid为主码，所以速度不会很慢
create table tmp1_table
select A.aid, FROM_UNIXTIME(time,"%Y-%m-%d") date, ipaddress
from access as A, archive_click as B
where A.aid = B.aid and B.click >= 100;
-- 分组统计,得到单日的click和union_ip
-- 得到tmp2_table大小约为3831*52 = 200,000
-- 执行主要是对tmp1_table表中元组一个排序过程，时间复杂度为O(nlogn)=20,000,000 
create table tmp2_table
select aid, date, count(*) click, count(distinct(ipaddress)) union_ip
from tmp1_table
group by aid,date;
-- 将所有信息融合到一个表中
-- 由于都是主码上的连接，所以速度也不会很慢
create table editor_record
select U.ID uid, U.userid username, T.aid aid, A.title title, A.pubdate pubdate, A.maxpage maxpage, T.click click, T.union_ip union_ip, T.date time
from tmp2_table T, archives A, user U
where T.aid = A.id and A.adminID = U.ID;
-- 删除临时表
drop table tmp1_table;
drop table tmp2_table;
drop table archive_click;

Answer 5

谢谢qzy6的热情帮忙，也谢谢DF的GF帮我解决了问题