Применение индексирования для ускорения запросов к базе данных | Статья в журнале «Молодой ученый»

Библиографическое описание:

Коптенок Е. В., Кузин А. В., Шумилин Т. Б., Храмченко В. Д., Крахмалев Н. О. Применение индексирования для ускорения запросов к базе данных // Молодой ученый. — 2019. — №4. — С. 8-12. — URL https://moluch.ru/archive/242/55870/ (дата обращения: 24.02.2019).



Индексирование таблиц в базах данных является отличным средством повышения производительности SQL-запросов. Физически все данные таблиц хранятся в файлах, которые разбиты на страницы (стандартный размер — 8КБ), без соблюдения какой-либо физической структуры. Индексы упорядочивают данные таблиц, оптимизируя время поиска данных (подобно бинарному поиску по упорядоченному массиву данных).

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

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

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

В данной статье будут приведены результаты работы, цель которой — изучить влияние использования различных индексов на скорость выполнения запросов к базе данных. Тестировать различные запросы будем на таблицах, содержащих два поля: числовое и текстовое. Таблицы будут заполнены случайными данными (числа до 1000000 в числовом поле и строка из 10 символов английского алфавита — в текстовом). Проверять скорость выполнения запросов будем на разных объемах данных: 10000, 100000, 1000000 и 10000000 записей.

Изначально таблицы не будут проиндексированы или секционированы. Сравнивать индексы будем по двум параметрам: эффективность относительно неиндексированной таблицы и увеличение времени выполнения запроса относительно увеличения объема данных.

Для начала, рассмотрим время выполнения запросов с выборкой только проиндексированного столбца. Будем рассматривать запросы на выборку по всей таблице с сортировкой, группировкой по числовому столбцу, выборку по условию (≈40 % от всей таблицы) и с группировкой по условию. Примеры запросов представлены на Рис. 1:

Рис. 1.Запросы к таблицам базы данных а) выборка всей таблицы с сортировкой; б) группировка по числовому столбцу; в) выборка по условию; г) группировка с условием

Отметим, что при каждом запуске запроса будет происходить очистка буфера. Сделано это для того, чтобы при повторном запуске запроса сервер БД не использовал предварительно скомпилированные данные от предыдущего выполнения. Это важно, так как каждый запрос будет запускаться несколько раз, и будет браться среднее время, чтобы результаты были более объективные. Также результаты запросов будут выводиться в текстовый файл, чтобы вывод данных занимал меньше времени.

Сравнение времени выполнения запросов для таблицы, содержащей 10000 строк, представлены на Рис. 2:

Рис. 2. Время выполнения запросов к таблицам данных а) 10000 строк; б) 100000 строк; в) 1000000 строк; г) 10000000 строк

Заметим, что если выборка идет только по индексу, то для небольших наборов данных индексирование малоэффективно, а columnstore эффективен только для наборов более 10000000 строк, особенно, если учесть затраты на перестройку и поддержание такого способа хранения. Некластеризованный индекс относительно эффективен для любых объемов, но стоит учесть, что он физически перестраивает хранение данных, соответственно, для одной таблицы может быть только один кластеризованный индекс. Некластеризованный индекс имеет в среднем схожую эффективность как с фильтром, так и без, но фильтрованный индекс эффективнее для небольших объемов данных.

Рассмотрим эффективность различных индексов на аналогичных запросах, но с возвратом полных кортежей данных. Снова проведем измерения на таких же объемах данных. Результаты представлены на Рис. 3а — 3г:

Рис. 3а. Время выполнения различных запросов к таблицам данных — 10000 записей

Рис. 3б. Время выполнения различных запросов к таблицам данных — 100000 записей

Рис. 3в. Время выполнения различных запросов к таблицам данных — 1000000

Рис. 3г. Время выполнения различных запросов к таблицам данных — 10000000 записей

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

Однако, в целом индексы остаются предпочтительными при использовании на больших объемах данных. Особенно для объемов от 10000000 записей эффективен columnstore — на всех запросах среднее время выполнения примерно на 40 % быстрее, чем без использования индексов, тогда как обычные индексы ускоряют процесс всего до 25–30 %. Заметим также, что максимальной эффективности индексы достигают, когда запросы предполагают выборку по условию.

Рис. 4.График роста времени выполнения запросов при росте объема данных а) Запрос с сортировкой; б) Запросы с группировкой; в) Выборка по условию; г) Группировка по условию

На Рис. 4 можно проследить, как быстро растет время выполнения запросов при росте объема данных. Отметим, линейную зависимость запросов на выборку и логарифмическую — при запросах на группировку. Практически во всех случаях использование кластеризованного индекса гарантирует рост эффективности. Впрочем, так как первичный ключ является как раз кластеризованным индексом, то это — довольно частое явление. Если группировка данных будет производиться не по первичному ключу, то уместно использовать некластеризованные индексы по полям группировки, особенно, учитывая низкие временные затраты на их создание и поддержание.

