ГлавнаяРазноеMs sql кластерный индекс

Влияние кластерного индекса на производительность БД. Ms sql кластерный индекс


Кластерный индекс в SQL Server MS SQL Server

Для Oracle или MySQL значения по умолчанию не совпадают. Каждая база данных имеет свои собственные значения по умолчанию и ее конкретные функции. Некоторые даже используют один и тот же термин для разных значений.

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

Если он не удаляется автоматически, мне нужно создать задание для перестройки и реорганизации индексов?

Правило № 1 по восстановлению индексов – измерение, измерение, измерение. Докажите, что перестройка была полезной, иначе не беспокойтесь, чтобы сделать это снова, если ничего не изменится.

Простое удаление (или тысяча) не является автоматической причиной для восстановления индекса.

Если вы собираетесь перестроить индекс, вы должны знать (1), сколько блоков данных до и после (2) времени доступа до и после.

Существует много дезинформации и суеверия относительно перестройки индекса как общей практики. Индексы, на которые вы ссылаетесь, являются структурами B-Tree. Они предназначены для масштабируемого доступа O (log N). Нет доказательств того, что индексы должны быть реорганизованы по умолчанию. Каждый индекс является его собственным животным. Индексы B-дерева достигают точки застоя (равновесия) после некоторого времени использования, а когда вы их перестраиваете, вы их уплотняете (хорошо), но в конечном итоге они возвращаются к этой точке застоя. Если я не обнаружу проблему производительности / ввода-вывода в одном из них, я сделаю это вручную в каждом конкретном случае.

Два самых больших преимущества перестройки:

  1. Более плотно упаковывает данные в меньшие блоки, что улучшает кеш и IO.
  2. Изменяет некластеризованные индексы по фактическим накопленным данным. Если ваши шаблоны доступа всегда последовательны (включая вставки), то это обычно не проблема, блоки будут упорядочены.

Oracle и SQL Server – замечательные технологии. Восстановление индексов без доказательств не подходит для профессионального администратора баз данных.

Я знаю, что когда мы создаем таблицу в sqlserver с ее основным ключом, кластеризованный индекс автоматически создается.

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

Но если я удалю строку из этой таблицы, кластерный индекс, связанный с этой строкой, все еще остается в таблице индексов или автоматически удаляется?

Если это действительно кластеризованный индекс, то индекс – это данные таблицы. Если вы удалите что-то из таблицы, оно исчезнет. Существует несколько внутренних операций, таких как записи призраков, но да, данные исчезли (с точки зрения приложения).

Если он не удаляется автоматически, мне нужно создать задание для перестройки и реорганизации индексов?

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

(то же самое для mysql, oracle и т. д.?)

На вопрос был отмечен SQL Server, поэтому я отвечаю на тег SQL Server. Это ОЧЕНЬ широкий вопрос для одного сообщения.

sqlserver.bilee.com

Влияние кластерного индекса на производительность БД MS SQL Server

Кластерный индекс должен содержать столбцы (столбцы), которые наиболее часто запрашиваются, чтобы дать наибольший шанс поиска или сделать некластеризованный индекс, охватывать все столбцы в запросе.

Первичный ключ и кластеризованный индекс не должны быть одинаковыми. Они являются и кандидатными ключами, и в таблицах часто есть более одного такого ключа.

Ты сказал

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

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

Как говорили другие, важно уменьшить количество столбцов / байтов в кластерном индексе, насколько это возможно.

Слишком плохо, что Sequence является случайным значением вместо увеличения, но это может оказаться невозможным. Ответ заключается не в том, чтобы вставить столбец идентификатора, если ваше приложение не может начать использовать его в качестве основного условия запроса в этой таблице (маловероятно). Теперь, поскольку вы застряли в этом случайном столбце «Последовательность» (предполагая, что он чаще всего запрашивается), давайте посмотрим на другое ваше утверждение:

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

Это не совсем так.

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

Строки внутри каждой страницы 8k не упорядочены. Просто все строки на каждой странице меньше, чем на следующей странице и больше, чем предыдущая. Проблема возникает, когда вы вставляете строку, и страница заполнена: вы получаете разделение страницы. Двигатель должен скопировать все строки после вставленной строки на новую страницу, и это может быть дорогостоящим. С помощью случайного ключа вы получите много разбиений на страницы. Вы можете улучшить проблему, используя нижний fillfactor при восстановлении индекса. Вам нужно будет сыграть с ним, чтобы получить нужный номер, но 70% или 60% могут служить вам лучше, чем 90%.

