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

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

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

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

Структуры кучи

Файл данных в базе данных SQL Server делится на страницы по 8 Кбайт. Каждая страница содержит данные, индексы или другие типы данных, которые нужны SQL Server, чтобы манипулировать файлами данных. Однако большая часть страниц - это страницы данных или индекса. Страницы представляют собой единицы, которые SQL Server считывает и записывает из файлов данных. Каждая страница содержит данные или информацию индекса только для одного объекта базы данных. Поэтому на каждой странице данных вы найдете данные об одном объекте, а на каждой странице индекса - только информацию индекса. В SQL Server 2000 невозможно разбить строки данных на несколько страниц; это означает, что строка данных должна уместиться на странице, что ограничивает размер строки примерно 8060 байт (за исключением больших объектов данных). В SQL Server 2005 это ограничение больше не существует для типов данных переменной длины, таких, как nvarchar, varbinary, CLR и т. п. Благодаря типам данных переменной длины строки могут занимать несколько страниц, но все строки с типом данных фиксированной длины все же должны вписываться в одну страницу.

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

Изучаем структуры кучи
  1. Запустите SQL Server Management Studio. Откройте окно New Query (Создать запрос) и измените контекст базы данных на Adventure Works.
  2. В следующем примере мы создадим две таблицы с именами dbo.Orders и dbo.OrderDetails. Для создания таблиц и заполнения их данными ведите и выполните следующие инструкции. Код для всего примера можно найти среди файлов примеров под именем Examining Heap Structures.sql.
    USE AdventureWorks; 
    GO
    CREATE TABLE dbo.Orders( SalesOrderID int NOT NULL, 
        OrderDate datetime NOT NULL, 
        ShipDate datetime NULL, 
        Status tinyint NOT NULL,
        PurchaseOrderNumber dbo.OrderNumber NULL, 
        CustomerID int NOT NULL, 
        ContactID int NOT NULL, 
        SalesPersonID int NULL 
        );
    CREATE TABLE dbo.OrderDetails( SalesOrderID int NOT NULL, 
        SalesOrderDetailID int NOT NULL, 
        CarrierTrackingNumber nvarchar(25), 
        OrderQty smallint NOT NULL, 
        ProductID int NOT NULL, 
        UnitPrice money NOT NULL, 
        UnitPriceDiscount money NOT NULL,
        LineTotal AS (isnull((UnitPrice*((1.0)-UnitPriceDiscount))*OrderQty,(0.0)))
        );
    INSERT INTO dbo.Orders
    SELECT SalesOrderID, OrderDate, ShipDate, Status, PurchaseOrderNumber,
        CustomerID, ContactID, SalesPersonID 
      FROM Sales.SalesOrderHeader;
    
    INSERT INTO dbo.OrderDetails(SalesOrderID, SalesOrderDetailID, 
                CarrierTrackingNumber, OrderQty,
                ProductID, UnitPrice, UnitPriceDiscount) 
    SELECT SalesOrderID, SalesOrderDetailID,CarrierTrackingNumber,OrderQty,
           ProductID, UnitPrice, UnitPriceDiscount 
      FROM Sales.SalesOrderDetail;
  3. Мы создали две таблицы со структурой хранения "куча". Для выполнения запроса к таблице dbo.Orders введите инструкции, которые приводятся ниже. Включите действительный план выполнения, нажав (Ctrl+M) до начала выполнения или выбрав из меню Query (Запрос) команду Include Actual Execution Plan (Включить действительный план выполнения). Выполните запрос:
    SET STATISTICS IO ON; SELECT * FROM dbo.Orders
    SET STATISTICS IO OFF

    Параметр SET STATISTICS IO включает функцию, которая вызывает отправку сообщений о выполненных операциях дискового ввода/ вывода обратно клиенту сервером SQL Server при выполнении инструкции. Это замечательная функция, которую следует использовать для определения стоимости операций ввода/вывода для запросов.

  4. Перейдите на вкладку Messages (Сообщения). Вы увидите примерно такое сообщение:

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

  5. Перейдите на вкладку Execution Plan (План выполнения). В плане выполнения, показанном на следующем рисунке, мы видим, что SQL Server использовал операцию Table Scan (Просмотр таблицы) для доступа к данным, как единственный возможный вариант.

  6. Теперь немного изменим запрос, чтобы он возвратил указанные строки.
    SET STATISTICS IO ON;
    SELECT * FROM dbo.Orders WHERE SalesOrderID =46699;
    SET STATISTICS IO OFF;
  7. Посмотрим вывод в виде сообщения и графическое представление плана выполнения. Вы видите, что для этого запроса SQL Server все еще требуется 178 считываний страниц и использование операции Table Scan (Просмотр таблицы). Просмотр таблицы используется потому, что SQL Server не располагает индексом, и, следовательно, ему необходимо просмотреть все данные, чтобы найти нужную строку. p>Мы видим, что SQL Server использует операции просмотра таблиц для доступа к таблицам, не имеющим индекса. Эти просмотры вынуждают SQL Server просматривать все данные независимо от размера таблицы. Если таблица очень велика, то для ее просмотра может потребоваться много времени.

Индексы в таблицах

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

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

Индексы в SQL Server встроены в древовидную структуру, которая называется сбалансированным деревом. Основная структура сбалансированного дерева показана ниже в примере. Как видите, нижний уровень называется уровнем листовых вершин. Уровень листовых вершин можно мыслить как оглавление книги. Он содержит по одной записи на каждую строку данных, причем записи сортируются по столбцу индекса. Чтобы ускорить поиск значений в индексе, дерево построено поверх него с использованием операций сравнения < (меньше чем) и > (больше чем). Число уровней индекса зависит от количества записей и размера ключа индекса. В реальных условиях страница индекса могла бы содержать намного больше значений, чем изображено в примере. Поскольку страница имеет размер 8 Кбайт, SQL Server может указать на странице индекса на тысячи страниц. Следовательно, индекс обычно не имеет много уровней, даже если таблица содержит миллионы строк. Этот факт способствует очень быстрому поиску определенных значений.

Надписи:
Root (Level 2) - Уровень корневой вершины (2 уровень)
Intermediate (Level 1) - Уровень внутренних вершин (1 уровень)
Leaf Level (Level 0) - Уровень листовых вершин (0 уровень)
KEY Pointer - Ключ-указатель

Как уже отмечалось ранее, в SQL Server используется два типа индексов: кластеризованный и некластеризованный. Оба типа индексов представляют собой сбалансированные деревья, но построены они по-разному. Давайте посмотрим, в чем заключается разница.

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

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

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

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

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

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

Примечание. SQL Server создает уникальный индекс, если для таблицы определено ограничение первичного ключа, или уникальности. Если определен первичный ключ, то он создает кластеризованный индекс по умолчанию, если такой индекс до сих пор не существует в таблице. Этот вид индекса в SQL Server должен использоваться, если создание первичного, или уникального, ограничения, может быть определено в инструкциях CREATE или ALTER TABLE с ключевыми словами CLUSTERED или NONCLUSTERED.
Гаральд Егоркин
Гаральд Егоркин
Россия
Павел Шелякин
Павел Шелякин
Россия