Теория: Производительность

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

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

EXPLAIN

SQL — это декларативный язык, то есть им мы описываем ЧТО хотим получить, а не КАК. Но это не устраивает машину, так как СУБД должна знать, каким образом добраться до этих данных.

В СУБД реализована подсистема, которая называется планировщик или scheduler. Она строит план запроса — описывает, как будут извлекаться данные, которые хранятся внутри базы. Когда планировщик строит план, он учитывает множество факторов — например, статистику обращений или информацию о количестве данных в таблицах.

Результат работы планировщика можно посмотреть командой 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 -> Hash -> Seq Scan. Подробнее про план читайте в статье Производительность запросов в PostgreSQL — шаг за шагом.

Индексы

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

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

Этот запрос создает индекс на таблице users по полю birthday. Теперь запросы с условием или сортировкой по birthday будут работать быстрее за счет использования индекса во время подготовки данных. Пример:

SELECT * from users WHERE birthday = '2000-01-01';

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

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

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

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

Еще один подход для оптимизации называется денормализацией — процесс, обратный нормализации. В реляционной теории такого понятия нет и оно противоречит ее идеям. Но на практике этот способ активно применяется, так как за счет избыточности позволяет упростить запросы, так как данные ближе и их легче извлечь.

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

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

Производительность базы данных — серьезная тема, поэтому ей посвящена не одна книга. В этом уроке мы разобрали основные направления, а остальное сможете познать на практике.