Лекции 11–13: Физическое проектирование, индексы, планы запросов, тюнинг
Связь с предыдущими лекциями
В лекциях 1–3 мы научились собирать требования и понимать жизненный цикл БД. В лекциях 4–6 построили концептуальную модель и зафиксировали бизнес-правила. В лекциях 7–10 перевели модель в логическую реляционную схему: таблицы, ключи, связи, ограничения и нормализацию.
Теперь мы переходим к физическому проектированию и производительности.
На логическом уровне мы говорили что хранить и какие правила должны быть истинны.
На физическом уровне мы решаем как именно хранить и как быстро получать данные в выбранной СУБД.Важно: физические решения всегда зависят от реальных запросов и нагрузки. Поэтому в этих лекциях мы будем постоянно связывать:
типовые запросы
индексы
планы выполнения
сбор статистики
измерения и тюнингЧто такое физическое проектирование
Физическое проектирование — это набор решений, которые определяют реальную работу БД:
выбор типов данных и их влияние на размер и сравнение
индексы и их состав
разбиение данных (партиционирование)
подходы к истории и архиву
настройки хранения и обслуживания (например, обновление статистики)Физический уровень должен поддерживать бизнес-требования из первых лекций:
производительность
надежность
безопасность
стоимость владенияТипы данных как фундамент производительности
Тип данных влияет на:
размер строк и страниц хранения
скорость сравнений и сортировок
возможность использовать индексы эффективноПрактические правила выбора типов
Храните числа числами, даты датами, а не строками
- плохо:
"2026-02-08" в текстовом поле
- хорошо:
DATE или
TIMESTAMP
Выбирайте минимально достаточный тип
- плохо: все идентификаторы как “огромные строки”
- хорошо: числовые
BIGINT или
UUID, если он реально нужен
Будьте осторожны с “универсальным текстом”
- текстовые поля удобны, но часто приводят к ошибкам в валидации и тяжелым сравнениям
Если используете PostgreSQL, справочник по типам данных: PostgreSQL: Data Types
Типичная ошибка
Хранить “значение + единицы” в одном поле.
плохо: "1000 RUB"
хорошо: отдельные поля amount и currency_code, где currency_code связан со справочникомЭто решение начинается на логическом уровне, но физически оно влияет на индексы, агрегации и сортировки.
Индексы: что это и зачем они нужны
Индекс — это структура данных, которая помогает находить строки быстрее, чем полный просмотр таблицы.
Простая интуиция:
без индекса БД часто вынуждена читать много лишних строк
с индексом БД может “перепрыгнуть” сразу к нужнымВ PostgreSQL обзор по индексам: PostgreSQL: Indexes
!Интуитивная иллюстрация, почему индекс ускоряет поиск
Цена индекса
Индексы ускоряют чтение, но имеют стоимость:
занимают место
замедляют вставки, обновления и удаления, потому что индекс тоже нужно обновлять
требуют обслуживания (вакуум, статистика, иногда перестроение)Физическое проектирование — это баланс.
Основные виды индексов и когда они нужны
Ниже — не полный список всех вариантов, а минимальный набор, который чаще всего нужен в прикладных системах.
Индекс на первичный ключ и внешние ключи
первичный ключ почти всегда индексируется автоматически
внешний ключ не всегда автоматически создает индекс, но он часто нуженПочему индекс на внешний ключ важен:
ускоряет соединения (JOIN)
ускоряет проверки целостности при удалении или обновлении в родительской таблицеПрактический пример:
есть orders(customer_id) как внешний ключ на customers(customer_id)
если вы часто ищете заказы клиента или соединяете клиентов с заказами, индекс на orders(customer_id) почти всегда оправданСоставной индекс
Составной индекс строится по нескольким полям, например (customer_id, created_at).
Его создают, когда запросы фильтруют или сортируют по комбинации.
Ключевое правило порядка полей:
индекс эффективен “слева направо”Пример:
индекс (customer_id, created_at) хорошо работает для условий:
-
WHERE customer_id = ...
-
WHERE customer_id = ... AND created_at >= ...
-
ORDER BY created_at внутри конкретного клиента
но он хуже подходит для:
-
WHERE created_at >= ... без
customer_idЧастичный индекс
Частичный индекс хранит только часть строк по условию.
Когда полезен:
таблица большая
часто ищете “активные” или “неархивные” строкиПример идеи:
у orders есть признак is_deleted = false или статус “активен”
большинство запросов работает только с активными
частичный индекс становится меньше и быстрееВ PostgreSQL: PostgreSQL: Partial Indexes
Индекс по выражению
Индекс по выражению строится не по самому полю, а по результату функции или выражения.
Когда полезен:
вы фильтруете по преобразованному значениюТипичный пример:
поиск без учета регистра (в зависимости от требований)В PostgreSQL: PostgreSQL: Indexes on Expressions
Покрывающий индекс
Идея покрывающего индекса:
запрос может прочитать нужные столбцы прямо из индекса и реже обращаться к таблицеЭто особенно полезно для “узких” запросов, которые выбирают несколько полей и имеют строгий фильтр.
В PostgreSQL, начиная с версии 11, это поддерживается через INCLUDE: PostgreSQL: Index-Only Scans and Covering Indexes
Почему индекс иногда не используется
Частый вопрос: “Мы создали индекс, почему запрос всё равно медленный?”
Типовые причины:
условие возвращает слишком много строк
- если выбирается большая часть таблицы, полный просмотр может быть дешевле
несовпадение условия с тем, как устроен индекс
- например, функция над полем в
WHERE без индекса по выражению
устаревшая статистика
- планировщик неверно оценивает, сколько строк вернется
тип данных и сравнение
- сравнение строки и числа, неявные преобразования, разные колляции
Планы запросов: как БД решает, что делать
Когда вы отправляете SQL-запрос, СУБД обычно проходит этапы:
разбирает SQL (парсинг)
проверяет права и корректность
строит несколько вариантов выполнения
выбирает план с минимальной оценочной стоимостью
выполняетПлан запроса — это дерево операций: чтение таблиц, использование индексов, соединения, сортировки, агрегации.
В PostgreSQL команда для просмотра плана: PostgreSQL: EXPLAIN
!Схема-дерево, показывающая, что план запроса состоит из операций
Как читать EXPLAIN на базовом уровне
В выводе EXPLAIN важно различать:
оценки планировщика
фактические значения при выполненииПоэтому для диагностики часто используют:
Что это дает:
ANALYZE показывает фактическое время и фактическое количество строк
BUFFERS показывает работу с памятью и чтение страницЕсли фактические строки сильно отличаются от оценочных, это сигнал:
статистика не актуальна
условие нетипичное
данные распределены неравномерно
в запросе есть коррелированные условия, которые тяжело оценитьТиповые операции в планах и их смысл
Seq Scan
Seq Scan — последовательное чтение таблицы.
Не всегда плохо:
если таблица маленькая
если запросу нужно много строкIndex Scan и Index Only Scan
Index Scan использует индекс, но обычно все равно обращается к таблице за остальными столбцами
Index Only Scan может обойтись чтением только индекса, если таблица “достаточно обслужена” и нужные данные есть в индексеВиды соединений
Для JOIN планировщик выбирает стратегию.
Nested Loop
- хорошо, когда одна сторона маленькая, а по второй есть хороший индекс
Hash Join
- хорошо для больших наборов без подходящего индекса
Merge Join
- хорошо, когда обе стороны уже отсортированы по ключу соединения или сортировка дешевая
В PostgreSQL подробности: PostgreSQL: Planner/Optimizer
Как тюнить запросы: практический алгоритм
Ниже — последовательность, которая помогает не “стрелять наугад”.
Зафиксируйте симптом
- какой запрос медленный
- какой SLA по времени
- при каких параметрах
Получите план выполнения
-
EXPLAIN (ANALYZE, BUFFERS)
Найдите самую дорогую часть
- обычно это узел с максимальным временем или большим количеством прочитанных страниц
Проверьте, корректны ли оценки
- сильное расхождение “ожидали 10 строк, получили 100000” почти всегда требует внимания
Улучшите доступ к данным
- индекс
- переписывание условий
- уменьшение выборки
Упростите результат
- выберите только нужные столбцы
- уберите лишние
JOIN
- проверьте, нужен ли
DISTINCT
Проверьте на реальных данных и повторите измерениеКлючевой принцип: тюнинг без измерений почти всегда превращается в ухудшение.
Проектирование индексов от запросов
Правильный способ думать об индексах:
не “на каждое поле по индексу”
а “под конкретные фильтры, соединения и сортировки”Мини-шаблон анализа запроса
Для каждого важного запроса выпишите:
фильтры WHERE
поля соединений JOIN ... ON
сортировку ORDER BY
группировку GROUP BYИ дальше принимайте решения:
нужен ли индекс по полям фильтра
нужен ли индекс по внешнему ключу
нужен ли составной индекс, который одновременно помогает фильтровать и сортироватьПартиционирование: когда таблица слишком большая
Партиционирование — разбиение логически одной таблицы на несколько физических частей по правилу.
Когда оно оправдано:
таблица очень большая
большинство запросов обращается к ограниченному диапазону
- например, заказы за последние 30 дней
Типовой вариант — партиционирование по дате.
Что партиционирование дает:
быстрее поиск по диапазону за счет “отсечения” ненужных частей
проще архивирование по частямЧто оно усложняет:
схему и миграции
некоторые виды уникальности и внешних ключей, в зависимости от СУБД и дизайнаВ PostgreSQL: PostgreSQL: Table Partitioning
Статистика, ANALYZE и почему планировщик может ошибаться
Планировщик выбирает план на основе статистики:
сколько строк в таблице
сколько разных значений в колонках
насколько равномерно распределены данныеЕсли статистика устарела, план может стать хуже.
В PostgreSQL статистика обновляется через ANALYZE, а автоматическое обслуживание делает autovacuum. Справка: PostgreSQL: Routine Vacuuming
Практическая мысль:
“медленно после загрузки данных” часто означает “не обновили статистику”Обслуживание: VACUUM, autovacuum и рост таблиц
В PostgreSQL из-за MVCC (механизм конкурентного доступа) обновления и удаления не всегда сразу освобождают место для чтения как “пустое”. Это нормальная модель, но она требует обслуживания.
Что важно понимать на уровне курса:
без обслуживания таблицы и индексы могут разрастаться
разрастание повышает число читаемых страниц и замедляет запросыЗадача проектировщика:
учитывать это в эксплуатации
не отключать обслуживание “ради скорости”, не понимая последствийАнтипаттерны физического проектирования
Индекс на каждую колонку “на всякий случай”
- замедляет записи и раздувает хранение
Игнорирование внешних ключей и их индексации
- соединения становятся тяжелыми, а проверки целостности дорогими
Тюнинг без замеров
- добавили индекс, стало хуже, потому что изменились планы других запросов
Усложнение схемы раньше времени
- партиционирование и сложные индексы без подтвержденной необходимости
Мини-чеклист по итогам лекций
Для каждой таблицы выберите корректные типы данных
Для каждой связи оцените индексацию внешних ключей
Соберите список ключевых запросов
Под каждый запрос проверьте: 1. фильтры
2. соединения
3. сортировки
4. нужные поля в SELECT
Постройте минимальный набор индексов, который покрывает критичные сценарии
Проверьте планы через EXPLAIN (ANALYZE, BUFFERS)
Убедитесь, что статистика и обслуживание включены и понятны команде эксплуатацииЧто будет дальше
В следующих лекциях курса обычно переходят к темам, которые связывают схему и работу системы в реальном времени:
транзакции и конкурентный доступ
уровни изоляции и блокировки
миграции и эволюция схемы без простояФизическое проектирование дает основу: даже идеальная логическая модель будет “тормозить”, если ей не соответствуют индексы, статистика и грамотный подход к запросам.