mysql 第1章

1、引入

• mysql 是我们很熟悉的数据库系统，我们对于mysql的了解有哪些？
• 如果让我们设计一个数据库系统我们会如何考虑？
• 能不能就mysql 的一个方面有一个较深入的了解？

2、还原mysql 面貌

2.1mysql 逻辑架构

mysql 的系统架构的分层：

第一层是大多数基于网络的客户端/服务器的工具或者服务都有的架构，用于处理网络请求的，连接处理，授权认证，安全等。

第二层是mysql的核心服务功能，包括查询解析、分析、优化、缓存以及所有的内置函数，所有跨存储引擎的功能都在这一层实现。

第三层包含了存储引擎，存储引擎负责了msyql 中的数据存储和提取。服务器通过API与存储引擎进行通信，存储引擎API包含了几十个底层函数，用于执行诸如开始一个事务。

• 总结 分层、抽象隔离架构设计

3、mysql事务

• 什么是事务？ 数据库为什么要支持事务？

例如：银行转账，A给B转账，A账号余额减少，B账号余额增多。这两个操作一定是同时成功或者同时失败。

我们有很多的业务都具有类似的特性，例如各种交易系统，电商系统，下单、库存、减余额，以及我们所有涉及多表操作的都需要事务管理。

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
• 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
• 事务用来管理 insert,update,delete 语句

事务有哪些特性？

• 事务的四大特性：原子性（A）、一致性（C）、隔离性（I）、持久性（D）

3.1原子性

• 原子性：同时成功或者同时失败，也就意味着需要保证一致，这与一致性也是相辅相成，那么靠什么来保证原子性呢？ (undo log、redo log)
• Undo Log的原理很简单，为了满足事务的原子性，在操作任何数据之前，首先将数据备份到Undo Log。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
• 数据库写入数据到磁盘之前，会把数据先缓存在内存中，事务提交时才会写入磁盘中。
• 用Undo Log实现原子性和持久化的事务的简化过程：
• 假设有A、B两个数据，值分别为1,2。 A. 事务开始. B. 记录A=1到undo log. C. 修改A=3. D. 记录B=2到undo log. E. 修改B=4. F. 将undo log写到磁盘。 G. 将数据写到磁盘。 H. 事务提交
• 如何保证持久性？
• 事务提交前，会把修改数据到磁盘前，也就是说只要事务提交了，数据肯定持久化了。
• 如何保证原子性？
• 每次对数据库修改，都会把修改前数据记录在undo log，那么需要回滚时，可以读取undo log，恢复数据。
• 若系统在G和H之间崩溃
• 此时事务并未提交，需要回滚。而undo log已经被持久化，可以根据undo log来恢复数据
• 若系统在G之前崩溃
• 此时数据并未持久化到硬盘，依然保持在事务之前的状态
• 缺陷：每个事务提交前将数据和Undo Log写入磁盘，这样会导致大量的磁盘IO，因此性能很低。
• 如果能够将数据缓存一段时间，就能减少IO提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即Redo Log.
• 和Undo Log相反，Redo Log记录的是新数据的备份。在事务提交前，只要将Redo Log持久化即可，不需要将数据持久化，减少了IO的次数。
• 先来看下基本原理：
• Undo + Redo事务的简化过程
• 假设有A、B两个数据，值分别为1,2
• A. 事务开始. B. 记录A=1到undo log buffer. C. 修改A=3. D. 记录A=3到redo log buffer. E. 记录B=2到undo log buffer. F. 修改B=4. G. 记录B=4到redo log buffer. H. 将undo log写入磁盘 I. 将redo log写入磁盘 J. 事务提交
• 安全和性能问题
• 如何保证原子性？
• 如果在事务提交前故障，通过undo log日志恢复数据。如果undo log都还没写入，那么数据就尚未持久化，无需回滚
• 如何保证持久化？
• 大家会发现，这里并没有出现数据的持久化。因为数据已经写入redo log，而redo log持久化到了硬盘，因此只要到了步骤I以后，事务是可以提交的。
• 内存中的数据库数据何时持久化到磁盘？
• 因为redo log已经持久化，因此数据库数据写入磁盘与否影响不大，不过为了避免出现脏数据（内存中与磁盘不一致），事务提交后也会将内存数据刷入磁盘（也可以按照固设定的频率刷新内存数据到磁盘中）。
• redo log何时写入磁盘
• redo log会在事务提交之前，或者redo log buffer满了的时候写入磁盘
• 这里存在两个问题：
• 问题1：之前是写undo和数据库数据到硬盘，现在是写undo和redo到磁盘，似乎没有减少IO次数
• 数据库数据写入是随机IO，性能很差
• redo log在初始化时会开辟一段连续的空间，写入是顺序IO，性能很好
• 实际上undo log并不是直接写入磁盘，而是先写入到redo log buffer中，当redo log持久化时，undo log就同时持久化到硬盘了。
• 因此事务提交前，只需要对redo log持久化即可。
• 另外，redo log并不是写入一次就持久化一次，redo log在内存中也有自己的缓冲池：redo log buffer。每次写redo log都是写入到buffer，在提交时一次性持久化到磁盘，减少IO次数。
• 问题2：redo log 数据是写入内存buffer中，当buffer满或者事务提交时，将buffer数据写入磁盘。
• redo log中记录的数据，有可能包含尚未提交事务，如果此时数据库崩溃，那么如何完成数据恢复？
• 数据恢复有两种策略：
• 恢复时，只重做已经提交了的事务
• 恢复时，重做所有事务包括未提交的事务和回滚了的事务。然后通过Undo Log回滚那些未提交的事务
• Inodb引擎采用的是第二种方案，因此undo log要在 redo log前持久化

