ОЦЕНКА ПОКАЗАТЕЛЕЙ РАЗНООБРАЗИЯ ТИПОВ ДАННЫХ В ФИЗИЧЕСКИХ СХЕМАХ БАЗ ДАННЫХ Текст научной статьи по специальности «Компьютерные и информационные науки»

Cloud of Science. 2020. T. 7. № 3 http:/ / cloudofscience.ru

Оценка показателей разнообразия типов данных в физических схемах баз данных

А. А. Рыбанов

Волжский политехнический институт (филиал) Волгоградский государственный технический университет 404121, Волжский, ул. Энгельса, 42а

e-mail: гуЪапо//@уапйех.ги

Аннотация. При проектировании баз данных информационных систем одним из важных этапов является правильный выбор типов данных полей в таблицах. Проведено сравнение применимости используемых в экологии индексов разнообразия для оценки распределения типов данных для физических схем баз данных. Для оценки разнообразия типов данных в физической схеме базы данных используются следующие меры доминирования: индекс разнообразия Симпсона, индекс разнообразия Шеннона, индекс выравненности Симпсона, индекс выравненности Пи-елу. Сравнительный анализ показателей разнообразия типов данных для физических схем баз данных показал, что значения некоторых индексов подчиняются определенным правилам. Предлагается индексы разнообразия использовать для качественной оценки физических схем баз данных и для построения новых моделей оценки сложности баз данных. Ключевые слова: индекс разнообразия, индекс Шеннона, индекс Симпсона, индекс Пиелу, база данных, MySQL, оценка качества.

1. Введение

Основное влияние на эксплуатационные характеристики базы данных (БД) оказывает физическая организация данных. Для того, чтобы база данных функционировала правильно и была максимально оптимизирована, важно тщательно выбирать типы данных для каждого поля в таблицах [1]. Разработчики баз данных должны знать и понимать, как использовать каждый из них правильно. Определяемые пользователями типы данных позволяют существенно усилить контроль над данными и повысить их целостность [2]. Правильно выбранные типы полей позволяют уменьшить физический размер строк таблицы и, соответственно, базу данных, делая чтение и запись быстрее и эффективнее.

2. Рекомендации по корректировке типов полей

Рекомендации по корректировке полей рассмотрим на примере СУБД MySQL. MySQL поддерживает большое разнообразие типов данных, и выбор правильного

типа для хранения данных имеет решающее значение для достижения хорошей производительности. Для некоторых полей таблицы БД типы данных могут быть изменены без существенного влияния на существующую схему базы данных. Рассмотрим следующие примеры.

Возраст человека. Для хранения возраста человека рекомендуется использовать поле с типом TINYINT UNSIGNED, который может хранить максимальное значение 256. Однако, не задумываясь, кто-то может использовать тип INTEGER UNSIGNED. Но при использовании типа данных TINYINT для хранения возраста требуется 1 байт, взамен 4 байтов при использовании типа данных INTEGER.

Типы DATE и TIME. Типы полей DATE и TIME занимают по 3 байта, в то время как поле типа DATETIME — 8 байт, но хранение всей информации в одном поле более предпочтительно для оптимизации обработки данных.

Замена BIGINT на INT. Уменьшить размер первичного ключа с 8 до 4 байтов можно путем изменения его типа данных BIGINT UNSIGNED AUTO_INCREMENT на тип данных INT UNSIGNED AUTO_INCREMENT. Данное преобразование возможно не только для первичного ключа, но и для всех внешних ключей, которые определены как BIGINT. Данный поход может значительно уменьшить пространство, необходимое для индексов в сильно нормализованной базе данных.

IP-адреса. Полю базы данных, предназначенному для хранения IPv4 адресов, как правило присваивают тип VARCHAR(15), размер которого в среднем составляет 12 байт. В целях уменьшения исходного размера столбца, рекомендуется для поля, содержащего IPv4-адрес, определять тип данных INT UNSIGNED, требующий 4 байта. Преобразования IP-адреса возможно с помощью функций INET_ATON() и INET_NTOA().

Пример: SET @ip = '255.139.67.15';

SELECT @ip, INET_ATON(@ip) AS str_to_i, INET_NTOA (INETATON (@ip)) AS i_to_str;

