Почему метод диаграмм работает

в главах с 5 по 7 рассказывалось, как настраивать SQL-код при помощи диаграмм, но я не рассказывал, почему этот метод позволяет получить хорошо настроенный SQL-запрос. Обладая безграничной верой и хорошей памятью, вы, вероятно, обошлись бы без знания, как именно работает метод. Однако даже если вы слепо верите в этот метод, все равно, вероятно, иногда вам придется объяснять причину изменений в SQL-запросе. Кроме того, этот метод достаточно сложен, и понимание, почему он действительно работает, поможет вам запомнить подробности лучше, чем если вы просто попытаетесь зазубрить их.

Аргументы в пользу вложенных циклов

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

AFa Jd>1

Jm=1

Рис 8.1. Тестовый запрос с двумя таблицами

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

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

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

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

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

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

Количество строк, которое вернет запрос, равно Са х Fa х Fb, где Са - это количество строк, возвращенное из таблицы А.

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

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

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

Когда лучший фильтр принадлежит детальной таблице (в этом случае таблице А), то, если Jd = 1, в силе остаются те же аргументы. Если Jd > 1, то большие значения Jd благоприятствуют выбору соединений хэшированием. Однако, если Jd не очень велико, этот фактор обычно не может превзойти преимущества обращения к каждой таблице через самый селективный фильтр. Когда значение Jd достаточно велико, это подразумевает, что главная таблица В намного меньше детальной таблицы А, и, следовательно, будет намного лучше кэширована. А значит, стоимость обращения к ней будет меньше вне зависимости от метода соединения. Я подробно описывал этот вариант в разделе Случаи, когда нужно выбрать соединения хэшированием главы 6, поэтому не буду повторяться. Смысл в том, что даже в случаях, когда соединения хэшированием максимально улучшают стоимость данного соединения, они обычно уменьшают только сравнительно небольшую составляющую стоимости запроса - стоимость обращения к намного меньшей, хорошо кэшированной главной таблице. Чтобы достигнуть даже этого небольшого преимущества, базе данных приходится помещать присоединяемый набор строк в память или, что еще хуже, временно хранить хэшированный набор на диске.

Выбор ведущей таблицы

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

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

Что может сказать такая диаграмма с отсутствующими связями? Даже без связей и коэффициентов соединения вы можете уверенно сделать несколько заключений.