Mysql InnoDB原理

Mysql InnoDB原理

Mysql 内部组件

在了解InnoDB 原理之前,需要先了解Mysql 内部组件结构

Mysql总体 可以分为 Server层 和 引擎层 两部分.

Server层

包含连接器,缓存,分析器,优化器,执行器等. 涵盖Mysql的大多数核心服务功能,以及所有的内置函数(如,日期,计算,加密等函数). 所有跨存储引擎的功能都在这一层实现. 例如,存储过程,触发器,视图等.

引擎层

引擎层负责数据的存储和读取.其架构模式为插件式(类似接口,可插拔), 支持InnoDB,MyISAM,Memory 等多种存储引擎. 目前,最常用的存储引擎有InnoDB, 在5.5.5版本之后 Mysql将InnoDB设置成为默认引擎.在创建表时,如果不指定表的存储引擎,那么Mysql会设置表的存储引擎为InnoDB.

Server层的 连接器 ,缓存, 分析器, 优化器, 执行器 的作用

连接器

客户端和Mysql进行通信连接需要通过Server端建立,而建立连接的工作就由连接器负责;
客户端建立连接的顺序
1.连接器和客户端通过TCP 协议 建立连接,获取权限,维持 以及 管理连接;
2.如果账号密码不正确,那么 Mysql会返回 Access denied for user 的错误信息,然后结束客户端;
3.如果账号密码正确,那么连接器会在权限表里面查询账号的权限. 如果当前账号的权限发生变更,那么需要重新创建连接才能刷新账号权限;

缓存

在完成连接创建之后. 如果执行select 查询语句.那么就会进行缓存查询操作.
Mysql在进行查询时,会先到缓存中查询,判断是否执行过相同语句. 如果已经执行过,那么对应的查询语句以及查询结果会以K-V结构 缓存在内存中. key 为查询语句,value为查询结果.如果查询能够直接在缓存中查找到 对应的key ,那么就直接将对应的value返回.

但是, 因为 key 为 查询语句 ,使得缓存使用效率较低.另外,如果对一个表的更新,那么表上所有的缓存都会被清空,这也使得缓存的使用效率低.

通常使用缓存的表 为更新较少的表.例如,配置表,字典表.
Mysql 中通过修改my.cnf 文件来修改 缓存 开关:

my.cnf
#query_cache_type有3个值 0代表关闭查询缓存OFF，1代表开启ON，2（DEMAND）
代表当sql语句中有SQL_CACHE关键词时才缓存
query_cache_type=2

因为使用场景以及使用效率较低,Mysql在8.0版本之后移除了缓存功能.

分析器

当查询没有在缓存中查询到数据,那么就会进行真正的语句查询.

1. Mysql会对sql语句进行词法分析 . 将关键在识别出来,例如 识别 select ,判断该sql语句为 查询语句;
2. 之后会进行 语法分析 根据 词法分析 的结果. 语法分析器 会根据语法规则,判断sql是否符合 Mysql 的语法.
3. 在完成词法分析之后 ,会生成语法树;
   

--语法树分析
select name,age from user where age >20 and level >5 and name='lilei';

优化器

在分析器对sql进行分析之后,优化器会对sql进行优化处理.决定使用哪个sql,有多表关联时,决定每个表之间的连接顺序顺序, 还有Mysql的优化机制.

执行器

在执行sql前,判断账号是否有查询对应表的权限,如果没有就会返回权限校验错误的信息; 如果有权限,那么执行器就会根据标的引擎定义,使用引擎提供的接口.

Innodb引擎的过程图

Innodb 中的 redo log

redo log : 如果事务提交成功,但是 buffer pool(缓存) 里面的数据还没写入磁盘, 但是系统 宕机, 此时就可以用redo log 中的数据恢复 buffer pool 中的数据, 并重新写入到磁盘中.

Innodb中的参数

innodb_log_buffer_size: 设置redo_log_buffer大小参数，默认16M ，最大值是4096M，最小值为1M.

show variables like '%innodb_log_buffer_size%';

innodb_log_group_home_dir: 设置redo log文件存储位置参数，默认值为"./"，即innodb数据文件存储位置.

