Физическая организация данных

• Название: Физическая организация данных
• Дата: 23.02.2018
• Размер: 487.44 Kb.
• Имя файла: db.docx
• Тип: Документы; #37060
• страница: 9 из 16

Из множества возможных планов выполнения запроса оптимизатор в соответствии с критерием выбирает лучший план (рис. 7.3).

В качестве критерия оптимизации может выступать:

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



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

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

Стоимость плана выполнения запроса определяется на основании сведений о распределении данных в таблицах, к которым обращается команда, и связанных с ними кластеров и индексов. Эти сведения о распределении значений данных называются статистикой и хранятся в словаре-справочнике данных. Для таблицы статистика может включать в се-бя:

• 
  общее количество блоков данных (страниц памяти), выделенных таблице;
  
• 
  количество пустых блоков данных (страниц памяти);
  
• 
  количество записей в таблице;
  
• 
  среднюю длину записи в таблице;
  
• 
  среднее количество записей на блок (страницу) памяти.
  

Для каждого индекса статистика может содержать такие данные, как:

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

Распределение значений в столбце может быть отражено с помощью гистограммы, которая также входит в статистику. Для этого всё множество значений столбца упорядочивается и разбивается на N интервалов. На рис. 7.4 приведено разбиение на N =10 интервалов множества значений некоторого столбца F. Для равномерного распределения это означает, что в первых 10% записей это поле имеет значение от 1 до 10, в следующих 10% записей - от 11 до 20 и т.д.

1 1 1 1 ! ! 1= 1 10 20 30 <«0 50 60 6) неравномерное распределение данных I | I i I 1 I	4— 70 |	4= 80 |	4— 50 |	4 100 !
1 1 1 I I 1 1 1 4 A 5 6 10 11	1 20	1 35	1 60	i 100

Рис.7.4. Примеры равномерного (а) и неравномерного (б) распределения значений

Г истограмма помогает оценить объём данных, удовлетворяющих усло-вию запроса. На рис. 7.4,б представлено неравномерное распределение дан-ных для некоторого столбца F. В словарь-справочник данных записываются полученные значения (1, 4, 4, 5, 6, 10, 11, 20, 35, 60, 100). При анализе запроса, например, с условием (F<=5) система сможет по этой гистограмме определить, что через индекс придётся выбрать не менее 30% записей таблицы.

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

Построение гистограммы бесполезно в следующих случаях:

• 
  значения столбца распределены равномерно;
  
• 
  столбец не используется в предикатах запросов;
  
• 
  значения столбца уникальны и используются только в предикатах эквивалентности.
  

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

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

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

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

SELECT * FROM Emp WHERE name LIKE 'Иванов%' AND salary<40000;

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

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

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

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

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

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

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

SELECT * FROM Emp WHERE 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, salary FROM depart AS d, emp AS e WHERE d.depNo=e.depNo AND e.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. Затем для каждой строки соединения проверить второе условие.
   

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

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

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

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

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

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

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

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

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

Рис.7.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 e, Children AS с WHERE с.empNo=e.empNo;