Как понять, что индекс в базе данных построен неправильно и какие проблемы могут быть с индексами

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

Умеете ли вы доказать проблему с индексом планом запроса, а не догадкой. Java-разработчику это нужно, потому что медленный запрос часто рождается в репозитории Spring Data или в HQL, а видно его только в базе. Следом обычно дают конкретный запрос и просят предложить индекс.

Как отвечать

Сначала найдите медленные запросы: pg_stat_statements или log_min_duration_statement в PostgreSQL, slow query log в MySQL, логирование SQL в Hibernate. Для каждого смотрите план через EXPLAIN (ANALYZE, BUFFERS).

Признаки плохого индекса:

• Seq Scan на большой таблице при выборке пары строк: индекса нет или он не подходит;
• неверный порядок колонок: составной индекс по (status, created_at) не помогает запросу только по created_at;
• функция или несовпадение типов: lower(email) или, в MySQL, сравнение строковой колонки с числом. Индекс не используется, нужен индекс по выражению или правильный тип параметра;
• низкая селективность: индекс по булеву флагу почти бесполезен, планировщик выберет полное чтение;
• оценка строк расходится с фактом в разы: статистика устарела, помогает ANALYZE;
• индекс никто не читает: idx_scan = 0 в pg_stat_user_indexes за долгий период. Он только тормозит запись;
• дубли: индекс по (a) рядом с (a, b) обычно лишний;
• раздувание: после массовых удалений индекс распухает, перестраивают его через REINDEX CONCURRENTLY, чтобы не блокировать запись.

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

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

Считают любой Seq Scan ошибкой. На маленькой таблице или при выборке большой доли строк он дешевле индекса.

Смотрят EXPLAIN без ANALYZE и делают выводы по оценкам. Реальную картину дают фактические строки и время.

Добавляют индекс под каждый медленный запрос. Через год на таблице десять индексов, вставка идёт медленно, а половина из них не используется.