• Что означает оптимизация операторов SQL?
• Оптимизация базы данных и оптимизация операторов SQL
• Форматирование операторов SQL
• Форматирование операторов для лучшего восприятия
• Правильный порядок таблиц в выражении FROM
• Правильный порядок условий связывания
• Наиболее ограничительное условие
• Полное сканирование таблиц
• Когда и как избегать полного сканирования таблиц
• Другие аспекты оптимизации
• Использование LIKE и знаков подстановки
• Замена операций OR выражением с ключевым словом IN
• Недостатки использования выражения с ключевым словом HAVING
• Долгие операции сортировки
• Использование готовых процедур
• Отмена использования индексов в больших пакетных операциях
• Средства для анализа производительности
• Резюме
• Вопросы и ответы
• Практикум
• Тесты
• Упражнения

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

Основными на этом уроке будут следующие темы.

• Что означает оптимизация операторов SQL?
• Оптимизация базы данных и оптимизация операторов SQL
• Форматирование операторов SQL
• Правильное связывание таблиц
• Наиболее ограничительные условия
• Полное сканирование таблиц
• Необходимость использования индексов
• Как избежать использования OR и HAVING
• Как избежать долгих операций сортировки

  Что означает оптимизация операторов SQL?

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

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

  Оптимизация базы данных и оптимизация операторов SQL

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

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

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

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

  Форматирование операторов SQL

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

• Форматирование операторов SQL для лучшего восприятия
• Порядок перечисления таблиц в выражении ключевого слова FROM
• Размещение наиболее ограничительных условий в выражении ключевого слова WHERE
• Размещение условий связывания в выражении ключевого слова WHERE

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

  Форматирование операторов для лучшего восприятия

Форматировать операторы SQL для лучшего восприятия вполне естественно, но, тем не менее, многие программисты не очень заботятся о виде создаваемых ими операторов. И хотя от внешнего вида оператора его скорость работы не зависит (база данных на красоту оператора внимания не обращает), правильное форматирование является первым шагом на пути оптимизации оператора SQL. При рассмотрении оператора SQL с точки зрения ускорения его работы прежде всего удобно сделать оператор максимально простым для чтения. Но как определить, является оператор удобным для чтения или нет?

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

• Каждое ключевое слово со своим выражением следует начинать с новой строки. Например, выражение с ключевым словом FROM не следует размещать в той же строке, что и выражение с ключевым словом SELECT. Точно также выражение с ключевым словом WHERE не следует размещать в той же строке, что и выражение с ключевым словом FROM и т. д.
• Если аргументы выражения не умещаются в одной строке, строки продолжения начинайте с отступами, используя для этого символы табуляции или пробелы.
• Отступы должны быть согласованными.
• При использовании в операторе нескольких таблиц используйте для таблиц псевдонимы. Использование полных имен таблиц быстро засоряет оператор и делает его трудным для понимания.
• Не увлекайтесь использованием комментариев в операторе (конечно, если соответствующая реализация SQL их допускает). Комментарии полезны с точки зрения документирования, но слишком большое их число мешает понять суть оператора при чтении.
• При выборе нескольких столбцов в выражении ключевого слова SELECT имя каждого из столбцов лучше начинать с новой строки.
• При выборе нескольких таблиц в выражении ключевого слова FROM имя каждой из таблиц лучше начинать с новой строки.
• При наличии нескольких условий в выражении ключевого слова WHERE каждое из условий лучше начинать с новой строки — тогда легко будет увидеть и каждое из условий в отдельности, и тот порядок, в котором они используются.

Вот пример трудного для чтения оператора.

SELECT CUSTOMER_TBL.CUST_ID, CUSTOMERJTBL.CUST_NAME,

CUSTOMERJTBL.CUST_PHONE, ORDERSJTBL.ORD_NUM, ORDERS_TBL.QTY

FROM CUSTOMER_TBL, ORDERS_TBL

WHERE CUSTOMER_TBL.CUST_ID = ORDERSJTBL.CUST_ID

AND ORDERSJTBL.QTY > 1 AND CUSTOMERJTBL.CUST_NAME LIKE 'G%'

