mysql - 最大化超长数字的数据库性能

Question

我有数字化的图像哈希，哈希是 2k 整数长。将其存储在数据库和搜索中的最佳解决方案是什么？行数将至少为 300 万。性能建议？ 我正在考虑创建 utf8_bin 归类列并将所有数字转换为区分大小写的哈希并在该列上添加索引，或者是否有其他更好的解决方案？

附言hash 可以修改，1k 整数会不太准确，所以我更喜欢存储 2k 左右。

Answer 1

存储 long 最紧凑的方法是使用 VARBINARY 将其存储为二进制字节数据类型，而不是具有 utf8_bin 排序规则的字符串。计算图像的数字哈希，转换为十六进制数字字符串，然后使用 UNHEX()转换为二进制字节。二进制字节存储在等效的十六进制数字字符串的一半空间中。例如，像 'FFFF' 这样的字符串需要四个字符，但 UNHEX('FFFF') 以两个二进制字节存储。

单独存储更紧凑只是对性能的适度改进。
更好的性能优势是使用索引。但是 InnoDB 对索引长度有限制。默认情况下，限制为 767 字节。

如果您设置innodb_large_prefix=1，您可以将 InnoDB 增加到 3072 字节(您必须使用 DYNAMIC 或 COMPRESSED 行格式，这意味着您必须使用 file-per-table)。这应该足以索引您的哈希的全长。

---

更新:我了解到 innodb_large_prefix 是 deprecated在 MySQL 5.7.7 和 MariaDB 10.2 中，该选项将在未来的版本中删除。但别担心，它已被弃用，因为大索引支持将成为默认行为。不再需要该选项，因为它始终有效。

CREATE TABLE MyTable (
  dhash VARBINARY(3072) NOT NULL,
  UNIQUE KEY (dhash)
);