Тема 4. Проектирование структур баз данных — Средства проектирования структур БД: объекты

«Здравствуйте, уважаемые студенты! Рад приветствовать вас в новом, четвертом семестре изучения дисциплины "Основы проектирования баз данных". В прошлом семестре мы проделали огромную теоретическую и практическую работу: изучили реляционную модель, научились нормализовать таблицы вплоть до третьей нормальной формы и формы Бойса-Кодда, освоили базовый SQL и механизмы первичного поиска данных.

Сегодня мы открываем совершенно новый, фундаментальный раздел — Тему 4: "Проектирование структур баз данных". Начиная с этого занятия, мы перестаем смотреть на базу данных как на примитивный плоский набор разрозненных таблиц. Промышленная реляционная СУБД — это сложная экосистема объектов. Наша сегодняшняя тема — "Средства проектирования структур БД: объекты". Мы разберем, как крупные корпорации организуют тысячи сущностей в схемы, как ускоряют тяжелейшие аналитические отчеты с помощью материализованных представлений, как генерируют миллионы уникальных номеров без единой взаимоблокировки через последовательности, и какими визуальными CASE-инструментами пользуются ведущие архитекторы данных. Открываем рабочие тетради или электронные конспекты, записываем тему занятия».

«Давайте начнем с реальной инженерной проблемы. Представьте: вы пришли на позицию архитектора БД в крупную региональную биллинговую компанию ЖКХ или маркетплейс. В базе данных хранится 10 миллионов строк начислений и платежей. Каждый рабочий день в 9 утра 200 менеджеров и директоров филиалов открывают дашборд сводного финансового отчета. Запрос содержит 7 сложных соединений JOIN, агрегаты SUM, COUNT и GROUP BY.

И в 9:01 сервер СУБД намертво зависает: загрузка процессора 100%, диск перегружен, транзакции обычных клиентов встают в очередь. При этом таблицы идеально нормализованы, первичные ключи на месте, индексы построены. Вопрос к вам: почему так происходит и как спасти сервер, если мы не имеем права переписывать код клиентского веб-приложения каждую неделю?»

«Идея сохранить результат расчетов — абсолютно верная. Но делать это вручную костылями во временных таблицах — верный путь к рассинхронизации данных. В промышленной СУБД для этого существует штатный объект архитектурного уровня: Материализованное представление (MATERIALIZED VIEW). Оно позволяет один раз ночью выполнить тяжелый расчет, сохранить готовые блоки данных на диск, построить по ним B-Tree индекс и отдавать аналитикам готовые цифры за доли миллисекунды. А вот для сокрытия персональных данных клиентов от сторонних подрядчиков мы применим совсем другой объект — обычное динамическое VIEW. Давайте детально разберем всю линейку объектов базы данных».

«Посмотрите на экран. В реляционной СУБД (например, PostgreSQL) существует строгая иерархия хранения. На самом верху находится Кластер (Instance) — это сам запущенный сервер СУБД, процессы операционной системы и разделяемая память. Внутри кластера располагаются независимые Базы данных (Databases). Запомните: стандартный SQL не позволяет выполнить прямой JOIN между таблицами из разных баз данных на одном сервере — они логически изолированы.

Внутри базы данных находятся Схемы, или пространства имен (Schemas / Namespaces). До сегодняшнего дня в простых лабораторных работах вы создавали таблицы в схеме по умолчанию — public. Это допустимо для учебных опытов, но в продакшене сваливать сотни таблиц в public — профессиональное преступление. Это равносильно тому, как сбросить 10 000 файлов прямо на рабочий стол Windows.

Схема позволяет разбить монолитную систему на логические модули: например, схема billing (для абонентов, счетчиков и квитанций), схема imaging (для снимков МРТ и протоколов), схема logistics или security. Создается схема простой командой: CREATE SCHEMA billing;. А чтобы СУБД понимала, в каких схемах искать объекты, если вы не указали префикс, настраивается системный путь поиска: SET search_path TO billing, public;».

«Следующий важнейший служебный объект — Последовательность (SEQUENCE). Зачем она нужна? Почему нельзя при вставке нового заказа просто писать SELECT MAX(id) + 1? Потому что, если в интернет-магазине 50 покупателей одновременно нажмут кнопку "Купить", они одновременно прочитают один и тот же максимальный ID и попытаются вставить записи с одинаковым первичным ключом. Произойдет конфликт первичного ключа и сбой покупки!

SEQUENCE — это генератор, работающий в общей разделяемой памяти СУБД. Он атомарно выдает следующее число через функцию nextval('my_seq') за наносекунды, вообще не блокируя строки таблицы.

Внимание, критически важный инженерный нюанс! Запишите это обязательно: последовательность никогда не откатывается назад! Если транзакция вызвала nextval(), получила номер 105, а затем упала по ошибке или сделала ROLLBACK — число 105 потеряно навсегда. Следующая транзакция получит номер 106. В последовательности суррогатных ключей неизбежно возникают пропуски (дыры). Это нормально! Первичный ключ ID нужен только для уникальности и связей, а не для бухгалтерской непрерывной нумерации».

«Переходим к кульминационной теме занятия — архитектуре представлений.

