Оптимизация SQL-запросов. Причины неэффективности SQL-запросов в Oracle. Оптимизация производительности SQL-запросов

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

1. Оптимизация таблиц . Необходима, когда было произведено много изменений в таблице: либо удалена большая часть данных, либо много изменений со строками переменной длины - text, varchar, blob. Дело в том, что удалённые записи продолжают поддерживаться в индексном файле, и при последующей вставке новых записей используются позиции старых записей. Чтобы дефрагментировать файл с данными, используюется команда OPTIMIZE.

   OPTIMIZE TABLE `table1`, `table2`…

   Не стоит забывать, что во время выполнения оптимизации, доступ к таблице блокируется.

Перестройка данных в таблице . После частых изменений в таблице, данная команда может повысить производительность работы с данными. Она перестраивает их в таблице и сортирует по определённому полю.

ALTER TABLE `table1` ORDER BY `id`

Тип данных . Лучше не индексировать поля, имеющие строковый тип, особенно поля типа TEXT. Для таблиц, данные которых часто изменяются, желательно избегать использования полей типа VARCHAR и BLOB, так как данный тип создаёт динамическую длину строки, тем самым увеличивая время доступа к данным. При этом советуют использовать поле VARCHAR вместо TEXT, так как с ним работа происходит быстрее.

NOT NULL и поле по умолчанию . Лучше всего помечать поля как NOT NULL, так как они немного экономят место и исключают лишние проверки. При этом стоит задавать значение полей по умолчанию и новые данные вставлять только в том случае, если они от него отличаются. Это ускорит добавление данных и снизит время на анализ таблиц. И стоит помнить, что типы полей BLOB и TEXT не могут содержать значения по умолчанию.

Постоянное соединение с сервером БД . Позволяет избежать потерь времени на повторное соединение. Однако стоит помнить, что у сервера может быть ограничение на количество соединений, и в том случае, если посещаемость сайта очень высокая, то постоянное соединение может сыграть злую шутку.

Разделение данных. Длинные не ключевые поля советуют выделить в отдельную таблицу в том случае, если по исходной таблице происходит постоянная выборка данных и которая часто изменяется. Данный метод позволит сократить размер изменяемой части таблицы, что приведёт к сокращению поиска информации.
Особенно это актуально в тех случаях, когда часть информации в таблице предназначена только для чтения, а другая часть - не только для чтения, но и для модификации (не забываем, что при записи информации блокируется вся таблица). Яркий пример - счётчик посещений.
Есть таблица (имя first) с полями id, content, shows. Первое ключевое с auto_increment, второе - текстовое, а третье числовое - считает количество показов. Каждый раз загружая страницу, к последнему полю прибавляется +1. Отделим последнее поле во вторую таблицу. Итак, первая таблица (first) будет с полями id, content, а вторая (second) с полями shows и first_id. Первое поле понятно, второе думаю тоже - отсыл к ключевому полю id из первой таблицы.
Теперь постоянные обновления будут происходить во второй таблице. При этом изменять количество посещений лучше не программно, а через запрос:

А выборка будет происходить усложнённым запросом, но одним, двух не нужно:

SELECT first.id, first.content, second.first_id, second.shows FROM second INNER JOIN first ON (first.id = second.first_id)

Стоит помнить, что всё это не актуально для сайтов с малой посещаемостью и малым количеством информации.

Имена полей , по которым происходит связывание, к примеру, двух таблиц, желательно, чтобы имели одинаковое название. Тогда одновременное получение информации из разных таблиц через один запрос будет происходить быстрее. Например, из предыдущего пункта желательно, чтобы во второй таблице поле имело имя не first_id, а просто id, аналогично первой таблице. Однако при одинаковом имени становится внешне не очень наглядно что, куда и как. Поэтому совет на любителя.

Требовать меньше данных . При возможности избегать запросов типа:

SELECT * FROM `table1`

Запрос не эффективен, так как скорее всего возвращает больше данных, чем необходимо для работы. Вариантом лучше будет конструкция:

SELECT id, name FROM table1 ORDER BY id LIMIT 25

Тут же сделаю добавление о желательности использования LIMIT. Данная команда ограничивает количество строк, возвращаемых запросом. То есть запрос становится "легче" и производительнее.
Если стоит LIMIT 10, то после получения десяти строк запрос прерывается.
Если в запросе применяется сортировка ORDER BY, то она происходит не по всей таблице, а только по выборке.
Если использовать LIMIT совместно с DISTINCT, то запрос прервётся после того, как будет найдено указанное количество уникальных строк.
Если использовать LIMIT 0, то возвращено будет пустое значение (иногда нужно для определения типа поля или просто проверки работы запроса).

Ограничить использование DISTINCT . Эта команда исключает повторяющиеся строки в результате. Команда требует повышенного времени обработки. Лучше всего комбинировать с LIMIT.
Есть маленькая хитрость. Если необходимо просмотреть две таблицы на тему соответствия, то приведённая команда остановится сразу же, как только будет найдено первое соответствие.

Ограничить использование SELECT для постоянно изменяющихся таблиц .

