Оптимизация SQL запросов в реляционных СУБД

Размещено на http://www.allbest.ru/

Федеральное агентство образования РФ

Государственное образовательное учреждение

Высшего профессионального образования

Владимирский государственный университет

Кафедра вычислительной техники

Пояснительная записка

к курсовой работе

дисциплина

«Распределённые СУБД»

Оптимизация SQL запросов в реляционных СУБД

Выполнил

ст. гр. ИВТ - 102

Голубев Михаил

Принял

Трофимов М. А.

Владимир 2006г.

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

Введение

Обычно, говоря про оптимизацию в реляционных СУБД, имеют в виду аспект оптимизации запросов, т.е. такой способ выполнения запросов, когда по начальному представлению запроса путем его синтаксических и семантических преобразований вырабатывается процедурный план выполнения запроса, наиболее оптимальный при существующих в базе данных управляющих структурах. Соответствующие преобразования начального представления запроса выполняются специальным компонентом СУБД - оптимизатором, и оптимальность производимого им плана запроса носит достаточно условный характер: план оптимален в соответствии с критериями, заложенными в оптимизатор; при этом, конечно, возможны отклонения от реальной оптимальности.

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

Можно рассматривать оптимизацию и в более широком смысле. Оптимизатор запросов выбирает наиболее оптимальный способ выполнения запроса на основе известных в оптимизаторе стратегий выполнения элементарных составляющих запроса и способов композиции более сложных стратегий на основе элементарных. Тем самым, пространство поиска оптимального плана выполнения запроса ограничено заранее фиксированными элементарными стратегиями. Поэтому существенным направлением исследований, непосредственно примыкающим к вопросам оптимизации, является поиск новых, более эффективных элементарных стратегий. В контексте реляционных СУБД это более всего относится к разработке эффективных алгоритмов выполнения реляционной операции соединения наиболее накладной реляционной операции.

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

1. Проблемы оптимизации запросов

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

1.1 Запросы

Запрос - это языковое выражение, которое описывает данные, подлежащие выборке из базы данных. В контексте оптимизации запросов часто предполагается, что запросы выражаются в манере, основанной на содержании, (в большинстве случаев ориентированной на множества, что дает оптимизатору достаточную возможность выбора между возможными процедурами вычисления). Запросы используются в нескольких окружениях. Наиболее очевидно приложение, в котором поступают непосредственные запросы конечного пользователя, нуждающегося в информации о структуре или содержимом базы данных. Если потребности пользователей ограничены набором стандартных запросов, они могут оптимизироваться вручную путем программирования соответствующих процедур поиска и ограничения пользовательского ввода форматом меню. Однако, если требуется задавать непредвиденные запросы с использованием языка запросов общего назначения, становится необходимой система автоматической оптимизации запросов. Второе применение запросов происходит в транзакциях, которые изменяют хранимые данные на основе их текущих значений (например, "повысить зарплату всем доцентам на 10%"). Наконец, выражения, подобные запросам, могут использоваться внутри СУБД, например, для проверки прав доступа, поддержки ограничений целостности и корректной синхронизации параллельного доступа.

1.2 Цели оптимизации

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

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

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

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

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

- Стоимость вычислений: Стоимость (или время) использования ЦП.

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

Стоимость доступа к вторичной памяти (обычно измеряемая числом обращения к страницам) и стоимость использования ЦП (часто измеряемая числом сравнений, которые требуется произвести). В основе большинства методов, разработанных для сокращения этой стоимости, лежит ряд общих идей:

1) избегать дублирования усилий;

2) использовать стандартизованные компоненты;

3) заглядывать вперед, чтобы избегать лишних операций;

4) выбирать наиболее дешевые способы выполнения элементарных операций;

5) выстраивать их последовательность оптимальным образом.

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

оптимизация запрос нормализация таблица

2 Оптимизация запросов. Путь обработки запроса в реляционной СУБД

Прежде, чем перейти к рассмотрению конкретных проблем и решений в области оптимизации запросов, рассмотрим типичный для современных реляционных СУБД путь обработки запроса, поступившего в СУБД на языке запросов SQL. Обработка запроса, поступившего в систему представленным на некотором языке запросов, состоит из этапов или фаз. На первой фазе запрос, представленный на языке запросов, подвергается лексическому и синтаксическому анализу. При этом вырабатывается его внутреннее представление, отражающее структуру запроса и содержащее информацию, которая характеризует объекты базы данных, упомянутые в запросе (отношения, поля и константы). Информация о хранимых в базе данных объектах выбирается из каталогов базы данных. Внутреннее представление запроса используется и преобразуется на следующих стадиях обработки запроса. Существенным является выбор внутреннего представления, которое должно быть достаточно удобным для последующих оптимизационных преобразований.

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

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

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

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

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

