day05-存储引擎

存储引擎

介绍

相当于Linux中的文件系统.存储引擎就是MySQL中的文件系统.负责和"磁盘"打交道

类型

支持多种存储引擎.插件式.针对不同表可以使用不同的存储引擎.
5.5 版本开始,用户自定义表,默认是InnoDB.
show engines;

InnoDB存储引擎核心特性

事务 
行锁
热备
自动故障恢复
MVCC
聚簇索引
AHI 
change buffer
多缓冲区支持
外键

彩蛋：

同源产品的存储引擎
Mariadb , percona : TokuDB
a. 高压缩比 25倍
b. Insert 性能高

基础管理

1 查看 
mysql> show engines;
mysql> select @@default_storage_engine;
mysql> show engine innodb status ;   

2 修改
create  table t1 (id int) engine=innodb;
alter table t1 engine=innodb ;
功能: 修改引擎,整理碎片.

3 存储引擎批量管理
mysql> select table_schema,table_name ,engine 
from information_schema.tables  
where 
table_schema not in ('sys','mysql','information_schema','performance_schema') 
and engine !='innodb';
  
mysql> select concat("alter table ",table_schema,".",table_name," engine=innodb;")   from information_schema.tables     where    table_schema not in ('sys','mysql','information_schema','performance_schema')    and engine !='innodb' into outfile '/tmp/a.sql';
mysql> source /tmp/a.sql

InnoDB存储引擎体系结构

1. on_disk

on_disk
	表空间的概念(5.5之后)
		## 其实就是在磁盘的上层，添加了一个TBS的逻辑表空间，然后它上与目录对接，下与多个磁盘对接。磁盘可以随时添加和随时缩减，算是逻辑上的磁盘整合。
	类型：
		1.共享表空间：就是所有的数据都放在一个叫ibdata(1..N)的文件中
		2.独立表空间：就是将数据用户独立了出来。
				.ibd 文件中存的是数据+索引
				.frm 文件中存的是单表字典+undo等
		3.undo表空间
			就是所谓的记录会滚日志。
		4.temp表空间
			存中间数据

on_disk细分表空间

## 共享表空间，5.5版本
	系统数据字典：整个数据字典：包含了整个系统的所有表的属性和定义等等
	undo:日志数据的回滚记录
	tmp:中间数据；临时存储在内存中。（相当于我们在过滤了一部分数据之后，但是还需要在过滤，但是这个数据又比较大，就存在这里边）
	change bufer ：改变缓冲区。辅助索引在进行数据插入变更的时候，都是会将数据先放在缓冲区中，然后等慢慢的同步到磁盘中。
	DWB：写缓冲区。就是在往数据库中写入数据的时候，突然想到了一半崩溃了，那么对于这个数据库来说，数据就是不完整或者损失的。但是对于磁盘来说，有的就是完整的，因为它存的大小是4kb。但是还是会说，数据毁坏丢失。这个时候，就需要用到写缓冲区了
	## 	会先将数据写一份到写缓冲区，也就是#ib_16384_0.dblwr。然后意义就是一旦数据崩溃，还有回找的机制。
	用户数据：用户存入的数据

-----------------------------------------------------------------------
-----------------------------------------------------------------------
	
	
5.5版本出现的功能. ibdataN....
各版本存储的内容:（每个版本都存放共享表空间，只是一直在瘦身，在剥离数据） 
	5.5 : 系统数据字典,undo ,tmp,DWB,change buffer,用户数据 
    5.6 : 将用户数据默认独立了.
	5.7 : tmp独立了,undo可以在初始化时,手工独立出来
	8.0.19之前 : 系统数据字典取消,只剩余change buffer和DWB
    8.0.19之后 :  DWB也独立了.只剩余change buffer.
    
  
# 管理
aa. 查看配置
mysql> select @@innodb_data_file_path;

在初始化的时候：
	直接就将ibdata给够，不需要在后期的添加
		## 每次自动扩展是64M 初始化默认为12M.
		## 在生产建议大小是1-4G,个数是2-3个
在后加的时候：
	修改配置文件。然后在重启。只不过在这里，第一个配置文件的大小必须是和初始化的是一样的，大了小了都不行，然后只需要在配置文件中进行添加，然后重启服务就可以了
	
bb. 后加
vim /etc/my.cnf
innodb_data_file_path=ibdata1:76M;ibdata2:128M:autoextend
	## 

cc. 重启数据库
/etc/init.d/mysqld restart

dd. 初始化时设定
[root@db01 data]# mysqld --defaults-file=/etc/my.cnf --initialize-insecure --user=mysql

	