Я считаю, что наличие datetime в качестве второго столбца CI может быть полезным, поскольку вы все равно будете иметь дело со страницами, которые необходимо разделить между двумя различными значениями последовательности, но это не так плохо, как если бы второй столбец в CI также был случайным , так как вам будет гарантировано разбиение страницы на каждую вставку, где с восходящим значением вам может повезти, если строка может быть добавлена ​​на страницу, потому что следующий номер последовательности начинается на следующей странице.

Сокращение типов данных и количества всех столбцов в таблице, а также некластеризованных индексов также может повысить производительность, так как больше строк на страницу = меньше страниц читается для каждого запроса. Особенно, если двигатель вынужден выполнять сканирование таблицы. Перемещение кучки редко запрашиваемых столбцов в отдельную таблицу 1-1 может творить чудеса для некоторых ваших запросов.

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

  • Измените столбец «Последовательность» на bigint, чтобы сохранить байт для каждой строки (8 байтов вместо 9 для числа).
  • Используйте таблицу поиска для клиента с 4-байтным столбцом идентификатора int вместо varchar (9). Это экономит 5 байт в строке. Если возможно, используйте smallint (-32768 to 32767), который составляет 2 байта, что еще больше экономит 7 байт в строке.

Реферат: CI должен начинаться с наиболее запрашиваемого столбца. Удалите все столбцы из CI, которые вы можете. Сократите столбцы (байты) столько, сколько сможете. Используйте нижний fillfactor для уменьшения разрывов страниц, вызванных столбцом случайной последовательности (если он должен оставаться первым из-за того, что его больше всего интересуют).

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

ОБНОВИТЬ

Еще одна стратегия, которую следует рассмотреть, – это если составной ключ, используемый в таблице, может быть преобразован в int, и создается таблица поиска значений. Предположим, что некоторая комбинация менее четырех столбцов повторяется более чем в 100 строках, например, Sequence + Client + Hash, но только с переменными значениями Date. Тогда вставка в отдельную таблицу SequenceClientHash с столбцом идентификации может иметь смысл, потому что тогда вы можете разыскать искусственный ключ один раз и использовать его снова и снова. Это также поможет вашему CI добавить новые строки только на последней странице (yay) и существенно уменьшить размер CI, как это повторяется во всех некластеризованных индексах (yippee). Но это имело бы смысл только в некоторых узких шаблонах использования.

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

О «тоннах разбиения страниц и фрагментации плохих индексов»: как я уже сказал, это может быть несколько улучшено с более низким коэффициентом заполнения. Кроме того, частая реорганизация онлайн-индекса (не такая же, как перестройка) может помочь уменьшить эффект этого.

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

Есть несколько вещей, которые вы недопонимаете о том, как SQL создает и использует индексы.

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

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

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

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

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

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

Поэтому, если у вас есть несколько некластеризованных индексов на вашей таблице, то вы определенно a) тратите много места (а не только на диск – также в ОЗУ вашего сервера!), И b) ваша производительность будет плохой.

Хороший кластеризованный индекс должен быть:

  • small (лучшая ставка: 4-байтовый INT) – у вас довольно плохо с до 28 байт за запись
  • уникальный
  • стабильный (без изменений)
  • постоянно растет

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

Честно говоря: просто добавьте суррогатный ID INT IDENTITY(1,1) в свой стол и сделайте основной кластеризованный ключ – вы должны увидеть довольно хороший прирост производительности, просто из-за этого, если у вас много INSERT (и UPDATE) операции продолжаются!

Посмотрите еще информацию о том, что делает хороший ключ кластеризации, и что важно для них, здесь:

  • GUID как ОСНОВНЫЕ КЛЮЧИ и / или ключ кластеризации
  • Дискуссия по кластерному индексу продолжается …
  • Постоянно растущий ключ кластеризации – обсуждение кластерных индексов ………. снова!

В конечном итоге я согласен с последним абзацем Эрика:

«В конечном счете, все сводится к точной системе и ее уникальному шаблону доступа к данным в сочетании с решениями о том, какие части вы хотите оптимизировать …»

Это основная вещь, заставляющая людей учиться: универсального решения нет.