2.1 Логическая оптимизация запросов

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

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

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

В частности, существуют подходы, связанные с преобразованием к алгебраической форме запросов на языке SQL. Можно выявить две основные побудительные причины преобразований запросов на SQL к алгебраической форме. Первой причиной может быть стремление к использованию реляционной алгебры в качестве унифицированного внутреннего интерфейса реляционной СУБД. Особенно распространен такой подход при использовании специализированных машин баз данных, на основе которых реализуются различные интерфейсы доступа к базам данных. Тогда, естественно, интерфейс машины баз данных должен быть унифицирован (например, быть алгебраическим), а все остальные интерфейсы, включая интерфейс на основе SQL, приводятся к алгебраическому.

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

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

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

Предикаты, допустимые в запросах языка SQL, можно разбить на следующие четыре группы:

1) Простые предикаты. Это предикаты вида Ri.Ck op X, где X константа или список констант, и op - оператор скалярного сравнения (=, !=, >, >=, <, <=) или оператор проверки вхождения во множество (IS IN, IS NOT IN).

2) Предикаты со вложенными подзапросами. Это предикаты вида Ri.Ck op Q, где Q - блок запроса^, а op может быть таким же, как для простых предикатов. Предикат может также иметь вид Q op Ri.Ck. В этом случае оператор принадлежности ко множеству заменяется на CONTAINS или DOES NOT CONTAIN. Очевидно, что эти две формы симметричны, так что достаточно рассматривать только одну. (В соответствии с принятыми при описании синтаксиса SQL правилами обозначениями нелитералов блоком запроса (query block) называется допустимая конструкция языка, начинающаяся с ключевого слова SELECT, т.е. в блоке запроса не допускаются теоретико-множественные конструкции с использованием UNION, INTERSECT и MINUS. )

3) Предикаты соединения. Это предикаты вида Ri.Ck op Rj.Cn, где Ri != Rj и op - оператор скалярного сравнения.

4) Предикаты деления. Это предикаты вида Qi op Qj, где Qi и Qj - блоки запросов, а op может быть оператором скалярного сравнения или оператором проверки вхождения в множество.

2.2 Семантическая оптимизация запросов

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

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

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

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

Идея семантической оптимизации в том, что используется набор знаний, которые не обязательно использовать при обработке запроса, но использование которых в некоторой комбинации может привести к более оптимальному выполнению запроса. Если считать, например, что семантическая оптимизация имеет дело только со знаниями, представленными в виде ограничений целостности базы данных, то концептуально действия при семантической оптимизации можно понимать следующим образом. Производится множество преобразованных внутренних представлений запроса, причем каждое преобразование использует некоторый поднабор ограничений целостности. Если, например, в базе данных определены два ограничения целостности A и B с логическими условиями F1 и F2, соответственно, и обрабатывается запрос с логическим условием выборки F, то в ходе семантической оптимизации будут получены внутренние представления, эквивалентные запросам с условиями выборки F, F AND F1, F AND F2 и F AND F1 AND F2.

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

3 Планы выполнения запросов

3.1 Выбор альтернативных планов выполнения запросов

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

Процедурным представлением или планом выполнения запроса называется такое его представление, в котором в детализированной форме отображен порядок выполнения операций доступа к базе данных физического уровня. Как правило, в реляционных СУБД, ориентированных на использования традиционной аппаратуры без использования специализированных процессоров или устройств внешней памяти, выделяется подсистема управления данными на физическом уровне. Например, в System R, такая подсистема называется RSS (Relational Storage System) и обеспечивает простой интерфейс, позволяющий последовательно просматривать кортежи отношений, удовлетворяющие заданным условиям на значения полей, с использованием индексов или простым сканированием страниц базы данных. Кроме того, RSS позволяет производить отсортированные временные файлы и заносить, удалять и модифицировать индивидуальные кортежи. Аналогичные подсистемы явно или неявно выделяются во всех подобных СУБД.

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

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

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

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