独立表空间 ： 5.6之后，默认用来存储用户数据的。
	8.0 之前一张表包含：
			 t1.ibd
			 t1.frm
			 ibdataN：包含了t1表的一部分数据
			 mysql统计日志
			 各种日志
	
	8.0之后包含
			t1.ibd：会存在于自己本身的ibd文件中，并且是一表一个ibd文件
			mysql统计日志
			各种日志
		select @@innodb_file_per_table;
        		0 共享
        		1 独立存储
			


undo表空间(记录回滚日志)
	## 一般设2-4个，默认是1G，并且能自动清理
		## 8.0之前，不需要调配，默认在ibdata文件里，生产建议是初始化手工独立undo到自己的表空间
		## 8.0之后，可随时随地的使用undo表空间，只需要创建的名是以.ibu结尾的文件
		
管理：
8.0之前，需要在初始化的时候，在配置文件中进行添加
SELECT @@innodb_undo_tablespaces;  ---->3-5个    #打开独立undo模式，并设置undo的个数。
SELECT @@innodb_max_undo_log_size;               #undo日志的大小，默认1G。
SELECT @@innodb_undo_log_truncate;               #开启undo自动回收的机制（undo_purge）。
SELECT @@innodb_purge_rseg_truncate_frequency;   #触发自动回收的条件，单位是检测次数。

8.0 之后
mysql> create undo tablespace oldguo add datafile 'oldguo.ibu';
mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
    ->   WHERE FILE_TYPE LIKE 'UNDO LOG';
mysql> alter undo tablespace oldguo set inactive;
mysql> drop undo tablespace oldguo;
mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES   WHERE FILE_TYPE LIKE 'UNDO LOG';


tmp 表空间
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M
	

日志文件

1 undo 日志 
存放至undo表空间,undu_001,002  ibu 
功能:存储回滚日志 

2 redo 日志 (前滚日志。默认是48M)
存放重做日志. 默认大小48M，默认2组。轮询使用
ib_logfile0
ib_logfile1


设置建议:
512M-4G,和TPS ,3-5组
TPS:每秒订单量（增删改）


select @@innodb_log_file_size      ;  字节量，可以写成M
select @@innodb_log_files_in_group ;  组数
select @@innodb_log_group_home_dir ;  存储的位置，可默认


注意: 
	生产中修改redo配置,必须优雅关闭数据库,重启数据库.
	set global innodb_fast_shutdown=0;

其他结构

`其他结构 
	a. ib_buffer_pool 5.7之后
	## 当关闭数据库后，内存中的东西也会立马被释放。但是当下次加载的时候，就又需要花费很多的时间，来进行加载。这个时候就需要在关闭数据之前，先将数据存到磁盘中，并且是以顺序的方式存放的。当再次调用的时候，比随机加载快很多
innodb_buffer_pool_load_at_startup 
innodb_buffer_pool_dump_at_shutdown



	b. DWB (double write buffer)
	写缓冲区（数据在写入一半时，崩溃了，对于数据库来说。这样的数据就是损坏的，所以为了避免出现这个问题造成数据的丢失，就会先写入一份到缓冲区中。然后做备份。）
#ib_16384_0.dblwr
#ib_16384_1.dblwr

2.IN-memory

`内存结构，innodb是有专门的缓存区。
## 用来缓存或者缓冲我们的数据页（page）+索引叶
	`共享内存区：
		IBP  innodb_buffer_pool mysql中最大的共享内存区域
			## innodb_buffer_pool_size 默认是128M 在生产中建议其内存大小是物理内存的50~75%之间
				里边存放的是data_buffer / change_buffer  / AHI 
			## 可以做在线修改，但是需要以字节为单位的修改。配置文件中可以使用单位，但是一般建议都使用字节
			
		ILB  innodb log buffer  日志缓冲区
			## 是缓冲redo log 的内存区域
				建议大小是512M-4G
				innodb_log_buffer_size ##也可在线调节
				
	`私有内存区：属sql层

innodb 核心特性

`事务：transaction（交易） 类似一种等价交易
	作用: 为保证在数据中，发生交易类的操作时候，能够保证“平衡”（在交易中，要不都成功，哪怕出现一点错，都是错）
	特性：
		A : 原子性 (Atomicity)
		事务中的所有查询,要么全成功,要么全失败.

		C : 一致性 ( Consistency)
		事务发生前,中,后.数据保证最终一致性.

		I : 隔离性(Isolation)
		并发事务期间,事务之间互不干扰.

		D : 持久性(Durability)
		事务一旦提交.永久生效.

`生命周期：
	1.标准事务控制语句 
	begin;/start transaction;
	DML 语句 .....  ## 只能是DML语句
	commit; ## 提交

	begin;/start transaction
	DML 语句 ....
	rollback ## 回退
	
