Какая структура индекса применяется для оптимизации запросов с оператором LIKE в SQL

Что проверяют

Понимаете ли вы, почему LIKE '%текст%' не ускоряется обычным индексом, и знаете ли, чем это лечится в конкретной СУБД. Вопрос часто вырастает из разбора медленного запроса, поэтому дальше просят рассказать, что вы увидите в плане и как убедитесь, что индекс действительно применился.

Как отвечать

Ключ в том, что B-tree хранит значения упорядоченно и умеет искать по префиксу.

• LIKE 'abc%': префикс известен, обычный индекс работает как поиск по диапазону. В PostgreSQL есть подвох: при локали, отличной от C, нужен индекс с классом операторов text_pattern_ops или varchar_pattern_ops, иначе планировщик его не возьмёт;
• LIKE '%abc': помогает функциональный индекс по reverse(col) и переписанный запрос вида reverse(col) LIKE 'cba%';
• LIKE '%abc%': префикса нет, порядок бесполезен. В PostgreSQL это расширение pg_trgm и индекс GIN с gin_trgm_ops: строка режется на триграммы, поиск идёт по ним, работает и для ILIKE, и для регулярных выражений. Паттерн короче трёх символов пользы не даст;
• полнотекстовый поиск: tsvector с GIN в PostgreSQL, MATCH ... AGAINST в MySQL. Он ищет по словам и словоформам, а не по произвольной подстроке, поэтому заменяет LIKE не всегда;
• регистр: ILIKE или UPPER(col) в условии убивают обычный индекс. Лечится индексом по тому же выражению lower(col) либо типом citext.

Закончите проверкой: EXPLAIN (ANALYZE, BUFFERS) и что вы там ищете, Seq Scan против Bitmap Index Scan, фактическое число строк против оценки. И оговоркой: когда поиск по подстроке становится основным сценарием, его выносят в поисковый движок, а не догоняют индексами в базе, которая обслуживает транзакции.

Где спотыкаются

Отвечают «нужен индекс», не уточняя шаблон. Для '%abc%' обычный индекс бесполезен, и именно это половина ожидаемого ответа.

Считают полнотекстовый индекс универсальной заменой. Поиск по части слова, по артикулу с дефисом или по фрагменту адреса он без отдельной настройки не даёт.

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