Выбор оптимальных типов данных в базе MySQL

Высокой производительности можно достичь только при хорошем логическом и физическом проектировании схемы базы данных MySQL. Следует проектировать свою схему для конкретных запросов, которые вы будете запускать. При этом часто приходится идти на компромиссы. Например, денормализованная схема может ускорить одни типы запросов, но замедлить другие. Добавление таблиц счетчиков и сводных таблиц — хороший способ оптимизации запросов, но их поддержка может дорого стоить. Особенности использования MySQL незначительно влияют на это.

Текущий блог и следующая статья (ссылка будет в конце), посвященная индексированию, охватывают характерные для MySQL области проектирования схемы. При этом мы предполагаем, что вы знаете, как проектировать базы данных. Поскольку это статья по проектированию базы данных MySQL, речь в ней пойдет о различиях при проектировании баз данных в MySQL и других СУРБД. Если вы хотите изучить основы проектирования баз данных, предлагаем прочесть книгу Клер Чурчер (Clare Churcher) Beginning Database Design (издательство Apress).

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

MySQL поддерживает множество типов данных. Выбор правильного типа для хранения вашей информации критичен с точки зрения увеличения производительности. Следующие простые рекомендации помогут сделать правильный выбор независимо от типа сохраняемых данных.

• Меньше — обычно лучше. Старайтесь использовать типы данных минимального размера, достаточного для их правильного хранения и представления. Меньшие по размеру типы данных обычно быстрее, поскольку занимают меньше места на диске, в памяти и кэше процессора. Кроме того, для их обработки обычно требуется меньше процессорного времени. Однако убедитесь, что правильно представляете диапазон возможных значений данных, поскольку увеличение размерности типа данных на многих участках схемы может оказаться болезненным и длительным процессом. Если вы сомневаетесь в том, какой тип данных использовать, выберите самый короткий при условии, что его размера будет достаточно. (Если система не сильно загружена, хранит не очень много данных или вы находитесь на раннем этапе процесса проектирования, то легко сможете позже изменить решение.)
• Просто — значит хорошо. Для выполнения операций с более простыми типами данных обычно требуется меньше процессорного времени. Например, сравнение целых чисел менее затратно, чем сравнение символов, поскольку различные кодировки и схемы упорядочения (правила сортировки) усложняют сравнение символов. Приведем два примера: значения даты и времени следует хранить во встроенных типах данных MySQL, а не в строках. Для IP-адресов используйте целочисленные типы данных. Мы обсудим этот вопрос позднее.
• Насколько это возможно, избегайте значений NULL. Очень часто в таблицах встречаются поля, допускающие хранение NULL (отсутствие значения), хотя приложению это совершенно не нужно просто потому, что такой режим установлен по умолчанию. Как правило, стоит объявить столбец как NOT NULL, если только вы не планируете хранить в нем значения NULL. MySQL тяжело оптимизировать запросы, содержащие допускающие NULL столбцы, поскольку из-за них усложняются индексы, статистика индексов и сравнение значений. Столбец, допускающий NULL, занимает больше места на диске и требует специальной обработки. Когда такой столбец проиндексирован, ему требуется дополнительный байт для каждой записи, а в MylSAM даже может возникнуть ситуация, когда придется преобразовать индекс фиксированного размера (например, индекс по одному целочисленному столбцу) в индекс переменного размера. Повышение производительности в результате замены столбцов NULL на NOT NULLобычно невелико, так что не делайте их поиск и изменение в существующих схемах приоритетными, если не уверены, что именно они вызывают проблемы. Но если вы планируете индексировать столбцы, по возможности не делайте их допускающими NULL. Конечно, бывают и исключения. Например, стоит упомянуть, что подсистема InnoDB использует для хранения значения NULL один бит, поэтому она может быть довольно экономичной для неплотно заполненных данных. Однако это не относится к MylSAM.

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

Следующим шагом является выбор конкретного типа. Многие типы данных MySQL позволяют хранить данные одного и того же вида, но с разными диапазоном значений и точностью или требующие разного физического пространства (на диске или в памяти). Кроме того, некоторые типы данных обладают специальным поведением или свойствами.

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

Здесь мы обсудим основные типы данных. В целях совместимости MySQL поддерживает различные псевдонимы, например INTEGER, BOOL и NUMERIC. Все это именно псевдонимы. Данный факт может сбить с толку, но не влияет на производительность. Если вы создадите таблицу с псевдонимом типа данных, а затем запустите команду SHOW CREATE TABLE, то увидите, что MySQL выдает базовый тип, а не использованный псевдоним.

Целые числа

Существуют два типа чисел: целые и вещественные (числа с дробной частью). Для хранения целых чисел используйте один из целочисленных типов: TINYINT, SMALLINT. MEDIUMINT, INT или BIGINT, Для хранения они требуют 8,16,24,32 и 64 бита соответственно. Они позволяют хранить значения в диапазоне от - 2^{(N - 1)} до 2^{(N- 1)} - 1, где N— количество битов, использованных для их хранения.