`autocommit自动提交  5.6版本之后加入的
	如果没有显式的开启事务(begin)
	MySQL 会自动在DML语句执行前,加上begin，并且在你要执行下一条语句的时候，会自动加上commit;
		## 如果开启自动提交，就会出现逐行处理，那么当想要几条DML语句一起执行的时候，就没办法了。
			## 那么为了打破逐行，当需要事务支持的时候，就需要手动的执行begin开启事务，这样就不逐行
	[begin]
	update t1 set name='a'm where id=1;
	commit;
	
`隐式 提交和回滚

begin;
DML1
DMl2
commit;
	## 只要出现了DML语句的后边出现了DDL/DCL/set/lock，就会出现自动的提交。
DDL语句


SET AUTOCOMMIT = 1	默认开启。
## 导致提交的非事务语句：
DDL语句： （ALTER、CREATE 和 DROP）
DCL语句： （GRANT、REVOKE 和 SET PASSWORD）
锁定语句：（LOCK TABLES 和 UNLOCK TABLES）

## 导致隐式提交的语句示例：
truncate table 
load data infile
select for update

# 隐式回滚 
会话窗口被关闭。
数据库关闭 。
出现事务冲突（死锁）。


`事务的隔离级别
a. 介绍
RU   : read-uncommitted  读未提交
RC   : read-committed    读已提交
RR   : repeatable-read   可重复读
SE   : serializable      串行化

b. 
配置方法 
mysql> select @@transaction_isolation;
	## 默认是可重复读
	## 隔离级别越高，那么事务的并发性就会越低。在生产中，很多的互联网公司使用的是RC 。好点的是RR 
	mysql> set global transaction_isolation='read-committed'; 在配置文件中
	
`隔离级别特性说明
	我们所隔离的是”读“ 操作，所谓读：就是先将数据给读取出来，那么insert update delect都算读操作
	
	## 有问题的读操作
		脏读：事务中读取到了别的事务未提交的数据.
			## 两个会话同时在做操作的时候，其他A只是begin，但是并没有做commit，但是在B会话中begin，却读到了并没有提交的事件数据
		不可重复读：同一个事务内,读取相同数据时,产生了不同的结果.
			## 两个会话同时在操作，因为A会话一直在做其中一行数据的插入或者更更改，那么在B会话的begin中查询到的，每次查询到的都是不同的值，那么如果在做统计的时候，就会造成数据的不准
		幻读：在同一个事务内,读取到了幻影数据.
			## 两个会话同时在操作，其中A会话进行了begin,并且操作了一个范围的查找。这个时候，我在B会话中，加入begin，并且插入了一个数值，并commit 。这个时候A会话输入commit，退出事务之后，就会发现多了一条数据，并且这条数据不是按照自己的意愿得到的。
            
            
	RU :  脏读 不可重复读 幻读
	RC :  不可重复读 幻读
	RR :  幻读.加上锁(next lock)的机制可以有效0避免幻读.
	SE :  NUL

扩展内容:ACID如何保证

 `redo log  重做日志
	ib_logfile0~N   日志文件
    innodb_log_buffer      缓冲区

`undo log  回滚日志 
	undo_001~002  .ibu
	data_buffer
	
`WAL   日志先行
	write ahead log	
落盘数据时,先写日志,再写数据.


`LSN 值序列号.
	标识了产生redo日志的字节量.单调递增
page lsn 
redo buffer 
redo log 
system 

`DB_TRX_ID（6字节）  事务ID号
InnoDB会为每一个事务生成一个事务号,伴随着整个事务生命周期.

`DB_ROLL_PTR（7字节） 回滚指针
rollback 时，会使用 undo 日志回滚已修改的数据。DB_ROLL_PTR指向了此次事务的回滚位置点，用来找到undo日志信息。


`脏页: Dirty Page
内存脏页,内存中发生了修改,没写入到磁盘之前,我们把内存页称之为脏页.


`CheckPoint
CKPT:检查点,就是将脏页刷写到磁盘的动作

事务工作逻辑redo==>重做操作

事务举例： 
begin; 
update t1 set A=2 where A=1;
commit;

