创建前缀索引时，如何确认“最佳长度”

最新推荐文章于 2024-06-19 18:01:08 发布

原创最新推荐文章于 2024-06-19 18:01:08 发布 · 1.3k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #mysql优化 #索引

MySQL学习笔记同时被 2 个专栏收录

2 篇文章

订阅专栏

数据库

1 篇文章

订阅专栏

在面临创建长字段索引时，可以采用前缀索引来优化。通过计算不同长度前缀的不重复索引值与总记录数的比值，找到比值最大时的前缀长度，例如比值不再明显增加时选取11作为最佳长度。创建前缀索引后，要验证其是否被有效利用。

当某个字段想要对其创建索引，但是它的长度可能又特别长时，可以考虑前缀索引。

1. 简单信息查询

查询前缀索引的最小/大长度，以及确定生产上数据量大不大，大概有个心理准备。（比如当前字段的最大长度为110，最短为108，创建100+长度索引有点长了）

--查询字段最长/最短长度，对欲建立索引的字段长度有个预知
SELECT min(length(code)),max(length(code)) FROM authorization_code;

2. 确定前缀索引的“最佳长度”

前缀索引的最好效果是：不重复的索引值（也称为基数cardinality）和数据表的记录总数的比值，越大越好，比如唯一索引比值为1

查询比值，确认前缀索引长度

select

count(distinct left(code,1))/count(*) as sel1,

count(distinct left(code,2))/count(*) as sel2,

count(distinct left(code,3))/count(*) as sel3,

count(distinct left(code,4))/count(*) as sel4,

count(distinct left(code,5))/count(*) as sel5,

count(distinct left(code,6))/count(*) as sel6,

count(distinct left(code,7))/count(*) as sel7,

count(distinct left(code,8))/count(*) as sel8,

count(distinct left(code,9))/count(*) as sel9,

count(distinct left(code,10))/count(*) as sel10,

count(distinct left(code,11))/count(*) as sel11,

count(distinct left(code,12))/count(*) as sel12,

count(distinct left(code,13))/count(*) as sel13,

count(distinct left(code,14))/count(*) as sel14,

count(distinct left(code,15))/count(*) as sel15,

count(distinct left(code,16))/count(*) as sel16,

count(distinct left(code,17))/count(*) as sel17,

count(distinct left(code,18))/count(*) as sel18,

count(distinct left(code,19))/count(*) as sel19,

count(distinct left(code,20))/count(*) as sel20

from authorization_code;

当看到长度为11时，比值不再明显增加，不极致追求1，选择长度11为最佳长度。

3. 创建前缀索引

alter table authorization_code add index idx_authorization_code(code(11));

4. 验证索引是否有被使用

可创建索引前先执行一下，比较效果

--查看执行计划
explain select * from authorization_code where code ='ATBGAiEA6EiRXwcHrdaD9Jc3hQdRyQ+mGE8OuU+sVzpqSoR0HFECIQC3TkAehOms5hr+pVqtQhTaMLypbCMNmw3y7FW6zxMqT16X/ysAAAAV';

创建前缀索引前：

创建前缀索引后：