Лучший способ индексировать эту очень большую таблицу

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

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

Ваши запросы, где пункт должен быть в форме:

WHERE setOn > @setOn AND userId = @userId

Или когда Вы возвращаете целый месяц:

WHERE setOn BETWEEN @setOnBegin AND @setOnEnd AND userId = @userId

Ваш новый дизайн схемы, без разделения был бы похож на это:

-- Stub table for foreign key
CREATE TABLE Users
(
 [userId] [uniqueidentifier] NOT NULL
  CONSTRAINT PK_Users PRIMARY KEY NONCLUSTERED
  CONSTRAINT DF_Users_userId DEFAULT NEWID(),
 [userName] VARCHAR(50) NOT NULL
)
GO

CREATE TABLE DiaryEntries
(
 [userId] [uniqueidentifier] NOT NULL
  CONSTRAINT FK_DiaryEntries_Users FOREIGN KEY REFERENCES Users,
 [setOn] [datetime] NOT NULL
  CONSTRAINT DF_DiaryEntries_setOn DEFAULT GETDATE(),
 [entry] [nvarchar](255) NULL,
 CONSTRAINT PK_DiaryEntries PRIMARY KEY CLUSTERED (setOn, userId)
)
GO

После того, как Вы получите ту работу, необходимо добавить разделение. Для этого запустите с Этого сообщения в блоге для некоторой теории. Затем начните читать это техническое описание MSDN. Отчет записан на 2005, и были улучшения раздела в 2008, что я не занялся расследованиями, таким образом, решение могло бы быть простым в 2008.

Не здесь для критикования решения, ни - я имеющий возможность делать так, поскольку я не знаю достаточно о проблеме. Вот моя обратная связь:

• Если единственная вещь Ваш не довольный использует слишком много дискового пространства из-за размера строки, проверьте редкие столбцы Таким образом, все пустые указатели не занимают так много места!
• Наличие внешних ключей собирается замедлить Ваши вставки значительно, Вы протестировали это?

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

Поэтому строки должны быть заказаны в хронологическом порядке

Поэтому setOn должен быть первой частью первичного ключа. - или, идеально, добавляет 'postId' столбец, который является просто целым числом что автоинкременты самим

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

Таким образом мы получили, быстро вставляют времена. Теперь мы хотим быстрое время поиска при выборе на основе на идентификатор пользователя.

Для этого мы должны добавить вторичный индекс к таблице, которая должна быть на useId. С тех пор существует только 90 записей на пользователя, это достаточно, чтобы rdbms смог получить быстро все строки для того пользователя (все 90 из них, так как строки только одного месяца за один раз), и затем сканирование таблицы те 90 строк, которые будут ослепляюще быстры.

Индекс может быть любым стандартным B-деревом, красно-черным деревом, индексом, независимо от того, что идет с Вашей базой данных.

Вставку немного замедлит вставка в индекс, но не слишком много. Древовидные структуры довольно хороши в обработке случайных вставок.

Так как индекс UserId основан на наборе UserIds, который является устойчивым множеством, дерево должно быть довольно стабильным и не нуждаться в слишком большом изменении баланса: просто вершины в конце изменятся, поскольку записи журнала добавлены и очищены, который действительно не изменит форму дерева слишком много.

Я не поклонник Вашего нового решения. Это просто собирается представить новые проблемы, крупнейшее существо, которое ОБНОВЛЕНИЯ (обычно) медленнее, чем, ВСТАВЛЯЕТ, и создайте больший риск для блокирования, когда обновления происходят.

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

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

К сожалению, значение по умолчанию SQL обычно 0 (что означает то же как 100), что означает, что все страницы абсолютно полны, который вызывает много расщеплений страницы. Многие люди рекомендуют значение 90 (10%-е свободное пространство в каждой странице данных). Я не могу сказать Вам, каков идеал для Вашей таблицы был бы, но если Ваш ультрапараноик о расщеплениях страницы, попробуйте 75 или еще меньше, если можно сэкономить дополнительное дисковое пространство. Существуют некоторые счетчики perfmon, которые можно контролировать для наблюдения за расщеплениями страницы, или Вы могли выполнить запросы, чтобы сказать Вам свободное пространство процента в каждой странице данных.

Относительно специфических особенностей индексов на Вашей таблице (исходная версия), я рекомендовал бы кластерный индекс на ([идентификатор пользователя], [setOn]), по причинам, что Remus упомянул.

Вам также будет нужен некластеризованный индекс на ([setOn]) так, чтобы Ваш "удалили старые записи" запрос, не должен делать полного сканирования таблицы для нахождения всех старых записей.

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

Править: Некоторые предварительные вычисления на предполагаемом fillfactor для этой таблицы.

Для каждого пользователя, 3 новых записей в день, сохранил 30 дней, таким образом, ~90 общих записей. Принятие Вас делает ежедневную чистку всех записей, более старых, чем 30 дней (только в противоположность чистке каждые 30 дней), затем Вы только добавляете/удаляете меньше чем 5% записей ежедневно.

Таким образом, fillfactor 90 (10%-е свободное пространство на каждой странице) должен быть более, чем достаточным.

Если бы Вы только производите чистку ежемесячно, то Вы позволили бы почти 60 дням сложить перед удалением самых старых 30 что означает, что Вам было бы нужно что-то как 50% fillfactor.

Я настоятельно рекомендовал бы ежедневную чистку.

Редактирование 2: После дальнейшего соображения некластерный индекс на [setOn] не может быть достаточно выборочным, чтобы использоваться Вашим запросом чистки (единственный день является 1/30 или 3,3% строк, который является правильным на краю "полезных"). Это могло бы просто сделать сканирование кластерного индекса так или иначе, даже если индекс существует. Вероятно, стоящий тестирования и с и без этого дополнительного индекса.

Я предлагаю:

1. Кластерный индекс на идентификаторе пользователя
2. Некластеризованный Закрывающий индекс на seton и записи или просто некластерном индексе на seton

Один способ решить это состоит в том, чтобы иметь таблицу в течение каждого дня.

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

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

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