Оптимизация SQL-запросов

Tsql теория > Оптимизация SQL-запросов
01.11.2017 12:56:28



Статья:

Введение

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

1. Демонстрационная база данных

Для демонстрации запросов в данном докладе потребуется база данных. Допустим, необходимо автоматизировать следующую предметную область: у торгового предприятия есть различные товары, которые продаются различным заказчикам. Необходимо обеспечить хранение данных по заказам клиентам, включая заказанные товары, и о статусах заказа, развернутых во времени. Создадим упрощенную базу данных.
БД имеет следующую структуру:

База наполнена тестовыми данными:

  • Status — 3 записи
  • Customer — 3 записи
  • Product — 10 записей
  • Order — 3 млн. записей.
  • OrderStatus — 9 млн. записей.
  • OrderProduct — 2,5 млн. записей

2. Запросы на выборку данных

Запросы на выборку данных реализуются с помощью конструкции SELECT языка SQL.
Запрос в MS SQL имеет следующий синтаксис:


  1. [ WITH <common_table_expression>]
  2. SELECT select_list [ INTO new_table ]
  3. [ FROM table_source ] [ WHERE search_condition ]
  4. [ GROUP BY group_by_expression ]
  5. [ HAVING search_condition ]
  6. [ ORDER BY order_expression [ ASC | DESC ] ]

2.1. Типы соединений таблиц

Если запрос адресован к нескольким таблицам БД, то выполняется соединение этих таблиц по определенным полям.
Существует несколько типов соединений:
Внутреннее (INNER JOIN)


  1. select o.OrderNumber
  2. from [Order] o inner join [Product] p on
  3. p.ProductId=o.ProductId

Внешнее левое/правое (LEFT/RIGHT JOIN)


  1. select o.OrderNumber
  2. from [Order] o left join [Product] p on
  3. p.ProductId=o.ProductId

Перекрестное (CROSS JOIN)
(Используется редко)

2.2. Вложенные запросы

Вложенный запрос представляет собой запрос типа select from select
Пример:


  1. select OrderId from
  2. (
  3. select OrderId, count(0) as ProductCount from [OrderProduct]
  4. group by OrderId
  5. ) as SubQuery
  6. where SubQuery.ProductCount < 1

2.3. Короткие и соотнесенные запросы

Пример короткого селекта:


  1. select
  2. OrderId,
  3. (select [Customer].CustomerName from [Customer] where [Customer].CustomerId = [Order].CustomerId)
  4. from [Order]

Пример соотнесенного запроса:


  1. select
  2. Count(OrderId)
  3. from [Order]
  4. where
  5. (select [Customer].CustomerName from [Customer] where [Customer].CustomerId = [Order].CustomerId) = ''ОАО РЖД''

В MS SQL короткие и соотнесенные запросы могут быть реализованы с помощью конструкции OUTER/CROSS APPLY.


  1. select
  2. OrderId, CustomerName
  3. from [Order] CROSS APPLY (select [Customer].CustomerName from [Customer] where [Customer].CustomerId = [Order].CustomerId) cus
  4. where CustomerName = ''ОАО РЖД''

Существует два типа оператора APPLY: CROSS APPLY и OUTER APPLY. Оператор CROSS APPLY возвращает только строки из внешней таблицы, которые создают результирующий набор из возвращающей табличное значение функции. Оператор OUTER APPLY возвращает и строки, которые формируют результирующий набор, и строки, которые этого не делают, со значениями NULL в столбцах, созданных возвращающей табличное значение функцией.

3. Индексы

3.1. Виды индексов MS-SQL

В MS SQL Индексы могут быть двух типов: кластерные и не кластерные.
Если упрощенно, то кластерный индекс отличается от не кластерного тем, что в листьях этого индекса содержатся не ссылки на записи в таблице, а сами записи. Таким образом, для таблицы допустим только один кластерный индекс.
Скорость доступа к строкам при указании условия where, совпадающего с условием индексирования для кластерного индекса, будет выше, чем при поиске записей по простому индексу.
Если в кластерный индекс включены часто обновляемые поля, то это отрицательно скажется на производительности, т. к. обновления будут приводить к логическому переупорядочиванию данных.
Если из таблицы, имеющей кластерный индекс, часто удаляются записи, то это приводит к сильной фрагментации таблицы.
MS SQL по умолчанию создает кластерные индексы по первичному ключу.