MD5 значения. Распространенной практикой является хранение значения MD5 в поле, имеющим тип CHAR (32). Для более эффективного хранения шестнадцате-ричного значения MD5 можно использовать функции UNHEX() и HEX(), сохраняя данные в типе данных BINARY(16). Выполнение этого преобразования позволяет уменьшить размер поля в два раза.

Пример:

SET @str = 'wwwvolpiru';

SELECT MD5(@str), LENGTH(MD5(@str)) AS len_md5, LENGTH(UNHEX (MD5 (@str))) AS len_unhex;

Функция PROCEDURE ANALYSE() позволяет получить рекомендации сервера баз данных MySQL по корректировке типов полей для таблицы, заполненной реальными данными, наличие которых играет существенную роль при принятии

решений. Предлагаемые функцией PROCEDURE ANALYSE() оптимальные типы данных для каждого поля, ориентированы на решение проблемы уменьшения размера таблицы, и как следствие, всей базы данных.

Синтаксис использования функции: SELECT * FROM <table_name> PROCEDURE ANALYSE([<max_elements>, [<max_memory]]); где <table_name> — имя анализируемой таблицы базы данных; <maх_elements> — максимальное количество различных значений, содержащихся в поле, которое функция PROCEDURE ANALYSE использует для проверки того, является ли оптимальным типом для поля тип ENUM (по умолчанию 256); <max_memory> — максимальное количество памяти, которое может занимать тип поля ENUM (по умолчанию 8192 байт).

Для исключения в результатах работы функции PROCEDURE ANALYSE рекомендаций типа ENUM для полей, которые содержат более 16 уникальных значений или занимают более 256 байтов нужно задать следующие значения аргументов: SELECT * FROM <table_name> PROCEDURE ANALYSE(16, 256);

Необходимо учитывать, что выдаваемые функцией PROCEDURE ANALYSE результаты это всего лишь рекомендации, на достоверность которых существенно влияет количество строк в таблице. И если таблица базы данных в дальнейшем будет пополняться новыми данными, то эти рекомендации могут оказаться неверными, поэтому принятие решения об их применении остается за администратором базы данных.

Общие положения по применению типов полей можно резюмировать в виде следующих рекомендаций:

- рекомендуется объявлять поля как NOT NULL;

- рекомендуется использовать типы полей, которые занимают меньше памяти;

- для цифровых данных не рекомендуется использовать символьные поля;

- рекомендуется использовать поля фиксированной длины, т. к. они обрабатываются быстрее;

- по возможности, рекомендуется использовать типы ENUM и SET;

- рекомендуется для хранения дат и времени использовать специализированные типы;

- рекомендуется использовать функцию PROCEDURE ANALYSE для проверки характеристик полей таблиц, заполненных реальными данными;

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

3. Постановка задачи

Физическая схема реляционной базы данных содержит все детали, необходимые конкретной СУБД для создания базы: наименования таблиц и столбцов, типы полей, определения первичных и внешних ключей, индексов.

Контроль качества физической схемы БД невозможен без числовых показателей. При отсутствии количественных измерений трудно принимать какие-либо проектные решения. Существующие модели количественной оценки сложности физических схем реляционных баз данных [3, 5, 6] не учитывают разнообразие используемых в них типов данных, поэтому задача формирования системы количественных критериев для оценки разнообразия типов данных, используемых в физической схеме БД, является актуальной.

Рассмотрим применение показателей экологического разнообразия для решения задачи оценки разнообразия типов данных в физической схеме базы данных для СУБД MySQL.

4. Метрики разнообразия типов данных

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

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

б) выравненность или равномерность распределения обилия типов данных в физической схеме БД.

Для оценки разнообразия типов данных в физической схеме базы данных воспользуемся следующими мерами доминирования, учитывающими выравненность: индекс разнообразия Симпсона, индекс разнообразия Шеннона, индекс выравнен-ности Симпсона, индекс выравненности Пиелу.

Индекс разнообразия Симпсона (D) рассчитывается по формуле:

где £ — количество типов данных в физической схеме БД (видовое богатство); Р — доля /-го типа данных в суммарной численности полей всех типов.

Чем больше индекс разнообразия Симпсона приближается к видовому богатству £, тем разнообразнее (с точки зрения используемых типов данных) рассматриваемая физическая схема БД.

(1)

В табл. 1-2 приведены данные для расчета индекс разнообразия Симпсона для физических схем учебных MySQL баз данных catalog и northwind (dev.mysql.com).

