Автор оригинала: Vlad Mihalcea.
Вступление
В этой статье мы рассмотрим, как реляционная база данных выполняет инструкции SQL и подготовленные инструкции.
Жизненный цикл оператора SQL
Основными модулями базы данных, ответственными за обработку инструкции SQL, являются:
- Синтаксический анализатор ,
- Оптимизатор ,
- Исполнитель .
Выполнение инструкции SQL выглядит так, как показано на следующей диаграмме.
Синтаксический анализатор
Анализатор проверяет инструкцию SQL и гарантирует ее правильность. Операторы проверяются как синтаксически (ключевые слова оператора должны быть правильно написаны и соответствовать рекомендациям по языку SQL), так и семантически (таблицы и столбцы, на которые имеются ссылки, существуют в базе данных).
Во время синтаксического анализа оператор SQL преобразуется во внутреннее представление базы данных, называемое синтаксическим деревом (также известное как дерево синтаксического анализа или дерево запросов ).
Если оператор SQL является представлением высокого уровня (более значимым с точки зрения человека), синтаксическое дерево представляет собой логическое представление объектов базы данных, необходимых для выполнения текущего оператора.
Оптимизатор
Для данного синтаксического дерева база данных должна выбрать наиболее эффективный алгоритм извлечения данных.
Данные извлекаются по пути доступа |, и оптимизатору необходимо оценить несколько вариантов обхода данных, таких как:
- Метод доступа для каждой таблицы ссылок (сканирование таблицы или сканирование индекса).
- Для сканирования индекса он должен решить, какой индекс лучше подходит для извлечения этого набора результатов.
- Для каждого отношения соединения (например, таблицы, представлений или Общего табличного выражения) он должен выбрать наиболее эффективный тип соединения (например, Соединения вложенных циклов , Хэш-соединения , Объединения слиянием ).
- Порядок соединения становится очень важным, особенно для соединений вложенных циклов.
Список путей доступа, выбранных оптимизатором, собирается в план выполнения.
Из-за большого количества возможных комбинаций планов действий найти хороший план выполнения не является тривиальной задачей.
Чем больше времени тратится на поиск наилучшего возможного плана выполнения, тем выше будет время отклика на транзакцию, поэтому оптимизатор имеет фиксированный бюджет времени для поиска разумного плана.
Наиболее распространенным алгоритмом принятия решений является оптимизатор на основе затрат (CBO).
Каждый метод доступа преобразуется в физическую операцию с базой данных, и можно оценить связанные с этим затраты ресурсов.
В базе данных хранятся различные статистические данные, такие как размеры таблиц и количество данных (насколько значения столбцов отличаются от одной строки к другой), чтобы оценить стоимость данной операции с базой данных.
Стоимость рассчитывается на основе количества циклов процессора и операций ввода-вывода, необходимых для выполнения данного плана.
При поиске оптимального плана выполнения оптимизатор может оценить несколько вариантов и, исходя из их общей стоимости, выбрать тот, для выполнения которого требуется наименьшее количество времени.
К настоящему времени ясно, что поиск правильного плана выполнения требует больших ресурсов, и для этой цели некоторые поставщики баз данных предлагают кэширование плана выполнения (чтобы сократить время, затрачиваемое на поиск оптимального плана).
Хотя кэширование может ускорить выполнение инструкций, оно также сопряжено с некоторыми дополнительными проблемами (обеспечение того, чтобы план оставался оптимальным при нескольких исполнениях).
Каждый план выполнения имеет определенный объем памяти, и большинство систем баз данных используют кэш фиксированного размера (отбрасывая наименее используемые планы, чтобы освободить место для более новых). Инструкции DDL (Язык определения данных) могут привести к повреждению планов выполнения, что сделает их устаревшими, поэтому база данных должна использовать отдельный процесс для проверки соответствия существующих планов выполнения.
Однако наиболее сложным аспектом кэширования является обеспечение того, чтобы в кэш попадал только хороший план выполнения, поскольку плохой план, многократно используемый повторно, может действительно снизить производительность приложения.
Исполнитель
От оптимизатора план выполнения передается Исполнителю, где он используется для извлечения связанных данных и построения результирующего набора.
Исполнитель использует Механизм хранения (для загрузки данных в соответствии с текущим планом выполнения) и Механизм транзакций (для обеспечения соблюдения гарантий целостности данных текущей транзакции).
Исполнитель просто запускает план выполнения, который похож на программу, созданную во время выполнения, которая сообщает Исполнителю, как получить данные, необходимые клиенту.
Подготовленные заявления
Поскольку синтаксический анализ инструкций и создание плана выполнения являются ресурсоемкими операциями, некоторые поставщики баз данных предлагают кэш плана выполнения.
Строковое значение оператора используется в качестве входных данных для функции хеширования, и полученное значение становится ключом ввода кэша плана выполнения.
Если значение строки инструкции изменяется от одного выполнения к другому, база данных не сможет повторно использовать уже созданный план выполнения.
Для этой цели динамические инструкции JDBC не подходят для повторного использования планов выполнения.
Подготовленные на стороне сервера инструкции позволяют логике доступа к данным повторно использовать один и тот же план выполнения для нескольких исполнений. Подготовленный оператор всегда связан с одним оператором SQL, и параметры привязки используются для изменения контекста выполнения во время выполнения.
Поскольку подготовленные операторы принимают SQL-запрос во время создания, база данных может предварительно скомпилировать соответствующую инструкцию SQL перед ее выполнением.
На этапе предварительной компиляции база данных проверяет инструкцию SQL и анализирует ее в синтаксическом дереве. Когда дело доходит до выполнения Подготовленной инструкции , драйвер отправляет фактические значения параметров, и база данных может перейти к компиляции и выполнению фактического плана выполнения.
Концептуально этапы подготовки и выполнения выполняются в отдельных циклах базы данных. Однако некоторые системы баз данных предпочитают оптимизировать этот процесс, поэтому объединяют эти две фазы в одну базу данных туда и обратно.
Из-за избирательности индекса в отсутствие фактических значений параметров привязки оптимизатор не может скомпилировать синтаксическое дерево в план выполнения.
Поскольку доступ к диску необходим для извлечения всех дополнительных данных на уровне строк, индексирование подходит при выборе только части данных всей таблицы. Большинство систем баз данных принимают это решение на основе избирательности индекса текущих значений параметров привязки.
Поскольку для каждого доступа к диску требуется чтение целого блока данных, доступ к слишком большому количеству разрозненных блоков может на самом деле работать хуже, чем сканирование всей таблицы (случайный доступ медленнее, чем последовательное сканирование).
Для подготовленных инструкций план выполнения может быть либо скомпилирован при каждом выполнении, либо его можно кэшировать и использовать повторно.
Перекомпиляция плана может генерировать наилучшие пути доступа к данным для любого заданного набора переменных привязки, оплачивая при этом использование дополнительных ресурсов базы данных.
Повторное использование плана может сэкономить ресурсы базы данных, но оно может не подходить для каждой комбинации значений параметров.
PostgreSQL
До версии 9.2 подготовленный оператор планировался и полностью компилировался на этапе подготовки, поэтому план выполнения был сгенерирован в отсутствие фактических значений параметров привязки.
Хотя эта стратегия предназначалась для экономии ресурсов базы данных, она была очень чувствительна к искаженным данным.
Начиная с PostgreSQL 9.2, этап подготовки только анализирует и переписывает инструкцию, в то время как этап оптимизации и планирования откладывается до времени выполнения. Таким образом, переписанное синтаксическое дерево оптимизируется в соответствии с фактическими значениями параметров привязки и генерируется оптимальный план выполнения.
Для одного выполнения простой инструкции требуется только одно обращение к базе данных, в то время как для подготовленной инструкции требуется два (запрос на подготовку и вызов выполнения).
Чтобы избежать сетевых накладных расходов, по умолчанию JDBC PreparedStatement(ы) выполняет этапы подготовки и выполнения по одному запросу к базе данных.
Подготовленная на стороне клиента инструкция должна выполняться не менее 5 раз, чтобы драйвер превратил ее в инструкцию на стороне сервера.
Значение числа выполнений по умолчанию задается параметром prepareThreshold , который настраивается как свойство подключения или через API для конкретного драйвера .
После нескольких выполнений, если производительность не зависит от значений параметров привязки, оптимизатор может выбрать преобразование плана в общий и кэшировать его для повторного использования.
MySQL
При подготовке инструкции анализатор MySQL генерирует синтаксическое дерево, которое дополнительно проверяется и предварительно оптимизируется механизмом resolution . Синтаксическое дерево подвергается нескольким преобразованиям, не зависящим от данных, и конечным результатом является постоянное дерево .
Поскольку MySQL 5.7.4 , все постоянные преобразования (повторные заказы или оптимизация подзапросов) выполняются на этапе подготовки, поэтому на этапе выполнения применяются только преобразования, зависящие от данных.
MySQL не кэширует планы выполнения, поэтому каждое выполнение оператора оптимизировано для текущих значений параметров привязки, что позволяет избежать проблем с искажением данных.
Из-за некоторых нерешенных проблем, начиная с версии 5.0.5 , драйвер MySQL JDBC эмулирует только подготовленные на стороне сервера инструкции.
Чтобы переключиться на подготовленные на стороне сервера инструкции, оба useServerPrepStmts и свойства cachePrepStmts подключения должны быть установлены в true .
Перед активацией этой функции лучше проверить последние примечания к выпуску Connector/J и убедиться, что эта функция безопасна для использования.
Вывод
Будучи декларативным языком, SQL описывает что , а не как .
Фактические структуры базы данных и алгоритмы, используемые для извлечения и подготовки желаемого набора результатов, скрыты от клиента базы данных, который должен сосредоточиться только на правильном определении инструкции SQL.