阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

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

203次阅读
没有评论

共计 2679 个字符,预计需要花费 7 分钟才能阅读完成。

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

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

下面分别对 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;

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

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

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

      EXPLAIN SELECT store_id FROM customer;

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

    3. type=range,索引范围扫描,常用于 <、<=、>、>=、between 等操作

      EXPLAIN SELECT * FROM customer WHERE customer_id>=10 AND customer_id<=20;

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

      注意这种情况下比较的字段是需要加索引的,如果没有索引,则 MySQL 会进行全表扫描,如下面这种情况,create_date 字段没有加索引:

      EXPLAIN SELECT * FROM customer WHERE create_date>='2006-02-13' ;

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

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

      store_id字段存在普通索引(非唯一索引)

      EXPLAIN SELECT * FROM customer WHERE store_id=10;

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

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

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

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

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

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

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

    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;

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

    6. type=const/system,单表中最多有一条匹配行,查询起来非常迅速,所以这个匹配行的其他列的值可以被优化器在当前查询中当作常量来处理

      const/system出现在根据主键 primary key 或者 唯一索引 unique index 进行的查询

      根据主键 primary key 进行的查询:

      EXPLAIN SELECT * FROM customer WHERE customer_id =10;

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

      根据唯一索引 unique index 进行的查询:

      EXPLAIN SELECT * FROM customer WHERE email ='MARY.SMITH@sakilacustomer.org';

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

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

    7. type=NULL,MySQL 不用访问表或者索引,直接就能够得到结果

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

  • 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 如何通过 EXPLAIN 分析 SQL 的执行计划

参考

《深入浅出 MySQL》

《深入浅出 MySQL》PDF 文字版(全)PDF 下载  http://www.linuxidc.com/Linux/2016-05/130922.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计2679字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中