【MySQL从入门到精通】【高级篇】(二十三)EXPLAIN的概述与table,id字段的剖析

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 上一篇文章我们介绍了【MySQL从入门到精通】【高级篇】(二十二)慢查询日志分析,SHOW PROFILE查看SQL执行成本,这篇文章我们接着来介绍一下MySQL中一个非常重要的命令 EXPLAIN。当我们定位到查询慢的SQL之后,我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。 DESCRIBE 语句的使用方法与EXPLAIN语句是一样的,并且分析结果也是一样的。

1. 简介

上一篇文章我们介绍了【MySQL从入门到精通】【高级篇】(二十二)慢查询日志分析,SHOW PROFILE查看SQL执行成本,这篇文章我们接着来介绍一下MySQL中一个非常重要的命令 EXPLAIN。当我们定位到查询慢的SQL之后,我们就可以使用EXPLAIN或DESCRIBE工具做针对性的分析查询语句。 DESCRIBE 语句的使用方法与EXPLAIN语句是一样的,并且分析结果也是一样的。

MySQL中有专门负责优化SELECT 语句的优化器模块,主要功能:通过分析系统中收集到的统计信息,为客户端请求的Query提供它认为最优的执行计划 (这部分最耗时)。


这个执行计划展示了接下来具体执行查询的方式,比如多表连接的顺序是什么,对于每个表采用什么访问方法来具体执行查询等等。MySQL为我们提供了EXPLAIN 语句来帮助我们查看某个查询语句的具体执行计划,大家看懂EXPLAIN 语句的各个输出项,可以针对性的提升我们查询语句的性能。

2. EXPLAIN能做什么?

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询、

3. 官网介绍以及Explain的概述

MySQL的官方文档对MySQL做了一个详细的介绍。Explain的官网介绍

EXPLAIN 不仅可以作用于以SELECT 开头的查询语句,还可以作用于DELETE、INSERT、REPLACE以及UPDATE语句上。用来查看这些语句的执行计划,只是平时我们更多的用于对SELECT 语句的分析上。


需要注意的是:执行EXPLAIN时并没有真正的执行其后面的语句,因此可以安全的查看执行计划。

EXPLAIN语句输出的各个列的作用如下:

列名 描述
id 在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type SELECT 关键字对应的那个查询的类型
table 表名
partitions 匹配的分区信息
type 针对单表的访问方法
possible_keys 可能用到的索引
key 实际用到的索引
key_len 实际使用到的索引长度
ref 当使用索引列等值查询时,与索引列进行等值匹配的对象信息
rows 预估的需要读取的记录条数
filtered 某个表经过搜索条件过滤后剩余记录条数的百分比
Extra 一些重要的额外信息

这里先做一个整体的介绍,其中: select_typetypekeyExtra 这几列非常重要

4. 准备测试数据

4.1. 创建测试表

CREATE TABLE s1(
  id INT AUTO_INCREMENT,
  key1 VARCHAR(100),
  key2 INT,
  key3 VARCHAR(100),
  key_part1 VARCHAR(100),
  key_part2 VARCHAR(100),
  key_part3 VARCHAR(100),
  common_field VARCHAR(100),
  PRIMARY KEY(id),
  INDEX idx_key1(key1),
  UNIQUE INDEX uidx_key2(key2),
  INDEX idx_key3(key3),
  INDEX idx_key_part(key_part1,key_part2,key_part3)
) ENGINE=INNODB CHARSET=utf8;
CREATE TABLE s2(
    id INT AUTO_INCREMENT,
  key1 VARCHAR(100),
  key2 INT,
  key3 VARCHAR(100),
  key_part1 VARCHAR(100),
  key_part2 VARCHAR(100),
  key_part3 VARCHAR(100),
  common_field VARCHAR(100),
  PRIMARY KEY(id),
  INDEX idx_key1(key1),
  UNIQUE INDEX uidx_key2(key2),
  INDEX idx_key3(key3),
  INDEX idx_key_part(key_part1,key_part2,key_part3)
) ENGINE=INNODB CHARSET=utf8;

这里创建了两张一模一样的表,分别是表s1和表s2。并且在表s1中设置了主键索引id,普通索引idx_key1,唯一索引uidx_key2,以及组合索引idx_key_part。

