存储引擎介绍

简介

存储引擎是MySQL中真正与文件打交道的子系统，类似linux的文件系统，但比文件系统强大。除了用于读写数据、组织数据存储方式外，存储引擎还能提供一些特殊的功能。

常见的存储引擎有：InnoDB、MyISAM、MEMORY、CSV、RocksDB、Myrocks、TokuDB，我们主要需要了解MySQL目前使用的InnoDB存储引擎即可。

功能

• 表的数据读写。
• 数据安全和一致性，在宕机时保证数据不丢失，启动时自动恢复。
• 提高性能，提供各种缓存、缓冲。
• 热备份，不同存储引擎有不同备份方式。
• 自动故障恢复。
• 高可用方面支持等。

InnoDB核心特性

事务、行级锁定、MVCC、外键、热备份和恢复、ACSR自动故障恢复、多线程复制。

InnoDB的核心特性也是InnoDB独有的特性，也是和MyISAM特性上的区别。

MySQL存储引擎相关命令

查询支持存储引擎

• SHOW engines;

查询默认存储引擎

• SELECT @@default_storage_engine;

修改默认存储引擎

• MySQL配置文件中添加default_storage_engine=InnoDB参数即可。

查询指定表的存储引擎

• SHOW CREATE TABLE [表名];

查询所有表的存储引擎

• SELECT table_schema,table_name,engine
  FROM information_schema.tables
  WHERE table_schema
  NOT IN('sys','mysql','performance_schema','information_schema');
  

修改指定表的存储引擎

• ALERT TABLE [表名] ENGINE=[存储引擎名];
• 该命令也可以对表进行碎片整理，例如：ALERT TABLE [表名] ENGINE=innodb;。
  • 快速、并且对业务影响较小，但是尽量不要在业务繁忙期使用，会锁一小会表。它会自动扫描全表的数据页，找到其中的缝隙，并且对其进行数据整理，以减少缝隙。
  • 删除类操作时会出现碎片，比如delete是逐行进行逻辑删除的，并且其中的空间不会被立即释放掉，有可能会产生碎片。并且其中的磁盘空间不会回收，表会越来越大，也就造成了数据页有缝隙，索引页也会有缝隙，最终导致影响性能。整理碎片可以提高查询性能、减少碎片的空间占用。

批量替换库中表的存储引擎

• 主要是通过CONCAT生成对应的修改命令。

• SELECT concat('alter table ',table_schema,'.',table_name,' engine=[存储引擎];')
  FROM information_schema.tables
  WHERE table_schema='[库名]';
  

MySQL存储引擎物理结构

数据库文件

• 数据库文件存储在初始化时指定的datadir路径中。
• ibdata1
  • 在5.6版本中存储元数据、UNDO表空间等数据、临时表磁盘存储。
  • 在5.7版本中存储元数据、UNDO表空间等数据。
  • 在8.0版本中存储元数据。
• ib_logfile
  • 存储mysql增删改查时变化的日志。
• ibtmp1
  • 存储临时表。
  • 临时表空间，在做JOIN、UNION等多表操作时，会将多张表整合成一张表，然后存储到临时表空间ibtmp中，查询完后就会自动释放清理。
• .frm
  • 存储表的列信息。
• .ibd
  • 存储表的数据行和索引。

日志文件

• redo log - 重做日志文件：ib_logfile0、ib_logfile1。
• undo log - 回滚日志文件，存储在共享空间中：ibdata1、ibdata2。

表空间(Tablespace)

表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表， 所以称作表空间。

共享表空间

• 共享表空间在MySQL5.5版本中默认使用 (ibdata1~N)，所有的数据包括索引、列、数据行等信息全部堆到一个文件里，方便扩容，但是管理非常不方便，数据会越来越大，且不方便备份。

独立表空间

• 将各种表进行独立存储管理，从5.6开始，默认表空间不再使用共享表空间，而是使用独立表空间，存储特点为一个表一个ibd文件，存储数据行和索引如: ibdata、*.ibd、ibtmp等文件。
• 5.6版本，共享表空间ibdata保留，但只用来存储元数据、undo、临时表等。
• 5.7版本，从共享表空间ibdata中，临时表ibtmp被独立出来了。
• 8.0版本，从共享表空间ibdata中，undo也被独立出来了。

