Общие рекомендации

Значение NULL для полей лучше никогда не использовать. Наличие этого флага увеличивает объём данных при хранении на диске и немного нагружает индексы. Гораздо лучше определять типы полей как NOT NULL и указывать значение по умолчанию (DEFAULT VALUE). Для текста это будет "" (пустая строка), для чисел — 0 (ноль), для дат (DATETIME или TIMESTAMP) — строка "0000-00-00 00:00:00".

Тип ENUM может уменьшить количество используемых таблиц, объём хранимых данных и упростить построение запросов. Но важно помнить, что в поле типа ENUM хранятся толко индексы элементов, а не значения. Сортировка будет происходить тоже по индексам.
Как следствие, JOIN по полю ENUM бесполезен: он не приведёт к ожидаемому результату.

Дата и время

Для хранения произвольных дат (рождения, исторических событий и т.д.), нужно использовать DATETIME.

Внутри базы данные будут храниться в виде целого числа вида YYYYMMDDHHMMSS и занимать 8 байт. Не имеет ограничений дат. Стандартное представление при выборке — текстовая строка в формате YYYY-MM-DD HH:MM:SS. Возвращаемое время не зависит от временной зоны, установленной в MySQL, то есть, выдаются в точности те дата и время, что записаны в базе.

Для хранения текущего времени (дата заметки, комментария или текущего события в логах) — надо использовать TIMESTAMP или INT UNSIGNED. Последний — предпочтительнее.

TIMESTAMP хранит в базе 4-байтное целое число, равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича (т.е. UNIX-время по Гринвичу). При записи в базу и получении данных используется смещение временной зоны, заданное настройками MySQL. Тип — всегда NOT NULL; значение по умолчанию — всегда NOW(). Представление при выборке — текстовая строка в формате YYYY-MM-DD HH:MM:SS.

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

INT UNSIGNED хранит данные в том же виде, что и TIMESTAMP, но позволяет полностью контролировать записываемые/считываемые данные. При записи нужно использовать функции конвертирования: например, UNIX_TIMESTAMP() внутри запроса. Представление при выборке — понятно, число.

Поясняющий пример:

mysql> create table datetest(dt datetime, ts timestamp, iu int unsigned);
mysql> insert into datetest(dt,iu) values(NOW(),UNIX_TIMESTAMP(NOW()));
mysql> select * from datetest;

+---------------------+---------------------+------------+
| dt                  | ts                  | iu         |
+---------------------+---------------------+------------+
| 2012-04-05 17:02:45 | 2012-04-05 17:02:45 | 1333630965 |
+---------------------+---------------------+------------+

mysql> set @@session.time_zone = '+00:00';
mysql> select * from datetest;

+---------------------+---------------------+------------+
| dt                  | ts                  | iu         |
+---------------------+---------------------+------------+
| 2012-04-05 17:02:45 | 2012-04-05 13:02:45 | 1333630965 |
+---------------------+---------------------+------------+

IP-адреса

IPv6 — пока ещё экзотика, поэтому речь идёт о IPv4.

Адрес IPv4 представляет собой структуру из 4 байт. Хранить его надо как UNSIGNED INT. Это упрощает хранение, сортировку и выполнение стандартных операции над ip-адресами. Для работы с адресами в привычном представлении можно пользоваться функциями MySQL: INET_ATON() и INET_NTOA().

Поясняющий пример:

mysql> create table iptest(ip int unsigned);
mysql> insert into iptest values (INET_ATON("192.168.0.1"));
mysql> select ip, INET_NTOA(ip),
> INET_NTOA(ip & INET_ATON('255.255.255.0')) as subnet,
> INET_NTOA(ip & INET_ATON('0.0.0.255')) as comp from iptest;

+------------+---------------+-------------+---------+
| ip         | INET_NTOA(ip) | subnet      | comp    |
+------------+---------------+-------------+---------+
| 3232235521 | 192.168.0.1   | 192.168.0.0 | 0.0.0.1 |
+------------+---------------+-------------+---------+

