Кластерный индекс - многослойный по сравнению с индексом единственной части и эффектами вставляет/удаляет
Этот вопрос о том, что происходит с реорганизацией данных в кластерном индексе, когда вставка сделана. Я предполагаю, что должно быть более дорого сделать, вставляет на таблице, которая имеет кластерный индекс, чем тот, который делает, не потому что реорганизация данных в кластерном индексе включает изменение физического расположения данных по диску. Я не уверен, как формулировать мой вопрос кроме через пример, с которым я столкнулся на работе.
Предположите, что существует таблица (Спам) и существует два запроса, которые сделаны на таблице, первые поиски запроса по имени и вторые поиски запроса по имени и Что-то. Поскольку я работаю над базой данных, я обнаружил, что таблица была составлена с двумя индексами, один для поддержки каждого запроса, как так:
--drop table Junk1
CREATE TABLE Junk1
(
Name char(5),
Something char(5),
WhoCares int
)
CREATE CLUSTERED INDEX IX_Name ON Junk1
(
Name
)
CREATE NONCLUSTERED INDEX IX_Name_Something ON Junk1
(
Name, Something
)
Теперь, когда я посмотрел на два индекса, кажется, что IX_Name избыточен, так как IX_Name_Something может использоваться любым запросом, который требует искать по имени. Таким образом, я устранил бы IX_Name и сделал бы IX_Name_Something кластерным индексом вместо этого:
--drop table Junk2
CREATE TABLE Junk2
(
Name char(5),
Something char(5),
WhoCares int
)
CREATE CLUSTERED INDEX IX_Name_Something ON Junk2
(
Name, Something
)
Кто-то предложил, чтобы первая схема индексации была сохранена, так как она привела бы к более эффективному, вставляет/удаляет (предположите, что нет никакой потребности волноваться об обновлениях для Имени и Чего-то). Это имело бы смысл? Я думаю, что второй метод индексирования был бы лучше, так как это означает тот, который должно сохраняться меньше индекса.
Я ценил бы любое понимание этого определенного примера или направления меня к большему количеству информации об обслуживании кластерных индексов.
3 ответа
Да, вставка в середину существующей таблицы (или ее страницы) может быть дорогостоящей, если у вас неоптимальный кластерный индекс. В худшем случае - разделение страницы: половину строк на странице придется переместить в другое место, а индексы (включая некластеризованные индексы в этой таблице) необходимо обновить.
Вы можете решить эту проблему, используя правильный кластерный индекс - в идеале он должен быть:
- узким (только одно поле, как можно меньше)
- статическим (никогда не изменяется)
- уникальным (чтобы SQL Server не нуждается в добавлении 4-байтовых идентификаторов уникальности в ваши строки)
- постоянно увеличивающийся (например, INT IDENTITY)
Вам нужен узкий ключ (в идеале один INT), поскольку каждая запись в каждом и каждый некластеризованный индекс также будет содержать ключ (и) кластеризации - вы не хотите помещать много столбцов в свой ключ кластеризации и не хотите помещать туда такие вещи, как VARCHAR (200)!
При постоянно увеличивающемся кластерном индексе вы никогда не увидите случая разделения страницы. Единственная фрагментация, с которой вы можете столкнуться, связана с удалениями (проблема "швейцарского сыра").
Ознакомьтесь с сообщениями в блоге Кимберли Трипп об индексировании - в первую очередь:
- GUID в качестве ПЕРВИЧНЫХ КЛЮЧЕЙ и / или ключа кластеризации.
- Дебаты по кластеризованному индексу продолжаются ... - это действительно показывает, что хороший кластерный индекс ускорит все операции, включая вставку, удаление и т. д., по сравнению с кучей без кластерного индекса!
- Постоянно растущий ключ кластеризации - дебаты по кластеризованному индексу .......... снова!
Предположим, существует таблица (Мусор) и есть два запроса, которые выполняются по таблица, первый запрос выполняет поиск по Имя и второй запрос ищут по Имя и что-то.Как я работаю над в базе данных я обнаружил, что таблица была создана с двумя индексы, по одному для поддержки каждого запроса, вот так:
Это определенно не обязательно - если у вас есть один индекс для (Name, Something) , этот индекс также можно и с таким же успехом использовать, если вы выполняете поиск и ограничиваете только WHERE Name = abc - наличие отдельного индекса только со столбцом Name совершенно не требуется и только тратит пространство (и требует времени, чтобы поддерживать его в актуальном состоянии).
По сути, вам нужен только один индекс для (Name, Something) , и я соглашусь с вами - если у вас нет других индексов в этой таблице, вы сможете сделать это кластерный ключ. Поскольку этот ключ не будет постоянно увеличиваться и, возможно, тоже может измениться (верно?), Это может быть не такой уж хорошей идеей.
Другой вариант - ввести суррогатный ID INT IDENTITY и кластеризовать его - с двумя преимуществами:
- это должен быть хороший кластерный ключ, включая постоянно увеличивающийся -> you ' у вас никогда не будет проблем с разделением страниц и производительностью операций INSERT
- , вы по-прежнему получаете все преимущества наличия ключа кластеризации (см. сообщения в блоге Кима Триппса - кластеризованные таблицы почти всегда предпочтительнее кучи)
Кто-то предложил сохранить первую схему индексирования, поскольку она приведет к более эффективным вставкам/удалениям
Это ошибочное утверждение. Упорядоченные данные - это упорядоченные данные, и будут выполняться те же операции ввода-вывода.
SET STATISTICS IO ON
-- your insert statement here
Вы можете создать кластерный индекс только для одного столбца, а не для двух или более, поэтому выберите столбец, по которому ваше приложение будет в основном запрашивать, например запросы с подстановочными знаками для полных имен клиентов и т. Д. (См. Обсуждение )