Операторы действий над множествами

ЛЕКЦИЯ № 11 3.3.3. Операторы действий над множествами.......1 3.3.3.1. Объединение результатов нескольких запросов 1 3.3.3.2. Определение разности таблиц...............8 3.3.3.3. Определение пересечения таблиц..........8 3.3.4. Рекурсивные подзапросы в секции WITH.......9 3.4. Оптимизация запросов на выборку данных..........14 3.4.1. Обработка запроса в реляционных СУБД.....15 3.4.2. Основные направления анализа «медленных» SQL-запросов 17 3.4.3. Рекомендации по построению и реструктуризации запросов конкретных типов......................................................20 3.3.3. Операторы действий над множествами При решении некоторых задач иногда требуется объединить результаты, полученные от нескольких запросов SELECT, в единое целое. Для реализации этой возможности в SQL предусмотрены операторы действий над множествами [56]. Результат каждого запроса SELECT считается множеством, и можно объединять эти множества, используя операторы UNION и UNION ALL, в СУБД Oracle также MINUS и INTERSECT, в MS SQL Server – EXCEPT и INTERSECT. Выражения SQL, содержащие операторы действий над множествами, называются сложными запросами (compound query), а каждый отдельный запрос именуется составляющим (component query). Чтобы таблицы результатов нескольких запросов можно было объединять, они должны соответствовать требованиям:  содержать одинаковое число столбцов;  тип данных каждого столбца любой таблицы должен совпадать с типом данных соответствующего столбца любой другой таблицы;  ни одна из таблиц промежуточного запроса не может быть отсортирована с помощью секции ORDER BY;  разрешается использовать в списке возвращаемых элементов только имена столбцов или указывать все столбцы (SELECT *) и запрещается использовать выражения. Иногда составляющие запроса называют вертикальными объединениями (vertical join), поскольку извлекаемые строки формируются на основе столбцов, а не строк. 3.3.3.1. Объединение результатов нескольких запросов При получении данных из таблиц БД необходимость в объединении результатов двух или более запросов в одну таблицу реализуется с помощью оператора UNION. UNION – это оператор, реализующий операцию объединения. Он объединяет вывод двух или более запросов в единый набор строк и столбцов и имеет вид: Запрос_Х UNION [{DISTINCT | ALL}] Запрос_Y UNION [{DISTINCT | ALL}] 1 Запрос_Z… Объединение таблиц с помощью оператора UNION отличается от вложенных запросов и соединений таблиц тем, что в нем ни один из двух (или больше) запросов не управляет другим запросом. Все запросы выполняются независимо друг от друга, а уже их вывод объединяется. Например, необходимо вывести ФИО абонентов и исполнителей ремонтных заявок, фамилии которых начинаются на букву Ш. Для этого можно использовать запрос: SELECT Fio AS AbonentFio FROM Abonent WHERE Fio LIKE 'Ш%' UNION SELECT Fio FROM Executor WHERE Fio LIKE 'Ш%'; Результат объединения представлен на рис. 3.138. Следует отметить, что результат запроса упорядочен по ФИО. ABONENTFIO Школьников С.М. Шлюков М.К. Шмаков С.В. Шубин В.Г. Шубина Т. П. Рис. 3.138. Результат объединения двух запросов Примечания. 1. 1. Имена атрибутов в НД берутся как имена возвращаемых элементов в первом запросе (или псевдоним столбца, как в предыдущем примере). 2. 2. Только последний запрос заканчивается точкой с запятой. Отсутствие точки с запятой после первого запроса дает понять SQL, что имеется еще 1 или более запросов. Объединение таблиц с помощью оператора UNION DISTINCT используется как синоним просто UNION для автоматического исключения дубликатов строк из вывода. Однако в соответствии со стандартом исключение дубликатов строк из вывода является режимом по умолчанию, поэтому слово DISTINCT использовать необязательно. На рис. 3.139 представлена диаграмма Венна для объединения запросов с исключением дубликатов. Рис. 3.139. Диаграмма Венна для UNION 2 Чтобы включить все строки в вывод запроса, следует указать UNION ALL. Использование UNION вместо UNION ALL приведет к операции сортировки с целью устранения дубликатов (см. рис. 3.138). Если бы был не только исполнитель с ФИО Школьников С.М., но и абонент с такими же ФИО и вместо UNION использовался бы UNION ALL, то строка с ФИО Школьников С.М. была бы выведена дважды. На рис. 3.140 представлена диаграмма Венна для объединения запросов с включением всех строк. Рис. 3.140. Диаграмма Венна для UNION ALL Операторы UNION и UNION ALL могут быть скомбинированы, чтобы удалять одни дубликаты, не удаляя других. Объединение запросов (Запрос_X UNION ALL Запрос_Y) UNION Запрос_Z; не обязательно даст те же результаты, что и объединение запросов Запрос_X UNION ALL (Запрос_Y UNION Запрос_Z);, так как дублирующиеся строки удалятся при использовании UNION без ALL. Результаты выполнения промежуточных запросов, участвующих в объединении, упорядочивать запрещено, однако результирующий набор можно отсортировать, указывая порядковые номера возвращаемых элементов. Например, объединить в одну таблицу информацию об услугах и неисправностях газового оборудования, а результат отсортировать по наименованию услуги и неисправности в обратном алфавитном порядке (рис. 3.141) можно с помощью запроса SELECT FailureCD, FailureNM FROM Disrepair UNION ALL SELECT ServiceCD, ServiceNM FROM Services ORDER BY 2 DESC; FAILU RECD 2 7 3 8 4 5 12 2 1 1 FAILURENM Электроснабжение Туго поворачивается пробка крана плиты Теплоснабжение При закрытии краника горелка плиты не гаснет Неисправна печная горелка Неисправен газовый счетчик Неизвестна Не горит АГВ Засорилась водогрейная колонка Газоснабжение 3 4 Водоснабжение Рис. 3.141. Результат объединения двух запросов с сортировкой В объединяемых запросах можно использовать одну и ту же таблицу. Пусть, например, требуется вывести первые 10 значений начисления за 2013 г., уменьшенные на 5 %, если значение меньше 200, на 10 %, если значение от 200 до 300, и уменьшенные на 20 %, если значение больше 300. Вывести также процент уменьшения, код начисления, прежнее и новое значения начислений. Запрос будет выглядеть так: SELECT FIRST 10 AccountCD,'Снижение – 5 %', NachislFactCD, NachislSum AS "Old_Sum", NachislSum*0.95 AS "New_Sum" FROM NachislSumma WHERE NachislSum < 200 AND NachislYear = 2013 UNION SELECT AccountCD,' Снижение – 10 %', NachislFactCD, NachislSum, NachislSum*0.90 FROM NachislSumma WHERE (NachislSum BETWEEN 200 AND 300) AND NachislYear=2013 UNION SELECT AccountCD,' Снижение – 20 %', NachislFactCD, NachislSum, NachislSum*0.80 FROM NachislSumma WHERE NachislSum > 300 AND NachislYear = 2013; Результат объединения представлен на рис. 3.142. ACCOUNTCD 005488 005488 015527 015527 015527 080047 080047 080047 080270 080270 080613 F_1 NACHISLFASTC D Снижение 52 – 10 % Снижение 3 –5% Снижение 53 – 20 % Снижение 67 – 20 % Снижение 24 –5% Снижение 54 – 10 % Снижение 33 –5% Снижение 45 –5% Снижение 55 – 10 % Снижение 70 – 20 % Снижение 57 Old_Sum New_Sum 266,70 240,0300 56,00 53,2000 343,36 274,6880 611,30 489,0400 38,32 36,4040 271,60 244,4400 22,56 21,4320 22,20 21,0900 278,25 250,4250 454,60 363,6800 258,80 232,9200 4 – 10 % 080613 Снижение 58 239,33 215,3970 – 10 % 115705 Снижение 25 37,15 35,2925 –5% 115705 Снижение 37 37,15 35,2925 –5% 136159 Снижение 29 8,30 7,8850 –5% 136160 Снижение 1 56,00 53,2000 –5% 136169 Снижение 75 528,44 422,7520 – 20 % 136169 Снижение 27 25,32 24,0540 –5% 443069 Снижение 77 444,45 355,5600 – 20 % 443690 Снижение 65 290,33 261,2970 – 10 % 443690 Снижение 41 21,67 20,5865 –5% Рис. 3.142. Результат использования одной и той же таблицы при объединении запросов Используя объединение запросов с одной и той же таблицей в MS SQL Server и Oracle, в некоторых случаях полезно проанализировать возможность решить задачу с помощью функций секции GROUP BY (см. п. 3.2.4). Например, пусть требуется вывести средние значения оплат по услугам и средние значения оплат по услугам и абонентам. Эта задача может быть решена двумя способами: с помощью объединения запросов оператором UNION и с использованием функции ROLLUP. Запрос с объединением UNION: SELECT ServiceCD "Услуга", 'по услуге' AS "Услуга", AVG(Paysum) AS "Средняя_Оплата" FROM PaySumma GROUP BY ServiceCD UNION SELECT ServiceCD, AccountCD, AVG (Paysum) AS "Средняя_Оплата" FROM PaySumma GROUP BY ServiceCD, AccountCD; Используя функцию ROLLUP, можно написать значительно короче аналогичный запрос: SELECT ServiceCD, AccountCD, AVG (Paysum), GROUPING(ServiceCD), GROUPING(AccountCD) FROM PaySumma GROUP BY ROLLUP (ServiceCD, AccountCD); В данном запросе столбцы с функцией GROUPING позволяют определить строки итогов (это может потребоваться для обработки значений NULL в основных столбцах итоговых строк). В отличие от предыдущего запроса с 5 UNION запрос с ROLLUP выведет дополнительно строку общего итога – среднее значение оплат по таблице PaySumma. Приведем еще один пример. Допустим нужно в одном запросе вывести средние значения оплат по услугам, средние значения оплат по абонентам и средние значения оплат по услугам и абонентам. Традиционное решение: SELECT CAST(SERVICECD AS VARCHAR(2)) "Услуга", 'по услуге' AS "Услуга", AVG (Paysum) AS "Средняя_Оплата" FROM PaySumma GROUP BY ServiceCD UNION SELECT 'по абоненту' AS "Услуга" , AccountCD, AVG (Paysum) AS "Средняя_Оплата" FROM PaySumma GROUP BY AccountCD UNION SELECT CAST(SERVICECD AS VARCHAR(2)), AccountCD, AVG(Paysum) AS "Средняя_Оплата" FROM PaySumma GROUP BY ServiceCD, AccountCD; Более простое решение с помощью функции CUBE: SELECT ServiceCD, AccountCD, AVG (Paysum) FROM PaySumma GROUP BY CUBE(ServiceCD, AccountCD); Объединения результатов нескольких запросов в один результирующий набор разрешены и внутри подзапросов. Например, требуется вывести все данные об оплатах за услугу с кодом, равным 1, по абонентам, фамилия которых начинается с буквы М, и по абонентам, которые подавали заявки с неисправностью газового оборудования с кодом, равным 1. Выполняем запрос: SELECT * FROM PaySumma WHERE AccountCD IN (SELECT AccountCD FROM Abonent WHERE Fio Like 'М%' UNION SELECT R.AccountCD FROM Request R WHERE FailureCD=1) AND ServiceCD=1 ORDER BY AccountCD; Результат объединения представлен на рис. 3.143. Из результата следует, что информация об оплатах выведена по абонентам, которые подавали заявки по неисправности с кодом, равным 1 (лицевые счета '005488', '080270', '115705', '136169'), и по абонентам, фамилия которых начинается с буквы М (лицевые счета '115705', '126112'). Так как для объединения использован оператор UNION, то исключены повторяющиеся строки по абоненту с номером лицевого счета, равным '115705', информация о котором возвращается в каждом из запросов, объединяемых с помощью UNION. 6 PAYFAC ACCOUN SERVICEC PAYS PAYDA PAYMO PAYYEAR TCD TCD D UM TE NTH 29 005488 1 65,00 03.05.201 4 2010 27 080270 1 60,00 05.03.201 2 2012 2 37 080270 1 58,10 07.01.201 12 2010 1 42 080270 1 60,00 07.06.201 5 2011 1 24 115705 1 37,15 04.11.201 10 2013 3 30 115705 1 37,80 12.07.201 5 2011 1 36 115705 1 37,15 23.12.201 11 2013 3 48 115705 1 37,15 10.08.201 6 2013 3 33 126112 1 15,30 08.09.201 8 2010 45 126112 1 25,30 10.09.201 8 2011 1 26 136169 1 25,32 03.02.201 1 2013 3 38 136169 1 28,32 08.02.201 1 2011 1 43 136169 1 28,32 05.03.201 2 2012 2 Рис. 3.143. Результат объединения запросов внутри подзапроса Часто полезна операция объединения двух запросов, в которой второй запрос выбирает строки, исключенные первым. Такая операция называется внешним объединением. Рассмотрим пример. Вывести список ремонтных заявок с указанием ФИО исполнителей тех заявок, которым назначен исполнитель, но при этом не отбрасывая и заявки, которым исполнитель не назначен. Если заявка не принята к исполнению, вместо ФИО исполнителя выводить «Неизвестен». Можно получить желаемые сведения, сформировав объединение двух запросов, один из которых выполняет выборку заявок с ФИО их исполнителей, а второй выбирает заявки с NULL в столбце ExecutorCD. SELECT R.RequestCD "Номер заявки", R.AccountCD "Номер лицевого счета абонента",E.Fio "ФИО исполнителя" FROM Executor E, Request R WHERE E.ExecutorCD = R.ExecutorCD UNION SELECT Requestcd, Accountcd, 'Неизвестен' FROM Request 7 WHERE ExecutorCD IS NULL; 3.3.3.2. Определение разности таблиц Объединение таблиц с помощью оператора UNION реализовано практически во всех РСУБД. Кроме этого, некоторые СУБД, в частности Oracle и MS SQL Server, предоставляют средства для выполнения и других операций над множествами. Для вычисления разности таблиц СУБД Oracle предоставляет специальный оператор MINUS. Результатом действия оператора MINUS являются строки, оставшиеся после вычитания строк, возвращенных вторым запросом, из строк, возвращенных первым [45]. На рис. 3.144 представлена диаграмма Венна для разности таблиц. Рис. 3.144. Диаграмма Венна для разности Ранее в п. 3.3.2.2 был рассмотрен пример нахождения кодов улиц, на которых не проживают абоненты (рис. 3.110), и приведена реализация запроса с использованием подзапроса и проверки на членство во множестве. Данный пример также был рассмотрен в п. 3.3.2.6, где была приведена реализация с использованием предиката EXISTS. В Oracle аналогичный запрос может быть реализован более просто с использованием оператора MINUS: SELECT S.StreetCD FROM Street S MINUS SELECT STREETCD FROM Abonent A; MS SQL Server для выполнения операции вычитания из первого множества строк второго множества предоставляет оператор EXCEPT [23]. Предыдущий запрос в MS SQL Server может быть записан так: SELECT S.StreetCD FROM Street S EXCEPT SELECT STREETCD FROM Abonent A; 3.3.3.3. Определение пересечения таблиц СУБД Oracle и MS SQL Server позволяют реализовывать операцию пересечения множеств и предоставляют для этих целей оператор INTERSECT. Оператор INTERSECT возвращает строки, извлекаемые обоими запросами, т.е. строки, которые имеются и в одной, и в другой таблице [45]. Предположим, необходимо вывести информацию об абонентах, подававших ремонтные заявки (рис. 3.145). Запрос может быть реализован с помощью оператора INTERSECT: SELECT AccountCD FROM Abonent A 8 INTERSECT SELECT AccountCD FROM Request R; ACCOUNTCD 005488 015527 080047 080270 080613 115705 136159 136160 136169 443069 Рис. 3.145. Результат использования оператора INTERSECT 3.3.4. Рекурсивные подзапросы в секции WITH Рекурсивные производные таблицы в секции WITH позволяют создавать рекурсивные запросы SELECT. Под рекурсивным запросом понимается запрос к таблице, которая сама каким-либо образом изменяется при выполнении этого запроса [21]. Рекурсивные запросы используются для обращения к иерархически связанным данным. Это требуется не всегда: так, когда узлы дерева размечены особыми служебными значениями, запросы можно свести к простому просмотру таблицы. Однако в случае, если иерархические данные изменяются регулярно, то часто идут на обычное хранение, узлы в таблицах не размечаются, а затем используют рекурсивные запросы для получения необходимой информации. Особенности использования рекурсивных производных таблиц:  рекурсивная производная таблица имеет ссылку на саму себя;  рекурсивная производная таблица – это объединение в одном запросе (UNION) рекурсивных и нерекурсивных частей;  должна присутствовать, по крайней мере, одна нерекурсивная часть, которая сообщает, откуда начинать выполнение запроса (начальная точка рекурсии);  нерекурсивные части располагаются в начале запроса, содержащего объединение;  рекурсивные части отделяются от нерекурсивных и от самих себя с помощью конструкции UNION ALL;  использование опции DISTINCT, секций GROUP BY, HAVING, а также использование агрегатных функций не допускается в рекурсивных частях запроса;  рекурсивная часть может иметь только одну ссылку на саму себя и только в секции FROM;  рекурсивная ссылка не может участвовать во внешнем соединении таблиц. 9 Все три СУБД, рассматриваемые в учебнике, реализуют рекурсивные запросы в соответствии со стандартом с помощью секции WITH. Рассмотрим пример использования рекурсивной производной таблицы в СУБД Firebird. Как уже отмечалось, если в Firebird используется рекурсивный подзапрос, то в секции WITH указывается ключевое слово RECURSIVE. Предположим, что таблица Abonent имеет вид, представленный на рис. 3.99. Необходимо с учетом изменений в таблице Abonent вывести по каждому абоненту общие суммы оплат за 2012 г. и 2013 г. (см. рис. 3.95) в виде дерева: перед ФИО каждого управляющего по дому поставить символ +, а ниже вывести всех абонентов, проживающих в данном доме, с указанием перед их ФИО четырех символов пробела. Запрос будет выглядеть так: WITH RECURSIVE Year_Abon_Pay AS (SELECT PayYear, AccountCD, SUM(PaySum) AS Total_Sum FROM PaySumma GROUP BY PayYear, AccountCD), Abonent_Tree AS (SELECT AccountCD, Head_Account, Fio, CAST ('+' AS VARCHAR(4)) AS Indent FROM Abonent WHERE Head_Account IS NULL UNION ALL SELECT A.AccountCD, A.Head_Account, A.Fio, CAST (' ' AS VARCHAR(4)) AS Indent FROM Abonent A JOIN Abonent_Tree Tr ON A. Head_Account = Tr.AccountCD) SELECT A.AccountCD, A. Indent || A.Fio AS Fio, God_2012.Total_Sum AS "Total_2012", God_2013.Total_Sum AS "Total_2013" FROM Abonent_Tree A LEFT JOIN Year_Abon_Pay God_2012 ON A.AccountCD = God_2012.AccountCD AND God_2012.PayYear = 2012 LEFT JOIN Year_Abon_Pay God_2013 ON A.AccountCD = God_2013.AccountCD AND God_2013.PayYear = 2013; Результат выполнения запроса представлен на рис. 3.146. 10 ACCOUNTCD FIO Total_2012 Total_2013 005488 +Аксенов С.А. 280,00 316,00 115705 +Мищенко Е.В. 553,85 111,45 015527 Конюхов В.С. 630,10 996,30 443690 +Тулупова М.И. 20,00 314,47 136159 +Свирина З.А. 428,30 188,43 443069 +Стародубцев Е.В. 504,97 444,45 136160 +Шмаков С.В. 18,00 56,00 136169 +Денисова Е.К. 723,69 553,76 126112 Маркова В.П. 615,40 NULL 080613 +Лукашина Р.М. 23,60 498,15 080047 +Шубина Т. П. 99,56 316,36 080270 +Тимошкина Н.Г. 510,00 738,00 Рис. 3.146. Результат выполнения запроса с рекурсией Как следует из результата (рис. 3.146), Firebird выводит в виде иерархического дерева результаты рекурсивного запроса без задания какихлибо дополнительных условий сортировки. Запросы с рекурсивными производными таблицами выполняются следующим образом: выбирается первая строка из нерекурсивной части запроса; для данной строки выполняется каждая рекурсивная часть с учетом текущих значений строки как параметров; если текущая рекурсивная часть не возвращает строк, происходят возврат на шаг назад и получение следующей строки из результирующего набора нерекурсивной части запроса. Синтаксис реализации рекурсивных запросов в MS SQL Server аналогичен синтаксису Firebird, за исключением того, что не используется ключевое слово RECURSIVE. Однако MS SQL Server требует явного задания сортировки для построения правильного иерархического дерева. Так, предыдущий запрос при удалении слова RECURSIVE и замене знака конкатенации с || на + в MS SQL Server выдаст результат, но абоненты с заполненным столбцом Head_Account (Маркова В.П. и Конюхов В.С.) будут выведены в конце списка, а не под своими вышестоящими абонентами. Приведем пример корректной реализации рекурсивного запроса в MS SQL Server. При этом допустим, что в таблице Abonent имеется несколько уровней вложенности, например, абонент Денисова Е.К. (лицевой счет 136169) не только является вышестоящей для абонента Маркова В.П. (лицевой счет 126112), но и сама подчиняется абоненту Аксенов С.А. (лицевой счет 005488). Пусть также абоненту Аксенов С.А. подчиняется Лукашина Р.М. (лицевой счет 080613), которая не имеет подчиненных. Суммы оплат абонентов выводить не будем, продемонстрируем только построение иерархического дерева. Запрос можно составить следующим образом: WITH Abonent_Tree AS (SELECT AccountCD, Head_Account, Fio, 0 AS Tier, 11 CAST('' AS VARCHAR(MAX)) + Fio AS PathStr FROM Abonent WHERE Head_Account IS NULL UNION ALL SELECT A.AccountCD, A.Head_Account, A.Fio, Tr.Tier + 1, Tr.PathStr + A.Fio FROM Abonent A JOIN Abonent_Tree Tr ON A. Head_Account = Tr.AccountCD) SELECT A.AccountCD, SPACE(A.Tier*2) + A.Fio AS Fio, A.Tier, A.PathStr FROM Abonent_Tree A ORDER BY A.PathStr; Приведенный запрос выполняет визуализацию данных таблицы Abonent в виде дерева, добавляя отступы в иерархии при извлечении данных (рис. 3.147). Отступы реализуются с помощью функции SPACE, которая добавляет перед Fio определенное число пробелов в соответствии с уровнем строки внутри дерева (столбец Tier). Для корректной сортировки вычисляется путь (PathStr) как строка из Fio абонентов от вышестоящего до подчиненного нижнего уровня. При этом используется тип данных MS SQL Server VARCHAR (MAX), поскольку неизвестно максимальное количество символов, которое потребуется при конкатенации Fio в рекурсивном запросе, который может оказаться очень глубоким. ACCOUNTC FIO D 005488 Аксенов С.А. 136169 Денисова Е.К. 126112 Маркова В.П. 080613 Лукашина Р.М. 115705 Мищенко Е.В. 015527 Конюхов В.С. 136159 Свирина З.А. 443069 Стародубцев Е.В. 080270 Тимошкина Н.Г. 443690 Тулупова М.И. 136160 Шмаков Tier PathStr Аксенов С.А. 1 Аксенов С.А.Денисова Е.К. 2 Аксенов С.А.Денисова Е.К.Маркова В.П. 1 Аксенов С.А.Лукашина Р.М. Мищенко Е.В. 1 Мищенко Е.В.Конюхов В.С. Свирина З.А. Стародубцев Е.В. Тимошкина Н.Г. Тулупова М.И. Шмаков С.В. 12 С.В. 080047 Шубина Шубина Т.П. Т.П. Рис. 3.147. Результат выполнения запроса с рекурсией в MS SQL Server В Oracle иерархические запросы появились еще задолго до появления стандарта. Сначала задача рекурсивных запросов к иерархически организованым данным решалась с помощью оператора CONNECT BY, позже, начиная с версии 11g, реализация рекурсивных запросов в Oracle была сведена к стандартной с использованием секции WITH [55]. Oracle, как и MS SQL Server, требует явного задания сортировки для корректного вывода результатов рекурсивного запроса и содержит для этого дополнительные опции в синтаксисе секции WITH: WITH имя_производной_таблицы1 [(<список_столбцов>)] AS (<табличный_подзапрос> ) [SEARCH [DEPTH FIRST] BY <имя_столбца> [ASC|DESC] SET <псевдоним_столбца>] [, имя_производной_таблицы2 [(<список_столбцов>)] AS (<табличный_подзапрос>)] [SEARCH [DEPTH FIRST] BY <имя_столбца> [ASC|DESC] SET <псевдоним_столбца>] ... . Следует отметить, что в Oracle список столбцов, в случае использования рекурсивного подзапроса, должен быть задан обязательно. Конструкция SEARCH используется для придания порядка строкам результата в вынесенном рекурсивном запросе [55]. С помощью SEARCH задается псевдоним столбца, в котором СУБД автоматически проставляет числовые значения на основании значений указанного столбца таблицы подзапроса (<имя_столбца>) и который включается в результирующий набор. На этот столбец можно сослаться далее уже в обычной секции ORDER BY для создания нужного порядка строк. Так, чтобы реализовать в Oracle предыдущий рекурсивный запрос, не требуется создание столбца PathStr для искусственной сортировки: WITH Abonent_Tree (AccountCD,Head_Account,Fio,Tier) AS (SELECT AccountCD, Head_Account, Fio, 0 AS Tier FROM Abonent WHERE Head_Account IS NULL UNION ALL SELECT A.AccountCD, A.Head_Account, A.Fio, Tr.Tier + 1 FROM Abonent A JOIN Abonent_Tree Tr ON A. Head_Account = Tr.AccountCD) SEARCH DEPTH FIRST BY AccountCD SET OrderVal SELECT A.AccountCD, LPAD (A.Fio, Length(A.Fio)+ A.Tier*2,' ') AS Fio, A.Tier FROM Abonent_Tree A ORDER BY OrderVal; 13 В данном запросе используется конструкция SEARCH для указания необходимости сортировки конечного результата по номеру лицевого счета. Результат будет отсортирован по возрастанию номеров лицевых счетов с учетом иерархии в дереве (рис. 3.148). ACCOUNTC D 005488 FIO Tier Аксенов С.А. 080613 Лукашина 1 Р.М. 136169 Денисова 1 Е.К. 126112 Маркова 2 В.П. 080047 Шубина Т.П. 080270 Тимошкина 0 Н.Г. 115705 Мищенко Е.В. 015527 Конюхов 1 В.С. 136159 Свирина З.А. 136160 Шмаков С.В. 443069 Стародубцев 0 Е.В. 443690 Тулупова М.И. Рис. 3.148. Результат выполнения запроса с рекурсией в Oracle 3.4. ОПТИМИЗАЦИЯ ЗАПРОСОВ НА ВЫБОРКУ ДАННЫХ Говоря о построении запросов на языке SQL, нельзя не затронуть такой важный аспект, как повышение оперативности выполнения запросов в ИС. Оптимизация SQL-запросов вносит существенный вклад в повышение производительности ИС в целом. Хотя SQL и задумывался как средство работы конечного пользователя, в конце концов, он стал настолько сложным, что превратился в инструмент программиста. Каждая современная СУБД имеет собственный оптимизатор запросов, однако существуют наиболее типичные проблемы, с которыми не справляются оптимизаторы многих СУБД. 14 Оптимизация SQL-запросов является наиболее важным и интересным направлением исследований и разработок во всей области баз данных (БД). Важность этого направления определяется тем, что от оптимизации запросов критически зависит общая производительность любой SQL-ориентированной СУБД [57]. Знакомство с методами оптимизации запросов полезно широкой аудитории: проектировщикам и администраторам систем баз данных, разработчикам приложений баз данных и даже пользователям этих приложений. В настоящем учебнике анализируются методы повышения оперативности формирования отчетов на уровне языка SQL, исследуются основные типы оптимизации SQL-запросов. Оптимизацию реляционных запросов нужно рассматривать с нескольких точек зрения. Во-первых, это внутренняя задача СУБД, которая заключается в определении наиболее эффективного способа выполнения реляционных запросов. Во-вторых, это задача программиста (или квалифицированного пользователя): она заключается в построении таких реляционных запросов, для которых СУБД могла бы использовать более эффективные способы нахождения данных [57]. Практически каждый разработчик может создавать запросы SQL, однако оптимизация запросов позволит ощутить разницу между приемлемой производительностью и по-настоящему эффективным приложением. 3.4.1. Обработка запроса в реляционных СУБД Каждая команда языка SQL может быть выполнена разными способами. Выполнение запроса в СУБД состоит из последовательности шагов, каждый из которых либо физически извлекает данные из памяти, либо делает подготовительную работу. Последовательность шагов, которую строит оптимизатор, называется планом выполнения [57]. Определение наиболее оптимального плана выполнения запроса называется оптимизацией. Выбором этого плана занимается оптимизатор – специальный компонент СУБД. В запросах на выборку данных план выполнения запроса можно задавать вручную с помощью секции PLAN (см. разд. 3.2). Но важно понимать, что стратегия оптимизации зависит от данных: наилучшая стратегия для выполнения определенного запроса может измениться в зависимости от того, сколько строк содержит таблица и какие значения хранятся в этих строках, так как это меняет селективность используемых индексов. При явном задании PLAN разработчик фактически сообщает серверу БД, что существует только один способ выполнения запроса, и оптимизатор СУБД не может изменить эту стратегию в ответ на изменение условий данных. Иными словами, PLAN полностью отключает оптимизатор, в связи с чем вручную определять планы не рекомендуется [29]. 15 Рассмотрим этапы обработки запроса в СУБД. Обработка запроса, поступившего в систему и представленного на языке запросов, состоит из фаз (этапов), представленных на рис. 3.149. Рис. 3.149. Последовательность выполнения запросов в реляционных СУБ В первой фазе запрос подвергается лексическому и синтаксическому анализу [58]. При этом вырабатывается его внутреннее представление, отражающее структуру запроса и содержащее информацию, которая характеризует объекты БД, упомянутые в запросе (отношения, поля и константы). Внутреннее представление запроса используется и преобразуется на следующих стадиях обработки запроса. Во второй фазе запрос в своем внутреннем представлении подвергается логической оптимизации. При этом могут применяться различные преобразования, «улучшающие» начальное представление запроса. Среди этих преобразований могут быть эквивалентные преобразования, после проведения которых получается внутреннее представление, семантически эквивалентное начальному (например, приведение запроса к некоторой канонической форме). Преобразования могут быть и семантическими, когда получаемое представление не является семантически эквивалентным начальному, но гарантирует, что результат выполнения преобразованного запроса совпадает с результатом запроса в начальной форме при соблюдении ограничений целостности, существующих в БД. В любом случае после выполнения второй фазы обработки запроса его внутреннее представление остается непроцедурным, хотя и является в некотором смысле более эффективным, чем начальное. Третья фаза обработки запроса состоит в выборе на основе информации, которой располагает оптимизатор, набора альтернативных процедурных планов выполнения данного запроса в соответствии с его внутреннем представлением, полученным на второй фазе. Основой является информация о существующих путях доступа к данным. Единственный путь доступа, который возможен в любом случае, – это последовательное чтение (FULL). Возможность использования других путей доступа зависит от способов размещения данных в памяти (например, кластеризация данных), наличия индексов и формулировки самого запроса. На этом же этапе для каждого плана оценивается предполагаемая стоимость выполнения запроса по этому плану. При оценках используется либо доступная оптимизатору статистическая информация о состоянии БД, либо информация о механизмах реализации различных путей доступа. Из полученных альтернативных планов выбирается наиболее оптимальный с точки зрения некоторого (заранее выбранного или заданного) критерия. 16 Внутреннее представление этого плана теперь соответствует обрабатываемому запросу. В четвертой фазе по внутреннему представлению наиболее оптимального плана выполнения запроса формируется процедурное представление плана. Выполняемое представление плана может быть программой в машинных кодах, если система ориентирована на компиляцию запросов в машинные коды, или быть машинно-независимым, но более удобным для интерпретации, если система ориентирована на интерпретацию запросов. В любом случае это непринципиально, поскольку четвертая фаза обработки запроса уже не связана с оптимизацией. Наконец, в последней, пятой фазе обработки запроса происходит его реальное выполнение в соответствии с выполняемым планом запроса. Это либо выполнение соответствующей подпрограммы, либо вызов интерпретатора с передачей ему для интерпретации выполняемого плана. Следует отметить, что анализ и оптимизация запроса обычно не занимают много времени (требуются только при подготовке, а не каждый раз при запуске). Также заметим, что для нашего рассмотрения несущественно разделение процесса обработки запроса на подготовительную (1–4 фазы) и исполнительную (5 фаза) части. В нашу схему укладывается и реальное отделение по времени первых 4-х фаз от 5-й (подход с предварительной компиляцией запроса до реального выполнения), и последовательное выполнение всех пяти фаз при каждом выполнении запроса. Заметим, что некоторые методы оптимизации (и даже подходы к оптимизации) сильно зависят от общей организации обработки запроса. При отрыве во времени процесса компиляции от реального выполнения запроса оптимизатор располагает меньшей и менее достоверной информацией, чем в том случае, когда этап компиляции тесно привязан к этапу выполнения (выполняется в рамках транзакции пользователя). 3.4.2. Основные направления анализа «медленных» SQL-запросов Рассмотрим второй аспект оптимизации SQL-запросов, который напрямую зависит от профессиональной грамотности разработчика SQLзапросов. При разработке новых запросов важно не просто получить необходимые данные, но также правильно построить запрос. В большинстве случаев одни и те же данные могут быть получены с помощью по-разному составленных SQL-запросов, и от того, как составлен запрос, будет зависеть скорость получения этих данных. Например, задачу поиска абонентов, подавших хотя бы одну заявку на ремонт газового оборудования, можно построить шестью запросами с одинаковой семантикой, но с разной структурой: SELECT DISTINCT A.AccountCD FROM Abonent A, Request R 17 WHERE A.AccountCD = R.AccountCD; SELECT DISTINCT A.AccountCD FROM Abonent A INNER JOIN Request R ON A.AccountCD = R.AccountCD; SELECT AccountCD FROM Abonent WHERE AccountCD IN (SELECT AccountCD FROM Request); SELECT AccountCD FROM Abonent WHERE AccountCD = ANY (SELECT AccountCD FROM Request); SELECT AccountCD FROM Abonent A WHERE EXISTS (SELECT * FROM Request R WHERE A.AccountCD = R.AccountCD); SELECT AccountCD FROM Abonent A WHERE 0 < (SELECT COUNT(*) FROM Request R WHERE A.AccountCD = R.AccountCD); Прежде чем начать анализ причин медленного выполнения запроса, нужно запустить его вне контекста клиентского приложения. Если при этом проблем не возникает, значит, имеет место проблема с кодом в приложении, а не с реализацией SQL-запроса. Иногда случается, что подготовка запроса в приложении занимает много времени. Часть этого времени занимает сервер БД, а часть – компоненты доступа к БД, которые производят поиск значений метаданных во время подготовки запроса. Если вне контекста приложения скорость не увеличилась, следует оптимизировать непосредственно SQL-запрос. Оптимизация направлена на минимизацию времени отклика для заданного запроса в данной системной среде. Целью оптимизации проблемных запросов должно стать нахождение альтернативных способов получения требуемого результата, преобразование запроса в такую эквивалентную форму, при которой удастся добиться более высокой производительности [59]. Одна из основных причин, по которой запрос может выполняться медленно, – большой объем обрабатываемых данных. Конечно, существуют запросы, которые по своей природе должны перерабатывать большое 18 количество данных, однако большинство запросов можно изменить так, чтобы они обращались к меньшему объему данных. Анализ медленно выполняющегося запроса проводится в два этапа. 1 этап. Сначала необходимо понять, не извлекает ли приложение больше данных, чем нужно. Иногда запрос отбирает больше данных, чем необходимо, а потом отбрасывает некоторые из них. Это требует дополнительной работы от SQL-сервера, приводит к росту накладных расходов передачи по сети, увеличивает потребление памяти и процессорного времени. Ниже перечислено несколько типичных ошибок. 1. Выборка ненужных строк. Например, применяется такой прием: выполнить запрос SELECT, который возвращает много строк, затем выбрать первые несколько необходимых строк. 2. Выборка всех столбцов. Выборка всех столбцов (SELECT * FROM… ) может препятствовать некоторым методам оптимизации, а также увеличивает потребление сервером ресурсов. Особенно это заметно при выборке всех столбцов из соединения нескольких таблиц. По этой причине, а также, чтобы уменьшить риск возникновения ошибок при изменении перечня столбцов таблицы, некоторые администраторы БД вообще запрещают применять SELECT * FROM.... 2 этап. Необходимо понять, не анализирует ли SQL-сервер больше строк, чем это необходимо. Наиболее важным шагом, который можно предпринять для увеличения скорости выполнения запроса, является ограничение количества данных, возвращаемых из БД. Ненужные строки могут исключаться в два этапа: в БД с помощью SQL-запроса и в самом приложении(клиенте) с использованием определенных ограничений, фильтрации. Для увеличения скорости на первом этапе необходимо исключить как можно большее количество строк. Таким образом, значительную часть нагрузки в процессе обработки строк можно переложить на сервер БД, тем самым исключив ненужные строки перед их возвращением в приложение. Распространенное название этой операции – перенос процесса выбора строк на сервер БД [37]. Однако иногда запрашивание большего количества данных, чем необходимо, упрощает разработку, так как дает возможность использовать один и тот же код во многих местах. Это может быть применимо только в случае, если точно известно, во что это обходится с точки зрения производительности. Простейшими метриками стоимости запроса являются:  время выполнения;  количество проанализированных строк;  количество возвращенных строк. Анализ времени выполнения полезен своей объективностью, однако зависит от текущей загрузки. Другие факторы – блокировки, высокая степень конкурентности и особенности оборудования – тоже могут оказывать существенное влияние на время выполнения запроса. Эта метрика полезна 19 для нахождения запросов, которые заметно нагружают сервер, но она ничего не говорит о том, является ли наблюдаемое время выполнения разумным для запроса заданной сложности. При анализе запросов полезно принимать во внимание, какое количество строк было просмотрено сервером, так как это показывает, насколько эффективно запрос находит нужные данные. Но и как время выполнения эта метрика не идеальна для выявления плохих запросов. Не все обращения к строкам одинаковы. Доступ к коротким строкам занимает меньше времени, а выборка строк из памяти происходит гораздо быстрее, чем чтение их с диска. Таким образом, при поиске причины медленного выполнения того или иного запроса необходим комплексный взгляд на проблему и анализ различных параметров. 3.4.3. Рекомендации по построению и реструктуризации запросов конкретных типов В различных СУБД возможности собственного оптимизатора запросов несколько отличаются, однако существуют некоторые наиболее типичные проблемы, в связи с чем можно выделить общие рекомендации по разработке новых запросов и оптимизации уже существующих. Известен ряд рекомендаций по разработке и реструктуризации запросов конкретных типов: запросов с условием WHERE, с группировкой GROUP BY, с агрегатной функцией COUNT(), а также запросов, содержащих объединения UNION, соединения нескольких таблиц JOIN и запросов с подзапросами. При этом следует отметить, что в некоторых случаях для выбора рационального варианта построения запроса требуется предварительный анализ БД, так как правильная стратегия может меняться в зависимости от данных БД. Рассмотрим особенности разработки запросов различных типов (различные типы оптимизации SQL-запросов). Следует отметить, что практическое исследование времени выполнения по-разному построенных запросов различных типов необходимо выполнять на БД с миллионами строк в таблицах. Исследование производительности приведенных далее SQLзапросов на таблицах учебной БД не даст ожидаемого эффекта. Запросы с WHERE. Для ускорения поиска в таблице строк с конкретными значениями рекомендуется создавать индекс по соответствующему столбцу: SELECT * FROM PaySumma WHERE PaySum = 250; Порядок следования столбцов в индексе значения не имеет. При выборке по диапазону значений рекомендуется создавать индекс по соответствующему столбцу: SELECT * FROM PaySumma WHERE PaySum >= 20 AND PaySum <= 500; В запросах, содержащих несколько условий, данные условия должны располагаться в порядке уменьшения селективности. Данный тип оптимизации запросов относится к таким типам оптимизации, которым для выбора рационального варианта построения запроса требуется предварительный анализ данных БД. Например, необходимо выбрать данные 20 из таблицы начислений абонентам. Данные можно получить с помощью запроса: SELECT * FROM NachislSumma WHERE ServiceCD = 1 AND NachislYear = 2010; Однако учитывая, что в таблице NachislSumma начислений за 2010 год около 13 % от общего количества начислений, а по услуге с кодом 1 – больше 40 %, запрос должен выглядеть так: SELECT * FROM NachislSumma WHERE NachislYear = 2010 AND ServiceCD = 1; Запросы, содержащие условие для проиндексированного столбца таблицы с небольшим числом строк, которая может быть считана за одно обращение к памяти, нужно формулировать так, чтобы система игнорировала индекс. Например, запрос на выборку наименования услуги с кодом 1 следует записать следующим образом: SELECT * FROM Services WHERE ServiceCD*1 = 1;, где ServiceCD – это первичный ключ, по которому есть индекс. Но при доступе через индекс потребуется минимум два обращения к диску. Включение индексированного столбца в выражение (ServiceCD*1 вместо ServiceCD) подавляет использование индекса. Запросы с UNION. Запросы, содержащие объединение результатов нескольких запросов UNION, всегда выполняются путем создания и заполнения временной таблицы. К подобным запросам СУБД может применить не так уж много оптимизаций. Однако можно помочь оптимизатору, перенеся секции WHERE, ORDER BY и другие условия из внешнего запроса в каждый SELECT, входящий в объединение [59]. При необходимости объединить результаты нескольких запросов рекомендуется использовать UNION ALL вместо UNION, если не требуется, чтобы сервер устранял строки-дубликаты или можно быть уверенными в том, что в объединяемых отношениях отсутствуют одинаковые строки или наличие одинаковых строк некритично. Если ключевое слово ALL отсутствует, сервер будет создавать временную таблицу, и для соблюдения уникальности будет производиться сравнение строк целиком. Такая операция может занять много времени [59]. В запросах необходимо варьировать использование UNION или OR в зависимости от наличия индекса. При наличии индекса рекомендуется использовать UNION, а при отсутствии – OR. Например, начисления по первой и второй услугам могут быть получены одним из способов: SELECT * FROM NachislSumma WHERE ServiceCD = 1 UNION ALL SELECT * FROM NachislSumma WHERE ServiceCD = 2; или SELECT * FROM NachislSumma WHERE ServiceCD = 1 OR ServiceCD = 2; 21 Более быстрым будет первый вариант, так как столбец ServiceCD таблицы начислений имеет индекс. Запросы с GROUP BY. Выполнение запроса с GROUP BY можно ускорить при наличии подходящих индексов. Однако существует ряд особенностей [59]. Если нужна группировка по значению столбца, который извлекается при соединении из справочной таблицы, то более продуктивно группировать по идентификатору из этой таблицы, а не по его значению. Тем не менее, требуется выводить значения столбцов, при этом в секцию SELECT нельзя включать столбцы, по которым не производится группировка. Чтобы обойти эту сложность, можно воспользоваться функциями MIN() или MAX(), если точно известно, что значения в группе одинаковы. Фиктивные MIN() и MAX() – признак того, что запрос структурирован неправильно, но иногда требуется, чтобы запрос выполнялся максимально быстро. Запросы с COUNT(). В общем случае запросы, содержащие COUNT(), с трудом поддаются оптимизации, поскольку обычно они должны переработать большой объем данных [59]. При использовании COUNT() Firebird вынуждена читать всю таблицу целиком, и это является достаточно «тяжелой» операцией для сервера, особенно если таблица содержит большой объем данных [60]. COUNT() – это особая функция, которая решает две очень разные задачи: подсчитывает значения и строки. Значение – это выражение, отличное от NULL. Если указать имя столбца или какое-нибудь другое выражение в скобках, то COUNT() посчитает, сколько раз это выражение имеет значение (сколько раз оно не равно NULL). Вторая форма COUNT() просто подсчитывает количество строк в результирующем наборе. При использовании COUNT(*) столбцы вообще игнорируются, а подсчитываются сами строки. Одна из наиболее часто встречающихся ошибок – задание имени столбца в скобках, когда требуется подсчитать строки. Если необходимо подсчитать строки, то следует использовать COUNT(*), что позволит избежать возможного падения производительности. Например, подсчет количества абонентов по таблице Abonent, в которой первичным ключом является номер лицевого счета AccountCD, должен быть реализован таким образом: SELECT COUNT(*) FROM Abonent; Реализация подсчета в виде SELECT COUNT(AccountCD) FROM Abonent; приведет к увеличению времени выполнения запроса, в данном случае не столь значительному, так как количество строк таблицы сравнительно невелико. При наличии индекса выполнение операции COUNT становится очень быстрым. 22 Запросы с соединениями JOIN. Поиск эффективных алгоритмов выполнения операции соединения как одной из наиболее накладных реляционных операций имеет существенное значение [58]. В запросах, которые реализуют соединение двух и более таблиц, эти таблицы должны стоять в списке в порядке уменьшения количества строк в них. Данный тип оптимизации запросов требует предварительного анализа данных БД. Например, начисления по каждому абоненту нужно получать так: SELECT * FROM NachislSumma N INNER JOIN Abonent A ON (A.AccountCD = N.AccountCD); Для повышения производительности рекомендуется строить индексы по столбцам, используемым во фразах ON или USING [57]. В запросах с соединением в секциях GROUP BY и ORDER BY наиболее рационально указывать столбцы только из одной таблицы, тогда СУБД сможет воспользоваться для этой операции индексом [59]. Запросы с подзапросами. Обработка подзапросов – предмет напряженного труда создателей оптимизатора СУБД. Среди методов оптимизации подзапросов, поддерживаемых многими СУБД, можно отметить такие, как сращивание подзапросов, удаление подзапросов с использованием оконных функций, устранение представлений для запросов с группировкой и т.д. Эти методы распознают и устраняют избыточность в структурах запроса и преобразуют запросы к потенциально более оптимальным формам [61]. Самая важная рекомендация при использовании любой СУБД – стараться по возможности использовать вместо них соединение [59]. Невозможность автоматической раскрутки подзапросов в соединения является важным недостатком, который следует учитывать. Временами стоимость создания и заполнения временных таблиц, необходимых для обработки подзапросов, слишком высока. Тем более, что временные таблицы, создаваемые в процессе выполнения подзапросов, не имеют индексов. Рассмотрим запрос, возвращающий информацию по начислениям абонента с ФИО Тимошкина Н.Г. Запрос может быть реализован с использованием подзапроса: SELECT * FROM NachislSumma N WHERE N. AccountCD IN (SELECT A.AccountCD FROM Abonent A WHERE A.Fio = 'Тимошкина Н.Г.'); Получить аналогичную информацию можно, используя соединение: SELECT * FROM NachislSumma N INNER JOIN Abonent A ON (a.AccountCD = N.AccountCD and A.Fio = 'Тимошкина Н.Г.'); Однако многие задачи решить с помощью соединений нельзя (например, вывести информацию об оплатах с суммами оплаты, равными минимальной оплаченной сумме). В реальных системах довольно часто получить требуемую информацию можно только путем использования запросов с подзапросами, причем иногда вложенность подзапросов достигает нескольких уровней. 23 В таких случаях нужно помнить, что обработка тех и иных видов подзапросов различается радикальным образом (виды подзапросов и способы их обработки СУБД были подробно рассмотрены при изучении подзапросов). Как правило, долго обрабатываются вложенные запросы в секции FROM, особенно если вложенность многоуровневая и подзапросы между собой связаны соединениями. Среди всех рассмотренных способов оптимизации SQL-запросов оптимизация подзапросов дает самый существенный прирост производительности. В заключение можно отметить, что применение методов оптимизации SQL-запросов – это эффективный способ повышения производительности ИС в целом и дает максимальный выигрыш на больших БД (объемом в несколько десятков Гб). 24