Таблица 1. Данные для расчета индекса разнообразия Симпсона для базы данных catalog

№ Тип данных Pi Количество полей данного типа

1 BLOB 0.025974 2

2 DATE 0.012987 1

3 INT 0.519481 40

4 SMALLINT 0.025974 2

5 TEXT 0.116883 9

6 TINYINT 0.012987 1

7 VARCHAR 0.272727 21

8 YEAR 0.012987 1

Видовое богатство S 77

Таблица 2. Данные для расчета индекса разнообразия Симпсона для базы данных northwind

№ Тип данных Pi Количество полей данного типа

1 BLOB 0.022472 2

2 DATE 0.056180 5

3 DECIMAL 0.033708 3

4 DOUBLE 0.011236 1

5 INT 0.033708 3

6 MEDIUMINT 0.044944 4

7 SMALLINT 0.089888 8

8 TEXT 0.044944 4

9 TINYINT 0.078652 7

10 VARCHAR 0.584270 52

Видовое богатство S 89

Тип данных считается доминирующим в физической схеме БД, если его количество составляет 50 и более % от всего числа рассматриваемых типов, редким — если менее 10%, а уникальным — менее 2% (рис. 1).

int varchar text blob smallint date tinyint year Тип ПОЛЯ

Рисунок 1. Кривая значимости типов данных физической схемы БД catalog

Индекс разнообразия Шеннона (Н) рассчитывается по формуле:

н = -£ р1п р. (2)

7 = 1

Чем больше значение индекса Шеннона, тем выше разнообразие типов данных в физической схеме БД.

Индекс выравненности Симпсона (Е) рассчитывается по формуле:

Е = Б/Б. (3)

Чем больше Е приближается к единице, тем равномернее представлены типы данных в физической схеме базы данных.

Индекс выравненности Пиелу (е) рассчитывается на основе индекса Шеннона:

е = Н/ 1п Б. (4)

Индекс Пиелу характеризует выравненность типов данных в физической схеме БД. Величина индекса Пиелу изменяется от 0 до 1. Чем более равномерно представлены в физической схеме БД составляющие ее типы данных, тем ближе его значение к единице.

В табл. 3 приведены показатели разнообразия типов данных для различных физических схем учебных и промышленных БД.

Таблица 3. Показатели разнообразия типов данных для физических схем БД

Наименование физической схемы БД Акроним Назначение БД S D H E e

flight FL учебная 9 1.9692 0.9508 0.4923 0.6858

world WO учебная 5 3.0316 1.3115 0.6063 0.8149

music MU учебная 4 2.1304 0.9911 0.5326 0.7149

employees ЕМ учебная 5 3.2799 1.3478 0.6559 0.8374

university UN учебная 4 2.0864 0.9693 0.5216 0.6992

classicmodels CL промышленная 8 2.5652 1.3559 0.3207 0.652

retailer RE учебная 8 2.5728 1.3659 0.3216 0.6568

chinook CH учебная 4 2.3406 0.9907 0.5851 0.7146

contracts CO учебная 6 3.0422 1.3281 0.5070 0.7413

northwind NO учебная 10 2.7342 1.5354 0.2734 0.6668

sakila SA учебная 14 5.4359 1.9983 0.3883 0.7572

catalog CA учебная 8 2.7797 1.3044 0.3475 0.6273

moodle MO промышленная 14 3.0758 1.5446 0.2197 0.5853

kadr oop KO промышленная 10 2.6165 1.3174 0.2617 0.5722

Сравнительный анализ показателей разнообразия типов данных для физических схем БД (табл. 3), показывает, что по значениям индексов учебные БД не выделяются на фоне промышленных БД, а также значения некоторых индексов подчиняются определенным правилам.

Так, значение индекса выравненности Симпсона для рассмотренных физических схем баз данных различной сложности (рис. 2) находится в интервале от 0.2197 до 0.65597. Значение индекса выравненности Пиелу лежит в интервале от 0.57215 до 0.83742.

КО МО СА С1_ РЕ N0 Р1_ им СН М11 СО БА \Л/0 ЕМ I Индекс выравненности Симпсона Я Индекс выравненности Пиелу

Рисунок 2. Индексы выравненности Симпсона и Пиелу

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

