14.2.13.3 FULLTEXT インデックス

テキストベースのカラム (CHAR、VARCHAR、または TEXT カラム) 上に作成されたインデックスのタイプです。これを使用すると、ストップワードとして定義されている任意の単語が省略されることで、これらのカラム内に含まれるデータ上での InnoDB のクエリーおよび DML 操作の速度を上げる際に役立ちます。

FULLTEXT インデックスは、CREATE TABLE ステートメントの一部として定義することも、あとで ALTER TABLE または CREATE INDEX を使用して追加することもできます。

全文検索は、MATCH() ... AGAINST 構文を使用して実行されます。使用法については、セクション12.9「全文検索関数」を参照してください。

全文インデックスの設計

InnoDB の FULLTEXT インデックスでは、「転置インデックス」の設計が使用されています。転置インデックスには、単語のリスト、および単語ごとに、その単語が出現するドキュメントのリストが格納されます。近接検索をサポートするために、単語ごとの位置情報もバイトオフセットとして格納されます。

全文インデックステーブル

次の例に示すように、InnoDB の FULLTEXT インデックスごとに、インデックステーブルのセットが作成されます。

CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200),
FULLTEXT idx (opening_line)
) ENGINE=InnoDB;

mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE name LIKE 'test/%';
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|      333 | test/FTS_0000000000000147_00000000000001c9_INDEX_1 |   289 |
|      334 | test/FTS_0000000000000147_00000000000001c9_INDEX_2 |   290 |
|      335 | test/FTS_0000000000000147_00000000000001c9_INDEX_3 |   291 |
|      336 | test/FTS_0000000000000147_00000000000001c9_INDEX_4 |   292 |
|      337 | test/FTS_0000000000000147_00000000000001c9_INDEX_5 |   293 |
|      338 | test/FTS_0000000000000147_00000000000001c9_INDEX_6 |   294 |
|      330 | test/FTS_0000000000000147_BEING_DELETED            |   286 |
|      331 | test/FTS_0000000000000147_BEING_DELETED_CACHE      |   287 |
|      332 | test/FTS_0000000000000147_CONFIG                   |   288 |
|      328 | test/FTS_0000000000000147_DELETED                  |   284 |
|      329 | test/FTS_0000000000000147_DELETED_CACHE            |   285 |
|      327 | test/opening_lines                                 |   283 |
+----------+----------------------------------------------------+-------+
12 rows in set (0.02 sec)

最初の 6 つのテーブルは転置インデックスを表し、「近接検索インデックステーブル」と呼ばれます。受信ドキュメントがトークン化されると、各単語が位置情報およびドキュメント ID (DOC_ID) とともに、インデックステーブルに挿入されます。単語は完全にソートされてから、単語の最初の文字の文字セット重みに基づいて、6 つのインデックステーブル間でパーティション化されます。

転置インデックスは、インデックスの並列作成をサポートするために、6 つの補助インデックステーブルに「パーティション化」されます。デフォルトでは、2 つのスレッドを使用して、単語および関連するデータのトークン化、ソート、およびインデックステーブルへの挿入が実行されます。スレッドの数は、innodb_ft_sort_pll_degree オプションを使用することで構成可能です。大きなテーブル上に FULLTEXT インデックスを作成する際には、スレッドの数を多くすることを検討してください。

補助インデックステーブル名の前には FTS_、後ろには INDEX_* が付けられます。各インデックステーブルは、インデックス付きのテーブルの table_id と一致するインデックステーブル名に含まれる 16 進値によって、インデックス付きのテーブルに関連付けられます。たとえば、test/opening_lines テーブルの table_id は 327 (16 進値は 0x147) です。前述の例で示したように、16 進値の 「147」 は、test/opening_lines テーブルに関連付けられたインデックステーブルの名前に表示されます。

補助インデックス名に表示されるもう 1 つの 16 進値は、FULLTEXT インデックスの index_id です。たとえば、補助テーブル名 test/FTS_0000000000000147_00000000000001c9_INDEX_1 では、16 進値 1c9 の 10 進値は 457 です。opening_lines テーブルで定義されたインデックス (idx) は、INFORMATION_SCHEMA.INNODB_SYS_INDEXES テーブルでこの値 (457) に対してクエリーを実行することで識別できます。

mysql> SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE index_id=457;
+----------+------+----------+-------+
| index_id | name | table_id | space |
+----------+------+----------+-------+
|      457 | idx  |      327 |   283 |
+----------+------+----------+-------+
1 row in set (0.00 sec)

innodb_file_per_table を有効にすると、インデックステーブルが独自のテーブルスペースに格納されます。innodb_file_per_table を無効にすると、インデックステーブルが InnoDB のシステムテーブルスペース (スペース 0) に格納されます。

注記

