mysql三层架构

一.mysql架构

1.Server层

1.1连接池

连接器主要负责客户端连接的权限校验、建立连接并且维持连接。我们最常用的TCP/IP形式的连接。

mysql -h ip -P port -u user -p		#数据库连接

show processlist									#查看数据库当前所有的连接

当客户端发起数据库连接时，连接层会查mysql.user表，对连接进行用户名和密码认证。如果用户名密码认证通过，连接器会到权限（mysql.user）里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限。这就意味着建立连接后，即使管理员修改了用户的权限，也不对已建立连接的用户造成影响。

如果一个连接没有执行操作，就会处于空闲状态，如果太长时间没有操作，就会断开连接，这个时间由wait_timeout控制，默认8h。

mysql有长连接和短连接之分：

• 短连接：短连接简单来说就是每一次操作数据库，都要打开和关闭数据库连接。数据库建立连接和关闭连接需要消耗很多资源，需要尽量避免。

• 长连接：长连接是指程序之间的连接在建立之后，就一直打开，被后续程序重用。长连接可以减少连接创建消耗，但是，MySQL 在执行过程中临时使用的内存是管理在连接对象里面的，这些资源在连接断开时才释放，所以内存可能会长的很快。可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

1.2管理服务和工具

1.3SQL接口

接受用户的SQL命令，并且返回用户需要查询的结果，存储中间过程、视图、触发器等。

1.4分析器

对输入的sql语句进行词法分析，如果语法有错误，则返回“You have an error in your SQL syntax”错误提示。

1.5优化器

经过了分析器，MySQL 就知道你要做什么了，但是sql不会立即执行，它会对sql进行优化，比如决定使用哪个索引，决定表的连接顺序等。

1.6缓冲区

存储之间sql查询的结果等

2.存储引擎层

mysql拥有丰富的存储引擎，这些引擎是基于表而不是数据库，用户可以根据需求灵活选用。

2.1常见存储引擎

1.lnnoDB 存储引擎：

设计理念：面向在线事务处理(OLTP)

存储位置：表空间

特性：

1. 支持事务
2. 使用聚簇索引
3. 支持行锁
4. 支持日志恢复
5. 支持外键
6. 数据存储idb文件，表定义存储在frm文件中

2.MyISAM

设计理念：面向一些（在线事务分析）OLAP

存储位置：索引文件和数据文件分开存储

特点：

1. 不支持事务
2. 不支持聚簇索引
3. 不支持行锁
4. 不支持日志恢复
5. 不支持外键
6. 表定义在frm文件，数据文件存储在MYD，索引文件存储在MYI
7. 表中存储了该表的数据行大小

3.Memory

用hash索引的方式将数据存储在内存中，mysql中的临时表就用Memory实现，支持表锁，并发性能较差，不支持TEXT和BLOB格式，不支持变长数据格式，如果在使用临时表过程中有TEXT或BLOB字段，会将其转存到MyISAM中进行存放。

4.Archive

只支持insert和select，支持1:10的数据压缩，适用于日志存储

mysql支持丰富的存储引擎，但是平时真正用到的比较少，主流的还是InnoDB存储引擎。虽然一个数据库中可以使用不同的存储引擎，但是存储引擎一般不会公用，因为在业务当中，很可能需要对一些表进行联表等操作，除此之外，InnoDB支持事务，还有各种恢复日志，所以，正常情况下，都会牺牲一些性能来保证数据的一致性和持续性。

2.2Innodb体系结构

InnoDB体系结构分为后台线程、内存池两部门组成。

1.后台线程

后台线程主要作用是负责刷新内存池中的数据，保证缓冲池中的内存缓存的是最近的数据。此外将已修改的数据文件刷新到磁盘文件，同时保证在数据库发生异常的情况下InnoDB 能恢复到正常运行状态。

**Master线程:**主要负责将缓冲池中的数据异步刷新到磁盘，保证数据的一致性，包括脏页的刷新、合并插入缓冲(INSERT BUFFER) 、UNDO 页的回收等。

**IO线程:**负责IO请求的回调处理

**Purge线程:**事务被提交后，其所使用的undolog 可能不再需要，因此需要PurgeThread 来回收已经使用并分配的undo 页。

**Page Cleaner线程:**完成脏页刷新的功能

2.内存

innodb中的内存分配包括三部分：缓冲池、 重做日志缓冲、额外内存池

• 缓冲池中通常包含数据页、索引页、插入缓冲、自适应哈希索引、锁信息、数据字典等缓冲，他们是通过LRU（Latest Recent Used，最近最少使用）算法进行管理的，即将最近访问的页放入到链表头，发生冲突时，淘汰掉最链表尾的页（最久未使用）。在实际情况中，可能某些查询会将缓冲池中的所有页（热点数据）刷掉，而本查询中很多页往往并不是热点数据，这将很大程度得影响数据库的性能，为此，InnoDB采用midpoint+innodb_old_blocks_time的实现方式对页面淘汰算法进行了改进。其设计思想大致如下：

1.将LRU列表分成两段，靠近头部的5/8是热点数据（new列表），剩余的是新加入缓存的数据（old列表）。

2.当某个页刚加入LRU列表时，将页置换到old列表头部

3.当这个页在old列表中存活了innodb_old_blocks_time，将其置换到new列表头部

• 重做日志缓冲

重做日志不是每执行一条语句就将日志写到磁盘中，而是先将其放入到重做日志缓冲中，然后再以一定的频率写到磁盘。这个频率一般不会超过一秒，所以重做日志缓冲占用的空间并不会很大。

缓冲写入磁盘时机：

