Roman Kryvolapov Engineering Blog

Реляційні SQL бази даних

Який синтаксис запиту в SQL

У SQL існує безліч ключових слів, які використовуються для створення, зміни, керування та отримання даних з баз даних. Наведу основні категорії та ключові слова, що використовуються в SQL:

DML (Data Manipulation Language) - Мова маніпуляції даними:
Ключові слова для роботи з даними, такими як вставка, оновлення та видалення даних.

SELECT:
вибирає дані з таблиці.

SELECT *
FROM users
WHERE age > 30;


SELECT COUNT(*)
FROM users
WHERE age > 30;

INSERT:
додає нові записи до таблиці.

INSERT
INTO users (name, age)
VALUES ('John', 25);

UPDATE:
оновлює наявні записи в таблиці.

UPDATE users
SET age = 26
WHERE name = 'John';

DELETE:
видаляє записи з таблиці.

DELETE
FROM users
WHERE age < 18;

MERGE:
об'єднує дані з двох таблиць на основі умов.

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:
викликає збережену процедуру.

CALL calculate_salary(12345);

DDL (Data Definition Language) — Мова визначення даних:
Ключові слова для створення та модифікації структури бази даних (таблиць, індексів та інших об'єктів).

CREATE:
створює нові об'єкти бази даних (таблиці, індекси тощо).

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10, 2)
);

ALTER:
змінює структуру наявних об'єктів бази даних.

ALTER
TABLE products
ADD COLUMN description TEXT;

DROP:
видаляє об'єкти бази даних (таблиці, індекси тощо).

DROP TABLE products;

TRUNCATE:
видаляє всі рядки з таблиці, але не видаляє саму таблицю.

TRUNCATE TABLE users;

COMMENT:
додає коментарі до об'єктів бази даних.

COMMENT
ON TABLE products
IS 'Table storing product details';

RENAME:
перейменовує об'єкт бази даних.

RENAME
TABLE old_products
TO new_products;

DCL (Data Control Language) — Мова керування доступом:
Ключові слова для керування правами доступу до даних.

GRANT:
надає користувачам або ролям права на об'єкти бази даних.

GRANT
SELECT, INSERT
ON orders
TO user_admin;

REVOKE:
відкликає права на об'єкти бази даних у користувачів або ролей.

REVOKE
INSERT ON orders
FROM user_admin;

TCL (Transaction Control Language) — Мова керування транзакціями:
Ключові слова для керування транзакціями в базі даних.

BEGIN TRANSACTION:
починає транзакцію.

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

COMMIT:
зберігає зміни, зроблені в межах поточної транзакції.

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:
скасовує зміни, зроблені в межах поточної транзакції.

BEGIN TRANSACTION;
DELETE FROM employees WHERE employee_id = 45;
UPDATE departments SET manager_id = NULL WHERE manager_id = 45;
ROLLBACK;

SAVEPOINT:
створює точку відновлення в транзакції.

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:
встановлює параметри для поточної транзакції.

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:
визначає унікальний ідентифікатор для кожного рядка таблиці.

CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

FOREIGN KEY:
встановлює зв'язок між двома таблицями.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

UNIQUE:
вимагає, щоб усі значення в стовпці або групі стовпців були унікальними.

CREATE TABLE users (
  user_id INT PRIMARY KEY,
  username VARCHAR(50) UNIQUE,
  email VARCHAR(100) UNIQUE
);

NOT NULL:
забороняє використання порожніх значень для цього стовпця.

CREATE TABLE products (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

CHECK:
визначає умову, яку мають виконувати дані.

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  salary DECIMAL(10, 2) CHECK (salary > 0),
  age INT CHECK (age >= 18)
);

DEFAULT:
встановлює значення за замовчуванням для стовпця.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status VARCHAR(20) DEFAULT 'PENDING'
);

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

WHERE:
фільтрує рядки на основі умови.

SELECT *
FROM employees
WHERE department = 'Sales'
AND salary > 50000;

GROUP BY:
групує рядки за значеннями одного або кількох стовпців.

SELECT department, COUNT(*) AS total_employees
FROM employees
GROUP BY department;

HAVING:
фільтрує рядки після групування.

SELECT department, AVG(salary) AS average_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;

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

