MySQL——通过EXPLAIN分析SQL的执行计划

在MySQL中,我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。

下面分别对EXPLAIN命令结果的每一列进行说明:

  • select_type:表示SELECT的类型,常见的取值有: 类型说明SIMPLE简单表,不使用表连接或子查询PRIMARY主查询,即外层的查询UNIONUNION中的第二个或者后面的查询语句SUBQUERY子查询中的第一个
  • table:输出结果集的表(表别名)
  • type:表示MySQL在表中找到所需行的方式,或者叫访问类型。常见访问类型如下,从上到下,性能由差到最好: ALL全表扫描index索引全扫描range索引范围扫描ref非唯一索引扫描eq_ref唯一索引扫描const,system单表最多有一个匹配行NULL不用扫描表或索引
    1. type=ALL,全表扫描,MySQL遍历全表来找到匹配行 一般是没有where条件或者where条件没有使用索引的查询语句 EXPLAIN SELECT * FROM customer WHERE active=0;
    1. type=index,索引全扫描,MySQL遍历整个索引来查询匹配行,并不会扫描表 一般是查询的字段都有索引的查询语句 EXPLAIN SELECT store_id FROM customer;
    1. type=range,索引范围扫描,常用于<、<=、>、>=、between等操作 EXPLAIN SELECT * FROM customer WHERE customer_id>=10 AND customer_id<=20;

    注意这种情况下比较的字段是需要加索引的,如果没有索引,则MySQL会进行全表扫描,如下面这种情况,create_date字段没有加索引: EXPLAIN SELECT * FROM customer WHERE create_date>='2006-02-13' ;

    1. type=ref,使用非唯一索引或唯一索引的前缀扫描,返回匹配某个单独值的记录行 store_id字段存在普通索引(非唯一索引) EXPLAIN SELECT * FROM customer WHERE store_id=10;

    ref类型还经常会出现在join操作中: customerpayment表关联查询,关联字段customer.customer_id(主键),payment.customer_id(非唯一索引)。表关联查询时必定会有一张表进行全表扫描,此表一定是几张表中记录行数最少的表,然后再通过非唯一索引寻找其他关联表中的匹配行,以此达到表关联时扫描行数最少。

    因为customerpayment两表中customer表的记录行数最少,所以customer表进行全表扫描,payment表通过非唯一索引寻找匹配行。 EXPLAIN SELECT * FROM customer customer INNER JOIN payment payment ON customer.customer_id = payment.customer_id;

    1. type=eq_ref,类似ref,区别在于使用的索引是唯一索引,对于每个索引键值,表中只有一条记录匹配 eq_ref一般出现在多表连接时使用primary key或者unique index作为关联条件。 film、film_text表关联查询和上一条所说的基本一致,只不过关联条件由非唯一索引变成了主键。 EXPLAIN SELECT * FROM film film INNER JOIN film_text film_text ON film.film_id = film_text.film_id;
    1. type=const/system,单表中最多有一条匹配行,查询起来非常迅速,所以这个匹配行的其他列的值可以被优化器在当前查询中当作常量来处理 const/system出现在根据主键primary key或者 唯一索引 unique index 进行的查询 根据主键primary key进行的查询: EXPLAIN SELECT * FROM customer WHERE customer_id =10;

    根据唯一索引unique index进行的查询: EXPLAIN SELECT * FROM customer WHERE email ='MARY.SMITH@sakilacustomer.org';

    1. type=NULL,MySQL不用访问表或者索引,直接就能够得到结果
  • possible_keys: 表示查询可能使用的索引
  • key: 实际使用的索引
  • key_len: 使用索引字段的长度
  • ref: 使用哪个列或常数与key一起从表中选择行。
  • rows: 扫描行的数量
  • filtered: 存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例(百分比)
  • Extra: 执行情况的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息 最主要的有一下三种: Using Index表示索引覆盖,不会回表查询Using Where表示进行了回表查询Using Index Condition表示进行了ICP优化Using Flesort表示MySQL需额外排序操作, 不能通过索引顺序达到排序效果

什么是ICP?

MySQL5.6引入了**Index Condition Pushdown(ICP)**的特性,进一步优化了查询。Pushdown表示操作下放,某些情况下的条件过滤操作下放到存储引擎。

EXPLAIN SELECT * FROM rental WHERE rental_date='2005-05-25' AND customer_id>=300 AND customer_id<=400;

在5.6版本之前:

优化器首先使用复合索引idx_rental_date过滤出符合条件rental_date='2005-05-25'的记录,然后根据复合索引idx_rental_date回表获取记录,最终根据条件customer_id>=300 AND customer_id<=400过滤出最后的查询结果(在服务层完成)。

在5.6版本之后:

MySQL使用了ICP来进一步优化查询,在检索的时候,把条件customer_id>=300 AND customer_id<=400也推到存储引擎层完成过滤,这样能够降低不必要的IO访问。Extra为Using index condition就表示使用了ICP优化。

参考

《深入浅出MySQL》

-----END-----

喜欢本文的朋友们,欢迎扫一扫下图关注公众号撸码那些事,收看更多精彩内容

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏撸码那些事

MySQL——通过EXPLAIN分析SQL的执行计划

在MySQL中,我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。

12920
来自专栏Java后端生活

MySQL(十五)DDL之常见的约束

18740
来自专栏nice_每一天

Mysql语句查询优化

EXPLAIN显示了mysql如何使用索引来处理select语句以及连接表。也就是校验sql语句是否使用了索引,以及sql语句的查询效率。

11710
来自专栏杨建荣的学习笔记

MySQL修改数据类型的问题总结(r10笔记第74天)

昨天快下班的时候,突然开发的同事找我说有个紧急需求,负责这个业务的DBA同事回家了,想让我帮忙看看,运行个SQL语句,几秒钟就好。我一听,就本着人道主义的精神留...

353100
来自专栏编程心路

写给新手的Mysql入门指南(二)

ALTER TABLE t1 ALTER age SET DEFAULT 20;

7920
来自专栏java达人

left join 过滤条件写在on后面和写在where 后面的区别

create table t1(id int, feild int); insert into t1 values(1 , 1); insert into t1...

280100
来自专栏散尽浮华

mysql操作命令梳理(2)-alter(update、insert)

在mysql运维操作中会经常使用到alter这个修改表的命令,alter tables允许修改一个现有表的结构,比如增加或删除列、创造或消去索引、改变现有列的类...

22160
来自专栏资深Tester

增删改查的查之简单查询

16330
来自专栏Java后端生活

MySQL(十三)DDL之库和表的管理

18640
来自专栏java工会

十个mysql语句的优化方法

20960

扫码关注云+社区

领取腾讯云代金券