В библиотеку

Методы оптимизации запросов к SQL Server - Советы для написания эффективных и быстрых запросов

Navita Kumari

Инновационный центр - Группа организаций Baba Farid

Автор перевода: Прищепа В.В.

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

I. ВВЕДЕНИЕ

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

II. ОБЗОР ВЫПОЛНЕНИЯ ЗАПРОСОВ С ИСПОЛЬЗОВАНИЕМ СТАТИСТИКИ ЧТЕНИЯ/ЗАПИСИ

Важным параметром является количество логических операций чтения производящихся по запросу. Возможность просматривать этот параметр предусмотрена в SQL Server Management Studio. Для определения числа логических операций чтения, вы можете включить или выключить отображение параметра STATISTICS IO с помощью такого запроса:

SET STATISTICS IO ON

Рассмотрим следующий запрос:

SELECT * FROM tablename

В окне результата SQL Server Management Studio вернулось следующее сообщение: "Table ‘tablename’. Scan count 1, logical reads 33, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0".

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

III. ОБЩИЕ РЕКОМЕНДАЦИИ ПО ОПТИМИЗАЦИИ ЗАПРОСОВ

Используйте конкретные имена столбцов вместо * в запросе SELECT

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

Пример:

SELECT col_1, col_2, col_3, col_4, subject FROM table_name;

вместо:

SELECT * FROM table_name.

Используйте альтернативные методы для возврата общего количества строк таблицы вместо COUNT (*)

SELECT COUNT (*) делает полное сканирование таблицы, это может занять много времени для больших таблиц. Если нам нужно узнать количество строк таблицы, мы можем использовать альтернативный способ – системную таблицу sysindexes. В ней присутствует столбец ROWS, содержащий общее количество строк для каждой таблицы в системе. Таким образом, мы можем использовать следующий оператор выбора:

SELECT rows FROM sysindexes
WHERE id = OBJECT_ID ('table_name') AND indid < 2.

Старайтесь избегать предложения HAVING в операторе SELECT

Его можно использовать, как фильтр в маленьких выборках строк.

Пишите:

SELECT Col_1, count (Col_1) FROM table_name
WHERE col_1!= 'testvalue1' AND col_1!= 'testvalue1'
GROUP BY col_1;

вместо:

SELECT Col_1, count (Col_1) FROM table_name
GROUP BY Col_1
HAVING Col_1!= ‗testvalue1‘ AND Col_1!= 'testvalue2'.

Постарайтесь свести к минимуму количество подзапросов

Пишите:

SELECT col_1 FROM table_name1
WHERE (col_2, col_3) = (SELECT MAX (col_2), MAX (col_3) FROM table_name2)
AND col_4 = 'testvalue1';

вместо:

SELECT col_1 FROM table_name1
WHERE col_2 = (SELECT MAX (col_2) FROM table_name2)
AND col_3 = (SELECT MAX (col_3) FROM table_name2)
AND col_4 = 'testvalue1'.

Используйте операторы EXISTS и IN с осторожностью и правильно:

Обычно IN имеет самую низкую производительность, он является эффективным только тогда, когда большинство критериев фильтрации помещаются в подзапрос.

EXISTS эффективен, когда большинство критериев фильтрации находятся главном запросе.

Например:

SELECT * FROM table1 t1
WHERE EXISTS (SELECT * FROM table2 t2 WHERE t2.col_id = t1.col_id);

вместо:

SELECT * FROM table1 t1
WHERE t1.col_id IN (SELECT t2.col_id FROM table2 t2).

Используйте EXISTS вместо DISTINCT при запросе с объединением таблиц, которые имеют отношения один-ко-многим

Например:

SELECT d.col_id, d.col2 FROM table1 d
WHERE EXISTS (SELECT 'X' FROM table2 e WHERE e.col2 = d.col2);

вместо:

SELECT DISTINCT d.col_id, d.col2 FROM table1 d, table2 e
WHERE e.col2 = e.col2.

Попробуйте использовать UNION ALL вместо UNION, если это возможно

UNION ALL работает быстрее, чем UNION, так как UNION ALL не считает дубликаты. Инструкция UNION проверяет таблицу на наличие дубликатов строк даже если они отсутствуют.

Пример:

SELECT id, col1 FROM table1
UNION ALL SELECT id, col1 FROM table2;

вместо:

SELECT id, col1, col2 FROM table1
UNION SELECT id, col1 FROM table2.

Тщательно подбирайте условия в инструкции WHERE

Например, для сравнения чисел пишите:

SELECT id, col1, col2 FROM table
WHERE col2 > 10;

вместо:

SELECT id, col1, col2 FROM table
WHERE col2 != 10.

Для сравнения строк:

SELECT id, col1, col2 FROM table
WHERE col1 LIKE 'Nav%';

вместо:

SELECT id, col1, col2 FROM table
WHERE SUBSTR(col1,1,3) = 'Nav'.

Для сравнения чисел в диапазоне:

SELECT Col1, Col2 FROM table
WHERE Col3 BETWEEN MAX (Col3) and MIN (Col3);