show variables like '%innodb_log_group_home_dir%';

innodb_log_files_in_group: 设置redo log文件的个数,命名方式如: ib_logfile0, ib_logfile1… ib_logfileN.默认2个,最大100个.

show variables like '%innodb_log_files_in_group%';

innodb_log_file_size: 设置单个redo log文件大小，默认值为48M。最大值为512G，注意最大值指的是整个 redo
log系列文件之和，即(innodb_log_files_in_group * innodb_log_file_size)不能大于最大值512G。

show variables like '%innodb_log_file_size%';

innodb_flush_log_at_trx_commit: 这个参数控制 redo log 的写入策略，它有三种可能取值:

设置为0：表示每次事务提交时都只是把 redo log 留在 redo log buffer 中，数据库宕机可能会丢失数据;
设置为1(默认值)：表示每次事务提交时都将 redo log 直接持久化到磁盘，数据最安全，不会因为数据库宕机丢失数据，
     但是效率稍微差一点，线上系统推荐这个设置;
设置为2：表示每次事务提交时都只是把 redo log 写到操作系统的缓存page cache里，这种情况如果数据库宕机是不会丢失数据的，
     但是操作系统如果宕机了，page cache里的数据还没来得及写入磁盘文件的话就会丢失数据

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 操作系统函数 write 写到文件系统的page cache，然后调用操作系统函数 fsync 持久化到磁盘文件.

redo log 写入策略流程图:

redo log 将数据从缓存中写入磁盘的过程

write pos : 当前记录的位置,一边写一边后移,写到第 5 号文件末尾后就回到 0 号文件开头.
check point : 要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件里.
write pos 和 checkpoint 之间的部分就是空着的可写部分,可以用来记录新的操作. 如果 write pos 追上checkpoint,表示redo log写满了,这时候不能再执行新的更新,得停下来先擦掉一些记录,将 checkpoint 推进.

WAL机制:
WAL机制: Write-Ahead-Logging , 预写日志系统. 是Mysql 的写操作不是立马将数据更新到磁盘中, 而是先记录在日志中,然后在合适的时间再更新到磁盘中. 这样主要是错开高峰期.
Mysql的日志 分为 undo log ,redo log , bin log .
undo log : 完成MVCC 从而实现Mysql的隔离级别;
redo log : 降低随机写的性能损耗, 并防止宕机导致写操作失败;
bin log : 写操作的备份, 保证主从一致;

bin log 二进制归档日志

bin log 二进制日志保存了所有执行过的修改操作的雨具, 不保存查询操作. 如果Mysql 宕机,可以通过二进制日志文件排查, 用户操作 或者 表结构操作, 用于恢复数据库数据.
bin log 日志记录参数:

#查看bin log 参数
show variable like '%log_bin%';

log_bin：binlog日志是否打开状态
log_bin_basename：是binlog日志的基本文件名，后面会追加标识来表示每一个文件，binlog日志文件会滚动增加
log_bin_index：指定的是binlog文件的索引文件，这个文件管理了所有的binlog文件的目录。
sql_log_bin：sql语句是否写入binlog文件，ON代表需要写入，OFF代表不需要写入。如果想在主库上执行一些操作，
但不复制到slave库上，可以通过修改参数sql_log_bin来实现。比如说，模拟主从同步复制异常。

在Mysql5.7版本中, bin log 是默认关闭的.在Mysql 8.0 版本是默认打开的. 如果需要修改bin log 的功能需要修改 配置文件my.ini(windows) / my.cnf(linux) ,并重启数据库.

 # log‐bin设置binlog的存放位置，可以是绝对路径，也可以是相对路径，这里写的相对路径，
    则binlog文件默认会放在 data数据目录下
 log‐bin=mysql‐binlog
 # Server Id是数据库服务器id，随便写一个数都可以，这个id用来在mysql集群环境中标记唯一mysql服务器，集群环
    境中每台mysql服务器的id不能一样，不加启动会报错
 server‐id=1
 # 其他配置
 binlog_format = row # 日志文件格式
 expire_logs_days = 15 # 执行自动删除binlog日志文件的天数， 默认为0， 表示不自动删除
 max_binlog_size = 200M # 单个binlog日志文件的大小限制，默认为 1GB

