MySQL --- mysql架构

MySQL属于关系型数据库。顾名思义，关系型数据库就是一种建立在关系模型的基础上的数据库。关系模型表明了数据库中所存储的数据之间的联系（一对一、一对多、多对多）。关系型数据库中，我们的数据都被存放在了各种表中（比如用户表），表中的每一列就存放着一条数据（比如一个用户的信息）。

大部分关系型数据库都使用 SQL 来操作数据库中的数据。并且，大部分关系型数据库都支持事务的四大特性(ACID)。常见的关系型数据库还有MySQL、PostgreSQL、Oracle、SQL Server、SQLite（微信本地的聊天记录的存储就是用的 SQLite） ......。

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

mysql架构

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

• 服务层：第二层服务层，主要完成大部分的核心服务功能， 包括查询解析、分析、优化、缓存、以及所有的内置函数，所有跨存储引擎的功能也都在这一层实现，包括触发器、存储过程、视图等。

• 引擎层：第三层存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。

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

面试题：MySQL 的查询流程具体是？or 一条SQL语句在MySQL中如何执行的？

大体来说，MySQL 可以分为 Server 层和存储引擎层两部分。

• Server 层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖 MySQL 的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

• 而存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始成为了默认存储引擎。可以通过指定存储引擎的类型来选择别的引擎，比如在 create table 语句中使用 engine=memory，来指定使用内存引擎创建表。

具体流程：

• 客户端发出请求
• 连接器负责连接客户端（验证用户身份，给予权限）

mysql -h$ip -P$port -u$user -p
#输入用户名和密码进行验证
#连接成功后，可以用过show processlist查看连接，如果没有操作wait_timeout控制，默认8个小时关闭。

• 连接建立完成后，你就可以执行 select 语句了。查询缓存（存在缓存则直接返回结果，不存在则执行后续操作）
  查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存（之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。）都会被清空。所以查询缓存的命中率会非常低（不建议）。
• 分析器（对SQL语句进行语法分析和词法分析操作）
  如果没有命中查询缓存，就要开始真正执行语句了。首先，MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析。
  词法分析：你输入的是由多个字符串和空格组成的一条 SQL 语句，MySQL 需要识别出里面的字符串分别是什么，代表什么。检查查询的表或者列是否存在。
  语法分析：语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。比如 select 少打了开头的字母“s”。
• 优化器（主要对执行的SQL优化，执行最优的方案）
  经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。优化器的工作：优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。
• 执行器（先检查用户是否有执行权限，有才会使用这个引擎提供的接口）
  MySQL 通过分析器知道了你要做什么，通过优化器知道了该怎么做，于是就进入了执行器阶段，开始执行语句。
  开始执行的时候，要先判断一下你对这个表 T 有没有执行查询的权限，如果没有，就会返回没有权限的错误，如果有权限，就打开表继续执行。打开表的时候，执行器就会根据表的引擎定义，去使用这个引擎提供的接口。
• 去引擎层获取数据的返回（如果开启查询缓存，则会缓存查询结果）

sql执行流程

重要的日志模块：redo log 和 binlog

前面我们说过，在一个表上有更新的时候，跟这个表有关的查询缓存会失效，所以这条语句就会把表 T 上所有缓存结果都清空。这也就是我们一般不建议使用查询缓存的原因。

接下来，分析器会通过词法和语法解析知道这是一条更新语句。优化器决定要使用 ID 这个索引。然后，执行器负责具体执行，找到这一行，然后更新。

与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是我们今天要讨论的主角：redo log（重做日志）和 binlog（归档日志）。

• 如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程 IO 成本、查找成本都很高。为了解决这个问题，MySQL 的设计者就用了类似酒店掌柜粉板的思路来提升更新效率（先记录，等不忙时处理）。
  redo log固定大小，从头开始写，写到末尾就又回到开头循环写。其中write pos （当前记录的位置）和 checkpoint（当前要擦除的位置） 之间的是“粉板”上还空着的部分，可以用来记录新的操作。如果 write pos 追上 checkpoint，表示“粉板”满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把 checkpoint 推进一下。
  ps：有了 redo log，InnoDB 就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为 crash-safe。要理解 crash-safe 这个概念，可以想想我们前面赊账记录的例子。只要赊账记录记在了粉板上或写在了账本上，之后即使掌柜忘记了，比如突然停业几天，恢复生意后依然可以通过账本和粉板上的数据明确赊账账目。

