Все вопросы:

В этой статье:
(нажмите для перехода)

➤ Какой синтаксис запроса в SQL
➤ Что такое VIEW (представление) в SQL
➤ Что такое INDEX (индексы) в SQL
➤ Как избежать конкуренции при доступе к записи в SQL
➤ Какие существуют виды отношений в SQL
➤ Как сделать соотношение Many-to-Many в SQL
➤ Как в SQL объединить запрос к нескольким таблицам
➤ Как использовать план запроса в SQL
➤ Как использовать индексы в SQL

➤ Какой синтаксис запроса в SQL

В SQL существует множество ключевых слов, которые используются для создания, изменения, управления и извлечения данных из баз данных. Приведу основные категории и ключевые слова, используемые в SQL:

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

SELECT:
выбирает данные из таблицы.

SELECT * 
FROM users 
WHERE age > 30;

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

INSERT:
добавляет новые записи в таблицу.

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

UPDATE:
обновляет существующие записи в таблице.

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

DELETE:
удаляет записи из таблицы.

DELETE 
FROM users 
WHERE age < 18;

MERGE:
объединяет данные из двух таблиц на основе условий.

MERGE INTO employees e
USING employees_updates eu
ON (e.employee_id = eu.employee_id)
WHEN MATCHED THEN
  UPDATE SET e.salary = eu.salary
WHEN NOT MATCHED THEN
  INSERT (employee_id, salary) VALUES (eu.employee_id, eu.salary);

CALL:
вызывает хранимую процедуру.

CALL calculate_salary(12345);

DDL (Data Definition Language) — Язык определения данных:
Ключевые слова для создания и модификации структуры базы данных (таблиц, индексов и других объектов).

CREATE:
создает новые объекты базы данных (таблицы, индексы и т.д.).

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

ALTER:
изменяет структуру существующих объектов базы данных.

ALTER 
TABLE products 
ADD COLUMN description TEXT;

DROP:
удаляет объекты базы данных (таблицы, индексы и т.д.).

DROP TABLE products;

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

TRUNCATE TABLE users;

COMMENT:
добавляет комментарии к объектам базы данных.

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

RENAME:
переименовывает объект базы данных.

RENAME 
TABLE old_products 
TO new_products;

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

GRANT:
предоставляет пользователям или ролям права на объекты базы данных.

GRANT 
SELECT, INSERT 
ON orders 
TO user_admin;

REVOKE:
отзывает права на объекты базы данных у пользователей или ролей.

REVOKE 
INSERT ON orders 
FROM user_admin;

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

BEGIN TRANSACTION:
начинает транзакцию.

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

COMMIT:
сохраняет изменения, сделанные в рамках текущей транзакции.

BEGIN TRANSACTION;
INSERT INTO orders (order_id, customer_id, order_total) VALUES (101, 5, 250.00);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (101, 20, 2);
COMMIT;

ROLLBACK:
отменяет изменения, сделанные в рамках текущей транзакции.

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

SAVEPOINT:
создает точку восстановления в транзакции.

BEGIN TRANSACTION;
UPDATE products SET price = price * 0.9 WHERE category = 'Electronics';
SAVEPOINT discount_applied;
DELETE FROM products WHERE discontinued = true;
ROLLBACK TO discount_applied;
COMMIT;

SET TRANSACTION:
устанавливает параметры для текущей транзакции.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT * FROM sales WHERE sale_date BETWEEN '2024-01-01' AND '2024-01-31';
COMMIT;

Ключевые слова для управления данными (Constraints):
Ключевые слова, используемые для задания ограничений на данные в таблицах.

PRIMARY KEY:
определяет уникальный идентификатор для каждой строки таблицы.

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

FOREIGN KEY:
устанавливает связь между двумя таблицами.

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

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

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

NOT NULL:
запрещает использование пустых значений для данного столбца.

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

CHECK:
определяет условие, которое должны выполнять данные.

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

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

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

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

WHERE:
фильтрует строки на основе условия.

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

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

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

HAVING:
фильтрует строки после группировки.

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

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

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

DISTINCT:
возвращает уникальные значения.

SELECT DISTINCT country
FROM customers;

LIMIT:
ограничивает количество возвращаемых строк (в некоторых СУБД, например MySQL).

SELECT * FROM products
ORDER BY price DESC
LIMIT 10;

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

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

Ключевые слова для работы с объединением таблиц:
Ключевые слова для объединения данных из нескольких таблиц.

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

SELECT o.order_id, c.first_name, c.last_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;

INNER JOIN:
возвращает строки с совпадающими значениями в обеих таблицах.

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

LEFT JOIN (или LEFT OUTER JOIN):
возвращает все строки из левой таблицы, даже если в правой таблице нет совпадений.

SELECT c.first_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

RIGHT JOIN (или RIGHT OUTER JOIN):
возвращает все строки из правой таблицы, даже если в левой таблице нет совпадений.

SELECT o.order_id, c.first_name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;

FULL JOIN (или FULL OUTER JOIN):
возвращает все строки из обеих таблиц с заполнением значений NULL, если нет совпадений.

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

CROSS JOIN:
возвращает декартово произведение двух таблиц (каждая строка из первой таблицы объединяется с каждой строкой из второй таблицы).

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

SELF JOIN:
соединяет таблицу с самой собой.

