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

Первые шесть таблиц индексов составляют обратный индекс и называются вспомогательными таблицами индексов. При разборе поступающих документов отдельные слова (также называемые «“токенами”») вставляются в таблицы индексов вместе с позиционной информацией и соответствующим 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_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 |
+----------+------+----------+-------+

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

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

Общие таблицы индексов сохраняются даже при удалении индексов полнотекстового поиска. При удалении индекса полнотекстового поиска столбец 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 создает его автоматически.

До MySQL 5.7.13 разрешенная разница между наибольшим использованным значением FTS_DOC_ID и новым значением FTS_DOC_ID составляла 10000. В MySQL 5.7.13 и более поздних версиях разрешенная разница составляет 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_SYS_INDEXES и INNODB_SYS_TABLES.

Более подробная информация в Раздел 14.16.4, «Таблицы InnoDB INFORMATION_SCHEMA FULLTEXT индексов».