存储引擎

作者: 麟之趾a | 来源:发表于2020-03-11 15:44 被阅读0次

简介

相当于linux文件系统，只不过比文件系统更加强大
MySQL支持同一个实例，存在多种存储引擎(于 DB2，sqlserver，oracle都不一样)，是针对表级别设计的

功能了解

数据读写
数据安全性和一致性
提高性能（提供了缓存区和索引）
热备份
自动故障恢复
高可用方面的支持

存储引擎介绍

MySQL的大致引擎分类

InnoDB（默认）
MyISAM
MEMORY
CSV
第三方存储引擎： ROCKSDB, myROCKS，TokuDB，属于压缩比高，数据插入性能高，其他功能和InnoDB一样。不过MySQL需要插件支持

同源产品存储引擎

perconaDB XtraDB（默认）
MariaDB InnoDB（默认）

InnoDB存储引擎的核心特性说明

事务
行锁（MyISAM表级锁）
MVCC(多版本并发控制）
外键
ASCR（自动故障恢复）
热备（MyISAM温备）
复制（多线程，GTID,MTS）

存储引擎查看

show engines 查看M有SQL自带的存储引擎
SELECT @@default_storage_engine 查看当前MySQL的存储引擎

查看表的存储引擎

show create table 表名

存储引擎修改

alter table 表名 engine='innodb'

存储引擎整理碎片

碎片: 是在做delete时，逐行删除原有数据时，原有数据的空间不会立即释放掉，所以会产生碎片。碎片中的数据越来越多，数据页就会出现间隙，所以索引也会出现间隙。影响查询效率.
alter table city engine='innodb'整理碎片(前提表是innodb引擎)，会有短暂的锁表
另一种方法：先把表数据导出来，truncate原先的表，再表导入进去（最干净，没有碎片）

批量替换zabbix小表innodb为tokudb

select concat('alter table',table_schema,'.',table_name,'engine=tokudb') from information_schema.tables where table_schema='zabbix'

InnoDB 存储的物理结构（最直观的存储方式/data/mysql/data）

ibdata1: 共享表空间
MySQL5.6: 存放数据字典，undo日志，临时表中的数据
MySQL5.7: 存放数据字典，undo日志
MySQL8.0：存放数据字典
ib_logfile0~ib_logfile1: 存放redo日志，事务日志文件
ibtmp1： 临时表空间设置，存放临时表
frm: 存储表的列信息
ibd: 存放表的数据和索引

表空间（tablespace）

ibdata1: 整个库的统计信息+undo
ibd: 数据行和索引

共享表空间（ibdata1~ibdataN）

MySQL5.5: 默认共享表空间模式，所有的数据都存在共享表空间里
MySQL5.6: 转换为独立表空间，共享表空间只用来存储数据字典，undo日志，临时表
MySQL5.7: 临时表被独立出去
MySQL8.0: undo日志也被独立出去
查看共享表空间

mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
表示初始值一个共享表空间，初始大小12M，自动增长，每次增长64M
共享表空间，可以在MySQL初始化时指定，如指定两个共享表空间文件，先把ibdata1用完，在用ibdata2

独立表空间（ibd）

从5.6开始，默认表空间不再使用共享表空间，替换为独立表空间。
存储特点：一个表一个ibd文件，存储数据行+ 索引信息
基本表结构元数据存储： xx.frm

最终结论

一张innodb表=ibd+ibdata1+frm

MySQL存储日志

redo log: ib_logfile0 ib_logfile1，重做日志
undo log: ibdata1 ibdata2（存储共享表空间中），回滚日志
临时表： ibtmp1 ,在做join union操作产生的临时数据，用完自动清理（当分配的MySQL内存不够时）

独立表空间设置问题

+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+
默认为1，表示1个表，代表一个独立的表空间文件

独立表空间迁移

alter table t100 discard tablespace;   //清楚t100表空间数据
alter table t100 import tablespace;    //导入t100 ibd数据

独立表空间迁移步骤

1 .建立和原表结构一致的表
2 .将空表的idb文件删除alter table t100 discard tablespace
3 .将原先表的ibd文件，拷贝，当前的数据目录下，并修改权限
4 .将原表的数据进行导入alter table t100 import tablespace

innodb的核心特性

事务

事务的ACID特性

事务就是保证交易在计算机（程序）中的完整性

原子性（Atomic）: 所有语句作为一个单元全部成功或全部取消，不能出现中间状态
一致性（Consistent）：如果数据库在事务开始时，处于一致状态，则在执行该事务期间保留一致状态
隔离性（isolated）: 事务之间不相互影响
持久性（durable）: 事务成功之后，所做的所有更改都会准备地记录在数据库中，所做的更改不会丢失。

事务的生命周期（标准的事务控制语句）

开启事务
begin
标准的事务语句
DML: insert update delete
事务结束
commit 提交
rollback 回滚（只回滚未提交的）
自动提交机制（autocommit）
autocommit 默认开启

在线修改autocommit参数

set autocommit = 0      //会话级别（只影响当前会话，及时生效）
set global autocommit =0  //全局级别（窗口断开，重连，才会生效）

配置文件修改

vim /etc/my.cnf
autocommit = 0

事务的隐式提交

触发隐式提交的语句

同一个会话内
begin 开始
DML 
DML
[commit]  触发
[set 命令] 触发
[DDL]   触发
[DCL] 触发
[锁定语句 lock tables和 unlock tables] 触发
[begin]  触发

事务的ACID如何保证

一些概念名词

redo log: 重做日志，ib_logfile0~ib_logfileN 轮询使用
redo log buffer: 负责redo日志的读和写，redo的内存区域
ibd: 存储数据行和索引
data buffer pool: 数据缓冲区池，数据和索引缓冲和缓存，ibd的内存区域
LSN: 日志序列号（类似于git版本号）存放于磁盘数据页（对应ibd文件），redo文件，data buffer pool，redo buffer，MySQL每此数据库启动，都会比较磁盘数据页（ibd）和redo日志中的LSN，必须要求两者的LSN一致数据库才能启动
WAL： 持久化的一种机制。write ahead log, 日志优先于数据写入磁盘
持久化: 是把内存的数据写入到磁盘
脏页： 内存脏页，内存中发生了修改，没写入到磁盘之前，我们把内存中的数据页称为脏页。即此时ibd里面的LSN好喝data buffer pool中的LSN号不一致
ckpt: checkpoints 检查点，就是脏页刷写到磁盘的动作（功能）
TXID: 事务号，InnoDB会为每一个事务生成一个事务号，伴随着整个事务周期。直到commit或rollback

redo日志

作用： 主要保证D(持久性）还为 A （原子性）和 C （一致性）也有作用

记录了什么
1 .记录了内存数据页的变化
2 .提供了快速持久化的功能

CSR过程中实现前滚操作（磁盘数据页和redo日志中的LSN号要保持一致）

redo日志工作过程
image.png
为什么把redo buffer写入到redo日志中，就已经commit成功了？

因为data buffer pool 是将ibd中所要修改行的中所在的整个数据页加载上来，进行修改，然后把整个数据页重新写入磁盘。如果进行大量的DML 语句，会加载大量的数据页，写入磁盘时，会造成磁盘IO瓶颈，所以采用redo日志的方式，记录行的变化，写入到磁盘。等数据页达到内存的一个限定值时，就会写入到磁盘

突然断电，导致data buffer pool数据没有写入到ibd中，但以commit成功时

启动MySQL，会检查redo 日志中的LSN 和 ibd中的LSN是否一致，如果不一致，则重新加载idb文件内容，和redo日志中的内容，进行自动故障恢复（ASCR）

自动故障恢复ASCR

1.先把脏页重新构建出来（重新加载idb文件内容，和redo日志中的内容到内存，构建脏页）
2 .构建出脏页后，立即触发ckpt

redo的刷写策略

commit 将redo buffer刷写到redo日志上（redo buffer中可能存在未提交的事务，也会被刷写到redo日志中）

undo

作用： 在ACID特性中，主要保证A（原子性）的特性，同时对C（一致性）和 I（隔离性）也有一定功效
记录了什么

记录了数据修改之前的状态
rollback将内存的数据修改恢复到修改之前的
在CSR实现未提交的数据进行回滚操作
实现一致性快照，配合锁机制，隔离级别，保证MVCC读和写的操作，不会相互阻塞

image.png
当redo buffer存在未提交的事务，也被刷到磁盘上，时断电情况

当redo日志，存在未提交事务时，会给此事务（TXID）打上未提交的标签，然后对比undo日志的TXID，从undo日志加载出来原先数据，到undo buffer中，进行回滚。最终保持了，ibd和redo日志中的LSN号一致

锁

实现了事务之间的隔离功能
innodb中实现了行级锁
row-level lock
gap 锁
next-lock

隔离级别

查看隔离级别
SELECT @@tx_isolation;
RU: 读未提交，可脏读（当事务没有提交时，就能读到内存中的数据）
例：当你去银行取钱，还没点确定时。老婆在家查余额，就发现少钱了
RC： 读以提交，可能出现幻读，可防止脏读，不可重复读，总是读到最新数据
例: 银行在做月结的时候 2020/01/31 23:59:59，当这个查询持续5分钟，但银行的业务并不会停止，做完查询可能就会读到2月份的数据，并且每次查询结果都不一样。
RR： 可重复读，功能是防止幻读现象，利用的是undo的快照技术和GAP（间隙锁）+Nextlock（下键锁）
可重复读: 是有MVCC实现的
MVCC： 使用undo快照，MVCC在每一个会话开启是，都会生成一致性快照，将来读取的时候都会去读这个快照的数据。所谓的多版本控制，就是开启了多个会话，提供了多个undo快照版本。直接commit会重新起一个新的会话
幻读： 在两个会话中，第一个修改ID>2的name值为zs，但是在第二个会话中，插入一个ID为10，name列为ls，此时第一个会话正在修改，第二个会话的事务已经提交，在第一个会话中查询会发现ID为10的列没有修改。
防止幻读：在RR模式下，GAP+next-lock进行避免幻读现象，必须有索引支持
SR： 可串行化，可以防止死锁，但是连接并发事务差
补充： 在RC级别下，卡哇伊减轻GAP+nextlock锁的问题，但是会出现幻读现象，一般为了读一致性，会在正常的select后，添加for update语句。但是，请牢记执行完一定要加commit，否则容易出现死锁

Innodb核心参数设置

# 存储引擎设置
default_storage_engine=innodb
# 表空间模式设置
innodb_file_per_table=1     //设置为独立表空间
# 共享表空间文件个数和大小
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
# 双一标准其中一个
innodb_flush_log_at_trx_commit=1
# 控制redo buffer和data buffer pool的刷写策略
innodb_flush_method=O_DIRECT
# redo 日志相关
innodb_log_buffer_size=16777216 (redo buffer 大小)
innodb_log_file=50331648     (innodb文件大小)
innodb_log_files_in_group=3 (redo 日志的个数)
# 脏页刷写策略
innodb_max_dirty_pages_pct=75
最大脏页占用内存比例75%，即data buffer 写入到data的限制
还有哪些机制触发写磁盘？
CSR(自动故障恢复)
redo 满了

image.png

innodb_flush_log_at_trx_commit=0 是指redo buffer 刷到os buffer 是每秒进行一次，从os buffer 到 data 也是每秒进行一次。一秒出现无数次事务，如果宕机，会造成一秒数据丢失
innodb_flush_log_at_trx_commit=1 是指redo buffer 刷到 os buffer 每commit 进行一次，os buffer 到 data 也是每进行一次commit进行一次
innodb_flush_log_at_trx_commit=2 从redo buffer 刷到 os buffer 每commit进行一次，从 os buffer 刷到 data 每秒进行一次

在追求性能的结构中，可以设置为innodb_flush_at_trx_commit=0 ,比如监控系统。

redo buffer 和data buffer 刷写策略

fsync（默认）

fsync.png

fsync是由redo buffer和data buffer pool 刷到os buffer，再由os buffer 刷到 data。因为redo buffer 和data buffer pool 都是在内存，fsync这种模式可能会给内存造成压力

O_DIRECT（推荐）

image.png

O_DIRECT是redo buffer直接刷到data，data buffer pool先刷到os buffer，再由os buffer 刷到data

O_DSYNC

image.png

O_DSYNC是把redo buffer直接刷到 data，data buffer pool 刷到os buffer，再由os buffer 刷到data

最高安全模式

innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

最高性能模式

innodb_flush_log_at_trx_commit=1
innodb_flush_method=fsync

存储引擎

简介

功能了解

存储引擎介绍

MySQL的大致引擎分类

同源产品存储引擎

InnoDB存储引擎的核心特性说明

存储引擎查看

查看表的存储引擎

存储引擎修改

存储引擎整理碎片

批量替换zabbix小表innodb为tokudb

InnoDB 存储的物理结构（最直观的存储方式/data/mysql/data）

表空间（tablespace）

共享表空间（ibdata1~ibdataN）

独立表空间（ibd）

最终结论

MySQL存储日志

独立表空间设置问题

独立表空间迁移

独立表空间迁移步骤

innodb的核心特性

事务

事务的ACID特性

事务的生命周期（标准的事务控制语句）

在线修改autocommit参数

事务的隐式提交

事务的ACID如何保证

一些概念名词

redo日志

自动故障恢复ASCR

redo的刷写策略

undo

锁

隔离级别

Innodb核心参数设置

redo buffer 和data buffer 刷写策略

fsync（默认）

O_DIRECT（推荐）

O_DSYNC

最高安全模式

最高性能模式

概述redo日志和undo日志

redo 日志

undo日志

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读