InnoDB存储引擎

2021-05-06

存储引擎的功能

数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持

1# 查看数据库支持的存储引擎
2show engines;
3
4# 查看数据库下所有innodb的表
5mysql> select table_schema,table_name ,engine 
6from information_schema.tables

其他 MySQL 的引擎

1PerconaDB:默认是XtraDB
2MariaDB:默认是InnoDB
3
4其他的存储引擎支持:
5TokuDB  
6RocksDB
7MyRocks
8以上三种存储引擎的共同点:压缩比较高,数据插入性能极高
9现在很多的NewSQL,使用比较多的功能特性.

核心特性

1、事务（Transaction）
2、MVCC（Multi-Version Concurrency Control 多版本并发控制）
3、行级锁(Row-level Lock)
4、ACSR（Auto Crash Safey Recovery）自动的故障安全恢复
5、支持热备份(Hot Backup)
6、复制 Replication: Group Commit , GTID (Global Transaction ID) ,多线程(MTS,Multi-Threads-SQL )

7、外键

存储擎操作类命令

 1# 确认会话存储引擎
 2SELECT @@default_storage_engine;
 3mysql> show variables like '%engine%';
 4
 5# 设置默认存储引擎
 6set default_storage_engine=myisam; # 会话级别
 7set global default_storage_engine=myisam; # 全局级别(仅影响新会话)
 8vim /etc/my.cnf # 写入配置文件重启之后,所有参数均失效.如果要永久生效:写入配置文件
 9[mysqld]
10default_storage_engine=myisam
11
12# 确认表的存储引擎
13SHOW CREATE TABLE city\G;
14SHOW TABLE STATUS LIKE 'city'\G
15
16# 确认每个表的存储引擎
17mysql> select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');
18+--------------+-----------------+--------+
19| table_schema | table_name      | engine |
20+--------------+-----------------+--------+
21| oldboy       | t100w           | InnoDB |
22| school       | course          | InnoDB |
23| school       | sc              | InnoDB |
24| school       | student         | InnoDB |
25| school       | teacher         | InnoDB |
26| world        | city            | InnoDB |
27| world        | country         | InnoDB |
28| world        | countrylanguage | InnoDB |
29| zrblog       | comment         | InnoDB |
30| zrblog       | link            | InnoDB |
31| zrblog       | log             | InnoDB |
32| zrblog       | lognav          | InnoDB |
33| zrblog       | plugin          | InnoDB |
34| zrblog       | tag             | InnoDB |
35| zrblog       | type            | InnoDB |
36| zrblog       | user            | InnoDB |
37| zrblog       | website         | InnoDB |
38+--------------+-----------------+--------+
39
40# 修改一个表的存储引擎
41注意：此命令我们经常使用他，进行innodb表的碎片整理
42alter table t1 engine innodb;
43
44# 将oldboy数据库下的所有1000表，存储引擎从MyISAM替换为innodb 
45select concat("alter table ",table_name," engine innodb;")
46from information_schema.tables 
47where table_schema='oldboy'
48into outfile '/tmp/alter.sql';
49

InooDB 存储引擎物理存储结构

文件名	作用
ibdata1	系统数据字典信息（统计信息），UNDO 表空间等数据
ib_logfile0 ~ ib_logfile1	REDO 日志文件，事务日志文件
ibtmp1	临时表空间磁盘位置，存储临时表
frm	存储表的列信息
ibd	表的数据行和索引
ib_buffer_pool	缓冲池（加载经常访问的数据到内存）

 1root@localhost ~]#  ll /data/mysql/data/
 2total 476084
 3-rw-r-----. 1 mysql mysql        56 Apr 28 13:45 auto.cnf
 4-rw-r-----. 1 mysql mysql       407 Apr 28 21:16 ib_buffer_pool
 5-rw-r-----. 1 mysql mysql  79691776 May  6 09:45 ibdata1
 6-rw-r-----. 1 mysql mysql  50331648 May  6 09:45 ib_logfile0
 7-rw-r-----. 1 mysql mysql  50331648 Apr 28 22:24 ib_logfile1
 8-rw-r-----. 1 mysql mysql  12582912 May  6 10:08 ibtmp1
 9-rw-r-----. 1 mysql mysql         6 Apr 28 21:16 localhost.pid
