Организация физического хранения данных и построение индексов

В данной главе рассматриваются вопросы, связанные с организацией хранения данных на диске и оптимизацией порядка выполнения запросов к БД с помощью индексов. Индекс (англ. index) — объект базы данных, создаваемый для логического упорядочивания хранимых записей и повышения скорости поиска данных. Индекс для таблицы БД может играть ту же роль, что алфавитный указатель в справочнике или учебнике (на английском языке алфавитный указатель — тоже index).

Темы хранения данных и построения индексов являются взаимосвязанными, чем и объясняется их совместное рассмотрение.

Организация хранения данных

Если говорить о современных серверных реляционных СУБД, то используемое ими для работы с БД дисковое пространство можно разделить на пространство для журналов транзакций и пространство для хранения данных.

Перед фиксацией каждой транзакции СУБД делает запись в журнал о произведенных транзакцией изменениях. Журналы требуются для восстановления БД, и при работе в обычном режиме туда последовательно пишутся производимые изменения, а чтений из журнала не производится.

Работа с областью для хранения данных более разнообразна: здесь производится как запись, так и чтение, причем эти операции могут попеременно затрагивать разные области диска. Именно поэтому для интенсивно используемых БД рекомендуется по возможности размещать журнал и данные на разных физических дисках. Если и журнал, и данные находятся на одном диске, требуются дополнительные затраты времени на перемещение головок диска в разные его области. Это снижает скорость работы с журналом и, соответственно, уменьшает число транзакций, обрабатываемых в единицу времени.

Однако вернемся к пространству для хранения данных. В зависимости от СУБД в качестве контейнера для хранения БД могут использоваться отдельные файлы, каталоги или даже выделенные разделы диска. Например, SQL Server в качестве контейнеров использует файлы. По принятому соглашению об именовании файл журнала получает расширение ldf, первичный или основной файл БД — расширение mdf, все остальные файлы данных — расширение ndf. Системные объекты БД всегда находятся в первичном файле данных (mdf-файле). Пользовательские объекты БД можно размещать как в первичном, так и в других файлах данных. Файлы данных организуются в группы. В БД SQL Server всегда присутствует первичная файловая группа PRIMARY, и могут создаваться дополнительные.

Нужно отметить, что если физически БД SQL Server организована как совокупность файлов, логически база состоит из схем (англ. schema). Схема является контейнером логического уровня, к которому относятся объекты БД. Во вновь созданной БД уже будет несколько схем: dbo, sys, information_schema. Схема dbo — это схема по умолчанию для новых пользовательских объектов, sys и information_schema — используются системными объектами. С помощью оператора CREATE SCHEMA в БД можно создавать новые схемы.

Любой объект принадлежит какой-то схеме. Например, полное имя таблицы может выглядеть как MyTest. lib.Bookl. Это будет означать, что таблица Bookl находится в схеме lib в БД MyTest. Если в имени пользовательского объекта опущено указание БД, будет использоваться текущая БД, если нет и названия схемы — будет использоваться схема по умолчанию (чаще всего — dbo).

Использование схем позволяет логически сгруппировать объекты БД. Например, используемые отделом маркетинга таблицы и представления можно разместить в схеме marketing. Кроме того, использование схем позволяет упростить настройку управления доступом к объектам БД.

В большинстве современных серверных СУБД для хранения данных используется страничная организация контейнеров (в частности, файлов): СУБД считывает или записывает на диск данные фрагментами строго определенного размера — страницами (англ. page). В Microsoft SQL Server размер страницы фиксирован, она равна 8 Кб [21, 22]. Из-за наличия служебных структур для данных на странице отводится 8060 байт. В других СУБД размер страницы может выбираться из определенного разработчиками перечня, например IBM DB2 может использовать страницы размером 4, 8, 16 или 32 Кб [19].