4.2. 创建随机数函数以及存储过程

创建一个生成随机字符串的函数:

CREATE DEFINER=`baduser`@`%` FUNCTION `rand_string`(n INT) RETURNS varchar(255) CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
  DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
  DECLARE return_str VARCHAR(255) DEFAULT '';
  DECLARE i INT DEFAULT 0;
  WHILE i<n DO 
   SET return_str=CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
  SET i=i+1;
  END WHILE;
  RETURN return_str;
END

创建往s1表中插入数据的存储过程:

CREATE DEFINER=`baduser`@`%` PROCEDURE `insert_s1`(IN min_num INT(10),IN max_num INT(10))
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit=0;
  REPEAT
  SET i=i+1;
  INSERT INTO s1 VALUES(
   (min_num+i),
  rand_string(6),
  (min_num+30*i+5),
  rand_string(6),
  rand_string(10),
   rand_string(5),
  rand_string(10),
  rand_string(10));
    UNTIL i=max_num 
    END REPEAT;
    COMMIT;
END

创建往s2表中插入数据的存储过程:

CREATE DEFINER=`baduser`@`%` PROCEDURE `insert_s2`(IN min_num INT(10),IN max_num INT(10))
BEGIN
  DECLARE i INT DEFAULT 0;
  SET autocommit=0;
  REPEAT
  SET i=i+1;
  INSERT INTO s2 VALUES(
   (min_num+i),
  rand_string(6),
  (min_num+30*i+5),
  rand_string(6),
  rand_string(10),
   rand_string(5),
  rand_string(10),
  rand_string(10));
    UNTIL i=max_num 
    END REPEAT;
    COMMIT;
END

执行存储过程

-- 调用存储过程
CALL insert_s1(10001,10000);
CALL insert_s2(10001,10000);

这里给s1表和s2表分别插入10000。执行完成之后查看下s1表中的总记录数。


5. EXPLAIN 各列作用

5.1. table

首先介绍的列是table列,无论我们的查询有多么复杂,里面包含了多少个表,到最后也需要对每个表进行单表访问的,所以MySQL规定EXPLAIN语句输出的每条记录都对应着某个单表的访问方法, 该条记录的table列代表着该表的表名(有时不是真实的表名,可能是简称)

单表查询

EXPLAIN SELECT * FROM s1;

这个语句只涉及对s1表的单表查询,所以EXPLAIN 输出中只有一条记录,其中的table列的值是s1,表明这条记录是用来说明对s1表的单表记录是用来说明对s1表的单表访问方法的。

  1. 连接查询
-- s1:驱动表 s2:被驱动表
EXPLAIN SELECT * FROM s1 INNER JOIN s2;


这条语句是s1表和s2表的关联查询,所以需要查询两张表。

在看下有临时表的情况吧

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

这里通过union关键字将两个查询联合起来。所以除了s1表和s2表以外,还多了一个临时表,这里的临时表的名字是<union 1,2>。

5.2 id

说完了table列,接下来说说 id列,在一个大的查询语句中每个SELECT 关键字都对应一个唯一的id。

单表查询的id列

EXPLAIN SELECT * FROM s1 WHERE key1='a';

单表查询很简单,就只有一条查询语句, 所以id值也就是1


关联查询的情况

是不是说,id值是累加的呢?多少个查询,id值就是多大?答案是否定的!!!!让我们来看下s1和s2的关联查询

EXPLAIN SELECT * FROM s1 INNER JOIN s2;

可以查看这两趟查询他们的id值是一样的,也就是说并不是多少次查询id值就累加到多少。id值一样的话可以认为是一组,从上往下顺序执行。


子查询的情况

EXPLAIN SELECT * FROM s1 WHERE key1 IN(SELECT key1 FROM s2) OR key3='a';

这里有个两个查询,一个主查询查s1表,一个子查询查s2表,按照常识我们可以知道子查询先执行,观察到子查询的id值为2。这就说明id值越大的表示越优先执行。

来看下另外一种子查询的情况

EXPLAIN SELECT * FROM s1 WHERE key1 IN(SELECT key3 FROM s2);

这里有三个查询,分别是主查询s1表,子查询s2表,以及中间表。

特殊情况说明

