Усовершенствуйте свои SQL-запросы для производственных систем данных

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

Зачем вообще нужна оптимизация запросов?

SQL — один из самых мощных инструментов обработки данных. Однако SQL является декларативным языком, то есть каждый запрос объявляет, что должен сделать механизм SQL, но не говорит как. Как оказалось, как — «план» — это то, что влияет на эффективность запросов, однако это очень важно.

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

Там нет пошагового руководства для того же. В свою очередь, мы должны использовать общие рекомендации по написанию запросов, какие операторы использовать. Затем проверьте «планы выполнения» и выясните, какая часть запроса занимает больше всего времени, и перепишите эту часть каким-либо другим способом.

Преимущества оптимизации запросов

  • Сведите к минимуму производственные проблемы. Неэффективные запросы требуют больших ресурсов ЦП, памяти и операций ввода-вывода, что в большинстве случаев приводит к взаимоблокировкам, сбоям транзакций, зависанию системы и другим производственным проблемам.
  • Проблемы с производительностью. Медленный запрос означает более медленное время отклика приложений, использующих запрос, что приводит к ухудшению взаимодействия с пользователем.
  • Экономия затрат на инфраструктуру. Поскольку неоптимизированный запрос требует больше ресурсов ЦП и памяти, это приводит к увеличению затрат на инфраструктуру.

Рекомендации по оптимизации запросов

  • Разумно выбирайте тип данных. Использование Varchar(10) для хранения данных фиксированной длины (например, номера мобильного телефона) повлечет за собой увеличение штрафа за выполнение запроса на 20 % по сравнению с использованием char(10) для данных фиксированной длины.
  • Избегайте использования неявного преобразования. Когда запрос сравнивает разные типы данных, он использует неявное преобразование типа данных. Следовательно, в запросах следует избегать сравнения столбцов и значений разных типов данных, например:
WHERE date >= "2022-01-01"
  • Избегайте использования условного предложения на основе функции.Когда запрос использует функцию в предложении WHERE или JOIN для столбца, он не будет использовать индекс, что замедляет выполнение.
-- WHERE date(ship_date) = '2022–01–01'
-- JOIN T2 ON CONCAT(first_name,' ',last_name) = 'garvit arya'
  • Избегайте одновременного использования DISTINCT и GROUP BY: если в вашем запросе уже есть GROUP BY, нет необходимости использовать DISTINCT отдельно.
  • Избегайте одновременного использования UNION DISTINCT и SELECT DISTINCT: для запросов с UNION DISTINCT дубликаты записей удаляются изначально, поэтому нет необходимости использовать SELECT DISTINCT.
  • Никогда не используйте SELECT *: выбор ненужных столбцов приведет к пустой трате памяти и процессорного времени. Всегда лучше выбирать имена столбцов вместо * или дополнительных столбцов.
  • По возможности избегайте подзапросов. Подзапрос создает временные таблицы для хранения данных, а иногда создает временные таблицы на диске, тем самым замедляя выполнение запроса. Предпочитайте предложение WITH вложенным подзапросам.
  • Избегайте использования сортировки в подзапросе. Упорядочивание в подзапросах в большинстве случаев является избыточным и приводит к значительным проблемам с производительностью.
  • Не GROUP числа: избегайте группировки по столбцам типа DOUBLE или FLOAT, так как это может привести к непредвиденному поведению из-за проблем с округлением и точностью.
  • Сведите к минимуму использование SELF соединений. Самостоятельные соединения требуют больших вычислительных ресурсов и во многих случаях могут быть заменены оконной функцией.
  • Не объединяйте таблицы с условием ИЛИ: его можно оптимизировать, используя UNION ALL вместо соединений на основе OR.
  • Избегайте соединения с не равным условием. Когда запрос объединяется с оператором NOT EQUAL, он ищет все строки и использует полное сканирование таблицы, что крайне неэффективно.
  • Избегайте полнотекстового поиска. Когда запрос ищет ключевые слова с подстановочным знаком в начале, он не использует индекс, и базе данных поручается поиск всех записей для совпадения в любом месте в выбранном поле. Следовательно, при необходимости предпочтительнее использовать подстановочные знаки только в конце фразы.
SELECT user_name
FROM test
WHERE user_name LIKE '%abc%'
  • Использовать WHERE вместо HAVING: предпочтительнее использовать where вместо having, поскольку операторы HAVING вычисляются после операторов WHERE.
  • IN по сравнению с EXISTS: оператор IN дороже, чем EXISTS с точки зрения сканирования, особенно когда результатом подзапроса является большой набор данных.

Последние мысли

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

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

Попробуйте эти методы и дайте мне знать, насколько хорошо они работают для вас, в разделе комментариев ниже!

Надеюсь, вы найдете эту короткую статью полезной. Спасибо за чтение!

Want to Connect?
You can reach out to me at — Linkedin | Twitter | Github.