Материал основан на рекомендациях, данных в книге "MySQL Оптимизация производительности", в которой подробно рассказано почему каждая рекомендация работает так, а не иначе. В заметке лишь приведены ключевые моменты, в стиле "Делайте так!". Все нижесказанное будет относиться к MySQL 5.1 и выше. Движок InnoDB. Для MyISAM также верно почти все нижесказанное.Прежде чем говорить о типах, рекомендация относительно []NULL/ NOT NULL[]. Наличие флага NULL, увеличивает объем данных при хранении на диске. И немного нагружает индексы. Определяйте типы полей как NOT NULL, и указывайте DEFAULT VALUE. Большой выигрыш в производительности это не даст, но как говорится "Копейка рубль бережет".
[]UUID[] - удалите тире или преобразуйте в 16 байтовые числа UNHEX() и сохранить в столбце BIN(16). Извлекать данные в шестнадцатеричном формате можно с помощью MySQL функции HEX().
[]IP[] адреса лучше всего хранить как UNSIGNED INT. И использовать MySQL функции INET_ATON() и INET_NTOA()
И так, наиболее часто используются типы int, varchar/char, date/time, enum.
++ Выбор оптимальных типов данных.1. []INT[] - все понятно, ничего интересного.
2. []CHAR / VARCHAR[]
VARCHAR имеет переменную длину в файле. Занимает столько места сколько записано данных. При UPDATE если данных стало больше - выделяется место в другом месте файла. Это создает дополнительную нагрузку и является причиной фрагментации. Используйте VARCHAR если данные обновляются редко, либо используется сложная кодировка, например UTF-8. Идеально для хранения неизменяемых данных.CHAR имеет фиксированную длину в файле. Фрагментация ему не страшна. CHAR полезен когда нужно хранить короткие строки приблизительно одинаковой длинны. Частое обновление не ведет к фрагментации. Идеален для хранение MD5 (CHAR(32)).
Старайтесь использовать CHAR, сортировка по такому полю обходится сильно "дешевле".
3. []DATETIME / TIMESTAMP[] - Используйте TIMESTAMP, он занимает на диске меньше места.
4. []ENUM[]
При создании таблицы:
CREATE TABLE enum_test ( e ENUM('fish','dog','apple') NOT NULL);создается справочник-индекс в *.frm файле. И при последующих INSERT/UPDATE в базу записывается номер(индекс) ENUM поля.
INSERT INTO enum_test(e) VALUES ('fish'),('dog'),('apple');
SELECT e + 0 FROM enum_test;вернет
1
3
2И сортировка происходит (сюрприз) по этим целочисленным значениям :)
SELECT e FROM enum_test ORDER BY e;
fish
apple
dogОбойти это неудобство можно используя FIELD().
Не используйте JOIN между CHAR/VARCHAR и ENUM.
URL:
Обсуждается: http://www.opennet.dev/tips/info/2586.shtml
> IP адреса лучше всего хранить как UNSIGNED INT. И использовать MySQL
> функции INET_ATON() и INET_NTOA()Вот так и делают новые проекты без поддержки IPv6. Зато сэкономили несколько байт, ура!
Никто не мешает использовать поля вроде BINARY для хранения 128-битных ipv6 адресов...
Мешают неразумные люди, следующие советам, не подумав. Советам вроде этих. И лучше всего не давать таких вот сомнительных советов. Ведь храня IP-адрес как строку достаточной длины, проблемы уровня хранения потом не возникнет.Это вот тоже из серии "вредные советы"
> 3. DATETIME / TIMESTAMP - Используйте TIMESTAMP, он занимает на диске меньше места.TIMESTAMP совершенно НЕ предназначен для хранения даты, он предназначен для хранения UPDATED/CREATED.. И не надо его использовать для чего-либо другого. Никогда. Пожалуйста!
Из-за таких советов горе-программисты пишут такой код, который считает, что дату можно класть в TIMESTAMP. Ага, два раза. На андроиде в контакт-листе завести день рождения человека до 1970 года невозможно. Ну не рождались тогда, по мнению гугла! Совершенно реальное поле "день рождения", который оптимизаторы запихнули в аналог TIMESTAMP. Я хоть в IT разбираюсь, мне просто смешно. А вот что далекие от IT люди думают по поводу таких вот ограничений, интересно?..
[quote]А вот что далекие от IT люди думают по поводу таких вот ограничений, интересно?..[/quote]
"Ух ты".
> Мешают неразумные люди, следующие советам, не подумав. Советам вроде этих. И лучше
> всего не давать таких вот сомнительных советов. Ведь храня IP-адрес как
> строку достаточной длины, проблемы уровня хранения потом не возникнет.Не согласен. Достоинства хранения IP в int более предпочтительные (объем хранимых данных,
скорость выборки). А вот недостаток только тот о котором вы говорите, но это решаемо если разработчик оказался недостаточно предусмотрителен (ALTER TABLE...)
Другими словами вы решаете в ущерб оптимизации возможные перспективы...
Так давайте тогда все данные хранить в строковых типах так проще по вашей логике.
Достоинства хранения (да и вообще представления)ip в int более, чем просто сомнительны. Большинство программ ждут, что ip им будет передан как текстовый тип, некоторые готовы принять 4 бинарных октета, int для ip - экзотика. Да и по здравому размышлению не является он таким типом. Не умножайте сущности.
> Достоинства хранения (да и вообще представления)ip в int более, чем просто сомнительны.
> Большинство программ ждут, что ip им будет передан как текстовый тип,
> некоторые готовы принять 4 бинарных октета, int для ip - экзотика.
> Да и по здравому размышлению не является он таким типом. Не
> умножайте сущности.А вы не интересовались как сетевой стек ОС оперирует IP чтобы так утверждать? Да да! Использует все те же целые числа. Так кто плодит сущности?
IP как мы привыкли видеть нужен только для человека - для удобства использования.Для тех программ (и людей тоже), которые хотят видеть в удобном представлении IP и были придуманы функции INET_ATON(), INET_NTOA() http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functio...
Что вы чушь несёте. Стек использует бинарные строки, но никак не ЗНАКОВЫЕ целые
> Что вы чушь несёте. Стек использует бинарные строки, но никак не ЗНАКОВЫЕ
> целыеЗа знаковые я ничего не говорил, а говорил за целы числа! Для вас есть замечательная команда для посвящения:
man inet_pton
И вот цитата из него:
"
AF_INET
src points to a character string containing an IPv4 network address in dotted-decimal format, "ddd.ddd.ddd.ddd", where ddd is a decimal
number of up to three digits in the range 0 to 255. The address is converted to a struct in_addr and copied to dst, which must be
sizeof(struct in_addr) (4) bytes (32 bits) long.
"Думаю сами переведете...
Сразу видно что не писали ничего серьезного с IP :) Еще одно преимущество это возможность быстрой выборки диапазона, например какие IP входят в определенную подсеть или в определенный диапазон. Делать INET_ATON на каждом поле при выборках хорошо? Мало того я даже MAC-адреса храню в виде INT64 и тоже только из за возможности выбирать диапазоны!
А насчет v6 можно вобще использовать префикс + последние 4 октета в виде того же INT!
>Ведь храня IP-адрес как строку достаточной длины, проблемы уровня хранения потом не возникнет.И что будет достаточной длиной? Предусмотреть разную длину CHAR для IPv4 и IPv6 абсолютно то же самое, что и предусмотреть правильный размер INT для них же. А если писать с расчетом на светлое будущее, то вообще все нужно в TEXT хранить, вот только в суровом настоящем такой проект жрать место, работать будет как черепаха и до светлого будущего не доживет. Кстати как вы собираетесь сортировать или искать диапазоны IP в текстовом виде да еще сразу с учетом разного представления v6 и v4?
Плохо, что нет просто UDF типа данных IP-address. А уж там он хоть IPv8...
Дополню немного:The TIMESTAMP data type has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
The DATETIME type is used when you need values that contain both date and time information. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
RTFM http://dev.mysql.com/doc/refman/5.5/en/datetime.html
>На андроиде в контакт-листе завести день рождения человека до 1970 года невозможно.Уточните какая версия андроида, т.к. на 2.2.1 вполне нормально заносятся в диапазоне от 1902 до 2036
2.2.2
Из LG Optimus 2x.
Берём signed int и вполне себе записываем даты < 1.1.1970 как отрицательные числа.
> Берём signed int и вполне себе записываем даты < 1.1.1970 как отрицательные
> числа.Не шутите так :) Во-первых, нестандартно, во-вторых, ну выйграете несколько десятилетий - но даты и до этой бывают.
Не все пишут программы для работы в Интернет. Есть программы сбора данных для локальных сетей. Использовать IPv6 в этом случае неразумно, а тратить +12 байт впустую просто глупо - будет БД из одних IP.
Вот кстати IPv6 хранить в BINARY(16), есть специальные функции для работы с ними
https://intsystem.org/coding/kak-rabotat-s-ipv6-v-php/INET6_ATON(expr) и INET6_NTOA(expr), но они доступны с версии MySQL 5.6.3. До этой версии есть специальное расширение.
ip только в int!
вы пробывали учитывать трафик по подсетям? ;)
в строковом варианте это изврат
> ip только в int!
> вы пробывали учитывать трафик по подсетям? ;)
> в строковом варианте это извратНу, под специфические задачи можно использовать разные способы хранения. Если у вас IP используется для подсчета трафика, храните в INT, никто не запрещает :)
Некоторые еще хранят в виде "C0A80201" в CHAR(8) - для удобства специфических действий.
Но под общую задачу хранения IP-адреса какого-нибудь ресурса, смысла запихивать в INT особо и нет.
эй, чудик, что хранит ip в char, отсортируй-ка их по возрастанию..
> эй, чудик, что хранит ip в char, отсортируй-ка их по возрастанию..А зачем, простите? Сходу даже задач не приходит в голову, где нужно сортировать по IP o.O Поиск по IP, еще понимаю..
Взять максимальный IP по выборке например:
SELECT * FROM table WHERE expr... ORDER BY int_ip DESC LIMIT 1;
в вашем случае при хранении в char это:
SELECT * FROM table WHERE expr... ORDER BY INET_ATON(char_ip) DESC LIMIT 1;
что соответственно overhead
Я не понимаю, что такое "максимальный IP". IP это просто адрес, из четырех чисел, если ipv4. Как вы у адреса определяете, какой больше и какой меньше? И главное, зачем?А для IPv6 вы считаете, что ipv6-in-ipv4 сегмент 2002:: это "больше" и лучше, чем нативные 2001:: адреса? Примерно такая логика?
А зачем? а чтобы выдать допустим следующий ip клиенту. Это довольно частая операция для провайдеров.
>> эй, чудик, что хранит ip в char, отсортируй-ка их по возрастанию..
> А зачем, простите? Сходу даже задач не приходит в голову, где нужно
> сортировать по IP o.O Поиск по IP, еще понимаю..Еще более экзотический вариант, в БД храним список подсетей в виде int - IP адрес сети, в виде int - маску сети. Задача проверки в какие из сети входит определенный IP. Вот тут уже получите более серъезный оверхед если будете хранить в char.
> эй, чудик, что хранит ip в char, отсортируй-ка их по возрастанию..легко, если хранить в HEX. Там же можно делать и выборкии по диапазонам.