SELECT e1.first_name AS employee, e2.first_name AS manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.employee_id;

Ключевые слова для управления последовательностями и индексами:
Ключевые слова для создания и управления последовательностями и индексами.

CREATE INDEX:
создает индекс для столбцов таблицы для повышения производительности запросов.

CREATE INDEX idx_last_name ON employees(last_name);

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

CREATE SEQUENCE order_seq
START WITH 1000
INCREMENT BY 1;

DROP INDEX:
удаляет индекс.

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

DROP SEQUENCE:
удаляет последовательность.

DROP SEQUENCE order_seq;

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

CREATE VIEW:
создает представление.

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

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

DROP VIEW active_customers;

ALTER VIEW:
изменяет существующее представление.

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

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

CREATE PROCEDURE:
создает хранимую процедуру.

CREATE PROCEDURE increase_salary(IN emp_id INT, IN percent DECIMAL)
BEGIN
  UPDATE employees
  SET salary = salary + (salary * percent / 100)
  WHERE employee_id = emp_id;
END;

CREATE TRIGGER:
создает триггер, который автоматически выполняется при наступлении определенного события (например, INSERT, UPDATE, DELETE).

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

EXECUTE:
выполняет хранимую процедуру.

CALL increase_salary(101, 10);

DROP PROCEDURE:
удаляет хранимую процедуру.

DROP PROCEDURE increase_salary;

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

DROP TRIGGER set_created_at;

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

COUNT():
возвращает количество строк.

SELECT COUNT(*) AS total_employees
FROM employees;

SUM():
возвращает сумму значений.

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

AVG():
возвращает среднее значение.

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

MIN():
возвращает минимальное значение.

SELECT MIN(price) AS cheapest_product
FROM products;

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

SELECT MAX(price) AS most_expensive_product
FROM products;

Другие ключевые слова и операторы:

EXISTS:
проверяет, существуют ли строки, соответствующие условию.

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

IN:
проверяет, содержится ли значение в списке значений.

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

BETWEEN:
проверяет, находится ли значение в определённом диапазоне.

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

LIKE:
ищет строковые данные по шаблону.

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

IS NULL:
проверяет, содержит ли значение NULL.

SELECT *  
FROM employees  
WHERE manager_id IS NULL;

CAST():
преобразует одно значение в другой тип данных.

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

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

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

CASE:
возвращает значение на основе условий (аналог оператора if-else).

SELECT first_name, last_name,  
  CASE  
    WHEN salary >= 100000 THEN 'High'  
    WHEN salary >= 50000 THEN 'Medium'  
    ELSE 'Low'  
  END AS salary_level  
FROM employees;

WITH:
используется для создания временных результатов в запросе (CTE — Common Table Expression).

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

UNION:
используется для объединения результатов двух или более запросов. Он позволяет объединять строки из нескольких SELECT запросов в один набор результатов. При этом строки из разных запросов не должны дублироваться, если явно не используется оператор ALL

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

Это ключевые слова, которые чаще всего встречаются в SQL. Они используются для работы с данными и управления структурой базы данных. Каждая СУБД может иметь свои уникальные ключевые слова, но перечисленные выше — это стандартные команды SQL.

▲К списку вопросов▲

➤ Что такое VIEW (представление) в SQL

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

Представления могут использоваться для отображения данных из одной или нескольких таблиц, а также для объединения, фильтрации и агрегации данных.

Пример создания VIEW:

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

Здесь представление active_users содержит только тех пользователей из таблицы users, которые имеют статус «active». Теперь можно использовать это представление как обычную таблицу:

SELECT * FROM active_users;

Это запросит данные из представления, и фактически выполнится сохранённый запрос.

Преимущества использования представлений:

Упрощение сложных запросов:
Представления позволяют скрыть сложные запросы за простым именем, что упрощает работу с ними. Вместо того чтобы каждый раз писать длинные SQL-запросы, можно просто обратиться к представлению.

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

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

Обеспечение совместимости:
Если структура таблицы изменяется (например, добавляются новые столбцы), представление может служить слоем совместимости, который продолжает предоставлять старый интерфейс для существующих приложений.

Обновляемые представления:

Представления могут поддерживать обновление, вставку и удаление данных (если это возможно с точки зрения SQL-сервера и логики запроса). Но не все представления обновляемы. Например, если представление использует агрегатные функции (SUM, COUNT, AVG и т.д.) или объединение (JOIN), то такие представления обычно нельзя обновлять напрямую.

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

CREATE VIEW basic_users AS
SELECT id, name
FROM users;

Можно обновлять данные в этом представлении:

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

Ограничения представлений:

Не сохраняют данные:
Представления не хранят данные. Это означает, что каждый раз, когда вы делаете запрос к представлению, сервер базы данных заново выполняет запрос, на основе которого оно создано.

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

Производительность:
В зависимости от сложности запроса, на основе которого построено представление, использование представлений может замедлить выполнение запросов, особенно если оно обращается к большим таблицам или делает сложные операции.

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

Если представление больше не нужно, его можно удалить с помощью команды DROP VIEW:

DROP VIEW active_users;

VIEW — это мощный инструмент для упрощения работы с данными, повышения безопасности и скрытия сложности структуры базы данных. Оно позволяет создавать виртуальные таблицы, которые могут объединять данные, фильтровать их и предлагать пользователям более удобные способы взаимодействия с базой данных.

