Зарегистрируйтесь для доступа к 15+ бесплатным курсам по программированию с тренажером

Производительность Основы реляционных баз данных

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

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

EXPLAIN

Начнем с того, как база данных выбирает данные. SQL — декларативный язык, то есть им мы описываем ЧТО хотим получить, а не КАК. Но это не устраивает машину, СУБД должна знать, каким образом добраться до этих данных. В СУБД реализована подсистема, которая называется планировщик (scheduler). Она строит так называемый план запроса. Этот план описывает, как конкретно будут извлечены данные, которые хранятся внутри базы данных. При построении плана планировщик учитывает множество факторов: например, статистику обращений, информацию о количестве данных в таблицах и многое другое. Более того, планировщик в PostgreSQL применяет генетические алгоритмы, для того, чтобы строить план быстро и эффективно. Результат работы планировщика можно посмотреть командой EXPLAIN:

EXPLAIN SELECT * FROM users
  JOIN topics ON users.id = topics.user_id
  WHERE users.created_at > '10.10.2018';

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Hash Join  (cost=10.66..23.59 rows=42 width=2377)
   Hash Cond: (topics.user_id = users.id)
   ->  Seq Scan on topics  (cost=0.00..11.30 rows=130 width=572)
   ->  Hash  (cost=10.50..10.50 rows=13 width=1805)
         ->  Seq Scan on users  (cost=0.00..10.50 rows=13 width=1805)
               Filter: (created_at > '2018-10-10 00:00:00'::timestamp without time zone)
(6 rows)

Справа в самом низу показаны операции, которые выполняются в первую очередь. Затем данные, полученные на этих шагах, передаются выше — и так до самого верха. Слева указаны операции, которые производятся с данными, например Seq Scan означает последовательный перебор таблицы (самая дорогая операция при условии, что данных много). Подробнее про план читайте в соответствующей статье.

Индексы

План запроса можно использовать по-разному: например, переписать (или даже разбить) запрос на более эффективный. С другой стороны, некоторые запросы уже достаточно оптимизированы, и тогда для их ускорения используют индексы. Индекс — это специальная структура внутри базы данных, создаваемая для ускорения поиска. Концептуально индекс в базе данных подобен предметному указателю в любой книге.

-- Пример создания индекса по полю birthday таблицы users
CREATE INDEX ON users(birthday);

Но создание индекса само по себе не гарантирует эффективности, многое зависит от того, правильный ли индекс создан, сколько уже индексов было в табличке (каждый новый индекс замедляет вставку и обновление данных), сколько данных в таблице, какие запросы выполняются к этой таблице.

В PostgreSQL встроено 6 разных видов индексов, подходящих под разные ситуации. Для работы с ними нужно понимать несколько вещей:

  1. Устройство индексов как таковых. Здесь не обойтись без знания структур данных и алгоритмов. В первую очередь речь идёт про сбалансированные деревья (btree).
  2. Классификацию видов запросов. Например, запрос по диапазону или запрос с применением LIKE оператора — совершенно разные виды запросов, которые по-разному работают и оптимизируются.
  3. Влияние различных конструкций SQL на план запроса: например, ORDER BY — дорогая операция, которая часто приводит к полному перебору таблицы.

Денормализация

Ещё один подход, связанный с оптимизацией, называется денормализацией. Денормализация — это процесс, обратный нормализации. С точки зрения реляционной теории, такого понятия не существует и оно само по себе противоречит её идеям, но на практике этот способ активно применяется, так как за счёт избыточности позволяет упростить запросы (так как данные ближе и их легче извлечь). Цена за денормализацию — дополнительный объём и (не во всех случаях) необходимость производить синхронизацию данных самостоятельно: например, мы можем хранить имя пользователя в разных таблицах, что создаёт сложности при изменении имени. Нужно не забыть поменять его во всех таблицах, где оно используется. В общем случае, денормализация значительно сокращает число запросов с соединениями (joins).


Дополнительные материалы

  1. Типы индексов в PostgreSQL
  2. B-дерево
  3. Продуманная оптимизация

Аватары экспертов Хекслета

Остались вопросы? Задайте их в разделе «Обсуждение»

Вам ответят команда поддержки Хекслета или другие студенты.

Для полного доступа к курсу нужен базовый план

Базовый план откроет полный доступ ко всем курсам, упражнениям и урокам Хекслета, проектам и пожизненный доступ к теории пройденных уроков. Подписку можно отменить в любой момент.

Получить доступ
900
упражнений
2000+
часов теории
3200
тестов

Открыть доступ

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

  • 130 курсов, 2000+ часов теории
  • 900 практических заданий в браузере
  • 360 000 студентов
Даю согласие на обработку персональных данных, соглашаюсь с «Политикой конфиденциальности» и «Условиями оказания услуг»

Наши выпускники работают в компаниях:

Логотип компании Альфа Банк
Логотип компании Aviasales
Логотип компании Yandex
Логотип компании Tinkoff
Рекомендуемые программы

С нуля до разработчика. Возвращаем деньги, если не удалось найти работу.

Иконка программы Python-разработчик
Профессия
Разработка веб-приложений на Django
30 июня 10 месяцев
Иконка программы PHP-разработчик
Профессия
Разработка веб-приложений на Laravel
30 июня 10 месяцев
Иконка программы Node.js-разработчик
Профессия
Разработка бэкенд-компонентов веб-приложений
30 июня 10 месяцев
Иконка программы Fullstack-разработчик
Профессия
Новый
Разработка фронтенд и бэкенд компонентов веб-приложений
30 июня 16 месяцев
Иконка программы Java-разработчик
Профессия
Разработка приложений на языке Java
30 июня 10 месяцев

Используйте Хекслет по максимуму!

  • Задавайте вопросы по уроку
  • Проверяйте знания в квизах
  • Проходите практику прямо в браузере
  • Отслеживайте свой прогресс

Зарегистрируйтесь или войдите в свой аккаунт

Даю согласие на обработку персональных данных, соглашаюсь с «Политикой конфиденциальности» и «Условиями оказания услуг»