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

MySQL的explain命令详解

188次阅读
没有评论

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

在 MySQL 中,当需要对某条 SQL 查询语句进行分析时,我们经常会使用 explain 命令 或 desc 命令进行操作,分析 SQL 语句时,explain 和 desc 的作用是一样的。

使用 explain 命令可以分析出 SQL 查询语句中索引的使用情况、扫描的行数、扫描的类型等等,以便帮助我们对索引和 SQL 语句进行优化。

使用方法:在 SQL 查询语句前面,加上 explain 或 desc 即可。

为了便于演示,现在我创建了一张 people 表:

create table people (id int unsigned not null auto_increment primary key comment '主键 id',
    last_name varchar(20) not null default '' comment ' 姓 ',
    first_name varchar(20) not null default '' comment ' 名 ',
    birthday date not null default '1970-01-01' comment '出生日期',
    gender tinyint unsigned not null default 3 comment '性别:1 男,2 女,3 未知',
    key(last_name, first_name, birthday)
) engine=innodb default charset=utf8;

可以看出,people 表中有一个主键索引(id)和一个复合索引 key(last_name, first_name, birthday)。

people 表中也已经插入了如下一些数据:

id last_name first_name birthday gender
1 Clinton Bill 1970-01-01 3
2 Allen Cuba 1960-01-01 3
3 Bush George 1970-01-01 3
4 Smith Kim 1970-01-01 3
5 Allen Cally 1989-06-08 3

然后,执行下面的语句:

explain select id,first_name from people where id=3 \G

结果如下:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: people
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra:
1 row in set (0.00 sec)

下面,对 explain 分析结果中的各个参数进行详细说明。

1. id

explain 得到的结果集的 id 编号,不重要。

2. select_type

select_type,即 SQL 查询语句的类型,准确地说,应该是当前的 select 语句操作 table 的方式。常见的值有以下几种:

  • SIMPLE 它表示简单的单表查询,没有 union 和子查询。
  • PRIMARY 它表示主表(也就是最外层的表)查询。这个类型通常可以在 DERIVED 和 UNION 时见到。
  • DERIVED 它表示派生表查询,派生表本身不是一个物理表。
  • DEPENDENT SUBQUERY 它表示子查询。
  • UNION 它表示 union 语句中的查询。

3. table

它表示和当前的输出结果相关的表。

4. type

type 是一个非常重要的参数,也较为复杂,它表示了当前的查询所使用的查找数据行的扫描方式或访问类型。访问类型有很多种,比如:全表扫描、索引扫描、范围扫描、唯一索引扫描、常数引用扫描等等。这里列的这些,速度是从慢到快,扫描的行数也是从多到少。现在,我们就根据这个顺序介绍几个常见的扫描方式:

  • ALL 全表扫描,表示需要扫描整张表,才能获取到需要的数据。不会使用到索引,一般来说,它的性能最差。如:

    desc select id,first_name from people where gender=2;
  • index 索引扫描,表示仅从索引中扫描获取数据,index 和 all 差不多,都需要扫描全部的记录,只不过 index 是从索引中扫描全部记录获取需要的数据。虽然从索引中扫描获取了数据,但实际上并没有用到索引加快查找速度的功能。也就是说索引失效了。性能也较差。如:

    explain select id,last_name,first_name,birthday from people where first_name='Cuba';
  • range 范围扫描,表示在给定的范围内进行扫描,会使用到索引,性能较好。如:

    explain select id,first_name from people where id < 3;
    或
    explain select id,first_name from people where last_name BETWEEN 'Allen' And 'Clinton';
  • ref 常规索引匹配扫描,表示用到的索引是常规索引(也叫普通索引),性能较好。需要注意的是,复合索引中的列顺序,要想使索引生效,需遵循最左前缀匹配法则。如:

    explain select id,first_name from people where last_name='Allen' and first_name='Cuba';
  • eq_ref 索引关联扫描,联表查询时,如果关联的键是主键,就会出现这个值。性能较好。

  • const 常量引用扫描,表示用到的索引是主键或唯一索引,索引会等值匹配一个定值。性能最好。如:

    explain select id,first_name from people where id=3;

5. possible_keys

表示当前查询可能用到的索引,这个值不太重要。

6. key

表示当前查询实际用到的索引。

7. key_len

表示当前查询用到的索引的长度(字节数)。

8. ref

表示索引的哪一列被用到了,如果是等值匹配索引,则是一个常数 const。

9. rows

表示当前查询实际扫描的行数。值越小越好。这个参数非常重要。

10. Extra

表示当前查询的额外信息,比如是根据什么方式排序的,获取数据的方式等。下面列举几个 Extra 参数常见的值:

  • using filesort 表示当前查询做了额外的步骤将结果集进行文件排序。如果看到这个值,就需要进行优化了。
  • using temporary 表示当前查询使用了一个临时表来存储结果。如果看到这个值,也需要进行优化。
  • using index 表示只使用索引就可以满足全部的查询需求,不需要再回表查询数据行,通常称之为索引覆盖查询。

通常,如果我们明确知道了哪条 SQL 查询语句效率很差,就可以用 explain 或 desc 来分析该 SQL 语句。但是,大多数情况下,我们并不知道是哪些 SQL 查询语句有问题,一般都是开启 MySQL 的慢查询日志,通过慢查询日志来确定查询效率较慢的问题 SQL,然后再对这些问题 SQL 进行分析和优化。

本文永久更新链接地址 :http://www.linuxidc.com/Linux/2016-11/13685.htm

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