В последнее время я читал об индексах всех типов, и главный совет - поместить кластеризованный индекс в первичный ключ таблицы, но что, если первичный ключ на самом деле не используется в запросе (через выбор или соединение? ) и помещается исключительно для реляционных целей, поэтому в этом случае он не запрашивается. Например, скажем, у меня есть таблица car_parts, содержащая 3 столбца: car_part_id, car_part_no и car_part_title. car_part_id - это уникальный столбец идентификации первичного ключа. В этом случае car_part_no также уникален и, скорее всего, car_part_title. car_part_no - это то, к чему чаще всего обращаются с запросами, так что не имеет смысла помещать кластеризованный индекс в этот столбец вместо car_part_id? Суть вопроса в том, какой столбец должен иметь кластеризованный индекс, поскольку вам разрешен только один из них?
Вопрос
В какой столбец следует поместить кластеризованный индекс?
- Очень хорошо, что вы задаете себе такие вопросы. Правильный выбор сейчас оказывает огромное влияние на вашу производительность и масштабируемость в будущем. Remus Rusanu 17.09.2009
- Большинство первичных ключей, предназначенных исключительно для реляционных целей, отражают ошибочное представление о реляционной модели. Если внешние ключи, ссылающиеся на первичный ключ, отсутствуют, велики шансы, что реляционная модель выбрала бы другой первичный ключ. Walter Mitty 17.09.2009
Ответы
Ответ 1 · рейтинг 9 · Remus Rusanu
Индекс, кластеризованный или некластеризованный, может использоваться оптимизатором запросов тогда и только тогда, когда фильтруется крайний левый ключ в индексе. Таким образом, если вы определяете индекс для столбцов (A, B, C), условие WHERE для B=@b, C=@c или B=@b AND C=@c не будет полностью использовать индекс (см. примечание). Это относится и к условиям присоединения. Любой фильтр WHERE, включающий A, будет учитывать индекс: A=@a или A=@a AND B=@b, или A=@a AND C=@c, или A=@a AND B=@b AND C=@c.
Таким образом, в вашем примере, если вы сделаете кластерный индекс для part_no крайним левым ключом, тогда запрос, ищущий конкретный part_id, не будет использовать этот индекс, а для part-id должен существовать отдельный некластеризованный индекс.
Теперь о вопросе, какой из множества индексов должен быть кластеризованным. Если у вас есть несколько шаблонов запросов, которые имеют примерно одинаковую важность и частоту и противоречат друг другу с точки зрения необходимых ключей (например, частые запросы от либо part_no или part_id), вы принимаете во внимание другие факторы:
- width: ключ кластеризованного индекса используется в качестве ключа поиска всеми другими некластеризованными индексами. Поэтому, если вы выберете широкий ключ (скажем, два столбца с уникальными идентификаторами), вы сделаете все остальные индексы шире, тем самым занимая больше места, генерируя больше операций ввода-вывода и замедляя все. Итак, между одинаково хорошими ключами с точки зрения чтения выберите самый узкий как сгруппированный, а более широкие сделайте не сгруппированными.
- конфликт: если у вас есть определенные шаблоны вставки и удаления, попытайтесь разделить их физически, чтобы они происходили в разных частях кластеризованного индекса. Например. если таблица действует как очередь со всеми вставками на одном логическом конце и всеми удалениями на другом логическом конце, попробуйте расположить кластеризованный индекс так, чтобы физический порядок соответствовал этому логическому порядку (например, порядок постановки в очередь).
- разделение: если таблица очень большая и вы планируете развернуть разделение, ключом разделения должен быть кластеризованный индекс. Типичным примером являются исторические данные, которые архивируются с использованием схемы секционирования скользящего окна. Несмотря на то, что сущности имеют логический первичный ключ, такой как 'entity_id', кластерный индекс выполняется столбцом даты и времени, который также используется для функции секционирования.
- стабильность: ключ, который часто меняется, является плохим кандидатом для кластерного ключа, поскольку каждое обновление значения кластеризованного ключа заставляет все некластеризованные индексы обновлять ключ поиска, который они хранят. . Поскольку обновление кластеризованного ключа также, вероятно, приведет к перемещению записи на другую страницу, это может вызвать фрагментацию кластеризованного индекса.
Примечание: не полностью использовать, так как иногда механизм выбирает некластеризованный индекс для сканирования вместо кластеризованного индекса просто потому, что он уже и, следовательно, имеет меньше страниц для сканирования. В моем примере, если у вас есть индекс на (A, B, C) и фильтр WHERE на B=@b, а запрос проецирует C, индекс, скорее всего, будет использоваться, но не как поиск, а как сканирование, потому что это все же быстрее, чем полное кластерное сканирование (меньше страниц).
Ответ 2 · рейтинг 5 · marc_s
Кимберли Трипп всегда был одним из лучших источников информации об индексации.
См. ее сообщение в блоге "Постоянно увеличивающийся ключ кластеризации - дебаты о кластерном индексе - снова!", в котором она довольно четко перечисляет и объясняет основные требования к хорошему ключу кластеризации - он должен быть:
- Уникальный
- Узкий
- Статический
и лучше всего, если вы можете управлять:
- постоянно растущий
Принимая все это во внимание, INT IDENTITY (или BIGINT IDENTITY, если вам действительно нужно более 2 миллиардов строк) оказывается лучшим выбором в подавляющем большинстве случаев.
Одна вещь, которую многие люди не осознают (и, следовательно, не принимают во внимание при выборе), заключается в том, что ключ кластеризации (все столбцы, составляющие кластеризованный индекс) будет добавлен к каждому индексу. запись для каждого некластеризованного индекса в вашей таблице - таким образом, "узкое" требование становится особенно важным!
Кроме того, поскольку ключ кластеризации используется для поиска закладок (поиск фактической строки данных, когда строка найдена в некластеризованном индексе), требование "уникальности" также становится очень важным. На самом деле настолько важно, что если вы выберете (набор) столбцов, уникальность которых не гарантируется, SQL Server добавит 4-байтовый уникализатор к каждой строке --> таким образом делая каждый из ваших очень широкие ключи кластеризованного индекса; определенно НЕ хорошо.
Марк
Ответ 3 · рейтинг 4 · Lukasz Lysik
Кластерные индексы хороши, когда вы запрашиваете диапазоны данных. Например
SELECT * FROM theTable WHERE age BETWEEN 10 AND 20
Кластерный индекс упорядочивает строки в определенном порядке на диске вашего компьютера. Поэтому строки с возрастом = 10 будут рядом друг с другом, а после них будут строки с возрастом = 11 и т. д.
Если у вас есть точный выбор, например:
SELECT * FROM theTable WHERE age = 20
некластеризованный индекс тоже хорош. Он не переставляет данные на диске вашего компьютера, а строит специальное дерево с указателями на нужные вам строки.
Так что это сильно зависит от типа запросов, которые вы выполняете.
Ответ 4 · рейтинг 2 · Chris Shaffer
Помните о шаблонах использования; Если вы почти всегда запрашиваете БД по car_part_no, то, вероятно, было бы полезно сгруппировать ее по этому столбцу.
Однако не забывайте о соединениях; Если вы чаще всего присоединяетесь к таблице и при объединении используется поле car_part_id, то у вас есть веская причина сохранить кластер на car_part_id.
Еще кое-что, о чем следует помнить (в меньшей степени в этом случае, но в целом при рассмотрении кластеризованных индексов), заключается в том, что кластеризованный индекс будет неявно появляться в каждом другом индексе таблицы; Так, например, если вы проиндексируете car_part_title, этот индекс также будет неявно включать car_part_id. Это может повлиять на то, покрывает ли индекс запрос, а также на то, сколько места на диске займет индекс (что влияет на использование памяти и т. д.).
Ответ 5 · рейтинг 1 · womp
Кластеризованный индекс должен идти по столбцу, который будет наиболее запрашиваемым. Сюда входят соединения, так как соединение должно обращаться к таблице так же, как прямой запрос, и находить указанные строки.
Вы всегда можете перестроить свои индексы позже, если ваше приложение изменится, и вы обнаружите, что вам нужно оптимизировать таблицу с другой структурой индекса.
Некоторые дополнительные рекомендации по выбору элементов кластеризации таблицы можно найти в MSDN здесь: Рекомендации по проектированию кластеризованного индекса.
Перевод вопроса и ответов сообщества Stack Overflow, лицензия CC BY-SA. Оригинал на Stack Overflow.