Почему индексирование всех полей таблицы — это плохая практика?

Создание индекса — это компромисс между скоростью чтения и затратами на запись/хранение. Индексирование всех полей приводит к негативным последствиям.

Основные проблемы:

1. Замедление операций записи (INSERT, UPDATE, DELETE): Каждый индекс — это отдельная структура данных (чаще B-дерево). При изменении строки в таблице должны быть обновлены все индексы, куда входят изменённые столбцы. Это увеличивает нагрузку на диск и блокировки.

   -- Каждый INSERT в эту таблицу будет обновлять 4 индекса
   CREATE INDEX idx_users_name ON users(name);
   CREATE INDEX idx_users_email ON users(email);
   CREATE INDEX idx_users_age ON users(age);
   CREATE INDEX idx_users_city ON users(city);

2. Значительный рост занимаемого места: Индексы могут занимать сопоставимый с самой таблицей или даже больший объём на диске, увеличивая затраты на хранение и время резервного копирования.

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

4. Бесполезные индексы: Индекс полезен, только если он используется в условиях WHERE, JOIN, ORDER BY или для покрывающих запросов (covering index). Индекс по полю, которое никогда не фигурирует в этих конструкциях, — пустая трата ресурсов.

Когда создавать индекс? Создавайте индексы для полей, которые:

• Часто используются в условиях WHERE (особенно с операторами =, >, <, BETWEEN).
• Участвуют в JOIN.
• Используются для сортировки (ORDER BY) или группировки (GROUP BY).
• Уникальны или почти уникальны (высокая кардинальность).

Пример правильного подхода:

-- Хорошо: Индекс на часто используемое поле для поиска
CREATE INDEX idx_users_email ON users(email);

-- Хорошо: Составной индекс для запроса по двум полям
CREATE INDEX idx_users_status_created ON users(status, created_at);

-- Плохо: Индекс по полю с низкой кардинальностью (например, 'пол')
-- если по нему нет частых и селективных запросов
-- CREATE INDEX idx_users_gender ON users(gender);

Мониторинг: Регулярно анализируйте использование индексов с помощью команд типа EXPLAIN ANALYZE и системных представлений (например, pg_stat_user_indexes в PostgreSQL). Удаляйте неиспользуемые индексы.