(精)mysql explain用法详解

最新推荐文章于 2024-08-17 11:26:03 发布
最新推荐文章于 2024-08-17 11:26:03 发布
阅读量528 收藏 1
点赞数
分类专栏： MySQL 文章标签： redis 数据库 mysql
原文链接：https://mp.weixin.qq.com/s?__biz=MzkwNjMwMTgzMQ==&mid=2247490262&idx=1&sn=a67f610afa984ecca130a54a3be453ab&chksm=c0ebc23ef79c4b2869dea998e413c5cbea6aeeea01ee74efc7c1a5fc228baa7beca215adf3ea&token=751314179&lang=zh_CN&scene=21#wechat_redirect
版权

慢查询 EXPLAIN 索引优化 执行计划 SQL优化
关键词由CSDN通过智能技术生成

对于互联网公司来说，随着用户量和数据量的不断增加，慢查询是无法避免的问题。一般情况下如果出现慢查询，意味着接口响应慢、接口超时等问题。如果是高并发的场景，可能会出现数据库连接被占满的情况，直接导致服务不可用。

慢查询的确会导致很多问题，我们要如何优化慢查询呢？

主要解决办法有：

  • 监控sql执行情况，发邮件、短信报警，便于快速识别慢查询sql
  • 打开数据库慢查询日志功能
  • 简化业务逻辑
  • 代码重构、优化
  • 异步处理
  • sql优化
  • 索引优化

其他的办法先不说，后面有机会再单独介绍。今天我重点说说索引优化，因为它是解决慢查询sql问题最有效的手段。

如何查看某条sql的索引执行情况呢？

没错，在sql前面加上explain关键字，就能够看到它的执行计划，通过执行计划，我们可以清楚的看到表和索引执行的情况，索引有没有执行、索引执行顺序和索引的类型等。

索引优化的步骤是：

  1. 使用explain查看sql执行计划
  2. 判断哪些索引使用不当
  3. 优化sql，sql可能需要多次优化才能达到索引使用的最优值

既然索引优化的第一步是使用explain，我们先全面的了解一下它。
 

explain介绍

先看看mysql的官方文档是怎么描述explain的：
在这里插入图片描述

  • EXPLAIN可以使用于 SELECT， DELETE， INSERT， REPLACE，和 UPDATE语句。

  • 当EXPLAIN与可解释的语句一起使用时，MySQL将显示来自优化器的有关语句执行计划的信息。也就是说，MySQL解释了它将如何处理该语句，包括有关如何连接表以及以何种顺序连接表的信息。

  • 当EXPLAIN与非可解释的语句一起使用时，它将显示在命名连接中执行的语句的执行计划。

  • 对于SELECT语句， EXPLAIN可以显示的其他执行计划的警告信息。

     

explain详解

explain的语法：

{EXPLAIN | DESCRIBE | DESC}
    tbl_name [col_name | wild]

{EXPLAIN | DESCRIBE | DESC}
    [explain_type]
    {explainable_stmt | FORCONNECTION connection_id}

explain_type: {
    EXTENDED
  | PARTITIONS
  | FORMAT = format_name
}

format_name: {
    TRADITIONAL
  | JSON
}

explainable_stmt: {
    SELECTstatement
  | DELETEstatement
  | INSERTstatement
  | REPLACEstatement
  | UPDATEstatement
}

用一条简单的sql看看使用explain关键字的效果：

explain select * from test1;

执行结果：图片从上图中看到执行结果中会显示12列信息，每列具体信息如下：

图片

说白了，我们要搞懂这些列的具体含义才能正常判断索引的使用情况。

话不多说，直接开始介绍吧。
 

id列

该列的值是select查询中的序号，比如：1、2、3、4等，它决定了表的执行顺序。

某条sql的执行计划中一般会出现三种情况：

  1. id相同
  2. id不同
  3. id相同和不同都有

那么这三种情况表的执行顺序是怎么样的呢？

1.id相同

执行sql如下：

explain select * from test1 t1 inner join test1 t2 on t1.id=t2.id

结果：
在这里插入图片描述
我们看到执行结果中的两条数据id都是1，是相同的。

这种情况表的执行顺序是怎么样的呢？

