首页 > 代码库 > mysql性能测试php版本

mysql性能测试php版本

所有数据库结构都为如下,有些id是int,看注释

100W数据:

id为索引字段,4字节的int,数据库引擎为myisam

echo "<br>".xdebug_time_index()."<br>";  //0.011001110076904for ($i=0; $i < 10000; $i++) {     $random = mt_rand(1,999999);    $id = $random;    $sql = "select * from test100m where id=".$id."";    $result = mysql_query($sql,$con);}echo "<br>".xdebug_time_index()."<br>";  //1.0551059246063

10000次查询,1s左右,每条select 0.1ms

 

100W数据:

id为索引字段,4字节的int,数据库引擎为innodb

echo "<br>".xdebug_time_index()."<br>";  //0.011001110076904for ($i=0; $i < 10000; $i++) {     $random = mt_rand(1,999999);    $id = $random;    $sql = "select * from test100m_innodb where id=".$id."";    $result = mysql_query($sql,$con);}echo "<br>".xdebug_time_index()."<br>";  //1.1981191635132

10000次查询,1s左右,每条select 0.1ms

 

200W数据:

id为索引字段,4字节的int,数据库引擎为innodb

echo "<br>".xdebug_time_index()."<br>";  //0.00099992752075195for ($i=0; $i < 1000; $i++) {     $random = mt_rand(1,1999999);    $id = $random;    $sql = "select * from test200m where id=".$id."";    $result = mysql_query($sql,$con);    }echo "<br>".xdebug_time_index()."<br>";  //4.5234520435333

1000次查询,4.5s左右,每条select 4.5ms

 

 

500W数据:

id为索引字段,4字节的int,数据库引擎为innodb

echo "<br>".xdebug_time_index()."<br>";  //0.0010001659393311for ($i=0; $i < 1000; $i++) {     $random = mt_rand(1,4999999);    $id = $random;    $sql = "select * from test500m where id=".$id."";    $result = mysql_query($sql,$con);    }echo "<br>".xdebug_time_index()."<br>";  //5.0744871616364

1000次查询,5s左右,每条select 5ms

 

1000W数据:

id为索引字段,4字节的int,数据库引擎为myisam

echo "<br>".xdebug_time_index()."<br>";  //0.0010008811950684for ($i=0; $i < 100; $i++) {     $random = mt_rand(1,9999999);    $id = $random;    $sql = "select * from test1000m where id=".$id."";    $result = mysql_query($sql,$con);    }echo "<br>".xdebug_time_index()."<br>";  //1.5391540527344

100次查询,1.5s左右,每条select 15ms

 

1000W数据:

id为索引字段,8字节的bigint,数据库引擎为myisam

echo "<br>".xdebug_time_index()."<br>";  //0.00099992752075195for ($i=0; $i < 100; $i++) {     $random = mt_rand(1,9999999);    $id = $random.‘0‘.$random.‘0‘;    $sql = "select * from test1000m_bigint where id=".$id."";    $result = mysql_query($sql,$con);}echo "<br>".xdebug_time_index()."<br>";  //1.2141208648682

100次,时间为1.2s ,  平均每条select 12ms

1000W数据:

id为索引字段,8字节的bigint,数据库引擎为myisam

代码如上

100次,时间为1.2s  ,  平均每条select 12ms

 

 

结论:

数据量    引擎    平均时间  索引字段

100W    myisam  0.1ms

100W    innodb   0.1ms

200W    innodb   4.5ms

500W      innodb   5ms

1000W      myisam     15ms  (麻痹的居然比bigint慢)

1000W    innodb   12ms  (bigint)

1000W    myisam  12ms  (bigint)

 

mysql性能测试php版本