存储引擎Innodb

第1章 存储引擎

1.1 存储引擎的介绍

相当于linux文件系统，只不过比文件系统强大简单来说存储引擎是基于表这种级别去设计的，不同的表可以设置不同的存储引擎

第2章 存储引擎的种类

1.1 查看MySQL支持的存储引擎

mysql>show engines;
记住几个存储引擎
innodb======Myisam======csv========memory
5.5版本开始MySQL默认支持的存储殷勤就是InnoDB

第3章 第三方存储引擎

1.1 RocksDB MyRocks TokuDB

RocksDB MyRocks TokuDB
这三种相对于InnoDB来说，压缩比高，插入性能好，其它的和InnoDB没什么差别

第4章 存储引擎的查看及设置

1.1 查看存储引擎

mysql>show engines;

1.2 查看系统默认的存储引擎

mysql>select @@default_storage_engines;

1.3 存储引擎的修改

1.1 永久修改（改配置文件)

vim /etc/my.cnf
#服务端
[mysqld]
default_storage_engine=innodb
说明:在服务端加入参数

1.2 临时修改(会话级别的修改)

mysql>set default_storage_engine=myisam

1.3 全局修改(断开会话，重连后生效)

mysql>set global default_storage_engine=innodb
说明:工作中不要修改

第5章 查看表的存储引擎状态

1.1 第一种方法

mysql> show create table t1;

1.2 第二种方法

mysql>show table status like '表名'G

1.3 第三种方法

select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');

第6章 修改表的存储引擎及整理碎片功能

1.1 修改表的存储引擎

mysql>alter table 表名 engine=myisam

1.2 整理碎片的功能

mysql> alter table 表名 engine=innodb;
==========这个语句同时兼备2个功能============
1.修改存储引擎              
2.整理碎片
=======================================
#说明
这条命令整理碎片只适用于innodb引擎，其它的存储引擎不能用

第7章 批量替换单库下表的存储引擎

1.1 批量替换zabbix 100张 innodb为tokudb

替换思路:indormation_schema
select concat("alter table ",table_schema,".",table_name,"  engine=tokudb;") from
information_schema.tables where table_schema='zabbix';

第8章 InnoDB存储引擎物理存储结构

1.1 InnoDB和MyISAM区别

1. Innodb支持事务，MyISAM不支持
2. Innodb支持行级锁，MyISAM支持表级锁
3. Innodb支持MVCC(多版本并发控制)，MyISAM不支持
4. Innodb支持ACSR(自动故障恢复)，MyISAM不支持
5. Innodb支持热备份，MyISAM支持温备份

第9章 表空间

1.1 共享表空间

5.5版本默认的模式，5.6版本开始替换为独立表空间模式
替换原因:
共享表空间是把所有的数据都存入在一个文件内，管理起来很混乱
表空间模式的演变过程:
5.5版本出现的管理模式,也是默认的管理模式
5.6版本共享表空间保留，但是只用来存储:数据字典信息+undo+临时表
5.7版本把临时表独立出来
8.0版本把undo独立出来

1.2 共享表空间的设置

#说明
设置共享表空间模式要在搭建MySQL时，初始化数据之前把参数加入配置文件中
======================================================================
mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
数据库第一次启动就会有一个ibdata1这个文件，大小为12M,并且会自动扩展，每次自动扩展64M
如果要设置在服务端加入参数
innodb_data_file_path=ibdata1:512M;ibdata2:512M:autoextend
说明:如果数据库已经搭建起来去设置的时候ibdata1的大小为此时数据库的实际大小
解释:设置2个共享表空间文件(大小可以自己设置),大小分别为512M 并且以自动扩展的方式(2个都满了)
innodb_autoextend_increment=64
解释:每次自动扩展的大小为64M
=============说明=============
一定要在初始化数据之前设置

1.3 独立表空间

从5.6版本开始，默认表空间不再使用共享表空间，替换为独立表空间
存储特点:一张表一个ibd文件，存储数据行和索引信息
得出结论:
一张InnoDB表=ibd+frm+ibdataX
frm:存储的是表结构信息

1.4 独立表空间设置

