Общие рекомендации
Значение 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 гигабайт текста. Напишите в комментариях, если этот тип кто-то использует.
Последние три типа по понятным причинам тоже могут вызывать фрагментацию таблиц.
- Рекомендации по оптимальному использованию типов данных в MySQL
- Типы данных MySQL
- 14 Оптимизация в MySQL
- Блокировки в MySQL
Комментарии
imen
#cid20779
Ответить
Есть мнение, что без указания типа таблиц (главная "фича" родного --- табличная блокировка на запись) и примерного количества записей заметка мягко говоря не полна.
#cid20786
Ответить
#cid20779, imen
Речь всего лишь про то, какой вид данных в каком формате лучше хранить.
Делюсь практическим опытом, не претендуя на полноту или объективность. Поэтому дополнительная информация и обоснованная критика — только приветствуются.
Никто не мешает использовать, например, InnoDB вместо MyISAM. Будет блокировка на уровне записей.
А сам выбор движка — он немного за рамками темы.
Не понял.
imen
#cid21042
Ответить
Есть мнение, что перечисленные выводы _могут_ зависеть от формата таблицы ("движка").
И вообще лично меня больше интересует общий принцип бэнчмарка баз данных (хотя бы намётки оного).
По моему достаточно невеликому опыту работы с Мускулом:
Для небольших записей и интенсивного потока запросов на изменение (как бы не больше 10% от общего количества).
На начальном этапе InnoDB заметно шустрее. Но с MyISAM приложение шевелилось дольше (база умирала на где-то втрое большем количестве записей).
Ещё роялит фактор скиллов использования SQL разработчиком приложения.
А зря. Начинать надо именно с него.
ЗЫ: Я бы реквестировал критический разбор одной из плюшек Мускула: AUTO_INCREMENT типа поля.
#cid21057
Ответить
#cid21042, imen
Многие иксперты-разработчики на уровне разработки структуры базы закладывают в неё неоптимальные типы данных.
Например, таблица с логами, которая разрастается быстрее всех и опрашивается чаще всех — содержит дату в формате DATETIME. Это ж пиздец, какой формат таблицы ни возьми. Заметка — как раз об этом.
Производительность неправильно рассматривать в отрыве от конкретной задачи. А там уже будет куча дополнительных факторов: вид хранимых данных, частота обращения, скорость и задержки между клиентом и сервером, и т.д.
Лично я для себя давно сделал вывод: производительность БД самой по себе — это сферический конь в вакууме.
Надо ещё учитывать, как минимум, возможности сервера MySQL (производительность), выбор файловой системы, на которой база расположена физически и внутренние настройки MySQL.
Этого недостаточно!
Например, как определить программиста от быдлокодера? Оба могут в совершенстве знать язык программирования. Но программист понимает, что его код реально делает в системе, а для быдлокодера текст программы — абстрактная сущность.
Хороший разработчик просто обязан знать, что происходит между нажатием кнопки в приложении и выводом данных пользователю. Весь цикл взаимодействий данных.
Возможно, имеет смысл добавить информацию про типы таблиц, да.
Добавил заметку к рубрике ♼REC, буду думать.
А что с ним не так?
imen
#cid21126
Ответить
Лезут на физический уровень (на котором им делать нечего).
По моему опыту на высоконагруженной системе журналы далеко не всегда обладают свойством извлекаемости полезной информации.
Зато i/o грузят...
Потому ИМХО правильно начинать с вопроса о ведении журнала в принципе.
Производительность сферической в вакууме БД --- сущность попросту бессмысленная.
Конвретная задача рулит, но метода должна быть общей.
Есть мнение, что серьёзные базы данных должны уметь работать с устройствами (накопителями) без прослойки в виде ОС (и файловой системы).
А ещё бывают ситуации, когда требования приложения... не вполне согласуются с оптимальным режимом работы БД.
1. Рекомендую термин "погромист".
2. Для языков высокого уровня (да с фичей переносимости) достаточно полное знание физики обычно не соблюдается.
Сейчас я работаю больше не с мускулом.
"Не так" же факт отсутствия аналогичной плюшки например у Оракла.
Что (при успешной коррекции роли гугля в чёрном деле тиражирования популярных заблуждений) не имеет отрицательного влияния на уровне логики приложения.
#cid21141
Ответить
#cid21126, imen
Наоборот, они должны понимать, для чего выбирают тот или иной тип. В том числе, должны понимать, как оно будет храниться и обрабатываться.
Ну — это, как бы, подразумевается.
Эта "прослойка" всё равно будет, только заточенная под узкую задачу.
Типа узкоспециализированный аппаратно-программный комплекс.
С СУБД Оракла не знаком.
Что писать про автоинкремент, как его можно критически разобрать — не знаю. Достаточно просто всё.
quizz
#cid21474
Ответить
Прояснил для себя несколько моментов. Написано толково, без груза и выебона, без отсылки на толмуты мануалов в гуглы и т.д. Спасибо.