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

InnoDB 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> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_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 |
+----------+----------------------------------------------------+-------+

最初の 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 テーブルに関連付けられたインデックステーブルの名前に表示されます。

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

mysql> SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_INDEXES
       WHERE index_id=457;
+----------+------+----------+-------+
| index_id | name | table_id | space |
+----------+------+----------+-------+
|      457 | idx  |      327 |   283 |
+----------+------+----------+-------+

プライマリテーブルが file-per-table テーブルスペースに作成される場合、インデックステーブルは独自のテーブルスペースに格納されます。

前述の例に示されているその他のインデックステーブルは、共通インデックステーブルと呼ばれ、FULLTEXT インデックスの削除処理および内部状態の格納に使用されます。 全文インデックスごとに作成される逆インデックステーブルとは異なり、このテーブルのセットは、特定のテーブルに作成されるすべての全文インデックスに共通です。

全文インデックスが削除されても、共通の補助テーブルは保持されます。 全文インデックスを削除すると、FTS_DOC_ID カラムの削除にはテーブルの再構築が必要になるため、インデックスに対して作成された FTS_DOC_ID カラムは保持されます。 FTS_DOC_ID カラムを管理するには、共通の軸テーブルが必要です。

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

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

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

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

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

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

mysql> 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 |
+---------+------+--------------------------------------------------+

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 カラムに一意インデックス (FTS_DOC_ID_INDEX) とともに非表示の FTS_DOC_ID カラムが作成されます。 独自の FTS_DOC_ID カラムを作成する場合は、次の例のように、カラムを BIGINT UNSIGNED NOT NULL として定義し、FTS_DOC_ID (すべて大文字) という名前を付ける必要があります:

mysql> CREATE TABLE opening_lines (
       FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       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 (すべて大文字) を作成できます。

mysql> CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID);

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

最大使用 FTS_DOC_ID 値と新しい FTS_DOC_ID 値の間の許容ギャップは 65535 です。

テーブルの再構築を回避するために、全文インデックスの削除時に FTS_DOC_ID カラムが保持されます。

全文インデックスカラムを含むレコードを削除すると、補助インデックステーブルで多数の小さな削除が発生し、これらのテーブルへの同時アクセスが競合ポイントになる可能性があります。 この問題を回避するために、削除されたドキュメントのドキュメント ID (DOC_ID) は、インデックス付きテーブルからレコードが削除されるたびに特別な FTS_*_DELETED テーブルに記録され、インデックス付きレコードは全文インデックスに残ります。 クエリー結果を返す前に、FTS_*_DELETED テーブルの情報を使用して、削除されたドキュメント ID を除外します。 この設計の利点は、削除が高速で、低負荷であることです。 欠点は、レコードの削除後に、すぐにインデックスのサイズが削減されないことです。 削除されたレコードの全文インデックスエントリを削除するには、innodb_optimize_fulltext_only=ON を使用してインデックス付けされたテーブルで OPTIMIZE TABLE を実行し、全文インデックスを再構築します。 詳細は、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;

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');

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

mysql> COMMIT;

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

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

INNODB_INDEXES および INNODB_TABLES をクエリーして、FULLTEXT のインデックスおよびテーブルの基本情報を表示することもできます。

詳細は、セクション15.15.4「InnoDB INFORMATION_SCHEMA FULLTEXT インデックステーブル」を参照してください。