SELECT first_name, last_name, hire_date
FROM employees
ORDER BY hire_date DESC;

DISTINCT:
повертає унікальні значення.

SELECT DISTINCT country
FROM customers;

LIMIT:
обмежує кількість рядків, що повертаються (у деяких СУБД, наприклад MySQL).

SELECT * FROM products
ORDER BY price DESC
LIMIT 10;

OFFSET:
визначає, з якого рядка почати виведення результату.

SELECT * FROM products
ORDER BY price DESC
LIMIT 10 OFFSET 10;

Ключові слова для роботи з об'єднанням таблиць:
Ключові слова для об'єднання даних з кількох таблиць.

JOIN:
об'єднує рядки з двох або більше таблиць на основі відношення між ними.

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:
повертає рядки зі збіжними значеннями в обох таблицях.

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):
повертає всі рядки з лівої таблиці, навіть якщо в правій таблиці немає збігів.

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):
повертає всі рядки з правої таблиці, навіть якщо в лівій таблиці немає збігів.

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, якщо немає збігів.

SELECT a.account_id, t.transaction_id
FROM accounts a
FULL JOIN transactions t ON a.account_id = t.account_id;

CROSS JOIN:
повертає декартів добуток двох таблиць (кожен рядок з першої таблиці об'єднується з кожним рядком з другої таблиці).

SELECT p.product_name, s.store_name
FROM products p
CROSS JOIN stores s;

SELF JOIN:
з'єднує таблицю саму з собою.

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:
створює індекс для стовпців таблиці для підвищення продуктивності запитів.

CREATE INDEX idx_last_name ON employees(last_name);

CREATE SEQUENCE:
створює послідовність для генерації унікальних чисел.

CREATE SEQUENCE order_seq
START WITH 1000
INCREMENT BY 1;

DROP INDEX:
видаляє індекс.

DROP INDEX idx_last_name;
// для деяких СУБД, наприклад PostgreSQL
DROP INDEX IF EXISTS idx_last_name;
// у MySQL, індекс видаляється інакше
ALTER TABLE employees DROP INDEX idx_last_name;

DROP SEQUENCE:
видаляє послідовність.

DROP SEQUENCE order_seq;

Ключові слова для роботи з представленнями (views):
Ключові слова для створення та керування представленнями.

CREATE VIEW:
створює представлення.

CREATE VIEW active_customers AS
SELECT customer_id, first_name, last_name, email
FROM customers
WHERE status = 'ACTIVE';

DROP VIEW:
видаляє представлення.

DROP VIEW active_customers;

ALTER VIEW:
змінює наявне представлення.

ALTER VIEW active_customers AS
SELECT customer_id, first_name, last_name, email, phone
FROM customers
WHERE status = 'ACTIVE';

Ключові слова для роботи зі збереженими процедурами та тригерами:
Ключові слова для створення та керування збереженими процедурами та тригерами.

CREATE PROCEDURE:
створює збережену процедуру.

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).

CREATE TRIGGER set_created_at
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = CURRENT_TIMESTAMP;

EXECUTE:
виконує збережену процедуру.

CALL increase_salary(101, 10);

DROP PROCEDURE:
видаляє збережену процедуру.

DROP PROCEDURE increase_salary;

DROP TRIGGER:
видаляє тригер.

DROP TRIGGER set_created_at;

Функції агрегації та аналітики:
Ключові слова для агрегування та аналізу даних.

COUNT():
повертає кількість рядків.

SELECT COUNT(*) AS total_employees
FROM employees;

SUM():
повертає суму значень.

SELECT SUM(order_total) AS total_revenue
FROM orders
WHERE order_date >= '2024-01-01';

AVG():
повертає середнє значення.

SELECT AVG(salary) AS average_salary
FROM employees
WHERE department = 'IT';

MIN():
повертає мінімальне значення.

SELECT MIN(price) AS cheapest_product
FROM products;

MAX():
повертає максимальне значення.

SELECT MAX(price) AS most_expensive_product
FROM products;

Інші ключові слова та оператори:

EXISTS:
перевіряє, чи існують рядки, що відповідають умові.

SELECT first_name, last_name
FROM customers
WHERE EXISTS (
  SELECT 1 FROM orders
  WHERE orders.customer_id = customers.customer_id
);

