30 способів оптимізувати ваші SQL-запити - javascript.org.ua

1. Вибір відповідних індексів

Поради

Створюйте відповідні індекси (для одного стовпця, комбіновані індекси тощо) для стовпців, які часто використовуються в запитах.

Приклад

Проблемний SQL:

SELECT name FROM employees WHERE department_id = 10;

Оптимізація: Створіть індекс для department_id:

CREATE INDEX idx_department_id ON employees(department_id);

2. Уникайте використання SELECT *

Поради

Запитуйте лише необхідні стовпці, щоб зменшити обсяг повернених даних.

Приклад

Проблемний SQL:

SELECT * FROM employees WHERE department_id = 10;

Оптимізація: Запитуйте лише потрібні стовпці:

SELECT name FROM employees WHERE department_id = 10;

3. Використовуйте JOIN замість підзапитів

Поради

Підзапити зазвичай менш ефективні, ніж JOIN.

Приклад

Проблемний SQL:

SELECT name FROM employees WHERE department_id IN (SELECT id FROM departments WHERE location = 'New York');

Оптимізація: Використовуйте JOIN замість підзапиту:

SELECT e.name FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.location = 'New York';

4. Використовуйте EXPLAIN для аналізу запитів

Поради

Використовуйте EXPLAIN або EXPLAIN ANALYZE, щоб переглянути план виконання SQL-запитів і виявити проблеми з продуктивністю.

Приклад

EXPLAIN SELECT name FROM employees WHERE department_id = 10;

5. Уникайте непотрібних операцій ORDER BY

Поради

ORDER BY споживає значні ресурси, особливо для великих наборів даних. Використовуйте його лише коли сортування необхідне.

Приклад

Проблемний SQL:

SELECT name FROM employees WHERE department_id = 10 ORDER BY hire_date;

Оптимізація: Видаліть ORDER BY, якщо сортування не потрібне.

6. Оптимізуйте запити на пагінацію за допомогою LIMIT

Поради

Для пагінації використовуйте LIMIT. Для запитів з великими зсувами оптимізуйте їх за допомогою індексів або кешування.

Приклад

Проблемний SQL:

SELECT name FROM employees ORDER BY hire_date LIMIT 100000, 10;

Оптимізація: Використовуйте первинні ключі або індекси для покращення продуктивності пагінації:

SELECT name FROM employees WHERE id > 100000 ORDER BY hire_date LIMIT 10;

7. Уникайте використання функцій у WHERE клаузі

Поради

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

Приклад

Проблемний SQL:

SELECT name FROM employees WHERE YEAR(hire_date) = 2023;

Оптимізація: Використовуйте діапазонні запити замість цього:

SELECT name FROM employees WHERE hire_date BETWEEN '2023-01-01' AND '2023-12-31';

8. Вибір правильного порядку для комбінованих індексів

Поради

У комбінованих індексах розміщуйте стовпець з більшою селективністю першим.

Приклад

Для запиту:

SELECT * FROM employees WHERE department_id = 10 AND status = 'active';

Створіть індекс з status першим для кращої селективності:

CREATE INDEX idx_status_department ON employees(status, department_id);

9. Використовуйте пакетні вставки замість одноразових вставок

Поради

Пакетні вставки значно зменшують витрати на I/O та блокування.

Приклад

Проблемний SQL: Вставка записів по одному:

INSERT INTO employees (name, department_id) VALUES ('John', 10);

Оптимізація: Використовуйте пакетні вставки:

INSERT INTO employees (name, department_id) VALUES ('John', 10), ('Alice', 20), ('Bob', 30);

10. Уникайте використання NOT IN

Поради

NOT IN має погану продуктивність; замініть його на NOT EXISTS або LEFT JOIN.

Приклад

Проблемний SQL:

SELECT name FROM employees WHERE department_id NOT IN (SELECT id FROM departments);

Оптимізація: Використовуйте LEFT JOIN:

SELECT e.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id WHERE d.id IS NULL;

11. Уникайте зайвого використання DISTINCT

Поради

Використовуйте DISTINCT тільки тоді, коли необхідно видалити дублікати даних.

Приклад

Проблемний SQL:

SELECT DISTINCT name FROM employees WHERE department_id = 10;