MySQL 5.6.5 で導入されたバグが原因で、innodb_file_per_table を有効にしても、インデックステーブルは InnoDB のシステムテーブルスペース (スペース 0) に作成されます。このバグは、MySQL 5.6.20 および MySQL 5.7.5 で修正されました (Bug#18635485)。

前述の例で示したその他のインデックステーブルは、FULLTEXT インデックスの削除処理および内部状態の格納で使用されます。

• FTS_*_DELETED および FTS_*_DELETED_CACHE: 削除されるが、データはまだ全文インデックスから削除されないドキュメントのドキュメント ID (DOC_ID) が含まれます。FTS_*_DELETED_CACHE は、FTS_*_DELETED テーブルのインメモリーバージョンです。

• FTS_*_BEING_DELETED および FTS_*_BEING_DELETED_CACHE: 削除され、現在データが全文インデックスから削除中であるドキュメントのドキュメント ID (DOC_ID) が含まれます。FTS_*_BEING_DELETED_CACHE テーブルは、FTS_*_BEING_DELETED テーブルのインメモリーバージョンです。

• FTS_*_CONFIG: FULLTEXT インデックスの内部状態に関する情報が格納されます。もっとも重要な点は、解析され、ディスクにフラッシュされたドキュメントを識別する FTS_SYNCED_DOC_ID が格納されることです。クラッシュリカバリの場合、ドキュメントを再解析し、FULLTEXT インデックスキャッシュに追加し直すことができるように、ディスクにフラッシュされていないドキュメントを識別する際に、FTS_SYNCED_DOC_ID 値が使用されます。このテーブル内のデータを表示するには、INFORMATION_SCHEMA.INNODB_FT_CONFIG テーブルでクエリーを実行します。

全文インデックスキャッシュ

ドキュメントが挿入されると、トークン化され、各単語および関連付けられたデータが FULLTEXT インデックスに挿入されます。このプロセスが実行されると、小さなドキュメントの場合でも、補助インデックステーブルへの多数の小規模な挿入が発生します。これにより、競合の発生時に、これらのテーブルへの並列アクセスが発生する可能性があります。この問題を回避するために、InnoDB では、最近挿入された行に対するインデックステーブルの挿入を一時的にキャッシュに入れるために、FULLTEXT インデックスキャッシュが使用されます。この「インメモリー」キャッシュの構造では、キャッシュがいっぱいになるまで挿入が保持され、そのあと、ディスク (補助インデックステーブル) にバッチフラッシュされます。INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE テーブルでクエリーを実行すると、最近挿入された行のトークン化されたデータを表示できます。

キャッシュおよびバッチフラッシュの動作によって、補助インデックステーブルへの頻繁な更新が回避されますが、負荷の高い挿入時および更新時に並列アクセスの問題が発生する可能性があります。また、バッチ技術を使用すると、同じ単語への挿入が複数回発生することも回避され、重複エントリも最小限になります。各単語を個別にフラッシュする代わりに、同じ単語の挿入がマージされ、単一のエントリとしてディスクにフラッシュされるため、補助インデックステーブルのサイズをできるかぎり小さく保ちながら、挿入の効率性が改善されます。

全文インデックスキャッシュのサイズを (テーブルごとに) 構成するには、innodb_ft_cache_size 変数が使用されます。これにより、全文インデックスキャッシュがフラッシュされる頻度が影響を受けます。特定のインスタンスで innodb_ft_total_cache_size オプションを使用すれば、すべてのテーブルに対応したグローバルな全文インデックスキャッシュのサイズ制限を定義することもできます。

全文インデックスキャッシュには、補助インデックステーブルと同じ情報が格納されます。ただし、全文インデックスキャッシュでは、最近挿入された行のトークン化されたデータのみがキャッシュに入れられます。すでにディスク (全文補助テーブル) にフラッシュされているデータは、クエリー時に全文インデックスキャッシュに戻りません。補助インデックステーブル内のデータは、直接クエリーが実行されます。補助インデックステーブルからの結果は、全文インデックスキャッシュからの結果とマージされてから返されます。

InnoDB の全文ドキュメント ID および FTS_DOC_ID カラム

InnoDB では、全文インデックス内の単語をその単語が出現するドキュメントレコードとマップする際に、ドキュメント ID (DOC_ID) と呼ばれる一意のドキュメント識別子が使用されます。このマッピングには、インデックス付きテーブル上の FTS_DOC_ID カラムが必要です。FTS_DOC_ID カラムが定義されていない場合は、全文インデックスの作成時に、InnoDB によって自動的に非表示の FTS_DOC_ID カラムが追加されます。次の例で、この動作を実演します。

次のテーブル定義には、FTS_DOC_ID カラムが含まれていません。

CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200)
) ENGINE=InnoDB;

CREATE FULLTEXT INDEX 構文を使用して、テーブル上に全文インデックスを作成すると、FTS_DOC_ID カラムが追加されるように InnoDB がテーブルを再構築してしていることをレポートする警告が返されます。