答案：从上到下执行，先执行表t1，再执行表t2。

执行的表要怎么看呢？

答案：看table字段，这个字段后面会详细解释。

2.id不同

执行sql如下：

explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);

结果：在这里插入图片描述
我们看到执行结果中两条数据的id不同，第一条数据是1，第二条数据是2。

这种情况表的执行顺序是怎么样的呢？

答案：序号大的先执行，这里会从下到上执行，先执行表t2，再执行表t1。

3.id相同和不同都有

执行sql如下：

explain
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.id=t2.mid

结果：
在这里插入图片描述

我们看到执行结果中三条数据，前面两条数据的的id相同，第三条数据的id跟前面的不同。

这种情况表的执行顺序又是怎么样的呢？

答案：先执行序号大的，先从下而上执行。遇到序号相同时，再从上而下执行。所以这个列子中表的顺序顺序是：test1、t1、

也许你会在这里心生疑问：<derived2> 是什么鬼？

它表示派生表，别急后面会讲的。

还有一个问题：id列的值允许为空吗？

答案在后面揭晓。
 

select_type列

该列表示select的类型。具体包含了如下11种类型：
在这里插入图片描述

但是常用的其实就是下面几个：

类型含义
SIMPLE简单SELECT查询，不包含子查询和UNION
PRIMARY复杂查询中的最外层查询，表示主要的查询
SUBQUERYSELECT或WHERE列表中包含了子查询
DERIVEDFROM列表中包含的子查询，即衍生
UNIONUNION关键字之后的查询
UNION RESULT从UNION后的表获取结果集

下面看看这些SELECT类型具体是怎么出现的：

  1. SIMPLE
    执行sql如下：
   explain select * from test1;

结果：
在这里插入图片描述
它只在简单SELECT查询中出现，不包含子查询和UNION，这种类型比较直观就不多说了。

  1. PRIMARY 和 SUBQUERY
    执行sql如下：
   explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);

结果：
在这里插入图片描述
我们看到这条嵌套查询的sql中，最外层的t1表是PRIMARY类型，而最里面的子查询t2表是SUBQUERY类型。

  1. DERIVED
    执行sql如下：
   explain
   select t1.* from test1 t1
   inner join (select max(id) mid from test1 group by id) t2
   on t1.id=t2.mid

结果：
在这里插入图片描述
最后一条记录就是衍生表，它一般是FROM列表中包含的子查询，这里是sql中的分组子查询。

  1. UNION 和 UNION RESULT
    执行sql如下：
   explain
   select * from test1
   union
   select* from test2

结果：
在这里插入图片描述
test2表是UNION关键字之后的查询，所以被标记为UNION，test1是最主要的表，被标记为PRIMARY。而<union1,2>表示id=1和id=2的表union，其结果被标记为UNION RESULT。

UNION 和 UNION RESULT一般会成对出现。

**此外，回答上面的问题：**id列的值允许为空吗？

如果仔细看上面那张图，会发现id列是可以允许为空的，并且是在SELECT类型为： UNION RESULT的时候。

 

table列

该列的值表示输出行所引用的表的名称，比如前面的：test1、test2等。

但也可以是以下值之一：

  • <unionM,N>：具有和id值的行的M并集N。
  • <derivedN>：用于与该行的派生表结果id的值N。派生表可能来自（例如）FROM子句中的子查询 。
  • <subqueryN>：子查询的结果，其id值为N

partitions列

该列的值表示查询将从中匹配记录的分区

type列

该列的值表示连接类型，是查看索引执行情况的一个重要指标。包含如下类型：

图片

执行结果从最好到最坏的的顺序是从上到下。

我们需要重点掌握的是下面几种类型：

system > const > eq_ref > ref > range > index > ALL

在演示之前，先说明一下test2表中只有一条数据：
图片
并且code字段上面建了一个普通索引：
图片

下面逐一看看常见的几个连接类型是怎么出现的：

  1. system
    这种类型要求数据库表中只有一条数据，是const类型的一个特例，一般情况下是不会出现的。

  2. const
    通过一次索引就能找到数据，一般用于主键或唯一索引作为条件的查询sql中，执行sql如下：

   explain select * from test2 where id=1;

