MySQL体系结构和执行计划

最新推荐文章于 2024-05-03 18:15:47 发布

燃尽余火

最新推荐文章于 2024-05-03 18:15:47 发布

阅读量161

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_42798851/article/details/116293451

版权

MySQL 专栏收录该内容

12 篇文章 1 订阅

订阅专栏

MySQL体系结构和执行计划

1. 前言
2. MySQL体系结构
- 2.1 MySQL Server
- 2.2 存储引擎
3. 执行计划

1. 前言

之前的文章都是介绍MySQL体系结构的某一个部分，之后的文章会对整个MySQL的运行流程或者整个运行流程进行讲解。

哈哈，先介绍下之前的MySQL文章，感兴趣的可以看下，如果看到错误麻烦留言，谢谢。如果感觉写的可以，看到有收获，也可以点赞。您的每次点赞都是我写作的动力。

此文章简单说下MySQL的执行计划，这个是MySQL优化必备的技能。而说到执行计划又不得不提MySQL的体系结构，在这里就同时说明下。

2. MySQL体系结构

MySQL数据库有三层结构。

MySQL server ：包括连接层和SQL层
存储引擎层：包括多种存储引擎。

2.1 MySQL Server

连接层
应用程序连接到MySQL的时候，首先经过连接层。包括通信协议，线程处理和用户认证三个部分。

  1、通信协议判断请求客户端的兼容情况。
  2、线程处理即为每个连接获取一个线程.
  3、用户验证判断用户名的账号和密码是否正确。

SQL 层
这个就比较重要。每条SQL执行的时候都会经过这几个流程，而且这个也是每个学习MySQL都应该清楚的内容。
处理流程为：当SQL进行查询的时候，依次进行

序号	功能	说明
1	权限判断	用于判断账号是否具有库表的访问权限
2	查询缓存	通过Query Cache 查询，如果有结果直接返回
3	解析器	对SQL语句进行解析
4	预处理	对SQL语句进行预处理
5	优化器	对SQL语句进行优化
6	执行计划	优化完成之后生成最合适的执行计划
7	调用程序API接口	调用MySQL的API接口
8	存储引擎	通过各种存储引擎访问数据，当然MySQL5.7 默认InnoDB

此外Server 层还存储MySQL的函数，视图，存储过程，触发器等。

2.2 存储引擎

MySQL的存储引擎有多种，例如 MyISAM,InnoDB,Archive,Memory,Federated 等
此外还有MySQL 的分支 Percona的存储引擎 TokuDB ，以及MariaDB 的存储引擎等。

Archive 支持压缩功能的存储引擎。
Memory 只在内存中使用。
Federated 支持远程访问 等，就不一一介绍了。

因为在MySQL8.0 之后只支持InnoDB了，但是在面试的时候还会问道MyISAM和InnoDB区别问题，此处列举几个：

1、InnoDB 支持事务，MyISAM 不支持事务
2、InnoDB 支持表级锁和行级锁，但是MyISAM 只支持表级锁
3、InnoDB的文件结尾有 frm 和  ibd ,但是 MyISAM的文件结尾是  frm ，MYI 和MYD。
	其中 frm 结尾统一代表表结构文件。InnoDB 中 ibd 文件存储了索引和数据，
	MyISAM 的 MYI 文件代表索引文件，MYD文件代表数据文件。
4、InnoDB 的并发粒度比MyISAM更高。
5、InnoDB 关注的重点是事务，MyISAM关注性能。
6、InnoDB 统计行数必须扫描表数据，MyISAM总行数会单独存放，但是带Where条件也必须查询表数据。
7. InnoDB 不仅缓存数据还缓存索引，MyISAM只缓存索引。

3. 执行计划

当SQL进行一系列的鉴权，解析，预处理，优化器之后生成执行计划，之后调用的是存储引擎。

当我们编写SQL的时候如何看自己SQL编写的好坏呢，是否能够达到快速查询的目的？
这个时候就要学会查看执行计划。

3.1. 如何查看SQL执行计划

Explain + SQL 语句
例如新建student,school 表，并查看执行计划:

