Кластерный индекс по внешнему ключу или первичному ключу?
У меня есть стол Item с первичным ключом autoinc int Id и внешний ключ UserId,
И у меня есть стол User с первичным ключом autoinc int Id,
По умолчанию это индекс для Item.Id становится кластеризованным.
Я буду в основном запрашивать элементы на user-id поэтому мой вопрос: было бы лучше установить UserId вместо этого будет кластеризован индекс внешнего ключа?
5 ответов
Наличие кластеризованного индекса в поле идентификатора имеет то преимущество, что записи будут храниться в порядке их создания. Новые записи добавляются в конце таблицы.
Если вы используете внешний ключ в качестве кластерного индекса, записи будут храниться в этом порядке. Когда вы создаете новые записи, данные будут фрагментированы, поскольку записи будут вставлены посередине, что может снизить производительность.
Если вам нужен индекс для внешнего ключа, просто добавьте некластеризованный индекс для него.
Ответ зависит только от сценария использования. Например, Гуффа сказал, что данные будут фрагментированы. Это неверно. Если ваши запросы зависят в основном от UserId, то данные, кластеризованные по ItemId, фрагментированы для вас, потому что элементы для одного и того же пользователя могут располагаться на большом количестве страниц.
Конечно, по сравнению с последовательным ItemId (если он является последовательным в вашей схеме), использование UserId в качестве кластеризованного ключа может привести к расщеплению страницы при вставке. Это две дополнительные записи страницы по максимуму. Но когда вы выбираете какого-либо пользователя, его элементы могут быть фрагментированы на десятках страниц (зависит от элементов на пользователя, размера элемента, стратегии вставки и т. Д.) И, следовательно, могут быть прочитаны многие страницы. Если у вас есть множество таких выборок на одну вставку (очень часто используются сценарии web / olap), вы можете столкнуться с сотнями операций ввода-вывода по сравнению с немногими, потраченными на разбиение страницы. Именно для этого был создан индекс кластеризации, а не только для кластеризации по суррогатным идентификаторам.
Таким образом, нет четкого ответа, является ли кластерный UserId в вашем случае хорошим или плохим, потому что это сильно зависит от контекста. Каково соотношение между операциями выбора / вставки? Насколько фрагментированы идентификаторы пользователей, если они сгруппированы по itemid? Сколько дополнительных указаний на столе, потому что есть ошибка (ниже) в сервере SQL.
Как вы, возможно, знаете, кластеризованный индекс требует уникальных значений. Это не большая проблема, потому что вы можете создать индекс для пары (UserId, ItemId). Кластерный индекс сам не хранится на диске, поэтому независимо от количества полей. Но некластеризованные индексы хранят значения кластеризованных индексов в своих листьях. Таким образом, если у вас есть кластеризованный индекс для UserId+ItemId (давайте представим, что их тип - [int], а размер - 8 байт) и некластеризованный индекс для ItemId, то этот индекс будет иметь двойной размер (8 байт на лист b-дерева) по сравнению только с ItemId как кластерный индекс (4 байта на лист).
В общем случае вы хотите кластеризовать наиболее часто используемый индекс. Но вам вовсе не обязательно иметь индекс кластеризации. Вы (или ваши администраторы баз данных) должны оценить вещи и взвесить преимущества и недостатки, чтобы выбрать наиболее подходящую стратегию индексации.
Если вы кластер на монотонном счетчике, как identity В столбце все новые строки будут вставлены в конец таблицы: это означает, что создается "горячая точка", которая может вызвать конфликт блокировки при вставках, поскольку каждый SPID, выполняющий вставку, попадает на одну и ту же страницу данных.
Таблицы без индекса кластеризации имеют свои страницы данных, организованные в виде кучи, в основном просто связанный список страниц данных.
Индексы SQL Server являются B-деревьями. Для некластеризованных индексов листовые узлы B-дерева являются указателями на соответствующую страницу данных. Это означает, что если индекс используется и не охватывает столбцы запроса, необходимо выполнить дополнительный просмотр в сторону, чтобы получить страницу данных. Это означает дополнительный ввод / вывод и пейджинг.
Кластерные индексы различны: их конечные узлы являются самими страницами данных, что означает, что куча по существу исчезает: сканирование таблицы означает обход B-дерева индекса кластеризации. Преимущество состоит в том, что как только вы нашли то, что вам нужно в кластеризованном индексе, у вас уже есть нужная страница данных, что позволяет избежать дополнительных операций ввода-вывода, которые могут потребоваться при поиске по некластеризованному индексу. Недостаток, конечно, заключается в том, что кластеризованный индекс больше, поскольку он несет с собой всю таблицу, поэтому обход кластеризованного индекса обходится дороже.
Кластерный индекс создается по первичному ключу, поэтому вы можете оставить его как кластеризованный, а затем создать некластеризованный индекс по идентификатору пользователя элемента. Это все равно будет очень быстро как пользователь. Идентификатор столбца будет кластеризованным индексом.
Возможно.
Это item.user-id столбец уникальный столбец в вашей таблице элементов? Если нет, вам нужно сделать этот кластеризованный первичный ключ, добавив в него второй (возможно, более) столбец, чтобы сделать его уникальным / возможно, это добавит дополнительные издержки, которые вы не ожидали.
Есть ли какие-либо отношения с item.id столбец? Если это так, это может быть важно для производительности вашего приложения, поэтому следует принять это во внимание.
Как часто item.user-id значение может измениться? Если нет, то это имеет значение в его пользу; чем чаще оно обновляется, тем хуже, поскольку это приводит к фрагментации.
Моя рекомендация будет заключаться в том, чтобы создать приложение с обычным item.id как кластеризованный ключ, позже, когда вы получите некоторые данные, попробуйте (в тестовой системе, использующей копию ваших производственных данных) переключить кластеризованный индекс и протестировать его влияние; Таким образом, вы можете легко увидеть реальные результаты, а не пытаться угадать множество возможностей. Это позволяет избежать преждевременной оптимизации / гарантирует, что вы сделаете правильный выбор.