mysql 组织架构

我们把数据库服务端分为三层架构，分别是连接层、SQL层、存储引擎层。

连接层

连接池：主要提供了连接管理功能

• 账号密码验证
• 连接方式（TCP/Socket）
• 通过线程与sql层交互

SQL 层

sql层的这些组件每个数据库中都有，是通用的。

1.接收连接层传过来的SQL语句
2.验证执行的SQL语法
3.验证SQL的语义（DDL,DML,DQL,DCL）
4.解析器：解析SQL语句，生成执行计划
5.优化器：将解析器传来的执行计划选择最优的一条执行
6.执行器：将最优的一条执行
6.1 与存储引擎层建立交互的线程
6.2 将要执行的sql发给存储引擎层
7.如果SQL层缓存有数据，则走缓存
8.记录日志（如binlog）

存储引擎层

作用：以innodb为例

• 建立于sql层交互的线程
• 接收sql层传来的语句
• 与磁盘交互，获取数据返给sql层

注意：innodb是应用层软件，是在跟操作系统打交道，具体是通过操作系统的文件系统提供的文件资源，来操作数据的存取。

几种存储引擎简介；

• innoDB；mysql主流，支持事务、行级锁、外键
• memory 一般不用，被redis 替代了。
• blackhole 不常用，可以用于主备复制中的分发主库。
• MySIAM：表级锁、不支持故障自动回复

锁的简介：锁会降低效率，但保证了数据安全

• 加锁越少越好
• 粒度越小越好

修改存储引擎

tips：在MySQL数据库中，frm文件是用来存储表结构信息的文件，它包含了表的元数据信息，如列名、数据类型、索引等。在MySQL 8中，一个重大的变化是frm文件被废除了，这表结构信息不再存储在frm文件中，而是存储在数据字典中。

配置文件配置

全局生效

vim /etc/my.cnf
[mysqld]
default-storage-engine=innodb
innodb_file_per_table=1

建表时设置

单表生效

CREATE TABLE egon(id INT) ENGINE=myisam;

临时修改

全局生效

#在MySQL命令行中临时设置
SET @@storage_engine=myisam

#查看
SELECT @@default_storage_engine;

替换存储引擎升级案例

背景：现有表为MyISAM引擎，计划迁移为InnoDB引擎的新版本数据库
现有问题：表级锁，效率低，不支持故障恢复，有数据丢失风险。
数据量：较小，可以考虑sql迁移，而非表数据迁移。

迁移方案：

1、从旧数据库导出存量SQL语句
2、更改所有建表语句中的MyISAM字段为InnoDB，重新指定存储引擎
3、向新数据库导入SQL

# –triggers (默认导出触发器，使用–skip-triggers屏蔽导出)
# -R:–routines,导出存储过程以及自定义函数
# 导出sql 
mysqldump -uroot -p123 -B db1 --triggers -R  > /tmp/db1.sql
# 重新指定引擎
sed -i 's#ENGINE=MYISAM#ENGINE=INNODB#gi' /tmp/db1.sql
# 导入sql
mysql -uroot -p123 < /tmp/db1.sql

该案例只是提供一种思路，并不完善，不完善有两点

• 1 生成数据库为实时增量，该种导出为某一时间点的数据，没有完善后来的增量部分数据迁移；
• 2 面对小表可以，面对运行周期长久的大数据库，无法通过mysqldump导出那么多sql语句，需要考虑采用物理备份迁移，但物理备份无法更改存储引擎，而且同一存储引擎的数据，数据库跨大版本升级，物理备份也可能会出现不兼容。

innoDB 逻辑架构

上面是mysql数据库的组织架构，下面我们深入存储引擎这一块。

存储引擎是应用层软件，需要调用操作系统的功能，最后才能将数据存入硬盘。所以有以下层级。

那么具体的存储逻辑是怎么样的呢？

应用软件用户态内存

innoDB内存架构分为4部分

• 缓冲池 buffer pull：存热点数据，降低IO提升访问性能，可以分配80%内存。
• 写缓冲 change buffer：
• 日志缓冲 log buffer：
• 自适应hash索引：优化innodb的查询（仅适用于等值匹配O(1)复杂度）

