mysql大数据量处理

mysql大数据量处理  

2008-07-11 10:41:58|  分类： 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




实际例子(在csdn中发布)：
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语句吧，谢谢 

以下是我慢慢实践的总结：

在这里修改了下，把查询语句中的*改成字段了，还度提升到17秒左右，再去掉order by速度为2.26秒左右
这里可以看到查询语句中的字段最好能确认那些需要查询的；

但这里的order by 很影响性能呀，大家指下啦，谢谢


还在查询总和的时候还是得不到更好的解决

暂时是通过sort_buffer_size默认是5M,可以放到32M来优化下，
另外一个思路就是先最小字段倒序查询出主键，现通过in来取到最后的结果，但这些都需要explain与实际查询的;

mysql 的myisam表会记录着表中所存在的总数count(),所以select count(*) 是很快的,但加上了where那速度就直线下降了