Выбор типов данных в Mysql

Thank you for reading this post, don't forget to subscribe!

При созда­нии таб­лиц в Mysql мы опре­де­ля­ем типы дан­ных и допол­ни­тель­ные пра­ви­ла для коло­нок (раз­мер, индек­сы, ограничения):

# При­мер созда­ния таб­ли­цы в Mysql

Как выбрать "пра­виль­ные" типы дан­ных? Очень про­сто — нуж­но соот­вет­ство­вать пра­ви­лу "чем мень­ше тем луч­ше". Чем мень­ше места будут зани­мать зна­че­ния в таб­ли­це, тем про­ще будет базе дан­ных читать и запи­сы­вать их.

Нужны ли все колонки?

Для нача­ла задай­те сво­е­му при­ло­же­нию пару вопро­сов. Нуж­ны ли все колон­ки? Воз­мож­но хва­тит толь­ко некоторых?

Убе­ри­те все лиш­ние колонки

Не ста­рай­тесь уга­дать буду­щее. Прак­ти­че­ски гаран­ти­ро­ва­но, что вы буде­те изме­нять струк­ту­ру таб­ли­цы со вре­ме­нем. Оста­но­ви­тесь толь­ко на тех дан­ных, кото­рые нуж­ны сейчас.

Какая самая короткая версия данных?

Сто­ит ли хра­нить пол (gender) поль­зо­ва­те­ля в пол­ную дли­ну? Или доста­точ­но будет одной бук­вы (f/m)? Сто­ит ли хра­нить теле­фон поль­зо­ва­те­ля с кодом стра­ны, либо доста­точ­но будет толь­ко пря­мо­го номера?

Сокра­ти­те дли­ну всех коло­нок до минимума

Задай­те эти вопро­сы всем колон­кам буду­щей таблицы.

NULL значения

Зна­че­ние NULL в Mysql — это спе­ци­аль­ное зна­че­ние. Для рабо­ты с ним преду­смот­ре­ны спе­ци­аль­ные функ­ции. Для его обра­бот­ки нуж­на допол­ни­тель­ная логи­ка. Хоро­шим пра­ви­лом будет избе­гать исполь­зо­ва­ния это­го зна­че­ния. Вме­сто это­го мож­но исполь­зо­вать пустые зна­че­ния для строк либо нули для чисел:

# Зна­че­ние NULL исполь­зо­вать­ся не будет

Одна­ко не вос­при­ни­май­те это, как огра­ни­че­ние. В неко­то­рых слу­ча­ях удоб­но исполь­зо­вать NULL, что­бы обо­зна­чить отсут­свие зна­че­ния. Напри­мер, в DATETIME колонках:

Целые числа

Для всех чис­ло­вых коло­нок обя­за­тель­но рас­счи­тай­те мак­си­маль­ное зна­че­ние. В Mysql суще­ству­ет 4 цело­чис­лен­ных типа:

  • TINYINT: 8 бит, мак­си­мум 127
  • SMALLINT: 16 бит, мак­си­мум 32 676
  • INT: 32 бит, мак­си­мум 2 x 109
  • BIGINT: 64 бит, мак­си­мум 9 x 1018

Пред­ставь­те, что вы исполь­зу­е­те тип INT для колон­ки, в кото­рой хра­нит­ся воз­раст поль­зо­ва­те­ля. Тогда, как вам доста­точ­но типа TINYINT, вы исполь­зу­е­те на 32 — 8 = 24 бита боль­ше. Для каж­дой стро­ки. Если у Вас 10 тыс. поль­зо­ва­те­лей, вы зря рас­хо­ду­е­те: 24/8 * 10 000 = 30 Кб. Если поль­зо­ва­те­лей 10 млн, то 30 Мб.

Выби­рай­те мини­маль­ный тип дан­ных исхо­дя из мак­си­маль­но­го зна­че­ния колонки.