mysql> select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
1：表示开启
0：表示关闭

1.5 InnoDB相关事务日志(redo log)

redo log: ib_logfile0  ib_logfile1               重做日志
undo log: ibdata1 ibdata2(存储在共享表空间中)      回滚日志

1.6 临时表:ibtmp1

在做join union操作产生临时数据，用完就自动清理

第10章 独立表空间迁移命令

1.1 2个迁移需要的命令

mysql>alter table 表名 discard tablespace;
mysql>alter table 表名 import tablespace;

1.2 迁移步骤

第一个里程:
创建一个和原来一模一样的表(拿到建表语句)
第二个里层:
将创建的新表的ibd文件删除
mysql>alter table 表名 discard tablespace;
第三个里程:
将原表的ibd文件拷贝过来，并且修改权限为mysql:mysql
第四个里程:
将原表的ibd文件导入
mysql>alter table 表名 import tablespace

1.3 真实案例

硬件及软件环境:
联想服务器（IBM） 
磁盘500G 没有raid
centos 6.8
mysql 5.6.33  innodb引擎  独立表空间
备份没有，日志也没开

为什么没有备份和日志没有开？
因为是开发用户专用库:
jira(bug追踪) 、 confluence(内部知识库)    ------>LNMT

因为是开发库，所以开发人员自己在维护，有一天，突然断电了，数据库起不来，开发人员处理了2天还没有
处理好，最后就把这个问题丢给我了
我接手以后，已经是下面这种情况了
故障
1.断电了，第二天启动,"根文件系统只读,建议使用fsck命令修复文件系统"
2.拿到手的操作系统起来了,但是数据库启动不了
3.检查问题,jira库目录丢失,只剩下confluence

我拿到以后用dd命令把那块有问题的盘插在新的机器上面，同时用一块新的盘
用dd[dd if=/dev/sda(有问题的盘) of=/dev/sdb(新盘)]命令把那块有问题的盘上的数据镜像到新盘上


处理问题思路:
confulence库中一共有107张表。
1、创建107张和原来一模一样的表。
他有2016年的历史库，我让他去他同时电脑上 mysqldump备份confulence库
mysqldump -uroot -ppassw0rd -B  confulence --no-data >test.sql
拿到你的测试库，进行恢复
到这步为止，表结构有了。
2、批量表空间删除并导出
select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
source /tmp/discard.sql
执行过程中发现，有20-30个表无法成功。主外键关系
很绝望，一个表一个表分析表结构，很痛苦。
set foreign_key_checks=0 跳过外键检查。
把有问题的表表空间也删掉了。
3、批量拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中
select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/import.sql';
4、验证数据
表都可以访问了，数据挽回到了出现问题时刻的状态

=====================================
模仿恢复:
准备环境
1.导入数据
[root@db01 ~]# mysql -uroot -p <./t100w.sql 

第一步:把t100w的ibd和frm拷贝到另一台机器上去
  [root@db01 /data/mysql/data/test]# cp -a t100w.* /data/3307/oldguo
第二步:在3307这台机器上登录数据库并查看数据
  mysql -S /data/3307/mysql.sock
  查看数据发现可以看到t100w这一张表在数据库中
  但是select * from t100w时会报错
  报错原因:一张innodb表=ibd+frm+ibdata(元数据信息)，此时并没有元数据
  思考:怎么样获取元数据？
  拿到原来的建表语句，重新建一张表，这样就能够保持ibd+frm+ibdata都有了
  此时，表里面是没有数据的
  我们要把新建表oldguo的ibd删除，并把原表(t100w)的ibd 信息拷贝过来，并改权限为mysql:mysql
  要用数据库专用的命名删除新建表的ibd
  alter table oldguo discard tablespace;
  拷贝原表ibd过来
  [root@db01 /data/mysql/data/test]# cp -a t100w.ibd /data/3307/data/oldguo/
  在3307这台机器上修改权限
  chown -R mysql:mysql /data/3307/data/oldguo
第三步：把原表的ibd导入进去
数据库专用命令
alter table t100w import tablespace;
此时在去查看数据，可以看到数据了

第11章 事务

1.1 什么是事务

