При оптимизации производительности разработчики и архитекторы часто упускают из виду настройку своих 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 триггеры
- Внешние ключи
Удачного обучения!
