Оптимизация SQL запросов в СУБД. На примере Oracle

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

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

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

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

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

два основных вида оптимизаторов. Оптимизатор, основанный на анализе заданных правил (rule-based optimizer). Оптимизатор, основанный на анализе затрат (cost-based optimizer).

rule-based optimizer. Этот оптимизатор выбирает методы доступа на основе предположения о статичности СУБД Такой оптимизатор учитывает иерархическое старшинство операций. Если для какой-либо операции существует более одного пути ее выполнения, то выбирается тот путь, чей ранг выше, т.к. в большинстве случаев он выполняется быстрее, чем путь с более низким рангом. План выполнения запроса формируется из выбранных путей доступа с максимальными рангами.

Ранжирование методов доступа в Oracle Ра Метод доступа 1 Одна строка по ее идентификатору 2 Одна строка по объединению кластеров 3 Одна строка по хэш-ключу кластера с уникальным или первичным ключом 4 Одна строка по уникальному или первичному ключу 5 Объединение кластеров 6 Кэш-ключ кластера 7 Индекс кластера 8 Составной индекс 9 Индекс на основе одного столбца 10 Ограниченный диапазон поиска по индексированным столбцам 11 Неограниченный диапазон поиска по индексированным столбцам 12 Объединение с сортировкой и слиянием 13 Поиск максимального или минимального значения по индексированным столбцам 14 Упорядочение по индексированным столбцам 15 Полное сканирование таблицы

Пример SELECT ИД FROM ПРОДАВЦЫ WHERE ИД >=110; При наличии первичного ключа поля ИД таблицы ПРОДАВЦЫ, для оптимизации кода в будет применен метод доступа 11

cost-based optimizer При использовании этого метода оптимизатор сначала строит несколько возможных планов выполнения запроса. При этом он применяет некоторые эвристики, т.е. правила, полученные опытным путем. Эти правила позволяют сузить пространство поиска оптимального плана благодаря тому, что неэффективные планы отбрасываются в самом начале и не рассматриваются. Для каждого из построенных планов рассчитывается его стоимость.

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

Оптимизация выполнения запроса осуществляется в следующем порядке: 1.Вычисление выражений и условий, содержащих константы. 2.Преобразование сложной команды в эквивалентную ей с использованием соединения (проводится не всегда). 3.Если команда выполняется над представлением, то оптимизатор обычно объединяет запрос на создание представления и запрос к этому представлению в одну команду. 4.Выбор метода оптимизации. 5.Выбор путей доступа к таблицам, к которым обращается запрос. 6.Выбор порядка соединения (если в запросе соединяются несколько таблиц, то оптимизатор определяет, какие две таблицы будут соединяться первыми, какая таблица следующей будет подключаться в результату и т.д.). 7.Выбор операции соединения для каждой команды соединения.

Задание режима оптимизации. Для указания режима оптимизации в файле параметров init.ora следует использовать приведенные ниже значения параметра OPTIMIZER_MODE.

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

Значения OPTIMIZER_MODE. RULE. При установке этого значения будет использована оптимизация, основанная на анализе правил.

Значения OPTIMIZER_MODE. FIRST ROWS. Это значение используется для минимизации времени отклика, т.е. для сведения к минимуму временного интервала между вводом запроса в СУБД и появлением результатов на экране. При этом будет выбран вариант оптимизации, основанный на анализе затрат Это значение следует использовать только в интерактивном приложении с множеством экранных форм вывода информации.

Значения OPTIMIZER_MODE. ALL ROWS. При установке этого значения будет использована оптимизация, основанная на анализе затрат, для минимизации общего количества строк, проходящих через систему за единицу времени (в транзакциях за секунду). Это значение следует использовать при работе с системами пакетной обработки

Для задания режима оптимизации на уровне выражения Ключевое слово Метод извлечения строк ROWIDДля извлечения строк используется их идентификатор CLUSTERСканирование ключа кластера HASHСканирование хэш-индекса INDEXСканирование индекса INDEX_ASCСканирование индекса в порядке возрастания NDEX_DESCСканирование индекса в порядке убывания AND_EQUALИспользование нескольких индексов со слиянием результатов ORDEREDИспользование порядка таблиц, указанного в предложении FROM, в качестве порядка их объе­динения

Пример оптимизации на уровне выражений 1. SELECT ИД FROM ПРОДАВЦЫ WHERE ДОЛЖНОСТЬ='МЕНЕДЖЕР' 2. SELECT ИД FROM ПРОДАВЦЫ WHERE ДОЛЖНОСТЬ='ПРОДАВЕЦ Для торговой организации с 10 менеджерами, 1000 продавцов и общим числом сотрудников около 6000

если применяется оптимизация, основанная на анализе правил, то при наличии неуникального индекса по столбцу ДОЛЖНОСТЬ будет выбран метод доступа 9 для обоих запросов при использовании оптимизации, основанной на анализе затрат, знание некоторых характеристик распределения данных (например, того, что строки с данными о менеджерах составляют 1/600 часть всех строк) позволяет применять неуникальный индекс для запроса 1. Однако для выполнения запроса 2 будет уместно и эффективно полное сканирование таблицы (т.е. использование метода доступа 15).

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

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

