【转载】数据库 | mysql 查询

MySQL中的表连接（外连接、内连接、交叉连接、自连接）

---

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。 在使用left jion时，on和where条件的区别如下：

1、on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

---

1、Mysql可以分为Server层和存储引擎层两部分。

2、Server层：包含连接器，查询缓存，分析器，优化器，执行器等，涵盖了MySQL的大多数核心功能区以及所有的内置函数。

①内置函数：日期，时间，数学和加密函数等；

②所有跨存储引擎的功能都在这一层实现，例如存储过程，触发器，视图等；

3、存储引擎层：负责数据的存储和提取。

①架构模式：插件式的，支持InnoDB（目前也是最常用的，MySQL5.5后默认存储引擎）、MyISAM、Memory等多个存储引擎。

②create table 时不指定引擎类型，默认使用的就是InnoDB.如果使用其他类型，可以利用create table语句中使用engine=memory，等来指定使用内存引擎来创建表。不同的存储引擎数据存储方式不同，支持的功能也不同。

4、不同的存储引擎共用一个server层，结合一个例子我们从总体上了解一下各部件的功能。

详细查看：MySQL基础架构

---

---

客户端
控制工具 mysql 用来管理数据库,备份,恢复,安全控制等等

• 连接池 管理缓冲用户连接，线程处理等需要缓存的需求
• sql接口 并且返回用户需要查询的结果，比如select from
• 解析器 sql命令分解成数据结构
• 优化器 查询之前，对sql查询语句进行优化。使用’选取-投影-联接’策略进行查询
  select uid,name from user where gender = 1;
  查询步骤：
  where语句选取，而不是全部查询，再gender过滤
  根据uid和name属性投影，而不是将属性全部取出，再过滤
  两个查询条件联接起来生成最终查询结果
  查询缓存
  如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据
  这个缓存机制是由一系列小缓存组成的，比如表缓存，记录缓存，key缓存，权限缓存

---

连接者：不同语言的代码程序和mysql的交互（SQL交互）
1、连接池 管理、缓冲用户的连接，线程处理等需要缓存的需求
2、管理服务和工具组件 系统管理和控制工具，例如备份恢复、Mysql复制、集群等
3、sql接口 接受用户的SQL命令，并且返回用户需要查询的结果
4、查询解析器 SQL命令传递到解析器的时候会被解析器验证和解析(权限、语法结构)
5、查询优化器 SQL语句在查询之前会使用查询优化器对查询进行优化
select id,name from user where age = 40;
a、这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行age过滤
b、这个select查询先根据id和name进行属性投影，而不是将属性全部取出以后再进行过滤
c、将这两个查询条件联接起来生成最终查询结果
6、缓存 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据
7、插入式存储引擎 存储引擎说白了就是如何管理操作数据（存储数据、如何更新、查询数据等）的一种方法。因为在关系数据库
中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

---

连接池

上MySQL中的连接池就是维护了与系统之间的多个数据库连接。除此之外，你的系统每次跟MySQL建立连接的时候，还会根据你传递过来的账号和密码，进行账号密码的验证，库表权限的验证。

我们的系统采用数据库连接池的方式去并发访问数据库，然后数据库自己其实也会维护一个连接池，其中管理了各种系统跟这台数据库服务器建立的所有连接。

把MySQL当个黑盒子一样执行SQL语句

我们可以通过数据库连接把要执行的SQL语句发送给MySQL数据库。大多数人完全当他是个黑盒子，来建表以及执行SQL语句

网络连接必须让线程来处理

完全当他是个黑盒子，来建表以及执行SQL语句。

网络连接必须得分配给一个线程去进行处理，由一个线程来监听请求以及读取请求数据，比如从网络连接中读取和解析出来一条我们的系统发送过去的SQL语句，如下图所示

SQL接口：负责处理接收到的SQL语句

当MySQL内部的工作线程从一个网络连接中读取出来一个SQL语句之后，此时会如何来执行这个SQL语句呢？

如果你要去执行这个SQL语句，去完成底层数据的增删改查，那这就是一项极度复杂的任务了！

所以MySQL内部首先提供了一个组件，就是SQL接口（SQL Interface），他是一套执行SQL语句的接口，专门用于执行我们发送给MySQL的那些增删改查的SQL语句

因此MySQL的工作线程接收到SQL语句之后，就会转交给SQL接口去执行，如下图。

解析器：让MySQL能看懂SQL语句

这个查询解析器（Parser）就是负责对SQL语句进行解析的，比如对上面那个SQL语句进行一下拆解，拆解成以下几个部分：

• 我们现在要从“users”表里查询数据
• 查询“id”字段的值等于1的那行数据
• 对查出来的那行数据要提取里面的“id,name,age”三个字段。

优化器

选择最优的查询路径

当我们通过解析器理解了SQL语句要干什么之后，接着会找查询优化器（Optimizer）来选择一个最优的查询路径

调用存储引擎接口，真正执行SQL语句

最后一步，就是把查询优化器选择的最优查询路径，也就是你到底应该按照一个什么样的顺序和步骤去执行这个SQL语句的计划，把这个计划交给底层的存储引擎去真正的执行。

不知道大家是否思考过，真正在执行SQL语句的时候，要不然是更新数据，要不然是查询数据，那么数据你觉得存放在哪里？

数据库也不是什么神秘莫测的东西，你可以把他理解为本身就是一个类似你平时写的图书馆管理系统

缓存

数据库自己就是一个编程语言写出来的系统而已，然后启动之后也是一个进程，执行他里面的各种代码，也就是我们上面所说的那些东西。所以对数据库而言，我们的数据要不然是放在内存里，要不然是放在磁盘文件里，没什么特殊的地方！

我们已经知道一个SQL语句要如何执行了，但是我们现在怎么知道哪些数据在内存里？哪些数据在磁盘里？我们执行的时候是更新内存的数据？还是更新磁盘的数据？我们如果更新磁盘的数据，是先查询哪个磁盘文件，再更新哪个磁盘文件？

存储引擎

是不是感觉一头雾水所以这个时候就需要存储引擎了，存储引擎其实就是执行SQL语句的，他会按照一定的步骤去查询内存缓存数据，更新磁盘数据，查询磁盘数据，等等，执行诸如此类的一系列的操作，如下图所示。

MySQL的架构设计中，SQL接口、SQL解析器、查询优化器其实都是通用的，他就是一套组件而已。

但是存储引擎的话，他是支持各种各样的存储引擎的，比如我们常见的InnoDB、MyISAM、Memory等等，我们是可以选择使用哪种存储引擎来负责具体的SQL语句执行的。

当然现在MySQL一般都是使用InnoDB存储引擎的，至于存储引擎的原理，后续我们也会深入一步一步分析，大家不必着急。

执行器：根据执行计划调用存储引擎的接口

我们回过头来思考一个问题，存储引擎可以帮助我们去访问内存以及磁盘上的数据，那么是谁来调用存储引擎的接口呢？

这个执行器会根据优化器选择的执行方案，去调用存储引擎的接口按照一定的顺序和
步骤，就把SQL语句的逻辑给执行了

举个例子，比如执行器可能会先调用存储引擎的一个接口，去获取“users”表中的第一行数据，然后判断一下这个数据的“id”字段的值是否等于我们期望的一个值，如果不是的话，那就继续调用存储引擎的接口，去获取“users”表的下一行数据。

就是基于上述的思路，执行器就会去根据我们的优化器生成的一套执行计划，然后不停的调用存储引擎的各种接口去完成SQL语句的执行计划，大致就是不停的更新或者提取一些数据出来