mysql資料庫3600萬測試資料產生方法及最佳化測試

標籤：

為公司項目最佳化調整，需要大容量資料表做測試，測試過程發現了很多有趣的東西，這裡一併發出來。

本次測試為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=utf8CREATE 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[email protected]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=utf8alter 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第一條件，如果被查詢欄位中有大量的未索引欄位，那麼速度會很受影響，這點務必注意。大家在測試中也可以體會下。

 

mysql資料庫3600萬測試資料產生方法及最佳化測試