MD5 и UUID

Пример MD5: cd4278b99534cbfe585b86c4eec29887.
Если нужно хранить в явном виде — используем CHAR(32).

Пример UUID: 8c049922-df7e-400f-9617-7ec36023759d.
Если нужно хранить в явном виде — используем CHAR(36).

Но можно хранить и в BIN(16), преобразовав данные в 16-байтовое число функцией UNHEX(). В UUID перед этим нужно удалить дефисы.
Преобразование из BIN(16) в обычное представление делается функцией HEX().

Флаги (логические величины)

В MySQL нет специального типа для логических значений. Обычно в этих целях используется тип TINYINT с явной избыточностью (целых 8 бит, хотя фактически используется только один).

Да, я жадный

Поэтому, если в таблице нужно хранить несколько флагов — можно объединить их в одно единственное поле целочисленного типа (до 8 флагов — TINYINT UNSIGNED; от 9 до 16 флагов — SMALLINT UNSIGNED и т.д.), а состояние разных флагов сохранять в значениях разных битов.

Поясняющий пример на PHP:

$flag1 = 1    // 0...0001
$flag2 = 2    // 0...0010
$flag3 = 4    // 0...0100

// Добавляем запись со включенными флагами 1 и 3
// (первый и третий биты, считая с конца):
mysql_query("insert into testtable(flags) values({$flag1} | {$flag3})")

// Делаем выборку записей, где установлен второй флаг:
mysql_query("select * from testtable where (flags & {$flag2}) > 0")

Текст

CHAR(n) — для короткого (не больше n байт, n<256) частоизменяемого текста. Не приводит к фрагментации таблицы за счёт хранения избыточных данных (если строка меньше n байт, она дополняется пробелами до n). Быстро сортируется.

VARCHAR(n) — для короткого текста (не больше n байт, n<256), который не будет меняться. Экономит объём хранимых данных, но при частых изменениях — приводит к фрагментации таблицы.

TEXT — для хранения текста до 64 килобайт.
MEDIUMTEXT — для хранения текста размером до 8 мегабайт.
LONGTEXT — до 4 гигабайт текста. Напишите в комментариях, если этот тип кто-то использует.

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



imen
2012.04.09 13:19:32
#cid20779

Ответить

Есть мнение, что без указания типа таблиц (главная "фича" родного --- табличная блокировка на запись) и примерного количества записей заметка мягко говоря не полна.

2012.04.09 16:24:38
#cid20786

Ответить

#cid20779, imen

Есть мнение, что без указания типа таблиц (главная "фича" родного --- табличная блокировка на запись) и примерного количества записей заметка мягко говоря не полна.

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

без указания типа таблиц (главная "фича" родного --- табличная блокировка на запись)

Никто не мешает использовать, например, InnoDB вместо MyISAM. Будет блокировка на уровне записей.
А сам выбор движка — он немного за рамками темы.

и примерного количества записей

Не понял.

imen
2012.04.12 15:24:28
#cid21042

Ответить

Речь всего лишь про то, какой вид данных в каком формате лучше хранить.

Есть мнение, что перечисленные выводы _могут_ зависеть от формата таблицы ("движка").

И вообще лично меня больше интересует общий принцип бэнчмарка баз данных (хотя бы намётки оного).

Никто не мешает использовать, например, InnoDB вместо MyISAM. Будет блокировка на уровне записей.

По моему достаточно невеликому опыту работы с Мускулом:
Для небольших записей и интенсивного потока запросов на изменение (как бы не больше 10% от общего количества).
На начальном этапе InnoDB заметно шустрее. Но с MyISAM приложение шевелилось дольше (база умирала на где-то втрое большем количестве записей).

Ещё роялит фактор скиллов использования SQL разработчиком приложения.

А сам выбор движка — он немного за рамками темы.

А зря. Начинать надо именно с него.

