Проектирование под выполняемые запросы

Что это такое

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

Почему это важно

Схема без индексов корректна и непригодна в масштабе; схема с неправильными индексами корректна, медленна и дорога для записи. Обе проблемы незаметны, пока данные не вырастут, а значит обратная связь приходит через месяцы после решения. Разрыв закрывает работа от запросов, а не от диаграммы сущностей: запросы известны раньше, чем объём данных.

Ключевые идеи

Запишите запросы до того, как зафиксируете схему

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

список заказов клиента, сначала новые
  -> индекс по (customer_id, placed_at desc)

поиск заказа по публичному идентификатору
  -> уникальный индекс по (public_id)

Каждый индекс выбран под запрос и стоит на каждой записи

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

Денормализуйте только то, что оправдано измерением

Хранение вычисленного итога в родительской строке убирает агрегацию из частого чтения, но вводит обязанность держать его правильным при каждой записи. Это настоящий компромисс и иногда верный, однако основанием для него должен быть измеренный запрос, а не предполагаемый. Там, где так делают, имя столбца как производной величины не даёт позднему читателю принять его за источник истины.

Представлению с тяжёлым чтением может понадобиться своя таблица

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

Секционирование и архивирование — решения проекта, а не довесок

Таблица, которая только растёт, в конце концов станет слишком большой для комфортной индексации и изменения. Решение о том, какие строки горячие, а какие — история, и о том, идёт ли это разделение по времени или по арендатору, позволяет горячей таблице оставаться небольшой. Сделать это позже — значит провести миграцию самой большой таблицы в системе.

Схема должна делать неправильное трудным

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

Практика

Проиндексируйте запросы одного приложения

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

Готово, когда: выполнение одного из запросов после добавления его индекса читает заметно меньше строк, и ни один индекс в списке не остаётся без запроса, которому он нужен.

Решите разделение на горячее и холодное

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

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

Частые ошибки

Проектирование от диаграммы сущностей и индексы потом — о нехватке индекса узнают на боевом объёме; исправить: сначала выпишите запросы и выведите индексы из них.
Индекс на каждый упомянутый где-либо столбец — пропускная способность записи и хранилище платят за ненужные индексы; исправить: обоснуйте каждый индекс запросом.
Денормализация на догадке — производное значение расходится с источником, и движка, который бы это поймал, нет; исправить: денормализуйте только оправданное измерением и помечайте копию.
Таблица растёт без плана — будущая миграция ложится на самую большую таблицу в системе; исправить: решите разделение на горячее и холодное, пока таблица невелика.
Важные правила только в коде приложения — новый путь или ручной скрипт их обходят; исправить: выразите правило ограничением.

Что читать дальше

Ключи и связи (05-backend-data/14-database-design/01-keys-and-relations) — ключи и связи, на которых строятся эти запросы.
Проектирование баз данных (05-backend-data/14-database-design) — дисциплина, которую завершает этот дочерний урок.
Индексы и планы запросов (05-backend-data/07-sql-relational-databases/03-indexes-and-query-plans) — как убедиться, что выбранные здесь индексы действительно используются.