Почему индексация важнее, чем ваш утренний кофе

Представьте себе: вы в библиотеке без системы каталогов. Нужна книга о выращивании репы в XVII веке? Удачи в поиске среди 2 миллионов томов, упорядоченных по детскому прозвищу автора. Именно это происходит, когда ваши запросы к базе данных выполняются без надлежащей индексации — и вот почему однажды я потратил вечер пятницы на отладку запроса поиска продукта за 43 секунды, который чуть не обрушил наше приложение знакомств для фермеров, выращивающих картофель.

Индексы базы данных: как свайпы Tinder для ваших данных

Индексы базы данных подобны свайпам Tinder — они помогают вашему SQL-движку быстро находить подходящие совпадения, не сканируя целые таблицы. Когда мы добавили наш первый составной индекс в таблицу user_profiles:

CREATE INDEX idx_swipe_preferences ON user_profiles (age_range, interests, geo_hash) INCLUDE (profile_score);

Наша скорость сопоставления запросов улучшилась с 12 секунд до 0,2 секунды, что позволило ежемесячно экономить 420 кг выбросов CO₂ от простаивающих экземпляров EC2 (и, вероятно, предотвратило 3 развода инженеров).

graph TD A[Запрос: найти профили в возрасте 25–30 лет
интересующиеся пешими походами] --> B{Кластеризованный индекс?} B -->|Нет| C[Полное сканирование таблицы
2,5 миллиона строк] B -->|Да| D[Поиск по индексу
Диапазон 25-30] D --> E[Фильтрация по 'пешим походам'
в листе индекса] E --> F[Поиск ключа для
оценки профиля] C --> G[Низкая производительность
12 секунд] F --> H[Молниеносно
0,2 секунды]

Зоопарк индексов: выбор идеального соответствия

1. Спид-дейтинг (индекс B-Tree)

Это базовый индекс, идеально подходящий для запросов на равенство и диапазон. Совет: сначала индексируйте столбцы, используемые в предложениях WHERE:

-- Хорошо для WHERE last_login > '2025-05-01' ORDER BY user_id
CREATE INDEX idx_activity ON users (last_login, user_id);

2. Многоязычный (покрывающий индекс)

Зачем делать дополнительные поиски, если можно получить всё сразу?

-- Покрывает как фильтрующие, так и выходные столбцы
CREATE INDEX idx_order_search ON orders (customer_id, order_date) INCLUDE (total_amount, shipping_status);

3. Специалист (отфильтрованный индекс)

Если вас интересуют только активные пользователи:

CREATE INDEX idx_active_users ON users (email) WHERE account_status = 'active';

Мы сократили размер индекса на 78%, используя его для нашей службы рассылки новостей, потому что, давайте признаем, неактивные пользователи не открывали наши письма после инцидента с массовым ответом в 2024 году.

Ловушки индексации: когда помощь становится вредом

На прошлый День благодарения мы усвоили этот урок на собственном горьком опыте, когда наш «оптимизированный» каталог продуктов начал сбоить при записи:

-- Инцидент «Сколько индексов — это слишком много?»
SELECT COUNT(*) FROM sys.indexes WHERE object_id = OBJECT_ID('products');
-- Возвращается 27...

Золотые правила из нашего анализа:

  • Никогда не индексируйте столбцы, которые обновляются чаще, чем запрашиваются.
  • Составные индексы должны соответствовать шаблонам запросов, как хороший партнёр по танцам.
  • Ежемесячно отслеживайте использование индекса с помощью:
SELECT index_name, usage_count FROM pg_stat_all_indexes WHERE schemaname = 'public';

Трудности обслуживания: поддерживайте свои индексы в актуальном состоянии

Наш контрольный список индексации сэкономил $24 тыс. в год на облачных расходах:

  • еженедельная проверка фрагментации индексов;
  • ежемесячная очистка неиспользуемых индексов;
  • ежеквартальный анализ плана запросов.
-- Найти неиспользуемые индексы
SELECT t.relname AS table_name, indexrelname AS index_name, pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
FROM pg_stat_user_indexes i JOIN pg_stat_user_tables t ON i.relid = t.relid
WHERE idx_scan = 0;

Реальная история войны: дело о недостающих миллисекундах

Когда наша панель аналитики начала отключаться во время демонстраций инвесторам (всегда в самый неподходящий момент), мы обнаружили:

  • сканирование таблицы с 4 столбцами в нашей таблице событий на 20 миллионов строк;
  • отсутствующий составной индекс (event_type, org_id, created_at);
  • устаревшая статистика, приводящая к плохим планам запросов. Решение? Тщательно созданный индекс и обновление статистики:
CREATE INDEX idx_event_analysis ON user_events (org_id, event_type, created_at) INCLUDE (payload);
ANALYZE VERBOSE user_events;

Результат: время выполнения запроса сократилось с 14,3 секунды до 230 мс, и наш генеральный директор перестал угрожать «перенести всё в Excel».

Дзен индексации: поиск баланса

Помните: индексы подобны специям в карри — их слишком мало, и блюдо будет пресным, а если слишком много, то несъедобным. Начните с самых важных запросов, оцените влияние и повторяйте. Ваша база данных будет шептать вам приятные слова в виде ответов за доли миллисекунд. А теперь извините, мне нужно объяснить своей спутнице, почему я только что упорядочил свой ящик для столовых приборов по типу и совместимости с блюдами…