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

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

Кластеризованный индекс должен иметь значение 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 году.

Я предполагаю, что у вас есть веские причины использовать направляющие в качестве идентификаторов.

Фрагментация чаще всего возникает при сканировании, а не при поиске. Фрагментация оказывает большое влияние на упреждающее чтение, а поиски не используют и не нуждаются в упреждающем чтении. Нефрагментированный индекс с плохим выбором столбцов всегда будет работать хуже, чем индекс фрагмента на 99% с хорошими, пригодными для использования столбцами. Если бы вы описали запросы в стиле отчетов DW, которые сканируют таблицы, я бы порекомендовал сосредоточиться на устранении фрагментации, но для описываемой вами нагрузки имеет смысл сосредоточиться на эффективных (охватывающих) поисках и (небольших) сканированиях диапазона.

Учитывая, что ваш шаблон доступа всегда управляется @userId, это должен быть крайний левый столбец в кластеризованном индексе. Я бы также добавил setOn в качестве второго столбца в кластеризованном индексе, потому что он добавляет некоторое предельное значение для большинства запросов (я говорю маргинальное, потому что @userId настолько селективен, в худшем случае составляет 90 записей из 90 миллионов, что дополнительная фильтрация добавляется @setOn не критично). Я не буду добавлять некластеризованный индекс, из описанных вами запросов в них нет необходимости.

Единственная проблема - это удаление старых записей (срок хранения 30 дней). Я бы не рекомендовал использовать вторичный индекс NC для удовлетворения этого. Я бы предпочел развернуть схему еженедельного разделения со скользящим окном, см. Как реализовать автоматическое скользящее окно в многораздельной таблице на SQL Server 2005. В этом решении старые записи удаляются переключением раздела, что является наиболее эффективным способом. Схема ежедневного разделения более точно удовлетворяет требованию к хранению в 30 дней, и, возможно, ее стоит попробовать и протестировать. Я не решаюсь рекомендовать 30 разделов напрямую, потому что вы описываете некоторые запросы, которые потенциально могут искать определенные записи @userId в каждом разделе, а 31 раздел может создать проблемы с производительностью при большой нагрузке. Лучше протестируйте и измерьте оба.

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

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

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

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

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

Если вам не нужен столбец postId, тогда первичным ключом будет (setOn, userId), в противном случае это может быть просто postId.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таким образом, коэффициент заполнения 90 (10% свободного места на каждой странице) будет более чем достаточно.

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

Я очень рекомендую ежедневную чистку.

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

Я предлагаю:

1. Кластерный индекс по идентификатору пользователя
2. Некластеризованный индекс покрытия на сетоне и входе или просто некластеризованный индекс на сетоне

Один из способов решить эту проблему - создать таблицу на каждый день.

Для таблицы из 3M записей нет проблем с кластеризованным индексом для идентификатора пользователя и набора. Ваше время вставки будет намного меньше.

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

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