MySQL高阶之架构篇以及redolog落盘机制

前言:Mysql成为java程序员每天都要交互的东西 但是大多数人只停留在会使用mysql的层面 比如写写sql查询满足业务需求 但是mysql真的那么容易吗?他的内部有哪些机制支撑着mysql 探索一下他的内部架构

MySQL逻辑架构图

Mysql Server: 整台车子除了引擎部分的模块,也是非常重要的,如果一台车子没了车架或方向盘也是不能启动的.Server层中有许多的组件 比如

1. .连接器 维护和客户端的连接的连接池
2. 分析器:SQL语句。对SQL语句进行词法分析语法分析。
3. 优化器 对分析结果进行优化。负责解析成字段、最左前缀原则检查、索引选择
4. 执行器 把优化结果交给数据库引擎执行。
5. 查询缓存-看情况开启

查询缓存:
key-value系统 k:sql v：对应结果集 
默认关闭 mysql8已经被干掉
优点：提高查询性能
缺点：1.建立缓存有时间开销 2.内存中存放结果集-空间开销 3.缓存的命中率不高 where条件经常改变
参数：query_cache_type on开启
开启：my.cnf配置
 0：关闭 
1：开启(默认缓存所有的select语句)
2：针对性对sql语句缓存 只缓存“select sql_cache xxx”的语句
查看缓存的命中率：
show status like 'qcache_hits';

Mysql engines: Mysql 引擎层.一句话理解.整台车子的引擎部分.是这辆车最核心的组件.当然引擎需要配合其他部件车子才能正常发动.其中包含了很多引擎.比较主流的比如

1. MyISAM
2. InnoDB

其中InnoDB是目前使用最多的引擎 后续的讲解都基于Innodb引擎来进行剖析

查看mysql支持的引擎:

mysql> show engines \G;
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)

除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该选择InnoDB引擎。

Mysql查看系统参数

1、查看系统参数
show variables like ‘%xxxx%’;

2、查看系统状态
show status like ‘%xxxx%’;

文件分类

以下以centos操作系统为例

配置文件:/etc/my.cnf 该文件配置了mysql 的全局配置 修改后需要重启mysql才会生效

数据库文件目录：/var/lib/mysql

1. 日志文件
   1).通用日志（一般情况下关闭 记录操作mysql的所有日志) 文件名:{hostname}.log
   2).慢查询日志(生产环境建议开启 记录查询过慢的sql)文件名:{hostname}-slow.log
   3).错误日志(系统错误日志-可以开启)
   4).二进制日志(bin-log 实现主从复制)
2. 数据文件
   1)表的结构定义文件(.frm)
   2)表的数据存储文件：
   .ibd文件(InnoDB引擎存储数据文件包括数据和索引信息)-非常重要
   .MYD:MyIsam引擎数据存储文件
   .MYI:MyIsam引擎索引存储文件
   3)系统表空间文件(.ibdata1) 记录系统的详细元数据信息
   4)重做日志文件-非常重要 是用来保障数据不丢失的重要文件 redo_log-顾名思义 重做日志 一般由这两个文件组成
   ib_logfile0
   ib_logfile1

Sql语句执行流程

Innodb架构图

磁盘结构(右边)

• System Tablespace：
  系统表空间-ibdata1文件 双写缓冲区、修改缓冲区、回滚日志

• File-Pre-Tablespaces：
  用户表空间 ibd文件 存储数据和索引信息
  一个表对应一个表空间 默认innodb_per_table=on开启
  off关闭 所有表都放到表系统空间-不建议这么玩

• General Tablespaces：
  通用表空间 需要通过create tablespace语句创建的 不需要使用

• Temporay TableSpace：
  临时表空间 ibtmp1 排序和分组

• Undo TableSpaces
  回滚表空间 默认存放在系统表空间

• Redo log：
  重做日志 ib_logfile0 ib_logfile1 采用循环写的模式
  使用这一组文件

| innodb_log_file_size        | 50331648 |
 | innodb_log_files_in_group   | 2 |

用户表空间的存储格式

ibd文件->段->区->页(基础单位)->行(实际数据行)

Mysql中数据的读写是以"页"为单位，每个数据页默认是16kb。
数据页中包含了行。行的大小取决于表结构的定义。

innodb：16KB(可配置)
操作系统：4KB
机械硬盘：512B
SSD：4KB

相当于读取一次数据会返回16KB的数据，因此读ssd比读机械硬盘要快。
设定一条数据5KB 最多读取3条记录

1B=8bit
1KB=1024B
1MB=1024KB
1GB=1024MB

内存结构

(非常重要!)

1.Buffer pool：
缓冲池 其中包含数据也和索引页。读写都是以页为单位
InnoDB存储引擎是基于磁盘存储的，并将其中的记录按照页的方式进行管理。但是由于CPU速度和磁盘速度之间的鸿沟，基于磁盘的数据库系统通常使用缓冲池记录来提高数据库的的整体性能

2.Channge buffer：
修改缓冲区 其中保存的是辅助索引(非主键索引)的缓冲区。当修改数据时，辅助索引也需对应进行修改。需要时间，所以先放在缓冲区，然后慢慢处理。优化插入不使用主键索引的情况

