MySQL database 36 million test data generation method and optimization test

Optimization of the company's projects, the need for large-capacity data sheets to do the test, the testing process found a lot of interesting things, and this is sent out.

This test for the MyISAM table of large-capacity data query optimization of the test data, in the test process using the merge table, each table 18 million data, to the program, the Sub-table operation is packaged, the program operation as the same table, the test results are more satisfactory, Crossing you can use the command line of this method to run to generate test data, or you can draw on the merge sub-table to split the bulk of the data.

Test Data Sheet Preparation

CREATE TABLE' time_1 ' (' ID ')bigint( -) not NULLauto_increment, ' Confid 'int( One) not NULL, ' Timeid 'int( One) not NULL,PRIMARY KEY(' id '),KEY' confid ' (' Confid '),KEY' Timeid ' (' Timeid ')) ENGINE=MyISAMDEFAULTCHARSET=UTF8CREATE TABLE' Testtime ' (' ID ')bigint( -) not NULLauto_increment, ' Confid 'int( One) not NULL, ' Timeid 'int( One) not NULL,PRIMARY KEY(' id '),KEY' confid ' (' Confid '),KEY' Timeid ' (' Timeid ')) ENGINE=Mrg_myisamDEFAULTCHARSET=UTF8 Insert_method=LastUNION =(' Time_1 ')

Here are two tables, testtime the main table, time_1 as the first table, and now start inserting data, run the following code at the command line

<?PHP/*36 million test data generation method, each table 18 million data, run in command line mode*/$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 ";}

Insert complete, now generate second table

CREATE TABLE' time_2 ' (' ID ')bigint( -) not NULLauto_increment, ' Confid 'int( One) not NULL, ' Timeid 'int( One) not NULL,PRIMARY KEY(' id '),KEY' confid ' (' Confid '),KEY' Timeid ' (' Timeid ')) ENGINE=MyISAMDEFAULTCHARSET=UTF8Alter TableTesttime engine=Mrg_myisamUnion=(' Time_1 ', ' time_2 ') insert_method=Last

Now run the above PHP data generation file again, the next 1800 data will be sent into the second table time_2.

Therefore, this method can at any time merge new table into the Testtime table, and the program operation database, only need to face testtime can, very convenient, query results with ID or confid query Timeid, simple optimization of some MySQL parameters, Query speed of not more than 0.4 seconds, the face of 36 million data this result has been quite satisfied, in practice, our table more than 2 million of the data may need to be divided into tables.

Here you can see the merge table is convenient and powerful, but everyone has not noticed the index and data fields of each table.

In a nutshell, if the index is not an int field, if the query does not have an indexed field in the where first condition, it is important to note that if there are a large number of unindexed fields in the queried fields, the speed will be affected. You can also feel it in the test.

MySQL database 36 million test data generation method and optimization test