Лекции и практики (1). Курс лекций и материалы для практических занятий

• Название: Курс лекций и материалы для практических занятий
• Дата: 17.03.2023
• Размер: 1.01 Mb.
• Имя файла: Лекции и практики (1).docx
• Тип: Курс лекций; #996812
• страница: 39 из 75

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

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

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

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

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

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

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

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

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

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

SELECT *

FROM emp

WHERE depNo=2 AND sex='ж';

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

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

SELECT *

FROM patients p, depart d

WHERE d.id IN (1,2) AND p.depNo=d.id;

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

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

SELECT *

FROM doctors

WHERE special>'A' AND special like '%хирург%';

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

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

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

SELECT name FROM depart

WHERE id*1=3;

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

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

SELECT *

FROM emp

WHERE empNo in (SELECT empNo FROM children);

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

SELECT DISTINCT e.*

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

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

SELECT *

FROM Patients

WHERE room=3 OR diagnose LIKE 'грипп%';

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

SELECT *

FROM Patients

WHERE room=3 UNION ALL

SELECT *

FROM Patients

WHERE diagnose LIKE 'грипп%';

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

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

SELECT * FROM Emp

WHERE deptNo<3 UNION ALL

SELECT * FROM Emp

WHERE deptNo>3;

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

SELECT *

FROM emp

WHERE depNo<3 ORDER BY tabNo;

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

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

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