Настройка производительности запросов к хранилищу данных

Цель лекции

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

• что такое оптимизации обработки запросов в реляционных СУБД;
• какие существуют методы и подходы к оптимизации запросов ;
• какие бывают основные типы оптимизаторов;
• как оптимизатор запросов выполняет анализ плана выполнения ;
• какая информация используется оптимизатором запросов для вычисления стоимости;
• как оптимизатор запросов формирует возможные планы выполнения при обработке команд SQL;
• как настроить команду SELECT ;

и научитесь:

• использовать оптимизатор для увеличения производительности команды SELECT ;
• понимать синтаксис операторов SQL с точки зрения их физической реализации.

Литература: [67].

Введение

Языки обработки данных и задача оптимизации обработки данных

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

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

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

Процедурные языки обработки данных

Большинство систем БД до начала применения SQL-технологии основывались на процедурных, или навигационных, языках обработки данных. Примерами таких систем БД могут служить ADABAS (Software Ag.), IDMS, IMS (IBM Corp.) и dBase. Процедурные языки обработки данных требуют от программиста кодирования программной логики, необходимой для навигации по физической структуре данных, для идентификации и доступа к требуемым данным. Например, при использовании ADABAS программист должен написать код для спецификации записей данных (FIND), получить специфицированное множество данных и организовать цикл его просмотра (GET), а также предоставить код для актуализации полученных данных для пользователя.

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

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

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

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

Декларативные языки обработки данных

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

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

• Отражение требований к изменению в структурах данных незначительно влияет на существующие прикладные программы. Например, если существующий индекс становится устаревшим, то его можно свободно удалить и создать новый индекс (в том числе и на других атрибутах) без влияния на существующие программы. Созданный новый индекс может либо улучшать производительность программы, либо ухудшать ее. Однако можно быть уверенным в том, что существующие программы будут выполняться без ошибок. Предполагается, что команда SQL будет подготовлена до выполнения, хотя некоторые реляционные СУБД, такие как СУБД семейства MS SQL Server, могут автоматически перекомпилировать сохраняемый план доступа команды SQL (SQL access plan). В процедурных языках обработки данных не всегда является очевидным, что привело к аварийному завершению программы — изменения в физической структуре или ее несоответствие программной логике.
• Уменьшается сложность прикладной программы. СУБД, а не программист определяет, как осуществлять навигацию по физической структуре данных. Такое решение освобождает программиста для решения других задач, так как этот аспект программирования является часто наиболее сложным аспектом программной логики.
• Снижается число ошибок в прикладных программах. Сложность доступа к данным часто приводит к программным ошибкам, если программист не обладает высокой квалификацией или не очень тщательно кодирует. Главное преимущество компьютера состоит в способность выполнять простые инструкции с высокой скоростью и без ошибок. Следовательно, СУБД в целом заменяют программиста, когда определяют, как осуществлять навигацию по физической структуре данных для доступа к требуемым данным.

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

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

Навигационная логика (вариант алгоритма) для доступа к требуемым данным называется путем или методом доступа (access path).

Последовательность выполняемых оптимизатором действий, которые обеспечивают выбранные пути доступа, называется планом выполнения (execution plan).

Процесс, используемый оптимизатор запросов для определения пути доступа, называется оптимизацией запросов (query optimization).

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

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

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

Синтаксическая оптимизация

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

Пример 24.1. Рассмотрим следующий запрос, который делает выборку данных из таблиц PRODUCT (ПРОДУКЦИЯ) и VENDOR (ПРОИЗВОДИТЕЛЬ):

SELECT VENDOR_CODE, PRODUCT_CODE, PRODUCT_DESC
FROM VENDOR, PRODUCT
WHERE VENDOR.VENDOR_CODE = PRODUCT.VENDOR_CODE AND VENDOR.VENDOR_CODE = "100";

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

1. Формируем декартово произведение таблиц PRODUCT и VENDOR.
2. Ограничиваемся в результирующей таблице строками, которые удовлетворяют условию поиска в предложении WHERE.
3. Выполняем проекцию результирующей таблицы на список колонок, указанный в предложении SELECT.

Оценим стоимость процесса обработки этого запроса в терминах операций ввода-вывода. Пусть для определенности таблица VENDOR содержит 50 строк, а таблица PRODUCT — 1000 строк. Тогда формирование декартова произведения потребует 50050 операций чтения и операций записи (в результирующую таблицу). Для ограничения результирующей таблицы потребуется более 50000 операций чтения и, если 20 строк удовлетворяют условиям поиска, то 20 операций записи. Выполнение операции проекции вызовет еще 20 операций чтения и 20 операций записи. Таким образом, обработка этого запроса обойдется системе в 100090 операций чтения и записи.

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

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

1. Ограничение по условию поиска во второй таблице (VENDOR_CODE = "100") приведет к 1000 операций чтения и 20-ти операциям записи.
2. Выполнение соединения полученной на 1 шаге результирующей таблицы с таблицей VENDOR потребует 20 операций чтения результирующей таблицы, 100 операций чтения из таблицы VENDOR и 20 операций записи в новую результирующую таблицу

