Оволодіння передовим SQL: Продуктивність та повторне використання

Як ваші набори даних зростають, а запити стають складнішими, стають важливими передові можливості SQL. У цьому блозі ми розглянемо оптимізацію продуктивності, повторно використовувані структури SQL та передові техніки запитів, які піднімуть ваші навички роботи з базами даних.

Індекси: Прискорення запитів

Індекси в SQL подібні до індексу в книзі — вони допомагають швидко знаходити конкретну інформацію. Без індексів базі даних доводиться сканувати кожен рядок таблиці для знаходження співпадінь, що може зайняти багато часу при великих наборах даних.

Створення індексу:

CREATE INDEX idx_lastname ON Employees(LastName);

Як працюють індекси:

• Індекси B-дерева: Найпоширеніший тип, використовується для впорядкованих пошуків і діапазонних запитів.
• Індекси повнотекстового пошуку: Оптимізовані для пошуку в великих текстових полях.

Найкращі практики:

• Індексуйте стовпці, які часто використовуються в умовах WHERE, JOIN або ORDER BY.
• Уникайте індексації кожного стовпця — це збільшує обсяг зберігання та уповільнює операції запису.

Аналіз продуктивності запиту за допомогою EXPLAIN:
Використовуйте ключове слово EXPLAIN, щоб побачити, як буде виконуватись ваш запит і які індекси використовуються.

EXPLAIN SELECT * FROM Employees WHERE LastName = 'Smith';

Подання: Спрощення складних запитів

Подання — це віртуальна таблиця, створена з запиту. Воно спрощує повторювану або складну логіку SQL, дозволяючи інкапсулювати її в одну структуру.

Створення подання:

CREATE VIEW HighEarningManagers AS   
SELECT FirstName, LastName, Salary   
FROM Employees   
WHERE Position = 'Manager' AND Salary > 75000;

Запит до подання:

SELECT * FROM HighEarningManagers;

Переваги подань:

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

Транзакції: Забезпечення цілісності даних

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

Властивості ACID транзакцій:

• Атомарність: Усі операції в транзакції успішно виконуються або не виконуються разом.
• Цілісність: База даних залишається в дійсному стані до та після транзакції.
• Ізоляція: Транзакції виконуються незалежно одна від одної.
• Тривалість: Зміни є постійними після коміту транзакції.

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

START TRANSACTION;   
UPDATE Accounts SET Balance = Balance - 1000 WHERE AccountID = 1;   
UPDATE Accounts SET Balance = Balance + 1000 WHERE AccountID = 2;   
COMMIT;

Скасування транзакції:
Якщо щось пішло не так, використовуйте ROLLBACK, щоб скасувати зміни.

ROLLBACK;

Збережені процедури та функції: Написання багаторазового SQL коду

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

Приклад збереженої процедури:

CREATE PROCEDURE GetEmployeesByDepartment(deptName VARCHAR(50))   
BEGIN   
SELECT * FROM Employees   
WHERE Department = deptName;   
END;

Виклик процедури:

CALL GetEmployeesByDepartment('Finance');

Функції:
Функції повертають значення та можуть використовуватися в SQL виразах.

CREATE FUNCTION CalculateBonus(salary DECIMAL(10, 2))   
RETURNS DECIMAL(10, 2)   
BEGIN   
RETURN salary * 0.10;   
END;

Використання функції:

SELECT EmployeeID, CalculateBonus(Salary) AS Bonus FROM Employees;

Загальні вирази таблиць (CTEs): Спрощення логіки запитів

CTEs надають спосіб структурувати запити для кращої зручності читання та повторного використання.

Приклад базового CTE:

WITH HighEarners AS  
(   
SELECT EmployeeID, FirstName, Salary   
FROM Employees   
WHERE Salary > 75000   
)   
SELECT * FROM HighEarners;

Рекурсивний CTE:
Рекурсивні CTE використовуються для ієрархічних або деревоподібних даних.

WITH RECURSIVE EmployeeHierarchy AS   
(   
SELECT EmployeeID, ManagerID, FirstName   
FROM Employees   
WHERE ManagerID IS NULL   
UNION ALL   
SELECT e.EmployeeID, e.ManagerID, e.FirstName   
FROM Employees e   
INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID   
)   
SELECT * FROM EmployeeHierarchy;

Завершення

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

У фінальному блозі ми зосередимося на застосуванні ваших знань SQL для вирішення реальних проблем та проектування ефективних баз даних.

Перекладено з: Mastering Advanced SQL: Performance and Reusability