Это может быть не так мно­го для дис­ка, зато кри­тич­но для опе­ра­тив­ной памяти.

UNSIGNED

Если отри­ца­тель­ное чис­ло неак­ту­аль­но для колон­ки, исполь­зуй­те UNSIGNED зна­че­ния. Тогда мак­си­маль­но зна­че­ние будет в два раза боль­ше, одна­ко мини­маль­ным будет ноль:

  • UNSIGNED TINYINT: 8 бит, мак­си­мум 255
  • UNSIGNED SMALLINT: 16 бит, мак­си­мум 65 535
  • UNSIGNED INT: 32 бит, мак­си­мум 4 x 109
  • UNSIGNED BIGINT: 64 бит, мак­си­мум 18 x 1018

Длинна числовых типов

В Mysql мож­но ука­зать дли­ну колон­ки после ука­за­ния чис­ло­во­го типа:

Это не име­ет ника­ко­го вли­я­ния ни на раз­мер колон­ки ни на мак­си­маль­ное чис­ло. Про­сто нико­гда не исполь­зуй­те дли­ну для чис­ло­вых типов.

Большие числа

Для хра­не­ния очень боль­ших точ­ных чисел Mysql пред­ла­га­ет исполь­зо­вать тип DECIMAL:

# Исполь­зо­ва­ние DECIMAL для неце­ло­го числа

В скоб­ках ука­зы­ва­ют­ся коли­че­ство коли­че­ство цифр все­го и их коли­че­ство после запя­той (может быть нулем). Посколь­ку про­цес­со­ры не под­дер­жи­ва­ют мате­ма­ти­че­ских опе­ра­ций с подоб­ны­ми чис­ла­ми, Mysql все под­сче­ты дела­ет на сво­ей сто­роне. А зна­чит, это очень медленно.

FLOAT / DOUBLE

В отли­чие от DECIMAL, тип FLOAT явля­ет­ся при­бли­зи­тель­ным (хра­нит неточ­ное чис­ло). В то же вре­мя, про­цес­сор уме­ет рабо­тать с этим типом напря­мую. Кро­ме это­го, FLOAT зани­ма­ет мень­ше места, чем DECIMAL для хра­не­ния таких же величин.

Исполь­зуй­те FLOAT / DOUBLE вме­сто DECIMAL, если вам не нуж­ны очень точ­ные числа

VARCHARCHAR

При выбо­ре типов строк так­же дей­ству­ет пра­ви­ло мини­му­ма. Оце­ни­те мак­си­маль­ную дли­ну стро­ки и поставь­те огра­ни­че­ние. Тип CHAR — тип фик­си­ро­ван­ной дли­ны. Это зна­чит, что для любой стро­ки будет выде­ле­но все­гда одно и то же коли­че­ство байт:

# для колон­ки будет все­гда выде­ле­но место под 2 сим­во­ла (даже, если ее зна­че­ние будет пустым)

VARCHAR — тип пере­мен­ной дли­ны. В такой колон­ке стро­ка будет зани­мать ров­но свою дли­ну (в коли­че­стве символов):

# колон­ка будет содер­жать от 1 до 32 сим­во­лов в зави­си­мо­сти от значения

Одна­ко Mysql при­ба­вит еще 1 или 2 бай­та на хра­не­ние дли­ны самой стро­ки. Так­же сто­ит учесть, что обнов­ле­ние такой стро­ки может быть доро­гой опе­ра­ци­ей (чре­ва­то фраг­мен­та­ци­ей дан­ных, а зна­чит — замед­ле­ни­ем чте­ния). Исполь­зуй­те такое правило:

Если зна­че­ния в тек­сто­вой колон­ке похо­жи по длине, выби­рай­те CHAR, ина­че — VARCHAR.

BLOBTEXT

Типы TEXT и BLOB отли­ча­ют­ся меж­ду собой толь­ко тем, что для вто­ро­го типа Mysql не дела­ет пре­об­ра­зо­ва­ния коди­ро­вок (хра­нит, как есть).