所有语句作为一个单元全部成功执行或全部取消，不能出现中间状态

第12章 事务的ACID特性

1.1 A(atomic)原子性

所有语句作为一个单元全部成功执行或全部取消，不能出现中间状态

1.2 C(consistent):一致性

如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态
通俗的讲:
A给B发一个50元的红包，整个过程下来，A少了50，B多了50。不能出现别的情况

1.3 I(Isolated):隔离性

事务之间不相互影响

1.4 D(durable):持久性

事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失

第13章 事务的生命周期(事务的控制语句)

1.1 事务的开启:begin

在5.5 以上的版本，不需要手工begin，只要执行的是一个DML语句，会自动在前面加一个begin命令

1.2 标准的事务语句

DML语句：update、delete、insert
mysql> use world;(切换数据库不是事务语句)
mysql> update city set countrycode='CHN' where id=1;
mysql> update city set countrycode='CHN' where id=2;
mysql> update city set countrycode='CHN' where id=3;

1.3 事务的结束:commit或rollback

commit：提交事务
完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。
rollback ：回滚事务
将内存中，已执行过的操作，回滚回去

第14章 自动提交机制

1.1 参数(autocommit)说明

查看自动提交机制是否开启
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
0:表示自动提交机制关闭了
1:表示自动机制是开启的

1.2 修改自动提交机制的方法

第一种:永久修改(改配置文件)
vim /etc/my.cnf
autocommit=0
在服务端加入此参数
第二种:当前会话窗口修改
mysql>set commit=0
只对当前会话生效
第三种:全局修改
mysql>set golbal commit=0;
断开窗口重连后生效,影响到所有新开的会话
##说明：
自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能
由于mysql目前的特性，在执行DML语句时，会自动的加上一个begin,并且会自动的提交！而且这种提交是一条一条的提交
这种机制虽然方便，但是，当我们要把多个语句作为一个事务时，这种就不合适了，所以我们平时要把这种自动提交机制给关掉

第15章 隐式提交的情况

1.1 导致提交的非实物语句

DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）

1.2 导致隐式提交的语句示例

truncate table
locd data infile
select for update
####说明######
在执行事务语句的时候，在同一个窗口不要有别的操作！

第16章 InnoDB 事务的ACID如何保证

1.1 名词介绍

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

1.2 redo是什么

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

1.3 redo作用

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

1.4 redo日志位置

redo的日志文件：iblogfile0 iblogfile1

1.5 redo buffer

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

1.6 redo的刷新策略

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

1.7 undo

作用: 在 ACID特性中,主要保证A的特性,同时对CI也有一定功效
(1)记录了数据修改之前的状态
(2)rollback 将内存的数据修改恢复到修改之前
(3)在CSR中实现未提交数据的回滚操作
(4)实现一致性快照,配合隔离级别保证MVCC,读和写的操作不会互相阻塞

第17章 redo log功能

1.1 前滚过程实现

第一情况:已经commit的情况

1.我们执行一个事务begin===update====》commit
2.当我们在begin时，会立即分配一个事务号TXID=01
3.update时，会将需要修改的数据页(A=1,LSN=101)加载到内存中及data buffer pool中
4.此时DBWR线程，会将数据页进行修改，并跟新LSN=102
5.LOGBWR日志写线程，会将数据页的变化+LSN=102+TXID号存到redo buffer中
6.执行commit时，会将redo buffer中存储的信息写到redo log中（基于WAL原则），在日志写入到磁盘后，commit命令才算执行完成(并在此日志上打上一个commit的标记)
7.此时如果宕机了，内存中的脏页还没有来得及写入磁盘中，内存中数据全部消失
8.当MySQL再次启动时，会去检查磁盘数据页的LSN号和redo log中的LSN号，而此时磁盘上的LSN=101，而redo log中的LSN号为102
9.MySQL此时无法正常启动,会立即触发ACSR(自动故障恢复)，加载磁盘上的数据页到内存，redo log中的日志也加载到redo buffer中。2者一起恢复脏页，并立即触发CKPT，将脏页刷写到磁盘，此时保证了磁盘上的LSN号和redo log中的LSN号一致，MySQL就可以正常启动了
10.以上的整个过程就是”前滚”的过程

