MySQL系列:(九)InnoDB存储引擎

admin 2021年01月11日 872次浏览

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缓存满