Вопрос производительности базы данных и запросов к ней с течением времени становится все актуальнее. Чем больше проект и данных в таблицах и сложнее связи, тем выше вероятность столкнуться с медленной работой и нежелательными блокировками.
Подобные вопросы редко касаются новичков. Но нужно знать, как работать с такими проблемами, так как в будущем с ними предстоит столкнуться. Поэтому в этом уроке разберем основные направления производительности базы данных.
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.
Мы разобрали основные подходы оптимизации базы данных. Подобный вопрос редко касается новичков, но, например, на собеседовании иногда спрашивают на эту тему. Если вы дадите хороший ответ, то у вас будет больше шансов на трудоустройство.
Производительность базы данных — серьезная тема, поэтому ей посвящена не одна книга. В этом уроке мы разобрали основные направления, а остальное сможете познать на практике.
