4 септември 2024

Релационни 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():
връща първата стойност в списъка, която не е NULL.

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 заявки, можем просто да се обърнем към изгледа.

Повишаване на сигурността:
Изгледите могат да се използват за ограничаване на достъпа до данните. Например на потребителите може да се предостави достъп само до определени колони или редове чрез изглед, а не до изходната таблица.

Абстракция на данните:
Изгледите помагат да се абстрахираме от сложните структури на базата данни. Например можем да създадем изглед, който обединява данни от няколко таблици, а потребителите може изобщо да не знаят как са свързани тези таблици.

Осигуряване на съвместимост:
Ако структурата на таблицата се промени (например се добавят нови колони), изгледът може да служи като слой за съвместимост, който продължава да предоставя стария интерфейс на съществуващите приложения.

Обновяеми изгледи:

Изгледите могат да поддържат обновяване, вмъкване и изтриване на данни (ако това е възможно от гледна точка на 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       |

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

Пълнотекстов индекс:

Използва се за бързо търсене в текстови данни (например в полета с описание или със съдържание на статии). Изгражда се с отчитане на морфологията и на стоп думите (stop words), което позволява търсене по думи и фрази.

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