ホーム › ホームページを作る › プログラミング › データベース › SQLite3 (PDO) を使った全文検索 (FTS) 入門

SQLite3 (PDO) を使った全文検索 (FTS) 入門

今回作成したプログラムで使用したデータベース操作（主なSQL）について紹介します
どれも基本的なものなので、SQLiteデータベースを使いこなすには必須です

実際に使用したコマンドを例として紹介していますので、SQLコマンドを網羅的に説明するものではありませんので、合わせて下記サイト等を参照して下さい

SQLiteで使えるSQLに関しては下記サイトが参考になります
http://www.sqlite.org/lang.html

本家サイト以外では下記が見やすくまとまっています
http://www.techonthenet.com/sqlite/index.php
http://www.sqlitetutorial.net/

日本語だと少し古いですが下記サイトが参考になります
http://net-newbie.com/sqlite/lang.html

SQL

データベース作成/接続

データベースへの接続を表す PDO または Ext_PDO のインスタンスを生成します

SQLiteの接続では、データベースファイルの指定だけで、ユーザー認証情報（ユーザー名、パスワード）の指定はありません

※拡張機能  -DSQLITE_USER_AUTHENTICATION　を有効にしてSQLite３を再コンパイルすればユーザー認証機能が使えるらしいのですが、試していません

Ext_PDO

$db = new Ext_PDO( string $dsn );

ここでは Ext_PDO というPDOを継承した拡張クラスを使ってデータベースへ接続します

$dsn : 接続するSQLite3データベースファイルを絶対パスで指定

※データベースファイルが存在しない場合は空のデータベースファイルが生成されます
※ファイル拡張子は、任意の拡張子を指定可能（ .sqlite3 .db .db3 等がよく使われます）

テーブル作成

SQLite３ のデータ型で先に紹介したように、SQLite３ではカラムのデータ型を指定しなくてもテーブルを作成出来ますが、適切なデータ型やカラム制約、INDEX等を設定することで、不正なデータを制約することや高速な検索を行えるようになります

※SQLiteは手軽に扱えるのでテーブル設計に手を抜きがちですが、ここで手を抜くと後で必ず痛い目にあいますので、適切なデータ型やカラム制約、INDEX等をきちんと設定するようにすることをお勧めします

テーブルは、CREATE TABLE を使用して作成します

例として今回作成したテーブルの１つを見てみます

$sql = "CREATE TABLE plugins_base (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            slug varchar(80) UNIQUE NOT NULL,
            name varchar(200) NOT NULL,
            author_id int(11),
            version varchar(16),
            added datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            last_updated datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            requires varchar(8),
            tested varchar(8),
            downloaded int(11) DEFAULT 0,
            active_installs int(11) DEFAULT 0,
            num_ratings int(11) DEFAULT 0,
            rating float,
            icon_link varchar(200),
            homepage varchar(200),
            country varchar(20)
        );";
        $db->exec($sql);
        //INDEX 設定
        $db->exec( "CREATE INDEX author_id ON plugins_base (author_id);" );
        $db->exec( "CREATE UNIQUE INDEX slug ON plugins_base (slug);" );
        $db->exec( "CREATE INDEX japan ON plugins_base (country);" );
        $db->exec( "CREATE INDEX download ON plugins_base (downloaded,added);" );

Plugins_base というテーブルを作成して、各カラムのデータ型を指定していきます
MySQL で使っていたSQLを元にしていますので、SQLiteのデータ型 INTEGER/TEXT/BLOB/REAL/NUMERIC での指定でなく、MySQLで使っていたデータ型のままですが、問題なくテーブルを作成することが出来ます

カラム制約

PRIMARY KEY 主キーの設定
NOT NULL カラム値のNULLを許可しない
UNIQUE カラム値の重複を許可しない
CHECK カラム値の有効性を指定された式により評価
DEFAULT カラム値にデフォルト値を設定

 

INDEX設定

SELECT文に合わせてインデックスがきちんと使われるよう適切に設定しておくと高速に検索を行えるようになります

