数据库MySQL学习系列三三．MYSQL事务与存储引擎
3.1-数据库事务
什么是事务
一系列有序的数据库操作：
o要么全部成功
o要么全部回退到操作前的状态
o中间状态对其他连接不可见
事务的基本操作： | 基本操作 | 说明 | | :------------- | :------------- | | 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)
事务的原子性
包含在事务中的操作要么全部被执行，要么都不执行
中途数据库或应用发生异常，未提交的事务都应该被回滚
事务的一致性
数据的正确性，合理性，完整性
数据一致性应该符合应用需要规则：
o余额不能是负数
o交易对象必须先有账号
o用户账号不能重复
事务的结果需要满足数据的一致性约束
事物的持久性
提交完成的事务对数据库的影响必须是永久性的
o数据库异常不会丢失事务更新
o通常认为成功写入磁盘的数据即为持久化成功
事务的持久化的实现
数据文件持久化
o随机同步刷新(慢)
事务日志持久化与实例恢复
o顺序同步刷新(快) -> 事务日志
o随机异步刷新 -> 磁盘
o事务日志 -> 磁盘(实例恢复)
事务的隔离性
数据库事务在提交完成前，中间的任何数据变化对其他的事务都是不可见的。
数据库隔离现象
隔离现象 描述
脏读(Dirty Read) 事务B读到事务A尚未提交的数据变更
不可重复读(NonRepeatable Read) 事务B读取前后两次读取一条记录之间该记录被事务A修改并提交，于是事务B读到了不一样的结果
幻读(Phantom Read) 事务B按条件匹配到了若干行记录并修改。但是由于修改过程中事务A新插入了符合条件记录，导致B更新完成后发现仍有符合条件却未被更新的记录。
数据库隔离等级
隔离等级 脏读 不可重复读 幻读
未提交读 可能 可能 可能
已提交读 不可能 可能 可能
可重复读 不可能 不可能 可能
可串行化读 不可能 不可能 不可能
MySQL的事务隔离级别
InnoDB默认标记为可重复读
InnoDB并不是标准定义上的课重复读
InnoDB默认在可重复读的基础上避免幻读
MySQL事务隔离级别设置
可在global/session/下个事务，级别分别进行设置
建议使用Read committed(同Oracle)
或者建议使用默认的Repeatable read
set tx_isolation = ''-- 设置隔离级别
事务与并发写
某个正在更新的记录再提交或回滚前不能被其他事务同时更新
事务回滚的实现
回滚段(rollback segment)与数据前像
3.2-存储引擎概述
MySQL程序层次架构
我们提供的服务有：成都网站设计、成都做网站、微信公众号开发、网站优化、网站认证、垦利ssl等。为上千多家企事业单位解决了网站和推广的问题。提供周到的售前咨询和贴心的售后服务，是有科学管理、有技术的垦利网站制作公司
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存储引擎体系架构
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里存放了什么:
o回滚段
o所有InnoDB表元数据信息
oDouble 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特性完整支持
o回滚段失败回滚
o支持主外键约束
o事务版本+回滚段=MVCC
o事务日志持久化
默认可重复读隔离级别，可以调整
3.4-InnoDB事务锁
什么是计算机程序锁
计算机程序锁
o控制对共享资源进行并发访问
o保护数据的完整性和一致性
数据库中的锁
分为两个大类
lock latch/mutex
对象 事务 线程
保护 数据库逻辑内容 内存数据结构
持续时间 事务过程中 临界资源争抢
我们主要关心的是事务锁
数据库事务并发
对同一行记录的修改必须串行化
事务锁粒度
行锁
oInnoDB, Oracle
页锁
oSQL Server
表锁
oMyISAM, Memory
锁升级
InnoDB存储引擎中的锁模式与粒度
四种基本锁模式
o共享锁(S) - 读锁 - 行锁
o排他锁(X) - 写锁 - 行锁
o意向共享锁(IS) - 表级
o意向排他锁(IX) - 表级
意向锁
o意向锁总是自动先加，并且意向锁自动加自动释放
o意向锁提示数据库这个session将要在接下来施加何种锁
o意向锁和X/S锁级别不同，除了阻塞全表级别的X/S锁外其他任何锁
InnoDB锁模式互斥
数据库加锁操作
一般的select语句不加任何锁，也不会被任何事物锁阻塞
o读的隔离性由MVCC确保
S锁
o手动：select from tb_test lock in share mode;
o自动：insert前
X锁
o手动：select from tb_test lock for update;
o自动：update，delete前
InnoDB行锁的实现
通过索引项加锁实现
o只有条件走索引才能实现行级锁
o索引上有重复值，可能锁住多个记录
o查询有多个索引可以走，可以对不同索引加锁
o是否对索引加锁实际上取决于MySQL执行计划
自增主键做条件更新，性能最好
没有索引的话会对整张表加锁。
InnoDB的gap lock
什么是幻读
gap lock消灭幻读
oInnoDB消灭幻读仅仅为了确保statement模式replicate的主从一致性
小心gap lock
自增主键做条件更新，性能最好
死锁
什么是死锁
oA、B两个事务，A先更新t1，同时B更新t2，A再更新t2，B再更新t1就发生了死锁。
死锁数据库自动解决
o数据库挑选冲突事务中回滚代价较小的事务回滚
死锁预防
o单表死锁可以根据批量更新里的更新条件排序
o可能冲突的跨表事务尽量避免并发
o尽量缩短事务长度
业务逻辑加锁
业务流程中的悲观锁
o任何的并发修改都有可能造成我们的业务逻辑最终的错误，在事务流程中一开始就加锁，最后释放
如何缩短锁的时间
