Оптимизация хранения данных изучение типов данных и нормализация в SQL

Оптимизация хранения данных и нормализация в SQL

 

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

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

 

Типы данных в SQL

 

В SQL существуют в основном два типа данных: строковые и числовые. Кроме этого, есть дополнительные типы данных, такие как булев, дата и время, массив, интервал, XML и т. д.

 

Типы данных строк

 

Эти типы данных используются для хранения символьных строк. Строка часто реализуется как тип данных массива и содержит последовательность элементов, обычно символов.

  1. CHAR(n): 

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

Ее максимальный диапазон составляет от 0 до 255 символов, и проблема этого типа данных заключается в том, что он занимает всю указанную память, даже если фактическая длина строки меньше. Лишняя длина строки заполняется дополнительным пространством памяти.

  1. VARCHAR(n):

Varchar похож на Char, но может поддерживать строки переменного размера, и здесь нет заполнения. Размер хранения этого типа данных равен фактической длине строки. 

Он может хранить до максимума 65535 символов. Из-за своей переменной природы размера его производительность не так хороша, как у типа данных CHAR.

  1. BINARY(n): 

Это похоже на тип данных CHAR, но принимает только двоичные строки или двоичные данные. Он может использоваться для хранения изображений, файлов или любых сериализованных объектов. Существует еще один тип данных VARBINARY(n), который похож на тип данных VARCHAR, но также принимает только двоичные строки или двоичные данные.

  1. TEXT(n):

Этот тип данных также используется для хранения строк, но имеет максимальный размер 65535 байт.

  1. BLOB(n): Означает Binary Large Object и хранит данные объемом до 65535 байт.

Кроме этого, есть другие типы данных, такие как LONGTEXT и LONGBLOB, которые могут хранить еще больше символов.

 

Числовые типы данных

 

  1. INT():

Он может хранить числовые целые числа, которые занимают 4 байта (32 бита). Здесь n обозначает ширину отображения, которая может быть максимумом до 255. Он указывает минимальное количество символов, используемых для отображения целочисленных значений.

Диапазон:

  1. a)  -2147483648 <= Знаковое INT <= 2147483647
  2. b)  0 <= Беззнаковое INT <= 4294967295
  1. BIGINT():

Он может хранить большое целое число размером до 64 бит.

Диапазон:

  1. a)  -9223372036854775808 <= Знаковое BIGINT <= 9223372036854775807
  2. b)  0 <= Беззнаковое BIGINT <= 18446744073709551615
  1. FLOAT():

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

  1. DOUBLE():

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

  1. DECIMAL(n, d):

Этот тип данных представляет точные десятичные числа с фиксированной точностью, обозначаемой параметром d. Параметр d указывает количество цифр после десятичной точки, а параметр n обозначает размер числа. Максимальное значение для d равно 30, а его значение по умолчанию равно 0.

Некоторые другие типы данных

  1. BOOLEAN:

Этот тип данных хранит только два состояния – True или False. Он используется для выполнения логических операций.

  1. ENUM:

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

Например, рассмотрим атрибут “цвет”, который может быть только “Красный”, “Зеленый” или “Синий”. Когда мы помещаем эти значения в ENUM, значение “цвет” может быть только из этих указанных цветов.

  1. XML:

XML означает расширяемый язык разметки. Этот тип данных используется для хранения структурированных данных XML.

  1. AutoNumber:

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

  1. Hyperlink:

Он может хранить гиперссылки на файлы и веб-страницы.

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

Нормализация в SQL

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

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

  1. Аномалия вставки:

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

  1. Аномалия удаления:

Аномалия удаления возникает, когда мы хотим удалить некоторые строки из таблицы, и некоторые другие данные также должны быть удалены из базы данных.

  1. Аномалия обновления:

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

Процесс нормализации содержит ряд рекомендаций, которые делают проектирование базы данных эффективным, оптимизированным и свободным от избыточностей и аномалий. Существует несколько видов нормальных форм, таких как 1NF, 2NF, 3NF, BCNF и т. д.

1. Первая нормальная форма (1NF)

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

Например-