Если оптимизатору удалось сгенерировать множество планов выполнения запроса на основе разумных стратегий декомпозиции и эффективных стратегий выполнения элементарных операций, то этого еще мало - нужно суметь выбрать один план, в соответствии с которым будет происходить реальное выполнение запроса. Если этот выбор будет неверным, то запрос будет выполнен неэффективно. Прежде всего необходимо определить, что понимается под эффективность выполнения запроса. Вообще говоря, это понятие неоднозначно и зависит от специфики операционной среды СУБД. В одних условиях можно считать, что эффективность выполнения запроса определяется временем его выполнения, т.е. реактивностью системы по отношению к обрабатываемым ею запросам. В других условиях определяющим является общая пропускная способность системы по отношению к смеси параллельно выполняемых запросов. Тогда мерой эффективности запроса можно считать количество системных ресурсов, требуемых для его выполнения: чем меньше ресурсов требуется, тем запрос эффективнее. Если пользователи СУБД работают в условиях бюджетной системы, то разумной мерой эффективности может быть бюджетная стоимость выполнения запроса и т.д. Заметим, что в любом случае оценка эффективности выполнения запроса опирается на ресурсные характеристики соответствующего плана; при использовании разных мер они просто по-разному участвуют в оценке.

3.2 Оценка альтернативных планов выполнения запросов

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

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

Как правило, подсистемы управления доступом к данным на внешней памяти осуществляют буферизацию блоков базы данных в оперативной памяти. Каждый блок базы данных, прочитанный в оперативную память для выполнения запроса, сохраняется в одном из буферов буферного пула СУБД до тех пор, пока не будет вытеснен из него другим блоком базы данных. Эта особенность СУБД, конечно, очень существенна для повышения общей эффективности системы, но практически не учитывается (за исключением очень частного, но и очень важного случая) при оценках стоимостей планов выполнения запроса. В любом случае тот компонент стоимости выполнения запроса, который связан с ресурсами устройств внешней памяти, монотонно зависит от числа блоков внешней памяти, доступ к которым потребуется при выполнении запроса.

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

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

Метод оценки распределения на основе сигнатур в общих словах можно описать следующим образом. Область значений поля разбивается на несколько интервалов. Для каждого интервала некоторым образом устанавливается число значений поля, попадающих в этот интервал. Внутри интервала значения считаются распределенными по некоторому фиксированному закону (как правило, принимается равномерное приближение). Рассмотрим теперь более точно два альтернативных подхода, связанных с сигнатурным описанием распределений.

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

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

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

4 Оптимизация запросов в Oracle 9.2

4.1 Постановка задачи

База данных, с которой мы будем работать имеет следующую структуру. Имеется два сервера, которые расположены в различных частях города. Между ними имеется прямая связь по двухмегабитному выделенному каналу. В данной РСУБД вся база данных полностью репликацирована по двум серверам. База данных состоит из множества таблиц с различными данными по энергии области. В ней имеются как маленькие таблицы (например, список станций), так и очень большие (почти 200 млн. записей). На обоих серверах установлена одинаковая СУБД - Oracle 9.2.

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

4.2 Построение запроса

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

select st_name as name_of_station,e_name as name_of_seti,fg_name as name_of_group,f_name as name_of_fider, p_value as znachenie_moshnosti from pyr.stations@orcl_sbyt,refbus.ets,pyr.fgroups@orcl_sbyt,pyr.fiders,pyr.power where st_id=fg_station and st_ets=e_id and fg_station=f_station and f_id=p_fider and p_value>'40' and p_hhnumber>'119000'

Данный запрос является распределённым, так как выборка идёт из таблиц, расположенных на различных серверах. Результатом данного запроса является выборка из 2 428 479 строк. Время выполнения его составило 344 секунды. Это довольно длительное время. План выполнения данного запроса представлен на рисунке 1.

Рисунок 1 - План выполнения распределённого запроса

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

4.3 Возможности оптимизации запроса

Рассмотрим структуру таблиц данного запроса. Выборка идёт из 5 таблиц.

Первая таблица - «pyr.stations». Она имеет следующую структуру (рисунок 2) :

Рисунок 2 - Структура таблицы pyr.stations

Данная таблица не проиндексирована. Она содержит 119 строк - названия станций. В данной распределённой базе содержится несколько табличных пространств. Стоимость выборки из данной таблицы невелика и составляет величину, равную 2. Но объединение происходит уже с большим количеством строк, что может повлиять на итоговую стоимость выполнения запроса.

