- 当需要在很长的字符列（如BLOB、TEXT或很长的VARCHAR类型的列）上创建索引时，这会造成索引特别大且慢。而前缀索引仅仅是选择一个字段的部分字符作为索引，可以节约索引空间，另一方面则可以提高索引效率，当然这种方式也会降低索引的选择性
- 当某个字段内容的前几位区分度很高的时候，这个时候采用前缀索引，可以在查询性能和空间存储方面达到一个平衡
语法
ALTER TABLE tbl_name
ADD KEY(col_name(prefix_length));
|
参数 |
说明 |
|
tbl_name |
数据表名 |
|
col_name |
数据列名 |
|
prefix_length |
字段的前缀长度 |
示例
ALTER TABLE user ADD KEY(adress(5));
注
- 前缀索引不能在 ORDER BY 或 GROUP BY 中使用，也不能用作覆盖索引
索引选择性（Index Selectivity）
- 关于索引的选择性（Index Selectivity），它是指不重复的索引值（也称为基数 cardinality)和数据表的记录总数的比值，取值范围在 [0,1] 之间，即不重复率。索引的选择性越高则查询效率越高，因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行
- 索引的选择性与前缀长度（prefix_length）有关，创建前缀索引的关键在于选择足够长的前缀以保证较高的索引选择性。索引选择性越高查询效率就越高，因为选择性高的索引可以在查找时过滤掉更多的数据行
- 为确定prefix_length的长度，我们需根据前缀长度（prefix_length） 的长度计算与整表选择性，然后与表的原始数据选择性进行对比，当两个值相等时就可以确定前缀长度（prefix_length），即
- 首先计算某字段全列的选择性，假设选择性为 95.5%
示例
SELECT COUNT(DISTINCT adress) / COUNT(*)
FROM user;
- 指定一个初始的前缀长度，评估前缀长度，计算全表的选择性。以初始前缀长度 5 为例子，使用 LEFT() 函数返回具有指定长度的字符串的左边部分
示例
SELECT COUNT( DISTINCT LEFT(adress, 5) ) / COUNT(*)
FROM user;
- 以前缀长度 10 为例子，如果此时选择性为 23.7%，则继续增加前缀长度
示例
SELECT COUNT( DISTINCT LEFT(adress, 10) ) / COUNT(*)
FROM user;
- 以前缀长度 11 为例子，如果此时选择性为 93.7%，则继续增加前缀长度
示例
SELECT COUNT( DISTINCT LEFT(adress, 11) ) / COUNT(*)
FROM user;
- 以前缀长度 12 为例子，如果此时选择性为 95.5%，则可以确定前缀长度（prefix_length）为 12
示例
SELECT COUNT( DISTINCT LEFT(adress, 12) ) / COUNT(*)
FROM user;
注
- 当不重复率远远小于原表数据的选择性，那么创建前缀索引不会有太大的效果