Вопросы оптимизации. Транзакции и блокировки

Некоторые замечания по оптимизации выполнения предложений SQL

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

Реляционная модель избыточна в том смысле, что допускает наличие разных выражений над отношениями, дающих одинаковый результат оптимизацию выполнения предложений SQL напрямую не затрагивает, полагая выбор той или иной формулировки запроса делом удобства программиста и передавая задачу построения оптимального плана обработки исключительно в компетенцию СУБД. Тем не менее в порядке помощи разработчикам в рамках реляционной теории были предложены способы ускорить вычисления ответов на запросы. Этому служит техника равносильных преобразований. Однако подобная техника не в полной мере применима к SQL ввиду отличий модели данных, подразумеваемой этим языком, от модели отношений (реляционной).

SQL предполагает возможное влияние формулировки запроса на эффективность вычислений. Кроме этого, он явно вводит специальные структуры в БД для ускорения вычислений: это индексы и овеществленные представления данных (materialized views). Многие советы по формулировкам запросов в SQL ставят своей целью побудить СУБД использовать при доступе к данным индекс.

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

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

Сокращение вычислений при локализации объектов доступа

Полное указание имен объектов в запросе пусть незначительно, но сокращает время разбора. Например, есть два запроса:

SELECT emp.ename FROM scott.emp;
SELECT ename FROM emp;

Второй имеет предпосылки обрабатываться дольше, так как при разборе запроса требует дополнительной работы по уточнению принадлежности таблицы схеме и столбца таблице. Заметьте к тому же, что, строго говоря, эти предложения не равносильны: при разборе второго может оказаться, что таблица EMP не принадлежит пользователю SCOTT (если запрос выдавался другим пользователем, имеющим одноименную таблицу, или если переменная сеанса CURRENT_SCHEMA имела значение, отличное от SCOTT).

Отказ от повторных вычислений выражений

В некоторых случаях формулировка запроса позволяет отказаться от повторного вычисления выражений. Примерами могут служить логические выражения BETWEEN и IN. Например, формулировка условного выражения

( sal + comm ) BETWEEN 1000 AND 2000

способна обрабатываться эффективнее равносильной формулировки

( sal + comm >= 1000 ) AND ( sal + comm <= 2000 )

Ответ на вопрос, будет ли первая формулировка действительно обрабатываться быстрее, зависит от сложности выражения. В данном случае подвыражение ( SAL + COMM ) достаточно просто, чтобы при построении плана, на этапе анализа формулировки, оптимизатор заметил, что во втором случае подвыражение повторяется, и фактически вычислял бы его однократно. Однако если бы вместо этого подвыражения стояла более сложная конструкция или если бы подвыражение содержало бы обращение к функции пользователя (все аспекты вычисления которой оптимизатору неизвестны), формулировку условного выражения через BETWEEN следовало бы признать более выгодной с вычислительной точки зрения. (Другие выгоды от BETWEEN — в надежности кода, из-за однократности записи выражения в запросе и в возможности задействовать в плане вычисления индекс, когда таковой имеется).

Аналогичные рассуждения обосновывают выгоду от использования для построения условных выражений операторов IN или = ANY перед употреблением цепочек сравнения, составленных с помощью OR, и выгоду от ссылки на имя столбца, данное во фразе SELECT, перед воспроизведением выражения в другой фразе. Так, возвращаясь к одному из примеров выше, предпочтение следует отдать формулировке

SELECT job, AVG ( sal ) avgsal FROM emp 
GROUP  BY job
ORDER  BY avgsal
;

перед формулировкой

SELECT job, AVG ( sal ) FROM emp 
GROUP  BY job
ORDER  BY AVG ( sal )
;

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

Оптимизация вычисления составного логического выражения

Логические выражения, составленные цепочками с помощью связок OR или AND, при отсутствии у Oracle информации о сложности вычисления подвыражений вычисляются во вполне определенном направлении. Это обстоятельство можно использовать для размещения элементов цепочек, наиболее вероятно дающих TRUE или же FALSE в соответствующих концах цепочки.

При просмотре сотрудников выражение

( sal > 1000 ) OR ( mgr IS NULL )

будет вычисляться скорее, чем

( mgr IS NULL ) OR ( sal > 1000 ) 

Это объясняется тем, что цепочка, построенная с помощью связок OR, вычисляется слева направо, а сотрудников с зарплатой более 1000 — больше, чем "президентов". Мизерная в данном случае разница может оказаться заметной на вычислительно более сложных выражениях.

