Язык структурированных запросов (structured query language, SQL), который через несколько лет разменяет шестой десяток, остается главным рабочим инструментом для многих специалистов по работе с данными. Недавно мы рассказывали, почему даже сегодня у этого языка нет конкурентов. Сегодня речь пойдет о базовых процессах, которые скрываются за выполнением SQL-запросов.
Тем нашим читателям, которые хотят изучать разработку с нуля, эта статья поможет понять, как улучшить производительность кода и получать нужную информацию из хранилищ максимально быстро. Даже если вы хотите выучить SQL быстро, чтобы применять только на прикладном уровне, без этих знаний вам не обойтись.
[course id=1078]
Прием, как слышно?
Чтобы разобраться, что происходит, когда SQL-сервер получает запрос, для начала заглянем в его внутреннее устройство. За обработку поступающих команд отвечают два механизма:
- Relational Engine (RE) — обеспечивает физическую обработку команды и создает план выполнения команды (query execution plan).
- Storage Engine (SE) — обеспечивает логическую обработку, принимая план к исполнению и возвращая результат.
Как нетрудно догадаться, запрос первым делом поступает в RE, где последовательно проходит через три операции:
- Парсинг команды. Попросту говоря, это проверка синтаксиса. На этом этапе SQL-движок не уточняет, существуют ли указанные в запросе таблицы и колонки — его интересует только корректность формулировок. Результатом этой проверки становится дерево разбора (parse tree), в котором содержатся логические шаги для выполнения запроса
- Семантический анализ (или связывание — binding). За эту фазу отвечает компонент с труднопереводимым названием algebrizer. Он удостоверяется, что команда имеет смысл — например, проверяет, действительно ли объединенные в команде таблицы, откуда нужно получить данные, являются таблицами. В результате algebrizer создает процессинговое дерево (processor tree) и передает его дальше.
- Оптимизация запроса. Это последний шаг перед составлением плана выполнения команды. Оптимизатор движка пытается найти наилучший способ предоставления нужных данных, что подразумевает минимальные затраты времени и вычислительных ресурсов. Для этого он сравнивает несколько доступных вариантов, прогоняя все возможные комбинации готовящегося плана.
Оптимизация — это ключевой этап обработки запроса, который определяет скорость предоставления данных. Остановимся на нем чуть подробнее, здесь есть хитрости, без которых быстро выучить 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 — это значит узнать, какие структуры подходят для каких типов данных и какие операции с ними наиболее эффективны.
Текст: Помогаев Дмитрий
