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

Основные принципы

При выборе типа данных для колонки таблицы необходимо руководствоваться следующими основными принципами:

  • чем меньше места занимает тип, тем лучше (чтобы все уместилось в память)
  • чем проще тип, тем лучше (число лучше строки)

Крайне желательно использовать NOT NULL:

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

Объяснение в официальной документации

Некоторые рекомендации по использованию различных типов данных

INT vs FLOAT vs DOUBLE vs DECIMAL

  • по возможности INT (TINYINT, SMALLINT и т.д.);
  • по возможности UNSIGNED;
  • для плавающей точки по возможности FLOAT вместо DOUBLE и DECIMAL
  • DECIMAL - если дробная часть действительно важна (цены)
  • INT(width) - влияет только на отображение

CHAR vs VARCHAR

  • CHAR - короткие строки и строки фиксированной длины, быстрее чем VARCHAR
  • CHAR удаляет пробелы в конце строки
  • VARCHAR - плюс 1 или 2 байта для хранения длины;
  • VARCHAR - меньше места, но дольше обработка, медленнее изменения

Максимальная длина строки значительно больше среднего значения - VARCHAR, произвольный текст - VARCHAR, хеши, пароли - CHAR.

TEXT vs BLOB

  • TEXT = BLOB + encoding + collation
  • Cуществуют подтипы TINYTEXT, MEDIUMTEXT, LONGTEXT, SMALLBLOB и т.д.
  • отдельный способ хранения по отношению к другим полям, отдельное хранилище в InnoDB
  • при сортировке используется max_sort_length символов (1024)
  • при построении индекса нужно указываеть его длину
  • temporary table и memory engine не поддерживают - при сложных запросах таблица на диске, снижение производительности

Вывод: использовать внимательно и по необходимости, а по возможности использовать VARCHAR.

Для html-текста лучше использовать LONGTEXT, а не TEXT, чтобы не поймать ограничение в 64кб.

DATETIME vs TIMESTAMP

  • если позволяет временной интервал - TIMESTAMP лучше
  • DATETIME - в два раза больше места
  • TIMESTAMP зависит от зоны, DATETIME - нет
  • поведение TIMESTAMP при INSERT и UPDATE можно настраивать (в новых версиях ограничение снято):
CREATE TABLE t1 (
  ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  dt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Желательно использовать TIMESTAMP, INT для хранения unix timestamp аналогичен TIMESTAMP, но неудобно.

Аргументы в пользу DATETIME перед TIMESTAMP и INT

  • для даты рожения TIMESTAMP не подходит, нижний интервал ограничен 1970-01-01, для DATETIME он равен 1000-01-01
  • для футуристических сайтов (высадка на Марс в 2050 году) верхний предел TIMESTAMP - 2038 год, DATETIME - 9999 год
  • в отличие от INT, удобно смотреть в базе
  • «у вас в приложении есть другие проблемные запросы», и оптимизация на DATETIME > TIMESTAMP не даст ничего существенного

Если очень хочется, можно использовать INT в виде YYYYMMDD, например 20160101 - 1 янврая 2016 года