一、MySQL逻辑架构

1.1 结构分析

整体来说，MySql的逻辑架构分成三个部分。

• 1）客户端:主要是建立连接的过程，交互的过程
• 2）核心服务
• 3）存储引擎

这个可能比较抽象，我们结合MySql的查询过程，结合着进行学习。

1.2 结构图

二、MySql查询过程

2.1 流程图

这个图其实就是在第一个的基础上，进行的更加细致的划分，因为上面只是大致画出了逻辑架构，但是这个就展示了一整个过程。

2.2 过程分析

2.2.1 小结

• 1）客户端向服务端发起一条请求
• 2）服务端先检查查询缓存，如果命中缓存，则直接返回结果，否则交给下一阶段
• 3）服务器进行SQL解析，预处理，在经过查询优化形成对应的执行计划
• 4）mysql根据执行计划，调用API给存储引擎，进行数据的读取和存储
• 5）将结果返回给客户端，并缓存查询结果

大致整体的步骤就是这样的，我们要把每一步都尽量的深入思考下，我可能考虑的有欠缺，欢迎大家进行留言。

2.2.2 mysql客户端和服务器通讯

2.2.2.1 半双工通信机制

　　mysql客户端和服务器之间的通讯协议是“半双工”的，这意味着，在任何一个时刻，要么由服务器向客户端发送数据，要么由客户端向服务器发送数据，这两个动作不能同时发生。

2.2.2.2 半双工通信机制优点和缺点

这种协议让mysql通信简单快速，但也限制了mysql。一个明显的限制是，这意味着没办法进行流量限制。一旦一端开始发生消息，另一端要接收完整个消息才能响应他。

2.2.2.3 执行分析

　　客户端用一个单独的数据包将查询传给服务器。一旦客户端发送了请求，它能做的事情就只是等待结果了。一般服务器响应给用户的数据通常很多，由多个数据包组成。当服务器开始响应客户端请求时，客户端必须完整的接受整个返回结果，而不是简单的只收取前面几条结果，然后让服务器停止发送数据。所以，使用limit子句去控制服务器发送给客户端数据的量。这样可以提高性能。

　　如果没有limit进行限制的话，查询出所有的数据都会发送给客户端，比如我只需要10条。但是没有限制，假如取出了100条数据.就会发送到客户端100条数据。这其中其实有些是不需要的。在这个过程中,客户端是无法说：我已经找到我需要的10条数据，剩下的90条数据服务器请不要再发送了。由于是半双工的通信机制，那么你要做的只能等待服务器发送的100条数据全部发送完毕，你才能进行操作。所以没有limit进行限制后，是不是增加了客户端的等待时间。对性能有影响。根据这种半双工的机制，一般这样做：客户端需要多少条数据，我就在服务器操作的时候使用limit进行限制只取出多少条，那么只会发送需要的条数给客户端。

　　而且，多数连接mysql的库函数都可以获得全部结果并缓存到内存里，还可以逐行获取所需要的数据。默认一般是获得全部结果并缓存到内存中。mysql通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接受全部结果并缓存通常可以减少服务器的压力，让查询能够早点结束、早点释放对应的资源。

2.2.3 查询状态

对于mysql连接，任何时刻都有一个状态，该状态表示了mysql当前正在做什么。使用show full processlist命令查看当前状态。在一个查询生命周期中，状态会变化很多次，下面是这些状态的解释：

• sleep：线程正在等待客户端发送新的请求；
• query：线程正在执行查询或者正在将结果发送给客户端；
• locked：在mysql服务器层，该线程正在等待表锁。在存储引擎级别实现的锁，例如InnoDB的行锁，并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态。
• analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的执行计划；
• copying to tmp table：线程在执行查询，并且将其结果集复制到一个临时表中，这种状态一般要么是做group by操作，要么是文件排序操作，或者union操作。如果这个状态后面还有on disk标记，那表示mysql正在将一个内存临时表放到磁盘上。
• sorting Result：线程正在对结果集进行排序。
• sending data：线程可能在多个状态间传送数据，或者在生成结果集，或者在向客户端返回数据。

2.2.4 查询缓存

　　在解析一个查询语句之前，如果查询缓存是打开的，那么mysql会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同，那也不会匹配缓存结果，这种情况下查询就会进入下一阶段的处理。

　　如果当前的查询恰好命中了查询缓存，那么在返回查询结果之前mysql会检查一次用户权限。这仍然是无须解析查询SQL语句的，因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题，mysql会跳过所有其他阶段，直接从缓存中拿到结果并返回给客户端。这种情况下，查询不会被解析，不用生成执行计划，不会被执行。

2.2.5 查询优化处理

查询的生命周期的下一步是将一个SQL转换成一个执行计划，mysql再依照这个执行计划和存储引擎进行交互。这包含多个子阶段：解析SQL、预处理、优化SQL执行计划。这个过程中任何错误都可能终止查询。

• 语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否合法。
• 查询优化器：当语法树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。
• 执行计划：mysql不会生成查询字节码来执行查询，mysql生成查询的一棵指令树，然后通过存储引擎执行完成这棵指令树并返回结果。最终的执行计划包含了重构查询的全部信息。

2.2.6 查询执行引擎

　　在解析和优化阶段，mysql将生成查询对应的执行计划，mysql的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构，而不是和很多其他的关系型数据库那样对应的字节码。

　　mysql简单的根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成。为了执行查询，mysql只需要重复执行计划中的各个操作，直到完成所有的数据查询。

2.2.7 返回结果给客户端

