Причины неэффективности SQL-запросов в Oracle. Оптимизация производительности SQL-запросов

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

1. Оптимизация таблиц.

Необходима, когда было произведено много изменений в таблице: либо удалена большая часть данных, либо много изменений со строками переменной длины - text, varchar, blob. Дело в том, что удалённые записи продолжают поддерживаться в индексном файле, и при последующей вставке новых записей используются позиции старых записей. Чтобы дефрагментировать файл с данными, используюется команда OPTIMIZE.

OPTIMIZE TABLE `table1`, `table2`…

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

2. Перестройка данных в таблице.

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

ALTER TABLE `table1` ORDER BY `id`

3. Тип данных.

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

4. NOT NULL и поле по умолчанию.

Лучше всего помечать поля как NOT NULL, так как они немного экономят место и исключают лишние проверки. При этом стоит задавать значение полей по умолчанию и новые данные вставлять только в том случае, если они от него отличаются. Это ускорит добавление данных и снизит время на анализ таблиц. И стоит помнить, что типы полей BLOB и TEXT не могут содержать значения по умолчанию.

5. Постоянное соединение с сервером БД.

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

6. Разделение данных.

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

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

Есть таблица (имя first) с полями id, content, shows. Первое ключевое с auto_increment, второе - текстовое, а третье числовое - считает количество показов. Каждый раз загружая страницу, к последнему полю прибавляется +1. Отделим последнее поле во вторую таблицу. Итак, первая таблица (first) будет с полями id, content, а вторая (second) с полями shows и first_id. Первое поле понятно, второе думаю тоже - отсыл к ключевому полю id из первой таблицы.

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

UPDATE second SET shows=shows+1 WHERE first_id=нужный_ид

А выборка будет происходить усложнённым запросом, но одним, двух не нужно:

SELECT first.id, first.content, second.first_id, second.shows FROM second INNER JOIN first ON (first.id = second.first_id)

Стоит помнить, что это не очень актуально для сайтов с малой посещаемостью и малым количеством информации.

7. Имена полей,

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

8. Требовать меньше данных.

При возможности избегать запросов типа:

SELECT * FROM `table1`

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

SELECT id, name FROM table1 ORDER BY id LIMIT 25

Тут же сделаю добавление о желательности использования LIMIT. Данная команда ограничивает количество строк, возвращаемых запросом. То есть запрос становится "легче"; и производительнее.

Если стоит LIMIT 10, то после получения десяти строк запрос прерывается.

Если в запросе применяется сортировка ORDER BY, то она происходит не по всей таблице, а только по выборке.

Если использовать LIMIT совместно с DISTINCT, то запрос прервётся после того, как будет найдено указанное количество уникальных строк.

Если использовать LIMIT 0, то возвращено будет пустое значение (иногда нужно для определения типа поля или просто проверки работы запроса).

9. Ограничить использование DISTINCT.

Эта команда исключает повторяющиеся строки в результате. Команда требует повышенного времени обработки. Лучше всего комбинировать с LIMIT.

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

SELECT DISTINCT table1.content FROM table1, table2 WHERE table1.content = table2.content

10. Ограничить использование SELECT для постоянно изменяющихся таблиц.

11. Не забывайте про временные таблицы типа HEAP.

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

12. Поиск по шаблону.

Зависит от размера поля и если уменьшить размер с 400 байтов до 300, то время поиска сократиться на 25%

13. Команда LOAD DATA INFILE

позволяет быстро загружать большой объём данных из текстового файла

14. Хранение изображений в БД нежелательно.

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

15. Максимально число запросов при генерации страницы,

как мне думается, должно быть не более 20 (+- 5 запросов). При этом оно не должно зависеть от переменных параметров.

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

Любая относительная оценка при определенных условиях мо­жет оказаться неверной. Одним из таких условий является фор­мирование результата запроса как итога агрегирования. Напри­мер, запрос, возвращающий сумму (одну строку) для таблицы из миллиона строк, вполне законно может требовать выполне­ния более десяти вызовов LIO.