Целые типы данных могут иметь необязательный атрибут UNSIGNED, запрещающий отрицательные значения и приблизительно вдвое увеличивающий верхний предел хранимых положительных значений. Например, тип TINYINT UNSIGNED позволяет хранить значения от 0 до 255, а не от -128 до 127.

Знаковые и беззнаковые типы требуют одинакового пространства и обладают одинаковой производительностью, так что используйте тот тип, который больше подходит для диапазона ваших данных.

Ваш выбор определяет то, как СУБД MySQL хранит данные в памяти и на диске. Однако для целочисленных вычислений обычно используются 64-разрядные целые типа BIGINT, даже на машинах с 32-разрядной архитектурой (исключение составляют некоторые агрегатные функции, использующие для вычислений тип DECIMAL или DOUBLE).

MySQL позволяет указать для целых чисел размер, например INT(11). Для большинства приложений это неважно — это не ограничивает диапазон возможных значений, лишь определяет, сколько позиций интерактивным инструментам MySQL (например, клиенту командной строки) необходимо зарезервировать для вывода числа. С точки зрения хранения и вычисления INT(1) и INT(20) идентичны.

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

Вещественные числа

Вещественные числа — это числа, имеющие дробную часть. Однако они используются не только для дробных чисел — в типе данных DECIMAL также можно хранить большие числа, не помещающиеся в типе BIGINT, MySQL поддерживает как «точные», так и «неточные» типы.

Типы PLOAT и DOUBLE допускают приближенные математические вычисления с плавающей точкой. Если вам нужно точно знать, как вычисляются результаты с плавающей точкой, изучите, как это реализовано на имеющейся у вас платформе.

Тип DECIMAL используется для хранения точных дробных чисел. В MySQL версии 5.0 и более поздних DECIMAL поддерживает еще и точные вычисления. MySQL 4.1 и более ранние версии для работы с DECIMAL применяли вычисления с плавающей точкой, которые иногда давали странные результаты из-за ухудшения точности. В этих версиях MySQL тип DECIMAL предназначался только для хранения.

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

Как типы с плавающей запятой, так и тип DECIMAL позволяют задавать нужную точность. Для столбца типа DECIMAL вы можете определить максимальное количество цифр до и после запятой. Это влияет на объем пространства, требующегося для хранения данных столбца. MySQL 5.0 и более новые версии упаковывают цифры в двоичную строку (девять цифр занимают четыре байта). Например, DECIMAL(18, 9) будет хранить девять цифр с каждой стороны от запятой, используя в общей сложности 9 байт: 4 байта для цифр перед запятой, 1 байт для самой запятой и 4 байта для цифр после нее.

Число типа DECIMAL в MySQL 5.0 и более новых версиях может содержать до 65 цифр. Более ранние версии MySQL имели предел 254 цифры и хранили значения в виде неупакованных строк (1 байт на цифру). Однако эти версии MySQL наделе не могли использовать такие большие числа в вычислениях, поскольку тип DECIMAL был просто форматом хранения. При выполнении вычислений значения DECIMAL преобразовывались в тип DOUBLE.

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

Типы с плавающей точкой обычно задействуют для хранения одного и того же диапазона значений меньше пространства, чем тип DECIMAL. Столбец типа FLOAT использует 4 байта. Тип DOUBLE требует 8 байт и имеет большую точность и больший диапазон значений, чем FLOAT. Как и в случае работы с целыми числами, вы выбираете тип только для хранения. MySQL использует тип DOUBLE для вычислений с плавающей точкой.

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

Но при некоторых операциях с большими числами целесообразнее применять тип BIGINT вместо DECIMAL и хранить данные как кратные наименьшей доле валюты, которую вам нужно обрабатывать. Предположим, необходимо хранить финансовые данные с точностью до 1/10 000 цента. Вы можете умножить все суммы в долларах на 1 миллион и сохранить результат в BIGINT, Тем самым избежите как неточности хранения типов с плавающей запятой, так и высоких затрат на точные расчеты типа DECIMAL.

Строковые типы

MySQL поддерживает большой диапазон строковых типов данных с различными вариациями. Эти типы претерпели значительные изменения в версиях 4.1 и 5.0, что сделало их еще более сложными. Начиная с версии MySQL 4.1, каждый строковый столбец может иметь собственную кодировку и соответствующую схему упорядочения. Данное обстоятельство может значительно повлиять на производительность.

Типы VARCHAR и CHAR

Два основных строковых типа — это VARCHAR и CHAR, предназначенные для хранения символьных значений. К сожалению, нелегко объяснить, как именно эти значения хранятся в памяти и на диске, поскольку конкретная реализация зависит от выбранной подсистемы хранения. Мы предполагаем, что вы используете InnoDB и/или MylSAM. В противном случае вам лучше прочитать документацию по используемой подсистеме хранения.