2. Не забывайте про временные таблицы типа HEAP . Несмотря на то, что таблица имеет ограничения, в ней удобно хранить промежуточные данные, особенно когда требуется сделать ещё одну выборку из таблицы без повторного обращения. Дело в том, что эта таблица хранится в памяти и поэтому доступ к ней очень быстрый.
3. Поиск по шаблону . Зависит от размера поля и если уменьшить размер с 400 байтов до 300, то время поиска сократиться на 25%.

Поделюсь опытом, который получил за несколько лет оптимизации sql запросов. Большая часть советов касается субд ORACLE.
Если кому статья покажется слишком очевидной, то считайте это заметкой чисто для себя, чтобы не забыть.

1. Ни каких подзапросов, только JOIN
Как я уже писал ранее , если выборка 1 к 1 или надо что-то просуммировать, то ни каких подзапросов, только join.
Стоит заметить, что в большинстве случаев оптимизатор сможет развернуть подзапрос в join, но это может случиться не всегда.

2. Выбор IN или EXISTS ?
На самом деле это сложный выбор и правильное решение можно получить только опытным путем.
Я дам только несколько советов:
* Если в основной выборке много строк, а в подзапросе мало, то ваш выбор IN . Т.к. в этом случае запрос в in выполнится один раз и сразу ограничит большую основную таблицу.
* Если в подзапросе сложный запрос, а в основной выборке относительно мало строк, то ваш выбор EXISTS . В этом случае сложный запрос выполнится не так часто.
* Если и там и там сложно, то это повод изменить логику на джойны.

3. Не забывайте про индексы
Совет для совсем новичков: вешайте индексы на столбцы по которым джойните таблицы.

4. По возможности не используйте OR.
Проведите тесты, возможно UNION выглядит не так элегантно, за то запрос может выполнится значительно быстрей. Причина в том, что в случае OR индексы почти не используются в join.

5. По возможности не используйте WITH в oracle.
Значительно облегчает жизнь, если запрос в with необходимо использовать несколько раз (с хинтом materialize) в основной выборке или если число строк в подзапросе не значительно.
Во всех других случаях необходимо использовать прямые подзапросы в from или взаранее подготовленную таблицу с нужными индексами и данными из WITH.
Причина плохой работы WITH в том, что при его джойне не используются ни какие индексы и если данных в нем много, то все встанет. Вторая причина в том, что оптимизатору сложно определить сколько данных нам вернет with и оптимизатор не может построить правильный план запроса.
В большинстве случаев WITH без +materialize все равно будет развернут в основной запрос.

6. Не делайте километровых запросов
Часто в web обратная проблема - это много мелких запросов в цикле и их советуют объединить в один большой. Но тут есть свои ограничения, если у вас запрос множество раз обернутый в from, то внутреннюю(ие) части надо вынести в отдельную выборку, заполнить временную таблицу, навесить индексы, а потом использовать ее в основной выборке. Скорость работы будет значительно выше (в первую очередь из-за сложности построения оптимального плана на большом числе сочетаний таблиц)

7. Используйте KEEP взамен корреляционных подзапросов.
В ORACLE есть очень полезные аналитические функции , которые упростят ваши запросы. Один из них - это KEEP.
KEEP позволит сделать вам сортировку или группировку основной выборки без дополнительно запроса.
Пример: отобрать контрагента для номенклатуры, который раньше остальных был к ней подвязан. У одной номенклатуры может быть несколько поставщиков.
SELECT n.ID, MIN(c.ID) KEEP (DENSE_RANK FIRST ORDER BY c.date ASC) as cnt_id FROM nmcl n, cnt c WHERE n.cnt_id = c.id GROUP BY n.ID При обычном бы подходе пришлось бы делать корреляционный подзапрос для каждой номенклатуры с выбором минимальной даты.
Но не злоупотребляйте большим числом аналитических функций, особенно если они имеют разные сортировки. Каждая разная сортировка - это новое сканирование окна.

8. Гуляние по выборке вверх-вниз
Менее популярная функция, но не менее полезная. Позволяет смещать текущую строку выборки на N элементов вверх или вниз. Бывает полезно, если необходимо сравнить показатели рядом стоящих строк.
Следующий пример отбирает продажи департаментов отсортированных по дате. К основной выборке добавляются столбцы со следующим и предыдущим значением выручки. Второй параметр - это на сколько строк сместиться, третьи - параметр по-умолчанию, если данные соседа не нашлись. SELECT deptno, empno, sal, LEAD(sal, 1, 0) OVER (PARTITION BY dept ORDER BY date) NEXT_LOWER_SAL, LAG(sal, 1, 0) OVER (PARTITION BY dept ORDER BY date) PREV_HIGHER_SAL FROM emp; ORDER BY deptno, date DESC; При обычном подходе бы пришлось это делать через логику приложения.

9. Direct Path Read
Установка этой настройки (настройкой или параллельным запросом) - чтение данных напрямую в PGA, минуя буферный кэш. Что укоряет последующие этапы запроса, т.к. не используется UNDO и защелки совместного доступа.

10. Direct IO
Использование прямой записи/чтения с диска без использования буфера файловой системы (файловая система конкретно для СУБД).
* В случае чтения преимущество в использовании буферного кэша БД, замен кэша ФС (кэш бд лучше заточен на работу с sql)
* В случае записи, прямая запись гарантирует, что данные не потеряются в буфере ФС в случае выключения электричества (для redolog всегда использует fsync, в не зависимости от типа ФС)

