PHP大批量插入的3种方法速度对比

环境:

• 64位 WIN7
• 内存4G
• PHP 5.6.20 (cli)

代码

以下代码作为简单测试

<?php
/**
 * CREATE TABLE `demo` (
 * `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 * `data` varchar(255) NOT NULL,
 * PRIMARY KEY (`id`)
 * ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 */

/*
 * 连接数据库
 */
$dsn      = 'mysql:host=127.0.0.1;dbname=testdb;';
$user     = 'root';
$password = '123456';
try {
    $dbh = new PDO( $dsn , $user , $password );
} catch ( \Exception $e ) {
    throw new \Exception( $e->getMessage () );
}

/*
 * 调整 Mysql Server接受的数据包
 */
$dbh->exec ( "set global max_allowed_packet = 2*1024*1024*1024" );

/*
 * 测试记录总数
 */
$rowsCount = 10000;

/*
 * 1 普通方式,逐行写入测试数据
 */
$time_start = microtime ( true );
try {
    for ( $i = 1 ; $i <= $rowsCount ; $i ++ ) {
        $sql = "insert into demo( data ) value ('" . mt_rand ( 10000000 , 99999999 ) . "')";
        $dbh->exec ( $sql );
    }
} catch ( \Exception $e ) {
    throw new \Exception( $e->getMessage () );
}
$time_end = microtime ( true );
$time     = $time_end - $time_start;
echo "1 Execution time: {$time} s" . PHP_EOL;

/*
 * 2 事务
 */
$time_start = microtime ( true );
$dbh->beginTransaction ();
try {
    for ( $i = 1 ; $i <= $rowsCount ; $i ++ ) {
        $sql = "insert into demo(data) value ('" . mt_rand ( 10000000 , 99999999 ) . "')";
        $dbh->exec ( $sql );
    }
    $dbh->commit ();
} catch ( \Exception $e ) {
    $dbh->rollBack ();
    throw new \Exception( $e->getMessage () );
}

$time_end = microtime ( true );
$time     = $time_end - $time_start;
echo "2 Execution time: {$time} s" . PHP_EOL;

/*
 * 3 值合并方式,values (...),(...)
 */
$time_start = microtime ( true );
try {
    $sql = "insert into demo( data ) values ";
    for ( $i = 1 ; $i <= $rowsCount ; $i ++ ) {
        $sql .= "('" . mt_rand ( 10000000 , 99999999 ) . "'),";
    }
    $dbh->exec ( rtrim ( $sql , ',' ) );
} catch ( \Exception $e ) {
    throw new \Exception( $e->getMessage () );
}
$time_end = microtime ( true );
$time     = $time_end - $time_start;
echo "3 Execution time: {$time} s " . PHP_EOL;

/*
 * 4 合并加事务
 */
$time_start = microtime ( true );
$dbh->beginTransaction ();
try {
    $sql = "insert into demo( data ) values ";
    for ( $i = 1 ; $i <= $rowsCount ; $i ++ ) {
        $sql .= "('" . mt_rand ( 10000000 , 99999999 ) . "'),";
    }
    $dbh->exec ( rtrim ( $sql , ',' ) );
    $dbh->commit ();
} catch ( \Exception $e ) {
    $dbh->rollBack ();
    throw new \Exception( $e->getMessage () );
}
$time_end = microtime ( true );
$time     = $time_end - $time_start;
echo "4 Execution time : {$time} s " . PHP_EOL;

输出结果:

• 10w数据:

1 Execution time: 269.58895611763 s
2 Execution time: 25.353534936905 s
3 Execution time: 1.2171220779419 s
4 Execution time : 1.1611158847809 s

• 50w数据:

1 Execution time: 1358.3988881111 s
2 Execution time: 119.97599983215 s
3 Execution time: 6.7320001125336 s
4 Execution time : 6.4200000762939 s

总结:

在数据量大的时候,进行数据合并形式"values(...),(...),...",如果有数据完整性的需求,采用事务,相对来说能好些.