IN:
перевіряє, чи міститься значення в списку значень.

SELECT *
FROM products
WHERE category_id IN (1, 3, 5);

BETWEEN:
перевіряє, чи перебуває значення в певному діапазоні.

SELECT *
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31';

LIKE:
шукає рядкові дані за шаблоном.

SELECT *
FROM users
WHERE email LIKE '%@example.com';

IS NULL:
перевіряє, чи містить значення NULL.

SELECT *
FROM employees
WHERE manager_id IS NULL;

CAST():
перетворює одне значення на інший тип даних.

SELECT CAST(order_total AS INT) AS total_as_integer
FROM orders;

COALESCE():
повертає перше ненульове значення в списку.

SELECT COALESCE(phone, 'No phone') AS contact_number
FROM customers;

CASE:
повертає значення на основі умов (аналог оператора if-else).

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).

WITH high_salary_employees AS (
  SELECT * FROM employees WHERE salary > 80000
)
SELECT department, COUNT(*)
FROM high_salary_employees
GROUP BY department;

UNION:
використовується для об'єднання результатів двох або більше запитів. Він дозволяє об'єднувати рядки з кількох SELECT запитів в один набір результатів. При цьому рядки з різних запитів не повинні дублюватися, якщо явно не використовується оператор ALL

SELECT first_name, email FROM customers
UNION
SELECT name, email FROM newsletter_subscribers;

Це ключові слова, які найчастіше зустрічаються в SQL. Вони використовуються для роботи з даними та керування структурою бази даних. Кожна СУБД може мати свої унікальні ключові слова, але перелічені вище — це стандартні команди SQL.

Що таке VIEW (представлення) в SQL

VIEW (представлення) в SQL — це віртуальна таблиця, яка формується на основі результату запиту. Представлення не зберігають дані фізично, вони лише зберігають SQL-запит, який виконується щоразу при зверненні до представлення. Це зручний інструмент для спрощення складних запитів, забезпечення безпеки та підвищення зручності роботи з даними.

Представлення можуть використовуватися для відображення даних з однієї або кількох таблиць, а також для об'єднання, фільтрації та агрегації даних.

Приклад створення VIEW:

CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

Тут представлення active_users містить лише тих користувачів з таблиці users, які мають статус "active". Тепер можна використовувати це представлення як звичайну таблицю:

SELECT * FROM active_users;

Це запитає дані з представлення, і фактично виконається збережений запит.

Переваги використання представлень:

Спрощення складних запитів:
Представлення дозволяють приховати складні запити за простим іменем, що спрощує роботу з ними. Замість того щоб щоразу писати довгі SQL-запити, можна просто звернутися до представлення.

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

Aбстракція даних:
Представлення можуть допомогти абстрагуватися від складних структур бази даних. Наприклад, можна створити представлення, яке об'єднує дані з кількох таблиць, і користувачі можуть не знати, як ці таблиці пов'язані.

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

Оновлювані представлення:

Представлення можуть підтримувати оновлення, вставку та видалення даних (якщо це можливо з погляду SQL-сервера та логіки запиту). Але не всі представлення оновлювані. Наприклад, якщо представлення використовує агрегатні функції (SUM, COUNT, AVG тощо) або об'єднання (JOIN), то такі представлення зазвичай не можна оновлювати напряму.

Приклад оновлюваного представлення:

CREATE VIEW basic_users AS
SELECT id, name
FROM users;

Можна оновлювати дані в цьому представленні:

UPDATE basic_users
SET name = 'New Name'
WHERE id = 1;

Обмеження представлень:

Не зберігають дані:
Представлення не зберігають дані. Це означає, що щоразу, коли ви робите запит до представлення, сервер бази даних наново виконує запит, на основі якого воно створене.

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

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

Видалення представлення:

Якщо представлення більше не потрібне, його можна видалити за допомогою команди DROP VIEW:

DROP VIEW active_users;

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

Що таке INDEX (індекси) в SQL

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

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

Структура індексу:
Індекси часто організовані у вигляді дерев (наприклад, B-дерево або B+ дерево), що дозволяє ефективно знаходити дані. Ці структури дозволяють виконувати пошук, вставку та видалення даних за логарифмічний час, що значно швидше порівняно з повним скануванням таблиці.
Індекси містять відсортовані дані стовпців, за якими вони побудовані. Це дозволяє базі даних швидко знаходити потрібні рядки.

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

