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

Цель лекции

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

и научитесь:

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

Введение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Компонента 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";

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

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

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

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

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

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

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

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

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

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

(A JOIN B) JOIN C

A JOIN (B JOIN C)

(A JOIN C) JOIN B

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

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

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

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

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

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

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

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

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

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

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

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

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

Оптимизатор СУБД семейства MS SQL Server

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

В СУБД семейства MS SQL Server предусмотрены инструменты мониторинга производительности работы сервера, для понимания работы которых необходимо познакомиться с используемой терминологией и принципами мониторинга производительности.

Мониторинг производительности начинается с определения эталонного графика производительности. Эталонный график производительности – это набор определенных показателей производительности, собранных в начале эксплуатации БД.

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

Эти действия являются важными, поскольку оптимизатор СУБД MS SQL Server опирается на собранную статистику при построении планов выполнения запросов.

По собранным данным определяются ресурсы, которые тормозят работу сервера.

В MS SQL Server предусмотрено достаточное количество счетчиков, которые позволяют определить ресурсы и запросы, оказывающие влияние на производительность сервера.

Оптимизатор СУБД MS SQL Server использует два основных показателя: время отклика системы на запросы пользователей и пропускную способность (throughput). Время отклика является субъективным показателем, а пропускная способность — объективным показателем работы сервера, числом транзакций в секунду.

Наиболее универсальным средством мониторинга и анализа производительности MS SQL Server является системный монитор.

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

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

Существует еще несколько инструментов, предназначенных для мониторинга и анализа производительности в СУБД семейства MS SQL Server.

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

Статистики, используемые оптимизатором запросов СУБД MS SQL Server

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

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

В СУБД семейства MS SQL Server имеются следующие возможности работы со статистикой:

Кроме того, SQL Server Management Studio позволяет в графическом интерфейсе просматривать и управлять объектами статистики, которые можно просматривать в проводнике объектов в специальной папке под каждым объектом таблицы.

В MS SQL Server применено множество влияющих на статистику показателей и механизмов, которые позволяют оптимизатор запросов улучшить выбор плана исполнения запроса за счет анализа более широкого диапазона запросов или же предоставить возможность более тонко управлять сбором статистики. Можно выделить следующие показатели и механизмы:

Приведем несколько определений терминов, которые будут использованы в дальнейшем.

Объект statblob: статистический Binary Large Object (BLOB), т.е. большой, бинарный статистический объект. Этот объект хранится во внутреннем представлении каталога sys.sysobjvalues.

Статистика String Summary: резюме строки — это такая форма статистики, которая описывает частоту распределения подстрок в поле записи. Используется для оценки селективности предикатов LIKE. Хранится в statblob для поля записи.

Объект sysindexes: системное представление каталога sys.sysindexes, которое содержит информацию о таблицах и индексах.

Объект Predicate: предикат — это условие, которое оценивается как истина или ложь. Предикаты используются в предложении WHERE или в JOIN запросов к базе данных.

Selectivity: селективность — это доля строк в получаемом предикатом наборе данных, которые удовлетворяют условию этого предиката. Также встречаются более сложные определения селективности, которые необходимы для оценки числа строк, вовлеченных в объединения, DISTINCT и другие операторы. Например, SQL Server 2005 оценивает селективность предиката "Sales.SalesOrderHeader.OrderID = 43659" в базе данных AdventureWorks как 1/31465 = 0.00003178.

Cardinality estimate: оценка числа элементов, позволяет определить объем результирующего набора. Например, если таблица T имеет 100000 строк, а запрос содержит предикат отбора: T.a = 10, и гистограмма показывает селективность T.a = 10 – 10 %, то оценка количества элементов в той доле строк T, которую нужно обработать запросом, будет: 10 % * 100000, и равна 10000 строк.

Объект LOB: большой объект, обычно имеет типы: image, text, ntext, varchar(max), nvarchar(max), varbinary(max).

