«Здравствуйте, коллеги! Садитесь. Сегодня у нас Практическое занятие №7 по разделу этапов проектирования баз данных, и посвящено оно фундаментальным механизмам извлечения информации — сортировке и фильтрации данных.
Представьте реальную ситуацию из практики системного администратора и разработчика. Вы развернули интернет-магазин с каталогом на несколько миллионов позиций. На первой странице выдачи (товары с 1 по 20) сайт откликается мгновенно — всего за 2 миллисекунды. Но вот поисковый робот или любопытный пользователь решает перейти по ссылке на страницу номер 5000, где задан запрос вида LIMIT 20 OFFSET 100000. И внезапно сервер базы данных зависает, утилизация диска взлетает до 100%, процессор перегревается, а время ответа вырастает до 15 секунд! Почему простейшая операция „показать следующие двадцать товаров“ способна положить высоконагруженный кластер? Сегодня на занятии мы разберем анатомию этого явления, изучим, как СУБД физически выполняет сортировку в оперативной памяти и на диске, как правильно управлять значениями NULL, как на упорядочивание влияют правила национальных алфавитов Collation, и как проектировать идеальные запросы и составные индексы».
«Давайте вспомним реляционную теорию. Что такое отношение по Эдгару Кодду? Это подмножество декартова произведения доменов, то есть математическое множество. А какое ключевое свойство множества в математике? В нем нет никакого порядка! Строки в таблице — это не строки в текстовом файле и не элементы массива с индексами от 0 до N. Они хранятся на дисковых страницах кучи (Heap) так, как СУБД было удобно их записать.
Запомните аксиому: если в вашем запросе нет ключевого слова ORDER BY, база данных имеет полное право вернуть строки в абсолютно случайном порядке. Даже если вчера они выводились по возрастанию ID, сегодня после фоновой очистки VACUUM или параллельного чтения они вернутся перемешанными.
Теперь обратимся к физике процесса. Как СУБД выполняет сортировку? Каждому процессу СУБД выделяется рабочий объем оперативной памяти — в PostgreSQL он регулируется параметром work_mem (по умолчанию 4 МБ). Если объем сортируемых данных помещается в work_mem, движок использует сверхбыстрый алгоритм In-Memory Quicksort, а если задан LIMIT k — алгоритм Top-N Heapsort.
Но что происходит, когда данных 500 мегабайт, а work_mem всего 4 мегабайта? СУБД переключается на алгоритм External Merge Sort (Внешняя сортировка слиянием). Сначала память заполняется блоком данных, сортируется и сбрасывается во временный файл на диск в виде серии — Run 1. Затем считывается следующая порция — сбрасывается Run 2, и так далее. На втором этапе движок открывает все эти временные файлы и сливает их через турнирное дерево (Min-Heap). В плане запроса появляется страшная надпись: Sort Method: external merge Disk: 45000kB. Это означает катастрофический сброс на диск, колоссальный дисковый I/O и просадку всей системы».
«Перейдем к синтаксису и коварным подводным камням. Первый камень — многоуровневая сортировка: ORDER BY category ASC, rating DESC, price ASC. СУБД сравнивает поля строго слева направо. Но что если после всех условий остаются одинаковые строки? Порядок их выдачи недетерминирован! В пагинации это вызовет мерцание строк. Поэтому золотое инженерное правило: всегда замыкайте сортировку первичным ключом (например, id ASC).
Второй камень — значение NULL. В стандарте SQL NULL обозначает неизвестность. Куда его ставить при сортировке? В PostgreSQL и Oracle по умолчанию NULL считается самым большим значением: при ASC он выводится в конце, а при DESC — неожиданно в самом начале! Представьте: вы отсортировали товары по рейтингу от лучших к худшим (ORDER BY rating DESC), и первыми на витрине выводятся товары с NULL, то есть те, у которых вообще нет отзывов! Чтобы этого избежать, стандарт SQL-99 ввел директивы: NULLS FIRST и NULLS LAST. Пишите явно: ORDER BY rating DESC NULLS LAST.
Третий камень — языковые локали (Collation). Если сортировка выполняется в бинарном режиме COLLATE "C", то по кодам UTF-8 русская буква „Ё“ (код 0401) встает перед буквой „А“ (код 0410)! В электронном журнале студент Елкин окажется в начале списка перед Абрамовым. Для корректной русской сортировки используется правило сопоставления COLLATE "ru_RU" (или ICU-локали).
Четвертый камень — архитектурная замена медленного OFFSET на курсорную пагинацию (Keyset Pagination). Вместо LIMIT 20 OFFSET 100000 мы запоминаем значение ключа последней записи и запрашиваем: WHERE (created_at, id) < ($last_date, $last_id) ORDER BY created_at DESC, id DESC LIMIT 20. Благодаря составному B-Tree индексу поиск выполняется за время $O(\\log N)$ без чтения лишних 100 000 строк!
Сейчас вы откроете рабочий лист zanyatie.html. В нем 4 варианта из реальных отраслей: электронная коммерция, аэропорты, страховые реестры и академические журналы. Приступаем к выполнению».
zanyatie.html, вводят фамилию, имя, группу и выбирают назначенный вариант кейса.
«Обратите внимание на поле фильтрации в Варианте 1: у вас условие WHERE price BETWEEN 10000 AND 50000 AND is_in_stock = TRUE. Если вы создаете составной индекс, какое поле должно идти первым: точное равенство is_in_stock или диапазон цен? Конечно, точное равенство! В сбалансированном B-Tree индексе поля равенства всегда ставятся левее диапазонных полей, иначе фильтрация по диапазону заблокирует использование последующих колонок для сортировки».
«В Варианте 4 обратите внимание на сортировку фамилий студентов: не забудьте явно прописать локаль COLLATE "ru_RU", чтобы проверить корректность упорядочивания фамилий на букву „Ё“».
«Давайте разберем основные ошибки, с которыми мы столкнулись в работах.
Первая типичная ошибка: составление запроса с OFFSET без включения первичного ключа в ORDER BY. Если у вас несколько строк с одинаковым значением поля сортировки, при переходе между страницами браузер то терял строки, то показывал дубликаты. Запомните: первичный ключ в конце ORDER BY обеспечивает абсолютный детерминизм.
Вторая ошибка: забытое указание NULLS LAST при сортировке по убыванию. В результате в первом варианте товары без отзывов всплывали на вершину рейтинга магазина.
Третья ошибка: вызов функций в условии WHERE, например WHERE EXTRACT(YEAR FROM doc_date) = 2026. Это превращает быстрый индексный поиск в медленный полный перебор всей таблицы (Seq Scan). Всегда используйте диапазонные сравнения: WHERE doc_date >= '2026-01-01' AND doc_date < '2027-01-01'.
Взгляните на слайд 15 презентации. Этот знаменитый комикс напоминает нам об инженерной ответственности. Проектирование запросов и схем БД требует математической строгости. Небрежность в запросах ведет к перегрузкам серверов, сбросам сортировок на диск и сбоям в работе сервисов.
Сейчас нажимаем кнопку «Печать / Экспорт в PDF» в ваших рабочих листах и сохраняем электронные отчеты. Оценки выставлены в журнал согласно критериальной рубрике. Если кому-то потребуется дистанционное закрепление темы, в папке доступно резервное домашнее задание. Всем спасибо за работу, занятие окончено!»