Оптимизация баз Microsoft SQL Server: Часть 2. Индексы на практике?

 

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

   Напомню вам определение схемы таблицы Staffs, с которой мы будем работать:

 

Таблица Staffs
Id	Int
Name	Nvarchar(50)
Surname	Nvarchar(50)
Salary	Int
Address	Nvarchar(50)
Telephone	Nvarchar(50)
Job	Nvarchar(50)
Photo	image

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

SELECT Id, Name, Job FROM Stuffs WHERE SALARY > 1000 AND Photo IS NOT NULL

   Ключом индекса будут столбцы SALARY и Photo, так как по этим полям производится фильтрация выборки. А столбы Id, Name и Job будут включенными в индекс столбцами.

   Общий синтаксис команды следующий:

USE <database_name> GO

CREATE NONCLUSTERED INDEX <index_name> ON <table_name> ( <column_name> ASC -- столбцы ключа индекса )

INCLUDE ( <column_name> -- включенные столбцы ) GO

   В нашем случае запрос будет выглядеть следующим образом:

CREATE NONCLUSTERED INDEX IDX_StaffsSearch ON Stuffs

( Salary, Photo ) INCLUDE ( Id, Name, Job ) GO

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

   Если бы наш код был следующим:

CREATE NONCLUSTERED INDEX IDX_StaffsSearch ON Stuffs

( Salary, Photo ) INCLUDE ( Id ) GO

   То в данном случае индекс перестает быть покрывающим, поскольку не включает в себя все столбцы, используемые в запросе. Оптимизатор все равно будет использовать данный индекс при выполнении запроса, однако его эффективность на порядок снизиться, поскольку потребуются обращения к базовой таблице.

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

CREATE CLUSTERED INDEX IDX_Stsffsid ON Stuffs (Id)

   Здесь был создан уникальный кластерный индекс, построенный на основе первичного ключа таблицы (столбца Id).

 

Реальный пример

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

   Создадим новую базу данных:

CREATE DATABASE TestDB;

   И единственную таблицу Customers, которая будет состоять из четырех столбцов:

USE [TestDB] GO

CREATE TABLE [dbo].[Customers](

[Id] [int] NOT NULL, [Num1] [int] NULL, [Num2] [int] NULL, [Num3] [int] NULL) GO

   Теперь заполним нашу таблицу случайными данными. Столбец Id будет наращиваться в цикле, а остальные три колонки таблицы будут заполняться случайными числами при помощи своеобразного варианта функции random:

USE TestDB GO

DECLARE @i int = 0;

WHILE (@i < 500000) BEGIN INSERT INTO Customers(Id, Num1, Num2, Num3) VALUES(

@i, abs(checksum(newid())), abs(checksum(newid())), abs(checksum(newid())) ) SET @i = @i + 1; END

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

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

  Чтобы включить режим сбора статистики необходимо выполнить следующую команду:

SET STATISTICS IO ON

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

   Нас интересует только значение параметра logical reads.

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

  1) SELECT Id, Num1, Num2 FROM Customers WHERE Id = 2000

  2) SELECT Id, Num1, Num2 FROM Customers WHERE Id >= 0 AND Id < 1000

  3) SELECT Id, Num1, Num2 FROM Customers WHERE Id >= 0 AND Id < 5000

  Данные запросы вернут соответственно 1 строку, 1000 строк и 5000 строк. Без индексов показатель производительности(количество логических считываний) для всех запросов одинаковый и равен 1621. Внесем данные в таблицу результатов:

	Запрос 1	Запрос 2	Запрос 3
Без индексов	1621	1621	1621

  Теперь создадим для таблицы Customers непокрывающий индекс:

USE TestDB GO

CREATE NONCLUSTERED INDEX TestIndex1 ON dbo.Customers(Id);

  Теперь выполним приведенные выше запросы еще раз и посмотрим на счетчик производительности:

	Запрос 1	Запрос 2	Запрос 3
Без индексов	1621	1621	1621
Непокрывающий индекс	4	1632	1632

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

   Теперь создадим покрывающий индекс, тем самым достигнув максимальной производительности.

   Для начала удалим предыдущий индекс:

USE TestDB GO DROP INDEX Customers.TestIndex1

   И создадим новый индекс:

USE TestDB GO

CREATE NONCLUSTERED INDEX TestIndex2 ON dbo.Customers(Id) INCLUDE (Num1, Num2);

   Теперь выполним наши запросы в третий раз и запишем результаты в таблицу:

	Запрос 1	Запрос 2	Запрос 3
Без индексов	1621	1621	1621
Непокрывающий индекс	4	1632	1632
Покрывающий индекс	3	7	20

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

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