Статистическая коллекция MS SQL Server

Оптимизатор СУБД семейства MS SQL Server собирает следующую коллекцию статистической информации уровня таблиц, которая является частью объекта статистики, и использует ее и для оценки стоимости запроса:

Оптимизатор СУБД семейства MS SQL Server собирает следующую статистику по столбцам таблицы и сохраняет ее в объекте статистики (statblob):

Гистограмма — это набор значений данного поля, ограниченный до 200 значений. Все значения поля, или выборка из них, отсортированы, и эта упорядоченная последовательность может быть разделена не более чем на 199 интервалов так, чтобы фиксировалась наиболее статистически важная информация. Как правило, эти интервалы имеют разные размеры. Ниже представлены значения или информация, достаточная для получения такой информации и сохраняемая для каждого шага в гистограмме.

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

Если гистограмма была сформирована с использованием выборки, то значения RANGE_ROWS, EQ_ROWS, DISTINCT_RANGE_ROWS и AVG_RANGE_ROWS будут иметь оценки и поэтому не будут целыми числами.

Плотность — это информация о числе дубликатов в анализируемом столбце или комбинации столбцов, и она вычисляется так: 1 / (число различающихся значений). Когда столбец используется в предикате равенства, тогда число квалифицированных строк будет оценено с применением значения плотности, полученного из гистограммы. Гистограммы также нужны для оценки селективности предикатов в выборках с неравенствами, объединениями и другими операторами.

В дополнение к timestamp (показывающему время, когда были собраны статистические данные), числу строк в таблице, числу отобранных для создания гистограммы строк, плотности, информационной и средней длине ключа и непосредственно к самой гистограмме, статистическая информация по одному столбцу включает еще значение All density, формируемое для каждого набора столбцов и определяющее префикс набора статистики столбца. Это значение можно увидеть во втором блоке строк, выводимом командой DBCC SHOW_STATISTICS. All density представляет из себя оценку: 1 / (число различающихся значений в префиксном наборе столбца). В следующем абзаце поясняется смысл этого значения.

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

sp_helpindex и sp_helpstats показывают списки статистик, доступные для анализируемой таблицы. sp_helpindex показывает все индексы таблицы, а sp_helpstats — список всех статистик по таблице. Каждый индекс также имеет статистическую информацию для ее столбцов. Создаваемая с использованием команды CREATE STATISTICS статистическая информация эквивалентна статистике, сформированной командой CREATE INDEX, если индекс создается на тех же столбцах. Единственная разница — при использовании команды CREATE STATISTICS будет задействована используемая по умолчанию выборка, в то время как для команды CREATE INDEX сбор статистики будет сопровождаться полным сканированием таблицы, так как в любом случае для построения индекса будут обработаны все строки таблицы.

Анализ запросов с целью повышения скорости их выполнения

Рассмотрим теперь общую процедуру настройки команды SELECT, результат выполнения которой не удовлетворяет требованиям производительности. Эта процедура является итерацией на пути построения оптимального набора индексов и состоит из семи шагов. При обсуждении этой процедуры мы будем ориентироваться на СУБД семейства MS SQL Server.

Шаг 1. Обновить статистику. До того, как добавить индексы, необходимо убедиться, что статистика БД в системном каталоге корректна. Если вы выполняете запрос без учета действительной производительности БД, вам следовало бы обновить статистику для всех таблиц, указанных в предложении FROM, используя команду UPDATE STSTISTICS или другую специальную команду СУБД. С другой стороны, если вы используете небольшую тестовую БД, то можно вручную вычислить необходимые статистические показатели и внести их в системный каталог.

Когда вы обновляете статистику, вам следовало бы скомпилировать команду SQL. Сравните новый план запроса со старым до обновления статистики, чтобы определить изменения в нем (иногда требуется довольно длительное время для построения плана.) Сравнивая планы, можно избежать повторного выполнения запроса только для того, чтобы убедиться, что производительность его выполнения идентична предыдущему выполнению этого запроса. Если статистика изменилась – выполните запрос, чтобы определить, увеличилась ли производительность и насколько.

