MySQL使用前缀索引时计算索引选择性

声明

文章内容出自《高性能Mysql第三版》

定义

索引选择性：不重复的索引值（也叫基数,cardinality）和数据总量(#T)的比值,范围从 1/#T 到 1 之间。选择性越高效率就越高，比如唯一索引选择性为1，所以是最好的索引选择性

计算索引选择性的方法

计算完整列的选择性，并使前缀的选择性接近与完整列的选择性。

实践

表结构

create table test (
	id int(11) primary key auto_increment,
	test_string varchar(255) not null default '' comment '测试字段，假设数据都比较长'
)

计算完整的选择性

select count(distinct test_string)/count(*) from test

假设得到数据为:0.0311

计算针对不同前缀长度计算

select 
	count(distinct left(test_string, 3))/count(*) as sel3,
 	count(distinct left(test_string, 4))/count(*) as sel4,
 	count(distinct left(test_string, 5))/count(*) as sel5,
 	count(distinct left(test_string, 6))/count(*) as sel6,
 	count(distinct left(test_string, 7))/count(*) as sel7
 from test

假设数据结果

| sel3 | sel4 | sel5 | sel6 | sel7 | | ---- | ---- | ---- | ---- | ---- | ---- | | 0.0222 | 0.0223 | 0.0302 | 0.0314 | 0.0315 |

由表可看出，当前缀到7的时候，再增加前缀长度选择提升的幅度已经很小了。

添加前缀索引

alter table test add key (test_string(7));

前缀索引的缺点

MYSQL无法使用前缀索引ORDER BY 和 GROUP BY ,也无法使用前缀索引做覆盖扫描