【Azure】App Service on Linux上のファイルDB「SQLite」でお高いマネージドDBを置き換えてみた：Tech TIPS

WebアプリのバックエンドDBMSとして用いられることも多いマネージドDBサービス。ただ小規模なDBだとコストパフォーマンスが良くないことも。ファイルベースのオープンソースDBMS「SQLite」で置き換えるために、App Serviceを前提として、その高速化テクニックを中心に説明する。

» 2025年06月25日 05時00分公開

[島田広道，デジタルアドバンテージ]

連載目次

Azure App Service on LinuxでSQLite DBを活用する

対象：Azure App Service on Linux

　Webアプリでは、データベース管理システム（DBMS）からデータを抽出することが多い。Azureであれば、Azure Database for MySQL／PostgreSQLやAzure SQL DatabaseといったマネージドDB（データベース）サービスをDBMSとして使用することも多いのではないだろうか。

　ただ、こうしたマネージドDBサービスの単価は安くない。特にDBの規模が小さいほど、DB側がオーバースペックとなってコストパフォーマンスは悪化しやすい。

　そのような場合は、ファイルベースのオープンソースDBMSである「SQLite」を活用することでコストパフォーマンスを向上できる可能性がある。実際、筆者が試した限りでは、幾つかの高速化テクニックを適用することで、小規模なDBならマネージドDBサービスを代替できる程度の性能を実現できた。

　そこで本Tech TIPSでは、小規模なDBを対象として、SQLiteとApp Serviceを組み合わせて、低コストかつ性能も良好なWebアプリを開発するうえで必要な高速化のテクニックについて説明しよう。

　SQLiteのDBファイルはApp Serviceとは別拠点の作業用PC（x64版Windows 10／11）で作成し、それをApp Serviceに配置してWebアプリからクエリするように設計している。Webアプリ上ではDBに書き込まず、読み出し専用とする。

　App ServiceのスタックはPHP 8.3または8.4である。そのプログラムや標準装備の拡張機能は置き換えず、App Service提供のものをそのまま利用する。PHPの設定については必要に応じて変更している。

　SQLiteのDBを作成する手順などについては、Azureと直接関係がないので割愛させていただく（そのため、SQLiteについてはある程度の知識が必要になる）。

高速化テクその1――ローカルディスクにDBファイルを移す

　App Serviceにコンテンツを保存する場合の標準的なストレージである「共有ディスク」は、相対的にあまり速くない。そのため、よく参照するデータファイルはインスタンスごとに用意されている「ローカルディスク」に配置した方が、より速くアクセスできる。

　このテクニックについては、Tech TIPS「Azure App Service on Linuxでファイルアクセスを高速化する方法（ローカルディスク活用編）」で詳細を説明している。その効果は大きく、筆者が管理／運用を担当しているAPIの一つでは、ローカルディスクに配置しただけで3倍以上も高速化できた。

DBファイルのありかとAPIの応答時間の関係
SQLite3のDBファイルを、共有ディスク上の［/home/site/］以下とローカルディスク上の［/var/opt/］以下にそれぞれ配置したときに、そのデータベースを参照するAPIの応答時間を測定した。横軸が応答時間で単位はmsec（ミリ秒）。グラフの棒が短い（数値が小さい）ほど性能が優れている。App ServiceのスケールはP1V3で、スタックはPHP 8.3。DBは非圧縮でインデックスは未作成。インメモリDBは未使用。10回測定した平均値を掲載している。測定対象のAPIは、地図上の矩形範囲を指定して呼び出すと、その中にある地点情報（緯度／経度など）を返すというもの。

高速化テクその2――DBを圧縮する

　筆者がApp Service＋PHP＋SQLiteの組み合わせで調査した限りでは、SQLiteのDBファイルのサイズが大きくなるほど、クエリがどんどん遅くなる傾向がある。

　そこで、DBファイルを圧縮して小さくすることで速度向上を図ったところ、「sqlite-zstd」というSQLiteの拡張機能（以下、zstd拡張機能）で圧縮するのが最良だった。

sqlite-zstd: Transparent dictionary-based row-level compression for SQLite（作者のphiresky氏のブログ）

　そこで、以下ではzstd拡張機能に焦点を当てて、App Serviceでの注意点なども含めて使い方を説明しよう。

●SQLite DBを扱うためのコマンドラインツールをインストールする

　例えばx64版Windows 10／11でSQLite DBを取り扱うには、以下のSQLiteダウンロードページにある「Precompiled Binaries for Windows」の「sqlite-tools-win-x64-＜バージョン番号＞.zip」をダウンロードし、実行検索パスに含まれるディレクトリにEXEファイルを解凍する。