1.2 第二种情况:未commit的情况（分为2种情况）

1.2.1 回滚过程的实现

1.我们执行一个事务begin===update====》commit
2.当我们在begin时，会立即分配一个事务号TXID=01
3.update时，会将需要修改的数据页(A=1,LSN=101)加载到内存中及data buffer pool中
4.此时DBWR线程，会将数据页进行修改，并跟新LSN=102
5.LOGBWR日志写线程，会将数据页的变化+LSN=102+TXID号存到redo buffer中
6.执行rollback时，innodb会拿着TXID号到undo log中，把数据页的原始状态加载到内存(因为此时内存中的数据已经发生了变化)，把变化的数据页恢复到原始状态。
7.以上的操作就是“回滚”过程的实现

1.2.2 第1种情况：宕机和断电时刻

1.  我们执行一个事务begin===update====》commit
2.	当我们在begin时，会立即分配一个事务号TXID=01
3.	update时，会将需要修改的数据页(A=1,LSN=101)加载到内存中及data buffer pool中
4.	此时DBWR线程，会将数据页进行修改，并跟新LSN=102
5.	LOGBWR日志写线程，会将数据页的变化+LSN=102+TXID号存到redo buffer中
6.  还未执行commit，此时突然宕机了，内存中的数据消失
7.  当再次启动MySQL时，发现磁盘上数据页的LSN号和redo log中的LSN号一致，并且发现redo log中还有TXID=0(上一个事务号)，当扫描到undo log中时发现有一个TXID=01的事务，这就说明TXID=01这个事务根本就没有提交，此时不需要做前滚和回滚的操作

1.2.3 第二种情况:被其它事务顺带提交

1.我们执行一个事务begin===》update====》commit
2.当我们在begin时，会立即分配一个事务号TXID=01
3.update时，会将需要修改的数据页(A=1,LSN=101)加载到内存中及data buffer pool中
4.此时DBWR线程，会将数据页进行修改，并跟新LSN=102
5.LOGBWR日志写线程，会将数据页的变化+LSN=102+TXID号存到redo buffer中
6.并未执行commit，但是被其它事务顺带提交了，并且打上了未commit的标记，此时突然宕机了
7.当再次启动MySQL时，发现磁盘上数据页的LSN号和redo log中的LSN号对不上
8.此时立即触发ACSR，进行前滚操作，恢复到数据库宕机时的内存状态，但是发现TXID=01这个事务并未提交(因为打上了未提交的标记),
9.这个时候会立即触发ACSR的第二个阶段,innodb拿着TXID号到undo log中，把事务修改之前的状态加载到内存，进行回滚的操作。回滚完成后，最终保证了LSN号的一致，MySQL就能正常启动了

第18章 隔离性的保证

1.1 锁

锁粒度 : 行级锁定,表级锁,数据字典锁,GAP(间隙)锁,Next-Lock下一键锁

1.2 隔离级别

RU(read uncommitted): 读未提交的数据.可脏读 
RC(read committed)： 读已提交的数据,可出现幻读和不可重复度,可以防止脏读.
RR(reoeatable-read):   可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
SR: 可串行化,可以防止死锁,但是并发事务性能较差
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.

1.3 默认级别的设置

默认模式
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
如果要修改在配置文件中加入需要改成什么级别即可：
transaction_isolation=read-uncommitted
transaction_isolation=read-committed
transaction_isolation=repeatable-read

1.4 RU（read-uncommitted)

例子解释：取款的时候，还没有点确认取款，但是查余额时，发现已经少了！所以这种模式一般不用

1.5 RC（read-committed）

例子解释：
这种隔离级别可以用于普通的互联网公司。但是不能用于金融类的公司
例如银行，当有一个大查询的时候，可能需要2~3个小时，这期间数据可能会变很多次。所以查询的结果不知道以哪个为准，所以这种模式不能用于金融类的公司
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般在为了读一致性会在正常select后添加for update语句.但是,请记住执行完一定要commit 否则容易出现所等待比较严重.

1.6 RR(repeatable-read)

