如何精确计算mysql的索引长度

一、前言

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

二、环境准备

创建表

CREATE TABLE `t` (
  `a` int(5) NOT NULL,
  `b` int(5) NOT NULL,
  `c` int(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

创建联合索引

create index idx_all on t(a,b,c);

三、试验

　　1、执行下面两条语句

explain select * from t where a=1 and c=1;
explain select * from t where a=1 and b=1;

 

语句1的执行计划为：

 

 语句2的执行计划为：

　　2、思路

查看表的索引

show index from t;

 

将组合索引想成书的一级目录、二级目录、三级目录，如index(a,b,c)，相当于a是一级目录，b是一级目录下的二级目录，c是二级目录下的三级目录。要使用某一目录，必须先使用其上级目录，除了一级目录除外。

所以

where a=1 and c=1只使用了一级目录，c在三级目录，没有使用二级目录，那么三级目录就没法使用

where a=1 and b=1只使用了一级目录、二级目录。

于是第二条查询的key_len更大。

但是，具体key_len怎么计算的，上面怎样计算出是4和8的呢?

　　3、索引长度的计算规则

1.所有的索引字段，如果没有设置not null，则需要加一个字节。

2.定长字段，int占四个字节、date占三个字节、char(n)占n个字符。

3.对于变成字段varchar(n)，则有n个字符+两个字节。

4.不同的字符集，一个字符占用的字节数不同。latin1编码的，一个字符占用一个字节，gbk编码的，一个字符占用两个字节，utf8编码的，一个字符占用三个字节。

5.索引长度 char()、varchar()索引长度的计算公式：

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

　　4、再看sql语句

SQL1

select * from t where a=1 and c=1;

执行计划：

key_len = 4 （a是int类型 4字节） = 4

所以得出结论，是命中了索引列a

SQL2

explain select * from t where a=1 and b=1;

执行计划：

key_len =4 （a是int类型 4字节） + 4（b是int类型 4字节）  = 8

所以得出结论，是命中了索引列a和索引列b

四、再实验

　　1、创建表

create table t2(id int(5) not null,name varchar(5) not null) engine=innodb default charset=latin1;

　　2、创建索引

create index idx_2 on t2(id,name);

　　3、计算索引字段

sql为：

explain select * from t2 where name="001" and id=1;

 执行计划为：

 key_len =4 （id是int类型 4字节） + 5（name是varcharr类型 5字节）*1（字符集是latin1）+变列长2  = 11