01、基础架构

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎

一、连接层

BIO、NIO和AIO MySQL连接层

最上层是一些客户端和连接服务。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限

MySQL的BIO

• mysql在启动后，创建了socket server，绑定了3306端口，并对其进行监听。和java里的写法类似，用一个while循环来监听新来的connection，如果有了新连接，就创建一个新的连接线程去处理（不能超过max-connections），这种就是典型的BIO的模式，为每一个连接创建一个线程

• mysql基于BIO，本质上是不接受大量的socket连接的，原因懂BIO的都知道，所以设置了max-connections这个限制，超过设置的max，那么新来的连接会被拒绝

• tomcat、netty之类的web服务器，都是基于NIO+IO多路复用的模式，来大幅提升性能，承载高并发访问的。但是到了DB层，就变成了hakiri、druid之类的线程池，开启10个线程去连接mysql，反复复用这个线程池

为什么MySQL不使用NIO

1. 原因很简单，JDBC不支持，JDBC出现了20年了，它是一个标准，在它被提出时，只有BIO模型一家，还没有别的IO呢，你用个毛线。各家数据库驱动对JDBC的实现都是BIO的形式，你的mysql驱动connector早早地实现了JDBC标准，就是采用阻塞的方式。当你进行一个select查询，在查询没有完成之前，整个调用线程会被卡住，等到天荒地老也要等下去，绝不是一请求立马收到返回，然后等mysql回调你结果

2. 民间也有人修改了mysql的协议，增加了NIO+多路复用的功能，但是本身形不成气候，根本原因还是mysql和web server功能意义都不一样，确切地说，90%的场景下，你不需要一个NIO的数据库

3. BIO+连接池已经发展了很多年，大部分问题都已经解决，在目前的java环境中，是非常靠谱的方案。已经出现了很多优秀的连接池框架，你只需要配置好账号密码和连接池数量，就能很开心的使用mysql了

4. 而从mysql的角度来说，客户端多是一些IO密集型的应用，在一个线程里频繁做大量IO操作，而不是说有巨多的客户端来反复连接我。毕竟，mysql的用户是你写的几个程序应用，而web server的用户是千千万的吃瓜群众

连接池

1. MySQL能支撑的连接数是有限的，那么就需要应用程序来利用好连接池。对于连接池来说，就是做好这10个连接的管理就好

   1. 对于MySQL来说，管理好自己的所有连接也是很重要，哪些空闲的该休息就休息，让出资源，该复用就复用，避免创建太多线程

2. 要记住一个基本原则，IO密集的时候，要减少连接数。譬如你要读写几千万数据，非常密集的IO操作，那么你可以尝试一下开启少量的MySQL连接（和cpu的核数相同）和开启大量的连接（成百上千），来做同样的事。你会发现，更少的连接数，会给你带来几百倍的性能提升

   1. 原因也很简单，你有4辆车（4核），要从仓库里拉一大堆货物到另一个地方。如果只有4条路，那么很简单，一个车走一路，反复折返跑就是了。现在有了100条路，你还是4个车，你的车要频繁的在100条路上来回切换，每条路都必须要走到，你说哪个快
   2. 线程间的轮转会耗费大量的资源，尤其在密集操作时。但是当不密集时，情况就变了，大量的线程处于休息状态，那么你即便多开了几个连接，CPU还是能很快的照顾到你，并没有什么大的影响

MySQL通信过程

MySQL网络协议分析

基于的就是tcp的底层协议，所以必然，需要经历tcp的三次握手，没错第一步就是tcp的三次握手（因为这里不是重点，就不详细说明，不太清楚的同学，可以查看我network的相关博客），建立连接之后就可以发送sql命令了吗，当然不能，细心的同学会发现，我用客户端登陆的时候，是需要用户名，密码的，这才是真正的mysql客户端与服务端的交互过程，之前还没有到应用层，下面就说一下，交互过程

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

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

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

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

二、服务层

1、连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接

mysql -h$ip -P$port -u$user -p

1. 连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码
2. 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限
3. 一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置

短连接、长连接

• 长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接
• 短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个

长连接优点
建立连接的过程通常是比较复杂的，所以使用长连接能避免多次创建连接

长连接缺点
使用长连接后，mysql占用的内存会不断上升。因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了

解决办法
1、定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连
2、如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态

2、查询缓存

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。如果你的查询能够直接在这个缓存中找到 key，那么这个 value 就会被直接返回给客户端

不建议使用查询缓存
查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空

将参数 query_cache_type 设置成 DEMAND，这样对于默认的 SQL 语句都不使用查询缓存；对于你确定要使用查询缓存的语句，可以用 SQL_CACHE 显式指定：select SQL_CACHE * from T where ID=10；

MySQL 8.0 版本直接将查询缓存的整块功能删掉了

3、分析器

对 SQL 语句做解析

1. 词法分析：解析语句，生成解析树

   1. 输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分 别是什么，代表什么
   2. MySQL 从你输入的”select”这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别成“表名 T”，把字符串“ID”识别成“列 ID”

2. 语法分析：根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法

4、优化器

优化sql执行效率

1. 优化器是在表里面有多个索引的时候，决定使用哪个索引
2. 在一个语句有多表关联（join）的时候，决定各个表的连接顺序

5、执行器

执行sql语句

1. 开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限

2. 如果没有，就会返回没有权限的错误，如下所示

   1. 在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限

      mysql> select * from T where ID=10; 
      ERROR 1142 (42000): SELECT command denied to user 'b'@'localhost' for table 'T'

3. 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口

三、存储引擎层

InnoDB设计思路

• 插件式的存储引擎架构模式，将查**询处理和其它的系统任务以及数据的存储提取相分离**。这种架构可以根据业务的需求和实际需要选择合适的存储引擎
• 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取

InnoDB、MyISAM、Memory

四、存储层

主要是将数据存储在运行于该设备的文件系统之上，并完成与存储引擎的交互