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

在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>=10AND 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操作中:

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;

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>=300AND customer_id<=400;

在5.6版本之前:

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

在5.6版本之后:

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

原文发布于微信公众号 - 撸码那些事(lumanxs)

原文发表时间:2018-08-02

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java达人

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

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

25510
来自专栏静默虚空的博客

sql 语言

sql 语言 DDL DDL 全称 Data Definition Language,即数据定义语言。 DATABASE 创建数据库 CREATE DATABA...

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

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

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

34510
来自专栏battcn

MySQL - 索引详解

索引依托于存储引擎的实现,因此,每种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。所有存储引擎支持每个表至少16个索引,总索引长度至...

862
来自专栏Java后端生活

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

1754
来自专栏数据库

开发篇-MySQL分区(一)

MySQL从5.1版本开始支持分区的功能。分区是指根据一定的规则,数据库把一个表分解成多个更小的、更容易管理的部分。就访问数据库的应用而言,逻辑上只有一个表或一...

2267
来自专栏散尽浮华

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

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

1996
来自专栏文渊之博

mysql执行计划看是否最优

介绍   本篇主要通过汇总网上的大牛的知识,简单介绍一下如何使用mysql的执行计划,并根据执行计划判断如何优化和是否索引最优。   执行计划可显示估计查询语...

35510
来自专栏资深Tester

增删改查的查之简单查询

1553
来自专栏butterfly100

mysql explain详解

Explain简介 本文主要讲述如何通过 explain 命令获取 select 语句的执行计划,通过 explain 我们可以知道以下信息:表的读取顺序,数据...

1.1K9

扫码关注云+社区