操作系统内核态缓存

储备知识：操作系统中提供两个系统调用函数fsync/write，用于控制用户内存数据的持久化。page cache 本身是操作系统为降低IO设计的架构。

• fsync：user buffer --> disk，从用户态强制IO刷盘，阻塞到完成
• write：user buffer --> page cache --> disk，数据写到页缓存就返回了，操作系统自己管理落盘。
• o_direct：数据不经过文件系统缓存直接落盘，搭配fsync使用

操作系统：write/fsync fsync的频率，o_direct

硬盘存储

InnoDB在硬盘上分为6部分（表、表空间、索引、双写缓冲、redo、undo）进行存储。

表：存放在idb文件(开启innodb_file_per_table是单表单文件。)
表空间：共享表空间文件ibdata; 独立表空间文件 idb
索引：存放在idb文件（自适应hash索引存在用户态内存，是innodb的优化手段）
双写缓冲：存在系统表空间，内存形式，落盘同时写入双写缓冲，提供冗余
redo：每次操作先写入redo，再落盘，用于保障最新数据
undo：每次执行sql前将旧数据写入undo，再生成新数据。用于保障上一版本数据。

innodb存储引擎的执行流程

三大阶段8小步

sql语句执行阶段

1.把该行数据从磁盘加载到buffer pool中，并对该行数据进行加锁
2.把旧数据写入undolog，以便修改出错情况下的回滚
3.在bufferpool中的数据更新，得到脏数据
4.把修改后的数据写入到redologbuffer当中

事务提交阶段

5.准备提交事务，redo log刷入磁盘
6.把修改的操作记录写入binlog日志
7.把binlog的文件名和位置写入commit标记，commit标记写入redolog中（redolog中存放的修改后的数据与binlog中的修改操作对应上，双管齐下），事务才算提交成功；否则不会成功

数据落盘阶段

8.IO线程把BufferPool中的脏数据刷入磁盘文件，完成最终修改

各部分作用简介

buffer pool：读缓存，写缓冲
undo log：存放历史数据用于回滚
redo log：存放改动之后的最新数据，事务完成后会释放，参数控制释放频率
binlog：当前执行的命令操作，写完之后不会释放。

redo 刷盘策略

innodb属于应用程序，redo log buffer，binlog buffer 都属于用户态内存，要存入硬盘需要经过操作系统内核态缓存（页缓存），围绕是否通过页缓存，引入刷盘策略。

事务提交的时候，redo log buffer 里的数据会马上刷到磁盘上吗？
答：依赖 innodb_flush_log_at_trx_commit

• 0：依靠innoDB定时刷入（1s）,不稳定，会丢失。
• 1：默认值，建议，提交事务，就把redolog buffer刷入磁盘，只要提交成功，就必然存入磁盘。
• 2：提交事务，redo日志写入操作系统缓存，由操作系统管理，此时mysql宕机数据还在，但服务器宕机数据就没有了。

binlog 刷盘策略

事务提交的时候，binlog 也会刷盘，binlog属于用户态的日志，不属于存储引擎。
依赖 sync_binlog 参数

• 0：默认值，事务提交后，binlog写入操作系统缓冲。操作系统宕机会丢
• 1：推荐值，事务提交后，binlog写入硬盘，不经过操作系统缓存。

commit 标记

redo log 刷盘 --> binlog 刷盘 --> binlog commit 标记写如redo log文件 ，此时提交成功，表数据才会落盘。

• 如果redo log 刷盘，binlog 刷盘失败，则事务提交失败；
• 如果redo log 刷盘，binlog 刷盘，但binlog commit 写入失败，事务也提交失败。

buffer pool 中脏数据刷盘

前提添加：commit 标记写如redo log后，事务提交成功，脏数据可以刷盘但是

• redo log 文件满了，脏数据刷盘
• 系统内存不足开始淘汰脏页，脏数据刷盘
• mysql 空闲时间，脏数据刷盘
• mysql 正常关闭之前，脏数据刷盘