Приклад роботи індексу:

Припустимо, у нас є таблиця employees, яка містить інформацію про співробітників. У таблиці є стовпець name, за яким часто виконуються запити для пошуку.

Без індексу:
Запит на кшталт SELECT * FROM employees WHERE name = 'Alice' призведе до того, що база даних повинна буде перевірити кожен рядок у таблиці (повне сканування таблиці), щоб знайти рядки з іменем "Alice". Це особливо витратно для великих таблиць.

З індексом:
Якщо ми створимо індекс на стовпець name за допомогою команди:

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):
Це індекс, який може містити неунікальні значення. Підходить для стовпців, за якими виконуються часті запити, але де значення можуть повторюватися.

CREATE INDEX idx_name ON employees(name);

Унікальний індекс (Unique Index):
Цей тип індексу вимагає, щоб усі значення в індексованому стовпці були унікальними. Він часто використовується для забезпечення унікальності даних, наприклад, для стовпців, які повинні містити унікальні ідентифікатори.

CREATE UNIQUE INDEX idx_unique_email ON employees(email);

Первинний ключ (Primary Key):
Первинний ключ автоматично створює унікальний індекс на відповідний стовпець або групу стовпців. В одній таблиці може бути лише один первинний ключ.

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

Складений індекс (Composite Index):
Індекс, який створюється на кількох стовпцях таблиці. Такий індекс корисний, коли запити часто фільтрують дані за кількома стовпцями одночасно.

CREATE INDEX idx_name_department ON employees(name, department);

Повнотекстовий індекс (Full-text Index):
Спеціальний тип індексу для швидкого пошуку текстової інформації у великих стовпцях, таких як текстові поля.

CREATE FULLTEXT INDEX idx_fulltext_description ON articles(description);

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

CREATE UNIQUE INDEX idx_unique_name_dept ON employees(name, department);

Коли індекси не використовуються:

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

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

Використання функцій на індексованих стовпцях:
Якщо в запиті використовується функція (наприклад, LOWER(name)), індекс може бути не використаний, оскільки перетворення даних робить його непридатним для індексації.

Неправильні запити:
Якщо запити написані так, що не дозволяють використовувати індекс (наприклад, використання від'ємних умов NOT, <>, !=), це може знизити ефективність індексу.

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

Як уникнути конкуренції при доступі до запису в SQL

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

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

Блокування на рівні бази даних (Pessimistic Locking)

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

Приклад використання:
Один користувач виконує SELECT … FOR UPDATE, що блокує запис для зміни іншими користувачами.
Поки транзакція першого користувача не завершена (через COMMIT або ROLLBACK), інші користувачі не можуть його оновити.

Приклад на SQL:

BEGIN TRANSACTION;


SELECT * FROM records
WHERE id = 1
FOR UPDATE;


-- Зміна даних...


UPDATE records
SET field = 'new_value'
WHERE id = 1;


COMMIT;

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

Оптимістичне блокування (Optimistic Locking)

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

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

Кроки роботи:
Користувач зчитує запис разом з його поточною версією.
При оновленні дані надсилаються на сервер разом з версією.
Сервер перевіряє, чи збігається версія в базі з переданою версією.
Якщо версії збігаються, дані оновлюються і версія інкрементується.
Якщо версії не збігаються, сервер відхиляє запит, і користувачеві показується повідомлення про конфлікт.

Приклад таблиці з полем версії:

CREATE TABLE records (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    version INT
);

Приклад на 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). Коли користувач оновлює дані, база даних перевіряє, чи не змінилася часова мітка запису з моменту його останнього читання.

Приклад таблиці з часовою міткою:

CREATE TABLE records (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    updated_at TIMESTAMP
);

Оновлення з перевіркою часової мітки:

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.

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.

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.

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 додається зовнішній ключ, який посилається на первинний ключ тієї самої таблиці.

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:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

Створюємо таблицю courses:

CREATE TABLE courses (
    id INT PRIMARY KEY,
    title VARCHAR(100) NOT NULL
);