RR 通过MVCC基础解决了不可重复读,但是有可能会出现幻读现象
在RR模式下,GAP和Next-lock进行避免幻读现象,必须在查询的条件列上有索引，没有索引也会出现幻读
通俗点讲就是：我们打开一个窗口查询时，里面的表的数据会永远保持此时的状态，不管在别的窗口怎么改，在此窗口读到的还是我们打开窗口时的状态！
这种模式就是利用undo快照技术+GAP(间隙锁）+nextlock(下一键锁）（MVCC实际上就是在每一个会话开启时都会生成一个快照），将来在读取的时候，在别的会话不管怎么改，读取的都是起始时候的数据

1.7 MVCC

1.每次开启一个事务恢复,都会利用UNDO技术,生成一个一致性快照,直到事务结束.
2.通过MVCC,可以实现一致性快照读,在RR级别实现可重复读功能
3.在RC级别下,应用的是当前快照读技术,读取的都是当前时间点的最新快照.

第19章 存储引擎核心参数innodb_flush_log_at_trx_commit

1.1"双一" 标准的其中一个 （十分重要）

在事务提交时，控制redo buffer往磁盘上刷写的策略
默认是:
mysql> select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
可以分为0、1、2三种情况

1.2 MySQL刷写动作

1.3 当innodb_flush_log_at_trx_commit=1时

每次事物的提交都会引起日志文件写入、flush磁盘的操作，确保了事务的ACID；flush 到操作系统的文件系统缓存  fsync到物理磁盘.

1.4 当innodb_flush_log_at_trx_commit=0 时

表示事务提交时。不立即做日志写入操作，而是每秒钟将redo log buffer中的数据写入到文件系统缓存，并flush磁盘一次
这种一定程度提高了性能（相当于是把事务攒在一起，减少IO）但是有可能会丢失数据

1 .5 当innodb_flush_log_at_trx_commit=2时

表示每次事务提交引起写入日志文件的动作，但每秒钟完成一次flush磁盘的操作（也有可能丢失数据）

1.6什么情况下使用1和什么情况下使用0

在追求性能的情况下使用0（数据不是那么重要！例如:ZABBIX)
在追求安全的情况下使用1

第20章 存储引擎核心参数-----innodb_flush_method

1.1作用

控制的是,log buffer 和data buffer,刷写磁盘的时候是否经过文件系统缓存

1.2 innodb_flush_method的三种模式

fsync    :  日志和数据缓冲区写磁盘,都走OS buffer
O_DIRECT :  数据缓冲区写磁盘,不走OS buffer(建议模式)
O_DSYNC  :  日志缓冲区写磁盘,不走 OS buffer

1.3 fsync模式

在这种模式下：redo buffer 和data buffer pool 都要经过2步
第一步：先写入OS buffer
第二步：在写入 disk

1.4 O_DIRECT模式(建议模式)

此种模式下：redo buffer 还是要经过2步
第一步：先写入OS buffer
第二步：在写入 disk
但是data buffer pool 是直接跳过OS BUFFER了直接写到磁盘了

1.5 O_DSYNC模式

这种模式下：
redo buffer 会直接写入磁盘
data buffer pool 则会经过2步 
这种模式redo buffer里的日志信息可能会丢失。不能恢复数据了

第21章 模式的选择

1.1 最高安全模式

innodb_flush_log_trx_commit=1
Innodb_flush_method=O_DIRECT
（这种组合模式就是保证了日志是确定写入了磁盘，虽然data buffer pool直接跳过了OS buffer有可能会丢失数据。但是可以通过redo log 和CSR一起恢复脏页原数据，所以这种模式是最安全的

1.2 最高性能模式

innodb_flush_log_trx_commit=0
Innodb_flush_method=FSYNC

第22章 脏页的刷写策略

innodb_max_dirty_pages_pct=75
还有哪些机制会触发写磁盘?
CSR 
redo满了（为什么可以不先把脏页写入磁盘，因为有redo buffer。如果redo buffer满了。不得不把脏页写入磁盘）

第23章 redo日志设置有关的

innodb_log_buffer_size=16777216
innodb_log_file_size=50331648
innodb_log_files_in_group = 3