MySQL では CREATE TABLE 文内で同時に設定することが可能でしたが、SQLiteでは CREATE TABLE 文内でインデックスを設定することはできないので、CREATE INDEX 文を使って設定していきます（複合インデックスの設定も可）。

インデックスを設定するコツとしては、どのような検索を行うかをきちんと意識することです。
高速化するには、検索を如何にシンプルに行えるかが肝となります。対象となるレコード数を絞り込んで減らし、ソートせずに済ませられるようにインデックスを設定するのが理想です

例えば、１０万件のプラグインデータをダウンロード順に１０件表示したいという場合にイン
デックスを使っていなければ１０万件全てのデータを調べ、ダウンロード数順にソートして、多い順に１０件のデータを取り出す必要があります（想像しただけでも時間がかかりそうですが、実際に１０秒程度かかると思います）

これが、ダウンロード数のカラムデータにインデックスが設定してあれば、単純に言うとインデックス順に最初のデータを１０件取り出すだけでソートする必要もありませんので大変高速に処理出来ます（１０ｍｓ程度）

もう少し複雑な条件として日本製のプラグインをダウンロード順に１０件取り出す事を考えてみます

ほとんどが日本製ならダウンロード順に日本製のプラグインを取り出したほうが速いですし、仮に３００件しか日本製でないなら、日本製を判定するデータカラムにインデックスを設定して日本製のプラグインを全て取り出してからソートさせたほうが速くなります

結局、どのようなデータがどのくらい登録されているかにより正解は異なりますが、一番大切なのは、対象となるレコード数を最初にどれだけ絞れるかです。その最初の絞込対象となるカラムへインデックスを設定して、対象データを激減させることが出来れば、後はすこしばかりソートが発生してもそんなに遅くはなりません　(^^)

※データ量やハードウエアスペックも関係すると思いますが、検索時にインデックスが有効に使われていれば平均して10m程度で実行されることが期待できます

 

FTSテーブル

ここまでは、通常のテーブルに関してでしたが、SQLiteで 全文検索（FTS）を行うには、別途、CREATE VIRTUAL TABLE を使用して専用のFTSテーブルを生成する必要があります

また、FTSテーブルでは指定されたカラムのデータ型は無視されて TEXT型として扱われます（DOCID列を除く）

FTSテーブルの検索はテーブル全体か指定したカラムを対象に行うことが出来ます

参照 1.2. Creating and Destroying FTS Tables

※FTSテーブルはバージョンにより fts3, fts4 とありますが、通常は fts4 を使うことが推奨されています。また最新ぼSQLite3 Ver3.9 以降では 更に fts5 が使えるようになったようですが、今回は Ver3.8 なので fts4 を使用しています

実際にデータベースを活用する場合には、TEXT以外のデータも扱うことが多いので、通常のテーブルとFTSテーブルを組み合わせて利用することになります

トークナイザー

全文検索するにあたり、文章からトークンを抽出することが必要となります。この文章を解析してトークンを抽出して全文検索用インデックスに変換するモジュールをトークナイザと言い、CREATE VIRTUAL TABLE 文内で指定することが出来ます

ここでは標準でサポートされている英文解析用のトークナイザー simple と porter を紹介します

  • 対象文字は、すべての英数字と、Unicodeコードポイント128以上の値の文字
  • それ以外は破棄
  • ASCIIの範囲内のすべての大文字が小文字に変換されます（大文字と小文字を区別しない）
