Длина ключа имеет значение в индексах SQL Server.

В приведенном ниже примере мы будем использовать метод столбца базовой таблицы, так как он наиболее гибкий, и мне не придется вдаваться в подробности о подсказках в операторах SQL.

Давайте начнем с создания таблицы для игры:

CREATE TABLE [dbo].[Table1]

(

Id int NOT NULL IDENTITY

PRIMARY KEY CLUSTERED,

StringCol varchar(MAX) NOT NULL

);

GO

А затем мы заполним ее некоторыми тестовыми данными (примечание: общий размер таблицы в этой демонстрации будет около 70 МБ):

SET NOCOUNT ON;

GO

INSERT INTO [dbo].[Table1](StringCol)

SELECT

REPLICATE('The quick brown fox jumps over the lazy dog. ', 5) +

CONVERT(varchar(MAX), NEWID())

FROM master..spt_values v

WHERE v.type = 'P';

GO 64

INSERT INTO [dbo].[Table1](StringCol)

VALUES ('The quick brown fox jumps over the lazy dog.');

GO 3

Итак, у нас есть около 130 000 строк случайного типа, а затем 3 строки, которые мы можем предсказать. Наша цель — эффективно найти эти 3 строки в тестовых запросах.

Поскольку строковое поле было определено как varchar(MAX), мы вообще не можем создать индекс для этого столбца, и единственный индекс, который у нас есть в базовой таблице, — это кластеризованный индекс для столбца Id. Если мы попробуем наивный поиск в этот момент, мы получим сканирование таблицы, что является худшим сценарием:

?

DECLARE @search varchar(MAX) =

'The quick brown fox jumps over the lazy dog.';

SELECT Id, StringCol

FROM [dbo].[Table1]

WHERE StringCol = @search;

Получаем обратно наши 3 ряда, но это далеко не идеально. Я не знаю, почему оптимизатор выбирает сканирование и фильтрацию вместо того, чтобы протолкнуть предикат в сам оператор сканирования, но это не имеет значения, потому что это ужасный план с ориентировочной стоимостью более 3.5.

Давайте продолжим и создадим наш столбец хеш-значений и заполним его. Обратите внимание, что если вы хотите обновить хеш-значения с помощью триггеров и сохранить столбец NOT NULL, вам нужно добавить ограничение по умолчанию, чтобы INSERT не терпел неудачу сразу, прежде чем код триггера получит возможность запуститься.

?

ALTER TABLE [dbo].[Table1]

ADD StringHash binary(16) NULL;

GO

UPDATE [dbo].[Table1]

SET StringHash = CONVERT(binary(16), HASHBYTES('MD5', StringCol));

GO

ALTER TABLE [dbo].[Table1]

ALTER COLUMN StringHash binary(16) NOT NULL;

GO

У нас есть наши хэш-значения в таблице, но все еще нет индекса для улучшения нашего запроса. Теперь создадим индекс:

CREATE NONCLUSTERED INDEX IX_Table1_StringHash

ON [dbo].[Table1](StringHash);

Хотя это выглядит ничем не примечательным, индекс был создан неуникальным намеренно — в первую очередь для размещения дублирующихся исходных (и, следовательно, хэш-значений), а также для учета возможности коллизий хэшей. Даже если ваши исходные значения гарантированно уникальны, этот индекс не должен быть уникальным. Кроме этого, этого должно быть достаточно, чтобы мы могли построить запрос, чтобы сделать поиск более эффективным:

DECLARE @search varchar(MAX) =

'The quick brown fox jumps over the lazy dog.';

SELECT Id, StringCol

FROM [dbo].[Table1]

WHERE

(StringHash = CONVERT(binary(16), HASHBYTES('MD5', @search))) AND

(StringCol = @search);

Вы заметите, что я по-прежнему включил предикат StringCol = @search в предложение WHERE — это необходимо для обеспечения правильных результатов запроса из-за коллизий хэшей. Если бы все, что мы делали, это сравнивали хэш-значения, мы могли бы получить дополнительные строки в результатах. Вот план выполнения запроса выше:

Мы получили поиск по индексу, что было главным, что мы искали. Поиск ключа ожидается здесь, потому что мы также должны сравнивать исходные значения, а они могут быть взяты только из базовой таблицы. Теперь вы понимаете, почему я сказал, что этот метод работает только для умеренно-высокоизбирательных запросов, потому что поиск ключа требуется, и если выбирается слишком много строк, эти случайные операции могут снизить производительность ( или оптимизатор может вернуться к сканированию таблицы). В любом случае теперь у нас есть оптимальный запрос, и даже с учетом поиска ключей расчетная стоимость составила 0,0066, что более чем в 500 раз больше для этой маленькой таблицы.

ЧАСТЬ 2

Лучше всего делать ключи индекса как можно более узкими, а SQL Server применяет максимальную длину ключа в 900 байт для большинства обычных кластеризованных и некластеризованных индексов.

Но что произойдет, если вы захотите оптимизировать поиск в широком столбце? Вам не обязательно не повезло, возможно, вам просто нужно немного проявить творческий подход.

Что делать, если мне нужно выполнить поиск равенства в широком столбце?

Допустим, у меня есть простая таблица. У меня есть узкий ключ в моем кластеризованном индексе, а затем у меня есть довольно широкий столбец переменной длины. Мне нужно, чтобы широкий столбец был юникодным, что делает его еще шире, так как типы юникодных данных занимают больше места.

