MySQL 的前缀索引和索引选择性 #

有时候需要索引很长的字符列，这会让索引变的大且慢。通常选择索引列开始的部分字符，这样大大减少了索引空间，但这样又会导致索引选择性降低，影响查询效率，所以需要一个权衡。

索引选择性

是指不重复的索引值(也称基数，cardinality)和数据表的记录总数的比值。索引选择性越高则查询效率越高。唯一索引的索引选择性是1，是最高的。

如何确定索引选择性 #

先计算完整列的索引选择性。

SELECT COUNT(DISTINCT <col_name>) / COUNT(*) FROM <table_name>

然后取 n，查询取长度为 n 的索引选择性。

SELECT COUNT(DISTINCT left(<col_name>, n)) / COUNT(*) FROM <table_name>

当检查到索引选择性和完整列的索引选择性很接近时就可以使用这个长度做前缀索引了。

创建前缀索引 #

n 是前缀长度

ALTER TABLE <table_name> ADD INDEX <index name> (<col_name>(n))

前缀索引的缺点 #

无法支持 ORDER BY 和 GROUP BY。也无法做覆盖扫描。