MySQL体系结构和执行计划
1. 前言
之前的文章都是介绍MySQL体系结构的某一个部分,之后的文章会对整个MySQL的运行流程或者整个运行流程进行讲解。
哈哈,先介绍下之前的MySQL文章,感兴趣的可以看下,如果看到错误麻烦留言,谢谢。如果感觉写的可以,看到有收获,也可以点赞。您的每次点赞都是我写作的动力。
- MySQL文件系统简介
- MySQL表字段类型,表空间,表信息
- MySQL悲观锁,乐观锁,行锁,表锁,共享锁,排他锁,MDL锁,意向锁,间隙锁,next-key lock,死锁
- 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