Mysql-InnoDB存储引擎、InnoDB事务

mysql

 2024/07/18 

MySQL是一个关系型数据库管理系统，由MySQL AB公司开发

MySQL发行版

Percona Server（性能）
是一个独立的数据库，可以完全与MySQL兼容，使用XtraDB，说它自己性能比InnoDB好
提供PXC高解决方案，提供precona-toolkit等管理工具
MariaDB（尝新）
为了防止MySQL被oracle闭源，由MySQL创始人开发，完全兼容MySQL，包括API和命令行
提供MySQL标准的存储引擎MyISAM和InnoDB，10.0.9版本后使用XtraDB代替InnoDB
MySQL（稳定）

SQLServer（微软的产品绑定使用）

总结
线上系统首选percona（稳定，工具包功能多），其次选择MySQL，其次Mariadb

mysql关键文件

错误日志：对mysql的启动运行关闭进行了记录

二进制日志：记录了对数据库进行变更的操作，不包括select和show操作，主要用于恢复、复制

慢查询日志：记录运行较慢的sql，用来做优化的参考

全查询日志：记录mysql的所有请求，用以审计和问题排查，会损失性能

redo日志：用来恢复数据的日志，数据库先写日志，再写数据文件

undo日志：存储了数据更新操作，回滚时将其数据回写到数据文件内

socket文件：linux下本地连接mysql时采用套接字socket方式，一般在/tmp下

pid文件：自己的pid进程号

mysql表结构文件：存放mysql表结构定义的文件，比如.*frm *.ibd

数据引擎文件：记录存储引擎信息的文件

存储引擎

存储引擎称为表类型，决定了数据库如何存储数据、如何为数据建立索引、如何更新、如何查询数据的实现方法

mysql可选的存储引擎：可选MyISAM、InnoDB，MERGE等，最常用MyISAM，InnoDB，MySQL5.5开始将默认存储引擎从MyISAM换成了InnoDB

MyISAM与InnoDB的对比

1.InnoDB支持事务，MyISAM不支持
2.MyISAM查询速度快，适合大量select，可以全文索引(查的更快)，InnoDB适合频繁修改和安全性高的业务
3.InnoDB支持外键、行级锁，MyISAM不支持
4.MyISAM索引和数据分开，索引是压缩的，索引缓存在内存中；InnoDB把数据缓存在内存中，内存越大优势越大
5.InnoDB支持高并发请求，比如大量insert、update操作
总结
1.如果要使用事务，使用InnoDB
2.如果只对查询性能要求比较高的情况，使用MyISAM（不推荐）

InnoDB存储引擎关键文件
.frm文件：表的结构，索引文件
ibdata1 数据文件
ib_logfile0 ib_logfile1：事务日志文件（读写操作日志）
数据库宕机时进行回滚，保证数据一致性。首先写0，写满后写1
mysql最大可以将这个文件设置成512G，一般设置为4G。对这个文件做镜像，可以做同时读写，

MyISAM存储引擎关键文件
.frm文件：表结构
.MYD：表数据文件
.MYI：表索引文件

InnoDB存储引擎

InnoDB Buffer Pool缓存池

不仅缓存索引数据，还会缓存表的数据。并且完全按照数据文件中的数据块结构信息来缓存

缓存池大小表明一共有多少个缓冲帧，每个缓冲帧buffer frame为16K

undo page
事务的提交回滚，保证事务的正常运行

change buffer page
保存缓存和索引的信息

adaptive hash index自适应哈希索引
通过缓冲池的B+树构造而来，InnoDB会根据访问的频率和模式自动建立哈希索引

index page
对于数据记录(查找、插入、删除)操作的缓存

lock info
缓存InnoDB中的锁信息

data dictionary数据字典
缓存数据字典的信息

additional memory pool额外内存池

用来保存数据字典信息和其他内部数据结构的内存池的大小

相关参数：
innodb_additional_mem_pool_size(字节)可以对这个池子进行设置

redo log buffer重做日志缓存

重做日志信息会缓存到buffer，然后写入重做日志文件ib_logfile0 ib_blogfile1

相关参数：
innodb_log_buffer_size 默认为8M

将redo log buffer中的重做日志信息刷新到重做日志文件有几种情况（策略）

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

2.每个事务提交时，刷新到文件

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

