存储引擎

简介：相当于linux文件系统，只不过此文件系统强大
功能：数据读写，提高性能，数据安全，热备份，自动恢复故障，高可用方面支持等

存储引擎是作用在表上的，也就意味着，不同的表可以使用不同的存储引擎类型
MySQL存储引擎类型
  innodb,myisam,memory,csv，默认是innodb
  PerconaDB：默认是XtraDB
  MAriaDB：默认是innodb
其他的存储引擎支持
  TokuDB
  RocksDB
  MyRocks
以上三种存储引擎的共同点：压缩比较高，数据插入性能极高

innodb和myisam区别

innodb:支持事务，行级锁，支持csr自动故障恢复，支持热备（备份和恢复），MVCC多版本并发控制
myisam:不支持事务，温备，不支持csr
innodb属于mysql5.5版本开始默认的，提供可靠性和高性能
优点：
事务（Transaction）
mvcc（Multi-Version Concurrency Control多版本并发控制）
行级锁（Row-level Lock）
ACSR(Auto Crash Safey Recovery)自动故障安全恢复
支持热备份（Hot Backup）
Replication：Group commit，GTID（Global Thransaction ID），多线程（Multi-Threads-SQL）

查看

查看默认存储引擎
3306 [(none)]>select @@default_storage_engine;
 +--------------------------+
 | @@default_storage_engine |
 +--------------------------+
 | InnoDB                   |
 +--------------------------+
 1 row in set (0.00 sec)
永久修改
vim /etc/my.cnf
default_storage_engine=myisam 
查看库中所有表的引擎
3306 [(none)]>show table status from world;
修改一个表的引擎
3306 [(none)]>alter table world.a engine=myisam;
 Query OK, 9 rows affected (0.32 sec)
 Records: 9  Duplicates: 0  Warnings: 0

生成修改所有表的引擎的语句
3306 [information_schema]>select concat('alter table ',table_schema,'.',table_name,' ','engine=innodb') from
information_schema.tables into outfile '/tmp/engine.sh';

Innodb存储引擎的物理存储结构

ibdata1：系统数据字典信息，undo表空间数据
ib_logfile0~1：redo日志文件，事务日志文件
ibtmp1：临时表空间磁盘位置，存储临时表
frm：存储表的列信息
ibd：表的数据行和索引

共享表空间

共享表空间：5.5版本推出，也是默认管理模式
需要将所有数据存储到同一个表空间中，管理表复杂
5.6版本以后，共享表空间保留，只用来存储数据字典信息，undo，临时表
5.7版本，临时表被独立出来
8.0版本，undo也被独立出来

设置：共享表空间设置（在搭建mysql时，初始化之前设置带参数文件中）
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
查看当前设置
3306 [information_schema]>select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)
3306 [information_schema]>show variables like '%extend%';
 +-----------------------------+-------+
 | Variable_name               | Value |
 +-----------------------------+-------+
 | innodb_autoextend_increment | 64    |
 +-----------------------------+-------+
 1 row in set (0.00 sec)

独立表空间

从5.6开始默认表空间不在使用共享表空间，替换为独立表空间
主要存储的是用户数据
存储特点为：一个表一个ibd文件，存储数据行和索引信息
Redo Log：ib_logfile0  ib_logfile1      redo日志
Undo Log：ibdata1  ibdata2  回滚日志
ibtmp1临时表，在做join  union操作时产生临时数据存储在这里

设置
3306 [information_schema]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)
alter table 库名 dicard tablespace; 删除表空间  alter table 库名 import tablespace; 重新加载表空间

事务的ACID特性

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

Consistent（一致性）
如果数据库在事务开始时处于一致状态，则在执行该事务期间将保留一致状态

Lsolated（隔离性）
事务之间不相互影响

Durable（持久性）
事务成功完成后，所做的所有更改都会准确的记录在数据库中，所做的更改不会丢失

事务的生命周期

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

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

自动提交策略（autocommit）

查看状态
3306 [(none)]>select @@autocommit;
 +--------------+
 | @@autocommit |
 +--------------+
 |            1 |
 +--------------+
 1 row in set (0.00 sec)
3306 [(none)]>set autocommit=0;         设置自动提交关闭
Query OK, 0 rows affected (0.00 sec)

3306 [(none)]>set global autocommit=0;  全局设定
Query OK, 0 rows affected (0.00 sec)

3306 [(none)]>select @@autocommit;      查看
 +--------------+
 | @@autocommit |
 +--------------+
 |            0 |
 +--------------+
 1 row in set (0.00 sec)

注：自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能
也可以在配置文件中设置
vim /etc/my.cnf
autocommit=0

隐式提交

