Кластерный индекс SQL Server - вопрос о порядке индекса
У меня такая таблица:
keyA keyB data
keyA и keyB вместе уникальны, являются первичным ключом моей таблицы и составляют кластерный индекс.
Существует 5 возможных значений keyB, но неограниченное количество возможных значений keyA ,. keyB обычно увеличивается.
Например, следующие данные можно упорядочить двумя способами в зависимости от того, какой ключевой столбец упорядочен первым:
keyA keyB data
A 1 X
B 1 X
A 3 X
B 3 X
A 5 X
B 5 X
A 7 X
B 7 X
или же
keyA keyB data
A 1 X
A 3 X
A 5 X
A 7 X
B 1 X
B 3 X
B 5 X
B 7 X
Нужно ли мне сообщать кластеризованному индексу, какой из ключевых столбцов имеет меньше возможных значений, чтобы он мог сначала упорядочить данные по этому значению? Или не имеет значения с точки зрения производительности, что заказывается первым?
Ответы 9
Лучшее, что вы можете сделать, - это попробовать оба решения и измерить время выполнения.
По моему опыту, настройка индекса - это почти точная наука.
Может быть, было бы лучше иметь keyB перед keyA в порядке столбца индекса
+1 за честность. Если вы точно не знаете, как (например,) SQL Server работает внутри, вы не можете быть уверены, как все работает на практике. Хотя теория прекрасна. Нет, правда;)
Я считаю, что SQL Server упорядочивает это именно так, как вы это говорите. Предполагается, что вы лучше всех знаете, как получить доступ к своему индексу.
В любом случае, я бы сказал, что это хорошая идея, где возможно указать именно то, что вы хотите, вместо того, чтобы надеяться, что база данных это выяснит.
Вы также можете попробовать оба способа, запустить несколько репрезентативных запросов, а затем сравнить сгенерированные планы выполнения, чтобы определить, какой из них лучше всего подходит для вас.
Проголосовали за это, но просто хочу отметить, что, хотя в этой ситуации хорошо указать, что вы хотите, часто вам следует позволить серверу решить, что лучше. Например, использование подсказок индекса в запросах, как правило, является плохой идеей, поскольку лучший план может меняться вместе с вашими данными.
Согласовано. Индексные подсказки - это крайнее средство злого перебора. Я имел в виду создание самого индекса в обоих направлениях, а затем тестирование репрезентативных запросов. (Во всяком случае, это то, что я делаю :))
Если вы создаете индекс (независимо от кластеризации или нет) с помощью (keyA, keyB), тогда значения будут упорядочены таким образом, например сначала keyA, затем keyB (это второй случай в вашем вопросе). Если вы хотите наоборот, вам нужно указать (keyB, keyA).
Это может иметь значение с точки зрения производительности, конечно, зависит от вашего запроса. Например, если у вас есть индекс (keyA, keyB), а запрос выглядит как WHERE keyB = ... (без упоминания keyA), то индекс использовать нельзя.
Вы указываете столбцы в том порядке, в котором вы обычно хотите, чтобы они были отсортированы в отчетах и запросах.
Однако я бы с осторожностью относился к созданию многоколоночного кластерного индекса. В зависимости от его ширины вы можете иметь огромное влияние на размер любых других создаваемых вами индексов, потому что все некластеризованные индексы содержат в себе значение кластеризованного индекса. Кроме того, строки должны быть переупорядочены, если значения часто меняются, и, по моему опыту, несуррогатные ключи, как правило, меняются чаще. Поэтому создание этого как кластеризованного вице-некластеризованного индекса может занять гораздо больше времени серверных ресурсов, если у вас есть значения, которые могут измениться. Я не говорю, что вам не следует этого делать, поскольку я не знаю, какой тип данных на самом деле содержат ваши столбцы (хотя я подозреваю, что они более сложные, чем A1, a2 и т. д.); Я говорю, что вам нужно подумать о последствиях этого. Вероятно, было бы неплохо внимательно прочитать BOL о кластеризованных вице-некластеризованных индексах, прежде чем делать это.
Как уже говорили другие, порядок основан на том, как вы указываете его в сценарии создания индекса (или ограничении PK). Однако о кластерных индексах следует помнить о многом.
Вы можете повысить общую производительность, используя свой кластерный индекс не на ПК, а на чем-то другом. Например, если вы пишете финансовую систему, и отчеты почти всегда основаны на дате и времени действия (все действия за последний год и т. д.), То лучше использовать кластерный индекс в этом столбце даты. Как сообщает HLGEM, на сортировку также может влиять ваш выбор кластерного индекса.
Кластерные индексы также могут влиять на вставки больше, чем другие индексы. Если у вас большой объем вставок и ваш кластерный индекс находится в чем-то вроде столбца IDENTITY, тогда могут возникнуть проблемы с конкуренцией для этой конкретной части диска, поскольку все новые строки вставляются в одно и то же место.
Для небольших справочных таблиц я всегда просто помещаю кластерный индекс на ПК. Однако для высокоэффективных таблиц рекомендуется потратить время на обдумывание (и тестирование) различных возможных кластерных индексов, прежде чем выбирать лучший.
Помните, что кластерный индекс - это физический порядок, в котором таблица хранится на диске.
Поэтому, если ваш кластерный индекс определен как ColA, запросы ColB будут выполняться быстрее, если они будут упорядочены в том же порядке, что и ваш кластеризованный индекс. Если SQL должен упорядочить B, A, для достижения правильного порядка потребуется сортировка после выполнения.
Я предлагаю добавить второй некластеризованный индекс на B, A. Также в зависимости от размера столбца данных ВКЛЮЧИТЬ (прочитать включенный столбец), чтобы предотвратить необходимость поиска ключей. Это, конечно, при условии, что эта таблица не сильно вставлена, поскольку вы всегда должны балансировать скорость запроса и скорость записи.
Реально ваш кластерный индекс должен представлять порядок, в котором наиболее вероятно будет доступ к данным, а также поддерживать тонкий баланс затрат ввода-вывода вставки \ обновления. Если ваш кластерный индекс таков, что вы постоянно вставляете его в середину страниц, вы можете потерять там производительность.
Как говорили другие, без знания длины таблицы, размеров столбцов и т. д. Нет правильного ответа. Метод проб и ошибок с большой дозой тестирования - ваш лучший выбор.
Вы должны сначала заказать составной кластерный индекс с наиболее избирательным столбцом. Это означает столбец с наиболее различающимися значениями по сравнению с общим количеством строк.
«Индексы B * TREE повышают производительность запросов, которые выбирают небольшой процент строк из таблицы». http://www.akadia.com/services/ora_index_selectivity.html?
Эта статья предназначена для Oracle, но все еще актуальна.
Кроме того, если у вас есть запрос, который выполняется постоянно и возвращает несколько полей, вы можете рассмотреть возможность создания составного индекса, содержащего все поля - ему не нужно будет обращаться к базовой таблице, а вместо этого он будет извлекать данные из индекса.
Важно запомнить комментарий ligget78 об обязательном упоминании первого столбца в составном индексе.
Не могли бы вы еще немного уточнить «самую селективную колонку»? По какой-то причине «Это означает столбец с наиболее различающимися значениями по сравнению с общим количеством строк». кажется немного запутанным. Вы говорите, что ответ в этом примере - поместить KeyA первым в кластеризованном индексе? (Второй пример?)
-1: вы не отвечаете на актуальный вопрос. Вы упомянули несколько вещей, которые относятся к производительности в целом, но здесь они не имеют отношения к делу. Вы предоставляете нулевые аргументы для первого абзаца, причем мог является допустимым ответом, но в его нынешнем виде не доказано. Статья, на которую вы ссылаетесь, тоже не очень актуальна.
На всякий случай это неочевидно: порядок сортировки вашего показатель ничего не говорит о порядке сортировки приводит к запросу.
В своих запросах вы все равно должны добавить
ORDER BY KeyA, KeyB
или же
ORDER BY KeyB, KeyA
Оптимизатор может быть рад найти данные, уже физически упорядоченные в индексе, и сэкономить время, но каждый запрос, который должен доставлять данные в определенном порядке, должен иметь в конце предложение ORDER BY. Без упорядочивания по, SQL Server не дает никаких обещаний относительно порядка набора записей или даже того, что он будет возвращаться в том же порядке от запроса к запросу.
Да, вы должны предложить, обычно механизм запросов пытается найти лучший план выполнения и индекс для использования, однако иногда лучше заставить механизм запросов использовать конкретный индекс. Есть и другие соображения при планировании индекса, а также при использовании индекса в вашем запросе. например, порядок столбцов в индексе, порядок столбцов в предложении where. вы можете сослаться на следующую ссылку, чтобы узнать о:
http://ashishkhandelwal.arkutil.com/sql-server/quick-and-short-database-indexes/
- Рекомендации по использованию индексов
- Как получить лучшие показатели формы
- Рекомендации по кластеризованному индексу
- Рекомендации по некластеризованным индексам
Я уверен, что это поможет вам при планировании index.
Фактически он основан на конкретных научных идеях. Немного узнав о том, как работают индексы в виде b-дерева, вы станете более информированными и потребуют меньше работы наугад.