The OpenNET Project / Index page

[ новости /+++ | форум | wiki | теги | ]

Рекомендации по оптимальному использованию типов данных в MySQL
Материал основан на рекомендациях, данных в книге "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.
 
07.06.2011 , Автор: gara
Ключи: mysql, optimization / Лицензия: CC-BY
Раздел:    Корень / Программисту и web-разработчику / SQL и базы данных / MySQL специфика / Оптимизация и администрирование MySQL

Обсуждение [ Линейный режим | Показать все | RSS ]
  • 1.1, Stax (ok), 14:10, 07/06/2011 [ответить] [﹢﹢﹢] [ · · · ]  
  • +/
    > IP адреса лучше всего хранить как UNSIGNED INT. И использовать MySQL
    > функции INET_ATON() и INET_NTOA()

    Вот так и делают новые проекты без поддержки IPv6. Зато сэкономили несколько байт, ура!

     
     
  • 2.2, igor (??), 20:13, 07/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    Никто не мешает использовать поля вроде BINARY для хранения 128-битных ipv6 адресов...
     
     
  • 3.3, Stax (ok), 22:54, 07/06/2011 [^] [^^] [^^^] [ответить]  
  • +1 +/
    Мешают неразумные люди, следующие советам, не подумав. Советам вроде этих. И лучше всего не давать таких вот сомнительных советов. Ведь храня IP-адрес как строку достаточной длины, проблемы уровня хранения потом не возникнет.

    Это вот тоже из серии "вредные советы"
    > 3. DATETIME / TIMESTAMP - Используйте TIMESTAMP, он занимает на диске меньше места.

    TIMESTAMP совершенно НЕ предназначен для хранения даты, он предназначен для хранения UPDATED/CREATED.. И не надо его использовать для чего-либо другого. Никогда. Пожалуйста!

    Из-за таких советов горе-программисты пишут такой код, который считает, что дату можно класть в TIMESTAMP. Ага, два раза. На андроиде в контакт-листе завести день рождения человека до 1970 года невозможно. Ну не рождались тогда, по мнению гугла! Совершенно реальное поле "день рождения", который оптимизаторы запихнули в аналог TIMESTAMP. Я хоть в IT разбираюсь, мне просто смешно. А вот что далекие от IT люди думают по поводу таких вот ограничений, интересно?..

     
     
  • 4.4, Анончик (?), 01:37, 08/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    [quote]А вот что далекие от IT люди думают по поводу таких вот ограничений, интересно?..[/quote]
    "Ух ты".
     
  • 4.5, ws (ok), 11:18, 08/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    > Мешают неразумные люди, следующие советам, не подумав. Советам вроде этих. И лучше
    > всего не давать таких вот сомнительных советов. Ведь храня IP-адрес как
    > строку достаточной длины, проблемы уровня хранения потом не возникнет.

    Не согласен. Достоинства хранения IP в int более предпочтительные (объем хранимых данных,
    скорость выборки). А вот недостаток только тот о котором вы говорите, но это решаемо если разработчик оказался недостаточно предусмотрителен (ALTER TABLE...)
    Другими словами вы решаете в ущерб оптимизации возможные перспективы...
    Так давайте тогда все данные хранить в строковых типах так проще по вашей логике.

     
     
  • 5.9, Dmitry (??), 13:07, 09/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    Достоинства хранения (да и вообще представления)ip в int более, чем просто сомнительны. Большинство программ ждут, что ip им будет передан как текстовый тип, некоторые готовы принять 4 бинарных октета, int для ip - экзотика. Да и по здравому размышлению не является он таким типом. Не умножайте сущности.
     
     
  • 6.10, ws (ok), 19:15, 09/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    > Достоинства хранения (да и вообще представления)ip в int более, чем просто сомнительны.
    > Большинство программ ждут, что ip им будет передан как текстовый тип,
    > некоторые готовы принять 4 бинарных октета, int для ip - экзотика.
    > Да и по здравому размышлению не является он таким типом. Не
    > умножайте сущности.

    А вы не интересовались как сетевой стек ОС оперирует IP чтобы так утверждать? Да да! Использует все те же целые числа. Так кто плодит сущности?
    IP как мы привыкли видеть нужен только для человека - для удобства использования.

    Для тех программ (и людей тоже), которые хотят видеть в удобном представлении IP и были придуманы функции INET_ATON(), INET_NTOA() http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_


     
     
  • 7.22, Антоним (?), 00:04, 19/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    Что вы чушь несёте. Стек использует бинарные строки, но никак не ЗНАКОВЫЕ целые
     
     
  • 8.25, ws (ok), 18:46, 20/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    За знаковые я ничего не говорил, а говорил за целы числа Для вас есть замечател... текст свёрнут, показать
     
  • 6.15, Сергей (??), 03:05, 17/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    Сразу видно что не писали ничего серьезного с IP :) Еще одно преимущество это возможность быстрой выборки диапазона, например какие IP входят в определенную подсеть или в определенный диапазон. Делать INET_ATON на каждом поле при выборках хорошо? Мало того я даже MAC-адреса храню в виде INT64 и тоже только из за возможности выбирать диапазоны!
    А насчет v6 можно вобще использовать префикс + последние 4 октета в виде того же INT!
     
  • 4.6, angra (ok), 13:41, 08/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    >Ведь храня IP-адрес как строку достаточной длины, проблемы уровня хранения потом не возникнет.

    И что будет достаточной длиной? Предусмотреть разную длину CHAR для IPv4 и IPv6 абсолютно то же самое, что и предусмотреть правильный размер INT для них же. А если писать с расчетом на светлое будущее, то вообще все нужно в TEXT хранить, вот только в суровом настоящем такой проект жрать место, работать будет как черепаха и до светлого будущего не доживет. Кстати как вы собираетесь сортировать или искать диапазоны IP в текстовом виде да еще сразу с учетом разного представления v6 и v4?

     
     
  • 5.8, zoonman (ok), 20:27, 08/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    Плохо, что нет просто UDF типа данных IP-address. А уж там он хоть IPv8...
     
  • 4.7, zoonman (ok), 20:25, 08/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    Дополню немного:

    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

     
  • 4.12, Alex (??), 16:13, 14/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    >На андроиде в контакт-листе завести день рождения человека до 1970 года невозможно.

    Уточните какая версия андроида, т.к. на 2.2.1 вполне нормально заносятся в диапазоне от 1902 до 2036

     
     
  • 5.16, Stax (ok), 23:29, 17/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    2.2.2
    Из LG Optimus 2x.
     
  • 4.13, Axel (??), 16:46, 15/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    Берём signed int и вполне себе записываем даты < 1.1.1970 как отрицательные числа.
     
     
  • 5.19, Stax (ok), 23:34, 17/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    > Берём signed int и вполне себе записываем даты < 1.1.1970 как отрицательные
    > числа.

    Не шутите так :) Во-первых, нестандартно, во-вторых, ну выйграете несколько десятилетий - но даты и до этой бывают.

     
  • 2.27, Сергей (??), 02:15, 25/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    Не все пишут программы для работы в Интернет. Есть программы сбора данных для локальных сетей. Использовать IPv6 в этом случае неразумно, а тратить +12 байт впустую просто глупо - будет БД из одних IP.
     
  • 2.28, Дмитрий (??), 03:15, 06/11/2015 [^] [^^] [^^^] [ответить]  
  • +/
    Вот кстати IPv6 хранить в BINARY(16), есть специальные функции для работы с ними
    https://intsystem.org/coding/kak-rabotat-s-ipv6-v-php/

    INET6_ATON(expr) и INET6_NTOA(expr), но они доступны с версии MySQL 5.6.3. До этой версии есть специальное расширение.

     

  • 1.11, Елка (?), 21:06, 13/06/2011 [ответить] [﹢﹢﹢] [ · · · ]  
  • +1 +/
    ip только в int!
    вы пробывали учитывать трафик по подсетям? ;)
    в строковом варианте это изврат
     
     
  • 2.18, Stax (ok), 23:33, 17/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    > ip только в int!
    > вы пробывали учитывать трафик по подсетям? ;)
    > в строковом варианте это изврат

    Ну, под специфические задачи можно использовать разные способы хранения. Если у вас IP используется для подсчета трафика, храните в INT, никто не запрещает :)

    Некоторые еще хранят в виде "C0A80201" в CHAR(8) - для удобства специфических действий.

    Но под общую задачу хранения IP-адреса какого-нибудь ресурса, смысла запихивать в INT особо и нет.

     

  • 1.14, Av (??), 06:05, 16/06/2011 [ответить] [﹢﹢﹢] [ · · · ]  
  • +/
    эй, чудик, что хранит ip в char, отсортируй-ка их по возрастанию..
     
     
  • 2.17, Stax (ok), 23:31, 17/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    > эй, чудик, что хранит ip в char, отсортируй-ка их по возрастанию..

    А зачем, простите? Сходу даже задач не приходит в голову, где нужно сортировать по IP o.O Поиск по IP, еще понимаю..

     
     
  • 3.20, Сергей (??), 02:29, 18/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    Взять максимальный 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
     
     
  • 4.24, Stax (ok), 05:39, 19/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    Я не понимаю, что такое "максимальный IP". IP это просто адрес, из четырех чисел, если ipv4. Как вы у адреса определяете, какой больше и какой меньше? И главное, зачем?

    А для IPv6 вы считаете, что ipv6-in-ipv4 сегмент 2002:: это "больше" и лучше, чем нативные 2001:: адреса? Примерно такая логика?

     
     
  • 5.26, COBA (?), 00:39, 24/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    А зачем? а чтобы выдать допустим следующий ip клиенту. Это довольно частая операция для провайдеров.
     
  • 3.21, Сергей (??), 02:33, 18/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    >> эй, чудик, что хранит ip в char, отсортируй-ка их по возрастанию..
    > А зачем, простите? Сходу даже задач не приходит в голову, где нужно
    > сортировать по IP o.O Поиск по IP, еще понимаю..

    Еще более экзотический вариант, в БД храним список подсетей в виде int - IP адрес сети, в виде int - маску сети. Задача проверки в какие из сети входит определенный IP. Вот тут уже получите более серъезный оверхед если будете хранить в char.

     
  • 2.23, Антоним (?), 00:06, 19/06/2011 [^] [^^] [^^^] [ответить]  
  • +/
    > эй, чудик, что хранит ip в char, отсортируй-ка их по возрастанию..

    легко, если хранить в HEX. Там же можно делать и выборкии по диапазонам.

     

     Добавить комментарий
    Имя:
    E-Mail:
    Заголовок:
    Текст:




    Спонсоры:
    Inferno Solutions
    Hosting by Hoster.ru
    Хостинг:

    Закладки на сайте
    Проследить за страницей
    Created 1996-2020 by Maxim Chirkov
    Добавить, Поддержать, Вебмастеру