mysql索引长度计算

索引长度限制

MySQL Innodb 对于索引长度的限制为 767 字节，并且UTF8mb4字符集是4字节字符集，则 767字节 / 4字节每字符 = 191字符(默认索引最大长度)，所以在varchar(255)或char(255) 类型字段上创建索引会失败，提示最大索引长度为767字节。

长度计算

定长类型:int占四个字节、date占三个字节、char(n)占n个字符。

对于varchar(n)，则有n个字符+两个字节。

不同的字符集，一个字符占用的字节数不同

latin1编码:一个字符占用一个字节。

gbk编码的：一个字符占用两个字节。

utf8编码的:一个字符占用三个字节。

针对索引长度 char()、varchar()索引长度的计算公式：

(Character Set：utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)

求证

1.查看表结构

2.增加索引

ALTER TABLE `mail_receive_log` ADD INDEX `idx_channel_uid` (channel_uid);

3.查看索引长度

64*4+1+2=259

案例

1.线上发现一个慢sql

 explain  select * from mail_receive_log r
where r.to='liqinad@qq.com' and r.created_at>'2022-04-01'

2.查看执行计划

全表扫描没命中索引

 3.查看现在存在索引 是否有合适索引

2550*3+1+2=7 653 超过索引长度了

 4.没有合适索引准备在to建立索引查看表结构

5.分析准备线上数据

奇怪的数据是json 怀疑是批量发送存储的json数组。

6.定位到代码调用发现是查询的一个config文件传递的email进去查询

 

 7.查看config表的email最大长度

 8.建立索引

42*3+2+1=129没有超出限制 大于42位条件查询走全表扫描

ALTER TABLE `mail_receive_log` ADD INDEX `ix_to_created_at` (`to`(42),created_at);

9.优化后执行计划

length=133 是因为 129+时间长度的3个字节=133