Повышение производительности запроса

Создаем и применяем кластеризованные индексы

1. Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.
2. Для создания уникального кластеризованного индекса в таблице Orders ведите и выполните следующую инструкцию. Код этого примера имеется в файлах примеров под именем Creating And Using Clustered Indexes.sql.

   CREATE UNIQUE CLUSTERED INDEX CLIDX_Orders_SalesOrderID 
       ON dbo.Orders(SalesOrderID)
3. Теперь выполните эти же две инструкции SELECT, как делали это раньше, и изучите различия. Обязательно включите при выполнении запроса действительный план выполнения.

   SET STATISTICS IO ON;
   
   SELECT * FROM dbo.Orders;
   SELECT * FROM dbo.Orders 
     WHERE SalesOrderID =46699;
   
   SET STATISTICS IO OFF;
4. Перейдите на вкладку Execution Plan (План выполнения).
   

   
   

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

   • Просмотр индекса.Просмотр уровня листовых вершин индекса со считыванием данных таблицы. Поскольку первая инструкция SELECT не имеет предложения WHERE, серверу SQL Server известно, что нужно возвратить все данные, которые хранятся на уровне листовых вершин индекса.
   • Поиск по индексу.Операция, при которой SQL Server выполняет поиск определенного значения, проходя по ветвям индекса, начиная от корневой вершины индекса.

   Эти две операции могут также соединяться, чтобы извлечь определенный диапазон данных. В этом виде операции частичного просмотра SQL Server пытается найти начало диапазона, а затем просматривает дерево до конца этого диапазона.
5. Перейдите на вкладку Messages (Сообщения), как показано ниже.
   

   
   увеличить изображение
   

   Мы видим, что первая инструкция SELECT генерирует почти тот же объем считываний страниц, что и операция просмотра таблицы, если у нее структура кучи. Это не удивительно, поскольку инструкция SELECT требует все данные, и, следовательно, SQL Server должен возвратить все данные. Но второй запрос генерирует только два считывания страниц, а это значительное улучшение по сравнению с теми 178 считываниями страниц, которые были показаны раньше. SQL Server необходимо только выполнить поиск по индексу, что требует гораздо меньшего количества операций ввода/вывода, чем поиск в каждой странице данных.
6. Введите следующую инструкцию SELECT, которая возвращает данные в отсортированной форме, и нажмите (Ctrl+L), чтобы получить предполагаемый план выполнения.

   SELECT * FROM dbo.Orders ORDER BY SalesOrderID;
   SELECT * FROM dbo.Orders ORDER BY OrderDate;

   

   
   

   Из рисунка видно, что первая инструкция выполняет только просмотр кластеризованного индекса и не сортирует данные. Причина заключается в том, что эти данные уже отсортированы по SalesOrderID, потому что этот столбец является ключом кластеризованного индекса. Следовательно, серверу SQL Server для того, чтобы получить строки в правильном порядке, остается только просмотреть данные на уровне листовых вершин и возвратить результат.

   Во втором запросе данные были отсортированы после получения. Следовательно, после операции просмотра кластеризованного индекса имела место операция Sort (Сортировка), которая отсортировала данные по столбцу OrderDate. Поскольку сортировка является очень дорогостоящей операцией, второй запрос генерирует 93% общей стоимости обоих запросов. Таким образом, имеет смысл определять кластеризованный индекс в столбцах, которые часто используются в качестве аргумента сортировки или группировки критериев в агрегате, поскольку агрегация данных требует, чтобы SQL Server сначала сортировал данные в соответствии с критериями группировки.

   Теперь создадим составной индекс в таблице OrderDetails. Составной индекс - это индекс, который определен более, чем для одного столбца. Ключи индекса сортируются сначала по первому ключевому столбцу индекса, затем по второму и т. д. Такой индекс полезен в тех случаях, когда два или более столбца используются совместно в качестве аргументов поиска в запросах или когда уникальность строки определена через несколько столбцов.

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

1. В SQL Server Management Studio введите и выполните следующие инструкции, чтобы создать составной кластеризованный индекс в таблице OrderDetails.

   CREATE UNIQUE CLUSTERED INDEX CLIDX_OrderDetails 
     ON dbo.OrderDetails(SalesOrderID,SalesOrderDetailID)
