MySQL系列：（九）InnoDB存储引擎

admin 2021年01月11日 783次浏览

1、存储引擎查看

  • 查看存储引擎设置

    # 查看数据库支持的引擎
mysql> SHOW ENGINES;
# 查看默认存储引擎
mysql> SELECT @@default_storage_engine;

  • 查看表存储引擎状态

    SHOW TABLE STATUS LIKE '表名';

2、存储引擎修改

  • 语法
    ALTER TABLE 表名 ENGINE=引擎名称

  • 示例

    mysql> ALTER TABLE student ENGINE=InnoDB;

3、碎片整理

  • 语法
    ALTER TABLE 表名 ENGINE=原引擎名称

  • 示例

    mysql> ALTER TABLE student ENGINE=InnoDB;

4、InnoDB物理存储结构

4.1、存储文件介绍

  • ibdata1：系统数据字典信息
  • UNDO_*：回滚信息
  • ib_logfile0：REDO日志文件
  • ib_logfile1：事务日志文件
  • ibtmp1：临时表空间
  • frm：存储表的列信息
  • ibd：表的数据行和索引

4.2、表空间

  • 共享表空间

    • 共享表空间在5.5版本中为默认模式，在5.6中转换为了独立表空间
    • 共享表空间需要将所有数据存储到同一个表空间中，其存在的问题是：管理比较混乱、ibdata文件过大
    • 5.6版本开始，共享表空间保留但只用来存储数据字典信息、undo、临时表
    • 5.7版本开始，临时表被独立出来
    • 8.0版本开始，undo表被独立出来

  • 共享表空间设置

    共享表空间设置需要在MySQL初始化数据之前设置到参数文件中

    # 查看共享表空间信息
mysql> select @@innodb_data_file_path;

# 设置默认共享表空间参数，在MySQL配置文件中添加以下参数
## 设置初始共享表空间大小与自动扩展
innodb_data_file_path=ibdata1:[默认大小]:ibdata2:[默认大小]:autoextend
## 示例
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

## 每次自动扩展的大小（单位：Mb）
innodb_autoextend_increment=64

  • 独立表空间
    从5.6开始，默认表空间不再使用共享表空间，而替换为独立表空间，主要用于存储用户数据，其存储特点为：一个表一个ibd文件，用于存储数据行和索引信息

  • MySQL的存储引擎日志：

    • Redo Log（重做日志）: ib_logfile0 ib_logfile1
    • Undo Log（回滚日志）: ibdata1 ibdata2
    • ibtmp1（临时表）：在做join、union操作时产生的临时数据，用完就自动清理

  • 独立表空间迁移

    • 创建和原表结构一致的空表
    • 将空表的ibd文件删除
    mysql> alter table [表名] dicard tablespace;
    • 将原表的ibd拷贝过来,并且修改权限
    • 将原表ibd进行导入
    mysql> alter table [表名] import tablespace;

5、InnoDB 存储引擎核心特性

5.1、事务

5.1.1、事务的ACID特性

  • Atomic（原子性）:所有语句作为一个单元全部成功执行或全部取消，不能出现中间状态

  • Consistent（一致性）:如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态

  • Isolated（隔离性）:事务之间不相互影响

  • Durable（持久性）:事务成功完成后，所做的所有更改都会准确地记录在数据库中，所做的更改不会丢失

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

  • 开启事务

    5.7版本后，在执行事务语句时，数据库默认隐含开启事务

    mysql> begin;

  • 标准的事务语句(DML)
    insert

    update

    delete

  • 结束事务

    • 提交
    mysql> commit;
    • 回滚
    mysql> rollback;

5.1.3、自动提交机制(autocommit)

自动提交机制是语句执行完毕后立即生效，不能执行回滚操作，因此建议关闭自动提交机制

  • 查看自动提交机制状态
mysql> select @@autocommit;

  • 在线修改参数

    • 会话级别(及时生效,只影响当前登录会话)
    mysql> set autocommit=0;
    • 全局级别(断开窗口重连后生效,影响到所有新开的会话)
    mysql> set global autocommit=0;
    • 永久修改(修改配置文件，将autocommit设置为0，然后重启生效)
    autocommit=0

5.1.4、隐式提交的情况

  • 导致提交的非事务语句
    DDL语句：ALTER、CREATE、DROP
    DCL语句：GRANT、REVOKE、SET PASSWORD
    锁定语句：LOCK TABLES、UNLOCK TABLES

5.2、保证事务的ACID

5.2.1、一些概念名词

  • redo：数据重做
    • redo log：重做日志
      相关文件：
      ib_logfile0~1 默认大小50M , 轮询使用
  • redo log buffer：redo的内存区域
  • ibd：存储数据行和索引
  • buffer pool：缓冲区池，数据和索引的缓冲
    • LSN : 日志序列号
      • 涉及LSN号的数据：ibd ，redolog ，data buffer pool，redo buffer
      • MySQL每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动
  • WAL (持久化):日志优先写的方式实现持久化，日志是优先于数据写入磁盘的.
  • 脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.
  • CKPT:检查点,就是将脏页刷写到磁盘的动作
  • TXID:事务号,InnoDB会为每一个事务生成一个事务号,事务号会伴随着整个事务生命期。