Страницы могут различаться по назначению: страницы для хранения данных, индексов, больших объектов (текста, изображений, данных в формате XML). На рис. 9.1 изображена структура страницы данных SQL Server |22|.

На странице файла с данными сначала размещается заголовок, за ним расположены строки данных, в конце страницы находится таблица смещения строк. Таблица смещения содержит одну запись для каждой строки на странице, в которой указано, насколько далеко от начала страницы находится первый байт строки. Записи в таблице смещения строк находятся в обратном порядке относительно последовательности строк на странице.

Однако страница — это тоже относительно небольшой объект, поэтому смежные страницы объединяются в экстенты (англ. extent). Экстенты являются основными единицами организации пространства для хранения данных.

В SQL Server экстент объединяет восемь страниц, физически размещенных подряд. В связи с тем, что в БД могут встречаться небольшие объекты, такие как маленькая таблица, SQL Server имеет два типа экстентов:

Рис. 9.1. Страница данных SQL Server.

• • однородные экстенты принадлежат одному объекту, все восемь страниц могут быть использованы только им;
• • смешанные экстенты могут находиться в общем пользовании. Каждая из восьми страниц экстента может принадлежать своему объекту, но только одному.

Рассмотрим теперь организацию таблиц БД при хранении. Архитектурные решения здесь во многом определяются разработчиками СУБД. Схема организации таблиц в СУБД SQL Server показана на рис. 9.2 [22].

Рис. 9.2. Организация таблиц в SQL Server.

По умолчанию таблица или индекс имеет единственную секцию (англ. partition), которая содержит все страницы таблицы или индекса. Секция располагается в одной файловой группе.

Можно создавать секционированные таблицы или индексы, где группы строк разнесены по секциям, основываясь на значениях заданной функции секционирования. Секции могут храниться в разных файловых группах БД. В то же время такая таблица при выполнении запросов или обновлении данных рассматривается как логически единая сущность.

Определим присутствующие на рис. 9.2 термины " куча" (англ. heap) и " сбалансированное дерево" (англ. b-tree). В рамках секции SQL Server может использовать один из двух подходов к организации данных:

• 1) кластеризованные таблицы (англ. clustered tables) — это таблицы, имеющие кластеризованный (иногда используется термин «кластерный») индекс. Строки данных упорядочиваются по ключу кластеризованного индекса, а сам он реализуется в виде сбалансированного дерева. Более подробно организация индексов будет рассмотрена далее. Первичный ключ таблицы обычно поддерживается с помощью индекса данного типа;
• 2) куча — это таблица, не имеющая кластеризованного индекса. Строки такой таблицы не упорядочиваются по значениям их атрибутов: СУБД выделяет страницы и сохраняет в них данные по мере добавления новых записей в таблицы.

Если куча или кластеризованная таблица содержит несколько секций, каждая секция имеет соответственно структуру кучи или сбалансированного дерева.

В таблице могут находиться различные данные. В SQL Server единица распределения (англ. allocation unit) — это коллекция страниц определенного типа. Существуют три типа единиц распределения:

• • IN_ROW_DATA — страницы содержат строки данных или индекса, за исключением больших объектов (англ. large object, LOB), таких как изображения или xml-данные. Страницы имеют тип «Data» или «Index» ;
• • LOB_DATA — страницы содержат данные больших объектов, к которым в SQL Server относятся данные типов text, ntext, image, xml, varchar (max), nvarchar (max), varbinary (max) или определяемые пользователем «большие» типы (CLR UDT). Страницы имеют тип «Text/Image» ;
• • ROW_OVERFLOW_DATA — данные переменной длины, хранящиеся в столбцах типов varchar, nvarchar, varbinary или sql_variant, которые превышают ограничение на размер строки, равное 8060 байт (максимальный объем данных, записываемых на страницу). Страницы в этой коллекции также имеют тип «Text/Image» .

В одной секции таблицы может быть только одна единица распределения каждого из перечисленных типов.