Кластеризованные индексы на основе GUID

Некоторые вещи отлично сочетаются друг с другом: шоколад и арахисовое масло, кино и попкорн и т.д. Однако предостаточно существует и неудачных сочетаний: лимонад и чистка зубов, понедельник и счастье, GUID и кластеризованные индексы…

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

GUID – это глобальный уникальный идентификатор длиной 16 байт. Собственно, в GUID нет ничего плохого, но при определенных условиях применения они могут сильно ухудшать производительность SQL Server. GUID часто используются для уникальной идентификации строк в таблице, благодаря весьма малой вероятности исчерпания всех возможных значений в данном диапазоне, по сравнению с другими типами данных, используемыми для подобных задач.

• Диапазон представимых целых чисел: от -231 до 231, т.е. 4 294 967 294 возможных комбинаций.
• Диапазон представимых длинных целых чисел: от -263 до 263, т.е. 18 446 744 073 709 551 614 возможных комбинаций.
• Число возможных GUID: -2122, т.е. 5 316 911 983 139 663 491 615 228 241 121 400 000.

Описание вычислений приведено в документе на сайте mrdee.blogspot.com/2005/11/how-many-guid-combinations-are-there.html. Кстати, точное значение 263 нельзя вставить в Microsoft Excel или в стандартный калькулятор Windows.

Итак, GUID хороши тем, что с их помощью можно расширить пул доступных уникальных строк в таблице с использованием одного столбца (здесь мы не будем рассматривать такие варианты, как многостолбцовые ключи, однако данная альтернатива существует). Плохо то, что это достигается ценой дополнительных 12 байт (если сравнивать с вводом столбца целых данных) или 8 байт (если сравнивать GUID с длинным целым). Существует также возможность фрагментации, начиная с момента вставки второй строки в таблицу при использовании GUID в качестве ключа кластеризации, если не принять соответствующие меры.

Непоследовательность GUID

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

Если вставить строки в таблицу (каждая строка заканчивается по достижении длины в 1 000 байт с использованием приведенных выше типов данных), мы получим результаты вызова sys.dm_db_index_physical_stats, см. листинг 2 и экран 1.

Экран 1. Результаты вызова sys.dm_db_index_physical_stats

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

Чтобы увидеть, насколько быстро индекс на основе GUID становится фрагментированным, вернемся в начало. Усечем таблицу и выполним ввод 8 записей. Состояние фрагментации после ввода первых 8 записей показано на экране 2.

Экран 2. Состояние фрагментации после ввода первых восьми? записей

Этого и следовало ожидать; однако нам еще предстоит заполнить страницу. Что произойдет, если добавить еще одну запись, мы видим на экране 3.

Экран 3. Добавление еще одной записи

После ввода 9-й записи наступает разбиение первой страницы. Так как GUID поступают в кластеризованный индекс непоследовательно, страницы будут разбиваться, чтобы сбалансировать содержимое (примерно поровну). В таблице 9 строк, поэтому равного разбиения быть не может. Что будет, если добавить еще две строки? Поскольку страницы заполнены лишь на 55%, а мы знаем, что на странице помещается 9 строк, после этого добавления мы должны по-прежнему иметь две страницы на уровне листьев индекса, не так ли (см. экран 4)?

Экран 4. Добавление еще двух строк

Однако мы получили еще одно разбиение страницы всего лишь после добавления двух строк. Пространство расходуется неэкономно, и при том, что используются 16-разрядные GUID, а не 4-байтовые INT или 8-байтовые BIGINT, и даже несмотря на то, что указан стопроцентный коэффициент заполнения индекса, мы не достигли этой отметки ни перед первым, ни перед вторым разбиением страницы. Проведем последний эксперимент.

Итак, у нас имеется три страницы индексов, заполненных примерно на 50%. Мы знаем, что на странице умещается девять строк, и задан коэффициент заполнения 100%. Теоретически должна существовать возможность вставить 12 строк: (50% от 9 округляем до 4, так как нельзя вставить половину записи, и умножаем на 3). Как мы уже поняли, вероятность 99% заполнения этих трех страниц чрезвычайно мала без перестроения индексов, поэтому вопрос лишь в том, насколько сильно будет фрагментироваться индекс? Выясним это.

Добавив еще 12 строк в таблицу, получаем результат, показанный на экране 5.

Экран 5. Добавление еще 12 строк

Странно! Добавление двух строк, 2/11 от общего их числа, вызвало разбиение страницы, то есть добавление еще одной страницы на уровне листьев индекса. Добавление еще 12 строк, то есть 12/23 от общего их числа, также вызвало разбиение страницы, но только одно, хотя доля страниц, добавленных в целом, в этом случае была намного больше, чем при добавлении 10-й и 11-й строк, в результате которого мы получили второе разбиение.

Проведем еще один маленький эксперимент: усечем таблицу и вновь добавим 23 строки. Поскольку используется команда GO #, строки добавляются в рамках единичных транзакций. Выводятся результаты вызова sys.dm_db_index_physical_stats после вставки 8-й, 9-й, 11-й и 23-й строк. Поскольку мы проходим весь процесс заново, должны получиться аналогичные результаты (см. листинг 3 и экран 6).

Экран 6. Результат усечения

По крайней мере, результаты близкие. Ввиду случайной природы значений GUID, получилась более благоприятная картина фрагментации внутри страниц; число строк на страницах осталось неизменным. Соответственно, общая средняя степень заполнения страниц не изменилась; на данном этапе индекс остается четырехстраничным. Следовательно, выполнение сценария с усечением таблицы и повторной загрузкой 23 строк все же приводит к изменению числа страниц индекса и картины фрагментации. Я проделал это несколько раз и наблюдал среднюю степень фрагментации 50 или 75% на четырех страницах уровня листьев, заполненных примерно на 71%, но также получил индекс с пятью страницами на уровне листьев с 40-процентной средней степенью фрагментации, то есть 57-процентным средним заполнением.

