Заметки Дмитрия Пилюгина о Microsoft SQL Server 

Twitter RSS
Home SQL Server (все заметки) Выбор полей для кластерного индекса
formats

Выбор полей для кластерного индекса

Существуют разные точки зрения на тему, какое поле лучше всего подходит в качестве кластерного индекса для таблицы. В частности такое «кластерный индекс должен удовлетворять запросам для выбора большого числа строк, желательно что бы это не было поле identity, т.к. при такой организации могут возникать hotspot при вставке, а выбор по диапазону identity явление очень редкое».
Так же есть мнения, что такой подход уже давно не актуален в новых версиях, а выбор в качестве кластерного индекса например поля guid — ведет к фрагментации. Для себя я выработал некоторое мнение по этому вопросу. А недавно наткнулся на интересную статью по выбору поля для кластерного индекса в блоге Kimberly L. Tripp, которая еще больше подтвердила некоторые мои мысли относительно этого.
Хотя статья не новая, но мне кажется она может быть полезна людям которые задавали или задают себе этот вопрос. По этому, привожу свою версию перевода статьи.

Всеувеличивающийся кластерный ключ — дебаты по кластерному индексу….снова. (перевод)
Автор: Kimberly L.Tripp
Оригинальный пост: в блоге Kimberly L.Tripp

Подготовка к моим занятиям на Tech Ed на тему «Indexing Best Practices in SQL Server 2005», напомнила мне о том, что есть много «лучших практик», которые на самом деле применимы как для SQL Server 2000, так и для SQL Server 2005. Когда дело касается индексирования, есть много зависимостей от структур хранилища. Эти зависимости являются теми самыми основами, из-за которых я рекомендую особый тип кластерного ключа для всех версий sql server, начиная от 7 и выше!

Я хочу начать с рекомендации по кластерному ключу, по нескольким причинам. Во-первых, это простое решение, и во вторых, принятие этого решения на ранних стадиях помогает предотвращать некоторые типы фрагментации. Если вы можете предотвратить определенные типы фрагментации базовой таблицы, тогда вы можете минимизировать некоторые виды обслуживающих работ (в большей степени в SQL Server 2000 и в меньшей в SQL Server 2005), делающих вашу таблицу недоступной. Ок, я вернусь к теме перестроения позднее…

Давайте начнем, с тех вещей, которые я ищу в кластерном ключе:
— уникальный
— узкий
— статичный

Почему уникальный?
Кластерный ключ должен быть уникальным, потому, что кластерный ключ (если он существует) используется для поиска во всех некластерных индексах. Например, посмотрите на оглавление в конце книги — если вам нужно найти какую-либо информацию на которая указывает запись — то эта запись (в оглавлении) должна быть уникальной, иначе, какая из записей будет именно той что вы искали? Итак, когда вы создаете кластерный индекс — он должен быть уникальным. Но sql server не требует, чтобы кластерный ключ создавался на колонке с уникальными значениями. Вы моежете создать его на любой колонке (колонках), которая вам нравится. Внутренне, если кластерный ключ не уникален, sql server его «уникализирует» добавлением к информации 4 байтового целого. Итак, если кластерный индекс создан на чем-то что не является уникальным, тогда не только возникают дополнительные накладные расходы на создание индекса, но так же расходуется место на диске, дополнительно возрастает стоимость операций вставки и обновления, а в sql server 2000, так же увеличивается стоимость перестроения кластерного индекса (поскольку неудачный выбор кластерного ключа теперь более вероятен).

Почему узкий?
Кластерный ключ должен быть узким в некотором по тем же причинам, что и уникальным. Если кластерный ключ используется для поиска во всех некластерных индексах, тогда его значения дублируются во всех некластерных индексах. Если кластерный ключ действительно широкий, тогда все некластерные индексы будут (без необходимости) широкими. Это будет трата дискового пространства, создаст дополнительную стоимость операций вставки и обновления и потребует больше времени (из-за размера) на перестроение этих структур индексов. Итак, что значит «узкий» — это значит нужно постараться использовать как можно меньше байт, чтобы уникально определить ваши строки. «Узкое» число, если возможно.