simple トークンの検索をより厳密一致する条件で行う場合(デフォルト）

シンプルにスペース区切りを一つのトークンとして扱います

例.　”Right now, they’re very frustrated.”　だと　”right now they re very frustrated” のように変換されてインデックス化される
この場合 “MATCH ‘Frustrated'” のように検索してもマッチします
porter トークンの検索をより柔軟に広く一致する条件で行う場合

simpleと同様にトークンを小文字に変換して抽出しますが、ポーターステミングアルゴリズムを使用して単語のゆらぎ（名詞、形容詞、動詞、複数形、過去形等）である接尾語を取り除きより幅広い検索が出来るようトークンを抽出します

例.　”Right now, they’re very frustrated.”　だと　”right now thei veri frustrat”　のように変換され、語尾の変化を除去した上 でインデックス化されます
この場合 “MATCH ‘Frustrated'” だけでなく “MATCH ‘Frustration'” で検索してもマッチします

※SQLiteのコンパイルオプションを指定して再コンパイルすると unicode61 というトークナイザも使えるようになります。詳細は不明ですがUnicodeのスペースや句読点文字でもワードを抽出することができるそうなので、プログ ラムのソースコードのような検索には向いているかもしれません

日本語トークンの抽出

残念ながら標準では日本語を解析して抽出する機能には対応していません

日本語の文章から全文検索するには別途、文章を日本語形態要素解析（MEcab等が有名）して、スペース区切りで全文をトークンに区切りるか、文章をN-gram変換等で単純に規則的に分割する等の方法があります

※位置情報のような既に都道府県や市町村に分類整理されているデータならそれを半角スペースで区切り登録するだけで問題なく検索できます

例えば、Japan　Tokyo　Itabashi 等の位置情報のような既に分類出来ているデータなら半角スペースで区切りそのまま登録、あるいは、更に 日本 東京 東京都 板橋 板橋区 にっぽん ニッポン NIPPON とうきょうと トウキョウ いたばし イタバシ 等を合わせて登録しておけば柔軟に住所を検索できます

工夫次第で、FTSを柔軟な複合インデックス的な感じにも使えるので、様々な使いみちがあると思います

INDEX設定

FTSテーブルは、カラムに登録されるテキスト自体をトークン毎にB-Tree管理して高速検索出来るようにしているインデックスの塊のようなものなので、通常のテーブルのようにINDEX を設定することは出来ません

全文検索は高速に行われるように設計されていますが、通常のINDEXを使った検索よりは時間はかかります。データ量、検索条件、ハードスペックにより異なりますが、ストレスなく使うには、100ms 程度での検索を目標に設計するのがよろしいと思われます

サンプル
今回作成したWordPressプラグイン情報の検索用テーブルを紹介します

データベースは、通常のテーブルでプラグインデータ、作者データの２つを作り、別にFTS検索用のテーブルを１つ作成します（各テーブル名は ‘plugins_base’, ‘plugins_author’, ‘plugins_fts’ ）

FTSテーブルでは、INSERT/UPDATE する時に、単純に プラグイン名やタグ、作者情報等をワード毎に半角スペースで区切りつつ登録しています

テーブル作成している実際のコードを元に説明します
クラスを使っているので $this->db が Ext_PDO のオブジェクトインスタンスです

    public function table_create() {
        $this->db->beginTransaction();
        try {
            //プラグインテーブル
            $sql = "CREATE TABLE $this->plugins_table (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                slug varchar(80) UNIQUE NOT NULL,
                name varchar(200) NOT NULL,
                author_id int(11),
                version varchar(16),
                added datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
                last_updated datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
                requires varchar(8),
                tested varchar(8),
                downloaded int(11) DEFAULT 0,
                active_installs int(11) DEFAULT 0,
                num_ratings int(11) DEFAULT 0,
                rating float,
                icon_link varchar(200),
                homepage varchar(200),
                country varchar(20)
                );";
            $this->db->exec($sql);
            //INDEX 設定
            $this->db->exec( "CREATE INDEX author_id ON $this->plugins_table (author_id);" );
            $this->db->exec( "CREATE UNIQUE INDEX slug ON $this->plugins_table (slug);" );
            $this->db->exec( "CREATE INDEX japan ON $this->plugins_table (country);" );
            $this->db->exec( "CREATE INDEX installs ON $this->plugins_table (active_installs);" );
            $this->db->exec( "CREATE INDEX review ON $this->plugins_table (num_ratings);" );
            $this->db->exec( "CREATE INDEX download ON $this->plugins_table (downloaded,added);" );

            //著者テーブル
            $sql = "CREATE TABLE $this->authors_table (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                profile varchar(100) UNIQUE NOT NULL,
                author varchar(200),
                location varchar(32),
                updated datetime DEFAULT '0000-00-00 00:00:00' NOT NULL
                );";
            $this->db->exec($sql);
            //INDEX 設定
            $this->db->exec( "CREATE UNIQUE INDEX profile ON $this->authors_table (profile);");

            //全文検索用テーブル
            //INSERT/UPDATE 時は plugins_base.id を docid という識別値として明示的に指定することで１対１に関連付け出来る
            //names, tags, authors にはスペース区切りのワードを検索対象として登録する
            //例えば INSERT INTO plugins_fts (docid, names, tags, authors) VALUES(1, 'hoge gallery', 'gallery image widget', 'autho1 author2 author3');　
            $sql = "CREATE VIRTUAL TABLE $this->fts_table USING fts4(
                names text DEFAULT '' NOT NULL,
                tags text DEFAULT '' NOT NULL,
                authors text DEFAULT '' NOT NULL,
                tokenize=porter );";
            $this->db->exec($sql);
            
        } catch (PDOException $e) {
            $this->db->rollback();
            return;
        }
        $this->db->commit();
    }

 

