Оптимизация запросов MySQL LIKE '%string%' в InnoDB

Многие приложения требуют поиска строки внутри текста. MySQL предлагает оператор LIKE для выполнения такого поиска, но как мы можем оптимизировать эти запросы для достижения оптимальной производительности?

Проблема

Рассмотрим следующую таблицу:

CREATE TABLE `example` (
`id` int(11) unsigned NOT NULL auto_increment,
`keywords` varchar(200) NOT NULL,
PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

При выполнении запроса типа:

SELECT id FROM example WHERE keywords LIKE '%whatever%'

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

ALTER TABLE `example` ADD INDEX `idxSearch` (`keywords`);

запрос EXPLAIN покажет, что MySQL по-прежнему необходимо сканировать всю таблицу.

Решение

MySQL использует индексы, сопоставляя начало строки поиска. Для таких запросов, как LIKE 'whatever%', можно использовать индекс, поскольку 'whatever' привязывается к началу строки.

Однако запросы типа LIKE '%whatever%' не имеют такой привязки. Поисковый запрос появляется «плавающим» внутри строки, заставляя MySQL сканировать все поле.

Чтобы оптимизировать такие запросы, мы можем использовать полнотекстовые индексы. Эти индексы специально разработаны для «плавающего» поиска. InnoDB поддерживает полнотекстовые индексы начиная с версии 5.6.4, что делает его подходящим вариантом для оптимизации запросов LIKE '%string%'.