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, 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, 22:54, 07/06/2011 [^] [ответить] [смотреть все]  
  • +1 +/
    Мешают неразумные люди, следующие советам, не подумав Советам вроде этих И луч... весь текст скрыт [показать]
     
     
  • 4.4, Анончик, 01:37, 08/06/2011 [^] [ответить] [смотреть все]  
  • +/
    quote А вот что далекие от IT люди думают по поводу таких вот ограничений, инте... весь текст скрыт [показать]
     
  • 4.5, ws, 11:18, 08/06/2011 [^] [ответить] [смотреть все]  
  • +/
    Не согласен Достоинства хранения IP в int более предпочтительные объем хранимы... весь текст скрыт [показать]
     
     
  • 5.9, Dmitry, 13:07, 09/06/2011 [^] [ответить] [смотреть все]  
  • +/
    Достоинства хранения да и вообще представления ip в int более, чем просто сомни... весь текст скрыт [показать]
     
     
  • 6.10, ws, 19:15, 09/06/2011 [^] [ответить] [смотреть все]  
  • +/
    А вы не интересовались как сетевой стек ОС оперирует IP чтобы так утверждать Да... весь текст скрыт [показать]
     
     
  • 7.22, Антоним, 00:04, 19/06/2011 [^] [ответить] [смотреть все]  
  • +/
    Что вы чушь несёте. Стек использует бинарные строки, но никак не ЗНАКОВЫЕ целые
     
     
  • 8.25, ws, 18:46, 20/06/2011 [^] [ответить] [смотреть все]  
  • +/
    За знаковые я ничего не говорил, а говорил за целы числа Для вас есть замечател... весь текст скрыт [показать]
     
  • 6.15, Сергей, 03:05, 17/06/2011 [^] [ответить] [смотреть все]  
  • +/
    Сразу видно что не писали ничего серьезного с IP Еще одно преимущество это во... весь текст скрыт [показать]
     
  • 4.6, angra, 13:41, 08/06/2011 [^] [ответить] [смотреть все]  
  • +/
    И что будет достаточной длиной Предусмотреть разную длину CHAR для IPv4 и IPv6 ... весь текст скрыт [показать]
     
     
  • 5.8, zoonman, 20:27, 08/06/2011 [^] [ответить] [смотреть все]  
  • +/
    Плохо, что нет просто UDF типа данных IP-address. А уж там он хоть IPv8...
     
  • 4.7, zoonman, 20:25, 08/06/2011 [^] [ответить] [смотреть все]  
  • +/
    Дополню немного The TIMESTAMP data type has a range of 1970-01-01 00 00 01 UT... весь текст скрыт [показать]
     
  • 4.12, Alex, 16:13, 14/06/2011 [^] [ответить] [смотреть все]  
  • +/
    Уточните какая версия андроида, т к на 2 2 1 вполне нормально заносятся в диапа... весь текст скрыт [показать]
     
     
  • 5.16, Stax, 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, 23:34, 17/06/2011 [^] [ответить] [смотреть все]  
  • +/
    Не шутите так Во-первых, нестандартно, во-вторых, ну выйграете несколько деся... весь текст скрыт [показать]
     
  • 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, 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, 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, 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:      
    Заголовок:
    Текст:



      Закладки на сайте
      Проследить за страницей
    Created 1996-2017 by Maxim Chirkov  
    ДобавитьРекламаВебмастеруГИД  
    Hosting by Ihor