Меню
ГлавнаяЯндекс

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

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

Существует ряд программ позволяющих автоматизировать и упростить эту задачу. Данный материал ориентирован на работу с сервером 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 скриптов, со временем вырабатывается привычка писать сразу «оптимально», что так же повышает ваш имидж как хорошего специалиста.

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

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

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

Синтаксический анализ SQL-запросов

Этап синтаксического анализа (parsing) главным образом состоит в выполнении проверки синтаксиса и семантики SQL-операторов. В конце этого этапа создается дерево синтаксического разбора (parse tree), отражающее структуру запроса.

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

Оптимизация SQL запросов

На этапе оптимизации Oracle применяет свой оптимизатор, который называется оптимизатором по стоимости (Cost-Base Optimizer - CBO), для выбора наилучшего метода доступа для извлечения данных из присутствующих в запросе таблиц и индексов. За счет использования предоставляемых статистических данных и любых указываемых в SQL-запросах подсказок, CBO генерирует для SQL-оператора оптимальный план выполнения.

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

Этап перезаписи запроса

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

Этап генерации плана выполнения

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

В общем, при генерации физического плана или плана выполнения запроса оптимизатор принимает во внимание следующие факторы:

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

Оптимизатор может генерировать несколько действительных физических планов запроса, которые являются потенциальными планами выполнения. Затем оптимизатор делает выбор между ними путем оценки стоимости каждого возможного физического плана на основании доступных ему статистических данных по таблицам и индексам и выбора того плана, подсчитанная стоимость которого оказывается наименьшей. Этот процесс оценки стоимости возможных физических планов запроса называется оптимизацией запроса по стоимости (cost-based optimization). Стоимость выполнения плана напрямую зависит от того, сколько ресурсов (ввода-вывода, памяти и ЦП) для него требуется. Потом оптимизатор передает выбранный самый низкий по стоимости физический план запроса механизму выполнения запросов Oracle. В следующем разделе рассматривается простой пример, чтобы можно было лучше разобраться в том, что собой представляет процесс оптимизации процесса по стоимости.

Пример оптимизации запроса по стоимости

Давайте предположим, что требуется выполнить показанный ниже запрос, предусматривающий поиск информации обо всех руководителях (supervisor), которые работают в Далласе (Dallas):

SQL> SELECT * FROM employee e, dept d WHERE e.dept_no = d.dept_no AND(e.job = "SUPERVISOR" AND d.city = "DALLAS"); SQL>

Получить список руководителей можно тремя способами. Давайте рассмотрим три этих способа и вычислим стоимость получения результатов в случае применения каждого из них.

Для произведения вычислений по стоимости давайте исходить из следующих предположений:

  • считывать и записывать данные можно только по одной строке за раз (в реальности операции ввода-вывода выполняются обычно на уровне блоков, а не на уровне строк);
  • база данных записывает каждый промежуточный шаг на диск (опять-таки, в реальном мире такого может и не быть);
  • с таблицами не ассоциированы никакие индексы;
  • в таблице employee содержится 2000 строк;
  • в таблице dept содержится 40 строк и руководителей тоже 40 (по одному на каждое отделение);
  • в Далласе всего функционирует десять отделений.

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

Запрос 1: декартово соединение

В случае применения этого запроса сначала получается декартово произведение таблиц employee и dept , а затем проверяться, какие из строк в нем удовлетворяют требованию:

WHERE e.job=supervisor AND d.dept=operations AND e.dept_no=d.dept_no

  • декартово произведение таблиц employee и dept потребует считывания обеих таблиц, т.е. 2000 + 40 = 2040 операций чтения;
  • создание декартова произведения - 2000 * 40 = 80000 операций записи;
  • считывание результата декартова произведения для его сравнения с условием выбора строк - 2000 * 40 = 80000 операций чтения;
  • итого общая стоимость ввода-вывода составит: 2040 + 80000 + 80000 = 162040.

Запрос 2: соединение двух таблиц

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

WHERE e.job=supervisor and city=Dallas

Общая стоимость выполнения этого запроса будет выглядеть так:

  • соединение таблиц employee и dep сначала потребует считывания всех строк из обеих таблиц, т.е. 2000 + 40 = 2.040 операций чтения;
  • создание соединения таблиц employee и dep - 2000 операций записи;
  • считывание результатов соединения будет стоить 2000 операций чтения;
  • итого общая стоимость ввода-вывода составит: 2040 + 2000 + 2000 = 6040.

Запрос 3: соединение сокращенных связей