Почему статичный?
Кластерный ключ должен быть статичным в основном по тем же причинам что и уникальным и узким. Если кластерный ключ используется для поиска из всех некластерных индексов, тогда он дублируется во всех некластерных индексах. На самом деле, для некоторых таблиц, кластерный ключ будет наиболее дублируемыми данными. И если эти данные изменяются, то требуется обновить как значения в базовой таблице, так и значения в каждом некластерном индексе. И если ключ изменяется, это заставляет запись перемещаться. Когда запись перемещается — это создает фрагментацию. На это будет тратиться дополнительное место на диске, создаваться дополнительная стоимость добавлений и обновлений, на это потребуется больше времени (из-за перемещения записи и (вероятно) последующих расщеплений) и потребуется больше операций обслуживания.

Ок, похоже, я хочу уникальное, узкое и статичное значение… Как насчет guid?
Обычно я рекомендую числовую колонку IDENTITY в качестве кластерного ключа, но я всегда получаю этот вопрос. На самом деле, часто я даже просто жду, сколько пройдет времени, прежде чем я получу этот вопрос. 😉 Так или иначе, guid соответсвует этим критериям довольно неплохо — он определенно уникальный, обычно статичный и относительно узкий. Так что же с ним не так? В sql server 2000 функция guid (newid()) построена таким образом, что создаваемое значение создается не в соответствии с шаблоном постоянно возрастающего значения (ever-increasing pattern), колонка IDENTITY создает такое значение. Но подождите, я же не говорила что вам нужно использовать шаблон постоянно возрастающего значения…

Ок, последний критерий, который я ищу в кластерном ключе: всегда возрастающее значение.
Если кластерный ключ постоянно возрастает, то новые строки имеют особое местоположение куда они могут быть помещены. Если это место в конце таблицы, тогда для новой строки должно быть выделено место, но при этом нет необходимости иметь свободное место в середине таблицы. Если строка добавляется туда, где нет свободного места, то это место должно быть создано (например, если ваша вставка основана на фамилии, тогда, когда появятся строки для вставки, свободное место потребуется там, куда должны быть добавлены строки содержащие эту фамилию). Если требуется свободное место, оно создается sql server некоторой операцией, известной как расщепление. Расщепления в SQL Server это разделение 50/50 простым перемещением — 50% данных остается, а 50% перемещается. Это сохраняет индекс целым логически (самый нижний уровень индекса — называемый листовым уровнем — представляет собой двусвязный список), но не физически. Когда в индексе происходят многочисленные расщепления, тогда говорят о том, что индекс фрагментирован. Хорошим примером всевозрастающего индекса являются колонки IDENTITY (и они так же естественным образом уникальны, статичны и узки) или нечто максимально похожее — например, колонка datetime (или т.к. сама по себе с большой вероятностью не уникально, то колонки datetime, identity). Но подождите, что же насчет guid?

В SQL Server 2000 единственной функцией формирования guid была newid() — которая не создавала всевозрастающие значения. В SQL Server 2005 вы можете использовать новую функцию для генерации guid, называемую newsequentialid(), для заполнения колонки uniqueidentifier. Вот пример как вы моежете это использовать.

CREATE TABLE Test
(
TestID uniqueidentifier CONSTRAINT Test_TestID_Default DEFAULT newsequentialid(),
Inserted datetime CONSTRAINT Test_Inserted_Default DEFAULT getdate()
)
go 

INSERT Test DEFAULT VALUES
go 

SELECT * FROM Test
go

Есть ли способ создавать последовательные guid в SQL Server 2000?
Да! Используйте расширенную процедуру Gert Drapers-а для генерации последовательных guid. Взять можно отсюда. Он опубликовал ее недавно и это хорошие перемены для приложений и баз данных SQL Server 2000.
Полное название ссылки:

XPGUID.DLL — Sequential GUID generation and GUID helper functions XP

Спасибо за чтение,
kt.

 

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Анти-спам: введите результат (цифрами) *