Четыре типичные ошибки в SQL
Несмотря на полезность SQL, он также подвержен определённым трудностям, которые могут серьезно сказаться на производительности баз данных.
В этой статье рассмотрим 4 типичные ошибки в SQL.
И начнем с первой проблемы, связанной с ошибками с использованием не SARGable предикатов.
Ошибки с использованием не SARGable предикатов
В запросах SQL индексы спроектированы для ускорения поиска данных, но если запрос содержит предикаты, которые модифицируют значения столбцов перед сравнением (например, применение функций к столбцам или использование операторов, изменяющих тип данных), это может заставить SQL Server выполнить полное сканирование таблиц, а не быстрый поиск по индексу. Примерами таких операций являются преобразования типов данных, использование функций, таких как YEAR(), MONTH(), или обертывание столбцов в функции, такие как LTRIM(), RTRIM(), или SUBSTRING() в условиях фильтрации.
Чтобы избежать проблем с производительностью из-за не-SARGable предикатов, необходимо переписать запросы так, чтобы они не требовали преобразования данных в столбцах, используемых для индексов. Например, вместо использования функции CONVERT для столбца даты в условии WHERE, лучше преобразовать литерал даты в соответствующий тип данных с помощью CAST, оставляя столбец без изменений, что позволит выполнить поиск по индексу. Вместо вычисления DAY(CreateDate) = 19, более производительным будет условие, в котором указан полный диапазон дат, например, CreateDate BETWEEN '2017-08-19 00:00:00' AND '2017-08-20 00:00:00' .
Но не забывайте о том, что оптимизация запросов под индексы может потребовать доп. изменений в структуре БД, таких как добавление вычисляемых столбцов или переиндексация для улучшения доступа к данным.
Можно также юзать встроенные инструменты SQL Server, такие как sp_BlitzCache, для анализа кеша планов запросов и идентификации не-SARGable запросов.
Неправильные столбцы в подзапросах
Основная ошибка здесь — отсутствие явных алиасов для столбцов, что может привести к тому, что SQL сервер неправильно интерпретирует, из какой именно таблицы должны выбираться данные.
Например, если в подзапросе используется столбец sale_date, но в контексте запроса существует более одной таблицы с таким столбцом, и не указано явно, откуда брать sale_date, SQL сервер может автоматом выбрать его из неправильной таблицы.
Для предотвращения таких ошибок следует использовать алиасы для таблиц и всегда явно указывать алиас при обращении к столбцам в подзапросах. Это не только устраняет неоднозначности, но и улучшает читаемость SQL кода. Пример:
SELECT sale_date, sale_amount
FROM Sales AS S
WHERE sale_date IN (
SELECT C.calendar_date
FROM Calendar AS C
WHERE C.holiday_name IS NOT NULL
);C является алиасом для таблицы Calendar, и использование C.calendar_date гарантирует, что SQL сервер выберет даты именно из таблицы Calendar.
Кроме того, важно тестировать подзапросы отдельно от основного запроса, чтобы убедиться, что каждый подзапрос корректно функционирует сам по себе.
Также иногда полезно юзать оператор EXISTS вместо IN, особенно когда ожидается, что подзапрос может вернуть большое количество строк.
Несоответствие типов данных
Проблема несоответствия типов данных в SQL часто возникает в основном из-за использования неподходящих или различных типов данных для хранения и обработки данных в БД. Несоответствие типов данных требует доп. ресурсов на преобразование типов во время выполнения запросов.
Для предотвращения проблем, связанных с несоответствием типов данных, рекомендуется стандартизировать использование типов данных во всей БД.Т. е нужно определять наиболее подходящие типы данных для каждого столбца данных и строго соблюдать эти определения при разработке БД. Например, если ожидается, что столбец будет хранить только целочисленные значения, он должен быть определен как тип данных INT или BIGINT, а не VARCHAR.
В случаях, когда несоответствие типов данных неизбежно, например, при интеграции с внешними системами или при обработке данных, полученных из различных источников, следует использовать явное приведение типов для корректной обработки данных. SQL имеет хорошие функции CAST и CONVERT, которые позволяют явно указать, какой тип данных должен быть использован в операциях с данными:
SELECT CAST(column_name AS INT)
FROM table_name;SELECT CONVERT(INT, column_name)
FROM table_name;Ошибка в порядке оценки предикатов
Проблема неожиданного порядка оценки предикатов в SQL-запросах может возникнуть из-за особенностей оптимизатора запросов, который может изменять порядок вычисления условий для ускорения выполнения запроса.
Для того, чтобы таких проблем было минимум, следует чётко структурировать запросы, используя логические блоки и явное указание порядка выполнения операций. Например, можно использовать подзапросы или временные таблицы.
Подзапросы выполняются до основного запроса и с ними можно точно контролировать порядок обработки данных. Например, можно сначала выполнить подзапрос для фильтрации данных по одному критерию, а результат использовать в основном запросе для дальнейшей фильтрации:
SELECT * FROM (
SELECT * FROM Employees WHERE DepartmentID = 10
) AS FilteredEmployees WHERE Salary > 50000;В SQL порядок оценки условий в выражении CASE всегда определён, что также позволяет точно контролировать логику выполнения запроса:
SELECT EmployeeID, Name, Salary,
CASE
WHEN Salary < 40000 THEN 'Low'
WHEN Salary BETWEEN 40000 AND 80000 THEN 'Average'
ELSE 'High'
END AS SalaryLevel
FROM Employees;Для контроля порядка выполнения предикатов можно использовать опции запроса, такие как OPTION (FORCE ORDER), которая заставляет SQL Server обрабатывать запросы в строгом соответствии с порядком, указанным в запросе.
Например, если нужно убедиться, что SQL Server не будет переупорядочивать таблицы в запросе JOIN вне зависимости от статистики и индексов, можно сделать так:
SELECT *
FROM Table1
JOIN Table2 ON Table1.id = Table2.id
JOIN Table3 ON Table2.id = Table3.id
OPTION (FORCE ORDER);OPTION (FORCE ORDER) заставит SQL Server обрабатывать JOIN в точном порядке, указанном в запросе, начиная с Table1, затем Table2, и, наконец, Table3.
Больше практических навыков по аналитике и работе с данными вы можете получить в рамках практических онлайн-курсов OTUS от экспертов отрасли.
Habrahabr.ru прочитано 3120 раз
