Реляционные базы данных – проще некуда

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

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

Почему именно это модель стала популярной и существует все эти годы несмотря на то, что вокруг многое уже поменялось? Дело в том, что данные проще всего представить именно таблицей. Практически любые данные можно записать в таблице Microsoft Excel или Google Sheets – две самые популярные программы для работы с данными.

Классическая задача телефонный справочник - это таблица из столбцов и строк:

У колонки есть имя, которое указывает, что именно находиться в данной колонки каждой строки. Ну а в строках сами данные.

В данном случае у нас есть одна проблема, которая может в реальной жизни стать большой. Как найти номер телефона по фамилии? У нас имя и фамилия в одной колонке и. . . Как отделить имя и фамилию? Имя может состоять из двух слов, бывают в наше время такие имена. Фамилия тоже может быть из двух слов, такое тоже бывает. Так что если будет написано Алексей Немирович Данченко – где имя, а где фамилия?

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

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

Дальше я фамилию опущу, чтобы проще было представлять данные.

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

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

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

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

А вот если мы посмотрим на последнюю колонку, то тут у нас город и в реальной жизни в телефонном справочнике может быть миллион и даже несколько миллионов записей с одним и тем же названием города. Казалось бы, в чем проблема, ну храним мы миллион раз слово Санкт-Петербург и что?

Дело в том, что в зависимости от того, какой вариант кодирования строки вы выбрали для хранения символов название города Санкт-Петербург может занимать от 15 байт до достаточно большого количества байт.

Есть тип данных char и если сказать, что максимальный размер названия города 50 символов, то база данных всегда будет выделять 50 символов на диске умноженное на размерность одного символа. В Unicode каждый символ может занимать два байта, хотя бывают различные Unicode кодировки, упростим простым вариантом в два байта.

Возможно, какие-то базы будут выделять только реально необходимое количество символов, но по задумке должно быть всегда 50 символов * 2 байта. И это очень много. Миллион записей с телефонными номерами обернуться в 100 миллионов байт на диске, а это ПОЧТИ 100 мегабайт только для хранения города – одной колонки. Это очень много и это как раз и есть избыточность.

Даже если использовать varchar, который должен выделять на диске только нужное количество символов, 15 символом умножить на размерность символа 2 байта, получается 30 миллионов байт на миллион записей или 30 мегабайт. Да, экономия приличная, но все же информация остается избыточной.

А если создать отдельный справочник городов, который будет состоять из двух колонок в таблице – число (номер города) и название города:

При таком раскладе наша таблица телефонного справочника может выглядеть так:

Теперь вместо хранения целого названия города мы храним только его номер и согласно справочнику городов 1 это Toronto, а 2 это New York.

Сколько в таком случае понадобиться памяти для хранения информации в колонке город? Если выбрать число из 2 байтов, то мы сможем закодировать чуть более 65 тысяч городов. Если выбрать число из 4 байт, то уже более 2 миллиардов (а если без знака, то все 4, но это уже отдельный вопрос) и этого будет с головой для хранения всех городов всей планеты. Я так думаю. На земле же меньше 2 миллиардов городов?

Итак, берем 4 байта и умножаем на количество записей и получаем около 4 мегабайт. Да, у нас есть еще одна запись в справочнике городов, где храниться само имя города, но вся запись там занимает 4 байта на хранения цифры (номера или идентификатора города) + 30 символов на хранение имени. Это мелочи.

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

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

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

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

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

Рассмотрим еще пример. А что если у нас есть пол:

Для хранения пола нам достаточно одного символа М или Ж. В Канаде уже можно увидеть больше вариантов, но все равно, их кажется не больше 5, что также можно обозначить все еще одной буквой.

В зависимости от кодировки один символ будет занимать в памяти 1 байт и более. Самая популярная Unicode в среднем занимает 2 байта. То есть для хранения поля с полом в одной строке нужно два байта.

Можно создать справочник для пола:

Выиграем мы от этого или нет? Для хранения номера можно выделить число размером в один байт и получиться, чтобы сможем адресовать 256 различных вариантов полов, что должно хватить на ближайшие лет 100, я надеюсь.

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

Но что, если у нас есть какая-то колонка, в которой в виде одного символа храниться какой-то код.

И вдруг кто-то пришел и сказал, а давайте поменяем коды с русских букв на английские. Если в таблице 1,000,000 записей, то придется найти каждую из них и изменить код с русской буквы А на английскую, с русской буквы Б на английскую B. Миллион записей превратиться в миллион обновлений.

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

Таблица кодов:

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

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

С теорией разобрались, пока переходить к практике. Я буду работать с базой данных на примере MySQL – бесплатная база данных, которая сейчас принадлежит Oracle. Она одна из самых популярных и устанавливается по умолчанию на большинстве хостингов в интернете. Это идеальное решение для небольших проектов и сайтов.