Блог

У меня есть таблица, которая получает тысячи запросов select в час. SQL server предлагает мне создать четыре индекса с оценкой улучшения 99% для каждого:

• [Имя таблицы] ([Квалификатор кода]) (Запрос 3)
• [Имя таблицы] ([Код строки], [Квалификатор кода], [Номер строки]) (Запрос 1)
• [Имя таблицы] ([Код строки], [Квалификатор кода], [Статус])
• [Имя таблицы] ([Код строки], [Квалификатор кода], [Статус], [Номер строки]) (Запрос 2)

Типы следующие:

• [Определитель кода] nvarchar(20)
• [Код строки] nvarchar(20)
• [Статус] tinyint
• [Строка No_] int

Таблица обновляется не так часто, количество запросов select, по-видимому, превышает количество обновлений/вставок примерно от 1000 до 1. Тогда возникает вопрос, следует ли мне создавать все индексы или один индекс будет служить для улучшения всех запросов? И если бы я хотел добавлять по одному за раз и проверять производительность после каждого, в каком порядке я должен их добавлять и следует ли их добавлять отдельно или в некоторых комбинациях? И если на этот вопрос нельзя ответить, могу ли я что-нибудь сделать, чтобы выяснить это, кроме проб и ошибок, что невозможно, потому что я не могу изменить их сам.

РЕДАКТИРОВАТЬ (запросы, связанные с предлагаемыми индексами):

Запрос 1:

 SELECT  TOP (1) 
    * --enumerated, but still all columns selected
FROM
    "TableName" WITH(UPDLOCK)  
WHERE 
    ("Line Code"=@0 AND "Code Qualifier"=@1 AND "Line No_"=@2)
ORDER BY 
    "Message Batch Number" ASC,
    "Message Line Number" ASC OPTION(OPTIMIZE FOR UNKNOWN)
 

Запрос 2:

 SELECT 
    * --enumerated, but still all columns selected
FROM 
    "TableName" WITH(UPDLOCK)
WHERE 
    ("Line Code"=@0 AND "Code Qualifier"=@1 AND "Status"=@2 AND "Line No_"=@3) 
ORDER BY 
    "Message Batch Number" ASC,
    "Message Line Number" ASC OPTION(OPTIMIZE FOR UNKNOWN, FAST 50)
 

Запрос 3:

 UPDATE 
    "TableName" 
SET 
    "Status"=@0 
WHERE ("Code Qualifier"=@1)
 

Итак, одно из них на самом деле было обновлением. Это делается также много раз в час, но не так часто, как при выборе. Я не смог сразу найти один из этих запросов, но, надеюсь, они подойдут.

ПРАВКА 2 (Существующие индексы): Таким образом, они уже существуют, но почти не используются в запросах:

• [Номер пакета сообщений] (int), [Номер строки сообщения] (int) (Кластеризованный)
• [Статус], [Номер Пакета Сообщений], [Номер строки сообщения]
• [Контрольная ссылка] (nvarchar(30), [Номер пакета сообщений], [Номер строки сообщения]

Рассматривая запросы изолированно, в идеале вам понадобятся следующие индексы:

Запрос 1

 CLUSTERED INDEX ([Line Code], [Code Qualifier], [Line No_],
  [Message Batch Number], [Message Line Number])
-- first three columns in any order
 

Запрос 2

 CLUSTERED INDEX ([Line Code], [Code Qualifier], [Line No_], Status,
  [Message Batch Number], [Message Line Number])
-- first four columns in any order
 

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

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

Таким образом, требуется окончательная индексация:

 CLUSTERED INDEX ([Line Code], [Code Qualifier], [Line No_],
  [Message Batch Number], [Message Line Number])
-- first three columns in any order

-- you can change it to NONCLUSTERED by using INCLUDE
NONCLUSTERED INDEX ([Line Code], [Code Qualifier], [Line No_],
  [Message Batch Number], [Message Line Number])
  INCLUDE (Status, anyOtherColumnsHere)
 

Для запроса 3 вам просто нужно,

 NONCLUSTERED INDEX ([Code Qualifier]) INCLUDE (Status)
 

Если вы используете ORM, такой как Linq2SQL (который, казалось бы, учитывая имена параметров), убедитесь, что передаются правильные типы данных переменных и длины, иначе индексы не будут использоваться

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