InnoDB事务

事务实际就是用于完成某件事的一组DML语句集合。事务具备ACID特性：Atomic(原子性)、Consistent(一致性)、Isolated(隔离性)、Durable(持久性)。

事务的ACID特性

• Atomic (原子性)

  • 一个事务当中的所有语句，要么全部执行成功，要么全部执行失败，不能出现中间状态。

• Consistent (一致性)

  • 如果数据库在事务开始时，处于一致状态，则在事务执行期间也要保留一致状态，不会出现多余的数据。
  • 比如：事务开始时AB加起来100块钱，则事务执行期间，比如A转给B了50元，他们加起来还是100元。

• Isolated (隔离性)

  • 事务之间不相互影响，禁止并发操作，也就是一个事务在操作某些数据行时，另一个事务就不能操作该数据行了。

• Durable (持久性)

  • 事务完成后，数据库所做的所有更改都要准确记录在磁盘中，而非内存中，以使更改不会丢失。

事务相关语句

• 类似于操作一个文件，需要保存才能生效，事务执行后，要么全部成功，要么全部失败。
• BEGIN; - 开启事务
  • 开启后，接下来的所有语句都会具有ACID特性，执行的事务语句，都是作为事务的过程。
• INSERT、UPDATE、DELETE - 事务语句
  • 实际就是DML语句，书写DML语句，就是定义事务的过程。
• ROLLBACK; - 回滚事务
  • 取消执行刚才的事务语句，回滚到事务开始前，只能回滚未提交的事务。
• COMMIT; - 提交事务
  • 提交并执行事务过程的语句，以结束该事务，要么全部成功，要么全部失败。

自动提交机制

作用

MySQL中如果没有手动执行BEGIN开启事务，则每执行一条DML语句，就会自动执行BEGIN，并自动提交。这种方式无法将多条DML语句作为一个事务，并且无法回滚。

配置

• SELECT @@autocommit; - 查看是否开启自动提交(默认开启的)。

• SET autocommit=0; - 临时局部修改，仅对当前会话，即时生效。

• SET GLOBAL autocommit=0; - 临时全局修改，断开MySQL会话，重连后生效。

• 配置文件中添加autocommit=0配置 - 永久修改，重启MySQL服务后生效。

隐式提交

• MySQL在某些情况下会进行隐式提交，隐式提交可能会导致事务不完整，所以应当了解哪些情况会导致隐式提交。

• 情况一：没有提交就又开启新事务

  • 同一会话下，没有提交当前事务就又开一个事务的情况下，会自动提交。
  • 例如：begin;update...;begin;

• 情况二：事务过程定义中执行非DML事务语句

  • 同一会话下，事务过程定义时，执行了非DML事务语句的其他操作语句，也会自动提交事务。
  • 所以应当使用标准的INSERT、UPDATE、DELETE事务语句。
  • 非DML事务语句的其他操作语句即是DCL、DDL语句等，DQL的SELECT、SHOW等查询语句不会，但尽量不要使用。
  • 例如：begin;update...;create database...;

保证事务ACID性的相关概念

• redo log - 重做日志

  • 该日志文件用于记录内存中数据页发生的变化过程，主要用于保证"D"持久性，已经提交的事务，因为有重做日志，所以不会再丢失了，一定可以重做出来。
  • 它是磁盘上的实际文件，例如：ib_logfile0、ib_logfile1，默认每个50M生成一个新日志。

• redo log buffer - redo内存区

  • redo读写时的内存区域，包括缓冲和缓存。

• ibd - 表数据文件

  • 存储行记录和索引的数据文件。

• data buffer pool - ibd缓冲区池

  • 行记录和索引读写时的内存区域，包括缓冲和缓存。

