首页 > 数据库 >mysql中explain命令详解

mysql中explain命令详解

时间:2024-05-15 22:10:11浏览次数:44  
标签:tb explain 查询 索引 详解 key mysql ref id

前言

我们可以使用 explain 命令来查看 SQL 语句的执行计划,从而帮助我们优化慢查询。

使用

注意:使用的 mysql 版本为 8.0.28

数据准备

CREATE TABLE `tb_product2` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '商品ID',
  `name` varchar(20) DEFAULT NULL COMMENT '商品名称',
  `en_name` varchar(20) DEFAULT NULL COMMENT '商品英文名称',
  `stock` int DEFAULT NULL COMMENT '库存量',
  PRIMARY KEY (`id`),
  index `index_name`(`name`)
) ENGINE=InnoDB;

CREATE TABLE `tb_order2` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  `product_id` bigint DEFAULT NULL COMMENT '商品ID',
  `quantity` int DEFAULT NULL COMMENT '购买数量',
  `price` decimal(10,2) DEFAULT NULL COMMENT '订单总金额',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `tb_product2`( `name`, `en_name` , `stock` ) VALUES('苹果11', 'iphone11', 10); 
INSERT INTO `tb_product2`( `name`, `en_name` , `stock` ) VALUES('小米6', 'xiaomi6', 20); 
INSERT INTO `tb_order2`( `product_id`, `quantity` , `price`, `create_time`) VALUES(1, 5, 100.00, now()); 
INSERT INTO `tb_order2`( `product_id`, `quantity` , `price` , `create_time`) VALUES(2, 3, 60.00, now()); 

查询执行计划

EXPLAIN SELECT * FROM `tb_product2` WHERE id = 1;

结果为

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tb_product2 null const PRIMARY PRIMARY 8 const 1 100.00 null
EXPLAIN SELECT * FROM `tb_product2` WHERE en_name = 'xiaomi6';

结果为

id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE tb_product2 null ALL null null null null 2 50.00 Using where

字段详解

id

每次select查询都会对应一个id,它代表着SQL执行的顺序,如果id值越大,说明对应的SQL语句执行的优先级越高。

image

select_type

表示执行计划对应的查询类型,常见的查询类型主要包括普通查询、联合查询以及子查询等。

  • simple: 简单的select查询,没有union或者子查询
  • primary: 有嵌套查询时的最外层的select查询
  • derived: 用来表示包含在FROM子句的子查询中的SELECT,MySQL会递归执行并将结果放到一个临时表中。MySQL内部将其称为是Derived table(派生表),因为该临时表是从子查询派生出来的
  • union: union中的第二个或随后的select查询,不依赖于外部查询的结果集
  • dependent union: union中的第二个或随后的select查询,依赖于外部查询的结果集
  • subquery: 子查询中的第一个select查询,不依赖与外部查询的结果集
  • dependent subquery: 子查询中的第一个select查询,依赖于外部查询的结果集

table

表示要查询哪张表,当然不一定是真实的表的名称,也可能是表的别名或者临时表。

partitions

表示在进行查询时,如果对应的表存在分区表,那么这里就会显示具体的分区信息。

type

type是非常核心的属性,需要重点掌握。它表示的是当前通过什么样的方式对数据库表进行访问。

  • system: 该表只有一行(相当于系统表),数据量很小,查询速度很快,system是const类型的特例。
    image
  • const: 在进行数据查询的时候,命中了primary key或唯一索引,此类数据查询速度非常快。
  • eq_ref: 对于每个来自于前面的表的行组合,从该表中读取一行,常用在一个索引是unique key或者primary key。
  • ref: 数据查询的时候如果命中的索引是二级索引不是唯一索引,测试查询速度也会很快,但是type是ref。另外如果是多字段的联合索引,那么根据最左匹配原则,从联合索引的最左侧开始连续多个列的字段进行等值比较也是ref的类型。
  • ref_or_null: 类似于 ref,区别在于 MySQL会额外搜索包含NULL值的行。
  • unique_subquery: 在where条件中的关于in的子查询条件集合。
  • index_subquery: 区别于unique_subquery,用于非唯一索引,可以返回重复值。
  • range: 使用索引进行行数据检索,只对指定范围内的行数据进行检索。换句话说就是针对一个有索引的字段,在指定范围中检索数据。在where语句中使用 bettween...and、<、>、<=、in 等条件查询 type 都是 range。
    image
  • all: 遍历全表进行数据匹配,此时的数据查询性能最差。
    image
  • index: index 与 all 其实都是读全表,区别在于index是遍历索引树读取,而ALL是从硬盘中读取。

possible_keys

表示哪些索引可以被 MySQL 的优化器进行选择,也就是索引候选者有哪些。

key

最终选择使用的索引。

key_len

表示索引的长度,和实际的字段属性以及是否为null都有关系。

ref

当使用字段进行常量等值查询时ref此处为const,当查询条件中使用了表达式或者函数则ref显示为func,其他的显示为null。

rows

表示 MySQL 认为它执行查询时必须检查的行数。行数越少,效率越高。