Данная таблица относится к табличному пространству «Constant_Data». Структура этого табличного пространства представлена на рисунке 3.



Рисунок 3 - Табличное пространство Constant_Data

На этом рисунке место, занимаемое таблицей «pyr.stations», обозначено цифрой 1. А полосой выделена его структура. Отсюда видно, что таблица занимает 1 экстент из 8 блоков, т.е. это минимальное количество дискового пространства, отведённого для таблиц в данной базе данных. Поэтому и общий вес в выборке из данной таблицы должен быть невелик. Из плана выполнения представленного запроса (рисунок 1) мы можем это увидеть.

Но тем не менее выборку из этой таблицы можно оптимизировать для данного запроса.

Так как нам из всей таблицы pyr.stations требуется лишь имя станции, то создадим новую таблицу, состоящую из имени станции, идентификатора и поля ST_ETS для связи с другой таблицей, и проиндексируем по идентификатору.

Данную таблицу разместим в табличном пространстве USERS. Теперь выполним запрос с новой таблицей.

select st_name as name_of_station,e_name as name_of_seti,fg_name as name_of_group,f_name as name_of_fider, p_value as znachenie_moshnosti from stationstest,refbus.ets,pyr.fgroups,pyr.fiders,pyr.power where st_id=fg_station and st_ets=e_id and fg_station=f_station and f_id=p_fider and p_value>'40' and p_hhnumber>'119000'

Таблица «STATIONSTSEST» здесь является новой. Время выполнения уже этого запроса составляет 0.59 секунды, что значительно меньше чем в предыдущий раз. Данный результат можно объяснить только количеством обращений к данной таблице.

Следующая таблица, которую мы будем рассматривать - это таблица «refbus.ets». Эта таблица аналогична предыдущей. Она расположена в том же табличном пространстве, содержит 30 записей о сетях, она не проиндексирована. Потому проделаем с ней тоже самое, т.е. создадим новую таблицу, состоящую из двух полей - идентификатора, для связи с другими таблицами, и имени сети, а так же организуем индекс по идентификатору.

Эта таблица так же занимает 1 экстент в табличном пространстве. Перепишем теперь запрос в соответствии с новой таблицей, сохранённой так же в табличном пространстве «USERS» :

select st_name as name_of_station,e_name as name_of_seti,fg_name as name_of_group,f_name as name_of_fider, p_value as znachenie_moshnosti from stationstest,etstest,pyr.fgroups,pyr.fiders,pyr.power where st_id=fg_station and st_ets=e_id and fg_station=f_station and f_id=p_fider and p_value>'40' and p_hhnumber>'119000'

В результате выполнения данного запроса получаем время выполнения 0.01 секунды. По сравнению с начальным значением - это значительный выигрыш в скорости выполнения.

Таблица «pyr.fgroups». Эта таблица занимает уже 5 экстентов, размещённых по табличному пространству «Constatnt_Data», т.е. можно сказать что это не оптимально (рисунок 4):

Рисунок 4 - Размещение таблицы pyr.fgroups по табличному пространству

Для того, чтобы оптимизировать выборку из этой таблицы, создадим новую, размещённую в одной области табличного пространства. Стоимость выборки из данной таблицы изначально составляет 2. В новую таблицу поместим 3 поля : идентификатор, по которому составим индекс, имя группы и код станции, для связи с другими таблицами. И поместим эту таблицу в табличное пространство «USERS». В данной таблице уже около 2000 строк. Посмотрим как изменится запрос с введением новой таблицы :

select st_name as name_of_station,e_name as name_of_seti,fg_name as name_of_group,f_name as name_of_fider, p_value as znachenie_moshnosti from stationstest,etstest,fgroupstest,pyr.fiders,pyr.power where st_id=fg_station and st_ets=e_id and fg_station=f_station and f_id=p_fider and p_value>'40' and p_hhnumber>'119000'

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

Таблица «pyr.fiders». Все аналогичные действия выполним с данной таблицей. В результате получаем проиндексированную таблицу :

Рисунок 5 - Таблица pyr.fiders

В данной таблице 5500 строк. Индекс здесь построен по полю f_id. Посмотрим теперь кА изменится время выполнения запроса с данной таблицей составляет уже 359 секунд. Т.е. опять хуже, чем в прошлый раз. Рассмотрим теперь последнюю таблицу и самую большую - «pyr.power».