Давайте посмотрим, как обычно значения VARCHAR и CHAR сохраняются на диске. Имейте в виду, что подсистема хранения может хранить CHAR или VARCHAR в памяти не так, как на диске, и что сервер может преобразовывать данные в другой формат, когда извлекает их из подсистемы хранения. Приведем общее сравнение этих двух типов.

VARCHAR

Тип VARCHAR хранит символьные строки переменной длины и является наиболее используемым строковым типом данных. Строки этого типа могут занимать меньше места, чем строки фиксированной длины, поскольку для VARCHAR используется столько места, сколько действительно необходимо (то есть для хранения более коротких строк требуется меньше пространства). Исключением являются таблицы типа MylSAM, созданные с параметром ROW_FORMAT=FIXED. В этом случае для каждой строки на диске отводится область фиксированного размера, поэтому место может расходоваться впустую.

В типе VARCHAR для хранения длины строки используются 1 или 2 дополнительных байта: 1 байт, если максимальная длина строки в столбце не превышает 255 байт, и 2 байта — при более длинных строках. Если используется кодировка latinl, тип VARCHAR (10) может занимать до 11 байт. Тип VARCHAR (1000) может использовать до 1002 байт, поскольку 2 байта требуется для хранения информации о длине строки.

Тип VARCHAR увеличивает производительность за счет экономии места. Но поскольку это строки переменной длины, они способны увеличиваться при обновлении, что требует дополнительной работы. Если строка становится длиннее и больше не помещается в отведенное для нее место, то дальнейшее поведение системы зависит от подсистемы хранения. Например, MylSAM может фрагментировать строку, a InnoDB, возможно, придется выполнить разбиение страницы. Другие подсистемы хранения могут вообще не обновлять данные в месте их хранения.

Обычно целесообразно использовать тип VARCHAR, если максимальная длина строки в столбце значительно больше средней, обновление поля выполняется редко, так что фрагментация не представляет проблемы, и если применяется сложная кодировка, например UTF-8, в которой для хранения одного символа используется переменное количество байтов.

В MySQL 5.0 и более новых версиях при сохранении и извлечении текстовых строк MySQL сохраняет пробелы в конце строки. В версии же 4.1 и более ранних они удаляются.

Интереснее ситуация с InnoDB, которая может хранить длинные значения типа VARCHAR в виде BLOB. Подробнее мы рассмотрим эту способность позже.

CHAR

Тип CHAR имеет фиксированную длину: MySQL всегда выделяет место для указанного количества символов. Сохраняя значения CHAR, MySQL удаляет все пробелы в конце строки (это справедливо также для типа VARCHAR в MySQL 4.1 и более ранних версий — CHAR и VARCHAR были логически идентичны и различались только форматом хранения). К значениям для сравнения добавляются пробелы.

Тип CHAR полезен, когда требуется сохранять очень короткие строки или все значения имеют приблизительно одинаковую длину. Например, CHAR хорошо подходит для хранения МD5-сверток паролей пользователей, которые всегда имеют одинаковую длину. Также тип CHAR имеет преимущество над VARCHAR при часто меняющихся данных, поскольку строка фиксированной длины не подвержена фрагментации. Если в столбцах очень короткие строки, тип CHAR также эффективнее, чем VARCHAR: если тип CHAR(1) применяется для хранения значений только Y и N, то в однобайтовой кодировке он займет лишь 1 байт, тогда как для типа VARCHAR(1) из-за наличия дополнительного байта длины строки потребуется 2 байта.

Это поведение может несколько сбивать с толку, поэтому проиллюстрируем его на примере. Сначала мы создали таблицу с одним столбцом типа CHAR (10) и сохранили в нем какие-то значения:

mysql> CREATE TABLE char_test( char_col CHAR(10));
mysql> INSERT INTO char_test(char_col) VALUES
    -> ('string1'), ('  string2'), ('string3 ');

При извлечении значений пробелы в конце строки удаляются:

mysql> SELECT CONCAT("'", char_col, "'") FROM char_test;
+----------------------------+
| CONCAT("'", char_col, "'") |
+----------------------------+
| 'string1'                  |
| '  string2'                |
| 'string3'                  |
+----------------------------+

Если мы сохраним те же значения в столбце типа VARCHAR(10), то после извлечения получим следующий результат:

mysql> SELECT CONCAT("'", varchar_col, "'") FROM varchar_test;
+-------------------------------+
| CONCAT("'", varchar_col, "'") |
+-------------------------------+
| 'string1'                     |
| '  string2'                   |
| 'string3 '                    |
+-------------------------------+

Как именно записываются данные, зависит от подсистемы хранения, и не все подсистемы одинаково обрабатывают значения фиксированной и переменной длины. В подсистеме Memory используются строки фиксированной длины, поэтому ей приходится выделять максимально возможное место для каждого значения, даже если это поле переменной длины2. Однако поведение при дополнении и удалении пробелов одинаково для всех подсистем хранения, поскольку эту работу выполняет сам сервер MySQL.

