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

2016-11-19 13:52:21   数据库

  mysql  

声明

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

定义

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

计算索引选择性的方法

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

实践

表结构

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

计算完整的选择性

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

假设得到数据为:0.0311

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

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

假设数据结果

sel3 sel4 sel5 sel6 sel7
0.0222 0.0223 0.0302 0.0314 0.0315

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

添加前缀索引

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

前缀索引的缺点

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