SQLiteのすすめ、チューニングのポイント

SQL ServerやMySQL、MariaDBなどサーバー型データベースでは必然的なチューニングですね。SQLiteもチューニングが必要になります。

チューニングのポイントを探る上で最も重要なポイントは、↓これです

SQL発行、処理時間等、正確な時間を測定。必要があれば記録を残し、比較する

これがわからないとチューニングの指針が定まりませんね。

個人的にSQLiteをよく利用する言語はPHPです。PHPで時間計測は以下のようにします。

$start = microtime(true); 
SQL発行処理();
echo number_format( microtime(true) - $start, 4 ) . PHP_EOL;

マイクロ秒で測定できます。（マイクロ秒は、ミリ秒の1/1000です）
マイクロ秒の現在時刻から記録した処理前時間を引くことで求められます。

要件を満たしているなら遅くてもチューニング不要だと思います。
ただ運用後、登録データ数が増えるテーブルは、遅くなる場合が多いのである程度のチューニングは必要かもしれません。

ここでは毎日1.25GBぐらいのデータ（２７テーブル以上）を更新する裏方処理、参照するWebサイトを設計した際の知見をまとめています。

チューニングのポイント
まとめ：業務系でもSQLite結構使えると感じています

チューニングのポイント

同じSQLiteのバージョン使っていますか？バージョンが異なるとチューニング意味ない場合があります。運用環境と同じSQLiteを使うのが吉です。FastCGI（ウェブサーバー）利用の場合、phpに組み込まれているSQLiteのバージョンを確認します。
バージョンの確認方法

ssh 運用環境サーバー; # 運用環境にログインします。
sqlite3 --version; # コマンドのバージョン確認
3.7.17 2013-05-20 00:56:22 118a3b35693b134d56ebd780123b7fd6f1497668

php -i | grep SQLite  ; # phpに組み込まれているSQLite3のバージョンを確認します
PDO Driver for SQLite 3.x => enabled
SQLite Library => 3.7.17
SQLite3 support => enabled
SQLite3 module version => 0.7
SQLite Library => 3.7.17

開発環境側でも同様にバージョンを確認し、一致すること確認します。

INDEX当たってますか？遅いSQLは、EXPLAIN QUERY PLANで実行計画を確認。
```
sqlite3 対象のデータベース
sqlite> explain query plan select ... 
```
普段のSQLの前にexplan query planを指定します。
```
sqlite> create table a( pk integer primary key autoincrement, name, x, y );
sqlite> explain query plan select  * from a; /* ① */
QUERY PLAN
`--SCAN a
sqlite> explain query plan select  * from a where x between 10 and 100;/* ② */
QUERY PLAN
`--SCAN a
```
①SQLのexplain query plan結果はSCANです。条件を指定していないため正常です。
②SQLのexplain query plan結果もSCANです。where区を指定しています。データが少ない場合高速です。データが増えると遅くなる要因となります。
where区で条件を指定しているフィールドに対してインデックスを設定します。
```
sqlite> create index idx_a_x on a ( x )
sqlite> explain query plan select  * from a where x between 10 and 100;
QUERY PLAN
`--SEARCH a USING INDEX idx_a_x (x>? AND x<?)
```
SCANからindexを使った検索に変わったことがわかります。
データを投入し、実際の速度を計測しながらインデックスを設定すべきかどうかを判断します。
なるべくCASTは使わない設計がいいです。 CASTを使いたいシーンがあるかと思います。where区での利用はなるべく避けた方がいいです。CASTすることでインデックスから除外されます。
```
sqlite> explain query plan select  * from a where CAST(x as INT) between 10 and 100;
QUERY PLAN
`--SCAN a
```
CAST使ってしまうとSCANに変わってしまいます。
実行中にexplain query planを使用することもできます。 attach databaseでデータベースを複数同時利用したり、処理中一時的に作るテーブルなども計測したい場合に便利です。explain query planをソースコードに埋め込んで実行した場合、id, parent, notused, detail、４つのフィールドで結果を取得できます。PHPの例
```
if( function_exists( 'explain_query_plan') == false ){
    function explain_query_plan( $db, $sql){
        $results = $db->query("explain query plan ".$sql);
        while($r = $results->fetchArray(SQLITE3_NUM)){
            echo implode( ' | ', $r ) . PHP_EOL;
        }
  }
}
```
inner / outer / subquery 構文使いますよね。少し複雑になるとSQLiteのパフォーマンス落ちる気がしています。inner joinやleft outer joinなど複雑ではないし、subqueryも普通だと思っています。ただパフォーマンス悪い場合が多いです。
① inner / outer / subqueryを利用した1つのSQL発行で処理した場合の速度
② メインのテーブルをselectしループしながら、関連データをquerySingleで取得した場合の速度
この２つの速度を比べてから速い方を選択するようにしています。
処理に合う中間テーブルをCREATEし、inner / outer / subqueryの登場回数を減らすSQLと速度を比較し、採用することもあります。
updateはできる限り使わない設計にする SQLiteを使っているとinsertに比べ、updateが遅いと感じること多いです。
またSQLiteのバージョン違いでupdate fromが使えない場合もあります。
特に遅いと感じるのは、virutal tableのFTS系のデータ更新です。FTS系のテーブルは更新せず、毎回CREATEから始めることが多いです。

① updateで処理するロジック
② 新しいテーブルを作成し、そこへ旧データを参照しながら挿入していくロジック
両方のパフォーマンスを比較の結果を元にロジックを選んでいます。
参照系はサーバーメモリに頼るに限る WebサイトからSQLiteデータベースを参照する際、パフォーマンスの要になるのは、サーバーのメモリと実感しました。契約によりますが1TBのメモリ使えます。
エックスサーバー

sqliteはメモリーで利用できます。全文検索する際大いに役立ちました。パフォーマンスは一定して速いと感じています。
```
$db = new SQLite3(":memory:");
$db->exec("attach database '" . __DIR__ . "/../sqlite.db" . "' as a ");
```
こんな感じでメモリーテーブルに本データベースを繋ぎます。Webリクエスト中だけアクティブな参照用データベースが出来上がります。ここは完全このセッションのみのデータベースになります。メモリテーブルにtableを作っても、挿入しても、database is lockedは発生しません。

私は、全文検索で見つかったIDリストをメモリテーブルに登録し、それをリンクして結果を表示させる仕組みにしています。

使えるメモリが多いから使える技です😀お使いのサーバーのメモリーを確認して多いようなら試してみてください。速度安定します。