mysql数据库3600万测试数据生成方法及优化测试 （转）

原文： http://www.cnblogs.com/phpinfo/p/4342657.html

为公司项目优化调整，需要大容量数据表做测试，测试过程发现了很多有趣的东西，这里一并发出来。

本次测试为myISAM表的大容量数据查询优化所做的测试数据，在测试过程中使用了merge分表，每张表1800万数据，对程序来说，分表操作被包装起来，程序操作如同是同一张表，测试结果较为满意，各位看官可以使用本方法的命令行运行来生成测试数据，也可以借鉴merge分表来拆分大容量数据。

 

测试数据表准备

CREATE TABLE `time_1` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`confid` int(11) NOT NULL,
`timeid` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `confid` (`confid`),
KEY `timeid` (`timeid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8


CREATE TABLE `testtime` (
`id` bigint(20) NOT NULL AUTO_INCREMENT ,
`confid` int(11) NOT NULL,
`timeid` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `confid` (`confid`),
KEY `timeid` (`timeid`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD = last UNION = ( `time_1` )

这里准备了两张表，testtime为主表，time_1为第一张分表，现在开始插入数据，在命令行下运行下列代码

<?php
/*3600万测试数据生成方法,每张表1800万数据,在命令行模式下运行*/


$con=@mysql_connect('localhost','root','root');
if(!$con){
     die("no".mysql_error());
}else{
     echo "yes!";
}
mysql_select_db("test", $con);
mysql_query("SET NAMES utf8");

$contime_start=20150101;
$confid=mt_rand(100000,999999);
$i=$v=0;
while($v<1800){
     $confid=mt_rand(100000,999999);
     while($i<100){    
          $sql="insert into testtime values('',{$confid},{$contime_start})";
          for($t=0;$t<100;$t++){
               $contime_start++;
               $sql.=",('',{$confid},{$contime_start})";
          }
          if (mysql_query($sql,$con)){
               echo "insert ok ".$v.'----'.$i."\r\n";
          }else{
               echo "Error creating database: " . mysql_error();
               exit();
          }
          $i++;
     }
    
     $i=0;
     $v++;
     echo $v."\r\n";
}

插入完成，现在生成第二张分表

CREATE TABLE `time_2` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`confid` int(11) NOT NULL,
`timeid` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `confid` (`confid`),
KEY `timeid` (`timeid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

alter table testtime engine=mrg_myisam union=(`time_1`,`time_2` ) insert_method=last;

现在再次运行上列的php数据生成文件，将会发先随后的1800数据插入了第二张表time_2.

 

所以，这种方法可以随时merge新表进testtime表中，而程序操作数据库，只需要面对testtime即可，非常方便，查询结果以id或者confid查询timeid，简单优化部分mysql参数后，查询速度不超过0.4秒，面对3600万数据这个结果已经相当满意了，实际应用中，我们的表超过200万数据可能就需要分表了。

这里大家可以看到merge分表的方便和强大，但是大家有没有注意到每张分表里的索引和数据字段。

简单的说一下结论，如果不是int字段索引，如果查询不以已索引字段为where第一条件，如果被查询字段中有大量的未索引字段，那么速度会很受影响，这点务必注意。大家在测试中也可以体会下。