Хранение данных и доступ к ним

11.7 Оптимизация запросов в SQL

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

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

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

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

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

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

Существует два типа оптимизаторов:

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

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

11.7.1 Планы исполнения

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

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

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

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

11.7.2 Примеры планов исполнения

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

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

Управлять планом исполнения можно размещая после слова SELECT подсказки в виде комментариев специального вида (hints). Например, подсказка в запросе

SELECT /*+INDEX*/ empno FROM emp WHERE empno = 1739;

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

Перечислим некоторые подсказки используемые для управления планом исполнения (таблица 11.6)

Примеры планов, приведенные ниже получены в СУБД Oracle. Их следует читать из глубины вверх. Помните, что выбор плана исполнения сильно зависит от настройки СУБД и ее версии, так что при самостоятельной работе вы можете получить совсем другие результаты. Как писал один из авторов хорошей книги по SQL-тюнингу, "не верь тому, что здесь написано".

Для просмотра планов исполнения в Oracle ХЕ необходимо выбрать закладку Explain.

Примеры планов:

1. Простейший запрос

   SELECT * FROM emp;
   

   План исполнения:

   SELECT STATEMENT
   TABLE ACCESS full emp
   

   Читаем план. Во второй строке сказано, что к таблице emp осуществлен полный доступ. Первая строка просто констатирует, что анализировалась инструкция SELECT. Получен самый медленный план с рангом 15, но ничего улучшить нельзя.

2. Запрос с фразой WHERE и по-прежнему без индексов

   SELECT * FROM emp WHERE sal>1000;
   

   План исполнения тот же, хотя после извлечения данных работает фильтр, определенный фразой WHERE.

3. Запрос

   SELECT * FROM emp ORDER BY ename;
   

   План исполнения

   SELECT STATEMENT SORT order by
   TABLE ACCESS full emp
   

   Добавилась сортировка в памяти, а если таблица станет большой, то может быть и на диске.

4. Тот же запрос

   SELECT * FROM emp ORDER BY ename;
   

   но теперь существует индекс i_emp_ename на столбец ename. План исполнения:

   SELECT STATEMENT
   TABLE ACCESS full emp
   INDEX full scan i_emp_ename
   

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

5. Запрос

   SELECT job,  sum(sal) FROM emp GROUP BY job HAVING sum(sal)> 100000;
   

   Индекс не существует. План исполнения:

   SELECT STATEMENT FILTER
   SORT group by
   TABLE ACCESS full emp
   

6. Запрос на доступ по значению ROWID:

   SELECT * FROM emp WHERE rowid=,00004F2A00A2 000C';
   

   дает самый быстрый план исполнения:

   SELECT STATEMENT
   TABLE ACCESS by rowid emp
   

7. Соединение с вложенными циклами

   SELECT * FROM emp, dept;
   

   План исполнения:

   SELECT STATEMENT
   NESTED LOOPS TABLE ACCESS full dept
   TABLE ACCESS full emp
   

8. Запрета на использование индекса можно добиться добавив к имени текстового столбца пустую строку а к числовому столбцу значение 0. Например, запрос

   SELECT ename FROM emp WHERE job  || ''^MANAGER';
   

   не использует индекс.

9. Сортировка слиянием

   SELECT * FROM emp, dept WHERE emp.deptno=dept.deptno;
   

   План исполнения:

   SELECT STATEMENT
   MERGE JOIN
   SORT JOIN
   TABLE ACCESS full emp
   SORT JOIN TABLE ACCESS full emp
   

10. Тот же запрос, но существует индекс idx_fk_emp_deptno на столбец deptno играющий роль внешнего ключа в emp. План исполнения:

    SELECT STATEMENT NESTED LOOPS TABLE ACCESS full dept
    TABLE ACCESS by rowid emp
    INDEX range scan idx_fk_emp_deptno
    

В Cache для просмотра плана исполнения достаточно в окне исполнения SQL-инструкции выбрать позицию Explain, но планы там описываются иначе.