SQL-Ex blog

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

Хотя нет прямого пути определения лучшей производительности, мы можем выбирать различные способы для улучшения производительности запросов SQL, которые относятся к разным категориям, таким как создание индексов, использование соединений, замена подзапроса использованием JOIN и т.д.

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

1. Используйте EXISTS вместо IN для проверки существования данных.


2. Избегайте использования оператора SELECT *. Перечисляйте имена требуемых столбцов.


3. Выбирайте подходящий тип данных. Например, для хранения строк используйте varchar вместо типа данных text (varchar(max)). Используйте varchar(max), когда вам необходимо хранить большие данные (более 8000 символов).


4. Если возможно, избегайте nchar и nvarchar, поскольку эти типы данных занимают вдвое больше памяти по сравнению с char и varchar.


5. Избегайте использования NULL в полях фиксированной длины. Если NULL необходим, используйте поля переменной длины (varchar), которые занимают меньше места для хранения NULL.


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


7. Создавайте кластеризованный и некластеризованные индексы.


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


9. Наиболее селективные столбцы должны следовать самыми левыми в некластеризованном индексе.


10. Удаляйте неиспользуемые индексы.


11. Лучше создавать индексы на целочисленных столбцах, а не на символьных. Целочисленные значения потребляют меньше накладных расходов, чем символьные значения.


12. Используйте соединения вместо подзапросов.


13. Используйте выражения WHERE для ограничения результирующих таблиц, создаваемых соединениями.


14. Используйте TABLOCKX при вставке в таблицу и TABLOCK при слиянии.


15. Используйте WITH (NOLOCK) при выборке данных из таблиц.


16. Используйте SET NOCOUNT ON и TRY- CATCH во избежание тупика.


17. Избегайте курсоров по причине их низкой производительности.


18. Используйте табличные переменные вместо временных таблиц. Использование временных таблиц требует взаимодействия с базой данных TempDB, что связано с затратами времени.


19. Где это возможно, используйте UNION ALL вместо UNION.


20. Указывайте имя схемы перед именем объекта.


21. Для часто востребованных данных и сложных запросов используйте хранимые процедуры.


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


23. Избегайте использования префикса "sp_" в именах пользовательских хранимых процедур, поскольку SQL Server сначала будет искать такую процедуру в базе данных master, а уже потом в базе данных текущей сессии.


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


25. Избегайте многооператорных табличнозначных функций (TVF). Эти функции более затратны по сравнению со встроенными (inline) TVF.

Из сказанного выше понятно, что в SQL Server есть много этапов, которые вы можете выполнить для улучшения производительности ваших запросов и в целом базы данных. Много этапов применимы и в других РСУБД, например, в Oracle.

Вот некоторые основные советы для настройки производительности Oracle SQL:

1. Используйте EXPLAIN PLAN или AUTOTRACE для анализа плана выполнения ваших запросов и обнаружения проблем при том способе, который используется для их исполнения.


2. Используйте хинт INDEX для указания индекса, который следует использовать оптимизатору для этого конкретного запроса.


3. Рассмотрите возможность использования хинтов FULL или INDEX_FFS, чтобы принудить оптимизатор использовать полное сканирование таблицы или быстрое полное сканирование индекса соответственно.


4. Используйте хинт OPTIMIZER_MODE для указания конкретного режима оптимизации запроса.


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


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


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


8. Используйте SQL Tuning Advisor для обнаружения и исправления любых потенциальных проблем производительности в ваших операторах SQL.


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


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

Ссылки по теме

1. Почему не следует использовать SELECT * в рабочих системах (никогда!)


2. Когда использовать CHAR, VARCHAR или VARCHAR(MAX)


3. Основы использования хинта NOLOCK в SQL Server


4. CTE или временные таблицы, что лучше?


5. О хранилище запросов, навязывании плана и табличных переменных


6. Блокировки, блокирование и тупики в SQL Server


7. Что такое материализованное представление, и зачем его использовать?


8. Как решить, нужно ли вам секционировать таблицы