3.2. Ограничения MS SQL при создании индексов

При создании индекса максимальная суммарная длина полей допускается равной 900 байт. Причем индекс может быть создан по полям, типы которых допускают большую длину полей, но при этом хранящиеся данные обязательно должны быть меньше.
Т.е., например, в таблице имеется 2 поля varchar(500), MSSQL допускает создание индекса при условии, что реальная длина записей по этим полям не превышает 900. При наличии такого индекса в таблице, при попытке вставки (или обновления) данных, если сумма реальных полей будет превышать 500 байт, то будет выдано сообщение об ошибке.
Для хранения каждого символа в типах nvarchar и nchar MS SQL использует 2 байта.

4. Планы запросов

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

4.1. Анализ плана запроса

Ядро SQL Server Database Engine может показывать, каким образом оно переходит к таблицам и использует индексы для доступа к данным. Это называется выводом плана выполнения. Для проведения анализа медленно выполняемого запроса полезно изучить план выполнения запроса, чтобы определить причину проблемы.
Планы выполнения запросов в MS SQL отображаются cредой SQL Server Management Studio. При этом отображается наилучший план выполнения, используемый ядром Database Engine для отдельных инструкций языка DML и Transact-SQL. В этом плане содержатся сведения о процессе компиляции хранимых процедур и о вызовах хранимых процедур произвольной глубины вложенности. Например, при выполнении инструкции SELECT можно увидеть, что Database Engine выполняет просмотр таблицы для получения данных. Выполнение инструкции SELECT может также показать, что просмотр индекса будет использоваться, если Database Engine определит, что просмотр индекса является наиболее быстрым способом получения данных из таблицы.
Таким образом, выбор оптимального плана во многом зависит от наличия в базе индексов и их оптимальности.

При выполнении запроса в MSSQL есть возможность поиска “отсутствующих” индексов. Т. е. MS SQL подсказывает, какие индексы можно создать, чтобы данный конкретный запрос выполнялся быстрее.
Пример:


  1. SET STATISTICS XML ON
  2. GO
  3. select * from [OrderStatus]
  4. where statusdate < GETDATE()
  5. GO

Запрос выдает рекомендации на создание индекса:


  1. <MissingIndexes>
  2. <MissingIndexGroup Impact="74.5401">
  3. <MissingIndex Database="" Schema="[dbo]" Table="[OrderStatus]">
  4. <ColumnGroup Usage="INEQUALITY">
  5. <Column Name="[StatusDate]" ColumnId="3" />
  6. </ColumnGroup>
  7. <ColumnGroup Usage="INCLUDE">
  8. <Column Name="[OrderId]" ColumnId="1" />
  9. <Column Name="[StatusId]" ColumnId="2" />
  10. </ColumnGroup>
  11. </MissingIndex>
  12. </MissingIndexGroup>
  13. </MissingIndexes>

4.2. Способы применения индекса в планах запроса

Основные типы получения записей индекса это Seek и Scan.
Index Scan получает все записи индекса, указанного в столбце Argument. Если необязательный предикат WHERE:() появится в столбце Argument, то возвращаются только те строки, которые удовлетворяют условию, указанному в этом предикате.
Оператор Index Seek использует возможности поиска по индексам для получения строк из индекса. С самого начала поиск обрабатывает только те строки, которые квалифицированы, и страницы, которые содержат эти квалифицированные строки, поэтому стоимость пропорциональна числу квалифицированных строк и содержащих их страниц, а не к общему числу строк в таблице. Таким образом, поиск является более эффективной стратегией, если мы имеем дело с очень селективным поисковым предикатом; то есть если мы имеем поисковый предикат, который отсекает большую часть таблицы.

4.3. Способы изменения плана

В MS SQL при выполнении запросов можно указать специальные «подсказки» (hint), с помощью которых можно повлиять на план запроса.
Оптимизатор запросов SQL Server обычно автоматически выбирает наилучший план выполнения запроса. Поэтому подсказки, в том числе <подсказки_по_соединению>, рекомендуется использовать только опытным пользователям и администраторам базы данных в случае крайней необходимости.
Примечание. В некоторых СУБД, например Firebird, можно косвенно влиять на план запроса, например добавлением «+0» к полю таблицы. В этом случае оптимизатор перестает использовать индекс.