• LSN - 日志序列号

  • 它相当于一个版本号，发生过一次实际操作就+1，用于版本号控制。存在于ibd、redolog、data buffer pool、redo buffer中。
  • MySQL每次数据库启动，都会比较ibd数据页和redolog的LSN号，必须要求两者LSN一致才能正常启动，如果不一致就会触发CSR，最终使日志和实际数据保持一致。

• WAL - 日志优先写(write ahead log)

  • 日志优先写的方式实现持久化。也就是实际操作数据后，会先优先将操作写入到日志，然后才会将更改后的数据实际写入到磁盘中的ibd文件。
  • 日志优先写(WAL)，并且写日志会比写修改数据快很多，提供了快速的持久化功能。

• undo log - 回滚日志

  • 它保存在ibdata1共享表空间文件中，记录数据修改之前的状态。
  • 它还用于实现一致性快照，配合隔离级别保证MVCC，读和写的操作不会互相阻塞。且在CSR中还用于实现未提交事务的回滚操作。

• 内存脏页

  • 内存中发生修改，但还没写入到磁盘之前的状态，我们把该内存页称之为脏页。

• CKPT - 检查点(checkpoint)

  • 就是将内存脏页写入到磁盘的动作。

• TXID - 事务号

  • InnoDB会为每一个事务生成一个事务号，事务开始(begin)时就会生成，伴随着整个事务。

事务的持久性(Durable)如何保证?

• 主要通过redo日志、idb文件、LSN序列号、日志优先写、自动故障恢复机制(CSR)来保证，redo日志主要用于保证"D"持久性。

• MySQL用户修改数据过程

  • 比如用户要修改一个记录的值从1到2。
  • 用户执行begin开启事务，然后执行DML语句修改，mysql就会先读取.ibd文件中数据页(16kB) 读到MySQL内存中的数据缓冲区里。
  • 用户使用DML事务语句数据修改之后，数据页变化的过程，会被记录到redo buffer中。
  • 当用户提交事务后，mysql会将redo buffer中的变化日志，优先写入磁盘日志文件中。
  • 这样的话，即便突然因为断电导致数据没有正常写入磁盘中，只要有redo日志还在，MySQL就可以读取重做日志中的变化信息，去重新执行一遍，就达到了持久化的目的。
  • 然后数据缓冲区中修改后的内存数据，会按时或者按量的(由参数决定)，一次行写入到磁盘的idb文件中。

• 如何保证事务持久性

  • 在redo日志或者脏页中的数据从内存写入到磁盘文件时，会连带着LSN号一起。
  • 这样，假如断电导致ibd数据没有写入，重启系统并且重启MySQL时，mysql发现redo日志文件的LSN号 比.ibd文件的LSN号大。
  • 然后就会触发MySQL的CSR自动故障恢复机制，会立即将旧的ibd文件、redo日志文件读取到对应内存中。
  • 然后MySQL会利用redo日志中的数据，进行重做操作，使得.ibd的原始数据，加上redo重做回的数据。
  • 将断电前的数据，也就是内存脏页，给重新构造出来，然后立即触发CKPT，将构造出来的数据，立马写入到ibd的磁盘文件中。
  • 然后mysql就可以正常启动了。

• redo的刷写策略

  • 在使用commit;语句提交事务时，会刷新当前事务的redo buffer到磁盘日志文件中。
  • 同时，还会顺便将一部分的redo buffer中没有提交的事务日志也刷新到磁盘中，但是会打一个未提交的标记。

事务的原子性(Atomic)如何保证?

• 主要通过undo日志来保证，undo日志主要用于保证"A"原子性。

• rollback回滚过程

  • 用户开始事务时，数据缓冲区会读取.ibd文件中相关的数据页到内存中，与此同时undo log也会记录该数据页的数据，并作为原始数据保留，另外还会记录该事务生成的TXID号。
  • 在用户修改数据时，redo缓冲和数据缓冲区的LSN都会+1，且redo缓冲在记录数据变化过程日志的同时还会记录该事务的TXID号。
  • 当事务提交后，redo缓冲会写入变化日志、TXID号、和一个事务已经提交的标记。
  • 但是如果用户没有提交，而是执行了rollback操作，那么InnoDB就会通过该事务的TXID号找到undo日志中对应事务的原始数据，并覆盖回内存的数据缓冲区当中。
    • 因为本来就是没有提交事务的，所以就不用覆盖到磁盘。

