Отсутствующие индексы 271

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

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

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

Отсутствующие индексы

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

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

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

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

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

Как часто конечные пользователи встречаются с задержками в работе приложения за неделю?

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

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

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

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

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

ВНИМАНИЕ

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

А1 А2

Рис. 9.5. Неотфильтрованное трехстороннее соединение

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

Неразрешимые проблемы

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

Соединения, не прошедшие фильтрацию

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

Как же оптимизировать такой запрос, если невозможно руководствоваться селективностью фильтров? Для планов с вложенными циклами едва ли играет роль выбранный порядок соединения, если, конечно, вы придерживаетесь дерева соединения. Однако для этих запросов лучше всего подходят соединения хэшированием, или, если их нельзя применить, то соединения методом сортировки слиянием. Предполагая, что можно выполнять соединения хэшированием, база данных должна считать все три таблицы методом полного сканирования и хэши-ровать меньшие таблицы, А1 и А2, по возможности кэшируя результат в памяти. Затем при последнем проходе через самую большую таблицу, М, каждая хэширо-ванная строка быстро сопоставляется с подходящими хэшированными в памяти строками таблиц А1 и А2. Стоимость запроса в идеальном случае приблизительно равна стоимости трех полных сканирований таблиц. База данных просто не может отработать лучше, даже теоретически, если предполагать, что требуются все строки из всех трех таблиц. Чаще всего стоимостные оптимизаторы хорошо справляются с задачей поиска оптимальных планов для таких запросов без ручной помощи.

5Q/\100