MySQL事务与存储引擎

3.1-数据库事务

事务的定义

一系列有序的数据库操作。

• 要么全部成功
• 要么全部回退到操作前的状态
• 中间状态对其他连接不可见

事务的基本操作

基本操作	说明
start transaction	开始事务
commit	提交(全部完成)
rollback	回滚(回到初始状态)

-- 开启一个事务
start transaction;
-- 或者使用(非标准sql)
begin;
insert into t values (1, 1, 1);
-- 事务结束，插入成功
commit;

begin;
insert into t values (2, 1, 1);
insert into t values (3, 1, 1);
insert into t values (4, 1, 1);
-- 事务结束，没有插入数据
rollback;

begin;

insert into t values (1, 1, 1);
savepoint a1;
insert into t values (2, 1, 1);
-- 回滚到指定的保存点
rollback to a1;
commit;

自动提交

• autocommit可以在session级别设置
• 每个DML操作都自动提交
• DDL永远都是自动提交，无法通过rollback回滚

事务的四个基本属性(ACID)

• 原子性(Atomicity)
  一个事务包含多个操作，这些操作要么全部执行，要么全都不执行。实现事务的原子性，要支持回滚操作，在某个操作失败后（数据库或应用发生异常），回滚到事务执行之前的状态。未提交的事务都应该被回滚。

• 一致性(Consistency)

• 数据的正确性，合理性，完整性

• 数据一致性要符合应用应该符合的规则：余额不为负／交易对象必须先有账号
  ／用户账号不重复

• 事务的结果需要满足数据的一致性约束

• 隔离性(Isolation)
  并发的事务是相互隔离的。数据库的某一事务在提交完成前，中间的任何数据变化对其他的事务都是不可见的。
  即一个事务内部的操作及正在操作的数据必须封锁起来，不被其它企图进行修改的事务看到（假如并发交叉执行的多个事务任意操纵相同的共享对象，可能引起异常）

• 持久性(Durability)
  发生故障时，确保已提交事务的更新不能丢失。
  一旦事务提交完成，对数据库中数据的影响必须是永久性的（数据成功写入磁盘 即 持久化成功）。

实现 事务的持久化

• 数据文件持久化
  • 随机同步刷新(慢)
• 事务日志持久化与实例恢复
  • 顺序同步刷新(快) -> 事务日志
  • 随机异步刷新 -> 磁盘
  • 事务日志 -> 磁盘(实例恢复)

数据库隔离现象

隔离现象	描述
脏读(Dirty Read)	事务B读到事务A尚未提交的数据变更
不可重复读(NonRepeatable Read)	事务B读取前后两次读取一条记录之间该记录被事务A修改并提交，于是事务B读到了不一样的结果
幻读(Phantom Read)	事务B按条件匹配到了若干行记录并修改。但是由于修改过程中事务A新插入了符合条件记录，导致B更新完成后发现仍有符合条件却未被更新的记录。

数据库隔离等级

隔离等级	脏读	不可重复读	幻读
未提交读(Read Uncommitted)	可能	可能	可能
已提交读(Read Committed)	不可能	可能	可能
可重复读(Repeated Read)	不可能	不可能	可能
可串行化读(Serialization)	不可能	不可能	不可能

只有在事务提交后，其更新结果才会被其他事务看见。

 ：在一个事务中，对于同一份数据的读取结果总是相同的，无论是否有其他事务对这份数据进行操作

MySQL的事务隔离级别

• InnoDB默认标记为可重复读
• InnoDB并不是标准定义上的课重复读
• InnoDB默认在可重复读的基础上避免幻读

MySQL事务隔离级别设置

• 可在global/session/下个事务，级别分别进行设置
• 建议使用Read committed(同Oracle)
• 或者建议使用默认的Repeatable read

set tx_isolation = ''
-- 设置隔离级别

事务与并发写

• 某个正在更新的记录再提交或回滚前不能被其他事务同时更新

事务回滚的实现

• 回滚段(rollback segment)与数据前像

3.2-存储引擎概述

MySQL程序层次架构

./sorence/images/01.jpg

MySQL存储引擎

• 有多种可选方案，可插拔，可修改存储引擎
• 基于表选择使用何种存储引擎

主要存储引擎

存储引擎	常用度	支持事务
InnoDB	主要，推荐	是
MyISAM	古老，偶尔有用，系统表	否
MEMORY	偶尔临时表有用，纯内存	否
BLACKHOLE	不用来存放数据，个别特殊用处	否
TokuDB	新颖，个别特殊场景有奇效	是
Cluster	新颖，分布式，内存，线上不要用	是

InnoDB存储引擎

• 索引组织表
• 支持事务
• 支持行级锁
• 数据块缓存
• 日志持久化
• 稳定可靠，性能好，线上尽量使用InnoDB

MyISAM存储引擎

• 堆表
• 不支持事务
• 只维护索引缓存池，表数据缓存交给操作系统
• 锁粒度较大
• 数据文件可以直接拷贝，偶尔可能会用上
• 不建议线上业务数据使用

MWMORY存储引擎

• 数据全内存存放，无法持久化
• 性能较高
• 不支持事务
• 适合偶尔作为临时表使用
• create temporary table tmp (id int) engine = memory ;

BLACKHOLE存储引擎

• 数据不作任何存储
• 利用MySQL Replicate，充当日志服务器
• 在MySQL Replicate环境中充当代理主

TokuDB

• 分形树存储结构
• 支持事务
• 行锁
• 压缩效率较高
• 适合大批量insert的场景

MySQL Cluster

• 多主分布式集群
• 数据节点间冗余，高可用
• 支持事务
• 设计上易于扩展
• 面向未来，线上慎用

