Диета для базы данных: сокращаем объем

В предыдущей статье цикла (опубликованной в Windows IT Pro/RE № 3 за 2016 год) речь шла о пользовательской базе данных внушительных размеров, которую нужно было сжать. Требовалось значительно сократить объем этой базы. Я разъяснил, почему важно было использовать сжатие ROW, и показал, каким образом измеряется выигрыш, получаемый в ходе выполнения этой процедуры.

Сегодня мы начнем с вопроса о том, как фактически работает сжатие методом ROW, после чего оценим преимущества использования сжатия по алгоритму PAGE. Кроме того, мы познакомимся с внутренними механизмами этой процедуры.

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

Несжатая таблица

Давайте начнем с той же таблицы, что и в прошлый раз, но теперь воссоздадим ее не во временной базе tempdb, а в постоянной базе данных (см. листинг 1).

После этого мы можем проверить, какое пространство занимает наша база данных:

EXEC sp_spaceused
   N'dbo.SalesOrderDetail';
GO

После выполнения этой команды отображаются данные, приведенные на экране 1.

Экран 1. Занимаемое базой данных пространство

Посмотрим на данные, размещенные на первой странице рассматриваемой таблицы. Нам известно, какие строки войдут в нее, поскольку мы упорядочим SELECT с помощью кластеризованного индекса:

SELECT TOP (3) *
FROM dbo.SalesOrderDetail
ORDER BY SalesOrderID, SalesOrderDetailID;
GO

Первые три строки первой страницы показаны на экране 2.

Экран 2. Первые три строки первой страницы

Позднее нам придется ссылаться на эти строки.

Теперь давайте посмотрим на содержимое страницы данных, а для этого выполним код из листинга 2.

Установив флаг трассировки 3604, мы перенаправляем выходные данные проверки целостности базы данных. Изначально они предназначались для журналов регистрации SQL Server, а теперь будут направлены клиенту. Далее мы используем sys.dm_db_database_page_allocations для обнаружения всех страниц таблицы и применяем фильтры с целью выделения страниц данных. На каждой из этих страниц имеется по две ссылки, так что я нашел страницу № 1 по отсутствию на ней ссылки на предыдущую страницу.

Отыскав интересующий меня номер страницы, я использовал команду DBCC PAGE для отображения ее содержимого. Его параметры — имя базы данных, номер файла (в данном случае 1), номер страницы и тип необходимых данных. Подставив значение 1, мы получим отформатированные выходные данные, которые требуются в данном случае.

Они состоят из трех базовых разделов. Первый раздел — это заголовок, он представлен на экране 3.

Экран 3. Заголовок данных

В нем содержится масса важных сведений, но в данный момент нас интересуют следующие его составляющие:

m_type = 1 означает, что мы имеем дело со страницей данных;
m_level = 0 — это уровень индекса, и в данном случае речь идет об уровне листьев кластеризованного индекса;
pminlen = 38 указывает на число байтов данных фиксированной длины в каждой строке;
m_slotCnt = 114 показывает число строк (слотов) на странице;
m_freeCnt = 2 указывает на то, что на данной странице свободно всего 2 байта. Страница фактически заполнена.

В конце страницы помещен третий раздел, который представляет собой массив слотов. Его данные размещаются в обратном порядке (см. экран 4). Чтобы текст не был слишком длинным, я удалил из него часть строк.

Экран 4. Данные массива слотов

Однако наибольший интерес для нас сегодня представляет второй раздел, а именно фактические данные (см. экран 5).

Экран 5. Раздел данных

Каждый байт представлен двумя шестнадцатеричными разрядами. Хранение величин организовано так, что первым следует наименее значимый байт. В первой строке первого слота (то есть слота 0) обозначен размер записи из 69 байт. Вы можете убедиться, что данные для каждой строки начинаются с одного и того же значения маркера в каждой строке.

Первые 38 байт размещаются в регионе фиксированных данных. Поскольку они представлены в шестнадцатеричном формате, будет полезно конвертировать некоторые значения из первой строки:

SELECT sys.fn_varbintohexstr (43659),
   sys.fn_varbintohexstr (776);
GO

В результате мы получаем выходные данные, представленные на экране 6.

Экран 6. Сконвертированные значения первой строки

Внимательно присмотревшись к значению первой строки фрагмента, помещенного выше, вы обнаружите, что это значение составляет 8baa0000, и это то же значение, байты которого представлены в обратном порядке. А если вы пройдете по всем остальным значениям, то увидите, что значения int и smallint располагаются в самом начале. Далее сохраняется значение nvarchar идентификационного номера поставщика 4911-403C-98. Вы сможете обнаружить его в данных позднее. Не забывайте, что этот номер хранится в формате двухбайтовых символов Unicode. Вот почему между символами стоят точки.

Добавляем сжатие строк

Теперь, когда мы знаем, как выглядят «нормальные» строки данных, давайте применим процедуру сжатия строк и вновь проверим объем данных (см. листинг 3).

Объем данных существенно сократился (см. экран 7).

Экран 7. Объем сокращенных данных

Теперь размер таблицы составляет 54% исходного. Иначе говоря, он уменьшился на 46%.

Еще раз взглянем на содержимое страницы (см. листинг 4).

Заголовок страницы почти не изменился (см. экран 8).

Экран 8. Заголовок сжатой страницы

Впрочем, обратите внимание, что значение параметра pminlen (фиксированная длина данных) теперь составляет 6, а количество строк на странице (m_slotCnt) — 214, и это при том, что значение параметра m_freeCnt составляет 26 (свободного пространства стало чуть больше).

Массив slot array, в сущности, не претерпел изменений, за одним исключением: в нем появились новые записи (см. экран 9).

Экран 9. Новые записи в массиве слотов

Однако обратим внимание на данные в строках. Они превратились в серию сжатых записей массива данных (см. экран 10).

Экран 10. Сжатые записи массива данных

Значения теперь хранятся в более коротких блоках, и следует отметить, что строковое значение, отображавшееся ранее с использованием типа данных nvarchar, сейчас отображается с помощью не двойных, а одинарных байтов. Общие размеры показанных строк составляют теперь 36 байт для каждой строки.

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

Применение сжатия по алгоритму PAGE

Если сжимать данные постранично, а не строка за строкой, степень сжатия может быть повышена. И, если мы считаем, что алгоритм ROW прекрасно справляется со своей задачей, можно ли сказать, что алгоритм PAGE еще лучше? Давайте посмотрим, как он работает. Начнем с применения метода сжатия PAGE, за которым последует повторная проверка объема данных (см. листинг 5).

На выходе мы получим результат, представленный на экране 11. Текущий объем составляет 31% исходного. Таким образом, объем таблицы сократился на 69%.

Экран 11. Результаты сжатия по алгоритму PAGE

Обратимся еще раз к содержимому страницы (см. листинг 6).

Мы видим, что заголовок страницы, а также таблица заголовка изменились незначительно (см. экран 12).

Экран 12. Измененный заголовок страницы

Отметим, что значение m slotCnt (число слотов) повысилось до уровня 479 слотов на страницу. Теперь то же число страниц вмещает намного большее количество строк.

Самые сильные изменения претерпел раздел данных. Обратите внимание на изменения деталей в строке данных (см. экран 13).

Экран 13. Изменившийся раздел данных

Отметим, сколь небольшое число байтов приходится на каждый столбец. При этом существенная часть имевшего место сокращения объясняется введением символов. Эти символы указывают на словарь, расположенный на странице. Здесь был использован прием сокращения префиксов, а дублирующие значения заменены символами.

К примеру, отметьте, что значение столбца 3 (в роли которого выступала строка идентификационного номера поставщика) было заменено однобайтным маркером. Куда же делось его значение? Его мы можем увидеть в разделе словаря дампа страницы (см. экран 14).