Родственными типами для CHAR и VARCHAR являются BINARY и VARBINARY, предназначенные для хранения двоичных строк. Двоичные строки очень похожи на обычные, но вместо символов в них хранятся байты. Метод дополнения пробелами также отличается: MySQL добавляет в строки типа BINARY значение \0 (нулевой байт) вместо пробелов и не удаляет дополненные байты при извлечении.

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

Щедрость не всегда разумна

Значение ' hello’ занимает одно и то же место и в столбце типа VARCHAR(5), и в столбце типа VARCHAR(200). Обеспечивает ли преимущество применение более короткого столбца?

Оказывается, преимущество есть, и значительное. Для большего столбца может потребоваться намного больше памяти, поскольку MySQL часто выделяет для внутреннего хранения значений участки памяти фиксированного размера. Это особенно плохо для сортировки или операций, использующих временные таблицы в памяти. То же самое происходит при файловой сортировке, использующей временные таблицы на диске.

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

Типы BLOB и TEXT

Строковые типы BLOB и TEXT предназначены для хранения больших объемов двоичных или символьных данных соответственно.

Фактически это семейства типов данных: к символьным типам относятся TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT И LONGTEXT, а К ДВОИЧНЫМ - TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB и LONGBLOB. BLOB является синонимом для SMALLBLOB, a TEXT — для SMALLTEXT. В отличие от остальных типов данных каждое значение BLOB и TEXT MySQL обрабатывает как отдельный объект. Подсистемы хранения часто хранят их особым образом: если они большого размера, InnoDB может использовать для них отдельную внешнюю область хранения. Каждому значению такого типа требуется от 1 до 4 байт в самой строке и достаточно места во внешнем хранилище для хранения фактического значения.

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

MySQL сортирует столбцы BLOB и TEXT иначе, чем столбцы других типов: вместо сортировки строки по всей ее длине она сортирует только по первым max_sort_length байтам каждого столбца. Если нужна сортировка только по нескольким первым символам, вы можете либо уменьшить значение серверной переменной max_sort_length, либо использовать конструкцию ORDER BY SUBSTRING (столбец, длина).

MySQL не может индексировать данные этих типов по полной длине и не может применять для сортировки индексы (в следующей статье мы рассмотрим этот вопрос подробнее).

Временные таблицы на диске и сортировка файлов

Поскольку подсистема хранения Memory не поддерживает типы BLOB и TEXT, для запросов, в которых используются столбцы такого типа и которым нужна неявная временная таблица, придется задействовать временные таблицы MylSAM на диске, даже если речь идет всего лишь о нескольких строках. Подсистема хранения Memory в пакете Percona Server поддерживает типы BLOB и TEXT, но во время написания книги она все еще не предотвращала использования таблиц на диске.

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

Лучше всего не использовать типы BLOB и TEXT, если можно без них обойтись. Если же избежать этого не удается, можно задействовать конструкцию SUBSTRING (столбец, длина) везде, где применяется тип BLOB (в том числе в разделе ORDER BY), для преобразования значений в символьные строки, которые уже могут храниться во временных таблицах в памяти. Только убедитесь, что выбираете достаточно короткие подстроки, чтобы временная таблица не вырастала до объемов, превышающих значения переменных max_heap_table_size или tmp_table_size, иначе MySQL преобразует ее в таблицу MyISAM на диске.

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

Приведем пример. Предположим, у вас есть таблица с 10 миллионами строк, которая занимает пару гигабайт на диске. В таблице есть столбец VARCHAR(1000) с кодировкой UTF8. Эта кодировка может использовать до 3 байт на один символ, что в худшем случае составит 3000 байт. Если вы укажете этот столбец в разделе ORDER BY, запрос ко всей таблице может-потребовать более 30 Гбайт временного пространства только для сортировки файлов!

Если столбец Extra в результате применения команды EXPLAIN содержит слова Using temporary, значит, для запроса использована неявная временная таблица.

Использование типа ENUM вместо строкового типа

Иногда взамен обычных строковых типов можно задействовать тип ENUM. Столбец ENUM может хранить предопределенный набор различных строковых значений. MySQL сохраняет их очень компактно, упаковывая в 1 или 2 байта в зависимости от количества значений в списке. Она хранит каждое значение внутри себя как целое число, отражающее позицию его значения в списке значений поля, и сохраняет справочную таблицу, которая определяет соответствие между числом и строкой в файле .frm.

Приведем пример:

mysql> CREATE TABLE enum_test(
    ->    e ENUM('fish', 'apple', 'dog') NOT NULL
    -> );
mysql> INSERT INTO enum_test(e) VALUES('fish'), ('dog'), ('apple');

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

mysql> SELECT e + 0 FROM enum_test;
+-------+
| e + 0 |
+-------+
|     1 |
|     3 |
|     2 |
+-------+

Эта двойственность может вызвать путаницу, если вы определите числа в качестве констант ENUM, например ENUM('1', '2', '3'). Мы не рекомендуем так делать.