▲К списку вопросов▲

➤ Что такое INDEX (индексы) в SQL

Индексы в SQL — это специальные структуры данных, которые используются для ускорения поиска строк в таблицах. Индексы создаются на одном или нескольких столбцах таблицы, и они работают как указатели, позволяющие базе данных находить данные быстрее, чем если бы она выполняла полное сканирование таблицы (т.е. просмотр всех строк).

Как работают индексы:

Структура индекса:
Индексы часто организованы в виде деревьев (например, B-дерево или B+ дерево), что позволяет эффективно находить данные. Эти структуры позволяют выполнять поиск, вставку и удаление данных за логарифмическое время, что значительно быстрее по сравнению с полным сканированием таблицы.
Индексы содержат отсортированные данные столбцов, по которым они построены. Это позволяет базе данных быстро находить нужные строки.

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

Пример работы индекса:

Предположим, у нас есть таблица employees, которая содержит информацию о сотрудниках. В таблице есть столбец name, по которому часто выполняются запросы для поиска.

Без индекса:
Запрос вроде SELECT * FROM employees WHERE name = ‘Alice’ приведет к тому, что база данных должна будет проверить каждую строку в таблице (полное сканирование таблицы), чтобы найти строки с именем «Alice». Это особенно затратно для больших таблиц.

С индексом:
Если мы создадим индекс на столбец name с помощью команды:

CREATE INDEX idx_employee_name ON employees(name);

То запрос SELECT * FROM employees WHERE name = ‘Alice’ будет использовать этот индекс. База данных уже будет знать, где находятся записи с именем «Alice», благодаря индексу, что значительно ускорит выполнение запроса.

Преимущества индексов:

Ускорение запросов на выборку данных:
Индексы значительно ускоряют выполнение запросов SELECT, особенно тех, которые включают условия WHERE, JOIN, ORDER BY или GROUP BY.

Повышение производительности соединений (JOIN):
Если используются индексы на столбцах, по которым таблицы соединяются, это также ускоряет выполнение запросов с JOIN.

Упорядочение данных:
Индексы могут помочь упорядочить данные в запросах с ORDER BY, так как они уже содержат отсортированные значения столбцов

Недостатки индексов:

Замедление операций вставки, обновления и удаления:
Каждый раз, когда происходит вставка, обновление или удаление строки в таблице, база данных должна обновлять индекс, что требует дополнительных ресурсов. Чем больше индексов в таблице, тем больше накладных расходов на эти операции.

Использование памяти:
Индексы занимают дополнительное пространство на диске и в памяти. Если таблица содержит несколько индексов, то для каждого из них необходимо хранить копию данных столбцов в отсортированном виде.

Типы индексов в SQL:

Обычный индекс (Non-Unique Index):
Это индекс, который может содержать неуникальные значения. Подходит для столбцов, по которым выполняются частые запросы, но где значения могут повторяться.

CREATE INDEX idx_name ON employees(name);

Уникальный индекс (Unique Index):
Этот тип индекса требует, чтобы все значения в индексируемом столбце были уникальными. Он часто используется для обеспечения уникальности данных, например, для столбцов, которые должны содержать уникальные идентификаторы.

CREATE UNIQUE INDEX idx_unique_email ON employees(email);

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

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

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

CREATE INDEX idx_name_department ON employees(name, department);

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

CREATE FULLTEXT INDEX idx_fulltext_description ON articles(description);

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

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

Когда индексы не используются:

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

Маленький объем таблицы:
Если таблица очень маленькая, база данных может решить, что сканирование всей таблицы быстрее, чем использование индекса.

Использование функций на индексируемых столбцах:
Если в запросе используется функция (например, LOWER(name)), индекс может быть не использован, так как преобразование данных делает его непригодным для индексации.

Неправильные запросы:
Если запросы написаны так, что не позволяют использовать индекс (например, использование отрицательных условий NOT, <>, !=), это может снизить эффективность индекса.

Часто обновляемые или удаляемые таблицы:
В таблицах с частыми операциями вставки, удаления или обновления использование индексов может снижать производительность из-за необходимости их постоянного обновления.

▲К списку вопросов▲

➤ Как избежать конкуренции при доступе к записи в SQL

Допустим 2 пользователя открыли одну и ту же запись в базе данных, как можно избежать ситуации, когда первый пользователь обновил данные раньше второго, и второй работает с не актуальными данными

В данной ситуации возникает классическая проблема конкуренции при работе с одной и той же записью базы данных, которая может привести к тому, что второй пользователь перезапишет изменения, внесенные первым. Чтобы избежать таких конфликтов, существует несколько стратегий управления конкурентным доступом к данным. Вот основные подходы:\

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

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

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

Пример на SQL:

BEGIN TRANSACTION;

SELECT * FROM records
WHERE id = 1
FOR UPDATE;

-- Изменение данных...

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

COMMIT;

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

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

Оптимистическая блокировка предполагает, что конфликты при доступе к записи редки, поэтому изменения разрешаются, но при обновлении проверяется, что запись не была изменена с момента её чтения. Если запись была изменена, второй пользователь должен повторить операцию.

Это достигается путем хранения специального поля, такого как версия записи или временная метка последнего изменения. При обновлении записи приложение проверяет, что версия записи совпадает с той, что была прочитана в момент начала работы с ней. Если версия изменилась — операция отменяется или предлагается обновить данные.