查看bin log 文件的数量

show binary logs;

bin log 日志格式:

1.STATEMENT：基于SQL语句的复制，每一条会修改数据的sql都会记录到master机器的bin-log中，这种方式日志量小，
            节约IO开销，提高性能，但是对于一些执行过程中才能确定结果的函数，比如UUID()、
            SYSDATE()等函数如果随sql同步到slave机器去执行，则结果跟master机器执行的不一样.
2.ROW：基于行的复制，日志中会记录成每一行数据被修改的形式，然后在slave端再对相同的数据进行修
       改记录下每一行数据修改的细节，可以解决函数、存储过程等在slave机器的复制问题，但这种方式日志量较大，
       性能不如Statement。举个例子，假设update语句更新10行数据，Statement方式就记录这条update语句，
       Row方式会记录被修改的10行数据.
3.MIXED：混合模式复制，实际就是前两种模式的结合，在Mixed模式下，MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志
         形式，也就是在Statement和Row之间选择一种，如果sql里有函数或一些在执行时才知道结果的情况，会选择Row，
         其它情况选择Statement，推荐使用这一种.

bin log 写入磁盘机制

bin log 写入磁盘机制通过 sync_binlog 参数控制, 默认值是 0.

1.为0的时候，表示每次提交事务都只 write 到page cache，由系统自行判断什么时候执行 fsync 写入磁盘.
  虽然性能得到提升，但是机器宕机，page cache里面的 binlog 会丢失.
2.设置为1，表示每次提交事务都会执行 fsync 写入磁盘，这种方式最安全;
3.还有一种折中方式，可以设置为N(N>1)，表示每次提交事务都write 到page cache，但累积N个事务后才 fsync 写入磁盘，
这种如果机器宕机会丢失N个事务的binlog;

bin log 重新生成情况:

1.服务器启动或重新启动;
2.服务器刷新日志，执行命令flush logs;
3.日志文件大小达到 max_binlog_size 值，默认值为 1GB;

删除bin log 日志文件方式:

1.删除当前bin log文件
reset master;
2.删除指定日志文件之前的所有日志文件，下面这个是删除6之前的所有日志文件，当前这个文件不删除
purge master logs to 'mysql-binlog.000006';
3.删除指定日期前的日志索引中的bin log日志文件
purge master logs before '2024-01-01 00:00:01';

查看bin log日志文件
通过 mysql 的mysqlbinlog 工具 查看binlog日志:

# 查看bin‐log二进制文件
mysqlbinlog ‐‐no‐defaults ‐v ‐‐base64‐output=decode‐rows D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000006
# 查看bin‐log二进制文件(时间范围)
mysqlbinlog ‐‐no‐defaults ‐v ‐‐base64‐output=decode‐rows D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000006 start‐datetime="2024‐01‐01 00:00:00" stop‐datetime="2023‐01‐01 00:10:00" start‐position="5000" stop‐position="20000"

bin log日志文件恢复数据方式
首先需要mysql 中开启了 bin log日志.
然后找到具体的bin log日志文件.根据 事务 开启和提交时的标记位.
如 事务开启时的标记位 219 ,事务提交时的标记位701.

# 根据事务开启 提交的标记位恢复数据
mysqlbinlog ‐‐no‐defaults ‐‐start‐position=219 ‐‐stop‐position=701 ‐‐database=test D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000009 | mysql ‐uroot ‐p123456 ‐v test

#根据时间 恢复数据
mysqlbinlog ‐‐no‐defaults ‐‐start‐datetime="2024‐01‐ 00:00:00" ‐‐stop‐datetime="2024‐01‐01 00:30:00" ‐‐database=test D:/dev/mysql‐5.7.25‐winx64/data/mysql‐binlog.000009 | mysql ‐uroot ‐p123456 ‐v test

每日数据备份