• MySQL 整体来看，其实就有两块：一块是 Server 层，它主要做的是 MySQL 功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。 redo log（重做日志） 是 InnoDB 引擎特有的日志，而 Server 层也有自己的日志，称为 binlog（归档日志）。
  ps：因为最开始 MySQL 里并没有 InnoDB 引擎。MySQL 自带的引擎是 MyISAM，但是 MyISAM 没有 crash-safe 的能力，binlog 日志只能用于归档。而 InnoDB 是另一个公司以插件形式引入 MySQL 的，既然只依靠 binlog 是没有 crash-safe 能力的，所以 InnoDB 使用另外一套日志系统——也就是 redo log 来实现 crash-safe 能力。

两种日志模块的区别：

• redo log 是 InnoDB 引擎特有的；binlog 是 MySQL 的 Server 层实现的，所有引擎都可以使用。
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，比如“给 ID=2 这一行的 c 字段加 1 ”。
• redo log 是循环写的，空间固定会用完；binlog 是可以追加写入的。“追加写”是指 binlog 文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

将 redo log 的写入拆成了两个步骤：prepare 和 commit，这就是"两阶段提交"。

如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。redo log 和 binlog 都可以用于表示事务的提交状态，而两阶段提交就是让这两个状态保持逻辑上的一致（两阶段提交是跨系统维持数据逻辑一致性时常用的一个方案）。

一个 SQL 执行的很慢，我们要分两种情况讨论:

• 偶尔很慢，则有如下原因:

(1)数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。
(2)执行的时候，遇到锁，如表锁、行锁。

• 这条 SQL 语句一直执行的很慢，则有如下原因:

(1)没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。
(2)数据库选错了索引。
(3)考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

大表数据查询，怎么优化？

（1）优化shema、sql语句+索引；
（2）第二加缓存，memcached, redis；
（3）主从复制，读写分离；
（4）垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
（5）水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key， 将数据定位到限定的表上去查，而不是扫描全部的表；

mysql有什么特点（优点）

（1）MySQL数据库是用C和C++语言编写的，并且使用了多种编辑器进行测试，以保证源码的可移植性
（2）支持多个操作系统，例如：Windows、Linux、Mac OS等等
（3）支持多线程，可以充分的利用CPU资源
（4）为多种编程语言提供API，包括C语言，Java，PHP。Python语言等
（5）MySQL优化了SQL算法，有效的提高了查询速度
（6）MySQL内提供了用于管理，检查以及优化数据库操作的管理工具
（7）它能够作为一个单独的应用程序应用在客户端服务器网络环境中，也可以作为一个库嵌入到其他的软件中并提供多种语言支持
（8）支持多种存储引擎。

Oracle数据库和MySQL数据库的不同之处

1、体积不同。Oracle它体积比较庞大，一般是用来开发大型应用（例如分布式）的。而MySQL的体积相对来说比较小，较之Oracle更容易安装、维护以及管理，操作也简单
2、容量不同。Oracle容量无限，根据配置决定。
3、平台支持及速度的区别。Oracle支持大多数平台；而MySQL支持各种平台，适合Linux
4、数据库崩溃造成的影响不同。Oracle数据库崩溃后恢复很麻烦，因为他把很多东西放在内存里
5、性能的区别。Oracle全面，完整，稳定，但一般数据量大，对硬件要求较高 ；而MySQL使用CPU和内存极少，性能很高，但扩展性较差。

分库分表如何选择？

为什么要分库分表（设计高并发系统的时候，数据库层面应该如何设计）？

首先要清楚，分库和分表是两回事，是两个独立的概念。分库和分表都是为了防止数据库服务因为同一时间的访问量（增删查改）过大导致宕机而设计的一种应对策略。

• 为什么要分库

按一般的经验来说，一个单库最多支持并发量到2000，且最好保持在1000。如果有20000并发量的需求，这时就需要扩容了，可以将一个库的数据拆分到多个库中，访问的时候根据一定条件访问单库，缓解单库的性能压力。

• 为什么要分表

分表也是一样的，如果单表的数据量太大，就会影响SQL语句的执行性能。分表就是按照一定的策略将单表的数据拆分到多个表中，查询的时候也按照一定的策略去查询对应的表，这样就将一次查询的数据范围缩小了。比如按照用户id来分表，将一个用户的数据就放在一个表中，crud先通过用户id找到那个表在进行操作就可以了。这样就把每个表的数据量控制在一定范围内，提升SQL语句的执行性能。

• 用过哪些分库分表的中间件？不同的分库分表中间件都有什么优点和缺点？

分库分表常见的中间件有：cobar、TDDL、atlas、sharding-jdbc和mycat等。

cobar：cobar是阿里的b2b团队开发和开源的，属于proxy层方案，介于应用服务器和数据库服务器之间。应用程序通过JDBC驱动访问cobar集群，cobar根据SQL和分库规则对SQL做分解，然后分发到MySQL集群不同的数据库实例上执行。cobar并不支持读写分离、存储过程、跨库join和分页等操作。早些年还可以用，但是最近几年都没更新了，基本没啥人用，算是淘汰了。

