MySQL体系结构和执行计划

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

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

• MySQL文件系统简介
• MySQL表字段类型&＃xff0c;表空间&＃xff0c;表信息
• MySQL悲观锁&＃xff0c;乐观锁&＃xff0c;行锁&＃xff0c;表锁&＃xff0c;共享锁&＃xff0c;排他锁&＃xff0c;MDL锁&＃xff0c;意向锁&＃xff0c;间隙锁&＃xff0c;next-key lock,死锁
• MySQL事务
• MySQL索引
• MySQL幻读以及当前读引起的问题验证

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

MySQL数据库有三层结构。

• MySQL server &＃xff1a; 包括连接层和SQL层
• 存储引擎层&＃xff1a; 包括多种存储引擎。

2.1 MySQL Server

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

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


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

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

2.2 存储引擎

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

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


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

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

3. 执行计划

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

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

3.1. 如何查看SQL执行计划

Explain &＃43; SQL 语句
例如新建student,school 表&＃xff0c;并查看执行计划:

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


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


执行计划如图所示&＃xff1a;

3.2. 执行计划各列说明

3.2.1. id

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

3.2.2. select_type

查看的类别&＃xff0c;通常有三种场景&＃xff0c;正常查询&＃xff0c;子查询和联合查询。

• 正常查询&＃xff1a;
  simple 属于子查询外和union外其他的查询方式。

示例&＃xff1a; explain select * from school

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

示例1&＃xff1a;

desc select * from student where school_id &＃61; (select id from school where school_name &＃61; “中关村二小”)

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

示例2(这个没有什么特别的意义&＃xff0c;只做演示)&＃xff1a;

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

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

示例3&＃xff1a;

explain select * from school where id &＃61; (select school_id from student where id &＃61; school.id)

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

示例4&＃xff1a;

set &＃64;p &＃61; 1;
explain select * from school where id &＃61; (select school_id from student where id &＃61; &＃64;p);

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

• union 查询

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

示例&＃xff1a;

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

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

3.2.3. table

表名&＃xff0c;可以是衍生表&＃xff0c;例如上述select_type中展示 school,。

3.2.4. partitions

查询表分区展示结果的分区。
创建新表展示&＃xff1a;

CREATE TABLE &＃96;test1&＃96; (
&＃96;id&＃96; INT ( 11 ), PRIMARY KEY ( &＃96;id&＃96; )
) ENGINE &＃61; INNODB DEFAULT CHARSET &＃61; 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&＃xff08;重要&＃xff09;

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

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


示例&＃xff1a;

• all 查询所有&＃xff1a;

explain select * from school;


• index 返回索引列:

explain select id from school;


这两种都是进行的全表扫描&＃xff0c;在查看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 &＃61; "中关村二小";


• ref_or_null 普通索引加上NULl值:

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


• eq_ref 唯一索引&＃xff1a;

explain select * FROM school where id in (select id from school where school_name &＃61; "中关村二小")


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

explain select * from school where id &＃61; 1;


3.2.6. possible_keys

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

3.2.7. key

实际用到的索引 。

3.2.8. key_len

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

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

3.2.9. ref

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

3.2.10. rows

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

3.2.11. filtered

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

3.2.12. Extra

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

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