慕课网MySQL教程
总览
总结
1. 数据更新时的日志问题
1.1 MySQL日志体系
1.2.1 binlog 归档日志
1.2.2 undo Log 回滚日志
- 更新时,写入一个反的sql语句日志,当需要回滚时,操作该日志可以实现回滚效果
1.2.3 redo Log 重做日志
- 写入环形磁盘时机通过参数进行控制
- redo Log 先更新在内存,当查询数据时,先查询内存,因此不会查询磁盘中旧的数据
1.2 数据更新流程
1.2.1 整体流程
-
每次对数据操作,都要先将磁盘中数据读入内存进行操作
-
每次都是对内存中的数据进行操作,写回磁盘是异步操作
-
日志优先,只要redo Log 写进去了,事务就写进去了,就可以 prepare ,可以进行下步提交了
-
但有个问题,都是写入到内存,如果断电,数据会丢,这就涉及到redo Log 刷盘操作
1.2.2 redo Log 刷盘
- 通过参数设置,将redo Log 写进环形磁盘中的频率
- 异步每秒刷盘,有可能丢失1秒中的更新事务请求
1.2.3 binLog 刷盘
1.2.4 持久化分析
1.2.5 为何redo Log 在binLog之前
- redo Log 决定事务数据是否准备好了,一旦redo Log写入,表明事务已经准备好了,数据已经更新了
- binLog涉及主从复制,不能撤销,只有当redo Log 确定事务已经更新了,才能复制备份,否则,顺序相反的话,数据复制到备库,主库回滚了或者事务更新失败了,redo Log没有写入,会导致主从数据不一致
1.3 常见面试题
更新流程和两日志问题
两阶段提交问题
2. MySQL锁
2.1 锁的种类
2.1.1 全局锁
2.2.2 表锁
2.2.3 元数据锁
2.2.4 行锁
3. 事务
3.1 事务特性
3.1.1 原子性
3.1.2 一致性
-
一致性是指事务执行前后,数据从一个 合法性状态变换到另外一个 合法性状态 。
-
满足 预定的约束 的状态就叫做合法的状态
-
满足了约束即满足了一致性
3.1.3 隔离性
3.1.4 持久性
3.2 隔离级别
常见的并发异常
- 在典型的应用程序中,多个事务并发运行,
- 经常会操作相同的数据来完成各自的任务(多个用户(线程)对统一数据进行操作)。
- 并发虽然是必须的,但可能会导致以下的问题。
1.丢失修改(脏写)
对于两个事务 Session 1、Session 2,访问同一条数据
一个事务 Session1在提交之前,另一个事务Session2对这条数据写了,就是脏写,对这条数据读了,就是脏读
- 指在一个事务读取一个数据还未提交时,另外一个事务也访问了该数据,
- 那么在事务2修改了这个数据后,事务1最终也修改了这个数据并提交或回滚。
- 这样事务2修改的数据会丢失,那么事务2写的这个数据是“脏数据”, 依据“脏数据”所做的操作可能是不正确的。
第一类丢失更新
-
某一个事务的回滚,导致另外一个事务已更新的数据丢失了
- 例如:由于事务1的回滚,导致事务2的更新无效,丢失了
第二类丢失更新
-
某一个事务的提交,导致另外一个事务已更新的数据丢失了。
- 例如:由于事务1的提交,导致事务2的更新无效,丢失了
读的问题
2.脏读( Dirty Read )
某一个事务,读取了另外一个事务未提交的数据。
- 当一个事务Session 1 正在访问数据并且对数据进行了修改,而这种修改还没有提交到数据库中,
- 这时另外一个事务Session 2 也访问了这个数据,然后读取了这个没提交的数据。
- 因为这个数据是还没有提交的数据, 那么另外一个事务读到的这个数据是“脏数据”, 依据“脏数据”所做的操作可能是不正确的。
3.不可重复读( Non-Repeatable Read )
某一个事务,对同一个数据前后读取的结果不一致
- 即事务2读取了数据,事务1将数据更新并提交了,事务2第二次读,数据前后不一致了,就出现问题了
- 这就发生了在一个事务内两次读到的数据是不一样的情况,因此称为不可重复读。 注意:重复只有同一个事务才叫重复,不同事务不叫重复。
3.幻读( Phantom )
某一个事务,对同一个表前后查询到的行数不一致。
- 与不可重复读类似,只是读到的数据行数前后不一致
- 它发生在一个事务( T2)读取了几行数据,
- 接着另一个并发事务( T1) 插入了一些数据时。
- 在随后的查询中,事务( T2)就会发现多了一些原本不存在的记录, 就好像发生了幻觉一样,所以称为幻读。
四种异常的严重程度对比
脏写 > 脏读 > 不可重复读 > 幻读
扩展问题:
脏读和不可重复读的区别:
- 脏读是两个事务读的数据不同,
- 不可重复读是同一事务读取的数据不同。
不可重复度和幻读区别:
- 不可重复读的重点是修改,
- 幻读的重点在于新增或者删除
3.2.1 读未提交
- 一个事务的任何操作,未提交前,另一个事务都能看到,并且能够改变
- 在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。不能避免脏读、不可重复读、幻读。
- 示例
3.2.2 读提交
- 一个事务未提交前,另一个事务不可见,无法读取未提交的更改数据
- 这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)
- 可以避免脏读,但不可重复读、幻读问题仍然存在。
- 示例
- 演示加锁机制
- 事务提交后,锁释放,其他事务才能查看到,并可以拿到写锁进行修改
3.2.3 可重复读
- 事务A在读到一条数据之后,此时事务B对该数据进行了修改并提交,那么事务A再读该数据,读到的还是原来的内容。
- 可以避免脏读、不可重复读,但幻读问题仍然存在。这是MySQL的默认隔离级别。
- 示例
- 如果B事务想查询到最新的数据,需要
commit
,提交事务后才能查询别的事务提交的更新数据,这是为了防止B事务在提交前的不可重复读的情况
3.2.4 串行化
在这个事务持续期间,禁止其他事务对该表执行插入、更新和删除操作。所有的并发问题都可以避免,但性能十分低下。能避免脏读、不可重复读和幻读。
- 示例
四种隔离级别对比和常见问题汇总
- 为了兼顾性能和隔离效果,==一般使用中间的`READ COMMITTED `:读已提交;`REPEATABLE READ `:可重复读==https://www.jianshu.com/p/447a10c7a7f3
- 对于幻读,
- 可以采用乐观锁解决,
- 或者是修改业务,
- 比如调整读取时间为更新比较少的时间段
- 后台统计时,只影响自己,数据有问题,可以再次统计
扩展问题:
- 与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ(可重复读)事务隔离级别下使用的是 Next-Key Lock 锁算法,
- 因此可以避免幻读的产生,这与其他数据库 系 统 ( 如 SQL Server) 是 不 同 的 。 所 以 说 InnoDB 存 储 引 擎 的 默 认 支 持 的 隔 离 级 别 是REPEATABLE-READ(可重复读) 已经可以完全保证事务的隔离性要求,即达到了 SQL 标准的SERIALIZABLE(可串行化)隔离级别。
- 因 为 隔 离 级 别 越 低 , 事 务 请 求 的 锁 越 少 , 所 以 大 部 分 数 据 库 系 统 的 隔 离 级 别 都 是
READ-COMMITTED( 读 取 已 提 交 )
, 但 是InnoDB
存 储 引 擎 默 认 使 用REPEATABLE-READ(可重复读)
并不会有任何性能损失。 - InnoDB 存储引擎在
分布式事务
的情况下一般会用到SERIALIZABLE(可串行化)
隔离级别。
4. MVCC
4.1 MVCC如何做到千人千面
- 每个事务都能读到特定版本的数据
- 主要应用于读可提交和不可重复读的隔离级别
4.1.1 行记录的版本控制
- 事务可以通过记录的undo Log读取之前的版本,可以不用回滚改写数据,只是给当前事务查询
4.1.2 快照读(一致性非锁定读)
- 读已提交:例如事务230将数据更新到d,但还没提交,事务190查询时,要通过undo Log 将上一个历史版本找到返回给事务190
- 可持续读,比如事务160查询时,已经有两个事务将数据更新且没有提交,要通过undo Log查找到事务开始时的版本,即begin之前的版本,name =
b
4.1.3 当前读(一致性锁定读)
- 适用于串行化读
4.2 隔离问题
- 可重复读,读取begin时回滚的旧版本的内容,但是,解决不了新增加的行记录,因为新增加的行记录没有旧的版本,即无法通过redo Log查询到旧版本记录,因此一般无法解决幻读问题
- 但是MySQL通过
间隙锁
等机制,部分解决了幻读的问题
4.2.1 MySQl如何解决幻读问题
- 可重读读加锁时,会同时锁住行锁和行记录左右间隙,例如,查询
age <= 10
的记录时,只能查询到一个,并同时将10左右的间隙锁住,这样,当当前事务没有结束并释放锁前,别的事务就不能在间隙中增加或删减行记录,这样,查询到的行记录都是固定的,避免了幻读的情况
4.2.2 间隙锁相关问题
Next-Key Lock加锁规则
- ·Next-Key的基本单位是:一个间隙和一个行记录,每次同时锁一个基本单位
示例数据表
1. 等值查询间隙锁
- 示例:
- 锁住间隙,其他事务无法插入间隙内的id的行记录
2. 非唯一索引等值锁
3. 主键索引范围锁
4. 非唯一索引范围锁
5. 非索引字段查询
4.3 MySQL刷脏页
4.3.1 脏页的产生
4.3.2 什么是刷脏
4.3.3 为何要刷脏
4.3.4 如何避免被迫刷脏
服务器IO配置
- 测试磁盘IO性能
配置合理的脏页比例上限
4.4 长事务的危害
4.4.1 行级锁长时间无法释放
4.4.2 死锁问题
- 检查的性能开销比较大
- 如果事务太多,可以优化事务的业务逻辑,关掉检查
4.4.3 MDL锁
- 对表的原数据加锁,不能修改表的结构,索引等
- 加了写锁,就不能加读锁
- 即使读锁是兼容的,但因为有了事务B,后面的读锁都入了队列等待,拿不到读锁
4.4.4 如何查看影响性能的锁