Для решения задачи оценки сходства двух баз данных по составу типов данных в физических схемах применим коэффициенты подобия Жаккара и Серсена — Чека-новского.

Для вычисления сходства по качественным признакам, воспользуемся индексом сходства видового состава Жаккара, который рассчитывается как:

1, , (5)

а + Ь + с

где а — число общих типов данных для двух баз данных, Ь — число типов, имею-

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

Для оценки сходства количественных признаков воспользуемся индексом Се-ресена — Чехановского, рассчитываемым как:

_ тт(х,; хы)

^_ ^ ^ , (5)

/ , Ха( + ^ ХЫ

где хш. и хы — количество полей /-го типа в первой и второй базе данных;

шт( хш; хы ) — наименьшее из значений величин обилия /-го типа в сравниваемых базах данных.

Ij = 0.64.

По индексу Жаккара состава по типам полей для баз данных catalog и northwind сходен на 64%:

7 +1 + 3

По индексу Серсена-Чекановского состав по типам полей для БД catalog и northwind сходен на 41%, поэтому в данном случае можно говорить о низкой степени сходства двух сравниваемых баз данных:

2 • (2 +1 + 0 + 0 + 3 + 0 + 2 + 4 +1 + 21 + 0) I, =—--- = 0.41.

J (77 + 89)

В табл. 4 представлены индексы подобия для физических схем БД различной сложности.

Таблица 4. Индексы подобия Жаккара и Серсена-Чекановского для физических схем БД

Акроним БД

FL WO MU ЕМ UN CL RE CH CO NO SA CA MO КО

FL - 0.29 0.33 0.29 0.60 0.20 0.20 0.00 0.11 0.06 0.13 0.04 0.06 0.08

WO 0.60 - 0.29 0.33 0.29 0.18 0.18 0.13 0.22 0.07 0.27 0.10 0.27 0.07

MU 0.27 0.21 - 0.13 0.30 0.05 0.08 0.03 0.02 0.17 0.29 0.04 0.13 0.08

ЕМ 0.26 0.43 0.13 - 0.29 0.30 0.30 0.29 0.57 0.20 0.27 0.24 0.19 0.25

UN 0.62 0.52 0.33 0.18 - 0.20 0.20 0.00 0.11 0.07 0.20 0.08 0.06 0.08

CL 0.08 0.12 0.09 0.36 0.07 - 0.45 0.20 0.27 0.64 0.22 0.51 0.38 0.38

RE 0.08 0.12 0.20 0.36 0.07 0.88 - 0.33 0.56 0.70 0.47 0.56 0.38 0.50

CH 0.00 0.09 0.14 0.25 0.00 0.70 0.75 - 0.43 0.52 0.29 0.64 0.29 0.27

CO 0.05 0.13 0.11 0.39 0.02 0.71 0.78 0.86 - 0.56 0.33 0.61 0.33 0.45

NO 0.17 0.15 0.17 0.25 0.17 0.50 0.80 0.27 0.45 - 0.50 0.41 0.50 0.43

SA 0.03 0.08 0.29 0.10 0.06 0.44 0.51 0.44 0.44 0.63 - 0.33 0.47 0.26

CA 0.20 0.18 0.09 0.30 0.33 0.45 0.78 0.20 0.40 0.64 0.47 - 0.29 0.38

MO 0.00 0.01 0.01 0.01 0.00 0.05 0.05 0.05 0.05 0.09 0.11 0.04 - 0.50

КО 0.01 0.02 0.00 0.09 0.00 0.22 0.24 0.26 0.29 0.30 0.27 0.28 0.14 -

В верхней части табл. 4 приведены значения коэффициентов сходства Жаккара (/,), в нижней — значения коэффициентов сходства Серсена — Чекановского (/Б). Например, из табл. 4. следует, что при оценке сходства проектов БД ЯЕ, СН, СО с проектом базы данных МО значения индексов /5, учитывающих качественные признаки, равны: /5 (КЕ,МО) = /8 (СН,МО) = (СО,МО) = 0.05. В тоже время, при оценке сходства проектов БД ЯЕ, СН, СО с проектом базы данных МО значения индексов / , учитывающих количественные признаки, имеют совершенно разные значения: (ЯЕ,МО) = 0.38; (СН,МО) = 0.29; (СО,МО) = 0.33.

В качестве меры, одновременно учитывающей качественные и количественные признаки сходства БД по составу типов полей, примем следующий интегральный индекс:

I = wij + щ Is, (7)

где щ, щ — весовые коэффициенты для индексов Жаккара и Серсена — Чеканов-ского.

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

6. Заключение

Рассмотренные показатели разнообразия типов данных (1)-(4) в физических схемах БД дополняют существующие на данный момент метрические характеристики баз данных. Дополнительные исследования закономерностей распределения значений показателей разнообразия типов данных на больших коллекциях баз данных позволят сформировать набор правил для качественной оценки физических схем баз данных.

Предложенная система количественных показателей (5)-(7) расширяет множество критериев для анализа на наличие плагиата в исходном программном коде.

Литература

[1] Черняев А. О., Рыбанов А. А. Разработка и исследование алгоритмов автоматизированного проектирования логических схем реляционных баз данных // В мире научных открытий. 2010. № 4-11 (10). С. 128-129.

[2] Нидзий А. В., Рыбанов А. А. Исследование метрических характеристик физических схем реляционных баз данных // Научное обозрение. Педагогические науки. 2019. № 33. С. 75-77.

[3] Рыбанов А. А., Свиридова О. В., Короткова Н. Н., Лясин Д. Н., Абрамова О. Ф. Модель оценки сложности физической схемы реляционной базы данных // Инженерный вестник Дона. 2019. № 3 (54). С. 12.

[4] Piattini M., Calero C., Genero M. Table oriented metrics for relational Databases // Software Quality Journal. 2001. Vol. 9. No. 2. P. 79-97.

[5] Calero С., Piattini M., Genero M. A case study with relational database metrics // Proceedings ACS/IEEE International Conference on Computer Systems and Applications. — Beirut, Lebanon, 2001. P. 485-487. doi: 10.1109/AICCSA.2001.934049

[6] Paul R. A., Kunii T. L., Shinagawa Y., Khan M. F. Software metrics knowledge and databases for project management // IEEE Transactions on Knowledge and Data Engineering.1999. Vol. 11. No. 1. P. 255-264. doi: 10.1109/69.755633

Автор:

Александр Александрович Рыбанов — кандидат технических наук, доцент, заведующий кафедрой «Информатика и технология программирования», Волжский политехнический институт (филиал) Волгоградского государственного технического университета

Assessment of Diversity Indicators of Data Types for Physical Schemes of Databases

A. A. Rybanov

Volzhskiy Polytechnical Institute, branch of the Volgograd State Technical University Engelsa st., 42a, Volzhskii, 404121, Russian Federation e-mail: [email protected]

Abstract. When designing information system databases, one important step is to correctly select the field data types in the tables. Comparison of applicability of the diversity indexes of used in ecology for assessment of distribution of data types for physical schemes of databases is carried out. The following dominance measures are used to estimate the diversity of data types in the physical database schema: Simpson's diversity index, Shannon diversity index, Simpson's evenness index, Pielu evenness index. A comparative analysis of diversity indices of data types for physical database schemes has shown that the values of some indices are subject to certain rules. It is offered to use diversity indexes for quality assessment of physical schemes of databases. The indexes discussed in the paper can also be used to build new database complexity assessment models.

Keywords: diversity index, Shannon index, Simpson index, Pielou index, database, MySQL, quality assessment.

References

[1] Chernjaev A. O., Rybanov A. A. (2010) Vmire nauchnykh otkrytiy, (4-11): 128—129. [In Rus]

[2] Nidzy A. V., Rybanov A. A. (2019) Nauchnoye obozreniye. Pedagogicheskiye nauki, (3):75—77. [In Rus]

[3] Rybanov A. A., Sviridova O. V., Korotkova N. N., Lyasin D. N., Abramova O. F. (2019) Inzhenernyy vestnikDona, 3(54):12. [In Rus]

[4] Piattini M., Calero C., Genero M. (2001) Software Quality Journal, 9(2):79—97.

[5] Calero C., Piattini M., Genero M. (2001) A case study with relational database metrics. In Proceedings ACS/IEEE International Conference on Computer Systems and Applications. Beirut, Lebanon. pp. 485— 487. doi: 10.1109/AICCSA.2001.934049

[6] Paul R. A. et al. (1999) IEEE Trans. on Knowledge and Data Engineering, 11(1): 255—264.