Другими словами, налицо последовательная непоследовательность. Я – администратор базы данных (DBA) и терпеть не могу последовательно непоследовательных вещей.

Ключи кластеризации INT и BIGINT с использованием свойства IDENTITY

Если вас не беспокоит возможность исчерпания доступных значений, существующая при использовании целых (INT) или длинных целых (BIGINT), я рекомендую всегда задействовать один из этих типов данных, а не GUID, для суррогатного ключа кластеризации. Это позволит выполнять вставку (INSERT) последовательно и реализовать уникальную идентификацию строк в таблице при более эффективном расходовании пространства и значительно меньшей степени фрагментации.

INT и BIGINT ведут себя одинаково, поэтому ограничимся рассмотрением INT, то есть целого длиной 4 байта, а не 16 байтов, как в примере с GUID. Построим новую таблицу с длиной строки 1000, как в примере с GUID, и выполним вставку строк 8, 9, 11 и 23, чтобы сравнить результаты (см. листинг 4).

Вначале результаты, полученные с GUID и INT, выглядят одинаково (см. экран 7).

Экран 7. Сравнение результатов при первом использовании GUID и INT

Получилось одной страницей меньше (25% экономии по сравнению с вариантом GUID), то есть степень заполнения страниц повысилась. Однако при таких маленьких таблицах результаты не представляют особого интереса; администраторов баз данных больше беспокоит то, что происходит, когда базы данных становятся огромными и неуправляемыми. Сравним, что получилось с использованием кластеризованного индекса на основе GUID для 80 000 строк (см. экран 8), с результатами для того же количества строк, полученными с ключом INT (см. экран 9).

Экран 8. 80 000-строчный кластеризованный индекс на?базе GUID

Экран 9. 80 000-строчный кластеризованный индекс на?базе INT

• 10 000 страниц на уровне листьев при использовании кластеризованного индекса на основе INT против 14 712 страниц – в случае с GUID.
• Последовательные кластеризованные индексы на основе INT практически не приводят к масштабной фрагментации; аналогичные результаты получаются для BIGINT.
• Более «узкий» индекс в виде B-дерева при использовании INT по сравнению с GUID (в моем примере: 36 промежуточных страниц против 66).

Отдавайте предпочтение INT, BIGINT и SMALLINT

При проектировании базовой структуры таблицы я бы рекомендовал избегать применения GUID. Рассмотрите варианты INT или BIGINT применительно к предполагаемому масштабу (если точно известно, что масштаб невелик, рассмотрите также SMALLINT (короткое целое) в качестве возможного варианта). Использование GUID увеличит пул доступных значений для уникальной идентификации строк в таблице, но это будет достигнуто ценой больших затрат вычислительных ресурсов и ущерба для производительности. На мой взгляд, лучше добавить дополнительный столбец, чтобы решить проблему масштаба, но не прибегать к GUID. Неэкономное расходование пространства и фрагментация – достаточные причины для того, чтобы держать таблицы и GUID на почтительном расстоянии друг от друга.

CREATE TABLE [dbo].[tblGUID_test]
(
[id] [UNIQUEIDENTIFIER] NOT NULL,
[name] [CHAR](969) NOT NULL,
date_stamp DATETIME NOT NULL,
CONSTRAINT [PK_tblGUID_test] PRIMARY KEY CLUSTERED (id)
WITH (PAD_INDEX = OFF, FILLFACTOR = 100)
)
GO

INSERT INTO tblGUID_test(id, name, date_stamp)
VALUES (NEWID(), 'FOO', GETDATE());
GO 80000
SELECT ixP.index_level
, CAST(ixP.avg_fragmentation_in_percent AS DECIMAL(5,2)) AS avg_frag_pct
, ixP.page_count
, ixP.avg_record_size_in_bytes AS avg_bytes_per_record
, CAST(ixP.avg_page_space_used_in_percent AS DECIMAL(5,2)) AS avg_page_fill_pct
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('tblGUID_test'), 1, NULL, 'detailed') ixP
INNER JOIN sys.indexes I
ON I.index_id = ixP.index_id
AND I.object_id = ixP.object_id;

TRUNCATE TABLE tblGUID_test;
GO
INSERT INTO tblGUID_test(id, name, date_stamp)
VALUES (NEWID(), 'FOO', GETDATE())
GO 23
SELECT ixP.index_level
, CAST(ixP.avg_fragmentation_in_percent AS DECIMAL(5,2)) AS avg_frag_pct
, ixP.page_count
, ixP.avg_record_size_in_bytes AS avg_bytes_per_record
, CAST(ixP.avg_page_space_used_in_percent AS DECIMAL(5,2)) AS avg_page_fill_pct
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('tblGUID_test'), 1, NULL, 'detailed') ixP
INNER JOIN sys.indexes I
ON I.index_id = ixP.index_id
AND I.object_id = ixP.object_id;
GO

CREATE TABLE [dbo].[tblINT_test]
(
[id] [INT] IDENTITY(1,1) NOT NULL,
[name] [CHAR](981) NOT NULL,
date_stamp DATETIME NOT NULL,
CONSTRAINT [PK_[tblINT_test] PRIMARY KEY CLUSTERED (id)
WITH (PAD_INDEX = OFF, FILLFACTOR = 100)
)
GO
--=====================================================
--RUN IN BATCHES OF 8, 1, 2, 12 REPORTING ON OUTCOMES:
--=====================================================
INSERT INTO tblINT_test(name, date_stamp)
VALUES ('FOO', GETDATE())