Вы должны знать свои данные и действия, выполненные против него. Вы должны знать, как часто происходят разные типы действий, их влияние и ожидаемое время выполнения (вам не нужно жестко настраивать некоторые редко выполняемые запросы и влиять на все остальное, если конечный пользователь соглашается с тем, что время выполнения запроса не так важно – допустим, ждать несколько минут для некоторого отчета один раз в неделю – все в порядке). Конечно, как сказал Эрик

«производительность – это не только время ожидания пользователя или даже время ответа на запрос, но также и ресурсы сервера»

Если такой запрос влияет на общую производительность сервера, его следует рассматривать как серьезный кандидат на оптимизацию, даже если время выполнения в порядке. Я видел очень быстрые запросы, которые использовали огромное количество процессоров на многопроцессорных серверах, а несколько более медленное решение было несравнимо «легче» с точки зрения использования ресурсов. В этом случае я почти всегда иду медленнее.

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

sqlserver.bilee.com

Является ли это хорошим местом для кластерного индекса для ms sql 2008? MS SQL Server

Согласно блогу Кимберли Л. Триппа , кластеризованный индекс должен быть:

  • уникальный
  • узкий
  • статический
  • все возрастающее

Итак, давайте оценим ваш предложенный «номер пересмотра» в свете этих критериев.

  • уникальный – это будет зависеть от вашего ответа на комментарий Эд Харпера. Если он не уникален, он может показаться комбинацией номера версии + файла.
  • узкий. Предполагая, что ревизия является чем-то вроде целого, вы здесь. Если вам нужно перейти к номеру ревизии + файл для уникальности, и если у файла есть идентификатор, это другое целое число, вы все равно в порядке.
  • static – похоже, что ревизия никогда не изменяется после ее создания, поэтому вы здесь хороши.
  • Когда-либо увеличивалось – я читаю между строк немного сейчас, но я думаю, что ваши новые версии, вероятно, созданы таким образом.

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

На вершине ответа Джо Стефанелли я бы добавил:

  • Как используется таблица?
  • Является ли это просто дампом или журналом активности, используется ли он для целей OLTP (поиск по нескольким строкам за раз), используется ли он для OLAP-подобной активности (читайте много и много строк за раз)?
  • Является ли первичным критически важным (требуется получение строк в микросекунду) или вторичным (скажем, для отчетов о конце дня)?

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

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

Если вы сообщаете обо всех файлах, загруженных в данный день, кластеризованный индекс в RevisionNumber был бы идеальным.

Если вам нужно искать отдельные файлы с любой частотой или целесообразностью, этот индекс будет сосать, поскольку, если бы у меня все получилось, было бы 100 000 000 строк (файлов) для каждого RevisionNumber, но простой некластеризованный index on FileName или FileName + RevisionNumber, будет охватывать это (но см. следующую идею).

Для быстрого поиска, FileName, FilePath или FilePath + FileName может быть больно длинными строками для индексации. Добавление столбца (или сохраненного расчетного столбца) для контрольной суммы (FileWhatever) и индексации на этом может сэкономить много времени. Запросы должны выглядеть примерно так:

SELECT FullFileName, Plus, Other, Columns from FileTable where RevisionNumber = @TargetRevision and ChecksumColumn = checksum(@TargetFullFileName) and FullFileName = @TargetFullFileName

Наконец, если вы действительно добавляете и отбрасываете около 100 000 000 строк каждый день, я серьезно рассмотрю разбиение таблиц с разделом на основе RevisionNumber.

sqlserver.bilee.com

Кластерный индекс MS SQL Server

Не 100% уверены в том, что вы ожидаете услышать – у вас может быть только один индекс кластеризации в таблице, и по умолчанию каждая таблица (с очень небольшим исключением) должна иметь один. Все индексы, как правило, больше всего помогают вашим SELECT, а некоторые, как правило, немного вредят INSERT, DELETE и, возможно, UPDATEs (или много, если они выбраны плохо).

Кластеризованный индекс делает таблицу быстрее для каждой операции. ДА! Оно делает. См. Превосходную статью Kim Tripp The Clustered Index Debate для справочной информации. Она также упоминает ее основные критерии для кластерного индекса:

  • узкий
  • статический (никогда не изменяется)
  • уникальный
  • если это когда-либо возможно: когда-либо возрастающее

INT IDENTITY отлично справляется с этим – GUID этого не делают. Для получения подробной справочной информации см. GUID в качестве основного ключа .

