Блог

У меня есть таблица с 5 миллионами записей следующим образом

 Id          BasePrefix  DestPrefix  ExchangeSetId   ClassId
11643987    0257016         57016           1           3
11643988    0257016         57278           1           3
11643989    0257016         57279           1           3
11643990    0257016         57751           1           3
  

Советник по настройке SQL рекомендовал следующий индекс

 CREATE NONCLUSTERED INDEX [ExchangeIdx] ON [dbo].[Exchanges]
(
    [ExchangeSetId] ASC,
    [BasePrefix] ASC,
    [DestPrefix] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  

Однако, учитывая следующее

 DECLARE @exchangeSetID int = 1;
DECLARE @BasePrefix nvarchar( 10 ) = '0732056456';
DECLARE @DestPrefix nvarchar( 10 ) = '30336456';
DECLARE @BaseLeft nvarchar( 10 ) = left(@BasePrefix,4);
  

Эти 2 запроса дают мне совершенно разные планы выполнения

Запрос 1

 SELECT TOP 1 ClassId
  FROM Exchanges
  WHERE
  exchangeSetID = @exchangeSetID
   AND BasePrefix LIKE '0732'   '%'
   AND '0732056456' LIKE BasePrefix   '%'
   AND '30336456' LIKE DestPrefix   '%';
  

Запрос 2

 SELECT TOP 1 ClassId
  FROM Exchanges
  WHERE
  exchangeSetID = @exchangeSetID
   AND BasePrefix LIKE @BaseLeft   '%'
   AND @BasePrefix LIKE BasePrefix   '%'
   AND @DestPrefix LIKE DestPrefix   '%';
  

Разница между 2 запросами @BaseLeft и '0732' соответственно

В основном, в первом примере используется индекс, а во втором — не так много

Есть ли какая-либо веская причина, почему это должно быть так?

И если это не просто фундаментальный недостаток в моем мышлении, как я могу передать переменную во второй запрос и использовать индекс?

Объяснение такого поведения — переломный момент (# 1, # 2 ).

В принципе, в зависимости от избирательности предиката, которая влияет на количество страниц данных 8K, считываемых из буферного пула, SQL Server имеет два варианта фильтрации строк:

1) Поиск по индексу поиск по ключу / RID

2) Таблица / [Clustere] Проверка индекса.

Почему SQL Server может использовать сканирование вместо поиска поиска? Поскольку в некоторых случаях (низкая / средняя / средне-высокая избирательность) использование поиска поиска может считывать из буферного пула больше страниц, чем за одно сканирование.

Что делать? Вы должны создать закрытый индекс таким образом:

 create nonclustered index ...
on ... (...)
include (ClassId);