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