mysql大数据量处理，求助

kill8108 2008-07-10 08:52:06

mysql大数据量处理，求助

从3张表中取数据，求一个更好的sql语句
表1:
CREATE TABLE `CorporationServer` (
`corporationServerID` int(11) NOT NULL AUTO_INCREMENT,
`corporationKey` int(11) NOT NULL,
`shelfCode` varchar(24) DEFAULT NULL,
`floorCode` varchar(24) DEFAULT NULL,
`ip` int(10) unsigned NOT NULL,
`isWhite` tinyint(1) DEFAULT '0',
PRIMARY KEY (`corporationServerID`),
UNIQUE KEY `AK_Key_2` (`ip`),
KEY `R_31` (`corporationKey`)
) ENGINE=MyISAM AUTO_INCREMENT=65537 DEFAULT CHARSET=utf8;

表2:
CREATE TABLE `IpCapture` (
`ipCaptureID` int(11) NOT NULL AUTO_INCREMENT,
`ip` int(10) unsigned NOT NULL,
`serviceType` varchar(128) DEFAULT NULL,
`proxyType` smallint(6) DEFAULT NULL ,
`sectScope` tinyint(1) DEFAULT '0',
PRIMARY KEY (`ipCaptureID`),
UNIQUE KEY `AK_Key_2` (`ip`)
) ENGINE=MyISAM AUTO_INCREMENT=65618 DEFAULT CHARSET=utf8 ;

表(分区)3:#这里是一天个分区，每天的数据最小量限为300W算

create table HttpGetLogPart2
(
httpGetLogID bigint(12) not null auto_increment,
dstIp int unsigned not null,
dstPort NUMERIC(5) not null,
srcIp int unsigned not null,
srcPort SMALLINT not null,
domainName VARCHAR(64) not null,
visitTime TIMESTAMP not null default CURRENT_TIMESTAMP,
urlHashcode bigint,
getUrl text,
vlanID INTEGER default -1,
probeIp VARCHAR(16) not null,
inputDate date default null,
primary key (httpGetLogID, inputDate)
) engine=myisam
PARTITION BY list (to_days(inputDate)) (
PARTITION p0 VALUES in (to_days('2008-07-05')),
PARTITION p1 VALUES in (to_days('2008-07-06')),
PARTITION p2 VALUES in (to_days('2008-07-07')),
PARTITION p3 VALUES in (to_days('2008-07-08')),
PARTITION p4 VALUES in (to_days('2008-07-09')));

create index Index_dstip on HttpGetLogPart2
(
dstIp
);

表HttpGetLogPart2: 16711680条记录;
表IpCapture:65617条记录;
表CorporationServer:65536条记录

查询语句：
select * from HttpGetLogPart2 log,IpCapture ipcapture ,CorporationServer
corporationServer
where(
log.inputDate> date '2008-07-06' and log.inputDate <date '2008-07-09'
and ipcapture.ip = log.dstIp
and corporationServer.ip = log.dstIp
) order by httpGetLogID desc limit 600000,15;

结果：15 rows in set (25.20 sec)

查询总和：
select count(*) from HttpGetLogPart2 log,IpCapture ipcapture ,CorporationServer
corporationServer
where(
log.inputDate> date '2008-07-06' and log.inputDate <date '2008-07-09'
and log.dstIp = ipcapture.ip
and log.dstIp = corporationServer.ip
);
+----------+
| count(*) |
+----------+
| 6684672 |
+----------+
1 row in set (25.58 sec)

查询的结果不怎么理想，计算下总和与查下数据，合起来的时间差不多一分钟了;

大家帮忙设计个sql语句吧，谢谢

...全文