Наконец, выполним шаг 5. Минимальным из всех относительных значений количества строк является г 1, поэтому наборы s 0 и s l, которые можно соединять до того, как запрос обработает А1, должны быть присоединены в этой точке порядка соединения перед соединением с А1. Следующее минимальное значение - это г 3, поэтому поднаборы s 2 и s 3 нужно присоединить в конце порядка соединения. Единственное дополнительное ограничение - поднаборы должны присоединяться сверху вниз, чтобы таблицы внизу обрабатывались, следуя дереву соединения. Например, С1 нельзя присоединить до соединения с В2; а D7 - до соединения с В5 и Сб. Так как я пометил главные таблицы по уровням, D ниже С, С ниже В, а В ниже Д вы можете составить соединения сверху вниз, просто отсортировав каждый набор в алфавитном порядке. Например, полный порядок соединения, отвечающий всем требованиям, будет выглядеть как (СЗ. D1. ВЗ. {D2. D3}. {С2, С4}. А1. М. A3. {В1. В2. С1}. {А2. В4. В5. С5. Сб. D4. D5, D6. 07}). Фигурные скобки в данном случае обозначают поднаборы.

Совмещенные соединения и фильтрующие индексы

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

0D 0.005

О 0.001 ЗОЗ

Рис. 9.4. Простое трехстороннее соединение

Стандартный надежный план, построенный на основе эвристических правил из главы 6, начинает с О и при помощи вложенных циклов переходит к индексу по внешнему ключу 0D, который указывает на 0. После перехода к таблице 0D база данных отбрасывает 99,5 % строк, так как они не проходят через очень селективный фильтр для 0D. Затем база данных переходит к S по индексу по ее первичному ключу и отбрасывает 70 % оставшихся строк, считав таблицу S, так как они не отвечают условиям фильтра для S. В целом это неплохой план выполнения, и он может бьггь достаточно быстрым, если количество строк в 00 мало, а требования к производительности невысоки.

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

Совмещенные соединения и фильтрующие индексы 269

SELECT ... FROM Shipments S. Order Details OD. Orders 0 WHERE O.OrderJD = OD.Order ID

AND OD.ShipmentJD = S.Shipment ID

AND O.Customer ID = :1

AND OD.Product ID - :2

AND S.Shipment Date > :3

Предполагая, что у нас приблизительно 1000 покупателей, 200 продуктов, а дата, обозначенная параметром : 3, указывает точку, находящуюся приблизительно в 30 % от конца периода регистрации поставок, мы получим коэффициенты фильтрации, указанные на диаграмме. Чтобы еще более конкретизировать задачу, предположим, что количество строк в Order Detai 1 s равно 10 ООО ООО. Согласно имеющемуся детальному коэффициенту соединения Orders с Order Detai 1 s, количество строк в Orders должно быть 200 ООО, то есть ожидается считывание 200 строк из Orders, которые соединятся с 10 ООО строкам из Order Detai 1 s. После отбрасывания записей Order Detai 1 s с неподходящими значениями Product ID, текущее количество строк станет равным 50. Эти строки соединятся с 50 строками из Shipments, и после того, как старые поставки будут отброшены, останется 15 строк.

Что же в плане вьшолнения составляет большую часть стоимости? Определенно затраты на Orders и Shi pments и их индексы минимальны, так как из этих таблиц считывается немного строк. Для считывания из индекса Order Details(Order ID) потребуется сканирование 200 диапазонов индекса, каждый из которых покрывает 50 строк. Каждое сканирование диапазона потребует прохождения по дереву индекса глубиной в три уровня и, вероятно, затронет один листовой блок для каждого сканирования, что потребует примерно три операции логического ввода-вывода. В целом это потребует приблизительно 600 достаточно хорошо кэшированных операций логического ввода-вывода для индекса. Только для самой таблицы Order Detai 1 s потребуется 10 ООО операций логического ввода-вывода, и эта таблица достаточно велика, чтобы многие операции также потребовали физического ввода-вывода. Как же можно улучшить ситуацию?

Фокус заключается в том, чтобы использовать условие фильтрации для Order Details еще до обработки этой таблицы, вместе с индексом. Если вы замените индекс по Order Detai 1 s (Order ID) новым индексом по Order Detai 1 s (Order ID. Product ID), то сканирование 200 диапазонов индекса по 50 строк каждый превратятся в сканирование 200 диапазонов индекса, размером в среднем с половину строки.

ПРИМЕЧАНИЕ-

Противоположный порядок столбцов в этом индексе для данного запроса также подходит. При этом произойдет даже лучшее самокэширование, так как необходимые записи индекса будут собраны вместе в одном Product ID.

С этим новым индексом вам придется считать только 50 действительно необходимых строк из Order Details, а это 200-кратная экономия на физическом и логическом вводе-выводе для данной таблицы. Так как Order Detai 1 s - это единственный объект запроса, требующий существенных объемов ввода-вывода, то описанное мной нововведение позволит достигнуть 50-кратного увеличения производительности всего запроса, предполагая намного лучшее кэширование других, небольших объектов.

Но почему же я тянул до главы 9 с описанием такой значительной возможности оптимизации? Практически везде в этой книге я ставил целью поиск лучшего плана вьшолнения, независимо от того, какие индексы есть в базе данных на текущий момент. Однако позади этой идеальной картинки просвечивает реальность: многие индексы, предназначенные для оптимизации отдельных, редко используемых запросов, будут стоить больше, чем смогут оказать помощи. Хотя индекс, охватывающий внешний ключ и условие фильтрации, ускорит запрос в нашем примере, он замедлит любую вставку и удаление, а также многие обновления, если они будут затрагивать индексированные столбцы. Эффект, который оказывает один новый индекс на любую данную операцию вставки, минимален. Однако, если распространить его на все вставки и добавить эффект множества других пользовательских индексов, то увеличение количества индексов легко может принести больше вреда, чем пользы.

Рассмотрим еще один способ оптимизации того же запроса. Узел S, как и 0D, обрабатывается через гслюч соединения, и для него есть фильтрующее условие. Что, если создать индекс по Sh1pnients(Sh1pnnent ID. Shiprrient Date), чтобы избежать ненужных считываний из таблицы Shipments? Количество считываний из этой таблицы сократится на 70 %, но это всего лишь экономия 35 операций логического ввода-вывода и, возможно, одной или двух операций физического ввода-вывода, что, вероятно, будет недостаточно для получения заметного эффекта. В реальных запросах такие минимальные улучшения с пользовательскими индексами, комбинирующими ключи соединения и условия фильтрации, встречаются намного чаще, чем возможности для существенных улучшений.

ПРИМЕЧАНИЕ -

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

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

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

В запросах с небольшим количеством узлов время вьтолнения больше всего расходуется на доступ к единственной таблице.