Какой тип данных является оптимальным для кластеризованного индекса таблицы, опубликованной с использованием репликации транзакций?

Question

У нас есть приложение, которое хранит данные в базе данных SQL-сервера. (В настоящее время мы поддерживаем SQL Server 2005 и выше). В нашей БД более 400 таблиц. Структура базы данных не идеальна. Самая большая проблема заключается в том, что у нас есть много таблиц с GUID (NEWID()) в качестве первичных CLUSTERED ключей. Когда я спросил нашего главного архитектора баз данных "почему?", Он сказал: "это из-за репликации". Наша БД должна поддерживать репликацию транзакций. Первоначально все первичные ключи были INT IDENTITY(1,1) CLUSTERED. Но позже, когда дело дошло до поддержки репликации, эти поля были заменены на UNIQUEIDENTIFIER DEFAULT NEWID(). Он сказал: "В противном случае это был кошмар, чтобы иметь дело с репликацией". NEWSEQUENTIALID() в то время не поддерживался SQL 7/2000. Итак, теперь у нас есть таблицы со следующей структурой:

CREATE TABLE Table1(
        Table1_PID uniqueidentifier DEFAULT NEWID() NOT NULL,
        Field1 varchar(50) NULL,
        FieldN varchar(50) NULL,
        CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (Table1_PID)
    )
    GO

CREATE TABLE Table2(
    Table2_PID uniqueidentifier DEFAULT NEWID() NOT NULL,
    Table1_PID uniqueidentifier NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table2 PRIMARY KEY CLUSTERED (Table2_PID),
    CONSTRAINT FK_Table2_Table1 FOREIGN KEY (Table1_PID) REFERENCES Table1 (Table1_PID)
)
GO

Все таблицы на самом деле имеют много полей (до 35) и до 15 некластеризованных индексов.

Я знаю, что GUID, который не является последовательным - как тот, у которого есть значения, сгенерированные в клиенте (с использованием.NET) ИЛИ, сгенерированные SQL-функцией NEWID () (как в нашем случае), является ужасно плохим выбором для кластеризованного индекса для две причины:

1. фрагментация
2. размер

Я также знаю, что ХОРОШИМ ключом кластеризации является то, что это:

1. уникальный,
2. узкая,
3. статичны,
4. постоянно растет,
5. ненулевой,
6. и фиксированной ширины

Для более подробной информации о причинах этого, посмотрите следующее отличное видео: http://technet.microsoft.com/en-us/sqlserver/gg508879.aspx.

Таким образом, INT IDENTITY действительно лучший выбор. BIGINT IDENTITY также хорош, но обычно для подавляющего большинства таблиц достаточно INT с 2+ миллиардами строк.

Когда наши клиенты начали страдать от фрагментации, было решено сделать первичные ключи не кластеризованными. В результате эти таблицы остались без кластерного индекса. Другими словами, эти таблицы были превращены в HEAPS. Мне лично не нравится это решение, потому что я уверен, что таблицы кучи не являются частью хорошего дизайна базы данных. Пожалуйста, ознакомьтесь с этой статьей о рекомендациях по SQL Server: http://technet.microsoft.com/en-us/library/cc917672.aspx.

В настоящее время мы рассматриваем два варианта улучшения структуры базы данных:

Первый вариант - заменить DEFAULT NEWID () на DEFAULT NEWSEQUENTIALID() для первичного кластерного ключа:

CREATE TABLE Table1_GUID (
  Table1_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
  Field1 varchar(50) NULL,
  FieldN varchar(50) NULL,
  CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (Table1_PID)
)
GO

Второй вариант - добавить столбец INT IDENTITY в каждую таблицу и сделать его индексом CLUSTERED UNIQUE, оставляя первичный ключ НЕ кластеризованным. Таким образом, Table1 будет выглядеть так:

CREATE TABLE Table1_INT (
  Table1_ID int IDENTITY(1,1) NOT NULL,
  Table1_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
  Field1 varchar(50) NULL,
  FieldN varchar(50) NULL,
  CONSTRAINT PK_Table1 PRIMARY KEY NONCLUSTERED (Table1_PID),
  CONSTRAINT UK_Table1 UNIQUE CLUSTERED (Table1_ID)
)
GO

Table1_PID будет использоваться для репликации (поэтому мы оставили его как PK), в то время как Table1_ID вообще не будет реплицироваться.

Короче говоря, после запуска тестов, чтобы увидеть, какой подход лучше, мы обнаружили, что оба решения не годятся:

Первый подход (Table1_GUID) выявил следующие недостатки: хотя последовательные GUID определенно намного лучше, чем обычные случайные GUID, они все равно в четыре раза больше, чем INT (16 против 4 байт), и это является фактором в нашем случае, потому что мы имеем много строк в наших таблицах (до 60 миллионов) и множество некластеризованных индексов в этих таблицах (до 15). Ключ кластеризации добавляется к каждому некластеризованному индексу, что значительно увеличивает отрицательный эффект от размера 16 против 4 байтов. Больше байтов означает больше страниц на диске и в оперативной памяти SQL Server, и, следовательно, больше дискового ввода-вывода и больше работы для SQL Server.