二进制日志缓冲区Binlog Buffer

主要用来缓存各种数据变更所产生的二进制日志信息：为了提高性能，mysql并不每次将二进制日志直接写入log file，而是先写入缓存，满足特定条件后再一次写入log file中

二进制日志缓冲区与重做日志缓存的区别

二进制日志

只记录mysql相关的日志记录
记录事务的具体操作内容，逻辑日志
只在事务提交完成后进行写入，只写磁盘一次

重做日志

记录物理内存中，每页的更改情况
在事务进行中就不断有重做日志写入重做日志文件

Double Write Buffer

是InnoDB表空间ibdata中一块连续128页（2兆）的存储空间

用来处理产生partial write时的数据恢复（断电之后page损坏，用来做数据恢复）

dirty page
指的是正在写入，但没有完全写入数据文件的page，会先刷新到Double Write Buffer中
如果此时出现数据丢失，就会通过Double Write Buffer将page刷新到数据文件中

InnoDB逻辑存储结构

表空间-段-区-页
表空间：所有数据都在表空间内
段：表空间有若干段组成，比如数据段、索引段、回滚段
区：每64个连续的页组成区，区大小正好为1M
页：InnoDB管理及磁盘的最小单位，固定为16K
行：InnoDB表中数据按行存储，每页最多可以存放7992个行

InnoDB事务

事务是指单个逻辑单元执行的一系列操作，要么完全执行要么完全不执行

一个逻辑工作单元要成为事务，必须满足ACID原子性、一致性、隔离型、持久性

A：Atomic，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；（如果失败就会回滚）

C：Consistent，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；

I：Isolation，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；

D：Duration，持久性，即事务完成后，对数据库数据的修改被持久化存储。

事务的问题

1.AB同时读写同一张表同一行
解决方法：锁行

2.脏读
A读取了B还未提交的数据

3.不可重复读
A重复读取同一个数据，这个过程中B进行了修改
解决方法：锁行，在修改事务完全提交后才可以进行读取数据

4.幻读
A修改后未提交时，B新增或删除了，A再查询，发现其中有某条未修改
解决方法：锁表

事务的隔离级别

读未提交read uncommited

最低的隔离级别，一个事务可以读到另一个事物未提交的结果

所有问题都会出现

读已提交read commited（推荐、大多数）

大多数数据库的默认隔离级别

只有事务提交后其更新结果才会被其他事务看见，能解决脏读

重复读repeated read（不安全）

少数数据库的默认隔离级别，如MySQL的InnoDB，但建议使用读已提交

在一个事务中对同个数据读取结果总是相同

解决了脏读、不可重复读，但不安全

串行化Serialization（表锁）

事务串行化执行，隔离级别最高，牺牲了系统的并发性能

可以解决所有问题

查看与设置数据库的隔离级别

show global variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
可以看到默认为重复读

设置参数
set global tx_isolation='READ-COMMITTED';
set session tx_isolation='READ-COMMITTED';
或添加my.cnf
tx_isolation=
READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ
SERIALIZABLE

事务的操作命令

start transaction;手动开始执行事务操作
rollback;         在事务未提交前回滚，放弃修改
savepoint a;      设置当前位置节点保存点为a
rollback to a;    回滚至保存点a
commit;           提交事务

查看是否自动提交事务
set global autocommit=0;
打开自动提交风险很大

事务隔离等级演示

读未提交

开两个窗口A和B
use wsdb;
set global autocommit=0;
set session tx_isolation='READ-UNCOMMITTED';
show session variables like 'tx_isolation';
+---------------+------------------+
| Variable_name | Value            |
+---------------+------------------+ 
| tx_isolation  | READ-UNCOMMITTED |
+---------------+------------------+
A窗口
insert into wst1 values(1,'ws1');
此时尚未提交，A和B都能查询到未提交的数据，这是脏数据

A回滚事务
rollback;
此时A和B都无法查询到数据

读已提交

退出会话，重新进入
mysql -uroot -p
切换隔离级别
set session tx_isolation='READ-COMMITTED';
show session variables like 'tx_isolation';
insert into wst1 values(2,'ws2');
可见未提交状态下，A终端可以查询到，而B终端无法查询到

但此时又会有重复读问题，因为commit后，重复读时值会变化

