Оптимизация производительности SQL Server с использованием индексов

Введение

Как известно, индексы повышают производительность аналогично оглавлению или предметному указателю в кнгие. Прочитав несколько статей в интернете и пару глав из книжек, хотелось бы узнать, насколько индексы помогают увеличить скорость выборки данных из SQL Server. Рассмотрим на примере.
Для этого нам понадобятся две таблицы, связанные внешним ключом. В главной таблице будет 10 000 строк, во второй — 1 000 000. В первой таблице будет первичный ключ и 2 столбца с текстом, вторая таблица будет содержать первичный ключ, 2 текстовых поля, 2 числовых поля, дата, вычисляемый столбец и внешний ключ.

Структура базы данных

CREATE TABLE "maintable"(
"ID" INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
"name" NVARCHAR (50) NULL,
"description" NVARCHAR (100) NULL
)

CREATE TABLE "secondtable" (
"ID" INT NOT NULL IDENTITY (1, 1) PRIMARY KEY,
"sname" NVARCHAR (50) NULL,
"sdescr" NVARCHAR (100) NULL,
"somefirstnumber" INT DEFAULT 0,
"somesecondnumber" INT DEFAULT 1,
"somedatetime" DATETIME NOT NULL,
"howmanyhours" INT NULL DEFAULT 0,
"newdate" AS DATEADD(HOUR, "howmanyhours", "somedatetime"),
"mainID" INT NOT NULL
CONSTRAINT FK_SecondToMain FOREIGN KEY ("mainID")
REFERENCES "maintable" ("ID")
);

--Хранимые процедуры
CREATE PROCEDURE "insertintomain"
@Name NVARCHAR (50), @Descr NVARCHAR (100)
AS
	INSERT INTO "SimpleIndex"."dbo"."maintable" ("name", "description")
	VALUES (@Name, @Descr);
GO

CREATE PROCEDURE "insertintosecond"
@Sname NVARCHAR(50), @Sdescr NVARCHAR(100),
@Firstnumber INT, @Secondnumber INT,
@SomeDT DATETIME, @Hours INT, @MainID INT
AS
	INSERT INTO "SimpleIndex"."dbo"."secondtable" (
	"sname", "sdescr", "somefirstnumber", 
	"somesecondnumber", "somedatetime", 
	"howmanyhours", "mainID")
	VALUES (
	@Sname, @Sdescr,@Firstnumber, @Secondnumber,
	@SomeDT, @Hours, @MainID)
GO

Вставка данных