3.2一致性

• 一致性转账的最终结果要不就是A扣款成功，B加款成功，要不就是A没有扣款，B没有增加。

3.3隔离性

• 隔离性指的是多个事务之间不会相互影响，例如同时开启了事务A,事务B,我们相要在这两个不同的事务之间保证数据隔离。
• 脏读、不可重复读、幻读
• 脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

事务B读到了为提交的数据

• 不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

事务A在同一个事务内读取同一条数据，由于其他事务提交了对改数据的修改，导致事务A获得了不同的结果,对于这种可称为不可重复读

• 幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

• mysql 隔离级别

READ UNCOMMITTED（读未提交）
READ COMMITTED （读提交）
REPEATABLE READ （可重复读）
SERIALIZABEL （串行化）

• mysql 隔离级别查询命令

//关闭事务自动提交
set @@autocommit=0;

//查看当前会话隔离级别
select @@tx_isolation;

//查看系统当前隔离级别
select @@global.tx_isolation;

//设置当前会话隔离级别
set session transaction isolation level 隔离级别

//设置系统隔离级别
set global transaction isolation level 隔离级别

• mysql 默认的隔离级别 REPEATABLE READ
• 
  

不会发生幻读

会发生幻读

• 隔离性的实现（锁机制）
• 表锁、行锁 、间隙锁
• 乐观锁 、 悲观锁
• 排他锁（X）、共享锁（S）、 写锁 、 读锁
• MVCC
• 以mysql 的默认隔离级别为例
• 行锁

• 表锁

锁住的是索引，当where条件没有索引时，会使用表锁，将整张表锁住。

• 间隙锁
• mysql 的间隙锁控制参数
• innodb_locks_unsafe_for_binlog #这个参数默认值是OFF， 也就是启用间隙锁

• 悲观锁
• 悲观锁，是一种保守做法，可以理解为想法比较保守，认为每次都有可能会发生并发冲突，所以在执行操作之前先上锁。
• select ....for update
• 乐观锁
• 乐观锁和悲观锁相反，乐观锁通常是一种不加锁的方式 ,利用版本号比较预期值也叫做CAS，比较并交换，通过利用版本号，每次都先获取版本号，将需要操作的记录和版本号比较，如果一致则执行，否则一直自旋等待。

for { 
user_t = select * from user_t where userId = "1" 
version = user_t.Version + 1 update set college_id = "04",version = version + 1 where version = user_t.Version and id = user_t.Id  
if ok { break }
}

• 写锁（排他锁、X锁）

lock tables user_t write;
update user_t set userName = "张三" where userId = "21";
unlock tables;

• 最后一定要释放锁
• 读锁（共享锁、S锁）