Экран 14. Словарь дампа страницы

Итак, мы убедились, что метод сжатия ROW весьма эффективен и в то же время очень мощным является алгоритм сжатия PAGE. Но можно ли применять его ко всем таблицам? Этот вопрос мы рассмотрим в следующей статье, где, в частности, будет предложен метод определения, какой алгоритм сжатия — ROW или PAGE — больше подходит для того или иного случая.

Листинг 1. Воссоздание таблицы в постоянной базе данных

USE master;
GO
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'CompressionTest')
BEGIN
    ALTER DATABASE CompressionTest SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE CompressionTest;
END;
GO


CREATE DATABASE CompressionTest;
GO

USE CompressionTest;
GO

CREATE TABLE dbo.SalesOrderDetail
(
        SalesOrderID int NOT NULL,
        SalesOrderDetailID int NOT NULL,
        CarrierTrackingNumber nvarchar(25) NULL,
        OrderQty smallint NOT NULL,
        ProductID int NOT NULL,
        SpecialOfferID int NOT NULL,
        UnitPrice money NOT NULL,
        UnitPriceDiscount money NOT NULL,
    CONSTRAINT PK_dbo_SalesOrderDetail
        PRIMARY KEY (SalesOrderID, SalesOrderDetailID)
);
GO

INSERT dbo.SalesOrderDetail
    (SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,
     OrderQty, ProductID, SpecialOfferID, UnitPrice,
     UnitPriceDiscount)
SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber,
       OrderQty, ProductID, SpecialOfferID, UnitPrice,
       UnitPriceDiscount
FROM AdventureWorks2012.Sales.SalesOrderDetail;
GO

Листинг 2. Просмотр содержимого страницы данных

DBCC TRACEON (3604);

DECLARE @DatabaseID int = DB_ID();
DECLARE @PageToDisplay int =
    (SELECT TOP(1) allocated_page_page_id
     FROM sys.dm_db_database_page_allocations
         (@DatabaseID, OBJECT_ID(N'dbo.SalesOrderDetail'), 1, NULL, 'DETAILED')
     WHERE page_type_desc = N'DATA_PAGE'
     AND previous_page_page_id IS NULL);
DBCC PAGE (@DatabaseID, 1, @PageToDisplay, 1);
GO

Листинг 3. Просмотр данных после сжатия

ALTER TABLE dbo.SalesOrderDetail
    REBUILD WITH (DATA_COMPRESSION = ROW);
GO

EXEC sp_spaceused N'dbo.SalesOrderDetail';
GO

Листинг 4. Проверка содержимого сжатой страницы

DECLARE @DatabaseID int = DB_ID();
DECLARE @PageToDisplay int =
    (SELECT TOP(1) allocated_page_page_id
     FROM sys.dm_db_database_page_allocations
         (@DatabaseID, OBJECT_ID(N'dbo.SalesOrderDetail'), 1, NULL, 'DETAILED')
     WHERE page_type_desc = N'DATA_PAGE'
     AND previous_page_page_id IS NULL);

DBCC PAGE (@DatabaseID, 1, @PageToDisplay, 1);
GO

Листинг 5. Сжатие данных по алгоритму PAGE

ALTER TABLE dbo.SalesOrderDetail
    REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

EXEC sp_spaceused N'dbo.SalesOrderDetail';
GO

Листинг 6. Проверка содержимого страницы, сжатой по алгоритму PAGE

DECLARE @DatabaseID int = DB_ID();
DECLARE @PageToDisplay int =
    (SELECT TOP(1) allocated_page_page_id
     FROM sys.dm_db_database_page_allocations
          (@DatabaseID, OBJECT_ID(N'dbo.SalesOrderDetail'), 1, NULL, 'DETAILED')
     WHERE page_type_desc = N'DATA_PAGE'
     AND previous_page_page_id IS NULL);
DBCC PAGE (@DatabaseID, 1, @PageToDisplay, 1);
GO