Другим сюрпризом является то, что поля типа ENUM сортируются по внутренним целочисленным значениям, а не по самим строкам:

mysql> SELECT e FROM enum_test ORDER BY e;
+-------+
| e     |
+-------+
| fish  |
| apple |
| dog   |
+-------+

Проблему можно решить, определив значения для столбца ENUM в желаемом порядке сортировки. Можно также использовать функцию FIELD(), чтобы явно задать порядок сортировки в запросе, но это не позволит MySQL применить для сортировки индекс:

mysql> SELECT e FROM enum_test ORDER BY FIELD(e, 'apple', 'dog', 'fish');
+-------+
| e     |
+-------+
| apple |
| dog   |
| fish  |
+-------+

Если бы мы определили значения в алфавитном порядке, нам не пришлось бы это делать.

Главным недостатком столбцов типа ENUM является то, что список строк фиксирован, а для их добавления или удаления необходимо использовать команду ALTER TABLE. Таким образом, возможно, не слишком хорошая идея — применить тип ENUM для представления строк, если предполагается изменить список возможных значений в будущем, за исключением случая, когда допустимо добавлять элементы в конец списка, что можно сделать без полного перестроения таблицы в MySQL 5.1.

Поскольку MySQL сохраняет каждое значение как целое число и вынуждена просматривать таблицы соответствий для преобразования их в строковое представление, то со столбцами типа ENUM связаны некоторые издержки. Обычно это компенсируется их малым размером, но так происходит не всегда. В частности, соединение столбца типа CHAR или VARCHAR со столбцом типа ENUM может оказаться медленнее, чем соединение с другим столбцом типа CHAR или VARCHAR.

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