SQLite Download Page（SQLite Home Page）

　コマンドプロンプトを開いて「sqlite3 --version」と実行したとき、バージョン情報が表示されれば、SQLite本体の準備は完了だ。

●DBを圧縮するためのzstd拡張機能を入手する

　以下のGitHub上の配布ページからzstd拡張機能をダウンロードする。

sqlite-zstdのリリースページ（GitHub）

　筆者がx64版Windows 10で作業した時は、［sqlite_zstd-v＜バージョン番号＞-x86_64-pc-windows-msvc.zip］を選択した。これを解凍して「sqlite_zstd.dll」をいずれかのディレクトリに格納する（一時的ではなく、固定的なディレクトリの方がよい）。

●SQLite DBを圧縮するためのSQLを用意する

　次に、DBを圧縮するためのSQLを以下のように記述して、［compress_by_ztsd.sql］といったファイル名で保存する。

PRAGMA journal_mode=WAL;
PRAGMA busy_timeout=2000;
.load '＜sqlite_zstd.dllのフルパス＞'
SELECT zstd_enable_transparent('{"table": "＜テーブル名＞", "column": "＜カラム名＞", "compression_level": 19, "dict_chooser": "''a''", "dict_size_ratio": 0.01}');
SELECT zstd_incremental_maintenance(null, 1);
VACUUM;

zstd拡張機能を使って既存のSQLite DBを圧縮するためのSQL
ファイル名： compress_by_ztsd.sql

　圧縮結果を大きく左右するのは、「SELECT zstd_enable_transparent」の行で、JSONで指定している圧縮に必要なパラメーターである。


キーワード	型や範囲	内容
table	文字列	圧縮対象のカラムを含むテーブル名
column	文字列	圧縮対象のカラム名
compression_level	1〜19の整数	この数値が大きいほど圧縮後のDBは小さくなる
dict_chooser	文字列	圧縮対象のカラムをなるべく似たデータ同士でグループ分けするためのSQL文。グループごとに異なる文字列を出力させる必要がある。圧縮に使われる辞書はこのグループごとに作成される。「''a''」だとグループは1つだけ、すなわち単一辞書で圧縮される。エスケープのため、シングルクオートは二重にする必要がある
dict_size_ratio	0より大きく1以下の実数	「dict_chooser」が分割した各グループ内で、そのデータ総量に対して辞書が対象とするデータ量の割合。デフォルトは0.01＝1％。圧縮時にエラーとなったり、非圧縮時よりDBが大きくなってしまったりした場合は、この数値をだんだん増やしながら再試行して、最適な値を探る必要がある
zstd拡張機能で圧縮に必要なパラメーター主要なパラメーターのみ記載している。その他はソースコード（transparent.rs）の「pub struct TransparentCompressConfig」以下のコメントを参照していただきたい。

　特に注意が必要なのは「dict_size_ratio」の数値である。データの内容によっては、デフォルトの「0.01」だと圧縮時にエラーが発生して失敗したり、圧縮そのものは成功してもDBファイルのサイズが非圧縮時より増えたりするからだ。そのような場合は、この数値をだんだん増やしながら圧縮を再試行して、DBファイルのサイズが最も小さくなる値を探る必要がある。

　また圧縮対象のカラムの内容が行によって大幅に異なる場合は、「dict_chooser」で行をグルーピングした方が、より効率良く圧縮できることがある。例えばプライマリキー「id」を含むテーブルで1000行ごとに圧縮対象カラムの内容が変わる場合は、dict_chooserを以下のように指定してみる。

"dict_chooser": "''i.'' || (id/1000)"

　「||」はその両側の文字列を連結するので、上記のdict_chooserの値は0〜999行が「i.0」、1000〜1999行が「i.1」、2000〜2999行が「i.2」、……という文字列になる。

　また、「name」カラムの内容が「＜ブランド名＞＜店舗名＞」の場合、ブランド名でグルーピングするには、以下のように文字列操作関数でブランド名を抽出する（ブランド名にスペースは含まれていないものとする）。

"dict_chooser": "SUBSTR(name, 1, INSTR(name, '' '') - 1)"

　dict_chooserによるグルーピングを確認するには、対象の非圧縮DBに対して以下のようなSQL文を実行して、出力の「dict_chooser」カラムをチェックすればよい。

SELECT ＜dict_chooserの内容（エスケープなし）＞ as dict_chooser, * from ＜圧縮対象のテーブル名＞ limit ＜出力する行数＞;

　ただ、dict_chooserに最適なSQL文を導出するのは難しいことも多い。まずは「''a''」を指定して単一辞書とし、dict_size_ratioの数値を変えながら圧縮を試み、うまくいかない場合はdict_chooserも探ってみるとよさそうだ。