1.Master Thread 每一秒将重做日志缓冲刷新到重做日志文件；

2.每个事务提交时会将重做日志缓冲刷新到重做日志文件；

3.当重做日志缓冲池剩余空间小于1/2 时，重做日志缓冲刷新到重做日志文件。

• 额外的内存池

额外的内存池主要用于存储缓冲控制对象，这些对象记录了LRU、锁等信息。

2.3innoDB的一些特性

**1.Checkpoint技术：**为了保证数据的可恢复性，当事务提交时，都是先写重做日志，再修改页。当由于发生宕机导致数据丢失时，通过重做日志来完善。随着mysql的运行和系统用户的增加，重做日志文件可能会变得越来越大，当进行数据恢复时，将要花费大量的时间，而checkpoint技术就是为了解决该问题。当数据库宕机时，数据库不需要重做所有的日志，因为checkpoint之前的页都已经刷新回磁盘了，数据库只需要对之后的数据进行恢复即可。

2.插入缓冲（后面升级为change buffer）

对于非聚集索引的插入或更新操作，不是每一次直接插入到索引页中，而是先判断插入的非聚集索引页是否在缓冲池中，若在，则直接插入；若不在，则先放入到一个Insert Buffer 对象中，然后再以一定的频率和情况况进行Insert Buffer 和辅助索引页子节点的merge （合并）操作，这时通常能将多个插入合并到一个操作中，这就大大提高了对于非聚集索引插入的性能。

插入缓冲能够很大程度得提升mysql的性能，但是使用它需要满足两个条件：

• 辅助索引
• 索引不是唯一的（如果是唯一的话，每次需要确保数据库中是否已经存在该索引）

3.两次写

如果说Insert Buffer 带给lnnoDB 存储引擎的是性能上的提升，那么doublewrite （两次写）带给InnoDB 存储引擎的是数据页的可靠性。zz

doublewrite实际上是为了保证数据页的完整性。我们知道redolog中记录了数据的物理结构，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，当数据库宕机时，可以用redolog来进行数据恢复。但是，mysql存储的最小单位是16k的页，而磁盘的最小存储单位是一个512字节的扇区，文件存储的基本单位簇（可能是2/4/8/16/32个扇区，一般是8个扇区），那么，在对页进行写磁盘时，很可能写到一半，mysql断电宕机了，那么这个数据页的完整性便被破坏了，这个时候再应用redolog，没有任何意义，所以这个时候，就要用到doublewrite技术。

doublewrite流程如上，在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是先将其复制到doublewrite buffer中，之后将其顺序写入共享表空间，然后再将其写入到数据文件中（离散写入）。当mysql崩溃需要进行数据恢复时，先从共享表空间中找到副本进行页恢复，然后再应用重做日志，这样便能保证数据的正确性。

4.自适应哈希

InnoDB 存储引擎会监控对表上各索引页的查询（同一个索引的同一种查询方式）。如果观察到建立哈希索引可以带来速度提升，则建立哈希索引，称之为自适应哈希索引(Adaptive Hash Index, AHi) 。他是通过缓冲池建立的哈希索引，速度很快，而且不需要对整张表建立哈希索引。

5.异步IO

为了提高磁盘操作性能，mysql支持异步IO，即当一次查询需要检索多个索引页时，需要多次io，用户可以发出一个io请求后，立刻发出另一个io请求，最后等待所有io操作的完成。

6.刷新临近页

当刷新一个脏页时， InnoDB 存储引擎会检测该页所在区(extent) 的所有页，如果是脏页，那么一起进行刷新。这样做的好处显而易见，通过AIO 可以将多个IO 写人操作合并为一个IO 操作

3.物理文件层

物理文件层和存储引擎有关，不同引擎的文件结构是不同的，比如InnoDB的表结构是定义在frm文件中，数据文件和索引文件都是存储在ibd文件当中，而MyISAM中的表结构是定义在frm文件中，数据文件定义在MYD文件中，索引文件定义在MYI文件里

二进制日志(binary log)记录了对MySQL 数据库执行更改的所有操作，但是不包括SELECT 和SHOW 这类操作

1.恢复(recovery): 某些数据的恢复需要二进制日志，例如，在一个数据库全备文件恢复后，用户可以通过二进制日志进行point-in-time 的恢复。

2.复制(replication) ：其原理与恢复类似，通过复制和执行二进制日志使一台远程的MySQL 数据库（一般称为slave 或standby) 与一台MySQL 数据库（一般称为master 或primary) 进行实时同步。

3.审计(audit) ：用户可以通过二进制日志中的信息来进行审计，判断是否有对数

据库进行注入的攻击。

表空间–>段（数据段、索引段、回滚段）–>区（1M）–>页（16K，Innodb磁盘管理的最小单位）–>行

默认所有数据都存储在ibdatal中，但是开启innodb_ file_per_tabl，开启后，每张表的数据段、索引段、插入缓冲Bitmap页都存储在各自的文件中，而回滚信息、二次缓冲等信息还是存储在共享表空间中。

行记录格式

Compact（紧凑的） 行记录格式，为了尽可能在一个页中存储更多行数据，所有为NULL的字段不占用存储空间，只用空值标志位表示

Redundant 行记录格式，VarChar类型的NULL不占用存储空间，但是Char类型的字段占用存储空间

Compressed 和Dynamic 行记录格式，对千存放在BLOB 中的数据采用了完全的**行溢出（将一条记录中的某些数据存储在真正的数据页面之外）**的方式，在数据页中只存放20 个字节的指针，实际的数据都存放在Off Page 中，而之前的Compact 和Redundant 两种格式会存放768 个前缀字节。