一、定义
explain命令显示了mysql如何使用索引来处理select语句以及连接表。
explain显示的信息可以帮助选择更好的索引和写出更优化的查询语句。
二、使用方法
explain ELECT * FROM table_name WHERE column_1='123';
三、EXPLAIN列的解释
四、SELECT_TYPE字段含义
1、UNION:
当通过union来连接多个查询结果时,第二个之后的select其select_type为UNION。
2、DEPENDENT UNION(从属联合)与DEPENDENT SUBQUERY(依赖子查询):
当union作为子查询时,其中第二个union的select_type就是DEPENDENT UNION。
第一个子查询的select_type则是DEPENDENT SUBQUERY。
3、SUBQUERY
子查询中的第一个select其select_type为SUBQUERY。
4、DERIVED
当子查询是from子句或者select字句时,其select_type为DERIVED。
5、SIMPLE:简单SELECT(不使用UNION或子查询)
6、PRIMARY:最外面的SELECT
7、UNION RESULT:UNION 的结果
五、TYPE含义解释
1、system:表仅有一行(=系统表)。这是const联接类型的一个特例。
2、const:表最多有一个匹配行,它将在查询开始时被读取。因为仅有一行,在这行的列值可被优化器剩余部分认为是常数。const表很快,因为它们只读取一次!
3、eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
在a表中的id是主键,b表中的id也是主键,该表可以认为是订单表的补充信息表,他们的关系是1对1,在下面的例子中可以看到b表的连接类型是eq_ref,这是极快的联接类型。
4、ref:对于每个来自于前面的表的行组合,所有有匹配索引值的行将从这张表中读取。与上面例子的区别就是多了一个条件
注意这里id如果只有一条,就是const,因为id已经写死了,所以如果id有重复的,也就是由id=1/G返回来的结果有很多行,所以位ref。
5、ref_or_null:该联接类型如同ref,但是添加了MySQL可以专门搜索包含NULL值的行。
在下面的查询中可以看到联接类型为ref_or_null,这是mysql为含有null的字段专门做的处理。在我们的表设计中应当尽量避免索引字段为NULL,因为这会额外的耗费mysql的处理时间来做优化。
注意:查询条件不是索引字段,type类型变化,在这两个截图中,查询是索引字段,是全表查询,即ALL,查询字段创建索引字段,type就变成了ref_or_null。
6、index_merge:该联接类型表示使用了索引合并优化方法。经常出现在使用一张表中的多个索引时。mysql会将多个索引合并在一起
7、unique_subquery:该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。
8、index_subquery:该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
9、range:只检索给定范围的行,使用一个索引来选择行。
10、index:该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
11、ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。这种情况一般是没有索引
六、extra列返回的描述的意义
1、Distinct:一旦MYSQL找到了与行相联合匹配的行,就不再搜索了
2、Not exists: MYSQL优化了LEFT JOIN,一旦它找到了匹配LEFT JOIN标准的行,就不再搜索了
hr_staff_base的主键是base_id,不存在为空的清空,所以该表不存在这样子的数据
3、Range checked for each Record(index map:#):没有找到理想的索引,因此对于从前面表中来的每一个行组合,MYSQL检查使用哪个索引,并用它来从表中返回行。这是使用索引的最慢的连接之一
4、Using index: 表示查询的列被索引覆盖,这个是查询性能比较高的体现,即所要查询的信息搜在索引里面可以得到,不用回表,索引被正确的使用。假如同时出现了using where,表示索引用于执行索引键值的查找;若是没有出现using where,则表示索引用于读取数据,而非执行查询的动作。
5、Using where:该属性与Using index相反,查询的列并没有被索引覆盖,where条件后面使用的是非索引的前导列,它仅仅是使用了where条件而已。使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行,并且连接类型ALL或index,是查询有问题不同连接类型的解释(按照效率高低的顺序排序)
6、Using temporary:表示使用了临时表存储中间的结果,一般在对结果排序的时候会使用临时表
7、Using filesort: 表示文件排序,说明Mysql对数据使用了外部的索引进行排序,并没有使用表中的索引进行排序
该表的staff_name不是索引列,所以用这个字段排序需要建立临时表
8、Using sort_union(…), Using union(…), Using intersect(…):这些函数说明如何为index_merge联接类型合并索引扫描
9、Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表
10、Using join buffer Using join buffer表示使用连接缓存,它强调在获取连接条件时,并没有使用索引,而是使用连接缓冲区来存储中间结果,若是出现该值,一般说明需要添加索引来进行优化了。