# redo log 重做日志如何应用
1. 用户发起update事务语句，将磁盘数据页（page100,A=1,LSN=1000）加载到内存(buffer_pool)缓冲区。
2. 在内存中发生数据页修改（A=1改成A=2），形成脏页，更改中数据页的变化，记录到redo buffer中，加入1000个字节日志。LSN=1000+1000=2000。
3. 当commit语句执行时，基于WAL机制，等到redo buffer中的日志完全落盘到ib_logfileN中，commit正式完成。
4. ib_logfileN中记录了一条日志。内容：page100数据页变化+LSN=2000。

## 补充一点： 
	MySQL有一种机制，批量刷写redo的机制。会在A事务commit时，顺便将redo buffer中的未提交的redo日志也一并刷到磁盘。
	为了区分不同状态的redo，日志记录时，会标记是否COMMIT。
	
所以，redo日志中，有commit,还有未commit的事务。

## redo保证了ACID哪些特性?
主要是D的特性，另外A、C也有间接关联。

undo回退工作逻辑

# undo log 回滚日志如何应用？
1. 事务发生数据页修改之前，会申请一个undo事务槽位，保存事务回滚日志（逆向操作的逻辑日志）。
2. undo写完之后，事务修改数据页头部（会记录DB_TRX_ID+DB_ROLL_PTR），这个信息也会被记录的redo。

情景1： 
当执行rollback命令时。根据数据页的DB_TRX_ID+DB_ROLL_PTR信息，找到undo日志，进行回滚。

情景2：
	begin; 
	update t1 set A=2 where A=1;
	宕机。

假设：undo 有，redo也有（没有commit标签。）
1. MySQL CR（自动故障恢复）工作模式，启动数据库时，自动检查redo的LSN和数据页LSN。
2. 如果发现redoLSN>数据页的LSN ，加载原始数据页+变化redo指定内存。使用redo重构脏页（前滚）。
3. 如果确认此次事务没有commit标记，立即触发回滚操作，根据DB_TRX_ID+DB_ROLL_PTR信息，找到und回滚日志，实现回滚。
以上流程被称之为InnoDB的核心特性：自动故障恢复（Crash Recovery）。先前滚再回滚，先应用redo再应用undo。


## undo在ACID中保证了啥？
主要保证事务的A的特性，同时C和I的特性也有关系。


`C 特性怎么保证？
InnoDB crash recovery：数据库意外宕机时刻，通过redo前滚+undo回滚保证数据的最终一致。
InnoDB doublewrite buffer： 默认存储在ibdataN中。解决数据页写入不完整
mysqld process crash in the middle of a page write, InnoDB can find a good copy of the page from the doublewrite buffer during crash recovery.
DWB一共2M。分两次，每次1M写入

##  总结：	CR的过程,对比日志和数据页LSN,判断是否需要CR.
			先进行REDO前滚,再进行undo回滚.
			
`I 特性如何保证
隔离级别 :  提供读的隔离.
锁      	： 读是不加锁
MVCC    : 多版本并发控制. 使用了UNDO快照
	RC  : 每次做新的查询,都会获得一次全新的readview.
	RR  : 在开启时候后,第一次查询数据时,就会生成一致性的readview.一直持续到事务结束.一致性快照读.
	
	
	## lock (引擎层)
		record lock    记录锁/行锁
		RR 级别下:   
		gap    lock    间隙锁 
		next   lock    = record + gap 下一键锁

		`锁的范围，是和你的索引有着很大的关系的。取索引的范围大。你得索引的范围就大。如果没有建索引，锁的就是整表数据。
	
	## 如何解决锁等待的问题：
		1.将RR改为RC
		2.取其他的列进行提取。
	
	## 如何监控锁等待：
		`5.7以上，use sys 根据索引进行加锁
				desc innodb_lock_waits;

				
	mysql> select * from sys.innodb_lock_waits\G
	mysql> select * from  performance_schema.threads where PROCESSLIST_ID=17;
	mysql> select * from  events_statements_history where thread_id=57\G

记录锁/行锁
RR 级别下:
gap lock 间隙锁
next lock = record + gap 下一键锁

	`锁的范围，是和你的索引有着很大的关系的。取索引的范围大。你得索引的范围就大。如果没有建索引，锁的就是整表数据。

## 如何解决锁等待的问题：
	1.将RR改为RC
	2.取其他的列进行提取。

## 如何监控锁等待：
	`5.7以上，use sys 根据索引进行加锁
			desc innodb_lock_waits;

			
mysql> select * from sys.innodb_lock_waits\G
mysql> select * from  performance_schema.threads where PROCESSLIST_ID=17;
mysql> select * from  events_statements_history where thread_id=57\G