この広告は30日以上更新がないブログに表示されております。
新規記事の投稿を行うことで、非表示にすることが可能です。

PHP＋PDOでsqlite3のデータベースを使うことがありました。

データ件数は、１日１０００件ぐらい増える感じで、１万件、５万件、10万件と増えていきます。

当然追加(INSERT)する際は、PK(実際はインデックスなし）の存在を確認してInsert Or Updateな処理が必要になります。

件数が多くなるとトランザクションは必須です。

テストデータは、５万件超えのもので確認しています。
何も考えずに、select pk from tbl where pk=?的なSQLで検索して、存在しなければInsert、存在すればUpdateという処理にすると

（以下、msec=ミリ秒です。）

select発行コストは、１件あたり0.001sec(1msec)とごくわずかな時間でした。が、
UPDATE発行コストは、１件あたり0.06sec(60msec)もかかります。
１０件で、600msec、１００件で6秒、１０００件で１分・・・・うーん数は力です。

UPDATE発行コストを下げるには・・・

件数が少ない場合、無視できるコストですが、件数が１０万件、将来的に１００万件を超えるようなテーブルでは、無視できません＞＜

パッと思いつく方法は、以下２つでした
1. UPDATE対象専用のテーブルに、INSERT、まとめてUPDATEする方法
2. UPDATE対象専用のテーブルに、INSERT後、更新対象をTBLからDELETE、その後UPDATE対象＝＞TBLヘ

「1. UPDATE対象専用のテーブルに、INSERT、まとめてUPDATEする方法」
＝＞INSERTコストは全く気になりません。
　　update tbl set x = (select uptbl.x from uptbl where tbl.pk = uptbl.pk ) where exists (select uptbl.pk from uptbl where tbl.pk = uptbl.pk)
　　みたいなSQLでUPDATE対象専用のテーブル＝＞TBLへまとめて更新する方法は、
　　１件ずつUPDATEする方法とほぼ同等のコストでした。＞＜

「2. UPDATE対象専用のテーブルに、INSERT後、更新対象をTBLからDELETE、その後UPDATE対象＝＞TBLヘ」
＝＞ delete tbl from pk = ?というSQLで１件ずつ削除すると１件削除の平均コストは124msecでした
　　delete 超遅い＞＜


たった２つしか試していませんがw・・・
m(_ _)m UPDATEコストを低減させる方法は、見つかりませんでした。

ここまでを一言でまとめると・・
sqlite3ではUPDATE/DELETEはコストが高い！です。

できるだけ、UPDATE/DELETEを発行しないような仕組みに作り変えます。

sqlite3は、INSERT/ALTERを多用しようぜ！

ここまで確認している中で、INSERTコストは、全く気になりません。
トランザクションのメリットですね。

ということで、INSERT主体の処理に変更します。
ちなみに、私は、この方法で、５分かかっていた処理が 3秒になりましたw

元
beginTransaction
LOOP START
SELECT PK
INSERT OR UPDATE
LOOP END
commit

と、
１テーブルに対して、SELECTして、値を得た結果でINSERTかUPDATEで挿入、更新を行う処理だったものを、

beginTransaction
SELECTで全件検索
　更新対象となるキー　値を書き換えて UPTBLへINSERT（更新）
更新対象外　　　　　そのままUPTBLへINSERT（更新対象外）
SELECT END
commit

更新対象で処理していないものを全部UPTBLへINSERT (新規）
という流れに変更しました。

UPTBLは、CREATE TABLEでTransaction開始前に作成しています。

この変更点をサクっというと、
５万件のテーブルは触れずに、新たに５万件のテーブルを更新しながら作っちゃう作戦です。
繰り返しになりますが、UPDATEやDELETEを発行しない方法です。

でも、
５万件のデータでも、２、３秒で終わります。


この後、元々のTBLをDROP TABLE、
更新したUPTBLをTBLに変更します。
＝＞ALTER TABLE UPTBL RENAME TO TBLでUPTBLをTBLに変更しちゃいます。

私が検証した結果では、速度面でUPDATE/DELETEは使ってはいけない、代わりに新たにテーブル作っちゃえという結果になりました。


最後に・・・
検証は行っていますが、あくまで個人の環境です。
お手元にあるDBの特性に合わせてチューニングすることをお勧めします。

・・・・

ファイル系のデータベース、sqlite以外にもSQL Server CompactやAccess(ACE)などもあります。

sqliteは、SQL Server Compact(sdf)やOLEDBに比べて、とても簡単な印象を受けました。
PHPなどでは何もインストールしなくても使えるし、fts3,4の仮想テーブルを使うと型宣言も必要ないし、・・

万件データを扱うようになると、やはりSQLコストが問題になってきます。
likeや=での検索は、致命的に遅いです＞＜

なので、全文検索のmatchという選択にせざる終えませんが、
謎の挙動が多いので、https://sqlite.org/fts3.html を徹底的に読んだ方が良さそうです・・・



sqlite3のデータベースは、ディスクIO速度が性能に直結します。
可能であれば、SSDの領域にdbファイルを置いて処理すると格段にパフォーマンスが上げられます。
さらに余裕があれば、メモリ領域に置くことで高速化可能です。

SQL Server/Oracle/MySQL/PostgreSQLなども同様ですが、結局のところ、IOコストですね・・

変更来歴
2015/1/17 言葉足らずな文章だった箇所を修正しています。
2017/9/22 SQLite3のコツ10選を追加しました。

続きを見る方はこちらをクリックしてください