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

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

Понимаете ли вы, почему один и тот же LIKE то летает, то читает всю таблицу: всё решает положение % в шаблоне. Следом обычно спрашивают, что делать с поиском по подстроке и во что превращается findByNameContaining из Spring Data.

Как отвечать

Ответ зависит от шаблона:

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

Полнотекстовый индекс стоит назвать с оговоркой: это другой поиск. tsvector с GIN в PostgreSQL или FULLTEXT с MATCH ... AGAINST в MySQL ищут слова с учётом морфологии, а не произвольную подстроку, и LIKE не ускоряют.

Со стороны Java полезно знать, какой шаблон генерирует ORM. В Spring Data findByNameStartingWith даёт x% и может пойти по B-tree, а Containing даёт %x% и без триграмм уходит в полное сканирование. Проверяется это через EXPLAIN ANALYZE на реальном запросе.

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

Считают, что обычный индекс по колонке помогает любому LIKE. Для %abc% база всё равно читает всю таблицу, и EXPLAIN покажет Seq Scan.

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

Не экранируют % и _ в пользовательском вводе. Поиск по строке «50%» тогда находит всё, что начинается с «50», а _ совпадает с любым символом.