Как оптимизировать SQL-запросы. Части 4-5
Части 1, 2, 3
Часть IV. Разрабатывайте схему для чтения
Некоторые запросы работают медленно, независимо от способа их написания, потому что они постоянно пересчитывают один и тот же ресурсоёмкий результат. Решение заключается в изменении структуры данных.
1. Нормализуйте данные с умом
Нормализация поддерживает чистоту и согласованность данных и является правильным вариантом по умолчанию. Однако полностью нормализованные данные могут медленно читаться, когда часто выполняемый запрос должен соединять и агрегировать одни и те же таблицы при каждом запросе. Для путей с интенсивным чтением допустимо денормализовывать данные: предварительно агрегировать данные и сохранять их.
-- Сохраняем агрегированные данные в сводной таблице
CREATE TABLE sales_summary AS
SELECT product_id, SUM(quantity) AS total_sold
FROM order_details
GROUP BY product_id;
Теперь чтение представляет собой простой поиск, а не агрегацию в реальном времени по всей таблице
order_details. Компромисс заключается в необходимости синхронизации сводной таблицы — её обновления по расписанию или при изменении исходных данных. Денормализацию следует проводить целенаправленно, для конкретных часто используемых запросов, а не повсеместно.2. Использование материализованных представлений
Материализованное представление физически хранит результат запроса, поэтому чтение обращается к предварительно вычисленным строкам, а не пересчитывает их. Это вариант сводной таблицы, описанной выше:
-- Храним агрегированные данные
CREATE MATERIALIZED VIEW mv_total_sales AS
SELECT product_id, SUM(quantity) AS total_qty
FROM order_details
GROUP BY product_id;
-- Уникальный индекс позволяет представлению обновляться без блокирования чтения
CREATE UNIQUE INDEX idx_mv_total_sales_product
ON mv_total_sales (product_id);
-- Обновление по расписанию; читатели будут получать старые данные до завершения обновления
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_total_sales;
Запросы к
mv_total_sales выполняются быстро, потому что агрегация уже была выполнена. Данные актуальны только на момент последнего обновления, поэтому используйте материализованные представления для ресурсоёмких агрегаций, которые могут допускать небольшое устаревание — панели мониторинга, отчёты и таблицы лидеров.Часть V. Повышение эффективности операций записи и транзакций
Медленная запись и длительные транзакции вызывают конфликты блокировок, заставляя остальные запросы ждать.
1. Пакетная обработка больших операций
Выполнение оператора для каждой строки приводит к перегрузке БД запросами и транзакционными издержками. Но один оператор, затрагивающий миллионы строк, также представляет проблему — он удерживает блокировки в течение длительного времени и может привести к переполнению журнала предварительной записи (WAL).
Промежуточным решением является пакетная обработка: обработка фиксированного фрагмента за раз. Следующий запрос перемещает строки в архивную таблицу по 1000 за раз, удаляя каждый фрагмент после его копирования:
WITH batch AS (
DELETE FROM source_table
WHERE ctid IN (
SELECT ctid
FROM source_table
WHERE processed = false
LIMIT 1000
)
RETURNING col1, col2
)
INSERT INTO archive_table (col1, col2)
SELECT col1, col2 FROM batch;
Запустите его в цикле, пока он не станет затрагивать 0 строк. Каждая партия фиксируется быстро, удерживает мало блокировок и поддерживает отзывчивость системы во время выполнения основной задачи.
2. Сокращайте транзакции
Транзакция удерживает блокировки до момента фиксации, и все другие запросы, которым нужны эти строки, должны ждать. Чем дольше транзакция остаётся открытой, тем больше конкуренции она создаёт. Держите транзакцию открытой только для операций записи, а медленные операции — вызовы API, файловый ввод-вывод, ресурсоёмкие вычисления — выполняйте вне её:
BEGIN;
UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;
INSERT INTO transactions (account_id, amount)
VALUES (1, -100);
COMMIT;
Эта транзакция открывается, выполняет две связанные операции записи и немедленно фиксируется. Никогда не оставляйте транзакцию открытой, ожидая ввода пользователя или ответа по сети.
Окончание следует…
Источник: https://antondevtips.com/blog/how-to-optimize-sql-queries-20-proven-best-practices
