Домашнее задание - Практическое занятие №6: ключевые поля, индексы, связи

Задание 1. Обоснование выбора полей для индексации и проблема внешних ключей

Рассмотрите реляционную схему интернет-магазина, состоящую из двух таблиц:

• Клиенты (id [PK], фио, email, телефон, город, дата_регистрации)
• Заказы (id [PK], client_id [FK], номер_заказа, сумма, статус, дата_создания)

Вопрос: Создает ли СУБД (PostgreSQL / Oracle) физический индекс на поле client_id в таблице Заказы автоматически при объявлении ограничения FOREIGN KEY? К каким двум тяжелым инженерным последствиям приводит отсутствие индекса на внешнем ключе?

Задание 2. Анализ правила левого префикса для составного индекса (Composite Index)

Для таблицы Товары (id, категория_id, бренд_id, цена, рейтинг, остаток) спроектирован составной B-Tree индекс:

CREATE INDEX idx_goods_filter ON Товары (категория_id, бренд_id, цена);

Проанализируйте 4 запроса и определите для каждого из них: сможет ли СУБД использовать данный индекс для быстрого поиска по дереву B-Tree? Обоснуйте ответ правилом левого префикса (Left-Prefix Rule).

1. SELECT * FROM Товары WHERE категория_id = 5 AND бренд_id = 12 AND цена < 5000;
2. SELECT * FROM Товары WHERE категория_id = 5 AND цена < 5000;
3. SELECT * FROM Товары WHERE бренд_id = 12 AND цена < 5000;
4. SELECT * FROM Товары WHERE категория_id = 5 AND бренд_id = 12;

Задание 3. Анализ плана выполнения запроса EXPLAIN и синтез индекса

В высоконагруженной системе логистики выполняется критический запрос поиска активных доставок:

SELECT * FROM deliveries 
WHERE courier_id = 42 AND delivery_date = '2026-10-01' AND status = 'IN_TRANSIT';

Команда EXPLAIN ANALYZE вернула следующий план выполнения:

Seq Scan on deliveries (cost=0.00..89240.00 rows=3 width=110) (actual time=12.420..184.210 rows=3 loops=1)
  Filter: ((courier_id = 42) AND (delivery_date = '2026-10-01'::date) AND (status = 'IN_TRANSIT'::text))
  Rows Removed by Filter: 4999997
Total runtime: 184.320 ms

Задание:

1. Проанализируйте метрики плана (тип узла, cost, Rows Removed by Filter, время). В чем заключается критический дефект?
2. Напишите SQL-команду создания оптимального индекса для этого запроса.
3. Опишите, как изменится план выполнения запроса после добавления индекса (тип сканирования, стоимость, время отклика).

Задание 4. Анализ компромисса чтения и записи (DML Overhead)

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

Оцените данное предложение с точки зрения архитектора баз данных. Что произойдет с операциями INSERT при потоке в 3 000 транзакций в секунду? Как изменятся накладные расходы дискового ввода-вывода (I/O) и объем WAL-журнала?