W Cat - SQL за 24 часа

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

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

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

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

• Столбцы, используемые в качестве ключевых.

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

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

• Столбцы, часто используемые в условиях запросов.

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

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

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

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

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

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

• Как избежать долгих операций сортировки

• Использование готовых процедур

Ключевое слово 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, скорее всего, индексирован, так что ЗапросЗ может использовать преимущества индекса).

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

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

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

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

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

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