Размер MySQL базы

#php #mysql #sql

Если к примеру есть 200 стран и 5000 городов, и чтобы в таблице вида:

id | country | town | message


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

id | country //table country

id | town //table town


Уменьшит ли это объем таблицы, если предположительно будет 500 миллионов записей,
правильно ли это и есть ли альтернативы?
    

Ответы

Ответ 1

Я бы сказал, что так просто необходимо делать. И дело даже не в объеме базы, хотя
он так же имеет значение. Представим, что вы ошиблись в названии города. В случае если
это название повторяется в базе более 1 раза вам будет необходимо поменять его во всех
записях, в которых оно встречается, поэтому в реляционные базах данных всегда стараются
придерживаться такого принципа, что все данные дложны хранится в единственном экземпляре,
а во всех остальных местах просто идет ссылка по ID на требуемый объект. Посмотрите
в гугле по запросу "Нормальная форма", там будет ссылка на вики, но на вики сухая теория,
сложная для понимания, рекомендую походить по другим ссылкам, даваемых гуглом.

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

Пишу все в нотации оракла, типы данных вибирайте согласно той БД с которой работаете
Таблица стран:

create table Countrys (
  c_id number not null primaty key, -- ID страны
  name varchar(200) -- название страны
  ... -- возможно еще какие то данные о стране
);


Таблица городов:

create table Towns (
 t_id number not null primaty key, -- ID города
 c_id number not null -- ID страны в которой расположен город, надо же знать
                      -- это Москва - столица РФ или одноименная деревня
                      -- в США
 name varchar(200) -- Название города
 -- Возможно еще какие то данные о городе
);


Ну и ваша загадочная messages:

create table Messages(
  msg_id number not null primary key,
  t_id number not null, -- Ссылка только на город, если будет нужно узнать страну
- мы ее найдем по городу
  message varchar(200)
);


Ну и напоследок немного SQL. С вставкой в общем то все просто, insert всегда идет
только в одну таблицу, отдельно заполняем справочник стран, потом городов и уже ваши
messages

insert into Countrys(c_id, name) values(1, 'Россия');

insert into Towns(t_id, c_id, name)
 select 100, c_id, 'Москва' from Countrys as C where C.name='Россия';
-- Но работа по имени очень не приветствуется, когда вы будете так вставлять
-- записи в messages как вы по имени зададите Москва, если в мире не 1 десяток 
-- городов с таким названием


Опять же от базы зависит откуда берутся id. в MySQL вы можете просто при объявлении
колонки сказать, что бы id автоматом назначались. Это сами поищите, я наизусть не помню.
А в оракле совсем другие механизмы.

Ну и выборка одного сообщения так, что бы получить по нему и город и страну

select C.name as country_name, T.name as town_name, M.message
  from Messages as M, Towns as T, Countys as C
 where M.msg_id=1 and T.t_id=M.t_id and C.c_id=T.t_id


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

И еще почитайте о foreign key, это ссылки, показывающие базе данных связь поля id
страны в таблице городов, с первичным ключем id в таблице стран. Их необходимо создавать.
Сначала кажется, что они усложняют вашу жизнь, не давая вставить запись в таблицу городов
с не существующим ID страны. Но именно этим они спасают вас от ошибок в коде, ведущим
зачастую к тяжелым последствиям. Представьте, вы вставляете 10к записей messages с
городом Нью-Васюки, а потом случайно удаляете город из справочника. У вас остаются
записи message, но вы не знаете к какому городу они относятся и что с ними дальше делать
(не говоря уже о том, что приведенный мной выше select просто не покажет эти строки).
Так вот foreign key не позволит вам удалить город на который есть хоть одна ссылка
и если вы действительно решили его удалить, то не забудете о тех messages которые на
него ссылаются и примете решение, например поменять у них город на Олд-Васюки


Ответ 2

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

Про нормализацию уже написали в других ответах, поэтому напишу кратко. Смысл ее в
том, что вы храните страны, города и сообщения отдельно друг от друга. Это разные сущности
и, по-хорошему, не нужно много раз писать в БД одно и то же (впрочем, есть исключения
из правила и о них ниже). О организации данных подробно написано в ответе  Mike.

НО! это верно, если объем данных в "обозримых" пределах. Когда мы говорим о очень
большом числе данных, тут надо учитывать характер работы с данными: эти данные больше
пишутся или больше читаются? Если читаются, то равномерно или есть такие данные, которые
нужны больше остальных (например, записи за последний месяц дергаются из БД чаще остальных)?

При больших объемах данных имеет смысл подумать о партициях. А если данные очень
часто дергаются пакетом (страна+город+сообщение), то допускаю, что хранение их в одной
таблице будет более оптимальным решением, чем постоянное объединение трех таблиц особенно
при таких объемах. Это - денормализация базы данных.

Еще вариант: хранить данные, которые нужны чаще, отдельно от данных, к которым обращаются
редко.

Как вариант можно подумать о хранении данных из небольших таблиц на стороне клиента
(например, в локальном хранилище браузера) и объединять данные уже при отрисовке страниц.

Вариантов решений море. Не ищите единственно верное, ищите оптимальное для вашей
конкретной ситуации.


Ответ 3

Да, уменьшит. Так и принято делать. Это называется нормализация