MySQL:对太长而无法索引的列进行高效查询

Question

在我的 MySQL 数据库中，我有一个字符串列(例如，一个 SHA 散列)，它变得太长而无法放置索引。如何针对此列运行高效查询？

• 我可以在列的前 N 个字符上放置一个索引，但是使用这个“部分”索引的查询是什么样的？
• 我可以用 N 个字符创建第二列，并在上面放置一个完整索引，作为“部分”索引的替代项。然后我会查询，获取一条或多条记录，并在内存中进行最后一步的过滤。
• 我可以使用 full text search functions ，但我需要使用 MyISAM。 MyISAM 不支持 ACIDity，因此不用谢。

在 MySQL 中实现此目的的正确方法是什么？

问题不在于减小我的列的大小或重新配置我的数据库(如果它配置的 key 长度太短)。它是关于轻松地利用部分索引或类似的东西，最好不要给应用程序带来负担或弹出额外的列。

在我的特殊情况下，我在 UTF8 表的两列上寻找复合键:

create table fingerprinted_item (
  type varchar (512) not null,
  fingerprint varchar (512) not null,
  primary key (fingerprint, type)
);

-- Then there may be a child table.

MySQL 说:

[42000][1071] Specified key was too long; max key length is 767 bytes

在不同的服务器上，最大 key 长度为 1000 字节。

Answer 1

真正的问题可能是对指纹列使用 VARCHAR。当使用 utf8 字符编码时，MySQL 强制执行“最坏情况”并计算每个字符 3 个字节。

要么将其更改为 1 字节编码(比如 Latin1)，要么改用 VARBINARY 类型:

create table fingerprinted_entry 
( type varchar (128) not null, 
  fingerprint varbinary (512) not null,
  PRIMARY KEY(type, fingerprint)) ENGINE InnoDB; -- no error here

---

如果您必须超出每个前缀 767 字节的限制，则您必须明确在创建索引时声明:

create table fingerprinted_entry 
( type varchar (128) not null, 
  fingerprint varbinary (2048) not null,              -- 2048 bytes
  PRIMARY KEY(type, fingerprint(767))) ENGINE InnoDB; -- only the first 767 bytes of fingerprint are stored in the index