MySQL explain中key_len的计算
key_len表示索引使用的字节数,根据这个值可以判断索引的使用情况,特别是在组合索引的时候,判断该索引有多少部分被使用到非常重要。
在计算key_len时,下面是一些需要考虑的点:
-
索引字段的附加信息:可以分为变长和定长数据类型讨论,当索引字段为定长数据类型时,如char,int,datetime,需要有是否为空的标记,这个标记占用1个字节(对于not null的字段来说,则不需要这1字节);对于变长数据类型,比如varchar,除了是否为空的标记外,还需要有长度信息,需要占用两个字节。
-
对于,char、varchar、blob、text等字符集来说,key len的长度还和字符集有关,latin1一个字符占用1个字节,gbk一个字符占用2个字节,utf8一个字符占用3个字节。
综上,下面来看一些例子:
列类型 | KEY_LEN | 备注 |
---|---|---|
id int | key_len = 4+1 | int为4bytes,允许为NULL,加1byte |
id bigint not null | key_len=8 | bigint为8bytes |
user char(30) utf8 | key_len=30*3+1 | utf8每个字符为3bytes,允许为NULL,加1byte |
user varchar(30) not null utf8 | key_len=30*3+2 | utf8每个字符为3bytes,变长数据类型,加2bytes |
user varchar(30) utf8 | key_len=30*3+2+1 | utf8每个字符为3bytes,允许为NULL,加1byte,变长数据类型,加2bytes |
detail text(10) utf8 | key_len=30*3+2+1 | TEXT截取部分,被视为动态列类型。 |
字符串
char(n):n字节长度
varchar(n):n字节存储字符串长度,如果是utf-8, 则长度是3n+2
数值类型
tinyint:1字节
smallint:2字节
int:4字节
bigint:8字节
时间类型
date:3字节
timestamp:4字节
datetime:8字节
如果字段允许为NULL,需要1字节记录是否为NULL
索引最大长度是768字节,当字符串过长时,MySQL会做一个类似做前缀索引的处理,将前半部分的字符串提取出来做索引。
备注:key_len只指示了where中用于条件过滤时被选中的索引列,是不包含order by/group by这一部分被选中的索引列的。
例如,有个联合索引idx(c1,c2,c3),3列均是int not null,那么下面的SQL执行计划中,key_len的值是8而不是12:
select ... from tb where c1=? and c2=? order by c1;
如果有来生,要做一片树叶。
春天恋上枝,炎夏恋上水。
深秋恋上土,东来化作泥。
润物细无声,生生世世恋红尘。