4)ref_or_null:对普通二级索引进行等值查询,该索引列也可以为NULL值时。
EXPLAIN SELECT * FROM user where user.name = 'a' OR name IS NULL;
5)index_merge:MySQL使用索引合并的方式执行的。
EXPLAIN SELECT * FROM user WHERE user.name = 'a' OR user.id = 1;
6)range:使用索引获取范围区间的记录,通常出现在 in, between ,> ,<, >= 等操作中。
EXPLAIN SELECT * FROM user WHERE user.id > 1;
7)index:扫描全表索引,这通常比ALL快一些。(index是从索引中读取的,而 ALL 是从硬盘中读取)
group 表里的两个字段都有索引。
EXPLAIN SELECT * FROM group;
8)ALL:即全表扫描,MySQL 需要从头到尾去查找表中所需要的行。通常情况下这需要增加索引来进行优化了。
EXPLAIN SELECT * FROM user;
5、possible_keys 列
possible_keys 列表示查询可能使用哪些索引来查找。
EXPLAIN 执行计划结果可能出现 possible_keys 列,而 key 显示 NULL 的情况,这种情况是因为表中数据不多,MySQL 会认为索引对此查询帮助不大,选择了全表查询。
如果 possible_keys 列为 NULL,则没有相关的索引。在这种情况下,可以通过检查 WHERE 子句去分析下,看看是否可以创造一个适当的索引来提高查询性能,然后用 EXPLAIN 查看效果。
另外注意:不是这一列的值越多越好,使用索引过多,查询优化器计算时查询成本高,所以如果可能的话,尽量删除那些不用的索引。
6、key 列
key 列表示实际采用哪个索引来优化对该表的访问。
如果没有使用索引,则该列是 NULL。如果想强制 MySQL使用或忽视 possible_keys 列中的索引,在查询中使用 force index、ignore index。
7、key_len 列
key_len 列表示当查询优化器决定使用某一个索引查询时,该索引记录的最大长度。
key_len 列计算规则如下:
字符串
char(n):n字节长度
varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n + 2
注意:该索引列可以存储NULL值,则key_len比不可以存储NULL值时多1个字节。
比如:varchar(50),则实际占用的key_len长度是 3 * 50 + 2 = 152,如果该列允许存储NULL,则key_len长度是153。
数值类型
tinyint:1字节
smallint:2字节
int:4字节
bigint:8字节
时间类型
date:3字节
timestamp:4字节
datetime:8字节
索引最大长度是768字节,当字符串过长时,MySQL 会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引。
举例1:
user_group表中的联合索引 idx_user_group_id 由 user_id 和 group_id 两个int 列组成,并且每个 int 是 4 字节。
EXPLAIN SELECT * FROM user_group WHERE user_id = 2;
通过结果中的 key_len=4可推断出查询使用了第一个列:user_id 列来执行索引查找。
举例2:
再看 user 表 name 字段是 varchar(45) 变长字符串类型,key_len为138 等于 45 * 3 + 2 (变长字节) + 1字节(允许存储NULL值)
EXPLAIN SELECT * FROM user WHERE name = 'a';
所以,以后再看到 key_len 字段的值,不要在懵逼咯,固定套路~
8、ref 列
ref 列显示了在 key 列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名(例:user.id)。
9、rows 列
rows 列是查询优化器估计要读取并检测的行数,注意这个不是结果集里的行数。