Вибір типів даних в mysql

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

# Приклад створення таблиці в Mysql

Чи потрібні всі колонки?

Для початку поставте своєму додатку пару питань. Чи потрібні всі колонки? Можливо вистачить тільки деяких? Приберіть всі зайві колонки

Чи не намагайтеся вгадати майбутнє. Практично гарантоване, що ви будете змінювати структуру таблиці з часом. Зупиніться тільки на тих даних, які потрібні зараз.

Яка найкоротша версія даних?

Задайте ці питання всіх колонках майбутньої таблиці.

NULL значення

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

# Значення NULL використовуватися не буде

Цілі числа

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

TINYINT. 8 біт, максимум 127
SMALLINT. 16 біт, максимум 32 676
INT. 32 біт, максимум 2 x 10 9
BIGINT. 64 біт, максимум 9 x 10 18

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

Це може бути не так багато для диска, зате критично для оперативної пам'яті.

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

UNSIGNED TINYINT. 8 біт, максимум 255
UNSIGNED SMALLINT. 16 біт, максимум 65 535
UNSIGNED INT. 32 біт, максимум 4 x 10 9
UNSIGNED BIGINT. 64 біт, максимум 18 x 10 18

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

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

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

великі числа

Для зберігання дуже великих точних чисел Mysql пропонує використовувати тип DECIMAL.

# Використання DECIMAL для нецілого числа

У дужках вказуються кількість кількість цифр всього і їх кількість після коми (може бути нулем). Оскільки процесори не підтримують математичних операцій з подібними числами, Mysql все підрахунки робить на своєму боці. А значить, це дуже повільно.

FLOAT / DOUBLE

На відміну від DECIMAL. тип FLOAT є приблизними (зберігає неточне число). У той же час, процесор вміє працювати з цим типом безпосередньо. Крім цього, FLOAT займає менше місця, ніж DECIMAL для зберігання таких же величин. Використовуйте FLOAT / DOUBLE замість DECIMAL, якщо вам не потрібні дуже точні числа

VARCHAR / CHAR

При виборі типів рядків також діє правило мінімуму. Оцініть максимальну довжину рядка і поставте обмеження. Тип CHAR - тип фіксованої довжини. Це означає, що для будь-якого рядка буде виділено завжди одне і те ж кількість байт:

# Колонка буде завжди займати 2 байта (навіть, якщо буде порожня)

VARCHAR - тип змінної довжини. У такій колонці рядок буде займати рівно свою довжину (в байтах):

# Колонка буде займати від 1 до 32 байт в залежності від конкретного значення

Однак Mysql додасть ще 1 байт на зберігання довжини самої рядки. Також варто врахувати, що оновлення такого рядка може бути дорогою операцією (загрожує фрагментацією даних, а значить - уповільненням читання). Використовуйте таке правило: Якщо значення в текстовій колонці схожі по довжині, вибирайте CHAR, інакше - VARCHAR.

BLOB / TEXT

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

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

# Вказуємо довжину колонки для індексації

Важко уявити навіщо потрібно індексувати текстові колонки, просто не робіть цього. Якщо ж захочете використовувати можливість створення індексу для перевірки унікальності, використовуйте допоміжну колонку для зберігання md5 хеша від тексту:

# За колонці body_md5 можна створити унікальний індекс

Значення рядків може бути одним з заздалегідь відомих значень (наприклад, список країн). Якщо список фіксований (нові країни не з'являються щодня), зручно буде використовувати тип ENUM. Він дозволяє задати певний список значень і використовувати тільки їх в рядках таблиці.

Перевага цього типу в тому, що він записує номер значення замість самого значення в кожен рядок. Цим забезпечується величезна економія місця.

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

DATETIME / TIMESTAMP

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

DATETIME займає 8 байт і дозволяє зберігати дати з 1001 року до 9999 року.
TIMESTAMP займає 4 байта і дозволяє зберігати дати з 1970 року до 2038 року.

# Ініціалізація і автообновление колонок TIMESTAMP

В інших випадках використовуйте DATETIME.

TL; DR версія

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