3.自适应hash索引：
由Innodb自己来维护的一个hash索引，用户无法干预

4.log buffer：
redo log的缓冲区 重写日志也会先写入缓冲区 然后慢慢处理
当innodb在缓冲池变更数据时,首先先把相关的变更写入重做日志缓冲中redo log buffer中.然后再按时或者提交事务时写入磁盘,完成真正的磁盘修改.

Innodb数据更新流程

1. 查看要更新的数据也是否存在buffer pool中
2. 如果不存在就从磁盘读取放到buffer pool
3. 如果存在就直接修改对应的数据页(内存)
4. 在修改数据页之前，应先生成redo log，把redo log写入缓冲区log buffer
5. 当用户commit时，需要把log buffer中的redo log写入到redo log文件中
6. 如果redo log写入成功代表commit成功
7. 如果redo log写入失败否则rollback回滚

redo log 文件顺序写，效率比随机写效率高。

如果系统崩溃，可以根据redo log中的日志信息，从而恢复数据。redo log中记录的是数据页中的什么位置修改了什么内容。

redo log是保证数据完整性的重要一个环节。

内存数据落盘分析

修改buffer pool中的数据页后，造成了内存中的数据页和磁盘中的数据页内容不一致，这就形成了脏页。脏页指的是内存中的数据页。buffer pool 采用LRU算法进行数据页的调度。

回来看这个图.如果要将内存数据page完成持久化到磁盘.是通过以下两个流程来完成的.
1. 脏页落盘
2. 预写redo log

脏页落盘

读取操作:将从磁盘中读到的页放在缓冲池中，下次再读相同的页时，首先判断该页是否在缓冲池中。若在缓冲池中，称该页在缓冲池中被命中，直接读取该页。否则，读取磁盘上的页。

修改操作:则首先修改在缓冲池中的页，然后再以一定的频率刷新到磁盘上。页
从缓冲池刷新回磁盘的操作并不是在每次页发生更新时触发，而是通过一种称为CheckPoint的机制刷新回磁盘。

CheckPoint

思考一下这个场景：如果重做日志可以无限地增大，同时缓冲池也足够大，那么是不需要将缓冲池中页的新版本刷新回磁盘。因为当发生宕机时，完全可以通过重做日志来恢复整个数据库系统中的数据到宕机发生的时刻。

因此Checkpoint（检查点）技术就诞生了，目的是解决以下几个问题：1、缩短数据库的恢复时间；2、缓冲池不够用时，将脏页刷新到磁盘；3、重做日志不可用时，刷新脏页。

脏页落盘的时机 采用CheckPoint检查点机制 以下机制都可通过参数控制

sharp checkpoint：强制落盘。把内存中所有的脏页都执行落盘操作。只有当关闭数据库之前才会执行。

fuzzy checkpoint：没落落盘。把一部分脏页执行落盘操作
	1.Master Thrad Checkpoint 主线程定时将脏页写入磁盘 每秒或每10s执行一次脏页。
	2.FLUSH_LRU_LIST buffer pool有脏页换出，执行落盘
	3.Async/Sync Flush checkpoint 当redo log快写满的时候执行落盘
		a.当redo log超过75%小于90%会执行异步落盘
		b.当redo log超过90%，会执行同步落盘操作。会阻塞写操作。
	4.Dirty Page too much checkpoint 如果buffer pool中脏页太多，脏页率超过75%执行落盘

总结：
由于磁盘是一种相对较慢的存储设备，内存与磁盘的交互是一个相对较慢的过程
由于innodb_log_file_size定义的是一个相对较大的值，正常情况下，由前面两种checkpoint刷新脏页到磁盘，在前面两种checkpoint刷新脏页到磁盘之后，脏页对应的redo log空间随即释放，一般不会发生Async/Sync Flush checkpoint。同时也要意识到，为了避免频繁低发生Async/SyncFlush checkpoint，也应该将innodb_log_file_size配置的相对较大一些。

重做日志落盘

Log Buffer写入磁盘的时机，由参数 innodb_flush_log_at_trx_commit 控制，默认是 1，表示事务提交后立即落盘。

redo log落盘控制参数

innodb_flush_log_at_trx_commit=1
	当属性值为0时，事务提交时，不会对重做日志进行写入操作，而是等待主线程按时写入每秒写入一次；
	当属性值为1时，事务提交时，会将重做日志写入文件系统缓存，并且调用文件系统的fsync，将文件系统缓冲中的数据真正写入磁盘存储，确保不会出现数据丢失；
	当属性值为2时，事务提交时，也会将日志文件写入文件系统缓存，但是不会调用fsync，而是让文件系统自己去判断何时将缓存写入磁盘。

双写落盘

Double Write双写
把要写入的数据页放到内存的双写缓冲区(2M)
吧内存中的双写缓冲区的数据写入系统表空间的双写缓冲区
吧内存中的双写缓冲区的数据写入用户表空间

数据崩溃：
如果系统表空间数据写坏，可以根据用户表空间的原始数据页+redo log恢复数据
如果用户表空间数据写坏，可以根据系统表空间中最新的数据页恢复数据

流程图