filtered

这个是一个百分比的值,表里符合条件的记录数的百分比。简单点说,这个字段表示存储引擎返回的数据在经过过滤后,剩下的满足条件的记录数量的比例。

extra

在其他列不显示额外信息在此列进行展示。

  • Using index: 在进行数据查询的时候,数据库使用了覆盖索引,就是查询的列被索引覆盖,使用到覆盖索引查询速度会非常快。
    image
  • Using where: 查询时未找到可用的索引,进而通过where条件过滤获取所需数据,但要注意的是并不是所有带where语句的查询都会显示Using where。
  • Using temporary: 表示查询后结果需要使用临时表来存储,一般在排序或者分组查询时用到。
  • Using filesort: 此类型表示无法利用索引完成指定的排序操作,也就是ORDER BY的字段实际没有索引,因此此类SQL是需要进行优化的。

参考

Mysql的explain,你真的会用吗?
MySQL优化之EXPLAIN命令解析
全网最全 | MySQL EXPLAIN 完全解读
Mysql中key 、primary key 、unique key 与index区别

标签:tb,explain,查询,索引,详解,key,mysql,ref,id
From: https://www.cnblogs.com/strongmore/p/18115525

相关文章

  • Linux Debian12 部署MySql 并建立外部连接
    一.下载MySql下载最新软件包。也可以在命令界面下使用下载最新的发行包。wgethttps://repo.mysql.com/mysql-apt-config_0.8.29-1_all.deb下载完成后,使用命令进行安装dpkg-imysql-apt-config_0.8.29-1_all.deb执行完后会跳转到安装MySql配置界面​TAB进行保存,选中OK,然......
  • VMWare Workstation 17命令行自动化测试高级用法详解
    VMwareWorkstation是一个强大的桌面虚拟化解决方案,允许用户在同一台物理机上运行多个虚拟机。虽然VMwareWorkstation主要提供图形用户界面(GUI)来管理虚拟机,但它也支持命令行工具来执行一些高级任务和自动化操作。VMwareWorkstation本身并不直接提供一套完整的命令行工......
  • mysql表名大小写敏感
    通过搜索了解到mysql在win下大小写不明感,在linux下大小写敏感本欲让ai写个脚本转表名,两次回答的结果都不能用后发现修改mysqld.conf中的lower_case_table_names=1可以忽略大小写但修改过后启动失败官方回答是初始化后不能再修改该参数,如需要修改需要重新初始化为了不折腾,......
  • MySQL存储过程中如何使用ROLLBACK
    在MySQL存储过程中,可以使用ROLLBACK来撤销之前执行的所有未提交的更改。当与BEGIN和COMMIT结合使用时,ROLLBACK可以帮助您管理事务并确保数据的完整性。以下是一个示例存储过程,它使用TRY...CATCH块来捕获异常,并在需要时执行ROLLBACK。请注意,MySQL本身并不直接支持TRY...CATCH错误......
  • 高效调度新篇章:详解DolphinScheduler 3.2.0生产级集群搭建
    转载自tuoluzhe8521导读:通过简化复杂的任务依赖关系,DolphinScheduler为数据工程师提供了强大的工作流程管理和调度能力。在3.2.0版本中,DolphinScheduler带来了一系列新功能和改进,使其在生产环境中的稳定性和可用性得到了显著提升。为了帮助读者更好地理解和应用这一版本,我们精......
  • Linux tcpdump 命令详解与示例
    命令概要Linux作为网络服务器,特别是作为路由器和网关时,数据的采集和分析是不可少的。tcpdump是Linux中强大的网络数据采集分析工具之一。用简单的话来定义tcpdump,就是:dumpthetrafficonanetwork,根据使用者的定义对网络上的数据包进行截获的包分析工具。作为互联网上经典的......
  • Ubuntu20.04安装MySQL8主从
    ......
  • MySQL主从- slave跳过错误
    mysql主从复制,经常会遇到错误而导致slave端复制中断,这个时候一般就需要人工干预,跳过错误才能继续跳过错误有两种方式:一、跳过指定数量的事务mysql>slavestop;mysql>SETGLOBALSQL_SLAVE_SKIP_COUNTER=1#跳过一个事务mysql>slavestart二、修改mysql的配置文件通过sl......
  • MySQL数据库连接超时
    应用日志报错,应用反馈频繁连接超时,导致应用无法正常提供服务。 可能的问题原因如下:数据库timeout参数设置不合理数据库连接串设置有误连接池配置不是最佳实践或者连接池满网络波动防火墙策略异常数据库压力大导致响应缓慢数据库连接达到上限未知原因处理过程:检查......
  • MySQL 给用户添加 ALTER VIEW 的权限
    本文分享自华为云社区《MySQL给用户添加ALTERVIEW的权限》,作者:皮牙子抓饭。MySQL是一个广泛使用的关系型数据库管理系统,用于许多Web应用程序和企业级解决方案中。在MySQL中,用户权限的管理是非常重要的,以确保数据库安全性和数据完整性。在某些情况下,需要为用户添加特定......