五、使用explain命令
在查询的时候使用explain命令可以查看是否使用了索引,以便查看查询语句的性能和索引建立的是否合适。
先来设定两张表:
学生基本信息表:student(id,name,sex,code,firstname,lastname)
学生课程表:study(student_id,course,timme)
给学生信息表加上索引:
主键:id
唯一索引:code,
普通索引:lastname
下面就基于以上的信息来学习一下explain命令:
Explain产生的主要有以下几列信息:
1.Id
id是用来顺序标识整个查询中SELELCT 语句的,语句中涉及到到少个查询,这里就与多少个id(其实也就对应应了有多少行),如一下语句:
显示了两条查询,其中最末的那条一般是最先执行的
2.select_type
查询语句的类型,一般可分为一下几种:
1)simple
简单的select查询语句,没有子查询、联合查询等
2) primary
最外层或最靠前的查询,如以下语句:
- UNION
Union查询中第二个语句及之后的语句,如以下例子
- DERIVED
from后中的子句,如如以下例子
- UNION RESULT
表示一个UNION查询的查询结果
7) DEPARTENT UNION
满足UNION查询,UNION中第二个子句,并且查询结果依赖于外部的查询。(也就是说这个UNION是个内部的查询),如以下例子:
- SUBQUERY
子查询中的第一个查询语句:
- DEPENDENT SUBQUERY
和DEPARTENT UNION原理相同
- table
表示查询语句所涉及到的表,有些记录不一定是表,是其他查询的中间结果
例如,以上例中的第一条table就是基于第二条查询的结果,查询结果规则为:derived+id
- type
这一列比较重要,说明了整查询语句中是如何进行表之间的关联操作,以及师傅使用到索引。可以分为以下几类(以下的类型理论上效率越来越低);
- const
只匹配一个数据,且是使用常量数据和主键或唯一索引进行比较时。如以下语句:
以上两个查询使用了主键、唯一键,且都是和常量比较。
以下查询虽然使用了常量比较和索引,但并非主键或唯一键,故不算const类型:
- system
const中的特殊情况,表示结果中只有一行数据满足情况,如:
以上例子中,第一条是基于在第二条的基础上上查询的,第二条已经是一个const类型,并且输出结果只有一行,所以第一条满足system的条件。
- eq_ref
索引被连接使用的情况(看不懂),主要一种场景就是 “=、>、<”等操作符中使用索引列,且必须为主键或者唯一键,操作符的另一边可以是常量、表达式(如果这里操作符另一边是一个常量数据,那就是const类型了),如:
然而这里是all,看了一下别人的说法,说是InnoDB如果查询数量较小的情况下,eq_ref会默认为all,这个我就去测了,大家有兴趣可以自己试一下。
- ref
区别于eq_ref,ref的场景主要为:
使用最左前缀的索引
使用的索引为非 主键或唯一键
- ref_or_null
和ref类似,区别在于在查询的时候回有一些对null的指令
6) index_merger
表示查询找中使用了多个索引合并来优化查询,这种类型在后面讲到key这一列中会标注使用了那些索引来优化。针对于复杂查询,这样的类型或许是个不错的方法。
7)unique_subquery
该类型替换了下面形式的IN子查询的ref:
value IN (SELECT primary_key(主键) FROM single_table(单表) WHERE some_expr)
unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
- index_subquery
与unique_subquery类似,不同之处在于子查询中的查询列为非唯一键,即替换的子查询为;
value IN (SELECT normal_key(普通非唯一键) FROM single_table(单表) WHERE some_expr)
9)range
只检索给定范围的行,使用一个索引来选择行,如:
10) index
和all差不多,要进行全表扫描,区别在于可能查询中可能存在使用排序order by,如果使用了索引来排序,则就是index类型
11)字面意思,不使用索引,全表扫描。
5.possible_keys
指出MySQL能使用哪个索引在该表中进行查询找到相应的行。
6..key
key列显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
7.key_len
key_len列显示MySQL决定使用的键长度。如果键是NULL,则长度为NULL。使用的索引的长度。在不损失精确性的情况下,长度越短越好 。
8.ref
显示使用哪个列或常数与key一起从表中选择行
9.rows
显示MySQL认为它执行查询时必须检查的行数。注意这是一个预估值
Extra
另外一个很重要的列,该列显示MySQL在查询过程中的一些详细信息,可以根据这些信息来判断查询与和索引是否要进行优化。。这些信息信息主要包含了以下几种类型:
- using filesort
查询语句中有排列指令时,通常有两种排序方式;一是使用排序操作;二是使用索引。当出现using filesort时,说明使用了索引排序,一般来说使用索引排序比较快。
- Using temporary
代表使用了临时表,一般看到它说明查询需要优化了,如果可以,尽量避免临时表使用。
- Not exists
MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行, 就不再搜索了。
- Using index
说明查询是覆盖了索引的,这是好事情。MySQL直接从索引中过滤不需要的记录并返回命中的结果。这是MySQL服务层完成的,无需再回表查询记录。
5) Using where
使用了WHERE从句来限制哪些行,筛选后的行再与下一张表匹配或者是返回给用户。
以上就是介绍的explain指令的内容,只介绍了一些基本的,虽然写一条业务sql并没有很复杂,但是尽量考虑多一点,考虑到以后的扩展,不给种植户挖坑,而且性能这个东西,能节约一点是一点,数据量上去了,就体现出差距了。