Sqlite 插入資料慢解決方案

sqlite 插入資料很慢的原因是因為它是已檔案的形式存在磁碟中，每次訪問時都要開啟一次檔案，如果對資料進行大量的操作，那時很慢。

 

解決方案是用事務的形式提交：因為我們開始事務後，進行的大量操作的語句都儲存在記憶體中，當提交時才全部寫入資料庫，此時，資料庫檔案也就只用開啟一次。

 

我在沒有用事務形式插入4366條資料時用了507.781s，平均每一插入一條資料是116ms,用事務形式，插入只用了391ms，相關很大吧。

 

事務方式：

 

begin;<br />INSERT INTO "table" VALUES ('a', 'b', 'c');<br />INSERT INTO "table" VALUES ('a', 'b', 'c');<br />INSERT INTO "table" VALUES ('a', 'b', 'c');<br />commit;

 

順帶一點在sqlite裡的單引號轉義不是用反斜線'/'而是用單引號，就是在單引號前再加一個單引號（''）.

 

下面是寫的一個統計apache的指令碼，apache日誌按日期分目錄，在日期目錄下按每小時分檔案，日誌資料來源：

124.115.0.27 - - [25/02/2011:23:00:01] "GET /images/css.css HTTP/1.1" 200 3487 "http://www.XXXX.com/index.php?categoryid=175" "Sosospider+(+http://help.soso.com/webspider.htm)"

221.130.177.108 - - [25/02/2011:22:59:53] "GET /index.php?newsid=2010122810463740 HTTP/1.1" 200 5079 "-" "-"

指令碼如下：

<?php<br />/**<br /> * apache訪問日誌記錄轉換為SQLite庫<br /> *<br /> * 只保留IP，時間，session id<br /> * 注意: 執行程式與日誌資料放在同一目錄下，sqlite資料儲存在ipdb目錄下，以免找不到資料來源<br /> *<br /> */<br />$rootPath = __DIR__;<br />//轉換資料目錄<br />$dirPath = $rootPath . '/';<br />//SQLite 資料庫目錄<br />$ipdbPath = $rootPath . '/ipdb';<br />$db = null;<br />$fileArr = dirList($dirPath);<br />foreach ($fileArr as $file){<br /> $dbPath = str_replace($dirPath, $ipdbPath, $file);<br /> $dbPath = substr($dbPath, 0, -7) . '.db';<br /> if (!file_exists($dbPath)){<br /> if (FALSE === ($fp = fopen($dbPath, 'w'))){<br /> exit("open file {$dbPath} fail!/n");<br /> }<br /> fclose($fp);<br /> if (!is_null($db)){ $db = null;}<br /> $db = new PDO('sqlite:'.$dbPath);<br />//$table = substr($file, -6, 2);<br />$table = 'log';<br /> // $sql = "DROP TABLE IF EXISTS '{$table}';<br />$sql = "CREATE TABLE '{$table}' (<br />'ip' TEXT,<br />'start_time' TEXT,<br />'ucode' TEXT<br />);<br />CREATE INDEX 'start_time'<br />ON '{$table}' ('start_time' ASC);<br />";<br />$db->exec($sql);<br /> }else{<br /> if (is_null($db)){ $db = new PDO('sqlite:'.$dbPath);}<br /> }<br /> //插入檔案夾記錄<br /> if (FALSE !== ($fp = fopen($file, 'r'))){<br /> $i = 0;<br />$db->beginTransaction();<br /> while (!feof($fp)){<br /> $buffer = fgets($fp, 1024);<br /> $ip = trim(substr($buffer, 0, strpos($buffer, '-')));<br /> $start_time = substr($buffer, strpos($buffer, '[')+1, 19);<br /> if (FALSE === (strpos($buffer, ' /')) && empty($ip)) continue;<br /> $ucode = substr($buffer, strpos($buffer, ' /')+2, 32);<br /> $sql = "INSERT INTO 'log' ('ip', 'start_time', 'ucode') VALUES ('{$ip}', '{$start_time}', '{$ucode}')";<br /> $db->exec($sql);<br /> //if (0 == ($i++ % 100)) echo '*';<br /> }<br />$db->commit();<br /> fclose($fp);<br /> }<br />}<br />function dirList ($path) {<br /> $dir = dir($path);<br /> static $fileArr = array();<br /> while(($file = $dir->read()) !== FALSE){<br /> if (is_dir("{$path}/{$file}") && $file != '.' && $file != '..'){<br /> dirList("{$path}/{$file}");<br /> }else{<br /> if ($file != '.' && $file != '..'){<br /> $fileArr[] = $path . '/' . $file ;<br /> }<br /> }<br /> }<br /> $dir->close();<br /> return $fileArr;<br />}<br />?><br />