前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >翻译翻译,什么TMD叫EXPLAIN

翻译翻译,什么TMD叫EXPLAIN

作者头像
一条coding
发布2022-02-08 13:31:22
1.3K0
发布2022-02-08 13:31:22
举报
文章被收录于专栏:一条IT

本文共4802字,21图,预计阅读13分钟。

哈喽,大家好,我是一条,一个梦想弃码从文的程序员!

先跟大家补一个元旦快乐!新年新气象,答应大家好久的sql优化内容也该提上日程。

其实网上有很多写的很好的sql优化文章,全面细致,但是都遗漏了一个问题,只教了大家怎么治病,没教怎么看病,这就好比一个饱读医书的大夫,病人往这一坐,望闻问切全都不会,一身的本事不知道该用哪个?

急死个人了。

所以今天就聊聊怎么看病,也就是如何看懂MySQL的执行计划

EXPLAIN

当客户端发送给服务端一条sql语句后,并不是拿过来就执行的,而是先经过优化器选取最优的方案,比如表的读取顺序,索引实际是否被使用,表之间的引用等。 而优化后的执行方案就称之为执行计划

EXPLAIN的作用就是查看执行计划,使用起来非常简单,无论是select、insert、update、delete,都是只需要在前面加explain

代码语言:javascript
复制
-- items : 商品主数据表
explain select * from items;

执行后的结果如下(为方便查看,使用树形结构展示):

左面就是执行计划的列名,我们的学习的关键就是要知道每列的含义。

右面是对应的值,在实际开发中通过分析值来诊断sql语句的问题。

看懂执行计划

id

select的执行顺序,怎么理解呢?看下面的sql:

代码语言:javascript
复制
  explain
  select *
  from items_img
  where item_id =
        (select id from items where item_name like '蛋糕');

共有两个查询,哪个先执行呢,可以通过id来判断:

  • id 越大,优先级越高,越先执行。
  • id相同的情况下执行顺序是由上到下。

验证一下:

可以看到id=2,对应items表,先执行。

select_type

观察刚才的输出结果,发现子查询的select_type值是不一样的,分别是什么意思呢?

顾名思义,应该是查询类型的意思,我们只要知道了某个小查询的select_type属性,就知道了这个小查询在整个大查询中扮演了一个什么角色。

PRIMARY是指查询中包含子查询,并且该查询位于最外层,而SUBQUERY翻译过来就是子查询。上面的SIMPLE则是最普通,最简单的查询。

还有一些其他的值如下:

  • DERIVED : 表示在from中包含子查询
  • UNION : 对于包含UNION或者UNION ALL的大查询来说,除了最外层的查询会被标记为PRIMARY,其余都会被标记为UNION
  • UNION RESULT : 表示UNION查询中的临时表。
  • MATERIALIZEDINEXISTS后的查询。

补充说明: MATERIALIZED翻译过来是物化的意思,即将子查询结果集中的记录保存到临时表的过程。 临时表称之为物化表。正因为物化表中的记录都建立了索引(基于内存的物化表有哈希索引,基于磁盘的有B+树索引),通过索引执行IN语句判断某个操作数在不在子查询结果集中变得非常快,从而提升了子查询语句的性能。

table

这个无需多说,表明这一行的数据是关于哪个表。

partitions

这里先介绍一下分区表的概念,和我们常说的分库分表不同。

分区表是指将数据文件在磁盘上进行分区,将一个大文件分成多个小文件。可以优化查询性能,特别是对于count查询可以并发统计,还可以通过指定分区快速删除废弃数据。

分区类型:

  • RANGE分区:根据给定一个连续的区间进行分区。在删除旧数据时特别有用。
  • LIST分区:根据具体数值分区。假设某商品销售在华东,华中,华北三个战区,按照战区分区,在where查询时只需要指定分区即可。
  • HASH分区:根据对固定整数取模来分区,这就要求数据分布比较平均。Hash分区也存在与传统Hash分表一样的问题,可扩展性差。MySQL也提供了一个类似于一致性Hash的分区方法——线性Hash分区,只需要在定义分区时添加LINEAR关键字。
  • KEY分区:与Hash分区很相似,只是Hash函数不同。

看一个创建分区表的示例:

代码语言:javascript
复制
 -- 创建user表
 create table user_partitions (
   id int auto_increment, 
   name varchar(12),
   primary key(id)
 )
 -- 按照id分区,id<100 p0分区,其他p1分区
 partition by range(id)(
     partition p0 values less than(100),
     partition p1 values less than maxvalue
 );

回到执行计划,partitions这列表明数据在哪个分区。

type

代表访问类型,即如何查找数据,结果值从最好到最坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery> range > index>all

可以只记住简化版的:

system > const > eq_ref > ref > range > index>all

生产环境一般需要达到refrange 级别。依次给大家介绍下:

system:表中只有一行记录(等于系统表),平时基本不会出现。

const:通过索引一次就找到了。

示例:

代码语言:javascript
复制
explain
    select * from items where id = 'bingan-1001';

eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。

ref:非唯一索引扫描,返回匹配某个单独值的所有行。

首先商品表给销量建一个索引,但不是唯一索引。

sql如下:

代码语言:javascript
复制
explain
    select * from items where sell_counts = 3308;

range:只检索给定范围的行。

还是销量这列,sql如下:

代码语言:javascript
复制
explain
    select * from items where sell_counts between 3000 and 10000;

index:当查询的结果全为索引列的时候。

代码语言:javascript
复制
explain
    select id,sell_counts from items;

all:全表扫描

代码语言:javascript
复制
explain
    select * from items where sell_counts;

null:是不是没想到还会有空的时候,空的意思是我都不需要查表,只需要查索引就能搞定,比如:

代码语言:javascript
复制
explain select min(id) from items;

table也是空,说明只查了索引。

possible_keys

翻译一下就是可能用到的key,但不一定真正会用到,有可能是因为MySQL认为有更合适的索引,也可能因为数据量较少,MySQL认为索引对此查询帮助不大,选择了全表查询。

如果想强制使用或不使用某个索引,可以在查询中使用 force indexignore index

key

真正用到的索引。

通过对比possible_keyskey,可以观察所建的索引是否被使用,即索引是否合理,从而进行优化。

索引不是建的越多越好,可能使用的索引越多,查询优化器计算查询成本时就得花费更长时间,所以如果可以的话,尽量删除那些用不到的索引。

对于线上已经存在大量数据的表,不要轻易增加索引,因为会增大数据库的压力。

key_len

表示索引使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。

看一个案例:

新建一个联合索引

执行如下sql

代码语言:javascript
复制
explain
    select * from items where sell_counts=300;

看一下结果

显然是用到了联合索引,但是具体用到那一列呢,发现ken_len是4,正好是一个int类型的长度,也是就只使用了sell_counts这列。

修改一下sql

代码语言:javascript
复制
explain
    select * from items where sell_counts=300 and item_name='好吃蛋糕甜点蒸蛋糕';

执行结果

索引不变,ken_len变为134,怎么来的呢?

需要先看一下item_name的长度是32

还需要知道字符编码是是什么?show variables like 'character%';

utf8mb4是个啥呢,简单说就是它才是MySQL中真正的utf8,而MySQL中的utf8是一种“专属的编码”,它能够编码的Unicode字符并不多。

这其实MySQL的一个bug,utf8mb4是用来修复的。

言归正传,用字段长度*编码所占字节=总的字节数。即32*4=128。(latin1占用一个字节,gbk占用两个字节,utf-8 占用三个字节)。

但是这还没完,因为varchar是可变长度的,还需要两位存储真正的长度。这样加上int的四个字节,刚好134,由此推断出用到了sell_contsitem_name两列(128+2+4=134)。

另外由于字符串是可以存储空值的,所以还需要一个标志位来存储是否为空,但是在本例中,item_name是非空列,所以不再加一。

ref

展示与索引列作等值匹配的值是什么,比如一个常数或者是某个列。

代码语言:javascript
复制
explain
    select * from items i where id = 'cake-1001';

这样是一个常数

代码语言:javascript
复制
explain
    select * from items i left join category c
    on c.id = i.cat_id;

这样是一个列

rows

大致所需要读取的行数。

如果查询优化器决定使用全表扫描的方式对某个表执行查询时,代表预计需要扫描的行数。

如果使用索引来执行查询时,就代表预计扫描的索引记录行数。

代码语言:javascript
复制
explain
    select * from items i where sell_counts >  100;

filtered

通过表条件过滤出的行数的百分比估计值。

Extra

顾名思义,Extra列是用来说明一些额外信息的,我们可以通过这些额外信息来更准确的理解MySQL到底将如何执行给定的查询语句,也是很重要的一列。主要有以下值:

  • Using index:查询的列被索引覆盖,也就是使用了覆盖索引,会很快。
  • Using where:表明使用了 where 过滤。
  • Using where Using index:查询的列被索引覆盖,但是不是索引的前导列(第一列)。
  • NULL:查询的列未被索引覆盖,并且where筛选条件是索引的前导列。即用到了索引,但还不够,需要回表(先拿到id,通过id再查一遍)
  • Using index condition:查询的列不完全被索引覆盖,where条件中是一个前导列的范围
  • Using temporary:用到了临时表,比如去重,分组。
  • Using filesort:排序列未创建索引。
  • Using join buffer (Block Nested Loop):关联查询时,当被驱动表没有索引时,MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度,也就是我们所讲的基于块的嵌套循环算法。
代码语言:javascript
复制
explain
    select * from items i left join category c
    on c.name = i.item_name;

还会有比如No tables used(没有from子句)等等。

总结

ok,EXPLAIN的所有列就已经聊完了,小结一下:

列名

含义

id

执行顺序

select_type

查询类型

table

用到的表

partitions

用到的分区

type

访问类型

possible_keys

可能用到的索引

key

真实用到的索引

key_len

索引用到的字节数

ref

与索引列匹配的值

rows

估计扫描的行数

filtered

筛选比

Extra

额外补充信息

最后

至此,成为一个江湖郎中已经不是问题,想成为sql优化的名医,还需要看下一节的移花接木之法。敬请期待!

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2022-01-05,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 一条coding 微信公众号,前往查看

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

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • EXPLAIN
  • 看懂执行计划
    • id
      • select_type
        • table
          • partitions
            • type
              • possible_keys
                • key
                  • key_len
                    • ref
                      • rows
                        • filtered
                          • Extra
                            • 总结
                            • 最后
                            相关产品与服务
                            云数据库 SQL Server
                            腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
                            领券
                            问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档