А-Б-В, SQL: как работает язык структурированных баз данных?

Язык структурированных запросов (structured query language, SQL), который через несколько лет разменяет шестой десяток, остается главным рабочим инструментом для многих специалистов по работе с данными. Недавно мы рассказывали, почему даже сегодня у этого языка нет конкурентов. Сегодня речь пойдет о базовых процессах, которые скрываются за выполнением SQL-запросов.

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

[course id=1078]

Прием, как слышно?

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

  • Relational Engine (RE) — обеспечивает физическую обработку команды и создает план выполнения команды (query execution plan).
  • Storage Engine (SE) — обеспечивает логическую обработку, принимая план к исполнению и возвращая результат.

Как нетрудно догадаться, запрос первым делом поступает в RE, где последовательно проходит через три операции:

  1. Парсинг команды. Попросту говоря, это проверка синтаксиса. На этом этапе SQL-движок не уточняет, существуют ли указанные в запросе таблицы и колонки — его интересует только корректность формулировок. Результатом этой проверки становится дерево разбора (parse tree), в котором содержатся логические шаги для выполнения запроса
  2. Семантический анализ (или связывание — binding). За эту фазу отвечает компонент с труднопереводимым названием algebrizer. Он удостоверяется, что команда имеет смысл — например, проверяет, действительно ли объединенные в команде таблицы, откуда нужно получить данные, являются таблицами. В результате algebrizer создает процессинговое дерево (processor tree) и передает его дальше.
  3. Оптимизация запроса. Это последний шаг перед составлением плана выполнения команды. Оптимизатор движка пытается найти наилучший способ предоставления нужных данных, что подразумевает минимальные затраты времени и вычислительных ресурсов. Для этого он сравнивает несколько доступных вариантов, прогоняя все возможные комбинации готовящегося плана. 

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

[course id=1300]

Когда лучшее — враг хорошего

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

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

В тех случаях, когда поступающий запрос оказывается совсем простым (SELECT * FROM <<имя_таблицы>>), оптимизатор может вообще не заниматься лишней работой, а выполнить команду, как она написана. Такие планы называются тривиальными, и в некоторых ситуациях они могут стать серьезной проблемой для разработчика. Почему? Потому что движок оказывается по-человечески ленивым и раз за разом выбирает простое решение вместо правильного — не рассчитывает нужные ресурсы, а идет по вроде бы подходящему пути… и в итоге тратит больше усилий, чем если бы оценил доступные варианты с самого начала.

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

[read id=1293]

Логических дел мастер

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

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

Здесь главное, что нужно помнить — это последовательность обработки аргументов запроса.

Как видите, в основном порядок совпадает с грамотным синтаксисом SQL-команды, за одним важным исключением — идущие в начале SELECT, DISTINCT и TOP обрабатываются в последнюю очередь. Об этом важно помнить, чтобы при падении производительности правильно анализировать источник проблем.

На каждом логическом шаге SE создает временную виртуальную таблицу, доступ к которой есть только у SQL-сервера. Таблица первого шага служит источником данных для шага второго, и так далее. Только последняя виртуальная таблица оказывается доступна пользователю — в ней и содержатся результаты его запроса.

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

[read id=1240]

Самые частые источники проблем с SQL-запросами

Множество внутренних операторов. Лучший способ запутать оптимизатор — дать ему команду с множеством операторов JOIN, CROSS APPLY и т.д. Таким образом вы заставляете движок перебирать множество вариантов по выполнению этих субзапросов, а ресурсы у вас не бесконечны.

Злоупотребление курсорами. Курсор в SQL позволяет хранить в памяти последний использованный оператор. Чаще всего используются внутри триггеров, хранимых процедур и сценариев. В абсолютном большинстве случаев курсоры лучше заменить на стандартные операторы, которые позволяют выполнять операции SELECT, UPDATE, INSERT, DELETE гораздо быстрее. 

Неэффективные индексы. Эти структуры данных чаще всего тормозят работу SQL-базы. Разработчику следует держать под контролем соотношение целевых рядов к общему их количеству в таблице — самыми продуктивными индексами будут те, у которых эта цифра будет минимальной (желательно, в районе 5%). Необходимо также отслеживать появление неиспользуемых индексов и оперативно от них избавляться. Наконец, как бы эти структуры ни были полезны для получения данных из базы, операции DML с ними проходят гораздо медленнее, так что помните о текущих задачах и выбирайте оптимальные методы.

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

Текст: Помогаев Дмитрий

(рейтинг: 5, голосов: 3)
Добавить комментарий