【mysql】记一次用explain优化sql过程

最新推荐文章于 2023-09-22 10:47:15 发布
最新推荐文章于 2023-09-22 10:47:15 发布
阅读量257 收藏
点赞数
分类专栏： 数据库 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wphero/article/details/107773317
版权

情景再现

接手别人项目，发现一个会员分页列表查询很慢，估计3-4s，sql大致如下：

SELECT
	a.*,
	( SELECT v.visit_time FROM tb_visit v WHERE v.member_id = a.id ORDER BY v.visit_time DESC LIMIT 1 ) AS visit_time,
	( SELECT SUM( j.money ) FROM tb_consumption j WHERE j.member_id = a.id ) AS money
FROM
	tb_member a
	LEFT JOIN tb_branch b ON a.branch_id = b.id
	LEFT JOIN tb_city c ON a.city_id = c.id
	LEFT JOIN tb_visit d ON a.id = d.member_id 
WHERE
	a.is_delete = '1' 
	AND a.shop_id = '1df5c9e33996427e98e067e16b996d40' 
GROUP BY
	a.id 
ORDER BY
	a.col1 ASC,
	a.create_time DESC 
	LIMIT 0,10

耗时3s，给人体验非常不好，恰好看到运行按钮旁有个“解释”功能

运行效果如下


在没详细了解explain之前看到这么多“all”大致也该明白

Explain详解

explain 命令可查看该sql有没有用上索引，有没有全表扫描，以及优化器采用何种查询策略等等。

字段详解

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传unmg-Bv2RYkzi-1596418523110)(img-unsZ0Ilc-15964184436956)]

一、id

这是最好理解的，就是SQL执行的顺序的标识,SQL从大到小的执行。
id相同时，执行顺序由上至下。
如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

二、select_type