Пять простых оптимизаций, которые можно реализовать на основе одних лишь метаданных (т. е. ограничений) и самого запроса Предлагаем вам адаптацию статьи Лукаса Эдера, рассчитанную на тех, кто имеет общее представление о базах данных и SQL, а также небольшой практический опыт работы с СУБД. Стоимостная оптимизация – фактически стандартный способ оптимизации SQL-запросов в современных базах данных. Именно поэтому настолько сложно написать вручную сложный алгоритм на 3GL (языках программирования третьего поколения) , производительность которого превышала бы динамически рассчитываемый план выполнения, сгенерированный современным оптимизатором. Сегодня мы не будем обсуждать стоимостную оптимизацию, то есть оптимизацию на основе стоимостной модели базы данных. Мы рассмотрим гораздо более простые оптимизации. Те,которые можно реализовать на основе одних лишь метаданных (т. е. ограничений) и самого запроса. Обычно их реализация для базы данных – не бином Ньютона, поскольку, в данном случае, любая оптимизация приведет к лучшему плану выполнения, независимо от наличия индексов, объемов данных и асимметрии распределения данных. "Не бином Ньютона" не в смысле легкости реализации оптимизации, а в том, следует ли это делать. Эти оптимизации устраняют [для базы данных] ненужную, дополнительную работу ().

Для чего эти оптимизации применяются?

• исправления ошибок в запросах;
• обеспечения повторного использования представлений без фактического выполнения логики представления базой данных.

Используемые базы данных

• Oracle 12.2;
• MySQL 8.0.2;
• SQL Server 2014;
• PostgreSQL 9.6;
• DB2 LUW 10.5.

1. транзитивное замыкание;
2. невозможные предикаты и ненужные обращения к таблицам;
3. устранение JOIN;
4. устранение "бессмысленных" предикатов;
5. проекции в подзапросах EXISTS;
6. cлияние предикатов;
7. доказуемо пустые множества;
8. oграничения CHECK;
9. ненужные рефлексивные соединения;
10. Pushdown предикатов

1. Транзитивное замыкание

Начнем с чего-нибудь попроще: транзитивного замыкания . Это тривиальное понятие, применимое ко множеству математических операций, например, оператору равенства. Его можно сформулировать в этом случае следующим образом: если A = B и B = C, то A = C.

Несложно, правда? Но это влечет некоторые интересные последствия для оптимизаторов SQL. Рассмотрим пример. Извлечем все фильмы с ACTOR_ID = 1: SELECT first_name, last_name, film_id FROM actor a JOIN film_actor fa ON a. actor_id = fa. actor_id WHERE a. actor_id = 1 ; Результат следующий: FIRST_NAME LAST_NAME FILM_ID PENELOPE GUINESS 1 PENELOPE GUINESS 23 PENELOPE GUINESS 25 PENELOPE GUINESS 106 PENELOPE GUINESS 140 PENELOPE GUINESS 166 . . . Взглянем теперь на план выполнения этого запроса в случае СУБД Oracle: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- | Id | Operation | Name | Rows | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- | 0 | SELECT STATEMENT | | | | 1 | NESTED LOOPS | | 19 | | 2 | TABLE ACCESS BY INDEX ROWID| ACTOR | 1 | | * 3 | INDEX UNIQUE SCAN | PK_ACTOR | 1 | | * 4 | INDEX RANGE SCAN | PK_FILM_ACTOR | 19 | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Predicate Information (identified by operation id) : -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - 3 - access ("A" . "ACTOR_ID" = 1 ) 4 - access ("FA" . "ACTOR_ID" = 1 ) Особенно тут интересен раздел предикатов. Предикат ACTOR_ID = 1, вследствие транзитивного замыкания применяется как к таблице ACTOR, так и таблице FILM_ACTOR. Если: A. ACTOR_ID = 1 (из предиката WHERE) и… A. ACTOR_ID = FA. ACTOR_ID (из предиката ON) То: FA. ACTOR_ID = 1 В случае более сложных запросов это приводит к некоторым весьма приятным результатам. В частности, точность оценок кардинальности существенно повышается, так как появляется возможность подбора оценок на основе конкретного константного значения предиката, а не, например, среднего числа фильмов по актерам, как в следующем запросе (возвращающем такой же результат): SELECT first_name, last_name, film_id FROM actor a JOIN film_actor fa ON a. actor_id = fa. actor_id WHERE first_name = "PENELOPE" AND last_name = "GUINESS" Его план: -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- | Id | Operation | Name | Rows | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- | 0 | SELECT STATEMENT | | | | 1 | NESTED LOOPS | | 2 | | * 2 | TABLE ACCESS BY INDEX ROWID BATCHED| ACTOR | 1 | | * 3 | INDEX RANGE SCAN | IDX_ACTOR_LAST_NAME | 3 | | * 4 | INDEX RANGE SCAN | PK_FILM_ACTOR | 27 | -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- Predicate Information (identified by operation id) : -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - 2 - filter ("A" . "FIRST_NAME" = "PENELOPE" ) 3 - access ("A" . "LAST_NAME" = "GUINESS" ) 4 - access ("A" . "ACTOR_ID" = "FA" . "ACTOR_ID" ) Как вы можете видеть, оценка числа строк таблицы FILM_ACTOR завышена, а оценка для вложенных циклов (NESTED LOOP) занижена. Вот пару интересных значений: SELECT count (* ) FROM film_actor WHERE actor_id = 1 ; SELECT avg (c) FROM ( SELECT count (* ) c FROM film_actor GROUP BY actor_id ) ; Результат: 19 27.315 Отсюда и получаются оценки. Если база данных знает, что речь идет о ACTOR_ID = 1, то может собрать статистику по количеству фильмов для этого конкретного актёра . Если же не знает (поскольку стандартный механизм сбора статистики не соотносит FIRST_NAME/LAST_NAME с ACTOR_ID), то мы получим среднее число фильмов для всех актеров . Простая, несущественная ошибка в данном конкретном случае, но в сложном запросе она может распространяться дальше, накапливаться и приводить дальше в запросе (выше в плане) к неправильному выбору JOIN. Так что всегда, когда только можете, проектируйте свои соединения и простые предикаты так, что воспользоваться преимуществами транзитивного замыкания. Какие еще базы данных поддерживают эту возможность?

