Оптимизация баз 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

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

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

Оптимизация баз Microsoft SQL Server: Часть 1. Зачем нужны индексы?

 

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

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

   1) Создание репликаций базы данных

   2) Шардинг базы данных

   3) Индексирование таблиц базы данных с целью оптимизации запросов

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

   Итак, разберемся, что такое индекс, и какие типы индексов мы можем создавать при разработке баз данных.

   Индексы создаются для таблицы и, по сути, являются копией этой таблицы. Различие состоит в том, что индекс может включать не все столбцы исходной таблицы, а лишь те которые указал разработчик. Рассмотрим пример. У нас есть таблица Stuffs, которая содержит 10 колонок и упорядоченный индекс IDX_StuffsSalary с ключом Salary, который содержит только два столбца из таблицы Stuffs:

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

Индекс IDX_StaffsSalary
Salary	Int
Name	Nvarchar(50)

   Как это работает и в чем тут оптимизация? Допустим, есть следующий запрос:

SELECT Name FROM Stuffs WHERE Salary > 400 AND Salary < 1000

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

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

  Однако есть у индексов и свои подводные камни. Они позволяют оптимизировать и существенно ускорить операции выборки данных, но при этом отрицательно сказываются на производительности запросов по вставке и удалению данных. Так как любые изменения в данных главной таблицы должны автоматически применяться для всех ее индексов. Если для таблицы создано четыре индекса, то простая операция вставки новой строки будет выполняться пять раз. Один раз – вставка в главную таблицу и по одному разу для каждого индекса. Таким образом, время выполнения запроса INSERT увеличится в четыре раза. Аналогично обстоит дело с операцией DELETE.

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

  Столбы в индексе могут быть двух типов:

  1) Столбцы в ключе индекса

  2) Столбы, включенные в индекс

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

  В зависимости от общего числа столбцов в индексе, индексы делятся на кластеризованные и некластеризованные индексы.

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

  Некластеризованный индекс – это индекс, в который включена только часть столбцов базовой таблицы. Пример такого индекса приведен выше.

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