Приложения, выполняющие код SQL, порождающий множество вызо­вов LIO, создает серьезные препятствия для масштабируемости систем с большим количеством пользователей. Лишние вызовы LIO не только отнимают мощность процессора, но и могут вызвать большое количе­ство событий latch free для защелок cache buffers chains . Получение и освобождение защелок само по себе может при­вести к избыточному потреблению мощности процессора, особенно в конфигурациях, где аналитики увеличили значение, заданное для _SPIN_COUNT по умолчанию (обычно не стоит этого делать).

В настоящее время есть ряд полезных ресурсов по оптимизации SQL: .1 Участники разнообразных списков рассылки, например Oracle-L (http://www.cybcon.com/~jkstill), также делают замечательное дело, помогая друг другу писать эффективный код. Все эти ресурсы содер­жат хорошие советы о том, как писать эффективный SQL, применяя методы, часть из которых перечислена ниже:

Диагностика выполнения команд SQL посредством таких инстру­ментов, как tkprof, EXPLAIN PLAN, и отладочных событий 10032, 10033,10046,10079,10104 и 10241.

Диагностика поведения оптимизатора запросов Oracle при помощи отладочного события, подобного 10053.

Работа с текстом SQL, направленная на использование более эффек­тивных планов выполнения.

Выбор эффективной стратегии индексирования с тем, чтобы обес­печить сокращение объема данных для запросов без создания до­полнительной нагрузки в операциях INSERT, UPDATE, MERGE и DELETE.

Применение хранимых планов выполнения с целью заставить оп­тимизатор запросов Oracle использовать выбранный вами план.

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

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

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

Оптимизация разбора

Избыточный разбор - это верный путь к невозможности обеспечить масштабируемость приложения для работы с большим количеством пользователей . Студенты обычно приходят к нам, считая, что полные разборы (hard parses) значительно замедля­ют обработку транзакций, а вот в частичном разборе (soft parse) нет ничего страшного. Более того, иногда люди считают, что полного раз­бора можно избежать, а частичного - нет. Оба мнения верны лишь на­половину. Полные разборы действительно так ужасны, как о них ду­мают, и их можно избежать, используя в коде SQL переменные связы­вания вместо литералов. Однако частичный разбор столь же ужасен, и часто без него тоже можно обойтись.

Многие авторы употребляют словосочетание «частичный разбор» (soft parse) как синоним для «вызова разбора» (parse call). Мне больше нра­вится термин «вызов разбора», т. к. он обращает наше внимание на приложение, внутри которого в действительности может быть пред­принято спасительное действие. Если же говорить о «частичном разбо­ре», то внимание акцентируется на базе данных, которая не является местом решения нашей проблемы. И вот почему. Каждый раз, когда серверный процесс Oracle получает вызов разбора от приложения, это­му процессу необходимо использовать процессор сервера базы данных. Если будет обнаружен подходящий для этого запроса разделяемый курсор в кэше курсоров сеанса или библиотечном кэше Oracle, то вызов разбора никогда не приведет к полному разбору, и окажется, что затра­ты на разбор совсем не так велики, как могли бы быть. Однако отсут­ствие разбора обходится еще дешевле, чем частичный разбор. Наилуч­шую масштабируемость для большого количества пользователей име­ют приложения, в которых разбор происходит как можно реже. Следу­ет по возможности избавиться от всех ненужных вызовов разбора.

Для обеспечения масштабируемости лучше всего, чтобы прило­жения осуществляли минимально возможное количество вызовов базы данных. Именно в этом направлении развивается Oracle Call Interface (OCI). Например, в версии 8 OCI предприняты меры для снижения количества пересылок между клиентом и сервером (http://otn.oracle.com/tech/oci/htdocs/Developing_apps.html). Версия 9.2 OCI идет еще дальше, делая так, что многие вызовы базы данных приложения вообще не достигают базы дан ных (http://otn.oracle.com/tech/oci/htdocs/oci9ir2_new_features).

В системах с высокой конкурентностью и неоправданно многочислен­ными вызовами разбора большое количество событий CPU service зачас­тую коррелирует с большим количеством событий ожидания latch free для библиотечного кэша, разделяемого пула и других защелок. Само по себе получение и освобождение защелок может привести к избыточно­му потреблению мощности процессора, особенно в конфигурациях, где аналитики увеличили значение, заданное для SPINCOUNT по умолчанию (опять-таки, обычно не стоит этого делать). Более того, избыточные вызовы разбора могут привести к ненужным задержкам SQL*Net message from client, способным добавить до нескольких секунд лишнего време­ ни отклика на каждую секунду реальной работы, выполняемой в базе данных. Наконец, вызовы разбора для длинных фрагментов SQL созда­ют ненужные задержки SQL*Net more data from client, которые также мо­гут внести существенный вклад в увеличение времени отклика.

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

Старайтесь обходиться без строковых литералов в инструкциях WHERE. Заменяйте их переменными связывания (заполнителями), особенно когда строковый литерал имеет высокую кардинальность (т. е. строка может иметь множество значений). Использование строковых литералов вместо переменных связывания приводит к затратам процессорного времени (CPU service), а также вызывает в системах с высокой конкурентностью ненужные события ожида­ния освобождения защелок для библиотечного кэша, разделяемого пула и объектов кэша строк.

Пример 11.2. Разбор внутри цикла серьезно препятствует масштабируемости

Отключите те функции интерфейсного драйвера, которые приводят к увеличению количества вызовов разбора по отношению к их чис­лу в исходном коде приложения. Например, Perl DBI содержит ат­рибут уровня prepare под названием ora_check_sql. Его значение по умолчанию равно 1, что означает два вызова разбора для каждого вызова функции Perl prepare. Первый вызов разбора выполняется с тем, чтобы помочь SQL-разработчику приложения быстрее отла­дить свой исходный код за счет предоставления более подробной диагностической информации для неудачных вызовов разбора. Од­ нако в промышленных системах такую функцию следует отключить, т. к. она приводит к излишним вызовам разбора.

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

AND

Очевидно, что в серии из нескольких операторов AND условия должны располагаться в порядке возрастания вероятности истинности данного условия. Это делается для того, чтобы при проверке условий БД не проверяла остальную часть условия. Эти рекомендации не относится к БД Oracle, где условия начинают проверяться с конца. Соответственно, их порядок должен быть обратным - по убыванию вероятности истинности.

OR

Ситуация с данным оператором прямо противоположна ситуации с AND. Условия должны располагаться в порядке убывания вероятности истинности. Фирма Microsoft настойчиво рекомендует использовать данный метод при построении запросов, хотя многие даже не знают об этом или, по крайней мере, не обращают на него внимание. Но опять-таки это не относится к БД Oracle, где условия должны располагаться по возрастанию вероятности истинности.

Еще одним условием для оптимизации можно считать тот факт, что если одинаковые колонки располагаются рядом, запрос выполняется быстрее. Например, запрос ".. WHERE column1 = 1 OR column2 = 3 OR column1 = 2" будет выполняться медленней, чем запрос "WHERE column1 = 1 OR column1 = 2 OR column2 = 3" . Даже если вероятность истинности условия column2 = 3 выше, чем column1 = 2.

Еще в школе мне рассказывали про распределительный закон. Он гласит, что A AND (B OR C) - то же самое, что и (A AND B) OR (A AND C ). Опытным путем было установлено, что запрос вида "...WHERE column1 = 1 AND (column2 = "A" OR column2 = "B")" выполняется несколько быстрее, чем "...WHERE (column1 = 1 AND column2 = "A") OR (column1 = 1 AND column2 = "B")" . Некоторые БД сами умеют оптимизировать запросы такого типа, но лучше перестраховаться.

NOT

Эту операцию всегда следует приводить к более "читабельному" виду (в разумных пределах, конечно). Так, запрос "...WHERE NOT (column1 > 5)" преобразуется в "...WHERE column1 <= 5" . Более сложные условия можно преобразовать используя правило де Моргана, которое ты тоже должен был изучить в школе. Согласно этому правилу NOT(A AND B) = (NOT A) OR (NOT B) и NOT(A OR B) = (NOT A) AND (NOT B) . Например, условие "...WHERE NOT (column1 > 5 OR column2 = 7)" преобразуется в более простую форму: ...WHERE column1 <= 5 AND column2 <> 7 .

IN

Многие наивно полагают, что запрос "... WHERE column1 = 5 OR column1 = 6" равносилен запросу "...WHERE column1 IN (5, 6)" . На самом деле это не так. Операция IN работает гораздо быстрее, чем серия OR. Поэтому всегда следует заменять OR на IN, где это возможно, несмотря на то, что некоторые БД сами производят эту оптимизацию. Там, где используется серия последовательных чисел, IN следует поменять на BETWEEN. Например, "...WHERE column1 IN (1, 3, 4, 5)" оптимизируется к виду: …WHERE column1 BETWEEN 1 AND 5 AND column1 <> 2 . И этот запрос действительно быстрее.

LIKE

Эту операцию следует использовать только при крайней необходимости, потому что лучше и быстрее использовать поиск, основанный на full-text индексах. К сожалению, я вынужден направить тебя за информацией о поиске на просторы всемирной паутины.

CASE

Сама эта функция может использоваться для повышения скорости работы запроса, когда в нем есть более одного вызова медленной функции в условии. Например, чтобы избежать повторного вызова slow_function() в запросе "...WHERE slow_function(column1) = 3 OR slow_function(column1) = 5" , нужно использовать CASE:

... WHERE 1 = CASE slow_function(column1)

WHEN 3 THEN 1

WHEN 5 THEN 1

Морис Льюис

Какую огромную разницу в скорость исполнения запроса может внести индекс! Недавно автор статьи получил еще одно наглядное подтверждение этой истины. Он ввел дополнительный индекс, и время обработки запроса уменьшилось с 40 минут до 12 секунд. Но индексы - всего лишь один из инструментов, применяемых администраторами баз данных для повышения производительности систем. Улучшить производительность можно настройкой самых разных параметров - от конфигурации технических средств до использования утилит баз данных. Ниже приведены 9 самых эффективных средств увеличения производительности SQL Server 6.5.

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

Чем большая часть базы данных сможет уместиться в кэше, тем быстрее будут обрабатываться запросы. Поэтому целесообразно увеличивать размер оперативной памяти пропорционально размеру базы данных. К примеру, если ваша база данных занимает 1 Гб, то оперативная память размером 1 Гб позволит разместить практически всю базу данных в памяти. Некоторую часть оперативной памяти следует оставить для Windows NT. Автор предпочитает оставлять для операционной системы от 64Мб до 128 Мб, а всю оставшуюся часть памяти отводить под SQL Server. И непременно надо сохранять объем доступной физической памяти NT не менее 4 Мб. Если он окажется ниже указанного предела, то NT немедленно начнет создавать страницы виртуальной памяти на диске.

2. Используйте массивы RAID уровня 0 или 5 для распараллеливания получения информации из базы данных.

Массивы RAID уровней 0 и 5 распределяют запросы на чтение по нескольким физическим дискам. Вы, наверняка, знаете, что творится на подступах к мостам в час пик, когда тысячи машин одновременно стремятся проехать через пространство ограниченной ширины. Такое же узкое место возникает и для запросов на чтение файлов с устройств вашей базы данных. Если вам удастся направить данные по нескольким каналам, то сервер сможет параллельно считывать блоки данных с каждого жесткого диска. При этом наблюдается почти линейное улучшение производительности. Такое увеличение пропускной способности для операций чтения обязано своим возникновением массивам RAID уровней 0 и 5. В качестве примера приведем цифры из книги Рона Саукапа "Внутри SQL Server 6.5", вышедшей в издательстве Microsoft Press в 1997 году. Он пишет, что один жесткий диск емкостью 4 Гб в состоянии обработать 80 - 90 операций ввода/вывода в секунду. В то же время массив RAID уровня 0 из 8 жестких дисков по 500 Мб каждый (то есть, обладающий такой же суммарной емкостью) пропускает 400 операций ввода/вывода в секунду. Конечно, при этом вопрос увеличения затрат остается открытым. Но в общем случае, чем больше жестких дисков в массиве, тем больше пропускная способность базы данных для операций чтения.

3. Позвольте функции Max Async I/O воспользоваться всеми преимуществами вашего компьютера.

Возможно, ваша дисковая подсистема в состоянии обрабатывать свыше восьми асинхронных операций ввода/вывода в секунду, то есть больше величины, принятой в качестве значения по умолчанию более трех лет назад при выходе в свет версии SQL Server 6.5. Для оптимизации этого параметра следует увеличивать его небольшими шагами, наблюдая при этом за значением счетчика средней длины очереди к дискам, AvgDiskQueueLength, в мониторе производительности NT (NT Performance Monitor). До тех пор, пока средняя очередь к дисковой подсистеме не превышает удвоенного количества дисков в ней, можно считать, что вы ее не перегружаете.

4. Установите пороги расширения блокировок на всю таблицу.

Три параметра расширения блокировок на всю таблицу (LE - Lock Escalation): Максимальный порог (LE Threshold Maximum), Минимальный порог (LE Threshold Minimum) и Пороговый процент (LE Threshold Percent), определяют, сколько страниц должен заблокировать SQL Server, прежде чем будет заблокирована вся таблица целиком. По умолчанию для этих параметров приняты значения соответственно 200, 20 и 0. Для очень больших таблиц блокировка всей таблицы позволяет избежать накладных расходов, связанных с тысячами блокировок. Если в базе данных содержатся сотни таблиц, то устранение таких накладных расходов может оказать существенное влияние на производительность.

5. Создайте кластеризованные индексы для запросов, которые считывают диапазоны значений.

В силу того, что кластеризованные индексы упорядочивают данные физически, располагая их в порядке следования значений в индексе, такие индексы представляют собой прекрасную основу для запросов, которые ищут диапазоны значений. Например, если у таблицы, содержащей отдельные записи (строчки) счетов, имеется кластеризованный индекс по столбцу с идентификатором счета, то все строчки, относящиеся к счету 0001, будут находиться в самом начале таблицы, а строчки счета 9999 расположатся в самом ее конце. Такой порядок означает, что если эта таблица будет соответствовать части "многие" отношения "один - ко - многим" при соединении с другой таблицей, то SQL Server сможет легко найти первую строку с нужным значением идентификатора счета, а затем последовательно двигаться по таблице до тех пор, пока значение идентификатора не изменится.

6. Сформируйте не кластеризованные индексы для запросов на поиск уникальных значений.

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

7. Создайте составные индексы для поддержания множества запросов.

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

8. Индексируйте соединенные столбцы.

При соединении двух таблиц SQL Server ищет во внутренней таблице все строки, значения которых удовлетворяют условию, вычисляемому на основании текущего значения из внешней таблицы. И такой поиск SQL Server повторяет для каждой строки из внешней таблицы. Если имеется индекс, то SQL Server сможет сначала отобрать только те строки, которые отвечают условию соединения. Когда размер внутренней таблицы в несколько раз больше размера внешней, выигрыш во времени выполнения соединения может составить несколько порядков. (Более подробно о соединении таблиц написано в статье Ицыка Бен-Гана и Кэйлен Дилани "Усовершенствованная техника соединения таблиц" ("Advanced JOIN Techniques"), опубликованной в декабрьском номере журнала за 1999 год.) Какой индекс выбрать - кластеризованный или не кластеризованный - в основном, зависит от того, присутствуют ли в списке SELECT другие столбцы. Если в список входят только те столбцы, по которым производится соединение, лучше всего применять не кластеризованный индекс.

9. Используйте преимущества покрывающих индексов.

По определению покрывающим индексом называется такой индекс, который содержит все столбцы, упомянутые в операторах SELECT, UPDATE или DELETE. Запрос при этом называется покрываемым запросом. Поскольку не кластеризованный индекс содержит на уровне листьев запись для каждой строки в таблице, то вся информация для выполнения запроса находится в индексе. В силу этого процессор запросов может сканировать не огромную таблицу, а только небольшой индекс. В общем случае, если вам удастся построить покрывающий индекс, то вы сразу почувствуете значительное улучшение производительности обработки запросов. Это объясняется тем, что индекс содержит не всю строку таблицы, а только ее подмножество. Однако оборотная сторона медали состоит в том, что введение в индекс дополнительных столбцов приводит к тому, что на странице индекса умещается меньше записей. Это, в свою очередь, вызывает увеличение места, занимаемого индексом, и возрастание числа операций ввода/вывода, необходимых для считывания индекса в кэш. Построение покрывающих индексов оправдано до тех пор, пока суммарная длина всех входящих в индекс столбцов остается значительно меньше длины строки таблицы.

Морис Льюис является президентом компании Holitech, специализирующейся на консалтинге и обучении технологиям Internet и разработкам корпорации Microsoft в области баз данных.

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

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

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

Синтаксический анализ SQL-запросов

Этап синтаксического анализа (parsing) главным образом состоит в выполнении проверки синтаксиса и семантики SQL-операторов. В конце этого этапа создается дерево синтаксического разбора (parse tree), отражающее структуру запроса.

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

Оптимизация SQL запросов

На этапе оптимизации Oracle применяет свой оптимизатор, который называется оптимизатором по стоимости (Cost-Base Optimizer - CBO), для выбора наилучшего метода доступа для извлечения данных из присутствующих в запросе таблиц и индексов. За счет использования предоставляемых статистических данных и любых указываемых в SQL-запросах подсказок, CBO генерирует для SQL-оператора оптимальный план выполнения.

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

Этап перезаписи запроса

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

Этап генерации плана выполнения

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

В общем, при генерации физического плана или плана выполнения запроса оптимизатор принимает во внимание следующие факторы:

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

Оптимизатор может генерировать несколько действительных физических планов запроса, которые являются потенциальными планами выполнения. Затем оптимизатор делает выбор между ними путем оценки стоимости каждого возможного физического плана на основании доступных ему статистических данных по таблицам и индексам и выбора того плана, подсчитанная стоимость которого оказывается наименьшей. Этот процесс оценки стоимости возможных физических планов запроса называется оптимизацией запроса по стоимости (cost-based optimization). Стоимость выполнения плана напрямую зависит от того, сколько ресурсов (ввода-вывода, памяти и ЦП) для него требуется. Потом оптимизатор передает выбранный самый низкий по стоимости физический план запроса механизму выполнения запросов Oracle. В следующем разделе рассматривается простой пример, чтобы можно было лучше разобраться в том, что собой представляет процесс оптимизации процесса по стоимости.

Пример оптимизации запроса по стоимости

Давайте предположим, что требуется выполнить показанный ниже запрос, предусматривающий поиск информации обо всех руководителях (supervisor), которые работают в Далласе (Dallas):

SQL> SELECT * FROM employee e, dept d WHERE e.dept_no = d.dept_no AND(e.job = "SUPERVISOR" AND d.city = "DALLAS"); SQL>

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

Для произведения вычислений по стоимости давайте исходить из следующих предположений:

• считывать и записывать данные можно только по одной строке за раз (в реальности операции ввода-вывода выполняются обычно на уровне блоков, а не на уровне строк);
• база данных записывает каждый промежуточный шаг на диск (опять-таки, в реальном мире такого может и не быть);
• с таблицами не ассоциированы никакие индексы;
• в таблице employee содержится 2000 строк;
• в таблице dept содержится 40 строк и руководителей тоже 40 (по одному на каждое отделение);
• в Далласе всего функционирует десять отделений.

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

Запрос 1: декартово соединение

В случае применения этого запроса сначала получается декартово произведение таблиц employee и dept , а затем проверяться, какие из строк в нем удовлетворяют требованию:

WHERE e.job=supervisor AND d.dept=operations AND e.dept_no=d.dept_no

• декартово произведение таблиц employee и dept потребует считывания обеих таблиц, т.е. 2000 + 40 = 2040 операций чтения;
• создание декартова произведения - 2000 * 40 = 80000 операций записи;
• считывание результата декартова произведения для его сравнения с условием выбора строк - 2000 * 40 = 80000 операций чтения;
• итого общая стоимость ввода-вывода составит: 2040 + 80000 + 80000 = 162040.

Запрос 2: соединение двух таблиц

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

WHERE e.job=supervisor and city=Dallas

Общая стоимость выполнения этого запроса будет выглядеть так:

• соединение таблиц employee и dep сначала потребует считывания всех строк из обеих таблиц, т.е. 2000 + 40 = 2.040 операций чтения;
• создание соединения таблиц employee и dep - 2000 операций записи;
• считывание результатов соединения будет стоить 2000 операций чтения;
• итого общая стоимость ввода-вывода составит: 2040 + 2000 + 2000 = 6040.

Запрос 3: соединение сокращенных связей

Третий запрос тоже подразумевает выполнение соединения таблиц employee и dept , но с соединением не всех, а только выборочных строк из этих двух таблиц. В случае его применения необходимые данные будут извлекаться так, как описано далее. Сначала будет осуществляться считывание таблицы employee для получения всех строк со значением SUPERVISOR . Затем будет выполняться считывание таблицы dept для извлечения всех строк со значением DALLAS . И, наконец, напоследок будет осуществляться соединение тех строк, которые были извлечены из таблиц employee и dept .

Общая стоимость выполнения этого запроса будет выглядеть так:

• считывание таблицы employee для извлечения строк со значением SUPERVISOR будет стоить 2000 операций чтения;
• запись строк со значением SUPERVISOR , которые были извлечены на предыдущем шаге - 40 операций записи;
• считывание таблицы dept для извлечения всех строк со значением DALLAS - 40 операций чтения;
• запись строк со значением DALLAS , извлеченных на предыдущем шаге - 10 операций записи;
• соединение строк со значением SUPERVISOR и со значением DALLAS , извлеченных на предыдущих шагах выполнения данного запроса - всего 40 + 10 = 50 операций записи;
• считывание результата соединения, полученного на предыдущем шаге - 50 операций чтения;
• итого всего стоимость ввода-вывода составит: 2000 + 2 (40) + 10 + 2 (50) = 2190.

Этот пример, каким бы простым он не был, показывает, что декартовы произведения обходятся дороже, чем соединения с более ограничивающими условиями. Даже выборочная операция соединения, как показывают результаты, обходится дороже, чем операция выбора. Хотя операция соединения в запросе 3 и представляет собой соединение двух сокращенных связей, размер соединения выглядит гораздо меньше, чем у соединения в запросе 2. Оптимизация запросов часто подразумевает выполнение ранних операций выборки (выбор только некоторых строк) и проекции (выбор только каких-то столбцов) для сокращения размера результирующего вывода или источников строк.

Эвристические стратегии для обработки запросов

Применение методики оптимизации по стоимости не является единственным способом выполнения оптимизации запросов. Для обработки запросов в базе данных могут также применяться и менее систематичные методики, известные как эвристические стратегии (heuristic strategies). Операция соединения является бинарной, а операция вроде выбора - унарной. Успешная стратегия в целом заключается в выполнении унарной операции на раннем этапе, чтобы в более сложных и длительных по времени бинарных операциях далее использовались меньшие операнды. Выполнение в первую очередь как можно большего количества унарных операций сокращает источники строк в операциях соединения. Ниже перечислены некоторые наиболее типичные эвристические стратегии по обработке запросов.

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

Выполнение запросов

На последнем этапе процесса обработки запросов осуществляется выполнение оптимизированного запроса (физического плана запроса, который был выбран). Если он представляет собой оператор SELECT , тогда производится возврат соответствующих строк пользователю, а если оператор INSERT, UPDATE или DELETE , тогда - внесение в строки соответствующих изменений. Исполняющий механизм SQL берет план выполнения, полученный на этапе оптимизации, и выполняет его.

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