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

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

CREATE TABLE `users` (
id int unsigned NOT NULL AUTO_INCREMENT,
email varchar(128) NOT NULL,
first_name varchar(32) NOT NULL,
last_name varchar(32) NOT NULL,
gender char(1) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY email (email)
) ENGINE=InnoDB;

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

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

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

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

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

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

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

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

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

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

NULL значения

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

CREATE TABLE `users` (
...
age tinyint NOT NULL DEFAULT 0,
gender char(1) NOT NULL DEFAULT '',
...
);

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

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

CREATE TABLE `users` (
...
signuped_at datetime NULL DEFAULT NULL,
...
);

Целые числа

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

• TINYINT: 8 бит, максимум 127
• SMALLINT: 16 бит, максимум 32 676
• INT: 32 бит, максимум 2 x 10⁹
• BIGINT: 64 бит, максимум 9 x 10¹⁸

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

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

CREATE TABLE `users` (
...
age TINYINT NOT NULL DEFAULT 0,
...
);

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

UNSIGNED

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

• UNSIGNED TINYINT: 8 бит, максимум 255
• UNSIGNED SMALLINT: 16 бит, максимум 65 535
• UNSIGNED INT: 32 бит, максимум 4 x 10⁹
• UNSIGNED BIGINT: 64 бит, максимум 18 x 10¹⁸

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

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

CREATE TABLE `users` (
...
phone INT(7) NOT NULL DEFAULT 0,
...
);

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

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

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

CREATE TABLE `planets` (
...
distance_to_sun DECIMAL(40,3) NOT NULL DEFAULT 0,
...
);

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

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

FLOAT / DOUBLE

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

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

VARCHAR / CHAR

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

CREATE TABLE `planets` (
...
state CHAR(2) NOT NULL DEFAULT '',
...
);

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

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

CREATE TABLE `planets` (
...
first_name VARCHAR(32) NOT NULL DEFAULT '',
...
);

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

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

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

BLOB / TEXT

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

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

Mysql не умеет выполнять сортировку по этим значениям, поэтому использует только первые max_sort_length символов. Точно также, при создании индекса по этой колонке необходимо указать длину:
CREATE INDEX article_body ON articles ( body(32) );

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

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

CREATE TABLE `articles` (
body TEXT NOT NULL DEFAULT '',
body_md5 CHAR(32)
);

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

ENUM

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

CREATE TABLE users(
...
region ENUM('EU', 'USA', 'ASIA') NOT NULL
...
);

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

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

CREATE TABLE posts(
...

category_id TINYINT UNSIGNED NOT NULL DEFAULT 0,
...

);
CREATE TABLE categories(
id TINYINTUNSIGNED NOT NULL AUTO_INCREMENT,
title char(24) NOT NULL
);

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

DATETIME / TIMESTAMP

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

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

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

CREATE TABLE users (
...
sign_up_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
last_visit_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
...
);

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

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

TL;DR версия

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

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