导致提交的非事务语句：
DDL语句：ALTER,CREATE,DROP
DCL语句：GRANT,REVOKE,SET PASSWORD
锁定语句：LOCK TABLES , UNLOCK TABLES
我个人理解非事务语句是，DDL,DCL语句，要么成功，要么失败，没有中间型，所以说不具备事务

InnoDB事务的ACID如何保证

ib_logfile：redo日志文件
redo buffer：还没刷写到ib_logfile中在内存中的redo日志
ibd：数据文件
page buffer cache：内存中的数据
ibdata：undo日志文件
undo buffer：没刷写到ibdata文件中的在内存中的undo日志

redo log

redo是什么
重做日志，是事务日志的一种，是innodb引擎CSR前滚的依据

作用是什么
在事务ACID过程中，实现的是"D"持久化的作用，对于"A和C"也有相应作用

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（事务ID）=tx_01
2.update时，会将需要修改的数据页（dp_01,LSN=101）,加载到data buffer中
3.DBWR线程，会进行dp_01数据页修改更新，并更新LSN=102
4.LOGWR日志写线程，会将dp_01数据页的变化+LSN+TXID存储到redo buffer中
5.执行commit时，LOGWR日志写线程会将redo buffer信息写入redolog日志文件中，基于WAL原则，在日志完全写入磁盘后，commit命令才执行成功（会将此日志打上commit标记）
6.如果此时宕机，内存脏页没有来得及写入磁盘，内存数据全丢失
7.MySQL再次重启时，必须要redolog和磁盘数据页的LSN是一致的，但是此时dp_01，TXID=tx_01磁盘是LSN=101，redolog中LSN是102，MySQL此时无法正常启动，MySQL触发CSR，在内存中追平LSN号，触发ckpt，将内存数据页更新到磁盘，从而保证磁盘数据页和redolog LSN一致，这时MySQL正常启动
以上的工作过程，我们把它称之为基于redo的"'前滚操作"

undo 回滚日志

undo是什么
回滚日志|

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

锁

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

隔离级别

影响到数据的读取，默认的级别是RR模式
transaction_isolation   隔离级别参数
负责的是，MVCC，读一致性问题

RU（读未提交）：会出现脏读，不可重复度，幻读
客户端A设置为RU模式，在A的事务提交之前，打开另一个客户端B，更新表，这时，客户端B的事务还没提交，但是客户端A就可以查询到B的数据，一旦B的事务因为原因进行回滚，所有操作将被撤销，那么A看见的数据就是脏数据

RC（读已提交）：可能出现幻读，可以防止脏读
客户端A查询表，在A的事务未提交之前，打开客户端B，更新表，这时客户端B还没提交，客户端A不能查询到B已经更新的数据，解决了脏读问题，这时B提交，客户端A执行上一步查询，结果不一样，即产生了不可重复度的问题
RC模式写数据只会锁住响应的行

RR（可重复读）：防止幻读，利用的是undo的快照技术+GAP（间隙锁）+Nextlock(下键锁)
客户端A查询表，在A的事务未提交之前，打开客户端B，更新表并提交，在客户端A操作，结果终是一样，没有出现不可重复读的问题

SR（可串行化）：可以防止死锁，但是并发事务性能较差
客户端A查询表，打开一个客户端B，插入一条记录报错，表被锁了
SR级别会锁表，因此不会产生幻读的情况，这种隔离级别并发性极低

innodb_flush_log_at_trx_commit(双一标准之一)

作用：
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0,1,2三个
3306 [(none)]>select @@innodb_flush_log_at_trx_commit;
+----------------------------------+
| @@innodb_flush_log_at_trx_commit |
+----------------------------------+
|                                1 |
+----------------------------------+
1 row in set (0.00 sec)

0：当事务提交时，不做日志写入，而是每秒钟将log buffer中的数据写入文件系统缓存并且秒fsync磁盘一次
1：每次事务的提交都会引起日志文件写入，flush磁盘的操作，确保了事务的ACID，flush到操作系统的文件系统缓存，fsync到物理磁盘
2：每次事务提交引起写入文件系统缓存，但每秒钟完成一次fsync磁盘操作

innodb_flush_method=

作用：
控制的是log buffer和data buffer，刷写磁盘的时候是否经过文件系统缓存
参数：
O_DIRECT：数据缓冲区写磁盘，不走OS buffer，日志缓冲区走OS buffer
fsync：日志和数据缓冲区写磁盘，都走OS buffer
O_DSYNC：日志缓冲区写磁盘，不走OS buffer，数据缓冲区走OS buffer

使用建议：
最高安全模式
innodb_flush_log_at_trx_commit=1
innodb)flush_method=O_DIRECT
最高性能
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync