3. Структуры данных СУБД, общий подход к организации представлений, таблиц, индексов и кластеров

1. Структуры данных СУБД общий подход к организации представлений, таблиц, индексов и кластеров

СТРУКТУРЫ ДАННЫХ СУБД
ОБЩИЙ ПОДХОД К ОРГАНИЗАЦИИ
ПРЕДСТАВЛЕНИЙ, ТАБЛИЦ,
ИНДЕКСОВ И КЛАСТЕРОВ

2. Структуры данных СУБД

Организация данных в системах управления базами данных (СУБД) строится
на многоуровневой архитектуре. Фундаментом этой архитектуры является
модель данных — интегрированный набор понятий для описания объектов
реального мира, связей между ними и ограничений целостности.
Модель данных состоит из трех компонентов:
1.
Структурная часть: набор правил построения базы данных.
2.
Управляющая часть: определение типов допустимых операций
(извлечение, обновление, изменение структуры).
3.
Ограничения целостности: правила, гарантирующие корректность
данных.

3. Таблицы как физическая единица хранения

На физическом уровне таблица в СУБД — это не просто набор строк, а
сложная иерархия структур хранения данных на диске.
Современные реляционные СУБД (такие как Microsoft SQL Server, PostgreSQL
или Oracle) оперируют блоками фиксированного размера, что позволяет
эффективно управлять вводом-выводом.

4. Таблицы как физическая единица хранения

Организацию таблиц можно разделить на компоненты:
1. Фундаментальные единицы: страницы и экстенты
Страница (Page)
Это минимальная единица ввода-вывода для дисковых операций. Все данные
таблиц и индексов хранятся исключительно внутри страниц.
• Размер: Стандартный размер страницы составляет 8 КБ (8192 байта).
• Структура страницы:
Каждая страница начинается с заголовка размером 96 байт. В нем хранится
системная информация: тип страницы, объем свободного места,
идентификатор объекта, номер последовательности LSN (для восстановления).
Сразу после заголовка располагаются сами строки данных. В самом конце
страницы находится массив слотов (Slot Array) — список двухбайтовых
смещений, указывающих на начало каждой строки. Массив слотов растет от
конца страницы к началу, а данные — от заголовка к концу. Это позволяет
удалять строки без переупорядочивания оставшегося пространства.

5. Таблицы как физическая единица хранения

Экстент (Extent)
Это группа из восьми физически непрерывных страниц, составляющая 64 КБ.
Экстенты используются для эффективного управления выделением дискового
пространства.
Существует два типа экстентов:
• Смешанные (Mixed Extents):
Содержат страницы, принадлежащие разным объектам (например, первые
страницы нескольких разных маленьких таблиц). Новая таблица получает
место именно из смешанного экстента.
• Однородные (Uniform Extents):
Все восемь страниц принадлежат одному объекту. Когда таблица разрастается
до 8 страниц, СУБД начинает выделять ей только однородные экстенты.

6. Таблицы как физическая единица хранения

Для отслеживания занятости этих структур СУБД использует специальные
битовые карты:
• GAM (Global Allocation Map):
Отмечает, какие экстенты полностью свободны.
• SGAM (Shared GAM):
Отмечает, какие экстенты являются смешанными и имеют хотя бы одну
свободную страницу.
• PFS (Page Free Space):
Хранит информацию о проценте заполненности каждой конкретной страницы
(пустая, 1–50%, 51–80%, 81–95%, 96–100%). Это критически важно для куч
(heaps), куда новые строки вставляются в первое попавшееся свободное место.

7. Логическая организация таблицы

Физически данные могут быть организованы двумя основными способами:
• Куча (Heap-организация)
Если у таблицы нет кластерного индекса, она представляет собой кучу. Это
простейшая структура: последовательность страниц, никак не связанных
между собой логическим порядком. Строки добавляются туда, где есть
свободное место (СУБД ищет подходящую страницу через PFS-карту).
➢ Особенности:
Быстрая вставка новых записей (не нужно поддерживать порядок). Однако
поиск по значению требует полного сканирования всех страниц таблицы (Full
Table Scan), если нет вспомогательных некластерных индексов.
➢ Связность:
Для навигации по куче используется карта распределения индексов (IAM —
Index Allocation Map). IAM-страницы связывают цепочки экстентов,
принадлежащих одной таблице, избавляя систему от необходимости хранить
прямые ссылки (указатели) от страницы к странице.

8. Логическая организация таблицы

• Кластеризованная таблица (Кластерный индекс)
Если у таблицы есть кластерный индекс, ее физическая структура
превращается в B+-дерево. Листовые узлы этого дерева содержат сами данные
таблицы, отсортированные по ключу индекса. Данные перестают быть
«кучей» и приобретают строгий физический порядок.
• Секционирование (Partitioning)
Большие таблицы разбиваются на секции. Каждая секция — это независимая
единица хранения со своим набором страниц и экстентов. Секции могут
храниться в разных файловых группах (на разных физических дисках). Для
пользователя таблица остается единым объектом, но на физическом уровне
СУБД работает с небольшими фрагментами, что ускоряет обслуживание
(индексацию, резервное копирование) и параллелизм.

9. Внутренняя структура строки и управление пространством

Внутри страницы строки обычно располагаются последовательно. Однако
существуют важные механизмы обработки нестандартных ситуаций:
• Ограничение в 8060 байт
Суммарный размер данных фиксированной и переменной длины в строке не
может превышать 8060 байт. Если при обновлении varchar-столбца строка
перестает помещаться на текущей странице, СУБД динамически переносит
переполняющиеся столбцы на специальные страницы в единице выделения
ROW_OVERFLOW_DATA. На исходной странице сохраняется 24-байтовый
указатель. Это предотвращает ошибки, но замедляет чтение, так как СУБД
вынуждена совершать дополнительные операции ввода-вывода.
• Данные больших объектов (LOB)
Типы данных varchar(max), nvarbinary(max) или xml хранятся иначе. Если
значение короткое, оно может лежать in-row. Если большое — СУБД создает
отдельную структуру страниц (LOB_DATA), а в основной строке оставляет
лишь 16-байтовый корень (указатель на дерево текстовых страниц).

10. Внутренняя структура строки и управление пространством

• Массив слотов и фрагментация
Поскольку строки удаляются и обновляются, внутри страницы возникают
"дыры" (свободное пространство). Массив слотов гарантирует, что логический
порядок строк (позиция 0, 1, 2...) всегда понятен СУБД, даже если физически
строка №2 лежит перед строкой №1. Со временем этот процесс приводит к
фрагментации — логической (в индексе нарушен порядок следования ключей)
и физической (страницы одного объекта разбросаны по файлу). Для борьбы с
этим применяются процедуры перестроения (REBUILD) или реорганизации
(REORGANIZE) индексов.

11. Метаданные и служебные структуры

Помимо самих данных, СУБД поддерживает ряд скрытых системных страниц
для обеспечения целостности и работы движка:
• DCM (Differential Changed Map):
Помечает экстенты, изменившиеся с момента последнего полного бэкапа.
Позволяет делать быстрые дифференциальные копии, читая только эти
страницы.
• BCM (Bulk Changed Map):
Используется при массовых операциях вставки (BULK INSERT) для
оптимизации записи в журнал транзакций.
Физическая таблица — это распределенный по экстентам и страницам набор
двоичных блоков, управляемый сложной системой карт (GAM, SGAM, PFS,
IAM), способный динамически перераспределять внутреннее пространство
строк для поддержания производительности и соблюдения жесткого лимита в
8 КБ на стандартную страницу памяти

12. Индексы: механизмы ускорения доступа

Индекс — это вспомогательная структура данных, которая дублирует
ключевые поля таблицы и содержит указатели на физические расположения
записей. Его главная цель — преобразовать операцию поиска со сложностью
O(n) (полное сканирование) в операцию со сложностью O(log n) или O(1).
Типы индексов по структуре:
• B-дерево (B-tree) и B+-дерево:
Стандарт де-факто для большинства СУБД (PostgreSQL, Oracle, MS SQL
Server). Сбалансированная древовидная структура, состоящая из корневого
уровня, промежуточных уровней и листового уровня. В B+ деревьях все
фактические ссылки на данные находятся только на листьях, а внутренние
узлы содержат лишь ключи для навигации.
• Битовые индексы (Bitmap):
Используют битовые карты для каждого уникального значения. Идеальны для
колонок с низкой кардинальностью (мало уникальных значений, например,
пол или статус заказа).
• R-деревья (R-tree): предназначены для индексирования пространственных
данных (геолокация, геометрические фигуры).

13. Индексы: механизмы ускорения доступа

Типы индексов по логике:
• Первичные (Primary Key):
Уникальный индекс, создаваемый автоматически при определении первичного
ключа.
• Уникальные (Unique):
Запрещают дублирование значений в индексируемых столбцах.
• Составные (Composite):
Строятся по нескольким столбцам. Порядок столбцов критически важен:
СУБД может эффективно использовать такой индекс только если в запросе
указаны левые префиксы этого составного ключа.
• Частичные (Partial/Predicate-based):
Включают в себя только подмножество строк таблицы (например, только
активные заказы), что экономит место и ускоряет операции над этим
подмножеством.

14. Кластерные и некластерные индексы

