Кубанский государственный университет
Опубликован: 24.12.2013 | Доступ: свободный | Студентов: 681 / 8 | Длительность: 24:28:00
Лекция 11:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица 11.6. Примеры подсказок
Подсказка Пояснение
FULL(таблица) выполнение полного просмотра таблицы
CASH разместить сканированную таблицу в кэше для сохранения ее блоков в памяти для последующего быстрого доступа
INDEX(индекс) использовать указанный индекс
USE_NL использовать вложенные циклы для объединения таблиц

Примеры планов, приведенные ниже получены в СУБД 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, но планы там описываются иначе.