DB2

MySQL

PostgreSQL

SQL Server

Резюме

2. Невозможные предикаты и ненужные обращения к таблицам

DB2

MySQL

Oracle

PostgreSQL

SQL Server?

3. Устранение JOIN

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

Даже если сервер базы данных использует самое мощное аппаратное обеспечение на свете, горсточка плохо себя ведущих запросов может плохо отразиться на его производительности. Фактически, даже один неудачный запрос (иногда их называют «вышедшими из-под контроля») может вызвать серьезное снижение производительности базы данных.

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

Анализ планов выполнения

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

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

Существует несколько способов извлечения плана выполнения запроса:

• В Management Studio есть функции отображения реального и приблизительного плана выполнения, представляющие план в графической форме. Это наиболее удобная возможность непосредственной проверки и, по большому счету, наиболее часто используемый способ отображения и анализа планов выполнения (примеры из этой статьи я буду иллюстрировать графическими планами, созданными именно таким способом).
• Различные параметры SET, например, SHOWPLAN_XML и SHOWPLAN_ALL, возвращают план выполнения в виде документа XML, описывающего план в виде специальной схемы, или набора строк с текстовым описанием каждой операции.
• Классы событий профайлера SQL Server, например, Showplan XML, позволяют собирать планы выполнения выражений методом трассировки.

Хотя XML-представление плана выполнения не самый удобный для пользователя формат, эта команда позволяет использовать самостоятельно написанные процедуры и служебные программы для анализа, поиска проблем с производительностью и практически оптимальных планов. Представление на базе XML можно сохранить в файл с расширением sqlplan, открывать в Management Studio и создавать графическое представление. Кроме того, эти файлы можно сохранять для последующего анализа без необходимости воспроизводить их каждый раз, как этот анализ понадобится. Это особенно полезно для сравнения планов и выявления возникающих со временем изменений.

Оценка стоимости выполнения

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

Существует несколько распространенных, но неверных представлений о приблизительной стоимости выполнения. Особенно часто считается, что приблизительная стоимость выполнения является хорошим показателем того, сколько времени займет выполнение запроса и что эта оценка позволяет отличить хорошие планы от плохих. Это неверно. Во-первых, есть много документов касающихся того, в каких единицах выражается приблизительная стоимость и имеют ли они непосредственное отношение ко времени выполнения. Во-вторых, поскольку значение это приблизительно и может оказаться ошибочным, планы с большими оценочными затратами иногда оказываются значительно эффективнее с точки зрения ЦП, ввода/вывода и времени выполнения, несмотря на предположительно высокую стоимость. Это часто случается с запросами, где задействованы табличные переменные. Поскольку статистики по ним не существует, оптимизатор запросов часто предполагает, что в таблице есть всего одна строка, хотя их во много раз больше. Соответственно, оптимизатор выберет план на основе неточной оценки. Это значит, что при сравнении планов выполнения запросов не следует полагаться только на приблизительную стоимость. Включите в анализ параметры STATISTICS I/O и STATISTICS TIME, чтобы определить истинную стоимость выполнения в терминал ввода/вывода и времени работы ЦП.

Здесь стоит упомянуть об особом типе плана выполнения, который называется параллельным планом. Такой план можно выбрать при отправке на сервер с несколькими ЦП запроса, поддающегося параллелизации (В принципе, оптимизатор запроса рассматривает использование параллельного плана только в том случае, если стоимость запроса превышает определенное настраиваемое значение.) Из-за дополнительных расходов на управление несколькими параллельными процессами выполнения, связанными с распределением заданий, выполнением синхронизации и сведением результатов, параллельные планы обходятся дороже, что отражает их приблизительная стоимость. Тогда чем же они предпочтительнее более дешевых, не параллельных планов? Благодаря использованию вычислительной мощности нескольких ЦП параллельные планы обычно выдают результат быстрее стандартных. В зависимости от конкретного сценария (включая такие переменные, как доступность ресурсов с параллельной нагрузкой других запросов) эта ситуации для кого-то может оказаться желательной. Если это ваш случай, нужно будет указать, какие из запросов можно выполнять по параллельному плану и сколько ЦП может задействовать каждый. Для этого нужно настроить максимальную степень параллелизма на уровне сервера и при необходимости настроить обход этого правила на уровне отдельных запросов с помощью параметра OPTION (MAXDOP n).

