MySQL Explain中key_len的计算

通常在优化SQL查询的时候，我们都会使用explain分析SQL执行计划，通常来说当用到组合索引的时候我们如何判断索引完全用上呢？当然高手看看表结构及SQL语句就知道到底用到了几个字段，对于不熟悉的同学呢？我们还是可以看看key_len的长度，当然这个计算还是有点复杂的，下面我们来谈谈explain key_len如何计算的。

a.表结构如下：

CREATE TABLE keylen_cal (

  id int(10) NOT NULL,

  tel int(10) DEFAULT NULL,

  first_name varchar(10) NOT NULL,

  last_name varchar(10) DEFAULT NULL,

  father char(10) NOT NULL,

  mother char(10) DEFAULT NULL,  PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

b.插入数据

insert into keylen_cal values (1,111,'A','AA','AAA','AAAA');

insert into keylen_cal values (2,222,'B','BB','BBB','BBBB');

insert into keylen_cal values (3,333,'C','CC','CCC','CCCC');

insert into keylen_cal values (4,444,'D','DD','DDD','DDDD');

c创建索引

alter table keylen_cal add index idx_id(id);

alter table keylen_cal add index idx_tel(tel);

alter table keylen_cal add index idx_fn(first_name);

alter table keylen_cal add index idx_ln(last_name);

alter table keylen_cal add index idx_father(father);

alter table keylen_cal add index idx_mother(mother);

d.测试每个key_len的长度

explain select * from keylen_cal where id=3;

##id int(10) NOT NULL,

key_len=4

explain select * from keylen_cal where tel=33;

##tel int(10) DEFAULT NULL,

key_len=5=4+1

alter table keylen_cal modify id(100) NOT NULL;

explain select * from keylen_cal where id=3;

key_len=4

explain select * from keylen_cal where first_name='C'

##first_name varchar(10) NOT NULL,

key_len=32=10*3+2 (utf8占用三个字节)

explain select * from keylen_cal where last_name='CC'

##last_name varchar(10) DEFAULT NULL,

key_len=33=10*3+2+1

explain select * from keylen_cal where father='CCC'

##father char(10) NOT NULL

key_len=30=3*10

explain select * from keylen_cal where mother='CCCC'

##mother char(10) DEFAULT NULL

key_len=31=3*10+1

通过上面的一系列实验，可以总结出来如下规律

1. 通常情况下， key_len=字段字符数*字符集每个字符所占字节数

2. default null ，会使 key_len+1

3. 变长的字段如varchar，会使key_len+2

4. int ,key_len=4

5. bigint, key_len=8