PHP中 sqlite3資料批量插入性能優化

        以下測試得出的時間僅供參考，實際時間因伺服器的配置不同會有差異

1. 傳統方式，一萬條資料插入耗時（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;
           

1. 傳統方式，關閉磁盤的讀寫檢查,一萬條資料的導入（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;
           

1. 開啟事務,一萬條資料的導入（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;
           

轉載于:https://my.oschina.net/gzheng/blog/844920