如果需要恢复大量数据, 例如数据库被删除,需要队整个数据库进行回复. 如果 所有的bin log 日志都在,那么就需要从第一个 bin log开始进行数据回复. 但是早期的数据 无法回复,因为bin log 会定期删除.
那么就需要对bin log 进行备份,可以参考的方式 为 每天在凌晨时进行全量数据库备份. 在回复数据库时, 可以用全量备份加上备份时间点之后的 bin log 恢复数据.
备份数据库 可以使用mysql 的 mysqldump 工具

mysqldump ‐u root 数据库名>备份文件名; #备份整个数据库
mysqldump ‐u root 数据库名 表名字>备份文件名; #备份整个表
mysql ‐u root test < 备份文件名 #恢复整个数据库，test为数据库名称，需要自己先建一个数据库test

为什么Mysql 设计了 redo log 和 bin log 两个日志?

Mysql 开始是使用 MyISAM引擎, 而MyISAM 引擎没有 crash-safe 的功能, 因为 bin log 只用于归档. 之后推出的Innodb引擎 为了保证有crash-safe 的功能, 就开发了 redo log的日志系统 ,让 redo log 来实现 crash-safe 的功能. 这样就可以保证Mysql 系统宕机 重启时,提交的数据不会丢失(也就是 crash-safe).

undo log 回滚日志

InnoDB 对 undo log 文件的管理 采用段的方式, 也就是回滚段(rollback segment) .每个混滚段记录 1024个 undo log segment ,每个事务只会使用一个 undo log segment.
在Mysql 5.5 版本,只有一个回滚段. 只支持 1024个事务数量.
在Mysql5.6版本及之后的版本,InnoDB 支持最大 128个回滚段,能够支持 128* 1024 哥在线事务.

undo log 部分参数

1. innodb_undo_directory：设置undo log文件所在的路径。该参数的默认值为"./"，
   即innodb数据文件存储位置，目录下ibdata1文件就是undo log存储的位置;
2. innodb_undo_logs: 设置undo log文件内部回滚段的个数，默认值为128;
3. innodb_undo_tablespaces: 设置undo log文件的数量，这样回滚段可以较为平均地分布在多个文件中. 设置该参数后，
   会在路径innodb_undo_directory看到undo为前缀的文件.

undo log 删除时间点

新增数据: 在事务提交之后清除;
修改数据: 在事务提交之后,不会马上清除,这些数据会用于 mvcc,当没有事务使用到该版本数据时,才会进行清除;

Mysql 为什么设计这些日志 而不是 直接将数据存入磁盘?

   因为来一个请求就直接对磁盘文件进行随机读写, 然后更新磁盘文件里的数据性能可能相当差.
磁盘随机读写的性能是非常差的, 所以直接更新磁盘文件是不能让数据库抗住很高并发的.
Mysql这套机制看起来复杂, 但它可以保证每个更新请求都是更新内存BufferPool, 然后顺序写日志文件, 
同时还能保证各种异常情况下的数据一致性.
更新内存的性能是极高的, 然后顺序写磁盘上的日志文件的性能也是非常高的, 要远高于随机读写磁盘文件.
正是通过这套机制, 才能让我们的MySQL数据库在较高配置的机器上每秒可以抗下几干甚至上万的读写请求.

错误日志

Mysql还有一个比较重要的日志是错误日志，它记录了数据库启动和停止，以及运行过程中发生任何严重错误时的相
关信息。当数据库出现任何故障导致无法正常使用时，建议首先查看此日志.
在MySQL数据库中，错误日志功能是默认开启的，而且无法被关闭.

# 查看错误日志存放位置
show variables like '%log_error%';

通用日志

通用查询日志记录用户的所有操作, 包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给
MySQL 数据库服务器的所有 SQL 指令等, 如select、show等，无论SQL的语法正确还是错误、也无论SQL执行成功
还是失败, MySQL都会将其记录下来.

通用查询日志用来还原操作时的具体场景, 可以帮助我们准确定位一些疑难问题, 比如重复支付等问题.
general_log: 是否开启日志参数, 默认为OFF, 处于关闭状态, 因为开启会消耗系统资源并且占用磁盘空间. 一般
不建议开启, 只在需要调试查询问题时开启.

show variables like '%general_log%';
# 打开通用查询日志
SET GLOBAL general_log=on;