10drwxr-x---. 2 mysql mysql      4096 Apr 28 13:45 mysql
11-rw-r-----. 1 mysql mysql       177 Apr 28 13:48 mysql-bin.000001
12-rw-r-----. 1 mysql mysql       177 Apr 28 13:48 mysql-bin.000002
13-rw-r-----. 1 mysql mysql       177 Apr 28 13:51 mysql-bin.000003
14-rw-r-----. 1 mysql mysql       573 Apr 28 13:53 mysql-bin.000004
15-rw-r-----. 1 mysql mysql   1389115 Apr 28 21:16 mysql-bin.000005
16-rw-r-----. 1 mysql mysql       177 Apr 28 21:16 mysql-bin.000006
17-rw-r-----. 1 mysql mysql       177 Apr 28 21:16 mysql-bin.000007
18-rw-r-----. 1 mysql mysql 293029039 May  6 09:45 mysql-bin.000008
19-rw-r-----. 1 mysql mysql       272 Apr 28 21:16 mysql-bin.index
20-rw-r-----. 1 mysql mysql     69650 May  6 09:23 mysql.log
21drwxr-x---. 2 mysql mysql        54 Apr 29 06:51 oldboy
22drwxr-x---. 2 mysql mysql      8192 Apr 28 13:45 performance_schema
23drwxr-x---. 2 mysql mysql       160 Apr 28 17:27 school
24drwxr-x---. 2 mysql mysql      8192 Apr 28 13:45 sys
25drwxr-x---. 2 mysql mysql       174 May  6 09:45 world
26drwxr-x---. 2 mysql mysql      4096 May  4 11:09 zrblog
27
28[root@localhost ~]#  ll /data/mysql/data/oldboy/
29total 77840
30-rw-r-----. 1 mysql mysql       65 Apr 28 22:03 db.opt
31-rw-r-----. 1 mysql mysql     8662 Apr 29 06:18 t100w.frm
32-rw-r-----. 1 mysql mysql 79691776 Apr 29 06:51 t100w.ibd
33

表空间(Tablespace)

共享表空间

需要将所有数据存储到同一个表空间中，管理比较混乱

5.5 版本出现的管理模式，也是默认的管理模式。（数据字典，undo，临时表，索引，表数据）

5.6 版本以，共享表空间保留，只用来存储:数据字典信息,undo,临时表。

5.7 版本,临时表被独立出来了

8.0 版本,undo 也被独立出去了

https://dev.mysql.com/doc/refman/5.6/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/5.8/en/innodb-architecture.html

 1# 共享表空间设置
 2共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)
 3>select @@innodb_data_file_path;
 4>show variables like '%extend%';
 5以下可以加入配置文件中
 6innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
 7innodb_autoextend_increment=64
 8
 9# 表空间查看  1代表独立表空间模式，0代表共享表空间模式
10mysql> select @@innodb_file_per_table;
11+-------------------------+
12| @@innodb_file_per_table |
13+-------------------------+
14|                       1 |
15+-------------------------+
16
17# 设置为共享表空间模式
18mysql> set global innodb_file_per_table=0;

独立表空间

从 5.6，默认表空间不再使用共享表空间，替换为独立表空间。
主要存储的是用户数据
存储特点为：一个表一个 ibd 文件，存储数据行和索引信息
基本表结构元数据存储：
xxx.frm
最终结论：

元数据数据行 + 索引

MySQL 表数据 =（ibdataX+frm）+ibd(段、区、页)
DDL DML+DQL

1# 删除表空间
2alter table city dicard tablespace;
3
4# 导入表空间
5alter table city import tablespace;

MySQL 的存储引擎日志
Redo Log: ib_logfile0 ib_logfile1，重做日志
Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志
临时表:ibtmp1，在做 join union 操作产生临时数据，用完就自动

事务 ACID

影响了 DML 语句（insert update delete 一部分 select）

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

 1# 事务的开始：在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。
 2begin; / start transaction;
 3提交事务：完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。
 4commit;
 5回滚事务：将内存中，已执行过的操作，回滚回去
 6rollback;
 7
 8# 自动提交策略（autocommit） 1代表自动提交 0代表关闭自动提交
 9db01 [(none)]>select @@autocommit;