Почему узкие? Поскольку кластерный ключ добавляется к каждой индексной странице каждого некластеризованного индекса в той же таблице (чтобы иметь возможность реально искать строку данных, если это необходимо). Вы не хотите иметь VARCHAR (200) в своем ключе кластеризации …

Почему уникально? См. Выше – ключ кластеризации – это элемент и механизм, которые SQL Server использует для уникального поиска строки данных. Он должен быть уникальным. Если вы выберете нестандартный ключ кластеризации, сам SQL Server добавит 4-байтовый идентификатор к вашим ключам. Будьте осторожны!

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

Кроме того, любые запросы, содержащие предложения WHERE, являются хорошим кандидатом – выбирайте те, которые выполняются много. Поместите индексы в столбцы, которые отображаются в предложениях WHERE, в операторах ORDER BY.

Далее: измерьте свою систему, проверьте представления DMV (динамические представления управления) для подсказок о неиспользуемых или отсутствующих индексах и снова и снова настраивайте свою систему. Это непрерывный процесс, вы никогда не закончите!

Еще одно предупреждение: с индексом грузовиков вы можете сделать любой запрос SELECT действительно очень быстрым. Но в то же время могут пострадать INSERT, UPDATE и DELETE, которые должны обновить все вовлеченные индексы. Если вы только ВЫБРАТЬ – сходите! В противном случае это прекрасный и деликатный баланс. Вы всегда можете настроить один запрос за пределы веры, но остальная часть вашей системы может пострадать при этом. Не переоценивайте свою базу данных! Поместите несколько хороших индексов на место, проверьте и посмотрите, как работает система, а затем, возможно, добавьте еще один или два, и снова: обратите внимание, как это влияет на общую производительность системы.

Я не совсем уверен, что вы подразумеваете под «должен использоваться для вставки / обновления / удаления», но, на мой взгляд, каждая таблица должна иметь кластеризованный индекс. Кластеризованный индекс определяет порядок, в котором данные фактически хранятся. Если кластерный индекс не определен, данные будут просто храниться в куче. Если у вас нет естественного столбца для кластеризованного индекса, вы всегда можете просто создать столбец идентификации как int или bigint, как это.

CREATE TABLE [dbo].[demo]( [ID] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nchar](10) NULL, [LastName] [nchar](10) NULL, [Job] [nchar](10) NULL, CONSTRAINT [PK_demo] PRIMARY KEY CLUSTERED ( [ID] ASC ))

sqlserver.bilee.com

Кластерные индексы SQL Server MS SQL Server

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

Когда хорошо иметь кластеризованный индекс

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

Кластерные индексы

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

Пока ваш кластерный индекс находится на том значении, которое наиболее часто используется для поиска, и оно уникально и увеличивает их, вы получаете некоторые потрясающие преимущества производительности из кластерного индекса. Например, если у вас есть таблица под названием USERS, в которой вы обычно просматриваете пользовательские данные на основе USER_ID, тогда кластеризация на USER_ID ускорит работу всех этих поисков. Это просто уменьшает количество страниц данных, которые необходимо прочитать, чтобы получить данные.

Если у вас слишком много ключей в вашем кластерном индексе, это также может замедлить работу.

Общие правила для кластеризованных индексов:

Не кластеры на каких-либо столбцах varchar.

Кластеризация на столбцах INT IDENTITY обычно лучше всего.

Кластер на то, что вы обычно ищете.

Кластеризация на UniqueIdentifiers

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

Резюме

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

Кластеризованный индекс – это не то же самое, что индексированная таблица. При использовании IOT каждое поле должно участвовать в ключе IOT. Кластеризованный индекс SQL Server не обязательно должен быть уникальным и не должен быть первичным ключом.

Кластеризованные индексы широко используются на SQL Server, так как почти всегда есть естественный порядок, который делает наиболее часто используемый запрос более эффективным. IOTs в Oracle несут больше багажа, поэтому они не так полезны, хотя они могут быть более полезными, чем они обычно получают кредит.

Исторически, действительно старые версии SQL Server pre 6.5 или 7.0 IIRC не поддерживали блокировку на уровне строк и могли блокироваться только на уровне таблицы или страницы. Часто кластерный индекс будет использоваться для обеспечения того, чтобы записи были разбросаны по физическому хранилищу таблицы, чтобы свести к минимуму конфликт на блокировки страниц. Тем не менее, SQL Server 6 получил поддержку несколько лет назад, поэтому приложения с этой проблемой будут ограничены редкими устаревшими системами.

