Всем привет, меня зовут Денис Лимарев, я руковожу разработкой в одной из продуктовых команд Uzum Tezkor. В этой статье разберу несколько оптимизаций запросов к БД, которыми наша команда пользуется при разработке своих сервисов, и опишу подход к оптимизациям запросов в целом. В своих проектах мы используем PostgreSQL версии 14.15, поэтому все запросы я проанализировал на ней, и ваши результаты могут отличаться в зависимости от вашей версии.
Оглавление:
Схема данных
Георгий работает в городской библиотечной сети, где отвечает за наполнение каталогов издателей. Однажды к нему подошёл тимлид со следующей проблемой: «Мы перестали укладываться в нормативы по скорости поиска книг в каталоге, и даже превысили допустимый порог ошибок за этот месяц — нужно срочно что-то предпринять». «Разберёмся», — ответил Георгий, и сразу отправился изучать метрики библиотечной системы.
Посмотрев метрики API, Георгий собрал профилировщиком статистику по медленным операциям сервиса. Стало очевидно, что проблема в запросах к БД. Поэтому Георгий изучил схему данных проекта:
CREATE TABLE catalogs ( id uuid primary key, publisher_id uuid not null, status enum('available', 'upcoming', 'archived') not null, constraint catalogs_publisher_status_uniq unique (publisher_id, status) ); CREATE TABLE genres ( id uuid primary key, name text not null, catalog_id uuid not null ); create index idx_genres_catalog_id on genres (catalog_id); CREATE TABLE book_genre ( book_id uuid not null, genre_id uuid not null ); create index idx_book_genre_hash_book_id on book_genre using hash (book_id); create index idx_book_genre_hash_genre_id on book_genre using hash (genre_id); create unique index book_genre_reverse_pkey on book_genre (genre_id, book_id); CREATE TABLE books ( id uuid primary key, title text not null, isbn text not null, price int not null, status enum('available', 'upcoming') not null, publisher_id uuid not null, catalog_id uuid not null ); CREATE INDEX idx_books_publisher_catalog_status on books (publisher_id, catalog_id, status); CREATE TABLE book_editions ( book_id uuid not null, edition_id uuid not null ); create unique index book_edition_pkey on book_editions (book_id, edition_id); CREATE TABLE editions ( id uuid primary key, title text not null, isbn text not null, price int not null, max_copies int not null ); CREATE TABLE stockrooms ( id uuid primary key, book_isbn text not null, quantity int not null, publisher_id uuid not null, updated_at timestamptz not null, constraint stockrooms_publisher_book_uniq unique (publisher_id, book_isbn) ); Оптимизации ad-hoc
Первым путем API, на который упал взгляд Георгия, был /api/v1/books. Он состоял из последовательных запросов в БД:
SELECT b.id, b.title, b.isbn, b.price FROM catalogs c JOIN genres g ON c.id = g.catalog_id JOIN book_genre bg ON bg.genre_id = g.id JOIN books b ON b.id = bg.book_id WHERE b.isbn IN (:isbns) AND c.status = 'available';Далее частями выбираются издания:
SELECT e.title, e.isbn, e.price FROM book_editions be JOIN editions e ON be.edition_id = e.id WHERE be.book_id IN (:book_ids);Записи изданий были не у всех книг, и в некоторых случаях они могли полностью отсутствовать. Поэтому, после некоторых рассуждений, Георгий пришел к выводу, что он мог бы уменьшить размер запроса на издания или полностью его убрать, добавив поле со знанием о том, есть ли издание у книги. Потому что в схеме данных издания не могли появиться в уже созданном каталоге, только в новых.
ALTER TABLE books ADD COLUMN with_editions boolean NOT NULL DEFAULT true;Таким образом удалось перенести принятие решения о наличии издания в код до непосредственного запроса в БД. Это помогло уменьшить время выполнения части запросов.
Денормализация данных
Затем Георгий перешел к запросу книг:
SELECT b.id, b.title, b.isbn, b.price FROM catalogs c JOIN genres g ON c.id = g.catalog_id JOIN book_genre bg ON bg.genre_id = g.id JOIN books b ON b.id = bg.book_id WHERE b.isbn IN (:isbns) AND c.status = 'available' AND c.publisher_id = :publisher_id;Первым делом выполнил EXPLAIN ANALYZE:
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Nested Loop (cost=2.39..692.44 rows=2 width=93) (actual time=0.738..78.045 rows=500 loops=1) | | -> Nested Loop (cost=0.71..42.29 rows=357 width=16) (actual time=0.542..12.698 rows=3280 loops=1) | | -> Nested Loop (cost=0.71..17.55 rows=12 width=16) (actual time=0.455..1.183 rows=155 loops=1) | | -> Index Scan using catalog_publisher_status_uniq on catalogs c (cost=0.29..2.50 rows=1 width=16) (actual time=0.049..0.051 rows=1 loops=1) | | Index Cond: ((publisher_id = '3b73167d-3ee3-4f09-a3af-c2989d76f04b'::uuid) AND (status = 'available'::catalog_status)) | | -> Index Scan using idx_genres_catalog_id on genres g (cost=0.42..14.93 rows=12 width=32) (actual time=0.402..1.085 rows=155 loops=1) | | Index Cond: (catalog_id = c.id) | | -> Index Scan using idx_book_genre_hash_genre_id on book_genre bg (cost=0.00..1.49 rows=57 width=32) (actual time=0.059..0.071 rows=21 loops=155) | | Index Cond: (genre_id = g.id) | | -> Index Scan using books_pkey on books b (cost=1.68..1.82 rows=1 width=93) (actual time=0.020..0.020 rows=0 loops=3280) | | Index Cond: (id = bg.book_id) | | Filter: ((isbn)::text = ANY ('{тут 500 isbn книг}'::text[])) | | Rows Removed by Filter: 1 | |Planning Time: 40.434 ms | |Execution Time: 78.272 ms | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+Попытка добавить новые индексы, которые могли бы улучшить ситуацию, не увенчалась успехом: текущие индексы лучше всего покрывали запрос. После обсуждения с коллегами появилась идея обогатить таблицу books дополнительными данными, которые уменьшат селективность запроса: добавить catalogs.id, так как информация о жанрах в запросе не требовалась, и связанные сущности genres и catalogs после создания записей books никогда не изменялись.
Миграцию новых данных провели в три подхода:
-
сначала добавили необязательное поле
catalog_id:ALTER TABLE books ADD COLUMN catalog_id uuid null; -
частями обновили данные по
books; -
после этого сделали новое поле обязательным.
Теперь новый запрос на книги выглядел так:
SELECT b.id, b.title, b.isbn, b.price FROM catalogs c JOIN books b ON b.catalog_id = c.id WHERE b.isbn IN (:isbns) AND c.status = 'available' AND c.publisher_id = :publisher_id;Уменьшение количества связанных таблиц в запросе значительно сократило время ответа.
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Nested Loop (cost=2.10..55697.11 rows=1 width=93) (actual time=8.349..33.778 rows=500 loops=1) | | -> Index Scan using catalog_publisher_status_uniq on catalogs c (cost=0.29..2.51 rows=1 width=16) (actual time=0.016..0.019 rows=1 loops=1) | | Index Cond: ((publisher_id = '3b73167d-3ee3-4f09-a3af-c2989d76f04b'::uuid) AND (status = 'available'::catalog_status)) | | -> Index Scan using idx_books_publisher_catalog_status on books b (cost=1.81..55694.59 rows=1 width=109) (actual time=8.328..33.659 rows=500 loops=1) | | Index Cond: (catalog_id = c.id) | | Filter: ((isbn)::text = ANY ('{тут 500 isbn книг}'::text[])) | | Rows Removed by Filter: 2780 | |Planning Time: 35.097 ms | |Execution Time: 33.880 ms | +-------------------------------------------------------------------------------------------------------------------------------------------------------------+Разница между новым и старым запросом:
Было:
Planning Time: 40.434 ms
Execution Time: 78.272 msСтало:
Planning Time: 35.097 ms
Execution Time: 33.880 ms
С этого момента команда завела мониторы на долгие запросы в БД и стала заранее узнавать о проседающих запросах.
Настройка условий запроса, или IN vs JOIN
Первым монитор сработал на медленный запрос стоков, когда количество записей в таблице stocks достигло отметки в один миллион записей. После этого команда запланировала работы по оптимизации. Георгий проявил инициативу и забрал эту задачу себе. Согласно планировщику, проблема была в следующем запросе: он перестал укладываться в 100 мс в 98 % запросов:
SELECT s.id, s.book_isbn, s.quantity FROM stockrooms s WHERE s.publisher_id = :publisher_id AND s.book_isbn IN (:isbns);Георгий не первый раз занимался оптимизацией, поэтому первым делом выполнил EXPLAIN ANALYZE:
+------------------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Index Scan using stockrooms_publisher_book_uniq on stockrooms s (cost=0.56..846.94 rows=15 width=45) (actual time=1.237..19.237 rows=500 loops=1) | | Index Cond: ((publisher_id = '3b73167d-3ee3-4f09-a3af-c2989d76f04b'::uuid) AND ((book_isbn)::text = ANY ('{тут 500 isbn книг}'::text[]))) | |Planning Time: 43.230 ms | |Execution Time: 19.324 ms | +------------------------------------------------------------------------------------------------------------------------------------------------------------+Из анализа было ясно, что индексы есть и запрос в них попадает. Попытки найти другие, более выгодные по времени ответа индексы не увенчались успехом, поэтому Георгий продолжил поиски. Старшие коллеги подсказали, что можно попробовать заменить IN на JOIN с CTE, что Георгий и попытался сделать. Получился такой запрос:
WITH book_isbns AS (SELECT unnest(ARRAY [:book_isbns]) AS book_isbn) SELECT s.id, s.book_isbn, s.quantity FROM stockrooms s JOIN book_isbns b ON s.book_isbn = b.book_isbn WHERE s.publisher_id = :publisher_id;Прогон запроса на продовых данных показал значительное ускорение по сравнению с версией с IN, поэтому Георгий решил выполнить EXPLAIN ANALYZE для выяснения причин:
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |Nested Loop (cost=0.56..1392.82 rows=7 width=45) (actual time=0.115..6.434 rows=500 loops=1) | | -> ProjectSet (cost=0.00..2.52 rows=500 width=32) (actual time=0.002..0.058 rows=500 loops=1) | | -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1) | | -> Index Scan using stockrooms_publisher_book_uniq on stockrooms s (cost=0.56..2.77 rows=1 width=45) (actual time=0.012..0.012 rows=1 loops=500) | | Index Cond: ((publisher_id = '3b73167d-3ee3-4f09-a3af-c2989d76f04b'::uuid) AND ((book_isbn)::text = (unnest('{тут 500 isbn книг}'::text[])))) | |Planning Time: 2.385 ms | |Execution Time: 6.523 ms | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------+В случае с JOIN операции сканирования индекса значительно «дешевле», то есть индекс таблицы используется более эффективно.
Но дальнейшая практика показала, что это может работать не во всех случаях, и рекомендуется всегда проверять через EXPLAIN ANALYZE оба варианта, так как может быть и обратная ситуация.
В итоге, в нашем случае разница между новым и старым запросом составила:
Было:
Planning Time: 43.230 ms
Execution Time: 19.324 msСтало:
Planning Time: 2.385 ms
Execution Time: 6.523 ms
Накинем ресурсов на бэкенд
В следующем квартале команда опять столкнулась с проблемой медленного ответа. В этот раз дело было в одном из путей, точнее, в массовом поиске по множеству издателей и книг:
SELECT b.id, b.title, b.price AS price, b.isbn AS isbn, b.status AS status FROM books b JOIN catalogs c ON c.publisher_id IN (:publisher_ids) AND c.status = 'available' WHERE b.publisher_id IN (:publisher_ids) AND b.catalog_id = c.id AND b.status IN ('available', 'upcoming'); +-------------------------------------------------------------------------------------------------------------------------------------------------------+ |QUERY PLAN | +-------------------------------------------------------------------------------------------------------------------------------------------------------+ |Nested Loop (cost=0.84..13.93 rows=1 width=98) (actual time=0.056..15.414 rows=871 loops=1) | | -> Index Scan using catalog_publisher_status_uniq on catalogs c (cost=0.29..5.01 rows=2 width=16) (actual time=0.032..0.060 rows=2 loops=1) | | Index Cond: ((publisher_id = ANY ('{тут несколько id}'::uuid[])) AND (status = 'available'::catalog_status)) | | -> Index Scan using idx_books_publisher_catalog_status on books b (cost=0.56..4.45 rows=1 width=114) (actual time=0.022..7.619 rows=436 loops=2)| | Index Cond: ((publisher_id = ANY ('{тут несколько id}'::uuid[])) AND (catalog_id = c.id)) | | Filter: (status = ANY ('{available,upcoming}'::book_status[])) | | Rows Removed by Filter: 6180 | |Planning Time: 0.461 ms | |Execution Time: 15.496 ms | +-------------------------------------------------------------------------------------------------------------------------------------------------------+Команда долго думала и решила, что оптимальным вариантом будет распараллелить запрос на бэкенде, чтобы сохранить запрос простым и уменьшить объем покрываемых записей.
Запрос практически не изменили, только ограничили количество выбираемых publisher_id одним значением.
SELECT b.id, b.title, b.price AS price, b.isbn AS isbn, b.status AS status FROM books b JOIN catalogs c ON c.publisher_id = :publisher_id AND c.status = 'available' WHERE b.publisher_id = :publisher_id AND b.catalog_id = c.id AND b.status IN ('available', 'upcoming');Логично, что изменений в скорости выполнения запроса не последовало, а если оценивать с учетом расходов на передачу данных по сети, то даже потеряли немного времени. Но этот вариант ограничивает сценарий выполнения. И команда стала выполнять запрос в несколько потоков отдельно по каждому publisher_id, а время экономили на сопоставлении данных.
Пакет database/sql из стандартной библиотеки не позволял сканировать результирующие строки потокобезопасно, следовательно, на большем количестве строк и нескольких колонках в ответе можно было значительно потерять в операции scan. Пример:
rows, err := db.Query(` SELECT b.id, b.title, b.price AS price, b.isbn AS isbn, b.status AS status FROM books b JOIN catalogs c ON c.publisher_id = :publisher_id AND c.status = 'available' WHERE b.publisher_id = :publisher_id AND b.catalog_id = c.id AND b.status IN ('available', 'upcoming'); `) defer rows.Close() for rows.Next() { // тут 10k значений, в несколько горутин мы обойти результат не можем var id int var name string err := rows.Scan(&id, &name) // обработка строки }На бэкенде запрос стали запускать отдельно, по каждому publisher_id до четырёх запросов одновременно. Это позволило оставить запрос к БД простым, но усложнило логику поддержки на бэкенде. В результате этих изменений Георгию удалось срезать до 50 мс на сканировании больших каталогов из БД в ОП.
Заключение
Я постарался разобрать часть механик, которыми наша команда пользуется при разработке сервисов. Надеюсь, они вам пригодятся или помогут в рассуждениях. Как правило, оптимизация запросов — это задача, которую можно решить множеством способов, и знание дополнительных техник позволяет гибче подходить к решению.
Помимо описанных выше вариантов, механики можно реализовать и в других комбинациях. Например, денормализацию данных можно выполнять без изменениях структуры начальных таблиц через агрегацию данных в matview. А оптимизации ad-hoc будут свои для каждого конкретного сервиса и его бизнес-процесса. Попробуйте рассмотреть ваши сервисы на разных уровнях детализации, это поможет понять, где можно сэкономить.
А если вы любите и умеете оптимизировать базы данных, приходите в команду. У нас много подобных интересных задач.
Список литературы и полезные ссылки
-
https://explain.tensor.ru/plan/ — визуализация explain;
-
https://www.percona.com/blog/sql-optimizations-in-postgresql-in-vs-exists-vs-any-all-vs-join/ — сравнение разных подходов поиска по списку значений для PostgreSQL 10;
-
https://habr.com/ru/companies/postgrespro/articles/662021/ — оптимизации большого количества JOIN;
-
https://minervadb.xyz/selectivity-and-cardinality-estimations-in-postgresql/ — короткая статья про оценку кардинальности и селективности запросов;
-
https://www.postgresql.org/docs/current/planner-stats.html — статья про использование статистики в запросах и причем тут селективность;
-
https://www.postgresql.org/docs/current/row-estimation-examples.html — статья про оценку количества строк через статистику;
-
https://habr.com/ru/companies/exWargaming/articles/323354/ — иногда причина низкой скорости выполнения запросов являются ошибки, например, такие как дедлоки.
ссылка на оригинал статьи https://habr.com/ru/articles/940624/
Добавить комментарий