Лекции по АБД. Лекция 6. Оптимизатор запросов
 Скачать 419.92 Kb.
|
|
Оптимизатор запросов Как в запросе обеспечить доступ к необходимым данным и обработку этих данных с максимальной эффективностью? Компонент системы базы данных, от- ветственный за такую деятельность, называется оптимизатором запросов. Задачей оптимизатора является рассмотрение множества возможных стра- тегий выполнения поиска требуемых в запросе данных и выбор наиболее эффек- тивной стратегии. Выбранная стратегия называется планом выполнения запроса. Оптимизатор принимает свои решения с учетом следующих факторов: размеры и количество таблиц в запросе, наличие индексов и вид логических операции (and, or, not) используются в предложении where. Оптимальный план выполнения запроса - это такая последовательность применения операторов реляционной алгебры к исходным и промежуточным от- ношениям, которое для конкретного текущего состояния БД (её структуры и наполнения) может быть выполнено с минимальным использованием вычисли- тельных ресурсов. Фазы обработки запроса. Задачей оптимизатора является разработка наиболее эффективного плана выполнения для заданного запроса. Эта задача ре- шается с использованием следующих четырех фаз (рис. 1). Обычно рассматриваются запросы на выборку данных (select). Оптимиза- тор запросов также используется для операторов insert, update и delete. Оператор insert может содержать подзапрос, а операторы update и delete часто содержат предложение where, которое должно быть обработано. Синтаксический разбор (parsing). Проверяется синтаксис запроса, сам за- прос преобразуется в дерево. После этого выполняется проверка всех объектов базы данных, на которые в запросе приводятся ссылки. Например, проверяется существование столбцов, на которые ссылается запрос. После процесса проверки формируется окончательное дерево запроса. Рис. 1. Фазы выполнения запроса Обычно рассматриваются запросы на выборку данных (select). Оптимиза- тор запросов также используется для операторов insert, update и delete. Оператор insert может содержать подзапрос, а операторы update и delete часто содержат предложение where, которое должно быть обработано. Синтаксический разбор (parsing). Проверяется синтаксис запроса, сам за- прос преобразуется в дерево. После этого выполняется проверка всех объектов базы данных, на которые в запросе приводятся ссылки. Например, проверяется существование столбцов, на которые ссылается запрос. После процесса проверки формируется окончательное дерево запроса. Компиляция запроса (query compilation). Дерево запроса компилируются оптимизатором запросов. Оптимизация запроса (query optimization). В качестве входных данных оп- тимизатор запросов получает скомпилированное дерево запроса, которое было сгенерировано на предыдущем шаге, и рассматривает различные стратегии до- ступа, прежде чем принять решение, как следует обрабатывать данный запрос. Для поиска наиболее эффективного плана выполнения запроса оптимизатор за- просов вначале выполняет анализ запроса, в процессе которого он отыскивает аргументы поиска и операции соединения. Затем оптимизатор выбирает ин- дексы, которые будут использоваться. Под конец, если существуют операции со- единения, оптимизатор выбирает порядок соединений и выбирает одну из техник обработки соединений. Выполнение запроса (query execution). После того как будет сгенерирован план выполнения, он сохраняется и запрос выполняется. Для некоторых операторов исключается синтаксический разбор и оптими- зация, если Database Engine знает, что существует только один жизнеспособный план. Такой процесс называется тривиальным планом оптимизации. Примером оператора, для которого может быть использован тривиальный план оптимиза- ции, может служить оператор insert в его простой форме (добавить одну строку). Фаза оптимизации запроса может быть разделена на следующие этапы: анализ запроса, выбор индекса, выбор порядка операций соединения и выбор технологии для обработки операций соединения. Анализ запроса. В процессе анализа запроса оптимизатор проверяет запрос на аргументы поиска, использование оператора or и существование критериев соединения - именно в этом порядке. Поскольку использование оператора or и существование критериев соединения не требуют объяснения, здесь обсужда- ются только аргументы поиска. Аргумент поиска является частью запроса, которая ограничивает проме- жуточный результирующий набор запроса. Основным назначением аргументов поиска является то, что они позволяют использовать существующие индексы применительно к конкретному выражению. Примеры аргументов поиска: emp_fname = 'Moser'; salary >= 50000; emp_fname = 'Moser' AND salary >= 50000. Существует несколько форм выражений, которые не могут быть использо- ваны оптимизатором в качестве аргументов поиска. К первой группе принадле- жат выражения с оператором отрицания (not, <>). Если используется арифмети- ческое выражение в левой части оператора, то это выражение не может быть ис- пользовано в качестве аргумента поиска. Примеры выражений, которые не явля- ются аргументами поиска: NOT IN (‘dl’, ’d2’); emp_no <> 0.9031; budget * 0.59 > 55000. Основным недостатком выражений, которые не могут быть использованы в качестве аргументов поиска, является то, что оптимизатор не может использо- вать существующие индексы в отношении выражения, чтобы повысить произво- дительность соответствующего запроса. Иными словами, в этом случае един- ственным способом доступа к данным может быть только последовательное ска- нирование таблицы. Выбор индекса. Идентификация аргументов поиска позволяет оптимиза- тору принять решение о том, можно ли использовать один или более существу- ющих индексов. На этой фазе оптимизатор проверяет каждый аргумент поиска на предмет, существуют ли подходящие индексы для соответствующего выра- жения. Если такой индекс существует, оптимизатор принимает решение, исполь- зовать его или нет. Это решение зависит от селективности соответствующего вы- ражения. Селективность выражения определяется как отношение количества строк, удовлетворяющих условию, к общему количеству строк в таблице. Оптимизатор проверяет селективность выражения с индексированным столбцом, используя статистические данные, которые создаются для распреде- ления значений в столбце. Оптимизатор запросов учитывает эту информацию для определения оптимального плана запроса, оценивая стоимость использова- ния индекса для выполнения запроса. Database Engine автоматически создает (для индекса и столбца) статистические данные, если для базы данных активиро- вана опция AUTO_CREATE_ STATISTICS. Селективность выражения с индексированным столбцом. Оптимизатор использует индексы для повышения скорости выполнения запроса. Когда идет обращение к таблице, у которой нет индексов, или если оптимизатор принимает решение не использовать существующий индекс, система выполняет последова- тельное сканирование таблицы. В процессе сканирования таблицы Database Engine последовательно читает страницы данных таблицы для поиска строк, ко- торые принадлежат результирующему набору данных. Доступ по индексу является методом доступа, при котором СУБД читает и записывает страницы данных с использованием индекса. Так как доступ по ин- дексу значительно сокращает количество операций ввода/вывода, он обычно вы- полняется гораздо быстрее, чем сканирование таблицы. Database Engine использует некластеризованный индекс для поиска дан- ных одним из двух способов. Если есть куча (таблица без кластеризованного ин- декса), то система вначале исследует структуру некластеризованного индекса, а затем отыскивает строку, используя идентификатор этой строки. Однако если анализируется кластеризованная таблица, то проход по некластеризованной ин- дексной структуре выполняется вслед за проходом по индексной структуре кла- стеризованного индекса таблицы. С другой стороны, использование кластеризо- ванного индекса для поиска данных всегда уникально: Database Engine начинает поиск с корневого узла соответствующего В-дерева и обычно после трех или че- тырех операций чтения достигает узла листа, где хранятся данные. По этой при- чине проход по индексной структуре кластеризованного индекса почти всегда выполняется значительно быстрее, чем проход по индексной структуре соответ- ствующего некластеризованного индекса. Из вышеприведенных рассуждений ясно, что ответ на вопрос, какой метод доступа (сканирование индекса или сканирование таблицы) является наиболее быстрым, не является простым и зависит от селективности и типа индекса. Тесты показывают, что сканирование таблицы часто выполняется быстрее, чем доступ к некластеризованным индексам, когда выбирается, по меньшей мере, 10% строк. В этом случае решение оптимизатора, когда переключаться от доступа к некластеризованному индексу к сканированию таблицы, может быть некорректным. Если известно, что оптимизатор собирается выполнять сканиро- вание таблицы без особых оснований, то можно использовать в запросе под- сказку index для изменения этого решения. По некоторым причинам кластеризованный индекс обычно выполняется быстрее, чем некластеризованный индекс. Когда система сканирует кластеризо- ванный индекс, нет необходимости покидать структуру В-дерева для сканирова- ния страниц данных, потому что такие страницы уже присутствуют на уровне листьев этого дерева. Некластеризованный индекс также требует больше опера- ций ввода/вывода, чем соответствующий кластеризованный индекс. Некластери- зованному индексу нужно читать страницы данных после просмотра В-дерева либо, если существует кластеризованный индекс для другого столбца (столбцов) таблицы, некластеризованному индексу нужно читать структуру В-дерева кла- стеризованного индекса. Поэтому ожидают, что кластеризованный индекс будет выполняться зна- чительно быстрее, чем сканирование таблицы, даже если его селективность до- вольно плохая (т. е. процент возвращенных строк велик, потому что запрос воз- вращает много строк). Тесты показывают, когда селективность выражения 75% или меньше, доступ к кластеризованному индексу, как правило, выполняется быстрее, чем сканирование таблицы. Статистические данные индекса обычно создаются, когда создается ин- декс для конкретного столбца (столбцов). Создание статистических данных ин- декса означает, что Database Engine создает гистограмму, основанную более чем на 200 значениях столбца. По этой причине создается более 199 интервалов. Ги- стограмма указывает, помимо других вещей, как много строк в точности соот- ветствует каждому интервалу, среднее количество строк с различными значени- ями внутри интервала и плотность значений. Статистические данные индекса всегда создаются для одного столбца. Если индекс является составным индексом (состоит из нескольких столбцов), то система генерирует статистические данные для первого столбца в этом индексе. Если требуется явно создать статистические данные индекса, то можно ис- пользовать системную процедуру sp_createstats или Management Studio. Системная процедура sp_createstats создает статистические данные по одиночному столбцу для всех столбцов всех пользовательских таблиц текущей базы данных. Новые статистические данные имеют те же имена, что и имена столбцов, для которых они создаются. Чтобы использовать Management Studio для создания статистических дан- ных индекса нужно последовательно развернуть сервер, раскрыть папку Databases, раскрыть базу данных, раскрыть папку Tables, раскрыть таблицу, щелкнуть правой кнопкой мыши по узлу Statistics и выбрать пункт New Statistics. В диалоговом окне New Statistics on Table задают имя статистических данных. Как только данные в столбце изменяются, статистические данные индекса становятся устаревшими. Устаревшие статистические данные могут значительно повлиять на производительность при выполнении запроса. Database Engine мо- жет автоматически обновлять статистические данные индекса, если активизиро- вана опция базы данных auto_update_statistics (установлена в on). В этом случае любые статистические данные, требуемые для оптимизации запроса, будут авто- матически обновляться в процессе оптимизации запроса. Также существует еще одна опция базы данных, auto_create_statistics, ко- торая создает любые отсутствующие статистические данные, требуемые для оп- тимизации запроса. Обе опции могут быть активированы (или деактивированы) при использовании оператора alter database или SQL Server Management Studio. Статистические данные столбца. Database Engine создает статистиче- ские данные для каждого существующего индекса. Система также может созда- вать статистические данные и для неиндексированных столбцов. Эти статисти- ческие данные называются статистическими данными столбца. Вместе со стати- стическими данными индекса статистические данные столбца используются для оптимизации плана выполнения запроса. Database Engine создает статистические данные даже для неиндексирован- ного столбца, который является частью условия в предложении where. Суще- ствует несколько ситуаций, при которых наличие статистических данных столбца может помочь оптимизатору принять правильное решение. Одной из них является ситуация, когда есть составной индекс из двух или более столбцов. Для такого индекса система генерирует статистические данные только для пер- вого столбца индекса. Существование статистических данных столбца для вто- рого столбца (и для всех других столбцов) составного индекса может помочь оп- тимизатору выбрать оптимальный план выполнения. Database Engine поддерживает два представления просмотра каталогов, используемых для статистических данных столбца - sys.stats и sys.stats_coluinns. Эти представления также могут быть использованы для редактирования инфор- мации, связанной со статистическими данными индекса. Представление sys.stats содержит одну строку для каждого статистиче- ского данного таблицы или представления. Помимо столбца nаmе, который за- дает имя статистических данных, это представление имеет два других столбца: auto_created - статистические данные созданы оптимизатором запросов и user_created - статистические данные явно созданы пользователем. Представление sys.stats_coluinns содержит дополнительную информацию относительно столбцов, которые являются частью представления sys.stats. Для определения этой дополнительной информации следует выполнить соединение этих двух представлений. Выбор порядка соединения. Обычно порядок, в котором две или более со- единяемые таблицы записываются в предложении from оператора select, не ока- зывает влияния на решение, принимаемое оптимизатором относительно порядка их обработки. Множество различных факторов влияет на решение оптимизатора, касающееся того, к какой таблице в первую очередь будет выполняться обраще- ние. Можно повлиять на выбор порядка соединения, используя FORCE ORDER. Операция соединения является операцией, больше всего забирающей время при обработке запроса. Database Engine поддерживает следующие три раз- личные техники обработки соединения, так что оптимизатор может выбрать одну из них в зависимости от статистических данных в каждой из таблиц: вло- женные циклы, слияние соединения и хеширование соединения. Использование вложенных циклов является техникой обработки, которая работает как "грубая сила". Другими словами, для каждой строки внешней таб- лицы отыскивается и сравнивается каждая строка из внутренней таблицы. Сле- дующий псевдокод демонстрирует эту технику для двух таблиц А и В. for each row in the outer table A do: read the row for each row in the inner table В do: read the row if A. join_column = B. join_column then accept the row and add it to the resulting set end if end for end for Здесь для каждой строки, выбранной из внешней таблицы (таблица А), осу- ществляется доступ ко всем строкам внутренней таблицы (таблица В). После этого выполняется сравнение значений, и строка добавляется в результирующий набор, если значения в обоих столбцах равны. Метод вложенных циклов является очень медленным, если не существует индекса для одного из соединяемых столбцов. При отсутствии индекса Database Engine должен выполнять сканирование внешней таблицы один раз, а внутрен- ней n раз, где n - количество выбранных строк внешней таблицы. По этой при- чине оптимизатор запросов обычно выбирает этот метод тогда, когда соединяе- мый столбец во внутренней таблице индексирован, так что для внутренней таб- лицы не нужно выполнять сканирование для каждой строки внешней таблицы. Техника слияния соединения предоставляет рентабельную альтернативу создания индекса вложенным циклам. Строки соединяемых таблиц должны быть физически упорядочены с использованием значений столбца соединения. Обе таблицы затем сканируются в порядке столбцов соединения, отыскивая соответ- ствующие строки с теми же значениями столбцов соединения. Техника выполнения слияния соединения будет иметь высокие накладные расходы, если строки в обеих таблицах неотсортированы. Однако этот метод яв- ляется предпочтительным, когда для значений обоих столбцов соединения вы- полнена предварительная сортировка. Это всегда тот случай, когда оба соединя- емых столбца являются первичными ключами в соответствующих таблицах, по- тому что Database Engine по умолчанию создает кластеризованный индекс для первичного ключа таблицы. Техника хеширования соединения обычно используется, когда не суще- ствует никаких индексов для соединяемых столбцов. В случае техники хеширо- вания соединения обе таблицы, которые должны быть соединены, рассматрива- ются как два потока ввода: компонуемый ввод и контрольный ввод. Наименьшая таблица обычно представляет компонуемый ввод. Этот процесс работает следу- ющим образом. 1. Значение соединяемого столбца строки из компонуемого ввода со- храняется в хешированном сегменте памяти в зависимости от количества, полу- ченного от алгоритма хеширования. 2. Как только все строки из компонуемого ввода будут обработаны, начинается обработка строк из контрольного ввода. 3. Каждое значение соединяемого столбца строки из контрольного ввода обрабатывается с использованием того же алгоритма хеширования. 4. Отыскиваются соответствующие строки из хешированного сегмента памяти и используются для создания результирующего набора. Техника хеширования соединения не требует никакого индекса. Поэтому данный метод хорошо применим в первую очередь для тех запросов, где не пред- полагается наличие индексов. При этом если оптимизатор использует в работе данную технику, то это может служить подсказкой о том, что нужно создать до- полнительные индексы для одного или более соединяемых столбцов. Инструменты для редактирования стратегии оптимизатора. Database Engine поддерживает несколько инструментов, которые позволяют редактиро- вать конкретные действия оптимизатора запросов, а именно: • оператор set - для отображения в виде текста или в формате XML планов выполнения; • Management Studio - для отображения планов выполнения в графиче- ском виде; • представления динамического управления (Dynamic management views, DMV) и функции; • SQL Server Profiler. Оператор SET. Для понимания различных опций оператора set нужно знать, что существуют три различные формы, в которых может отображаться план выполнения запроса: текстовая форма, использование XML, и графическая форма. Первые две формы используют оператор set. Результат текстовой формы плана выполнения возвращается в форме строк. Database Engine использует вертикальную полосу для отображения зави- симостей между существующими операциями. Текстовая форма планов выпол- нения может отображаться с использованием двух опций оператора set – это SHOWPLAN_TEXT и SHOWPLAN_ALL. Пользователи, выполняющие запрос, могут отображать текстовый план выполнения запроса при помощи активации значения ON до того, как они введут соответствующий оператор select. На рис. 2 пример использование опции SHOWPLAN_TEXT. Рис. 2. Пример выполнения SET SHOWPLAN_TEXT ON Как только активируетcя опцию SET SHOWPLAN_TEXT ON, все последу- ющие операторы T-SQL не будут выполняться, пока не деактивируется эта оп- цию с помощью OFF. Оператор SET имеет много других опций, которые используются по отно- шению к блокировке, транзакции и операторам даты-времени. В связи со стати- стическими данными Database Engine поддерживает следующие три опции опе- ратора SET: STATISTICS IO, STATISTICS TIME и STATISTICS PROFILE. В случае задания опции STATISTICS IO система отображает статистиче- скую информацию, связанную со степенью активности по отношению к диску, сгенерированную запросом - например, количество операций ввода/вывода, об- работанных в запросе. При задании опции STATISTICS TIME система отображает время обработки, оптимизации и выполнения запроса. На рис. 3 пример использование опции STATISTICS TIME. Графический план выполнения является лучшим способом отображения плана выполнения запроса. Эта форма отображения использует пиктограммы для представления операторов в плане запроса. В качестве примера того, как графические планы выполнения могут быть запущены и как они выглядят, приведена иллюстрация на рис. 4. Рис. 3. Пример выполнения STATISTICS TIME Для отображения плана выполнения в графической форме следует набрать запрос в окне запросов утилиты SQL Server Management Studio и щелкнуть мы- шью по кнопке Показать предполагаемый план выполнения в панели инструмен- тов Management Studio. Существует одна пиктограмма для каждого оператора в плане выполнения. Если переместить мышь на одну из этих пиктограмм, то появится детальная ин- формация по этому оператору, включая предполагаемые затраты на операции ввода/вывода и загрузку центрального процессора, ожидаемое количество строк и их размер, затраты на оператор. Стрелки между пиктограммами представляют потоки данных (можно щелкнуть мышью по стрелке, в этом случае будет отоб- ражаться такая информация, как ожидаемое количество строк и ожидаемый раз- мер строки). Рис. 4. Пример плана выполнения запроса set statistics time on go USE Моя_БД2; SELECT С.Код_Сотрудника, С.Код_должности, С.ФИО, Д.Код_должности, Д.Должность FROM dbo.Сотрудники_ТОГУ AS С JOIN dbo.Должности AS Д ON С.Код_должности = Д.Код_должности; go set statistics time off |