首页 > 代码库 > 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版本