优化体系 | 我是怎么计算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数据库参数优化，都总结在这里了

（编辑：河北网）

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