mysql 索引太长_MySQL：对太长而无法建立索引的列的有效查询

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?

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.

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.

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 says:

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

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

解决方案

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.

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

If you have to go beyond the 767 byte limit per prefix, you will have to explicitly state that when you create the index:

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