Рабочий лист: Практическое занятие №6: ключевые поля, индексы, связи

Дисциплина «ОП.08 Основы проектирования баз данных» — Занятие №18 (СИЭУиП)
Выберите вариант практического задания:
Вариант 1: Высоконагруженная СУБД интернет-банкинга

Часть 1. Анализ предметной области, связей и индексация внешнего ключа

Рассматривается ядро транзакционной банковской системы (OLTP), состоящее из двух ключевых сущностей:

Accounts (Счета): id_счета [PK], id_клиента, номер_счета (UNIQUE), валюта, баланс, дата_открытия.
Transactions (Транзакции): id_транзакции [PK], id_счета [FK], дата_транзакции, сумма, тип_операции, назначение.

Задание 1.1: Напишите SQL-скрипт объявления внешнего ключа между таблицами и команду индексации внешнего ключа. Объясните, почему индексация FK id_счета критически необходима для устранения каскадных блокировок и ускорения JOIN.

Часть 2. Синтез составного B-Tree индекса для банковской выписки

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

SELECT * FROM Transactions 
WHERE id_счета = ? AND дата_транзакции BETWEEN ? AND ? 
ORDER BY дата_транзакции DESC;

Задание 1.2: Спроектируйте оптимальный составной B-Tree индекс. Обоснуйте порядок колонок с точки зрения правила левого префикса (Left-Prefix Rule), селективности предикатов (равенство vs диапазон) и устранения фазы сортировки в оперативной памяти.

Часть 3. Анализ ускорения по EXPLAIN, накладные расходы DML и самопроверка

В таблице транзакций накоплено 10 000 000 строк. Средний клиент совершает 40 операций в месяц.

Задание 1.3:

  1. Сравните планы выполнения Seq Scan и Index Scan: сколько страниц считывается в каждом случае и как меняется метрика cost?
  2. Оцените накладные расходы (DML Overhead): что происходит при вставке новой транзакции (расщепление страниц Page Split, рост WAL)? Почему нельзя индексировать все поля подряд?
  3. Самопроверка: Если запрос ищет транзакции только по WHERE дата_транзакции BETWEEN ..., будет ли использоваться составной индекс (id_счета, дата_транзакции)? Обоснуйте ответ.
Вариант 2: Биллинговая система оператора связи

Часть 1. Анализ предметной области и схемы таблиц

Subscribers (Абоненты): id_абонента [PK], номер_телефона [UNIQUE], фио, баланс.
Tariffs (Тарифы): id_тарифа [PK], название, цена_минуты.
Calls (Звонки): id_звонка [PK], номер_телефона, id_тарифа [FK], время_начала, длительность, статус ('ACTIVE', 'COMPLETED', 'FAILED').

Задание 2.1: Напишите DDL-команды создания связей и индексов для таблицы звонков (включая индекс внешнего ключа тарифа и поиск абонента по номеру телефона). Объясните, чем чревато отсутствие индекса на FK при удалении тарифа.

Часть 2. Проектирование частичного индекса (Partial Index)

В таблице Calls хранится 100 000 000 архивных вызовов. В любой момент времени статус 'ACTIVE' имеют не более 50 000 звонков (0.05% строк). Биллинговому сервису необходимо ежесекундно проверять активные звонки абонента для тарификации:

SELECT * FROM Calls WHERE статус = 'ACTIVE' AND номер_телефона = ?;

Задание 2.2: Спроектируйте частичный индекс (Partial Index). Обоснуйте колоссальную экономию оперативной памяти (RAM) и кэша Buffer Pool по сравнению со стандартным B-Tree индексом на 100 млн записей.

Часть 3. Анализ EXPLAIN, накладные расходы и самопроверка

Задание 2.3:

  1. Как изменится план выполнения EXPLAIN при использовании частичного индекса?
  2. Что происходит с частичным индексом, когда звонок завершается и статус меняется на 'COMPLETED'?
  3. Самопроверка: Почему создание B-Tree индекса на обычное поле со статусом (без условия WHERE) является ошибкой при низкой селективности статуса?
Вариант 3: Каталог недвижимости

Часть 1. Анализ предметной области и схемы таблиц

Cities (Города): город_id [PK], название.
Realtors (Риелторы): риелтор_id [PK], фио, телефон.
Properties (Объекты): объект_id [PK], город_id [FK], риелтор_id [FK], цена, комнат, площадь, статус.

Задание 3.1: Напишите DDL-скрипт создания связей по внешним ключам и их обязательной физической индексации. Обоснуйте необходимость индексов на FK при генерации отчетов по агентствам недвижимости.

Часть 2. Составной индекс по диапазону и селективность колонок

Пользователи каталога непрерывно ищут квартиры по сложному фильтру с диапазоном цен:

SELECT * FROM Properties 
WHERE город_id = 42 AND комнат = 2 AND цена BETWEEN 5000000 AND 9000000;

Задание 3.2: Спроектируйте оптимальный составной B-Tree индекс (город_id, комнат, цена). Обоснуйте, почему диапазонное поле цены обязано стоять строго последним. Что произойдет, если создать индекс как (цена, город_id, комнат)?

Часть 3. Анализ плана EXPLAIN, DML Overhead и самопроверка

Задание 3.3:

  1. Опишите разницу в планах EXPLAIN между правильным индексом (город_id, комнат, цена) и ошибочным (цена, город_id, комнат).
  2. Оцените накладные расходы при регулярном обновлении стоимости объектов риелторами (UPDATE цены).
  3. Самопроверка: Сможет ли СУБД использовать составной индекс (город_id, комнат, цена) для запроса WHERE комнат = 2 AND цена < 6000000 без указания город_id? Обоснуйте ответ правилом левого префикса.
Вариант 4: Служба логистики и доставки

Часть 1. Анализ предметной области и схемы таблиц

Couriers (Курьеры): id_курьера [PK], фио, транспорт.
Routes (Маршруты): id_маршрута [PK], название_зоны.
Shipments (Заказы): id_заказа [PK], трек_номер, id_курьера [FK], id_маршрута [FK], дата_доставки, статус.

Задание 4.1: Напишите DDL-команды создания уникального индекса на трек_номер и индексов на внешние ключи. Объясните двойную роль уникального индекса (бизнес-ограничение целостности + быстрый поиск).

Часть 2. Индекс по внешнему ключу курьера для оперативных отчетов

Каждое утро диспетчер логистического центра формирует путевой лист для сотен курьеров:

SELECT * FROM Shipments 
WHERE id_курьера = ? AND дата_доставки = CURRENT_DATE 
ORDER BY id_заказа;

Задание 4.2: Спроектируйте составной индекс для ускорения ежедневных отчетов диспетчера. Обоснуйте порядок колонок и влияние индекса на устранение операций Seq Scan.

Часть 3. Анализ EXPLAIN, накладные расходы DML и самопроверка

Задание 4.3:

  1. Как выглядит план выполнения EXPLAIN при поиске по трек-номеру через уникальный индекс по сравнению с полным сканированием?
  2. В логистической системе регистрируется 500 000 новых отправлений в сутки. Каковы накладные расходы проверки уникальности (Unique Check Overhead) при массовом INSERT?
  3. Самопроверка: В чем отличие кластеризованного индекса от некластеризованного при поиске посылок курьера?
Ответы сохранены в браузере!