MySQL——explain

已于 2022-11-29 18:41:49 修改
阅读量102 收藏
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 sql
于 2022-11-29 18:34:57 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/didpip/article/details/128076500
版权

一、explain的简介

在写SQL语句的时候，我们经常会关注这些问题 ——》 “看看怎么优化这个sql”、“这条select走没走索引” … 那么我们应该怎么做呢！？
在执行SQL语句的时候，使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，了解MySQL是如何处理我们所要执行的SQL语句的，获取关于这条SQL语句的执行计划的信息，进而帮助我们分析出如何优化这条SQL语句。

注意：explain只能解释select查询，并不会对存储过程调用和insert，update，delete或其他语句做解释。但是，我们可以重写这些非select语句来利用explain。

通过explain，我们可以分析出SQL语句的这些属性：

  • 表的读取顺序
  • 数据读取操作的操作类型
  • 哪些索引可以使用
  • 哪些索引被实际使用
  • 表之间的引用
  • 每张表有多少行被优化器查询

这样的概述有点过于抽象，我们详细说说！

什么是索引？
索引（Index）是帮助MySQL高效获取表格中数据的数据结构。所以索引的本质是：一种数据结构（排好序的快速查找数据结构）。

二、explain的使用

使用方式：explain + SQL语句（select）

explain select * from t1

执行计划包含的信息：
在这里插入图片描述

1、explain之id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。id值越大，优先级越高，越先被执行。

id结果的三种情况：

  • id相同，执行顺序由上至下
    在这里插入图片描述
    加载表的顺序如上图table列所示：t1 t3 t2
  • id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
    在这里插入图片描述
    如果存在子查询，id的序号会递增，id值越大，则表示执行的优先级越高，越先被执行。
  • id相同和不同的情况，同时存在
    在这里插入图片描述
    id如果相同，可以认为是一组，执行顺序从上往下；在所有组中，id值越大，则优先级越高，越先被执行。

2、explain之select_type

常用值如下：分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
在这里插入图片描述

  • SIMPLE：简单的select查询，查询中不包含子查询或者UNION
  • PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
  • SUBQUERY：在SELECT或WHERE列表中包含了子查询
  • DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
  • UNION：若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
  • UNION RESULT：从UNION表获取结果的SELECT

3、explain之table

指的就是当前SQL语句执行的表。
在这里插入图片描述

4、explain之type

type所显示的是使用了哪种类型的查询，type包含的类型包括如下图所示的几种：
在这里插入图片描述
从最好到最差依次是：

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

一般来说，得保证查询至少达到range级别，最好能达到ref。

  • system ：表中只有一行记录（等于系统表），这是const类型的特列，平时不会出现。
  • const ：表示通过索引一次就找到了，const用于比较primary key
    或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。

So：首先进行子查询得到一个结果的d1临时表，子查询条件为id = 1 是常量，所以type是const，id为1的 相当于只查询一条记录，所以type为system。

在这里插入图片描述

  • eq_ref ：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描.
  • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

So：col1并不是主键或者唯一索引，只是普通索引，所以type是ref。
在这里插入图片描述

  • range： 只检索给定范围的行，使用一个索引来选择行，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
    在这里插入图片描述

  • index（Full Index Scan）：Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
    在这里插入图片描述

  • all（Full Table Scan）：将遍历全表以找到匹配的行

5、explain之possible_key 和 key

  • possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
  • key ：实际使用的索引，如果为NULL，则没有使用索引（可能原因包括没有建立索引或索引失效等）。
    情况一：可能用到了索引，实际没用到在这里插入图片描述
    情况二：可能用到了索引，实际也用到了
    在这里插入图片描述
    情况三：可能没用到索引，实际却用到了。典型代表是覆盖索引。
    在这里插入图片描述
    在这里插入图片描述

6、explain之key_len

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

7、explain之ref

显示索引的哪些列或常量被用于查找索引列上的值。如果可能的话，最好是一个常数。
在这里插入图片描述
So：对于t2来说，没有限制条件，走的是全表查询；对于t1来说，t1.col1取的是t2.col1的值，t1.col2取的是常量“ac”，t1表的索引idx_col1_col2被充分利用。

8、explain之rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，值越小越好。
在这里插入图片描述

9、explain之Extra

数据准备：
在这里插入图片描述
索引：
在这里插入图片描述

（1）Using filesort
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
在这里插入图片描述

（2）Using temporary
由于排序没有走索引、使用union、子查询连接查询、使用某些视图等原因，创建了内部临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。
在这里插入图片描述

（3）Using index / Using where
表示相应的select操作中使用了覆盖索引（Covering Index），可以直接读取索引文件中的值，避免了回表查询，效率不错。如果同时出现using where，表明索引被用来执行索引键值的查找。
在这里插入图片描述

