Как оптимизировать SQL-запросы?

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

В этом руководстве мы рассмотрим 8 основных советов по SQL для оптимизации вашего SQL-сервера.

Совет 1. Выберите правильный тип данных для столбца

Каждый столбец таблицы в SQL имеет связанный тип данных. Вы можете выбирать из целых чисел, дат, переменных, логических значений, текста и т.д. При разработке важно выбрать правильный тип данных. Числа должны быть числового типа, даты должны быть датами и т.д. Это чрезвычайно важно для индексации.

Давайте посмотрим на пример ниже.

SELECT employeeID, employeeName
FROM employee
WHERE employeeID = 13412;

Вышеупомянутый запрос извлекает идентификатор и имя сотрудника с идентификатором 13412. Что, если тип данных для employeeID — строка? Вы можете столкнуться с проблемами при использовании индексации, поскольку это займет много времени, когда это должно быть простое сканирование.

Совет 2: Табличные переменные и объединения

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

Давайте посмотрим на пример соединения:

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

Табличные переменные — это локальные переменные, которые временно хранят данные и обладают всеми свойствами локальных переменных. Не используйте табличные переменные в объединениях, как SQL видит их как одну строку. Несмотря на то, что они быстрые, табличные переменные плохо работают в соединениях.

Совет 3. Используйте условное предложение WHERE

Условные предложения WHERE используются для подмножества. Допустим, у вас есть такая ситуация:

-if SEQ_VAR in (1, 2, 3) & diff(DATE_VAR2, DATE_VAR1)≥ 0
— elif SEQ_VAR in (4, 5, 6) & diff(DATE_VAR2, DATE_VAR1) ≥1
— else diff(DATE_VAR2, DATE_VAR1) ≥2

С условным предложением WHERE это будет выглядеть так:

SELECT 
  DAT.ID_VAR,
  DAT.SEQ_VAR,
  DAT.NUM_VAR,
  DATE_VAR1,
  DATE_VAR2,
  TRUNC(DATE_VAR2) - TRUNC(DATE_VAR1) AS LAG_IN_DATES

FROM 
  CURRENT_TABLE      DAT 
WHERE
  (TRUNC(DATE_VAR2) - TRUNC(DATE_VAR1)) >= CASE WHEN SEQ_VAR IN (1,2,3) THEN 0 WHEN SEQ_VAR IN (4,5,6) THEN 1 ELSE 2 END 
ORDER BY ID_VAR, SEQ_VAR

Совет 4: используйте SET NOCOUNT ON

При выполнении операций INSERT, SELECT, DELETE и UPDATE, используйте SET NOCOUNT ON. SQL всегда возвращает соответствующее количество строк для таких операций, поэтому, когда у вас есть сложные запросы с большим количеством соединений, это может повлиять на производительность.

С SET NOCOUNT ON SQL не будет подсчитывать затронутые строки и улучшить производительность.

В следующем примере мы предотвращаем отображение сообщения о количестве затронутых строк.

USE AdventureWorks2012;  
GO  
SET NOCOUNT OFF;  
GO  
-- Display the count message.  
SELECT TOP(5)LastName  
FROM Person.Person  
WHERE LastName LIKE 'A%';  
GO  
-- SET NOCOUNT to ON to no longer display the count message.  
SET NOCOUNT ON;  
GO  
SELECT TOP(5) LastName  
FROM Person.Person  
WHERE LastName LIKE 'A%';  
GO  
-- Reset SET NOCOUNT to OFF  
SET NOCOUNT OFF;  
GO

Совет 5: Избегайте ORDER BY, GROUP BY и DISTINCT

Использование ORDER BY, GROUP BYи DISTINCT только в случае необходимости. SQL создает рабочие таблицы и помещает туда данные. Затем он организует данные в рабочей таблице на основе запроса и затем возвращает результаты.

Совет 6. Полностью уточняйте имена объектов базы данных

Цель использования полностью определенных имен объектов базы данных — устранить двусмысленность. Полное имя объекта выглядит так:

DATABASE.SCHEMA.OBJECTNAME.

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

Поэтому вместо использования такого оператора:

SELECT * FROM TableName

Вам следует использовать:

SELECT * FROM dbo.TableName

Совет 7. Узнайте, как полностью защитить свой код

Базы данных хранят всевозможную информацию, что делает их основными целями атак. Распространенные атаки включают SQL-инъекции, когда пользователь вводит инструкцию SQL вместо имени пользователя и извлекает или изменяет вашу базу данных. Примеры SQL-инъекций:

textuserID = getRequestString("userID");
textSQL = "SELECT * FROM Users WHERE userID = " + textuserID;

Допустим, у вас есть это, вы textuserIDполучите ввод от пользователя. Вот как это может пойти не так:

SELECT * FROM Users WHERE userID = 890 OR 1=1;

Поскольку 1=1 всегда верно, он будет извлекать все данные из таблицы Users.

Вы можете защитить свою базу данных от SQL-инъекций, используя параметризованные операторы, проверки ввода, очистку ввода и т. Д. Как вы защищаете свою базу данных, зависит от СУБД. Вам нужно будет разобраться в своей СУБД и ее проблемах безопасности, чтобы вы могли писать безопасный код.

Совет 8: используйте LAG и LEAD для последовательных строк

Функция LAG позволяет запрашивать более одной строки в таблице, не вступая в таблицу к себе. Он возвращает значения из предыдущей строки таблицы.

LAG(expression [,offset[,default_value]]) OVER(ORDER BY columns)

Функция LEAD делает то же самое, но и для следующей строки.

LEAD(expression [,offset[,default_value]]) OVER(ORDER BY columns)

Отказ от использования самостоятельных соединений повышает производительность, поскольку уменьшается количество операций чтения. Но, вы должны проверить, как LEAD и LAG влияют на производительность запросов.

Что изучать дальше

В этой статье мы рассмотрели несколько важных советов по SQL, но всегда есть чему поучиться. Вот несколько хороших следующих шагов:

  • Оптимизация просмотров
  • Вложенные запросы
  • INSERT триггеры
  • Внешние ключи

Удачного обучения!

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.