5.2.2、redo：重做日志

  • 作用

    保证数据的持久性，对数据的原子性和一致性也有一定的作用

  • 记录内容
    （1）、记录了内存数据页的变化.
    （2）、提供快速的持久化功能(WAL)
    （3）、CSR（自动故障恢复）过程中实现前滚的操作(使磁盘数据页和redo日志LSN一致)

  • redo日志位置
    redo的日志文件：iblogfile0、iblogfile1

  • redo buffer
    redo的buffer，记录数据页的变化信息与数据页当时的LSN号

  • redo的刷写策略

    • commit;
    • 刷新当前事务的redo buffer到磁盘，同时会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘
    • MySQL在启动时，必须保证redo日志文件和数据文件LSN必须一致，如果不一致就会触发CSR（自动故障恢复），最终保证一致

5.2.3、undo：回滚日志

  • 作用:

    在 ACID特性中,主要保证数据的原子性，同时对数据的一致性和隔离性有一定作用

  • 记录内容
    （1）、记录了数据修改之前的状态
    （2）、将内存的数据修改恢复到修改之前（rollback）
    （3）、在CSR中实现未提交数据的回滚操作
    （4）、实现一致性快照,配合隔离级别保证MVCC（多版本变更控制）,读和写的操作不会互相阻塞

5.2.4、锁

​ 实现了事务之间的隔离功能，InnoDB中实现的是行级锁，在数据修改时，会锁定部分行，在锁定行上，只有上一个事务提交后，才能进行下一个事务。

5.3、隔离级别

  • RU（read-uncommitted）: 读未提交，可脏读，在生产环境中不使用
  • RC（read-committed）: 读已提交，可能出现幻读，但是可以防止脏读（该隔离级别只有行级锁，没有GAP(间隙锁)和NextLock(下键锁)，相对RR模式而言，性能略好）
  • RR（REPEATABLE-READ）: 可重复读，功能是防止"幻读"现象 （必须有索引，没有索引也可能会出现“幻读”），该隔离级别利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)，在该隔离级别下，只能查询到截止到查询开始时的数据（默认级别）
  • SR: 可串行化,可以防止死锁,但是并发事务性能较差

5.3.1、查询隔离级别

# 8.0版本以前
mysql> SELECT @@tx_isolation;
# 8.0版本以后
mysql> SELECT @@transaction_isolation;

5.3.2、修改隔离级别

在配置文件中添加参数

transaction_isolation=[隔离级别]

5.3.3、相关名词解释

  • 不可重复读：在业务执行过程中，如果有新的事务发生并提交后，执行中的业务可以立即查看到新事务提交后的数据，不能将业务结果固定在某一时刻。
  • 幻读：在业务执行过程中，如果有新的数据插入并提交后，新插入的数据不能达到业务执行的预期效果。

5.4、InnoDB存储引擎核心参数介绍

  • 存储引擎默认设置

    default_storage_engine=innodb

  • 表空间模式

    innodb_file_per_table=1

  • 共享表空间文件个数和大小

    # 语法
innodb_data_file_path=ibdata1:[默认大小]:ibdata2:[默认大小]:autoextend
# 示例
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

  • redo日志刷写策略

    innodb_flush_log_at_trx_commit=1
    • 值为0 : 提交事务的时候，不立即把 redo log buffer 里的数据刷入磁盘文件中，而是依靠 InnoDB 的主线程每秒执行一次刷新到磁盘。此时可能有提交事务了，但是mysql 宕机了，然后此时内存里的数据全部丢失。
    • 值为1 : 提交事务的时候，就必须把 redo log 从内存刷入到磁盘文件里去，只要事务提交成功，那么 redo log 就必然在磁盘里了。由于操作系统的“延迟写”特性，此时的刷入只是写到了操作系统的缓冲区中，因此执行同步操作才能保证一定持久化到了硬盘中。
    • 值为2 : 提交事务的时候，把 redo 日志写入磁盘文件对应的 os cache 缓存里去，而不是直接进入磁盘文件，可能 1 秒后才会把 os cache 里的数据写入到磁盘文件里去。

  • 控制innodb数据文件及redo log的刷写模式

    Innodb_flush_method=[fsync、O_DIRECT、O_DSYNC]

    • fsync模式（默认方式)：写数据时，会先将redo buffer和buffer pool写入到系统缓存中，然后再同步到磁盘中

    • O_DIRECT模式：写数据时，会先将redo buffer写入到系统缓存中，然后再同步到磁盘中；但是buffer pool不经过系统缓存，直接写入到硬盘中

    • O_DSYNC模式：写数据时，会先将buffer pool写入到系统缓存中，然后再同步到磁盘中；但是redo buffer不经过系统缓存，直接写入到硬盘中

    • redo常用刷写策略组合

      • 最高安全模式

        innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT

      • 最高性能

        innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync

  • redo日志设置有关的参数

    # redo日志缓存大小（单位：字节）
innodb_log_buffer_size=16777216
# redo日志空间大小（单位：字节）
innodb_log_file_size=50331648
# redo日志文件数量
innodb_log_files_in_group = 3

  • 脏页刷写策略（百分比）

    当脏页比例达到设定值时，数据库会将redo buffer中的数据写入到硬盘中

    innodb_max_dirty_pages_pct=75

  • 触发redo缓存写磁盘时机

    • CSR
    • redo缓存满
上一篇： MySQL系列：（十）MySQL日志管理