　複数のカラムまたはテーブルを圧縮するには、カラムごとに「SELECT zstd_enable_transparent」の行を追加すること。

●既存DBの圧縮を実行する

　［compress_by_ztsd.sql］を用意できたら、以下のコマンドラインで実際にDBを圧縮する。

sqlite3.exe ＜既存の非圧縮DBファイルのパス＞ < compress_by_ztsd.sql

　指定したDBファイルがそのまま圧縮され、元の非圧縮のDBファイルは残らないので注意が必要だ。

●App Service＋PHPで解凍するためのzstd拡張機能を入手する

　ここからは、圧縮したDBを利用するWebアプリ側、すなわちApp Service on Linux＋PHPでのSQLite対応について説明する。

　まずは、zstd拡張機能の配布ページからLinux用のアーカイブ［sqlite_zstd-v＜バージョン番号＞-x86_64-unknown-linux-gnu.tar.gz］をダウンロードし、［libsqlite_zstd.so］というファイルを解凍する。

sqlite-zstdのリリースページ（GitHub）

　ここで注意が必要なのが、PHPに組み込まれているSQLiteライブラリとzstd拡張機能それぞれのバージョンだ。執筆時点でPHP 8.3／8.4にはVer. 3.40.1のSQLiteライブラリが組み込まれていた。これにzstd拡張機能のVer. 0.3.5をロードしようとしたらバージョン不整合のエラーが発生してしまった。そこで1つ前のVer. 0.3.2にバージョンダウンしたところ、無事にロードできた。

●PHPの設定を変更しつつzstd拡張機能を配置する

　次に、App Service上で［libsqlite_zstd.so］を配置するディレクトリを決める。App Serviceの再起動時に消失しないように、［/home/sqlite/extensions］のような［/home］以下のディレクトリにした方が楽だろう。

　その上で、PHPの設定ファイル［/usr/local/etc/php/conf.d/php.ini］に、以下の行を追記する。「=」の右側には上述のディレクトリのフルパスを指定すること。

sqlite3.extension_dir=/home/sqlite/extensions

　ただし、［/usr/local/etc/php/conf.d/php.ini］はApp Serviceを再起動すると元に戻ってしまう。再起動後もphp.iniの変更（カスタマイズ）を維持するには、［/home/］ディレクトリ以下に配置した変更済みのphp.iniをスタートアップスクリプトで［/usr/local/etc/php/conf.d/php.ini］に上書きする、といった対処が必要だ。詳細はTech TIPS「【Azure】App Service on LinuxのWebサーバ『NGINX』をカスタマイズする方法」を参照していただきたい。

　php.iniを変更したらApp Serviceをいったん再起動して、上記のphp.iniの変更を反映させる。その後にPHP上で「phpinfo()」を実行してPHPの設定一覧を出力し、「sqlite3」のセクションにある「sqlite3.extension_dir」が上記のディレクトリを指しているかどうか確認するとよい。

　ここまで設定できたら、上記のディレクトリに［libsqlite_zstd.so］を配置しておく。

●zstd拡張機能で圧縮されたDBをApp Service上のPHPから利用する

　ここまでで準備は完了だ。実際にApp Service＋PHPで、zstd拡張機能によって圧縮されたDBを参照するプログラムコードの例を紹介しよう。

// ＜前略＞

// DBと接続する
$dsn = "＜DBファイルのフルパス＞";
$db = new SQLite3($dsn, SQLITE3_OPEN_READONLY); // 読み出し専用でDBを開く
$db->enableExceptions(true); // エラー時に例外を発生させる
$db->loadExtension("libsqlite_zstd.so"); // zstd拡張機能をロード

// DBをクエリしてデータを抽出する
$sql = "SELECT id, lat, lon, name, info FROM poi_data WHERE lat BETWEEN ? AND ? AND lon BETWEEN ? AND ?";

$stmt = $db->prepare($sql);
$stmt->bindValue(1, $s, SQLITE3_FLOAT); // 南側の緯度を割り当てる
$stmt->bindValue(2, $n, SQLITE3_FLOAT); // 北側の緯度
$stmt->bindValue(3, $w, SQLITE3_FLOAT); // 西側の経度
$stmt->bindValue(4, $e, SQLITE3_FLOAT); // 東側の経度
$results = $stmt->execute(); // クエリを実行

$data = [];
while ($row = $results->fetchArray(SQLITE3_ASSOC)) { // 連想配列で全データを収集
$data[] = $row;
}

$db->close(); // DBをクローズする

// ＜$dataに格納されたデータを利用するコード＞