结果：
图片

  1. eq_ref
    常用于主键或唯一索引扫描。执行sql如下：
   explain select * from test2 t1 inner join test2 t2 on t1.id=t2.id;

结果： 图片
此时，有人可能感到不解，const和eq_ref都是对主键或唯一索引的扫描，有什么区别？

答：const只索引一次，而eq_ref主键和主键匹配，由于表中有多条数据，一般情况下要索引多次，才能全部匹配上。
  1. ref
    常用于非主键和唯一索引扫描。执行sql如下：
   explain select * from test2 where code = '001';

结果：
图片

  1. range
    常用于范围查询，比如：between … and 或 In 等操作，执行sql如下：
   explain select * from test2 where id between 1 and 2;

结果：
图片
6. index
全索引扫描。执行sql如下：

   explain select code from test2;

结果：
在这里插入图片描述

  1. ALL
    全表扫描。执行sql如下：
   explain select *  from test2;

结果：
在这里插入图片描述
 

possible_keys列

该列表示可能的索引选择。

请注意，此列完全独立于表的顺序，这就意味着possible_keys在实践中，某些键可能无法与生成的表顺序一起使用。
在这里插入图片描述

如果此列是NULL，则没有相关的索引。在这种情况下，您可以通过检查该WHERE 子句以检查它是否引用了某些适合索引的列，从而提高查询性能。

 

key列

该列表示实际用到的索引。

可能会出现possible_keys列为NULL，但是key不为NULL的情况。

演示之前，先看看test1表结构：
在这里插入图片描述

test1表中数据：
在这里插入图片描述

使用的索引：
在这里插入图片描述

code和name字段使用了联合索引。

执行sql如下：

explain select code  from test1;

结果：
在这里插入图片描述

这条sql预计没有使用索引，但是实际上使用了全索引扫描方式的索引。
 

key_len列

该列表示使用索引的长度。上面的key列可以看出有没有使用索引，key_len列则可以更进一步看出索引使用是否充分。不出意外的话，它是最重要的列。
在这里插入图片描述

有个关键的问题浮出水面：key_len是如何计算的？

决定key_len值的三个因素：

1.字符集

2.长度

3.是否为空

常用的字符编码占用字节数量如下：
在这里插入图片描述

目前我的数据库字符编码格式用的：UTF8占3个字节。

mysql常用字段占用字节数：

字段类型占用字节数
char(n)n
varchar(n)n + 2
tinyint1
smallint2
int4
bigint8
date3
timestamp4
datetime8

此外，如果字段类型允许为空则加1个字节。

上图中的 184是怎么算的？： 184 = 30 * 3 + 2 + 30 * 3 + 2

再把test1表的code字段类型改成char，并且改成允许为空：
在这里插入图片描述

执行sql如下：

explain select code  from test1;

结果：
在这里插入图片描述

怎么算的？： 183 = 30 * 3 + 1 + 30 * 3 + 2

还有一个问题：为什么这列表示索引使用是否充分呢，还有使用不充分的情况？

执行sql如下：

explain select code  from test1 where code='001';

结果：
在这里插入图片描述

上图中使用了联合索引：idx_code_name，如果索引全匹配key_len应该是183，但实际上却是92，这就说明没有使用所有的索引，索引使用不充分。

计算mysql explain中key_len流程讲析
 

ref列

该列表示索引命中的列或者常量。
执行sql如下：

explain select *  from test1 t1 inner join test1 t2 on t1.id=t2.id where t1.code='001';

结果：
在这里插入图片描述
我们看到表t1命中的索引是const(常量)，而t2命中的索引是列sue库的t1表的id字段。
 

rows列

该列表示MySQL认为执行查询必须检查的行数。
在这里插入图片描述
对于InnoDB表，此数字是估计值，可能并不总是准确的。
 

filtered列

该列表示按表条件过滤的表行的估计百分比。最大值为100，这表示未过滤行。值从100减小表示过滤量增加。
在这里插入图片描述
rows显示了检查的估计行数，rows× filtered显示了与下表连接的行数。例如，如果 rows为1000且 filtered为50.00（50％），则与下表连接的行数为1000×50％= 500。
 

Extra列

