С какими структурами индексов приходилось работать?

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

Понимаете ли вы, что индекс это структура данных со своими ограничениями, а не галочка «сделать быстрее». Для бэкендера на Java важна вторая половина ответа: умеете ли вы прочитать план запроса и связать его с тем, что нагенерировал ORM.

Как отвечать

Пройдитесь по типам и сразу привязывайте их к задачам.

• B-дерево: умолчание в реляционных базах. Годится для равенства, диапазонов, сортировки и поиска по префиксу составного ключа;
• хэш-индекс: только равенство, ни диапазона, ни сортировки;
• инвертированный индекс, в PostgreSQL это GIN: поиск внутри jsonb, по массивам и полнотекстовый;
• BRIN: очень компактный вариант для огромных таблиц, где физический порядок строк совпадает с порядком значений, например по времени вставки;
• частичный индекс с условием и индекс по выражению, скажем по lower(email).

Отдельно про хранение: в InnoDB таблица физически лежит в кластерном индексе первичного ключа, а вторичный индекс хранит значение этого ключа, поэтому поиск идёт в два шага. Покрывающий индекс, в котором есть все нужные колонки, позволяет не ходить в таблицу вовсе.

Закончите тем, как проверяли результат: EXPLAIN ANALYZE, лог медленных запросов, просмотр реального SQL, который отправляет Hibernate.

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

Вешают индекс на каждую колонку. Оптимизатор всё равно предпочтёт последовательное чтение, если условие отбирает заметную часть таблицы, а запись, обновление и размер базы дорожают с каждым индексом.

Второе: путаются в порядке колонок составного индекса. Индекс по паре колонок работает для запроса по первой из них и по обеим сразу, но не помогает там, где в условии только вторая.

Третье: убивают индекс собственным кодом. Функция поверх колонки, приведение типов из-за неудачного типа параметра, поиск по шаблону с ведущим символом подстановки. Рядом стоит проблема лишних запросов из ленивых связей: она лечится не индексом, а выборкой связанных сущностей одним запросом.