Настройка приложений

Цель настройки приложений – повышение эффективности работы с БД. В настройку приложений входит:

• создание индексов;
• настройка команд SQL;
• выбор метода оптимизации SQL-запросов;
• использование средств сбора статистики.

Первый пункт мы уже обсуждали (см. разделы 4.5.2).

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

Для оптимизации приложений необходимо иметь представление о порядке и механизмах реализации запросов в СУБД. Основные информационные потоки между пользователями, оперативной памятью и базой данных приведены на рис. 7.5. В ОП для каждого сеанса связи с БД выделяется специальная область – курсор, куда помещается результат выполнения последнего (текущего) запроса пользователя.

Рис.7.5. Информационные потоки в БД

Приведем основные рекомендации по написанию запросов, удобных для оптимизатора и эффективных при выполнении.

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

Например, для получения списка сотрудниц второго отдела при условии, что во втором отделе сотрудников около 5% от общего числа сотрудников, а женщин на предприятии – примерно половина, запрос должен выглядеть так:

SELECT * FROM empWHERE depNo=2 AND sex='ж';

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

Например, список пациентов по отделениям №№1,2:

SELECT * FROM patients p, depart dWHERE d.id IN (1,2) AND p.depNo=d.id;

1. Если запрос содержит условие с неопределённой лидирующей частью типа (field LIKE '%…') или (field LIKE '_…'), то необходимо дополнять это условие так, чтобы система могла воспользоваться индексом по полю field (если он существует).

Например, список всех хирургов:

SELECT * FROM doctorsWHERE special>'A' AND special like '%хирург%';

Здесь условие special>'A' не исключает из поиска ни одной записи таблицы, но позволяет системе проводить этот поиск по индексу, который занимает гораздо меньше памяти, чем сама таблица.

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

Например, запрос на выборку названия отделения №3:

SELECT name FROM departWHERE id*1=3;

id – это первичный ключ, по нему есть индекс. Но при доступе через индекс потребуется минимум два обращения к диску. Включение индексированного поля в выражение (id*1 вместо id) подавляет использо-вание индекса.

1. Следует использовать UNION ALL вместо UNION, если в объединяемых отношениях отсутствуют одинаковые записи (или наличие одинаковых записей некритично). Дело в том, что UNION вычисляется путем сортировки, которая может занять много времени, а UNION ALL сортировки не требует.
2. Следует использовать IN вместо EXISTS, если EXISTS не оптимизируется. Например, список сотрудников, у которых есть дети:

SELECT * FROM empWHERE empNo in (SELECT empNo FROM children);

Но для подзапросов, выдающих большой список, более оптимальным может оказаться вариант запроса с соединением (при наличии индекса по внешнему ключу):

SELECT DISTINCT e.* FROM Emp AS е, Children AS сWHERE с.empNo=e.empNo;

1. Если оптимизатор плохо оптимизирует операцию "или" (OR), то можно заменять её операцией UNION при наличии индексов. Убедиться в "плохой оптимизации" можно так: выполнить запрос по условию (field=X) и запрос с условием ((field=X) OR (field=Y)) на большой таблице. Если второй запрос выполняется намного дольше, чем первый, то OR не оптимизируется. Например, список "Пациенты палат №3 и пациенты, больные гриппом" в отсутствие индексов можно сформулировать так:

SELECT * FROM PatientsWHERE room=3 OR diagnose LIKE 'грипп%';

а если индексы есть, то таким:

SELECT * FROM PatientsWHERE room=3UNION ALLSELECT * FROM PatientsWHERE diagnose LIKE 'грипп%';

1. Условие "не равно" ('<>') также подавляет использование индекса. Поэтому, если значения индексированного столбца распределены неравномерно, следует заменять его комбинацией условий '<' OR '>' и, с учетом предыдущего правила, реализовывать это с помощью UNION.

Например, список сотрудников всех отделов (10% от общего числа), кроме сотрудников центрального офиса (отдел №3) будет выглядеть так:

SELECT * FROM EmpWHERE deptNo3;

1. 9. Некоторые оптимизаторы будут использовать индексное сканирование, если запрос содержит раздел ORDER BY с указанием индексированного столбца. Для выполнения следующего запроса будет использован индекс на столбце tabNo, даже если этот столбец не используется в условиях раздела WHERE:

SELECT * FROM empWHERE depNo

1. Условие <выражение1> op <выражение2>, где op – операция, также не позволяют использовать индекс. Из выражений надо по возможности вынести в левую часть поле, по которому есть индекс. Например, условие (salary*0.87>30000) лучше записать так: salary>30000/0.87.

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

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

	"Сложная система, спроектированная наспех, никогда не работа-ет, и исправить её, чтобы заставить работать, невозможно".
	Законы Мерфи. 16-й закон системантики