Понятное дело, что просто так не вставить миллион строк, воспользуемся C#.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace SimpleAddingRandomRecordsToDatabase
{
    class Program
    {
        static string alphabet()
        {
            //Количество пробелов означает частоту их появляния
            string alphabet = "abcdefgh ijklmnopqrst uvwxyzабвгд еёжзийкл мнопросту фхцчшщыэюя ";

            //преобразуем алфавит в юникод
            Encoding enc = Encoding.UTF8;
            var bytes = enc.GetBytes(alphabet);
            alphabet = enc.GetString(bytes);
            return alphabet;
        }

        static int alphlen = alphabet().Length;

        static string getRandomString(int lenth, int rndparam, bool allowspacebars)
        {
            string str = "";
            Random rand = new Random(rndparam);
            for (int i = 0; i < lenth; i++)
            {
                int num = rand.Next(alphlen);
                str = str + alphabet()[num];
            }
            if (!allowspacebars)
            {
                str = str.Replace(" ", "");
            }
            return str;
        }

        static void Main(string[] args)
        {
             

            string result;
            Console.WriteLine("Нажмите 1 для начала операции");
            result = Console.ReadLine();
            if (result.Equals("1"))
            {
                //Подключаемся к БД
                SqlConnection cn = new SqlConnection();
                SqlConnectionStringBuilder cs = new SqlConnectionStringBuilder();
                cs.IntegratedSecurity = true;
                cs.DataSource = @"KONSTANTINKONSTANTINSQL";
                cs.InitialCatalog = "SimpleIndex";
                cn.ConnectionString = cs.ToString();
                cn.Open();

                //генерируем первые 10 000 записей для главной таблицы
                int i2 = 0;
                for (int i = 0; i < 10000; i++)
                {
                    SqlCommand cm = cn.CreateCommand();
                    cm.CommandType = CommandType.StoredProcedure;
                    cm.CommandText = "insertintomain";
                    i2++;
                    cm.Parameters.Add(new SqlParameter("Name", getRandomString(50, i2, false)));
                    i2++;
                    cm.Parameters.Add(new SqlParameter("Descr", getRandomString(100, i2, true)));
                    cm.ExecuteNonQuery();
                }
   
                //Генерируем 1 000 000 записей, соедниённых с первой таблицей
                i2 = 0;
                for (int i = 0; i < 1000000; i++)
                {
                    Random rand = new Random();
                    DateTime dt = DateTime.Now;
                    dt = dt.AddHours((-1) * rand.Next(2000));
                    SqlCommand cm = cn.CreateCommand();
                    cm.CommandType = CommandType.StoredProcedure;
                    cm.CommandText = "insertintosecond";
                    i2++;
                    cm.Parameters.Add(new SqlParameter("Sname", getRandomString(50, i2, false)));
                    i2++;
                    cm.Parameters.Add(new SqlParameter("Sdescr", getRandomString(100, i2, true)));
                    i2++;
                    Random rand2 = new Random(i2);
                    cm.Parameters.Add(new SqlParameter("Firstnumber", rand2.Next(10000)));
                    i2++;
                    rand2 = new Random(i2);
                    cm.Parameters.Add(new SqlParameter("Secondnumber", rand2.Next(100)));
                    cm.Parameters.Add(new SqlParameter("SomeDT", dt));
                    i2++;
                    rand = new Random(i2);
                    cm.Parameters.Add(new SqlParameter("Hours", rand.Next(30)));
                    i2++;
                    rand = new Random(i2);
                    cm.Parameters.Add(new SqlParameter("MainID", rand.Next(9999)+1));
                    cm.ExecuteNonQuery();
                }
                cn.Close();
                Console.WriteLine("Данные вставлены.");
            }
        }
    }
}

Запускаем консольное приложение, жмём «1» и ждём. У меня ушло минут 15-20 на вставку.

Описание результатов

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

Поиск по первичному ключу

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

SELECT "name", "description" FROM "maintable" WHERE "ID" = 3823;

Время, затраченное на поиск: 1,523 секунды.
Повторный запрос затратил 0,9 секунды, а третий — 0,1 секунды.

Выполним второй запрос с большой селективностью из этой же таблицы:

SELECT "name", "description" FROM "maintable" WHERE "ID" BETWEEN 5000 AND 6000;

Первый запрос затратил 0,3 секунды, второй — 0,26 сек., третий — 0,25 сек.

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

SELECT * FROM "secondtable" WHERE "ID" = 728472;

Время, затраченное на поиск: 1,4 сек., второй запрос показал 1,36 сек.. Далее будет показано среднее время запроса.

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

Поиск по шаблону

Рассмотрим поиск текста по шаблону с индексированными и непроиндексированными столбцами:

SELECT * FROM "maintable" WHERE "name" LIKE 'мл%';

Без индекса такой запрос выполняется за 0,172 секунды из 10 000 строк. С индексом

CREATE NONCLUSTERED INDEX iName
ON "maintable" ("name")

такой выполняется за 0,112 секунды, а с составным индексом, содержащим поля «name» и «description», запрос занял 0,09 секунды.

Запрос

SELECT * FROM "maintable" WHERE "name" LIKE '%dc%';

выполняется за 0,172 секунды без индекса и 0,112 секунды с индексом.

