Понимание порядка выполнения SQL запроса

Что такое порядок выполнения SQL запроса

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

Возьмём пример SQL запроса и посмотрим, как он выполняется:

SELECT
customers.name,
COUNT(order_id) as Total_orders,
SUM(order_amount) as total_spent
FROM customers
JOIN orders ON customers.id = orders.customer_id
WHERE order_date >= '2023-01-01'
GROUP BY customers.name
HAVING total_spent >= 1000
ORDER BY customers.name
LIMIT 100;

Порядок выполнения этого запроса следующий:

1. Предложение FROM: Первым шагом является определение таблиц, задействованных в запросе. В данном случае это customers и orders.
2. Предложение JOIN: Следующим шагом является выполнение операции объединения на основе условия объединения. В данном случае это customers.id = orders.customer_id, которое связывает две таблицы, сопоставляя id клиентов.
3. Предложение WHERE: Третий шаг — применить условие фильтрации к объединённой таблице. В данном случае это order_date >= '2023-01-01', который выбирает только заказы, сделанные 1 января 2023 года и позже. Теперь важно написать запрос SARGABLE, чтобы эффективно использовать индексы. SARGABLE означает Searched ARGUment ABLE и относится к запросам, которые могут использовать индексы для более быстрого выполнения. Мы углубимся в SARGABLE запросы позже.
4. Предложение GROUP BY: Четвёртый шаг — сгруппировать строки по указанным столбцам. В данном случае это customers.name, которое создаёт группы на основе имени клиента.
5. Предложение HAVING: Пятый шаг — фильтрация групп по условию. В данном случае это total_spent >= 1000, при котором выбираются группы с общей потраченной суммой 1000 и более.
6. Предложение SELECT: Шестой шаг — выбор столбцов и агрегатных функций из каждой группы. В данном случае это customers.name, COUNT(order_id) as Total_orders и SUM(order_amount) as total_spent.
7. Предложение ORDER: Седьмой шаг — сортировка строк по указанным столбцам. В данном случае это customers.name, которое сортирует строки в алфавитном порядке по именам клиентов.
8. Предложение LIMIT: Последний шаг — пропустить несколько строк из отсортированного набора результатов. В данном случае результат ограничивается максимум 100 строками.

Источник изображения

Почему запросы SARGABLE имеют значение

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

Запрос SARGABLE, если он использует операторы и функции, которые могут использовать преимущества индексов. Например, использование операторов равенства (=), неравенства (<>, !=), диапазона (BETWEEN) или членства (IN) в индексированных столбцах может сделать запрос SARGABLE.

Запрос не SARGABLE, если он использует операторы или функции, которые препятствуют использованию индекса или требуют полного сканирования таблицы. Например, использование операторов отрицания (NOT), подстановочных знаков (LIKE) или арифметических операций (+, -, *, /) в индексированных столбцах может сделать запрос не SARGABLE.

Для написания SARGABLE запроса необходимо следовать некоторым общим рекомендациям:

• Избегайте использования функций для индексированных столбцов в предложении WHERE, таких, как UPPER(), LOWER(), SUBSTRING() и т.д.
• Избегайте использования арифметических операций над индексированными столбцами в предложении WHERE, например столбец + 1 > 10, столбец * 2 < 20 и т.д.
• Избегайте использования операторов отрицания для индексированных столбцов в предложении WHERE, таких, как NOT IN, NOT LIKE, NOT EXISTS и т.д.
• Избегайте использования подстановочных знаков в индексированных столбцах в предложении WHERE с ведущими подстановочными знаками (%), такими как LIKE '%abc', LIKE -xyz%' и т.д.
• Используйте соответствующие типы данных для столбцов и литералов, чтобы избежать неявных преобразований, которые могут повлиять на использование индекса.

Вот несколько примеров запросов SARGABLE и не SARGABLE:

Плохой: SELECT ... WHERE Year(myDate) = 2022
Исправленный: SELECT ... WHERE myDate >= '01-01-2022' AND myDate < '01-01-2023'

Плохой: Select ... WHERE SUBSTRING(DealerName,4) = 'Ford'
Исправленный: Select ... WHERE DealerName Like 'Ford%'

Плохой: Select ... WHERE DateDiff(mm, OrderDate, GetDate ()) >= 30
Исправленный: Select ... WHERE OrderDate < DateAdd(mm, -30, GetDate())

Как настроить производительность на уровне базы данных

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

• Используйте соответствующие индексы: Проанализируйте шаблоны запросов и определите столбцы, часто используемые в операциях поиска, объединения и фильтрации. Создавайте индексы для этих столбцов для более быстрого извлечения данных и уменьшения потребности в полном сканировании таблиц.
• Оптимизируйте операции JOIN: Убедитесь, что условия JOIN эффективны и используйте соответствующие индексы. По возможности используйте INNER JOIN вместо OUTER JOIN, поскольку это обычно приводит к повышению производительности. Рассмотрите порядок объединения нескольких таблиц, чтобы свести к минимуму размер промежуточного набора результатов.
• Ограничивайте размер набора результатов: Используйте предложение LIMIT для ограничения количества строк возвращаемых запросом. Это может уменьшить объём обрабатываемых данных и сократить время ответа на запрос.
• Избегайте ненужной сортировки и группировки: Устраните ненужные операции сортировки и группировки, включая их только по необходимости. Этого можно добиться путём тщательного анализа запроса и удаления ненужных предложений ORDER BY и GROUP BY.
• Ранняя фильтрация с предложением WHERE: Применяйте условие фильтрации как можно раньше в порядке выполнения запроса с помощью предложения WHERE. Это уменьшает количество строк, обрабатываемых на следующих этапах, повышая производительность.
• Используйте подходящие типы данных: Выберите правильные типы данных для столбцов, чтобы обеспечить эффективное хранение и извлечение данных. Использование соответствующих типов данных. Использование соответствующих типов данных может помочь сократить потребление памяти и повысить скорость выполнения запросов.
• Избегайте ненужных вычислений и функций: Сведите к минимуму использование вычислений и функций в запросе, особенно в индексированных столбцах. Эти операции могут препятствовать использованию индекса и влиять на производительность. Рассмотрите возможность предварительного вычисления значений или использования производных столбцов, когда это необходимо.
• Инструменты оптимизации запросов: Используйте инструменты или подсказки оптимизации запросов для конкретной базы данных, чтобы направлять механизм базы данных в создании эффективных планов выполнения. Эти инструменты могут предоставить информацию, рекомендации и статистику для повышения производительности.

Заключение

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