Шаги работы:
Пользователь считывает запись вместе с её текущей версией.
При обновлении данные отправляются на сервер вместе с версией.
Сервер проверяет, совпадает ли версия в базе с переданной версией.
Если версии совпадают, данные обновляются и версия инкрементируется.
Если версии не совпадают, сервер отклоняет запрос, и пользователю показывается сообщение о конфликте.

Пример таблицы с полем версии:

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

Пример на SQL:

UPDATE records
SET name = 'New Name', version = version + 1
WHERE id = 1 AND version = 3;
-- Если это обновление затронуло 0 строк, значит, версия изменилась.

Использование временных меток (Timestamp-Based Concurrency Control)

Похожий подход на оптимистическую блокировку, но вместо хранения версии записи используется временная метка (например, поле updated_at или last_modified). Когда пользователь обновляет данные, база данных проверяет, не изменилась ли временная метка записи с момента её последнего чтения.

Пример таблицы с временной меткой:

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

Обновление с проверкой временной метки:

UPDATE records
SET name = 'New Name', updated_at = CURRENT_TIMESTAMP
WHERE id = 1 AND updated_at = '2024-09-03 12:00:00';

-- Если обновление не произошло (0 строк изменено), значит, запись была обновлена другим пользователем.

Применение версий данных на уровне приложения:
На уровне приложения можно реализовать логику, которая отслеживает состояние данных. Например, при редактировании данных пользователю может быть показана версия записи, которую он редактирует. Если другой пользователь внес изменения, приложение может либо предупредить пользователя, что данные устарели, либо предложить автоматическое слияние изменений.

Комбинирование подходов:
Часто оптимальный вариант — это комбинация методов. Например:
При работе с критически важными данными можно использовать пессимистическую блокировку.
Для менее критичных операций использовать оптимистическую блокировку или проверку временных меток.

▲К списку вопросов▲

➤ Какие существуют виды отношений в SQL

В реляционных базах данных отношения между таблицами описывают, как данные в одной таблице связаны с данными в другой. Основные виды отношений в SQL базах данных:

Один ко многим (One-to-Many)

Это самый распространённый тип отношений в базах данных. Он обозначает ситуацию, когда одна запись в таблице может быть связана с несколькими записями в другой таблице, но каждая из этих записей связана только с одной записью в первой таблице.

Пример:
Таблица authors (авторы) и таблица books (книги). Один автор может написать несколько книг, но каждая книга имеет только одного автора.

Реализация:
В таблице books добавляется внешний ключ, который ссылается на первичный ключ таблицы authors.

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

