Поиск индекса с объединением

У меня есть таблица [MyTable] со столбцом [MyColumn] NVarchar (50). У меня есть некластеризованный индекс в этом столбце, теперь при запуске ниже двух запросов:

SELECT  1
FROM    [MyTable] M
WHERE   M.[MyColumn] = @MyColumn

SELECT  1
FROM    [MyTable] M
WHERE   M.[MyColumn] = COALESCE(@MyColumn, M.[MyColumn] )

Я заметил, что первый запрос использует Index Seek (NonClustered), а второй использует Index Scan (Non Clustered). Могу ли я узнать, как я буду использовать поиск индекса с coalesce или isnull?

Ответ 1

Могу ли я узнать, как я буду использовать поиск индекса с coalesce или isnull?

Возможно, не ответ на ваш вопрос, но у вас могут быть два разных запроса. Один для случая, когда @MyColumn is null и один для случая, когда вы хотите использовать @MyColumn в предложении where.

IF @MyColumn IS NULL
BEGIN
  SELECT  1
  FROM    [MyTable] M
END  
ELSE
BEGIN
  SELECT  1
  FROM    [MyTable] M
  WHERE   M.[MyColumn] = @MyColumn
END

Ответ 2

Использование таких функций, как COALESCE или ISNULL в предложении where, запрашивает у сервера поиск результатов этих функций, которые неизвестны до тех пор, пока они не будут выполнены для каждой строки в результирующем наборе, поэтому нет способа для того, чтобы использовать индекс.

Чтобы воспользоваться всеми преимуществами индекса, не используйте функции в предложении WHERE, измените его со стандартными условиями, например. WHERE MyColumn = @MyColumn OR @MyColumn IS NULL

Ответ 3

Это непросто, так как Алекс указал, что функции заставляют сканировать, так как оптимизатор знает, что нужно проверять каждую строку.

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

На самом деле не самый красивый способ поиска.

EDIT:

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

ИЗМЕНИТЬ 2:

Вместо вашей текущей логики попробуйте что-то вроде:

...
WHERE (M.[MyColumn] = @MyColumn 
       OR @MyColumn IS NULL)

Ответ 4

Я думаю, вы будете использовать этот запрос в более сложном, возможно, с EXISTS:

EXISTS 
( SELECT  1
  FROM    [MyTable] M
  WHERE   M.[MyColumn] = COALESCE(@MyColumn, M.[MyColumn] )
)

Попробуйте это вместо:

EXISTS 
( SELECT  1
  FROM    [MyTable] M
  WHERE   M.[MyColumn] = @MyColumn
)
OR EXISTS
( SELECT  1
  FROM    [MyTable] M
  WHERE   @MyColumn IS NULL
)

Или этот:

CASE WHEN @MyColumn IS NULL
     THEN 1
     ELSE 
        ( SELECT  1
          FROM    [MyTable] M
          WHERE   M.[MyColumn] = @MyColumn
        )
END

Ответ 5

В запросе с предложением coalesce оптимизатор знает, что "MyColumn" - это диапазон значений, поэтому он решит использовать сканирование индекса. Единственный метод использования поиска, когда передается переменная, отличная от нуля, заключается в том, чтобы закодировать два сохраненных procs и вызвать подходящий через логическое тестирование переменной.

Если у вас есть такая же простая ситуация, как и в вашем примере, и вы хотите использовать индексный поиск, когда переменная NOT NULL, вы должны закодировать запрос как:

If @MyColumn is NULL
Begin
    EXEC MyStoredProcWithMyColumn=Mycolumn
END
ELSE
Begin
    EXEC MyStoredProcWithMyColumn=Variable @MyColumn
END

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