CREATE TABLE webservicecalls (
   day date NOT NULL,
   account smallint NOT NULL,
   service varchar(10) NOT NULL,
   method varchar(50) NOT NULL,
   calls INT NOT NULL,
   items INT NOT NULL,
   time FLOAT NOT NULL,
   cost decimal(9,5) 

Таблица содержит около 110 000 строк, ее объем порядка 10 Мбайт, поэтому она целиком помещается в памяти. Столбец service содержит пять различных значений со средней длиной четыре символа, а столбец method — 71 значение со средней длиной 20 символов.

Мы сделали копию этой таблицы и преобразовали столбцы service и method к типу ENUM следующим образом:

CREATE TABLE webservicecalls_enum (
   ... опущено ...
   service ENUM(...значения пропущены...) NOT NULL,
   method ENUM(...значения пропущены...) NOT NULL,
   ... опущено ...
) ENGINE=InnoDB;

Затем измерили производительность соединения таблиц по столбцам первичного ключа. Для этого использовали такой запрос:

mysql> SELECT SQL_NO_CACHE COUNT(*)
    -> FROM webservicecalls
    ->    JOIN webservicecalls USING(day, account, service, method);

Мы варьировали этот запрос, соединяя столбцы типа VARCHAR и ENUM в различных комбинациях. Результаты показаны в табл. 1.

Таблица 1. Скорость соединения столбцов типа VARCHAR и ENUM

Соединение становится быстрее после преобразования всех столбцов к типу ENUM, но соединение столбцов типа ENUM со столбцами типа VARCHAR происходит медленнее. В данном случае кажется, что преобразование имеет смысл, если не планируется соединение этих столбцов со столбцами типа VARCHAR. Распространенная практика проектирования состоит в использовании справочных таблиц с целочисленными первичными ключами, чтобы избежать соединения по символьным значениям.

Однако можно получить пользу и от преобразования столбцов: команда SHOW TABLE STATUS в столбце Data_length показывает, что после преобразования двух столбцов к типу ENUM таблица стала приблизительно на треть меньше. В некоторых ситуациях это может принести выгоду даже в случае соединения столбцов типа ENUM со столбцами типа VARCHAR. Кроме того, сам первичный ключ после преобразования уменьшается приблизительно вдвое. Поскольку это таблица InnoDB, то если в ней есть и другие индексы, поэтому уменьшение размера первичного ключа приведет к уменьшению и их размера. Мы поясним это в следующей статье.

Типы Date и Time

MySQL содержит много различных типов для даты и времени, например YEAR и DATE. Минимальной единицей времени, которую может хранить MySQL, является 1 секунда. (У MariaDB минимальная единица временного типа — 1 микросекунда.) Однако вычисления с временными данными могут выполняться с точностью до 1 микросекунды, и мы покажем, как обойти ограничение хранения.

Большинство временных типов не имеют альтернатив, поэтому вопроса о том, какой из них лучше, не возникает. Нужно лишь решить, что делать, когда требуется сохранять и дату, и время. Для этих целей MySQL предлагает два очень похожих типа данных: DATETIME и TIMESTAMP. Для большинства приложений подходят оба, но в некоторых случаях один работает лучше, чем другой. Давайте рассмотрим их более подробно.

DATETIME

Этот тип дает возможность хранить большой диапазон значений — с 1001 по 9999 год — с точностью до 1 секунды. Дата и время упаковываются в целое число в формате YYYYMMDDHHMMSS независимо от часового пояса. Для хранения требуется 8 байт.

По умолчанию MySQL показывает данные типа DATETIME в точно определенном допускающем сортировку формате: 2008-01-16 22:37:08. Это стандарт представления даты и времени ANSI.

TIMESTAMP

Как следует из названия, в типе TIMESTAMP хранится количество секунд, прошедших после полуночи 1 января 1970 года по Гринвичу (GMT), — и как во временной метке UNIX. Для хранения данных типа TIMESTAMP используются только 4 байта, поэтому он позволяет представить значительно меньший диапазон дат, чем тип DATETIME: с 1970 года до некоторой даты в 2038 году. В MySQL имеются функции FROM_UNIXTIME() и UNIX_TIMESTAMP(), служащие для преобразования временной метки UNIX в дату и наоборот.

В MySQL 4.1 и более новых версиях значения типа TIMESTAMP форматируются точно так же, как значения DATETIME, но в MySQL 4.0 и более старых версиях они отображаются без разделителей между частями. Это различие проявляется только при выводе — формат хранения значений TIMESTAMP одинаков во всех версиях MySQL.

Отображаемое значение типа TIMESTAMP зависит также от часового пояса. Часовой пояс определен для сервера MySQL, операционной системы и клиентского соединения.

Таким образом, если в поле типа TIMESTAMP хранится значение 0, то для часового пояса Eastern Standard Time (EST), отличающегося от гринвичского времени на 5 часов, будет выведена строка 1969-12-31 19:00:00. Стоит подчеркнуть эту разницу: если вы храните данные, относящиеся к нескольким часовым поясам, или получаете к ним доступ, поведение TIMESTAMP и DATETIME будет различаться. Первый сохраняет значения относительно используемого часового пояса, а последний — текстовое представление даты и времени.

Кроме того, у типа TIMESTAMP есть специальные свойства, которых нет у типа DATETIME. По умолчанию, если вы не указали значение для столбца, MySQL вставляет в первый столбец типа TIMESTAMP текущее время. Кроме того, по умолчанию MySQL изменяет значение первого столбца типа TIMESTAMP при обновлении строки, если ему явно не присвоено значение в команде UPDATE. Вы можете настроить поведение при вставке и обновлении для каждого столбца типа TIMESTAMP. Наконец, столбцы типа TIMESTAMP по умолчанию создаются в режиме NOT NULL, в отличие от остальных типов данных.

В общем случае мы советуем пользоваться типом TIMESTAMP, если это возможно, поскольку с точки зрения занимаемого на диске места он намного эффективнее, чем DATETIME. Иногда временные метки UNIX сохраняют в виде целых чисел, но обычно это не дает никаких преимуществ. Целочисленное представление часто неудобно, поэтому мы не рекомендуем его применять.

Но что, если вам нужно сохранить значение даты и времени с точностью выше секунды? Сейчас в MySQL для этих целей нет соответствующего типа данных, но вы можете создать собственный формат хранения: применить тип BIGINT и сохранить значение как временную метку в микросекундах либо воспользоваться типом DOUBLE и указать дробную часть секунды после запятой. Оба подхода вполне работоспособны. Или вы можете воспользоваться MariaDB вместо MySQL.

Битовые типы данных

В MySQL есть несколько типов данных, использующих для компактного хранения значений отдельные биты. Все эти типы с технической точки зрения являются строковыми вне зависимости от формата хранения и обработки.

BIT

До версии MySQL 5.0 ключевое слово BIT было просто синонимом TINYINT. Но начиная с версии MySQL 5.0 это совершенно иной тип данных с особыми характеристиками. Обсудим его поведение.

Вы можете использовать столбец типа BIT для хранения одного или нескольких значений true/false в одном столбце. В1Т(1) определяет поле, содержащее 1 бит, BIT(2) хранит 2 бита и т. д. Максимальная длина столбца типа BIT равна 64 битам. Поведение типа BIT зависит от подсистемы хранения. MylSAM для хранения упаковывает битовые столбцы, поэтому для хранения 17 отдельных столбцов типа BIT требуется только 17 бит (предполагается, что ни в одном из столбцов не разрешено значение NULL). При вычислении размера места для хранения MylSAM округлит это число до 3 байт. Другие подсистемы, например Memory и InnoDB, хранят каждый столбец как наименьший целочисленный тип, достаточно большой для размещения всех битов, поэтому сэкономить пространство не получится.

MySQL рассматривает BIT как строковый, а не числовой тип. Когда вы извлекаете значение типа BIT(1), результатом является строка, но ее содержимое представляет собой двоичное значение 0 или 1, а не значение 0 или 1 в кодировке ASCII. Однако, если вы извлечете значение в числовом виде, результатом будет число, в которое преобразуется битовая строка. Учитывайте это, когда понадобится сравнить результат с другим значением. Например, если вы сохраните значение b'00111001', являющееся двоичным эквивалентом числа 57, в столбце типа BIT(8), а затем извлечете его, то получите строку, содержащую код символов 57. Этот код в кодировке ASCII соответствует цифре 9. Но в числовом виде вы получите значение 57:

mysql> CREATE TABLE bittest(a bit(8));
mysql> INSERT INTO bittest VALUES(b'00111001');
mysql> SELECT a, a + 0 FROM bittest;
+------+-------+
| a    | a + 0 |
+------+-------+
| 9    |    57 |
+------+-------+

Такое поведение может сбивать с толку, так что рекомендуем с осторожностью использовать тип BIT. В большинстве приложений лучше вообще его избегать.

Если возникла необходимость сохранять значение true/false в одном бите, можно также создать столбец типа CHAR(0) с возможностью хранения NULL. Такой столбец может хранить как отсутствие значения (NULL), так и значение нулевой длины (пустая строка).

SET

Если нужно хранить множество значений true/false, попробуйте объединить несколько столбцов в один естественный для MySQL столбец типа SET. В MySQL его внутренним представлением является упакованный битовый вектор. Он эффективно использует пространство, а в MySQL есть такие функции, как FIND_IN_SET() и FIELD(), которые облегчают запросы. Основным недостатком являются затраты на изменение определения столбца — эта процедура требует команды ALTER TABLE, которая для больших таблиц обходится очень дорого (но далее в моем блоге мы рассмотрим обходной путь). Кроме того, в общем случае при поиске в столбцах типа SET нельзя применять индексы.

Побитовые операции над целочисленными столбцами

Альтернативой типу SET является использование целого числа как упакованного набора битов. Например, вы можете поместить 8 бит в тип TINYINT и выполнять с ним побитовые операции. Для упрощения работы можно определить именованные константы для каждого бита в коде приложения.

Главным преимуществом такого подхода по сравнению с использованием типа SET является то, что вы можете изменить перечисление, которое представляет поле без команды ALTER TABLE. Недостаток же заключается в том, что запросы становятся труднее для написания и понимания (что означает, если бит 5 установлен?). Одни люди хорошо ориентируются в побитовых операциях, а другие — нет, поэтому использование описанного метода — дело вкуса.

Примером применения упакованных битов является список управления доступом (access control list, ACL), в котором хранятся разрешения. Каждый бит или элемент SET представляет значение, например CAN_READ, CAN_WRITE или CAN_DELETE. Если вы используете столбец типа SET, то позволяете MySQL сохранять в определении столбца соответствие между битами и значениями; если же применяется целочисленный столбец, то такое соответствие устанавливается в коде приложения. Приведем примеры запросов со столбцом типа SET:

mysql> CREATE TABLE acl (
    ->    perms SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL
    -> );
mysql> INSERT INTO acl(perms) VALUES ('CAN_READ,CAN_DELETE');
mysql> SELECT perms FROM acl WHERE FIND_IN_SET('AN_READ', perms);
+---------------------+
| perms               |
+---------------------+
| CAN_READ,CAN_DELETE |
+---------------------+

При использовании целочисленного столбца вы могли бы написать этот пример следующим образом:

mysql> SET @CAN_READ   := 1 << 0,
    ->     @CAN_WRITE  := 1 << 1,
    ->     @CAN_DELETE := 1 << 2;
mysql> CREATE TABLE acl (
    ->    perms TINYINT UNSIGNED NOT NULL DEFAULT 0
    -> );
mysql> INSERT INTO acl(perms) VALUES(@CAN_READ + @CAN_DELETE);
mysql> SELECT perms FROM acl WHERE perms & @CAN_READ;
+-------+
| perms |
+-------+
|     5 |
+-------+

Для определения столбцов мы использовали переменные, но вы можете применять в своем коде и константы.

Выбор идентификаторов

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

При выборе типа данных для столбца идентификатора следует учитывать не только тип хранения, но и то, как MySQL выполняет вычисления и сравнения с этим типом. Например, MySQL хранит типы ENUM и SET как целые числа, но преобразует их в строки при сравнении в строковом контексте.

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

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

Выбирайте самый маленький размер поля, способный вместить требуемый диапазон значений, а при необходимости оставляйте место для увеличения в дальнейшем. Например, если вы храните названия штатов США в столбце state_id, в нем не будет тысяч или миллионов значений, поэтому не используйте тип INT. Вполне достаточно типа TINYINT, который на 3 байта короче. Если вы используете это значение как внешний ключ в других таблицах, 3 байта могут стать существенными. Дарим несколько советов.

• Целочисленные типы. Обычно хорошо подходят для идентификаторов, поскольку работают быстро и допускают AUTO_INCREMENT.

• ENUM и SET. Типы ENUM и SET обычно не подходят для идентификаторов, хотя могут быть хороши для статических таблиц определений, содержащих значения состояния или типа. Столбцы ENUM и SET подходят для хранения такой информации, как состояние заказа, вид продукта или пол человека. Например, если поле ENUM используется для определения вида продукта, вам может потребоваться справочная таблица с первичным ключом по идентичному полю ENUM (можете включить в справочную таблицу столбцы с текстом описания, создать глоссарий или добавить комментарии в элементах раскрывающегося меню на сайте). В таком случае можно использовать тип ENUM для идентификатора, но в целом лучше этого избегать.

• Строковые типы. По возможности избегайте строковых типов для идентификаторов, поскольку они занимают много места и обычно обрабатываются медленнее целочисленных. Особенно осторожными следует быть при использовании строковых идентификаторов в таблицах MyISAM. Подсистема хранения MylSAM по умолчанию использует упакованные индексы для строк, что замедляет поиск. Во время тестов мы обнаружили, что в процессе работы с упакованными индексами MylSAM производительность падает чуть ли не в шесть раз. Следует также быть очень внимательными при работе со случайными строками, например сгенерированными функциями MD5(), SHA1() или UUID(). Случайные значения, созданные с их помощью, распределяются случайным образом в большом пространстве, что может замедлить работу команды INSERT и некоторых типов запросов SELECT.

  1. Они замедляют запросы INSERT, поскольку вставленное значение должно быть помещено в случайное место в индексах. Это приводит к разделению страниц, появлению возможности произвольного доступа к диску и фрагментации кластерного индекса в подсистемах хранения, которые его поддерживают. Более подробно мы рассмотрим это в следующей статье.
  2.  Случайные значения ухудшают работу кэша при всех типах запросов, поскольку нарушают принцип локальности ссылок, лежащий в основе его работы. Если весь набор данных одинаково «горячий», то нет смысла в хранении какой-то части информации кэшированной в памяти, а если рабочие данные не помещаются в памяти, то часто будут возникать сбросы из кэша.

Если вы решили сохранять значения UUID, то нужно удалить тире или, что еще лучше, преобразовать значения UUID в 16-байтные числа с помощью функции UNHEX() и сохранить их в столбце типа BINARY(16). Вы можете извлекать значения в шестнадцатеричном формате с применением функции НЕХ().

Характеристики значений, сгенерированных с помощью функции UUID(), отличаются от сгенерированных криптографическими хеш-функциями, такими как SHA1(): данные UUID распределены неравномерно и являются в некоторой степени последовательными. Однако они не настолько хороши, как монотонно увеличивающиеся целые числа.

Остерегайтесь автоматически сгенерированных схем

Мы описали наиболее важные особенности типов данных (одни больше влияют на производительность, другие — меньше), но еще не говорили о недостатках автоматически сгенерированных схем.

Плохо написанные программы миграции схем и программы, автоматически генерирующие схемы, могут вызывать серьезные проблемы с производительностью. Некоторые программы создают большие поля VARCHAR для всех типов данных или используют различные типы данных для столбцов, которые будут сравниваться при соединениях. Тщательно изучите схему, если она была сгенерирована автоматически.

Системы объектно-реляционного отображения (object-relational mapping, ORM) (и фреймворки, которые они используют) — еще один кошмар для желающих достичь высокой производительности. Некоторые из этих систем позволяют хранить любой тип данных в произвольном хранилище, а это обычно означает, что они не могут использовать сильные стороны конкретного хранилища. Иногда они записывают каждое свойство объекта в отдельную строку с использованием хронологического контроля версий, в результате чего возникает сразу несколько версий каждого свойства!

Возможно, такой подход привлекателен для разработчиков, поскольку позволяет им применять объектно-ориентированный стиль, не задумываясь о том, как данные хранятся. Однако приложения, скрывающие сложность от разработчиков, обычно плохо масштабируются. Мы советуем хорошо подумать, прежде чем променять производительность на удобство разработки. Всегда тестируйте на реалистичных наборах данных, чтобы проблемы с производительностью не были выявлены слишком поздно.

 

Специальные типы данных

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

Другой пример — IPv4-адреса. Для их хранения часто используют столбцы типа VARCHAR(15). Однако наделе IP-адрес является 32-битным беззнаковым целым числом, а не строкой. Запись с точками, разделяющими байты, предназначена только для того, чтобы человеку было удобнее его воспринимать. Лучше хранить IP-адреса как беззнаковые целые числа. У MySQL есть функции INET_ATON() и INET_NTOA() для преобразования между двумя представлениями.

Вас заинтересует / Intresting for you:

Модель развития базы данных My... 1418 просмотров Ирина Светлова Thu, 10 Jan 2019, 12:29:03

Транзакции в базе данных MySQL 21514 просмотров Ирина Светлова Mon, 07 Jan 2019, 05:18:23

Использование MySQL в качестве... 2262 просмотров Андрей Волков Tue, 01 Oct 2019, 05:41:51

Обзор версий MySQL - какой рел... 10621 просмотров Ирина Светлова Fri, 05 Feb 2021, 17:19:41