TDDL：TDDL是淘宝团队开发的，属于client层方案。支持基本的crud语法和读写分离，但是并不支持join、多表查询等语法。目前使用的也不多，因为使用还需要依赖淘宝的diamond配置管理系统。

atlas：atlas是360开源的，属于proxy层方案。以前是有一些公司再用的，但是社区最新的维护都在5年前了，现在用的公司也基本没有了。

sharding-jdbc：sharding-jdbc是当当开源的，属于client层方案。这个中间件对SQL语法的支持比较多，没有太多限制。2.0版本也开始支持分库分表、读写分离、分布式id生成、柔性事务（最大努力送达型事务、TCC事务）。目前社区也还一直在开发和维护，算是比较活跃，是一个现在也可以选择的方案。

mycat：mycat是基于cobar改造的，属于proxy层方案。其支持的功能十分完善，是目前非常火的一个数据库中间件。社区很活跃，不断在更新。相比于sharding-jdbc来说，年轻一些，经历的锤炼也少一些。

• 总结

综上所述，现在建议考量使用的就是sharding-jdbc和mycat。

sharding-jdbc这种client层的优点在于不用部署，因此运维成本也就比较低。同时因为不需要代理层的二次转发请求，性能很高。但是如果遇到升级的话，需要各个系统都重新升级版本再发布，因为各个系统都需要耦合sharding-jdbc的依赖。

mycat这种proxy方案的缺点在于需要部署，因此运维成本也就比较高。但是优点在于其对于各个项目是透明（解耦）的，如果要升级的话只需要在中间件处理就行了。

通常来说，这两个方案都是可以选用的。但是建议中小型公司选用sharding-jdbc比较好，因为client层方案轻便，维护成本低；建议中大型公司选用mycat比较好，因为proxy层方案可以应对多个系统和项目大量使用，虽然维护成本相对来说会较高，但是中大型公司还缺这点人力吗。

• 水平拆分的概念

水平拆分的意思，就是把一个表的数据拆分到多个库的多个表里面去。这里面的每个库的表结构都是一样的，只不过是表中存放的数据不一样，每个库表的数据汇总起来就是全部数据。水平拆分的意义在于将数据均匀地存放在各个库表里，依靠多个库来杠更高的并发，而且还能借助多个库的存储容量来进行扩容。

• 垂直拆分的概念

垂直拆分的意思，就是把一个有很多字段的表给拆分成多个表或者多个库上面去，每个库表的结构都不一样，每个库表都包含部分字段。一般来说，会将较少的访问频率很高的字段放到一个表里面去，然后将较多的访问频率很低的字段放到另外一个表里面去。因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里面缓存更多的行，性能也就越好。这个一般在表层面做的较多一些。

• 水平拆分和垂直拆分的场景

所谓表层面的拆分，就是分表。具体就是将一个表拆分为N个表，让每个表的数据量控制在一定的范围内，保证SQL的性能。否则，单表的数据量越大，SQL的性能也就越差，一般是200万行左右，不要太多。如果你的SQL越复杂，就尽量让单表的行数越少。

无论是分库还是分表，主流的数据库中间件都是可以支持的。这些中间件可以在你分库分表之后，根据指定的某个字段值自动路由到对应的库和对应的表上面。这时就只要考虑项目如何分库分表就行了。一般来说，垂直拆分，可以在表层面做，即对一些字段特别多的表做一下拆分；水平拆分的话，可能是因为并发承载不了或容量承载不了，也就可以按某个字段去分布到不同的库表里面去。

• 分库分表的两个方案，这里说一下两种分库分表的方案和它们的优缺点。

1.按照range来分。比如说按照时间范围来分库分表，每个库表中存放的都是连续时间范围的数据。但是这种方式一般很少用，因为很容易会产生热点问题，大量的流量都打在最新的数据上了。这种方案的优点在于扩容的时候非常简单，比如只要预备好每个月都准备一个库就可以了，到了下一个新的月份自动将数据写入新的库。缺点则是，如果大部分请求都是访问最新的数据，那么在这里，分库分表的设计目的就只是简单的扩容，而不是为了应对高并发了。

2.按照hash分发。按照某个字段的hash值均匀分散，这个较为常用。优点在于可以平均分配每个库表的数据量和请求压力；缺点在于扩容比较麻烦，因为会存在一个数据迁移的过程，即之前的数据需要重新计算hash值并重新分配到不同的库表中。

巨人的肩膀：

https://zhuanlan.zhihu.com/p/29150809
https://juejin.im/post/5e3eb616f265da570d734dcb#heading-105
https://blog.csdn.net/yin767833376/article/details/81511377
https://www.cnblogs.com/yanggb/p/11214339.html