一本彻底搞懂MySQL索引优化EXPLAIN百科全书 (4)

file

4)ref_or_null:对普通二级索引进行等值查询,该索引列也可以为NULL值时。

EXPLAIN SELECT * FROM user where user.name = 'a' OR name IS NULL;

file

5)index_merge:MySQL使用索引合并的方式执行的。

EXPLAIN SELECT * FROM user WHERE user.name = 'a' OR user.id = 1;

file

6)range:使用索引获取范围区间的记录,通常出现在 in, between ,> ,<, >= 等操作中。

EXPLAIN SELECT * FROM user WHERE user.id > 1;

file

7)index:扫描全表索引,这通常比ALL快一些。(index是从索引中读取的,而 ALL 是从硬盘中读取)

group 表里的两个字段都有索引。

EXPLAIN SELECT * FROM group;

file

8)ALL:即全表扫描,MySQL 需要从头到尾去查找表中所需要的行。通常情况下这需要增加索引来进行优化了。

EXPLAIN SELECT * FROM user;

file

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;

file

通过结果中的 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';

file

所以,以后再看到 key_len 字段的值,不要在懵逼咯,固定套路~

8、ref 列

ref 列显示了在 key 列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名(例:user.id)。

9、rows 列

rows 列是查询优化器估计要读取并检测的行数,注意这个不是结果集里的行数。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zyxwsz.html