Опубликован: 29.07.2008 | Доступ: свободный | Студентов: 1175 / 115 | Оценка: 4.49 / 4.15 | Длительность: 17:53:00
Лекция 2:

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

Индексы, ускоряющие операции соединения

Операторы соединения используются для соединения таблиц или промежуточных результатов. В SQL Server используется три типа операторов соединения.

  • Соединения вложенных циклов (Nested Loop Join) используют один ввод соединения в качестве внутренней входной таблицы, а другой ввод соединения в качестве внешней входной таблицы. Вложенные циклы однократно сканируют каждую строку ввода и ищут соответствующие строки во внешнем вводе. Если в столбцах условий соединения внешнего ввода существуют индексы, SQL Server может использовать поиск по индексу (метод seek) для отыскания строк во внешнем вводе. Если индексов не существует, то SQL Server приходится использовать операторы сканирования, чтобы найти во внешнем вводе совпадающие строки для каждой строки внутреннего ввода. Вложенные циклы всегда используются в тех случаях, когда внутренний ввод имеет всего несколько строк, поскольку в этом случае, это самая эффективная операция соединения.
  • Соединение слиянием (Merge Join) используется, когда вводы соединения сортируются по своим столбцам соединения. Выполняя операцию соединения слиянием, SQL Server сканирует однократно отсортированный ввод и выполняет слияние данных, подобно тому, как закрывают замок-молнию. Операция соединения слиянием очень эффективна, но сортировка данных должна быть выполнена заранее, а это означает, что в соединяемых столбцах должны существовать индексы. Если индексы не существуют, SQL Server может принять решение сначала выполнить сортировку ввода, но это не следует делать очень часто, поскольку сортировка данных - обычно неэффективный процесс.
  • Хэшированное соединение (Hash Join) используется для больших не-индексированных вводов без сортировки. Хэшированное соединение использует для соединения вводов операции хэширования в соединяемых столбцах. Чтобы вычислить и сохранить результат операции хэширования, SQL Server требуется больше времени и ресурсов процессора, чем для других операций соединения.
Изучаем операции соединения
  1. Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.
  2. Введите и выполните следующую инструкцию, не забудьте включить действительный план выполнения запроса. Код этого примера можно найти в файлах примеров под именем Examining Join Operations.sql. План выполнения этой инструкции показан ниже. Мы видим, что SQL Server для того, чтобы извлечь строки внутреннего ввода (данные из таблицы OrderDetails ), использует поиск по индексу (метод seek ), а затем оператор Nested Loop Join, потому что во внешнем вводе есть только одна совпадающая строка (данные из таблицы Orders ). Совпадающие строки внешнего ввода возвращаются также с помощью поиска по индексу (метод seek ), поскольку существует совпадающий индекс. Для извлечения данных SQL Server требуется только пять считываний страниц, как указано на вкладке Messages (Сообщения).
    SET STATISTICS IO ON
    SELECT o.SalesOrderID, o.OrderDate, od.ProductID
       FROM dbo.Orders o INNER JOIN dbo.OrderDetails od
          ON o.SalesOrderID = od.SalesOrderID
       WHERE o.SalesOrderID = 43659
  3. Измените запрос так, чтобы требовалось извлечь более одного значения SalesOrderID. В этом случае SQL Server выполнит соединение слиянием, поскольку в индексе есть отсортированные строки, а во внутреннем вводе много строк. Введите и выполните следующий запрос: Вы увидите, что для выполнения этого запроса SQL Server требуется 19 считываний страниц.
    SELECT o.SalesOrderID, o.OrderDate, od.ProductID 
       FROM dbo.Orders o 
       INNER JOIN dbo.OrderDetails od 
          ON o.SalesOrderID = od.SalesOrderID 
       WHERE o.SalesOrderID BETWEEN 43659 AND 44000
  4. Введите и выполните следующий пакет, который удаляет опорные индексы.
    DROP INDEX CLIDX_Orders_SalesOrderID ON dbo.Orders
     DROP INDEX CLIDX_OrderDetails ON dbo.OrderDetails
  5. Снова выполните инструкцию SELECT, которую мы использовали ранее (она снова показана ниже) и изучите изменения планов выполнения запросов и операций ввода/вывода.
    SELECT o.SalesOrderID, o.OrderDate, od.ProductID 
       FROM dbo.Orders o INNER JOIN dbo.OrderDetails od 
         ON o.SalesOrderID = od.SalesOrderID 
       WHERE o.SalesOrderID = 43659
    SELECT o.SalesOrderID, o.OrderDate, od.ProductID 
       FROM dbo.Orders o INNER JOIN dbo.OrderDetails od 
         ON o.SalesOrderID = od.SalesOrderID 
       WHERE o.SalesOrderID BETWEEN 43659 AND 44000

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

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