该字段包含有关MySQL如何解析查询的其他信息，这列还是挺重要的，但是里面包含的值太多，就不一一介绍了，只列举几个常见的。

  1. Impossible WHERE

    表示WHERE后面的条件一直都是false，

    执行sql如下：

   explain select code  from test1 where 'a' = 'b';

结果：
在这里插入图片描述

  1. Using filesort

    表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。

    执行sql如下：

   explain select code  from test1 order by name desc;

结果：
在这里插入图片描述

这里建立的是code和name的联合索引，顺序是code在前，name在后，这里直接按name降序，跟之前联合索引的顺序不一样。

  1. Using index
    表示是否用了覆盖索引，说白了它表示是否所有获取的列都走了索引。
    在这里插入图片描述
    上面那个例子中其实就用到了：Using index，因为只返回一列code，它字段走了索引。

  2. Using temporary
    表示是否使用了临时表，一般多见于order by 和 group by语句。

    执行sql如下：

   explain select name  from test1 group by name;

结果：
在这里插入图片描述

  1. Using where
    表示使用了where条件过滤。

  2. Using join buffer
    表示是否使用连接缓冲。来自较早联接的表被部分读取到联接缓冲区中，然后从缓冲区中使用它们的行来与当前表执行联接。
     

索引优化的过程

1.先用慢查询日志定位具体需要优化的sql

2.使用explain执行计划查看索引使用情况

3.重点关注：
​ key（查看有没有使用索引）
​ key_len（查看索引使用是否充分）
​ type（查看索引类型）
​ Extra（查看附加信息：排序、临时表、where条件为false等）
一般情况下根据这4列就能找到索引问题。

4.根据上1步找出的索引问题优化sql

5.再回到第2步

