Этап 1. Организационный момент и мотивация
80 мин: 5 минут (00:00–00:05) | 60 мин: 3 минуты (00:00–00:03)
Прямая речь преподавателя:
«Здравствуйте, коллеги! Прошу занять свои рабочие места и включить мониторы. Сегодня у нас знаковое занятие — занятие номер 20. Этим практическим занятием мы завершаем третий семестр изучения дисциплины "Основы проектирования баз данных" и подводим итоги Темы 3.
Представьте типичную ситуацию: вы спроектировали идеальную реляционную схему в третьей нормальной форме, залили в базу данные, запустили веб-сервис, и пока в базе было 1 000 строк — всё летало за доли миллисекунды. Но вот прошел год: в базе 5 000 000 клиентов. Пользователь вбивает в поле поиска номер договора или вводит фильтр "Регион ХМАО, статус активен, доход от 100 тысяч", нажимает кнопку — и сайт зависает на полминуты. Почему? База данных физически считывает гигабайты диска. Сегодня наша цель — освоить искусство проектирования поисковых фильтров по одному и нескольким полям и научиться составлять инженерный поисковый протокол с фиксацией реального времени отклика!»
Реакция и действия студентов:
Студенты запускают браузер, открывают локальный интерактивный лист zanyatie.html, проверяют готовность СУБД и авторизацию.
Этап 2. Актуализация опорных знаний
80 мин: 10 минут (00:05–00:15) | 60 мин: 7 минут (00:03–00:10)
Прямая речь преподавателя:
«Давайте освежим в памяти фундамент, который мы заложили на прошлых занятиях. Кто напомнит: как физически хранятся записи таблицы на диске, если на ней нет кластеризованного индекса?»
Ответ студента:
«Таблица хранится в виде неупорядоченной "кучи" — Heap. Страницы по 8 килобайт заполняются по мере вставки, и строки лежат там в произвольном порядке без какой-либо сортировки».
Прямая речь преподавателя:
«Абсолютно верно! А если мы пишем WHERE id = 500 без индекса — что делает СУБД?»
Ответ студента:
«СУБД запускает Sequential Scan — последовательно читает абсолютно все страницы файла таблицы с диска и сравнивает каждую строку в процессоре. Вычислительная сложность составляет $O(N)$».
Прямая речь преподавателя:
«Именно. А когда мы строим индекс B-Tree, сложность превращается в $O(\log N)$. Для 10 миллионов записей глубина дерева — всего 3 или 4 страницы! СУБД считывает 3 страницы индекса, находит точный физический адрес строки — TID (Block Number, Offset) — и делает ровно одно обращение к куче. Время выполнения падает с 5 000 миллисекунд до 0.04 миллисекунды. Зафиксируйте эту разницу».
Этап 3. Методический разбор поисковых механизмов и инструктаж
80 мин: 15 минут (00:15–00:30) | 60 мин: 10 минут (00:10–00:20)
Прямая речь преподавателя:
«Переходим к главному техническому разбору. На слайдах 4–10 представлены четыре базовых сценария поиска, с которыми вы столкнетесь в своей инженерной практике.
Первый сценарий — точечный поиск по первичному/уникальному ключу. Предикат равенства WHERE contract_number = '...'. Работает узел Index Scan. Время < 0.05 мс.
Второй сценарий — диапазонный поиск. Предикаты BETWEEN, >=, <=. Почему B-Tree идеально подходит для диапазонов? Потому что листовые узлы соединены двусвязным списком! СУБД спускается от корня к левой границе за $O(\log N)$, а дальше просто бежит по листьям горизонтально. И самое приятное: если в запросе есть ORDER BY, база данных выдает строки уже отсортированными — узел Sort в плане просто исчезает!
Третий сценарий — сопоставление с шаблоном через оператор LIKE. Здесь совершается 80% ошибок на собеседованиях. Если мы пишем LIKE 'Иван%' — это левый префикс. СУБД превращает это в диапазон: "от строки Иван до строки Ивао", и B-Tree работает! Но если вы напишете LIKE '%Иван%' — B-Tree бесполезен! Телефонную книгу нельзя открыть с конца или середины без знания первой буквы фамилии. В продакшне такой запрос роняет сервер в Sequential Scan!
Четвертый сценарий — поиск по нескольким полям и правило составного индекса. Запомните золотое правило: "Равенства вперед, диапазоны в конец!". Если ваш фильтр содержит WHERE region = 'Югра' AND status = 'ACTIVE' AND income > 100000, то индекс должен быть строго: (region, status, income). Поставьте диапазон первым — и вы заставите базу сканировать гигабайты чужих регионов!
В рабочем листе zanyatie.html перед каждым из вас 4 варианта: Интернет-банк, Библиотека, Поликлиника и Service Desk. Ваша задача — составить SQL-запросы, определить необходимые индексы, замерить время и заполнить поисковый протокол».
Сокращенный регламент (60 минут): Преподаватель опускает подробный вывод математики векторизации tsvector и сразу фокусирует студентов на правиле левого префикса и таблице поискового протокола.
Этап 4. Практическая самостоятельная работа студентов
80 мин: 40 минут (00:30–01:10) | 60 мин: 32 минуты (00:20–00:52)
Прямая речь преподавателя:
«Приступаем к индивидуальному выполнению. В рабочем листе укажите свою фамилию и учебную группу. Обратите внимание: все ваши данные автоматически сохраняются в памяти браузера через
localStorage.
Распределение вариантов:
- Вариант 1 (Интернет-банк): Номер договора, маска ФИО, комплексный фильтр региона и доходов.
- Вариант 2 (Каталог библиотеки): ISBN книги, поиск по автору, полнотекстовый поиск по аннотации через FTS и GIN-индекс.
- Вариант 3 (Реестр медицинских карт): Полис ОМС, диапазон дат визитов, фильтрация отделения и диагноза.
- Вариант 4 (Служба поддержки Service Desk): ID тикета, префикс темы, очередь инженера по приоритету и статусу.
Я подхожу для индивидуальных консультаций. При анализе планов выполнения обязательно указывайте реальный узел:
Index Scan,
Bitmap Index Scan или
Seq Scan».
Действия студентов:
Студенты формулируют DDL-команды создания индексов (B-Tree, составные, GIN), пишут поисковые SELECT-запросы с предикатами, анализируют время отклика и заносят метрики в интерактивные таблицы протоколов. Отвечают на вопросы блока самопроверки.
Этап 5. Защита поисковых протоколов, подведение итогов 3 семестра и рефлексия
80 мин: 10 минут (01:10–01:20) | 60 мин: 8 минут (00:52–01:00)
Прямая речь преподавателя:
«Коллеги, время практической части подошло к концу. Прошу сохранить заполненные рабочие листы.
Давайте разберем ключевые выводы сегодняшней работы. Кто из первого варианта объяснит: почему при запросе full_name LIKE '%Иван%' индекс не сработал?»
Ответ студента:
«Потому что символ процента в начале маски скрывает первый символ строки. Дерево B-Tree отсортировано по алфавиту с первой буквы, поэтому СУБД не знает, в какую ветвь идти, и вынуждена перебирать все строки таблицы через Seq Scan».
Прямая речь преподавателя:
«Прекрасно! А студенты второго варианта — в чем преимущество GIN-индекса перед LIKE при поиске по аннотациям?»
Ответ студента:
«GIN индексирует отдельные слова (лексемы) после стемминга. Мы можем искать слова в любых падежах и формах, а время поиска составляет доли миллисекунды, так как база пересекает готовые списки идентификаторов строк».
Прямая речь преподавателя:
«Отличная инженерная работа! Критерии оценивания: 2 балла за точечный и диапазонный поиск, 1 балл за составной фильтр и шаблон, 1 балл за поисковый протокол и самопроверку. Итого максимальные 4 балла.
Поздравляю вас с успешным завершением третьего семестра изучения дисциплины "Основы проектирования баз данных"! Мы прошли путь от сущностей и связей до третьей нормальной формы, физических индексов и оптимизации многомерного поиска. В четвертом семестре нас ждут средства проектирования структур БД, транзакции и триггеры. Всем спасибо за продуктивную работу!»