可重复读

退出会话，重新进入
mysql -uroot -p
切换隔离级别
set session tx_isolation='REPEATABLE-READ';

开始事务
start transaction;

A窗口插入数据
insert into wst1 values(3,'ws3');

此时A能查到，B无法查到

A窗口提交数据
commit;

此时A能查到，B依然无法查到（因为B处于事务状态）

串行化

退出会话，重新进入
mysql -uroot -p
切换隔离级别
set session tx_isolation='SERIALIZABLE';

AB窗口开始事务
start transaction;

A窗口插入
insert into wst1 values(4,'ws4');

B窗口select查询，发现被锁，处于阻塞状态

A窗口commit;
此时B窗口查询成功

MVCC多版本并发控制

MVCC（Multi-Version Concurrency Control），即多版本并发控制，是一种用于提高数据库并发性能的技术。它允许在数据库中存在数据的多个版本，从而在不阻塞读写操作的情况下，处理并发事务中的读写冲突。

最早的数据库版本之后读读可以并发，读写/写读都会阻塞，引入多版本后，只有写写相互阻塞，其他三种操作都可以并行，大幅提高了InnoDB的并发度

内部实现中，InnoDB通过在undo log中实现，通过undo log可以找回数据的历史版本（前镜像），也可以在回滚的时候覆盖数据页上的数据

实现原理

当每个事务启动时，InnoDB会为每个事务提供一个当前时刻的镜像

为了实现此功能，InnoDB会为每个表提供2个隐藏字段
1.保存行的创建时间
2.保存行的失效时间(系统版本号SVN)

某个事务中使用和当前事务相等或更旧的版本号数据，保证都是过去的数据

MVCC的特点

优点
获取数据的时候使用SVN检查版本号，来控制读取需要的数据版本，而不使用锁，使并发度能大大提高

缺点
为了实现SVN，每行都需要增加相应字段来存储版本信息。在检索行时需要进行版本的比较，降低了一些性能；需要定期清理不再需要的版本，及时回收空间，增加开销

注意事项
MVCC只在读已提交和重复读的隔离级别下有效
因为读未提交一定是读取最新的数据行；串行化会对所有行都加锁

InnoDB的redo和undo

redo log重做日志

任何事务的操作都会记录redo日志，是InnoDB独有的，用于恢复数据库到宕机为止

redo log记录的是新数据的备份
在事务提交前，会先写到redo log中——即使事务未提交，数据库崩溃时，也可以通过redo log进行恢复

在数据库崩溃之后先用redo log和旧的数据块做前滚
再用undo log做回滚
通过这种方式来保护修改又没写入到磁盘的脏页

rolling forward前滚

数据库启动时，通过redo logs，重现实例崩溃前对数据库的修改操作

将已经提交但未写入的数据，以及未提交的事务的数据全部写入数据文件

rolling back回滚

前滚后，虽然已修改的数据已经被写入数据文件，但未提交的也被写入，所以使用undo log将这些事务撤销

dirty page与redo log

1.InnoDB的buffer pool是数据库页面的缓存，进行数据修改会首先在page上进行，这样的页面会被放到专门的dirty page缓存页上，后续再由专门的线程阶段性将其写入磁盘

可以避免每次写对硬盘造成的负载，所以设计了一个这样类似水位线的机制

2.为了避免dirty page丢失，将所有页面修改写入了redo log，当数据库启动时就可以进行恢复操作

3.redo log info会写写到redo log buffer（这也是InnoDB存储引擎的一个缓存池），根据条件刷新到redo log file

4.redo log file由log block组成，每个block512字节，重做日志文件都是以块的方式进行保存，称为重做日志块redo log block

redo log触发机制

每1秒写
大于log buffer空间二分之一的时候
log buffer到了1M时
WAL日志先写——日志要比磁盘时数据更新，就写日志
commit提交时

（1）Innodb_flush_log_at_trx_commit=0事务提交不刷新redo log buffer，直接写redo log file（失去最后一秒的数据）
（2）Innodb_flush_log_at_trx_commit=0 事务提交直接将buffer刷新到磁盘（最安全不会丢数据、IO压力大）
（3）Innodb_flush_log_at_trx_commit=0 事务提交时将buffer刷新到os缓存（可能丢失数据）