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

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

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

Анализ Данных: курс-тренажер по SQL
Идет набор в группу 1 600₽ в месяц

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

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

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

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

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

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

Специализация «Frontend-разработчик»
Идет набор в группу 5900₽ в месяц

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поделиться:
Опубликовано в рубрике Веб-разработкаTagged ,

SkillFactory.Рассылка