Литература:

  1. Дунаев, В. В. Базы данных. Язык SQL для студента / В. В. Дунаев. — М.: БХВ-Петербург, 2017. — 288 c.
  2. Аллен, Г. Тейлор SQL для чайников / Аллен Г. Тейлор. — М.: Диалектика, Вильямс, 2015. — 416 c.
  3. Владимир, Михайлович Илюшечкин Основы использования и проектирования баз данных / Владимир Михайлович Илюшечкин. — М.: Юрайт, 2015. — 516 c.
  4. Карвин, Билл Программирование баз данных SQL. Типичные ошибки и их устранение / Билл Карвин. — М.: Рид Групп, 2018. — 336 c.
Основные термины (генерируются автоматически): индекс, время выполнения, таблица данных, запрос, таблица, кластеризованный индекс, время выполнения запросов, объем данных, группировка, выборка.


Похожие статьи

Применение секционирования таблиц для ускорения запросов...

На Рис. 2 представлено сравнение времени выполнения запросов данных к секционированным и несекционированным таблицам. Полученные результаты показывают, что запросы к секционированным таблицам обрабатываются примерно за то же время, что и к...

Использование апостериорного анализа данных для обнаружения...

Однако если рассматривать запрос уже после его выполнения, то цель запроса становится известной – СУБД возвратит данные

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

Анализ поисковых алгоритмов при решении задач идентификации...

База данных; расстояние Хемминга; сравнение строк; расстояние Левенштейна; метод расширенной выборки; сигнатурный

Для решения проблем, связанных с организации хранения и централизованного управления большими объемами разнородных данных...

Второй шаг выполнения запроса.

Блоки таблицы распределены по узлам произвольным образом. Первый шаг выполнения запроса. Map1

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

Осваиваем работу с реляционными базами в MS Excel 2013

Таблицы, являющиеся частью базы данных, должны быть представлены в табличном редакторе

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

Возможности функции ИНДЕКС более широкие, но требуют более внимательного...

Особенности вычисления временных интервалов в Excel

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

Обзорное описание метода Multi-Fragment-ReplicationJoin...

Все блоки таблиц измерений и групп таблицы фактов распределены MapReduce по узлам

Если у вас несколько узлов, их можно кластеризовать, а затем, очереди и темы, можно

Для того, чтобы разместить этот огромный объем данных, система syslog-ng поддерживает...

Сводные таблицы в MS Excel 2013 | Статья в сборнике...

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

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

Особенности проектирования и разновидности хранилищ данных

В результате, выполнение сложных запросов приводит к тому, что необходимо объединить между собой большое количество таблиц, что

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

Обсуждение

Социальные комментарии Cackle

Похожие статьи

Применение секционирования таблиц для ускорения запросов...

На Рис. 2 представлено сравнение времени выполнения запросов данных к секционированным и несекционированным таблицам. Полученные результаты показывают, что запросы к секционированным таблицам обрабатываются примерно за то же время, что и к...

Использование апостериорного анализа данных для обнаружения...

Однако если рассматривать запрос уже после его выполнения, то цель запроса становится известной – СУБД возвратит данные

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

Анализ поисковых алгоритмов при решении задач идентификации...

База данных; расстояние Хемминга; сравнение строк; расстояние Левенштейна; метод расширенной выборки; сигнатурный

Для решения проблем, связанных с организации хранения и централизованного управления большими объемами разнородных данных...

Второй шаг выполнения запроса.

Блоки таблицы распределены по узлам произвольным образом. Первый шаг выполнения запроса. Map1

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

Осваиваем работу с реляционными базами в MS Excel 2013

Таблицы, являющиеся частью базы данных, должны быть представлены в табличном редакторе

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

Возможности функции ИНДЕКС более широкие, но требуют более внимательного...

Особенности вычисления временных интервалов в Excel

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

Обзорное описание метода Multi-Fragment-ReplicationJoin...

Все блоки таблиц измерений и групп таблицы фактов распределены MapReduce по узлам

Если у вас несколько узлов, их можно кластеризовать, а затем, очереди и темы, можно

Для того, чтобы разместить этот огромный объем данных, система syslog-ng поддерживает...

Сводные таблицы в MS Excel 2013 | Статья в сборнике...

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

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

Особенности проектирования и разновидности хранилищ данных

В результате, выполнение сложных запросов приводит к тому, что необходимо объединить между собой большое количество таблиц, что

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

Задать вопрос