Давайте сравним методы индексного доступа и полного сканирования таблицы на определенном примере. На рис. 2.6 показаны два пути, ведущие к пяти строкам, обозначенным черным цветом, в таблице, состоящей из 40 блоков. Обычно в таких таблицах содержится приблизительно 3 200 строк.

Корень

Ветвь В

Листовой блок L

Идвнтификатор Щ строки

Считывание нвскольких

блоков по 64 Кбайт

Рис. 2.6. Индексный доступ и полное сканирование таблицы

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

В случае полного сканирования таблицы, если до его начала блоки таблицы еще не находятся в кэше, база данных выполняет пять считываний фрагментов данных размером по 64 Кбайт, то есть считывает всю таблицу до отметки заполнения. Затем сервер баз данных проходит через все 40 блоков и 3 200 строк, отметая все, кроме пяти строк, удовлетворяющих условию запроса. Если у базы данных нет кэша и вы заботитесь только о времени, которое занимает перемещение считывающих головок диска при вьшолнении физического ввода-вывода, то вы насчитаете семь операций физического ввода-вывода для случая индексированного доступа и пять операций для полного сканирования таблицы, и, следовательно, выберете полное сканирование. Однако небольшая таблица и небольшой индекс, как в этом случае, скорее всего, полностью кэшированы. Семь операций логического ввода-вывода дешевле, чем 40 операций логического ввода-выюда, даже если

они нужны для полного сканирования таблицы. Кроме затрат на логический ввод-вывод, индексированный план выполнения избегает затрат на работу процессора, которая выполняется при просмотре более чем 3 ООО ненужных строк.

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

Выбор между полным сканированием таблицы и индексным доступом

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

Индексные считывания практически всегда кэшируются.

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

У одного блока вероятность попасть в кэш больше, чем у группы из нескольких блоков, поэтому эффективный коэффициент попадания в кэш для таблицы лучше при индексном считывании. Например, если в кэше находится половина блоков таблицы, выбранная случайным образом, то коэффициент попадания в кэш при чтении одного блока из этой таблицы равен 50 %, а вероятность нахождения в кэше всех восьми блоков при считывании нескольких блоков равна всего лишь 0,5, то есть приблизительно 0,4 %. Чтобы достигнуть эффективного коэффициента попадания в кэш не менее 50 % для считывания восьми блоков, необходимо достигнуть коэффициента попадания в кэш, равного 91,7 %, для отдельных блоков, кэшированных случайным образом.

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

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

Я не пытаюсь зря потратить ваше время на незначительный пример. Различия будут иметь значение, если расширить этот пример до весьма объемных таблиц и индексов, но такой пример невозможно проиллюстрировать так же подробно, как на рис. 2.6. При помоши этого небольшого примера я знакомлю вас с общими принципами.

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

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

>20% строк

Полное сканирование таблицы.

<0,5% строк Индексный доступ.

0,5%-20%

Необходимо рассмотреть дополнительные условия.

Если количество строк в необходимом фрагменте таблицы составляет от 0,5 % до 20 % ее объема, то выбор стратегии затрудняется. Дополнительные условия должны быть какими-то особенными, чтобы способствовать выбору индекса в случае, близком к 20 %. Также не следует выбирать полное сканирование таблицы, если объем фрагмента ближе к 0,5 %, если нет условий, диктующих выбор именно этой стратегии. Далее перечислены несколько факторов, относящихся к определенным запросам, которые должны заставить вас выбрать индексный доступ для фрагментов, чей объем приближается к 20 % от всего объема таблицы.

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

Запрос обращается к строкам, которые достаточно горячи, обеспечивая лучшее кэширование в индексированном диапазоне, чем будет выполнено при полном сканировании таблицы.

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