Шаг 2. Упростить команду SELECT. Перед добавлением индексов или переписыванием плана выполнения следует попытаться упростить запрос. Задача состоит в том, чтобы сделать выражение SELECT как можно проще, сократив по мере возможностей число переменных в нем. Упростив запрос, скомпилируйте команду, чтобы посмотреть план запроса. Сравните новый план запроса со старым. Определите, увеличилась ли производительность запроса, выполнив его.

Для того чтобы упростить SELECT, необходимо:

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

Примерами предложений, которые обычно включаются в запрос, но не являются необходимыми, являются:

WHERE DEPT_NO = 10 AND DEPT_NAME = 'PERATIONS'.

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

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

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

При использовании связанных переменных в предикате селекции оптимизатор запросов вынужден использовать умалчиваемый фактор селективности 1/3.

Рассмотрим следующий пример.

Пример 24.2. Предикаты: Amount > :BindVar и Amount > 1000. Преимущество применения первого предиката состоит в том, что команда может быть откомпилирована один раз и затем много раз использована с различными значениями. Недостаток состоит в том, что оптимизатор запросов имеет меньше информации для его оценки во время компиляции. Он не знает, будет ли 10 или 10000000 стоять вместо связанной переменной. Следовательно, он не способен вычислить корректно фактор селективности. В этой ситуации будет установлено значение по умолчанию, которое не обязательно будет отражать истинную ситуацию в данных. Поскольку значение фактора селективности, равное 1/3, относительно высокое (при отсутствии других предикатов), оптимизатор не может использовать какой-либо индекс, связанный с колонкой в этом предикате.

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

Связанные переменные также приводят к проблеме при использовании предиката оператора LIKE. Этот оператор может включать символ подстановки.

Пример 24.3. Рассмотрим запрос на поиск всех продавцов, чье имя начинается с буквы "А":

SELECT * FROM CUSTOMER WHERE NAME LIKE 'A%';

Символ подстановки может стоять и в начале, и в середине, и в конце строки шаблона. Природа индексной структуры на основе В-дерева такова, что она может работать с символом подстановки, если он не стоит в начальной позиции строки. Ясно, что оптимизатор не будет задействовать индекс, если символ постановки будет стоять на первой позиции, так же, как при использовании связанной переменной в предикате LIKE. В этих случаях будет применено сканирование таблицы.

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

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

Шаг 4. Локализовать узкие места. Запрос, который выполняется медленно, может содержать много предложений и предикатов. Если это так – нужно определить, какие предложения или предикаты приводят к плохой производительности. Если удалить одно или два предложения или предиката, производительность выполнения запроса возрастает значительно. Эти предложения являются критическими параметрами выполнения запроса.

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

Примеры.

Шаг 5. Создать индексы для одной колонки для критических параметров. Если завершены шаги 1-4 и запрос все еще не удовлетворяет требованиям производительности, можно попытаться создать индексы специально для этого запроса, чтобы увеличить его производительность. В общем, индексы для одной колонки предпочтительнее, чем составные индексы, так как более вероятно их использование в других запросах. Попытайтесь увеличить производительность запроса с индексом для одной колонки, прежде чем разрабатывать многоколоночные индексы.

Локализуйте следующие колонки таблицы из запроса, которые не имеют индекса.

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

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

Шаг 6. Создать индексы для нескольких колонок. Процедура идентификации колонок для создания составных индексов состоит в следующем.

Типы составных индексов приведены ниже.

Шаг 7. Удалить все индексы, которые не используются в плане запроса. Как уже указывалось выше, индексы замедляют выполнение команд DML, а их сопровождение требует времени и увеличивает стоимость обработки. Следовательно, вам следует проследить за использованием всех созданных индексов и удалить те, которые не используются запросом.