sqlserver.bilee.com

Как выбрать кластерный индекс в SQL Server? MS SQL Server

Согласно The Queen of Indexing – Кимберли Трипп – то, что она ищет в кластерном индексе, в первую очередь:

  • уникальный
  • узкий
  • статический

И если вы также можете гарантировать:

  • Постоянно растущая модель

то вы довольно близки к тому, что у вас есть идеальный ключ кластеризации!

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

Все, что похоже на INT (например, INT IDENTITY) или, возможно, INT и DATETIME, являются идеальными кандидатами. По другим причинам GUID не являются хорошими кандидатами вообще, поэтому у вас может быть GUID как ваш ПК, но не кладите на него свою таблицу – он будет фрагментирован до неузнаваемости, и производительность пострадает.

Лучший кандидат для индекса CLUSTERED – это ключ, который вы чаще всего используете для записи своих записей.

Обычно это PRIMARY KEY , так как это то, что используется в поисках и / или отношения FOREIGN KEY .

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

Если вы создадите индекс Orders.CustomerID , произойдет следующее:

  1. CustomerID не уникален. Чтобы обеспечить уникальность, в каждую запись будет добавлен специальный скрытый 32-bit столбец, известный как uniquifier .

  2. Записи в таблице будут храниться в соответствии с этой парой столбцов (CustomerID, uniquifier) .

  3. Будет создан вторичный индекс для Order.ID с Order.ID (CustomerID, uniquifier) в качестве указателей записи.

  4. Запросы:

    SELECT * FROM Orders WHERE ID = 1234567

    придется выполнять внешнюю операцию Clustered Seek , поскольку не все столбцы хранятся в индексе ID . Чтобы получить все столбцы, запись должна сначала быть расположена в кластеризованной таблице.

Эта дополнительная операция требует IndexDepth поскольку многие страницы читаются как простой Clustered Seek , IndexDepth beign O(log(n)) общего количества записей в вашей таблице.

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

ROT: Кластер, на котором вы хотите показать коллекцию. Позициями в заказе на поставку является классический пример.

sqlserver.bilee.com

SQL Server – Разделенные таблицы или кластерный индекс? MS SQL Server

Предположим, у вас есть одна массивная таблица с тремя столбцами, как показано ниже:

[id] INT NOT NULL, [date] SMALLDATETIME NOT NULL, [sales] FLOAT NULL

Также предположим, что вы ограничены одним физическим диском и одной файловой группой (PRIMARY). Вы ожидаете, что эта таблица будет продавать 10 000 000+ идентификаторов за 100 лет (легко 1Б + записи).

Как и во многих сценариях хранения данных, данные, как правило, растут последовательно по дате (т. Е. Каждый раз, когда вы выполняете загрузку данных, вы будете вставлять новые даты и, возможно, обновлять некоторые более поздние даты данных). В аналитических целях данные часто запрашиваются и агрегируются для случайного набора из ~ 10000 идентификаторов, которые будут указаны посредством соединения с другой таблицей. Часто эти запросы не указывают диапазоны дат или указывают очень широкие диапазоны дат, что приводит меня к моему вопросу: как лучше всего индексировать / разделять эту таблицу?

Я подумал об этом некоторое время, но застрял в противоречивых решениях:

Вариант №1: поскольку данные будут загружаться последовательно по дате, определите кластеризованный индекс (и первичный ключ) как [дата], [id]. Также создайте функцию / схему разделения «скользящего окна» в дате, что позволяет быстро перемещать новые данные в / из таблицы. Потенциально создайте некластеризованный индекс для id, чтобы помочь с запросом.

Ожидаемый результат №1: эта настройка будет очень быстрой для целей загрузки данных, но не оптимальна, когда дело доходит до аналитических просмотров, как в худшем случае (без ограничений по датам, к неудачам с запросом id), 100% страницы данных могут быть прочитаны.

Вариант № 2: поскольку данные будут запрашиваться только для небольшого подмножества идентификаторов за раз, определите кластеризованный индекс (и первичный ключ) как [id], [date]. Не беспокойтесь, чтобы создать секционированную таблицу.

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

