Оптимизация SQL-запросов (Часть 2)

26 июня 2014 в 15:18 Разработка

В данной статье рассматриваются рекомендации по разработке оптимальной структуры БД. В первой части статьи «Оптимизация SQL-запросов (Часть 1)» рассматриваются особенности запросов на выборку данных, виды индексов, использование планов запросов, различные подходы к оптимизации запросов.

6. Представления, временные таблицы, хранимые процедуры

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

Некоторые рекомендации по оптимизации хранимых процедур:

1. Включать в процедуры строку — SET NOCOUNT ON. С каждым DML выражением SQL Server возвращает количество обработанных записей. Устанавливая SET NOCOUNT ON, эта функцию будет отключена. Для хранимых процедур, содержащих множество запросов и/или циклов, данное действие может дать значительный прирост производительности, потому как количество трафика будет значительно снижено.
2. Использование имя схемы для доступа к объекту. Данная операция подсказывает серверу где искать объекты. При большом количестве баз, таблиц и хранимых процедур может повысить быстродействие.
3. Не использовать префикс «sp_» в имени процедур: если имя нашей процедуры начинается с «sp_», SQL Server в первую очередь будет искать в своей главной базе данных.

7. Рекомендации по разработке структуры БД

Лучший способ решения проблем с производительностью — просто не допустить их.

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

7.1. Выбор первичного ключа

Исторически сложилось так, что наиболее общим подходом в проектировке БД для идентификации конкретной строки использовалась целочисленная последовательность. Как правило, такая последовательность генерируется на стороне сервера в момент вставки новой строки. Такой подход подходит для многих приложений.

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

7.2. Создание индексов по внешним ключам

MS SQL автоматически НЕ создает индексы по внешним ключам.

Однако индексация таблиц по внешнему ключу может оказаться полезна по следующим причинам:

• Лучшая поддержка реляционных отношений для удалений из первичного/уникального ключа.
• Если отношения внешнего ключа определены как NO ACTION (для изменений/удалений), тогда строка, на которую ссылаются, не может быть удалена, поскольку это оставило бы такие строки-ссылки «осиротевшими». Поиск строк эффективнее с индексом по столбцу внешнего ключа!
• Если отношения внешнего ключа определены как CASCADE (для изменений/удалений) тогда если строка, на которую ссылаются, изменяется, все ссылающиеся на неё строки тоже должны измениться точно также (измениться, чтобы принять новое значение, или каскадно удалиться). Поиск изменённых строк эффективнее с индексом по столбцу внешнего ключа!
• Улучшение работы соединений. По многим описанным выше причинам, SQL Server может более эффективно находить соединяемые строки, когда таблицы соединяются по отношениям первичных/внешних ключей. Однако, это не всегда будет лучшим выбором индексов для соединений.

7.3. Денормализация БД

Иногда для оптимизации запросов может быть оправдана денормализация БД.

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

В такой ситуации может помочь:

• Денормализация путем сокращения количества таблиц. Лучше объединять в одну несколько таблиц, имеющих небольшой размер, содержащих редко изменяемую (как часто говорят, условно-постоянную или нормативно-справочную) информацию, причем информацию, по смыслу тесно связанную между собой.
• В общем случае, если в большом количестве запросов требуется объединять более пяти или шести таблиц, следует рассмотреть вариант денормализации базы данных.
• Денормализация путём ввода дополнительного поля в одну из таблиц. При этом появляется избыточность данных, требуются дополнительные действия для сохранения целостности БД.

Расчетные значения в запросе зачастую медленно выполняются, и потребляют много ресурсов запросы, в которых производятся какие-то сложные вычисления, особенно при использовании группировок и агрегатных функций (Sum, Max и т. п.). Иногда имеет смысл добавить в таблицу 1-2 дополнительных столбца, содержащих часто используемые (и сложно вычисляемые) расчетные данные.

В рассматриваемом примере можно добавить поле «общая стоимость заказа» и хранить его отдельным полем в таблице Order.

8. Примеры оптимизации запроса

8.1. Использование правильных типов данных

Пример запроса:

1. select *
2. from
3. [Order]
4. where
5. OrderNumber=500
6.  
7. SQL Server Execution Times:
8. CPU time = 250 ms, elapsed time = 252 ms.

Поле «OrderNumber» объявлено на типе varchar, поэтому при выполнении приведенного выше запроса, СУБД выполняет приведение типов.

Если переписать запрос следующим образом:

1. select *
2. from
3. [Order]
4. where
5. OrderNumber='500'

То запрос будет эффективнее.

8.2. Использование функций при составлении условий

Пример:

1. select
2. OrderNumber
3. from
4. [Order]
5. where
6. LEFT(OrderNumber, 1) = '5';
7.  
8. SQL Server Execution Times:
9. CPU time = 390 ms, elapsed time = 1686 ms.

Запрос отбирает все заказы с номером, начинающимся на 5.
Если переписать запрос следующим образом:

1. select
2. OrderNumber
3. from
4. [Order]
5. where
6. OrderNumber Like '5%';

То в плане Index Scan меняется на Index Seek, и запрос становится эффективнее.

8.3. Оптимизация с использованием индекса

Допустим, необходимо отобрать список заказов по определенному поставщику за определенный период.

1. select
2. OrderNumber
3. ,OrderDate
4. ,CustomerId
5. from
6. [Order]
7. where
8. CustomerId = '36e45241-b4d7-44d3-bfe4-1759fa9a7255'
9. and OrderDate between '01/01/2014'
10. and '01/31/2014'
11.  
12. (6649 row(s) affected)
13. SQL Server Execution Times:
14. CPU time = 436 ms, elapsed time = 3461 ms.

Добавим индекс по дате заказа.

1. (6649 row(s) affected)
2. SQL Server Execution Times:
3. CPU time = 187 ms, elapsed time = 339 ms.

Добавим составной индекс.

1. (6649 row(s) affected)
2. SQL Server Execution Times:
3. CPU time = 15 ms, elapsed time = 163 ms.

Таким образом, добавление нужного индекса может ускорить запрос в десятки раз.

8.4. Оптимизация с изменением текста запроса

Допустим, необходимо отобрать список завершенных заказов (по номеру заказа), имеющих общую стоимость более 1 млн, и получить следующие данные: номер заказа, наименование клиента и стоимость заказа.

Напишем такой запрос:

1. select
2. [Order].OrderNumber
3. ,[Customer].CustomerName
4. ,sum([Product].Price * [OrderProduct].Quantity)
5. from
6. [Order]
7. inner join [OrderProduct]
8. on [order].OrderId = [OrderProduct].OrderId
9. inner join [Product]
10. on [Product].ProductId = [OrderProduct].ProductId
11. inner join [Customer]
12. on [Customer].CustomerId = [Order].CustomerId
13. where (select
14. top 1 [OrderStatus].StatusId
15. from
16. [OrderStatus]
17. where
18. [OrderStatus].OrderId = [order].OrderId
19. order by
20. [OrderStatus].StatusDate DESC
21. ,[OrderStatus].StatusId DESC) = 3
22. group by
23. [Order].OrderNumber
24. ,[Customer].CustomerName
25. having
26. sum([Product].Price * [OrderProduct].Quantity) > 1000000

Статистика выполнения:

1. (7 row(s) affected)
2.  
3. SQL Server Execution Times:
4. CPU time = 67034 ms, elapsed time = 68534 ms.

Посмотрев план, обнаруживаем, что самая затратная операция — поиск статуса заказа через короткий SELECT.

Перепишем запрос через вложенные запросы.

1. select
2. [Order].OrderNumber
3. ,[Customer].CustomerName
4. ,sum([Product].Price * [OrderProduct].Quantity)
5. from
6. [Order]
7. inner join [OrderProduct]
8. on [order].OrderId = [OrderProduct].OrderId
9. inner join [Product]
10. on [Product].ProductId = [OrderProduct].ProductId
11. inner join [Customer]
12. on [Customer].CustomerId = [Order].CustomerId
13. inner join (select
14. max([OrderStatus].StatusDate) as MaxStatusDate
15. ,OrderId
16. from
17. [OrderStatus]
18. group by
19. OrderId) as OrderStatusDate
20. on [order].OrderId = [OrderStatusDate].OrderId
21. inner join [OrderStatus]
22. on [OrderStatus].OrderId = [OrderStatusDate].OrderId
23. and [OrderStatusDate].MaxStatusDate = [OrderStatus].StatusDate
24. where
25. [OrderStatus].StatusId = 3
26. group by
27. [Order].OrderNumber
28. ,[Customer].CustomerName
29. having
30. sum([Product].Price * [OrderProduct].Quantity) > 1000000

Результат:

1. (7 row(s) affected)
2.  
3. SQL Server Execution Times:
4. CPU time = 23197 ms, elapsed time = 24001 ms.

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

1. select
2. [Order].OrderNumber
3. ,[Customer].CustomerName
4. ,sum([Product].Price * [OrderProduct].Quantity)
5. from
6. [Order]
7. inner join [OrderProduct]
8. on [order].OrderId = [OrderProduct].OrderId
9. inner join [Product]
10. on [Product].ProductId = [OrderProduct].ProductId
11. inner join [Customer]
12. on [Customer].CustomerId = [Order].CustomerId
13. group by
14. [Order].OrderNumber
15. ,[Customer].CustomerName
16. having
17. sum([Product].Price * [OrderProduct].Quantity) > 1000000