Точнее, после того, как я вставил 25 миллионов строк реальных данных в каждую таблицу, а затем создал 15 некластеризованных индексов для каждой таблицы, я увидел большую разницу в пространстве, используемом таблицами:

EXEC sp_spaceused 'Table1_GUID' -- 14.85 GB
EXEC sp_spaceused 'Table1_INT' -- 11.68 GB

Кроме того, тест показал, что INSERT в Table1_GUID был немного медленнее, чем в Table1_INT.

Второй подход (Table1_INT) показал, что в большинстве запросов (SELECT) объединение двух таблиц в плане выполнения Table1_INT.Table1_PID = Table2_INT.Table1_PID ухудшилось из-за появления дополнительного оператора Key Lookup.

Теперь вопрос: я считаю, что должно быть лучшее решение для нашей проблемы. Если бы вы могли порекомендовать мне что-нибудь или указать хороший ресурс, я был бы очень признателен. Заранее спасибо.

Обновлено:

Позвольте мне привести пример оператора SELECT, в котором появляется дополнительный оператор поиска ключей:

--Create 2 tables with int IDENTITY(1,1) as CLUSTERED KEY.
--These tables have one-to-many relationship.
CREATE TABLE Table1_INT (
    Table1_ID int IDENTITY(1,1) NOT NULL,
    Table1_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table1_INT PRIMARY KEY NONCLUSTERED (Table1_PID),
    CONSTRAINT UK_Table1_INT UNIQUE CLUSTERED (Table1_ID)
)
GO

CREATE TABLE Table2_INT(
    Table2_ID int IDENTITY(1,1) NOT NULL,
    Table2_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
    Table1_PID uniqueidentifier NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table2_INT PRIMARY KEY NONCLUSTERED (Table2_PID),
    CONSTRAINT UK_Table2_INT UNIQUE CLUSTERED (Table2_ID),
    CONSTRAINT FK_Table2_Table1_INT FOREIGN KEY (Table1_PID) REFERENCES Table1_INT (Table1_PID)
)
GO

И создайте две другие таблицы для сравнения:

--Create the same 2 tables, BUT with uniqueidentifier NEWSEQUENTIALID() as CLUSTERED KEY.
CREATE TABLE Table1_GUID (
    Table1_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table1_GUID PRIMARY KEY CLUSTERED (Table1_PID),
)
GO

CREATE TABLE Table2_GUID(
    Table2_PID uniqueidentifier DEFAULT NEWSEQUENTIALID() NOT NULL,
    Table1_PID uniqueidentifier NULL,
    Field1 varchar(50) NULL,
    FieldN varchar(50) NULL,
    CONSTRAINT PK_Table2_GUID PRIMARY KEY CLUSTERED (Table2_PID),
    CONSTRAINT FK_Table2_Table1_GUID FOREIGN KEY (Table1_PID) REFERENCES Table1_GUID (Table1_PID)
)
GO

Теперь запустите следующие операторы select и посмотрите на план выполнения для сравнения:

SELECT T1.Field1, T2.FieldN
FROM Table1_INT T1 
    INNER JOIN Table2_INT T2 
        ON T1.Table1_PID = T2.Table1_PID;

SELECT T1.Field1, T2.FieldN
FROM Table1_GUID T1 
    INNER JOIN Table2_GUID T2 
        ON T1.Table1_PID = T2.Table1_PID;

Answer 1

Я лично пользуюсь INT IDENTITY для большинства моих первичных и кластерных ключей.

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

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

Смотрите ее статьи об индексации здесь:

• GUID как первичные ключи и / или ключ кластеризации
• Дискуссия по кластерному индексу продолжается...
• Постоянно растущий ключ кластеризации - Дебаты о кластеризованных индексах.......... снова!
• Дисковое пространство дешево - не в этом дело!

а также см. "Стоимость GUID" Джимми Нильссона в качестве первичного ключа.

GUID действительно плохой выбор для ключа кластеризации, так как он широкий, абсолютно случайный и, следовательно, приводит к плохой фрагментации индекса и низкой производительности. Кроме того, строки ключей кластеризации также хранятся в каждой записи каждого некластеризованного (дополнительного) индекса, так что вы действительно хотите сохранить его небольшим - GUID 16 байт против INT 4 байта, и с несколькими некластеризованными индексами и несколькими миллионами строк, это делает ОГРОМНОЕ различие.

В SQL Server ваш первичный ключ по умолчанию является вашим ключом кластеризации, но это не обязательно. Вы можете легко использовать GUID в качестве вашего некластерного первичного ключа и INT IDENTITY как ваш ключ кластеризации - просто нужно немного знать об этом.