Третий запрос тоже подразумевает выполнение соединения таблиц employee и dept , но с соединением не всех, а только выборочных строк из этих двух таблиц. В случае его применения необходимые данные будут извлекаться так, как описано далее. Сначала будет осуществляться считывание таблицы employee для получения всех строк со значением SUPERVISOR . Затем будет выполняться считывание таблицы dept для извлечения всех строк со значением DALLAS . И, наконец, напоследок будет осуществляться соединение тех строк, которые были извлечены из таблиц employee и dept .

Общая стоимость выполнения этого запроса будет выглядеть так:

  • считывание таблицы employee для извлечения строк со значением SUPERVISOR будет стоить 2000 операций чтения;
  • запись строк со значением SUPERVISOR , которые были извлечены на предыдущем шаге - 40 операций записи;
  • считывание таблицы dept для извлечения всех строк со значением DALLAS - 40 операций чтения;
  • запись строк со значением DALLAS , извлеченных на предыдущем шаге - 10 операций записи;
  • соединение строк со значением SUPERVISOR и со значением DALLAS , извлеченных на предыдущих шагах выполнения данного запроса - всего 40 + 10 = 50 операций записи;
  • считывание результата соединения, полученного на предыдущем шаге - 50 операций чтения;
  • итого всего стоимость ввода-вывода составит: 2000 + 2 (40) + 10 + 2 (50) = 2190.

Этот пример, каким бы простым он не был, показывает, что декартовы произведения обходятся дороже, чем соединения с более ограничивающими условиями. Даже выборочная операция соединения, как показывают результаты, обходится дороже, чем операция выбора. Хотя операция соединения в запросе 3 и представляет собой соединение двух сокращенных связей, размер соединения выглядит гораздо меньше, чем у соединения в запросе 2. Оптимизация запросов часто подразумевает выполнение ранних операций выборки (выбор только некоторых строк) и проекции (выбор только каких-то столбцов) для сокращения размера результирующего вывода или источников строк.

Эвристические стратегии для обработки запросов

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

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

Выполнение запросов

На последнем этапе процесса обработки запросов осуществляется выполнение оптимизированного запроса (физического плана запроса, который был выбран). Если он представляет собой оператор SELECT , тогда производится возврат соответствующих строк пользователю, а если оператор INSERT, UPDATE или DELETE , тогда - внесение в строки соответствующих изменений. Исполняющий механизм SQL берет план выполнения, полученный на этапе оптимизации, и выполняет его.

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

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

AND

Очевидно, что в серии из нескольких операторов AND условия должны располагаться в порядке возрастания вероятности истинности данного условия. Это делается для того, чтобы при проверке условий БД не проверяла остальную часть условия. Эти рекомендации не относится к БД Oracle, где условия начинают проверяться с конца. Соответственно, их порядок должен быть обратным - по убыванию вероятности истинности.

OR

Ситуация с данным оператором прямо противоположна ситуации с AND. Условия должны располагаться в порядке убывания вероятности истинности. Фирма Microsoft настойчиво рекомендует использовать данный метод при построении запросов, хотя многие даже не знают об этом или, по крайней мере, не обращают на него внимание. Но опять-таки это не относится к БД Oracle, где условия должны располагаться по возрастанию вероятности истинности.

Еще одним условием для оптимизации можно считать тот факт, что если одинаковые колонки располагаются рядом, запрос выполняется быстрее. Например, запрос ".. WHERE column1 = 1 OR column2 = 3 OR column1 = 2" будет выполняться медленней, чем запрос "WHERE column1 = 1 OR column1 = 2 OR column2 = 3" . Даже если вероятность истинности условия column2 = 3 выше, чем column1 = 2.

Еще в школе мне рассказывали про распределительный закон. Он гласит, что A AND (B OR C) - то же самое, что и (A AND B) OR (A AND C ). Опытным путем было установлено, что запрос вида "...WHERE column1 = 1 AND (column2 = "A" OR column2 = "B")" выполняется несколько быстрее, чем "...WHERE (column1 = 1 AND column2 = "A") OR (column1 = 1 AND column2 = "B")" . Некоторые БД сами умеют оптимизировать запросы такого типа, но лучше перестраховаться.

NOT

