Я работаю над базой данных, которая обычно использует GUID в качестве первичных ключей.
По умолчанию SQL Server помещает кластеризованный индекс в столбцы первичного ключа. Я понимаю, что это глупая идея для столбцов GUID и что некластеризованные индексы лучше.
Как вы думаете - следует ли мне избавиться от всех кластерных индексов и заменить их некластеризованными?
Почему бы настройщику производительности SQL не предложить это в качестве рекомендации?
Основная причина использования кластеризованного индекса - это когда вы часто хотите получить строки для диапазона значений для данного столбца. Поскольку данные физически упорядочены в таком порядке, строки могут быть извлечены очень эффективно.
Что-то вроде GUID, хотя и отлично подходит для первичного ключа, может положительно сказаться на производительности, так как будут дополнительные затраты на вставки и не будет ощутимой выгоды при выборе.
Так что да, не кластеризуйте индекс по GUID.
Что касается того, почему это не предлагается в качестве рекомендации, я бы предположил, что тюнер знает об этом факте.
UNIQUEIDENTIFIER только в том случае, если вам нужно, например для тиражирования.
- person Jonathan Gilbert; 28.06.2016
Вы почти наверняка захотите установить кластерный индекс для каждой таблицы в своей базе данных. Если таблица не имеет кластеризованного индекса, это то, что называется «кучей», и производительность большинства типов общих запросов составляет меньше для кучи, чем для таблицы кластеризованного индекса.
Для каких полей следует установить кластерный индекс, зависит от самой таблицы и ожидаемых шаблонов использования запросов к таблице. Почти в каждом случае вы, вероятно, хотите, чтобы кластеризованный индекс находился в столбце или комбинации столбцов, которые являются уникальными, то есть (альтернативным ключом), потому что в противном случае SQL добавит уникальное значение в конец любого поля, которые вы выбираете в любом случае. Если в вашей таблице есть столбец или столбцы, которые будут часто использоваться запросами для выбора или фильтрации нескольких записей (например, если ваша таблица содержит транзакции продаж, а ваше приложение часто запрашивает транзакции продаж по идентификатору продукта или даже лучше, таблица сведений о счете, где почти в каждом случае вы будете извлекать все подробные записи для конкретного счета, или таблицу счетов, где вы часто получаете все счета для конкретного клиента ... Это верно независимо от того, будете ли вы выбраны крупным количество записей по одному значению или по диапазону значений)
Эти столбцы являются кандидатами для кластерного индекса. Порядок столбцов в кластеризованном индексе имеет решающее значение. Первый столбец, определенный в индексе, должен быть столбцом, который будет выбран или отфильтрован первым в ожидаемых запросах.
Причина всего этого основана на понимании внутренней структуры индекса базы данных. Эти индексы называются индексами сбалансированного дерева (B-Tree). они похожи на двоичное дерево, за исключением того, что каждый узел в дереве может иметь произвольное количество записей (и дочерних узлов) вместо двух. Что отличает кластерный индекс, так это то, что конечные узлы в кластеризованном индексе являются фактическими страницами данных физического диска самой таблицы. тогда как конечные узлы некластеризованного индекса просто «указывают» на страницы данных таблиц.
Следовательно, когда таблица имеет кластеризованный индекс, страницы данных таблицы являются конечным уровнем этого индекса, и каждая из них имеет указатель на предыдущую страницу и следующую страницу в порядке индекса (они образуют двусвязный список) .
Итак, если ваш запрос запрашивает диапазон строк, который находится в том же порядке, что и кластеризованный индекс ... процессору нужно только пройти индекс один (или, возможно, дважды), чтобы найти начальную страницу данных, а затем следовать указатели связанного списка для перехода на следующую страницу и следующую страницу, пока не будут прочитаны все необходимые страницы данных.
Для некластеризованного индекса он должен проходить индекс один раз для каждой строки, которую он извлекает ...
ПРИМЕЧАНИЕ: РЕДАКТИРОВАТЬ
Чтобы решить проблему с последовательностью столбцов Guid Key, имейте в виду, что SQL2k5 имеет NEWSEQUENTIALID (), который фактически генерирует Guid "старым" последовательным способом.
или вы можете исследовать алгоритм руководства Jimmy Nielsens COMB, который реализован в коде на стороне клиента:
Проблема с кластеризованными индексами в поле GUID заключается в том, что идентификаторы GUID являются случайными, поэтому при вставке новой записи значительную часть данных на диске необходимо переместить, чтобы вставить записи в середину таблицы.
Однако в кластерных индексах, основанных на целых числах, целые числа обычно являются последовательными (как в спецификации IDENTITY), поэтому они просто добавляются в конец, и нет необходимости перемещать данные.
С другой стороны, кластерные индексы не всегда плохо работают с идентификаторами GUID ... все зависит от потребностей вашего приложения. Если вам нужно иметь возможность быстро SELECT записи, тогда используйте кластерный индекс ... скорость INSERT пострадает, но скорость SELECT будет улучшена.
Хотя кластеризация по идентификатору GUID обычно является плохой идеей, имейте в виду, что идентификаторы GUID при некоторых обстоятельствах могут вызывает фрагментацию даже в некластеризованных индексах.
Обратите внимание, что если вы используете SQL Server 2005, newsequentialid () функция производит последовательные идентификаторы GUID. Это помогает предотвратить проблему фрагментации.
Я предлагаю использовать SQL-запрос, подобный следующему, для измерения фрагментации, прежде чем принимать какие-либо решения (извините за синтаксис, отличный от ANSI):
SELECT OBJECT_NAME (ips.[object_id]) AS 'Object Name',
si.name AS 'Index Name',
ROUND (ips.avg_fragmentation_in_percent, 2) AS 'Fragmentation',
ips.page_count AS 'Pages',
ROUND (ips.avg_page_space_used_in_percent, 2) AS 'Page Density'
FROM sys.dm_db_index_physical_stats
(DB_ID ('MyDatabase'), NULL, NULL, NULL, 'DETAILED') ips
CROSS APPLY sys.indexes si
WHERE si.object_id = ips.object_id
AND si.index_id = ips.index_id
AND ips.index_level = 0;
Если вы используете NewId (), вы можете переключиться на NewSequentialId (). Это должно помочь вставке perf.
Да, нет смысла иметь кластерный индекс для случайного значения.
Вероятно, вам нужны кластерные индексы ГДЕ-НИБУДЬ в вашей базе данных. Например, если у вас есть таблица «Автор» и таблица «Книга» с внешним ключом для «Автор», и если у вас есть запрос в вашем приложении, который говорит: «выберите ... из книги, где AuthorId = .. ", то вы будете читать набор книг. Будет быстрее, если эти книги будут физически расположены рядом друг с другом на диске, так что головке диска не придется прыгать от сектора к сектору, собирая все книги этого автора.
Итак, вам нужно подумать о своем приложении, о том, как оно запрашивает базу данных.
Внесите изменения.
А потом тест, потому что мало ли ...
Как уже отмечалось большинством, избегайте использования случайных идентификаторов в кластеризованном индексе - вы не получите преимуществ кластеризации. Фактически, вы испытаете увеличенную задержку. Избавиться от них всех - хороший совет. Также имейте в виду, что newsequentialid () может быть чрезвычайно проблематичным в сценарии репликации с несколькими мастерами. Если базы данных A и B вызывают newsequentialid () перед репликацией, возникнет конфликт.
Да, вам следует удалить кластерный индекс на первичных ключах GUID по причинам, указанным выше в Галвеге. Мы сделали это в наших приложениях.
Это зависит от того, делаете ли вы много вставок или вам нужен очень быстрый поиск по PK.
