лекция 10. Оптимизация sql запросов в субд. Индексирование данных

• Название: Оптимизация sql запросов в субд. Индексирование данных
• Дата: 12.10.2022
• Размер: 0.77 Mb.
• Имя файла: лекция 10.pptx
• Тип: Документы; #729077

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

Определение

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

Работа оптимизатора состоит из следующих 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	Полное сканирование таблицы

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-области, можно исключить предобработку запроса, что является важным моментом оптимизации приложений.

Рекомендации по оптимизации

Соединяйте таблицы в правильном порядке.

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

При возможности используйте только поиск через индексы.

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


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

Варьируйте использование 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;

Если после слияния таблиц отбираются поля только из одной таблицы, то вместо операции 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);


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