Эту операцию всегда следует приводить к более "читабельному" виду (в разумных пределах, конечно). Так, запрос "...WHERE NOT (column1 > 5)" преобразуется в "...WHERE column1 <= 5" . Более сложные условия можно преобразовать используя правило де Моргана, которое ты тоже должен был изучить в школе. Согласно этому правилу NOT(A AND B) = (NOT A) OR (NOT B) и NOT(A OR B) = (NOT A) AND (NOT B) . Например, условие "...WHERE NOT (column1 > 5 OR column2 = 7)" преобразуется в более простую форму: ...WHERE column1 <= 5 AND column2 <> 7 .

IN

Многие наивно полагают, что запрос "... WHERE column1 = 5 OR column1 = 6" равносилен запросу "...WHERE column1 IN (5, 6)" . На самом деле это не так. Операция IN работает гораздо быстрее, чем серия OR. Поэтому всегда следует заменять OR на IN, где это возможно, несмотря на то, что некоторые БД сами производят эту оптимизацию. Там, где используется серия последовательных чисел, IN следует поменять на BETWEEN. Например, "...WHERE column1 IN (1, 3, 4, 5)" оптимизируется к виду: …WHERE column1 BETWEEN 1 AND 5 AND column1 <> 2 . И этот запрос действительно быстрее.

LIKE

Эту операцию следует использовать только при крайней необходимости, потому что лучше и быстрее использовать поиск, основанный на full-text индексах. К сожалению, я вынужден направить тебя за информацией о поиске на просторы всемирной паутины.

CASE

Сама эта функция может использоваться для повышения скорости работы запроса, когда в нем есть более одного вызова медленной функции в условии. Например, чтобы избежать повторного вызова slow_function() в запросе "...WHERE slow_function(column1) = 3 OR slow_function(column1) = 5" , нужно использовать CASE:

... WHERE 1 = CASE slow_function(column1)

WHEN 3 THEN 1

WHEN 5 THEN 1

Морис Льюис

Какую огромную разницу в скорость исполнения запроса может внести индекс! Недавно автор статьи получил еще одно наглядное подтверждение этой истины. Он ввел дополнительный индекс, и время обработки запроса уменьшилось с 40 минут до 12 секунд. Но индексы - всего лишь один из инструментов, применяемых администраторами баз данных для повышения производительности систем. Улучшить производительность можно настройкой самых разных параметров - от конфигурации технических средств до использования утилит баз данных. Ниже приведены 9 самых эффективных средств увеличения производительности SQL Server 6.5.

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

Чем большая часть базы данных сможет уместиться в кэше, тем быстрее будут обрабатываться запросы. Поэтому целесообразно увеличивать размер оперативной памяти пропорционально размеру базы данных. К примеру, если ваша база данных занимает 1 Гб, то оперативная память размером 1 Гб позволит разместить практически всю базу данных в памяти. Некоторую часть оперативной памяти следует оставить для Windows NT. Автор предпочитает оставлять для операционной системы от 64Мб до 128 Мб, а всю оставшуюся часть памяти отводить под SQL Server. И непременно надо сохранять объем доступной физической памяти NT не менее 4 Мб. Если он окажется ниже указанного предела, то NT немедленно начнет создавать страницы виртуальной памяти на диске.

2. Используйте массивы RAID уровня 0 или 5 для распараллеливания получения информации из базы данных.

Массивы RAID уровней 0 и 5 распределяют запросы на чтение по нескольким физическим дискам. Вы, наверняка, знаете, что творится на подступах к мостам в час пик, когда тысячи машин одновременно стремятся проехать через пространство ограниченной ширины. Такое же узкое место возникает и для запросов на чтение файлов с устройств вашей базы данных. Если вам удастся направить данные по нескольким каналам, то сервер сможет параллельно считывать блоки данных с каждого жесткого диска. При этом наблюдается почти линейное улучшение производительности. Такое увеличение пропускной способности для операций чтения обязано своим возникновением массивам RAID уровней 0 и 5. В качестве примера приведем цифры из книги Рона Саукапа "Внутри SQL Server 6.5", вышедшей в издательстве Microsoft Press в 1997 году. Он пишет, что один жесткий диск емкостью 4 Гб в состоянии обработать 80 - 90 операций ввода/вывода в секунду. В то же время массив RAID уровня 0 из 8 жестких дисков по 500 Мб каждый (то есть, обладающий такой же суммарной емкостью) пропускает 400 операций ввода/вывода в секунду. Конечно, при этом вопрос увеличения затрат остается открытым. Но в общем случае, чем больше жестких дисков в массиве, тем больше пропускная способность базы данных для операций чтения.

3. Позвольте функции Max Async I/O воспользоваться всеми преимуществами вашего компьютера.

