MySQL:对太长而无法建立索引的列进行有效查询 [英] MySQL: efficient query on a column too long to be indexed

问题描述

在我的MySQL数据库中，我有一个字符串列(例如 ，一个SHA哈希)，它的长度过长而无法放置索引.如何针对此列运行有效的查询?

In my MySQL database, I have a string column (for example, an SHA hash) which grows too long to put an index on. How can I run efficient queries against this column?

• 我可以在该列的前N个字符上放置一个索引，但是使用此部分"索引的查询是什么样的?
• 我可以用N个字符创建第二列，并在其上放置一个完整索引，以作为部分"索引的替代.然后，我将查询，获取一个或多个记录，并在内存中进行过滤的最后一步.
• 我可以使用全文搜索功能，但是然后我需要使用MyISAM. MyISAM不支持ACIDity，因此不，谢谢.

• I can put an index on the first N characters of the column, but then what does the query that makes use of this "partial" index look like?
• I could create a second column with N characters and put a full index on that, as a surrogate for a "partial" index. I would then query, get one or more records, and do the final step of the filtering in memory.
• I can use full text search functions, but then I need to use MyISAM. MyISAM does not support ACIDity, therefore no thank you.

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

What is a proper way to achieve this in MySQL?

问题不在于减小列的大小或重新配置数据库(如果它配置的密钥长度太短).这是不费吹灰之力地利用部分索引或类似索引的事情，最好不要给应用程序造成负担或弹出其他列.

The question is not about reducing the size of my column or reconfiguring my database if it's configured with a too short key length. It's about leveraging a partial index or something of the kind painlessly, preferably without putting a burden on the application or popping up additional columns.

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

In my particular case, I am looking for a composite key on two columns in a UTF8 table:

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

在另一台服务器上，最大密钥长度为1000个字节.

On a different server, the max key length is 1000 bytes.

推荐答案

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

The real issue is probably to use VARCHAR for the fingerprint column. When using the utf8 character encoding, MySQL enforces the "worst case scenario" and counts 3 bytes per character.

将其更改为1字节编码(例如Latin1)，或者改用VARBINARY类型:

Either change that to 1-byte encoding (say Latin1), or use the VARBINARY type instead:

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

这篇关于MySQL:对太长而无法建立索引的列进行有效查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！