10db01 [(none)]>set autocommit=0;
11db01 [(none)]>set global autocommit=0;
12自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
13不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能
14(1)
15set autocommit=0;   
16set global autocommit=0;
17(2)
18vim /etc/my.cnf
19autocommit=0  
20
21# 事务的隐式控制
22用于隐式提交的SQL语句
23begin 
24a
25b
26
27导致提交的非事务语句：
28DDL语句： （ALTER、CREATE 和 DROP）
29DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
30锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
31
32导致隐式提交的语句示例：
33TRUNCATE TABLE
34LOAD DATA INFILE
35SELECT FOR UPDATE

InnoDB 事务的 ACID 如何保证

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

redo log

Redo 是什么
redo,顾名思义“重做日志”，是事务日志的一种。

作用是什么
在事务 ACID 过程中，实现的是“D”持久化的作用。对于 AC 也有相应的作用

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

redo buffer
redo 的 buffer:数据页的变化信息 + 数据页当时的 LSN 号
LSN：日志序列号磁盘数据页、内存数据页、redo buffer、redolog

redo 的刷新策略
commit;
刷新当前事务的 redo buffer 到磁盘
还会顺便将一部分 redo buffer 中没有提交的事务日志也刷新到磁盘

MySQL CSR——前滚
MySQL : 在启动时,必须保证 redo 日志文件和数据文件 LSN 必须一致, 如果不一致就会触发 CSR,最终保证一致
情况一:
我们做了一个事务,begin;update;commit.
1.在 begin ,会立即分配一个 TXID=tx_01.
2.update 时,会将需要修改的数据页(dp_01,LSN=101),加载到 data buffer 中
3.DBWR 线程,会进行 dp_01 数据页修改更新,并更新 LSN=102
4.LOGBWR 日志写线程,会将 dp_01 数据页的变化 +LSN+TXID 存储到 redobuffer
5. 执行 commit 时,LGWR 日志写线程会将 redobuffer 信息写入 redolog 日志文件中,基于 WAL 原则,
在日志完全写入磁盘后,commit 命令才执行成功,(会将此日志打上 commit 标记)
6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
7.MySQL 再次重启时,必须要 redolog 和磁盘数据页的 LSN 是一致的.但是,此时 dp_01,TXID=tx_01 磁盘是 LSN=101,dp_01,TXID=tx_01,redolog 中 LSN=102
MySQL 此时无法正常启动,MySQL 触发 CSR.在内存追平 LSN 号,触发 ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和 redolog LSN 一值.这时 MySQL 正长启动
以上的工作过程,我们把它称之为基于 REDO 的"前滚操作

undo log

undo 是什么？

undo,顾名思义“回滚日志”

作用是什么？

在事务 ACID 过程中，实现的是“A” 原子性的作用
另外 CI 也依赖于 Undo
在 rolback 时,将数据恢复到修改之前的状态
在 CSR 实现的是,将 redo 当中记录的未提交的时候进行回滚.
undo 提供快照技术,保存事务修改之前的数据状态.保证了 MVCC,隔离性,mysqldump 的热备

概念性的东西
redo 怎么应用的
undo 怎么应用的
CSR(自动故障恢复)过程
LSN :日志序列号
TXID:事务 ID
CKPT(Checkpoint)

锁
“锁”顾名思义就是锁定的意思。
“锁”的作用是什么？
在事务 ACID 过程中，“锁”和“隔离级别”一起来实现“I”隔离性和"C" 一致性 (redo 也有参与).
悲观锁:行级锁定(行锁)
谁先操作某个数据行,就会持有 < 这行 > 的(X)锁.
乐观锁: 没有锁

隔离级别
影响到数据的读取,默认的级别是 RR 模式.
transaction_isolation 隔离级别(参数)
负责的是,MVCC,读一致性问题
RU : 读未提交,可脏读,一般部议叙出现
RC : 读已提交,可能出现幻读,可以防止脏读.
RR : 可重复读,功能是防止"幻读"现象 ,利用的是 undo 的快照技术 +GAP(间隙锁)+NextLock(下键锁)
SR : 可串行化,可以防止死锁,但是并发事务性能较差
补充: 在 RC 级别下,可以减轻 GAP+NextLock 锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常 select 后添加 for update 语句.但是,请记住执行完一定要 commit 否则容易出现所等待比较严重.
例如:
[world]>select * from city where id=999 for update;
[world]>commit;

作者：Soulboy