Кластерный индекс на столбце внешнего ключа увеличивают производительность соединения по сравнению с некластеризованным?
Во многих местах рекомендуется, чтобы кластерные индексы были лучше использованы при использовании для выбора диапазона строк с помощью оператора BETWEEN. Когда я выбираю присоединение полем внешнего ключа таким способом, которым используется этот кластерный индекс, я предполагаю, та кластеризация должна помочь также, потому что диапазон строк выбирается даже при том, что у них всех есть то же кластеризованное значение ключа, и МЕЖДУ не используется.
Полагание, что я забочусь только о том одном выборе с помощью соединения и ничего иного, является мной неправильно с моим предположением?
5 ответов
Обсуждение этого типа проблемы в абсолютном не очень полезно.
Это всегда индивидуальная ситуация!
По сути, доступ посредством кластерного индекса экономит одно косвенное обращение , точка.
Предполагая, что ключ, используемый в JOIN, является ключом кластеризованного индекса, за одно чтение [будь то поиск по индексу, сканирование или частичное сканирование, не имеет значения], вы получите всю строку (запись) .
Одна из проблем с кластерными индексами заключается в том, что вы получаете только один для каждой таблицы. Поэтому использовать его нужно с умом. Действительно, в некоторых случаях даже разумнее вообще не использовать какой-либо кластерный индекс из-за накладных расходов INSERT и фрагментации (в зависимости от ключа и порядка новых ключей и т. Д.)
Иногда можно получить эквивалентные преимущества кластерного индекса , с покрывающим индексом , то есть индексом с желаемой последовательностью ключей (ей), за которой следуют интересующие нас значения столбцов. Так же, как кластеризованный индекс, покрывающий индекс не требует косвенного обращения к нижележащая таблица. Действительно, индекс покрытия может быть немного более эффективным, чем кластерный индекс, потому что он меньше.
Однако, как и в случае с кластеризованными индексами, помимо накладных расходов на хранилище, существует снижение производительности, связанное с любым дополнительным индексом во время запросов INSERT (и DELETE или UPDATE) .
И да, как указано в других ответах, «внешний ключ» ключа, используемого для кластеризованного индекса, абсолютно не влияет на производительность индекса. FK - это ограничения, направленные на облегчение поддержания целостности базы данных, но базовые поля (столбцы) в остальном такие же, как и любые другие поля в таблице.
Чтобы принимать мудрые решения о структуре индекса, нужно
- понимать, как работают различные типы индексов (и куча)
(и, кстати, это несколько различается между реализациями SQL) - , чтобы иметь хорошее представление о статистическом профиле имеющихся баз данных:
какие большие таблицы, какие отношения, какая средняя / максимальная мощность отношения, какая типичная скорость роста базы данных и т. д. - , чтобы иметь хорошее представление о том, как базы данных будут (будут) использоваться / запрашиваться
Тогда и только тогда можно сделать обоснованные предположения о интерес [или его отсутствие] к заданному кластерному индексу.
Я бы спросил еще кое-что: было бы разумно поместить мой кластерный индекс в столбец внешнего ключа только для ускорения одного JOIN? Наверное, это помогает, но ... по цене!
Кластерный индекс ускоряет работу таблицы при каждой операции. ДА! Оно делает. См. Превосходную Ким Триппа. Продолжение дебатов по кластерному индексу для получения справочной информации. Она также упоминает свои основные критерии для кластерного индекса:
- узкий
- статический (никогда не изменяется)
- уникальный
- , если когда-либо возможно: когда-либо возрастающий
INT IDENTITY полностью соответствует этому, а GUID - нет. См. GUID в качестве первичного ключа для получения более подробной информации.
Почему узкий? Потому что ключ кластеризации добавляется на каждую страницу индекса каждого некластеризованного индекса в той же таблице (для того, чтобы иметь возможность фактически искать строку данных, если это необходимо) . Вы не хотите иметь VARCHAR (200) в вашем ключе кластеризации ....
Почему уникальный ?? См. Выше - ключ кластеризации - это элемент и механизм, которые SQL Server использует для однозначного поиска строки данных . Он должен быть уникальным. Если вы выберете неуникальный ключ кластеризации, SQL Server сам добавит к вашим ключам 4-байтовый уникальный определитель. Остерегайтесь этого!
Таковы мои критерии - поместите ключ кластеризации в узкий, стабильный, уникальный, надеюсь, постоянно увеличивающийся столбец.Если ваш столбец внешнего ключа соответствует им - отлично!
Однако я бы не ни при каких обстоятельствах не помещал свой ключ кластеризации на широкий или даже составной внешний ключ. Помните: значения ключа кластеризации добавляются к каждой записи некластеризованного индекса в этой таблице! Если у вас есть 10 некластеризованных индексов, 100 000 строк в вашей таблице - это один миллион записей. Имеет огромное значение, является ли это 4-байтовым целым числом или 200-байтовым VARCHAR - HUGE. И не только на диске, но и в памяти сервера. Очень хорошо подумайте о том, что сделать кластерным индексом!
SQL Server может нуждаться в добавлении уникального средства, что еще больше усугубляет ситуацию. Если значения когда-либо изменятся, SQL Server придется проводить много операций по учету и обновлению повсюду.
Вкратце:
- Добавление индекса к вашим внешним ключам - определенно отличная идея - делайте это постоянно!
- Я был бы очень осторожен при создании кластерного индекса. Прежде всего, вы получаете только один кластерный индекс, так какое отношение FK вы выберете? И не помещайте ключ кластеризации в широкий и постоянно меняющийся столбец
Повышение производительности обычно достигается при последовательном выборе данных в кластере. Кроме того, это полностью зависит от размера таблицы (данных) и условий в вашем операторе between.
Индекс на колонке FK поможет JOIN, потому что сам индекс упорядочен: кластеризованный означает, что данные на диске (лист) упорядочены, а не в B-дереве.
Если вы замените его на покрывающий индекс, то кластеризованный и некластеризованный не имеют значения. Важно иметь полезный индекс.
Это зависит от реализации базы данных.
Для SQL Server кластерный индекс - это структура данных, в которой данные хранятся в виде страниц, а есть B-деревья, которые хранятся как отдельная структура данных. Причина, по которой вы получаете высокую производительность, заключается в том, что вы можете быстро добраться до начала цепочки, а диапазоны представляют собой простой связанный список.
Некластеризованные индексы - это структура данных, которая содержит указатели на фактические записи и, как таковые, различные проблемы.
См. Документацию относительно структур кластерного индекса .
Индекс не поможет в отношении отношения внешнего ключа, но он поможет благодаря концепции «покрытого» индекса. Если ваше предложение WHERE содержит ограничение на основе index. он сможет быстрее сгенерировать возвращаемый набор данных. Вот откуда берется производительность.