InnoDB индексы полнотекстового поиска имеют обратный индекс. Обратные индексы хранят список слов и для каждого слова список документов, в которых оно встречается. Для поддержки поиска по близости также хранится информация о позиции каждого слова как смещение байтов.

При создании InnoDB индекса полнотекстового поиска создается набор таблиц индекса, как показано в следующем примере:

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

Первые шесть таблиц индекса составляют обратный индекс и называются вспомогательными таблицами индекса. При разбиении входных документов на токены отдельные слова (также называемые “токенами”) вставляются в таблицы индекса вместе с информацией о позиции и связанным DOC_ID. Слова полностью сортируются и распределяются между шестью таблицами индекса на основе веса сортировки набора символов первой буквы слова.

Обратный индекс разделен на шесть вспомогательных таблиц индекса для поддержки одновременного создания индекса. По умолчанию два потока разбивают на токены, сортируют и вставляют слова и связанные данные в таблицы индекса. Количество потоков, выполняющих эту работу, можно настроить с помощью переменной innodb_ft_sort_pll_degree. При создании индексов полнотекстового поиска больших таблиц рекомендуется увеличить количество потоков.

Имена вспомогательных таблиц индекса начинаются с fts_ и заканчиваются index_#. Каждая вспомогательная таблица индекса связана с таблицей, для которой индекс создан, шестнадцатеричным значением в имени вспомогательной таблицы индекса, соответствующим table_id индексируемой таблицы. Например, table_id таблицы test/opening_lines равняется 327, шестнадцатеричное значение которого равно 0x147. Как показано в приведенном выше примере, шестнадцатеричное значение “147” появляется в именах вспомогательных таблиц индекса, связанных с таблицей test/opening_lines.

Шестнадцатеричное значение, представляющее index_id индекса полнотекстового поиска, также появляется в именах вспомогательных таблиц индекса. Например, в имени вспомогательной таблицы test/fts_0000000000000147_00000000000001c9_index_1 шестнадцатеричное значение 1c9 имеет десятичное значение 457. Индекс, определённый для таблицы opening_lines (idx), можно определить, запросив значение в таблице Информационной схемы 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 |
+----------+------+----------+-------+

Если основная таблица создана в табличном пространстве, таблицы индексов хранятся в своём собственном табличном пространстве. В противном случае таблицы индексов хранятся в табличном пространстве, где расположена индексируемая таблица.

Другие таблицы индексов, показанные в предыдущем примере, называются общими таблицами индексов и используются для обработки удаления и хранения внутреннего состояния индексов полнотекстового поиска. В отличие от таблиц обратного индекса, которые создаются для каждого индекса полнотекстового поиска, этот набор таблиц является общим для всех индексов полнотекстового поиска, созданных для определённой таблицы.

Общие таблицы индексов сохраняются даже если индексы полнотекстового поиска удалены. При удалении индекса полнотекстового поиска столбец FTS_DOC_ID, созданный для индекса, сохраняется, так как удаление столбца FTS_DOC_ID потребует перестройки ранее индексированной таблицы. Общие таблицы индекса необходимы для управления столбцом FTS_DOC_ID.

При добавлении документа он разбивается на токены, а отдельные слова и связанные данные вставляются в индекс полнотекстового поиска. Этот процесс, даже для небольших документов, может привести к множеству небольших вставок в вспомогательные таблицы индекса, что может привести к проблеме одновременного доступа к этим таблицам. Для решения этой проблемы InnoDB использует кэш индекса полнотекстового поиска для временного кеширования вставок в таблицы индекса для недавно вставленных строк. Эта структура кэша в оперативной памяти хранит вставки до тех пор, пока кэш не заполнится, а затем выполняет пакетные операции записи на диск (в вспомогательные таблицы индекса). Вы можете запросить таблицу Информационной схемы INNODB_FT_INDEX_CACHE для просмотра токенизированных данных для недавно вставленных строк.