CREATE TABLE books (
    id INT PRIMARY KEY,
    title VARCHAR(100),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

Многие ко многим (Many-to-Many)

Этот тип отношения обозначает, что одна запись в первой таблице может быть связана с несколькими записями во второй таблице, и наоборот. Однако такие отношения не могут быть выражены напрямую в SQL. Для реализации такого отношения используется промежуточная таблица (связующая таблица), которая разбивает отношение «многие ко многим» на два отношения «один ко многим».

Пример:
Таблицы students (студенты) и courses (курсы). Один студент может быть записан на несколько курсов, и один курс может быть записан на нескольких студентов.

Реализация:
Промежуточная таблица students_courses, которая связывает таблицы students и courses.

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

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

CREATE TABLE students_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Один к одному (One-to-One)

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

Пример:
Таблицы users (пользователи) и user_profiles (профили пользователей). Каждый пользователь имеет только один профиль, и каждый профиль связан только с одним пользователем.

Реализация:
В таблице user_profiles внешний ключ, который также является уникальным (или первичным), ссылается на users.

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

CREATE TABLE user_profiles (
    user_id INT PRIMARY KEY,
    address VARCHAR(255),
    phone VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Самоссылающееся отношение (Self-Referencing Relationship)

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

Пример:
Таблица employees (сотрудники), где один сотрудник может быть начальником другого сотрудника.

Реализация:
В таблице employees добавляется внешний ключ, который ссылается на первичный ключ той же таблицы.

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

Примеры использования отношений:

Один ко многим:
Это наиболее часто встречающийся тип связи. Используется, когда есть основная сущность (например, заказчик) и связанные с ней сущности (например, заказы).
Пример: Клиенты и их заказы (один клиент — много заказов).

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

Один к одному:
Используется для разделения сущностей по отдельным таблицам для улучшения структуры базы данных. Например, пользователь и его профиль.

Самоссылающиеся отношения:
Очень полезны при моделировании иерархий (например, сотрудники и их начальники, категории товаров и подкатегории).

▲К списку вопросов▲

➤ Как сделать соотношение Many-to-Many в SQL

Для создания отношения Many-to-Many (многие ко многим) в реляционных базах данных используется промежуточная (связующая) таблица, которая связывает два других объекта (таблицы). Это делается, потому что реляционные базы данных напрямую не поддерживают связь «многие ко многим» между двумя таблицами.

Пример:
Предположим, у нас есть две таблицы

Students (студенты)
Courses (курсы)

Каждый студент может записаться на несколько курсов, а каждый курс может быть записан многими студентами. Это типичная ситуация связи «многие ко многим».

Создаем таблицу students:

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

Создаем таблицу courses:

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

Создаем промежуточную таблицу students_courses:
Эта таблица связывает студентов и курсы через их идентификаторы (student_id и course_id). Она будет содержать по одному записи на каждое участие студента в конкретном курсе.

CREATE TABLE students_courses (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Что происходит в промежуточной таблице:
Таблица students_courses содержит два столбца: student_id и course_id. Вместе они составляют составной первичный ключ, что предотвращает дублирование записей (то есть, один студент не может записаться на один и тот же курс дважды).
Внешние ключи (FOREIGN KEY) используются для обеспечения целостности данных, указывая, что значения в student_id и course_id должны соответствовать существующим записям в таблицах students и courses соответственно.

Пример вставки данных:

Добавляем студентов:

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

Добавляем курсы:

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

Связываем студентов с курсами:

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

Пример запроса для получения данных:
Чтобы узнать, на какие курсы записан студент, можно использовать запрос с JOIN:

SELECT students.name, courses.title
FROM students
JOIN students_courses ON students.id = students_courses.student_id
JOIN courses ON students_courses.course_id = courses.id;

Этот запрос покажет имена студентов и названия курсов, на которые они записаны.

▲К списку вопросов▲

➤ Как в SQL объединить запрос к нескольким таблицам

В SQL существует несколько подходов для объединения данных из нескольких связанных таблиц в один результирующий набор.

Используем пример таблиц:

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  name         VARCHAR(100),
  city         VARCHAR(100)
);
CREATE TABLE orders (
  order_id      INTEGER PRIMARY KEY,
  customer_id   INTEGER,
  order_date    DATE,
  total_amount  DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
CREATE TABLE archived_orders (
  order_id      INTEGER PRIMARY KEY,
  customer_id   INTEGER,
  order_date    DATE,
  total_amount  DECIMAL(10,2)
  -- FOREIGN KEY
);

Объединение с помощью JOIN:

JOIN – самый распространённый и понятный способ «соединения» таблиц по общему полю. Используются различные виды соединений, в зависимости от того, какие строки вы хотите включить в результат:

INNER JOIN:
Возвращает только те строки, где значения в полях-соединителях совпадают в обеих таблицах.
Быстродействие: При наличии индексов на связывающих столбцах INNER JOIN часто работает очень быстро, так как СУБД применяет такие алгоритмы, как вложенные циклы (Nested Loops), сортировка слиянием (Merge Join) или хеш-соединения (Hash Join).

LEFT (или RIGHT) JOIN:
Возвращает все строки из левой (или правой) таблицы и совпадающие строки из другой таблицы. Если соответствия нет, то в столбцах правой (или левой) таблицы будут значения NULL.
Быстродействие: Обычно подобные соединения работают с похожей эффективностью, что и INNER JOIN, но могут быть менее эффективными при отсутствии индексов или при обработке большого количества NULL-значений.

FULL OUTER JOIN:
Объединяет строки из обеих таблиц, включая те строки, где соответствий нет (порождая NULL в недостающих полях).
Быстродействие: Этот тип JOIN может быть менее эффективным, так как требует дополнительных операций для объединения непересекающихся строк. Некоторые СУБД даже эмулируют FULL JOIN через объединение LEFT и RIGHT JOIN.

CROSS JOIN:
Производит декартово произведение двух таблиц (каждая строка из первой таблицы сочетается с каждой строкой из второй).
Быстродействие: Как правило, используется реже, поскольку при больших объёмах таблиц результат может быть крайне объёмным и, соответственно, затратным по времени.

Общие моменты по быстродействию JOIN:
Наличие индексов на полях соединения существенно улучшает производительность.
Выбор конкретного алгоритма соединения (nested loop, hash join, merge join) зависит от СУБД, объёма данных и наличия подходящих индексов.
Оптимизатор запросов может по-разному реагировать на сложные цепочки соединений; иногда стоит переписывать запросы или применять подсказки для оптимизации.

Пример: INNER JOIN
Получим список клиентов с информацией по их заказам

SELECT
  c.customer_id,
  c.name,
  c.city,
  o.order_id,
  o.order_date,
  o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;

Комментарий по быстродействию:
При наличии индексов по полю customer_id в обеих таблицах СУБД выберет оптимальный план (например, используя hash join или merge join). Если данные распределены нормально, такой запрос работает эффективно.

Пример: LEFT JOIN
Если требуется получить всех клиентов, даже если у них нет заказов

SELECT
  c.customer_id,
  c.name,
  c.city,
  o.order_id,
  o.order_date,
  o.total_amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

Комментарий по быстродействию:
В случае больших объёмов данных отсутствие заказов может привести к большему количеству возвращаемых строк с NULL-значениями, но при правильном индексировании производительность сохраняется на достойном уровне.

Объединение результатов с помощью UNION / UNION ALL:

UNION и UNION ALL позволяют объединить результаты нескольких SELECT-запросов в один результирующий набор, при этом строки одного SELECT располагаются под строками другого.

UNION:
При этом варианте СУБД автоматически устраняет дублирующиеся строки в итоговом наборе.
Быстродействие:
Дополнительный этап устранения дубликатов может потребовать значительных затрат ресурсов, особенно при большом количестве строк.

UNION ALL:
Объединяет результаты без удаления дубликатов.
Быстродействие:
Как правило, работает быстрее, поскольку отсутствует этап сортировки и проверки на дубликаты. Хорош для случаев, когда уникальность строк гарантирована на уровне данных или не критична.

Применимость:
Этот способ применим, если объединяемые запросы имеют одинаковое число столбцов и совместимые типы данных. Его используют, когда данные разбросаны по разным таблицам, но представляют собой логически однородные наборы.

Представим, что заказы хранятся в двух таблицах: активные заказы и архивные заказы. Чтобы получить полный список заказов, можно объединить результаты двух запросов.

Пример: UNION

SELECT order_id, customer_id, order_date, total_amount
FROM orders
UNION
SELECT order_id, customer_id, order_date, total_amount
FROM archived_orders;

Комментарий по быстродействию:
UNION выполняет дополнительную операцию по удалению дубликатов, что может замедлить выполнение при большом количестве записей.

Пример: UNION ALL

SELECT order_id, customer_id, order_date, total_amount
FROM orders
UNION ALL
SELECT order_id, customer_id, order_date, total_amount
FROM archived_orders;

Комментарий по быстродействию:
UNION ALL просто объединяет результаты без проверки дубликатов, поэтому работает быстрее.

SELECT product_id, quantity, total_price FROM sales_2022
UNION ALL
SELECT product_id, quantity, total_price FROM sales_2023;

Использование подзапросов и производных таблиц:

Вместо прямого объединения таблиц можно использовать подзапросы, которые возвращают данные из других таблиц. Подзапрос может находиться в секции SELECT, FROM или WHERE:

Подзапросы в секции FROM (производные таблицы):
Определяют временные таблицы, результаты которых затем объединяются с данными основного запроса.

Быстродействие:

Независимые подзапросы (non-correlated subqueries):
Обычно оптимизируются отдельно и могут быть сравнительно эффективными.

Коррелированные подзапросы:
Вычисляются для каждой строки основного запроса, что может привести к значительному замедлению, особенно при большом объёме данных.
Важно учитывать, что современные оптимизаторы могут переписывать запросы, объединяя их с основным запросом, но это не всегда происходит автоматически.

Подзапросы в секции SELECT:
Позволяют получить отдельные значения или агрегаты из другой таблицы для каждой строки основного запроса.

Пример: Подзапрос в секции SELECT
Получим список клиентов с количеством заказов для каждого:

SELECT
  c.customer_id,
  c.name,
  c.city,
  (SELECT COUNT(*)
   FROM orders o
   WHERE o.customer_id = c.customer_id) AS orders_count
FROM customers c;

Комментарий по быстродействию:

Если подзапрос коррелирован (выполняется для каждой строки), то при большом количестве клиентов может наблюдаться ухудшение производительности.
Некоторые СУБД умеют оптимизировать коррелированные подзапросы, но в критичных случаях имеет смысл рассмотреть агрегирование в отдельном запросе.

Пример: Подзапрос в секции FROM (производная таблица)
Агрегируем данные по заказам и соединяем с таблицей клиентов

SELECT
  c.customer_id,
  c.name,
  c.city,
  o.total_orders
FROM customers c
JOIN (
  SELECT customer_id, COUNT(*) AS total_orders
  FROM orders
  GROUP BY customer_id
) o ON c.customer_id = o.customer_id;

Комментарий по быстродействию:
Производная таблица агрегирует данные один раз, что зачастую эффективнее, чем выполнение коррелированного подзапроса для каждой строки.

Общие табличные выражения (CTE) с конструкцией WITH:

CTE позволяют создавать временные именованные результаты (как «виртуальные таблицы»), которые можно использовать в основном запросе. Это повышает читаемость запроса и упрощает структурирование сложной логики объединения.

Быстродействие:

CTE по сути являются алиасами для подзапросов. Их производительность часто аналогична подзапросам в секции FROM, но в некоторых СУБД может быть недостаточная оптимизация, если CTE не инлайнится.
При использовании рекурсивных CTE следует учитывать, что они могут быть менее эффективными, если глубина рекурсии высока.

Пример: CTE для вычисления количества заказов
Сначала создадим CTE, которое агрегирует заказы, а затем выполним LEFT JOIN с клиентами:

WITH OrderCounts AS (
  SELECT customer_id, COUNT(*) AS total_orders
  FROM orders
  GROUP BY customer_id
)
SELECT
  c.customer_id,
  c.name,
  c.city,
  COALESCE(oc.total_orders, 0) AS total_orders
FROM customers c
LEFT JOIN OrderCounts oc ON c.customer_id = oc.customer_id;

Комментарий по быстродействию:

Если CTE инлайнится оптимизатором, то они работают так же эффективно, как и подзапросы в секции FROM.
В некоторых СУБД CTE не оптимизируются («materialized»), что может повлиять на производительность при большом наборе данных.

Дополнительные моменты и рекомендации по быстродействию:

Оптимизация запросов:
Независимо от выбранного метода, наличие подходящих индексов – ключевой фактор быстродействия. Стоит также изучить план выполнения запроса (EXPLAIN PLAN или аналог) для выявления «узких мест».

Размеры таблиц и распределение данных:
При работе с большими таблицами все методы требуют тщательного тестирования. Например, если одна таблица содержит миллионы записей, а другая – тысячи, то оптимизатор может выбрать иной порядок соединений для повышения эффективности.

Особенности СУБД:
Разные системы (PostgreSQL, MySQL, Oracle, SQL Server) могут по-разному обрабатывать JOIN, UNION или CTE. Поэтому стоит протестировать запросы в конкретной среде и, при необходимости, применять специализированные настройки или подсказки для оптимизации (hints).

Читаемость vs. производительность:
Иногда более читаемая конструкция запроса (например, с использованием CTE) может уступать по производительности переписанному с JOIN’ами. Поэтому рекомендуется балансировать между поддерживаемостью и скоростью выполнения, проводя бенчмаркинг в реальной рабочей среде.

Заключение:

JOIN (INNER, LEFT/RIGHT, FULL, CROSS):
Наиболее распространённый способ с хорошей оптимизацией при наличии индексов.
Хорош для непосредственного объединения связанных данных, особенно при правильном индексировании.

UNION / UNION ALL:
Отлично подходит для объединения нескольких SELECT-запросов; UNION ALL предпочтительнее для производительности, если устранение дубликатов не требуется.
Полезны для объединения результатов из таблиц с одинаковой структурой (например, активные и архивные заказы).

Подзапросы и производные таблицы:
Универсальный инструмент, но следует избегать коррелированных подзапросов в случаях большого объёма данных.
Могут быть удобны для агрегирования или фильтрации данных, но следует быть осторожным с коррелированными подзапросами.

CTE (WITH):
Повышают читаемость сложных запросов, однако не всегда гарантируют улучшенное быстродействие.
Улучшают читаемость сложных запросов и позволяют переиспользовать промежуточные результаты, хотя иногда могут влиять на быстродействие, если не оптимизируются инлайн.

▲К списку вопросов▲

➤ Как использовать план запроса в SQL

План запроса (execution plan) — это подробное описание того, как СУБД собирается выполнить SQL-запрос. Он позволяет увидеть, какие операции выполняются (сканирование таблиц, использование индексов, соединения, сортировки, агрегации и т.д.), в каком порядке они происходят и как распределяются затраты по времени и ресурсам. Это важный инструмент для оптимизации запросов и диагностики проблем с производительностью.

PostgreSQL:

EXPLAIN:

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

Вывод:

                          QUERY PLAN                           
---------------------------------------------------------------
 Seq Scan on employees  (cost=0.00..35.50 rows=5 width=50)
   Filter: (department_id = 10)
(2 rows)

Объяснение:

Seq Scan on employees: последовательное сканирование таблицы employees.
cost=0.00..35.50: оценка затрат от начала до завершения операции.
rows=5: предполагаемое количество строк, удовлетворяющих условию.
width=50: средний размер строки в байтах.
Filter: (department_id = 10): условие фильтрации, применяемое при сканировании.

EXPLAIN ANALYZE:

EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 10;

Вывод:

                                                      QUERY PLAN                                                       
--------------------------------------------------------------------------------------------------------------------------
 Seq Scan on employees  (cost=0.00..35.50 rows=5 width=50) (actual time=0.021..0.040 rows=2 loops=1)
   Filter: (department_id = 10)
   Rows Removed by Filter: 3
Planning Time: 0.123 ms
Execution Time: 0.060 ms
(5 rows)

Объяснение:

actual time=0.021..0.040: фактическое время начала и окончания сканирования.
rows=2: фактическое число строк, прошедших фильтр.
Rows Removed by Filter: 3: количество строк, отсеянных фильтром.
Planning Time и Execution Time: дают представление о времени планирования запроса и его выполнения соответственно.

MySQL:

EXPLAIN:

EXPLAIN SELECT * FROM employees WHERE department_id = 10;

Вывод (в виде таблицы):

+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table     | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | employees | ref  | dep_index     | dep_index | 4       | const |    5 | Using where; Using index |
+----+-------------+-----------+------+---------------+-----------+---------+-------+------+--------------------------+

Объяснение:

id: идентификатор запроса (для более сложных запросов с подзапросами или соединениями).
select_type: тип запроса (SIMPLE означает отсутствие подзапросов).
table: имя таблицы.
type: тип доступа (например, ref означает использование индекса для поиска).
possible_keys: индексы, которые могли бы быть использованы.
key: фактически использованный индекс.
rows: оценочное число строк, которое будет прочитано.
Extra: дополнительные сведения, например, Using where указывает на применение фильтра.

Oracle:

Для Oracle зачастую выполняют два шага- сначала формируют план, затем его просматривают.

Шаг 1:
Формирование плана

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

Шаг 2:
Просмотр плана

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Вывод (в виде таблицы):

--------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |       |       |    3  |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |     5 |   120 |    3  |
|*  2 |   INDEX RANGE SCAN          | DEP_INDEX    |     5 |       |    2  |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------------
   1 - filter("DEPARTMENT_ID"=10)
   2 - access("DEPARTMENT_ID"=10)

Объяснение:

TABLE ACCESS BY INDEX ROWID: доступ к строкам через индекс.
INDEX RANGE SCAN: использование диапазонного сканирования по индексу DEP_INDEX.
Cost: оценка затрат на выполнение каждой операции.
Predicate Information: уточняет, какое условие применяется для каждой операции.

Microsoft SQL Server:

В SQL Server существует возможность получения плана выполнения как в графическом интерфейсе SSMS, так и через T-SQL.

Пример:
использование SHOWPLAN

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

Вывод (в виде таблицы):

------------------------------------------------------------------------------------------------------------------
| stmtid | nodeid | parent | physical_op          | logical_op           | argument                              |
------------------------------------------------------------------------------------------------------------------
| 1      | 0      | NULL   | Clustered Index Scan | Clustered Index Scan | Table: employees, Index: PK_employees |
| 1      | 1      | 0      | Filter               | Filter               | Predicate: department_id = 10         |
------------------------------------------------------------------------------------------------------------------

Объяснение:

Clustered Index Scan: сканирование с использованием кластерного индекса.
Filter: применение условия фильтрации.
stmtid, nodeid, parent: идентификаторы, определяющие иерархию операций в плане выполнения.

▲К списку вопросов▲

➤ Как использовать индексы в SQL

Индексы существенно повышают производительность запросов, однако их чрезмерное или неуместное использование может замедлить операции вставки, обновления и удаления из-за дополнительного обслуживания индекса. Выбор типа индекса определяется конкретными задачами:

B-Tree индекс:
стандарт для большинства случаев.

Уникальный индекс:
для обеспечения уникальности.

Композитный индекс:
для запросов с фильтрами по нескольким столбцам.

Полнотекстовый индекс:
для работы с текстовыми данными.

Пространственный индекс:
для географических и геометрических данных.

Функциональный индекс:
для оптимизации запросов с применением функций.

Обычный (B-Tree) индекс:

Стандартный тип индекса, который используется для большинства поисковых операций. Он строится по принципу бинарного дерева, что позволяет быстро находить строки по значению столбца.

CREATE INDEX idx_employee_department ON employees(department_id);

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

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

Обеспечивает уникальность значений в столбце или наборе столбцов. При попытке вставить дублирующее значение СУБД выдаёт ошибку.

CREATE UNIQUE INDEX idx_unique_email ON employees(email);

Как используется:
Если в таблице employees нельзя иметь два сотрудника с одинаковым email, уникальный индекс гарантирует выполнение этого ограничения и ускоряет поиск по столбцу email.

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

Создаётся по двум или более столбцам. Он полезен, когда запросы используют несколько столбцов в условиях фильтрации или соединения. Важно помнить, что порядок столбцов в индексе имеет значение.

CREATE INDEX idx_employee_dept_lastname ON employees(department_id, last_name);

Как используется:
При запросе индекс позволит быстро выбрать строки по обоим столбцам.

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

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

Используется для быстрого поиска по текстовым данным (например, в полях с описанием или содержимым статей). Он строится с учётом морфологии и стоп-слов, что позволяет выполнять поиск по словам и фразам.

MySQL:
Используется для столбцов типа TEXT или VARCHAR.

CREATE FULLTEXT INDEX idx_fulltext_description ON products(description);

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

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

PostgreSQL:
использует индекс типа GIN с функцией to_tsvector для полнотекстового поиска.

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

Поиск выполняется так:

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

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

-- Создание каталога полнотекстового поиска
CREATE FULLTEXT CATALOG ftCatalog AS DEFAULT;

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

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

Пространственные индексы используются для ускорения запросов по геометрическим данным (точки, линии, полигоны). Они оптимизированы для поиска по двумерным (или многомерным) пространственным координатам.

MySQL:
При использовании типов данных GEOMETRY, POINT и т.п. Здесь geom – столбец, содержащий геометрические данные.

CREATE SPATIAL INDEX idx_spatial_location ON locations(geom);

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

CREATE INDEX idx_spatial_location ON locations USING gist(geom);

Oracle:
Поддерживает пространственные индексы через Oracle Spatial. Пример создания:

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

Функциональный (вычисляемый) индекс:

Функциональные индексы создаются на основе выражений или функций, применяемых к столбцам. Это удобно, если запросы часто используют функции, например, для приведения к нижнему регистру, обрезки строк и т.п.

Oracle и PostgreSQL:
Допустим, требуется индекс для быстрого поиска по email без учета регистра:

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

Запрос будет использовать индекс, если условие записано так:

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

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

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

▲К списку вопросов▲

Вопросы и ответы — Реляционные SQL базы данных

Все вопросы:

В этой статье:(нажмите для перехода)

➤ Какой синтаксис запроса в SQL

➤ Что такое VIEW (представление) в SQL

➤ Что такое INDEX (индексы) в SQL

➤ Как избежать конкуренции при доступе к записи в SQL

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

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

Использование временных меток (Timestamp-Based Concurrency Control)

➤ Какие существуют виды отношений в SQL

Один ко многим (One-to-Many)

Многие ко многим (Many-to-Many)

Один к одному (One-to-One)

Самоссылающееся отношение (Self-Referencing Relationship)

➤ Как сделать соотношение Many-to-Many в SQL

➤ Как в SQL объединить запрос к нескольким таблицам

Объединение с помощью JOIN:

Объединение результатов с помощью UNION / UNION ALL:

Использование подзапросов и производных таблиц:

Общие табличные выражения (CTE) с конструкцией WITH:

Дополнительные моменты и рекомендации по быстродействию:

Заключение:

➤ Как использовать план запроса в SQL

PostgreSQL:

MySQL:

Oracle:

Microsoft SQL Server:

➤ Как использовать индексы в SQL

В этой статье:
(нажмите для перехода)