Почему это сканирование индекса, а не поиск индекса?

Ответ 1

Помня о том, что это приведет к тому, что запрос, который может сильно сработать, и когда в него будут внесены дополнительные изменения, использование INNER LOOP JOIN должно заставить индекс покрытия использоваться на dbo.LocationCache.

SELECT      top 100 a.LocationId, b.SearchQuery, b.SearchRank
FROM        dbo.Locations a
INNER LOOP JOIN dbo.LocationCache b ON a.LocationId = b.LocationId
WHERE       a.CountryId = 2
AND         a.Type = 7

Ответ 2

Он использует сканирование индекса прежде всего потому, что он также использует объединение слиянием. Оператору Merge Join требуется два входных потока, которые сортируются в порядке, совместимом с условиями объединения.

И используется оператор Merge Join для реализации вашего INNER JOIN, потому что он считает, что это будет быстрее, чем более типичный оператор вложенного Loop Join. Вероятно, это правильно (обычно это так), используя два указанных им индекса, он имеет входные потоки, которые предварительно отсортированы в соответствии с условием соединения (LocationID). Когда входные потоки предварительно отсортированы, как это, то Merge Joins почти всегда быстрее, чем два других (объединения Loop и Hash).

Недостатком является то, что вы заметили: он, кажется, сканирует весь индекс, так как это может быть быстрее, если он считывает столько записей, которые никогда не будут использоваться? Ответ заключается в том, что сканирование (из-за их последовательного характера) может считывать от 10 до 100 раз больше записей в секунду.

Теперь Иски обычно выигрывают, потому что они выборочны: они получают только строки, которые вы запрашиваете, тогда как сканы неселективны: они должны возвращать каждую строку в диапазоне. Но поскольку сканы имеют гораздо более высокую скорость чтения, они могут часто бить Seeks до тех пор, пока отношение строк с отброшенными рядами к соответствующим строкам ниже, чем отношение строк сканирования/сек VS. Искать строки/сек.

Вопросы?

Хорошо, меня попросили объяснить последнее предложение больше:

"Отброшенная строка" - это та, которую считывает Scan (потому что она должна читать все в индексе), но это будет отклонено оператором Merge Join, потому что у него нет соответствия на другой стороне, возможно, из-за того, что условие предложения WHERE уже исключило его.

"Matching Rows" - это те, которые он читает, которые в действительности совпадают с чем-то в Merge Join. Это те же строки, которые были бы прочитаны Поиском, если бы Сканирование было заменено поиском.

Вы можете выяснить, что есть, посмотрев статистику в Плане запросов. Посмотрите на ту огромную стрелку слева от сканирования индекса? Это показывает, сколько строк оптимизатор считает, что он будет считывать с помощью Scan. В поле статистики индексного сканирования, которое вы опубликовали, отображается возвращаемое значение Actual Rows, составляет около 5,4 МБ (5,394,402). Это равно:

TotalScanRows = (MatchingRows + DiscardedRows)

(По-моему, в любом случае). Чтобы получить строки соответствия, просмотрите "Фактические строки", сообщаемые оператором Merge Join (вам может потребоваться снять TOP 100, чтобы получить это точно). Как только вы это знаете, вы можете получить отброшенные строки:

DiscardedRows = (TotalScanRows - MatchingRows)

И теперь вы можете рассчитать соотношение.

Ответ 3

Вы пытались обновить свою статистику?

UPDATE STATISTICS dbo.LocationCache

Вот несколько хороших ссылок на то, что это делает и почему оптимизатор запросов выберет сканирование по поиску.

http://social.msdn.microsoft.com/Forums/en-CA/sqldatabaseengine/thread/82f49db8-0c77-4bce-b26c-1ad0a4af693b

Резюме

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

Затем он решает, больше ли это эффективно искать индекс, или сканировать весь уровень листа индекса (в этом случае речь идет о трогании каждой странице в таблице, потому что это кластеризованный индекс). Это делает глядя на некоторые вещи. Во-первых, он догадывается, сколько строк/страниц, которые нужно будет сканировать. Эта называется точкой опрокидывания и является чем вы думаете. Смотрите этот замечательный блог Кимберли Трип http://www.sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx

Если вы находитесь в пределах Это может быть связано с тем, что ваш статистика устарела или ваша индекс сильно фрагментирован.

Можно заставить SQL искать с помощью запроса FORCESEEK подскажите, но используйте это с предостережение, как правило, предоставление вам сохранить все следы, SQL довольно хорошо решить, что самый эффективный план будет!

Ответ 4

Вкратце: у вас нет фильтра в LocationCache, все содержимое таблицы должно быть возвращено. У вас есть полностью покрывающий индекс. Индекс SCAN (один раз) является самой дешевой операцией, и оптимизатор запросов выбирает ее.

Чтобы оптимизировать: Вы присоединяетесь ко всем таблицам, а затем получаете только 100 лучших результатов. Я не знаю, насколько они велики, но попробуйте подзапросить таблицу [Locations] CountryId, Type, а затем присоедините только результат с [LocationCache]. Будет waaaay быстрее, если у вас более 1000 строк. Кроме того, попробуйте добавить несколько более ограничительных фильтров до объединения, если это возможно.

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

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

Если в таблице есть индекс, и если запрос касается большего объема данных, это означает, что запрос извлекает более 50% или 90% данных, а затем оптимизатор будет просто сканировать все данные страниц для извлечения строк данных.

источник

Ответ 5

Я сделал быстрый тест и придумал следующее

CREATE TABLE #Locations
(LocationID INT NOT NULL ,
CountryID INT NOT NULL ,
[Type] INT NOT NULL 
CONSTRAINT PK_Locations
        PRIMARY KEY CLUSTERED ( LocationID ASC )
)

CREATE NONCLUSTERED INDEX [LocationsIndex01] ON #Locations
(
    CountryID ASC,
    [Type] ASC
)

CREATE TABLE #LocationCache
(LocationID INT NOT NULL ,
SearchQuery VARCHAR(50) NULL ,
SearchRank INT NOT NULL 
CONSTRAINT PK_LocationCache
        PRIMARY KEY CLUSTERED ( LocationID ASC )

)

CREATE NONCLUSTERED INDEX [LocationCacheIndex01] ON #LocationCache
(
    LocationID ASC,
    SearchQuery ASC,
    SearchRank ASC
)

INSERT INTO #Locations
SELECT 1,1,1 UNION
SELECT 2,1,4 UNION
SELECT 3,2,7 UNION
SELECT 4,2,7 UNION
SELECT 5,1,1 UNION
SELECT 6,1,4 UNION
SELECT 7,2,7 UNION
SELECT 8,2,7 --UNION

INSERT INTO #LocationCache
SELECT 4,'BlahA',10 UNION
SELECT 3,'BlahB',9 UNION
SELECT 2,'BlahC',8 UNION
SELECT 1,'BlahD',7 UNION
SELECT 8,'BlahE',6 UNION
SELECT 7,'BlahF',5 UNION
SELECT 6,'BlahG',4 UNION
SELECT 5,'BlahH',3 --UNION

SELECT * FROM #Locations
SELECT * FROM #LocationCache

SELECT      top 3 a.LocationId, b.SearchQuery, b.SearchRank
FROM        #Locations a
INNER JOIN  #LocationCache b ON a.LocationId = b.LocationId
WHERE       a.CountryId = 2
AND         a.[Type] = 7

DROP TABLE #Locations
DROP TABLE #LocationCache

Для меня план запроса показывает запросы с внутренним соединением вложенного цикла. Если вы запустите это, вы получите оба искания? Если вы это сделаете, сделайте тест в своей системе и создайте копию своей таблицы Locations and LocationCache и вызовите их, указав Locations2 и LocationCache2 со всеми индексами и скопируйте свои данные в них. Затем попробуйте выполнить запрос на новые таблицы?