Диагностика и сбор данных. Оптимизация запросов

Оптимизатор запросов определяет наилучший план извлечения данных и наиболее эффективный путь их изменения. Он выбирает способ поиска записей, объединения таблиц и сортировки. Для выбора плана используется система оценок, основанная на стоимости выполнения того или иного варианта.

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

1. Синтаксический разбор, состоящий из проверки SQL – предложений и формирования обрабатываемой далее структуры – дерева запроса.

2. Стандартизация – преобразование дерева запроса с целью удаления избыточных структур (например, Between преобразуется в интервал, IN в OR и т.д.).

3. Оптимизация – выработка плана исполнения запроса.

4. Компиляция – перевод выбранного плана в исполняемый код.

5. Исполнение запроса.

Процесс оптимизации запроса включает этапы:

1. Анализ запроса, 2. Выбор индексов, 3. Выбор порядка соединения, 4. Выбор наилучшего плана, 5.Анализ запроса.

Анализ запроса включает определение аргументов поиска, условий или (OR), условий межтабличного соединения (Join)

Цель определения аргументов поиска – установление возможности использования индексов. Аргументом поиска является условие, позволяющее сузить область поиска данных. К таким относятся условие, задаваемые простыми операторами сравнения (=, >, <, >=, <=), где сравниваются поля и константы. Условия поиска можно объединить оператором конъюнкции (AND). Несколько объединенных условий, могут быть признаны аргументы поиска, если все поля, участвующие в сравнениях принадлежат одной таблице.

Не является аргументом поиска условие – А/12>=300, не является и аргументом поиска сравнения двух полей А=В, не являются аргументами поиска и условия с наличием в сравнении отрицания - NOT, NOT IN(), <>; (!=), NOT EXISTS; так как условие отрицания не позволяет ограничить область поиска, а наоборот, требует проверки каждого конкретного значения на предмет удовлетворения условию (может иногда переписать условие, избежав отрицания А<>0. А типа tinyint=> A>0).

Вторая фаза оптимизации – выбор индексов. При этом проверяется, существует ли условие по полю, на котором настроен индекс. При этом условии должно быть аргументом поиска или условием соединения. Если существует индекс, который может быть задействован для доступа к данным, определяется избирательность заданного условия.

Избирательность условия – это отношение количества записей, удовлетворяющих условию, к общему количеству записей в таблице. С помощью индексной статистики, расположенной на статистической странице, оптимизатор определяет, сколько записей удовлетворяет условию. Оптимизатор пытается использовать диаграмму распределения индекса или применяется плотность индекса. Если статистической страницы нет, используются специальные защитные соотношения – «волшебные проценты», которые зависят от условия и имеют величины:

Условие Избирательность

= 100%

одностороннее сравнение(<,>) 33%

интервал 25%

Статистическая страница не создается, если индекс строится на таблице, в которой нет данных. При выполнении команды TRUNCATE TABLE уничтожаются все статистические страницы для индексов данной таблицы. Чтобы создать статистическую страницу следует выполнить команду UPDATE STATISTICS. Для каждого индекса оптимизатор строит одну статистическую страницу и включает диаграмму распределения и плотность индекса. Статистическая страница имеет объем 2 Кб. Диаграмма – это гистограмма. Количество шагов =длине сводного пространства 2016 байт делится на длину строки шага = 2 байта (под номер шага)+количество байт под значение ключа (тип индекса). Затем число строк делится на количество шагов. В каждый шаг попадает определенное количество значений индексов. Если в запросе задано условие равенства и имеется уникальный индекс количества искомых записей по определению не больше 1. При этом оптимизатор полагает количество отбираемых записей единицей и не обращается к статистике независимо от ее наличия. Оценив количество записей в таблице, предположительно удовлетворяющих условию запроса, оптимизатор рассчитывает количество логических чтений страниц, необходимое для их считывания. Число страниц, которое должны быть прочитаны, зависит не только от количества записей, но и от метода доступа, выбранного оптимизатором, для получения этих записей. Оптимизатор производит расчет для всех возможных методов доступа. Количество логических чтений для него равно общему количеству страниц данных в таблице. Если возможно применение кластерного индекса, расчет производится по формуле:

Logic_Reads=Cl_IndLevels+N_Data_Pages, где

Количество Кол-во уровней предполагаемое

логических кластерного кол-во страниц

чтений индекса данных = целочисленному кол-ву выбранных записей/ среднее кол-во записей на странице данных.

Среднее кол-во записей на странице во всех формулах определяется по таблице Sysindexes. Если применяется некластерный индекс, модель расчета меняется. SQL Server считывает ключевые значения с нижнего индексного уровня. Для каждой индексной записи по указателю производится считывание со страниц данных. При этом каждой записи данных соответствует чтение страницы. SQL Server не проверяет, ссылаются ли указатели на одну и ту же страницу данных. Всегда предполагается худший сценарий – все требуемые записи располагаются на разных страницах. Формула для использования некластерного индекса:

Logical_Reads=N_Ind_Levels+N_Ind_Pages+N_Records

N_Ind_Levels – количество уровней некласерного индекса.

N_Ind_Pages – кол-во страниц нижнего индексного уровня = оценочное кол-во выбранных записей /среднее количество записей на странице нижнего индексного уровня/

N_Records – кол-во отобранных записей. Количество чтений страниц положено в основу определения стоимости запроса. При оценке стоимости главным является временной аспект. Чтение рассматривается как наиболее затратный по времени процесс, поэтому остальные процессы оптимизатор игнорирует.

Выбор порядка соединения таблиц – третья основная фаза оптимизации запроса, цель которой – выбор лучшей, с точки зрения минимизации количества операций чтения, последовательности таблиц. Когда в запросе указано несколько таблиц, требуется выбрать оптимальный план соединения таблиц. Оптимизатор рассматривает возможные варианты, оценивает необходимое количество чтений, и выбирает наименее затратный муть. Версия 6.5. использует только одну технику соединения – «вложенные циклы». Многотабличный запрос в принципе обрабатывается следующим образом: таблицы выстраиваются в определенном порядке. Таблица, обрабатываемая первой, называется внешней (Outer), остальные таблицы называются внутренними (inner). Любые соединения таблицы в цепи являются по отношению друг к другу внешней и внутренней. Первую таблицу в цепи часто называют самой внешней (outermost). Выполняется проход на самой внешней таблице. Для каждой записи внешней таблицы из внутренней выбирается записи, удовлетворяющие условием соединения и другим табличным ограничениям. Процесс продолжается до достижения самой внутренней таблицы – получается последовательность вложенных циклов.

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

Дата добавления: 2014-10-15