Результат:

1. SQL Server Execution Times:
2. CPU time = 17160 ms, elapsed time = 17743 ms.

Т. е. определение статуса занимает порядка 20 % запроса. О чем и свидетельствует план:

Основное время уходит на расчет стоимости заказа: поиск всех элементов и умножение на цену.

Если бы общая стоимость заказа хранилась непосредственно в Order, то запрос был бы оптимальнее?

Проведем эксперимент: добавим стоимость заказа в таблицу Order.

1. alter table [Order] add
2. TotalCost numeric(18,2)
3. Go
4.  
5. Update [Order]
6. Set TotalCost = (
7. select
8. sum([Product].Price * [OrderProduct].Quantity)
9. from
10. [OrderProduct]
11. inner join [Product]
12. on [Product].ProductId = [OrderProduct].ProductId
13. where
14. [Order].OrderId = [OrderProduct].OrderId
15. )

Перепишем запрос:

1. select
2. [Order].OrderNumber
3. ,[Customer].CustomerName
4. ,sum([Order].TotalCost)
5. from
6. [Order]
7. inner join [Customer]
8. on [Customer].CustomerId = [Order].CustomerId
9. inner join (select
10. max([OrderStatus].StatusDate) as MaxStatusDate
11. ,OrderId
12. from
13. [OrderStatus]
14. group by
15. OrderId) as OrderStatusDate
16. on [order].OrderId = [OrderStatusDate].OrderId
17. inner join [OrderStatus]
18. on [OrderStatus].OrderId = [OrderStatusDate].OrderId
19. and [OrderStatusDate].MaxStatusDate = [OrderStatus].StatusDate
20. where
21. [OrderStatus].StatusId = 3
22. group by
23. [Order].OrderNumber
24. ,[Customer].CustomerName
25. having
26. sum([Order].TotalCost) > 1000000

Результат:

1. (7 row(s) affected)
2.  
3. SQL Server Execution Times:
4. CPU time = 20467 ms, elapsed time = 21066 ms.

Как видим, производительности особо не прибавилось. Основное время по прежнему занимает группировка. Попробуем группировать не по имени клиента, а по Id:

1. select
2. [Order].OrderNumber
3. ,[Order].CustomerId
4. ,sum([Product].Price * [OrderProduct].Quantity)
5. from
6. [Order]
7. inner join [OrderProduct]
8. on [order].OrderId = [OrderProduct].OrderId
9. inner join [Product]
10. on [Product].ProductId = [OrderProduct].ProductId
11. inner join (select
12. max([OrderStatus].StatusDate) as MaxStatusDate
13. ,OrderId
14. from
15. [OrderStatus]
16. group by
17. OrderId) as OrderStatusDate
18. on [order].OrderId = [OrderStatusDate].OrderId
19. inner join [OrderStatus]
20. on [OrderStatus].OrderId = [OrderStatusDate].OrderId
21. and [OrderStatusDate].MaxStatusDate = [OrderStatus].StatusDate
22. where
23. [OrderStatus].StatusId = 3
24. group by
25. [Order].OrderNumber
26. ,[Order].CustomerId
27. having
28. sum([Product].Price * [OrderProduct].Quantity) > 1000000
29.  
30. (7 row(s) affected)
31.  
32. SQL Server Execution Times:
33. CPU time = 17893 ms, elapsed time = 22562 ms.

Добавим индекс по номеру заказа и коду клиента:

1. SQL Server Execution Times:
2. CPU time = 13276 ms, elapsed time = 13876 ms.

Эффект появился.
Поменяем местами поля в индексе:

1. CREATE NONCLUSTERED INDEX IDX_OrderNumber
2. ON [Order] (CustomerId asc, OrderNumber Asc)
3. Go
4.  
5. SQL Server Execution Times:
6. CPU time = 10577 ms, elapsed time = 11159 ms.

Эффект усилился.

Для получения наименования поставщика обернем все во внешний запрос:

1. select
2. CustomerName
3. ,OrderNumber
4. ,TotalCost
5. from
6. [Customer]
7. inner join
8. (
9. select
10. [Order].OrderNumber
11. ,[Order].CustomerId
12. ,sum([Product].Price * [OrderProduct].Quantity) as TotalCost
13. from
14. [Order]
15. inner join [OrderProduct]
16. on [order].OrderId = [OrderProduct].OrderId
17. inner join [Product]
18. on [Product].ProductId = [OrderProduct].ProductId
19. inner join (select
20. max([OrderStatus].StatusDate) as MaxStatusDate
21. ,OrderId
22. from
23. [OrderStatus]
24. group by
25. OrderId) as OrderStatusDate
26. on [order].OrderId = [OrderStatusDate].OrderId
27. inner join [OrderStatus]
28. on [OrderStatus].OrderId = [OrderStatusDate].OrderId
29. and [OrderStatusDate].MaxStatusDate = [OrderStatus].StatusDate
30. where
31. [OrderStatus].StatusId = 3
32. group by
33. [Order].OrderNumber
34. ,[Order].CustomerId
35. having
36. sum([Product].Price * [OrderProduct].Quantity) > 1000000
37. ) as OrderCost
38. on OrderCost.CustomerId = [Customer].CustomerId
39.  
40. SQL Server Execution Times:
41. CPU time = 10780 ms, elapsed time = 11616 ms.

Таким образом, исходный запрос был оптимизирован с 67 секунд до 10 секунд.

9. Оптимизация БД

9.1. Обслуживание статистики индексов

Статистика индексов — ключевой момент для оптимизатора запроса в выборе плана выполнения.

Если статистика устареет и не будет отражать реальную картину с данными в таблице, то выбор индекса может быть неверен. Чтобы быть полезной, статистика должна содержать текущие реальные данные.

SQL Server позволяет обновлять статистику автоматически, без привлечения дополнительных усилий со стороны DBA. Проверить, установлен ли флаг автоматического обновления статистики, можно командой:

1. SELECT DATABASEPROPERTYEX('<dbname>','IsAutoUpdateStatistics')

Если результат равен 1 — опция автоматического обновления статистики включена. Установить опцию можно командой:

1. ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS ON

А выключить:

1. ALTER DATABASE dbname SET AUTO_UPDATE_STATISTICS OFF

В большинстве случаев рекомендуется оставлять эту функцию включенной. В этом случает SQL Server будет сам обновлять статистику, когда посчитает ее устаревшей. Алгоритм обновления полностью определяется SQL Server и зависит от количества обновлений, удалений и добавлений записей в таблицу. Если таблица имеет один миллион записей, и только 100 из них были изменены (0.01 %), вряд ли имеет смысл обновлять статистику, поскольку такие изменения в таблице вряд ли драматически поменяли общую картину данных.

Кроме того, если размер таблицы более 8 МБ (1 000 страниц), SQL Server не будет использовать все данные для вычисления статистики. Все эти ограничения разработаны для того, чтобы работа со обновлением статистики наносила как можно меньший удар на быстродействие сервера.

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

1. UPDATE STATISTICS [Order] [IDX_OrderNumber]

9.2. Дефрагментация БД

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

В SQL Server выделяют два типа фрагментации: внутренняя и внешняя.
Внутренняя подразумевает пустоты внутри страницы. Внешняя — непоследовательность связей страниц.

Если страницы не полностью заполнены данными, это приводит к дополнительным операциям I/O и «повышенному расходу» оперативной памяти, т. к. страницы в оперативной памяти есть зеркальное отражение страниц на диске.

В SQL Server есть несколько путей преодоления внутренней фрагментации. Один из этих методов состоит в том, чтобы использовать команду DBCC REINDEX для перестройки кластеризованных и некластеризованных индексов. После перестройки индексов страницы данных становятся логически непрерывными, и дисковый ввод/вывод минимизирован. К сожалению, внутренняя фрагментация — это только лишь часть проблемы фрагментации: выполнение DBCC REINDEX не сказывается на внешней фрагментации.

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

Для устранения внешней фрагментации используются специализированные утилиты.

10. Резюме

• Стратегии оптимизации запросов: можно использовать индексы, другие варианты запроса, сохранение промежуточных результатов.
• Выбор варианта оптимизации зависит от конкретного случая. При этом могут быть использованы сразу несколько подходов.
• Не все индексы одинаково полезны. При разработке индексов необходимо учитывать их селективность.
• Для проверки быстродействия запроса:
1. SET STATISTICS TIME ON
2. GO
• Для проверки статистики ввода/вывода:
1. SET STATISTICS IO ON
2. GO
• Для вывода плана запроса:

1. SET STATISTICS XML ON
2. GO

11. Источники

technet.microsoft.com/ru-ru/magazine/2007.11.sqlquery.aspx

www.lektorium.tv/lecture/?id=14561

В первой части статьи «Оптимизация SQL-запросов (Часть 1)» рассматриваются особенности запросов на выборку данных, виды индексов, использование планов запросов, различные подходы к оптимизации запросов.

query-optimizationquery-performancesqlsql-server