Версия для печати

На главную страницу

Кластеризованные Индексы в SQL Server: Вы должны это знать.

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

Зачем нужны кластеризованные индексы!

Как Вы знаете, механизм SQL Server обрабатывает таблицу без кластеризованного индекса, также называемую кучей, не так, как кластеризованную таблицу. В отличие от таблицы-кучи, кластеризованная таблица содержит свои страницы с данными в связанном списке и упорядоченными в соответствии с последовательностью ее индексной структуры. Таблица-куча использует IAM и PFS страницы для поиска страницы, имеющей достаточно свободного места для размещения новой строки, если не находит места на текущей странице (более подробную информацию можно найти в BOL).

Чтобы проиллюстрировать это различие в поведении, я создал небольшой скрипт, который Вы можете сами выполнить.

-- Init

Use tempdb

SET NOCOUNT ON

IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL DROP TABLE dbo.TestTable
CREATE TABLE dbo.TestTable ( GUID_col_indexed uniqueidentifier, CHAR_col char(10), GUID_col uniqueidentifier)
CREATE CLUSTERED INDEX IX1 ON dbo.TestTable ( GUID_col_indexed )
GO

-- Step 1: Load TestTable with 100.000 records
declare @t int
set @t=1
while @t <= 100000
begin
insert TestTable values (newid(), char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65)+ char(rand()*26 + 65), newid())
select @t=@t + 1
end
go

-- Step 2: Select 100 random values
select top 100 * from TestTable order by GUID_col
go

-- Step 3: Delete 1000 random values
delete TestTable where GUID_col in (select top 1000 GUID_col from TestTable order by GUID_col desc)
go

-- Step 4: Insert 1000 more values
declare @t int
set @t=1
while @t <= 1000
begin
insert TestTable values (newid(), char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65) + char(rand()*26 + 65)+ char(rand()*26 + 65), newid())
select @t=@t + 1
end

go

Этот скрипт содержит 4 шага:
1. Загрузка 100 000 записей в таблицу, использующая сгенерированные случайным образом значения для всех полей.
2. Выбираются 100 случайных записей.
3. Удаляется 1 000 случайных записей.
4. Загружается еще 1 000 записей.

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

Вы должны запустить этот скрипт дважды, используя сначала в точности приведенный текст (создание кластеризованного индекса), а затем, убрав из него ключевое слово [CLUSTERED]. Я надеюсь, что Вы получите результаты, сопоставимые с теми, которые я получил после нескольких тестовых испытаний:

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

Кластеризованные индексы на столбцах uniqueidentifier: отказаться! Или нет?

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

Но в некоторых системах VLDB (очень большие базы данных) с высокой транзакционной активностью это может быть решением, когда пропускная способность IO является потенциально узким местом. Предположим, что Вы имеете большую (> 100 Гбайт) таблицу базы данных, использующую файловую группу, которая распределена более чем на 10 физических дисках с кластеризованным индексом на столбце [date-inserted] (дата вставки). Когда имеет место много вставок в сочетании с большим количеством обновлений недавно добавленных записей, вся производительность будет сосредоточена на наборе физически группированных страниц данных, создавая точку перегрузки. Массивы незафиксированных страниц, которые процесс контрольной точки хочет сбросить на диск, могут стать слишком большими для обработки одним диском. Вот здесь кластеризованный индекс на столбце uniqueidentifier как раз может помочь. Вместо того чтобы сбрасывать все грязные (незафиксированные) страницы на один диск, ввод-вывод будет размазан по всем физическим дискам.

Обратите особое внимание на фрагментацию, используя это решение. Из-за вставок, происходящих случайным образом, разбиения страниц становятся более вероятными, увеличивая фрагментацию. Когда объемы ваших данных становятся слишком велики для ежедневной перестройки, выполняйте общую перестройку индексов каждую неделю. Я лично предпочитаю оператор CREATE INDEX … WITH DROP_EXISTING для перестройки индексов.

Заключение

1. Всегда используйте кластеризованный индекс на таблице базы данных OLTP.
2. Если Вы рассматриваете возможность использования кластеризованного индекса на столбце uniqueidentifier:
    a. Протестируйте ваше решение в тесте при полной нагрузке.
    b. Регулярно перестраивайте индексы.

08/12/2005

На главную страницу

  Версия для печати