explain执行计划

desc user;

 

show index from  user; user表id字段是主键,number,name,area_id是三个普通索引

 

 

 

 

 explain-id:

explain select id from (select * from user limit 10) alias;

 

 

 id相同时执行顺序由上至下,id不同时越大的id越先执行

explain-select_type:

SIMPLE：简单的select查询，查询中不包含子查询或者union

explain select * from user where id=99

 

 

PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为primary,或者说 复杂查询中最外层的 select 

explain select id from (select * from user limit 10) alias;

 

 

subquery:子查询

 explain select * from user where id=(select id from test where name='name88')

 

derived:派生,from后是临时表

explain select id from (select * from user limit 10) alias;

 

 union:联合查询

 EXPLAIN select * from user where name='name-11' union all select * from user_nonindex where name='name-11'

 

 explain-table:查询的表

 explain-type:关联类型或访问类型,性能从好到差依次是system >const > eq_ref > ref > fulltext > ref_or_null > index_merge >unique_subquery > index_subquery > range > index > ALL

const: 主键索引或唯一索引列等于常数(如果是普通索引列,就不是const了)

 explain select * from user where id=20

 ref:  非唯一索引

 eq_ref:

explain select * from user,test where user.id=test.id 

all: 全表扫描

 如果把上面的内连接改成左连接或右连接,那对应的左表/右表的type是all

explain-rows:查询的行数

explain-filtered: 返回结果的行数占需要读取行数的百分比,数值越大越好