（4）Using join buffer
表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

（5）impossible where
where子句的值总是false，不能用来获取任何元组。
在这里插入图片描述

（6）select tables optimized away
在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

（7）distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作

三、索引失效和索引优化

数据准备：
在这里插入图片描述

建立索引：idx_staffs_nameAgePos
在这里插入图片描述

1、全值匹配我最爱
在这里插入图片描述

2、最佳左前缀法则（带头索引不能死，中间索引不能断）

  • 带头索引死：
    在这里插入图片描述
  • 中间索引断：带头索引生效，其他索引失效在这里插入图片描述

3、不要在索引上做任何操作（计算、函数、自动/手动类型转换），不然会导致索引失效而转向全表扫描
在这里插入图片描述

4、mysql存储引擎不能继续使用索引中范围条件（bettween、<、>、in等）右边的列
在这里插入图片描述

5、**尽量使用覆盖索引（只查询索引的列（索引列和查询列一致）），减少select ***
在这里插入图片描述

6、索引字段上使用（ != 或者 < >）判断时，会导致索引失效而转向全表扫描
在这里插入图片描述

7、索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描（最左前缀匹配原则）
在这里插入图片描述
由结果可知，like以通配符结束相当于范围查找，索引不会失效。与范围条件（bettween、<、>、in等）不同的是：不会导致右边的索引失效。

如何解决like ‘%字符串%’时，索引失效问题？使用覆盖索引可以解决。
在这里插入图片描述

8、索引字段是字符串，但查询时不加单引号，会导致索引失效而转向全表扫描
在这里插入图片描述

9、OR 会导致联合索引失效
在这里插入图片描述

那些情况适合建索引？

  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段，外键关系建立索引
  • 单键/组合索引的选择问题
  • 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度（因为索引是排好序的）
  • 查询中统计或者分组字段
  • 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
  • Where条件里用不到的字段不创建索引

建索引的一般性建议：

  • 对于单键索引，尽量选择针对当前query过滤性更好的索引。
  • 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  • 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引。
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

优化真经：

全值匹配我最爱， 最左前缀要遵守；

带头大哥不能死， 中间兄弟不能断；

索引列上少计算， 范围之后全失效；

LIKE 百分写最右， 覆盖索引不写 *；

不等空值还有 OR， 索引影响要注意；

VAR 引号不可丢， SQL 优化有诀窍。