Существует ключевое различие в физической организации данных:
• Кластерный индекс (Clustered Index):
Определяет физический порядок хранения данных в таблице. Листовые узлы
такого индекса содержат сами строки данных, а не указатели на них.
Поскольку данные физически упорядочены, у таблицы может быть только
один кластерный индекс. Он максимально эффективен для выборок
диапазонов (range scans), так как исключает лишние операции ввода-вывода.
Структура: Корневая страница --> Промежуточные страницы -->Листовые
страницы (содержат данные).
• Некластерный индекс (Non-clustered Index):
Представляет собой отдельную структуру. Листовые узлы содержат
скопированные индексируемые значения и указатели (адреса строк или
первичные ключи), ведущие к реальным данным в куче или в кластерном
индексе. У одной таблицы может быть множество некластерных индексов.

15. Представления (Views)

Представление — это виртуальная таблица, результат выполнения
сохраненного SQL-запроса (SELECT). В отличие от таблиц, представления
обычно не занимают постоянного дискового пространства для хранения самих
данных (они хранят только текст запроса в системном каталоге)
Механизм работы:
При обращении к представлению СУБД выполняет слияние (view merging)
текста запроса-представления с текстом пользовательского запроса, создавая
итоговый план выполнения.
Однако существуют материализованные представления (Materialized Views).
Для них СУБД периодически вычисляет результат сложного запроса и
сохраняет его на диске как физическую таблицу-кэш.
Это радикально ускоряет аналитические запросы, но требует механизмов
обновления (по расписанию или триггерам) для поддержания актуальности
данных.

16. Представления (Views)

Функции представлений:
• Безопасность: сокрытие конфиденциальных столбцов или строк от
конечного пользователя.
• Абстракция: изоляция клиентских приложений от изменений в схеме
реальных таблиц.
• Удобство: инкапсуляция сложной бизнес-логики и соединений (JOIN)
множества таблиц за простым именем.

17. Кластеры (Data Clusters)

Кластеризация — это метод физического группирования данных из двух и
более таблиц на основе общего ключа. Цель кластера — минимизировать
количество операций ввода-вывода при выполнении соединений (joins) этих
таблиц.
Если две таблицы часто соединяются по полю DEPT_ID, СУБД может
поместить записи обеих таблиц с одинаковым значением DEPT_ID на одну и
ту же физическую страницу данных или на соседние страницы.
Когда СУБД считывает страницу с данными отдела №5 из первой таблицы,
данные сотрудников этого же отдела из второй таблицы уже находятся в
оперативной памяти.

18. Кластеры (Data Clusters)

Особенности реализации:
• Индексный кластер:
Использует отдельный индекс (cluster index) для поиска блока данных,
содержащего связанные строки разных таблиц.
• Хеш-кластер:
Вместо B-дерева использует хеш-функцию для определения физического
адреса блока, где будут сгруппированы связанные данные.
Кластеры значительно повышают производительность тяжелых
транзакционных систем с большим количеством связанных сущностей, но
усложняют управление свободным местом и могут приводить к деградации
производительности при неравномерном распределении ключей.

19. Обработка данных СУБД

Логика обработки данных в СУБД выглядит следующим образом:
• Оптимизатор запросов анализирует SQL-выражение.
• При наличии материализованного представления оптимизатор проверяет
возможность переписать исходный запрос через это представление (query
rewrite).
• Выбираются пути доступа: полное сканирование таблицы, использование
некластерного индекса с последующим поиском по закладке (bookmark
lookup), либо прямой проход по кластерному индексу.
• Если требуется соединение таблиц, оценивается целесообразность
использования кластера или алгоритмов соединения (Nested Loop, Hash
Join, Merge Join).
• Диспетчер хранилища обращается к менеджеру буферного пула, который
управляет обменом страницами между диском и оперативной памятью,
обеспечивая работу всех вышеперечисленных структур.

20. Практическое задание:

1) Role-play: воспользуйтесь списком вопросов заказчику или своими
вопросами для формирования требования к БД.
Список вопросов:
1. Каковы основные бизнес-сущности и каков их жизненный цикл?
Зачем спрашивать: чтобы определить базовые таблицы, первичные ключи и
атрибуты. В SSMS это ляжет в основу структуры колонок и типов данных (int,
uniqueidentifier, datetime2).
Уточняющий пример: «Какие объекты главные в вашей системе: Клиенты,
Заказы, Поставки или Оборудование? Что происходит с Заказом после его
закрытия?»
2. Какова интенсивность записи (INSERT/UPDATE) против интенсивности
чтения (SELECT) и каков профиль нагрузки?
Зачем спрашивать: это критический вопрос для выбора физической
организации таблиц в SSMS. Если нагрузка преимущественно OLTP (много
мелких вставок), нужны кучи или узкие кластерные индексы. Если аналитика
(OLAP) — стоит рассмотреть колоночные индексы (Columnstore).
Уточняющий пример: «Сколько новых строк будет создаваться в секунду?
Запросов на чтение при этом больше, чем операций изменения данных?»