ORDER BY CUSTOMERJTBL.CUST_NAME;

CUST_ID CUST_NAME CUST_PHONE ORD_NUM QTY

         ----------------------------------------

          287 GAVINS PLACE 3172719991 18D778 10

                    1 строка выбрана.

А вот пример того же оператора после форматирования с целью улучшения его восприятия.

SELECT C.CUST_ID,

             С.CUST_NAME, 

             С.CUST_PHONE, 

             O.ORD_NUM, 

             О.QTY 

       FROM CUSTOMERJTBL С,

       ORDERSJTBL О

              WHERE C.CUST_ID = 0.CUST_ID 

       AND O.QTY > 1 

       AND С.CUST_NAME LIKE 'G%' 

       ORDER BY 2; 

CUST_ID  CUST_NAME  CUST_PHONE  ORD_NUM  QTY

       ----------------------------------------------

          287  GAVINS PLACE  3172719991  18D778   10 

          1 строка выбрана.

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

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

  Правильный порядок таблиц в выражении FROM

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

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

  Правильный порядок условий связывания

Как вы уже узнали из урока 13, "Объединение таблиц в запросах", часто для связывания таблиц используется связующая таблица, имеющая по одному или сразу по несколько общих столбцов с другими таблицами в запросе. Связующую таблицу можно назвать в запросе главной. С ней связаны почти все или даже все другие таблицы в запросе. Обычно столбец связующей таблицы в выражении ключевого слова WHERE размещают справа от знака определяющей связь операции. Таблицы, связываемые с главной таблицей, обычно располагают в порядке от самой маленькой до самой большой, точно так же, как и в списке ключевого слова FROM.

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

FROM таблица1,        Наименьшая из таблиц

таблица2,

таблица3                           Наибольшая из таблиц или связующая таблица 

WHERE таблица1.столбец - таблицаЗ.столбец   Условие связывания 

AND таблица2.столбец = таблицаЗ.столбец       Условие связывания 

[ AND условие1 ] Условие фильтра [ AND условие2 ] Условие фильтра

В этом случае таблицаЗ используется В качестве связующей, а таблица! и таблица2 оказываются связанными с ней.

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

  Наиболее ограничительное условие

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

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

FROM таблица!. Наименьшая из таблиц таблица2,

таблицаЗ Наибольшая из таблиц или связующая таблица

WHERE таблица1.столбец = таблицаЗ.столбец Условие связывания

AND таблица2.столбец = таблицаЗ.столбец Условие связывания

[ AND условие1 ] Наименее ограничительное

[ AND условие2 ] Наиболее ограничительное

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

Для примера рассмотрим следующую тестовую таблицу. 

Имя таблицы                                TEST 

Число строк                                  95867

УСЛОВИЯ                                  WHERE LAST_NAME_= 'SMITH'

                                                       Возвращает 2000 строк

                                                       WHERE CITY = 'INDIANAPOLIS'

                                                       Возвращает 30000 строк 

Наиболее ограничительное условие WHERE LAST_NAME = 'SMITH'

Запрос1

SELECT COUNT(*) 

       FROM TEST

       WHERE LAST_NAME = 'SMITH' 

       AND CITY = 'INDIANAPOLIS';

COUNT(*) 

        --------

          1024

3anpoc2

SELECT COUNT<*) 

       FROM TEST

       WHERE CITY = 'INDIANAPOLIS' 

       AND LAST_NAME = 'SMITH';

COUNT(*)

        --------

         1024

Предположим, что Запрос! выполнялся 20 секунд, а Запроса — 10 секунд. Поскольку Запрос2 был выполнен быстрее и при этом наиболее ограничительное условие было последним, то можно смело предположить, что оптимизатор обрабатывает выражение ключевого слова WHERE от конца к началу.

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

  Полное сканирование таблиц

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

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

  Когда и как избегать полного сканирования таблиц

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

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

Вот список данных, которые следует индексировать.

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

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

  Другие аспекты оптимизации

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

• Использование ключевого слова LIKE и знаков подстановки
• Замена операций OR выражением с ключевым словом IN
• Недостатки использования выражения с ключевым словом HAVING
• Как избежать долгих операций сортировки
• Использование готовых процедур

  Использование LIKE и знаков подстановки

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

