MySQL для двоичных файлов для хеш-идентификаторов

Ответ 1

Да. Часто хеш-дайджест хранится как ASCII-представление шестнадцатеричных цифр, например MD5 слова "hash":

0800fc577294c34e0b28ad2839435945

Это 32-символьная строка ASCII.

Но MD5 действительно производит 128-битное двоичное хэш-значение. Для этого необходимо сохранить только 16 байтов в виде двоичных значений вместо шестнадцатеричных цифр. Таким образом, вы можете получить некоторую экономию пространства, используя двоичные строки.

CREATE TABLE test.foobar (
  id BINARY(16) NOT NULL PRIMARY KEY
);

INSERT INTO test.foobar (id) VALUES (UNHEX(MD5('hash')));

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

Я не знаю, по какой причине тип данных BINARY был бы быстрее, чем CHAR.

Быть вдвое большим может быть преимуществом для производительности, если вы эффективно используете кеш-буферы. То есть, определенное количество кэш-памяти может хранить в два раза больше строк данных BINARY, если строка равна половине размера CHAR, необходимой для хранения того же значения в шестнадцатеричном формате. Аналогично, кэш-память для индекса в этом столбце может хранить в два раза больше.

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

Что касается разницы между хэш-строкой, хранящейся в BINARY в сравнении с BIGINT, я бы выбрал BIGINT. Эффективность кеша будет еще больше, а также на 64-битных процессорах целочисленная арифметика и сравнения должны быть очень быстрыми.

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

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

mysql> SELECT BENCHMARK(100000000, 'foo' = 'FOO');
1 row in set (5.13 sec)

mysql> SELECT BENCHMARK(100000000, 'foo' = BINARY 'FOO');
1 row in set (4.23 sec)

Таким образом, сравнение двоичных строк на 17,5% быстрее, чем сравнение строк без учета регистра. Но обратите внимание, что после оценки этого выражения в 100 миллионов раз общая разница составляет менее 1 секунды. Хотя мы можем измерить относительную разницу в скорости, абсолютная разница в скорости действительно несущественна.

Итак, я повторю:

• Измерьте, не догадывайтесь или не предполагайте. Ваши образованные догадки будут ошибочными во много раз. Измеряйте до и после каждого изменения, которое вы делаете, чтобы вы знали, насколько это помогло.
• Поделитесь своим временем и вниманием, где вы получите наибольший удар по доллару.
• Не потейте мелкие вещи. Конечно, крошечная разница складывается с достаточным количеством итераций, но, учитывая эти итерации, улучшение производительности с большей абсолютной выгодой по-прежнему предпочтительнее.

Ответ 2

Из руководство:

The BINARY and VARBINARY types are similar to CHAR and VARCHAR, except
that they contain binary strings rather than non-binary strings. That is,
they contain byte strings rather than character strings. This means that
they have no character set, and sorting and comparison are based on the
numeric values of the bytes in the values. 

Так как CHAR (32) BINARY вызывает создание BINARY (32) столбца под капотом, преимущество в том, что для этого потребуется меньше времени для сортировки по этому столбцу и, вероятно, меньше времени для поиска соответствующих строк, если колонка индексируется.