Поведение кэширования и пакетной записи на диск предотвращает частые обновления вспомогательных таблиц индекса, что может привести к проблемам одновременного доступа во время интенсивных операций вставки и обновления. Метод пакетирования также предотвращает несколько вставок одного и того же слова и сводит к минимуму дублирование записей. Вместо записи каждого слова по отдельности, вставки одного и того же слова объединяются и записываются на диск как одна запись, повышая эффективность вставки и сохраняя вспомогательные таблицы индекса максимально компактными.

Переменная innodb_ft_cache_size используется для настройки размера кэша индекса полнотекстового поиска (для каждой таблицы), что влияет на частоту сброса кэша индекса полнотекстового поиска. Вы также можете определить глобальный лимит размера кэша индекса полнотекстового поиска для всех таблиц в данном экземпляре с помощью переменной innodb_ft_total_cache_size.

Кэш индекса полнотекстового поиска хранит ту же информацию, что и вспомогательные таблицы индекса. Однако кэш индекса полнотекстового поиска кеширует только токенизированные данные для недавно вставленных строк. Данные, которые уже записаны на диск (во вспомогательные таблицы индекса), не возвращаются в кэш индекса полнотекстового поиска при запросе. Данные из вспомогательных таблиц индекса запрашиваются напрямую, а результаты из вспомогательных таблиц индекса объединяются с результатами из кэша индекса полнотекстового поиска перед возвращением.

InnoDB использует уникальный идентификатор документа, называемый 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 возвращается предупреждение, в котором сообщается, что InnoDB перестраивает таблицу для добавления столбца FTS_DOC_ID.

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 без предупреждения.

Определение столбца FTS_DOC_ID во время CREATE TABLE требует меньших затрат, чем создание индекса полнотекстового поиска для таблицы, которая уже загружена данными. Если столбец FTS_DOC_ID определен в таблице до загрузки данных, для добавления нового столбца не требуется перестройка таблицы и ее индексов. Если вы не беспокоитесь о производительности CREATE FULLTEXT INDEX, оставьте столбец FTS_DOC_ID, чтобы InnoDB создал его за вас. 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_INDEX (все заглавные буквы) по столбцу FTS_DOC_ID.

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 сохраняется при удалении индекса полнотекстового поиска.

Удаление записи, имеющей столбец индекса полнотекстового поиска, может привести к множеству небольших удалений в вспомогательных таблицах индексов, что сделает одновременный доступ к этим таблицам проблемой. Чтобы избежать этой проблемы, DOC_ID удалённого документа записывается в специальную таблицу FTS_*_DELETED всякий раз, когда запись удаляется из индексируемой таблицы, а индексируемая запись сохраняется в индексе полнотекстового поиска. Перед возвратом результатов запроса информация в таблице FTS_*_DELETED используется для фильтрации удалённых DOC_ID. Преимущество этого дизайна заключается в том, что удаления быстрые и недорогие. Недостатком является то, что размер индекса не уменьшается немедленно после удаления записей. Для удаления записей о полнотекстовых индексах удаленных записей выполните OPTIMIZE TABLE на индексируемой таблице с innodb_optimize_fulltext_only=ON, чтобы перестроить индекс полнотекстового поиска. Более подробную информацию см. в Оптимизация индексов полнотекстового поиска InnoDB.

Индексы полнотекстового поиска InnoDB имеют особые характеристики обработки транзакций из-за кэширования и пакетной обработки. В частности, обновления и вставки в индекс полнотекстового поиска обрабатываются во время завершения транзакции, что означает, что полнотекстовый поиск может видеть только данные, которые были подтверждены. Следующий пример демонстрирует это поведение. Полнотекстовый поиск возвращает результат только после подтверждения вставленных строк.

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

Вы можете отслеживать и анализировать аспекты обработки текста в индексах полнотекстового поиска InnoDB, обращаясь к следующим таблицам INFORMATION_SCHEMA:

Вы также можете просмотреть основную информацию об индексах полнотекстового поиска и таблицах, обратившись к INNODB_INDEXES и INNODB_TABLES.

Дополнительную информацию см. в разделе 17.15.4, «Таблицы InnoDB INFORMATION_SCHEMA FULLTEXT Index».