今回作成したテーブルのポイントまとめると以下の様になります

テーブルを分割して結合/サブクエリーを活用 プラグインデータ、作者データ、FTS用データの３つのテーブルを作成

  • プラグインデータの id とFTSデータに暗黙に存在する docid を１対１に関連付け
  • 作者データの id とプラグインデータの author_id を 1対多 の関連付け

この関連付けにより各テーブルを結合したりサブクエリーを使って操作することが出来ます
不正データ対策
  • slug や profile 等の唯一のデータに対して UNIQUE 制約を付ける
  • データの登録、更新、削除時にトランザクションを使用する
高速化対策
  • 高速に検索したいカラムデータ（ダウンロード数、登録日時や国情報等）に適切なデータ型とINDEXを設定
  • FTSに対してはデータ内容（プラグイン名、タグ名、作者等）毎にカラムを分けて、検索対象を絞り込めるようにする
FTS検索の正確性 正確性は、使用するトークナイザーにより変わってきます 標準では simple と porter の２つしか利用できませんが、今回は検索語に対してより幅広くマッチするように porter を設定しています

 

その他

PRAGMAコマンドについて

auto_vacuum

SQLiteは、データの追加、削除を行っていくと、追加時に削除したデータエリアが再利用されるわけではないので、データベースファイルがだんだん大きくなっていきます
データベースファイルが大きくなり無駄な領域が増えてくるとパフォーマンス的にも影響してくるので、VACUUM という独自コマンドでデータベースファイルの無駄な領域を開放してデータベースファイルをコンパクトにする機能があります

その vacuum をトランザクションのコミット時に自動的に行わせる設定が auto_vacuum です

設定自体は簡単で PRAGMA auto_vacuum コマンドをSQL文の代わりに発行するだけです
PDOの exec メソッドを使い下記のように実行すればOKです

$db->exec("PRAGMA auto_vacuum = 1");

※auto_vacuum の設定はテーブル作成前に行う必要があり、後から設定することは出来ません

 

テーブルの確認

プログラムを作成するとテーブルが存在しているかどうか確認したい場合があります
そんな場合は、データベースファイル内部に sqlite_master というデータベースのテーブル定義情報が保持されているので sqlite_master に指定したテーブル名が存在するかSQL文を実行して確認することが可能です

よく使うので Ext_PDO で is_table_exist($table) というメソッドを定義してあります

public function is_table_exist($table) {
    //sqlite_master から作成済みのテーブル構造を取得
    $res = $this->sql_get_row("SELECT * FROM sqlite_master WHERE type = 'table' AND name = '$table'");
    if($res === false)
        return false;
    return true;
}

How do I list all tables/indices contained in an SQLite databas

次は、データ追加/変更/削除についてです　(^^)

データベース, , , , ,

まとめ記事紹介

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

go-to-top