Оптимізація: Видаліть DISTINCT, якщо дублікати не потрібні.

12. 12. Використовуйте відповідні типи об’єднань (Join Types)

Поради

Використовуйте INNER JOIN, якщо не потрібно отримувати всі дані. Уникайте LEFT JOIN або RIGHT JOIN без потреби.

Приклад

Проблемний SQL:

SELECT e.name, d.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

Оптимізація: Використовуйте INNER JOIN:

SELECT e.name, d.name FROM employees e INNER JOIN departments d ON e.department_id = d.id;

13. Використовуйте партиціювання таблиць

Поради

Партиціонуйте великі таблиці для покращення продуктивності запитів.

Приклад

CREATE TABLE employees (  
 id INT,  
 name VARCHAR(50),  
 hire_date DATE  
)  
PARTITION BY RANGE (YEAR(hire_date)) (  
 PARTITION p2020 VALUES LESS THAN (2021),  
 PARTITION p2021 VALUES LESS THAN (2022)  
);

14. Оптимізуйте запити з GROUP BY

Поради

Оптимізуйте запити з GROUP BY за допомогою індексів.

Приклад

SELECT department_id, COUNT(*) FROM employees GROUP BY department_id;

15. Оптимізуйте використання IN

Поради

Для великих операцій з IN зберігайте дані в тимчасових таблицях і використовуйте JOIN замість цього.

Приклад

Проблемний SQL:

SELECT name FROM employees WHERE department_id IN (1, 2, 3, 4, 5);

Оптимізація: Зберігайте ID в тимчасовій таблиці:

CREATE TEMPORARY TABLE temp_ids (id INT);  
INSERT INTO temp_ids (id) VALUES (1), (2), (3), (4), (5);  
SELECT e.name FROM employees e JOIN temp_ids t ON e.department_id = t.id;

SELECT e.name, d.name  
FROM employees e JOIN departments d  
ON e.department_id = d.id  
WHERE e.location = 'New York';

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

22. Мульти-стовпцеві індекси та об’єднання індексів

Поради

Коли запитуєте кілька стовпців, використовуйте комбіновані індекси, де це можливо. Якщо ні, база даних може спробувати об’єднати індекси.

Приклад

Проблемний SQL:

SELECT * FROM orders WHERE customer_id = 10 AND product_id = 5;

Оптимізація: Комбінуйте індекси на customer_id і product_id для кращої продуктивності. Використовуйте EXPLAIN, щоб перевірити, чи використовується об’єднання індексів.

23. Оптимізуйте багатовимірний аналіз з CUBE та ROLLUP

Поради

Використовуйте CUBE та ROLLUP для багатовимірної агрегації, що дозволяє зменшити кількість запитів з GROUP BY.

Приклад

Проблемний SQL: Кілька запитів з GROUP BY.

SELECT department_id, SUM(sales) FROM sales_data GROUP BY department_id;  
SELECT region, SUM(sales) FROM sales_data GROUP BY region;

Оптимізація: Використовуйте ROLLUP для агрегації на кількох рівнях:

SELECT department_id, region, SUM(sales)  
FROM sales_data  
GROUP BY department_id, region WITH ROLLUP;

24. 24. Використовуйте віконні функції для складних запитів аналізу

Поради

Віконні функції (наприклад, ROW_NUMBER(), RANK(), LAG(), LEAD()) спрощують складний аналіз, зменшуючи потребу в самопоєднаннях (self-joins) або підзапитах (subqueries).

Приклад

Проблемний SQL: Самопоєднання для отримання попереднього запису.

SELECT a.*,  
 (SELECT sales FROM sales_data b WHERE b.id = a.id - 1) AS previous_sales  
FROM sales_data a;

Оптимізація: Використовуйте віконну функцію:

SELECT id, sales, LAG(sales, 1) OVER (ORDER BY id) AS previous_sales  
FROM sales_data;

25. Партиціонування для великих таблиць

Поради

Використовуйте партиціонування для обмеження діапазону сканування даних у дуже великих таблицях.

Приклад

Проблемний SQL:

SELECT * FROM transactions WHERE transaction_date BETWEEN '2023-01-01' AND '2023-01-31';

