Клерк.Ру

Небольшое исследование на тему больших таблиц и индексов

Статья образовалась в ходе обсуждения статьи "История оптимизации одного большого запроса средствами MSSQL Profiler и 1С". По ходу обсуждения был задан вопрос: "Что выгоднее оптимизировать - количество обрабатываемых строк или скорость выполнения операции?"

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

Еще раз добрый день, уважаемые коллеги. 

Эта небольшая статья родилась в ходе обсуждения моей предыдущей статьи - История оптимизации одного запроса. Спасибо Сергею Костякову (Srom), который подтолкнул меня к проведению этого исследования. Я сначала даже хотел ответить в комментариях к статье, но когда понял что есть ограничение на количество скриншотов, подумал что есть смысл это вынести в небольшую статью.

Если вкратце, то Srom задал резонный вопрос

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

На что я ответил:

Сложно судить, но мне почему-то как раз пришло в голову именно с таблицей разобраться, чем пытаться помочь с TABLE SCAN'ом ) 


И далее у меня родилась следующая гипотеза (цитирую свой комментарий):

По-большому счету любую операцию можно рассматривать как V=f(P,T), где V объем, т.е. количество выполненных операций за все время, P производительность, т.е. скорость выполнения этих операций в единицу времени) и T время затрачиваемое на работу, т.е. общее время выполнения операции. Предположим (для упрощения, но можно проверить и составить реальный график по реальным данным) график будет линейным (V=P*T). 

Предположим, что у нас на начальном этапе скорость обработки данных - 100 единиц в единицу времени, а объем необходимый для производства равен 100 000 единиц (у нас цифры были другие 2 млн строк и 2 минуты), тогда время которое необходимо затратить, чтобы произвести (обработать) 100 000 единиц - 100 000 / 100 = 1000 единиц времени. 

Двигаемся дальше - скорость оставляем НЕИЗМЕННОЙ, а значительным образом уменьшим количество записей до 1000, тогда время будет равно 10 единицам, а теперь подумаем во сколько раз нам нужно увеличить скорость выполнения операций при НАЧАЛЬНЫХ условиях объема 100 000 и заданном времени T=10, скорость выполнения операции должна стать 10 000, т.е. вырасти в 100 раз (математика начальная школа :)))). 

Ну, а в нашем случае, у нас было 2 млн. строк и я планировал его уменьшить до 2000 примерно, соответственно скорость выполнения нужно было увеличить в 1000 раз, чтобы получить сравнимый прирост :) 

С моей точки зрения, я мог с высокой степенью гарантии уменьшить количество записей до 2000, но получить прирост в 1000раз при добавлении индекса - я получил бы вряд ли. 


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

две таблицы MainTable (2 млн. строк) и CopyTable (160 строк). Каждая содержит две колонки ID и Rand - заполненных случайным образом двузначными числами (CopyTable получалась из MainTable путем копирования только 160 строк).

Запрос для теста будет следующим:

select M.ID,M.Rand,C.ID,M.Rand

from MainTable M 

inner join CopyTable C 

on M.ID = C.ID

1) Выполняем запрос без всяких индексирований - время выполнения 23 секунды. Видим везде Table Scan. Оптимизатор говорит - не хватает индекса - "ну надо так надо" (с)

ПВЗ №1

2) Выполняем тот же запрос, но с созданным индексом по полю ID. И видим следующую интересную картину - время выполнения уменьшилось на 1 секунду :) - 22 секунды. И оптимизатор использует индекс только для первой (маленькой) таблицы, а вторую продолжает гонять table scan'ом.

ПВЗ №2

Почему не использует индекс? Добавим инструкцию WITH (Index = name_index) и заставим оптимизатор использовать индекс, хоть он этого и не очень хочет. Посмотрим что будет :)))

3) Выполняем запрос с конкретным индексом. Время выполнения опять 22 секунды. Видим следующую картину (см. ниже). Данные лежат в куче (heap), что логично, так как у нас индекс не кластерный. И оптимизатор тратит 97% на поиск строки по закладке - оператор RID LookupNB! Оптимизатор говорит, что ему явно не хватает поля Rand в индексе - попробуем его добавить в оба индекса

ПВЗ №3

4) Выполняем исходный запрос. Время выполнения - ОПЯТЬ 22 секунды!!! =))) ПВЗ - четко использует оба индекса, но возникает Nested Loop. И кстати Index Seek на 2 млн. записей - все равно отнимает 77% от общего выполнения запроса, что кстати БОЛЬШЕ!!! чем Table Scan из второго запроса, где он занимал 56% от общего времени (а время выполнения одинаковое для обоих запросов)

ПВЗ №4

5) Делаем оба индекса кластерными - посмотрим что из этого получится. Ну и собственно ничего из этого особенного не получилось - Clustered Index Scan и Nested Loops и 24 секунды времени

ПВЗ №5

6) Ну и напоследок уменьшаем количество записей до 20 000 и смотрим на время выполнения и план выполнения запроса:

select M.ID,M.Rand,C.ID,M.RAND

from (select top 20000 * from MainTable) M

inner join CopyTable C on

M.ID = C.ID

ПВЗ №6

Два Clustered Index Scana и меньше 1 секунды на выполнение

7) Убираем оба индекса и прогоняем запрос. Вернулись оба Table Scana, но время выполнения все равно осталось меньше 1секунды

ПВЗ №7

Выводы

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

График зависимости времени от количества записей

2) Индексы не панацея. Во-первых, индексы не всегда можно создать (например если есть колонка с строкой неограниченной длины). Во-вторых, индексы не всегда работают. В-третьих, когда индексы работают - не факт, что они дадут прирост производительности.

Налоговые проверки становятся жестче. Научитесь защищать себя в онлайн-курсе «Клерка» — «Налоговые проверки. Тактика защиты».

Посмотрите рассказ о курсе от его автора Ивана Кузнецова, налогового эксперта, который раньше работал в ОБЭП.

Заходите, регистрируйтесь и обучайтесь. Обучение полностью дистанционно, выдаем сертификат.