EXPLAIN SELECT * FROM s1 WHERE key1 IN(SELECT key2 FROM s2 WHERE common_field='a');

大家看到这sql语句猜想一下这里有几个查询,每个查询的id值是多少。如果单从sql语句出发,我们会习惯性的认为这里有两个查询,其中,一个主查询,一个子查询,子查询的id为2,主查询的id为1。那么实际情况是不是这样的呢?

很遗憾,答案是否定的!!!

这里的结果跟前面的s1和s2的关联查询结果一模一样,这就是因为 查询优化器可能对涉及子查询的查询语句进行重写,转成了多表查询的操作。

EXPLAIN SELECT * FROM s1 UNION SELECT * FROM s2;

这里有三个查询,分别是驱动表s1的主查询,被驱动表s2的联合查询,以及临时表<union1,2> 这里临时表的作用是将s1和s2的查询结果去重,它不算一个表查询。

比较 UNION ALL 的使用大家就有一个清晰的认识。

EXPLAIN SELECT * FROM s1 UNION ALL SELECT * FROM s2;

由于UNION ALL 关键字没有去重的效果,所以,它不需要临时表。

小结

id 如果相同,可以认为是一组,从上往下顺序执行

在所有组中,id值越大,优先级越高,越先执行

关注点:id号每个号码,表示一趟独立的查询,一个sql的查询趟数越少越好

总结

本文对EXPLAIN命令做了一个详细的介绍,EXPLAIN 命令是分析慢查询SQL的利器,通过它我们可以窥探SQL的执行情况,从而为我们进行SQL优化提供指导意义。接着,我们详细介绍了table列和id列的含义和各种情况的说明。


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1天前
|
分布式计算 关系型数据库 MySQL
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型 图像处理 光通信 分布式计算 算法语言 信息技术 计算机应用
17 8
|
10天前
|
关系型数据库 MySQL Java
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
SpringBoot项目中mysql字段映射使用JSONObject和JSONArray类型
20 0
|
27天前
|
SQL 关系型数据库 MySQL
MySQL异常一之: You can‘t specify target table for update in FROM clause解决办法
这篇文章介绍了如何解决MySQL中“不能在FROM子句中指定更新的目标表”(You can't specify target table for update in FROM clause)的错误,提供了错误描述、需求说明、错误做法和正确的SQL写法。
262 0
|
28天前
|
SQL 存储 关系型数据库
MySQL新增字段/索引会不会锁表?
MySQL新增字段/索引会不会锁表?
|
1月前
|
SQL 关系型数据库 MySQL
MySQL EXPLAIN该如何分析?
本文将详细介绍MySQL中`EXPLAIN`关键字的工作原理及结果字段解析,帮助优化查询性能。`EXPLAIN`可显示查询SQL的执行计划,其结果包括`id`、`select_type`、`table`等字段。通过具体示例和优化建议,帮助你理解和应用`EXPLAIN`,提升数据库查询效率。
61 0
|
1月前
|
SQL 关系型数据库 MySQL
MySQL 查询某个字段含有字母数字的值
MySQL 查询某个字段含有字母数字的值
58 0
|
1月前
|
存储 关系型数据库 MySQL
MySQL 字符字段长度设置详解:语法、注意事项和示例
MySQL 字符字段长度设置详解:语法、注意事项和示例
160 0
|
24天前
|
存储 关系型数据库 MySQL
Mysql(4)—数据库索引
数据库索引是用于提高数据检索效率的数据结构,类似于书籍中的索引。它允许用户快速找到数据,而无需扫描整个表。MySQL中的索引可以显著提升查询速度,使数据库操作更加高效。索引的发展经历了从无索引、简单索引到B-树、哈希索引、位图索引、全文索引等多个阶段。
57 3
Mysql(4)—数据库索引
|
10天前
|
关系型数据库 MySQL Linux
在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。
本文介绍了在 CentOS 7 中通过编译源码方式安装 MySQL 数据库的详细步骤,包括准备工作、下载源码、编译安装、配置 MySQL 服务、登录设置等。同时,文章还对比了编译源码安装与使用 RPM 包安装的优缺点,帮助读者根据需求选择最合适的方法。通过具体案例,展示了编译源码安装的灵活性和定制性。
46 2
|
13天前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
54 4