mysql的explain怎么看_[MySQL] 查看explain中的key_len判断究竟使用了哪个索引

在一张表里有多个索引 , 我们where字段里条件有多个索引的值 , 那么究竟使用的哪个呢?

我们可以使用explain来查看 , 其中的key_len字段可以看得出来

比如下面这条sql

explain select * from ent_calendar_diary where email='xxxx' and diary_id=1784;

+----+-------------+--------------------+------------+-------+-------------------------+---------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |

+----+-------------+--------------------+------------+-------+-------------------------+---------+---------+-------+------+----------+-------+

| 1 | simple | ent_calendar_diary | null | const | primary,idx_email_stime | primary | 4 | const | 1 | 100.00 | null |

+----+-------------+--------------------+------------+-------+-------------------------+---------+---------+-------+------+----------+-------+

possible_keys里面有两个索引字段 , 但是看key_len  是4个字节

列类型key_len备注

id int

key_len = 4+1 = 5

允许null，加1-byte

id int not null

key_len = 4

不允许null

user char(30) utf8

key_len = 30*3+1

允许null

user varchar(30) not null utf8

key_len = 30*3+2

动态列类型，加2-bytes

user varchar(30) utf8

key_len = 30*3+2+1

动态列类型，加2-bytes；允许null，再加1-byte

detail text(10) utf8

key_len = 30*3+2+1

text列截取部分，被视为动态列类型，加2-bytes；且允许null

备注，key_len 只指示了where中用于条件过滤时被选中的索引列，是不包含 order by/group by

int类型并且not null 是4个字节 , 因此上面的sql是使用的主键索引

explain select * from ent_calendar_diary where email='xxxx';

+----+-------------+--------------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra |

+----+-------------+--------------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+

| 1 | simple | ent_calendar_diary | null | ref | idx_email_stime | idx_email_stime | 767 | const | 111 | 100.00 | null |

+----+-------------+--------------------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+

这个是767个字节 , varchar(255)  not null   255 * 3 +2正好符合 , 因此是使用的email那个普通索引

create table `ent_calendar_diary` (

`diary_id` int(10) unsigned not null auto_increment,

`email` varchar(255) not null,

`title` varchar(100) not null,

`summary` varchar(500) not null default '',

`stime` bigint(11) not null default '0',

`ctime` int(10) unsigned not null default '0',

primary key (`diary_id`),

key `idx_email_stime` (`email`,`stime`)

) engine=innodb auto_increment=1809 default charset=utf8

如您对本文有疑问或者有任何想说的，请点击进行留言回复，万千网友为您解惑！