关于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>";