Створюємо проміжну таблицю students_courses:
Ця таблиця пов'язує студентів і курси через їхні ідентифікатори (student_id і course_id). Вона міститиме по одному запису на кожну участь студента в конкретному курсі.

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 відповідно.

Приклад вставки даних:

Додаємо студентів:

INSERT INTO students (id, name) VALUES (1, 'Alice'), (2, 'Bob');

Додаємо курси:

INSERT INTO courses (id, title) VALUES (101, 'Mathematics'), (102, 'Biology');

Пов'язуємо студентів з курсами:

INSERT INTO students_courses (student_id, course_id) VALUES (1, 101), (1, 102), (2, 101);

Приклад запиту для отримання даних:
Щоб дізнатися, на які курси записаний студент, можна використати запит з JOIN:

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 існує кілька підходів для об'єднання даних з кількох пов'язаних таблиць в один результуючий набір.

Використовуємо приклад таблиць:

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)
);
+-------------+------------------+---------------+
| 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
Лише ті рядки, де значення в полях-з'єднувачах збігаються в обох таблицях.

SELECT c.name, o.order_id, o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
| 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
Усі рядки з лівої таблиці та збіжні рядки з іншої таблиці.

SELECT c.name, o.order_id, o.total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
| 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

SELECT c.name, o.order_id, o.total_amount
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;
| 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
Об'єднує рядки з обох таблиць, включно з тими рядками, де відповідностей немає.

SELECT c.name, o.order_id, o.total_amount
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;
| 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
Утворює декартів добуток двох таблиць, кожен рядок з першої таблиці поєднується з кожним рядком з другої.

SELECT c.name, o.order_id
FROM customers c
CROSS JOIN orders o;
| 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 в один результат і видаляє дублікати.

SELECT order_id, customer_id, order_date, total_amount FROM orders
UNION
SELECT order_id, customer_id, order_date, total_amount FROM archived_orders;
| 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 в один результат.

SELECT customer_id FROM orders
UNION ALL
SELECT customer_id FROM archived_orders;
| customer_id |
+-------------+
| 1           |
| 2           |
| 1           |
| 3           |
| 2           |
| 3           |
| 4           |

Використання підзапитів і похідних таблиць

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

Тип вкладеного запитуДе використовуєтьсяПідтримка
У SELECTПовертає скаляр✅ Зазвичай
У FROMТабличний підзапит✅ Стандартно
У WHERE / HAVINGФільтрація✅ Стандартно
EXISTS / NOT EXISTSПеревірка наявності✅ Часто
IN / NOT INПорівняння з множиною✅ Стандартно
Порівняння з ANY, ALLСкалярні порівняння✅ Часто
Корельовані підзапитиВикористовують зовнішні поля✅ Стандартно

Підзапит у SELECT

SELECT name
AS name,
(SELECT AVG(total_amount) FROM orders)
AS global_avg
FROM customers;
| name             | global_avg |
+------------------+-------------+
| Alice Johnson    | 225.00      |
| Bob Smith        | 225.00      |
| Charlie Brown    | 225.00      |
| Diana Prince     | 225.00      |

Підзапит у FROM

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

SELECT name
FROM customers
WHERE customer_id IN (
  SELECT DISTINCT customer_id
  FROM orders
);

Підзапит у EXISTS / NOT EXISTS

SELECT name
FROM customers c
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
);

Підзапит у IN / NOT IN

SELECT name
FROM customers
WHERE customer_id IN (
  SELECT customer_id
  FROM orders
);

Підзапит у ALL

SELECT order_id, total_amount
FROM orders
WHERE total_amount > ALL (
  SELECT total_amount
  FROM archived_orders
);

Підзапит у ANY / SOME

SELECT order_id, total_amount
FROM orders
WHERE total_amount > ANY (
  SELECT total_amount
  FROM archived_orders
);

Вкладений підзапит залежить від рядка зовнішнього запиту

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 з клієнтами:

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:

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

Вивід:

                          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:

EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;

Вивід:

                                                      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:

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

Вивід (у вигляді таблиці):

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
| 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:
Формування плану

EXPLAIN PLAN FOR
SELECT * FROM employees WHERE department_id = 10;

Крок 2:
Перегляд плану

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Вивід (у вигляді таблиці):

