История стара как мир. Две таблицы:
Нужно выбрать первых 100 записей People, отсортированных по Cites.
Засучив рукава, бодро пишем:
Select top 100 p.Name, c.Name as City from People p
order by c.Name
При этом мы получим что-то вроде:
За… 6 секунд. (MS SQL 2008 R2, i5 / 4Gb)
Но как же так! Откуда 6 секунд?! Мы ведь знаем, что в первых 100 записях будет исключительно Алматы! Ведь записей – 10 миллионов, и значит на город приходится по 100 тыс. Даже если это и не так, мы ведь можем выбрать первый город в списке, и проверить, наберется ли у него хотя бы 100 жителей.
Почему SQL сервер, обладая статистикой, не делает так:
Select * from People p
left join Cities c on c.Id=p.CityId
where p.CityId
in (select top 1 id from Cities order by Name)
order by c.
Данный запрос возвращает примерно 100 тыс. записей менее чем за секунду! Убедились, что есть искомые 100 записей и отдали их очень-очень быстро.
Однако MSSQL делает все по плану. А план у него, «чистый термояд» (с).
Вопрос к знатокам:
каким образом необходимо исправить SQL запрос или сделать какие-то действия над сервером, чтобы получить по первому запросу результат в 10 раз быстрее?
P.S.
CREATE TABLE . (
uniqueidentifier
ON
GO
CREATE TABLE . (
uniqueidentifier NOT NULL,
nvarchar(50) NOT NULL,
ON
GO
P.P.S
Откуда растут ноги:
Задача вполне реальная. Есть таблица с основной сущностью, от нее по принципу «звезда» отходит множество измерений. Пользователю нужно ее отобразить в гриде, предоставив сортировку по полям.
Начиная с некоторого размера основной таблицы сортировка сводится к тому, что выбирается окно с одинаковыми (крайними) значениями, (вроде «Алматы») но при этом система начинает жутко тормозить.
Хочется иметь ОДИН параметризированный запрос, который будет эффективно работать как с малым размером таблицы People так и с большим.
P.P.P.S
Интересно, что если бы City были бы NotNull и использовался InnerJoin то запрос выполняется мгновенно.
Интересно, что ДАЖЕ ЕСЛИ поле City было бы NotNull но использовался LeftJoin – то запрос тормозит.
В комментах идея: Сперва выбрать все InnerJoin а потом Union по Null значениям. Завтра проверю эту и остальные безумные идеи)
P.P.P.P.S Попробовал. Сработало!
WITH Help AS
select top 100 p.Name, c.Name as City from People p
INNER join Cities c on c.Id=p.CityId
order by c.Name ASC
UNION
select top 100 p.Name, NULL as City from People p
WHERE p.CityId IS NULL
SELECT TOP 100 * FROM help
Дает 150 миллисекунд при тех же условиях! Спасибо
Наверное, каждый 1С-ник задавался вопросом "что быстрее, соединение или условие в ГДЕ?" или, например, "сделать вложенный запрос или поставить оператор В()"?
После чего 1С-ник идет на форум, а там ему говорят - надо смотреть план запроса. Он смотрит, и ничего не понимая, навсегда забрасывает идею оптимизации запросов через планы, продолжая сравнивать варианты простым замером производительности.
В результате, на машине разработчика запрос начинает просто летать, а затем в боевой базе при увеличении количества записей все умирает и начинаются жалобы в стиле "1С тормозит". Знакомая картинка, не правда ли?
В данной статье я не дам вам исчерпывающих инструкций по чтению планов запроса. Но я постараюсь объяснить доходчиво - что это такое и с какой стороны к ним подойти.
Более того, я не считаю себя хорошим оптимизатором запросов, поэтому, в статье весьма вероятны фактологические косяки. Ну тут пусть гуру меня поправят в каментах. На то мы тут и сообщество, чтобы помогать друг-другу, верно?
Если вы уже умеете читать планы запросов, то, наверное, стоит пропустить статью. Тут будет самое простое и с начала начал. Статья ориентирована на тех разработчиков, которые пока еще не выяснили, что это за зверь - план запроса.
А начну я издалека. Дело в том, что компьютеры, к которым мы привыкли, они не такие уж и умные. Вы же наверняка помните первые уроки информатики, или младшие курсы ВУЗа? Помните сортировку массивов пузырьком там, или чтение файла построчно? Так вот, принципиально нового ничего не изобретено в современных реляционных СУБД.
Если на лабораторках вы считывали строчки из файла, а потом записывали их в другое место, то вы уже примерно представляете, как работает современная СУБД. Да, разумеется, там все намного (совсем намного) сложнее, но - циклы они и в Африке циклы, чтение диска все еще не стало быстрее чтения ОЗУ, а алгоритмы O(N) все еще медленнее алгоритмов O(1) при увеличении N.
Давайте представим, что к вам, простому 1С-нику пришел человек и говорит: "смотри, дружище, надо написать базу данных. Вот тут файл, в нем строчки какие-нибудь пиши. А потом оттуда читай". Представим, что отказаться вы не можете. Как бы вы решали эту задачу?
А решали бы вы ее точно так же, как решают ее ребята из Microsoft, Oracle, Postgres и 1С. Вы бы открыли файл средствами вашего языка программирования, прочитали бы оттуда строки и вывели бы их на экран. Никаких принципиально отличных алгоритмов, от тех, что я уже описал - мир не придумал.
Представьте, что у вас есть 2 файла. В одном записаны контрагенты, а в другом - договоры контрагентов. Как бы вы реализовывали операцию ВНУТРЕННЕЕ СОЕДИНЕНИЕ? Вот прямо в лоб, без каких-либо оптимизаций?
Контрагенты
Договоры
|
IDКонтрагента |
НомерДоговора |
|
Давайте сейчас для простоты опустим нюансы открывания файлов и чтения в память. Сосредоточимся на операции соединения. Как бы вы его делали? Я бы делал так:
Для Каждого СтрокаКонтрагент Из Контрагенты Цикл Для Каждого СтрокаДоговор Из Договоры Цикл Если СтрокаДоговор.IDКонтрагента = СтрокаКонтрагент.ID Тогда ВывестиРезультатСоединения(СтрокаКонтрагент, СтрокаДоговор); КонецЕсли; КонецЦикла; КонецЦикла;
В примере ф-я ВывестиРезультатСоединения просто выведет на экран все колонки из переданных строк. Ее код здесь не существенен.
Итак, мы видим два вложенных цикла. Внешний по одной таблице, а потом во внутреннем - поиск ключа из внешней простым перебором. А теперь, внезапно, если вы откроете план какого-нибудь запроса с СОЕДИНЕНИЕМ в любой из 1С-ных СУБД, то с довольно высокой вероятностью увидите там конструкцию "Nested Loops". Если перевести это с языка вероятного противника на наш, то получится "Вложенные циклы". То есть, в "плане запроса" СУБД вам объясняет, что вот тут, для "соединения" она применила алгоритм, описанный выше. Этот алгоритм способен написать любой школьник примерно 7-го класса. И мощные боевые СУБД мирового уровня применяют этот алгоритм совершенно спокойно. Ибо в некоторых ситуациях - он лучшее, что есть вообще.
И вообще, чего это я сразу с "соединения" начал. Давайте предположим, что вам нужно просто найти контрагента по наименованию. Как бы вы решали эту задачу? Вот есть у вас файл с контрагентами. Напишите алгоритм. Я напишу его вот так:
Для Каждого СтрокаКонтрагент Из Контрагенты Цикл Если СтрокаКонтрагент.Имя = "Иванов" Тогда ВывестиРезультат(СтрокаКонтрагент); КонецЕсли; КонецЦикла;
Нет, ну серьезно, а как еще его можно написать? А никак по сути. Если неизвестно в каком порядке лежат записи в таблице, то придется пересмотреть ее всю, как ни крути. На языке планов запроса это называется Scan. Сканирование. Полный просмотр данных и ничего больше.
А как же мы можем ускорить поиск данных в таблице? Ну правда, всё время пересматривать всё - это же зло какое-то.
Вспомним картотеку в поликлинике или библиотеке. Как там выполняется поиск по фамилии клиента? В деревянных шкафчиках стоят аккуратные карточки с буквами от А до Я. И пациент "Пупкин" находится в шкафчике с карточкой "П". Просматривать подряд все прочие буквы нет необходимости. Если мы отсортируем данные в нашей таблице и будем знать, где у нас (под какими номерами строк) находятся записи на букву "П", то мы существенно приблизимся к быстродействию тетеньки из регистратуры. А это уже лучше, чем полный перебор, не так ли?
Так вот, слово "Индекс" в данном контексте означает (опять же, в переводе с языка вероятного противника) "Оглавление". Чтобы быстро найти главу в книге, вы идете в оглавление, находите там название главы, потом смотрите номер страницы и идёте сразу на эту страницу.
Когда базе данных нужно найти запись в таблице, она идет в оглавление, смотрит на название контрагента, смотрит на номер записи, под которой он лежит, и идет в нужную область файла данных сразу за этой записью.
В виде кода это может выглядеть примерно так:
Индекс = Новый Соответствие; // бла-бла НомерЗаписи = Индекс["Иванов"] ВывестиРезультат(ТаблицаКонтрагентов[НомерЗаписи]);
Известно, что чудес не бывает, поэтому, память под Соответствие "Индекс", а также поиск в самом соответствии - это небесплатные операции. Но они намного дешевле, чем прямой перебор всех данных. Ах, да, это Соответствие придется постоянно поддерживать в актуальном состоянии при добавлении или изменении основных данных.
Теперь давайте подумаем, а как бы вы реализовывали сам этот индекс? Можно хранить записи в файле данных сразу в отсортированном виде. И все бы ничего, но, во-первых, искать надо каждый раз по разным полям, а во-вторых, если в уже заполненную от А до Я таблицу пользователь захочет вставить запись на букву М? А ведь он захочет, я вас уверяю.
Вспомним, как вообще ведется запись в файл.
Fseek(file, position); // переход к нужному адресу write(file, dataArray, dataLength); // запись dataLength байт из массива dataArray
Если адрес position указывает куда-то в середину файла, и на этом месте есть данные, то они затираются новыми. Если нужно вставить что-то в середину файла (и массива в памяти в том числе) то нужно в явном виде "подвинуть" все, что находится после position, освободив место, а уже потом писать новые данные. Как вы понимаете, "подвижка" данных это опять же циклы и операции ввода/вывода. То есть, не так уж быстро. Ничего в компьютере "само" не происходит. Все по команде.
Вернемся к индексу. Пользователь хочет вставить что-то в середину. Хочешь не хочешь, а придется двигать данные, либо исхитряться с хранением данных в "страницах", связанных между собой в список. Физически писать в конец, или в пустое место, но как будто в середину таблицы. И потом еще обновлять в оглавлении номера записей. Они же теперь сдвинулись и индекс показывает не туда куда нужно. Вы, наверное, слышали, что индексы в БД ускоряют поиск, но замедляют вставку и удаление. Теперь, вы знаете, почему это так.
Ну так вот, мы еще не решили проблему поиска по разным полям. Мы же не можем хранить данные в файле в разном порядке. Одному пользователю по имени, а другому, скажем - по дате. Причем одновременно. Как бы вы решали эту задачу? По-моему, решение очевидно - нужно хранить отдельно данные и отдельно оглавления, отсортированные по нужным полям. Т.е. в базе данные лежат, как придется, но рядышком мы создадим файлик, где записи отсортированы по имени. Это будет индекс по полю "Имя". А еще рядышком будет другой такой же файлик, но отсортированный по полю "Дата". Для экономии места мы будем хранить в индексах не все колонки основной таблицы, а только те, по которым выполнена сортировка (чтобы быстро тут искать, находить номер записи и моментально прыгать к ней, чтоб прочитать остальные данные).
Ребята, которые пишут взрослые СУБД тоже не придумали ничего лучше. Индексы в БД устроены именно так. Все данные из таблицы лежат отсортированными рядышком в отдельной сущности. По сути, индекс, это просто еще одна таблица. И места она занимает пропорционально размеру основной таблицы, что логично. Да, там еще есть разные ухищрения, типа сбалансированных деревьев и всякого такого, но смысл не сильно меняется.
Кстати, если записывать данные в основную таблицу сразу упорядоченными, то можно не делать отдельно хранимый индекс и считать индексом саму таблицу с данными. Здорово, правда? Такой индекс называют "кластерным". Логично, что поле, по которому отсортированы записи в таблице должно стараться монотонно нарастать. Вы же помните про вставку в середину, верно?
Представьте, что у вас таблица в пять миллионов записей. И есть у нее индекс. Надо быстренько найти запись со словом "Привет". А еще представьте, что у вас такая же таблица, но с тремя записями. И тоже надо найти "Привет". Какой способ поиска выбрать? Открыть файл индекса, пробежаться по нему двоичным поиском, найти нужный номер записи, открыть файл основной таблицы, перейти к записи по ее номеру, прочитать ее? Или запустить цикл от одного до трех, проверив каждую запись на соответствие условию? Современный компьютер циклы от одного до трех выполняет просто ужас, как быстро.
Чтобы принять решение, планировщик запроса должен понимать, с чем имеет дело. Он оперирует такой штукой, как статистика. Статистика включает в себя количество записей по таблицам, распределение данных по колонкам, селективность и прочее и прочее. Это все подсказки планировщику о том, какой способ сбора данных будет быстрее. Не самым быстрым из возможных, а хотя бы достаточно быстрым с некоторой вероятностью. И у планировщика ограничено время на принятие решения. мы же хотим быстро получить данные, а не ждать, пока он там планирует себе.
Вот тут уже я бы не стал браться за работу по написанию планировщика, не защитив предварительно диссертацию. Как он там работает и как умудряется делать это вполне сносно - не знаю. Поэтому, ограничимся документацией СУБД. Из нее следует, что на основании статистики планировщик строит несколько возможных вариантов пошагового выполнения запроса, а потом выбирает из них наиболее подходящий. Например, первый попавшийся. Тоже ведь эвристика, разве нет?
"Что мне сделать сначала" - думает планировщик: "обойти всю таблицу А, отобрав записи по условию, а потом соединить с таблицей Б вложенными циклами, или же найти индексом все подходящие записи таблицы Б, а уже потом пробежаться по таблице А"? Каждый из шагов имеет определенный вес или стоимость. Чем больше стоимость, тем сложнее выполнять. В плане запросов всегда написана стоимость каждого из шагов, которые выполнил движок СУБД, чтобы собрать результаты запроса.
Каждый шаг плана запроса реализован в виде некоторого объекта, на вход которого подается одно множество записей, а на выходе получается другое. Если представить это в виде кода, то получится, что оператор плана запросов представляет собой реализацию абстрактного интерфейса с одним методом:
Interface IQueryOperator { DataRow GetNextRow(); }
для тех кто не понял, что тут написано, поясню. Каждый оператор плана запросов имеет метод "ДайСледующуюЗапись". Движок СУБД дергает оператор за этот метод и при каждом таком дергании добавляет полученную запись к результату запроса. Например, оператор фильтрации записей на входе имеет всю таблицу, а на выходе - только те, которые удовлетворяют условию. Далее, выход этого оператора подается на оператор, например, ПЕРВЫЕ 100, а далее на оператор агрегации (СУММА или КОЛИЧЕСТВО), которые точно так же, внутри инкапсулируют всю обработку, а на выход выдают запись с результатом.
Схематично это выглядит так:
ВсеДанные ->Фильтр(Имя="Петров")->Первые(100)->Аггрегация(КОЛИЧЕСТВО)
Когда вы откроете план запроса, то увидите кубики, соединенные стрелочками. Кубики - это операторы. Стрелочки - направление потоков данных. Данные бегут по стрелочкам от одного оператора к другому, сливаясь в конце в результат запроса.
Каждый оператор имеет некие параметры: количество обработанных записей, стоимость, количество операций ввода/вывода, использование кэшей и прочее и прочее. Все это позволяет судить об эффективности выполнения запроса. Scan таблицы, пробежавший миллион записей и выдавший две на выходе - это не очень хороший план запроса. Но лучше планировщик ничего не нашел. У него не было индекса, чтобы поискать в нем. А может, наврала статистика и сказала, что в таблице три записи, а на самом деле туда успели написать миллион штук, но статистику не обновили. Все это предмет для разбирательства инженером, который изучает запрос.
План запроса - это пошаговый отладчик запроса. Вы пошагово смотрите, что именно, какой алгоритм (в буквальном смысле) применила СУБД, чтобы выдать результат. Примеры самих алгоритмов вы видели - они чрезвычайно сложны, ведь там есть циклы и условия. Даже порой несколько циклов вложены, вот ведь ужас. Важно понимать, какие процессы происходят внутри каждого оператора. Какой алгоритм применялся к массиву записей в процессе выполнения и сколько он работал.
Конкретные операторы, встречающиеся в планах запроса и их внутреннее устройство я планирую рассмотреть в следующей статье. Спасибо за то, что прочитали до конца!
1 msdevcon.ru #msdevcon
3 Олонцев Сергей SQL Server MCM, MVP Лаборатория Касперского
4 Structured Query Language
5 Пример запроса select pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber from HumanResources.Employee as emp inner join Person.Person as pers on pers.businessentityid = emp.businessentityid where pers.firstname = N"John" and emp.hiredate >= " "
6 Логическое дерево запроса Project pers.firstname, pers.lastname, emp.jobtitle, emp.nationalidnumber D A T A Filter Join pers.firstname = N"John" and emp.hiredate >= " " pers.businessentityid = emp.businessentityid Person.Person as pers Get Data Get Data HumanResources.Employee as emp
7 План запроса Показывает, как происходит исполнение T-SQL запроса на физическом уровне.
8 Несколько способов
9 DEMO Простой план Выбор всех данных из таблицы, как получить план запроса
11 Методы оператора Init() Метод Init() заставляет физический оператор инициализировать себя и подготовить все необходимые структуры данных. Физический оператор может получать много вызовов Init(), хотя обычно получает лишь один. GetNext() Метод GetNext() заставляет физический оператор получить первую или последующую строку данных. Физический оператор может получить много вызовов GetNext() или не получить ни одного. Метод GetNext() возвращает одну строку данных, а число его вызовов отображается значением ActualRows в выводе инструкции Showplan. Close() При вызове метода Close() физический оператор выполняет некоторые действия по очистке и закрывается. Физический оператор получает только один вызов Close().
12 Взаимодействие между операторами Operator 1 Operator 2 Operator 3
13 Взаимодействие между операторами 1. Request Row Operator 1 Operator 2 Operator 3
14 Взаимодействие между операторами 1. Request Row 2. Request Row Operator 1 Operator 2 Operator 3
15 Взаимодействие между операторами 1. Request Row 2. Request Row Operator 1 Operator 2 Operator 3 3. Send Row
16 Взаимодействие между операторами 1. Request Row 2. Request Row Operator 1 Operator 2 Operator 3 4. Send Row 3. Send Row
17 Взаимодействие между операторами 1. Request Row 2. Request Row Operator 1 Operator 2 Operator 3 4. Send Row 3. Send Row
18 DEMO Оператор TOP Или почему лучше оператор называть итератором
19 Таблиц не существует!
20 HoBT Page 1 Page 2 Page 3 Page 4 Row 1 Row 3 Row 5 Row 7 Row 2 Row 4 Row 6 Row 8
21 HoBT Page Page Page Page Page Page Page
22 DEMO Операторы доступа к данным Scan, Seek, Lookup
23 У кого в базе данных есть только одна таблица?
24 Nested Loops, Hash Join и Merge Join
25 Операторы соединения Nested Loops inner join, left outer join, left semi join, left anti semi join Merge Join inner join, left outer join, left semi join, left anti semi join, right outer join, right semi join, right anti semi join, union Hash Join все типы логических операций
26 DEMO Операторы соединения, сортировки и первый оператор Nested Loops, Merge Join, Hash Join, Sort, First Operator
27 Предупреждения
28 DEMO Ошибки и предупреждения в планах запросов
29 Я знаю, что ничего не знаю. Сократ
30 DEMO Небольшой пример непонятного
31 Диагностика планов запросов -- TOP 10 запросов, которые потребляю больше всего CPU и их планы select top(10) substring(t.text, qs.statement_start_offset / 2, case when qs.statement_end_offset = -1 then len(t.text) else (qs.statement_end_offset - qs.statement_start_offset) / 2 end), qs.execution_count, cast(qs.total_worker_time / as decimal(18, 2)) as total_worker_time_ms, cast(qs.total_worker_time * 1. / qs.execution_count / as decimal(18, 2)) as avg_worker_time_ms, cast(p.query_plan as xml) as query_plan from sys.dm_exec_query_stats as qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as t cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as p order by qs.total_worker_time desc; go
32 Техника чтения больших планов запросов Пробовать разбивать на логические блоки и анализировать постепенно. В SSMS при графическом отображении плана в правом нижнем углу появляется кнопка для более удобной навигации по плану запроса. Можно использовать XQuery\XPath.
33 DEMO Большой план запроса
35 DEMO SQL Sentry Plan Explorer
36 Подведем итоги Первый оператор Optimization level Compile time Size in cache Parameters, Compile Values Reason for Early Termination Стоимость итераторов Смотрите в первую очередь на операторы с самой высокой стоимостью. Не забывайте, что это всего лишь предполагаемые значения (даже в актуальных планах выполнения).
37 Подведем итоги Bookmark\Key Lookup Если их мало, то скорее всего проблемы нет. Если их много, создание покрывающего индекса поможет от них избавиться. Предупреждения Необходимо проверить, почему оно возникает и при необходимости принять меры.
38 Подведем итоги Соединения между операторами (потоки данных) Чем толще соединение, тем больше данных прошло между этими операторами. Особенно стоит обращать внимание, если на каком-то этапе поток данных резко увеличивается. Порядок соединения таблиц Чем меньше потоки данных, тем их легче соединять. Поэтому в первую очередь нужно соединять те таблицы, чей результирующий поток данных будет меньше.
39 Подведем итоги Сканирования Сканирования не означают, что есть проблема. Вполне возможно, что не хватает индекса на таблице, чтобы сделать более эффективный поиск. С другой стороны, при необходимости выбора всех или большой части таблицы сканирование будет более эффективно. Поиск не значит, что все хорошо. Большое число поисков по некластеризованным индексам может быть проблемой. Все, что вы не знаете в плане потенциально может быть проблемой
40 Вопросы
41 Контакты Олонцев Сергей Лаборатория Касперского
42 2013 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.
Рассмотрим некоторые примеры оптимизации запросов и представление соответствующих деревьев их плана выполнения. На рисунке 27 приведен запрос и план его выполнения. В данном примере таблица Клиент 1 не имеет индекса по полю Имя. Для выбора информации по условию Имя = ‘Петров 4 используется просмотр строк таблицы (т.е. последовательно читается вся таблица без использования индекса).
Рис. 27.
На рисунке 28 приведен другой запрос к этой же таблице и план его выполнения. В этом примере таблица Клиент 1 имеет некластеризо- ванный индекс по полю Кодклиента. Для выбора информации по условию Кодклиента =2 сначала используется поиск в индексе и по найденному значению RID производится обращение к соответствующей странице данных таблицы КлиентТ Таким образом, запрос с условием для индексированного поля может использовать оптимальный план с применением индекса. Для запроса с условием для неиндексированно- го поля индекс в плане использоваться не может.
Рис. 28.
На рисунках 29, 30 приведены планы выполнения для запросов к таблице Клиент, которая имеет кластеризованный индекс. На рисунках видно, что независимо от того, по какому полю задано условие в запросе, используется Кластеризованный индекс (в разделе Организация хранения данных в MS SQL Server было показано, что кластеризованный индекс объединяет дерево индекса и данные). Отличие состоит в том, что если условие в запросе задано по индексированному полю (в данном примере поле Кодк- лиента), то используется поиск в индексном дереве (Clustered Index Seek, рисунок 30). В противном случае используется последовательное сканирование индексного дерева (Clustered Index Scan, рисунок 29).
Рис. 29.
Рис. 30.
На рисунках 31, 32 приведены планы выполнения для запросов к таблице new_addresses, которая имеет некластеризованный индекс для столбца StateProvincelD. Первый запрос по условию a.StateProvincelD = 32 является высоко селективным (выбирается одна строка из 19814):
SELECT * FROM new_addresses AS a WHERE a.StateProvincelD = 32;
В этом случае из структуры плана видно (рисунок 31), что оптимизатор использует поиск в индексе для столбца StateProvincelD с последующим побращением к странице данных таблицы по найденному R1D. Оператор Nested Loops (вложенный цикл) отображается для запроса к одной таблице (внешний цикл поиск в индексе, внутренний - обращение к таблице).
Рис. 31.
Второй запрос по условию a.StateProvincelD = 9 является низко селективным (отношение количества строк, удовлетворяющему условию, к общему количеству строк равно 23%):
SELECT * FROM new_addresses а WHERE a.StateProvincelD -9;
В этом случае из структуры плана видно (рисунок 32), что оптимизатор не использует поиск в индексе для столбца StateProvincelD, а обращается непосредственно к страницам таблицы данных (индекс не используется, таблица сканируется).
Рис. 32.
Рассмотрим пример использования подсказки оптимизатору WITH(INDEX(0)). Если не существует кластеризованный индекс для таблицы, то INDEX (0) требует сканирований таблицы. При существовании кластеризованного индекса INDEX (0) определяет сканирование кластеризованного индекса.
USE AdventureWorks;
SELECT * FROM Per son. Address AS a WHERE a.StateProvincelD = 32.
Дерево плана выполнения запроса показано на рисунке 33.
Рис. 33.
USE AdventureWorks;
SELECT * FROM Person.Address AS a W1TH(INDEX(0))
WHERE a.StateProvincelD = 32.
Дерево плана выполнения запроса показано на рисунке 34. Видно, что с этой подсказкой не используется поиск в некластеризованном индексе.
Рис. 34.
Рассмотрим примеры планов выполнения запросов с разными техниками обработки соединения таблиц. В соединяемых столбцах таблиц HumanResources.Employee и HumanResources.EmployeeAddress имеются первичные ключи и, соответственно, - кластеризованные индексы. На рисунке 35 показано, что в таблице HumanResources.EmployeeAddress имеется составной кластеризованный индекс по полям EmployeelD и AddressID.
Рис. 35.
В первом запросе задано условие e.EmployeelD = 10:
USE AdventureWorks;
SELECT * FROM HumanResources.Employee AS e JOIN HumanResources.EmployeeAddress AS a ON e.EmployeelD = a.EmployeelD AND e.EmployeelD = 10;
Дерево плана запроса показано на рисунке 36. Видно, что используется техника обработки соединения Вложенные циклы. Оптимизатор принимает решение использовать вложенные циклы, так как существует дополнительный фильтр (e.EmployeelD = 10),
сокращающий результирующий набор до одной строки. Сначала производится поиск по индексу в таблице HumanResources.Employee соответствующей условию строки. Далее поиск в таблице HumanResources.EmployeeAddress. Оператор Clustered Index Seek использует поисковые возможности индексов для получения строк из кластеризованного индекса.
Рис. 36.
В данном примере последовательно производится обращение к таблицам HumanResources.Employee и HumanRe-
sources.EmployeeAddress. В такой последовательности эти таблицы указаны и в запросе (FROM HumanResources.Employee AS е JOIN HumanResources.Employee Address AS a).
Поменяем последовательность таблиц в запросе:
USE AdventureWorks;
SELECT * FROM HumanRe sources. Employ ееAddress AS a JOIN HumanResources.Employee AS e ON e.EmployeelD = a.EmployeelD AND e.EmployeelD = 10;
Дерево плана останется неизменным. Т.е. изменение последовательности таблиц в запросе не повлияло на последовательность их обработки. Это происходит потому, что оптимизатор определяет первоначальный выбор из таблицы HumanResources.Employee наилучшим (по одной найденной строке производится одно обращение к таблице
HumanResources.EmployeeAddress по найденному значению). Если использовать другую последовательность обработки таблиц, то для каждой строки таблицы HumanResources.EmployeeAddress нужно много раз обращаться таблице HumanResources.Employee и стоимость выполнения запроса будет выше.
Если из запроса удалить условие:
USE AdventureWorks;
SELECT * FROM HumanResources.Employee AS e JOIN HumanResources.EmployeeAddress AS a ON e.EmployeelD = a.EmployeelD,
то техника обработки соединения изменится. Дерево плана выполнения запроса показано на рисунке 37.
Рис. 37.
В этом запросе отсутствует условие e.EmployeelD = 10 и результирующий набор содержит много строк, но они упорядочены по индексу. В этом случае оптимизатор выбирает метод слияния соединения. Merge Join (Слияние соединения).
Рассмотрим план выполнения запроса:
USE AdventureWorks;
SELECT *
ON a.StateProvincelD = s.StateProvincelD
Таблица Person.Address, ее ключи и индексы приведены на рисунке 38.
Рис. 38.
Таблица Person.StateProvince, ее ключи и индексы приведены на рисунке 39.
Рис. 39.
Дерево плана выполнения запроса показано на рисунке 40.
Рис. 40.
Видно (рисунки 38, 39), что таблицы, участвующие в соединении, имеют кластеризованные индексы и дерево плана использует соответственно сканирование кластеризованного идекса для каждой из таблиц. Техника обработки соединения- хеширование соединения. Это происходит потому, что записи таблицы Person.Address не отсортированы по полю StateProvincelD.
Приведем пример позказки соединения. Выше был приведен запрос:
USE AdventureWorks;
SELECT * FROM Person.Address a JOIN Person.StateProvince s
ON a.StateProvincelD = s.StateProvincelD
и план его выполнения, в котором использовалась техника обработки соединения - хеширование соединения (Hash Match).
При добавлении в запрос подсказки
USE AdventureWorks;
SELECT * FROM Person.Address a JOIN Person.StateProvince s
ON a.StateProvincelD = s.StateProvincelD
OPTION (MERGE JOIN),
техника обработки соединения меняется (дерево плана показано на рисунке 41). Используется техника обработки соединения- слиянием. На рисунке видно, что предварительно выполняется операция сортировки (Sort).
Оптимизация запросов в SQL Server 2005, статистика баз данных SQL Server 2005, CREATE STATISTICS, UPDATE STATISTICS, SET NOCOUNT ON, планы выполнения запросов, количество логических чтений (logical reads), хинты оптимизатора (optimizer hints), MAXDOP, OPTIMIZE FOR, руководства по планам выполнения (plan guides), sp_create_plan_guide
Если все остальные способы оптимизации производительности уже исчерпаны, то в распоряжении разработчиков и администраторов SQL Server остается последний резерв - оптимизация выполнения отдельных запросов. Например, если в вашей задаче совершенно необходимо ускорить создание какого-то одного специфического отчета, можно проанализировать запрос, который используется при создании этого отчета, и постараться изменить его план, если он неоптимален.
Отношение к оптимизации запросов у многих специалистов неоднозначное. С одной стороны, работа программного модуля Query Optimizer , который генерирует планы выполнения запросов, вызывает множество справедливых нареканий и в SQL Server 2000, и в SQL Server 2005. Query Optimizer часто выбирает не самые оптимальные планы выполнения запросов и в некоторых ситуациях проигрывает аналогичным модулям из Oracle и Informix . С другой стороны, ручная оптимизация запросов - процесс чрезвычайно трудоемкий. Вы можете потратить много времени на такую оптимизацию и, в конце концов, выяснить, что ничего оптимизировать не удалось: план, предложенный Query Optimizer изначально, оказался наиболее оптимальным (так бывает в большинстве случаев). Кроме того, может случиться так, что созданный вами вручную план выполнения запросов через какое-то время (после добавления новой информации в базу данных) окажется неоптимальным и будет снижать производительность при выполнении запросов.
Отметим также, что для выбора наилучших планов построения запросов Query Optimizer необходима правильная информация о статистике. Поскольку, по опыту автора, далеко не все администраторы знают, что это такое, расскажем о статистике подробнее.
Статистика - это специальная служебная информация о распределении данных в столбцах таблиц. Представим, например, что выполняется запрос, который должен вернуть всех Ивановых, проживающих в городе Санкт-Петербурге. Предположим, что у 90% записей в этой таблице одно и то же значение в столбце Город - "Санкт-Петербург" . Конечно, с точки зрения выполнения запроса вначале выгоднее выбрать в таблице всех Ивановых (их явно будет не 90%), а затем уже проверять значение столбца Город для каждой отобранной записи. Однако для того, чтобы узнать, как распределяются значения в столбце, нужно вначале выполнить запрос. Поэтому SQL Server самостоятельно инициирует выполнение таких запросов, а потом сохраняет информацию о распределении данных (которая и называется статистикой) в служебных таблицах базы данных.
Для баз данных SQL Server 2005 по умолчанию устанавливаются параметры AUTO_CREATE_STATISTICS и AUTO_UPDATE_STATISTICS . При этом статистика для столбцов баз данных будет создаваться и обновляться автоматически. Для самых больших и важных баз данных может получиться так, что операции по созданию и обновлению статистики могут мешать текущей работе пользователей. Поэтому для таких баз данных иногда эти параметры отключают, а операции по созданию и обновлению статистики выполняют вручную в ночное время. Для этого используются команды CREATE STATISTICS и UPDATE STATISTICS .
Теперь поговорим об оптимизации запросов.
Первое, что необходимо сделать, - найти те запросы, которые в первую очередь подлежат оптимизации. Проще всего это сделать при помощи профилировщика, установив фильтр на время выполнения запроса (фильтр Duration в окне Edit Filter (Редактировать фильтр), которое можно открыть при помощи кнопки Column Filters на вкладке Events Selection окна свойств сеанса трассировки). Например, в число кандидатов на оптимизацию могут попасть запросы, время выполнения которых составило более 5секунд. Кроме того, можно использовать информацию о запросах, которая предоставляется Database Tuning Advisor .
Затем нужно проверить, устанавлен ли для ваших соединений, хранимых процедур и функций параметр NOCOUNT . Установить его можно при помощи команды SET NOCOUNT ON . При установке этого параметра, во-первых, отключается возврат с сервера и вывод информации о количестве строк в результатах запроса (т. е. не отображается строка "N row(s) affected" на вкладке Messages (C ообщения) окна работы с кодом при выполнении запроса в Management Studio ). Во-вторых, отключается передача специального серверного сообщения DONE_IN_PROC , которое по умолчанию возвращается для каждого этапа хранимой процедуры. При вызове большинства хранимых процедур нужен только результат их выполнения, а количество обработанных строк для каждого этапа никого не интересует. Поэтому установка параметра NOCOUNT для хранимых процедур может серьезно повысить их производительность. Повышается скорость выполнения и обычных запросов, но в меньшей степени (до 10%).
После этого можно приступать к работе с планами выполнения запросов.
План выполнения запроса проще всего просмотреть из SQL Server Management Studio . Для того чтобы получить информацию об ожидаемом плане выполнения запроса, можно в меню Query (Запрос) выбрать команду Display Estimated Execution Plan (Отобразить ожидаемый план выполнения). Если вы хотите узнать реальный план выполнения запроса, можно перед его выполнением установить в том же меню параметр Include Actual Execution Plan (Включить реальный план выполнения). В этом случае после выполнения запроса в окне результатов в SQL Server Management Studio появится еще одна вкладка Execution Plan (План выполнения), на которой будет представлен реальный план выполнения запроса. При наведении указателя мыши на любой из этапов можно получить о нем дополнительную информацию (рис. 11.15).
Рис. 11.15. План выполнения запроса в SQL Server Management Studio
В плане выполнения запроса, как видно на рисунке, может быть множество элементов. Разобраться в них, а также предложить другой план выполнения - задача достаточно сложная. Надо сказать, что каждый из возможных элементов оптимален в своей ситуации. Поэтому обычно этапы оптимизации запроса выглядят так:
q вначале в окне Management Studio выполните команду SET STATISTICS IO ON . В результате после каждого выполнения запроса будет выводиться дополнительная информация. В ней нас интересует значение только одного параметра - Logical Reads . Этот параметр означает количество логических чтений при выполнении запросов, т. е. сколько операций чтения пришлось провести при выполнении данного запроса без учета влияния кэша (количество чтений и из кэша, и с диска). Это наиболее важный параметр. Количество физических чтений (чтений только с диска) - информация не очень представительная, поскольку зависит от того, были ли перед этим обращения к данным таблицам или нет. Статистика по времени также является величиной переменной и зависит от других операций, которые выполняет в это время сервер. А вот количество логических чтений - наиболее объективный показатель, на который в наименьшей степени влияют дополнительные факторы;
q затем пытайтесь изменить план выполнения запроса и узнать суммарное количество логических чтений для каждого плана. Обычно план выполнения запроса изменяется при помощи хинтов (подсказок) оптимизатора. Они явно указывают оптимизатору, какой план выполнения следует использовать.
Хинтов оптимизатора в SQL Server 2005 предусмотрено много. Прочитать информацию о них можно в Books Online (в списке на вкладке Index (Индекс) нужно выбрать Query Hints [ SQL Server ] (Хинты запросов ), Join Hints (Хинты джойнов) или Table Hints [ SQL Server ] (Табличные хинты )). Чаще всего используются следующие хинты:
q NOLOCK , ROWLOCK , PAGLOCK , TABLOCK , HOLDLOCK , READCOMMITTEDLOCK , UPDLOCK , XLOCK - эти хинты используются для управления блокировками (см. разд. 11.5.7) ;
q FAST количество_строк - будет выбран такой план выполнения запроса, при котором максимально быстро будет выведено указанное количество строк (первых с начала набора записей). Если пользователю нужны именно первые записи (например, последние заказы), то для их максимально быстрой загрузки в окно приложения можно использовать этот хинт;
q FORCE ORDER - объединение таблиц при выполнении запроса будет выполнено точно в том порядке, в котором эти таблицы перечислены в запросе;
q MAXDOP (от Maximum Degree of Parallelism - максимальная степень распараллеливания запроса) - при помощи этого хинта указывается максимальное количество процессоров, которые можно будет использовать для выполнения запроса. Обычно этот хинт используется в двух ситуациях:
· когда из-за переключения между процессорами (context switching ) скорость выполнения запроса сильно снижается. Такое поведение было характерно для SQL Server 2000 на многопроцессорных системах;
· когда вы хотите, чтобы какой-то тяжелый запрос оказал минимальное влияние на текущую работу пользователей;
q OPTIMIZE FOR - этот хинт позволяет указать, что запрос оптимизируется под конкретное значение передаваемого ему параметра (например, под значение фильтра для WHERE );
q USE PLAN - это самая мощная возможность. При помощи такого хинта можно явно определить план выполнения запроса, передав план в виде строкового значения в формате XML . Хинт USE PLAN появился только в SQL Server 2005 (в предыдущих версиях была возможность явно определять планы выполнения запросов, но для этого использовались другие средства). План в формате XML можно написать вручную, а можно сгенерировать автоматически (например, щелкнув правой кнопкой мыши по графическому экрану с планом выполнения, представленному на рис. 11.15, и выбрав в контекстном меню команду Save Execution Plan As (Сохранить план выполнения как)).
В SQL Server 2005 появилась новая важная возможность, которая позволяет вручную менять план выполнения запроса без необходимости вмешиваться в текст запроса. Очень часто бывает так, что код запроса нельзя изменить: он жестко "прошит" в коде откомпилированного приложения. Чтобы справиться с этой проблемой, в SQL Server 2005 появилась хранимая процедура sp_create_plan_guide . Она позволяет создавать так называемые руководства по планам выполнения (plan guides ), которые будут автоматически применяться к соответствующим запросам.
Если вы анализируете запросы, которые направляет к базе данных какое-то приложение, то имеет смысл в первую очередь обратить внимание на следующие моменты:
q насколько часто в планах выполнения запроса встречается операция Table Scan (Полное сканирование таблицы). Вполне может оказаться, что обращение к таблице при помощи индексов будет эффективнее;
q используются ли в коде курсоры. Курсоры - очень простое средство с точки зрения синтаксиса программы, но чрезвычайно неэффективное с точки зрения производительности. Очень часто можно избежать применения курсоров, используя другие синтаксические конструкции, и получить большой выигрыш в скорости работы;
q используются ли в коде временные таблицы или тип данных Table . Создание временных таблиц и работа с ними требуют большого расхода ресурсов, поэтому по возможности нужно их избегать;
q кроме создания временных таблиц, значительного расхода системных ресурсов требует и изменение их структуры. Поэтому команды на изменение структуры временных таблиц должны сразу привлекать ваше внимание. Обычно есть возможность сразу создать временную таблицу со всеми необходимыми столбцами;
q иногда запросы возвращают больше данных, чем реально требуется приложению (лишнее количество столбцов или строк). Конечно, это не способствует повышению производительности;
q если приложение передает на сервер команды EXECUTE , то имеет смысл подумать о том, чтобы заменить их на вызов хранимой процедуры sp_executesql . Она обладает преимуществами в производительности по сравнению с обычной командой EXECUTE ;
q повышения производительности иногда можно добиться, устранив необходимость повторной компиляции хранимых процедур и построения новых планов выполнения запросов. Нужно обратить внимание на применение параметров, постараться не смешивать в коде хранимой процедуры команды DML и DDL и следить за тем, чтобы параметры подключения SET ANSI_DEFAULTS , SET ANSI_NULLS , SET ANSI_PADDING , SET ANSI_WARNINGS и SET CONCAT_NULL_YIELDS_NULL не изменялись между запросами (любое изменение таких параметров приводит к тому, что старые планы выполнения считаются недействительными). Обычно проблема может возникнуть тогда, когда эти параметры устанавливаются на уровне отдельного запроса или в коде хранимой процедуры.
Отметим, что в любом случае создание планов выполнения запросов вручную и использование хинтов - это крайнее средство, которого следует по возможности избегать.