Переформулировка для сокращения объема обрабатываемых строк

Перестановка фильтров строк способна сократить объем обработки. Так, предложение SELECT с фразой HAVING иногда можно переформулировать в содержательно равносильное, но вычислительно более эффективное за счет перенесения отбора строк из фразы HAVING во фразу WHERE. Вот пример запроса на количество разных сотрудников по специальностям, помимо клерков:

SELECT   job, COUNT ( * ) 
FROM     emp
GROUP BY job 
HAVING   job <> 'CLERK'
;

Из сказанного ранее следует, что логическая последовательность действий по вычислению результата на этот запрос будет следующей:

1. отбор строк по условию WHERE;
2. разбиение строк на группы по условию GROUP BY;
3. вычисление сверток (агрегатов) по каждой группе;
4. отбор требуемых групп по условию HAVING.

Памятуя логический порядок вычислений, объем вычислений затратной группировки можно сократить, переписав запрос в виде

SELECT   job, COUNT ( * ) 
FROM     emp
WHERE    job <> 'CLERK'
GROUP BY job
;

Некоторые специалисты вовсе дают рекомендацию избегать отсева групп фразой HAVING.

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

SELECT e.ename, d.dname 
FROM         emp e
INNER JOIN   dept d
USING      ( deptno )
WHERE e.job <> 'SALESMAN'
;
SELECT e.ename, d.dname 
FROM
   ( SELECT * FROM emp WHERE job <> 'SALESMAN' ) e
INNER JOIN
   dept d
USING
   ( deptno )
;

В то же время не следует недооценивать оптимизатор Oracle: для двух последних запросов (пусть и не самых сложных) он дает одинаковый план исполнения, так что выбор одного из них становится всего делом предпочтения программиста.

Возможность использовать индекс для доступа к строкам таблицы

Хотя доступ к строкам таблицы по индексу не гарантирует высокую скорость (а иногда приводит даже к замедлению), часто он оказывается оправдан. Но решению СУБД употребить существующий индекс может, помимо прочего, препятствовать формулировка условного выражения. Например, в случае указания поля, соответствующего индексированному обычным индексом (древовидным и без функционального преобразования ключа) столбцу, в качестве параметра для функции СУБД откажется от использования индекса:

SELECT empno FROM emp WHERE TRUNC ( empno ) = 7369;

СУБД может воспользоваться индексом (если сочтет целесообразным), только если индексированное поле присутствует в одной из частей сравнения без каких-либо преобразований:

SELECT empno FROM emp WHERE empno >= 7369;

но не в этом случае:

SELECT empno FROM emp WHERE empno + 0 >= 7369;

Oracle не будет также отказываться от использования индекса в сравнениях с помощью других операторов:

empno BETWEEN 7000 AND 8000
empno IN ( 7369, 7865, 8888 )
empno = ANY ( 7369, 7865, 8888 )
empno = ALL ( 7369, 7865, 8888 )

В сравнении оператором LIKE СУБД сможет привлекать индекс только если в проверочной маске первый слева символ не является специальным:

job LIKE 'SAL%'

но не:

job LIKE '_SAL%'

Транзакции и блокировки

Транзакции и блокировки есть механизм регулирования доступа к БД из приложений.

Транзакция в SQL есть логическая последовательность операций DML по внесению изменений в БД, принимаемая или же отвергаемая СУБД в конечном итоге ("по завершению транзакции") в целом. В английском языке слово transaction обозначает единицу общения двух агентов (в нашем случае — программы и СУБД), завершающуюся оказанием взаимного воздействия друг на друга.

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

• восстанавливать данные при аварийном прекращении сеанса связи программы с СУБД ("единица восстановления");
• гарантировать целостное представление данных программе при одновременной работе с данными нескольких программ;
• гарантировать целостное хранение данных в БД при одновременной работе с ними нескольких программ.

Широко известны общие требования к механизму транзакций ("свойства ACID"):

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

Некоторые эксперты полагают, что требование согласованности для транзакций в БД, то есть соблюдение ограничений целостности, должно обеспечиваться на уровне не транзакции (как то допускают и стандарт SQL, и Oracle), а отдельного оператора DML. Получается, что выполнение этого требования механизмом тразнакций свидетельствует о недостаточности языка SQL для моделирования событий в предметной области. К сожалению это не единственная возможная претензия к языку.

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