改变表的存储引擎

alter table m ENGINE=innodb;

3.3-InnoDB存储引擎

InnoDB存储引擎体系架构

/sorence/images/02.png

InnoDB相关的磁盘文件

文件	名称	数量	位置
系统表空间	ibdata1	一个实例一个	innodb_data_home_dir
日志文件	ib_logfile0/1	一个实例两个(可配置)	innodb_log_group_home_dir
表定义文件	表名.frm	每张表一个	Schema目录下
表数据文件	表名.ibd	如果innodb_file_per_table = 1, 则每张表一个	Schema目录下

InnoDB系统表空间文件

• ibdata1里存放了什么:
  • 回滚段
  • 所有InnoDB表元数据信息
  • Double Write, Insert buffer dump等等....
• 自动扩展机制

InnoDB与磁盘文件有关的参数

参数	样例值	备注
innodb_data_home_dir	/data/mysql/node_1	数据主目录
innodb_log_group_home_dir	/data/mysql/node_1	一般同上
innodb_data_file_path	ibdata1:512M:autoextned	请开启autoextned
innodb_autoextend_increment	128	MB,勿太大或太小
innodb_file_per_table	1	强烈建议开启
innodb_log_file_size	100MB	性能相关
innodb_log_files_in_group	2	性能相关

InnoDB数据文件存储结构

• 索引组织表(聚簇表)
• 根据表逻辑主键排序
• 数据节点每页16K
• 根据主键寻址速度很快
• 主键值递增的insert插入效率较好
• 主键值随机insert插入效率差
• 因此，InnoDB表必须指定主键，建议使用自增数字

InnoDB数据块缓存池

• 数据的读写需要经过缓存
• 数据以整页(16K)为单位读取到缓存中
• 缓存中的数据以LRU策略换出
• IO效率高，性能好

InnoDB Buffer Pool相关参数

参数	样例值	备注
innodb_buffer_pool_size	10G	根据总物理内存设置

InnoDB数据持久化与事务日志

• 事务日志实时持久化
• 内存变化数据(脏数据)增量异步刷出到磁盘
• 实例故障靠重放日志恢复
• 性能好，可靠，恢复快

InnoDB日志持久化相关参数

参数	样例值	备注
innodb_flush_log_at_trx_commit	1	可选：0：每隔1s写入并持久化一次日志。1：每次commit都写入并持久化日志。2：每次提交日志写到内存，每1s持久化一次

InnoDB行级锁

• 写不阻塞读
• 不同行间的写互相不阻塞
• 并发性能好

InnoDB与事务ACID

• 事务ACID特性完整支持
  • 回滚段失败回滚
  • 支持主外键约束
  • 事务版本+回滚段=MVCC
  • 事务日志持久化
• 默认可重复读隔离级别，可以调整

3.4-InnoDB事务锁

什么是计算机程序锁

• 计算机程序锁
  • 控制对共享资源进行并发访问
  • 保护数据的完整性和一致性

数据库中的锁

• 分为两个大类

	lock	latch/mutex
对象	事务	线程
保护	数据库逻辑内容	内存数据结构
持续时间	事务过程中	临界资源争抢

• 我们主要关心的是事务锁

数据库事务并发

• 对同一行记录的修改必须串行化

事务锁粒度

• 行锁
  • InnoDB, Oracle
• 页锁
  • SQL Server
• 表锁
  • MyISAM, Memory
• 锁升级

InnoDB存储引擎中的锁模式与粒度

• 四种基本锁模式
  • 共享锁(S) - 读锁 - 行锁
  • 排他锁(X) - 写锁 - 行锁
  • 意向共享锁(IS) - 表级
  • 意向排他锁(IX) - 表级
• 意向锁
  • 意向锁总是自动先加，并且意向锁自动加自动释放
  • 意向锁提示数据库这个session将要在接下来施加何种锁
  • 意向锁和X/S锁级别不同，除了阻塞全表级别的X/S锁外其他任何锁

InnoDB锁模式互斥

/sorence/images/03.png

数据库加锁操作

• 一般的select语句不加任何锁，也不会被任何事物锁阻塞
  • 读的隔离性由MVCC确保
• S锁
  • 手动：select * from tb_test lock in share mode;
  • 自动：insert前
• X锁
  • 手动：select * from tb_test lock for update;
  • 自动：update，delete前

InnoDB行锁的实现

• 通过索引项加锁实现
  • 只有条件走索引才能实现行级锁
  • 索引上有重复值，可能锁住多个记录
  • 查询有多个索引可以走，可以对不同索引加锁
  • 是否对索引加锁实际上取决于MySQL执行计划
• 自增主键做条件更新，性能最好

没有索引的话会对整张表加锁。

InnoDB的gap lock

• 什么是幻读
• gap lock消灭幻读
  • InnoDB消灭幻读仅仅为了确保statement模式replicate的主从一致性
• 小心gap lock
• 自增主键做条件更新，性能最好

死锁

• 什么是死锁

  • A、B两个事务，A先更新t1，同时B更新t2，A再更新t2，B再更新t1就发生了死锁。

• 死锁数据库自动解决

  • 数据库挑选冲突事务中回滚代价较小的事务回滚

• 死锁预防

  • 单表死锁可以根据批量更新里的更新条件排序
  • 可能冲突的跨表事务尽量避免并发
  • 尽量缩短事务长度

业务逻辑加锁

• 业务流程中的悲观锁

  • 任何的并发修改都有可能造成我们的业务逻辑最终的错误，在事务流程中一开始就加锁，最后释放

• 如何缩短锁的时间