mysql_5_2 INNODB 核心特性
标签（空格分隔）： mysql
什么是事务?
事务是伴随着 《交易类》的业务场景出现的工作机制
保证交易的“和谐“
计算机中
A 转账 B
开启事务
A 账户 100元 ： A-100元 update A - 100
B 账户 0元： B+100元 update B + 100
事务结束
事务的ACID标准特性
原子 atomicity
原子是物质的最小构成单元，具有不可再分的特性。
在一个事务工作单元中，所有标准事务语句DML 要么全成功，要么没发生
一致 consistency
事物发生前，中、后都应该保证数据的一致状态。
mysql的各项功能的设计，都是要最终保证一致性。
隔离 isolation
mysql可以支持多事务并发工作的系统。
A工作的时候，不能够受到其他事务的影响。
持久 durability
事务提交后，此次事务操作的所有数据，都要永久保存下去。
不会因为数据实例发生故障，导致数据失效。
如何开启事务
开启事务
begin/start transaction
commit 提交事务
rollback 回滚事务
哪些dml语句可以使用事务
INSERT
DELETE
UPADTE
SELECT 没什么作用
事务的自动提交
自动提交 autocommit 默认开启
select @@autocommit
show variables like "autocommit";
如果是交易类的业务
方案1：
autocommit=0 ; commit,手动提交才生效
方法2：
autocommit=1
每次想要发生事务性操作
begin和commit都手动操作
1.临时生效
set global autocommit=0；
重启开启会话生效
永久生效
vim /etc/my.cnf
autocommit=0
重启数据库生效。
什么时候自动提交事务
使用dml语句时 会自动在这个dml之前加一个begin；之后加一个commit
隐式提交
设置了autocommit=1
定义一些DDL、DCL非DML语句时操作
创建表
修改列属性
他就会隐式提交这些事务。
session 1
begin；
DML1
DML2
COMMIT;
DROP DATABASE WORLD
什么时候隐式提交
DDL语句 ALTER CREATE DROP
DCL语句 GRANT REVOKE SETPASSWORD
锁定语句 LOCK TABLES 和 UNLOCK TABLES
隐式提交的语句实例：
TRUNCATE TABLE
LOAD DATA INFILE
SELECT FOR UPDATE
隐式回滚
会话关闭
数据库宕机
事务语句执行失败
analyze table
统计数据
查询优化器
保存点 save point
修改3次
可以在第二次保存
begin
sql语句
save point s1
sql语句
save point s2
sql语句
save point s3
可以rollback 点
回滚就只会回一次
INNODB事务的ACID如何保证？
重做日志
redo log 重做日志 ib_logfile0-N 48M 轮训使用
记录的是数据页的变化
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会为每一个事务生成一个事务号，伴随着整个事务。
UNDO:IBDATA1 存储了事务工作过程中的回滚信息。
redo作用
重做日志，事务日志的一种。
在事务ACID过程中，实现的是D持久化的作用，对于AC也有相应的作用
redo日志位置
iblogfile0
iblogfile1
redo buffer
redo buffer
LSN
commit;
刷新当前事务的redo buffer到磁盘
还会顺便将一部分redo buffer中没有提交的事务日志刷新到磁盘
redo存储的是事务工作过程中，数据页变化，commit时会立即写入磁盘 默认.日志落盘成功commit。
正常mysql工作流程当中
主要的工作是提供快速持久化功能。
当mysql出现crash异常宕机时，
主要提供的是前滚功能(CSR).
innodb_flush_log_at_trx_commit = 0 / 1 / 2
1:每次事务提交时，会立即刷新redo到磁盘，commit才能成功
0:每秒刷新日志redo buffer到os cache 在fsync到磁盘 异常宕机时，可能会丢失一秒内的数据。
2.每次事务提交，都立即刷新redo buffer 到 os cache 在每秒fsync() 磁盘，异常宕机时，会有可能导致丢失1s内的事务。
默认是1
redo buffer 还跟操作系统缓存机制有关，所以刷写策略可能和innodb_flush_method参数有一定关系。
redo 也有 group commit 可以理解为 在每次刷新已提交的redo时，顺便可以将一些未提交的事务redo页一次性刷写到磁盘。此时为了区分不同装填的redo，会加一些比较特殊的标记（是否提交标记）
undo logs
undo 回滚日志
作用
在事务ACID过程中，实现的是“A”原子性的作用
另外CI也依赖于undo
在rollback是，将数据恢复到修改之前的状态
在CSR实现的是，将redo当中几率的未提交的时候进行回滚。
undo提供了快照技术，保存事务修改之前的数据状态，保证MVCC，隔离性，mysqldump的热备恢复机制。
undo在生成过程中也会记录redo信息。
宕机使用redo前滚 再使用undo进行回滚 重新恢复内存中的信息 。
什么是一致性快照
每个事物开启时，都会通过undo日志获取一个一致性的快照。
undo提供快照技术，保存事务修改之前的数据状态
隔离级别和锁机制
主要是提供I的特性，另外对于C的特性也有保证。
隔离性设置
set session transaction isolation level READ COMMITTED
set global transaction_isolation="read-uncommitted"
vim /etc/my.cnf
transaction_isolation="read-uncommitted"
重启生效
读 未提交 RU
问题 脏读 不可重复读 幻读
读 已提交 RC
问题 幻读 不可重复读
可重复读 RR
一个事物第一次读过某条记录后，即使其他事务修改了记录的值提交，该事务之后在读这条记录时，读到的仍然是第一次读到的值，而不是每次都读到不同的数据，这就是可重复读
解决了不可重复，但会出现幻读。
mysql 隔离级别在可重复读 (） 的标准 但是 还是存在 幻读 的问题
MVCC重要功能
防止不可重复读现象。
利用了undo的一致性快照
幻读
通过RR，已经可以解决99以上的幻读。为了更加严谨，加入了GAP间隙锁，next-lock
间隙锁 锁定 范围内的所有记录 解决幻读
串行化 SR
同一时刻只能够执行 读或写的操作。
不会出现任何问题
脏读
任何一个事务修改 其他事务就能够读取新的数据 就是脏读
不可重复读
一个事务读取上一秒还是老数据，下一秒等其他事务提交后。第一个事务读取发现数据就变了。
幻读
select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。
亦或是 更新 时 别人提交了 但是发现自己更新的数据 多了一条没有更新的（别人提交的数据）
并发读
transaction_id 事务id 最近修改这条数据的事务id
roll_pointer 回滚节点
例子
环境是
读已提交
0->1(80)->2(200)
200事务 会不管现在的数据，然后根据transaction_id去找自己修改的事务id的数据 roll_pointer指向了版本连的数据。
A(100) b(200)
A读数据 会发现没有id是自己的
就会创建readView
创建m_ids:[81,82,200,300]
在m_ids 寻找已提交的事务 80 在取数据。
如何这时候b 事务 提交了
a的readview 就会变化
m_ids:[81,82,300]
直接取200 这个已提交的事务即可
未修改的地方存放在undolog里面。
m_ids 提交完成的会在前面。
ReadView 实现了 MVCC 多版本并发控制 完成了 读已提交 和 可重复读 隔离级别。
环境是
可重复读
A B
1 1-2
COMMIT;
还是1
还是基于readview
A去查
生成
readview
m_ids:[81,82,200,300];
b commit;
如何这时候b 事务 提交了
a的readview 在可重复读的环境下，不会变化， 而是直接用上次生成好的。
m_ids:[81,82,200,300]
mysql 锁机制
介绍
相当于厕所的门
作用
保证事务的隔离性。也保证了数据的一致性
保证资源不被同时争用。锁是属于资源的，不是某个事物的特性。
每次事务需要资源的时候，需要申请持有资源的锁
锁类型
资源：
内存锁：
mutex latch
文件锁 对象锁：
MDL（元数据锁） 修改元数据时 DDL --> alter
table_lock 表锁 DDL 备份（FTWRL全局表锁） lock tables t1 read 也可以升级为表锁
record(row) lock 行锁 索引锁 锁定聚簇索引
GAP 间隙锁 RR级别 非唯一索引 普通辅助索引间隙锁
Next_lock 下一键锁 GAP+RECORD LOCK 普通辅助索引范围锁 update 一个范围 这个范围再插入 就会爆锁等待
更新表里面的30% 开始走全表 这时候会锁住表
粒度：
MDL（元数据锁） 粒度最高 都锁住了
table_lock 表都锁柱了
record(row) lock 可以锁多行或一行
GAP 可以锁多行或一行
Next_lock 可以锁多行或一行
功能锁：
IS : 意向共享锁 表级别
S : 共享锁 读锁 行级别
IX : 意向排它锁 表级别
X : 排它锁 写锁 行级别
排他锁 不允许他人在进行修改
功能锁冲突表：
X锁 IX锁 S锁 IS锁
X锁 冲突 冲突 冲突 冲突
IX锁 冲突 不冲突 冲突 不冲突
S锁 冲突 冲突 不冲突 不冲突
IS锁 冲突 不冲突 不冲突 不冲突
冲突 就会出现锁等待
表加锁与解锁
lock tables t1 read; 加上s锁
unlock tables t1;
lock tables t1 write; 加上x锁
unlock tables t1;
select ... lock in share mode 加上 is 锁
select ... for update 加上 ix 锁
查看锁等待的情况
select * from sys.innodb_lock_waits
事物ACID的保证
A : 原子性 UNDO REDO LOG 来保证
D : 持久性 commit的数据性 redo(WAL)记录保证
I : 隔离性 ISOLATION LEVEL 数据隔离级别 LOCK 锁 MVCC (UNDO 日志) 技术
C ：一致性 保证工作前、中、后。数据的状态都是完整的
C的特性是以上共同保证的特性。
写一致性
undo redo lock
读一致性
ISOLATION LEVEL 数据隔离级别 MVCC(UNDO 日志)技术
数据页的一致性
double write buffer 磁盘区域
内存写入磁盘 会先在redo log 里面保存doblewrite 顺序读写 让后在写入数据。放置宕机，写入失败。
存储引擎核心参数
双一标准之一： redo log 刷写参数
innodb_flush_log_at_trx_commit=1/0/2
1:每次事务提交时，会立即刷新redo到磁盘，commit才能成功
0:每秒刷新日志redo buffer到os cache 在fsync到磁盘 异常宕机时，可能会丢失一秒内的数据。
2.每次事务提交，都立即刷新redo buffer 到 os cache 在每秒fsync() 磁盘，异常宕机时，会有可能导致丢失1s内的事务。
innodb_flush_method=fsync/O_DIRECT/O_DIRECT/O_SYNC
作用 ： 控制mysql 刷写磁盘时 是否需要使用os_cache
OOM ------> innodb_buffer_pool_size = 80% total
fsync 参数 默认
buffer pool 的数据写磁盘的时候 需要经历os_cache 再写入磁盘
redo buffer 的数据写磁盘的时候 需要经历os_cache 再写入磁盘
o_sync:
buffer pool 的数据写磁盘的时候 需要经历os_cache 再写入磁盘
redo buffer 的数据写磁盘的时候 不经历os_cache 写入磁盘
O_DIRECT：
buffer pool 的数据写磁盘的时候 不经历os_cache 写入磁盘
redo buffer 的数据写磁盘的时候 需要经历os_cache 再写入磁盘
建议是用o_direct,最好配合固态盘
innodb_buffer_pool_size
作用：数据缓冲区的总大小。缓冲数据页和索引页。是mysql最大的内存区域。
70% 左右
查看innodb状态
show engine innodb status
并发写
读锁 共享锁 S锁 只能读了 不能写
写锁 排它锁
select 不加锁 同时也无视锁
X锁 S锁
X锁 冲突 冲突
S锁 冲突 不冲突
select .. lock in share mode
加读锁
select for update
加写锁
delete
insert
update
都是写锁
行锁
LOCK_REC_NOT_GAP 单个行记录上的锁
LOCK_GAP 间隙锁 锁定一个范围 但不包括纪律本身 gap锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。
LOCK_ORDINARY 锁定一个范围 并且锁定记录本身，对于行的查询 都是采用这个方法，主要目的解决幻读
Read commited 读已提交
隔离级别 锁的情况
主键使用 for update 上锁 只会锁住那一行
唯一索引同样
普通索引一样
select * from t1 where c = '1' for update;
符合的加锁 不符合的数据不会加锁
主键使用 for update 上锁 锁住了一行 可以isnert 操作全表 会出现幻读情况
上锁需要考虑间隙 加锁也是对间隙而言的 没在间隙内的 可以做写操作的
repeat commited 重复度
主键使用 for update 上锁 只会锁住那一行
主键使用 for update 上锁 锁住了一行 无法isnert 操作全表 B+树 无法插入 无法排序将数据放入叶子节点中。 防止了幻读
上锁需要考虑间隙 加锁也是对间隙而言的 没在间隙内的 可以做写操作的
不使用索引 mysql就会索引全部范围 不运行insert 为了解决幻读。
表锁
lock table f1 read
lock table f1 write
select readview
update 加锁
表锁
如果有行锁
lock is锁
lock ix锁