PHP PDO Mysql插入性能

问题描述

我通常不会在PHP中使用很多SQL东西，但是最近它被一个朋友强迫使用，以帮助他调试某些东西.

I normally don't use a lot of SQL stuff in PHP but recently have been forced into it by a friend as to help him debug something.

我将PDO与PHP一起使用以插入一些数据，但是插入性能却很糟糕.一个简单的151次插入循环需要将近6秒钟！我不知道为什么.

I am using PDO with PHP to insert some data but the insert performance is abysmal. A simple loop of 151 inserts takes nearly 6 seconds! and I am lost as to why.

这是我的代码:

<?php

$database='XXXXXX';
$username='XXXXXX';
$password='XXXXXX';
$hostname='127.0.0.1';

$inserted=0;
$counted=0;
$dsn = 'mysql:dbname='.$database.';host='.$hostname.'';
$start=microtime(true);
try {
    $data = new PDO($dsn, $username, $password, array(PDO::ATTR_EMULATE_PREPARES => false));
} catch (PDOException $e) {
    echo('Connection failed: ' . $e->getMessage());
}
for($i=1;$i<=150;$i++) {
    $time=time();
    $query=$data->prepare("INSERT INTO `tbl_temp` (aid, bid) VALUES (?, ?)");
    $query->execute(array($i, $time));
}
$data=null;
print "Took: ".(microtime(true)-$start)." S to insert $i records\n";

// Took: 5.569482088089 S to insert 151 records <--- result
?>

我也使用bindParam尝试了相同的代码，并且速度大致相同.该服务器具有8核Xeon处理器和64GB RAM.该脚本是从命令行(php-cgi)运行的，数据库和表是新的并且为空.数据库类型为InnoDB.谁能指出我为什么会这么慢的正确方向?因为我敢肯定MySQL从来没有像现在这么慢！

I have tried the same code using bindParam also and the speed is roughly the same. The server has an 8 core Xeon processor and 64gb of RAM. The script is being run from the command line (php-cgi) and the database and table are new and empty. The database type is InnoDB. Can anyone point me in the right direction of where to look as to why it's so slow? Because I am sure MySQL never used to be this slow!

推荐答案

找到了这个. PDO/PHP/MySQL中的性能:事务与直接执行.所以试试这个.

found this one. Performance in PDO / PHP / MySQL: transaction versus direct execution. so try this one.

$query = $data->prepare("INSERT INTO `tbl_temp` (aid, bid) VALUES (?, ?)");
try {
        $data->beginTransaction();
        for($i=0; $i < 150; $i++) {
            $time = time();
            $query->bindValue(1, $i, PDO::PARAM_INT);
            $query->bindValue(2, $time, PDO::PARAM_STR);
            $query->execute();
        }
        $data->commit();
    } catch(PDOException $e) {
            $data->rollBack();
    }

这篇关于PHP PDO Mysql插入性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！