explain详解、mysql索引

Mysql / 199人浏览 / 0人评论



SQL编写过程:
	select      .. from     ..  join     ..  on     ..  where   ..   group by   .. having   .. order by   ..  limit  ..
SQL解析过程:
	from       .. on        ..  join   .. where   .. group by  .. having       .. select    ..  order by  ..  limit  ..

创建索引 : 

方式一:create 索引类型 索引名  on 表(字段);
单值索引语法: create   index    dept_index    on    tb(dept);
唯一索引语法:  create  unique index   name_index   on  tb(name);
复合索引语法:  create  index    dept_name_index    on   tb(dept,name);


方式二:alter table  表名  add   索引类型   索引名 (字段)
单值索引语法: alter  table   tb  add   index   dept_index(dept) 
唯一索引语法:  alter  table   tb  add  unique index  name_index(name);
复合索引语法:  alter  table   tb  add   index   dept_name_index(dept,name);
主键索引语法alter   table   tb  add  constraint  tid_pk primary key(tid);

删除索引:drop index 索引名  on 表名                            alter table  表名  drop   primary key;
                             drop index name_index on tb;
查询索引:show index fron 表名;

explain查询执行计划 :explain + sql语句 (explain select * from tb;)
    id:编号
	select_type:查询类型
	table:表
	type:类型
	possible_keys:预测用到的索引
	key:实际用到的索引
	key_len:实际用到的索引长度
	ref:表之间的引用
	rows:用过索引查询到的数据量
	Extra:额外的信息
id详解:
		id值相同:从上往下执行,数据量少的表优先查询(笛卡尔积)
		id值不同:id值越大越优先查询(本质:在嵌套子查询时,先查内层,再查外层)
select_type:
		PRIMARY:包含子查询的sql中的主查询(最外层)
		SUBQUERY:包含子查询sql中的子查询(非最外层)
		simple:简单查询(不包含子查询、union)
		derived:衍生查询(使用到了临时表)
			a.在from子查询中只有一张表
				explain select cr.name from (select * from tb  where tid in (1,2)) cr
			b.在from子查询中,如果有tb1 union tb2 , 则 tb1就是derived ,tb2就是union
				eplain select cr.name from (select * from tb where tid=1 union select * from tb where tid=2) cr
		union:
		union result:
	system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_sub
type详解:

system > const > eq_ref > ref > range > index > all

    system:只有一条数据的系统表或衍生表只有一条数据的主查询

     const:仅仅能查到一条数据的sql,用于主键索引和唯一索引 

    eq_ref:唯一索引:对于每个索引建的查询,返回匹配唯一行数据(有且只有一个)

     ref:非唯一索引,对于每个索引建的查询,返回匹配的所有行;(0,多)

     range:检索指定范围的行,where后面是一个范围查询(between,in,> ,<, >=);in有时会失效

     index:查询全部索引中的数据;  ---只扫描索引表,不需要扫描全部数据

     all:查询全部表中的数据;        ----需要全表扫描

    system/const:结果只有一条数据;

    eq_ref:结果多条,但是每条数据是惟一的;

    ref:结果多条,但是每条数据是0或者多条;


possible_keys:可能用到的索引,不一定准确;
key:实际用到的索引;

注意:如果possible_keys/key是null,则说明没用索引;

key_len:索引的长度,用于判断复合索引是否被完全使用
ref:指明当前表参照的字段
rows:被索引优化查询的数据个数;

Extra:
		(1)using filesort:性能消耗大,需要‘额外’的一次排序;一般出现在order by 语句中;
	排序:先查询,再排序  
	单值索引:如果排序和查找是同样一个字段不会出现using filesort,如果不是同一个字段,则会出现using filesort
	避免:where哪些字段。order by哪些字段

	复合索引:不能跨列(最佳左前缀)
	避免:where和order by 按照复合索引的顺序使用,不要跨列或无序使用(where和order by拼起来不跨列)
		

		(2)using temporary:性能消耗大,用到了临时表,一般出现在group by 语句中;
	避免:查询哪些字段就根据哪些字段group by;

		(3)using index :性能提升:索引覆盖。原因:不读取原文件,只从索引文件中获取数据(不需要回表查询); 只要使用到的列,全部都在索引中,就是索引覆盖;
		(4)using where:需要回表查询
			假设age是索引列
		查询语句:select age,name from ... where age=... 此语句中必须回原表查name,因此会显示using where
















0 条评论

还没有人发表评论

发表评论 取消回复

记住我的信息,方便下次评论
有人回复时邮件通知我