Примеры использования методов оптимизации запросов

Рассмотрим оптимизацию по синтаксису следующего запроса SQL:

Пример 7.2. Запрос, выбирающих всех сотрудников по фамилии 'Иванов' с зарплатой менее 40000 рублей:

SELECT *FROM EmpWHERE name LIKE 'Иванов%' AND salary

Пусть таблица Emp имеет следующее правило целостности и индексы:

• столбец tabNo определен как PRIMARY KEY, ему соответствует индекс PK_TABNO;
• существует индекс NAME_IND для столбца name;
• существует индекс SALARY_IND для столбца salary.

Возможны следующие пути доступа:

• полный просмотр таблицы (ранг 15);
• доступ по одиночному индексу NAME_IND. Этот путь становится доступным по условию name LIKE 'Иванов%' (ранг 9);
• доступ с помощью открытого интервала salary

Индекс PK_TABNO недоступен, т.к. в запросе нет условий на значение поля tabNo. Оптимизатор выберет доступ по индексу с рангом 9.

Теперь рассмотрим примеры оптимизации по стоимости.

Пример 7.3. Запрос, выбирающий сотрудников с номерами больше 7500:

SELECT * FROM EmpWHERE tabNo > 7500;

Статистика для столбца tabNo, в частности, включает значения HIGH_VALUE и LOW_VALUE (максимальное и минимальное значения). Если нет гистограммы, то оптимизатор предполагает, что значения равно-мерно распределены в интервале [LOW_VALUE, HIGH_VALUE], и может определить процент значений, попадающий в интервал до 7500. Доступ будет осуществляться по индексу, если этот процент невысок, например, не более 10, хотя конкретное пороговое значение зависит и от других параметров, например, количества записей в блоке памяти.

Пример 7.4. Запрос, выбирающий название отделов (name из таблицы De-part) и всех сотрудников с максимальной зарплатой в своём отделе (name, salary из таблицы Emp):

SELECT d.name, e.name, salaryFROM depart AS d, emp AS eWHERE d.depNo=e.depNo ANDe.salary=(SELECT max(salary) FROM emp AS p WHERE p.depNo=e.depNo);

Для таблиц есть индексы по первичным ключам (Depart.depNo и Emp.tabNo) и по внешнему ключу (Emp.depNo).

Этот запрос можно выполнить по разным планам, например:

1. Выбрать все записи из таблиц Emp и Depart, соединить их по условию d.depNo=e.depNo, затем для каждой полученной строки посчитать подзапрос (выбрать максимальную зарплату для данного отдела, обра-тившись к таблице Emp по индексу) и проверить второе условие.
2. Выбрать все записи из таблицы Emp, для каждой записи найти соответствие по условию d.depNo=e.depNo в таблице Depart через индекс по первичному ключу (на поле depNo), затем для каждой полученной строки посчитать подзапрос и проверить второе условие.
3. Выбрать все записи из таблицы Emp, каждую запись соединить по усло-вию d.depNo=e.depNo с таблицей Depart через индекс по первично-му ключу (на поле depNo). Предварительно посчитать подзапрос, добавив в него условие группировки по номерам отделов GROUP BY depNo. Затем для каждой строки соединения проверить второе условие.

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

Третий план от второго отличается предварительным вычислением подзапроса. Это позволит один раз построить агрегированные значения (по количеству отделов). А во втором запросе агрегированные значения будут строиться для каждого сотрудника. Т.е. чем больше сотрудников в одном отделе, тем больше выигрыш по времени в третьем запросе.

Таким образом, при оптимизации по стоимости оптимизатор вероятнее всего выберет третий план как самый оптимальный с точки зрения времени выполнения запроса.