mysql explain
语法:explain select语句
作用:查看SQL语句的执行计划,查看该SQL语句有没有使用上了索引,有没有做全表扫描
范例:
alter table emp add index emp_index(ename,age,job);//建索引
explain select * from emp where ename='zan' and age=22 and job='kezhang';//执行explain语句查看是否使用了索引
注意这里的条件,我们使用了全值匹配原则,就是emp_index这个索引包含的所有列都用做了where条件,并且连顺序都跟建立索引时的顺序一样。
这里讨论两个问题:1.三个索引列,只使用一个或者几个可不可以?答案是,不确定。如果是全值匹配,那么一定是使用到了索引的,这个不讨论,
如果只使用一个索引列作为查询条件,那么会遵循一个原则,最左匹配原则,即根据我们创建索引时的顺序从左到右,ename->age->job
连续匹配,则会使用索引,即explain select * from emp where ename='zan' ;或者explain select * from emp where ename='zan' and age='22';或者
explain select * from emp where ename='zan' and age='22' and job='kezhang';
但如果不连续,比如,不从ename作第一个,直接用age或者job开始,explain select * from emp where age='22' and job='kezhang';如下图
这样就没有使用到索引,因为我们建立索引的顺序是从ename开始的,使用时要求必须起始点开始且不能间隔
全值匹配可以不分顺序,但本质上也是遵循顺序原则,只不过mysql内置的查询优化器帮我们做了这个事,它去做了这个顺序最优调整。本着优化性能的角度,因此我们尽量还是遵守嘛,给优化器少了一份活儿干。
所以到这我总结:给一张表建一个列作为唯一索引是最好的,简单
下面详解这张表,
id: 数字标识,表示查询中执行select子句或操作表的顺序
id相同,执行顺序从上往下
id不同,id值越大,优先级越高,越先执行
select_type:
查询类型,主要用于区别普通查询,联合查询,子查询等的复杂查询
1、simple ——简单的select查询,查询中不包含子查询或者UNION
2、primary ——查询中若包含任何复杂的子部分,最外层查询被标记
3、subquery——在select或where列表中包含了子查询
4、derived——在from列表中包含的子查询被标记为derived(衍生),MySQL会递归执行这些子查询,把结果放到临时表中
5、union——如果第二个select出现在UNION之后,则被标记为UNION,如果union包含在from子句的子查询中,外层select被标记为derived
6、union result:UNION 的结果
table:
输出的行所引用的表,显示这一步所访问数据库中表名称(显示这一行的数据是关于哪张表的),有时不是真实的表名字,可能是简称,也可能是第几步执行的结果的简称
partitions: buzhidao
type:
对表访问方式,表示MySQL在表中找到所需行的方式,又称“访问类型”。
常用的类型有: ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好)
ALL:Full Table Scan, MySQL将遍历全表以找到匹配的行
index: Full Index Scan,index与ALL区别为index类型只遍历索引树,通常比all快,因为索引文件比数据文件小很多
range:只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪个索引,一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好,因为它开始于索引的某一个点,而结束另一个点,不用全表扫描
ref: 非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,可能会找多个符合条件的行,属于查找和扫描的混合体
eq_ref: 类似ref,区别就在使用的索引是唯一索引或主键索引,对于每个索引键值,表中只有一条记录匹配,简单来说,就是多表连接中使用primary key或者 unique key作为关联条件
const、system: 当MySQL对查询某部分进行优化,并转换为一个常量时,使用这些类型访问。如将主键置于where列表中,MySQL就能将该查询转换为一个常量,system是const类型的特例,当查询的表只有一行的情况下,使用system
NULL: MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。
tips:一般保证查询至少达到range级别,最好能达到ref !!!
possible_keys:显示MySQL能使用哪个索引在该表中找到行,即该表设置的所有索引列举出来,使用否不清楚。
Key:key列显示MySQL实际使用的(索引)
如果没有选择索引,键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX
查询中如果使用覆盖索引,则该索引和查询的select字段重叠
key_len:表示索引中使用的字节数,该列计算查询中使用的索引的长度在不损失精度的情况下,长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度,并非实际使用长度。
ref:显示索引的哪一列被使用了,如果有可能是一个常数,哪些列或常量被用于查询索引列上的值 列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
rows:根据表统计信息以及索引选用情况,大致估算出找到所需的记录所需要读取的行数
Extra:包含不适合在其他列中显示,但是十分重要的额外信息
1、Using filesort:说明mysql会对数据适用一个外部的索引排序。而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”
2、Using temporary:使用了临时表保存中间结果,mysql在查询结果排序时使用临时表。常见于排序order by和分组查询group by。
3、Using index:表示相应的select操作用使用覆盖索引,避免访问了表的数据行。如果同时出现using where,表名索引被用来执行索引键值的查找;如果没有同时出现using where,表名索引用来读取数据而非执行查询动作。
4、Using where :表明使用where过滤 不用读取表中所有信息,仅通过索引就可以获取所需数据,这发生在对表的全部的请求列都是同一个索引的部分的时候,表示mysql服务器将在存储引擎检索行后再进行过滤 即where条件的列都是同一个索引的列
5、using join buffer:强调了在获取连接条件时没有使用索引,并且需要连接缓冲区来存储中间结果。如果出现了这个值,那应该注意,根据查询的具体情况可能需要添加索引来改进能
6、impossible where:where子句的值总是false,不能用来获取任何元组 where语句会导致没有符合条件的行
7、select tables optimized away:在没有group by子句的情况下,基于索引优化Min、max操作或者对于MyISAM存储引擎优化count(*),不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
8、distinct:优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作。
9、No tables used:Query语句中使用from dual 或不含任何from子句