Оптимізація: Партиціонуйте таблицю за датою та використовуйте партиціювання:

CREATE TABLE transactions (  
 id INT,  
 amount DECIMAL(10, 2),  
 transaction_date DATE  
)  
PARTITION BY RANGE (YEAR(transaction_date)) (  
 PARTITION p2023 VALUES LESS THAN (2024)  
);

26. Мінімізуйте використання тимчасових таблиць

Поради

Зменшуйте використання тимчасових таблиць у складних запитах, оскільки вони збільшують I/O диск і впливають на продуктивність.

Приклад

Проблемний SQL: Використання тимчасової таблиці для зберігання проміжних результатів.

CREATE TEMPORARY TABLE temp_sales AS  
SELECT department_id, SUM(sales) FROM sales_data GROUP BY department_id;

Оптимізація: Використовуйте підзапити (subqueries) або загальні таблиці виразів (CTEs):

WITH temp_sales AS (  
 SELECT department_id, SUM(sales) FROM sales_data GROUP BY department_id  
)  
SELECT * FROM temp_sales;

27. Оптимізуйте паралельні запити

Поради

Використовуйте паралельне виконання запитів для великих наборів даних для підвищення ефективності.

Приклад

Проблемний SQL: Велике сканування даних без паралелізму.

SELECT SUM(sales) FROM sales_data;

Оптимізація: Увімкніть паралельне виконання запитів:

ALTER SESSION ENABLE PARALLEL QUERY;  
SELECT /*+ PARALLEL(sales_data, 4) */ SUM(sales) FROM sales_data;

28. Прискорення складних запитів за допомогою матеріалізованих views

Поради

Для складних агрегаційних запитів використовуйте матеріалізовані views для зберігання попередньо обчислених результатів.

Приклад

Проблемний SQL: Складний агрегаційний запит із проблемами продуктивності.

SELECT department_id, SUM(sales) FROM sales_data GROUP BY department_id;

Оптимізація: Створіть матеріалізований view:

CREATE MATERIALIZED VIEW mv_sales_data AS  
SELECT department_id, SUM(sales) AS total_sales FROM sales_data GROUP BY department_id;

29. Уникайте блокувань для оптимізації одночасних запитів

Поради

У середовищах з високою одночасністю (high concurrency) уникайте блокувань таблиць або рядків за допомогою відповідних механізмів блокування.

Приклад

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

SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;

Оптимізація: Блокуйте лише конкретні рядки:

SELECT * FROM orders WHERE order_id = 123 FOR UPDATE;

30. Оптимізуйте транзакції, зменшуючи час блокування

Поради

Для довготривалих транзакцій мінімізуйте час блокування та обмежте область блокування.

Приклад

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

BEGIN;  
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;  
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;  
COMMIT;

Оптимізація: Розділіть на менші транзакції або зменшіть час блокування:

BEGIN;  
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;  
COMMIT;

BEGIN;  
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;  
COMMIT;

Оптимізація SQL-запитів — це і мистецтво, і наука.

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

Кожна база даних унікальна — те, що працює в одному випадку, може не працювати в іншому.

Завжди аналізуйте, тестуйте та вдосконалюйте свої запити для побудови власної оптимізації.

Ми — Leapcell, ваш найкращий вибір для розгортання бекенд-проєктів у хмарі.

Leapcell — це платформа нового покоління без серверів для веб-хостингу, асинхронних завдань та Redis:

Підтримка кількох мов програмування

Розробляйте на Node.js, Python, Go або Rust.

Безкоштовне розгортання необмежених проєктів

платіть лише за використання — без запитів, без витрат.

Неймовірна ефективність витрат

Оплата за фактичне використання без додаткових витрат на простої.
Приклад: $25 підтримує 6,94 млн запитів при середньому часі відповіді 60 мс.

Удосконалений досвід розробника

Інтуїтивно зрозумілий інтерфейс для легкого налаштування.
Повністю автоматизовані CI/CD пайплайни та інтеграція GitOps.
Метрики та логи в реальному часі для отримання корисних відомостей.

Легке масштабування та висока продуктивність

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

Дізнайтесь більше в Документації!

Слідкуйте за нами в X: @LeapcellHQ

Перекладено з: 30 Ways to Optimize Your SQL