.NET Разработчик: “День 2752. #BestPractices #SQL Как оптимизировать SQL-запросы. Части 4…”

День 2752. #BestPractices #SQL
Как оптимизировать 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