　　查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果给客户端，mysql仍然会返回这个查询的一些信息，如该查询影响到的行数。如果查询可以被缓存，那么mysql在这个阶段也会将结果放到查询缓存中。

       mysql将结果集返回客户端是一个增量、逐步返回的过程。这样有两个好处：服务器端无须存储太多的结果，也就不会因为返回太多结果而消耗太多的内存；这样处理也让msyql客户端第一时间获得返回的结果。

　　结果集中的每一行都会以一个满足mysql客户端/服务器通信协议的包发送，再通过tcp协议进行传输，在tcp传输的过程中，可能对mysql的封包进行缓存然后批量传输。

三、MySql连接方式

3.1 小结

　　mysql是应用，我们需要实现的是mysql客户端与服务端进行通信，这里好比http，所以在客户端找到服务端之前，就需要他们所处的物理机先建立起连接，就如同http建立连接之前，需要tcp先建立连接。Mysql的主要连接方式包括：Unix套接字，内存共享，命名管道，TCP/IP套接字等。

3.2 Unix套接字

　　 在计算机世界，一个socket是一种内部进程通信形式，它被用于在相同主机上形成进程间的双向通信连接点(在本地系统上的一个物理文件)。所以在Linux和Unix环境下，可以使用Unix套接字进行Mysql服务器的连接；Unix套接字其实不是一个网络协议，只能在客户端和Mysql服务器在同一台电脑上才可以使用。

3.3 命名管道和内存共享

在window系统中客户端和Mysql服务器在同一台电脑上，可以使用命名管道和共享内存的方式，

• 命名管道开启：–shared-memory=on/off；
• 共享内存开启：–enable-named-pipe=on/off;

3.4 命名管道和内存共享

　　任何系统下都可以使用的方式，也是使用的最多的方式，其实熟悉操作系统的朋友应该能体会出来，像前两种，因为客户端和服务端在同一台主机上，也就是一台主机的两个应用，所以这也就是进程间通信的方式，而在不同的主机上就不一样了，就需要网络，tcp/ip建立了。

四、MySql客户端与服务端的交互过程

4.1 小结

主要分为两部分：握手认证阶段，命令执行阶段
注意哦，这个握手和上面过的握手不一样哦。

4.2 握手认证阶段

握手认证阶段为客户端与服务器建立（TCP）连接后进行，交互过程如下：

• 服务器 -> 客户端：握手初始化消息
• 客户端 -> 服务器：登陆认证消息
• 服务器 -> 客户端：认证结果消息

4.3 命令执行阶段

客户端认证成功后，会进入命令执行阶段，交互过程如下：

• 客户端 -> 服务器：执行命令消息
• 服务器 -> 客户端：命令执行结果

4.4 流程图

4.5 连接过程分析

4.5.1 为什么还要进行三次握手认证

　　因为tcp三次握手，只是将客户端与服务端建立起了连接，然后通过端口知道我要访问的是mysql这个服务，但是mysql它不同于http，只要你知道url就能得到一个响应，mysql必须登陆后你才能进行操作。所以这个过程最重要的就是验证客户端的登陆权限。

4.5.2 为什么是服务端主动给客户端发送认证呢？

4.5.2.1 服务端主动给客户端发送认证与请求响应的区别

　　http不是说只有客户端主动与服务端进行请求，服务端不是不能在没有请求的情况下主动进行响应吗？
　　首先，大家也不要陷入误区，在进行认证的这个交互中，实际上客户端与服务端还没有进行任何业务上的往来，只是进行一个认证，所以与上面说的http的不同，如果细心的话你会发现，认证成功后，在命令执行阶段，mysql这种通信方式是与http非常类似的，是一种半双工的通信机制，在没有请求的情况下，服务端的mysql也不会主动给你发送任何数据，所以这里不要混淆。

　　再说为什么服务端先发送，那肯定是因为他有不得不发送的道理，所以我们就需要理解一下，他发送的是什么东西。

4.5.2.2 MySQL报文

4.5.2.2.1 MySQL报文分类

主要分成三个部分：登录认证报文，客户端请求报文以及服务器端返回报文。

4.5.2.2.2 登陆认证报文（服务端->客户端）

• 协议版本号：服务端所使用的mysql协议的版本号
• 服务器线程ID：服务端为此客户端所创建的线程的ID
• 挑战随机数：MySQL数据库用户认证采用的是挑战/应答的方式，服务器生成该挑战数并发送给客户端，由客户端进行处理并返回相应结果，然后服务器检查是否与预期的结果相同，从而完成用户认证的过程。
• **服务器权能标志：**用于与客户端协商通讯方式

大致就是告诉客户端服务端能接受的通讯方式是什么样的。

4.5.2.2.3 登陆认证报文（客户端 -> 服务器）

• 客户端权能标志：客户端收到服务器发来的初始化报文后，会对服务器发送的权能标志进行修改，保留自身所支持的功能，然后将权能标返回给服务器，从而保证服务器与客户端通讯的兼容性。
• 消息长度：客户端发送请求时所支持的最大消息长度值
• 字符编码：表示通讯过程中使用的字符编码，与服务器在认证报文中发送的相同
• 用户名：客户端登陆的用户名
• 挑战认证数据：客户端用户密码使用服务器发送的挑战随机数进行加密后，生成挑战认证数据，返回给服务器用于服务端的认证

4.5.2.2.4 服务端认证结果报文（服务端->客户端）

　　服务端主要验证，用户名，密码是否正确存在，如果都是正确的，就返回ok报文，错误的话就是ERROR报文。

4.5.3 wireshark抓包验证实现

　　http://blog.itpub.net/28944233/viewspace-1665982/

五、参考文章

https://blog.csdn.net/gao_yu_long/article/details/74905490

https://www.cnblogs.com/kingle-study/p/9961748.html

https://blog.csdn.net/LYue123/article/details/89256577

https://blog.csdn.net/LYue123/article/details/89256577