php高性能sqllite,PHP中 sqlite3数据批量插入性能优化

以下测试得出的时间仅供参考，实际时间因服务器的配置不同会有差异

传统方式，一万条数据插入耗时(46s)

$sqlite_db = new PDO('sqlite:sqlitedata.db', 'zheng', 'zheng', []);

$start = time();

$n = 0;

while ($n < 100000) {

$n ++;

$sql = "INSERT INTO `result_data` (`buguid`, `buname`, `签约金额_万`, `认购金额_万`, `认购套数_万`, `date`, `number1`, `number2`, `rp_buname_95938`, `rp_type_95938`, `rp_parentguid_95938`, `rp_levelcode_95938`) VALUES ('871709c2-083c-e511-80c0-00155d0a6f23', '名称', '31', '547', '776', '2015-12-07 00:00:00', '123.7890', '456.5678', '名称', '4', 'e64c7c15-7ac9-406c-9969-d41aeebf3a09', 'a.aa.001.01')";

$sqlite_db->exec($sql);

}

echo time() - $start;

传统方式，关闭磁盘的读写检查,一万条数据的导入(10s)

$sqlite_db = new PDO('sqlite:sqlitedata.db', 'zheng', 'zheng', []);

$start = time();

$n = 0;

$sqlite_db->exec('PRAGMA synchronous = OFF;');

while ($n < 10000) {

$n ++;

$sql = "INSERT INTO `result_data` (`buguid`, `buname`, `签约金额_万`, `认购金额_万`, `认购套数_万`, `date`, `number1`, `number2`, `rp_buname_95938`, `rp_type_95938`, `rp_parentguid_95938`, `rp_levelcode_95938`) VALUES ('871709c2-083c-e511-80c0-00155d0a6f23', '名称', '31', '547', '776', '2015-12-07 00:00:00', '123.7890', '456.5678', '名称', '4', 'e64c7c15-7ac9-406c-9969-d41aeebf3a09', 'a.aa.001.01')";

$sqlite_db->exec($sql);

}

echo time() - $start;

开启事务,一万条数据的导入(0.6s)

$sqlite_db = new PDO('sqlite:sqlitedata.db', 'zheng', 'zheng', []);

$start = time();

$n = 0;

$sqlite_db->beginTransaction();

while ($n < 10000) {

$n ++;

$sql = "INSERT INTO `result_data` (`buguid`, `buname`, `签约金额_万`, `认购金额_万`, `认购套数_万`, `date`, `number1`, `number2`, `rp_buname_95938`, `rp_type_95938`, `rp_parentguid_95938`, `rp_levelcode_95938`) VALUES ('871709c2-083c-e511-80c0-00155d0a6f23', '名称', '31', '547', '776', '2015-12-07 00:00:00', '123.7890', '456.5678', '名称', '4', 'e64c7c15-7ac9-406c-9969-d41aeebf3a09', 'a.aa.001.01')";

$sqlite_db->exec($sql);

}

$sqlite_db->commit();

echo time() - $start;