mysql大数据量处理
以下是个人的总结，有不对的地方大家指点：
设计上：
冗余：有些能冗余的就冗余吧，尽量少关联表；
垂直分区，一条记录中有text,varchar()这些能拆出来就拆出来，能用小的类型就用小的类型，如：char替换varchar之类的，能使用smallint,int就不要使用long等更大的数字型;
水平分区：range,list,hash,key,composite分区，这里分区说一下range是less than 的，查分区时会查前后的分区，list的in是range的相象，不过只查一个分区,composite是一个组合分区了，对于更超大的合适，先用range/list然后再hash/key分区;
目前mysql分区只支持内置的日志函数有：year,month,to_day
物理上：
分区与索引存放在不同的物理分区上；
隔一定时间使数据迁移走做备份
查询语句：
查询中不要使用*做查询返回字段，应写字段；尽量不要使用嵌套语句；
尽量使用更好的关系语句；
尽量少关联表；
查询大数据表的时候尽量不要返回text,varchar类型字段，并且这些字段最好就不要放在同一张表中，同过ID再查详情之类形式返回
操作上：
尽量让操作与查询分离，特别是在大并发操作的时候；
配置上：
对mysql的配置参数优化，
如key_buffer这些缓存可以设大点的：默认是8M吧，设到400M也不过份的(索引缓存)；
sort_buffer_size默认是5M,可以放到32M
从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语句吧，谢谢
以下是我慢慢实践的总结：
在这里修改了下，把查询语句中的*改成字段了，还度提升到17秒左右，再去掉order by速度为2.26秒左右
这里可以看到查询语句中的字段最好能确认那些需要查询的；
但这里的order by 很影响性能呀，大家指下啦，谢谢
还在查询总和的时候还是得不到更好的解决
暂时是通过sort_buffer_size默认是5M,可以放到32M来优化下，
另外一个思路就是先最小字段倒序查询出主键，现通过in来取到最后的结果，但这些都需要explain与实际查询的;
mysql 的myisam表会记录着表中所存在的总数count(),所以select count(*) 是很快的,但加上了where那速度就直线下降了