-- create table student
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `phone` char(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `school_id` int(11) DEFAULT NULL,
  `school_name` varchar(255) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
-- create table school
CREATE TABLE `school` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_name` varchar(255) DEFAULT NULL,
  `school_address` varchar(255) DEFAULT NULL,
  `school_area` varchar(255) DEFAULT NULL,
  `school_province` varchar(20) DEFAULT NULL,
  `create_time` datetime DEFAULT NULL,
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 插入数据
insert into student(name,age,phone,address,school_id,school_name,create_time) VALUES
('lihua',15,'13966667777','北京市海淀区',1,'中关村二小',now());
insert into school(school_name,school_address,school_area,school_province,create_time)VALUES
('中关村二小','北京市海淀区中关村','海淀区','北京市',now());
insert into student(name,age,phone,address,school_id,school_name,create_time) VALUES
('lihua1',15,'13966667777','北京市海淀区',1,'中关村二小',now());
insert into school(school_name,school_address,school_area,school_province,create_time)
VALUES('中关村二小2','北京市海淀区中关村','海淀区','北京市',now());

-- 查看所有数据
select * from student;
select * from school;

在这里插入图片描述

-- 查看执行计划
explain select * from student;

执行计划如图所示：
在这里插入图片描述

3.2. 执行计划各列说明

3.2.1. id

Query Optimizer(查询分析器) 所选定的执行计划中查询的序列号。

3.2.2. select_type

查看的类别，通常有三种场景，正常查询，子查询和联合查询。

正常查询：
simple 属于子查询外和union外其他的查询方式。

示例： explain select * from school

在这里插入图片描述

子查询：
primary 子查询的最外层查询。
subquery 子查询的第一个结果集，被用户外部依赖。
uncached subquery 无法缓存的子查询
dependent subquery 子查询中的第一个select ,依赖外部的查询结果
received 衍生表，通过子查询产生的

示例1：

desc select * from student where school_id = (select id from school where school_name = “中关村二小”)

这里是外部的查询结果是primary,子查询是subquery。

示例2(这个没有什么特别的意义，只做演示)：

explain select * from student t,(select count(id) count,school_name from school where school_name = “中关村二小” GROUP BY school_name) t2 where t.id = t2.count

received 一般是统计或者分组的时候产生的表，然后用来关联查询。

示例3：

explain select * from school where id = (select school_id from student where id = school.id)

dependent subquery 依赖外部的条件school.id

示例4：

set @p = 1;
explain select * from school where id = (select school_id from student where id = @p);

通过设置变量来查询就会存在无法缓存的子查询。

union 查询

union union之后的所有select都使用union
dependent union 子查询中的union
union result 组合结果

示例：

explain
select * FROM school where id in
(
select id from school where school_name = “中关村二小”
union select id from school where school_name = “中关村二小2”
)
union
select * FROM school where id = 2

这里 union 就是联合查询会显示，union result 是最后联合查询的结果。
而 dependent union 就是子查询中的union 。

3.2.3. table

表名，可以是衍生表，例如上述select_type中展示 school,<union2,3>。

3.2.4. partitions

查询表分区展示结果的分区。
创建新表展示：

CREATE TABLE `test1` ( 
`id` INT ( 11 ), PRIMARY KEY ( `id` ) 
) ENGINE = INNODB DEFAULT CHARSET = utf8 
PARTITION BY RANGE ( id ) (
		PARTITION p0 VALUES less than ( 5 ), 
		PARTITION p1 VALUES less than MAXVALUE 
);
insert into test1 values (1),(11);
explain select * from test1;

在这里插入图片描述
这里的id 1 和 11 分别在不同的分区中。

3.2.5. type（重要）

显示连接使用了何种类型，对表所使用的访问方式。
从最好到最差的连接类型为const、eq_reg、ref、range、index和all。
null > system > const > eq_ref > ref > range > index > all

system: 只有一行记录
const: 只通过唯一索引并只查询了一条数据就找到了
eq_ref: 唯一性索引扫描，主键索引或者唯一索引扫描
ref: 非唯一索引
ref_or_null： 比ref多一个null值的查询
unique_subquery： 子查询返回是主键或者唯一索引
index_merge： 多个索引使用后merge后组合
index_subquery： 子查询返回的是索引，但是不是唯一索引或主键
range: 范围查询  beteween/on ，或者 in,超过一定的数据量会变成全表烧苗
index: 全表扫描，但是查询的列全部是索引
all： 全数据扫描

示例：

all 查询所有：

explain select * from  school;

在这里插入图片描述

index 返回索引列:

explain select id  from  school;

在这里插入图片描述
这两种都是进行的全表扫描，在查看SQL执行情况的时候都是需要优化的。

range 范围查询:

  explain select id  from  school where id < 2

在这里插入图片描述

ref 普通索引:

	--- 增加索引
	alter table school add index idx_school_name(school_name);
	-- 查看执行计划
	EXPLAIN select * from school where  school_name = "中关村二小";

在这里插入图片描述

ref_or_null 普通索引加上NULl值:

explain select * from school where  school_name = "中关村二小" or school_name is null;

在这里插入图片描述

eq_ref 唯一索引：

explain select * FROM school where id in (select id  from school where school_name = "中关村二小")

在这里插入图片描述

const 只查找一行就找到数据:

explain select * from school where id = 1;

在这里插入图片描述

3.2.6. possible_keys

可能使用的key,用于预测。

3.2.7. key

实际用到的索引。

3.2.8. key_len

查询的结果集的字符长度。
这个可以查看是否充分使用了索引，当计算的时候需要考虑多个点，

字符类型：例如 int 类型 4个字节。
可以查看我之前的文章说明：MySQL表字段类型，表空间，表信息
NULL ：是否为null,长度加1
varchar : 长度加2
字符集，latin1 每个字节长度 1，GBK 每个字节长度2，UTF-8 长度 3，utf8mb4 长度4

3.2.9. ref

显示的是列的名字，显示索引的哪一列被使用了，MySQL将根据这些列来选择行，如果可能的话，是一个常数最好。

3.2.10. rows

mysql查询用于返沪需要的行数，最好的1，可能出现不准确的情况，是预估值。
当进行查询的时候结果越小越好。

3.2.11. filtered

Filtered表示返回结果的行数占需读取行数的百分比 Filtered列的值越大越好。
Filtered列的值依赖于统计信息,例如子查询了100行信息，但是使用的时候只用到50行，就显示50，显示的展示结果的百分比。

3.2.12. Extra

包含不适合在其他列中显示但十分重要的信息。

using index ：查询的所有列都是索引列，即是使用了覆盖索引。
using filesort: MySQL中无法利用索引完成的排序操作称为“文件排序”
已经建立了索引，但是实际的时候没有用到，并新建了索引，例如索引 abc,查询a = “xx” order by c
using temporary: 用到了临时表，并且对临时表进行了排序，一般是group by，order by
using where : 使用了where 或者 on
using join buffer : 是不是用到了join 缓存 ,有缓存大小 show variable like “%join_buffer_size%”
impossible where : where总是返回false 例如 where 1=2