// ＜後略＞

【PHP】zstd拡張機能で圧縮されたDBでクエリを実行してデータを抽出する
これは緯度（lat）と経度（lon）、地点名（name）、地点概要（info）からなる地点情報テーブル（poi_data）から、地表上の特定の矩形範囲内にある地点を抽出するプログラムの例である。

　zstd拡張機能のために必要なのは「$db->loadExtension("libsqlite_zstd.so");」だけで、その他は変更する必要はない。DBが圧縮されていない場合と同じプログラムコードがそのまま適用できる。

●zstd拡張機能で圧縮した効果は？

　実際にAPIで参照しているSQLiteのDBをzstd拡張機能で圧縮して、APIの応答時間がどれくらい短縮できるか、測定してみた。

DBの圧縮／非圧縮とAPIの応答時間の関係
SQLite3の非圧縮DB（ファイルサイズ16.0MB）と、zstd拡張機能で圧縮したDB（同4.52MB）それぞれをAPIから参照したときの応答時間を測定した。DBファイルは共有ディスクに配置している。その他の条件は前出の図「DBファイルのありかとAPIの応答時間の関係」と共通。

　上図の通り、APIの応答時間は半分以下に短縮された。前述したローカルディスクを利用できない場合は、その代わりにzstd拡張機能でDBを圧縮すれば高速化できそうだ。

●zstd拡張機能のデメリット

　筆者が試した限りでは、PHPのデータベース抽象化レイヤーである「PDO」（PHP Data Objects）でzstd拡張機能をロードしようとするとエラーが発生してしまい、利用できなかった。そのため、本Tech TIPSではDBとの接続に「SQLite3」クラスを使用している。

●zstd拡張機能に関するエラーが生じた場合の対処方法

　SQLiteのコマンドラインコマンドで圧縮済みのDBに接続し、対話的にSELECTなどを実行しようとすると、「Parse error: unsafe use of zstd_decompress_col()」というエラーが生じることがある。その場合は、以下のSQLを実行すると解消できる。

PRAGMA trusted_schema=1;

　「PRAGMA」はSQLite特有の設定を参照または変更するためのものだ。「trusted_scheme」の詳細は、SQLite Home Pageのレファレンスを参照していただきたい。

高速化テクその3――DBを細分化して多数のDBファイルに分ける

　筆者がマネージドDBサービス（Azure Database for MySQL）で運用していたDBをSQLiteに移す際には、性能向上のためにDBを細かく分割する必要もあった。

●APIが同時に参照しない要素ごとにDBファイルを分割する

　元のAzure Database for MySQLでは、複数のチェーン店の地点情報（店舗の緯度／経度や店舗名など）を1つのDBに格納していた。しかし前述のように、App Service＋PHP＋SQLiteの組み合わせではDBファイルのサイズが大きくなるほど、どんどんクエリが遅くなる。数百あるいは数千のチェーンを単一DBにまとめたら、大幅に性能が劣化することは自明だった。

　そこで、チェーンごとにDBを分割した。つまり［chain001.db］［chain002.db］［chain003.db］……というようにDBファイルを細分化した。これにより、特に地点情報が少ない小中規模のチェーンに対するクエリが速くなった。

●APIごとにカラムを分類して別々のテーブル≒DBファイルに割り当てる

　次は、複数種類のAPIがそれぞれ必要とするカラムをまとめて別々のテーブルに格納しつつ、各テーブルを別々のDBファイルに格納するようにした。以下にその例を記す。

位置情報のみ必要とするAPI向けDB： coordinatesテーブル（地点IDと緯度、経度を格納）
各地点の詳細を必要とするAPI向けDB： pointsテーブル（地点IDと地点名、地点の詳細情報、リンク先URL、ピン画像URLなどを格納）

　この分割により、各APIがオープンするDBファイルのサイズが減り、クエリ時間はさらに短縮できた。

　この手法のデメリットは、プログラムを大幅に書き換える必要があることだろう。カラムやテーブル、DBファイルの構成が変わる他、インデックスも設計をやり直す必要があるだろう。

全ての高速化テクニックを適用した結果は？

　ここまで説明してきた全ての高速化テクニックをSQLite DBに適用したところ、下表のようにAPI応答時間を従来のMySQL DBより短縮できた。


DBMS	API応答時間
Azure Database for MySQL （2つの仮想CPUコア、16GBメモリ、最大5000 IOPS）	103msec
App Service＋PHP＋SQLite （全ての高速化テクニックを適用しない）	339msec
App Service＋PHP＋SQLite （ローカルディスク、圧縮、DB分割を適用）	91msec
従来のMySQL DBとAPIの応答時間を比較する