Индексный поиск против индексации

Глядя на план выполнения медленного запроса, и я заметил, что некоторые из узлов являются поиском индекса, а некоторые из них - сканированием индекса.

В чем разница между поиском индекса и индексированием?

Что лучше?

Как SQL выбирает один из них?

Я понимаю, что это 3 вопроса, но я думаю, что ответ на первый из них объяснит остальные.

59 голосов | спросил Greg 20 Mayam13 2013, 10:32:12

4 ответа


72

Краткая версия: поиск намного лучше

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

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

Сканирование таблицы . Без каких-либо индексов, имеющих отношение к вашему запросу, планировщик вынужден использовать сканирование таблицы, означающее, что каждая строка просматривается. Это может привести к тому, что каждая страница, относящаяся к данным таблицы, считывается с диска, что часто бывает наихудшим. Обратите внимание, что для некоторых запросов он будет использовать сканирование таблицы, даже если присутствует полезный индекс - это обычно потому, что данные в таблице настолько малы, что больше хлопот пересекать индексы (если это так, вы ожидаете, что планируют меняться по мере роста данных, предполагая, что показатель селективности индекса хорош).

Сканирование индексов с поиском строк: . Без индекса, который может быть использован непосредственно для поиска, найден, но есть индекс, содержащий правильные столбцы, может использоваться индексное сканирование. Например, если у вас есть большая таблица с 20 столбцами с индексом в столбце1, col2, col3 и вы выпускаете SELECT col4 FROM exampletable WHERE col2=616, в этом случае сканирование индекса на запрос col2 лучше, чем сканирование всей таблицы. После того, как найдены совпадающие строки, страницы данных должны быть прочитаны для записи col4 для вывода (или дальнейшего присоединения), что является тем, что этап «поиска в закладке», когда вы видите его в планах запросов.

Индексные проверки без поиска строк: Если приведенный выше пример был SELECT col1, col2, col3 FROM exampletable WHERE col2=616, то дополнительные усилия для чтения страниц данных не необходимо: после нахождения строк индекса, соответствующих col2=616, все запрошенные данные известны. Вот почему вы иногда видите столбцы, которые никогда не будут искать, но, скорее всего, будут запрашиваться для вывода, добавляются в конец индексов - он может сохранять поиск строк. При добавлении столбцов в индекс по этой причине и только по этой причине добавьте их с предложением INCLUDE, чтобы сообщить движку, что ему не нужно оптимизировать макет индекса для запросов на основе этих столбцов (это может ускорить обновление, сделанное для этих столбцов). Сканирование индексов может также возникать из запросов без предложений фильтрации: SELECT col2 FROM exampletable будет сканировать этот примерный индекс вместо табличных страниц.

Index Seeks (с поиском строк или без него) : В поиске не учитывается весь индекс. Для запроса SELECT * FROM exampletable WHERE c1 BETWEEN 1234 AND 4567 механизм запроса может найти первую строку, которая будет соответствовать, выполнив поиск по дереву в индексе на c1 то он может перемещаться по индексу до тех пор, пока он не дойдет до конца диапазона (это то же самое с запросом для c1=1234), поскольку может быть много строк, соответствующих условию даже для =). Это означает, что для каждой страницы в индексе (или таблице) необходимо читать только соответствующие страницы индекса (плюс несколько, необходимые для первоначального поиска).

Кластеризованные индексы: С кластеризованным индексом данные таблицы хранятся в листовых узлах этого индекса вместо того, чтобы находиться в отдельной структуре кучи. Это означает, что после поиска строк, использующих этот индекс, не потребуется никаких дополнительных поисков строк, независимо от того, какие столбцы необходимы [если у вас нет данных вне страницы, таких как =) или TEXT, содержащие длинные данные.

Для этой цели может быть только один кластерный индекс [1] , кластеризованный индекс является вашей таблицей вместо отдельной структуры кучи, поэтому, если вы используете один [2] выбрал, где вы тщательно его размещаете, чтобы получить максимальный выигрыш.

Также обратите внимание, что кластеризованный индекс, потому что «кластерный ключ» для таблицы и включен в каждый некластеризованный индекс в таблице, поэтому широкий кластеризованный индекс обычно не является хорошей идеей.

[1] Фактически, вы можете эффективно иметь несколько кластерных индексов, определяя некластеризованные индексы, которые покрывают или включают в себя каждый столбец в таблице, но это скорее всего, будет расточительным пространством, имеет влияние производительности записи, поэтому, если вы считаете, что это действительно нужно, вам действительно нужно.