Анализ плана выполнения

Теперь рассмотрим простой запрос, его план выполнения и некоторые способы повышения производительности. Предположим, что я выполняю этот запрос в Management Studio с включенным параметром включения реального плана выполнения в примере базы данных Adventure Works SQL Server 2005:

SELECT c.CustomerID, SUM(LineTotal) FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh ON od.SalesOrderID=oh.SalesOrderID JOIN Sales.Customer c ON oh.CustomerID=c.CustomerID GROUP BY c.CustomerID

В итоге я вижу план выполнения, изображенный на рис. 1 . Этот простой запрос вычисляет общее количество заказов, размещенных каждым клиентом в базе данных Adventure Works. Глядя на этот план, вы видите, как ядро базы данных обрабатывает запросы и выдает результат. Графические планы выполнения читаются сверху вниз, справа налево. Каждый значок соответствует выполненной логической или физической операции, а стрелки - потокам данных между операциями. Толщина стрелок соответствует количеству переданных строк (чем толще, тем больше). Если поместить курсор на один из значков оператора, появится желтая подсказка (такая, как на рис. 2 ) со сведениями о данной операции.

Рис. 1 Пример плана выполнения

Глядя на операторы, можно анализировать последовательность выполненных этапов:

1. Ядро базы данных выполняет операцию сканирования кластеризированных индексов с таблицей Sales.Customer и возвращает столбец CustomerID со всеми строками из этой таблицы.
2. Затем оно выполняет сканирование индексов (не кластеризированных) над одним из индексов из таблицы Sales.SalesOrderHeader. Это индекс столбца CustomerID, но подразумевается, что в него входит столбец SalesOrderID (ключ кластеризации таблицы). Сканирование возвращает значения обоих столбцов.
3. Результаты обоих сеансов сканирования объединяются в столбце CustomerID с помощью физического оператора слияния (это один из трех возможных физических способов выполнения операции логического объединения. Операция выполняется быстро, но входные данные приходится сортировать в объединенном столбце. В данном случае обе операции сканирования уже возвратили строки, рассортированные в столбце CustomerID, так что дополнительную сортировку выполнять не нужно).
4. Затем ядро базы данных выполняет сканирование кластеризированного индекса в таблице Sales.SalesOrderDetail, извлекая значения четырех столбцов (SalesOrderID, OrderQty, UnitPrice и UnitPriceDiscount) из всех строк таблицы (предполагалось, что возвращено будет 123,317 строк. Как видно из свойств Estimated Number of и and Actual Number of Rows на рис. 2 , получилось именно это число, так что оценка оказалась очень точной).
5. Строки, полученные при сканировании кластеризованного индекса, передаются оператору вычисления стоимости, умноженной на коэффициент, чтобы вычислить значение столбца LineTotal для каждой строки на основе столбцов OrderQty, UnitPrice и UnitPriceDiscount, упомянутых в формуле.
6. Второй оператор вычисления стоимости, умноженной на коэффициент, применяет к результату предыдущего вычисления функцию ISNULL, как и предполагает формула вычисленного столбца. Он завершает вычисление в столбце LineTotal и возвращает его следующему оператору вместе со столбцом SalesOrderID.
7. Вывод оператора слияния с этапа 3 объединяется с выводом оператора стоимости, умноженной на коэффициент с этапа 6 и использованием физического оператора совпадения значений хэша.
8. Затем к группе строк, возвращенных оператором слияния по значению столбца CustomerID и вычисленному сводному значению SUM столбца LineTotal применяется другой оператор совпадения значений хэша.
9. Последний узел, SELECT - это не физический или логический оператор, а местозаполнитель, соответствующий сводным результатам запроса и стоимости.

В созданном на моем ноутбуке плане выполнения приблизительная стоимость равнялась 3,31365 (как видно на рис. 3 ). При выполнении с включенной функцией STATISTICS I/O ON отчет по запросу содержал упоминание о 1,388 логических операциях чтения из трех задействованных таблиц. Процентное значение под каждым оператором - это его стоимость в процентах от общей приблизительной стоимости всего плана. На плане на рис. 1 видно, что большая часть общей стоимости связана со следующими тремя операторами: сканирование кластеризованного индекса таблицы Sales.SalesOrderDetail и два оператора совпадения значений хэша. Перед тем как приступить к оптимизации, хотелось отметить одно очень простое изменение в моем запросе, которое позволило полностью устранить два оператора.

Поскольку я возвращал из таблицы Sales.Customer только столбец CustomerID, и тот же столбец включен в таблицу Sales.SalesOrderHeaderTable в качестве внешнего ключа, я могу полностью исключить из запроса таблицу Customer без изменения логического значения или результата нашего запроса. Для этого используется следующий код:

SELECT oh.CustomerID, SUM(LineTotal) FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh ON od.SalesOrderID=oh.SalesOrderID GROUP BY oh.CustomerID

Получился другой план выполнения, который изображен на рис. 4 .