Оптимизация запросов для схем типа "звезда"

В этом разделе мы рассмотрим некоторые особенности, которые связаны с оптимизацией запросов к схемам типа "звезда", характерным для ХД. Изложение будет основываться на возможностях СУБД семейства MS SQL Server.

Основной принцип построения плана

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

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

Запросы к схеме типа "звезда" можно разделить на три группы, как показано на рис 24.1.

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

В первый класс включены высокоизбирательные запросы, обрабатывающие до 15% строк таблицы фактов. Второй класс, со средней избирательностью, содержит запросы, обрабатывающие от 15 до 75% строк таблицы фактов. Запросы в третьем классе, с низкой избирательностью, требуют обработки более 75% строк таблицы фактов. Прямоугольники на рисунке показывают основные планы выполнения запросов для каждого класса избирательности.

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

Так как высокоизбирательные запросы к схеме типа "звезда" обычно получают не более 10-15% строк таблицы фактов, им можно позволить случайный доступ к таблице. Поэтому планы запросов для этого класса основаны на соединениях вложенных циклов вместе с поисками индексов (некластеризованных) и поиском закладок в таблице фактов. Так как они выполняют произвольный ввод/вывод в таблицу фактов, последовательный ввод/вывод, при получении больших количеств таблицы фактов, получается более производительным. Поэтому по мере роста количества строк, получаемых из таблицы фактов, выше определенного предела, применяется другой план запроса.

Поскольку запросы к схеме типа "звезда" средней избирательности обрабатывают значительную часть строк таблицы фактов, для доступа в таблицу фактов обычно применяются хэш-соединения со сканированием таблицы фактов или сканирование диапазона таблицы фактов. MS SQL Server использует растровые фильтры для улучшения производительности хэш-соединений.

Реализация соединения для каждого соединения – хэш-соединение, которое позволяет MS SQL Server взять информацию о соответствующих строках из таблиц измерений и получить информацию об уменьшении кардинальности соединения для обеих таблиц измерений. Физический оператор доступа к данным (например, сканирование таблицы) использует эту информацию о строках таблицы измерения, чтобы исключить строки таблицы фактов, которые не соответствуют условиям соединения против измерений.

Оптимизатор убирает эти строки таблицы фактов как можно раньше в процессе обработки запроса. Это позволяет экономить время работы ЦП и, возможно, сократить ввод/вывод с диска, потому что убранные строки не нужно обрабатывать в дальнейших операторах плана запроса.

Конвейер оптимизации запросов к схеме типа "звезда"

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

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

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

Эвристика соединения схемы типа "звезда"

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

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

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

Параллелизм секционированных таблиц

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

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

В MS SQL Server 2008 включена новая функция — параллелизм секционированных таблиц (PTP), которая улучшает производительность запросов в случае секционирования, лучшим образом используя вычислительные мощности имеющегося оборудования, независимо от того, сколько секций затрагивает запрос или каков относительный размер отдельных секций. В типичном случае ХД с секционированной таблицей фактов пользователи смогут заметить значительное улучшение запросов, выполняющихся параллельно, особенно если количество доступных ядер процессора больше числа секций, затрагиваемых запросом. И эта новая функция работает сразу, без дополнительной настройки или изменений.

Сжатие данных

По мере того, как бизнес-аналитика становится популярной, предприятия добавляют все больше данных для анализа в свои ХД. Результат – экспоненциальный рост объема управляемых данных. Размер ХД утраивается каждые два года. Это ставит новые вопросы об управлении такими большими объемами данных и обеспечении приемлемой скорости выполнения запросов к ХД. Такие запросы обычно сложные, включающие много соединений и агрегатов, им нужен доступ к большим объемам данных. И многие запросы в рабочей нагрузке зависят от ввода-вывода.

