На какой столбец следует кластеризовать индекс?
В последнее время я занимался чтением индексов всех типов, и главный совет - поместить кластерный индекс в первичный ключ таблицы, но что, если первичный ключ на самом деле не используется в запросе (с помощью выбора или объединения) и просто ставится для чисто реляционных целей, поэтому в этом случае он не запрашивается. Пример, скажем, у меня есть 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? Основы вопроса в том, какой столбец должен иметь кластерный индекс, поскольку вам разрешен только один из них?
5 ответов
Индекс, кластеризованный или не заблокированный, может использоваться оптимизатором запросов тогда и только тогда, когда фильтруется крайний левый ключ индекса. Так что если вы определяете индекс по столбцам (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,
Так что в вашем примере, если вы сделаете индекс clustred на part_no как крайний левый ключ, то запрос ищет конкретный part_id не будет использовать индекс, и отдельный некластеризованный индекс должен существовать на part-id,
Теперь о том, какой из множества индексов должен быть кластерным. Если у вас есть несколько шаблонов запросов, которые имеют примерно одинаковую важность и частоту и противоречат друг другу с точки зрения необходимых ключей (например, частые запросы part_no или же part_id) тогда вы принимаете во внимание другие факторы:
- ширина: ключ кластеризованного индекса используется в качестве ключа поиска всеми другими некластеризованными индексами. Поэтому, если вы выберете широкий ключ (скажем, два столбца-идентификатора), вы сделаете все остальные индексы шире, тем самым занимая больше места, генерируя больше операций ввода-вывода и замедляя все. Таким образом, между одинаково хорошими ключами с точки зрения чтения выберите самый узкий как кластеризованный и сделайте более широкие некластеризованными.
- утверждение: если у вас есть определенные шаблоны вставки и удаления, попробуйте разделить их физически, чтобы они встречались в разных частях кластерного индекса. Например. если таблица действует как очередь со всеми вставками на одном логическом конце и удаляет все на другом логическом конце, попытайтесь расположить кластеризованный индекс так, чтобы физический порядок соответствовал этому логическому порядку (например, порядок очереди).
- разбиение на разделы: если таблица очень большая, и вы планируете развернуть разделение, то ключ разделения должен быть кластеризованным индексом. Типичным примером являются исторические данные, которые архивируются с использованием схемы секционирования скользящего окна. Даже если у сущностей есть логический первичный ключ, такой как "entity_id", привязанный индекс выполняется столбцом datetime, который также используется для функции разделения.
- стабильность: часто изменяемый ключ является плохим кандидатом на кластеризованный ключ, так как каждый обновляет значение кластеризованного ключа и заставляет все некластеризованные индексы обновлять ключ поиска, который они хранят. Обновление кластеризованного ключа также может переместить запись на другую страницу, что может привести к фрагментации кластеризованного индекса.
Примечание: не полностью использовать, так как иногда механизм будет выбирать некластеризованный индекс для сканирования вместо кластеризованного индекса просто потому, что он уже и поэтому имеет меньше страниц для сканирования. В моем примере, если у вас есть индекс на (A, B, C) и фильтр WHERE на B=@b и проекты запросов C индекс, скорее всего, будет использоваться, но не как поиск, а как сканирование, потому что оно все же быстрее, чем полное кластерное сканирование (меньше страниц).
Кимберли Трипп всегда является одним из лучших источников информации об индексации.
См. Ее сообщение в блоге " Постоянно увеличивающийся ключ кластеризации - снова дебаты по кластерному индексу!", В котором она довольно четко перечисляет и объясняет основные требования для хорошего ключа кластеризации - это должно быть:
- уникальный
- узкий
- статический
и лучше всего, если вы можете управлять:
- постоянно растет
Принимая все это во внимание, INT IDENTITY (или же BIGINT IDENTITY если вам действительно нужно более 2 миллиардов строк) в большинстве случаев это лучший выбор.
Одна вещь, которую многие люди не осознают (и поэтому не принимают во внимание при выборе), это то, что ключ кластеризации (все столбцы, составляющие кластеризованный индекс) будет добавлен к каждому индексу запись для каждого некластеризованного индекса в вашей таблице - таким образом, "узкое" требование становится очень важным!
Кроме того, поскольку ключ кластеризации используется для поиска по закладкам (поиск фактической строки данных, когда строка находится в некластеризованном индексе), требование "уникальности" также становится очень важным. На самом деле настолько важно, что если вы выберете (набор) столбцов, которые / не будут гарантированно уникальными, SQL Server добавит 4-байтовый uniquefier в каждую строку -> тем самым сделав каждый из ваших ключи кластерного индекса очень широкие; определенно НЕ хорошая вещь.
Марк
Кластерные индексы хороши, когда вы запрашиваете диапазоны данных. Например
SELECT * FROM theTable WHERE age BETWEEN 10 AND 20
Кластерный индекс размещает строки в определенном порядке на диске вашего компьютера. Поэтому ряды с возрастом = 10 будут рядом друг с другом, а после них будут строки с возрастом = 11 и т. Д.
Если у вас есть точный выбор, вот так:
SELECT * FROM theTable WHERE age = 20
некластеризованный индекс тоже хорош. Он не переставляет данные на диске вашего компьютера, но создает специальное дерево с указателями на нужные вам строки.
Так что это сильно зависит от типа запросов, которые вы выполняете.
Имейте в виду шаблоны использования; Если вы почти всегда запрашиваете БД для car_part_no, то, вероятно, было бы полезно кластеризовать ее в этом столбце.
Однако не забывайте о соединениях; Если вы чаще всего присоединяетесь к таблице, а объединение использует поле car_part_id, то у вас есть веская причина оставить кластер на car_part_id.
Следует помнить еще кое-что (в данном случае это не так, но обычно при рассмотрении кластерных индексов), что кластерный индекс будет отображаться неявно в любом другом индексе таблицы; Так, например, если вы хотите проиндексировать car_part_title, этот индекс также будет неявно включать car_part_id. Это может повлиять на то, охватывает ли индекс запрос или нет, а также на то, сколько дискового пространства займет индекс (что влияет на использование памяти и т. Д.).
Кластерный индекс должен идти по столбцу, который будет наиболее запрашиваемым. Это включает в себя объединения, так как объединение должно обращаться к таблице так же, как прямой запрос, и находить указанные строки.
Вы всегда можете перестроить свои индексы позже, если ваше приложение изменится, и вы обнаружите, что вам нужно оптимизировать таблицу с другой структурой индекса.
Некоторые дополнительные рекомендации по выбору кластеризованной таблицы можно найти в MSDN здесь: Рекомендации по проектированию кластерного индекса.