1. Обычное динамическое представление (VIEW). Это виртуальная таблица. Сколько байт на диске занимает сам VIEW? Ровно 0 байт! В системном каталоге хранится только текст вашего запроса SELECT. Когда пользователь пишет SELECT * FROM v_clients, оптимизатор СУБД на лету соединяет дерево этого представления с запросом пользователя. Зачем нужен VIEW? Во-первых, для безопасности и сокрытия персональных данных (Security through Views): мы можем скрыть столбец с паролями, номерами паспортов или замаскировать фамилии пациентов. Во-вторых, чтобы скрыть от программистов бэкенда ужас из 8 соединений таблиц, предоставив им одну красивую виртуальную таблицу.

2. Материализованное представление (MATERIALIZED VIEW). Здесь логика иная. Результат SQL-запроса один раз вычисляется и физически записывается на жесткий диск или SSD в блоки данных СУБД. Витрина занимает реальное место на диске. Главное преимущество: аналитический запрос по 10 миллионам строк читается моментально, за доли миллисекунды, как из обычной таблицы! Более того, по столбцам материализованного представления мы имеем полное право построить любые индексы: B-Tree, GIN, Hash.

Но как поддерживать данные в актуальном состоянии? С помощью команды REFRESH MATERIALIZED VIEW. При обычном обновлении накладывается эксклюзивный замок, и на время пересчета никто не может читать витрину. Поэтому в высоконагруженных системах используют опцию REFRESH MATERIALIZED VIEW CONCURRENTLY. В этом режиме клиенты спокойно читают старый снимок, пока СУБД в фоне готовит обновления.
Запомните правило: чтобы работал CONCURRENTLY, на материализованном представлении обязан существовать хотя бы один UNIQUE INDEX, иначе СУБД выдаст ошибку!».

«В реальных проектах создавать базы данных вручную консольными командами для сотен таблиц крайне неэффективно. Для этого существуют специальные CASE-инструменты (Computer-Aided Software Engineering).

Обратите внимание на 4 главных инструмента современной индустрии:

1. pgModeler: великолепный специализированный инструмент с открытым исходным кодом под PostgreSQL. Позволяет визуально спроектировать схемы, таблицы, материализованные представления, индексы, роли, и сразу увидеть готовый чистый DDL-код.
2. DBeaver: наш основной рабочий инструмент на практических занятиях. Он умеет визуализировать ER-диаграммы на лету прямо из подключенной базы данных.
3. dbForge Studio: мощная корпоративная среда для сравнения схем (Schema Compare) и синхронизации баз данных.
4. ERwin Data Modeler: классический enterprise-инструмент, разделяющий концептуальную, логическую и физическую модели данных.

В CASE-системах существуют два ключевых направления инженерной работы:

• Forward Engineering (Прямое проектирование): вы мышкой рисуете визуальную диаграмму, нажимаете кнопку, и программа генерирует исполняемый SQL-скрипт с командами CREATE SCHEMA, CREATE TABLE, CREATE VIEW.
• Reverse Engineering (Обратное проектирование): инструмент подключается к работающей базе данных, считывает системный каталог и автоматически строит подробную графическую диаграмму связей. Это незаменимо при аудите незнакомых унаследованных систем (Legacy code)».

«Чтобы закрепить сегодняшний материал, взгляните на Слайд 15. Это знаменитый мем про "Little Bobby Tables" от автора Рэндалла Манро. Мама назвала сына именем Robert'); DROP TABLE Students;--, и школьная база данных удалила таблицу студентов при вставке имени!

Почему архитектура объектов решает эту проблему? Потому что, если бы разработчики школы не давали веб-форме прямой доступ к таблице Students, а ограничили права через схему, типизированные поля и защищенное представление VIEW, никакая инъекция не смогла бы разрушить объекты системного каталога.

На следующем занятии мы будем практически проектировать архитектуру объектов в рабочем листе zanyatie.html. В нем представлены 4 прикладных отраслевых кейса:

• Вариант 1 (ЖКХ): спроектировать схему billing, генератор номеров квитанций receipt_seq и материализованную витрину mv_monthly_debt_summary для сбора долгов.
• Вариант 2 (Медицина PACS): схема imaging, доменный тип ENUM для томографов и безопасное VIEW, скрывающее ФИО пациентов.
• Вариант 3 (Логистика): схема logistics, генератор трек-номеров через SEQUENCE и партиционированные таблицы трекинга.
• Вариант 4 (Деканат): изоляция схем academic и personal с защитой ведомостей.

Кто может кратко сформулировать главное правило: в каком случае мы выбираем обычный VIEW, а в каком — MATERIALIZED VIEW?»

«Отлично! Сегодня мы заложили прочный архитектурный фундамент четвертого семестра. Теперь вы понимаете, что реляционная СУБД оперирует не просто таблицами, а богатейшим набором специализированных объектов: схемами, представлениями, генераторами и типами.

Наше занятие завершено. Повторите конспект лекции. В материалах также размещено резервное дистанционное домашнее задание domashnee_zadanie.html. Помните: его выполняют только те обучающиеся, которым оно будет назначено индивидуально при пропуске пары или на дистанционном обучении. Всем спасибо за продуктивную работу, до встречи на практическом занятии!»