MySQL学习笔记--MySQL逻辑架构，sql写与加载顺序以及七种JOIN模式图解

一、MySQL的逻辑架构

MySQL的最大特点是其插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储，提取相分离。这种架构可以根据业务的需求和实际需求选择合适的存储引擎。正因为插件式引擎的特点它的架构可以在多种不同的场景中应用并发挥良好的性能。

1. 连接层：

为请求做连接处理，授权认证，安全等。

处理流程：

• 每个连接的查询都在一个进程中的线程完成。
• 服务器负责缓存线程，所以服务层不需要为每个连接新建线程。

认证流程：

2. 服务层：

查询解析，分析，优化，缓存，提供内建函数;存储过程，触发器，视图。

• 在解析查询之前，服务器会“询问”是否进行了查询缓存(只能缓存SELECT语句和相应结果)。缓存过的直接返回结果，未缓存的就需要进行解析查询，优化，重新执行返回结果。
• 解析查询时会创建一个内部数据结构(树)，然后对其进行各种优化。
• 优化:重写查询，决定查询的读表顺序，选择需使用的索引。
  

3. 引擎层：

不光做存储和提取数据，而且针对特殊数据引擎还要做事务处理。

最常用的两个存储引擎比较：

当向MySQL发送一个请求的时候，MySQL到底做了些什么呢？下图展示了MySQL的查询过程：

二、SQL语句的读写顺序与执行顺序

读写顺序：

SELECT DISTINCT <select_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
ORDER BY <order_by_condition>
LIMIT <limit_number>

执行顺序：

(7)     SELECT
(8)     DISTINCT <select_list>
(1)     FROM <left_table>
(3)     <join_type> JOIN <right_table>
(2)     ON <join_condition>
(4)     WHERE <where_condition>
(5)     GROUP BY <group_by_list>
(6)     HAVING <having_condition>
(9)     ORDER BY <order_by_condition>
(10)    LIMIT <limit_number>

附一张图，解释MySQL中sql语句的解析顺序：

tips:

group by 存在时，select中除了聚集函数外，所有的基本列必须是group by里面存在的；having基本上同group by一起使用的，having类似于where语句，只是having过滤是基于group by 分组后的数据，having一般通过select语句里面的聚集函数进行过滤。

三、七种JOIN模式

多表查询，首先的弄清楚表与表之间的关系---外键约束

然后根据查询要求选择JOIN模式

建表：

在这里呢我们先来建立两张有外键关联的张表。

CREATE DATABASE db0206;
USE db0206;

CREATE TABLE `db0206`.`tbl_dept`(  
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `deptName` VARCHAR(30),
  `locAdd` VARCHAR(40),
  PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8;

CREATE TABLE `db0206`.`tbl_emp`(  
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20),
  `deptId` INT(11),
  PRIMARY KEY (`id`),
  FOREIGN KEY (`deptId`) REFERENCES `db0206`.`tb_dept`(`id`)
) ENGINE=INNODB CHARSET=utf8;
/*插入数据*/
INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);

INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);

INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);

INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);

INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);

1. 内连接

sql语句：

select * from tbl_dept a inner join tbl_emp b on a.id=b.deptId;

查询结果：

2. 左外连接：

sql语句：

select * from tbl_dept a left join tbl_emp b on a.id=b.depId;

查询结果:

3. 右外连接

sql语句：

select * from tbl_dept a right join tbl_emp b on a.id=b.deptId;

查询结果：

4. 左连接：

sql语句：

select * from tbl_dept a left join tbl_emp b on a.id=b.deptId where b.deptId=null;

查询结果：

5. 右连接：

sql语句：

select * from tbl_dept a right join tbl_emp b on a.id=b.deptId where a.id is null;

查询结果：

6. 全连接：

sql语句：

select * from tbl_depId a left join tbl_emp b on a.id=b.deptId
union
select * from tbl_depId a right join tbl_emp b on a.id=b.depId;

查询结果：

7. 两张表的非公共集合

sql语句：

select * from tbl_deptId a left join tbl_emp b on a.id=b.deptId where a.id is null
union
select * from tbl_deptId a right join tbl_emp b on a.id=b.depId where b.id is null

查询结果：

参考博客：

https://blog.csdn.net/github_36379934/article/details/54897227

https://www.cnblogs.com/baochuan/archive/2012/03/15/2397536.html

https://blog.csdn.net/fuzhongmin05/article/details/70904190