SQL-сервер - стоит индексировать большие строковые ключи?

У меня есть таблица с большим строковым ключом (varchar(1024)), который я собирался проиндексировать на сервере SQL (я хочу иметь возможность быстрого поиска по нему, но также важны вставки). В SQL 2008 я не получаю предупреждение об этом, но в SQL Server 2005 он сообщает мне, что он превышает 900 байт и что вставки / обновления столбца с таким размером будут отброшены (или что-то в этой области)

Каковы мои альтернативы, если я хочу индексировать этот большой столбец? Я не знаю, стоило ли бы это того, если бы я мог так или иначе.

Источник

2 ответа

Решение

Индекс со всеми ключами около 900 байт будет очень большим и очень глубоким (очень мало ключей на страницу приводит к очень высоким B-деревьям).

Это зависит от того, как вы планируете запрашивать значения. Индекс полезен в нескольких случаях:

  • когда значение проверяется. Это наиболее типичное использование, когда в таблице ищется точное значение. Типичные примеры WHERE column='ABC' или условие соединения ON a.column = B.someothercolumn,
  • когда диапазон сканируется. Это также довольно типично при поиске диапазона значений в таблице. Помимо очевидного примера WHERE column BETWEEN 'ABC' AND 'DEF' Есть и другие, менее очевидные примеры, такие как частичное совпадение: WHERE column LIKE 'ABC%',
  • требование заказа. Это использование менее известно, но индексы могут помочь запросу, который имеет явный ORDER BY column Требование избегать сортировки по принципу "останови и работай", а также может помочь определенному скрытому ROW_NUMBER() OVER (ORDER BY column),

Итак, зачем вам индекс? Какие запросы будут использовать это?

Для сканирования диапазона и для требований заказа нет другого решения, кроме как иметь индекс, и вам придется взвесить стоимость индекса и выгоды.

Для проб можно потенциально использовать хеш, чтобы избежать индексации очень большого столбца. Создать постоянный вычисляемый столбец как column_checksum = CHECKSUM(column) а затем индексировать по этому столбцу. Запросы должны быть переписаны для использования WHERE column_checksum = CHECKSUM('ABC') AND column='ABC', Тщательное рассмотрение должно быть уделено взвешиванию преимущества узкого индекса (32-битной контрольной суммы) по сравнению с недостатками двойной проверки столкновения и отсутствием возможностей сканирования диапазона и порядка.

после комментария

Однажды у меня была похожая проблема, и я использовал хэш-столбец. Значение было слишком велико для индекса (>1 КБ), и мне также нужно было преобразовать значение в идентификатор для хранения (в основном, словарь). Нечто подобное:

create table values_dictionary (
  id int not null identity(1,1),
  value varchar(8000) not null,
  value_hash = checksum(value) persisted,
  constraint pk_values_dictionary_id
     primary key nonclustered (id));
create unique clustered index cdx_values_dictionary_checksum on (value_hash, id);
go

create procedure usp_get_or_create_value_id (
   @value varchar(8000),
   @id int output)
begin
   declare @hash = CHECKSUM(@value);
   set @id = NULL;
   select @id = id
      from table
      where value_hash = @hash
      and value = @value;
  if @id is null
  begin
      insert into values_dictionary (value)
        values (@value);
      set @id = scope_identity();
  end
end

В этом случае таблица словаря организована в виде кластерного индекса на values_hash столбец, который группирует все сталкивающиеся хеш-значения вместе. id Добавлен столбец, чтобы сделать кластеризованный индекс уникальным, избегая необходимости в скрытом столбце уникализатора. Эта структура делает поиск для @value максимально эффективно, без крайне неэффективного индекса на value и минуя ограничение в 900 символов. Первичный ключ на id не кластеризовано, что означает, что глядя вверх value от и id несет накладные расходы одного дополнительного зонда в кластеризованном индексе.

Не уверен, что это решит вашу проблему, вы, очевидно, знаете больше о ваших реальных сценариях, чем я. Кроме того, код не обрабатывает ошибки и может фактически вставлять повторяющиеся записи @value, что может быть или не быть правильным.

Источник

Общие рекомендации по разработке индекса

При разработке индекса учитывайте следующие рекомендации для столбцов:

  • Длина ключа индекса должна быть короткой для кластерных индексов. Кроме того, кластерные индексы выигрывают от создания уникальных или ненулевых столбцов. Для получения дополнительной информации см. Рекомендации по проектированию кластерного индекса.

  • Столбцы с типами данных ntext, text, image, varchar(max), nvarchar(max) и varbinary(max) нельзя указывать в качестве столбцов ключа индекса. Однако типы данных varchar(max), nvarchar(max), varbinary(max) и xml могут участвовать в некластеризованном индексе как столбцы неключевого индекса. Для получения дополнительной информации см. Индекс с включенными столбцами.

  • Изучите распределение данных в столбце. Часто длительный запрос вызывается индексацией столбца с несколькими уникальными значениями или выполнением объединения для такого столбца. Это фундаментальная проблема с данными и запросами, и, как правило, ее невозможно решить без выявления этой ситуации. Например, физический телефонный справочник, отсортированный в алфавитном порядке по фамилии, не ускорит поиск человека, если все люди в городе будут названы Смит или Джонс

Источник
Другие вопросы по тегам