Вот наша примерная таблица с несколькими строками (просто представьте, что их намного больше):

  1. СОЗДАТЬ ТАБЛИЦУ dbo.LookupValues ​​(
  2. i int личность,
  3. bigval nvarchar(2000) по умолчанию (REPLICATE(‘d’,700)),
  4. ограничение pk_LookupValues_i первичный ключ (i)
  5. );
  6. GO
  7. — Вставить строки со значениями по умолчанию
  8. начать транс
  9. объявить @i smallint = 0;
  10. а @i ‹ 10000
  11. начать
  12. вставить dbo.LookupValues ​​по умолчанию значения;
  13. установить @i=@i+1;
  14. конец
  15. зафиксировать
  16. GO
  17. — Вставьте несколько меньших значений
  18. insert dbo.LookupValues ​​(bigval) VALUES (‘big’);
  19. insert dbo.LookupValues ​​(bigval) VALUES (‘кролик’);
  20. insert dbo.LookupValues ​​(bigval) VALUES («кролик-кролик»);
  21. GO

Допустим, мы редко пишем в эту таблицу, но часто запрашиваем ее. Когда этот запрос выполняется, я хочу сделать его как можно быстрее:

  1. ВЫБРАТЬ я
  2. из dbo.LookupValues
  3. где bigval = N'bunny';

Прямо сейчас этот запрос должен сканировать каждую строку в кластеризованном индексе (вся таблица), чтобы найти экземпляры, где bigval=N’bunny’. Это не идеально, и по мере роста таблицы она будет становиться все хуже и хуже, сжигая больше операций ввода-вывода и ЦП и со временем занимая больше времени.

Обычно есть простой способ сделать такой запрос быстрым: просто создайте некластеризованный индекс для столбца bigval. Но когда я пытаюсь, это не работает из-за ограничений на размер ключа.

  1. — Сделайте мой запрос быстрее!
  2. СОЗДАТЬ НЕКЛАСТЕРНЫЙ ИНДЕКС ix_LookupValues_bigval в dbo.LookupValues ​​(bigval);
  3. GO

SQL говорит:

[код] Внимание! Максимальная длина ключа составляет 900 байт. Индекс ix_LookupValues_bigval имеет максимальную длину 4000 байт. Для некоторых комбинаций больших значений операция вставки/обновления завершится ошибкой.
Сообщение 1946, уровень 16, состояние 3, строка 1
Операция не удалась. Запись индекса длиной 1400 байт для индекса ix_LookupValues_bigval превышает максимальную длину в 900 байт.
Оператор был завершен.
[/code]

Прекращено. Ага. Я не могу просто проиндексировать это, чтобы сделать мой запрос быстрым.

Варианты индексации широких ключей

Так что же делать настройщику производительности?

Моя первая мысль, когда я столкнулся с этой проблемой, заключалась в том, что мне, возможно, придется использовать полнотекстовый индекс. Здесь может работать полнотекстовый индекс — он позволяет индексировать большие столбцы, но было бы не очень удобно это делать. Полнотекстовые индексы имеют дополнительные накладные расходы и на самом деле предназначены для других целей, чем простой поиск на равенство, поэтому это будет похоже на использование отбойного молотка, потому что вы не можете найти молоток.

Мой партнер Jeremiah Peschka придумал быстрое и умное решение, используя индексированный вычисляемый столбец. С вычисляемыми столбцами в SQL Server можно творить всевозможные чудеса — главное — просто запомнить их!

Вот как это работает: вы добавляете в таблицу вычисляемый столбец, который является хэшем большого значения. Затем вы индексируете вычисляемый столбец и модифицируете свой запрос, чтобы воспользоваться им.

В этом примере мы используем SHA_512 для алгоритма хеширования. Это даст на выходе 64 байта — вполне в пределах наших ограничений для размеров ключа индекса.

  1. ALTER TABLE dbo.LookupValues ​​ADD bigvalhash AS HASHBYTES(‘SHA2_512’, bigval) PERSISTED;
  2. GO
  3. СОЗДАТЬ НЕКЛАСТЕРНЫЙ ИНДЕКС ix_LookupValues_bigvalhash в dbo.LookupValues ​​(bigvalhash) INCLUDE (bigval);
  4. GO

Теперь, чтобы заставить запрос работать, нам нужно немного изменить его:

  1. ВЫБРАТЬ я
  2. из dbo.LookupValues
  3. где bigvalhash = HASHBYTES('SHA2_512', N'bunny')
  4. и bigval = N’зайчик’;
  5. GO

Этот пересмотренный подход дает мне целенаправленный поиск по индексу и ограничивает мои логические чтения. Вуаля!

Мелкий шрифт в этом решении

Есть несколько замечаний:

  • Результаты HASHBYTES зависят от типа данных. Если бы мой запрос использовал HASHBYTES(‘SHA2_512’, ‘bunny’), он не нашел бы ни одной строки, потому что столбец содержит хешированные значения юникода, а я предоставил хешированное значение, отличное от юникода.
  • Я по-прежнему включаю bigval= N’bunny в свой запрос. Теоретически не должно быть коллизий с SHA-512, но это не добавляет больших затрат на запрос, и в моем примере я посчитал, что оно того стоит. Возможно, вы сделаете другой выбор.

Иногда помогают старые инструменты

Что мне больше всего нравится в этом решении, так это то, что оно творческое, но в нем нет ничего странного, если подумать. Он использует стандартные функции, которые были в SQL Server в течение длительного времени, чтобы создать способ сделать то, что кажется, что продукт не будет поддерживать, и это действительно здорово.