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選を追加しました。

SQLite3のコツ 10選

SQLIte3を使ったシステムやサイトに携わり、この記事を書いた当初より考え方や知識が向上しました。得た知識をSQLite3のコツ 10選としてご紹介しています。

SQLite3のコツ1 EXPLAIN QUERY PLANを使ってコストを確認する

ある程度データが溜まったところで、以下のようにEXPLAIN QUERY PALNを使ってコストを確認できます。
sqlite> EXPLAIN QUERY PLAN select ...
高速化のコツは、SCAN TABLE ..と単純なテーブルスキャンとなっている部分を見つけて、
ここの箇所にインデックスを追加します。
SEARCH TABLE ... using INDEX インデックス名ようにインデックスを使った検索にすることで高速化できます。
SQLite3公式ドキュメント：The EXPLAIN QUERY PLAN Command

SQLite3のコツ2 テーブルはできるだけPK( pk integer primary key autoincrement)フィールドを用意する

PK：重複しないprimary keyがあるだけでSQLコストがだいぶ減ります。

create table tbl ( pk integer primary key autoincrement, ... )

SQLite3のINTEGER型は、入っている数値によって1バイトから8バイトまで可変で成長します。BIGINTと同等の扱いができるので、MAXになる心配はほぼ不要です。

すでに作ったテーブルにPKを追加したい場合は、関連するインデックス、VIEWを削除の上、以下のような流れで追加できます。
create table newtbl( pk integer primary key autoincrement, 元のフィールド・・・) ;
insert into newtbl( 元のフィールド・・・ ) select * from 元のテーブル
alter table 元のテーブル rename to 元テーブルのバックアップ
alter table newtbl rename to 元のテーブル
関連するインデックス、VIEWの作成

SQLite3のコツ3 更新系は粒度を細くしたデータベースファイルを用意する

データベースサーバーを使っているとインスタンスやデータベースを新たに作ることはおおごとになりがちです。sqlite3はファイルを１つ増やすだけで済みます。
SQLIte3のテーブルロックによる失敗を限りなく減らすことが可能になる考え方です
更新が必要なテーブルを１つのデータベースファイルに分けておくと並列で更新することができます。分けるといっても全てのテーブルがある状態のファイルをコピーする方法でもOKです。



例えばメインのスケジュール処理で以下のようにします。考え方の１つです。

copy マスター.sqlite 更新1.sqlite
・・・
copy マスター.sqlite 更新10.sqlite
del 完了結果.1 .. 完了結果.10
start 更新プログラム1 
start 更新プログラム9
start 更新プログラム10 
完了結果ファイルの出来上がりを待つ
sqlite3 更新1.sqlite3 ".dump table1" | sqlite3 new.master.db 
sqlite3 更新2.sqlite3 ".dump table2" | sqlite3 new.master.db 
・・・
sqlite3 更新10.sqlite3 ".dump table1" | sqlite3 new.master.db 
copy new.master.db マスター.sqlite

コマンドプロンプトを例にしていますが、なんのプログラムでも構いません。複数の更新処理を同時実行させ、出来上がったものをsqlite3コマンドでnew.master.dbへまとめあげています。

SQLite3のコツ5 CRUDでちょっと遅いかなと思ったら

更新系では不要なインデックスもまとめて削除した方が高速になります。
drop index if exists インデックス名
これでインデックスを削除できます。

