Фундаментальное руководство по оптимизации SQL-запросов

Лучшие практики, которые заставили меня писать эффективные запросы

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

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

Предпочитайте UNION ALL вместо UNION

Если дубликаты не являются проблемой, ОБЪЕДИНЕНИЕ ВСЕ не будет их отбрасывать, а поскольку ОБЪЕДИНЕНИЕ ВСЕ не занимается удалением дубликатов, запрос будет более эффективным.

Используйте TRUNCATE вместо DELETE, чтобы удалить все строки в таблице.

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

Удалить индекс перед загрузкой массовых данных

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

В таких случаях предпочтительно удалять индексы перед загрузкой таблицы. После завершения загрузки заново создайте индексы в таблице.

Избегайте слишком большого количества подзапросов или соединений CTE

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

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

Это помогло мне сократить время выполнения запроса с 7+ часов до 10 минут. Это просто смешно!!!!!

Используйте сжатие данных, когда это возможно

Это очень хорошо работает для столбчатых хранилищ данных, таких как красное смещение и снежинка, а также для баз данных на основе строк, таких как SQL Server.

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

Используйте тот же тип данных в предложениях JOIN и WHERE

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

Убедитесь, что ваши запросы используют преимущества ИНДЕКСИРОВАНИЯ

Одна из самых распространенных проблем, с которой сталкиваются люди при возникновении проблем с производительностью запросов к базе данных, — это отсутствие адекватной индексации.

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

Избегайте коррелированных подзапросов, если это не требуется

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

Избегать

SELECT c.Name, c.City,
(
SELECT CompanyName 
FROM Company 
WHERE ID = c.CompanyID
) AS CompanyName 
FROM Customer c

Предпочтение

SELECT c.Name, c.City, co.CompanyName 
FROM 
Customer c 
LEFT JOIN 
Company co 
ON c.CompanyID = co.CompanyID

Используйте оператор равенства (=) вместо предложения LIKE

Предложения «=» и LIKE используются для сопоставления строк, имеющих определенное значение. Основное различие между ними заключается в том, что оператор LIKE используется для сопоставления подстановочных знаков, таких как %, для поиска неполных строк, тогда как оператор равенства «=» ищет точные совпадения.

Если вам нужно выбрать между ними, всегда отдавайте предпочтение оператору равенства («=»), так как он использует индексированные столбцы, что ускоряет поиск по значению, но если требование поиска связано с поиском по определенному шаблону, тогда НРАВИТСЯ можно использовать.

Избегайте объединения столбцов в предложении WHERE

По возможности избегайте конкатенации в предложении WHERE. Вам следует избегать объединения нескольких столбцов в предложении WHERE. Если есть конкатенация, разбейте запрос на несколько условий.

Избегать

SELECT name, surname 
FROM class 
WHERE name || surname = ‘Alexmercer’

Предпочтение

SELECT name, surname
FROM class 
WHERE name = ‘Alex’ 
AND surname = ‘mercer’

Избегайте SELECT DISTINCT для больших таблиц

Предложение SELECT DISTINCT позволяет получить уникальные записи из запроса, удалив повторяющиеся записи. Однако SELECT DISTINCT требует значительных вычислительных ресурсов. Желательно избегать его как можно больше, пока это не станет обязательным.

Подпишитесь на меня в Linkedin и Medium, чтобы узнать больше интересного. Ваше здоровье !!!

Посмотрите и другие мои посты. 😊