Волк В. - Базы данных. Проектирование, программирование, управле. Практикум по проектированию, программированию и администрированию баз данных, включающий примеры и практические задания для самостоятельного выполнения
 Скачать 3.21 Mb.
|
|
ГЛАВА 13. ОПТИМИЗАЦИЯ ПРОЦЕДУРНЫХ ПЛАНОВ ИСПОЛНЕНИЯ SQL-ЗАПРОСОВ Повышение производительности информационных систем (ИС), активно использующих операции доступа к данным, — комплексная задача, решение которой не ограничивается оптимизацией исключительно базы данных и может затрагивать различные аспекты функционирования ИС — организационные, технические, архитектурные, программные, эксплуатационные. Важнейшей эксплуатационной характеристикой ИС, влияющей на ее производительность, является время отклика на запрос к базе данных, которое во многом зависит от реализации физической модели данных и, в частности, от правильности построения индексных структур данных. • «Почему запрос выполняется так долго?» • «Почему длительности выполнения двух практически одинаковых за- просов так сильно отличаются?» • «Почему сегодня этот запрос выполняется гораздо дольше, чем он вы- полнялся вчера?» • «Почему все так плохо даже при наличии индексов?» Для получения ответов на такие вопросы, часто задаваемые администра- тору пользователями и программистами баз данных, необходимо рассмотреть процесс формирования процедурного плана выполнения SQL-запроса, генери- руемого сервером баз данных в результате трансляции соответствующего SQL-кода. 13.1. SQL — язык программирования декларативного типа Программируя на классическом языке высокого уровня, таком, например, как Basic, Pascal или C#, мы, по существу, описываем на этом языке алгоритм решения некоторой задачи в виде последовательности операторов, то есть явно определяем процедуру обработки данных, реализация которой должна приве- сти к получению желаемого результата. Такие высокоуровневые языки отно- сятся к категории процедурных языков программирования. Процедурными являются и низкоуровневые (машинные) языки, так как машинная программа — это детальное описание алгоритма обработки данных в виде последовательности команд процессора. Из этого, в частности, следует, что компиляция исходного кода программы, написанной на процедурном языке высокого уровня, в ее машинный код — это (всего лишь!) преобразование од- ного описания процедуры обработки данных в другое описание этой же проце- дуры. В отличие от процедурных языков программирования, язык SQL является языком декларативного типа, и текст SQL-запроса к базе данных не содержит описания алгоритма получения результата, а только декларирует требования к этому результату. 23 / 24 168 Например, SQL-запрос вида Select * From T Where T.x>T.y требует выбор- ки из таблицы T только тех ее строк, в которых значение поля x больше значе- ния поля y, не описывая при этом алгоритма выполнения такой операции. 13.2. Типовая схема трансляции SQL-запроса Если SQL-запрос не содержит описания процедуры поиска данных, а в результате компиляции SQL-кода все же получается процедурный машинный код, то, очевидно, задачу выбора необходимой процедуры транслятор решает самостоятельно, без прямого участия программиста. В состав транслятора с языка SQL входит специализированный компо- нент — оптимизатор запросов, задача которого — сгенерировать оптималь- ный процедурный план выполнения запроса, то есть, фактически, «переписать» исходный непроцедурный SQL-код в эквивалентный ему код на некотором промежуточном процедурном языке. На завершающем этапе трансляции этот процедурный план будет скомпилирован в исполнимый машинный код. Процесс трансляции SQL-запроса (рис. 4.10) включает несколько после- довательных фаз его обработки. Перед тем как попасть на вход оптимизатора, исходный SQL-код подвергается предварительной обработке (на рисунке не показано), включающей его синтаксический анализ, лексическое и логическое преобразования. Рис. 4.10 Упрощенная схема трансляции SQL-запроса Фаза 1. Синтаксический анализ Стандартная для любых трансляторов фаза — синтаксический разбор (parsing) исходного кода. Синтаксический анализатор просматривает инструк- цию SELECT, разбивает ее на логические единицы (ключевые слова, выраже- ния, операторы и идентификаторы), контролирует правильность написания языковых конструкций. Фаза 2. Лексические преобразования На этой фазе проверяется корректность использования имен логических объектов (таблиц, индексов, полей таблиц), а сами эти имена заменяются на со- 24 / 24 169 ответствующие им внутренние идентификаторы (вспомним системный каталог базы данных и, в частности, таблицы SysObjects, SysColumns, SysIndexes). В результате формируется новое (все еще непроцедурное) представление запроса, синтаксически эквивалентное исходному SQL-коду. Фаза 3. Логические преобразования Дальнейшая обработка запроса связана с его логическими преобразова- ниями с целью упрощения процесса дальнейшего анализа и принятия решений, связанных с генерацией процедурных планов. При этом применяются как син- таксические, так и семантические преобразования. Синтаксические преобразования запроса выполняет специализирован- ный компонент транслятора — algebrizer (алгебраизатор), в котором входное представление запроса приводится к виду, удобному для его последующей трансформации в последовательность операций реляционной алгебры. В результате синтаксических преобразований: – производится упрощение предикатов ограничения выборки в предикаты соединений (логические выражения разделов Where и Join) путем приведения их к некоторой канонической форме; – исключается вложенность запросов, например запрос вида Select R1.a From R1 Where R1.b IN (Select R2.d From R2 Where R2.e = R1.c) приводится к виду Select R1.a From R1 Join R2 ON (R1.b = R2.d AND R1.c = R2.e); – запросы, заданные на представлениях, преобразуются путем объедине- ния кода запроса с кодом представления, например пусть создано представле- ние V(C): Create View V(C) AS Select T.C1 From T Where T.C1>6 и на базе этого представления написан следующий запрос: Select * From V Where V.C<6 План выполнения такого запроса (без предварительного логического пре- образования) состоял бы из двух фаз: 1) «материализация» представления V путем выборки из таблицы Т строк, соответствующих ограничению T.C1>6, с сохранением результатов во времен- ной таблице, например в таблице Tmp.V; 2) выборка из временной таблицы Tmp.V тех строк, которые удовлетво- ряют ограничению Tmp.V.C<6. После объединения кодов запроса и представления получится запрос вида Select T.C1 From T Where T.C1>6 AND T.C1<6, в котором раздел Where содержит тождественно ложное логическое выражение, из чего явно следует, что строить и, тем более, оптимизировать процедурный план вообще не потребуется — до- статочно возвратить в результат запроса пустое множество строк. В процессе семантических преобразований формируется новый запрос, синтаксически не эквивалентный исходному запросу, но дающий точно такой же результат. 1 / 24 170 Пусть, например, в базе данных, обслуживающей систему кадрового уче- та компании, имеются две связанные таблицы: таблица Employees, содержащая данные обо всех сотрудниках компании, в том числе размер должностного оклада сотрудника (поле Salary), и таблица Posts — справочник наименований должностей компании (поле Title). Следующий исходный SQL-запрос производит выборку всех сотрудников компании, занимающих должности начальников («Head»): Select Employees.Name From Employees Inner Join Posts ON Employees.Post_ID = Posts.Post_ID Where Posts.Title Like «Head»; План реализации такого запроса будет включать процедуру внутреннего соединения двух таблиц, например методом вложенных циклов, и процедуру фильтрации строк временной таблицы, сформированной первой процедурой. Заметим, что процедура соединения таблиц — одна из наиболее дорогостоящих процедур, даже при наличии соответствующих индексов. Пусть для поля Salary таблицы Employees задано следующее ограниче- ние целостности CONSTRAINT, соответствующее утверждению, что зарплата начальника не может быть меньше $1000: ALTER TABLE Employees ADD CONSTRAINT MaxHeadSalary CHECK( If (Select Posts.Title WHERE Posts.Post_ID = Employees.Post_ID) Like «Head» Then Employees.Salary> = $1000); С помощью такого ограничения можно, например, контролировать пра- вильность заполнения данных о заработной плате сотрудников. В этих условиях исходный запрос может быть семантически преобразо- ван в другой запрос, синтаксически отличающийся от исходного, но эквива- лентный ему по результату выполнения, и при этом гораздо более простой и «удобный» для оптимизатора, генерирующего процедурный план: Select Employees.Name From Employees Where Employees.Salary>=$1000; К тому же план исполнения такого запроса не содержит процедуры со- единения таблиц, следовательно, он будет существенно менее дорогостоящим по сравнению с процедурным планом выполнения исходного запроса. Четвертая и пятая фазы трансляции SQL-запроса реализуются непосред- ственно оптимизатором запросов, который получает непроцедурное пред- ставление запроса, прошедшее предварительную обработку на предшествую- щих фазах трансляции, и генерирует процедурный план выполнения запроса. В своей работе оптимизатор использует дополнительную статистическую ин- формацию о текущем состоянии базы данных. 2 / 24 171 Фаза 4. Генерация альтернативных планов выполнения запроса Генератор процедурных планов получает внутреннее непроцедурное представление запроса (результат его предшествующих преобразований) и за- прашивает дополнительную информацию о текущем состоянии объектов базы данных, указанных в запросе: например, данные о мощности таблиц, наличии и типах индексов, созданных в этой таблице по столбцам, затрагиваемым запро- сом. В распоряжении генератора имеется набор типовых стратегий реализации запросов по существу, набор правил, применяемых в определенных условиях. Генератор анализирует информацию о текущем состоянии объектов базы дан- ных и принимает решение о возможности использования определенных страте- гий при формировании планов. Результатом данной фазы трансляции запроса является множество аль- тернативных планов, каждый из которых представлен в виде соответствующего дерева логических операторов, описывающего на концептуальном уровне по- следовательность выполнения операций реляционной алгебры (вот где оказы- вается полезной проведенная ранее алгебраизация непроцедурного представле- ния запроса). Примеры логических операторов: – Cross Join — соединяет каждую строку из первого (верхнего) входного параметра с каждой строкой второго (нижнего) входного параметра; – Distinct — удаляет дубликаты из набора строк; – Lazy Spool — сохраняет все строки входных данных в скрытом времен- ном объекте, который хранится в системной базе данных TempDB. Фаза 5. Оценка стоимости и выбор оптимального плана На этой фазе оптимизатор решает задачу эффективной реализации логи- ческих операторов, описывающих альтернативные планы выполнения запроса. Для каждого логического оператора выбирается подходящий физический опе- ратор (или несколько физических операторов) и определяется «стоимость» их выполнения. В результате альтернативный план представляется деревом физи- ческих операторов и вычисляется его суммарная стоимость. Каждый физический оператор является объектом или процедурой, вы- полняющей соответствующую логическую операцию, например сканирование таблицы или индекса, соединение таблиц, вычисление, статистическую обра- ботку, проверку целостности данных и др. Примеры физических операторов: – Filter — просматривает входные данные и возвращает только строки, удовлетворяющие критерию фильтрации; – Nested Loops — выполняет логические операции внутреннего соедине- ния методом вложенных циклов; – Clustered Index Delete — удаляет строки из кластеризованного индекса; – Index Scan — получает все записи некластеризованного индекса, кото- рые удовлетворяют условию, указанному в предикате. 3 / 24 172 Некоторые низкоуровневые операторы (например, Index Scan) являются и логическими, и физическими. С полным перечнем операторов, используемых для представления процедурных планов, можно ознакомиться на соответству- ющем официальном ресурсе корпорации Microsoft. На рисунке 4.11 приведен пример графического представления процедур- ного плана выполнения следующего SQL-запроса: Select * From MyTable_4 Where Key0 = 4 AND Key1>50 Таблица MyTable_4 содержит около 10 000 строк, не имеет кластеризо- ванного индекса, а по полям Key0 и Key1 этой таблицы созданы некластеризо- ванные неуникальные индексы. Результирующая выборка составила 11 строк. Рис. 4.11 Пример графического представления процедурного плана Для оценки стоимости выполнения каждого физического оператора оптими- затор запроса использует соответствующую этому оператору модель стоимости. Например, для оператора Index Seek эта модель определена как сумма стоимостей операций ввода-вывода (IO_Cost = 0,003125) и обработки данных (ProcessingCost = 0,0001699), умноженная на количество операций ( = 1). Оценка стоимости операторов процедурного плана выполняется на осно- вании статистических данных, характеризующих состояние объектов, затраги- ваемых SQL-запросом. Статистические данные содержат усредненную инфор- мацию о таблицах и индексах (например, количество занимаемых страниц, сте- пень селективности предикатов выборки данных, гистограммы распределения значений полей таблиц и т. д.). MS SQL-сервер собирает статистику в фоновом режиме в соответствии со сценарием, заданным администратором базы данных. Например, процедура сбора статистики может запускаться в фиксированное время суток или после каждой модификации данных, при этом данные таблиц могут собираться как 4 / 24 173 полностью (для первых 200 строк), так и случайной выборкой части строк больших таблиц. Соответствующий инструмент среды SQL-Server Management Studio (вкладка «Статистика») позволяет в любой момент времени обновить статисти- ку или просмотреть время ее последнего обновления. Просмотреть текущее состояние статистики индекса Ind таблицы Т1 можно командой DBCC show_statistics(T1,Ind). Тот из альтернативных планов, стоимость которого оказалась минималь- ной, получает статус предполагаемого плана (estimated execution plan) и запи- сывается в хранилище процедурных планов (PlanCache), где временно хранится вместе с планами других запросов в прекомпилированном виде (в виде дерева физических операторов) для последующего извлечения и многократного ис- пользования. На этом работа оптимизатора запроса завершается и управление переда- ется подсистеме выполнения запросов (Storage Engine) сервера баз данных. 13.3. Исполнение процедурного плана выполнения запроса Предполагаемые планы выполнения запросов, записанные в хранилище, не хранятся там вечно — сервер ведет статистику использования сохраненных планов и регулярно удаляет из хранилища редко используемые планы. При поступлении на обработку очередного SQL-запроса производится проверка наличия в хранилище предполагаемого плана его выполнения. Если нужного плана в хранилище нет, запускается описанный выше пол- ный процесс трансляции исходного SQL-кода (фазы с 1-й по 5-ю), по заверше- нии которого сформированный оптимизатором запросов предварительный план записывается в хранилище. При наличии соответствующего предварительного плана он извлекается из хранилища, проверяется возможность его выполнения в текущем состоянии базы данных, и если план осуществим, он получает статус действительного (actual) плана, который затем компилируется в машинный код и исполняется. Если принимается решение о невозможности реализации ранее сохранен- ного предполагаемого плана, повторно запускается процесс оптимизации (4-я и 5-я фазы трансляции запроса — генерация альтернативных процедурных планов, их оценивание и выбор оптимального предварительного плана). По завершении процесса оптимизации старый план заменяется в хранилище новым планом. Во многих случаях предполагаемый и действительный планы будут сов- падать. Типичные причины нереализуемости сохраненного ранее предвари- тельного плана: – статистика, на основе которой был сформирован предполагаемый план выполнения запроса, к текущему моменту либо устарела («out of date»), либо была обновлена; – логические объекты базы данных, затрагиваемые запросом, были моди- фицированы после создания процедурного плана (например, были созданы но- 5 / 24 174 вые индексы, изменены или удалены старые, удалены временные таблицы, на которые ссылается запрос, и т. д.). 13.4. Средства анализа и визуализации процедурных планов На завершающем этапе процесса отладки SQL-запроса (хранимого пред- ставления, процедуры или пакета, состоящего из множества таких объектов в любых их комбинациях) можно просмотреть не только результат его выполне- ния, но также и соответствующий процедурный план, сгенерированный опти- мизатором запросов. При этом предусмотрена возможность визуализации как предполагаемого плана, извлекаемого из хранилища, так и действительного плана, актуального в текущих условиях реализации запроса. Язык TransactSQL содержит инструкции группы SET, позволяющие со- хранить в текстовом формате или в формате XML-документа как предполагае- мый, так и действительный план выполнения запроса (табл. 4.9). Инструкция SET SHOWPLAN_... ON/OFF включает/выключает соответ- ствующий режим сохранения предварительного плана, извлекаемого из храни- лища, блокируя при этом исполнение запроса. Инструкция SET STATISTICS_... ON/OFF включает/выключает режим отоб- ражения действительного плана, не препятствуя исполнению запроса. Таблица 4.9 Операторы управления отображением процедурных планов SET SHOWPLAN_XML Возвращает сведения о приблизительном плане выполнения в виде XML-документа SET SHOWPLAN_TEXT Возвращает сведения о приблизительном плане выполнения SET SHOWPLAN_ALL Возвращает полную информацию о приблизительном плане вы- полнения запроса SET STATISTICS XML Возвращает сведения о действительном плане выполнения в виде XML-документа SET STATISTICS PROFILE Возвращает полную информацию о действительном плане выпол- нения запроса SET STATISTICS IO Отображает сведения о дисковой активности во время выполнения запроса SET STATISTICS TIME Отображает время (в миллисекундах), которое потребовалось для синтаксического анализа, компиляции и выполнения запроса На рисунке 4.12 приведен пример отображения действительного плана выполнения следующего SQL-запроса: SET STATISTICS XML ON SET STATISTICS PROFILE ON SET STATISTICS IO ON SET STATISTICS TIME ON Select * From T1 Inner Join T2 ON T1.c2 > T2.c2 Where T2.c3<500 OR T1.c3<500; 6 / 24 175 Такой формат табличного отображения плана запроса достаточно инфор- мативен: каждая строка представляет один физический оператор и содержит код этого оператора, информацию о времени его выполнения и количестве об- работанных строк. Примечание. Приведенный пример — результат реализации запроса в MS SQL-Server 2005. В более поздних версиях возможность отображения планов в текстовом формате не предусмотрена, хотя соответствующие команды оставлены для совместимости со старшими версиями. В более поздних версиях в результате применения любой из команд, приведенных в таблице 4.9, план за- проса будет сохранен в XML-формате, однако утилита просмотра такого плана отобразит его на экране в графическом формате, как показано на ри- сунках 4.11 и 4.14. Рис. 4.12 Пример табличного представления процедурного плана Среда SQL-Server Management Studio предоставляет возможность графи- ческого представления предварительного и действительного планов выполне- ния запроса в виде дерева физических процедурных операторов. Для включения/отключения режимов графического отображения планов на экранной панели имеются соответствующие кнопки (рис. 4.13). Рис. 4.13 Режимы просмотра графического плана: 1 — предполагаемый план; 2 — действительный план. 7 / 24 176 Следует помнить, что при включении режима отображения предвари- тельного плана (так же как и в случае с применением команды SET SHOWPLAN_ ...ON) выполнение запроса блокируется. На рисунке 4.14 приведен пример графического отображения действи- тельного плана выполнения запроса из приведенного выше примера. Рис. 4.14 Пример графического представления плана запроса Каждый оператор на схеме плана представляется соответствующим гра- фическим символом (табл. 4.10), под которым отображается код этого операто- ра. При наведении курсора на оператор на экране появляется всплывающая подсказка (ToolTips), как это показано на рисунке 4.15. Подсказка содержит имена логического и физического операторов, количество строк, возвращаемых оператором (предполагаемое и фактическое), стоимость операции, количество исполнений и другую информацию, полезную для анализа процедурного плана. Рис. 4.15 Всплывающая подсказка 8 / 24 177 Читать графические планы следует справа налево, в соответствии с направлением стрелок. Толщина стрелки также информативна — она пропор- циональна количеству передаваемых строк. При наведении курсора на стрелку на экране также появляется соответствующая подсказка. С полным перечнем низкоуровневых операторов процедурных планов можно ознакомиться на официальном ресурсе разработчика. Таблица 4.10 Графическое представление операторов процедурных планов Table Scan Сканирует таблицу (кучу) Clustered Index Scan Index Scan Сканирует кластеризованный индекс/ некластеризованный индекс Clustered Index Seek Index Seek Производит поиск по кластеризованному индексу/ некластеризованному индексу Key Lookup Производит поиск закладок в таблице с кластери- зованным индексом RID Lookup Производит поиск закладки в куче по заданному идентификатору строки Table Insert Clustered Index Insert Index Insert Вставляет строки в таблицу/ кластеризованный индекс/ некластеризованный индекс Filter Просматривает входные данные и возвращает строки, удовлетворяющие критерию фильтрации Nested Loops Соединяет таблицы по методу вложенных циклов 9 / 24 |