『Mysql进阶』Mysql explain详解(五)

ops/2024/10/21 4:16:18/

目录

Explain 介绍

Explain分析示例

explain中的列

1. id 列

2. select_type 列

3. table 列

4. partitions 列

5. type 列

6. possible_keys 列

7. key 列

8. key_len 列

9. ref 列

10. rows 列

11. filtered 列

12. Extra 列

Explain 介绍

     EXPLAIN 语句提供有关 MySQL 如何执行语句的信息，使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 。

        我们根据 MySQL 的慢查询去记录一些执行时间比较久的SQL语句, 然后使用explain命令来查看这些SQL语句的执行计划, 根据执行计划内容，判断该SQL语句有没有使用上索引, 有没有做全表扫描等, 进而排查出SQL执行慢的原因。

官网介绍

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

Explain分析示例

  • CREATE TABLE `actor` (`id` int(11) NOT NULL AUTO_INCREMENT,`name` varchar(45) NOT NULL,`create_time` datetime DEFAULT NULL,PRIMARY KEY (`id`),UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;INSERT INTO `actor` (`id`, `name`, `create_time`) VALUES (1,'a',NOW()), (2,'b',NOW()), (3,'c',NOW());CREATE TABLE `film` (`id` int(11) NOT NULL AUTO_INCREMENT,`actor_id` int(11) NOT NULL,`name` varchar(10) DEFAULT NULL,`create_time` datetime DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_actorid` (`actor_id`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;INSERT INTO `film` (`id`, `actor_id`, `name`, `create_time`) VALUES (1,1,'一路向东',NOW());

explain中的列

 explain select * from actor;

1. id 列

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。id列越大执行优先级越高，id相同则从上往下执行。

  • id 相同，按table列由上至下顺序执行
  • id 不同，如果是子查询，id的序号会递增，id的值越大优先级越高，越先被执行。
  • id 想通不同同时存在，id如果相同，可以认为是一组，（本组内）从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。
  • 当使用union时，id会出现NULL值，同时table列会出现<unionM,N>的值，其表示id的值为M和N的行的联合。
2. select_type 列

表示对应行是简单还是复杂的查询。

  • simple：简单查询。查询不包含子查询和union

  • primary：复杂查询中最外层的 select

  • subquery：包含在 select 中的子查询（不在 from 子句中）

  • derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表(derived的英文含义)

  • union：在 union 中的第二个和随后的 select

  •  union result: 从union表获取结果的select, UNION的结果集
  • dependent union： UNION UNIONUNION中的第二个或以后的SELECT的SELECT的SELECT语句，取决于外部查询

  •  dependent subquery:  子查询中的第一个SELECT，依赖于外部查询，表示这个subquery的查询要受到外部表查询的影响

3. table 列

这一列表示 explain 的一行正在访问哪个表。

它也可以是以下值之一：

  • <unionM，N>：行引用ID值为M和N的行的并集。
  • <derivedN>：该行引用ID值为N的行的派生表结果。派生表可以由例如FROM子句中的子查询产生。
  • <subqueryN>：该行引用ID值为N的行的具体化子查询的结果。
4. partitions 列

使用的哪些分区（对于非分区表值为null）。

5. type 列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为：

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

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

explain select min(id) from actor

NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表.

  • system: 表中只有一行数据或者是空表。等于系统表，这是const类型的特列，平时不会出现，可以忽略不计 .

  • const : 使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type 是const。其他数据库也叫做唯一索引扫描。 

explain select * from actor where id=1;

  • eq_ref: primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。在连接表，被驱动表会出现.
explain select * from  actor  a  JOIN film b on a.id=b.actor_id

  • ref : 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
explain select * from film where actor_id=1

  •  fulltext : 全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时， mysql不管代价，优先选择使用全文索引 .
  • ref_or_null : 与ref方法类似，只是增加了null值的比较。实际用的不多。
  • unique_subquery: 用于where中的in形式子查询，子查询返回不重复值唯一值
  • index_subquery: 用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复
    值，可以使用索引将子查询去重。
  • range : 范围扫描通常出现在 in(), between ,> ,= 等操作中。使用一个索引来检索给定范围的行。 
explain select * from actor where id >=1 and id <=3;

  • index_merge: 表示查询使用了两个以上的索引，最后取交集或者并集，常见 and ， or 的条件使 用了不同的索引.
  • index : 扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。(全叶子索引扫描，扫描的是二级索引) 
explain select actor_id from film;

  • ALL：即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

    explain select * from film;

6. possible_keys 列

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

7. key 列

查询真正使用到的索引

8. key_len 列

显示MySQL决定使用的索引size

key_len 计算规则:

  1. 索引字段，非NOT NULL，加1个字节。
  2. 定长字段：tinyint占1个字节、smallint占2个字节、int占4个字节、bitint占8个字节、date占3个字节、timestamp占4个字节，datetime占8个字节，char(n)占n个字符。
  3. 变长字段：varchar(n)占n个字符+2个字节。
  4. 不同的字符集，一个字符占用的字节数不同：
    • latin1编码，每个字符占用一个字节
    • gbk编码，每个字符占用两个字节
    • utf8编码，每个字符占用三个字节
    • utf8mb4编码，每个字符占用四个字节

例子

字段：phone varchar(20) DEFAULT NULL COMMENT ‘手机号’

条件：where phone=‘xxx’

通过explain查看key_len

utf8mb4编码下，key_len=83，即20*4+2+1

utf8编码下，key_len=63，即20*3+2+1

9. ref 列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

10. rows 列

显示此查询一共扫描了多少行，这个是一个估计值，不是精确的值。

11. filtered 列

表示此查询条件所过滤的数据的百分比

12. Extra 列

这一列展示的是额外信息。常见的重要值如下：

  • Using where：使用 where 语句来处理结果，表示MySQL将对InnoDB提取的结果在SQL Layer层进行过滤，过滤条件字段无索引； 

explain select * from film where name='一路向东';

  • Using index：使用覆盖索引-----不会回表

覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

explain select actor_id from film where actor_id=1;

  •  Using index condition: 用不了索引, 但用了索引下推优化,使之可以使用到索引.
explain select * from film where actor_id>=1;

  • Using temporary: mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。(先扫描出来放在临时表，然后再去重)
explain select DISTINCT name from film;

  • Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。

explain select * from film ORDER BY name;

  • Using join buffer (Block Nested Loop)：在连接查询执行过程中，当被驱动表不能有效的利用索引加快访问速度，MySQL一般会为其分配一块名叫join buffer的内存块来加快查询速度 .

http://www.ppmy.cn/ops/125480.html

相关文章

python菜鸟知识

python菜鸟知识

去除空格 str 这是 含 空格 print(f去除两端空格{str.strip()}) print(f去除左端空格{str.lstrip()}) print(f去除右端空格{str.rstrip()}) print(f去除全部空格{str.replace(" ", "")}) 方法返回对象yield yield :.join([ip, port])yield {ranking…
阅读更多...
MPI错误对照表

MPI错误对照表

00CA : no resources available&#xff0c;没有资源可用到的 　　00CB : configuration error&#xff0c;配置错误 　　00CD : illegal call&#xff0c;违法的喊声 　　00CE : module not found&#xff0c;模块不发现 　　00CF : driver not loaded&#xff0c;驱动程序不装…
阅读更多...
Python 代码使用 OpenCV 库实现了从摄像头获取视频流，并在视频中检测特定颜色区域的边缘线条

Python 代码使用 OpenCV 库实现了从摄像头获取视频流，并在视频中检测特定颜色区域的边缘线条

import cv2 import numpy as np #想和大神交朋友或想软件开发兼职接项目&#xff0c;请通过手机端搜小#程#序: "黄页小艺" 。 def extend_line_to_borders(x1, y1, x2, y2, width, height):# Compute the slope and interceptif x1 x2 or abs(x2 - x1) < 1e-6: …
阅读更多...
基于深度学习的复杂器官建模与模拟

基于深度学习的复杂器官建模与模拟

基于深度学习的复杂器官建模与模拟是一项前沿技术&#xff0c;它利用深度学习模型从大量医学图像和临床数据中提取信息&#xff0c;生成复杂器官的三维结构模型&#xff0c;并对其进行功能模拟。这项技术对于医学诊断、手术规划、药物开发和疾病研究有重要意义&#xff0c;特别…
阅读更多...
【动态规划-最长递增子序列（LIS）】【hard】力扣1671. 得到山形数组的最少删除次数

【动态规划-最长递增子序列（LIS）】【hard】力扣1671. 得到山形数组的最少删除次数

我们定义 arr 是 山形数组 当且仅当它满足&#xff1a; arr.length > 3 存在某个下标 i &#xff08;从 0 开始&#xff09; 满足 0 < i < arr.length - 1 且&#xff1a; arr[0] < arr[1] < … < arr[i - 1] < arr[i] arr[i] > arr[i 1] > … &g…
阅读更多...
手机解锁如何工作？解锁手机的顶级应用程序

手机解锁如何工作？解锁手机的顶级应用程序

解锁手机软件是指用于解锁特定运营商或网络的手机的计算机程序或工具&#xff0c;使其能够与其他运营商一起使用或在国际上使用。 当个人想要切换到不同的网络提供商或在国外旅行时使用其设备时&#xff0c;通常会使用此软件。以下是解锁手机软件的概述&#xff1a;需要注意的…
阅读更多...
Java List 的介绍与实现原理

Java List 的介绍与实现原理

什么是 List 在 Java 中&#xff0c;List 是一种有序集合&#xff0c;允许重复的元素。它是 Java Collections Framework 的一部分&#xff0c;提供了一种便捷的方式来存储和操作线性数据。常见的实现类包括 ArrayList、LinkedList 和 Vector。 实现原理 1. ArrayList Arra…
阅读更多...
selenium有多个frame页时的操作方法（5）

selenium有多个frame页时的操作方法（5）

之前文章我们提到&#xff0c;在webdriver.WebDriver类有一个switch_to方法&#xff0c;通过switch_to.frame()可以切换到不同的frame页然后才再定位某个元素做一些输入/点击等操作。 比如下面这个测试网站有2个frame页&#xff1a;http://www.sahitest.com/demo/framesTest.h…
阅读更多...
最新文章

Copyright @ 2022~2023