Kill_Weakness
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
MySQL——数据库表.rar
08-04
MySQL提供了许多性能优化手段，如查询优化（通过EXPLAIN分析查询计划）、配置参数调整、InnoDB存储引擎的缓冲池、索引优化以及使用慢查询日志分析性能问题。 12. **安全性**： MySQL支持用户权限管理，通过GRANT...
Mysql Explain 详细介绍
01-21
Mysql Explain 这里做一个资料的全面整理。 一.语法 explain 例如: explain select * from t3 where id=3952602; 二.explain输出解释 +—-+————-+——-+——-+——————-+———+———+——-+——+——...
MySQL——EXPLAIN用法详解
醉酒的戈多的专栏
10-24 6683
MySQL——EXPLAIN用法详解 mysql性能优化
MySql——Explain关键字
weixin_41793026的博客
05-28 234
做MySQL性能优化的时候第一步就是使用Explain关键字查看执行计划，让我们知道MySQL是如何执行我们的SQL语句的，从而我们可以根据这个对我们的SQL语句进行优化。 Explain能干嘛 1、查询表的读取顺序 2、查询数据读取操作的操作类型 3、哪些索引可以被使用 4、哪些索引实际被使用 5、表之间的引用 6、每张表有多少行被优化器优化 使用 使用Explain关键字很简单只需要在SQL语句前加入Explain即可 比如:explain select title from article 可以得到：
[MySQL总结] Explain详解、索引最佳优化
qq_45061342的博客
06-18 1145
like KK%相当于=常量，%KK和%KK% 相当于范围。
MySQL——explain查看sql性能
qq_40987442的博客
08-13 660
MySQL——explain查看sql性能MySQL查看sql性能explain命令explain命令value解释 一、id二、select_type三、table四、type五、possible_keys六、Key七、key_len八、ref九、rows十、Extra总结： MySQL查看sql性能 explain命令 explain显示了select语句的查询计划、如何使用索引，以及相关的查询分析，如果select查询比较慢，可以通过添加索引的方式来达到相应的目的。然后再通过explain查看添加的索
MySQL——Explain能看出点啥
李豆豆的博客
04-20 751
mysql explain用法和结果的含义 explain或desc显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 explain 数据表 或 desc 数据表 显示数据表各字段含义 explain sql 或desc sql 显示sql执行效率 explain列解释 table 输出的行所引用的表 select_typ...
mysql——explain
大白的博客
05-07 195
解析sql的执行计划便于分析sql性能，可以用explain。 用法：explain select ... 详情：https://www.cnblogs.com/xuanzhi201111/p/4175635.html https://www.cnblogs.com/nixi8/p/4574709.html 索引explain： https://blog.csdn.net/wuseyuk...
MySql —— EXPLAIN 索引处理情况与查询优化
Genius only means hard-working all one's life.
04-26 471
文章目录MySql —— EXPLAN 索引处理情况简介字段详解idSELECT_TYPETABLETYPEPOSSIBLE_KEYSKEYKEY_LENREFROWSEXTRA MySql —— EXPLAN 索引处理情况 简介 如果遇到一条SQL很慢，我们可以通过EXPLAN 命令分析SQL索引情况。 字段详解 id SELECT_TYPE TABLE TYPE POSSIBLE_KEYS K......
mysql——explain分析
mine_song的博客
06-15 365
mysql——explain分析
MYSQL——explain执行计划结果分析
weixin_42924812的博客
11-23 654
文章目录explain执行计划结果分析0、id1、select_type2、table3、partitions4、type5、possible_keys6、key7、key_len8、ref explain执行计划结果分析 在MySQL中，我们可以通过explain命令查看sql的执行情况。 获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。 例...
二、mysql——Explain工具介绍和查看mysql运行时间
lovesman的博客
08-02 1471
使用的数据库脚本 DROP TABLE IF EXISTS `actor`; CREATE TABLE `actor` ( `id` int(11) NOT NULL, `name` varchar(45) DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `actor` (`id`, `name`, `upda
MySQL索引——Explain
Chen_chenjiasheng的博客
06-27 706
数据类型 描述 DATE() 日期: 格式：YYYY-MM-DD 注释: 支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’ DATETIME() 日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’ TIMESTAMP() 时间戳: TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的描述来存储。格式：YY
mysql学习之引擎、Explain和权限的深入讲解
09-09
本篇文章将深入探讨MySQL中的两种主要引擎——InnoDB和MyISAM，以及如何利用`EXPLAIN`命令来分析SQL查询的执行计划，最后会简述MySQL的权限系统。 1. **InnoDB引擎**： - InnoDB引擎支持ACID事务，提供行级锁，...
MySQL查询性能优化
01-19
排队时间是指服务器因为等待某些资源而没有真正执行查询的时间——可能是等待 I/O 操作完成，也可能是等待行锁，等等。 扫描的行数和返回的行数理想情况下扫描的行数和返回的行数是相同的，但在实际应用中，该情况...
燕十八 封笔之作——MySQL优化.zip
05-03
【燕十八 封笔之作——MySQL优化】 燕十八，一位在IT行业内享有盛名的专家，以其深入浅出的讲解风格和丰富的实战经验在MySQL优化领域留下了深刻的印记。他的“封笔之作”聚焦于MySQL数据库的性能提升，旨在帮助...
MYSQL
maoqiwei的博客
07-23 268
1.修改student 表中年龄(sage)字段属性，数据类型由int 改变为smallint 2.为Course表中Cno 课程号字段设置索引,并查看索引 3.为SC表建立按学号(sno)和课程号(cno)组合的升序的主键索引，索引名为SC_INDEX4.创建一视图 stu info,查询全体学生的姓名，性别，课程名，成绩5.删除所有索引
MySql 主从同步会不会影响到SQL执行速度？
最新发布
weixin_44892327的博客
07-24 779
作用：负责将主库的二进制日志（binary log）数据传输到从库。工作方式：当从库连接到主库时，主库会为每个连接的从库开启一个 Binlog Dump 线程。这个线程从主库的二进制日志中读取日志记录，并将其发送给从库。Binlog Dump 线程：将二进制日志传输给从库。Binlog Dump GTID 线程（在启用 GTID 模式时）：处理 GTID 相关的复制任务。
MySQL字段设置的varchar长度小于数据长度自动截取丢弃超出的长度而不是报错？
weixin_43831997的博客
07-23 366
当MySQL数据库设置为==严格模式（STRICT_TRANS_TABLES 或 STRICT_ALL_TABLES）==时，如果尝试插入或更新超过设定长度的数据，将会导致错误，并阻止该事务进行。在默认情况下，或者在非严格模式（NO_STRICT）下，当插入长度超出 VARCHAR 字段定义的字符串时，MySQL会自动截断超出的部分，并发出一个警告，而不是错误。
mysql中explain的filtered
09-01
- *2* *3* [MySQL 优化 —— EXPLAIN 执行计划详解](https://blog.csdn.net/u014745069/article/details/104173763)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值