mysql> CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);
Query OK, 0 rows affected, 1 warning (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

ALTER TABLE を使用して、FTS_DOC_ID カラムが存在しないテーブルに全文インデックスを追加するときにも、同じ警告が返されます。CREATE TABLE の実行時に全文インデックスを作成する場合に、FTS_DOC_ID カラムを定義しないと、InnoDB によって警告なしで、非表示の FTS_DOC_ID カラムが追加されます。

CREATE TABLE の実行時に FTS_DOC_ID カラムを定義するには、すでにデータがロードされているテーブル上に全文インデックスを作成する必要があります。データをロードする前に、テーブル上に FTS_DOC_ID カラムが定義されている場合は、新しいカラムが追加されるようにテーブルおよびそのインデックスを再構築する必要がありません。CREATE FULLTEXT INDEX のパフォーマンスに関心がない場合は、InnoDB で自動的に作成されるように、FTS_DOC_ID カラムを除外します。InnoDB によって、FTS_DOC_ID_INDEX という名前の FTS_DOC_ID カラム上に、一意のインデックスとともに非表示の FTS_DOC_ID が作成されます。独自の FTS_DOC_ID カラムを作成する場合は、次の例で示すように、カラムを BIGINT UNSIGNED NOT NULL として定義し、FTS_DOC_ID (すべて大文字) という名前を付けます。

注記

AUTO_INCREMENT として FTS_DOC_ID カラムを定義する必要がありませんが、AUTO_INCREMENT を使用した方が簡単にデータをロードできます。

CREATE TABLE opening_lines (
FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200)
) ENGINE=InnoDB;

FTS_DOC_ID カラムをユーザー自身で定義するように決定した場合は、空の値や重複する値が回避されるようにカラムを管理することがユーザーの責任となります。FTS_DOC_ID 値は再使用できません。つまり、FTS_DOC_ID 値は増加し続けます。

オプションで、FTS_DOC_ID カラム上に必要な一意の FTS_DOC_ID_INDEX (すべて大文字) を作成することもできます。

CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID);

FTS_DOC_ID_INDEX を作成しない場合は、InnoDB によって自動的に作成されます。

InnoDB による全文インデックスの削除処理

全文インデックスカラムが含まれるレコードを削除すると、補助インデックステーブルへの多数の小規模な削除が発生します。これにより、競合の発生時に、これらのテーブルへの並列アクセスが発生する可能性があります。この問題を回避するために、インデックス付きのテーブルからレコードが削除されるたびに、削除されたドキュメントのドキュメント ID (DOC_ID) が特別な 「DELETED」 テーブルに記録され、インデックス付きのレコードが全文インデックスに残ります。クエリーの結果が返される前に、「DELETED」 テーブル内の情報を使用して、削除されたドキュメント ID が取り除かれます。この設計の利点は、削除が高速で、低負荷であることです。欠点は、レコードの削除後に、すぐにインデックスのサイズが削減されないことです。削除したエントリの全文インデックスエントリを削除するには、innodb_optimize_fulltext_only=ON を使用してインデックス付きのテーブル上で OPTIMIZE TABLE を実行して、全文インデックスを再構築します。詳細は、InnoDB 全文インデックスの最適化を参照してください。

InnoDB による全文インデックスのトランザクション処理

InnoDB の FULLTEXT インデックスには、そのキャッシュおよびバッチ処理の動作のために、特別なトランザクション処理の特性が備わっています。特に、FULLTEXT インデックス上の更新および挿入は、トランザクションのコミット時に処理されます。つまり、FULLTEXT 検索では、コミットされたデータのみを表示できます。次の例で、この動作を実演します。FULLTEXT 検索では、挿入された行がコミットされたあとにはじめて、結果が返されます。

mysql> CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200),
FULLTEXT idx (opening_line)
) ENGINE=InnoDB;

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES
('Call me Ishmael.','Herman Melville','Moby-Dick'),
('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'),
('I am an invisible man.','Ralph Ellison','Invisible Man'),
('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
('It was love at first sight.','Joseph Heller','Catch-22'),
('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

InnoDB による全文インデックスのモニター

次の INFORMATION_SCHEMA テーブルでクエリーを実行すると、InnoDB の FULLTEXT インデックスの特別なテキスト処理の側面をモニターおよび調査できます。

• INNODB_FT_CONFIG

• INNODB_FT_INDEX_TABLE

• INNODB_FT_INDEX_CACHE

• INNODB_FT_DEFAULT_STOPWORD

• INNODB_FT_DELETED

• INNODB_FT_BEING_DELETED

INNODB_SYS_INDEXES および INNODB_SYS_TABLES でクエリーを実行すると、FULLTEXT インデックスおよびテーブルに関する基本情報を表示することもできます。

これらのテーブルについての詳細は、INFORMATION_SCHEMA のドキュメントを参照してください。