本文总结一下学习的知识点,主要记一下Extra字段的用途,方便以后查找。
先进行一下数据准备:
create table user (
id int primary key,
name varchar(20),
sex varchar(5),
index(name)
)engine=innodb;
insert into user values(1, '天媛','no');
insert into user values(2, 'zhangsan','no');
insert into user values(3, 'lisi', 'yes');
insert into user values(4, 'lisi', 'no');
用户表:id主键索引,name普通索引(非唯一),sex无索引;
四行记录:其中name普通索引存在重复记录lisi;
开始举例分析。
一、【Using where】
explain select * from user where sex='no';
此语句的执行结果Extra为Using where,表示使用了where条件过滤数据
需要注意的是:
(1)返回所有记录的SQL,不使用where条件过滤数据,大概率不符合预期,对于这类SQL往往需要进行优化;
(2)使用了where条件的SQL,并不代表不需要优化,往往需要配合explain结果中的type(连接类型)来综合判断。例如本例查询的sex未设置索引和主键,所以返回的type为ALL,仍有优化空间,可以建立索引优化查询。
二、【Using index】
explain select id,name from user where name='tianyuan';(id为主键,name为索引)
此句执行结果为Extra为Using index,说明sql所需要返回的所有列数据均在一棵索引树上,而无需访问实际的行记录(索引树的概念在这里不多说了),这样的语句性能往往比较好。
三、【Using index condition】
explain select id,name,sex from user where name='tianyuan';
这一句跟上一句的差异是多查询了一个sex,执行结果Extra为Using index condition,意思是确实命中了索引,但并不是所有列都在索引树上(sex),所以即使访问到了索引,但还需要访问具体的行记录才能拿到结果。
这类语句的执行结果也还可以,不过肯定是不如上面的那个Using index效率高。
四、【Using filesort】
explain select * from user order by sex;
执行结果Extra为Using filesort,这说明,得到所需结果集,需要对所有记录进行文件排序。
这类SQL语句性能极差,需要进行优化。
典型的,在一个没有建立索引的列上进行了order by,就会触发filesort,常见的优化方案是,在order by的列上添加索引,避免每次查询都全量排序。
五、【Using temporary】
explain select * from user group by name order by sex;
执行结果Extra为Using temporary,这说明需要建立临时表(temporary table)来暂存中间结果。
典型的,group by和order by同时存在,且作用于不同的字段时,就会建立临时表,以便计算出最终的结果集。
这类SQL语句性能较低,往往也需要进行优化。
六、【Using join buffer (Block Nested Loop)】
explain select * from user where id in(select id from user where sex='no');
执行结果Extra为Using join buffer (Block Nested Loop)说明,需要进行嵌套循环计算
这里每个表都有四条记录,内外表查询的type都为ALL(因为查询条件是sex),所以需要循环进行4*4次查询。
典型的,两个关联表join,关联字段均未建立索引,就会出现这种情况。常见的优化方案是,在关联字段上添加索引,避免每次嵌套循环计算。
这类SQL语句性能往往也较低,需要进行优化。