Полностью устранены две операции - сканирование кластеризированного индекса таблицы Customer и слияние Customer и SalesOrderHeader, а совпадение значений хэша заменено на куда более эффективную операцию слияния. При этом для слияния таблиц SalesOrderHeader и SalesOrderDetail нужно вернуть строки обеих таблиц, рассортированные по общему столбцу SalesOrderID. Для этого оптимизатор кластера выполнил сканирование кластеризованного индекса таблицы SalesOrderHeader вместо того, чтобы использовать сканирование некластеризованного индекса, который был бы дешевле с точки зрения ввода/вывода. Это хороший пример практического применения оптимизатора запроса, поскольку экономия, получающаяся при изменении физического способа слияния, оказалась больше дополнительной стоимости ввода/вывода при сканировании кластеризованного индекса. Оптимизатор запроса выбрал получившуюся комбинацию операторов, поскольку она дает минимально возможную примерную стоимость выполнения. На моем компьютере, несмотря на то, что количество логических считываний возросло (до 1,941), временные затраты ЦП стали меньше, и приблизительная стоимость выполнения данного запроса упала на 13 процентов (2,89548).

Предположим, что я хочу еще улучшить производительность запроса. Я обратил внимание на сканирование кластеризованного индекса таблицы SalesOrderHeader, которое теперь является самым дорогим оператором плана выполнения. Поскольку для выполнения запроса нужно всего два столбца, можно создать некластеризованный индекс, где содержатся только эти два столбца. Таким образом, вместо сканирования всей таблицы можно будет просканировать индекс гораздо меньшего размера. Определение индекса может выглядеть примерно так:

CREATE INDEX IDX_OrderDetail_OrderID_TotalLine ON Sales.SalesOrderDetail (SalesOrderID) INCLUDE (LineTotal)

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

Создав этот индекс и выполнив тот же запрос, я получил новый план, который изображен на рис. 5 .

Сканирование кластеризованного индекса таблицы SalesOrderDetail заменено некластеризованным сканированием с заметно меньшими затратами на ввод/вывод. Кроме того, я исключил один из операторов вычисления стоимости, умноженной на коэффициент, поскольку в моем индексе уже есть вычисленное значение столбца LineTotal. Теперь приблизительная стоимость плана выполнения составляет 2,28112 и при выполнении запроса производится 1,125 логических считываний.

Упражнение. Запрос заказа покупателя

Вопрос. Вот пример запроса заказа покупателя. Попробуйте получить определение индекса: выясните, наличие каких столбцов превратит его в индекс покрытия данного запроса и повлияет ли порядок столбцов на производительность.

Ответ. Я предложил рассчитать оптимальный индекс покрытия для создания таблицы Sales.SalesOrderHeader на примере запроса из моей статьи. При этом нужно в первую очередь отметить, что запрос использует только два столбца из таблицы: CustomerID и SalesOrderID. Если вы внимательно прочли эту статью, то заметили, что в случае с таблицей SalesOrderHeader индекс покрытия запроса уже существует, это индекс CustomerID, который косвенно содержит столбец SalesOrderID, являющийся ключом кластеризации таблицы.

Конечно, я объяснял и то, почему оптимизатор запроса не стал использовать этот индекс. Да, можно заставить оптимизатор запроса использовать этот индекс, но это решение было бы менее эффективным, чем существующий план с операторами сканирования кластеризованного индекса и слияния. Дело в том, что оптимизатор запроса пришлось бы принудить либо выполнить дополнительную операцию сортировки, необходимую для использования слияния, либо откатиться назад, к использованию менее эффективного оператора совпадения значений хэша. В обоих вариантах приблизительная стоимость выполнения выше, чем в существующем плане (версия с оператором сортировки работала бы особенно плохо), поэтому оптимизатор запроса не будет их использовать без принуждения. Итак, в данной ситуации лучше сканирования кластеризованного индекса будет работать только некластеризованный индекс в столбцах SalesOrderID, CustomerID. При этом нужно отметить, что столбцы должны идти именно в таком порядке:

CREATE INDEX IDX_OrderHeader_SalesOrderID_CustomerID ON Sales.SalesOrderHeader (SalesOrderID, CustomerID)

Если вы создадите этот индекс, в плане выполнения будет использовано не сканирование кластеризованного индекса, а сканирование индекса. Разница существенная. В данном случае некластеризованный индекс, который содержит только два столбца, заметно меньше всей таблицы в виде кластеризованного индекса. Соответственно, при считывании нужных данных будет меньше задействован ввод/вывод.

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

Индекс покрытия

Индекс, созданный из таблицы SalesOrderDetail, представляет собой так называемый «индекс покрытия». Это некластеризованный индекс, где содержатся все столбцы, необходимые для выполнения запроса. Он устраняет необходимость сканирования всей таблицы с помощью операторов сканирования таблицы или кластеризованного индекса. По сути индекс представляет собой уменьшенную копию таблицы, где содержится подмножество ее столбцов. В индекс включаются только столбцы, которые необходимы для ответа на запрос или запросы, то есть только то, что «покрывает» запрос.

