---
title: "Реляційні SQL бази даних"
url: "https://romankryvolapov.com/uk/relational-sql-databases/"
description: "Синтаксис SQL, VIEW та індекси, JOIN і CTE, блокування при конкурентному доступі та читання плану запиту в PostgreSQL, MySQL і Oracle."
language: uk
updated: 2026-01-21
---
## Який синтаксис запиту в SQL
У SQL існує безліч ключових слів, які використовуються для створення, зміни, керування та отримання даних з баз даних. Наведу основні категорії та ключові слова, що використовуються в SQL:
**DML (Data Manipulation Language) - Мова маніпуляції даними:** \
Ключові слова для роботи з даними, такими як вставка, оновлення та видалення даних.
**SELECT:** \
вибирає дані з таблиці.
```sql
SELECT *
FROM users
WHERE age > 30;
SELECT COUNT(*)
FROM users
WHERE age > 30;
```
**INSERT:** \
додає нові записи до таблиці.
```sql
INSERT
INTO users (name, age)
VALUES ('John', 25);
```
**UPDATE:** \
оновлює наявні записи в таблиці.
```sql
UPDATE users
SET age = 26
WHERE name = 'John';
```
**DELETE:** \
видаляє записи з таблиці.
```sql
DELETE
FROM users
WHERE age < 18;
```
**MERGE:** \
об'єднує дані з двох таблиць на основі умов.
```sql
MERGE INTO employees e
USING employees_updates eu
ON (e.employee_id = eu.employee_id)
WHEN MATCHED THEN
UPDATE SET e.salary = eu.salary
WHEN NOT MATCHED THEN
INSERT (employee_id, salary) VALUES (eu.employee_id, eu.salary);
```
**CALL:** \
викликає збережену процедуру.
```sql
CALL calculate_salary(12345);
```
**DDL (Data Definition Language) — Мова визначення даних:**\
Ключові слова для створення та модифікації структури бази даних (таблиць, індексів та інших об'єктів).
**CREATE:** \
створює нові об'єкти бази даних (таблиці, індекси тощо).
```sql
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10, 2)
);
```
**ALTER:** \
змінює структуру наявних об'єктів бази даних.
```sql
ALTER
TABLE products
ADD COLUMN description TEXT;
```
**DROP:** \
видаляє об'єкти бази даних (таблиці, індекси тощо).
```sql
DROP TABLE products;
```
**TRUNCATE:** \
видаляє всі рядки з таблиці, але не видаляє саму таблицю.
```sql
TRUNCATE TABLE users;
```
**COMMENT:** \
додає коментарі до об'єктів бази даних.
```sql
COMMENT
ON TABLE products
IS 'Table storing product details';
```
**RENAME:** \
перейменовує об'єкт бази даних.
```sql
RENAME
TABLE old_products
TO new_products;
```
**DCL (Data Control Language) — Мова керування доступом:**\
Ключові слова для керування правами доступу до даних.
**GRANT:** \
надає користувачам або ролям права на об'єкти бази даних.
```sql
GRANT
SELECT, INSERT
ON orders
TO user_admin;
```
**REVOKE:** \
відкликає права на об'єкти бази даних у користувачів або ролей.
```sql
REVOKE
INSERT ON orders
FROM user_admin;
```
**TCL (Transaction Control Language) — Мова керування транзакціями:**\
Ключові слова для керування транзакціями в базі даних.
**BEGIN TRANSACTION:** \
починає транзакцію.
```sql
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;
```
**COMMIT:** \
зберігає зміни, зроблені в межах поточної транзакції.
```sql
BEGIN TRANSACTION;
INSERT INTO orders (order_id, customer_id, order_total) VALUES (101, 5, 250.00);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (101, 20, 2);
COMMIT;
```
**ROLLBACK:** \
скасовує зміни, зроблені в межах поточної транзакції.
```sql
BEGIN TRANSACTION;
DELETE FROM employees WHERE employee_id = 45;
UPDATE departments SET manager_id = NULL WHERE manager_id = 45;
ROLLBACK;
```
**SAVEPOINT:** \
створює точку відновлення в транзакції.
```sql
BEGIN TRANSACTION;
UPDATE products SET price = price * 0.9 WHERE category = 'Electronics';
SAVEPOINT discount_applied;
DELETE FROM products WHERE discontinued = true;
ROLLBACK TO discount_applied;
COMMIT;
```
**SET TRANSACTION:** \
встановлює параметри для поточної транзакції.
```sql
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM sales WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31';
COMMIT;
```
**Ключові слова для керування даними (Constraints):**\
Ключові слова, що використовуються для задання обмежень на дані в таблицях.
**PRIMARY KEY:** \
визначає унікальний ідентифікатор для кожного рядка таблиці.
```sql
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
);
```
**FOREIGN KEY:** \
встановлює зв'язок між двома таблицями.
```sql
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
```
**UNIQUE:** \
вимагає, щоб усі значення в стовпці або групі стовпців були унікальними.
```sql
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(50) UNIQUE,
email VARCHAR(100) UNIQUE
);
```
**NOT NULL:** \
забороняє використання порожніх значень для цього стовпця.
```sql
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
```
**CHECK:** \
визначає умову, яку мають виконувати дані.
```sql
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
salary DECIMAL(10, 2) CHECK (salary > 0),
age INT CHECK (age >= 18)
);
```
**DEFAULT:** \
встановлює значення за замовчуванням для стовпця.
```sql
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
status VARCHAR(20) DEFAULT 'PENDING'
);
```
**Ключові слова для роботи з умовами:**\
Ключові слова, які використовуються для фільтрації, сортування та агрегування даних.
**WHERE:** \
фільтрує рядки на основі умови.
```sql
SELECT *
FROM employees
WHERE department = 'Sales'
AND salary > 50000;
```
**GROUP BY:** \
групує рядки за значеннями одного або кількох стовпців.
```sql
SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department;
```
**HAVING:** \
фільтрує рядки після групування.
```sql
SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;
```
**ORDER BY:** \
сортує результат за одним або кількома стовпцями.
```sql
SELECT first_name, last_name, hire_date
FROM employees
ORDER BY hire_date DESC;
```
**DISTINCT:** \
повертає унікальні значення.
```sql
SELECT DISTINCT country
FROM customers;
```
**LIMIT:** \
обмежує кількість рядків, що повертаються (у деяких СУБД, наприклад MySQL).
```sql
SELECT * FROM products
ORDER BY price DESC
LIMIT 10;
```
**OFFSET:** \
визначає, з якого рядка почати виведення результату.
```sql
SELECT * FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 10;
```
**Ключові слова для роботи з об'єднанням таблиць:**\
Ключові слова для об'єднання даних з кількох таблиць.
**JOIN:** \
об'єднує рядки з двох або більше таблиць на основі відношення між ними.
```sql
SELECT o.order_id, c.first_name, c.last_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
```
**INNER JOIN:** \
повертає рядки зі збіжними значеннями в обох таблицях.
```sql
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;
```
**LEFT JOIN (або LEFT OUTER JOIN):** \
повертає всі рядки з лівої таблиці, навіть якщо в правій таблиці немає збігів.
```sql
SELECT c.first_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
```
**RIGHT JOIN (або RIGHT OUTER JOIN):** \
повертає всі рядки з правої таблиці, навіть якщо в лівій таблиці немає збігів.
```sql
SELECT o.order_id, c.first_name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;
```
**FULL JOIN (або FULL OUTER JOIN):** \
повертає всі рядки з обох таблиць із заповненням значень NULL, якщо немає збігів.
```sql
SELECT a.account_id, t.transaction_id
FROM accounts a
FULL JOIN transactions t ON a.account_id = t.account_id;
```
**CROSS JOIN:** \
повертає декартів добуток двох таблиць (кожен рядок з першої таблиці об'єднується з кожним рядком з другої таблиці).
```sql
SELECT p.product_name, s.store_name
FROM products p
CROSS JOIN stores s;
```
**SELF JOIN:** \
з'єднує таблицю саму з собою.
```sql
SELECT e1.first_name AS employee, e2.first_name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;
```
**Ключові слова для керування послідовностями та індексами:**\
Ключові слова для створення та керування послідовностями та індексами.
**CREATE INDEX:** \
створює індекс для стовпців таблиці для підвищення продуктивності запитів.
```sql
CREATE INDEX idx_last_name ON employees(last_name);
```
**CREATE SEQUENCE:** \
створює послідовність для генерації унікальних чисел.
```sql
CREATE SEQUENCE order_seq
START WITH 1000
INCREMENT BY 1;
```
**DROP INDEX:** \
видаляє індекс.
```sql
DROP INDEX idx_last_name;
// для деяких СУБД, наприклад PostgreSQL
DROP INDEX IF EXISTS idx_last_name;
// у MySQL, індекс видаляється інакше
ALTER TABLE employees DROP INDEX idx_last_name;
```
**DROP SEQUENCE:** \
видаляє послідовність.
```sql
DROP SEQUENCE order_seq;
```
**Ключові слова для роботи з представленнями (views):**\
Ключові слова для створення та керування представленнями.
**CREATE VIEW:** \
створює представлення.
```sql
CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE status = 'ACTIVE';
```
**DROP VIEW:** \
видаляє представлення.
```sql
DROP VIEW active_customers;
```
**ALTER VIEW:** \
змінює наявне представлення.
```sql
ALTER VIEW active_customers AS
SELECT customer_id, first_name, last_name, email, phone
FROM customers
WHERE status = 'ACTIVE';
```
**Ключові слова для роботи зі збереженими процедурами та тригерами:**\
Ключові слова для створення та керування збереженими процедурами та тригерами.
**CREATE PROCEDURE:** \
створює збережену процедуру.
```sql
CREATE PROCEDURE increase_salary(IN emp_id INT, IN percent DECIMAL)
BEGIN
UPDATE employees
SET salary = salary + (salary * percent / 100)
WHERE employee_id = emp_id;
END;
```
**CREATE TRIGGER:** \
створює тригер, який автоматично виконується при настанні певної події (наприклад, INSERT, UPDATE, DELETE).
```sql
CREATE TRIGGER set_created_at
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = CURRENT_TIMESTAMP;
```
**EXECUTE:** \
виконує збережену процедуру.
```sql
CALL increase_salary(101, 10);
```
**DROP PROCEDURE:** \
видаляє збережену процедуру.
```sql
DROP PROCEDURE increase_salary;
```
**DROP TRIGGER:** \
видаляє тригер.
```sql
DROP TRIGGER set_created_at;
```
**Функції агрегації та аналітики:**\
Ключові слова для агрегування та аналізу даних.
**COUNT():** \
повертає кількість рядків.
```sql
SELECT COUNT(*) AS total_employees
FROM employees;
```
**SUM():** \
повертає суму значень.
```sql
SELECT SUM(order_total) AS total_revenue
FROM orders
WHERE order_date >= '2024-01-01';
```
**AVG():** \
повертає середнє значення.
```sql
SELECT AVG(salary) AS average_salary
FROM employees
WHERE department = 'IT';
```
**MIN():** \
повертає мінімальне значення.
```sql
SELECT MIN(price) AS cheapest_product
FROM products;
```
**MAX():** \
повертає максимальне значення.
```sql
SELECT MAX(price) AS most_expensive_product
FROM products;
```
**Інші ключові слова та оператори:**
**EXISTS:** \
перевіряє, чи існують рядки, що відповідають умові.
```sql
SELECT first_name, last_name
FROM customers
WHERE EXISTS (
SELECT 1 FROM orders
WHERE orders.customer_id = customers.customer_id
);
```
**IN:** \
перевіряє, чи міститься значення в списку значень.
```sql
SELECT *
FROM products
WHERE category_id IN (1, 3, 5);
```
**BETWEEN:** \
перевіряє, чи перебуває значення в певному діапазоні.
```sql
SELECT *
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';
```
**LIKE:** \
шукає рядкові дані за шаблоном.
```sql
SELECT *
FROM users
WHERE email LIKE '%@example.com';
```
**IS NULL:** \
перевіряє, чи містить значення NULL.
```sql
SELECT *
FROM employees
WHERE manager_id IS NULL;
```
**CAST():** \
перетворює одне значення на інший тип даних.
```sql
SELECT CAST(order_total AS INT) AS total_as_integer
FROM orders;
```
**COALESCE():** \
повертає перше ненульове значення в списку.
```sql
SELECT COALESCE(phone, 'No phone') AS contact_number
FROM customers;
```
**CASE:** \
повертає значення на основі умов (аналог оператора if-else).
```sql
SELECT first_name, last_name,
CASE
WHEN salary >= 100000 THEN 'High'
WHEN salary >= 50000 THEN 'Medium'
ELSE 'Low'
END AS salary_level
FROM employees;
```
**WITH:** \
використовується для створення тимчасових результатів у запиті (CTE — Common Table Expression).
```sql
WITH high_salary_employees AS (
SELECT * FROM employees WHERE salary > 80000
)
SELECT department, COUNT(*)
FROM high_salary_employees
GROUP BY department;
```
**UNION:** \
використовується для об'єднання результатів двох або більше запитів. Він дозволяє об'єднувати рядки з кількох SELECT запитів в один набір результатів. При цьому рядки з різних запитів не повинні дублюватися, якщо явно не використовується оператор ALL
```sql
SELECT first_name, email FROM customers
UNION
SELECT name, email FROM newsletter_subscribers;
```
Це ключові слова, які найчастіше зустрічаються в SQL. Вони використовуються для роботи з даними та керування структурою бази даних. Кожна СУБД може мати свої унікальні ключові слова, але перелічені вище — це стандартні команди SQL.
## Що таке VIEW (представлення) в SQL
VIEW (представлення) в SQL — це віртуальна таблиця, яка формується на основі результату запиту. Представлення не зберігають дані фізично, вони лише зберігають SQL-запит, який виконується щоразу при зверненні до представлення. Це зручний інструмент для спрощення складних запитів, забезпечення безпеки та підвищення зручності роботи з даними.
Представлення можуть використовуватися для відображення даних з однієї або кількох таблиць, а також для об'єднання, фільтрації та агрегації даних.
**Приклад створення VIEW:**
```sql
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';
```
Тут представлення active_users містить лише тих користувачів з таблиці users, які мають статус "active". Тепер можна використовувати це представлення як звичайну таблицю:
```sql
SELECT * FROM active_users;
```
Це запитає дані з представлення, і фактично виконається збережений запит.
**Переваги використання представлень:**
**Спрощення складних запитів:**\
Представлення дозволяють приховати складні запити за простим іменем, що спрощує роботу з ними. Замість того щоб щоразу писати довгі SQL-запити, можна просто звернутися до представлення.
**Підвищення безпеки:**\
Представлення можуть бути використані для обмеження доступу до даних. Наприклад, можна надати користувачам доступ лише до певних стовпців або рядків через представлення, а не до вихідної таблиці.
**Aбстракція даних:**\
Представлення можуть допомогти абстрагуватися від складних структур бази даних. Наприклад, можна створити представлення, яке об'єднує дані з кількох таблиць, і користувачі можуть не знати, як ці таблиці пов'язані.
**Забезпечення сумісності:**\
Якщо структура таблиці змінюється (наприклад, додаються нові стовпці), представлення може слугувати шаром сумісності, який продовжує надавати старий інтерфейс для наявних застосунків.
**Оновлювані представлення:**
Представлення можуть підтримувати оновлення, вставку та видалення даних (якщо це можливо з погляду SQL-сервера та логіки запиту). Але не всі представлення оновлювані. Наприклад, якщо представлення використовує агрегатні функції (SUM, COUNT, AVG тощо) або об'єднання (JOIN), то такі представлення зазвичай не можна оновлювати напряму.
**Приклад оновлюваного представлення:**
```sql
CREATE VIEW basic_users AS
SELECT id, name
FROM users;
```
**Можна оновлювати дані в цьому представленні:**
```sql
UPDATE basic_users
SET name = 'New Name'
WHERE id = 1;
```
**Обмеження представлень:**
**Не зберігають дані:**\
Представлення не зберігають дані. Це означає, що щоразу, коли ви робите запит до представлення, сервер бази даних наново виконує запит, на основі якого воно створене.
**Не всі представлення можуть бути оновлюваними:**\
Деякі представлення неможливо використовувати для зміни даних, особливо якщо вони включають складні операції, такі як агрегації або підзапити.
**Продуктивність:**\
Залежно від складності запиту, на основі якого побудоване представлення, використання представлень може сповільнити виконання запитів, особливо якщо воно звертається до великих таблиць або робить складні операції.
**Видалення представлення:**
Якщо представлення більше не потрібне, його можна видалити за допомогою команди DROP VIEW:
```sql
DROP VIEW active_users;
```
VIEW — це потужний інструмент для спрощення роботи з даними, підвищення безпеки та приховування складності структури бази даних. Воно дозволяє створювати віртуальні таблиці, які можуть об'єднувати дані, фільтрувати їх та пропонувати користувачам зручніші способи взаємодії з базою даних.
## Що таке INDEX (індекси) в SQL
Індекси в SQL — це спеціальні структури даних, які використовуються для прискорення пошуку рядків у таблицях. Індекси створюються на одному або кількох стовпцях таблиці, і вони працюють як вказівники, що дозволяють базі даних знаходити дані швидше, ніж якби вона виконувала повне сканування таблиці (тобто перегляд усіх рядків).
**Як працюють індекси:**
**Структура індексу:**\
Індекси часто організовані у вигляді дерев (наприклад, B-дерево або B+ дерево), що дозволяє ефективно знаходити дані. Ці структури дозволяють виконувати пошук, вставку та видалення даних за логарифмічний час, що значно швидше порівняно з повним скануванням таблиці.\
Індекси містять відсортовані дані стовпців, за якими вони побудовані. Це дозволяє базі даних швидко знаходити потрібні рядки.
**Основна ідея:**\
Замість того щоб сканувати всю таблицю при виконанні запиту (особливо якщо таблиця містить багато рядків), база даних звертається до індексу, який містить відсортовані значення. За допомогою індексу база даних знаходить потрібні рядки з мінімальними витратами часу.
**Приклад роботи індексу:**
Припустимо, у нас є таблиця employees, яка містить інформацію про співробітників. У таблиці є стовпець name, за яким часто виконуються запити для пошуку.
**Без індексу:**\
Запит на кшталт SELECT \* FROM employees WHERE name = 'Alice' призведе до того, що база даних повинна буде перевірити кожен рядок у таблиці (повне сканування таблиці), щоб знайти рядки з іменем "Alice". Це особливо витратно для великих таблиць.
**З індексом:**\
Якщо ми створимо індекс на стовпець name за допомогою команди:
```sql
CREATE INDEX idx_employee_name ON employees(name);
```
То запит SELECT \* FROM employees WHERE name = 'Alice' буде використовувати цей індекс. База даних уже знатиме, де перебувають записи з іменем "Alice", завдяки індексу, що значно прискорить виконання запиту.
**Переваги індексів:**
**Прискорення запитів на вибірку даних:**\
Індекси значно прискорюють виконання запитів SELECT, особливо тих, які включають умови WHERE, JOIN, ORDER BY або GROUP BY.
**Підвищення продуктивності з'єднань (JOIN):**\
Якщо використовуються індекси на стовпцях, за якими таблиці з'єднуються, це також прискорює виконання запитів з JOIN.
**Упорядкування даних:**\
Індекси можуть допомогти впорядкувати дані в запитах з ORDER BY, оскільки вони вже містять відсортовані значення стовпців
**Недоліки індексів:**
**Сповільнення операцій вставки, оновлення та видалення:**\
Щоразу, коли відбувається вставка, оновлення або видалення рядка в таблиці, база даних повинна оновлювати індекс, що вимагає додаткових ресурсів. Чим більше індексів у таблиці, тим більше накладних витрат на ці операції.
**Використання пам'яті:**\
Індекси займають додатковий простір на диску та в пам'яті. Якщо таблиця містить кілька індексів, то для кожного з них необхідно зберігати копію даних стовпців у відсортованому вигляді.
**Типи індексів у SQL:**
**Звичайний індекс (Non-Unique Index):**\
Це індекс, який може містити неунікальні значення. Підходить для стовпців, за якими виконуються часті запити, але де значення можуть повторюватися.
```sql
CREATE INDEX idx_name ON employees(name);
```
**Унікальний індекс (Unique Index):**\
Цей тип індексу вимагає, щоб усі значення в індексованому стовпці були унікальними. Він часто використовується для забезпечення унікальності даних, наприклад, для стовпців, які повинні містити унікальні ідентифікатори.
```sql
CREATE UNIQUE INDEX idx_unique_email ON employees(email);
```
**Первинний ключ (Primary Key):**\
Первинний ключ автоматично створює унікальний індекс на відповідний стовпець або групу стовпців. В одній таблиці може бути лише один первинний ключ.
```sql
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100)
);
```
**Складений індекс (Composite Index):**\
Індекс, який створюється на кількох стовпцях таблиці. Такий індекс корисний, коли запити часто фільтрують дані за кількома стовпцями одночасно.
```sql
CREATE INDEX idx_name_department ON employees(name, department);
```
**Повнотекстовий індекс (Full-text Index):**\
Спеціальний тип індексу для швидкого пошуку текстової інформації у великих стовпцях, таких як текстові поля.
```sql
CREATE FULLTEXT INDEX idx_fulltext_description ON articles(description);
```
**Унікальний складений індекс:**\
Унікальний індекс на кілька стовпців, який гарантує, що комбінація значень у цих стовпцях унікальна.
```sql
CREATE UNIQUE INDEX idx_unique_name_dept ON employees(name, department);
```
**Коли індекси не використовуються:**
Індекси не завжди використовуються, навіть якщо вони існують. Існує кілька ситуацій, коли індекси можуть бути проігноровані:
**Малий обсяг таблиці:**\
Якщо таблиця дуже маленька, база даних може вирішити, що сканування всієї таблиці швидше, ніж використання індексу.
**Використання функцій на індексованих стовпцях:**\
Якщо в запиті використовується функція (наприклад, LOWER(name)), індекс може бути не використаний, оскільки перетворення даних робить його непридатним для індексації.
**Неправильні запити:**\
Якщо запити написані так, що не дозволяють використовувати індекс (наприклад, використання від'ємних умов NOT, \<>, !=), це може знизити ефективність індексу.
**Часто оновлювані або видалювані таблиці:**\
У таблицях з частими операціями вставки, видалення або оновлення використання індексів може знижувати продуктивність через необхідність їх постійного оновлення.
## Як уникнути конкуренції при доступі до запису в SQL
Припустимо, 2 користувачі відкрили один і той самий запис у базі даних, як можна уникнути ситуації, коли перший користувач оновив дані раніше за другого, і другий працює з неактуальними даними
У цій ситуації виникає класична проблема конкуренції при роботі з одним і тим самим записом бази даних, яка може призвести до того, що другий користувач перезапише зміни, внесені першим. Щоб уникнути таких конфліктів, існує кілька стратегій керування конкурентним доступом до даних. Ось основні підходи:\\
## Блокування на рівні бази даних (Pessimistic Locking)
Песимістичне блокування передбачає, що щойно один користувач починає працювати із записом, запис блокується, щоб інші користувачі не могли його змінювати до завершення поточної транзакції. Це дозволяє уникнути проблем з конкурентним доступом, але може знижувати продуктивність і призводити до блокувань при високому навантаженні.
**Приклад використання:**\
Один користувач виконує SELECT … FOR UPDATE, що блокує запис для зміни іншими користувачами.\
Поки транзакція першого користувача не завершена (через COMMIT або ROLLBACK), інші користувачі не можуть його оновити.
**Приклад на SQL:**
```sql
BEGIN TRANSACTION;
SELECT * FROM records
WHERE id = 1
FOR UPDATE;
-- Зміна даних...
UPDATE records
SET field = 'new_value'
WHERE id = 1;
COMMIT;
```
При використанні блокувань база даних не дозволить другому користувачеві отримати доступ до зміни запису до завершення роботи першого.
## Оптимістичне блокування (Optimistic Locking)
Оптимістичне блокування передбачає, що конфлікти при доступі до запису рідкісні, тому зміни дозволяються, але при оновленні перевіряється, що запис не був змінений з моменту його читання. Якщо запис був змінений, другий користувач повинен повторити операцію.
Це досягається шляхом зберігання спеціального поля, такого як версія запису або часова мітка останньої зміни. При оновленні запису застосунок перевіряє, що версія запису збігається з тією, що була прочитана в момент початку роботи з ним. Якщо версія змінилася — операція скасовується або пропонується оновити дані.
**Кроки роботи:**\
Користувач зчитує запис разом з його поточною версією.\
При оновленні дані надсилаються на сервер разом з версією.\
Сервер перевіряє, чи збігається версія в базі з переданою версією.\
Якщо версії збігаються, дані оновлюються і версія інкрементується.\
Якщо версії не збігаються, сервер відхиляє запит, і користувачеві показується повідомлення про конфлікт.
**Приклад таблиці з полем версії:**
```sql
CREATE TABLE records (
id INT PRIMARY KEY,
name VARCHAR(100),
version INT
);
```
**Приклад на SQL:**
```sql
UPDATE records
SET name = 'New Name', version = version + 1
WHERE id = 1 AND version = 3;
-- Якщо це оновлення торкнулося 0 рядків, отже, версія змінилася.
```
## Використання часових міток (Timestamp-Based Concurrency Control)
Схожий підхід на оптимістичне блокування, але замість зберігання версії запису використовується часова мітка (наприклад, поле updated_at або last_modified). Коли користувач оновлює дані, база даних перевіряє, чи не змінилася часова мітка запису з моменту його останнього читання.
**Приклад таблиці з часовою міткою:**
```sql
CREATE TABLE records (
id INT PRIMARY KEY,
name VARCHAR(100),
updated_at TIMESTAMP
);
```
**Оновлення з перевіркою часової мітки:**
```sql
UPDATE records
SET name = 'New Name', updated_at = CURRENT_TIMESTAMP
WHERE id = 1 AND updated_at = '2024-09-03 12:00:00';
-- Якщо оновлення не відбулося (0 рядків змінено), отже, запис був оновлений іншим користувачем.
```
**Застосування версій даних на рівні застосунку:**\
На рівні застосунку можна реалізувати логіку, яка відстежує стан даних. Наприклад, при редагуванні даних користувачеві може бути показана версія запису, яку він редагує. Якщо інший користувач вніс зміни, застосунок може або попередити користувача, що дані застаріли, або запропонувати автоматичне злиття змін.
**Комбінування підходів:**\
Часто оптимальний варіант — це комбінація методів. Наприклад:\
При роботі з критично важливими даними можна використовувати песимістичне блокування.\
Для менш критичних операцій використовувати оптимістичне блокування або перевірку часових міток.
## Які існують види відношень у SQL
У реляційних базах даних відношення між таблицями описують, як дані в одній таблиці пов'язані з даними в іншій. Основні види відношень у SQL базах даних:
## Один до багатьох (One-to-Many)
Це найпоширеніший тип відношень у базах даних. Він позначає ситуацію, коли один запис у таблиці може бути пов'язаний з кількома записами в іншій таблиці, але кожен з цих записів пов'язаний лише з одним записом у першій таблиці.
**Приклад:**\
Таблиця authors (автори) і таблиця books (книги). Один автор може написати кілька книг, але кожна книга має лише одного автора.
**Реалізація:** \
У таблицю books додається зовнішній ключ, який посилається на первинний ключ таблиці authors.
```sql
CREATE TABLE authors (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE books (
id INT PRIMARY KEY,
title VARCHAR(100),
author_id INT,
FOREIGN KEY (author_id) REFERENCES authors(id)
);
```
## Багато до багатьох (Many-to-Many)
Цей тип відношення позначає, що один запис у першій таблиці може бути пов'язаний з кількома записами в другій таблиці, і навпаки. Однак такі відношення не можуть бути виражені напряму в SQL. Для реалізації такого відношення використовується проміжна таблиця (сполучна таблиця), яка розбиває відношення "багато до багатьох" на два відношення "один до багатьох".
**Приклад:**\
Таблиці students (студенти) і courses (курси). Один студент може бути записаний на кілька курсів, і на один курс може бути записано кількох студентів.
**Реалізація:** \
Проміжна таблиця students_courses, яка пов'язує таблиці students і courses.
```sql
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE courses (
id INT PRIMARY KEY,
title VARCHAR(100)
);
CREATE TABLE students_courses (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);
```
## Один до одного (One-to-One)
Цей тип відношення позначає, що один запис в одній таблиці пов'язаний лише з одним записом в іншій таблиці, і навпаки. Такі відношення використовуються, коли дані можна розділити на дві таблиці для покращення структури або для зберігання додаткової інформації, яка використовується рідше.
**Приклад:**\
Таблиці users (користувачі) і user_profiles (профілі користувачів). Кожен користувач має лише один профіль, і кожен профіль пов'язаний лише з одним користувачем.
**Реалізація:** \
У таблиці user_profiles зовнішній ключ, який також є унікальним (або первинним), посилається на users.
```sql
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE user_profiles (
user_id INT PRIMARY KEY,
address VARCHAR(255),
phone VARCHAR(50),
FOREIGN KEY (user_id) REFERENCES users(id)
);
```
## Самопосилальне відношення (Self-Referencing Relationship)
Це відношення використовується, коли запис у таблиці посилається на інший запис у цій самій таблиці. Таке відношення може описувати ієрархічні структури даних.
**Приклад:**\
Таблиця employees (співробітники), де один співробітник може бути начальником іншого співробітника.
**Реалізація:** \
У таблицю employees додається зовнішній ключ, який посилається на первинний ключ тієї самої таблиці.
```sql
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employees(id)
);
```
**Приклади використання відношень:**
**Один до багатьох:** \
Це найчастіше вживаний тип зв'язку. Використовується, коли є основна сутність (наприклад, замовник) і пов'язані з нею сутності (наприклад, замовлення).\
Приклад: Клієнти та їхні замовлення (один клієнт — багато замовлень).
**Багато до багатьох:** \
Використовується, коли об'єкти обох таблиць можуть бути пов'язані з багатьма об'єктами іншої таблиці. Наприклад, студенти та курси (один студент може записатися на кілька курсів, а один курс може відвідувати багато студентів).
**Один до одного:** \
Використовується для розділення сутностей за окремими таблицями для покращення структури бази даних. Наприклад, користувач і його профіль.
**Самопосилальні відношення:** \
Дуже корисні при моделюванні ієрархій (наприклад, співробітники та їхні начальники, категорії товарів і підкатегорії).
## Як зробити співвідношення Many-to-Many в SQL
Для створення відношення Many-to-Many (багато до багатьох) у реляційних базах даних використовується проміжна (сполучна) таблиця, яка пов'язує два інші об'єкти (таблиці). Це робиться, тому що реляційні бази даних напряму не підтримують зв'язок "багато до багатьох" між двома таблицями.
**Приклад:**\
Припустимо, у нас є дві таблиці
Students (студенти)\
Courses (курси)
Кожен студент може записатися на кілька курсів, а на кожен курс може бути записано багато студентів. Це типова ситуація зв'язку "багато до багатьох".
**Створюємо таблицю students:**
```sql
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
```
**Створюємо таблицю courses:**
```sql
CREATE TABLE courses (
id INT PRIMARY KEY,
title VARCHAR(100) NOT NULL
);
```
**Створюємо проміжну таблицю students_courses:**\
Ця таблиця пов'язує студентів і курси через їхні ідентифікатори (student_id і course_id). Вона міститиме по одному запису на кожну участь студента в конкретному курсі.
```sql
CREATE TABLE students_courses (
student_id INT,
course_id INT,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);
```
**Що відбувається в проміжній таблиці:**\
Таблиця students_courses містить два стовпці: student_id і course_id. Разом вони складають складений первинний ключ, що запобігає дублюванню записів (тобто один студент не може записатися на один і той самий курс двічі).\
Зовнішні ключі (FOREIGN KEY) використовуються для забезпечення цілісності даних, вказуючи, що значення в student_id і course_id мають відповідати наявним записам у таблицях students і courses відповідно.
**Приклад вставки даних:**
**Додаємо студентів:**
```sql
INSERT INTO students (id, name) VALUES (1, 'Alice'), (2, 'Bob');
```
**Додаємо курси:**
```sql
INSERT INTO courses (id, title) VALUES (101, 'Mathematics'), (102, 'Biology');
```
**Пов'язуємо студентів з курсами:**
```sql
INSERT INTO students_courses (student_id, course_id) VALUES (1, 101), (1, 102), (2, 101);
```
**Приклад запиту для отримання даних:**\
Щоб дізнатися, на які курси записаний студент, можна використати запит з JOIN:
```sql
SELECT students.name, courses.title
FROM students
JOIN students_courses ON students.id = students_courses.student_id
JOIN courses ON students_courses.course_id = courses.id;
```
Цей запит покаже імена студентів і назви курсів, на які вони записані.
## Як у SQL об'єднати запит до кількох таблиць
У SQL існує кілька підходів для об'єднання даних з кількох пов'язаних таблиць в один результуючий набір.
**Використовуємо приклад таблиць:**
```sql
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
name VARCHAR(100),
city VARCHAR(100)
);
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
total_amount DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE archived_orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
total_amount DECIMAL(10,2)
);
```
```text
+-------------+------------------+---------------+
| customer_id | name | city |
+-------------+------------------+---------------+
| 1 | Alice Johnson | New York |
| 2 | Bob Smith | Los Angeles |
| 3 | Charlie Brown | Chicago |
| 4 | Diana Prince | Miami |
+-------------+------------------+---------------+
+----------+-------------+------------+--------------+
| order_id | customer_id | order_date | total_amount |
+----------+-------------+------------+--------------+
| 101 | 1 | 2024-05-01 | 250.00 |
| 102 | 2 | 2024-05-03 | 150.00 |
| 103 | 1 | 2024-06-01 | 300.00 |
| 104 | 3 | 2024-06-15 | 200.00 |
+----------+-------------+------------+--------------+
+----------+-------------+------------+--------------+
| order_id | customer_id | order_date | total_amount |
+----------+-------------+------------+--------------+
| 201 | 2 | 2023-12-20 | 180.00 |
| 202 | 3 | 2023-11-30 | 220.00 |
| 203 | 4 | 2023-10-10 | 100.00 |
+----------+-------------+------------+--------------+
```
## Об'єднання за допомогою JOIN
Спосіб «з'єднання» таблиць за спільним полем.
**INNER JOIN**\
Лише ті рядки, де значення в полях-з'єднувачах збігаються в обох таблицях.
```sql
SELECT c.name, o.order_id, o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
```
```text
| name | order_id | total_amount |
+------------------+----------+--------------+
| Alice Johnson | 101 | 250.00 |
| Alice Johnson | 103 | 300.00 |
| Bob Smith | 102 | 150.00 |
| Charlie Brown | 104 | 200.00 |
```
**LEFT JOIN**\
Усі рядки з лівої таблиці та збіжні рядки з іншої таблиці.
```sql
SELECT c.name, o.order_id, o.total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
```
```text
| name | order_id | total_amount |
+------------------+----------+--------------+
| Alice Johnson | 101 | 250.00 |
| Alice Johnson | 103 | 300.00 |
| Bob Smith | 102 | 150.00 |
| Charlie Brown | 104 | 200.00 |
| Diana Prince | NULL | NULL |
```
**RIGHT JOIN**
```sql
SELECT c.name, o.order_id, o.total_amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
```
```text
| name | order_id | total_amount |
+------------------+----------+--------------+
| Alice Johnson | 101 | 250.00 |
| Alice Johnson | 103 | 300.00 |
| Bob Smith | 102 | 150.00 |
| Charlie Brown | 104 | 200.00 |
| Diana Prince | NULL | NULL |
```
**FULL OUTER JOIN**\
Об'єднує рядки з обох таблиць, включно з тими рядками, де відповідностей немає.
```sql
SELECT c.name, o.order_id, o.total_amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
```
```text
| name | order_id | total_amount |
+------------------+----------+--------------+
| Alice Johnson | 101 | 250.00 |
| Alice Johnson | 103 | 300.00 |
| Bob Smith | 102 | 150.00 |
| Charlie Brown | 104 | 200.00 |
| Diana Prince | NULL | NULL |
```
**CROSS JOIN**\
Утворює декартів добуток двох таблиць, кожен рядок з першої таблиці поєднується з кожним рядком з другої.
```sql
SELECT c.name, o.order_id
FROM customers c
CROSS JOIN orders o;
```
```text
| name | order_id |
+------------------+----------+
| Alice Johnson | 101 |
| Alice Johnson | 102 |
| Alice Johnson | 103 |
| Alice Johnson | 104 |
| Bob Smith | 101 |
| Bob Smith | 102 |
| Bob Smith | 103 |
| Bob Smith | 104 |
| Charlie Brown | 101 |
| Charlie Brown | 102 |
| Charlie Brown | 103 |
| Charlie Brown | 104 |
| Diana Prince | 101 |
| Diana Prince | 102 |
| Diana Prince | 103 |
| Diana Prince | 104 |
```
**Загальні моменти щодо швидкодії JOIN:**\
Наявність індексів на полях з'єднання суттєво покращує продуктивність.\
Вибір конкретного алгоритму з'єднання (nested loop, hash join, merge join) залежить від СУБД, обсягу даних і наявності відповідних індексів.\
Оптимізатор запитів може по-різному реагувати на складні ланцюжки з'єднань; іноді варто переписувати запити або застосовувати підказки для оптимізації.
## Об'єднання результатів за допомогою UNION / UNION ALL
**UNION**\
Об'єднує SELECT в один результат і видаляє дублікати.
```sql
SELECT order_id, customer_id, order_date, total_amount FROM orders
UNION
SELECT order_id, customer_id, order_date, total_amount FROM archived_orders;
```
```text
| order_id | customer_id | order_date | total_amount |
+----------+-------------+------------+--------------+
| 101 | 1 | 2024-05-01 | 250.00 |
| 102 | 2 | 2024-05-03 | 150.00 |
| 103 | 1 | 2024-06-01 | 300.00 |
| 104 | 3 | 2024-06-15 | 200.00 |
| 201 | 2 | 2023-12-20 | 180.00 |
| 202 | 3 | 2023-11-30 | 220.00 |
| 203 | 4 | 2023-10-10 | 100.00 |
```
**UNION ALL**\
Об'єднує SELECT в один результат.
```sql
SELECT customer_id FROM orders
UNION ALL
SELECT customer_id FROM archived_orders;
```
```text
| customer_id |
+-------------+
| 1 |
| 2 |
| 1 |
| 3 |
| 2 |
| 3 |
| 4 |
```
## Використання підзапитів і похідних таблиць
Замість прямого об'єднання таблиць можна використовувати підзапити, які повертають дані з інших таблиць.
| Тип вкладеного запиту | Де використовується | Підтримка |
| --- | --- | --- |
| У SELECT | Повертає скаляр | ✅ Зазвичай |
| У FROM | Табличний підзапит | ✅ Стандартно |
| У WHERE / HAVING | Фільтрація | ✅ Стандартно |
| EXISTS / NOT EXISTS | Перевірка наявності | ✅ Часто |
| IN / NOT IN | Порівняння з множиною | ✅ Стандартно |
| Порівняння з ANY, ALL | Скалярні порівняння | ✅ Часто |
| Корельовані підзапити | Використовують зовнішні поля | ✅ Стандартно |
**Підзапит у SELECT**
```sql
SELECT name
AS name,
(SELECT AVG(total_amount) FROM orders)
AS global_avg
FROM customers;
```
```text
| name | global_avg |
+------------------+-------------+
| Alice Johnson | 225.00 |
| Bob Smith | 225.00 |
| Charlie Brown | 225.00 |
| Diana Prince | 225.00 |
```
**Підзапит у FROM**
```sql
SELECT sub.customer_id, sub.avg_amount
FROM (
SELECT
customer_id, AVG(total_amount) AS avg_amount,
COUNT(*) AS cnt
FROM orders
GROUP BY customer_id
) AS sub
WHERE sub.cnt > 1;
```
**Підзапит у WHERE**
```sql
SELECT name
FROM customers
WHERE customer_id IN (
SELECT DISTINCT customer_id
FROM orders
);
```
**Підзапит у EXISTS / NOT EXISTS**
```sql
SELECT name
FROM customers c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.customer_id
);
```
**Підзапит у IN / NOT IN**
```sql
SELECT name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
);
```
**Підзапит у ALL**
```sql
SELECT order_id, total_amount
FROM orders
WHERE total_amount > ALL (
SELECT total_amount
FROM archived_orders
);
```
**Підзапит у ANY / SOME**
```sql
SELECT order_id, total_amount
FROM orders
WHERE total_amount > ANY (
SELECT total_amount
FROM archived_orders
);
```
Вкладений підзапит залежить від рядка зовнішнього запиту
```sql
SELECT order_id, customer_id, total_amount
FROM orders o1
WHERE total_amount > (
SELECT AVG(total_amount)
FROM orders o2
WHERE o2.customer_id = o1.customer_id
);
```
## Загальні табличні вирази (CTE) з конструкцією WITH
CTE дозволяють створювати тимчасові іменовані результати (як «віртуальні таблиці»), які можна використовувати в основному запиті. Це підвищує читабельність запиту та спрощує структурування складної логіки об'єднання.
**Швидкодія:**
- CTE по суті є аліасами для підзапитів. Їхня продуктивність часто аналогічна підзапитам у секції FROM, але в деяких СУБД може бути недостатня оптимізація, якщо CTE не інлайниться.
- При використанні рекурсивних CTE слід враховувати, що вони можуть бути менш ефективними, якщо глибина рекурсії висока.
**Приклад: CTE для обчислення кількості замовлень**\
Спочатку створимо CTE, яке агрегує замовлення, а потім виконаємо LEFT JOIN з клієнтами:
```sql
WITH OrderCounts AS (
SELECT
customer_id,
COUNT(*) AS total_orders
FROM orders
GROUP BY customer_id
)
SELECT
c.customer_id,
c.name,
c.city,
COALESCE(oc.total_orders, 0) AS total_orders
FROM customers c
LEFT JOIN OrderCounts oc
ON c.customer_id = oc.customer_id;
```
**Коментар щодо швидкодії:**
- Якщо CTE інлайниться оптимізатором, то вони працюють так само ефективно, як і підзапити в секції FROM.
- У деяких СУБД CTE не оптимізуються («materialized»), що може вплинути на продуктивність при великому наборі даних.
## Додаткові моменти та рекомендації щодо швидкодії
**Оптимізація запитів:**\
Незалежно від обраного методу, наявність відповідних індексів – ключовий фактор швидкодії. Варто також вивчити план виконання запиту (EXPLAIN PLAN або аналог) для виявлення «вузьких місць».
**Розміри таблиць і розподіл даних:**\
При роботі з великими таблицями всі методи вимагають ретельного тестування. Наприклад, якщо одна таблиця містить мільйони записів, а інша – тисячі, то оптимізатор може обрати інший порядок з'єднань для підвищення ефективності.
**Особливості СУБД:**\
Різні системи (PostgreSQL, MySQL, Oracle, SQL Server) можуть по-різному обробляти JOIN, UNION або CTE. Тому варто протестувати запити в конкретному середовищі і, за потреби, застосовувати спеціалізовані налаштування або підказки для оптимізації (hints).
**Читабельність vs. продуктивність:**\
Іноді читабельніша конструкція запиту (наприклад, з використанням CTE) може поступатися за продуктивністю переписаному з JOIN’ами. Тому рекомендується балансувати між підтримуваністю і швидкістю виконання, проводячи бенчмаркінг у реальному робочому середовищі.
## Висновок
**JOIN (INNER, LEFT/RIGHT, FULL, CROSS):**\
Найпоширеніший спосіб з хорошою оптимізацією за наявності індексів.\
Хороший для безпосереднього об'єднання пов'язаних даних, особливо при правильному індексуванні.
**UNION / UNION ALL:**\
Чудово підходить для об'єднання кількох SELECT-запитів; UNION ALL кращий для продуктивності, якщо усунення дублікатів не потрібне.\
Корисні для об'єднання результатів з таблиць з однаковою структурою (наприклад, активні та архівні замовлення).
**Підзапити та похідні таблиці:**\
Універсальний інструмент, але слід уникати корельованих підзапитів у випадках великого обсягу даних.\
Можуть бути зручні для агрегування або фільтрації даних, але слід бути обережним з корельованими підзапитами.
CTE (WITH):\
Підвищують читабельність складних запитів, однак не завжди гарантують покращену швидкодію.\
Покращують читабельність складних запитів і дозволяють перевикористовувати проміжні результати, хоча іноді можуть впливати на швидкодію, якщо не оптимізуються інлайн.
## Як використовувати план запиту в SQL
План запиту (execution plan) — це докладний опис того, як СУБД збирається виконати SQL-запит. Він дозволяє побачити, які операції виконуються (сканування таблиць, використання індексів, з'єднання, сортування, агрегації тощо), в якому порядку вони відбуваються і як розподіляються витрати за часом і ресурсами. Це важливий інструмент для оптимізації запитів і діагностики проблем з продуктивністю.
## PostgreSQL
**EXPLAIN:**
```sql
EXPLAIN SELECT * FROM employees WHERE department_id = 10;
```
**Вивід:**
```text
QUERY PLAN
---------------------------------------------------------------
Seq Scan on employees (cost=0.00..35.50 rows=5 width=50)
Filter: (department_id = 10)
(2 rows)
```
**Пояснення:**
**Seq Scan on employees**: послідовне сканування таблиці `employees`.\
**cost=0.00..35.50**: оцінка витрат від початку до завершення операції.\
**rows=5**: передбачувана кількість рядків, що задовольняють умову.\
**width=50**: середній розмір рядка в байтах.\
**Filter: (department_id = 10)**: умова фільтрації, що застосовується при скануванні.
**EXPLAIN ANALYZE:**
```sql
EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;
```
**Вивід:**
```text
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=0.00..35.50 rows=5 width=50) (actual time=0.021..0.040 rows=2 loops=1)
Filter: (department_id = 10)
Rows Removed by Filter: 3
Planning Time: 0.123 ms
Execution Time: 0.060 ms
(5 rows)
```
**Пояснення:**
**actual time=0.021..0.040**: фактичний час початку та закінчення сканування.\
**rows=2**: фактична кількість рядків, що пройшли фільтр.\
**Rows Removed by Filter: 3**: кількість рядків, відсіяних фільтром.\
**Planning Time** і **Execution Time**: дають уявлення про час планування запиту та його виконання відповідно.
## MySQL
**EXPLAIN:**
```sql
EXPLAIN SELECT * FROM employees WHERE department_id = 10;
```
**Вивід (у вигляді таблиці):**
```text
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
| 1 | SIMPLE | employees | ref | dep_index | dep_index | 4 | const | 5 | Using where; Using index |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
```
**Пояснення:**
**id**: ідентифікатор запиту (для складніших запитів з підзапитами або з'єднаннями).\
**select_type**: тип запиту (SIMPLE означає відсутність підзапитів).\
**table**: ім'я таблиці.\
**type**: тип доступу (наприклад, `ref` означає використання індексу для пошуку).\
**possible_keys**: індекси, які могли б бути використані.\
**key**: фактично використаний індекс.\
**rows**: оцінна кількість рядків, яка буде прочитана.\
**Extra**: додаткові відомості, наприклад, `Using where` вказує на застосування фільтра.
## Oracle
Для Oracle зазвичай виконують два кроки- спочатку формують план, потім його переглядають.
**Крок 1:** \
Формування плану
```sql
EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;
```
**Крок 2:** \
Перегляд плану
```sql
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
```
**Вивід (у вигляді таблиці):**
```sql
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 120 | 3 |
|* 2 | INDEX RANGE SCAN | DEP_INDEX | 5 | | 2 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------------
1 - filter("DEPARTMENT_ID"=10)
2 - access("DEPARTMENT_ID"=10)
```
**Пояснення:**
**TABLE ACCESS BY INDEX ROWID**: доступ до рядків через індекс.\
**INDEX RANGE SCAN**: використання діапазонного сканування за індексом `DEP_INDEX`.\
**Cost**: оцінка витрат на виконання кожної операції.\
**Predicate Information**: уточнює, яка умова застосовується для кожної операції.
## Microsoft SQL Server
У SQL Server існує можливість отримання плану виконання як у графічному інтерфейсі SSMS, так і через T-SQL.
**Приклад:** \
використання SHOWPLAN
```sql
SET SHOWPLAN_ALL ON;
GO
SELECT * FROM employees WHERE department_id = 10;
GO
SET SHOWPLAN_ALL OFF;
```
**Вивід (у вигляді таблиці):**
```text
------------------------------------------------------------------------------------------------------------------
| stmtid | nodeid | parent | physical_op | logical_op | argument |
------------------------------------------------------------------------------------------------------------------
| 1 | 0 | NULL | Clustered Index Scan | Clustered Index Scan | Table: employees, Index: PK_employees |
| 1 | 1 | 0 | Filter | Filter | Predicate: department_id = 10 |
------------------------------------------------------------------------------------------------------------------
```
**Пояснення:**
**Clustered Index Scan**: сканування з використанням кластерного індексу.\
**Filter**: застосування умови фільтрації.\
**stmtid, nodeid, parent**: ідентифікатори, що визначають ієрархію операцій у плані виконання.
## Як використовувати індекси в SQL
Індекси суттєво підвищують продуктивність запитів, однак їх надмірне або недоречне використання може сповільнити операції вставки, оновлення та видалення через додаткове обслуговування індексу. Вибір типу індексу визначається конкретними завданнями:
**B-Tree індекс:** \
стандарт для більшості випадків.
**Унікальний індекс:** \
для забезпечення унікальності.
**Композитний індекс:** \
для запитів з фільтрами за кількома стовпцями.
**Повнотекстовий індекс:** \
для роботи з текстовими даними.
**Просторовий індекс:** \
для географічних і геометричних даних.
**Функціональний індекс:** \
для оптимізації запитів із застосуванням функцій.
**Звичайний (B-Tree) індекс:**
Стандартний тип індексу, який використовується для більшості пошукових операцій. Він будується за принципом бінарного дерева, що дозволяє швидко знаходити рядки за значенням стовпця.
```sql
CREATE INDEX idx_employee_department ON employees(department_id);
```
**Як використовується:**\
При виконанні запиту з умовою за стовпцем `department_id` (наприклад, `WHERE department_id = 10`) оптимізатор запиту обирає індекс замість повного сканування таблиці.
**Унікальний індекс:**
Забезпечує унікальність значень у стовпці або наборі стовпців. При спробі вставити дубльоване значення СУБД видає помилку.
```sql
CREATE UNIQUE INDEX idx_unique_email ON employees(email);
```
**Як використовується:**\
Якщо в таблиці employees не можна мати двох співробітників з однаковим email, унікальний індекс гарантує виконання цього обмеження та прискорює пошук за стовпцем email.
**Композитний (мультиколонковий) індекс:**
Створюється за двома або більше стовпцями. Він корисний, коли запити використовують кілька стовпців в умовах фільтрації або з'єднання. Важливо пам'ятати, що порядок стовпців в індексі має значення.
```sql
CREATE INDEX idx_employee_dept_lastname ON employees(department_id, last_name);
```
**Як використовується:**\
При запиті індекс дозволить швидко вибрати рядки за обома стовпцями.
```sql
SELECT * FROM employees
WHERE department_id = 10 AND last_name = 'Ivanov';
```
**Повнотекстовий індекс:**
Використовується для швидкого пошуку за текстовими даними (наприклад, у полях з описом або вмістом статей). Він будується з урахуванням морфології та стоп-слів, що дозволяє виконувати пошук за словами та фразами.
**MySQL:**\
Використовується для стовпців типу TEXT або VARCHAR.
```sql
CREATE FULLTEXT INDEX idx_fulltext_description ON products(description);
```
Після створення індексу можна виконувати запити з оператором MATCH … AGAINST:
```sql
SELECT * FROM products
WHERE MATCH(description) AGAINST('електроніка');
```
**PostgreSQL:**\
використовує індекс типу GIN з функцією to_tsvector для повнотекстового пошуку.
```sql
CREATE INDEX idx_fts_content ON articles USING gin(to_tsvector('russian', content));
```
Пошук виконується так:
```sql
SELECT * FROM articles
WHERE to_tsvector('russian', content) @@ to_tsquery('електроніка');
```
**SQL Server:** \
має вбудований механізм повнотекстового пошуку. Спочатку необхідно створити повнотекстовий каталог, потім повнотекстовий індекс.
```sql
-- Створення каталогу повнотекстового пошуку
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;
-- Створення повнотекстового індексу за таблицею products
CREATE FULLTEXT INDEX ON products(description)
KEY INDEX PK_products ON ftCatalog;
```
**Просторовий індекс:**
Просторові індекси використовуються для прискорення запитів за геометричними даними (точки, лінії, полігони). Вони оптимізовані для пошуку за двовимірними (або багатовимірними) просторовими координатами.
**MySQL:**\
При використанні типів даних GEOMETRY, POINT тощо. Тут geom – стовпець, що містить геометричні дані.
```sql
CREATE SPATIAL INDEX idx_spatial_location ON locations(geom);
```
**PostgreSQL з розширенням PostGIS:**\
Для просторових даних використовується індекс GIST. Такий індекс прискорює просторові запити, наприклад, пошук об'єктів у певному радіусі.
```sql
CREATE INDEX idx_spatial_location ON locations USING gist(geom);
```
**Oracle:**\
Підтримує просторові індекси через Oracle Spatial. Приклад створення:
```sql
CREATE INDEX idx_spatial_location ON locations(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;
```
**Функціональний (обчислюваний) індекс:**
Функціональні індекси створюються на основі виразів або функцій, що застосовуються до стовпців. Це зручно, якщо запити часто використовують функції, наприклад, для приведення до нижнього регістру, обрізки рядків тощо.
**Oracle і PostgreSQL:**\
Припустимо, потрібен індекс для швидкого пошуку за email без урахування регістру:
```sql
CREATE INDEX idx_lower_email ON employees(LOWER(email));
```
Запит буде використовувати індекс, якщо умова записана так:
```sql
SELECT * FROM employees WHERE LOWER(email) = 'ivanov@example.com';
```
**SQL Server:**\
У SQL Server можна створити індекс на обчислюваний стовпець. Спочатку додають обчислюваний стовпець, а потім створюють за ним індекс:
```sql
ALTER TABLE employees ADD lower_email AS LOWER(email);
CREATE INDEX idx_lower_email ON employees(lower_email);
```
[ Copyright: Roman Kryvolapov ](https://t.me/RomanKryvolapov)