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

То же предупреждение возвращается при добавлении индекса полнотекстового поиска к таблице, не имеющей столбца FTS_DOC_ID с помощью ALTER TABLE. Если вы создаете индекс полнотекстового поиска во время 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».