MySQL架构设计

共计 7345 个字符，预计需要花费 19 分钟才能阅读完成。

MySQL驱动

MySQL给各个语言都提供了MySQL驱动，封装了最底层的网络通信，提供Connection连接对象。基于这个Connection连接就可以和MySQL服务器通信了，比如增删改查。 MySQL架构设计

指定配置文件

mysqld –defaults-file=/tmp/myconfig.txt

数据库连接池

一个Java系统不可能只跟数据库建立一个连接,假设一个web系统使用tomcat部署
tomcat容器是多线程的，他们去抢夺一个connection去访问数据库的话，性能肯定低。每个线程都去创建使用和销毁connection的话，建立网络连接是低效率的。 MySQL架构设计池子的好处是：
一批建立好连接的connection扔到池子里面去，用的时候去池子里面拿，不用的时候还到池子里面，也不去销毁，后续可以继续使用。解决了并发建立connection和connection销毁的问题MySQL中的连接池就是维护了与系统之间的多个数据库连接。除此之外，系统每次跟MySQL建立连接的时候，还会根据你传递过来的账号和密码，进行账号密码的验证，库表权限的验证。 MySQL架构设计

案例

手写jdbc连接数据库

 public static void query(int id) throws SQLException {
//加载驱动
Class.forName(DRIVER_CLASS);
//获取连接
Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
//书写sql语句
String sql = "SELECT * FROM tb_user WHERE id = ?";
PreparedStatement ps = null;
ResultSet rs = null;
try {
//创建 Statement对象
ps = connection.prepareStatement(sql);
//设置查询参数
ps.setInt(1, id);
//执行sql并获取结果集
rs = ps.executeQuery();
while (rs.next()) {
id = rs.getInt(1);
String userName = rs.getString(2);
String telPhone = rs.getString(8);
System.out.println("id=" + id + "tuserName=" + userName + "ttelPhone=" + telPhone);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 关闭记录集
rs.close();
//关闭声明
ps.close();
//关闭连接对象
connection.close();
}
}

服务端处理connection流程

服务端需要一个线程来监听connection，有数据来了，就从connection读取数据并解析数据
sql语句从connection读取出来了之后，就会交给SQL接口来处理，SQL接口可以理解为一个门面(网关/入口)他是一套执行SQL语句的接口，专门用于执行我们发送给MySQL的那些增删改查的SQL语句.
执行查询解析器（Parser）对SQL语句进行解析。所谓的SQL解析，就是按照既定的SQL语法，对我们按照SQL语法规则编写的SQL语句进行解析，然后理解这个SQL语句要干什么事情。
执行查询优化器（Optimizer）来选择一个最优的查询路径。
调用存储引擎接口，真正执行SQL语句

MySQL架构设计

查询解析器

一条SQL语句，是人使用的语法，MySQL没办法理解的，MySQL需要转化成自己能理解的语法。那么MySQL就提供了一个查询解析器去解析SQL语句，对SQL进行拆解，比如：select id,name,age from user where id=1，MySQL就拆解成

从user表查询数据
查询id=1那一行的数据
查出来的数据，提取出id、name、age三个字段

SQL解析，就是对符合SQL语言的SQL语句进行分析和拆解

查询优化器

SQL的执行，可能会有多种路径，比如遍历表中需要的字段，一条一条对比id，或者直接根据id定位到那一条数据取需要的字段。
查询优化器，要优化出一条最优的查询路径，提高查询的效率。他会生成一个查询路径树，然后从里面选择一条最优查询路径，你就按照这个查询的步骤和顺序来执行操作就好了。

执行器

查询优化器给出来一条SQL执行计划，就需要有人来执行这个执行计划。执行器接下来就会根据这个执行计划，去多次调度存储引擎的接口；执行器是非常核心的一个组件，负责跟存储引擎配合完成一个SQL语句在磁盘与内存层面的全部数据更新操作。

存储引擎接口

存储引擎是真正的存储和处理数据的地方，数据主要是存储在内存和磁盘。存储引擎接口，是一个Facade（外观模式），对执行器提供简单的调用方法，屏蔽掉了内部复杂的处理逻辑。

存储引擎

为满足不同的场景需求，比如性能、事务、存储限制、索引的支持等等，MySQL提供了三种存储引擎
三种模型：

完全基于内存存储的，要求速度快，性能高，但是存储的容量小，数据会丢失
完全基于磁盘的，存储容量大，数据不丢失，但是速度慢，性能低；
基于内存+磁盘，兼顾上面两种的优缺点。

InnoDB的BufferPool

直接读写磁盘文件的效率太低，MySQL在操作数据的时候，先将数据加载到内存中在进行操作。

BufferPool

MySQL服务器启动的时候就向操作系统申请了一片连续的内存，他们给这片内存起了个名，叫做Buffer Pool，默认情况下Buffer Pool只有128M大小。可以在通过修改innodb_buffer_pool_size参数的值来调整Buffer Pool的大小。
[server]
innodb_buffer_pool_size = 268435456

BufferPool内部结构

Buffer Pool默认的缓存页和磁盘上的页大小是一样的，都是16KB，Buffer Pool每一个缓存页都创建了一些所谓的控制信息，这些控制信息包括该页所属的表空间编号、页号、缓存页在Buffer Pool中的地址、链表节点信息、一些锁信息以及LSN信息。 MySQL架构设计整体在内存中的结构是这样子的，这里的描述数据其实就是控制信息。每个（描述数据）控制块大约占用缓存页大小的5%，大约800B

MySQL架构设计

BufferPool初始化

数据库启动时，会按照设置的Buffer Pool大小，稍微再加大一点，去找操作系统申请一块内存区域，作为Buffer Pool的内存区域。当内存区域申请完毕之后，数据库就会按照默认的缓存页的16KB的大小以及对应的800个字节左右的描述数据（控制数据）的大小，在Buffer Pool中划分出来一个一个的缓存页和一个一个的他们对应的描述数据（控制数据）。

查看BufferPool信息

SHOW ENGINE INNODB STATUS

多个Buffer Pool

Buffer Pool本质是InnoDB向操作系统申请的一块连续的内存空间，在多线程环境下，访问Buffer Pool中的各种链表都需要加锁处理，在Buffer Pool特别大而且多线程并发访问特别高的情况下，单一的Buffer Pool可能会影响请求的处理速度。所以在Buffer Pool特别大的时候，可以把它们拆分成若干个小的Buffer Pool，每个Buffer Pool都称为一个实例，它们都是独立的，独立的去申请内存空间，独立的管理各种链表等待，所以在多线程并发访问时并不会相互影响，从而提高并发处理能力。可以通过设置innodb_buffer_pool_instances的值来修改Buffer Pool实例的个数。
[server]
innodb_buffer_pool_instances = 2
innodb_buffer_pool_size = 268435456
这样就表明我们要创建2个Buffer Pool实例。 MySQL架构设计

配置Buffer Pool时的注意事项

服务器启动时通过配置innodb_buffer_pool_size启动参数来调整大小，在服务器运行过程中是不允许调整该值的。主要是因为Buffer Pool需要连续的内存，比如你8g调整到16g，就需要把8g的数据拷贝到16gb去。因此最好不要这么做
innodb_buffer_pool_size必须是innodb_buffer_pool_chunk_size × innodb_buffer_pool_instances的倍数
推荐Buffer Pool配置成总内存的50%-60%
Buffer Pool实例向操作系统申请内存空间，不是一次性申请连续的空间，而是以一个所谓的chunk为单位向操作系统申请空间。也就是说一个Buffer Pool实例其实是由若干个chunk组成的，一个chunk就代表一片连续的内存空间，里边儿包含了若干缓存页与其对应的控制块，画个图表示

总结

磁盘太慢，用内存作为缓存很有必要。
Buffer Pool本质上是InnoDB向操作系统申请的一段连续的内存空间，可以通过innodb_buffer_pool_size来调整它的大小。
Buffer Pool向操作系统申请的连续内存由控制块和缓存页组成，每个控制块和缓存页都是一一对应的，在填充足够多的控制块和缓存页的组合后， Buffer Pool剩余的空间可能产生不够填充一组控制块和缓存页，这部分空间不能被使用，也被称为碎片。
InnoDB使用了许多链表来管理Buffer Pool。
free链表中每一个节点都代表一个空闲的缓存页，在将磁盘中的页加载到Buffer Pool时，会从free链表中寻找空闲的缓存页。
为了快速定位某个页是否被加载到Buffer Pool，使用表空间号 + 页号作为key，缓存页地址作为value，建立哈希表。
在Buffer Pool中被修改的页称为脏页，脏页并不是立即刷新，而是被加入到flush链表中，待之后的某个时刻同步到磁盘上。
LRU链表分为热数据和冷数据两个区域，可以通过innodb_old_blocks_pct来调节冷数据区域所占的比例。首次从磁盘上加载到Buffer Pool的页会被放到冷数据区域的头部，在innodb_old_blocks_time间隔时间内访问该页不会把它移动到热数据区域头部。在Buffer Pool没有可用的空闲缓存页时，会首先淘汰掉冷数据区域的一些页。
我们可以通过指定innodb_buffer_pool_instances来控制Buffer Pool实例的个数，每个Buffer Pool实例中都有各自独立的链表，互不干扰。
自MySQL 5.7.5版本之后，可以在服务器运行过程中调整Buffer Pool大小。每个Buffer Pool实例由若干个chunk组成，每个chunk的大小可以在服务器启动时通过启动参数调整。
可以用下边的命令查看Buffer Pool的状态信息：
SHOW ENGINE INNODB STATUS

free链表

数据库启动后，并没有真实的磁盘页被缓存到Buffer Pool中，之后随着程序的运行，会不断的有磁盘上的页被缓存到Buffer Pool中。为了区分Buffer Pool中哪些缓存页是空闲的，哪些已经被使用了，需要在某个地方记录一下Buffer Pool中哪些缓存页是可用的，这个时候缓存页对应的控制块就派上大用场了， 把所有空闲的缓存页对应的控制块作为一个节点放到一个链表中，这个链表也可以被称作**free**链表（或者说空闲链表）。刚刚完成初始化的Buffer Pool中所有的缓存页都是空闲的，所以每一个缓存页对应的控制块都会被加入到free链表中。

链表头结点

这个free链表，他本身其实就是由Buffer Pool里的描述数据（控制数据）块组成的，可以认为是每个描述数据块里都有两个指针，一个是free_pre，一个是free_next，分别指向自己的上一个free链表的节点，以及下一个free链表的节点。不会单独开辟空间去存储**free**链表，但是为了管理好这个free链表，特意为这个链表定义了一个基节点，里面包含着链表的头节点地址，尾节点地址，以及当前链表中节点的数量等信息。 ** 链表的基节点占用的内存空间并不包含在Buffer Pool内，而是单独申请的一块内存空间占40个字节**。

MySQL架构设计这个free链表里面就是各个缓存页的控制信息，只要缓存页是空闲的，那么他们对应的描述数据块就会加入到这个free链表中，每个节点都会双向链接自己的前后节点，组成一个双向链表。

磁盘数据加载

每当需要从磁盘中加载一个页到Buffer Pool中时，就从free链表中取一个空闲的缓存页，并且把该缓存页对应的控制块的信息填上（就是该页所在的表空间、页号之类的信息），然后把该缓存页对应的free链表节点从链表中移除，表示该缓存页已经被使用了 MySQL架构设计

缓存页的哈希处理

既然bufferpool的设计是为了提高性能，尽量在内存里面操作数据，那操作一条记录，得先去检查在bufferpool里面是否存在。
那如何快速的知道一条记录是否命中缓存呢？
MySQL又设计了一个哈希表，用表空间号 + 页号作为key，** 缓存页地址作为value**，在需要访问某个页的数据时，先从哈希表中根据表空间号 + 页号看看有没有对应的缓存页，如果有，直接使用该缓存页就好，如果没有，那就从free链表中选一个空闲的缓存页，然后把磁盘中对应的页加载到该缓存页的位置。

flush链表

凡是修改过的缓存页对应的描述数据都会作为一个节点加入到一个链表中，因为这个链表节点对应的缓存页都是需要被刷新到磁盘上的，所以也叫flush链表。链表的构造和free链表差不多。 MySQL架构设计

脏页

修改了Buffer Pool中某个缓存页的数据，那它就和磁盘上的页不一致了，这样的缓存页也被称为脏页。如果一个缓存页成为了脏页，它会把它的描述数据块加入到flush链表里面，io线程去刷盘的时候，根据flush链表刷新脏页。（产生脏页不会立刻刷入磁盘）

链表头结点

flush链表和free链表一样，也有一个不属于BufferPool的外置node节点，里面记录有多少count的脏页，第一个脏页和最后一个脏页的地址。

LRU链表

LRU链表（最近最少使用链表）是为了解决缓存页不够用时，将哪些脏页刷入磁盘的问题。原则上刷盘策略肯定是最少使用的先淘汰掉，因此引入LRU链表，使用最近最少使用算法。 MySQL架构设计

LRU工作机制

只要使用到某个缓存页，就把该缓存页调整到LRU链表的头部，这样LRU链表尾部就是最近最少使用的缓存页，

链表头结点

lru链表和free链表一样，也有一个不属于BufferPool的外置node节点，里面记录有多少count的脏页，第一个脏页和最后一个脏页的地址。

单纯的LRU链表带来的问题

加载到Buffer Pool中的页不一定被用到(因为MySQL预读机制的存在还有全表扫描的查询例如select * from table)。
如果非常多的使用频率偏低的页被同时加载到Buffer Pool时，可能会把那些使用频率非常高的页从Buffer Pool中淘汰掉(因为频率低的被移动到链表表头了，单纯的LRU 是根据最近使用来插入表头的，不是根据使用频率)。

解决简单LRU链表淘汰掉热点数据的问题

因为有上述两种情况的存在，所以引入优化版的LRU链表，采用的思想是冷热数据分离，这种LRU链表按照一定比例分成两截，分别是：一部分存储使用频率非常高的缓存页，所以这一部分链表也叫做热数据，或者称young区域。另一部分存储使用频率不是很高的缓存页，所以这一部分链表也叫做冷数据，或者称old区域。

数据比例
冷热数据的比例可以通过innodb_old_blocks_pct参数调整，默认冷数据占37%
加载方式

第一次加载数据的时候，加载到冷区域头部。如果这个缓存页1s后，还有被使用，说明它可能经常被使用，那么就移动到热数据区域头部。通过冷热数据分离，每次淘汰，淘汰冷数据区域就可以了。放进来1s后都没有的数据，就会变成冷数据

解决热数据区域的链表频繁节点移动问题

热数据区域的数据本来就是热数据，访问一次就提到头部，肯定效率不是最高的。因此如果是热数据区域前1/4数据的访问，不再移动，只有后3/4的数据被访问了，才往前提。

数据什么时候刷盘淘汰

冷数据

定时io线程，刷盘冷数据区域链表尾部的几个数据，从lru链表和flush链表移除，归还到free链表中。如果free链表都没有数据了，就从冷数据末端去刷盘一个缓存页，从lur链表和flush链表移除，归还到free链表中。

热数据

热数据必然是在flush链表里面的，是脏数据，flush链表有一个后台任务，在合适的时候，去刷盘脏页数据。从lur链表和flush链表移除，归还到free链表中。

避免频繁冷数据刷盘

给Buffer Pool的内存设置大一点，即使高峰期free消耗的速率比flush、lru刷盘的数据快，但是还是有很多内存可以使用，等高峰期过了，free又慢慢被还原了。

链表总结

Buffer Pool在运行中被使用的时候，实际上会频繁的从磁盘上加载数据页到他的缓存页里去，然后free链表、flush链表、lru链表都会在使用的时候同时被使用。

磁盘数据加载到一个缓存页，free链表里会移除这个缓存页，然后lru链表的冷数据区域的头部会放入这个缓存页。
然后如果是修改了一个缓存页，那么flush链表中会记录这个脏页，lru链表中还可能会把你从冷数据区域移动到热数据区域的头部去。
如果是查询了一个缓存页，那么此时就会把这个缓存页在lru链表中移动到热数据区域去，或者在热数据区域中也有可能会移动到头部去。
系统不停的加载数据到缓存页里去，不停的查询和修改缓存数据，free链表中的缓存页不停的在减少，flush链表中的缓存页不停的在增加，lru链表中的缓存页不停的在增加和移动。
后台线程不停的在把lru链表的冷数据区域的缓存页以及flush链表的缓存页，刷入磁盘中来清空缓存页，然后flush链表和lru链表中的缓存页在减少，free链表中的缓存页在增加。

MySQL架构设计

MySQL驱动

指定配置文件

数据库连接池