Статья образовалась в ходе обсуждения статьи "История оптимизации одного большого запроса средствами 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. Оптимизатор говорит - не хватает индекса - "ну надо так надо" (с)
2) Выполняем тот же запрос, но с созданным индексом по полю ID. И видим следующую интересную картину - время выполнения уменьшилось на 1 секунду :) - 22 секунды. И оптимизатор использует индекс только для первой (маленькой) таблицы, а вторую продолжает гонять table scan'ом.
Почему не использует индекс? Добавим инструкцию WITH (Index = name_index) и заставим оптимизатор использовать индекс, хоть он этого и не очень хочет. Посмотрим что будет :)))
3) Выполняем запрос с конкретным индексом. Время выполнения опять 22 секунды. Видим следующую картину (см. ниже). Данные лежат в куче (heap), что логично, так как у нас индекс не кластерный. И оптимизатор тратит 97% на поиск строки по закладке - оператор RID Lookup. NB! Оптимизатор говорит, что ему явно не хватает поля Rand в индексе - попробуем его добавить в оба индекса
4) Выполняем исходный запрос. Время выполнения - ОПЯТЬ 22 секунды!!! =))) ПВЗ - четко использует оба индекса, но возникает Nested Loop. И кстати Index Seek на 2 млн. записей - все равно отнимает 77% от общего выполнения запроса, что кстати БОЛЬШЕ!!! чем Table Scan из второго запроса, где он занимал 56% от общего времени (а время выполнения одинаковое для обоих запросов)
5) Делаем оба индекса кластерными - посмотрим что из этого получится. Ну и собственно ничего из этого особенного не получилось - Clustered Index Scan и Nested Loops и 24 секунды времени
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
Два Clustered Index Scana и меньше 1 секунды на выполнение
7) Убираем оба индекса и прогоняем запрос. Вернулись оба Table Scana, но время выполнения все равно осталось меньше 1секунды
Выводы
1) С моей точки зрения гипотеза подтвердилась. В общем случае - выгоднее оптимизировать количество обрабатываемых данных, чем оптимизировать скорость операции. Кстати зависимость времени от количества строк действительно практически линейная (см. диаграмму ниже)
2) Индексы не панацея. Во-первых, индексы не всегда можно создать (например если есть колонка с строкой неограниченной длины). Во-вторых, индексы не всегда работают. В-третьих, когда индексы работают - не факт, что они дадут прирост производительности.
Начать дискуссию