ЗЫ: Я бы реквестировал критический разбор одной из плюшек Мускула: AUTO_INCREMENT типа поля.

2012.04.12 19:52:04
#cid21057

Ответить

#cid21042, imen

Есть мнение, что перечисленные выводы _могут_ зависеть от формата таблицы ("движка").

Многие иксперты-разработчики на уровне разработки структуры базы закладывают в неё неоптимальные типы данных.
Например, таблица с логами, которая разрастается быстрее всех и опрашивается чаще всех — содержит дату в формате DATETIME. Это ж пиздец, какой формат таблицы ни возьми. Заметка — как раз об этом.

И вообще лично меня больше интересует общий принцип бэнчмарка баз данных (хотя бы намётки оного).

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

Лично я для себя давно сделал вывод: производительность БД самой по себе — это сферический конь в вакууме.

По моему достаточно невеликому опыту работы с Мускулом:
Для небольших записей и интенсивного потока запросов на изменение (как бы не больше 10% от общего количества).
На начальном этапе InnoDB заметно шустрее. Но с MyISAM приложение шевелилось дольше (база умирала на где-то втрое большем количестве записей).

Надо ещё учитывать, как минимум, возможности сервера MySQL (производительность), выбор файловой системы, на которой база расположена физически и внутренние настройки MySQL.

Ещё роялит фактор скиллов использования SQL разработчиком приложения.

Этого недостаточно!

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

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

А сам выбор движка — он немного за рамками темы.

А зря. Начинать надо именно с него.

Возможно, имеет смысл добавить информацию про типы таблиц, да.
Добавил заметку к рубрике ♼REC, буду думать.

ЗЫ: Я бы реквестировал критический разбор одной из плюшек Мускула: AUTO_INCREMENT типа поля.

А что с ним не так?

imen
2012.04.13 12:19:24
#cid21126

Ответить

Многие иксперты-разработчики на уровне разработки структуры базы закладывают в неё неоптимальные типы данных.

Лезут на физический уровень (на котором им делать нечего).

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

По моему опыту на высоконагруженной системе журналы далеко не всегда обладают свойством извлекаемости полезной информации.
Зато i/o грузят...
Потому ИМХО правильно начинать с вопроса о ведении журнала в принципе.

Лично я для себя давно сделал вывод: производительность БД самой по себе — это сферический конь в вакууме.

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

Надо ещё учитывать, как минимум, возможности сервера MySQL (производительность), выбор файловой системы, на которой база расположена физически и внутренние настройки MySQL.

Есть мнение, что серьёзные базы данных должны уметь работать с устройствами (накопителями) без прослойки в виде ОС (и файловой системы).

Этого недостаточно!

А ещё бывают ситуации, когда требования приложения... не вполне согласуются с оптимальным режимом работы БД.

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

1. Рекомендую термин "погромист".
2. Для языков высокого уровня (да с фичей переносимости) достаточно полное знание физики обычно не соблюдается.

А что с ним не так?

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

2012.04.13 15:16:01
#cid21141

Ответить

#cid21126, imen

Лезут на физический уровень (на котором им делать нечего).

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

Потому ИМХО правильно начинать с вопроса о ведении журнала в принципе.

Ну — это, как бы, подразумевается.

Есть мнение, что серьёзные базы данных должны уметь работать с устройствами (накопителями) без прослойки в виде ОС (и файловой системы).

Эта "прослойка" всё равно будет, только заточенная под узкую задачу.
Типа узкоспециализированный аппаратно-программный комплекс.

факт отсутствия аналогичной плюшки например у Оракла.

С СУБД Оракла не знаком.
Что писать про автоинкремент, как его можно критически разобрать — не знаю. Достаточно просто всё.

quizz
2012.04.16 23:53:28
#cid21474

Ответить

Прояснил для себя несколько моментов. Написано толково, без груза и выебона, без отсылки на толмуты мануалов в гуглы и т.д. Спасибо.