Проблему помогает решить собственное сжатие данных. В MS SQL Server 2005 SP2 был включен новый формат хранения переменной длины под названием vardecimal для десятичных и числовых данных. Этот новый формат хранения может значительно уменьшить размер баз данных. Выигрыш в объеме может, в свою очередь, двумя способами улучшить производительность запросов, зависимых от ввода-вывода. Во-первых, нужно считывать меньше страниц, а во-вторых, так как данные хранятся в буферном пуле сжатыми, увеличивается ожидаемое время жизни страницы (иначе говоря, увеличивается вероятность, что нужная страница окажется в буфере). Конечно, выгода в объеме от сжатия данных приводит к нагрузке на ЦП в процессе сжатия и распаковки данных.

SQL Server 2008 использует формат хранения vardecimal, обеспечивая два вида сжатия: сжатие ROW и PAGE. Сжатие ROW расширяет формат хранения vardecimal за счет хранения всех типов данных фиксированной длины в формате хранения переменной длины.

Некоторые примеры типов данных фиксированной длины — integer, char и свободные типы данных. Несмотря на то, что MS SQL Server хранит эти типы данных в формате переменной длины, их семантика не меняется (с точки зрения приложения, тип данных продолжает быть фиксированной длины). Это значит, что можно воспользоваться преимуществами сжатия данных, не изменяя свои приложения.

Сжатие PAGE уменьшает избыточность данных в столбцах в одной или более строке на данной странице. Оно использует собственную реализацию алгоритма LZ78 (Лемпеля-Зива), сохраняя избыточные данные единожды на странице, ссылаясь затем на них из многих столбцов. Заметьте, что если вы применяете сжатие PAGE, сжатие ROW тоже используется.

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

Индексированные представления, выровненные по секциям

В SQL Server 2008 индексированные представления, выровненные по секциям, позволяют более эффективно создавать и управлять общими агрегатами в реляционном ХД, а также использовать их в тех сценариях, в которых раньше это было неэффективно. Это повышает производительность запроса. В типичном случае имеется таблица фактов, секционированная по дате. Индексированные представления (или общие агрегаты) определяются на этой таблице, чтобы ускорить запросы. При переключении на новую секцию таблицы соответствующие, определенные на секционированной таблице секции индексированных представлений, выровненных по секциям, тоже переключаются, причем автоматически.

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

Укрупнение блокировок на уровне секции

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

Важным моментом является схема доступа к данным. Запросы и операции DML могут нуждаться в доступе или манипуляциях только с частью секций. Поэтому если вы, например, анализируете данные продаж за 2008 год, вам требуется доступ только к соответствующим секциям; в идеальном варианте запросы, обращающиеся к данным в других секциях, не должны оказывать на вас никакого влияния (не считая ресурсов системы). В MS SQL Server 2005 одновременный доступ к различным секциям иногда приводит к блокировке таблицы, которая может затруднить доступ к другим секциям.

Чтобы снять такие ограничения, в SQL Server 2008 включен на уровне таблиц параметр, позволяющий контролировать укрупнение блокировок на уровне секции или таблицы. Политику укрупнения блокировок для таблицы можно изменить. Например, можно установить такое укрупнение блокировок:

ALTER TABLE <mytable> set (LOCK_ESCALATION = AUTO)

Эта команда предписывает SQL Server выбрать подходящую для схемы таблицы гранулярность укрупнения блокировок. Если таблица не секционирована, укрупнение блокировок будет на уровне TABLE (таблицы). Если она секционирована, то гранулярность укрупнения блокировок будет на уровне секции. Этот параметр также используется в SQL Server как способ уменьшить вероятность гранулярности блокировки на уровне таблицы.

Резюме

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

Была рассмотрена важная для проектировщика ХД общая процедура настройки команды SELECT, результат выполнения которой не удовлетворяет требованиям производительности. Эта процедура является итерацией на пути построения оптимального набора индексов и состоит из семи шагов.