Рекомендации по оптимизации 1. Раздел WHERE является критическим. Для следующих примеров раздела WHERE индексный путь доступа не будет использоваться, даже если индекс существует (COL1 и COL2 - столбцы одной таблицы, и создан индекс на COL1): COL1 > COL2 COL1 < COL2 COL1 >= COL2 COL1

Любые выражения, функции и вычисления, включающие индексированные столбцы, препятствуют использованию индекса. Например, в следующем примере наличие функции UPPER не дает возможность использовать сканирование по индексу, и будет применен полный просмотр таблицы: SELECT DEPT_NAME FROM DEPARTMENT WHERE UPPER(DEPT_NAME) like 'SALES%');

2. Для фильтрации записей используйте WHERE, а не HAVING. Если для таблицы EMP существует индекс на столбце DEPTID, в при выполнении следующего запроса этот индекс использоваться не будет: SELECT DEPTID, SUM(SALARY) FROM EMP GROUP BY DEPTID HAVING DEPTID = 100; Однако этот запрос можно переписать так, чтобы индекс применялся: SELECT DEPTID, SUM(SALARY) FROM EMP WHERE DEPTID = 100 GROUP BY DEPTID;

3. Указывайте в разделе WHERE начальные столбцы ключа индекса. Для следующего запроса может быть применен составной индекс на столбцах PART_NUM и PRODUCT_ID, образованный в связи с ограничением первичного ключа: SELECT * FROM PARTS WHERE PART_NUM = 100; то время как в приводимом ниже запросе составной индекс использоваться не может: SELECT * FROM PARTS WHERE PRODUCT_ID = 5555;

Последний запрос можно переписать так, чтобы индекс можно было применить. В этом запросе предполагается, что столбец PART_NUM будет всегда содержать положительные значения: SELECT * FROM PARTS WHERE PART_NUM > 0 AND PRODUCT_ID = 5555;

4. Сравните сканирование через индекс с полным просмотром таблицы. При выборе из таблицы более 15 процентов строк полный просмотр таблицы обычно выполняется быстрее, чем сканирование через индекс. Когда использование индекса приносит больше вреда, чем пользы, можно применять методы, чтобы воспрепятствовать использованию индекса. SELECT * FROM EMP WHERE SALARY+0 = 50000;

5. Используйте ORDER BY для индексного сканирования. Оптимизатор Oracle будет использовать индексное сканирование, если запрос содержит раздел ORDER BY с указанием индексированного столбца. Для выполнения следующего запроса будет использован индекс на столбце EMPID, даже если этот столбец не используется в условиях раздела WHERE. Для каждой строки из индекса будет извлекаться ROWID, а потом с использованием ROWID будет производиться обращение к строке. SELECT SALARY FROM EMP ORDER BY EMPID;

6. Минимизируйте число просмотров таблиц Таблица STUDENT содержит четыре столбца с именами NAME, STATUS, PARENT_INCOME и SELF_INCOME. Форма запроса предполагает два просмотра таблицы STUDENT, создание временной таблицы для последующей обработки и сортировку для устранения дубликатов: SELECT NAME, PARENT_INCOME FROM STUDENT WHERE STATUS = 1 UNION SELECT NAME, SELF_INCOME FROM STUDENT WHERE STATUS = 0; Тот же самый результат будет получен при выполнении запроса с одним просмотром таблицы: SELECT NAME, PARENT_INCOME * STATUS + SELF_INCOME * (1 - STATUS) FROM STUDENT;

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

8. При возможности используйте только поиск через индексы. Оптимизатор будет использовать только поиск в индексе, если вся информация, необходимая для выполнения запроса, содержится в самом индексе. Если для таблицы EMP существует составной индекс на столбцах LNAME и FNAME, то при выполнении следующего запроса будет использован только поиск в индексе: SELECT FNAME FROM EMP WHERE LNAME = 'SMITH'; В то же время при выполнении запроса SELECT FNAME, SALARY FROM EMP WHERE LNAME = 'SMITH'; будет производиться индексное сканирование таблицы с доступом к ее строкам по ROWID

9. Старайтесь писать как можно более простые и тупые операторы SQL.

10. Варьируйте использование UNION или OR в зависимости от наличия индекса. Например, список пациентов палат 3 и 8 при наличии индекса должен быть таким: select * from patients where room=3 union all select * from patients where room=8; а если индекса нет, то таким: select * from patients where room=3 or room=8;

11. Если после слияния таблиц отбираются поля только из одной таблицы, то вместо операции join надо использовать операцию in Исходный запрос: select emp.name from emp, empjob where emp.no = empjob.emp and empjob.salary > 900; Оптимизированный запрос: select name from emp where no in (select emp from empjob where salary > 900);

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