Обработка запроса в этом случае потребует 1120 операций чтения и 40 операций записи для получения того же самого результата, что и в первом случае. Преобразование, описанное в данном примере, называется синтаксической оптимизацией (syntax optimization).

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

Оптимизация, основанная на правилах

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

Кроме этого, появилось много новых алгоритмов для выполнения соединения таблиц. Двумя наиболее основными алгоритмами выполнения соединения являются:

• соединение с помощью вложенного цикла (Nested Loop Join). В этом алгоритме строка читается из первой таблицы, называемой внешней (outer) таблицей, и затем читается каждая строка второй таблицы, называемой внутренней (inner), как кандидат для соединения. Затем читается вторая строка первой таблицы и снова каждая строка из второй, и так до тех пор, пока все строки первой таблицы не будут прочитаны. Если в первой таблице находится M строк, а во второй — N, то читается M x N строк;
• соединение посредством объединения (Merge Join). Этот метод выполнения соединения предполагает, что таблицы отсортированы (или проиндексированы) таким образом, что строки читаются в порядке значений колонки (колонок), по которым они соединяются. Это позволяет выполнять соединение посредством чтения строк из каждой таблицы и сравнивания значений колонок соединения до тех пор, пока соответствие этих значений существует. В этом способе соединение завершается за один проход по каждой таблице.

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

(A JOIN B) JOIN C

A JOIN (B JOIN C)

(A JOIN C) JOIN B

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

Одним из первых подходов на пути борьбы с комбинаторной сложностью выполнения соединений состоит в установлении эвристических правил для выбора между путями доступа и методами соединений, который называется оптимизацией, основанной на правилах (rule-based optimization). В этом подходе веса и предпочтения назначаются альтернативам на основе принципов, которые являются общепризнанными. Используя эти веса и предпочтения, оптимизатор запросов производит возможные планы выполнения до тех пор, пока не будет достигнут лучший план выполнения, удовлетворяющий этим правилам. Некоторые из этих правил, используемых оптимизаторами такого типа, основываются на размещении переменных служебных символов (variable tokens), таких как имена таблиц и колонок в синтаксических структурах запроса. Когда эти имена размещаются, значительная разница в производительности выполнения запроса иногда может иметь место. По этой причине оптимизаторы, основанные на правилах, как говорят, являются синтаксически зависимыми, и один из методов настройки оптимизаторов этого типа СУБД включает размещение символов (tokens) в различных позициях внутри утверждения.

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

• число строк в таблице;
• интервал и распределение значений данной колонки;
• длина строки и, соответственно, число строк на физической странице диска;
• высота индекса;
• число терминальных (leaf) страниц в индексе.

Эти характеристики данных могут сильно влиять на эффективность обработки запроса. Использование таких характеристик приводит к следующему типу оптимизации.

Оптимизация, основанная на вычислении стоимости

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

Для понимания того, как статистика может быть использована для выбора плана выполнения запроса, рассмотрим следующий запрос к таблице CUSTOMER (ПОКУПАТЕЛЬ):

SELECT CUST_NBR, CUST_NAME
FROM CUSTOMER
WHERE STATE = "FL";

Если существует индекс на колонку STATE, оптимизатор, основанный на правилах, использовал бы его для обработки запроса. Однако если девяносто процентов строк в таблице CUSTOMER имеют FL в колонке STATE, то использование индекса будет в действительности приводить к более медленному выполнению запроса, чем простая последовательная обработка таблицы. Оптимизатор, основанный на вычислении стоимости, с другой стороны, обнаружил бы, что использование индекса не дает никаких преимуществ перед последовательным просмотром таблицы.

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

Последовательность шагов оптимизации запросов

Несмотря на то, что оптимизаторы запросов современных реляционных СУБД различаются по сложности и принципам создания, все они следуют одним и тем же основным этапам в выполнении оптимизации запроса.

• Синтаксический разбор запроса (parsing). Оптимизатор сначала разбивает запрос на его синтаксические компоненты, проверяет ошибки в синтаксисе и затем преобразует запрос в его внутреннее представление для дальнейшей обработки.
• Преобразование (conversion). Далее оптимизатор применяет правила преобразования запроса и транслирует его в формат, оптимальный с точки зрения синтаксиса.
• Построение альтернатив (Develop alternatives). Когда запрос проходит синтаксическую оптимизацию, оптимизатор разрабатывает альтернативы для его выполнения.
• Создание плана выполнения запроса (Сreate execution plan). Окончательно оптимизатор выбирает лучший план выполнения запроса либо следуя набору эвристических правил, либо вычисляя стоимость для каждой альтернативы выполнения.

Так как шаги 1 и 2 производятся независимо от действительных данных, находящихся в таблицах, нет необходимости повторять их, если запрос не требует перекомпиляции. Следовательно, большинство оптимизаторов будет сохранять результаты 2-го шага и использовать его снова, когда они переоптимизируют запрос в другой раз.