5、存储引擎
1 简介
相当于Linux文件系统，是在Linux文件系统之上的一层结构
2 提供的功能
数据读写
数据安全和一致性
提高性能
热备份
自动故障恢复
高可用方面支持
3 存储引擎种类
3.1 Oracle Mysql支持的存储引擎
Innodb
Myisam
Memory：直接存在内存中，不存在磁盘上，information_shcema库里的视图是这种类型
Archive
Federated
Example
Blackhole：使用这种存储引擎，数据会直接丢弃，但是会产生日志
Merge
Ndbcluster
CSV
3.2 第三方数据库的存储引擎
PerconaDB：默认是XtraDB
MariaDB：默认是InnoDB
PerconaDB、MariaDB还支持：
TokuDB、RocksDB、MyRocks，这三种存储引擎压缩比较高，数据插入性能极高。因为这些功能特性，很多NewSQL，使用比较多。
3.3 查看存储引擎信息
查看全部的存储引擎
show engines;
查看有没有某种存储引擎的表
#以CSV为例
select table_schema,table_name,engine from information_schema.tables where engine='CSV';
4 InnoDB存储引擎介绍
在MySQL 5.5版本之后，默认的存储引擎，提供高可靠性和高性能。
4.1 优点
支持事务（遵从ACID）
MVCC（Multi-Version Concurrency Control，多版本并发控制）
InnoDB 行级锁
Orcale 一致性非锁定读取
聚集索引组织表
支持外键，保证多表的数据一致性
支持ACSR（Auto Crash Safety Recovery）自动故障恢复
支持热备
5 存储引擎查看
5.1 select查看默认存储引擎
查看默认的存储引擎
select @@default_storage_engine;
5.2 设定默认存储引擎
会话级别
set default_storage_engine=myisam;
全局级别(仅影响新会话)
set global default_storage_engine=myisam;
以上设定，重启后会失效
5.3 show确认每个表的存储引擎
看特定库的表的存储引擎
show table status from 库名;
看所有表的存储引擎
select table_schema,table_name,engine from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys');
5.4 修改表的存储引擎
修改单表，使存储引擎更改为InnoDB还会进行表的碎片整理，经常使用!
alter table 表名 engine=innodb;
批量修改某库下的所有表
select concat('alter table ',table_schema,'.',table_name,' engine=Innodb')from information_schema.tables where table_schema='库名';
6 InnoDB存储引擎物理结构
6.1 最直观的存储方式
ibdata1：系统数据字典信息（整个数据库的元数据信息），UNDO表空间等数据
ib_logfile0~ib_logfile1：REDO日志文件，事务日志文件。
ibtmp1：临时表空间磁盘位置，存储临时表
frm：存储表的列信息
ibd：表的数据行和索引
6.2 表空间（Tablespace）
6.2.1 共享表空间
将所有数据存储到同一个表空间中，管理比较混乱
5.5版本出现的管理模式，也是默认的管理模式
5.6版本，共享表空间保留，只用来存储系统数据字典信息、undo、临时表。
5.7版本，用来存储系统数据字典信息、undo
8.0版本，用来存储系统数据字典信息
6.2.2 共享表空间设置
ibdata1是共享表空间文件，初始化是12M，默认以64M的方式增长
查看共享表空间信息
# 查看初始大小
[(none)]>select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend |
+-------------------------+
# 查看自动增长值
[(none)]>select @@innodb_autoextend_increment;
+-------------------------------+
| @@innodb_autoextend_increment |
+-------------------------------+
| 64 |
+-------------------------------+
共享表空间设置（在搭建MySQL时，初始化数据之前设置到参数文件中）
vim /etc/my.cnf
....
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
innodb_autoextend_increment=64
6.2.3 独立表空间
从5.6版本以后，默认表空间不再使用共享表空间，替换为独立表空间。
表空间只存储用户数据，实际上是ibd文件。
存储特点为：一个表一个ibd文件，存储数据行和索引信息
基本表结构：frm文件，存储列的信息
最终Mysql数据表的结构如下：
mysql表数据=(ibdataX+frm)+ibd(段、区、页)
ibdataX-->表的元数据
frm-->表的列信息
ibd-->数据行+索引
MySQL的存储引擎日志：
Redo log：ib_logfile0、ib_logfile1，重做日志
Undo log：ibdata1 ibdata2（存储在共享表空间中），回滚日志
临时表：ibtmp1，在做join union操作产生临时数据，用完自动释放
6.2.4 独立表空间设置
查看默认表空间模式
[(none)]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
| 1 |
+-------------------------+
设置全局默认表空间模式（仅影响新会话）
set global innodb_file_per_table=0;
6.2.5 独立表空间的删除和导入
删除，仅删除ibd文件，表的列信息和元数据保留
alter table 表名 discard tablespace;
导入表空间，需要先将ibd文件拷贝到库文件夹下
alter table 表名 import tablespace;
7 事务的ACID特性
Innodb存储引擎才支持事务
Atomic（原子性）
所有语句作为一个单元全部成功执行或全部取消，不能出现中间态。
Consistent（一致性）
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态。例如MVCC的读一致性。
Isolated（隔离性）
事务之间不互相影响。依靠锁机制。
Durable（持久性）
事务完成后，所做的所有更改都会准确的地记录在数据库中。所有更改不会丢失。
8 事务的生命周期（事务控制语句）
8.1 事务的开始
begin
在5.5版本之后，不需要手工begin，只需要执行一个DML，会自动在前面加一个begin命令。
8.2 事务的结束
commit：提交事务
完成一个事务，一旦事务提交成功，就说明具备ACID特性了。
rollback：回滚事务
将内存中，已执行过的操作，回滚回去。
8.3 自动提交策略（autocommit）
默认自动提交是打开的
[(none)]>select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
临时关闭autocommit，仅当前会话生效
set autocommit=0;
临时全局关闭autocommit，全局新会话生效
set global autocommit=0;
永久生效需要写到my.cnf配置文件中
[mysqld]
...
autocommit=0
注：
自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，一般建议设置为0，可以很大程度上提高数据库性能
8.4 隐式提交语句
1.begin后，再次输入begin
begin
a
b
begin
2.set语句
3.DDL语句：（alter、create、drop）
4.DCL语句：（grant、revoke、set password）
5.锁定语句：（lock tables、unlock tables）
6.truncate table
7.load data infile
8.select for update
8.5 开始事务流程
1.检查autocommit是否为关闭状态
select @@autocommit;
2.开启事务，并结束事务
begin;
delete from city where id<5;
rollback;
begin;
delete from city where id<5;
commit;
9 InnoDB 事务的ACID
9.1 一些概念
redo log：重做日志 ib_logfile0~1，50M，轮询使用
redo log buffer：redo内存区域
ibd：存储数据行和索引
buffer pool：缓冲区池，数据和索引的缓冲
LSN：日志序列号
WAL：write ahead log 日志优先写的方式实现持久化
脏页：内存脏页，内存中发生了修改，没写入到磁盘之前，我们把内存页称之为脏页
CKPT：Checkpoint，检查点，将脏页刷写到磁盘的动作
TXID：事务号，InnoDB会为每一个事务生成一个事务号，伴随整个事务。
MySQL每次启动数据库，都会比较磁盘数据页和redo log的LSN，必须要求两者LSN一致数据库才能正常启动
9.2 redo log
9.2.1 redo是什么？
redo，重做日志，是事务日志的一种
9.2.2 作用是什么？
在事务ACID过程中，实现的是"D"持久化的作用，对于AC也有想用的作用
9.2.3 redo日志位置
redo的日志文件：ib_logfile0、ib_logfile1
9.2.4 redo buffer
redo的buffer：数据页的变化信息+数据页当时的LSN号
LSN：日志序列号，在磁盘数据页、内存数据页、redo buffer、redolog中监控
9.2.5 redo刷新策略
commit;
刷新当前事务的redo buffer到磁盘，还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘
9.2.6 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存储到redo buffer
5.执行commit时,LGWR日志写线程会将redo buffer信息写入redo log日志文件中,基于WAL原则,在日志完全写入磁盘后,commit命令才执行成功,(会将此日志打上commit标记)
6.假如此时宕机,内存脏页没有来得及写入磁盘,内存数据全部丢失
7.MySQL再次重启时,必须要redo log和磁盘数据页的LSN是一致的.但是,此时dp_01,TXID=tx_01磁盘中LSN=101,dp_01,TXID=tx_01,redo log中LSN=102。MySQL此时无法正常启动,MySQL触发CSR。在内存追平LSN号,触发ckpt,将内存数据页更新到磁盘,从而保证磁盘数据页和redo log LSN一致。这时MySQL正常启动以上的工作过程,我们把它称之为基于REDO的"前滚操作"。
9.3 undo 回滚日志
9.3.1 undo是什么？
undo，回滚日志
9.3.2 作用是什么？
在事务ACID过程中，实现的是"A"原子性的作用，另外CI也依赖于Undo
在rollback时，将数据恢复到修改之前的状态
在CSR实现时，将redo当中记录的未提交的事务进行回滚
undo提供快照技术，保存事务修改之前的数据状态，保证了MVCC，隔离性，mysqldump的热备
9.4 锁
在事务ACID过程中，"锁"和"隔离级别"一起来实现"I"隔离性和"C"一致性（redo也有参与）
悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它解锁。传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。就像for update，再比如Java里面的同步原语synchronized关键字的实现也是悲观锁。
乐观锁：顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。
9.5 隔离级别
影响到数据的读取，负责MVCC读一致性问题，默认是RR(REPEATABLE-READ)模式
RU : 读未提交,可脏读,一般部议叙出现
RC : 读已提交,可能出现幻读,可以防止脏读.
RR : 可重复读,功能是防止"幻读"现象 ,利用的是undo的快照技术+GAP(间隙锁)+NextLock(下键锁)
SR : 可串行化,可以防止死锁,但是并发事务性能较差
补充: 在RC级别下,可以减轻GAP+NextLock锁的问题,但是会出现幻读现象,一般为了读一致性会在正常select后添加for update语句，此时select语句产生行锁。但是，请记住执行完一定要commit否则锁等待比较严重。
参数是transaction_isolation
[(none)]>select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
10 InnoDB存储引擎核心特性-参数补充
10.1 存储引擎相关
10.1.1 查看存储引擎
show engines;
show variables like "default_storage_engine";
select @@default_storage_engine;
10.1.2 指定和修改存储引擎
(1)通过参数设置默认存储引擎
(2)建表的时候进行设置
(3)alter table t1 engine=innodb;
10.2 表空间
10.2.1 共享表空间
innodb_data_file_path，共享表空间大小、自动增长等属性
一般是在初始化数据之前就设置好
例子：
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend;
10.2.2 独立表空间
innodb_file_per_table=1，表示使用独立表空间
10.3 缓冲区池
10.3.1 查询缓冲区大小
select @@innodb_buffer_pool_size;
show engine innodb status;
innodb_buffer_pool_size 一般建议是物理内存的75%-80%，因为操作系统还需使用内存。
10.4 innodb_flush_log_at_trx_commit (双1标准之1)
10.4.1 作用
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0、1、2三个。
10.4.2 查询
select @@innodb_flush_log_at_trx_commit;
10.4.3 参数说明
(1)innodb_flush_log_at_trx_commit=1：
在每次事务提交时，会进行日志文件flush到文件系统缓存，fsync到物理磁盘操作，确保了事务的ACID。
(2)innodb_flush_log_at_trx_commit=0：
每次事务提交时，不做日志写入，而是每秒钟将log buffer中的数据写入文件系统缓存并且每秒fsync到物理磁盘一次；
(3)innodb_flush_log_at_trx_commit=2：
每次事务提交时，将日志写入文件系统缓存，但不是立即fsync到物理磁盘，而是每秒钟fsync一次。
10.5 Innodb_flush_method=(O_DIRECT, fsync)
10.5.1 作用
控制buffer pool和log buffer刷写磁盘时，是否经过文件系统缓存
10.5.2 查看
show variables like "%log_at%";
10.5.3 参数说明
O_DIRECT:
buffer pool写磁盘,不走OS buffer
fsync:
buffer pool和log buffer写磁盘,都走OS buffer，性能最好
O_DSYNC:
log buffer写磁盘,不走 OS buffer
10.5.4 使用建议
配合innodb_flush_log_at_trx_commit来使用
(1)最高安全
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT
(2)最高性能
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync
10.6 redo日志有关参数
innodb_log_buffer_size=16777216
innodb_log_file_size=50331648
innodb_log_files_in_group = 3
