关于php 的pdo mysqli mysql查询速度比较

虽然PHP官方一直在推PDO，因为接口通用，并建议即使不用PDO，也要用MYSQLI更优化的函数。因为，至少在安全上，可以防SQL注入。

然而，在php5.6上作的测试查询效率上，却是最早mysql函数最快。PDO，不仅慢而且内存开销大。

测试对象是一个20多万条以上数据表，容易在100多M，使用PDO查询时。

报错：

Allowed memory size of 134217728 bytes exhausted

必须把php.ini的memory_limit开到1024M才能查询。且查询也没有用到fetchAll只是query。而mysql 和mysqli可以128M内在限制下运行。

循环十次测试结果如下：

start pdo...
...2.3373029232025
...2.3417119979858
...2.2374789714813
...3.187735080719
...2.2158219814301
...2.1973550319672
...2.1981711387634
...2.1964230537415
...3.1916470527649
...2.2108449935913
...2.2034590244293
average 2.6517951250076

start mysqli....
209840...1.6516010761261
209840...0.62066102027893
209840...1.6278269290924
209840...1.6215031147003
209840...0.63836812973022
209840...1.6128249168396
209840...0.64786219596863
209840...1.6257820129395
209840...1.6182668209076
209840...0.64711594581604
209840...1.6313529014587
average 1.3943165063858

start mysql...
209840...1.6287319660187
209840...0.60986304283142
209840...1.6302440166473
209840...0.60696887969971
209840...1.6387059688568
209840...1.6185088157654
209840...0.65926814079285
209840...1.6151170730591
209840...1.6063089370728
209840...0.61127114295959
average 1.2224987983704

可以看出mysqli 和mysql差不多速度，pdo最慢。

代码如下，十分简单的测试。

<?php
  $total=0;
  echo "start pdo...<BR>";
 
  for ($i=0;$i<=10;$i++) {
    $t=time();
    $m=microtime(true);
    $dsn="mysql:host=localhost;dbname=westmis";
    $PDO_dblink=new PDO ($dsn,'web','admin');
    $PDO_dblink->query("SET NAMES 'GBK'");
    $PDO_dblink->query("select * from 10000_base");
    echo "...";
    $a=time()-$t+microtime(true)-$m;
    echo $a;
    $total=$total+$a;
    echo "<BR>";
    flush();
    }
  $total=$total/10;
  echo "average $total <BR>";
 
  $total=0;
  echo "<HR> start mysqli....<BR>";
  for ($i=0;$i<=10;$i++) {
    $t=time();
    $m=microtime(true);
    $link=mysqli_connect('localhost','web','admin');
    mysqli_select_db($link,"westmis");
    mysqli_set_charset($link,"GBK");
    $result=mysqli_query($link,"select * from 10000_base");
    echo mysqli_num_rows($result);
 
     echo "...";
    $a=time()-$t+microtime(true)-$m;
    echo $a;
    $total=$total+$a;
    echo "<BR>";
  }
  $total=$total/10;
  echo "average $total <BR>";
 
  echo "<HR>";
  echo "start mysql...<BR>";
  $total=0;
  for ($i=1;$i<=10;$i++) {
    $t=time();
    $m=microtime(true);
    $link=@mysql_connect('localhost','web','admin');
    mysql_select_db("westmis");
    mysql_query("SET NAMES 'GBK'");
    $result=mysql_query("select * from 10000_base");
    echo mysql_num_rows($result);
 
    echo "...";
    $a=time()-$t+microtime(true)-$m;
    echo $a;
    $total=$total+$a;
    echo "<BR>";
    flush();
  }
 
  $total=$total/10;
  echo "average $total <BR>";