Создание индексов покрытия наиболее частых запросов - один из самых простых и распространенных способов тонкой настройки запроса. Особенно хорошо он работает в ситуациях, когда в таблице несколько столбцов, но запросы часто ссылаются только на некоторые из них. Создав один или несколько индексов покрытия, можно значительно повысить производительность соответствующих запросов, так как они будут обращаться к заметно меньшему количеству данных и, соответственно, количество вводов/выводов сократится. Тем не менее, поддержка дополнительных индексов в процессе модификации данных (операторы INSERT, UPDATE и DELETE) подразумевает некоторые расходы. Следует четко определить, оправдывает ли увеличение производительности эти дополнительные расходы. При этом учтите характеристики своей среды и соотношение количества запросов SELECT и изменений данных.

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

С моим примером запроса можно сделать еще кое-что. Создав индекс покрытия таблицы SalesOrderHeader, можно дополнительно оптимизировать запрос. При этом будет использовано сканирование некластеризованного индекса вместо кластеризованного. Предлагаю вам выполнить это упражнение самостоятельно. Попробуйте получить определение индекса: выясните, наличие каких столбцов превратит его в индекс покрытия данного запроса и повлияет ли порядок столбцов на производительность. Решение см. в боковой панели "Упражнение. Запрос заказа покупателя".

Индексированные представления

Если выполнение моего примера запроса очень важно, я могут пойти немного дальше и создать индексированное представление, в котором физически хранятся материализованные результаты запроса. При создании индексированных представлений существуют некоторые предварительные условия и ограничения, но если их удастся использовать, производительность сильно повысится. Обратите внимание, что расходы на обслуживание индексированных представлений выше, чем у обычных индексов. Их нужно использовать с осторожностью. В данном случае определение индекса выглядит примерно так:

CREATE VIEW vTotalCustomerOrders WITH SCHEMABINDING AS SELECT oh.CustomerID, SUM(LineTotal) AS OrdersTotalAmt, COUNT_BIG(*) AS TotalOrderLines FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh ON od.SalesOrderID=oh.SalesOrderID GROUP BY oh.CustomerID

Обратите внимание на параметр WITH SCHEMABINDING, без которого невозможно создать индекс такого представления, и функцию COUNT_BIG(*), которая потребуется в том случае, если в нашем определении индекса содержится обобщенная функция (в данном случае SUM). Создав это представление, я могу создать и индекс:

CREATE UNIQUE CLUSTERED INDEX CIX_vTotalCustomerOrders_CustomerID ON vTotalCustomerOrders(CustomerID)

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

Если перезапустить запрос, то результат будет зависеть от используемой версии SQL Server. В версиях Enterprise или Developer оптимизатор автоматически сравнит запрос с определением индексированного представления и использует это представление, вместо того чтобы обращаться к исходной таблице. На рис. 6 приведен пример получившегося плана выполнения. Он состоит из одной-единственной операции - сканирования кластеризованного индекса, который я создал на основе представления. Приблизительная стоимость выполнения составляет всего 0,09023 и при выполнении запроса производится 92 логических считывания.

Это индексированное представление можно создавать и использовать и в других версиях SQL Server, но для получения аналогичного эффекта необходимо изменить запрос и добавить прямую ссылку на представление с помощью подсказки NOEXPAND, примерно так:

SELECT CustomerID, OrdersTotalAmt FROM vTotalCustomerOrders WITH (NOEXPAND)

Как видите, правильно использованные индексированные представления могут оказаться очень мощными орудиями. Лучше всего их использовать в оптимизированных запросах, выполняющих агрегирование больших объемов данных. В версии Enterprise можно усовершенствовать много запросов, не изменяя кода.

Поиск запросов, нуждающихся в настройке

Как я определяют, что запрос стоит настроить? Я ищу часто выполняемые запросы, возможно, с невысокой стоимостью выполнения в отдельном случае, но в целом более дорогие, чем крупные, но редко встречающиеся запросы. Это не значит, что последние настраивать не нужно. Я просто считаю, что для начала нужно сосредоточиться на более частых запросах. Так как же их найти?

К сожалению, самый надежный метод довольно сложен и предусматривает отслеживание всех выполненных запросов к серверу с последующий группировкой по подписям. При этом текст запроса с реальными значениями параметров заменяется на замещающий текст, который позволяет выбрать однотипные запросы с разными значениями. Подписи запроса создать тяжело, так что это сложный процесс. Ицик Бен-Ган (Itzik Ben-Gan) описывает решение с использованием пользовательских функций в среде CLR и регулярных выражений в своей книге «Microsoft SQL Server 2005 изнутри: запросы T-SQL».

Существует еще один метод, куда более простой, но не столь надежный. Можно положиться на статистику всех запросов, которая хранится в кэше плана выполнения, и опросить их с использованием динамических административных представлений. На рисунке 7 есть пример запроса текста и плана выполнения 20 запросов из кэша, у которых общее количество логических считываний оказалось максимальным. С помощью этого запроса очень удобно быстро находить запросы с максимальным количеством логических считываний, но есть и некоторые ограничения. Он отображает только запросы с планами, кэшированными на момент запуска. Не кэшированные объекты не отображаются.