Не исполь­зуй­те TEXT/BLOB типы для сор­ти­ро­воч­ных колонок

Mysql не уме­ет выпол­нять сор­ти­ров­ку по этим зна­че­ни­ям, поэто­му исполь­зу­ет толь­ко пер­вые max_sort_length сим­во­лов. Точ­но так­же, при созда­нии индек­са по этой колон­ке необ­хо­ди­мо ука­зать длину:

# Ука­зы­ва­ем дли­ну колон­ки для индексации

Тяже­ло пред­ста­вить зачем нуж­но индек­си­ро­вать тек­сто­вые колон­ки, про­сто не делай­те это­го. Если же захо­ти­те исполь­зо­вать воз­мож­ность созда­ния индек­са для про­вер­ки уни­каль­но­сти, исполь­зуй­те вспо­мо­га­тель­ную колон­ку для хра­не­ния md5 хэша от текста:

# По колон­ке body_md5 мож­но создать уни­каль­ный индекс

ENUM

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

Пре­иму­ще­ство это­го типа в том, что он запи­сы­ва­ет номер зна­че­ния вме­сто само­го зна­че­ния в каж­дую стро­ку. Этим обес­пе­чи­ва­ет­ся огром­ная эко­но­мия места.

Не исполь­зуй­те этот тип для дина­ми­че­ских зна­че­ний. Если есть спи­сок зна­че­ний, кото­рый может рас­ши­рять­ся (напри­мер, кате­го­рии това­ров), исполь­зуй­те отдель­ную таб­ли­цу с идентификатором:

# Не исполь­зуй­те ENUM для дина­ми­че­ских значений

DATETIME / TIMESTAMP

Оба фор­ма­та дат поз­во­ля­ют хра­нить зна­че­ния даты и вре­ме­ни вплоть до секунд. Одна­ко меж­ду ними есть отличия:

  • DATETIME зани­ма­ет 8 байт и поз­во­ля­ет хра­нить даты с 1001 года до 9999 года.
  • TIMESTAMP зани­ма­ет 4 бай­та и поз­во­ля­ет хра­нить даты с 1970 года до 2038 года.

Исполь­зуй­те фор­мат TIMESTAMP для про­ста­нов­ки дат собы­тий (для чего он и создан). Напри­мер, вре­мя реги­стра­ции поль­зо­ва­те­ля или пуб­ли­ка­ции ком­мен­та­рия. К тому же, он име­ет удоб­ный меха­низм ини­ци­а­ли­за­ции и обновления:

# Ини­ци­а­ли­за­ция и авто­об­нов­ле­ние коло­нок TIMESTAMP

В осталь­ных слу­ча­ях исполь­зуй­те DATETIME.

  • Уда­ли­те ненуж­ные колон­ки из схемы.
  • Сокра­ти­те дли­ну коло­нок до минимума.
  • Избе­гай­те исполь­зо­ва­ния NULL значений.
  • Выбе­ри­те мини­маль­но необ­хо­ди­мые чис­ло­вые типы (TINYINT / SMALLINT вме­сто INT).
  • Исполь­зуй­те FLOAT / DOUBLE вме­сто DECIMAL для при­бли­зи­тель­ных чисел.
  • Выбе­ри­те CHAR для строк при­бли­зи­тель­но оди­на­ко­вой длины.
  • Для осталь­ных строк — выбе­ри­те VARCHAR.
  • Не исполь­зуй­те TEXT / BLOB для сор­ти­ров­ки и индексирования.
  • Исполь­зуй­те ENUM вме­сто строк из фик­си­ро­ван­но­го набо­ра (напри­мер, спис­ка стран).
  • Исполь­зуй­те TIMESTAMP для про­ста­нов­ки вре­ме­ни собы­тий (реги­стра­ция, отправ­ка сооб­ще­ния и т.п.).
  • Для осталь­ных дат исполь­зуй­те DATETIME.