事务的隔离性(Isolated)如何保证?

• 主要是通过锁来实现事务之间的隔离功能，在InnoDB中实现的是行级锁。来防止一个事务在对某一数据行进行操作时，其他事务又对其进行操作。
• 行级锁即是GAP Lock(间隙锁) + Next Lock(下键锁)。
  • 间隙锁用于锁定一个范围，但不包含记录本身。
    • 比如一个表有id为1,3,5的记录，我们通过UPDATE语句修改时的WHERE条件是id BETWEEN 1 AND 5，则这条DML语句中潜在可被操作的间隙就是2,4，MySQL会对其加上间隙锁，防止并发操作。
  • 下键锁用于锁定一个范围，并且锁定记录本身。
    • 比如一个表有id为1,3,5的记录，我们通过UPDATE语句修改时的WHERE条件是id BETWEEN 1 AND 5，则这条DML语句中已经存在的记录就是1,3,5，MySQL会对其加上下键锁，防止并发操作。

隔离级别

• MySQL中可以通过定义隔离级别，来实现不同的读写隔离策略。

• 查看当前隔离级别

  • SELECT @@tx_isolation;

• 修改隔离级别

  • 在配置文件中添加以下配置其中之一，然后重启MySQL实例即可。

  • transaction_isolation=read-uncommitted - RU读未提交，会导致脏读，生产环境中不要出现。

  • transaction_isolation=read-committed - RC读已提交。

  • transaction_isolation=repeatable-read - RR可重复读，默认级别，不配置该参数即可。

相关名词

• 脏读

  • 表示读取的是内存脏页中的数据，可能会造成幻读。

• 不可重复读

  • 表示读取的一直是最新数据，不管当前会话有没有开启事务，读取的都是最新提交事务的数据。
  • 也就意味着我们在同一个事务中执行完全相同的SELECT语句时可能看到不一样的结果。

• 可重复读

  • 表示事务开启后直到提交为止，事务过程中读取到数据永远是当前事务的快照数据。
  • 也就是说只要开启了事务，即便其他会话新提交了事务，当前会话中能查询到的也只会是事务开启时的快照数据。它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

• 幻读

  • 幻读是一种现象，指同一个事务前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。
  • 比如：A事务读取一个条件范围的数据，此时B事务插入了一条符合条件的数据并提交事务，然后A事务用相同的条件查询时，却发现多了一条数据。

级别

• RU - 读未提交

  • 可脏读，一般不允许出现。
  • 该隔离级别的问题是可脏读，会读取到未提交的数据。

• RC - 读已提交

  • 不可重复读，不可脏读。
  • 该隔离级别的问题是不可重复读，也就意味着我们在同一个事务中执行完全相同的SELECT语句时可能看到不一样的结果。
  • 普通业务中允许使用，但金融相关不允许。

• RR - 可重复读 (默认)

  • 可重复读，不可脏读。
  • 该隔离级别的问题是可能出现幻读，但是InnoDB引擎通过MVCC解决了快照读的幻读问题，因为每次读取的都是快照，所以不会有幻读。
    • 快照数据是通过undo日志实现的，MVCC每开启一个事务，就会开启一个undo快照。
  • 或者在事务开始时通过加行级锁，防止其他事务对符合范围条件的行进行修改，来解决幻读。
    • 行级锁 = GAP Lock锁 + Next Lock锁。
    • 特殊情况就是，事务A开启事务后，首先进行的是不加锁查询，事务B插入新数据之后，才进行加锁查询，此时就会发生幻读的情况，但这种情况是只需要在事务开始时进行加锁查询就可以避免。

• SR - 可串行化

  • 可串行化，它是在每个读的数据行上加上共享锁，这个级别可能导致大量的超时现象和锁竞争，并发事务性能较差，所以一般不会使用。

InnoDB核心参数

默认存储引擎配置

• default_storage_engine=innodb

表空间设置

• 表空间模式(共享0/独立1)

  • innodb_file_per_table=1

