Индексы, представления и транзакции

В этом уроке ты узнаешь

Объяснять, зачем нужен индекс, и создавать его с помощью CREATE INDEX
Создавать представление с помощью CREATE VIEW и использовать его как таблицу
Управлять транзакциями с помощью BEGIN, COMMIT, ROLLBACK и понимать ACID

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

Индексы

Без индекса для WHERE city = 'Bakı' СУБД проверяет каждую строку таблицы (полный просмотр). Индекс хранит значения столбца в отсортированном виде и знает, в каких строках встречается каждое значение, — как предметный указатель в конце учебника. Тогда СУБД сразу переходит к нужным строкам. EXPLAIN QUERY PLAN показывает, как SQLite собирается выполнить запрос.

SQL· Измени код и запусти снова

EXPLAIN QUERY PLAN
SELECT first_name FROM students WHERE city = 'Bakı';

▸ Ожидаемый результат

id | parent | notused | detail
2 | 0 | 216 | SCAN students

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

SQL· Измени код и запусти снова

CREATE INDEX idx_students_city ON students (city);

EXPLAIN QUERY PLAN
SELECT first_name FROM students WHERE city = 'Bakı';

▸ Ожидаемый результат

id | parent | notused | detail
3 | 0 | 62 | SEARCH students USING INDEX idx_students_city (city=?)

С индексом: SEARCH ... USING INDEX — читаются только нужные строки.

Для столбцов PRIMARY KEY и UNIQUE СУБД создаёт индекс сама. CREATE UNIQUE INDEX и ускоряет поиск, и запрещает повторяющиеся значения. Ниже добавить второго ученика с e-mail Айсель не получается.

SQL· Измени код и запусти снова

CREATE UNIQUE INDEX idx_students_email ON students (email);

INSERT INTO students (first_name, last_name, email)
VALUES ('Test', 'User', 'aysel@example.com');

▸ Ожидаемый результат

Error: UNIQUE constraint failed: students.email

Представления (VIEW)

Определение

Представление (view)

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

Каждый раз заново писать JOIN трёх таблиц утомительно. Сохраним его один раз как представление student_scores — после этого запросы становятся совсем простыми.

SQL· Измени код и запусти снова

CREATE VIEW student_scores AS
SELECT s.first_name || ' ' || s.last_name AS student,
       c.title AS course,
       e.score
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
JOIN courses  AS c ON c.id = e.course_id;

SELECT student, course, score
FROM student_scores
WHERE score >= 93
ORDER BY score DESC;

▸ Ожидаемый результат

student | course | score
Rəşad Kərimov | Python Basics | 99
Fidan Cəfərova | Algebra | 97
Leyla Hüseynova | Geometry | 95
Fidan Cəfərova | Mechanics | 94
Nigar Rzayeva | World History | 93

SQL· Измени код и запусти снова

CREATE VIEW student_scores AS
SELECT s.first_name || ' ' || s.last_name AS student,
       c.title AS course,
       e.score
FROM enrollments AS e
JOIN students AS s ON s.id = e.student_id
JOIN courses  AS c ON c.id = e.course_id;

SELECT course, COUNT(*) AS students, MAX(score) AS best
FROM student_scores
GROUP BY course
ORDER BY best DESC
LIMIT 3;

▸ Ожидаемый результат

course | students | best
Python Basics | 3 | 99
Algebra | 4 | 97
Geometry | 3 | 95

С представлением можно работать как с обычной таблицей: WHERE, GROUP BY, ORDER BY и даже JOIN.

Транзакции

Перевод 30 манатов со счёта Айсель на счёт Мурада — это две команды: списать с одного счёта и зачислить на другой. Если после первой команды пропадёт электричество, деньги исчезнут! Транзакция объединяет несколько команд в одно неделимое действие: либо выполняются все (COMMIT), либо ни одна (ROLLBACK).

SQL· Измени код и запусти снова

CREATE TABLE accounts (
  id      INTEGER PRIMARY KEY,
  owner   TEXT NOT NULL,
  balance INTEGER NOT NULL CHECK (balance >= 0)
);
INSERT INTO accounts (owner, balance) VALUES ('Aysel', 100), ('Murad', 50);

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 30 WHERE owner = 'Aysel';
UPDATE accounts SET balance = balance + 30 WHERE owner = 'Murad';
COMMIT;

SELECT owner, balance FROM accounts ORDER BY id;

▸ Ожидаемый результат

owner | balance
Aysel | 70
Murad | 80

COMMIT подтверждает оба изменения вместе. Если попытаться списать у Айсель 150 манатов, CHECK отклонит команду, и программа отменит весь перевод с помощью ROLLBACK.

ROLLBACK отменяет все изменения, сделанные после BEGIN. Ниже мы по ошибке удаляем все заказы, но так как мы внутри транзакции, ROLLBACK возвращает их все.

SQL· Измени код и запусти снова

BEGIN TRANSACTION;
DELETE FROM orders;                      -- oops, no WHERE!
SELECT COUNT(*) AS inside FROM orders;   -- 0 inside the transaction
ROLLBACK;                                -- undo everything since BEGIN

SELECT COUNT(*) AS after_rollback FROM orders;

▸ Ожидаемый результат

after_rollback
12

Буква	Свойство	Смысл
A	Atomicity	атомарность: всё или ничего
C	Consistency	согласованность: после транзакции все правила (ограничения) соблюдены
I	Isolation	изолированность: одновременные транзакции не мешают друг другу
D	Durability	долговечность: подтверждённое изменение сохраняется даже после сбоя

ACID — четыре свойства надёжных транзакций.

СУБД	Начало транзакции	План запроса
SQLite	BEGIN TRANSACTION	EXPLAIN QUERY PLAN
MySQL	START TRANSACTION	EXPLAIN
PostgreSQL	BEGIN	EXPLAIN
SQL Server	BEGIN TRANSACTION	план выполнения (Execution Plan)

COMMIT и ROLLBACK везде одинаковы.

Задание

Создай представление city_stats, которое показывает число учеников (students) в каждом городе. Затем выбери из него города, где не меньше 2 учеников: по убыванию числа учеников, при равенстве — по названию города.

Задание · SQL· Измени код и запусти снова

CREATE VIEW city_stats AS
-- the query that counts students per city
;

SELECT city, students
FROM city_stats
-- keep cities with at least 2 students and sort

▸ Ожидаемый результат

city | students
Bakı | 4
Gəncə | 2
Sumqayıt | 2

Задание

Оформи продажу в одной транзакции: покупатель № 5 покупает 2 штуки товара № 3 (Headphones) 2025-08-01. Добавь заказ в orders, уменьши остаток товара на 2 и выполни COMMIT. Затем выведи для Headphones название, остаток (stock) и число заказов (orders).

Задание · SQL· Измени код и запусти снова

BEGIN TRANSACTION;
-- INSERT the order
-- UPDATE the stock
COMMIT;

SELECT name,
       stock,
       (SELECT COUNT(*) FROM orders WHERE product_id = 3) AS orders
FROM products
WHERE id = 3;

▸ Ожидаемый результат

name | stock | orders
Headphones | 38 | 3

Главное

Индекс ускоряет поиск, но занимает место и замедляет запись.
CREATE UNIQUE INDEX и ускоряет поиск, и запрещает повторы.
Представление (CREATE VIEW) — сохранённый запрос с именем, который используют как таблицу.
Транзакция: BEGIN → команды → COMMIT (подтвердить) или ROLLBACK (отменить).
ACID: атомарность, согласованность, изолированность, долговечность.

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

1 / 10

Для чего нужен индекс?