优化体系 | 我是怎么计算MySQL数据库索引长度的？

概述

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

那么怎么去计算mysql数据库索引长度呢?

实验测试

先看网上一道题目，针对表t，包含了三个字段a、b、c，假设其默认值都非空，现创建组合索引index(a,b,c) 分析select * from t where a=1 and c=1 和select * from t where a=1 and b=1区别?

1、创建表

create table t(a int(5) not null,b int(5) not null,c int(2) not null); 
create index idx_all on t(a,b,c); 

2、分别执行这两条语句

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

3、思路

这里可以发现，前面两个的区别主要是在于key_len上，我的理解是：

将组合索引想成书的一级目录、二级目录、三级目录，如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的呢?

4、key_len的计算.

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(变长列)

所以从上面可以得出

where a=1 and c=1而言，key_len=4

where a=1 and b=1而言，key_len=4+4=8

5、创建新的测试表t2

创建一个t2表，数据结构如下

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

6、计算key_len

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

分析key_len=4+5*1+2=11，因为字段都是not null，int类型4个字节，varchar(5) 占用5个字符+2个字节，latin1编码的表一个字符占1个字节，故varchar(5) 占用7个字节。

总结

因为MySQL具有查询优化器，所以对where a=1 and c=1类型的查询，字段顺序没有任何影响，查询优化器会自动优化。where c=1 and a=1会被优化成where a=1 and c=1，但是建议还是使用where a=1 and c=1吧，便于理解以及查询缓冲。

【编辑推荐】

1. 详解MySQL数据库JSON类型：用法、意义、内置函数
2. 新特性解读 | MySQL 5.7升级到MySQL 8.0的注意事项
3. 推荐一款MySQL高可用复制管理工具——Orchestrator介绍
4. 详解MySQL数据库的Index Condition Pushdown(ICP)特性
5. 超详细的MySQL数据库参数优化，都总结在这里了

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!