вместо:

SELECT Col1, Col2 FROM table
WHERE Col3 >= MAX (Col3) and Col3 <= MIN (Col3).

Применяйте модифицированное значение колонки в инструкции WHERE только при необходимости.

Пример:

SELECT id, Col1, Col2 FROM table
WHERE Col2 < 25000;

вместо:

SELECT id, Col1, Col2 FROM table
WHERE Col2 + 10000 < 35000.

IV. ЕЩЕ НЕСКОЛЬКО СОВЕТОВ ПО ОПТИМИЗАЦИИ ЗАПРОСОВ/ТАБЛИЦ/ХРАНИМЫХ ПРОЦЕДУР

  • Таблица должна иметь минимум один кластеризованный индекс и соответствующее число не кластеризованных индексов;
  • Избегайте использования триггеров, если это возможно. Включите логику триггера в хранимую процедуру;
  • Таблица должна иметь ключевое поле;
  • Старайтесь использовать переменные таблицы вместо временных. Переменные занимают меньше системных ресурсов и ресурсов логов;
  • Избегайте использования VIEW, постарайтесь заменить их таблицами;
  • Избегайте инструкции DISTINCT, используйте ее только если это действительно необходимо;
  • Используйте TOP в иснтрукции SELECT, если необходимо выбрать некоторое количество строк в начале таблицы;
  • Оформите повторяющийся код в пользовательскую процедуру. Это поможет улучшить производительность, ускорить вашу работу, уменьшить сетевой трафик;
  • Использование TRUNCATE вместо DELETE позволит ускорить удаление строк из таблицы, потому что удаление происходит без записи информации в лог-файл;
  • Избегайте использования курсоров, если это возможно, они сильно замедляют производительность;
  • Когда разрабатывается запрос с подзапросами:
    • Используйте коррелированный подзапрос только тогда, когда возвращаемый результат будет относительно небольшим и/или другие критерии быстродействия подзапроса будут эффективными;
    • Используйте не коррелированные подзапросы при работе с большими таблицами, из которых ожидается большой результат и/или подзапрос имеет низкие показатели эффективности;
    • Убедитесь в том, что несколько подзапросов расположены в наиболее эффективном порядке;
    • Переписывание подзапроса с JOIN иногда может повысить эффективность;
  • Для хранения символьных и строковых данных используйте char/varchar вместо nchar/nvarchar, если нет необходимости в использовании UNICODE. В первом случае для хранения символов используется один байт, во втором – два;
  • Можно попытаться использовать инструкцию RETURN для возвращения целочисленного значения вместо того, чтобы это значение было частью результирующего набора данных;
  • Очистите систему от неиспользуемых индексов, они занимают место на диске и замедляют операции DML;
  • Создавайте индексы для целочисленных полей, это способствует меньшему объему индекса на диске, меньшему количеству операций чтения при использовании индекса;
  • Если часто используется объединение одних и тех же таблиц, то стоит создать индекс для объединяемых столбцов.

V. ВЫВОДЫ

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

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

ССЫЛКИ

  1. Sunderi Dejan, Microsoft SQL Server 2005 Stored Procedure Programming in T-SQL & .NET, Third Edition, McGraw-Hill, 2006, ISBN:0072262281.
  2. Dave Pinal, SQL SERVER – Optimization Rules of Thumb – Best Practices – Reader's Article. April 26, 2008. http://blog.sqlauthority.com/2008/04/26/sql-server-optimization-rules-ofthumb-best-practices-readers-article/
  3. Andrei Volkov, SQL Server Optimization, http://msdn.microsoft.com/enus/library/aa964133(v=sql.90).aspx.
  4. SQL Server 2005 Books Online, Introducing SQL Trace. http://technet.microsoft.com/en-us/library/ms191006.aspx.
  5. SQL Server 2005 Books Online, Understanding plan forcing. http://msdn2.microsoft.com/en-us/library/ms186343.aspx.
  6. SQL Tuning or SQL Optimization. http://beginner-sql-tutorial.com/sqlquery-tuning.htm.
  7. SQL Server Optimization Tips. http://santhoshgudise.weebly.com/uploads/8/5/4/7/8547208/sql_server_optimization_tips-1.doc.
  8. Optimize MS SQL Server. http://omarabid.hubpages.com/hub/OptimizeMS-SQL-Server.
  9. SQL Spark. http://sqlspark.blogspot.in/2011/07/best-ways-to-write-sqlquery.html.
  10. MySQL Load Balancing PHP script. http://www.f1teksolutions.com/.
  11. What are the most common SQL Optimizations. http://stackoverflow.com/questions/1332778/what-are-your-most-commonsql-optimizations.
  12. Index Optimization Tips. http://www.mssqlcity.com/Tips/tipInd.htm.
  13. Review, Change, or Delete Transferred or Imported data. http://turbotax.intuit.com/support/iq/Import/Review--Change--or-DeleteTransferred-or-Imported-Data/GEN12079.html.
В библиотеку