Распределение данных и статистика

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

  1. Запрос передается SQL Server.
  2. Запускается Оптимизатор запросов SQL Server, который определяет, к каким данным необходимо выполнить доступ.
  3. SQL Server выполняет поиск статистических данных в столбце, к которому происходит обращение.
  4. Если статистика уже существует и не является устаревшей, SQL Server может продолжать выполнение запроса.
  5. Если статистика не существует, SQL Server генерирует новые статистические данные.
  6. Если статистика существует, но является устаревшей, SQL Server вычисляет новые статистические данные для этих данных.
  7. Оптимизатор запросов SQL Server продолжает работу и генерирует план выполнения запроса.

Таково поведение по умолчанию, но для большинства баз данных существует лучший вариант. Можно воспользоваться инструкцией ALTER DATABASE, чтобы информировать SQL Server о том, что нужно обновлять данные статистики асинхронно; это означает, что программа не будет ждать новых статистических данных при генерации плана выполнения запроса. Безусловно, это означает, что сгенерированный план выполнения запроса может не быть оптимальным, поскольку при его создании были использованы устаревшие статистические данные. В особых случаях может быть желательным сгенерировать или обновить данные статистики вручную. Это можно сделать с помощью инструкций CREATE STATISTICS или UPDATE STATISTICS. Можно также отключить автоматическое создание и обновление индекса на уровне базы данных, выполнив инструкцию ALTER DATABASE. Все эти варианты следует использовать только в особых ситуациях, потому что поведение по умолчанию прекрасно подходит для большинства случаев. Дополнительную информацию об этих вариантах можно прочитать в официальном документе "Statistics Used by the Query Optimizer in Microsoft SQL Server 2005" (Статистические данные, используемые Оптимизатором запросов в Microsoft SQL Server 2005), который можно найти по ссылке http://www.microsoft.com/technet/prodtechnol/sql/ 2005/qrystats.mspx.

Просматриваем статистику распределения данных
  1. Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.
  2. Чтобы получить информацию о существующей статистике, можно выполнить запросы к представлениям sys.stats и sys.stats_columns.

Введите и выполните следующий запрос, чтобы узнать, какие статистические данные существуют для таблицы dbo:OrderDetails. Код этого примера можно найти среди файлов примеров под именем DataDistributionStatistics.sql.

SELECT s.NAME, COL_NAME ( s.object_ID, sc.column_id) as CNAME 
  FROM sys.stats s INNER JOIN sys.stats_columns sc 
  ON s.stats_id = sc.stats_id 
  AND s.object_id = sc.object_id 
  WHERE s.object_id = OBJECT_ID('dbo.OrderDetails') 
  ORDER BY s.NAME;"

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

  1. Чтобы получить статистическую информацию, можно использовать инструкцию DBCC SHOW_STATISTICS. Чтобы отобразить статистику для столбца LineTotal таблицы dbo.OrderDetails, введите и выполните следующую инструкцию:
DBCC SHOW_STATISTICS('dbo.OrderDetails', 'LineTotal')

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

  • RANGE_HI_KEY - значение верхней границы шага.
  • EQ_ROWS - количество строк, которое равно значению the RANGE_HI_KEY.
  • RANGE_ROWS - количество строк в диапазоне, не считая границ.
  • DISTINCT_RANGE_ROWS - количество неповторяющихся значений внутри диапазона.
  • AVG_RANGE_ROWS - среднее количество строк в расчете на каждое различимое значение ключа.
  • Закройте окно среды SQL Server Management Studio.
Гаральд Егоркин
Гаральд Егоркин
Россия
Павел Шелякин
Павел Шелякин
Россия