21. Практическое задание:

3. Какие запросы являются самыми частыми и самыми тяжелыми (по времени
выполнения)?
Зачем спрашивать: ответ определит стратегию индексирования. Нужно понять,
будут ли преобладать поиск по точному значению (нужен некластерный
индекс), диапазонные выборки (лучше кластерный индекс) или соединения
многих таблиц. Уточняющий пример: «Какой отчет строится дольше всего?
По каким полям пользователи чаще всего ищут информацию в формах?»
4. Насколько жесткие требования к консистентности данных и какие правила
целостности существуют?
Зачем спрашивать: для настройки внешних ключей (FOREIGN KEY),
проверочных ограничений (CHECK constraints) и триггеров. В SSMS
использование декларативной ссылочной целостности предпочтительнее
логики в коде приложения.
Уточняющий пример: «Может ли заказ существовать без привязанного
клиента? Может ли остаток товара на складе стать отрицательным?»

22. Практическое задание:

5. Как планируется масштабировать систему через 2–3 года и каков
ожидаемый объем данных?
Зачем спрашивать: чтобы заложить архитектуру секционирования
(Partitioning) таблиц и индексов уже на этапе создания схемы. Если таблица
вырастет до терабайта, отсутствие партиций сделает ее обслуживание
невозможным.
Уточняющий пример: «Через сколько месяцев база данных достигнет размера
в 500 ГБ? Планируете ли вы хранить данные старше 3 лет в оперативном
доступе?»
6. Требуется ли хранение исторических версий данных (версионность) и аудит
изменений?
Зачем спрашивать: для реализации медленно меняющихся измерений (SCD
Type 2) или системных версионируемых таблиц (System-Versioned Temporal
Tables). В SSMS это требует добавления служебных колонок (ValidFrom,
ValidTo) и включения системного версионирования.
Уточняющий пример: «Нужно ли вам видеть цену товара, которая была
актуальна именно на момент совершения покупки год назад?»

23. Практическое задание:

7. Кто является потребителем данных вне основной операционной системы?
Зачем спрашивать: для планирования ETL-процессов, уровня изоляции
снимков данных (Snapshot Isolation) и репликации. Если аналитики будут
запускать тяжелые отчеты на живой базе, они заблокируют работу кассиров.
Уточняющий пример: «Будут ли сторонние BI-системы тянуть данные
напрямую из этой БД? Нужна ли выгрузка данных в бухгалтерию раз в
сутки?»
8. Каковы требования к безопасности на уровне строк (Row-Level Security) и
столбцов?
Зачем спрашивать: настройка ролей в SSMS, динамическое маскирование
данных (Dynamic Data Masking) и RLS. Это позволяет скрыть зарплаты
менеджеров от сотрудников склада прямо на уровне движка СУБД.
Уточняющий пример: «Должен ли менеджер филиала видеть только заказы
своего региона, даже если технически у него есть доступ ко всей таблице
заказов?»

24. Практическое задание:

9. Какой уровень доступности (SLA) требуется и каковы окна обслуживания?
Зачем спрашивать: для выбора модели восстановления (Full, Bulk-Logged или
Simple), настройки Always On Availability Groups и плана обслуживания
(индексация, обновление статистики). Модель Full дает надежность, но
требует управления файлами журналов (LDF).
Уточняющий пример: «Допустим ли простой базы данных в течение часа
ночью для обслуживания? Или система должна работать 24/7 без возможности
отключения?»
10. Есть ли специфические типы данных (геолокация, иерархии, файлы) и
требования к производительности полнотекстового поиска?
Зачем спрашивать: для использования специализированных возможностей
SSMS: типа geography/geometry, hierarchyid, FileTable или службы Full-Text
Search.
Уточняющий пример: «Нужен ли пользователям поиск по тексту внутри
прикрепленных PDF-документов? Будете ли вы отображать точки клиентов на
географической карте?»

25. Практическое задание:

2) На основе данных, собранных у «заказчика», создайте БД в СУБД SSMS;
3) При построении БД приведите её к 3 нормальной форме;
4) При необходимости, создавайте дополнительные таблицы для
упорядочивания данных и нормализации таблиц;
5) Установите первичные и внешние ключи, настройте связи между
таблицами;
6) Наполните таблицы данными вручную или через скрипт;
7) Выгрузите БД в виде скрипта и закрепите в дз;

26. Благодарю за внимание