• 共享表空间文件个数:大小:拓展策略

  • innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

• 控制redo缓冲刷写时间点参数

  • innodb_flush_log_at_trx_commit=1，较为重要，"双一"标准中的其中一个。

  • 0 - 每隔1秒刷写redo缓冲到磁盘中，延迟写，实时刷

    • 会经过两个过程，首先刷写redo缓冲到文件系统缓冲(OS Buffer)，然后再调用fsync()函数去刷写文件系统缓冲到磁盘中，只有这两个步骤都完成，才算事务提交成功。
    • 缺点是该方式如果MySQL挂掉或者机器宕机，可能会丢失1秒的事务数据。对数据安全不太敏感、追求性能的业务下可以使用该模式。

  • 1 - 每次事务提交立即刷写redo缓冲到磁盘中，实时写，实时刷 (默认)

    • 会经过两个过程，同0参数一样，只不过是一有事务提交就进行刷写。
    • 缺点是该方式对硬件IO的要求较高，追求安全的业务下可以使用该模式。

  • 2 - 每次事务提交立即写redo缓冲到OS缓冲中，每隔1秒刷OS缓冲到磁盘中，实时写，延迟刷

    • 只保证redo缓冲刷写到文件系统缓冲(OS Buffer)，同时每隔一秒调用fsync()函数函数去刷写文件系统缓冲到磁盘中。只要redo缓冲刷写到文件系统缓冲就算事务提交成功。
    • 该方式不会立即刷写文件系统缓冲到磁盘中，如果只是MySQL数据库挂掉了，由于文件系统没有问题，那么对应的事务数据并没有丢失。只有在主机操作系统损坏或者突然断电的情况下，数据库的事务数据才可能丢失1秒的事务数据。
    • 该方式减少了事务数据丢失的概率，而对底层硬件的IO要求也没有那么高，因为只是一个buffer转移到另一个buffer不构成磁盘IO。它比0参数更安全、比1参数性能更好，对数据安全不太敏感、追求性能的业务下可以使用该模式。

• 控制redo缓冲和数据缓冲区刷写方式

  • innodb_flush_method=fsync

  • fsync - redo日志和数据缓冲区刷写磁盘时，都经过OS缓冲(默认)

    • 先将redo缓冲和数据缓冲区刷写到文件系统缓冲，然后再调用fsync()函数去刷写文件系统缓冲到磁盘中。

  • O_DIRECT - redo日志刷写经过OS缓冲，数据缓冲区刷写不经过OS缓冲 (建议的模式)

    • 数据缓冲区刷写到磁盘时不经过文件系统缓冲，但是redo缓冲还是会先刷写到文件系统缓冲然后再从文件系统缓冲写到磁盘上。

  • O_DSYNC - redo日志刷写不经过OS缓冲，数据缓冲区刷写经过OS缓冲

    • 和O_DIRECT模式刚好相反，redo缓冲刷写到磁盘时不经过文件系统缓冲，但是数据缓冲区还是会先刷写到文件系统缓冲然后再从文件系统缓冲写到磁盘上。

• 使用建议

  • #平衡(默认)
    innodb_flush_log_at_trx_commit=1
    innodb_flush_method=fsync
    
    # 最安全的模式
    innodb_flush_log_at_trx_commit=1
    innodb_flush_method=O_DIRECT
    
    # 最高性能模式
    innodb_flush_log_at_trx_commit=0
    innodb_flush_method=fsync
    

redo日志设置

• 日志的缓冲区大小

  • innodb_log_buffer_size=16777216

• 日志文件的磁盘占用大小

  • innodb_log_file_zise=50331648

• 日志文件个数

  • innodb_log_files_in_group=3

脏页刷写策略

• innodb_max_dirty_pages_pct=75
• 控制脏页最大缓冲空间占用比率，此处表示如果脏页大小超过数据缓存区的75%就会触发写入到磁盘中。越大性能越好，越小性能影响越大，但一般不会比75大。
• 另外在CSR故障自动修复机制时会触发脏页刷写，redo文件满了时也会触发脏页刷写。