作者:cndz 围观群众:602 更新于 标签:mysqlExplainMysql Explain
MySQL中的Explain是一个非常有用的工具,它可以帮助我们分析查询语句的性能。通过Explain,我们可以查看查询语句的执行计划,了解MySQL是如何处理查询语句的,从而优化查询性能。Explain的输出结果包括了查询语句所用到的索引、表的读取顺序、使用到的Join类型等等。
Explain的使用非常简单,只需要在查询语句前加上Explain关键字即可。例如:
Explain select * from zshop_user where username = 'dd';
执行以上查询语句后,MySQL就会输出查询语句的执行计划。
该语句的唯一标识。如果explain的结果包括多个id值,则数字越大越先执行;而对于相同id的行,则表示从上往下依次执行。
查询类型,有如下几种取值
表示当前这一行正在访问哪张表,如果SQL定义了别名,则展示表的别名
当前查询匹配记录的分区。对于未分区的表,返回null
连接类型,有如下几种取值,性能从好到坏排序 如下:
system:该表只有一行(相当于系统表),system是const类型的特例
const:针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可
eq_ref:当使用了索引的全部组成部分,并且索引是PRIMARY KEY或UNIQUE NOT NULL 才会使用该类型,性能仅次于system及const。
-- 多表关联查询,单行匹配
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
-- 多表关联查询,联合索引,多行匹配
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
ref:当满足索引的最左前缀规则,或者索引不是主键也不是唯一索引时才会发生。如果使用的索引只会匹配到少量的行,性能也是不错的。
-- 根据索引(非主键,非唯一索引),匹配到多行
SELECT * FROM ref_table WHERE key_column=expr;
TIPS
最左前缀原则,指的是索引按照最左优先的方式匹配索引。比如创建了一个组合索引(column1, column2, column3),那么,如果查询条件是:
WHERE column1 = 1、WHERE column1= 1 AND column2 = 2、WHERE column1= 1 AND column2 = 2 AND column3 = 3 都可以使用该索引; 在建立复合索引时你放在最左边的字段就能享受索引的支持,即使没有单独为你建立索引,而不是最左边的就不能单独享受这个索引的支持了。
fulltext:全文索引
ref_or_null:该类型类似于ref,但是MySQL会额外搜索哪些行包含了NULL。这种类型常见于解析子查询
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
index_merge:此类型表示使用了索引合并优化,表示一个查询里面用到了多个索引
unique_subquery:该类型和eq_ref类似,但是使用了IN查询,且子查询是主键或者唯一索引。例如:
value IN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery:和unique_subquery类似,只是子查询使用的是非唯一索引
value IN (SELECT key_column FROM single_table WHERE some_expr)
range:范围扫描,表示检索了指定范围的行,主要用于有限制的索引扫描。比较常见的范围扫描是带有BETWEEN子句或WHERE子句里有>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE、IN()等操作符。
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
WHERE key_column IN (10,20,30);
index:全索引扫描,和ALL类似,只不过index是全盘扫描了索引的数据。
possible_keys 显示可能应用在这张表中的索引,一个或多个。查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用。
key 实际使用的索引,如果为NULL,则没有使用索引。(可能原因包括没有建立索引或索引失效)
查询中若使用了覆盖索引(select 后要查询的字段刚好和创建的索引字段完全相同),则该索引仅出现在key列表中
表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度,在不损失精确性的情况下,长度越短越好。key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的。
显示索引的那一列被使用了,如果可能的话,最好是一个常数。哪些列或常量被用于查找索引列上的值。
根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数,也就是说,用的越少越好
Using filesort
说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
Using temporary
使用了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
Using index
表示相应的select操作中使用了覆盖索引(Covering Index),避免访问了表的数据行,效率不错。如果同时出现using where,表明索引被用来执行索引键值的查找;如果没有同时出现using where,表明索引用来读取数据而非执行查找动作。
Using where
表明使用了where过滤
Using join buffer
表明使用了连接缓存,比如说在查询的时候,多表join的次数非常多,那么将配置文件中的缓冲区的join buffer调大一些。
impossible where
where子句的值总是false,不能用来获取任何元组
SELECT * FROM t_user WHERE id = ‘1’ and id = ‘2’
elect tables optimized away
在没有GROUP BY子句的情况下,基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作,不必等到执行阶段再进行计算,查询执行计划生成的阶段即完成优化。
distinct
优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作
我们通过一个简单的示例来演示如何使用Explain来优化查询语句的性能。假设我们有一个存储学生信息的表students,表结构如下:
CREATE TABLE students (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
age int(11) NOT NULL,
gender varchar(255) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;
现在我们需要查询年龄大于20岁的学生信息,查询语句如下:
Select * from students where age > 20;
我们可以先使用Explain来查看该查询语句的执行计划,代码如下:
Explain Select * from students where age > 20;
执行以上代码后,我们可以得到如下的执行计划:
从以上执行计划可以看出,该查询语句并没有使用到任何索引,而是通过全表扫描的方式来查找数据。这样的查询方式在数据量大的情况下会非常慢,因此我们需要优化查询语句。
我们可以为students表添加一个age字段的索引,代码如下:
Alter table students add index idx_age(age);
添加索引之后,我们再次使用Explain来查看查询语句的执行计划,代码如下:
Explain Select * from students where age > 20;
执行以上代码后,我们可以得到如下的执行计划:
从以上执行计划可以看出,该查询语句使用了idx_age索引,使用了索引查找的方式来查找数据。这样的查询方式在数据量大的情况下会非常快。
通过以上的示例,我们可以看出Explain在优化查询语句性能方面的重要性。使用Explain可以帮助我们了解查询语句的执行计划,从而针对性的进行优化。在实际应用中,我们应该尽可能的使用Explain来分析查询语句的性能,从而提高系统的性能表现。