(Простий) шлях з SQL — #6: Оволодіння MERGE для операцій з великими обсягами даних

Коли працюєш з великими обсягами даних у SQL, часто виникають ситуації, коли потрібно вирішити, чи оновлювати існуючі записи, чи додавати нові. Команда MERGE надає ефективний спосіб вирішення цього завдання, поєднуючи функціональність команд INSERT та UPDATE в одній операції.

Однак, продуктивність може стати викликом у таких випадках. Непотрібні сканування індексів, надмірне блокування або великомасштабні міграції даних можуть значно уповільнити процес. У цій статті ми розглянемо, як:

• Використовувати MERGE для операцій з великими обсягами даних.
• Застосовувати тимчасові таблиці для покращення продуктивності.
• Оптимізувати оновлення та вставки в основну таблицю за допомогою первинних ключів для досягнення максимальної ефективності.

Об’єднання даних великого обсягу

Уявіть, що вам потрібно інтегрувати мільйони рядків у таблицю продукції (target_table) з тимчасової таблиці (temp_table). Ваша мета полягає в тому, щоб:

1. Оновити існуючі записи, де знайдений збіг за первинним ключем.
   2.
   Вставити нові записи, де немає збігу.

Основна операція MERGE

MERGE INTO target_table t  
USING temp_table s  
ON (t.primary_key = s.primary_key)  
WHEN MATCHED THEN  
 UPDATE SET   
 t.column1 = s.column1,  
 t.column2 = s.column2  
WHEN NOT MATCHED THEN  
 INSERT (primary_key, column1, column2)  
 VALUES (s.primary_key, s.column1, s.column2);

Виклики та проблеми з продуктивністю

Непотрібні сканування індексів: операції MERGE можуть ініціювати непотрібні сканування на індексованих стовпцях, особливо при обробці великих наборів даних. Рішення: використовуйте тимчасову таблицю для попередньої обробки даних і обмеження розміру набору даних.

Блокування таблиць: Великомасштабні оновлення можуть спричинити надмірне блокування, що впливає на інші запити. Рішення: розбийте операцію на менші пакети або використовуйте розподілення.

Навантаження транзакцій: Поєднання оновлень та вставок в одній транзакції може призвести до високого навантаження на вхід/вихід та використання пам'яті.
Рішення: Оптимізуйте логіку MERGE за допомогою вибіркового фільтрування та переконайтеся, що індекси відповідають операціям.

Оптимізований підхід з тимчасовими таблицями

1. Розміщення даних у тимчасовій таблиці.
2. Виконання оновлень або вставок у цільову таблицю, використовуючи первинний ключ для максимальної продуктивності.

Крок 1: Завантаження даних у тимчасову таблицю

CREATE TEMPORARY TABLE temp_table AS  
SELECT * FROM staging_area;

Крок 2: Виконання оновлень пакетами

UPDATE target_table t  
SET   
 t.column1 = s.column1,  
 t.column2 = s.column2  
FROM temp_table s  
WHERE t.primary_key = s.primary_key;

Крок 3: Вставка нових записів

INSERT INTO target_table (primary_key, column1, column2)  
SELECT s.primary_key, s.column1, s.column2  
FROM temp_table s  
LEFT JOIN target_table t  
ON s.primary_key = t.primary_key  
WHERE t.primary_key IS NULL;

Чому використовувати тимчасові таблиці?

Покращена продуктивність: Тимчасові таблиці зменшують кількість сканувань індексів і блокування в основній таблиці.
Операції з тимчасовими таблицями виконуються швидше, оскільки вони зазвичай не мають індексів.

Ізоляція: Розміщення даних дозволяє попередньо обробити і перевірити їх перед об'єднанням з цільовою таблицею.

Гнучкість: Тимчасові таблиці можна використовувати для застосування фільтрів або трансформацій, що спрощують основну операцію MERGE.

Практичне використання: Об'єднання даних в інформаційні панелі

Операції MERGE особливо корисні в таких сценаріях, як:

• Оновлення живих інформаційних панелей свіжими даними при збереженні історичних записів.
• Синхронізація даних між виробничим і тестовим середовищами.
• Виконання операцій ETL (Extract, Transform, Load) в масштабах великого обсягу з мінімальним простоєм.

Команда MERGE є потужним інструментом для обробки операцій з великими обсягами даних, але її продуктивність залежить значною мірою від того, як ви структурируєте свої запити та дані.
Використовуючи тимчасові таблиці, розбиваючи оновлення на менші пакети та оптимізуючи використання індексів, ви можете досягти повної продуктивності навіть з мільйонами рядків.

У майбутніх статтях ми глибше розглянемо передові техніки MERGE, включаючи обробку конфліктів та роботу з розділеними таблицями для ще більшої масштабованості.

Перекладено з: The (Uncomplicated) Journey with SQL — #6: Mastering MERGE for High-Volume Data Operations