4.4. Кэширование и повторное использование плана

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

4.5. Проблема параметров при кэшировании запросов

Если к серверу выполняется несколько однотипных параметризованных запросов, то сервер строит план только для первого запроса, далее этот план попадает в кэш, и остальные запросы выполняются с уже кэшированным планом.
Рассмотрим 2 запроса:


  1. select * from [Order] where orderdate > ''01/01/2001''


  1. select * from [Order] where orderdate > ''01/01/2041''

Для выполнения этих запросов используются разные планы, т. к. при построении плана, MS SQL анализирует количество возвращаемых данных.
Если данный запрос сделать параметризованным:


  1. select * from [Order] where orderdate > @PDate

то план запроса будет определятся самым первым запросом.
Это демонстрирует следующий скрипт:


  1. declare @PDate datetime = ''01/01/2041''
  2. select * from [Order] where orderdate > @PDate
  3. go
  4.  
  5. select * from [Order] where orderdate > ''01/01/2041''
  6. go
  7.  
  8. (0 row(s) affected)
  9. Table ''Order''. Scan count 1, logical reads 55446, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  10.  
  11. (0 row(s) affected)
  12. Table ''Order''. Scan count 1, logical reads 17992, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


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

5. Подходы к оптимизации запросов

5.1. Поиск неоптимальных запросов

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


  1. SELECT
  2. substring(text,qs.statement_start_offset/2
  3. ,(CASE
  4. WHEN qs.statement_end_offset = -1 THEN len(convert(nvarchar(max), text)) * 2
  5. ELSE qs.statement_end_offset
  6. END - qs.statement_start_offset)/2)
  7. ,qs.plan_generation_num as recompiles
  8. ,qs.execution_count as execution_count
  9. ,qs.total_elapsed_time - qs.total_worker_time as total_wait_time
  10. ,qs.total_worker_time as cpu_time
  11. ,qs.total_logical_reads as reads
  12. ,qs.total_logical_writes as writes
  13. FROM sys.dm_exec_query_stats qs
  14. CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
  15. LEFT JOIN sys.dm_exec_requests r
  16. ON qs.sql_handle = r.sql_handle
  17. ORDER BY 3 DESC

5.2. Статистика индексов

Для индекса существует понятие плотности распределения. Если каждое значение в таблице уникальное, то плотность будет 1/<число записей>. Например, если в таблице 100 записей, то плотность будет равна 1%. Теория говорит, что чем меньше плотность, тем лучше – это увеличивает избирательность, а, следовательно, и ценность построенного индекса.
Например, если колонка содержит только 3 значения, плотность распределения будет равна 33.3%, что показывает бесполезность построения индекса по данному полю. Индексы занимают место на диске и в оперативной памяти и отнимают быстродействие. В идеале, самый лучший индекс имеет плотность распределения равную единице, деленной на количество записей в таблице — все записи уникальны.
При построении индексов, обращайте внимание на плотность распределения – если она превышает 10%, то индекс можно считать бесполезным. Сканирование по таблице в таком случае будет более эффективным.

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


  1. DBCC SHOW_STATISTICS ([Order], IDX_ORDERNUMBER)

5.3. Фильтрация в индексах

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


  1. select * from OrderProduct where Quantity > 9900

Результат:


  1. (77785 row(s) affected)
  2.  
  3. SQL Server Execution Times:
  4. CPU time = 452 ms, elapsed time = 488 ms.

Создадим индекс по полю Quantity:


  1. CREATE NONCLUSTERED INDEX OrderVIP
  2. ON [OrderProduct] (Quantity)

Тот же запрос:


  1. SQL Server Execution Times:
  2. CPU time = 31 ms, elapsed time = 335 ms.

Создадим фильтрованный индекс:


  1. CREATE NONCLUSTERED INDEX OrderVIP
  2. ON [OrderProduct] (Quantity)
  3. where [OrderProduct].Quantity >9900
  4. GO

Результат:


  1. SQL Server Execution Times:
  2. CPU time = 16 ms, elapsed time = 338 ms.

Кроме того, фильтрованный индекс создается гораздо быстрее.

5.4. Индексы с включенными полями

