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

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

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



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

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

Одним из методов оптимизации хранения табличных данных и, соответственно, увеличения производительности SQL-запросов к серверу является секционирование таблиц. Секционирование подразумевает физическое разбиение таблицы на несколько секций, которые хранятся в разных файлах. Разбиение происходит по определенным параметрам, задаваемым администратором базы данных. Такой подход приносит ряд существенных преимуществ.

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

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

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

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

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

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

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

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

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

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

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

Рассмотрим запрос (Рис. 4). Он возвращает все числа из таблицы LargeTable (1000000 записей), которые встречаются в таблице GreatTable (10000000 записей), и количество раз, которое оно встречается.

Рис. 4. Запрос на объединение данных

Время выполнения запроса составило 16700 мс. А теперь рассмотрим план выполнения запроса (Рис. 5):

Рис. 5. План выполнения запроса

План запроса демонстрирует последовательность действий сервера и, самое важное, временные затраты на тот или иной шаг. Видно, что просмотр строк таблицы GreatTable (10000000 строк) занимает почти половину от общего времени, а еще четверть — агрегатная функция (группировки). Отметим также, что планировщик сразу указал на отсутствие индекса для LargeTable, давая понять, что добавление оного ускорит выполнение запроса.

Попробуем оптимизировать запрос, опираясь на план и используя индексы. После добавления предложенного планировщиком индекса время выполнения запроса составило 14535мс, однако, снова посмотрев на план, можно заметить, что главные временные затраты не изменились (Рис. 6). На рисунке также приведен увеличенный фрагмент предыдущего плана:

Рис. 6. Сравнение двух планов выполнения запроса

Очевидно, что нам нужно ускорить время обработки таблицы GreatTable. Данные, которые мы получили ранее, говорят о том, что секционирование этой таблицы даст ощутимый прирост во времени. Секционировав таблицу, мы получили время выполнения запроса 9720 мс. Наконец, отменим секционирование и некластеризованный индекс, заменив их кластеризованными на обеих таблицах. Время выполнения запроса составило 10812 мс. Это демонстрирует нам, что эффективная (как казалось) оптимизация, на деле лишь затормозила выполнение нашего запроса.

Литература:

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


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

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

Индексы упорядочивают данные таблиц, оптимизируя время поиска данных (подобно бинарному поиску по упорядоченному массиву данных).

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

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

Рис. 1 – Оценка SQL запроса до его выполнения. При поступлении очередного SQL запроса к базе данных сенсорная подсистема СОВ производит перехват

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

Методы выполнения запросов к хранилищу данных в Hadoop...

Библиографическое описание: Ложкин О. Методы выполнения запросов к хранилищу данных в Hadoop

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

При чтении данных нужно запросить у мастера метаданные, но сами данные читаются...

Язык R в базе данных Oracle | Статья в журнале «Молодой ученый»

‒ Выполнять параллельное и непараллельное выполнение данных. ‒ Использовать пакеты CRAN с открытым исходным кодом. Будучи языком статистического программирования, R предсказуемо предлагает богатый набор инструментов для анализа данных.

Асинхронное выполнение SQL-запросов на языке...

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

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

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

Обработка и загрузка данных для тендеров по лекарственным...

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

Системы сбора информации в аспекте кибербезопасности

Демон собирает результаты запроса с течением времени и генерирует журналы, которые указывают

Запись информации не происходит все время. Вместо этого, когда требуется часть

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

Интеграция Java-приложения с базой данных MongoDB

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

Обсуждение

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

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

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

Индексы упорядочивают данные таблиц, оптимизируя время поиска данных (подобно бинарному поиску по упорядоченному массиву данных).

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

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

Рис. 1 – Оценка SQL запроса до его выполнения. При поступлении очередного SQL запроса к базе данных сенсорная подсистема СОВ производит перехват

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

Методы выполнения запросов к хранилищу данных в Hadoop...

Библиографическое описание: Ложкин О. Методы выполнения запросов к хранилищу данных в Hadoop

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

При чтении данных нужно запросить у мастера метаданные, но сами данные читаются...

Язык R в базе данных Oracle | Статья в журнале «Молодой ученый»

‒ Выполнять параллельное и непараллельное выполнение данных. ‒ Использовать пакеты CRAN с открытым исходным кодом. Будучи языком статистического программирования, R предсказуемо предлагает богатый набор инструментов для анализа данных.

Асинхронное выполнение SQL-запросов на языке...

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

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

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

Обработка и загрузка данных для тендеров по лекарственным...

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

Системы сбора информации в аспекте кибербезопасности

Демон собирает результаты запроса с течением времени и генерирует журналы, которые указывают

Запись информации не происходит все время. Вместо этого, когда требуется часть

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

Интеграция Java-приложения с базой данных MongoDB

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

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