首页 / MYSQL / MySQL——通过EXPLAIN分析SQL的执行计划

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

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了MySQL——通过EXPLAIN分析SQL的执行计划，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含2954字，纯文字阅读大概需要5分钟。

内容图文

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

技术分享图片

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

  • select_type:表示SELECT的类型，常见的取值有：

    类型 说明
    SIMPLE 简单表，不使用表连接或子查询
    PRIMARY 主查询，即外层的查询
    UNION UNION中的第二个或者后面的查询语句
    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;

      技术分享图片

    2. type=index，索引全扫描，MySQL遍历整个索引来查询匹配行，并不会扫描表

      一般是查询的字段都有索引的查询语句

      EXPLAIN SELECT store_id FROM customer;

      技术分享图片

    3. 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‘ ;

      技术分享图片

    4. type=ref，使用非唯一索引或唯一索引的前缀扫描，返回匹配某个单独值的记录行

      store_id字段存在普通索引（非唯一索引）

      EXPLAIN SELECT * FROM customer WHERE store_id=10;

      技术分享图片

      ref类型还经常会出现在join操作中：

      customer、payment表关联查询，关联字段customer.customer_id（主键），payment.customer_id（非唯一索引）。表关联查询时必定会有一张表进行全表扫描，此表一定是几张表中记录行数最少的表，然后再通过非唯一索引寻找其他关联表中的匹配行，以此达到表关联时扫描行数最少。

      技术分享图片

      因为customer、payment两表中customer表的记录行数最少，所以customer表进行全表扫描，payment表通过非唯一索引寻找匹配行。

      EXPLAIN SELECT * FROM customer customer INNER JOIN payment payment ON customer.customer_id = payment.customer_id;

      技术分享图片

    5. 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;

      技术分享图片

    6. 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‘;

      技术分享图片

      技术分享图片

    7. 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》




微信公众号：
技术分享图片

来源：http://songwenjie.cnblogs.com/
声明：本文为博主学习感悟总结，水平有限，如果不当，欢迎指正。如果您认为还不错，不妨点击一下下方的【推荐】按钮，谢谢支持。转载与引用请注明出处。

内容总结

以上是互联网集市为您收集整理的MySQL——通过EXPLAIN分析SQL的执行计划全部内容，希望文章能够帮你解决MySQL——通过EXPLAIN分析SQL的执行计划所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

扫描二维码推送至手机访问。

本文链接：https://qyyshop.com/info/1104648.html

来源：【匿名】