MySQL中的Explain性能分析工具

逃离我推掉我的手 2023-01-17 10:48 127阅读 0赞

使用explain 不会对数据库进行修改，例如explain insert … 。

#### explain关键字是MySQL中的sql性能分析工具。 ####

很多时候会遇到java开发的后台接口响应的比较慢。形象的比喻就是点击一个操作后会发现浏览器一直在转圈圈。

这种转圈圈的情况实际上有很多种可能（例如网络问题、服务器被攻击、本身接口的问题）。

本篇内容主要讲解一下SQL本身的性能问题。

--------------------

当我们写完一条sql的时候，有些时候sql比较复杂可能会出现很多嵌套（可能这套sql本身就有几十行甚至上百行）  
这种时候如果sql功底不够深厚，那么很可能写出来的sql本身查询就很慢，自然影响了整体系统的响应时间。

--------------------

此前我们学一下sql的性能分析工具Explain关键字  
如下是explain返回的内容，有：`id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra`

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxODEzMjA4_size_16_color_FFFFFF_t_70]

#### id 表示加载的顺序（编号越大越先执行） ####

查询的id，编号越大的越先执行，如果相同id则是由上而下（按照sql中定义的顺序）

#### select\_type（查询的类型） ####

值可以是

1.  Primary 主查询，最外一层sql
2.  Simple 简单的select查询，查询`不含子查询 和 union` （理解为单表查询）
3.  SubQuery 子查询
4.  Derived 衍生表（`临时表`）在from列表中包含的子查询会标记为Derived（`简单的理解为查询产生的临时表，再次查询这张临时表就会标记为derived`）。如果出现derived2表示id为2的查询产生的临时表。
5.  Union 第2个Select出现再Union之后会被标记为Union
6.  Union Result 表示union合并的结果操作

#### table（查询那张表） ####

表示用到了那张表。

#### type（查询的类型） ####

type的结果有（`表示查询的类型，越前面效率越高`）

常用的顺序  
`system > const > eq_ref > ref > range > index > all`

完整的顺序  
`null > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > range > index > all`

--------------------

1.  system：表只有一行记录（`等于系统表`），这种情况几乎不会出现，因为一条记录没必要存数据库，直接硬编码
2.  const（`主键 或 unique索引`）：表示通过索引一次性就找到了，例如主键通过索引一次性就找到了，这种情况就是const，同理不重复的列的索引也是const类型的，能够一次性找到
3.  eq\_ref （`唯一性索引 1对1 匹配、类似于 主键对主键`）,类似于关联查询（主键对主键）`t1.id=t2.id`这种情形
4.  ref （`主键 对 外键`）返回匹配某个单独值的所有行
5.  range（`部分扫描`）常见的就是使用between a and b，实际上就是>=a且<=b。in也可以做到range。或者条件运算符：>、< 等运算符。
6.  index（`全索引扫描`）遍历索引树。
7.  all（`全表扫描`） 表示全表扫描，如果表达到100w数据一定要优化

--------------------

在实际开发过程中扫描的范围从index 到 ref 都是可以的。

#### possible\_keys（可能用到的索引） ####

可能用到的索引，但不一定被使用

#### key（实际用到的索引） ####

实际查询中用到的索引（不一定被包含于possible\_keys中的索引哦）

#### key\_len（越短越好） ####

表示索引中使用的字节数。通过列计算查询中使用的索引的长度。

#### rows（扫描了多少行） ####

实际上有多少行被扫描

#### extra（额外重要的信息） ####

extra会有如下内容

1.`using filesort` 内部进行一次排序（很耗性能，出现这个，如果表数据量很大就需要进行优化）  
2. `using temporary` 产生了临时表（先拷贝再查询、用完后还要删，特别耗性能，数据量很 大时一定要进行优化，否则非常慢）  
3. `using index` 使用索引（非常好的情况，避免了访问表的数据行）  
4. using where 使用了过滤  
5. using join buffer 使用了连接缓存（可以调大缓存的内存）  
6. impossible where （where子句的值总是false，不能用来获取任何数据）  
7. select tables optimized away （再没有group by子句的情况下，基于索引优化min/max操作或者对于MyISAM存储引擎优化count(\*)操作）  
9. distinct （优化distinct操作）

--------------------

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQxODEzMjA4_size_16_color_FFFFFF_t_70]: /images/20221021/8221011e0dea4fcf91ce73cddac00b8b.png