MySql架构浅谈

一、MySQL基本逻辑架构图

                           

从上图可以看出，MySQL内部逻辑架构包括Server层以及下边的存储引擎层。Server层又包括连接器，查询缓存，分析器，优化器和执行器。存储引擎常见的包括：InnoDB，MyISAIM以及Memory等

二、Server层

1、连接器：

由图中可以看到，每一个客户端都是和Server层的连接器建立连接，连接器负责客户端与数据库建立连接，获取权限，维持和管理连接。通过以下命令来建立连接：

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

在这行命令之后，我们在交互命令中输入密码即可建立连接。（强烈不建议直接将密码附在-p后边，会导致密码泄漏）。连接命令中的 mysql 是客户端工具，用来跟服务端建立连接。在完成经典的 TCP 握手后，连接器就要开始认证你的身份，这个时候用的就是你输入的用户名和密码。

• 如果用户名或密码不对，你就会收到一个"Access denied for user"的错误，然后客户端程序结束执行。
• 如果用户名密码认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。

这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。建立连接之后，我们可以通过show processlist来查看已经建立的连接。

如果客户端一段时间内没有活跃行为，那么连接器在默认的8个小时后主动断开连接。如果在连接被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。这时候如果你要继续，就需要重连，然后再执行请求了。连接方式有：

• 短连接：每次查询几次之后会断开，再次查询需要重新建立连接。（成本较高）
• 长连接：长连接会导致内存OOM，导致MySQL异常重启。

那么如何解决长连接导致的OOM问题？

• 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

• 如果你用的是 MySQL 5.7 或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

2、查询缓存

这个查询缓存比较好理解，在每一次的查询时，我们都先去看看是否命中缓存，命中则直接返回，提高了系统的响应速度。但是这个功能有一个相当大的弊病，那就是一旦这个表中数据发生更改，那么这张表对应的所有缓存都会失效。对于更新压力大的数据库来说，查询缓存的命中率会非常低。除非你的业务就是有一张静态表，很长时间才会更新一次。比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。在MySQL 8.0 版本之前，我们可以通过将 参数query_cache_type 设置成 DEMAND，来关闭查询缓存的功能；在MySQL8.0版本之后直接完全删掉了这部分功能。

3、分析器：

系统在真正执行你输入的语句之前，必须分析出你的语句想要干嘛？首先通过select关键字得知这是一条查询命令，还包括分析你要查询的是哪张表以及查询条件是什么？同时，分析器必须分析你输入语句的语法正确性。相信我们都遇到过这个错误吧？  “You have an error in your SQL syntax” 

4、优化器：

优化器是MySQL用来对你输入的语句在真正执行之前所做的最后一步优化。优化内容包括：选择哪个索引？是否选择索引？多表查询的联合顺序等。 每一种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

5、执行器：

MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如下所示 (在工程实现上，如果命中查询缓存，会在查询缓存返回结果的时候，做权限验证。查询也会在优化器之前调用 precheck 验证权限)。

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

 如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。比如我们这个例子中的表 T 中，ID 字段没有索引，那么执行器的执行流程是这样的：

• 调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，如果不是则跳过，如果是则将这行存在结果集中；
• 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
• 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

至此，这个语句就执行完成了。

对于有索引的表，执行的逻辑也差不多。第一次调用的是“取满足条件的第一行”这个接口，之后循环取“满足条件的下一行”这个接口，这些接口都是引擎中已经定义好的。在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的。

6、总结：

MySQL的Server层主要包括：连接器，查询缓存，分析器，优化器，执行器。多个组件的共同配合，我们的SQL命令才可以执行成功。

二、存储引擎层

MySQL的存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。我们来创建一张简单的表：

mysql> create table T(c int) engine=InnoDB

通过engine来指定当前表所使用的数据存储引擎，通俗的说就是这一张表的类型。不同存储引擎的表数据存取方式不同，支持的功能也不同。接下来我们主要介绍 InnoDB、MyISAM这两种存储引擎。

1、InnoDB存储引擎：

InnoDB是当前MySQL的默认的存储引擎，也是互联网等公司数据库存储引擎的不二选择。

InnoDB的特性如下：

• 支持数据库事务，在可重复读的隔离级别下，通过MVCC解决了不可重复读的问题，通过间隙锁的引入解决了幻读的问题
• 支持行级锁和表级锁，默认是行级锁，更小的锁粒度意味着更高的并发度。
• 支持外键
• 为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。
• InnoDB中不保存表的行数（eg:select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行）；清空整个表时，InnoDB是一行一行的删除，效率非常慢。
• InnoDB使用B+ Tree来做索引，查询效率高，支持索引上的范围查询

2、MyISAM存储引擎

在MySQL5.5版本之前，其默认的存储引擎是MyISAM。MyISAM 管理非事务表、是ISAM 的扩展格式。除了提供ISAM里所没有的索引的字段管理等的大量功能、MyISAM 还使用一种表格锁定的机制、来优化多个并发的读写操作。MyISAM 提供高速存储和检索、以及全文搜索能力。

MyIASM存储引擎的特性如下：

• 不支持事务、不具备AICD特性(原子性、一致性、分离性、永久性)
• 表级别锁定形式(更新数据时锁定整个表、这样虽然可以让锁定的实现成本很小但是同时大大降低了其并发的性能)
• 读写相互阻塞(不仅会在写入的时候阻塞读取、还会在读取的时候阻塞写入、但是读取不会阻塞读取)
• 只会缓存索引(myisam通过key_buffer_size来设置缓存索引，提高访问性能较少磁盘IO的压力、但是只缓存索引、不缓存数据)
• 读取速度快、占用资源比较少
• 不支持外键约束、只支持全文检索

MyIASM存储引擎的应用场景：

• 不需要事务支持的场景
• 读多或者写多的单一业务场景、读写频繁的则不适合、会阻塞
• 读写并发访问较低的业务
• 数据修改相对较少的业务
• 以读为主的业务
• 对数据的一致性要求不是很高的业务
• 服务器硬件资源相对比较差的机器

三、参考资料

1、https://blog.csdn.net/qq_25827845/article/details/90544530