Jaemon
关注
专栏目录
MySQL的EXPLAIN用法
m0_71777195的博客
08-17 930
引言：在处理大量数据的情况下，优化查询语句的性能是至关重要的。为了帮助开发人员优化查询，MySQL提供了一个非常有用的工具，即EXPLAIN。EXPLAIN是MySQL用于分析查询语句执行计划的命令。通过分析查询语句的执行计划，开发人员可以了解到查询是如何被MySQL引擎处理的，可以发现查询语句的性能瓶颈，进而进行优化。一、EXPLAIN语法其中，table_name是查询的表名，condition是查询的条件。需要注意的是，EXPLAIN只能用于SELECT查询语句。
【MySQL】explain 用法详解
Sunnyside_
04-20 1844
【MySQL】explain 用法详解   explain命令主要来查看SQL语句的执行计划，查看该SQL语句有没有使用索引，有没有做全表扫描等。它可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理用户的SQL语句。 文章目录【MySQL】explain 用法详解一、explain能干什么二、如何使用explain三、explain各字段的含义3.1 id3.2 select_type3.3 table3.4 type3.5 possible_keys3.6 key3.7 key_len3.8
mysql查询语句优化命令mysql explain
wengyupeng 蜗牛一步一步向前。。。
08-28 2241
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 使用方法，在select语句前加上explain就可以了： 如：explain select surname,first_name form a,bwherea.id=b.id 分析结果形式如下： table | type | possible_keys |...
MySQL中的EXPLAIN的详解
最新发布
SHI940637335的博客
08-17 2504
是一种优化技术，允许 MySQL 在存储引擎层面应用部分。
mysql explain用法
weixin_34326429的博客
07-20 98
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。使用方法，在select语句前加上explain就可以了，如： explain select * from statuses_status where id=11; 创建测试表： CREATE TABLE people( id int auto_in...
MySQL的explain使用详解
qq_42133326的博客
02-28 2170
通过本篇文章了解MySQL中explain的用法和执行结果中每个字段的含义以及具体使用场景，explain工具有助于我们发现sql语句中的潜在问题，给sql语句的优化指明了一定的方向。
MySQL高级知识（四）——Explain
weixin_30813225的博客
06-22 227
前言：explain（执行计划），使用explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句。explain主要用于分析查询语句或表结构的性能瓶颈。 注：本系列随笔如无特殊说明都MySQL版本都为5.7.22。 1.explain的作用 通过explain+sql语句可以知道如下内容： ①表的读取顺序。（对应id） ②数据读取操作的操作类型。（对应s...
mysql中explain用法详解
09-10
总之，掌握`EXPLAIN`的使用是提升MySQL查询性能的关键步骤，它让我们能够深入理解查询的执行过程，为数据库优化提供有力的支持。在开发和维护数据库系统时，定期使用`EXPLAIN`分析复杂查询，可以有效避免不必要的...
MySQL中EXPLAIN命令详解
01-19
使用方法，在select语句前加上explain就可以了： 如： mysql> explain select * from kt_course order by create_time desc; +----+-------------+-----------+------+---------------+------+---------+------+---...
详解 MYSQL 中 EXPLAIN 用法
PJ码匠人
12-07 4387
执行顺序4：id列为1，表示是UNION里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为,表示查询结果来自一个衍生表，其中derived3中的3代表该查询衍生自第三个select查询，即id为3的select。表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。子查询得到结果的d1临时表，子查询条件为id = 1 常量，type是const，id为1的相当于只查询一条记录，type为system。
MySQL explain 使用
谈谈1974
01-10 1173
文章目录1. explain 的作用2. explain 输出详解2.1 优先级-id2.2 查询类型-select_type 1. explain 的作用 2. explain 输出详解 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE user NULL ALL NULL NU...
mysql 数据库优化 之explain
u012762054的博客
07-04 1949
mysql查询语句分析 explain/desc用法explain或desc显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。explain 数据表 或 desc 数据表显示数据表各字段含义explain sql 或desc sql显示sql执行效率使用方法，在select语句前加上explain就可以了，如：1explain select...
MySql中explain的用法
zy763402261的博客
12-13 1098
explain命令展示MySQL如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 explain命令使用 只需要在select前面加上EXPLAIN即可，如： explain select * from table_name where col_name = XX 注：explain只对select查询做解释，insert、update、delete不会 explain列的解释 id:select标识符，即selct查询序列号 select_type:
mysql中explain使用说明
weixin_43401380的博客
08-14 3486
mysql中确定sql是否需要优化的方式很多，常见的就是看sql的执行时间，一般的sql执行的正常查询时间是在毫秒级，几十毫秒或是几百毫秒(数据在千万级别的除外，暂时没有处理过此处不做讨论)，执行时间超过此时间段的就需要进行优化了。这也是本文的重点说明的内容，将具体讲述执行计划中每个字段的含义。Using index condition && using where:使用索引查询，sql语句的where子句查询条件字段存在非同一索引字段，且开启索引下推功能，需要回表查询即可拿到结果。...
mysql的Explain使用
wan_Szw的博客
11-16 724
Explain工具介绍 使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是 执行这条SQL 注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中 Explain分析示例 参考官方文档 点这里 示例表： DROP TABLE IF EXISTS `actor`; CREATE TABLE `actor` ( `id`
Mysql中explain的用法详解
总结分享
06-12 2534
explain关键字可以模拟MySQL优化器执行SQL语句，可以很好的分析SQL语句或表结构的性能瓶颈。
Mysql: explain的用法
小小臭臭的博客
06-02 304
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句 explain select * from t_user_info t_u, t_user_grade t_g where t_u.USER_NAME = t_g.user_name explain结果解析 id SELECT识别符。这是SELECT的查询序列号 select_type SELECT类型,可以为以下任何一种: SIMP...
mysqlexplain使用
07-28
mysqlexplain是一个MySQL数据库中的关键字，用于查询优化和性能调优。它用于分析和解释查询语句的执行计划，以便开发人员可以更好地了解查询的执行方式和性能瓶颈。 使用mysqlexplain时，你需要在查询语句前加上EXPLAIN关键字，如下所示： EXPLAIN SELECT * FROM table_name WHERE condition; 执行该查询后，MySQL将返回一个描述查询执行计划的结果集。这个结果集包含了查询的各个步骤、使用的索引、扫描的记录数等信息，帮助你分析和评估查询的性能。 通过解析EXPLAIN的结果，你可以识别潜在的性能问题，并根据需要进行索引优化、查询重写等操作，以提高查询的效率和性能。 注意：mysqlexplain只是一个用于分析查询执行计划的工具，它并不能直接优化查询。你需要根据分析结果来调整查询语句或数据库结构来优化性能。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值