Несмотря на полезность 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 от экспертов отрасли.
Комментарии (24)
Portnov
22.05.2024 05:25+5По-моему, если составлять "топ самых частых ошибок в SQL", то он наполовину будет заполнен разными ошибками, связанными с неправильным пониманием как работают null-значения...
Akina
22.05.2024 05:25Ещё очень частая ошибка - игнорирование приоритета логических операторов. Кстати, тут Майкрософт тоже отличилась - в документации в таблице приоритетов не указан приоритет оператора AND. Забыли про его существование, панимаишь...
Adgh
22.05.2024 05:25А есть принципиальная разница в использовании оператора
EXISTSвместоINна элементах подзапросов?Akina
22.05.2024 05:25+1Несомненно. Даже если сервер окажется достаточно умным, чтобы проиндексировать результат подзапроса или воспользоваться имеющимся индексом, WHERE IN вряд ли обгонит WHERE EXISTS... или это будет ну очень специальный кейс.
Кстати, у этих вариантов отбора есть ещё третья альтернатива - некоррелированный или латеральный подзапрос во FROM с INNER JOIN. И зачастую он даёт гораздо более понятный текст запроса. И, в зависимости от статистики данных, он может оказаться более быстрым.
Кстати, современные построители плана выполнения запроса настолько умны, что на не очень комплексных запросах строят один и тот же план для всех трёх вариантов.
Akina
22.05.2024 05:25+1В 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;Не, ну вот стоило писАть про определённый порядок, чтобы тут же в примере это не учитывать, а? Должно быть
WHEN Salary <= 80000 THEN 'Average'. Да и то, при условии, что Salary - целое.
Akina
22.05.2024 05:25это может заставить SQL Server выполнить полное сканирование таблиц, а не быстрый поиск по индексу
Следует обязательно помнить, что при низкой селективности условия отбора полное сканирование таблицы быстрее, чем "быстрый поиск по индексу" с последующим чтением тела таблицы для получения отобранных записей. И если статистика данных такова, то какой смысл копья ломать за SARGable предикат, если сервер всё одно не станет использовать индекс?
ptr128
22.05.2024 05:25+1Это часто, но не всегда так. Например, если таблица настолько широка, что каждая её запись занимает страницу БД, а индекс на два порядка компактней, то даже при 90% селективности, может быть выгодней прочитать 10000 страниц индекса и 900000 страниц БД, чем все 1000000 страниц БД.
В случае BRIN индекса тоже самое может проявиться и на узкой таблице.
А в случае, если все необходимые поля уже есть в индексе или включены в него, то точно полное сканирование индекса выиграет.
Batalmv
22.05.2024 05:25Странные чуток ошибки
Ошибка в порядке оценки предикатов
Всегда считал и буду считать, что не надо заниматься "директ" командами оптимизатору, поэтому я вообще не вижу тут проблемы
Несоответствие типов данных
Опять же, если проблемы на таком уровне - это пичалька. Плюс надо понимать, что это не проблема использования SQL как такового, а скорее проблема проектирования, что совсем другое
Остальное ОК
Akina
22.05.2024 05:25это не проблема использования SQL как такового, а скорее проблема проектирования
Позвольте не согласиться.
Наиболее типичный и показательный пример - это EAV-паттерн хранения. Который абсолютно все атрибуты хранит в поле строкового типа. Системы с неопределённым на этапе проектирования или свободно-расширяемым набором атрибутов встречаются сплошь и рядом. А альтернатива - вроде хранения в сериализованном виде (JSON там и иже с ним),- во-первых, вроде совсем даже не лучшее, что можно придумать для БД, во-вторых, всё равно до конца проблемы не решает. Ну а уж несколько полей разных типов так и вовсе смотрятся как ишак в табуне.
Да даже в повседневной форме - например, литералы даты-времени в запрос поступают в форме строкового литерала. Да, можно явно указать модификатор типа значения, но как-то все серверы и без него справляются. Причём далеко не всегда контекст использования позволяет определить требуемый контекст типа данных.. вот сравниваем мы поле типа Дата и строковый литерал со значением даты - какой нужен тип сравнения, строковый или даты? Спасает то, что стандартно используемый формат даёт одинаковый результат сравнения в обоих контекстах... это Оракл или там SQLite либо как-то выворачиваются по прописанным в документации правилам, либо требуют явного указания контекста.
А потому порой при проектировании - вот на пупе извернись, а ничего не сделать.
Batalmv
22.05.2024 05:25Так это и есть "проектирование" :) Выбор паттерна хранения, решения задачи расширяемости и т.д., нет?
Akina
22.05.2024 05:25Я имею в виду, что это не всегда проблема, это может быть вполне штатное состояние, никакой проблемы не представляющее. Но которое нужно, несомненно, учитывать на стадии фактической реализации проекта.
alexhott
22.05.2024 05:25В 99,9% случаев все описанное давно не актуально, СУБД и ее планировщик это все давно корректно обрабатывают.
а в 0,1 это когда слишком замудреный запрос - там может разве что порядок предикатов съехать
gleb_l
22.05.2024 05:25+2Типичная затравочная статья - бросить кость аудитории, и ждать, как за нее передерутся участники, имеющие право комментирования. Типичных ошибок проектирования баз данных и способов работы с ними - сотни и тысячи, имхо бессмысленно спорить, насколько хорошо приведенные четыре вписываются в это множество.
Кроме того, что это все желтизна. "Три ошибки начинающего водителя зимой", "Пять принципов настоящего мачо", "Семь продуктов для моментального похудения" итд - просто пропускайте все это мимо - даже если статью заминусуют, дерево комментариев под ней накрутит показов в плюс компании-публиканту. Вы уверены, что хотите работать белочкой, несущей изумрудные орешки данной компании?
baldr
22.05.2024 05:25+2Ну, в целом, вы правы на все 99%. Тем более, это OTUS, который уже заклеймил себя (в моих глазах как минимум) как худший блог на хабре за совершенно пустые статьи.
Однако! Должен сказать что с тех пор как в этот блог пришел @badcasedaily1 - всё начало меняться в лучшую сторону. Несколько статей были реально неплохими, хотя общий тренд не сильно сдвинулся - всё ещё по верхам пишете, товарищ!
Раньше, когда видел в авторах @MaxRokatansky - если и заходил в статью, то сразу чтобы поставить минус. Было реально плохо.
Сейчас захожу уже чтобы, хоть и бегло, но оценить нового автора и, если могу, поддержать плюсом или в комментариях помочь.
Конкретно эта статья - ни о чём.
mentin
22.05.2024 05:25Подзапросы выполняются до основного запроса и с ними можно точно контролировать порядок обработки данных. Например, можно сначала выполнить подзапрос для фильтрации данных по одному критерию, а результат использовать в основном запросе для дальнейшей фильтрации:
SELECT * FROM (
SELECT * FROM Employees WHERE DepartmentID = 10
) AS FilteredEmployees WHERE Salary > 50000;Нет, никаких гарантий что фильтр в подзапросе выполнится до основного запроса нет. SQL Server (как и большинство разумных DBMS) может проверить
Salary > 50000раньше, чем фильтр поDepartmentID. Скажем если есть clustered index по Salary то наверняка так и сделает. К счастью, он это обычно делает лучше чем большинство писателей SQL, но если очень надо SO обсуждает как действительно заставить его делать что вам нужно
baldr
Статья специфична только для MS SQL Server. Некоторые "ошибки" совсем не пропустит PostgreSQL (неоднозначность в полях). Также часть синтаксиса отсутствует в других диалектах (
OPTION (FORCE ORDER)).Одни ошибки вы объясняете, но в примерах приводите новые. Например, подзапросы - внешний запрос вполне может уже не использовать индексы.
Tzimie
MSSQL также выругается, если поле с одним именем у разных таблиц, а не указан алиас
Gummilion
Так в этом и смысл, как я понял - во внутреннем запросе указываем критерий, по которому мы хотим использовать индекс (если хотим обойтись без явного указания имени индекса в хинтах), а по остальным критериям записи будут просто отфильтровываться
Akina
По-моему, сервер никогда и не при каких обстоятельствах не интерпретирует имя поля как поле внешней таблицы, если есть одноимённое поле внутренней таблицы.
Более того, по логике, если имя поля без алиаса таблицы принадлежит внешней таблице, сервер должен бы выдавать очевидную ошибку "поле не найдено". То есть до тех пор, пока это не указано явно, подзапрос должен бы считаться некоррелированным. Увы, к сожалению, это не описано стандартом, и SQL Server это дело пропускает - эдакий себе lamer-friendly mode. И это странно, потому как, в запросе на обновление такой фортель уже не проходит, выдавая Msg 209 Level 16 State 1 Ambiguous column name.
TerraV
Двадцать лет назад такая проблема была. В то время вообще было много всякой радости. Типа возможности использования неаггрегированных полей в SELECT при использовании группировки.
valery1707
Такое позволял MySQL относительно недавно - буквально лет 5 назад точно.
Akina
MySQL это и сейчас позволяет - допустимость такой нестандартной конструкции определяется установленным параметром Server SQL Mode, конкретно флагом ONLY_FULL_GROUP_BY.
И ещё есть исключение. Если в выражение агрегирования включается немодифицированное поле первичного ключа, то дополнительное включение других полей той же таблицы совершенно бессмысленно. И всё больше СУБД приходят к этой мысли и пропускают запросы с такой "особенностью" синтаксиса без сообщения об ошибке.