Thank you for reading this post, don't forget to subscribe!
При создании таблиц в Mysql мы определяем типы данных и дополнительные правила для колонок (размер, индексы, ограничения):
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE `users` ( <b>id</b> int unsigned NOT NULL AUTO_INCREMENT, <b>email</b> varchar(128) NOT NULL, <b>first_name</b> varchar(32) NOT NULL, <b>last_name</b> varchar(32) NOT NULL, <b>gender</b> char(1) NOT NULL, PRIMARY KEY (id), UNIQUE KEY email (email) ) ENGINE=InnoDB; |
# Пример создания таблицы в Mysql
Как выбрать "правильные" типы данных? Очень просто — нужно соответствовать правилу "чем меньше тем лучше". Чем меньше места будут занимать значения в таблице, тем проще будет базе данных читать и записывать их.
Нужны ли все колонки?
Для начала задайте своему приложению пару вопросов. Нужны ли все колонки? Возможно хватит только некоторых?
Уберите все лишние колонки
Не старайтесь угадать будущее. Практически гарантировано, что вы будете изменять структуру таблицы со временем. Остановитесь только на тех данных, которые нужны сейчас.
Какая самая короткая версия данных?
Стоит ли хранить пол (gender) пользователя в полную длину? Или достаточно будет одной буквы (f/m)? Стоит ли хранить телефон пользователя с кодом страны, либо достаточно будет только прямого номера?
Сократите длину всех колонок до минимума
Задайте эти вопросы всем колонкам будущей таблицы.
NULL значения
Значение NULL в Mysql — это специальное значение. Для работы с ним предусмотрены специальные функции. Для его обработки нужна дополнительная логика. Хорошим правилом будет избегать использования этого значения. Вместо этого можно использовать пустые значения для строк либо нули для чисел:
|
1 2 3 4 5 6 |
CREATE TABLE `users` ( ... age tinyint <b>NOT NULL</b> DEFAULT 0, gender char(1) <b>NOT NULL</b> DEFAULT '', ... ); |
# Значение NULL использоваться не будет
Однако не воспринимайте это, как ограничение. В некоторых случаях удобно использовать NULL, чтобы обозначить отсутсвие значения. Например, в DATETIME колонках:
|
1 2 3 4 5 |
CREATE TABLE `users` ( ... signuped_at <b>datetime NULL DEFAULT NULL</b>, ... ); |
Целые числа
Для всех числовых колонок обязательно рассчитайте максимальное значение. В 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 Мб.
Выбирайте минимальный тип данных исходя из максимального значения колонки.
|
1 2 3 4 5 |
CREATE TABLE `users` ( ... age <b>TINYINT</b> NOT NULL DEFAULT 0, ... ); |
Это может быть не так много для диска, зато критично для оперативной памяти.
UNSIGNED
Если отрицательное число неактуально для колонки, используйте UNSIGNED значения. Тогда максимально значение будет в два раза больше, однако минимальным будет ноль:
- UNSIGNED TINYINT: 8 бит, максимум 255
- UNSIGNED SMALLINT: 16 бит, максимум 65 535
- UNSIGNED INT: 32 бит, максимум 4 x 109
- UNSIGNED BIGINT: 64 бит, максимум 18 x 1018
Длинна числовых типов
В Mysql можно указать длину колонки после указания числового типа:
|
1 2 3 4 5 |
CREATE TABLE `users` ( ... phone <em>INT(7)</em> NOT NULL DEFAULT 0, ... ); |
Это не имеет никакого влияния ни на размер колонки ни на максимальное число. Просто никогда не используйте длину для числовых типов.
Большие числа
Для хранения очень больших точных чисел Mysql предлагает использовать тип DECIMAL:
|
1 2 3 4 5 |
CREATE TABLE `planets` ( ... distance_to_sun <em>DECIMAL(40,3)</em> NOT NULL DEFAULT 0, ... ); |
# Использование DECIMAL для нецелого числа
В скобках указываются количество количество цифр всего и их количество после запятой (может быть нулем). Поскольку процессоры не поддерживают математических операций с подобными числами, Mysql все подсчеты делает на своей стороне. А значит, это очень медленно.
FLOAT / DOUBLE
В отличие от DECIMAL, тип FLOAT является приблизительным (хранит неточное число). В то же время, процессор умеет работать с этим типом напрямую. Кроме этого, FLOAT занимает меньше места, чем DECIMAL для хранения таких же величин.
Используйте FLOAT / DOUBLE вместо DECIMAL, если вам не нужны очень точные числа
VARCHAR / CHAR
При выборе типов строк также действует правило минимума. Оцените максимальную длину строки и поставьте ограничение. Тип CHAR — тип фиксированной длины. Это значит, что для любой строки будет выделено всегда одно и то же количество байт:
|
1 2 3 4 5 |
CREATE TABLE `planets` ( ... state <b>CHAR(2)</b> NOT NULL DEFAULT '', ... ); |
# для колонки будет всегда выделено место под 2 символа (даже, если ее значение будет пустым)
VARCHAR — тип переменной длины. В такой колонке строка будет занимать ровно свою длину (в количестве символов):
|
1 2 3 4 5 |
CREATE TABLE `planets` ( ... first_name <b>VARCHAR(32)</b> NOT NULL DEFAULT '', ... ); |
# колонка будет содержать от 1 до 32 символов в зависимости от значения
Однако Mysql прибавит еще 1 или 2 байта на хранение длины самой строки. Также стоит учесть, что обновление такой строки может быть дорогой операцией (чревато фрагментацией данных, а значит — замедлением чтения). Используйте такое правило:
Если значения в текстовой колонке похожи по длине, выбирайте CHAR, иначе — VARCHAR.
BLOB / TEXT
Типы TEXT и BLOB отличаются между собой только тем, что для второго типа Mysql не делает преобразования кодировок (хранит, как есть).
Не используйте TEXT/BLOB типы для сортировочных колонок
Mysql не умеет выполнять сортировку по этим значениям, поэтому использует только первые max_sort_length символов. Точно также, при создании индекса по этой колонке необходимо указать длину:
|
1 |
CREATE INDEX article_body ON articles <b>( body(32) )</b>; |
# Указываем длину колонки для индексации
Тяжело представить зачем нужно индексировать текстовые колонки, просто не делайте этого. Если же захотите использовать возможность создания индекса для проверки уникальности, используйте вспомогательную колонку для хранения md5 хэша от текста:
|
1 2 3 4 |
CREATE TABLE `articles` ( body TEXT NOT NULL DEFAULT '', <b>body_md5 CHAR(32)</b> ); |
# По колонке body_md5 можно создать уникальный индекс
ENUM
Значения строк может быть одним из заранее известных значений (например, список стран). Если список фиксирован (новые страны не появляются каждый день), удобно будет использовать тип ENUM. Он позволяет задать определенный список значений и использовать только их в строках таблицы.
|
1 2 3 4 5 |
CREATE TABLE users( ... region <b>ENUM('EU', 'USA', 'ASIA')</b> NOT NULL ... ); |
Преимущество этого типа в том, что он записывает номер значения вместо самого значения в каждую строку. Этим обеспечивается огромная экономия места.
Не используйте этот тип для динамических значений. Если есть список значений, который может расширяться (например, категории товаров), используйте отдельную таблицу с идентификатором:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE posts( ... <b>category_id TINYINT</b> UNSIGNED NOT NULL DEFAULT 0, ... ); CREATE TABLE categories( <b>id TINYINT</b> UNSIGNED NOT NULL AUTO_INCREMENT, title char(24) NOT NULL ); |
# Не используйте ENUM для динамических значений
DATETIME / TIMESTAMP
Оба формата дат позволяют хранить значения даты и времени вплоть до секунд. Однако между ними есть отличия:
- DATETIME занимает 8 байт и позволяет хранить даты с 1001 года до 9999 года.
- TIMESTAMP занимает 4 байта и позволяет хранить даты с 1970 года до 2038 года.
Используйте формат TIMESTAMP для простановки дат событий (для чего он и создан). Например, время регистрации пользователя или публикации комментария. К тому же, он имеет удобный механизм инициализации и обновления:
|
1 2 3 4 5 6 |
CREATE TABLE users ( ... sign_up_at TIMESTAMP <b>DEFAULT CURRENT_TIMESTAMP</b>, last_visit_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP <b>ON UPDATE CURRENT_TIMESTAMP</b>, ... ); |
# Инициализация и автообновление колонок TIMESTAMP
В остальных случаях используйте DATETIME.
- Удалите ненужные колонки из схемы.
- Сократите длину колонок до минимума.
- Избегайте использования NULL значений.
- Выберите минимально необходимые числовые типы (TINYINT / SMALLINT вместо INT).
- Используйте FLOAT / DOUBLE вместо DECIMAL для приблизительных чисел.
- Выберите CHAR для строк приблизительно одинаковой длины.
- Для остальных строк — выберите VARCHAR.
- Не используйте TEXT / BLOB для сортировки и индексирования.
- Используйте ENUM вместо строк из фиксированного набора (например, списка стран).
- Используйте TIMESTAMP для простановки времени событий (регистрация, отправка сообщения и т.п.).
- Для остальных дат используйте DATETIME.