В Таблице 1 атрибут STUD_PHONE содержит более одного номера телефона. Но в Таблице 2 этот атрибут разложен в 1-ую нормальную форму.

 

2. Вторая нормальная форма

 

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

Например, рассмотрим таблицу с именем Employees, в которой есть следующие атрибуты.

EmployeeID (Первичный ключ)
ProjectID (Первичный ключ)
EmployeeName
ProjectName
HoursWorked

 

Здесь EmployeeID и ProjectID вместе образуют первичный ключ. Однако вы можете заметить частичную зависимость между EmployeeName и EmployeeID. Это означает, что EmployeeName зависит только от части первичного ключа (т.е. EmployeeID). Для полной зависимости EmployeeName должно зависеть как от EmployeeID, так и от ProjectID. Таким образом, это нарушает принцип второй нормальной формы.

Чтобы привести это отношение во вторую нормальную форму, мы должны разделить таблицы на две отдельные таблицы. Первая таблица содержит все сведения об работниках, а вторая — все сведения о проектах.

Таким образом, таблица Employee имеет следующие атрибуты,

EmployeeID (Первичный ключ)
EmployeeName

 

А таблица Project имеет следующие атрибуты,

Project ID (Первичный ключ)
Project Name
Hours Worked

 

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

 

3. Третья нормальная форма

 

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

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

Рассмотрим отношение R(A, B, C), где A — первичный ключ, а B и C — непервичные атрибуты. Пусть A→B и B→C будут двумя функциональными зависимостями, тогда A→C будет транзитивной зависимостью. Это означает, что атрибут C не определяется напрямую A. B выступает в качестве посредника между ними.

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

 

4. Нормальная форма Бойса-Кодда

 

Хотя 2-я и 3-я нормальные формы устраняют большую часть избыточности, все же избыточность не устраняется на 100%. Избыточность может возникнуть, если левая часть функциональной зависимости не является кандидатом или суперключом. Кандидатский ключ формируется из первичных атрибутов, а суперключ является надмножеством кандидатского ключа. Чтобы преодолеть эту проблему, доступен другой тип функциональной зависимости, называемый Нормальная форма Бойса-Кодда (BCNF).

Для того, чтобы таблица находилась в Нормальной форме Бойса-Кодда, левая часть функциональной зависимости должна быть кандидатом или суперключом. Например, для функциональной зависимости X→Y, X должен быть кандидатом или суперключом.

Рассмотрим таблицу Employee, которая содержит следующие атрибуты. 

  1. Employee ID (первичный ключ)
  2. Employee Name
  3. Department
  4. Department Head

   

EmployeeID – это первичный ключ, который уникально идентифицирует каждую строку. Атрибут Department представляет отдел конкретного сотрудника, а атрибут Department Head представляет идентификатор сотрудника, который является руководителем этого отдела.

Теперь мы проверим, находится ли эта таблица в BCNF. Условие состоит в том, что LHS функциональной зависимости должен быть суперключом. Ниже приведены две функциональные зависимости этой таблицы.

Функциональная зависимость 1: Employee ID → Employee Name, Department, Department Head

Функциональная зависимость 2: Department → Department Head

Для ФЗ1 EmployeeID является первичным ключом, который также является суперключом. Но для ФЗ2 атрибут Department не является суперключом, потому что несколько сотрудников могут находиться в одном и том же отделе.

Таким образом, эта таблица нарушает условие BCNF. Чтобы удовлетворить свойство BCNF, нам нужно разделить эту таблицу на две отдельные таблицы: Employees и Departments. Таблица Employees будет содержать EmployeeID, EmployeeName и Department, а таблица Department будет содержать Department и Department Head.

     

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

Мы рассмотрели все известные методы нормализации, но помимо них есть еще две нормальные формы: 4NF и 5NF. Если вы хотите узнать больше о них, обратитесь к этой статье от GeeksForGeeks.

 

Заключение

 

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

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

Это все на сегодня. До встречи, продолжайте читать и учиться.     Арьян Гарг – студент факультета электротехники, находящийся на последнем курсе обучения. Его интересы лежат в области веб-разработки и машинного обучения. Он преследует этот интерес и с нетерпением желает работать в этих направлениях.