mysql - 创建具有特定键长度的索引的启发式方法？

Question

我刚刚了解到，某些数据库可以指定索引长度，如果我理解正确的话，它只会索引字段中的前 n 个字符。

什么时候应该指定索引前缀长度？多长合适？

我在 Rails 中使用 PostgreSQL，但此信息应该足够通用，可以应用于其他 DBMS。

根据http://apidock.com/rails/ActiveRecord/ConnectionAdapters/SchemaStatements/add_index

add_index(:accounts, :name, name: 'by_name', length: 10)

生成:

CREATE INDEX by_name ON accounts(name(10))

不幸的是，该页面没有提及任何有关索引长度的信息。

Answer 1

MySQL page on CREATE INDEX syntax提供了更多信息。

Indexes can be created that use only the leading part of column values.

使用与上面类似的示例:

If names in the column usually differ in the first 10 characters, this index should not be much slower than an index created from the entire name column.

措辞有点令人困惑，但我认为它是说，如果您指定索引前缀长度为 10，并且前 10 个字符通常足以唯一，那么这个较短的索引可能比全长索引慢指数，但如果是的话，幅度也不会太大。

我不认为这就是他们所说的“这个索引不应该慢很多”的意思，但这就是它所说的。我想它会更快，因为它运行的字符更少，但也快不了多少。

一些更有用的信息:

Also, using column prefixes for indexes can make the index file much smaller, which could save a lot of disk space and might also speed up INSERT operations.

因此您可能会拥有更小的索引文件和更快的插入速度。

A UNIQUE index creates a constraint such that all values in the index must be distinct. [...] If you specify a prefix value for a column in a UNIQUE index, the column values must be unique within the prefix.

如果可以保证前缀中的值是唯一的，则应该只为唯一索引指定索引长度。

至于什么是好的长度......这很难回答，也许其他人也可以插话。我认为足够短的长度可以获得更小的索引文件大小和更快的操作的好处，但足够长前缀中的值很有可能是唯一的(或者如果您还使用唯一索引，则保证是唯一的)。