MySQL存储引擎
一.存储引擎简介
相当于Linux文件系统，只不过比文件系统强大
二.存储引擎的种类
MyISAM存储引擎
由于该存储引擎不支持事务、也不支持外键，所以访问速度较快。因此当对事务完整性没有要求并以访问为主的应用适合使用该存储引擎。
InnoDB存储引擎
mysql 5.5版本以后默认的存储引擎
由于该存储引擎在事务上具有优势，即支持具有提交、回滚及崩溃恢复能力等事务特性，所以比MyISAM存储引擎占用更多的磁盘空间。因此当需要频繁的更新、删除操作，同时还对事务的完整性要求较高，需要实现并发控制，建议选择。
MEMORY
MEMORY存储引擎存储数据的位置是内存，因此访问速度最快，但是安全上没有保障。适合于需要快速的访问或临时表。
BLACKHOLE
黑洞存储引擎，写入的任何数据都会消失，可以应用于主备复制中的分发主库。
ARCHIVE
FEDERATED
EXAMPLE
MERGE
NDBCLUSTER
CSV
还可以使用第三方存储引擎:
01）MySQL当中插件式的存储引擎类型
02）MySQL的两个分支
- perconaDB
- mariaDB
三.innodb的特性
3.1innodb存储引擎的简介
在MySQL5.5版本之后，默认的存储引擎，提供高可靠性和高性能。
优点:
01）事务安全（遵从 ACID）
02）MVCC（Multi-Versioning Concurrency Control，多版本并发控制）
03）InnoDB 行级别锁定
04）Oracle 样式一致非锁定读取
05）表数据进行整理来优化基于主键的查询
06）支持外键引用完整性约束
07）大型数据卷上的最大性能
08）将对表的查询与不同存储引擎混合
09）出现故障后快速自动恢复
10）用于在内存中缓存数据和索引的缓冲区池
innodb核心特性
重点:
- MVCC
- 事务
- 行级锁
- 热备份
- Crash Safe Recovery（自动故障恢复）
MyISAMT特性
在MySQL5.5以前使用的版本，存在很多的局限不足，例如表级锁，不支持事务、不支持外键以及不支持多并发版本控制等。
查看存储引擎
3.2查看存储引擎
1）使用 SELECT 确认会话存储引擎
#查询默认存储引擎
SELECT @@default_storage_engine;
2）使用 SHOW 确认每个表的存储引擎
#查看表的存储引擎
SHOW CREATE TABLE City\G
SHOW TABLE STATUS LIKE 'CountryLanguage'\G
3）使用 INFORMATION_SCHEMA 确认每个表的存储引擎
#查看表的存储引擎
SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLESWHERE TABLE_NAME = 'City'AND TABLE_SCHEMA = 'world'\G
3.3存储引擎的设置
1）在启动配置文件中设置服务器存储引擎
#在配置文件的[mysqld]标签下添加
[mysqld]
default-storage-engine=<Storage Engine>
2）使用 SET 命令为当前客户机会话设置
#在MySQL命令行中临时设置
SET @@storage_engine=<Storage Engine>
3）在 CREATE TABLE 语句指定
#建表的时候指定存储引擎
CREATE TABLE t (i INT) ENGINE = <Storage Engine>;
四.innodb存储引擎物理存储结构
1、.frm文件
不论是什么存储引擎，每一个表都会有一个以表名命名的.frm文件，与表相关的元数据（meta)信息都存放在此文件中，包括表结构的定义信息等，一般称为表结构
2、.ibd文件和ibdata文件
存放innoDB的数据文件（包括索引）。innoDB存储引擎有两种表空间方式：独享表空间和共享表空间。
独享表空间：使用.ibd文件来存放数据，且每个表一个.ibd文件，文件存放在和myisam数据相同的位置。
共享表空间：使用.ibdata1文件，所有表共同使用一个（或多个，自行配置，默认大小12M,空间是自增的，可以在配置文件写入生成多个文件）ibdata1文件。用来保存系统数据、临时表、undo log（事务日志）。
3、db.opt文件 此文件在每一个自建的库里都会有，记录这个库
4、5.7版本中默认会将undo和临时表独立出来，5.6版本也可以独立，只不过需要在初始化的时候进行配置
5、MySQL的存储引擎日志：
Redo Log: ib_logfile0 ib_logfile1，重做日志。默认大小48M,该文件的大小是不支持扩展的，两个文件循环使用，也就意味着先写满的文件是会被清空。
Undo Log: ibdata1 ibdata2(存储在共享表空间中)，回滚日志
临时表:ibtmp1，在做join union操作产生临时数据，用完就自动释放。
扩展：
myisam的数据存放文件
1、.MYD文件
myisam存储引擎专用，存放myisam表的数据（data)。每一个myisam表都会有一个.MYD文件与之呼应，同样存放在所属数据库的目录下
2、.MYI文件
也是myisam存储引擎专用，存放myisam表的索引相关信息。对于myisam存储引擎来说，可以被缓存(cache)的内容主要就是来源于.MYI文件中。
每一个myisam表对应一个.MYI文件，其存放的位置和.frm及.MYD一样
五.innodb核心特性——事务
5.1事务的ACID特性
Atomic（原子性）
所有语句作为一个单元全部成功执行或全部取消。
Consistent（一致性）
如果数据库在事务开始时处于一致状态，则在执行该。事务期间将保留一致状态。
Isolated（隔离性）
事务之间不相互影响。
Durable（持久性）
事务成功完成后，所做的所有更改都会准确地记录在数据库中。所做的更改不会丢失
5.2事务的的生命周期
事务的生命周期（事务控制语句）
事务的开始
begin
说明:在5.5 以上的版本，不需要手工begin，只要你执行的是一个DML，会自动在前面加一个begin命令。
事务的结束
commit：提交事务
完成一个事务，一旦事务提交成功 ，就说明具备ACID特性了。
rollback ：回滚事务
将内存中，已执行过的操作，回滚回去
自动提交策略（autocommit）
mysql>select @@autocommit;
mysql>set autocommit=0;
mysql>set global autocommit=0;
注：
自动提交是否打开，一般在有事务需求的MySQL中，将其关闭
不管有没有事务需求，我们一般也都建议设置为0，可以很大程度上提高数据库性能
(1)临时设置
set autocommit=0;
set global autocommit=0;
(2)永久设置，配置文件修改需要重启数据库
vim /etc/my.cnf
autocommit=0
隐式提交语句
用于隐式提交的 SQL 语句：
begin
a
b
begin
SET AUTOCOMMIT = 1
导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）
导致隐式提交的语句示例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE
5.3事务的隔离级别
四种隔离级别：
READ UNCOMMITTED（独立提交）
允许事务查看其他事务所进行的未提交更改
READ COMMITTED
允许事务查看其他事务所进行的已提交更改
REPEATABLE READ
确保每个事务的 SELECT 输出一致
InnoDB 的默认级别
SERIALIZABLE
将一个事务的结果与其他事务完全隔离
#查看隔离级别
mysql> show variables like '%iso%';
#修改隔离级别为RU
[mysqld]
transaction_isolation=read-uncommit
#修改隔离级别为RC
[mysqld]
transaction_isolation=read-commit
#修改隔离级别为RR
[mysqld]
transaction_isolation=REPEATABLE-READ
#修改隔离级别为SERIALIZABLE
transaction_isolation=SERIALIZABLE
#阻塞所有操作，查询的时候，不能修改，修改的时候，不能查询
六.InnoDB 事务的ACID的保证（CSR）
6.1基本概念
redo log ---> 重做日志 ib_logfile0~1 50M , 轮询使用
redo log buffer ---> redo内存区域
ibd ----> 存储 数据行和索引
buffer pool --->缓冲区池,数据和索引的缓冲
LSN : 日志序列号
磁盘数据页,redo文件,buffer pool,redo buffer
MySQL 每次数据库启动,都会比较磁盘数据页和redolog的LSN,必须要求两者LSN一致数据库才能正常启动
WAL : write ahead log 日志优先写的方式实现持久化
脏页: 内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.
CKPT:Checkpoint,检查点,就是将脏页刷写到磁盘的动作
TXID: 事务号,InnoDB会为每一个事务生成一个事务号,伴随着整个事务.
6.2redo log
1Redo是什么？
redo,顾名思义“重做日志”，是事务日志的一种。
2 作用是什么？
在事务ACID过程中，实现的是“D”持久化的作用。对于AC也有相应的作用
3 redo日志位置
redo的日志文件：iblogfile0 iblogfile1
4 redo buffer
redo的buffer:数据页的变化信息+数据页当时的LSN号
LSN：日志序列号 磁盘数据页、内存数据页、redo buffer、redolog
5 redo的刷新策略
commit;
刷新当前事务的redo buffer到磁盘
还会顺便将一部分redo buffer中没有提交的事务日志也刷新到磁盘
定时提交
到了一定的时间自动刷新数据写入到磁盘
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存储到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的"前滚操作"
6.3 undo 回滚日志
1 undo是什么？
undo,顾名思义“回滚日志”
2 作用是什么？
在事务ACID过程中，实现的是“A” 原子性的作用
另外CI也依赖于Undo
在rolback时,将数据恢复到修改之前的状态
在CSR实现的是,将redo当中记录的未提交的时候进行回滚.
undo提供快照技术,保存事务修改之前的数据状态.保证了MVCC,隔离性,mysqldump的热备
七.MySQL存储引擎-锁
1）什么是“锁”？
“锁”顾名思义就是锁定的意思。
2）“锁”的作用是什么？
在事务ACID特性过程中，“锁”和“隔离级别”一起来实现“I”隔离性的作用。
3）MySQL中的锁
排他锁：保证在多事务操作时，数据的一致性。(修改操作会被阻塞)
共享锁：保证在多事务工作期间，数据查询时不会被阻塞。
乐观锁：谁先提交，以谁为准
悲观锁：谁先执行事务，并阻塞其他事务查询
4）锁的粒度
innodb：行级锁
myisam：表级锁
八.MVCC多版本并发控制
1）只阻塞修改类操作（排他锁），不阻塞查询类操作（共享锁）
2）乐观锁的机制（谁先提交谁为准）
