• Заметки

    • Типы данных MySQL. Чем руководствоваться при выборе типа данных

    Меню

    Опубликовано: 23 Апреля 2019

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

    Далее необходимо выбрать конкретный тип данных из представленных в классе. Многие типы данных 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.

    Тип данных Бит По умолчанию UNSIGNED
    TINYINT 8 -128 — 127 0 — 255
    SMALLINT 16 -32768 — 32767 0 — 65535
    MEDIUMINT  24 -8388608 — 8388607 0 — 16777215
    INT  32 -2147483648 — 2147483647 0 — 4294967295
    BIGINT  64 -9223372036854775808 — 9223372036854775807 0 — 18446744073709551615

    Хранение дробных чисел

    Все типы допускают приближенные математические вычисления с плавающей точкой, но в случае с FLOAT и DOUBLE операции выполняются быстрее, так как процессор выполняет их естественным для него образом.

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

    M — количество отводимых под число символов. D — количество символов дробной части. 

    Для типов с плавающей точкой можно указать максимально разрешенное количество цифр до и после десятичной запятой. В случае с 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) потребуется два байта из-за наличия дополнительного байта длины строки.

    Примеры

    Значение CHAR(4) Требуется хранилище VARCHAR(4) Требуется хранилище
    '' '    ' 4 байта '' 1 байт
    'ab' 'ab  ' 4 байта 'ab' 3 байта
    'abcd' 'abcd' 4 байта 'abcd' 5 байт
    'abcdefgh' 'abcd' 4 байта 'abcd' 5 байт

    Двоичные строки

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

    • VARBINARY — хранит бинарные строки переменной длины.
    • BINARY — хранит бинарные строки фиксированной длины.

    Текстовые и бинарные типы 

    Предназначены для хранения больших объемов двоичных или символьных данных. 

    MySQL обрабатывает значения BLOB и TEXT как отдельные объекты. Единственное различие между семействами BLOB и TEXT заключается в том, что типы BLOB хранят двоичные данные без учета схемы упорядочения и кодировки, а с типами TEXT ассоциированы схемы упорядочения и кодировка.

    Семейство TEXT используется для хранения непосредственно текста:

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

    Cемейство BLOB — для хранения изображений, звука, электронных документов и т.д.:

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

    MySQL не может индексировать данные этих типов по полной длине и не может использовать для сортировки индексы.

    Подсистема хранения Memory не поддерживает типы BLOB и TEXT.

    Типы данных Макс. размер.  Байт
    TINYTEXT или TINYBLOB 28-1 255
    TEXT или BLOB 216-1 (64K-1) 65535
    MEDIUMTEXT или MEDIUMBLOB 224-1 (16M-1) 16777215
    LONGTEXT или LONGBLOB 232-1 (4G-1) 4294967295

    Составные типы

    • 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 года по гринвичскому времени (начало эпохи UNIX). С точки зрения занимаемого места на диске он гораздо эффективнее, чем DATETIME. Для хранения типа TIMESTAMP используется только четыре байта, поэтому он позволяет представить значительно меньший диапазон дат, чем тип DATETIME: с 1970 года до некоторой даты в 2038 году. В MySQL имеют ся функции FROM_UNIXTIME() и UNIX_TIMESTAMP(), служащие для преобразования временной метки UNIX в дату и наоборот. Столбцы типа TIMESTAMP по умолчанию создаются в режиме NOT NULL, если вы не указали значение для столбца, MySQL вставляет в первый столбец типа TIMESTAMP текущее время. Тип TIMESTAMP имеет также специальные свойства, которых нет у типа DATETIME.
    • YEAR(N) — предназначен для хранения года. Число N задает формат года: YEAR (2) — 70, а YEAR (4) — 1970. По умолчанию, N = 4.
    Типы данных Формат
    DATETIME ‘0000-00-00 00:00:00’
    DATE ‘0000-00-00’
    TIMESTAMP 00000000000000 (длина зависит от количества выводимых символов)
    TIME ‘00:00:00’
    YEAR 0000
    MySQL back-end