CC BY
6УДК 004.78
И.Г. Проценко, Н.С. Юсов
Камчатский государственный технический университет, Петропавловск-Камчатский, 683003 e-mail: ip1954@list.ru
ОПТИМИЗАЦИЯ РАБОТЫ С БАЗОЙ ДАННЫХ ИС «РЫБОЛОВСТВО»
В статье предлагается теоретический материал по способам оптимизации баз данных, показаны конкретные примеры использования распространенных методов оптимизации, такие как индексирование и выбор типа данных. Проведено исследование экономии памяти и производственной мощи при использовании методов оптимизации на примере отраслевой геоинформационной системы «Рыболовство».
Ключевые слова: оптимизация, тип данных, ER-модель, индексирование, таблица, СУБД.
I.G. Protsenko, N.S. Yusov
Kamchatka State Technical University, Petropavlovsk-Kamchatski/, 683003 e-mail: ip1954@list.com
OPTIMIZATION FOR FISHERY INFORMATION SYSTEM DATABASE
The article offers theoretical material on ways to optimize databases, shows specific examples of the use of common optimization methods, such as indexing and data type selection. The research of saving memory and production power when using optimization methods is carried out on the example of the information system "Fishing".
Key words: optimization, data type, ER model, indexing, table, DBMS.
Оптимизация работы с базами данных (БД) является важной задачей для продуктивного функционирования системы управления базами данных (СУБД) MySQL, средствами которой реализована физическая модель базы данных отраслевой геоинформационной системы мониторинга рыболовства (ОСМ) [1]. Игнорирование проблемы оптимизации баз данных способно привести к использованию лишнего объема памяти и производственных мощностей сервера, увеличению времени отклика СУБД [2]. Также возможно возникновение ошибок при выполнении операций c таблицами БД [3].
Одним из самых распространенных и продуктивных способов оптимизации баз данных являются индексирование и правильный выбор типов данных.
Индексирование - один из самых эффективных методов увеличения работоспособности базы данных, является одним из основных механизмов БД [4].
Зачастую строки БД хранятся в порядке их создания. Для чтения из записи данных требуется последовательное считывание соответствующих строк БД. Индексы создают отдельное множество строк, сопоставленных в порядке с выбранными индексами и хранящие указатели на исходные строки. Индексированные таблицы (ИТ) считываются гораздо быстрее, чем неиндек-сированные таблицы (НТ). Однако индексирование использует дополнительный объем данных. Помимо этого, на модификацию ИТ тратится больше временных ресурсов [5], поскольку все использующиеся индексы также приходится изменять. Система управления базами данных проводит индексирование автоматически; в крупных СУБД - индексирование происходит автоматически или выполняется выборочно.
Использование типов CHAR и VARCHAR в работе с базами данных MySQL
Тип данных CHAR является типом данных фиксированной длины. Он может хранить символы в строках размером до 8 000 байт. Тип данных CHAR наилучшим образом используется для хранения данных, которые имеют сопоставимую длину. Например, позывные или бортовые
номера рыбопромысловых судов и т. п. Тип данных CHAR является не лучшим выбором для хранения данных, которые сильно варьируются по длине. Столбцы, хранящие данные по типу названия книг или адресов, не подойдут для типа данных CHAR.
Это не означает, что столбец CHAR не может содержать значения, которые варьируются по размеру. Когда в столбец CHAR заносятся строки, которые короче, чем длина столбца, справа будут добавляться пробелы. Число этих пробелов определяется разностью между размером столбца и длиной сохраняемых символов.
Столбцы VARCHAR хранят данные переменной длины. Столбец изменчивого размера занимает только то место, которое требуется для хранения строки символов. По этой причине столбцы VARCHAR отлично подходят для хранения строк, которые сильно варьируются по размеру.
Для поддержки столбцов переменной длины необходимо, помимо самих данных, хранить их длину. Поскольку длина необходима для вычислений и используется СУБД при чтении и сохранении в столбцах переменной длины, считается, что они несколько менее производительны по сравнению со столбцами CHAR. Однако они используют только то пространство, которое им необходимо, экономия места на диске сама по себе может компенсировать потери производительности при использовании типа VARCHAR.
Различия типов данных CHAR и VARCHAR
Фундаментально отличие CHAR от VARCHAR состоит в том, что тип данных CHAR имеет фиксированную длину, в то время как тип данных VARCHAR поддерживает столбцы данных переменной длины. Но они и похожи. Оба предназначены для хранения алфавитно-цифровых данных. Для лучшего понимания разницы между этими двумя типами приведена табл. 1, где сделан обзор их подобия и отличий.
Таблица 1
Сравнение типов CHAR и VARCHAR
CHAR УАИСНАИ
Используется для хранения строк фиксированного размера Используется для хранения переменной длины
Размер может меняться в диапазоне от 1 до 8 000 байтов Размер может меняться в диапазоне от 1 до 8 000 байтов
Использует хранилище фиксированной величины на основе размера столбца Использует хранилище переменного размера на базе размера хранимой строки
Занимает от 1 до 4 байт на каждый символ в зависимости от коллации Занимает от 1 до 4 байт на каждый символ в зависимости от установленной коллации и одного или более байт для хранения данных
Лучше производительность Немного худшая производительность, поскольку требуется учитывать длину
Строки справа дополняются пробелами, если их длина меньше фиксированного размера Нет необходимости в дополнении пробелами в силу переменного размера
Сравнение производительности типов CHAR и VARCHAR
Объем операций, который выполняет СУБД при сохранении и извлечении столбцов VARCHAR больше, чем для столбца CHAR. При каждом извлечении информации из столбца VARCHAR СУБД должна использовать информацию о длине, хранящуюся вместе с данными в столбце VARCHAR.
Использование информации о длине вызывает лишние циклы работы ЦП. В то же время постоянная длина CHAR позволяет SOL Server быстрее выполнять навигацию, благодаря его фиксированной длине.
При работе со столбцами CHAR и VARCHAR проблемой может стать дисковое пространство. Поскольку столбец типа CHAR имеет фиксированную длину, он всегда будет занимать одинаковое пространство диска. Столбцы VARCHAR изменяются по размеру, поэтому необходимое пространство основывается на размере хранимых строк, а не на размере в определении столбца. Когда подавляющее большинство значений, хранимых в столбце CHAR, меньше заданного размера, то использование столбца VARCHAR может занимать меньше дискового пространства. Для небольшого объема таблиц, требуется меньше времени на операции ввода/вывода при работе с данными столбца, что означает улучшение производительности. Эти два соображения определяют выбор между CHAR и VARCHAR.
Тестирование скорости работы и объема занимаемой памяти
Для тестирования результатов оптимизации БД путем индексирования с использованием типов данных CHAR и VARCHAR нами были составлены модели двух баз данных в нотации Питера Чена, отображенные на рис. 1.
N ot_0 pt 1 m_test Optim_test
id fisli id reft
Fish Reg Catch V Catch id Size
V Size id fish Name
Fish
Id lufi Name
Re«
Рис. 1. ER-модель "Entity"
В базе данных not_optim_test находятся две аналогичные таблицы за исключением того, что в таблице catch типом данных выступает Char, а в таблице Catch_2 тип данных VarChar.
Вторая БД состоит из шести таблиц.
Первая и вторая таблица "сatch" (вылов) аналогичны друг другу, за исключением выбора типов данных char, varchar и содержат индексы (id_fish, id_reg) из 3-й - 6-й таблицы.
Третья и четвертая таблица (рис. 2) аналогичны друг другу, за исключением выбора типов данных char, varchar, и содержат идентификатор и наименование рыбы.
Пятая и шестая таблица (рис. 3) - аналоги друг друга, за исключением выбора типов данных char, varchar, и содержат идентификатор и наименование региона.
Какова же внутренняя суть процесса?
БД анализирует все возможные пути выполнения запроса, выбирая самый оптимальный из них.
Каждый возможный путь называется планом выполнения запросов. По сути это последовательность операций для получения результата SQL-запроса реляционной системе управления базами данных (СУРБД).
А компонент СУРБД, определяющий наиболее эффективный способ выполнения запроса с учетом анализа всех возможных планов, называется оптимизатором запросов.
Индексы используют оптимальный метод поиска, известный как двоичный поиск. Двоичный поиск - это алгоритм для поиска записи в отсортированном списке. Базируется на повторяющемся разделении данных пополам и определении того, находится ли искомая запись до или после записи в середине структуры данных. Если значение искомой записи меньше срединного, то поиск продолжается в первой половине, иначе - во второй. Эта процедура повторяется вплоть до нахождения значения. Благодаря данному методу уменьшается число требуемых поисков (табл. 2) и, следовательно, ускоряется выполнение запросов.
Пример соотношения записей данных и максимального количества операций поиска для нахождения нужной записи приведен в табл. 2.
В БД хранятся разные виды данных. Некоторые из них строго числовые, некоторые состоят из букв или комбинации букв и даже специальных символов. При хранении данных требуется, чтобы каждая часть данных имела тип.
При определении типа данных столбца важно подумать о виде информации, которая будет храниться, чтобы назначить правильный тип данных. Длина хранимого значения будет константной или динамической? С каким интервалом будут изменяться данные? Возможно ли
изменение длины столбца со временем? Возможны и другие обстоятельства, такие как необходимость продуктивного использования дискового пространства и производительности, которые способны подтолкнуть нас к принятию нужного типа данных.
Not_Optim_test Optimtest
catch Catch
Id_fish имеет Idreg
Рис. 2. ER-модель "Relationship"
^_optim_test
Рис. 3. Перечень таблиц в СУБД MySQL
Таблица 2
Демонстрация соотношения записей данных и максимального числа поисков
Количество записей данных Максимальное количество операций поиска для нахождения нужной записи (^„2)
8 3
100 7
1 000 10
10 000 14
100 000 17
1 000 000 20
Результаты тестирования
Было проведено тестирование созданных таблиц с помощью функций SELECT, UPDATE и DELETE. Результаты оптимизации видны только при работе с большим количеством информации, и чем больше ее объем, тем больший эффект она приносит. Для тестирования баз данных с помощью функций, в таблицы catch и catch_2 с помощью специальной команды было занесено 100 тысяч случайных записей, в таблицы fish и reg была занесено 10 записей.
Тестирование функцией SELECT.
Для исследования производительности баз данных была использована функция SELECT, выполняющая поиск информации в таблице при помощи заданных аргументов.
Информация о 10 аналогичных запросах была занесена в программу Excel, и при помощи этой программы составлен график (данные численных экспериментов приведены на рис. 4 и рис. 5), демонстрирующий работоспособность таблиц баз данных.
Рис. 4. График производительности таблиц БД при выполнении функции SELECT Также было проведено сравнение среднего времени выполнения данного запроса.
Рис. 5. Гистограмма средней производительности таблиц БД при выполнении функции SELECT
Исходя из данных, можно сказать, что при вызове функции SELECT индексированные таблицы вне зависимости от типов данных CHAR и VARCHAR работают со схожей скоростью, но превосходят в пять раз по скорости таблицы с типом данных VARCHAR и в 25 раз таблицы с типом данных CHAR.
Тестирование функцией UPDATE.
Для исследования производительности баз данных была использована функция UPDATE, выполняющая обновление информации в таблице при помощи заданных аргументов (данные численных экспериментов приведены на рис. 6 и 7).
Рис. 6. График производительности таблиц БД при выполнении функции UPDATE
Рис. 7. Гистограмма средней производительности таблиц БД при выполнении функции UPDATE
Исходя из данных численного эксперимента, можно сказать, что при вызове функции UPDATE индексированные таблицы вне зависимости от типов данных CHAR и VARCHAR работают со схожей скоростью.
Индексированные таблицы превосходят на 15% по скорости таблицу без индексов с типом varchar и в два раза опережают таблицу с типом данных char.
Тестирование функцией DELETE.
Для исследования производительности баз данных была использована функция DELETE, выполняющая удаление записей из таблицы при помощи заданных аргументов (данные численных экспериментов приведены на рис. 8 и 9).
Рис. 8. График производительности таблиц БД при выполнении функции DELETE
среднее время выполнения 10
Char Varchar Char+iridex Varchar+lndex ■
4,27613 3,63071 3,17663 6,30142 3
(1
5
3 Ш ш
Char | Varchar j Chart index j Vardiart-index средни время выполнения
Рис. 9. Гистограмма средней производительности таблиц БД при выполнении функции DELETE
На основе полученных результатов, можно утверждать, что при вызове функции DELETE индексированные таблицы не имеют преимущества. Вместо этого они уступают им в 2-3 раза.
С точки зрения оптимизации использования дискового пространства индексирование дает преимущество перед неоптимизированными БД: при индексировании сэкономленное пространство в данном численном эксперименте показало экономию памяти более 50%, что отражено в табл. 3.
Таблица 3
Сравнение объемов занимаемой памяти
Объем занимаемой памяти
Char Varchar Char+index Varchar+index
36,5Mb 35,6Mb 21,3 20,5
Таким образом, были проведены расчеты и численные эксперименты по исследованию подходов к оптимизации баз данных MySQL путем индексирования и изменения типов данных. В результате получены результаты оптимизации тестовых БД, рассмотрена внутренняя суть процесса оптимизации.
Проведенное исследование наглядно показывает важность оптимизации путем индексирования и изменения типов данных при создании БД. Ее отсутствие может привести к серьезным потерям памяти и производственной мощности на БД больших объемов.
Литература
1. Проценко И.Г. Информационная система мониторинга рыболовства // Рыбное хозяйство. Спец. вып. - 2001. - С. 3-18.
2. Базы данных: теория нормализации: Методические указания / Сост. Н.А. Кривошеева, М.Г. Таспаева; Оренбургский гос. ун-т. - Оренбург: ОГУ, 2021. - 48 с
3. Карпова И.П. Базы данных: Учебное пособие / Московский государственный институт электроники и математики (Технический университет). - М., 2009. - 131 с
4. Кузнецов С.Д. Основы баз данных: Учебное пособие. - 2-е изд., испр. - М.: Интернет-университет информационных технологий: БИНОМ. Лабораторная знаний, 2007. - 484 с.
5. Верхолат А.М., Суслов В.П. Проектирование структуры базы данных: Пособие по курсовому проектированию. Изд. 2-е, испр. и доп. / Балт. гос. техн. ун-т. - СПб., 2018. - 65 с.