Выполним поиск по шаблону к таблице, содержащей миллион записей.

SELECT * FROM "secondtable" WHERE "sname" LIKE 'юуж%';

Этот запрос без индексации занял бы в среднем 1,7 секунды, тогда как с индексом столбца будет потрачено 0,15 секунды.

Поиск по дате

Для начала выполним запрос малой селективности

SELECT * FROM "secondtable" 
WHERE "somedatetime" BETWEEN '2012-26-11 11:30:00' AND '2012-26-11 11:32:00'

Без индекса он займёт в среднем 0,7 сек., проиндексированный — 0,22 сек.

Запрос большой селективности (420600 строк)

SELECT * FROM "secondtable" 
WHERE "somedatetime" BETWEEN '2012-26-11 11:30:00' AND '2012-31-12 11:32:00'

Без индекса — 19,219 сек, с индексом только поля даты — 9,9 сек.

Запрос по дате к вычисляемому столбцу

SELECT * FROM "secondtable" 
WHERE "newdate" BETWEEN '2012-26-11 11:30:00' AND '2012-31-12 11:32:00'

Этот запрос без индекса займет 12,2 секунды, тогда как с индексом — 9,75. Важно учесть то, что исходный столбец, содержащий дату, уже был проиндексирован.

Объединение таблиц

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

CREATE NONCLUSTERED INDEX iFK ON "secondtable" ("mainID");
SELECT "name", "sname", "somefirstnumber" 
FROM "maintable" M
INNER JOIN "secondtable" S
	ON M."ID" = S."mainID" 
WHERE
	M."ID" = 8271

Такой запрос вернёт 101 строку. Без индекса — 0,422 сек, а с индексом — 0,122 сек. Как мы видим, скорость запроса увеличилась более чем в три раза. Рассмотрим остальные запросы, связанные с объединением.

Но индекс не всегда увеличивает производительность. Например, запрос (420 000 строк)

SELECT "name", "sname", "somefirstnumber" 
FROM "maintable" M
INNER JOIN "secondtable" S
	ON M."ID" = S."mainID" 
WHERE
	S."somedatetime" BETWEEN '2012-26-11 11:30:00' AND '2012-31-12 11:32:00';

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

Объединение с поиском по шаблону

Выполним объединение с поиском по шаблону среди миллиона записей:

SELECT "name", "sname", "somefirstnumber" 
FROM "maintable" M
INNER JOIN "secondtable" S
	ON M."ID" = S."mainID" 
WHERE
	"sname" LIKE 'юуж%';

Без индексов запрос займёт 0,766 секунды и вернёт 5 строк.
С индексом только внешнего ключа — 0,4 сек.;
С индексом только текстового поля — 0,125 сек.;
С индексом внешнего ключа и индексом текстового поля — 0,109 сек.;
С составным индексом, содержащим внешний ключ и текстовое поле — 0,35 сек..

Сравнение результатов

Рассмотрим результаты в виде графиков. Вертикальная ось показывает затраченное время (а не скорость!).
1. Выборка по первичному ключу. Так как уже есть кластеризованный индекс, рассмотрим большую и малую селективную выборку из одной таблицы:

Из этого графика видно, что выборка большого количества данных обрабатывается быстрее.

2. Поиск по первичному ключу с объединением таблиц. Следующий график показывает, что индексирование внешнего ключа ускоряет операцию поиска более чем в три раза:

3. Поиск по шаблону текста. Из графика видно, что производительность во много раз вырастает, если использовать индекс при большом объёме данных:

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

Объединение таблиц и поиск по дате показал одинаковые результаты как с индексами, так и без них.

5. Поиск по вычисляемому столбцу. Индекс также уменьшает время поиска.

6. Поиск по шаблону с объединением. Индекс текстового поля и оба индекса значительно увеличивают производительность:

Подведём итоги

Проанализировав полученные результаты, можно сказать, что:

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

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

Автор: WINGUNT

Источник