2. Теперь введите еще две инструкции SELECT. Первая будет выполнять поиск указанного значения SalesOrderID, а вторая -указанного значения SalesOrderDetailID. Оба столбца являются индексными столбцами нашего индекса CLIDX_OrderDetails. Нажмите (Ctrl+L), чтобы отобразить предполагаемый план выполнения.

   SELECT * FROM dbo.OrderDetails 
      WHERE SalesOrderID = 46999 
   SELECT * FROM dbo.OrderDetails 
      WHERE SalesOrderDetailID = 14147

Легко заметить, что в первом запросе, который выполняет поиск значения из первого столбца составного индекса, SQL Server для поиска строки использует поиск по индексам (метод seek). Во втором запросе он использует просмотр индекса, который является более дорогостоящей операцией. Просмотр индекса используется потому, что невозможно искать значение только во втором столбце составного индекса, ведь индекс изначально сортируется по первому столбцу. Следовательно, важно продумать порядок индексных столбцов в составном индексе. Запомните, что составной индекс следует использовать только тогда, когда поиск по дополнительным столбцам выполняется исключительно в сочетании с первым столбцом или если должна быть применена уникальность.

Некластеризованные индексы

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

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

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

  CREATE [ UNIQUE ] NONCLUSTERED INDEX index name 
      ON <object> ( column [ ASC | DESC ] [ ,...n ] )

Создаем и применяем некластеризованные индексы

1. Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.
2. Введите следующую инструкцию SELECT и нажмите клавиатурную комбинацию (Ctrl+L), чтобы отобразить предполагаемый план выполнения. Код этого примера имеется в файлах примеров под именем Creating And Using Nonclustered Indexes.sql.

   SELECT DISTINCT SalesOrderID, CarrierTrackingNumber 
     FROM dbo.OrderDetails 
     WHERE ProductID = 776

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

   Примечание. Операция Sort (Сортировка) применяется в этом плане выполнения запроса для получения неповторяющегося результата.
3. Чтобы создать некластеризованный индекс в столбце Product ID таблицы OrderDetail, введите и выполните следующую инструкцию:

   CREATE INDEX NCLIX_OrderDetails_ProductID 
     ON dbo.OrderDetails(ProductID)
4. Введите предыдущую инструкцию SELECT и нажмите клавиатурную комбинацию (Ctrl+L), чтобы отобразить предполагаемый план выполнения.

   SELECT DISTINCT SalesOrderID, CarrierTrackingNumber 
     FROM dbo.OrderDetails 
     WHERE ProductID = 776

   

   
   увеличить изображение
   

   Если вы задержите указатель мыши над оператором IndexSeek, то увидите, что SQL Server выполняет поиск по индексу (метод seek) в таблице NCLIX_OrderDetails_ProductID, чтобы извлечь указатели на нужные записи. Поскольку в этой таблице существует кластеризованный индекс, SQL Server извлекает список ключей кластеризации в качестве указателей. Этот список передается на вход оператора Nested Loops (Вложенные циклы), который является разновидностью оператора Join (Соединение) (соединения будут рассмотрены далее в этой лекции). Оператор Nested Loops использует поиск (метод seek ) по кластеризованному индексу, чтобы возвратить нужные строки данных, которые затем переходят к оператору Sort (Сортировка), чтобы исключить повторяющиеся значения в результате. Так SQL Server извлекает строки при помощи некластеризованного индекса, если существует кластеризованный индекс.
5. Теперь посмотрим, как SQL Server осуществляет доступ к данным, когда существует некластеризованный индекс в таблице, не имеющей кластеризованного индекса. Введите и выполните следующую инструкцию DROP INDEX, чтобы удалить из таблицы OrderDetails кластеризованный индекс.

   DROP INDEX OrderDetails.CLIDX_OrderDetails
6. Введите предыдущую инструкцию SELECT и нажмите клавиатурную комбинацию (Ctrl+L), чтобы отобразить предполагаемый план выполнения.

7. SELECT DISTINCT SalesOrderID, CarrierTrackingNumber 
     FROM dbo.OrderDetails 
     WHERE ProductID = 776

   

   
   увеличить изображение
   

   Мы видим, что в этом случае SQL Server использует оператор RID Lookup, потому что указатели, которые SQL Server получил в результате поиска по индексу (метод seek), представляют собой указатели на физические строки данных, а не на ключи кластеризации. Оператор RID Lookup - это оператор, который используется в SQL Server для извлечения данных непосредственно со страницы.
8. Введите и выполните следующую инструкцию, чтобы снова создать кластеризованный индекс.

   CREATE UNIQUE CLUSTERED INDEX CLIDX_OrderDetails 
     ON dbo.OrderDetails(SalesOrderID,SalesOrderDetailID)