Вариант № 3: Кластерный (и первичный ключ) следующим образом: [id], [date]; «Скользящее окно», раздел / схема на дату.

Ожидаемый результат №3: Не уверен, чего ожидать. Учитывая, что первый столбец в кластерном индексе имеет значение [id] и, следовательно, (я понимаю), данные упорядочены по идентификатору, я бы ожидал хорошей производительности из своих аналитических запросов. Однако данные разделяются по дате, что противоречит определению кластерного индекса (но все же выровнено, поскольку дата является частью индекса). Я не нашел много документации, которая говорит об этом сценарии, и какие, если таковые имеются, преимущества в производительности, которые я могу получить от этого, что подводит меня к моему окончательному вопросу о бонусах:

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

Solutions Collecting From Web of "SQL Server – Разделенные таблицы или кластерный индекс?"

Эта таблица очень узкая. Если реальная таблица будет такой узкой, вы должны быть счастливы иметь сканирование таблицы вместо index-> ​​lookups.

Я бы сделал это:

CREATE TABLE Narrow ( [id] INT NOT NULL, [date] SMALLDATETIME NOT NULL, [sales] FLOAT NULL, PRIMARY KEY(id, date) --EDIT, just noticed your id is not unique. ) CREATE INDEX CoveringNarrow ON Narrow(date, id, sales)

Это обрабатывает точечные запросы с помощью запросов и широкодиапазонных запросов с ограниченным просмотром по критериям даты и критериям идентификатора. Нет никакого поиска записей из индекса. Да, я удвоил время записи (и используемое пространство), но это нормально, imo.

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

CREATE VIEW Narrow200801 AS SELECT * FROM Narrow WHERE '2008-01-01' <= [date] AND [date] < '2008-02-01' --There is some command that I don't have at my finger tips to make this a clustered view.

Кластеризованные представления могут использоваться в запросах по имени, или оптимизатор будет использовать кластерные представления, когда предложения FROM и WHERE являются подходящими. Например, этот запрос будет использовать кластерное представление. Обратите внимание, что базовая таблица упоминается в запросе.

SELECT SUM(sales) FROM Narrow WHERE '2008-01-01' <= [date] AND [date] < '2008-02-01'

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

Кластеризованный индекс даст вам преимущества производительности для запросов при локализации ввода-вывода. Дата представляет собой традиционную стратегию разделения, так как многие D / W-запросы смотрят на движения по дате.

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

Было бы необычно видеть много прироста производительности от кластерного индекса при разнообразной аналитической нагрузке. Оптимизатор запросов будет использовать метод под названием «Индекс пересечения», чтобы выбирать строки, даже не попав в таблицу фактов. См. Здесь для сообщения, которое я сделал по другому вопросу, который объясняет это более подробно некоторыми ссылками. Кластеризованный индекс может участвовать или не участвовать в пересечении индексов, поэтому вы можете обнаружить, что он довольно мало влияет на общую рабочую нагрузку на запрос.

Вы можете найти обстоятельства при загрузке, когда кластеризованные индексы дают вам некоторую выгоду, особенно если вы получили вычисления (такие как Earned Premium ), которые вычисляются в процессе ETL. В этом случае вы можете получить некоторые преимущества. Если у вас есть определенный запрос, который, как вы знаете, будет выполняться все время, это может иметь смысл использовать кластерные индексы для этого. Варианты № 2 и № 3 будут только значительно приносить вам пользу, если вы ожидаете, что этот тип запросов будет подавляющим большинством работы, выполняемой приложением.

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

Если вы используете разделы в операторах выбора, вы cn получаете некоторую скорость.

Если вы его не используете, используйте только «стандартные» варианты, тогда у вас нет никакой пользы.

По вашей первоначальной проблеме: я бы порекомендовал вам вариант №1 с некластеризованным индексом по включенному идентификатору.

Я бы сделал следующее:

  • Некластерный индекс на [Id]
  • Кластерный индекс в [Дата]
  • Преобразуйте тип данных [sales] в числовой, а не в float

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

Кластеризованный индекс в столбце даты не подходит, если у вас будут вставленные вставки, которые будут вставлены быстрее, чем разрешение datetime 3,33 мс. если вы это сделаете, вы получите 2 ключа с одинаковым значением, и ваш индекс должен будет получить еще один внутренний уникальный идентификатор, который увеличит его размер.

я бы пошел с # 2 ваших вариантов.

sqlserver.bilee.com