lock tables user_t read;
update user_t set userName = "t" where userId = "1"; -- ERROR 1099 (HY000): Table 'user_t' was locked with a READ lock and can't be updated
unlock tables;

• MVCC
• 生效的隔离级别（RC、RR）
• 多版本并发控制，通过事务id+回滚指针，以及undolog

3.4 持久性

• 持久性通过最终持久化到磁盘，数据可以长久的保存，mysql数据回滚依赖什么? binlog
• binglog的作用： 主从复制、持久化

4、尝试使用事务

4.1事务示例

-- 开启事务
begin;
-- 操作。。。
-- insert update delete
-- 回滚或提交
rollback;
or commit;

-- 建表
CREATE TABLE `account` (
  `id` bigint(20) NOT NULL COMMENT '主键',
  `account` varchar(64) NOT NULL COMMENT '账号',
  `money` bigint(255) DEFAULT NULL COMMENT '余额',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

// A->B 转账
//开启
tx := Db.Begin()
where := map[string]interface{}{"account":"A"}
account,err := Query(tx,where)
if err != nil || account.Money < 800 {
    //回滚
    tx.Rollback()
    return
}
err = tx.Debug().Table("account").Where("account","A").Update("money",gorm.Expr("money - ?",800)).Error
if err != nil {
    //回滚
    tx.Rollback()
}
err = tx.Debug().Table("account").Where("account","B").Update("money",gorm.Expr("money + ?",800)).Error
if err != nil {
    //回滚
    tx.Rollback()
}
//提交
tx.Commit()

假设有多个人需要给同一个用户转账，并且采用的方式是先查的这个人的余额，然后更新。此时会有什么问题？

例如我们业务中的名单，value值更新。

并发操作一行，正常来说只更新一行会给该行加行级锁，但是需要where 语句有索引。

//方式1 不做任何处理
for i := 0;i < 100; i++ {
    go func() {
        acc := model.Account{}
        Db.Debug().Table("account").Where("account","B").Find(&acc)
        money := acc.Money + 800
        Db.Debug().Table("account").Where("account","B").Update("money",money)
    }()
}
//方式2 事务
for i := 0;i < 100; i++ {
    go func() {
        tx := Db.Begin()
        acc := model.Account{}
        tx.Debug().Table("account").Where("account","B").Find(&acc)
        money := acc.Money + 800
        tx.Debug().Table("account").Where("account","B").Update("money",money)
        tx.Commit()
    }()
}
//方式3 数据库加锁select for update
for i := 0;i < 100; i++ {
    go func() {
        //select for update
        tx := Db.Begin()
        var acc  model.Account
        if err := tx.Debug().Select("money").Set("gorm:query_option", "FOR UPDATE").First(&acc, 2).Error;err != nil {
            tx.Rollback()
        }
        money := acc.Money + 800
        err := tx.Debug().Table("account").Where(map[string]interface{}{"account":"B"}).Update("money",money).Error
        if err != nil {
            tx.Rollback()
        }
        if err := tx.Commit().Error;err != nil {
            tx.Rollback()
        }
    }()
}
//方式4 程序加锁
var muLock sync.Mutex
for i := 0;i < 100; i++ {
    go func() {
        muLock.Lock()
        acc := model.Account{}
        Db.Debug().Table("account").Where("account","B").Find(&acc)
        money := acc.Money + 800
        Db.Debug().Table("account").Where("account","B").Update("money",money)
        muLock.Unlock()
    }()
}
//方式5 分布式锁

4.2mysql 死锁

死锁：对方都需要自己持有的资源，但是自己都不愿意释放该资源，从而导致循环等待。

mysql 死锁的示例

A B 都在等待对方的行锁释放。

在RR隔离级别下，存在间隙锁，B锁住了32以后的，A锁住了31，B需要A 的间隙锁，A需要B 31的间隙锁。

避免死锁方法：

加锁时机，程序层面解决，悲观锁、合适的索引，优化查询条件。

5、分布式事务

2PC (两段式提交)

6、总结

数据库系统应该支持哪些需求

mysql 事务的概念以及特性

ACID 实现原理

mysql 隔离级别、锁机制、死锁

扩展展望，分布式事务