アフィリエイト広告を利用しています

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

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

チューニングのポイントを探る上で最も重要なポイントは、↓これです
  1. SQL発行、処理時間等、正確な時間を測定。必要があれば記録を残し、比較する

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

個人的にSQLiteをよく利用する言語はPHPです。PHPで時間計測は以下のようにします。
$start = microtime(true); 
SQL発行処理();
echo number_format( microtime(true) - $start, 4 ) . PHP_EOL;
  • マイクロ秒で測定できます。(マイクロ秒は、ミリ秒の1/1000です)
  • マイクロ秒の現在時刻から記録した処理前時間を引くことで求められます。


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


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

チューニングのポイント



  1. 同じ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
    開発環境側でも同様にバージョンを確認し、一致すること確認します。

  2. 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;/* A */
    QUERY PLAN
    `--SCAN a

    @SQLのexplain query plan結果はSCANです。条件を指定していないため正常です。
    ASQLの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を使った検索に変わったことがわかります。
    データを投入し、実際の速度を計測しながらインデックスを設定すべきかどうかを判断します。

  3. なるべく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に変わってしまいます。

  4. 実行中にexplain query planを使用することもできます。 attach databaseでデータベースを複数同時利用したり、処理中一時的に作るテーブルなども計測したい場合に便利です。explain query planをソースコードに埋め込んで実行した場合、id, parent, notused, detail、4つのフィールドで結果を取得できます。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;
    }
    }
    }

  5. inner / outer / subquery 構文使いますよね。 少し複雑になるとSQLiteのパフォーマンス落ちる気がしています。inner joinやleft outer joinなど複雑ではないし、subqueryも普通だと思っています。ただパフォーマンス悪い場合が多いです。
    @ inner / outer / subqueryを利用した1つのSQL発行で処理した場合の速度
    A メインのテーブルをselectしループしながら、関連データをquerySingleで取得した場合の速度
    この2つの速度を比べてから速い方を選択するようにしています。

  6. updateはできる限り使わない設計にする SQLiteを使っているとinsertに比べ、updateが遅いと感じること多いです。またSQLiteのバージョン違いでupdate fromが使えない場合もあります。
    特に遅いと感じるのは、virutal tableのFTS系のデータ更新です。まじ遅い

    パフォーマンス比較の結果、新しいテーブルを作成し、そこへ旧データを参照しながら挿入していくような流れを選んでいます。

  7. 参照系はサーバーメモリに頼るに限る WebサイトからSQLiteデータベースを参照する際、パフォーマンスの要になるのは、サーバーのメモリと実感しました。契約によりますが1TBのメモリ使えます。


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

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

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



まとめ:業務系でもSQLite結構使えると感じています


OracleやSQL Serverと違い、SQLiteは同時更新に対応していません。複数スレッドやプロセスから同時に書き込むような処理には向いていないのは確かです。

シングルで複数データベースを作り上げ、それをまとめて、最後にSQL Serverへ投入するといったような設計も可能です。
サーバー系データベースはキャッシュに当たっているかどうかがパフォーマンスに影響しやすいですよね。SQLiteはこれがないのでシンプルでいいです。

SSDが一般的になり、サーバーメモリも豊富、インデックスが適切に設定されたSQLiteは快適でした。

 

  • 初期費用0円(2022年8月4日に撤廃)
  • 安定したサイト運営が可能(CPU/メモリ リソース保証 6コア/8GB〜)
  • 契約中は独自ドメインつき 独自ドメイン永久無料特典があります
  • 最大10日間無料で、サーバーのお試し利用ができます
  • ディスクキャッシュも高速 NVMe SSDを採用(300GB〜)
  • 大量アクセスでも安心 転送量は無制限

 

 

この記事へのコメント
コメントを書く

お名前:

メールアドレス:


ホームページアドレス:

コメント:

※ブログオーナーが承認したコメントのみ表示されます。

この記事へのトラックバックURL
https://fanblogs.jp/tb/12345363
※ブログオーナーが承認したトラックバックのみ表示されます。

この記事へのトラックバック
最新記事
最新コメント
タグクラウド
カテゴリアーカイブ