Индекс с неключевыми столбцами может значительно повысить производительность запроса, когда все столбцы запроса включены в индекс как ключевые или неключевые. Производительность повышается благодаря тому, что оптимизатор запросов может найти все значения столбцов в этом индексе. При этом нет обращения к данным таблиц или кластеризованных индексов, что приводит к меньшему количеству дисковых операций ввода-вывода.
Пример. Допустим, необходимо найти номера и даты заказов по определенному поставщику.
Запрос:


  1. select CustomerId, OrderNumber, OrderDate from [Order]
  2. where CustomerId = (select top 1 customerid from customer);
  3.  
  4. (262144 row(s) affected)
  5. (1 row(s) affected)
  6. SQL Server Execution Times:
  7. CPU time = 328 ms, elapsed time = 1379 ms.

Создаем индекс на поле CustomerId:


  1. CREATE NONCLUSTERED INDEX IDX_OrderCustomer
  2. ON [Order] (CustomerId)

Результат такой же. MSSQL индекс не использует. Оптимизатор решает, что быстрее сделать Scan по первичному ключу, т.к. селективность индекса маленькая.

Создаем индекс с включенными полями:


  1. CREATE NONCLUSTERED INDEX IDX_OrderCustomer
  2. ON [Order] (CustomerId)
  3. include (OrderNumber, OrderDate)

Время выполнения запроса уменьшается, индекс попадает в план:


  1. SQL Server Execution Times:
  2. CPU time = 63 ms, elapsed time = 1426 ms.

5.5. Дополнительные команды при создании индексов

Команда CREATE INDEX имеет дополнительные параметры. Рассмотрим некоторые из них, которые в дальнейшем могут повлиять на производительность запросов.
ASC|DESC
ASC значит что индекс будет построен по возрастанию ключей. DESC соответственно – по убыванию. Эта опция не дает никакой разницы на поиск данных, но оказывает существенное влияние на скорость выполнения ORDER BY опции в запросах.
Пример.


  1. select OrderDate from [Order]
  2. order by OrderDate Desc

Вариант 1.


  1. CREATE NONCLUSTERED INDEX IDX_OrderDate
  2. ON [Order] (OrderDate)
  3. SQL Server Execution Times:
  4. CPU time = 187 ms, elapsed time = 2562 ms.

Вариант 2.


  1. CREATE NONCLUSTERED INDEX IDX_OrderDate
  2. ON [Order] (OrderDate Desc)
  3. SQL Server Execution Times:
  4. CPU time = 156 ms, elapsed time = 2563 ms.

SORT_IN_TEMPDB
Полезна при создании индексов. Эта опция может увеличить производительность системы во время создания индексов. Индексы создаются в два этапа. На первом создается временный набор данных с отсортированными ключами для некластерного индекса. На втором – окончательный результат переносится на место его хранения в базе данных. Без указания данной опции временный результат создается в той же file group, где и будет создан индекс. При указанной опции временный результат будет находиться в базе данных Tempdb. Преимущества: индекс будет менее фрагментирован, временную базу можно поместить на другой диск и тем самым увеличить скорость чтения/записи. Недостаток: требуется больше памяти на дисках и надо уделять дополнительное внимание базе данных Tempdb.

5.6. Оптимизация композитных индексов

В создании композитного (сложного) индекса участвуют несколько полей таблицы. При создании индекса следует обращать внимание на порядок следования полей в индексе.
Например, если создается индекс по полям Field1, Field2, то он может быть применен только в запросе, где в критериях используются оба этих поля. Также этот индекс будет полезен для условий, построенных для одного Field1. Для одного Field2 этот индекс не может быть применен.
Если в дополнение к индексу по полям Field1, Field2 добавить индекс по полям Field2, Field1, то SQL Server при построении плана запроса будет анализировать, какой из них более селективен в применении к ограничениям на условия запроса. Последний момент в построении композитного индекса по полям Field1, Field2 – он не равен сумме индексов по указанным полям. В случае, когда в запросе могут быть использованы оба поля как критерий поиска при раздельных индексах по полям, будет построено пересечение по индексам, что медленнее чем выборка из композитного индекса.
Примечание. В некоторых СУБД, например Firebird, применение нескольких индексов в запросе для одной таблицы ведет к существенному снижению производительности.
Пример. Отберем заказы определенного клиента, начиная с указанной даты.


  1. select customerid, OrderDate from [order] where customerid = ''222a3c77-50e9-4abb-be21-aa45b540f2a6''
  2. and OrderDate > GetDate()
  3. Go
  4. SQL Server Execution Times:
  5. CPU time = 141 ms, elapsed time = 861 ms.