Рис. 7 Поиск 20 самых дорогих с точки зрения ввода/вывода при считывании запросов.

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(qt.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1), qs.execution_count, qs.total_logical_reads, qs.last_logical_reads, qs.min_logical_reads, qs.max_logical_reads, qs.total_elapsed_time, qs.last_elapsed_time, qs.min_elapsed_time, qs.max_elapsed_time, qs.last_execution_time, qp.query_plan FROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp WHERE qt.encrypted=0 ORDER BY qs.total_logical_reads DESC

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

Удачной настройки!

После написания программы и появления «живых» данных выясняется, что реакция программы на тестовые наборы, порой сильно отличается от работы с реальными данными. Программисты обычно мало внимания уделяют формированию тестовых наборов данных, что является серьезной ошибкой. Ставка делается на то, что используются современные «крутые» СУБД, которые сами себя настраивают. К сожалению это не совсем так, и работе с базой данных следует уделять пристальное внимание. В идеале, за обработку бизнес логики должны отвечать специалисты. Но и рядовым программистам полезно иметь навыки и знания по архитектуре СУБД и написанию SQL запросов.

Нередки случаи, когда используются генераторы скриптов и программного кода для доступа к данным. Авторы программ надеются на то, что современные технологические новинки сами выполнят за вас всю работу. В результате нередки случаи, когда несколько месяцев спустя после внедрения программы, пользователи начинают жаловаться, что программа «еле шевелится». Начинается всеобщая паника с привлечением дорогостоящих специалистов, которые смогут найти “бутылочное горлышко” тормозящее программу и спасти проект.

Практика показывает что, анализируя и перестраивая SQL запросы можно снизить время их выполнения в десятки, а иногда и в сотни раз. После разработки нескольких проектов, у программистов вырабатываются навыки написания более «быстрых» запросов. Но все равно полезно выполнять периодический анализ затрат ресурсов сервера при работе вашего творения. И хотя по большому счету анализ использования ресурсов сервера это работа администратора базы данных, иметь навыки по оптимизации программ никому не помешает. Тем более что это не так сложно, как кажется на первый взгляд.

Существует ряд программ позволяющих автоматизировать и упростить эту задачу. Данный материал ориентирован на работу с сервером Oracle , но и для других баз данных есть аналогичные средства анализа и оптимизации «тюнинга». Первым нашим помощником станет программа мониторинга работы сервера Oracle с названием « Spotlight on Oracle » фирмы Quest software (http://www.quest.com). Это очень мощный инструмент, предназначенный для контроля функционирования вашего сервера. Данная программа выполнена в необычной цветовой палитре, что резко выделяет ее от других продуктов. После запуска данной программы необходимо создать учетную запись пользователя для чего потребуется учетная запись SYS или запись с системными привилегиями DBA. Помощник создания новой учетной записи вызывается из меню “ File > User Wizard ”.

После создания учетной записи пользователя и соединением с сервером Oracle нам представляется визуальная картинка, которая отображает компоненты и процессы работы сервера. Если один, или несколько компонентов сервера работает не оптимально или с перегрузкой, то его цвет изменяется от зеленого до красного, в зависимости от степени перегрузки. Возможен мониторинг сразу нескольких серверов, список которых отображается в левой панели и так же меняет цвет. Иконка в панели задач также меняет цвет синхронно с программой, что позволяет оперативно реагировать при “свернутом” в приложении. Пример мониторинга показан на рисунке 1.

Очень полезной особенностью данной программы является система авто-рекомендаций решения проблем. Достаточно кликнуть мышкой по красному участку изображения, чтобы получить развернутое описание проблемы и возможные методы ее устранения. Если же все нормально, то благодаря данной программе можно подстроить параметры запуска сервера для уменьшения используемых им системных ресурсов. Например, по рисунку 1 можно сделать вывод, что размер табличного пространства файла базы данных можно смело уменьшить в два раза, и желательно выделить дополнительную память под “ Shared Pool ”.

Но это все проблемы администратора базы данных. Разработчиков же больше волнует, как работают их творения и сколько ресурсов «кушают» запросы к базе данных. Для этого вызываем пункт меню “ Navigator > Top Sessions ”. После заполнения параметров фильтра отбора данных нам будет показан список текущих запросов к серверу базы данных. Предварительно отсортировав запросы по требованиям к ресурсам, можно выделить самые “прожорливые”. В этом же окне можно посмотреть план выполнения запроса, пример которого показан на рисунке 2. Причем план запросов можно представить в виде графа, дерева или словесного описания. Здесь так же используется цветовая маркировка проблемных участков.

После выявления проблемных SQL запросов настал черед их оптимизации. Для автоматизации этого процесса воспользуемся программой SQL Expert фирмы LECCO (http://www.leccotech.com). Вызываем окно SQL редактора и добавляем в него скрипт запроса. Здесь так же можно посмотреть план выполнения запроса. Но нас больше всего интересует функция меню “SQL-> Optimize ”, которая генерирует список альтернативных вариантов построения заданного SQL скрипта. А функция “SQL-> Butch Run ” позволяет проанализировать время выполнения всех запросов на “живых” данных и вывести результирующую таблицу, которую можно отсортировать по требуемому параметру. Выбрав наиболее оптимальный запрос, его можно сравнить с оригиналом и принять решение о возможности дальнейшего его использования в своем приложении. Пример работы по оптимизации запроса показан на рисунке 3.

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