[2] Когда я говорю «если вы используете кластерный индекс», обратите внимание, что обычно рекомендуется, чтобы вы делаем по одному в каждой таблице. Существуют исключения, как и во всех правилах большого пальца, таблицы, которые видят немного больше, чем объемные вставки и неупорядоченные чтения (возможно, являются промежуточными таблицами для процессов ETL).

Дополнительная точка: НеполнаяСканы:

Важно помнить, что в зависимости от остальной части запроса сканирование таблицы /индекса не может фактически сканировать всю таблицу - если логика позволяет план запроса может привести к его прерванию раньше. Простейшим примером этого является VARCHAR(MAX) - если вы посмотрите на план запроса для этого, вы увидите, что из сканирования было возвращено только одна строка, и если вы наблюдаете за IO статистика (SELECT TOP(1) * FROM HugeTable), вы увидите, что он читает только очень небольшое количество страниц (возможно, только одно).

То же самое может произойти, если предикат предложения SET STATISTICS IO ON; SELECT TOP(1) * FROM HugeTable или WHERE может запускаться одновременно с проверкой, которая является источником, если его данные. Планировщик запросов /бегун иногда может быть очень умным в том, что он преследует предикаты назад к источникам данных, чтобы позволить раннее завершение сканирования таким образом (а иногда вы могут быть умны в переупорядочивании запросов, чтобы помочь ему это сделать! ). В то время как данные data ​​em> передаются справа налево в соответствии со стрелками на экране стандартного плана запросов, логика работает слева направо и каждый шаг (справа налево) слева) не обязательно заканчивается до начала следующего. В простом примере выше, если вы посмотрите на каждый блок в плане запроса в качестве агента, агент JOIN ... ON запрашивает агент SELECT для строки, которая, в свою очередь, запрашивает TOP для агента, тогда агент TABLE SCAN запрашивает другое, но агент /> не знает, что нет необходимости, даже не спрашивайте читателя таблицы , агент SELECT получает ответ «больше не имеет значения» и знает, что вся работа выполнена. Многие операции часто блокируют такую ​​оптимизацию в более сложных примерах, когда сканирование таблицы /индекса действительно делает чтение каждой строки, но будьте осторожны, чтобы не перейти к выводу, что любое сканирование должно быть дорогостоящей операцией .

ответил David Spillett 20 Maypm13 2013, 13:01:40
5

Как правило, поиск хорош, сканирование плохое.

Ищет, где запрос способен эффективно использовать индекс и использовать его для поиска требуемых строк.

Сканирование - это то, где запрос просматривает весь индекс, пытаясь найти то, что ему нужно.

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

Есть несколько книг для чтения, которые могут быть интересны здесь - оба из книжного магазина Red-Gate в http: //www.red-gate.com/community/books/

  • Планы выполнения SQL Server по Grant Fritchey
  • Внутри оптимизатора запросов от Benjamin Nevarez
  • Статистика SQL Server от Holger Schmeling
ответил Thomas Rushton 20 Mayam13 2013, 10:46:45
5

Если вы хотите копать тему, очень полезная книга (по крайней мере для меня) - это планы выполнения SQL Server от Grant Fritchey, свободно доступные в RedGate здесь .

Если у вас есть запрос, например

SELECT *
FROM myTable

SQL Server, скорее всего, будет использовать сканирование индекса, так как ему нужно пройти через все строки, чтобы отобразить требуемые результаты.

Наоборот,

SELECT *
FROM myTable
WHERE myID = 1

, безусловно, приведет к поиску индекса. SQL Server будет использовать структуру B-tree индекса myID и получения правильной строки будет намного быстрее.

ответил KookieMonster 20 Mayam13 2013, 10:53:35
5

Другие хорошо определили различия между поиском и проверкой. В этом случае ваш запрос и планировщик выполнения должны предоставить вам необходимую информацию, чтобы увидеть, какие значения используются в качестве предикатов (фильтров) для запроса в каждой части. Как правило, хорошей практикой всегда является добавление некластеризованных индексов на внешние ключи, и в зависимости от вариантов использования в программном коде вам может понадобиться создать дополнительные индексы с несколькими столбцами или включить индексы столбцов. С терминологией, представленной здесь, поиск в Google даст достойные результаты по примерам на каждом из них.

Но в качестве примера скажем, что ваш код запрашивает столбцы A и столбцы B для данных фильтров, но вы также хотите вернуть значения столбца C и столбца E, вам может понадобиться создать индекс в столбцах A и B с опцией INCLUDE, содержащей столбцы C и E. Таким образом, поиск по одному индексу возвращает все, что вам нужно, поскольку нет необходимости выполнять поиск, чтобы извлекать другие значения (C и E) в одной строке.

ответил Kahn 20 Maypm13 2013, 12:57:33

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132