План показывает сканирование по первичному ключу. MS SQL рекомендует создание композитного индекса по коду поставщика и дате.

Создадим индекс:


  1. CREATE NONCLUSTERED INDEX IDX_OrderCustomerDate
  2. ON [Order] (CustomerId, OrderDate)
  3. Go

Тот же самый запрос стал выполняться быстрее.


  1. SQL Server Execution Times:
  2. CPU time = 93 ms, elapsed time = 819 ms.

Уберем из условия отбор по дате:


  1. select customerid, OrderDate from [order] where customerid=''222a3c77-50e9-4abb-be21-aa45b540f2a6''
  2. /*and OrderDate&gt;GetDate()*/
  3. Go

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


  1. select customerid, OrderDate from [order] where /*customerid=''222a3c77-50e9-4abb-be21-aa45b540f2a6''
  2. and*/ OrderDate&gt;GetDate()
  3. Go

Индекс используется, только вместо более быстрой операции Seek используется Scan.

5.7. Общие рекомендации по проектированию индексов

Большое количество индексов в таблице снижает производительность инструкций INSERT, UPDATE, DELETE и MERGE, потому что при изменении данных в таблице все индексы должны быть изменены соответствующим образом.
Большое число индексов может повысить производительность запросов, которые не изменяют данных, таких как инструкции SELECT, поскольку у оптимизатора запросов будет больший выбор индексов при определении самого быстрого способа доступа.
Индексирование маленьких таблиц может оказаться не лучшим выбором, так как поиск данных в индексе может потребовать у оптимизатора запросов больше времени, чем простой просмотр таблицы. Следовательно, для маленьких таблиц индексы могут вообще не использоваться, но тем не менее их необходимо поддерживать при изменении данных в таблице.

5.8. Выбор типа соединения таблиц

Выбор типа соединения таблиц в запросе (INNER/LEFT/RIGHT) зависит от задачи. Практика показывает, что если в запросе, который подразумевает однозначное соединение одной таблицы с другой, нет большой разницы, использовать INNER или LEFT JOIN. Оптимизатор генерирует для таких запросов одинаковый план.
Пример:


  1. select * from
  2. [Order]
  3. left join [OrderProduct]
  4. on [Order].OrderId = [OrderProduct].OrderId
  5. where [OrderProduct].Quantity > 100

или


  1. select * from
  2. [Order]
  3. inner join [OrderProduct]
  4. on [Order].OrderId = [OrderProduct].OrderId
  5. where [OrderProduct].Quantity > 100

5.9. Рекомендации по оптимизации запросов от Microsoft

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

  • Запросы с предикатами, которые содержат операторы сравнения столбцов одной и той же таблицы.
  • Запросы с предикатами, использующими операторы, и выполнение одного из следующих условий:
    • отсутствует статистика для столбца, указанного с любой стороны от оператора;
    • распределение значений в статистике неоднородно, а запрос выполняет поиск весьма ограниченного набора значений. Эта ситуация особенно вероятна, если оператор не является оператором равенства (=);
    • предикат использует оператор неравенства (!=) или логический оператор NOT.
  • Запросы с любыми встроенными функциями SQL Server или пользовательскими скалярными функциями, которым в качестве аргументов передаются выражения, отличные от констант.
  • Запросы, в которые включены столбцы, соединяемые по арифметическим операторам или объединением строк.
  • Запросы, которые сравнивают переменные, значения которых в момент компиляции и оптимизации запроса неизвестны.

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


  1. SELECT * FROM MyTable
  2. WHERE MyTable.Col1 > MyTable.Col2

Можно повысить производительность, добавив вычисляемый столбец Col3 к таблице MyTable, который подсчитывает разницу между Col1 и Col2 (Col1 минус Col2). Затем переписать запрос:


  1. SELECT * FROM MyTable
  2. WHERE Col3 > 0

Возможно, производительность еще больше повысится, если построить индекс для столбца MyTable.Col3.

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

http://iocsha.ddns.net/blog/sql-optimization-2