--------------------------------------------------------------------------------
| 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

SET SHOWPLAN_ALL ON;
GO
SELECT * FROM employees WHERE department_id = 10;
GO
SET SHOWPLAN_ALL OFF;

Вивід (у вигляді таблиці):

------------------------------------------------------------------------------------------------------------------
| 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) індекс:

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

CREATE INDEX idx_employee_department ON employees(department_id);

Як використовується:
При виконанні запиту з умовою за стовпцем department_id (наприклад, WHERE department_id = 10) оптимізатор запиту обирає індекс замість повного сканування таблиці.

Унікальний індекс:

Забезпечує унікальність значень у стовпці або наборі стовпців. При спробі вставити дубльоване значення СУБД видає помилку.

CREATE UNIQUE INDEX idx_unique_email ON employees(email);

Як використовується:
Якщо в таблиці employees не можна мати двох співробітників з однаковим email, унікальний індекс гарантує виконання цього обмеження та прискорює пошук за стовпцем email.

Композитний (мультиколонковий) індекс:

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

CREATE INDEX idx_employee_dept_lastname ON employees(department_id, last_name);

Як використовується:
При запиті індекс дозволить швидко вибрати рядки за обома стовпцями.

SELECT * FROM employees
WHERE department_id = 10 AND last_name = 'Ivanov';

Повнотекстовий індекс:

Використовується для швидкого пошуку за текстовими даними (наприклад, у полях з описом або вмістом статей). Він будується з урахуванням морфології та стоп-слів, що дозволяє виконувати пошук за словами та фразами.

MySQL:
Використовується для стовпців типу TEXT або VARCHAR.

CREATE FULLTEXT INDEX idx_fulltext_description ON products(description);

Після створення індексу можна виконувати запити з оператором MATCH … AGAINST:

SELECT * FROM products
WHERE MATCH(description) AGAINST('електроніка');

PostgreSQL:
використовує індекс типу GIN з функцією to_tsvector для повнотекстового пошуку.

CREATE INDEX idx_fts_content ON articles USING gin(to_tsvector('russian', content));

Пошук виконується так:

SELECT * FROM articles
WHERE to_tsvector('russian', content) @@ to_tsquery('електроніка');

SQL Server:
має вбудований механізм повнотекстового пошуку. Спочатку необхідно створити повнотекстовий каталог, потім повнотекстовий індекс.

-- Створення каталогу повнотекстового пошуку
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;


-- Створення повнотекстового індексу за таблицею products
CREATE FULLTEXT INDEX ON products(description)
KEY INDEX PK_products ON ftCatalog;

Просторовий індекс:

Просторові індекси використовуються для прискорення запитів за геометричними даними (точки, лінії, полігони). Вони оптимізовані для пошуку за двовимірними (або багатовимірними) просторовими координатами.

MySQL:
При використанні типів даних GEOMETRY, POINT тощо. Тут geom – стовпець, що містить геометричні дані.

CREATE SPATIAL INDEX idx_spatial_location ON locations(geom);

PostgreSQL з розширенням PostGIS:
Для просторових даних використовується індекс GIST. Такий індекс прискорює просторові запити, наприклад, пошук об'єктів у певному радіусі.

CREATE INDEX idx_spatial_location ON locations USING gist(geom);

Oracle:
Підтримує просторові індекси через Oracle Spatial. Приклад створення:

CREATE INDEX idx_spatial_location ON locations(geom)
INDEXTYPE IS MDSYS.SPATIAL_INDEX;

Функціональний (обчислюваний) індекс:

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

Oracle і PostgreSQL:
Припустимо, потрібен індекс для швидкого пошуку за email без урахування регістру:

CREATE INDEX idx_lower_email ON employees(LOWER(email));

Запит буде використовувати індекс, якщо умова записана так:

SELECT * FROM employees WHERE LOWER(email) = '[email protected]';

SQL Server:
У SQL Server можна створити індекс на обчислюваний стовпець. Спочатку додають обчислюваний стовпець, а потім створюють за ним індекс:

ALTER TABLE employees ADD lower_email AS LOWER(email);
CREATE INDEX idx_lower_email ON employees(lower_email);

Copyright: Roman Kryvolapov