Предположим, вам нужно составить запрос с использованием таблицы EMPJ.OYEJTBL, из которой необходимо выбрать данные столбцов EMP^ID, LAST_NAME, FIRST_NAME и STATE. Точнее, получить табельные номера, фамилии, имена и информацию о месте проживания всех служащих по фамилии Стивене. Следующие три оператора SQL представляют собой различные примеры соответствующих запросов с использованием знаков подстановки.

Запрос1:

SELECT EMP__ID, LAST NAME, FIRST__NAME, STATE

FROM EMPLOYEEJTBL

WHERE LAST_NAME LIKE '%И%';

Запрос2:

SELECT EMP_ID, LAST_NAME, FIRST_NAME, STATE

FROM EMPLOYEEJTBL

WHERE LAST_NAME LIKE '%ИВЕНС%';

ЗапросЗ:

SELECT EMP_ID, LAST_NAME, FIRST_NAME, STATE

FROM EMPLOYEEJTBL

WHERE LAST_NAME LIKE 'CT%';

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

  Замена операций OR выражением с ключевым словом IN

Простое использование в операторе 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', 'BROWNSBURG¹, 'GREENFIELD');

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

  Недостатки использования выражения с ключевым словом HAVING

Выражение с ключевым словом 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 на оптимальность?

Каждая реализация языка предлагает свои средства проверки оптимальности операторов SQL. Для проверки на оптимальность операторов этой книги использовались средства Oracle?. В Oracle для этого предусмотрен ряд возможностей. Среди них средства EXPLAIN PLAN, TKPROF и команды SET. Поищите информацию о подобных средствах в документации по той реализации языка, которую вы используете.

  Практикум

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

  Тесты

1. Нужно ли использовать уникальный индекс в случае маленькой таблицы?

2. Что происходит, когда оптимизатор принимает решение не использовать индекс таблицы при выполнении запроса?

3. Где в выражении ключевого слова WHERE следует разместить наиболее ограничительные условия — до или после условий связывания таблиц?

  Упражнения

Перепишите приведенные ниже операторы SQL в виде, который позволит увеличить скорость их выполнения по сравнению с исходным видом. Используйте таблицы EMPLOYEE_TBL И EMPLOYEE_PAY_TBL следующей Структуры.

EMPLOYEE_TBL_____

EMP_ID     VARCHAR2(9) NOT NULL Ключевое поле

LAST_NAME  VARCHAR2U5) NOT NULL

FIRST_NAME VARCHAR2(15) NOT NULL

MIDDLE_NAME VARCHAR2(15)

ADDRESS    VARCHAR2(30) NOT NULL

CITY       VARCHAR2(15) NOT NULL

STATE      CHAR (2)     NOT NULL

ZIP        NUMBER(5)    NOT NULL

PHONE      CHAR(10)

PAGER      CHAR(10)

  CONSTRAINT EMP_PK PRIMARY KEY (EMP_ID)

EMPLOYEE__PAY_ TBL

EMP_ID     VARCHAR2(9)  NOT NULL Ключевое поле

POSITION   VARCHAR2(15) NOT NULL

DATE_HIRE  DATE

PAY_RATE   NUMBER(4,2)  NOT NULL

DATE_LAST-RAISE DATE

CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID) REFERENCED

EMPLOYEE_TBL (EMP_ID)

a. SELECT EMP_ID, LAST_NAME, FIRST_NAME, PHONE 

FROM EMPLOYEE_TBL 

WHERE SUBSTR(PHONE,1,3) = '317' OR

SUBSTR(PHONE,1,3) = '812' OR

SUBSTR(PHONE,1,3) = '765';

б. SELECT LAST_NAME, FIRST_NAME 

FROM EMPLOYEE_TBL 

WHERE LAST_NAME LIKE '%ALL%';

в. SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME, EP.SALARY 

FROM EMPLOYEE_TBL E,

EMPLOYEE_PAY_TBL EP 

WHERE LAST_NAME LIKE 'S%' 

AND E.EMP_ID EP.EMP_ID;