プログラムでCRUDする際は、トランザクションを使います。
BEGIN DEFERRED;
ステートメント = prepare( "insert into tbl( ..) values( :a, :b );
bindValue( ':a', value);
ステートメント->execute();
COMMIT

PHPの場合は、bindValue()をお勧めします。bindParam()は遅延実行されるので問題が起きやすいです。

C#の場合は、以下のような流れですね。
using( SQLiteTransaction tran = connect.BeginTransaction() ){
SqlCommand command = new SqlCommand(null, connect );
command.CommandText = "insert into tbl (...) values (@a,@b)";
command.Parameters.Add( new SqlParameter("@a", SqlDbType.Text, value )) ;
command.Parameters.Add( new SqlParameter("@b", SqlDbType.Text, value )) ;
command.Prepare();
ループ処理{
command.Parameters[0].value = a, command.Parameters[1].value = b;
command.ExecuteNonQuery();
}
}

SQLite3のコツ6 分離、まとめる際はテーブル名を変更する

コツ3でご紹介した粒度を細かくする際や、細かくしたテーブルをまとめる際に使えます。同じフィールド構成のテーブルが複数あった場合、プログラム側は同一にしておきたい場合に使える考え方です。条件文が少ないプログラムを仕立てることで品質向上を目指せます！
以下コマンドでリネームできます。
alter table 元のテーブル名 rename to 新しいテーブル名

注意点があって、元テーブルに関係したインデックスやビューがあると失敗します。そのため、事前にdrop index,drop viewを使って削除しておく必要があります。

SQLite3のコツ7 参照系はまとめてインデックスを活用しまくる

更新系と参照系のデータベースファイルを分けることができるので、参照系は、selectでwhereするフィールドを重点的にindexを作成しておきます。

select * from tbl where type in ('a','b','c')というSQLがあったら、以下のようなコマンドで追加しましょう
drop index if exist tbl_type ; create index tbl_type on tbl ( type )

ダメだったらdropすればいいだけです。
また、以下コマンドで大きくなったファイルサイズを元に戻すこともできます。
VACUUM;

SQLite3のコツ8 型はプログラムで意識的につける

sqlite3のフィールドには型を指定することができます。この型は、ほぼおまけです。varcharやtextと書いても数値を設定できますし、int,integerと書いても文字列を入れることができます。おまけです。コメントです。

プログラム側で型を指定して追加することを意識します。
具体的には、prepareを使ったSQLに値を設定する際、型を指定します。
PHP: $stmt->bindValue ( 場所, 値, 型[SQLITE3_TEXT / SQLITE3_INTEGER/SQLITE3_FLOAT/SQLITE3_BLOB] )
C# : new SqlParameter( 場所, 型[SqlDbType.Text...],値 )

SQLite3のコツ9 全文検索(FTS)を活用する

SSD上にあるsqlite3はlike文でも遅いと感じることは少ないです。ただ仮想テーブルFTSを使うと安定して速い結果を得ることができます。

create virtual table tbl using fts4 ( motoid, txt );

select motoid from tbl where txt match 'キーワード'

スペースで区切ったテキストデータを全文検索テーブルのデータとするのがミソです。スペースじゃないとうまくマッチしてくれません。
insert into tbl (txt) select 'キーワード キーワード2 キーワード3 ・・・'


FTSテーブルは、必要なインデックスを自動で作成します。そのためサイズは大きくなることに注意してください。using FTS指定には、fts3, fts4, fts5が指定できます。sqlite3自体のバージョンによって使えるftsが異なります。古いSQLite3では使えません。

fts3,4,5の違いや利用できるバージョンは以下を見てみてください。
SQLite3公式：SQLite FTS3 and FTS4 Extensions
SQLite FTS5 Extension

全文検索(FTS)を使うならコンパイルオプションに気をつける

キーワードに近いデータを探せるNEAR文などを使い始めると環境によって検索結果が変わってくることがあります。同じデータベースでも違います。
sqlite3コマンドとphpやapache、sqlite3.dllなどコンパイルされたオプションが異なるために起きます。

ご利用されるシステムでコンパイルオプションを統一してください。
これが難しい場合、できる限り複雑なことを避ける設計にした方が問題を回避できます。

SQLITE_FTS3_MAX_EXPR_DEPTHパラメータが影響します。

詳しい説明は、こちらのSQLite3のコンパイルオプション一覧からご確認できます。
Compile-time Options

SQLite3のコツ10 PHPでSQLite3はcreateFunctionを活用しよう！

PHPのSQLite3は、createFunctionが使えます。
これを使うとPHPの関数をSQLで利用することができます。

$db = new SQLite3("");
$db->createFunction("php_date", "date", -1 );
$db->createFunction("php_strtotime", "strtotime", 1);
var_dump($db->querySingle("select php_date('Y-m-d') , php_strtotime(php_date('Y-m-d')) ", true ));

実行結果例
array(2) {
["php_date('Y-m-d')"]=>
string(10) "2017-09-22"
["php_strtotime(php_date('Y-m-d'))"]=>
int(1506038400)
}

超強力な正規表現関数preg_matchのほか、自作で作った関数もSQLで呼び出すことができます。phpは可変引数にも対応しているので何かと便利ですよ^^

この機能を使うことで、数式演算結果と合わせて検索させたりすることも可能になります。
ぜひチャレンジしてみてください。


最後に、

MySQLよりSQLite3の方が良いと思ったこと

MySQLを始め、SQL Server、Oracle、PostgreSQLなどのリレーショナルデータベース管理システム（データベースサーバー）は、システムなので、DBサーバーを立ち上げたり、権限を設定したり、運用ノウハウを蓄積したり大掛かりになりがちです。

オンライン系とオフライン系、遠隔地バックアップ、定期メンテナンス、緊急メンテナンスなどシステムを使っている上で避けられないイベントが発生しやすいです。

SSISなどを使ってデータを収集し、スターフレークスキーマーにしてSSASキューブを作るなどMicrosoft製品にどっぷりはまっていることもありました。振り返って考えると、大規模なシステムでもphpとSQLiteを組み合わせることでひとつ一つの要素を単純化できることに気がつきました。単純化のメリットは、コストを抑えてより利益あげられることですね！



データベースサーバーとSQLite3を比べると何が違うのか、少し前まではやっぱりディスクIOの違いが大きいと思っていました。サーバーはSQLを効率的にキャッシュしてくれるメリットもありますしね！ただ最近、サーバー系のストレージはSSD化の波がきていて、ディスクIOコストが激減していると感じています。

高速ストレージ上のSQLite3は十分速いです。

SQLite3は所詮ファイルなので、システムを経由せずにバックアップやコピーの作成、弾力的なメンテナンスが可能です。

データベースサーバーを使っているとインデックスを後から追加する必要が出てきたりしますよね。この場合、参照系はよくても、CRUD系処理への影響を検討し、提案しないといけません。
SQLite3なら、参照系とCRUD系のデータベースを高速コピーで対応できるので簡単に分けることができます。