II. RF KMF DokuWiki

Вибираючи тип даних для стовпця, перш за все варто визначити загальний клас даних, який краще використовувати для стовпця: числові, строкові або часові;
Дані необхідно вибрати конкретний тип даних з представлених в класі. Багато типів даних MySQL дозволяють зберігати дані одного і того ж типу, але з різними діапазонами значень, точністю або необхідним фізичним простором (на диску або в пам'яті).
Деякі типи мають специфічні властивості.
Наприклад, у DATETIME і TIMESTAMP можна зберігат и один і той же тип даних: дату і час, з точністю до секунди.
Однак тип TIMESTAMP потребує вдвічі менше місця, дозволяє працювати з часовими поясами і володіє спеціальними засобами автоматичного оновлення. З іншого боку, діапазон допустимих значень для нього менший.

На що звернути увагу при виборі типу даних:

• Намагатися використовувати типи даних мінімального розміру, достатнього для їх правильного зберігання та обробки. Як правило, менші за розміром типи даних швидші, оскільки займають менше місця на диску, в пам'яті і в кеші процесора.
• Чим простіше, тим краще. C точки зору системи, порівняння цілих чисел простіше порівняння символів, оскільки через різні кодування і правила сортування порівняння символів ускладнюється.
  

Тому значення дати і часу краще зберігати у вбудованих типах даних MySQL, а не в рядках, а для IP-адрес має сенс використовувати цілочисельні типи даних.

• Уникайте значень NULL. Для MySQL оптимізація запитів, що містять у стовпцях NULL, викликає додаткові складності, оскільки через них ускладнюються індекси, статистика індексів і порівняння значень. Стовпець, що допускає NULL, займає більше місця на диску і вимагає спеціальної обробки всередині MySQL. Якщо є необхідність відобразити в таблиці факт відсутності значення, можна обійтися без використання NULL. Замість цього, наприклад, можна використовувати 0, спеціальне значення або порожній рядок.
• Має сенс використовувати у зв'язаних стовпцях однакові типи даних. Використання різних типів даних може уповільнити обробку запиту.

Швидкість при з'єднанні стовпців типу VARCHAR і ENUM:
Запитів в секунду:

 З'єднання VARCHAR з VARCHAR	2.6
 З'єднання VARCHAR з ENUM	1.7
 З'єднання ENUM з VARCHAR	1.8
 З'єднання ENUM з ENUM	3.5

З метою сумісності MySQL підтримує різні псевдоніми, наприклад INTEGER, BOOL - це псевдоніми (синоніми) одного і того ж типу даних. Даний факт не впливає на продуктивність.

Числові типи
BIT - можна використовувати для зберігання одного або декількох значень true-false в одному стовпці. BIT (1) визначає поле, що містить один біт, BIT (2) - два біта і т. д. Максимальна довжина стовпчика типу BIT дорівнює 64 бітам.
До версії MySQL 5.0 слово BIT було синонімом TINYINT. Поведінка типу BIT залежить від підсистеми зберігання.

Зберігання цілих чисел

• TINYINT (N) (синонім INTEGER, BOOL, BOOLEAN) - 8 біт;
• SMALLINT (N) - 16 біт;
• MEDIUMINT (N) - 24 біта;
• INT (N) - 32 біта;
• BIGINT (N) - 64 біта.

СУБД MySQL дозволяє вказувати для цілих чисел «розмір», наприклад INT (11).
Для більшості додатків це не має значення: діапазон можливих значень цим не обмежується. Однак даний параметр говорить деяким інтерактивним інструментам MySQL, скільки позицій необхідно зарезервувати для виведення числа.
З точки зору зберігання і обчислення INT(1) і INT(20) ідентичні.
Цілочисельний тип даних довжиною N біт дозволяє зберігати значення від -2^(N-1) до 2^(N-1) -1.
Цілі типи даних можуть мати необов'язковий атрибут UNSIGNED, що забороняє негативні значення і вдвічі збільшує верхню межу додатних значень. Наприклад, тип TINYINT UNSIGNED дозволяє зберігати значення від 0 до 255, а не від -128 до 127.
Знакові і беззнакові типи потребують однакового простору і мають однакову продуктивністю.
Необов'язковий атрибут ZEROFILL заповнить нулями вільні позиції зліва. Наприклад з TINYINT (3) ZEROFILL, величина 2 буде записана, як 002.

Зберігання дробовних чисел
Всі типи допускають наближені математичні обчислення з плаваючою точкою, але у випадку з FLOAT і DOUBLE операції виконуються швидше, так як процесор виконує їх природним для нього чином.

• FLOAT (M, D) - число з плаваючою точкою невеликий точності (пам’ять 4 байта).
• DOUBLE (M, D) (синоніми REAL, DOUBLE PRECISION) - число з плаваючою точкою подвійної точності (пам'ять 8 байт), має більшу точність і більший діапазон значень.
• DECIMAL (M, D) (синоніми DEC, NUMERIC, FIXED) - дробове число, яке зберігається у вигляді рядка, якщо десяткове значення дорівнює 0, значення не буде мати дробової частини. M - кількість символів, які відводяться під число. D - кількість символів дробової частини. Призначений для зберігання точних дрібних чисел (можна зберігати великі цілі числа, що не поміщаються в типі BIGINT). Має сенс використовувати тільки тоді, коли потрібні точні результати при обчисленнях з дробовими числами, наприклад, при зберіганні фінансових даних.

Для типів з плаваючою точкою можна вказати максимально дозволену кількість цифр до і після десяткової коми. У випадку з DECIMAL це впливає на об’єм пам’яті, необхідної для зберігання даних стовпця. При заданні точності, в деяких випадках, MySQL непомітно для користувача може вибирати інший тип даних або буде округляти значення при збереженні.
FLOAT і DOUBLE можуть мати параметр UNSIGNED, що забороняє негативні числа, але діапазон значень від цього не зміниться.
Число типу DECIMAL в MySQL 5.0 і новіших версіях може містити до 65 цифр. У більш ранніх версіях MySQL тип DECIMAL мав межа 254 цифри і зберігав значення у вигляді упакованих рядків (один байт на цифру).
Однак ці версії СУБД не вміли використовувати такі великі числа в обчисленнях, оскільки тип DECIMAL був просто форматом зберігання. При виконанні будь-яких операцій значення DECIMAL перетворювалися в тип DOUBLE.

Строкові типи
У типах CHAR і VARCHAR рядки розглядаються як послідовності символів, тому, при використанні багатобайтових кодувань, наприклад UNICODE, розмір рядка в байтах буде більше, ніж в символах.

• VARCHAR (N) - зберігає символьні рядки змінної довжини і є найбільш загальним строковим типом даних. Значення N може приймати значення від 0 до 65535 (до версії MySQL 5.0.3 значення N могло бути від 0 до 255).
  Рядки цього типу можуть займати менше місця, ніж рядки фіксованої довжини CHAR. Це тому, що в VARCHAR використовується лише ту кількість місця, яке дійсно необхідно (за винятком таблиць у яких заданий фіксований розмір рядків). У типі VARCHAR використовується один або два додаткових байта для зберігання довжини рядка: один байт, якщо максимальна довжина рядка в стовпці не перевищує 255 байт, і два байта, якщо – більш довгих рядків.
  Тобто тип VARCHAR (10) може займати до 11 байт. Тип VARCHAR (1000) займає до 1002 байт, оскільки в даному випадку для зберігання інформації про довжину рядка потрібно два байта. VARCHAR збільшує продуктивність за рахунок меншого споживання місця на диску.
  Однак оскільки рядки мають змінну довжину, вони здатні збільшуватися при оновленні, що викликає додаткове навантаження. Якщо рядок стає довшим і більше не поміщається в раніше відведене для нього місце, то ця ситуація залежить від підсистеми зберігання.
  

Зазвичай має сенс використовувати тип VARCHAR при дотриманні хоча б однієї з таких умов:
максимальна довжина рядка в стовпці значно більше середньої;
оновлення поля виконується рідко, так що фрагментація не викликає особливих проблем;
або використовується складне кодування, наприклад UTF-8, в якому для зберігання одного символу використовується змінна кількість байтів.

• CHAR (N) - має фіксовану довжину, від 0 до 255 байт. При збереженні коротких значень CHAR вони доповнюються справа пробілами до зазначеної довжини.
  Тип CHAR корисний, коли потрібно зберігати дуже короткі строки або всі значення мають приблизно однакову довжину. Наприклад, CHAR є хорошим вибором для зберігання MD5-згорток паролів користувачів, які завжди мають однакову довжину. Тип CHAR також має перевагу над VARCHAR для даних, які часто змінюються, оскільки рядок фіксованої довжини не фрагментуються. У разі дуже коротких стовпців тип CHAR також ефективніший, ніж VARCHAR.
  

При створенні таблиці можна комбінувати стовпці типів CHAR і VARCHAR. В цьому випадку MySQL змінить тип стовпців CHAR на тип VARCHAR.
Уявімо що строковий тип застосовується для зберігання значень Y і N. У разі використання CHAR (1) значення займе один байт, тоді як для типу VARCHAR (1) буде потрібно два байта через наявність додаткового байта довжини рядка.

Приклади\\??????????????????????????

Двійкові рядки
Для сумісності зі старими версіями MySQL введені два спеціальних типи даних: BINARY і VARBINARY, які еквівалентні типам CHAR і VARHAR, проте рядок в них розглядається як послідовність байтів, а не символів.
У BINARY рядках не можна кодувати і упорядковані вони, як звичайні послідовності байтів.
Ці типи можуть бути корисні, коли потрібно зберігати двійкові дані, і потрібно, щоб MySQL порівнював значення як байти, а не як символи. При цьому, двійкове порівняння може виявитися значно простіше і швидше символьного.

• VARBINARY - зберігає бінарні рядки змінної довжини.
• BINARY - зберігає бінарні рядки фіксованої довжини.

Текстові та бінарні типи Вони призначені для зберігання двійкових або символьних даних великих об’ємів.
MySQL обробляє значення BLOB і TEXT, як окремі об'єкти. Єдина відмінність між BLOB і TEXT полягає в тому, що типи BLOB зберігають двійкові дані без урахування впорядкування і кодування, а з типами TEXT асоційовані схеми впорядкування і кодування.
Сімейство TEXT використовується для зберігання безпосередньо тексту:

• TEXT (синонім SMALLTEXT)
• TINYTEXT
• MEDIUMTEXT
• LONGTEXT

Сімейство BLOB використовується для зберігання зображень, звуку, електронних документів і т.д .:

• BLOB (синонім SMALLBLOB)
• TINYBLOB
• MEDIUMBLOB
• LONGBLOB

MySQL не може індексувати дані цих типів по повній довжині і не може використовувати для сортування індекси.
Підсистема зберігання Memory не підтримує типи BLOB і TEXT.

Складені типи

• ENUM ( 'value1', 'value2', …, 'valueN') - рядки цього типу можуть приймати тільки одне із значень множини. Можна зберігати до 65 535 різних строкових значень. MySQL зберігає їх дуже компактно, упаковуючи в 1 або 2 байта, в залежності від кількості значень в списку. MySQL сприймає кожне значення як ціле число, яке представляє позицію значення в списку значень поля, і окремо зберігає в frm-файлі «довідкову таблицю», яка визначає відповідність між числом і рядком.
  Поля типу ENUM упорядковані відповідно до внутрішнім цілочисельним значенням, а не за самими рядками. Головним недоліком стовпців типу ENUM є те, що список рядків фіксований, а для їх додавання або видалення необхідно використовувати команду ALTER TABLE.
  Цей тип даних зручно використовувати, якщо в стовпці повинен зберігатися вибір зі списку або відповідь на питання.
• SET ( 'value1', 'value2', …, 'valueN') - рядки цього типу можуть приймати будь-яке або всі елементи із значень множини. Як правило, при пошуку в стовпцях типу SET не використовуються індекси. Можна зберігати до 64 різних строкових значень. Може займати до 8 байт, в залежності від кількості значень в списку.

Часові типи

• DATE - призначений для зберігання дати. Формат: „YYYY”, роздільник, „ММ”, роздільник, „DD”. Як роздільник може виступати не тільки дефіс «-», а й будь-який символ відмінний від цифри.
• DATETIME - призначений для зберігання і дати і часу доби. Дозволяє зберігати значення в великому діапазоні, з 1001 до 9999 роки, з точністю в одну секунду.
  Дата і час упаковуються в ціле число в форматі YYYYMMDDHHMMSS незалежно від часового поясу. Під значення відводиться вісім байт. За замовчуванням MySQL показує дані типу DATETIME в чітко визначеному форматі, і сортування реалізує у форматі: 2008-01-16 22:37:08.
  Цей спосіб представлення дати і часу відповідає стандарту ANSI.
• TIME - призначений для зберігання часу доби. Значення вводиться і зберігається в звичному форматі: hh: mm: ss, де hh - години, mm - хвилини, ss - секунди. Як роздільник може виступати будь-який символ відмінний від цифри.
• TIMESTAMP - призначений для зберігання дати та часу доби у вигляді кількості секунд, що пройшли з півночі 1 січня 1970 року по грінвічському годиннику. З точки зору використання пам’яті він набагато ефективніше, ніж DATETIME.
  

Для зберігання типу TIMESTAMP використовується тільки чотири байти, але він дозволяє представити значно менший діапазон дат, ніж тип DATETIME: з 1970 року до деякої дати в 2038 році.
В MySQL є функції FROM_UNIXTIME () і UNIX_TIMESTAMP () – службові функції для перетворення. Стовпці типу TIMESTAMP за замовчуванням створюються в режимі NOT NULL, якщо не вказано значення для стовпця, MySQL вставляє в перший стовпець типу TIMESTAMP поточний час.
Тип TIMESTAMP має також спеціальні властивості, яких немає у типу DATETIME.

• YEAR (N) - призначений для зберігання року. Число N задає формат року: YEAR (2) - 70, а YEAR (4) - 1970. За замовчуванням, N = 4.

Таблиці типу Memory
Таблиці типу Memory (раніше називались таблицями типу HEAP) корисні, коли необхідно здійснити швидкий доступ до даних, які або ніколи не змінюються, або немає потреби в їх збереженні після перезапуску.
Зазвичай таблиці типу Memory обробляються приблизно на порядок швидше, ніж таблиці MyISAM. Всі їх дані зберігаються в пам'яті, тому запитам не потрібно чекати виконання операцій дискового введення / виводу. Структура таблиці Memory зберігається після перезапуску сервера, але дані губляться.
Векілька корисних застосувань для таблиць Memory:

• Для «довідкових» таблиць або таблиць «відповідності», наприклад для таблиці, в якій поштовими кодами відповідають назви регіонів
• Для кешування результатів періодичного агрегування даних
• Для проміжних результатів при аналізі даних Таблиці Memory підтримують індекси типу HASH, що забезпечують дуже велику швидкість виконання пошукових запитів.

Таблиці Memory працюють дуже швидко, але не завжди годяться в якості заміни дискових таблиць. Вони використовують блокування на рівні таблиці, що зменшує конкуренцію під час запису, і не підтримують стовпці типу TEXT і BLOB.
Також вони допускають використання тільки рядків фіксованого розміру, тому значення типу VARCHAR зберігаються як значення типу CHAR, що призводить до зростання споживання пам'яті.
MySQL всередині себе використовує підсистему Memory для зберігання проміжних результатів при обробці запитів, яким потрібна тимчасова таблиця. Якщо проміжний результат стає занадто великим для таблиці Memory або містить стовпці типу TEXT або BLOB, то MySQL перетворює його в таблицю MyISAM на диску.
Часто плутають таблиці типу Memory з тимчасовими таблицями, які створюються командою CREATE TEMPORARY TABLE.
Тимчасові таблиці можуть використовувати будь-яку підсистему зберігання. Це не те ж саме, що таблиці типу Memory. Тимчасові таблиці видно тільки в одному з'єднанні і повністю зникають при його закритті.