表示查询的类型
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-miSIc7Zj-1596418451372)(http://47.94.3.87:28083/upload/20200731_17370480.png)]

三、table

表示对应行正在访问哪一个表，表名或者别名

四、type（重要）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UbPekZm2-1596418451374)(http://47.94.3.87:28083/upload/20200731_17384173.png)]

五、possible_keys

指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询

六、Key（重要）

实际使用的索引，如果为NULL，则没有使用索引。
查询中如果使用了覆盖索引，则该索引仅出现在key列表中

七、key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
不损失精确性的情况下，长度越短越好

八、ref

显示索引的那一列被使用了，如果可能，是一个常量const。

九、rows（重要）

估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数

十、Extra

该列包含MySQL解决查询的详细信息,有以下几种情况：

  • Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
  • Using temporary（效率低）：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
  • Using filesort（效率低）：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
  • Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
  • Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
  • Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
  • No tables used：Query语句中使用from dual 或不含任何from子句

优化方法

1、select_type

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HCJlIgKr-1596418451376)(http://47.94.3.87:28083/upload/20200731_18032349.png)]
select_type 出现 DEPENDENT SUBQUERY，根据table找到sql语句：[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dlsGVyuX-1596418451378)(http://47.94.3.87:28083/upload/20200731_17555777.png)]
这两个子查询sql用于获取会员的消费总额和到访记录，根据上文解释：子查询的第一个SELECT，取决于外面的查询，也就是说id为2、3的子查询取决于di=1，table=a外层主查询，它意味先执行第一个sql，得到rows=107的一个大结果集，然后大结果集的每一条记录再与子查询sql组成新sql，等于说要执行107*2次。
考虑到此处业务不是很复杂，把这两项sql剥离出去，单独写个两个sql，去掉这两个sql的运行结果：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-FrnMtRRB-1596418451380)(http://47.94.3.87:28083/upload/20200731_18111665.png)]
实际业务测试也缩减到0.08s（原来3s左右）

2、type

效率由大到小：NULL > system > const > eq_ref > ref > range > index > ALL
######（1） ALL
可以看到explain后有3处的type都是ALL，all，全表扫描，是一种非常暴力和原始的查找方法，非常的耗时而且低效。用all去查找数据就好比这样的一个情形：S学校有俩万人，我告诉你你给我找到小明，然后你怎么做呢！你当然是把全校俩万人挨个找一遍，即使你很幸运第一个人便找到了小明，但是你仍然不能停下，因为你无法确认是否有另外一个小明存在，直到你把俩万人找完为止。所以，基本所有情况，我们都要避免这样类型的查找，除非你不得不这样做。
######（2） index
这种连接类型只是另外一种形式的全表扫描，只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据，和all相比，他们都是取得了全表的数据，而且index要先读索引而且要回表随机取数据，因此index不可能会比all快（取同一个表数据），但为什么官方的手册将它的效率说的比all好，唯一可能的原因在于，按照索引扫描全表的数据是有序的。这样一来，结果不同，也就没法比效率的问题了。

（3）range

range指的是有范围的索引扫描，相对于index的全索引扫描，它有范围限制，因此要优于index。关于range比较容易理解，需要记住的是出现了range，则一定是基于索引的。同时除了显而易见的between，and以及’>’,’<'外，in和or也是索引范围扫描。

id主键：唯一索引、主键索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Bxzcc1lW-1596418451381)(http://47.94.3.87:28083/upload/20200731_22514834.png)]

name:无索引

(4)ref

使用了非主键和唯一的索引。其实，意思就是虽然使用了索引，但该索引列的值并不唯一，有重复。这样即使使用索引快速查找到了第一条数据，仍然不能停止，要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表，因为索引是有序的，即便有重复值，也是在一个非常小的范围内扫描。下面为了演示这种情形，给会员表中的name列添加一个普通的key（值允许重复）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YaNRKm7J-1596418451385)(http://47.94.3.87:28083/upload/20200731_23062529.png)]

（5）ref_eq[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZmNKSpl9-1596418451386)(http://47.94.3.87:28083/upload/20200731_23162322.png)]

ref_eq 与 ref相比牛的地方是，它知道这种类型的查找结果集只有一个？什么情况下结果集只有一个呢！那便是使用了主键或者唯一性索引进行查找的情况，比如根据学号查找某一学校的一名同学，在没有查找前我们就知道结果一定只有一个，所以当我们首次查找到这个学号，便立即停止了查询。这种连接类型每次都进行着精确查询，无需过多的扫描，因此查找效率更高，当然列的唯一性是需要根据实际情况决定的。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ViMCAV03-1596418451387)(http://47.94.3.87:28083/upload/20200731_23163074.png)]

（6）const

查找主键索引，返回的数据至多一条（0或者1条）。 属于精确查找，通常情况下，如果将一个主键放置到where后面作为条件查询，mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化，这个取决于优化器。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SOz7l6rN-1596418451388)(http://47.94.3.87:28083/upload/20200731_2323094.png)]

3、Extra


根据extra显示Using temporary; Using filesort，临时表和文件排序，两个都没用到索引，查询效率低；其次，查询字段不在索引上，没有使用覆盖索引（SQL只需要通过索引就可以返回查询所需要的数据，而不必通过二级索引查到主键之后再去查询数据。），需要通过索引回表查询；也有数据分布的原因。
添加一个联合索引便可以避免回表和文件排序，利用覆盖索引提升查询速度，同时利用索引完成排序。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传i7mg-YXaDkfrX-1596418534802)(img-i7nzBZ0M-1596418445191)(http://47.94.3.87:28083/upload/20200731_23523068.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JK7qDI2s-1596418544385)(img-ai1Pxvsa-1596418451393)

吃鱼的小朋
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL——通过Explain执行计划优化SQL(索引)
专注写bug
06-01 224
文章目录简介测试数据库表创建索引优化注意点 简介 上一篇博客中，讲述了MySQL给开发者提供的Explain的用法，以及执行指定的sql后，获取到数据的每条列各自的含义信息。 具体如何对SQL进行性能优化和什么情况下会导致SQL索引失效呢，下面具体做相关说明。 测试数据库表创建 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名
MySQL SQL语句优化explain关键字
12-14
MySQL中的`EXPLAIN`关键字是分析SQL查询性能的关键工具，它可以帮助我们理解查询的执行计划，以便进行有效的优化。本篇文章将深入探讨`EXPLAIN`的使用方法以及如何通过索引来提升查询效率。 首先，`EXPLAIN`关键字...
MySQL的Explain 及SQL优化的一般步骤
jsxztshaohaibo的博客
05-09 315
Explain 使用 explain select * from tableA 查看SQL语句的执行计划时，需要重点关注type、rows、filtered、extra。 Type 由上至下，效率越来越高 ALL：：全表扫描。从硬盘中读取 index：：全表扫描。跟ALL的区别是： 使用索引进行遍历索引树读取 range ：：使用索引范围扫描。就是针对一个有索引的字段，在指定范围中检索数据常用语<,<=,>=,between,in等操作 re...
如何使用explain关键字优化SQL
JohnDeng520的博客
07-19 391
前言 通常我们使用SQL查询是如何判断SQL的健康程度呢?我们会根据SQL查询时间来判断快慢，还有别的方法吗？ 简介 explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句 使用 EXPLAIN SELECT * FROM tb_account_user WHERE ID=1-------------------------------------------------------------------------------
explain分析sql语句查询优化
qq_41842356的博客
12-24 310
索引类似大学图书馆建书目索引，可以提高数据检索的效率，降低数据库的IO成本。MySQL在300万条记录左右性能开始逐渐下降，虽然官方文档说500~800w记录，所以大数据量建立索引是非常有必要的。MySQL提供了Explain，用于显示SQL执行的详细信息，可以进行索引的优化。 一、导致SQL执行慢的原因 1.硬件问题。如网络速度慢，内存不足，I/O吞吐量小，磁盘空间满了等。 2.没有索引或者索引失效。（一般在互联网公司，DBA会在半夜把表锁了，重新建立一遍索引，因为当你删除某个数据的时候，索引的树结构就不
mysql sql语句优化 explain_使用explain来分析SQL语句实现优化SQL语句
weixin_34170028的博客
02-01 136
用法：explain sql作用：用于分析sql语句mysql> explain select * from quser_1 where loginemail = "quctest2124@163.com";+----+-------------+---------+------+-----------------+-----------------+---------+-------+--...
Mysql查询最近一条记录的sql语句(优化篇)
12-16
本文将探讨如何在MySQL中查询最近一条记录，并提供不同级别的查询优化策略。 首先，我们来分析一下基础的SQL语句。一个简单的查询最近一条记录的方法是通过`ORDER BY`和`LIMIT`结合，如下所示： ```sql SELECT * ...
一次Mysql使用IN大数据量的优化记录
01-19
mysql版本号是5.7.28，表A有390W条记录，使用InnoDB引擎，其中varchar类型字段mac已建立索引，索引方法为B-tree。B表仅有5000+条记录。 有一条SQL指令是这样写的： SELECT * FROM A WHERE mac IN(aa:aa:aa:aa:aa:aa...
MySql中如何使用 explain 查询 SQL 的执行计划
09-09
在MySQL中，`EXPLAIN`命令是一个强大的工具，用于分析和理解SQL查询的执行计划。通过使用`EXPLAIN`，我们可以深入了解查询优化器如何处理SQL语句，从而优化查询性能。下面我们将详细介绍如何使用`EXPLAIN`以及如何...
MySQL优化 – EXPLAIN
12-14
MySQL优化中的`EXPLAIN`命令是数据库管理员和开发者用来分析SQL查询执行计划的重要工具。它可以帮助我们理解查询是如何在数据库中执行的，从而找到潜在的性能瓶颈并进行优化。下面我们将详细讨论`EXPLAIN`输出的各个...
Mysql Sql 优化之 Explain
Jerry
05-01 908
Explain工具就是用来分析某个慢查询执行情况的工具。通过在select 语句前加上explain 关键字，然后执行就会得到某个sql 执行计划信息，通过分析执行计划，我们就可以确定是否需要对其进行优化。
【转帖】mysql优化（三）–explain分析sql语句执行效率
cleble的专栏
11-29 220
xplain分析sql语句执行效率
mysql sql语句优化 explain_mysql优化:explain分析sql语句执行效率
weixin_36202127的博客
01-28 94
Explain命令在解决数据库性能上是第一推荐使用命令，大部分的性能问题可以通过此命令来简单的解决，Explain可以用来查看SQL语句的执行效 果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。Explain语法：explain select … from … [where …]例如：explain select * from news;输出：+----+-------------+...
MYSQL执行计划详解（explain）及SQL优化
zhaozhifu11的博客
10-19 463
MYSQL执行计划详解（explain）及SQL优化 对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，Mysql提供了explain 命令可以用来分析select 语句的运行效果。 explain select * from account; 执行计划包含的信息 不同版本的Mysql和不同的存储引擎执行计划不完全相同，但基本信息都差不多。mysql执行计划主要包含以下信息: id 由一组数字组成。表示一个查询中各个子查询的执行顺序; id相同执行顺序由上至下。 id不同，id
explain优化sql语句
随波主流
06-22 174
1.explain是用来对select语句查询效率查看的语句，通过explain我们可以优化自己的sql语句。执行完explain后有几个关键的字段， type: ref key: yExtra: Using where type:表示搜索的类型，好坏依次为system &gt; const &gt; eq_ref &gt; ref &gt; fulltext &gt; ref_or_nul...
explain分析执行计划
qq_40236927的博客
09-27 122
explain分析执行计划 通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句 的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。 explain select * from tb_item where id = 1; explain select * from tb_item where title = '阿尔卡特 (OT-979) 冰川白 联通3G手机3'; 字段 含义 id sel
利用Explain优化sql 详解
最新发布
JustDI0209的博客
09-22 1457
Explain关键字是Mysql中的常用，通常都会使用Explain来，从而快速的找出sql的问题所在。在讲解Explain之前首先创建需要的，执行后可以看到执行的结果如下：可以看到这里有12个字段那个且都有对应的值，这就是explain的执行计划，能看懂这个执行计划，你离精通sql优化就不远了，下面就来详细的介绍这12个字段分别表示什么意思。
explain调优
西魏陶渊明的博客
01-04 633
作者: 西魏陶渊明 博客: https://blog.springlearn.cn/ (opens new window) 西魏陶渊明 莫笑少年江湖梦，谁不少年梦江湖 这篇文章主要讲 explain 如何使用，还有 explain 各种参数概念，之后会讲优化 # 一、Explain 用法 explain模拟Mysql优化器是如何执行SQL查询语句的，从而知道Mysql是如何处理你的SQL语句的...
sql语句的分析（explain）与优化
weixin_56320090的博客
05-21 1352
文章目录前言一、Sql语句优化步骤1.定位慢的sql语句2.explain分析执行计划(1)explain之id<重点>(2)explain之select_type<重要>(3)explain之type<重点>(4)explain之possible_keys 和 key 、key_len(5)explain 之 rows(6)explain 之 extra3.优化sql语句(1)优化inset语句(2)优化order by语句环境准备：两种排序方式Filesort 的优化
MySQL数据库Explain深度解析：SQL执行优化指南
在SQL优化过程中，Explain是MySQL提供的一种强大的工具，它可以帮助我们理解查询的执行过程，识别潜在的性能瓶颈。下面我们将详细解释Explain返回的各种字段及其含义。 1. **Id** - Id列显示的是查询中每个选择...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值