Методическое руководство для преподавателя

1. Регламент и хронометраж учебного занятия

Занятие рассчитано на 2 академических часа. Ниже приведен детальный поминутный тайминг для основного регламента (80 минут) и сокращенного расписания (60 минут).

2. Теоретико-методический базис физического индексирования

2.1. Структура сбалансированного дерева B-Tree

В большинстве современных реляционных СУБД (PostgreSQL, MySQL/InnoDB, Oracle, MS SQL Server) стандартным типом индекса по умолчанию является B-Tree (сбалансированное дерево поиска). Преподавателю важно подчеркнуть его физические свойства:

• Сбалансированность: Все листовые узлы (Leaf Nodes) находятся на строго одинаковой глубине от корня, что гарантирует время поиска (\log N)$ операций ввода-вывода (I/O).
• Высокий коэффициент ветвления (Fan-out): В узле дерева хранится не один ключ (как в бинарном дереве), а сотни ключей и указателей на страницы данных. Благодаря этому дерево глубиной в 3–4 уровня способно адресовать сотни миллионов записей.
• Двусвязный список листьев: Листовые страницы связаны между собой двунаправленными указателями, что обеспечивает колоссальную эффективность операций выборки диапазонов (BETWEEN, >=, <=) и сортировки (ORDER BY) без повторного обращения к корню.

2.2. Уникальные vs Неуникальные индексы

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

• CREATE UNIQUE INDEX: гарантирует, что ни одна комбинация значений столбцов не повторится. Используется для альтернативных ключей (номер паспорта, ИНН, трек-номер, email). При вставке СУБД выполняет предварительную проверку наличия ключа в дереве (Unique Check).
• CREATE INDEX (неуникальный): оптимизирует поиск по полям с множественными совпадениями (внешние ключи, даты, категории, статусы).

2.3. Составные индексы (Composite Index) и правило левого префикса

Составной индекс строится по нескольким столбцам: CREATE INDEX idx_abc ON T (A, B, C);. Данные в B-Tree сортируются строго лексикографически: сначала по колонке A, внутри одинаковых A — по B, внутри одинаковых B — по C.

2.4. Кластеризованные vs Некластеризованные индексы

Преподавателю необходимо разъяснить различие в архитектурах хранения данных различных СУБД:

• Кластеризованный индекс (Clustered Index / Index-Organized Table): Сама таблица физически упорядочена на диске по ключу индекса (в MySQL InnoDB таблица ВСЕГДА хранится как B-Tree первичного ключа; в MS SQL Server — первичный ключ по умолчанию кластеризован). Листья такого индекса содержат сами строки данных.
• Некластеризованный индекс (Secondary / Non-clustered Index): Отдельная структура B-Tree, листья которой хранят только индексируемые значения и указатель на физическое местоположение строки:
  • В PostgreSQL — указатель TID / ctid (номер страницы блока и смещение строки в блоке).
  • В MySQL InnoDB — значение первичного ключа (что требует двойного поиска: сначала по вторичному индексу, затем по кластеризованному дереву PK).

2.5. Влияние индексов на скорость чтения vs Накладные расходы (Read vs Write Trade-off)

2.6. Внешние ключи (Foreign Keys) и критическая необходимость их индексации

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

• СУБД автоматически создает уникальный индекс только на первичный ключ (PRIMARY KEY).
• Столбец внешнего ключа в дочерней таблице (например, Transactions.id_счета) по умолчанию НЕ проиндексирован!
• Катастрофические последствия отсутствия индекса на FK:
  1. При операциях соединения JOIN дочерняя таблица сканируется последовательно (Seq Scan), что обрушивает производительность отчетов и выборок.
  2. При выполнении DELETE или UPDATE в родительской таблице (например, удаление или блокировка счета) СУБД обязана проверить отсутствие ссылок в дочерней таблице. Без индекса на FK СУБД накладывает блокировку на всю дочернюю таблицу и запускает полный просмотр (Seq Scan) миллионов транзакций, парализуя работу всей информационной системы!

2.7. Чтение планов выполнения запросов EXPLAIN (Seq Scan vs Index Scan)

Оптимизатор запросов СУБД выбирает план выполнения на основе внутренней статистики распределения данных:

-- Пример плана без индекса:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-> Seq Scan on orders (cost=0.00..45210.00 rows=15 width=72) (actual time=0.045..142.310 rows=15 loops=1)
   Filter: (customer_id = 42)
   Rows Removed by Filter: 1999985

-- Пример плана с B-Tree индексом:
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
-> Index Scan using idx_orders_customer on orders (cost=0.43..12.50 rows=15 width=72) (actual time=0.021..0.052 rows=15 loops=1)
   Index Cond: (customer_id = 42)

• cost=0.43..12.50: стартовая стоимость (поиск в B-Tree) и полная стоимость выборки. При Seq Scan стоимость исчисляется десятками тысяч.
• Rows Removed by Filter: 1999985 — очевидный признак катастрофически неэффективного запроса: система прочитала 2 миллиона строк с диска, чтобы вернуть всего 15.
• Bitmap Index Scan: промежуточный вариант, когда СУБД сначала формирует в оперативной памяти битовую карту страниц по индексу, а затем пакетом читает страницы с диска (используется при выборке сотен или тысяч подходящих строк).

3. Типовые ошибки студентов и педагогические приемы