Выбор наилучшего плана выполнения

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

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

Как соединяются таблицы в плане выполнения?

В каком порядке соединяются таблицы в плане выполнения?

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

Надежные планы выполнения

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

пределений данных или просто в другом экземпляре базы данных, обрабатываемом тем же приложением. Надежные планы также терпимы к неточностям и ошибкам; с надежным планом небольшая ошибка в вычисленной селективности фильтра может привести к не оптимальному, но не к чудовишному плану. Когда вы используете надежные планы вьшолнения, то практически всегда можете решить проблему настройки SQL раз и навсегда, вместо того чтобы повторно решать ее каждый раз, когда со временем или на другом наборе покупателей вам встречаются другие распределения данных.

ПРИМЕЧАНИЕ

Неточности и ошибки неизбежны при решении проблемы оптимизации. Например, даже имея прекрасную информацию на время разбора (когда база данных создает план выполнения), условие наподобие Last Name = : LName обладает неточной селективностью, зависящей от реального значения, которое во время выполнения будет привязано к параметру : LName. Неизбежность появления неточностей и ошибок делает надежность планов выполнения особенно важной.

Надежные планы вьшолнения обычно обладают несколькими свойствами.

Их стоимость выполнения пропорциональна количеству возвращенных строк.

Им праетически не требуется пространство в памяти для сортировки или хэширования.

Они не требуют изменений по мере увеличения размеров таблиц.

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

Они особенно хороши, когда оказывается, что запрос возвращает меньше строк, чем вы ожидали (когда фильтры более селестивны, чем кажется).

ПРИМЕЧАНИЕ

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

Требование надежности описывается несколькими условиями.

Обращаться к первой таблице через селеетивный индекс.

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

ПРИМЕЧАНИЕ

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

Переходить вниз к первичным ключам перед тем, как переходить вверх к неуникальным внешним ключам.

ПРИМЕЧАНИЕ

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

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

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

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

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

Обычный эвристический порядок соединения

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

1. Сначала обработать таблицу с наилучшим (ближайшим к нулю) коэффициентом фильтрации.

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

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

Сейчас эти шаги могут быть еще не очень понятны. Не беспокойтесь. Далее в этой главе я подробно объясню каждый шаг. Эвристические методы обычно легче продемонстрировать, чем объяснить.

Если ведущая таблица оказалась на несколько уровней ниже верхней детальной таблицы {корневой таблицы, поскольку она находится в корне дерева), то вам потребуется возвращаться к шагу 2 после каждого перемещения вверх по дереву в шаге 3. Я описываю некоторые тонкости для редких специальных случаев, но в целом поиск оптимального плана очень прост, если у вас уже есть диаграмма запроса.

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