Таблица «pyr.power». Данная таблица уже проиндексирована. Она состоит из 177 016 568 записей. И каждый день она пополняется на 50 Мбайт. В ней присутствуют три поля : код фидера, номер получасовки и значение энергии за эту получасовку. Она занимает отдельное табличное пространство «MP_DATA» (рисунок 6) :



Рисунок 6 - Табличное пространство MP_DATA

Как видим таблица занимает 169 экстентов. Поэтому полный перебор из данной таблицы займёт большое время. Так как таблица проиндексирована, то мы с ней делать ничего будем.

Таким образом получается, что самым оптимальным вариантом выполнения оптимизации запроса является индексирование первых двух таблиц. Посмотрим на этот план запроса (рисунок 7)

Рисунок 7 - Оптимизированый план запроса

4.4 Анализ планов выполнения запросов

Мы получили два плана выполнения одного и того же запроса. На рисунке 1 представлен план выполнения неоптимизированного запроса, а на рисунке 7 - оптимизированного. Но оптимизация в данном случае выполнялась при помощи анализа таблиц, табличных пространств, занимаемых этими таблицами, а так же при помощи нормализации данных таблиц.

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

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

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

select distinct c_name as Potrebitel,st_name as podstancia from pyr.stations,potr.consumers,pyr.fiders where c_id=f_consumer and f_station=st_id

Здесь выборка идёт из локального сервера. Время выполнения такого запроса равна 0.01 секунды. Посмотрим теперь на план выполнения запроса (рисунок 9).

Рисунок 9 - Выборка с локального сервера

Проведём аналогичную выборку, но уже из двух серверов. Например, таблицу «POTR.CONSUMERS» мы запросим со второго сервера и выведем план выполнения запроса (рисунок 10)

Рисунок 10 - Выборка из двух серверов

Как видим, стоимость запроса увеличилась, и увеличилось время выполнения запроса до 0.52 секунды. Сравнив оба плана выполнения запросов, можно увидеть, что они идентичны. Но время выполнения получается разным только потому, что во втором случае приходится сравнивать данные, полученные на одном сервере, с данными, полученными на другом сервере (это происходит на итерации 5). Соответственно можно предположить, что если выбирать только из второго сервера, то время выполнения запроса уменьшится. Это и происходит - время выполнения составило уже 0.09 секунды, хотя стоимость увеличилась до 71. Увеличение стоимости связано с тем, что происходит больше обращений к удалённому серверу. А уменьшение времени выполнения связано с тем, что в итоге передаётся меньшее количество данных.

Таким образом этот пример доказывает, что планы запроса строятся из анализа структуры таблиц.

Теперь продемонстрируем пример, когда время выполнения запроса на локальном сервере больше времени выполнения запроса на удалённом сервере. Подсчитаем количество записей в самой большой таблице «pyr.power» и посмотрим на результаты (рисунок 11) :

Рисунок 11 - Сравнение планов выполнения запросов с разных серверов

Как видно, вывелись вполне ожидаемые результаты. Данная таблица на локальном сервере проиндексирована и, соответственно, стоимость запроса оказалась меньше чем, при выборке из удалённого сервера. Но неожиданными оказались результаты по времени выполнения запросов. Время выборки их локального сервера равно 198 секунд, а время выборки из удалённого сервера - 72 секунды.

Заключение

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

а) Оптимизация проводится для каждого конкретного запроса в отдельности (или для узкого класса запросов);

б) Существует несколько способов по оптимизации. Это оптимизация при помощи нормализации таблиц, оптимизация при помощи самих запросов, оптимизации при помощи эффективного использования табличных пространств;

в) Оптимизация в распределённых базах (если на разных серверах установлена одна и та же СУБД) с водится к оптимизации на одном сервере. Разницу здесь составляет лишь время передачи данных по сети.

Список используемых источников

1 Распределённые СУБД, http://mrivkin.narod.ru/Publ/RASPBD.htm

2 Кузнецов С., Оптимизация запросов в системах баз данных, http://www.citforum.ru/database/articles/query_optimization/

3 Кузнецов С., Оптимизация запросов в системах баз данных, security.software-testing.ru/wiki/OptimizacijaZaprosovVSistemaxBazDannyx

Размещено на Allbest.ru