Возможно, ваша дисковая подсистема в состоянии обрабатывать свыше восьми асинхронных операций ввода/вывода в секунду, то есть больше величины, принятой в качестве значения по умолчанию более трех лет назад при выходе в свет версии SQL Server 6.5. Для оптимизации этого параметра следует увеличивать его небольшими шагами, наблюдая при этом за значением счетчика средней длины очереди к дискам, AvgDiskQueueLength, в мониторе производительности NT (NT Performance Monitor). До тех пор, пока средняя очередь к дисковой подсистеме не превышает удвоенного количества дисков в ней, можно считать, что вы ее не перегружаете.

4. Установите пороги расширения блокировок на всю таблицу.

Три параметра расширения блокировок на всю таблицу (LE - Lock Escalation): Максимальный порог (LE Threshold Maximum), Минимальный порог (LE Threshold Minimum) и Пороговый процент (LE Threshold Percent), определяют, сколько страниц должен заблокировать SQL Server, прежде чем будет заблокирована вся таблица целиком. По умолчанию для этих параметров приняты значения соответственно 200, 20 и 0. Для очень больших таблиц блокировка всей таблицы позволяет избежать накладных расходов, связанных с тысячами блокировок. Если в базе данных содержатся сотни таблиц, то устранение таких накладных расходов может оказать существенное влияние на производительность.

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

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

6. Сформируйте не кластеризованные индексы для запросов на поиск уникальных значений.

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

7. Создайте составные индексы для поддержания множества запросов.

В тех случаях, когда с вашими таблицами в основном выполняются операции UPDATE и INSERT, а также производится чтение данных, уменьшение количества индексов позволит снизить накладные расходы на сопровождение индексов. Операция INSERT заставляет SQL Server добавлять новые записи в индекс, а операция UPDATE может привести к перемещению строки на новое место в индексе, или даже на новую страницу в таблице. Более того, часто SQL Server выполняет операцию удаления как последовательность двух операций: сначала удаляется старая строка, а затем вставляется новая. С точки зрения накладных расходов управления индексами, это наихудший вариант. Выход из этой ситуации - создание составных индексов, которые SQL Server сможет применять для разнообразных запросов.

8. Индексируйте соединенные столбцы.

При соединении двух таблиц SQL Server ищет во внутренней таблице все строки, значения которых удовлетворяют условию, вычисляемому на основании текущего значения из внешней таблицы. И такой поиск SQL Server повторяет для каждой строки из внешней таблицы. Если имеется индекс, то SQL Server сможет сначала отобрать только те строки, которые отвечают условию соединения. Когда размер внутренней таблицы в несколько раз больше размера внешней, выигрыш во времени выполнения соединения может составить несколько порядков. (Более подробно о соединении таблиц написано в статье Ицыка Бен-Гана и Кэйлен Дилани "Усовершенствованная техника соединения таблиц" ("Advanced JOIN Techniques"), опубликованной в декабрьском номере журнала за 1999 год.) Какой индекс выбрать - кластеризованный или не кластеризованный - в основном, зависит от того, присутствуют ли в списке SELECT другие столбцы. Если в список входят только те столбцы, по которым производится соединение, лучше всего применять не кластеризованный индекс.

9. Используйте преимущества покрывающих индексов.

По определению покрывающим индексом называется такой индекс, который содержит все столбцы, упомянутые в операторах SELECT, UPDATE или DELETE. Запрос при этом называется покрываемым запросом. Поскольку не кластеризованный индекс содержит на уровне листьев запись для каждой строки в таблице, то вся информация для выполнения запроса находится в индексе. В силу этого процессор запросов может сканировать не огромную таблицу, а только небольшой индекс. В общем случае, если вам удастся построить покрывающий индекс, то вы сразу почувствуете значительное улучшение производительности обработки запросов. Это объясняется тем, что индекс содержит не всю строку таблицы, а только ее подмножество. Однако оборотная сторона медали состоит в том, что введение в индекс дополнительных столбцов приводит к тому, что на странице индекса умещается меньше записей. Это, в свою очередь, вызывает увеличение места, занимаемого индексом, и возрастание числа операций ввода/вывода, необходимых для считывания индекса в кэш. Построение покрывающих индексов оправдано до тех пор, пока суммарная длина всех входящих в индекс столбцов остается значительно меньше длины строки таблицы.

Морис Льюис является президентом компании Holitech, специализирующейся на консалтинге и обучении технологиям Internet и разработкам корпорации Microsoft в области баз данных.