W Cat - SQL за 24 часа Страница 42

Эти операторы SQL не обязательно возвратят одинаковые результаты. Вероятнее всего, Запрос! возвратит больше строк, чем другие два запроса. Запрос2 и ЗапросЗ более специфичны в отношении извлекаемых данных, отсеивая больше данных и тем самым сокращая время выполнения запроса. Кроме того, ЗапросЗ скорее всего будет выполнен быстрее, чем Запрос2, поскольку поиск проводится по первым буквам (и, кроме того, столбец LAST_NAME, скорее всего, индексирован, так что ЗапросЗ может использовать преимущества индекса).

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

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

Вот пример запроса с использованием операций OR.

SELECT EMP_ID, LAST_NAME, FIRST_NAME

FROM EMPLOYEE_TBL

WHERE CITY = 'INDIANAPOLIS'

OR CITY = 'BROWNSBURG'

OR CITY = 'GREENFIELD';

А вот тот же запрос с использованием выражения с ключевым словом IN.

SELECT EMP_ID, LAST_NAME, FIRST_NAME

FROM EMPLOYEEJTBL

WHERE CITY IN ('INDIANAPOLIS', 'BROWNSBURG1, 'GREENFIELD');

Эти два оператора SQL дают одинаковые результаты, но опыт показывает, что во втором случае в результате замены OR на IN результат получается значительно быстрее.

Выражение с ключевым словом HAVING оказывается очень полезным, но за все нужно платить. Использование выражения с ключевым словом HAVING загружает оптимизатор дополнительной работой, что выливается в дополнительные затраты времени. Если это возможно, то желательно переписать оператор SQL таким образом, чтобы выражения с ключевым словом HAVING в нем не было.

Долгие операции сортировки являются следствием использования выражений с ключевыми словами ORDER BY, GROUP BY и HAVING. При выполнении сортировки соответствующие подмножества данных сохраняются в оперативной памяти или на диске (когда оперативной памяти для этого не хватает). Сортировать данные приходится часто. Здесь главным моментом является то, что операции сортировки увеличивают время выполнения запроса.

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

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

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

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

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

Многие базы данных предлагают специальные средства, позволяющие ускорить оптимизацию операторов SQL, а также работу базы данных в целом. Например, в Oracle имеется средство EXPLAIN PLAN, с помощью которого пользователь может ознакомиться с планом, по которому сервер базы данных собирается осуществить выполнение конкретного оператора SQL. С помощью другого средства Oracle - TKPROF - можно оценить ожидаемое время выполнения оператора. SQL Server предлагает целый ряд команд SET, с помощью которых можно оценить как скорость работы отдельных операторов SQL, так и скорость работы базы данных в целом. По поводу средств, доступных в вашем конкретном случае, обратитесь к соответствующей документации и проконсультируйтесь у своего администратора базы данных.

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

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

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

Ради чего было потрачено столько усилий на обсуждение вопросов оптимизации, какой реальный выигрыш в скорости выполнения операций можно ожидать от применения описанных выше методов?

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

Какие существуют способы проверки операторов SQL на оптимальность?