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