文章目录
- 通过重做日志和回滚日志保证事务的原子性与一致性
- 正常情况
- 做到一半发现逻辑不对的情况
- 提交事务之前断电、系统崩溃的情况
- 提交事务以后马上断电、系统崩溃的情况
- 总结
- 如何理解事务的一致性?
- 如何理解事务的隔离性?
- READ UNCOMMITTED
- READ COMMITTED
- REPEATABLE READ
- SERIALIZABLE
- 设计表的关键思想:
- 在一对多关系时
- 在多对多关系时
- 设计表时需要遵守的几大范式
- MySQL 设计的原则:
- 核心原则
- 字段类原则
- 索引类原则
- SQL类原则
通过重做日志和回滚日志保证事务的原子性与一致性
假设有一个Account表,记录了用户的余额,对Account表有以下操作
-- 模拟用户1给用户2转了500块钱:
1. START TRANSACTION;
2. UPDATE Account SET balance = balance - 500 WHERE id = 1;
3. UPDATE Account SET balance = balance + 500 WHERE id = 2;
4. COMMIT;
当数据库处理到命令1
的时候,会在内存中生成两个日志:
- 重做日志(redo log),用于按顺序记录所有操作。
- 回滚日志(undo log),用于逆顺序,记录所有操作的逆操作。
这两个日志的作用就是保持事务的原子性,怎么保持呢?
正常情况
先说正常情况下重做日志和回滚日志是怎样工作的。
- 当执行到
命令2
,数据库会从磁盘把Account表的对应数据加载到内存,然后对内存中的balance执行减少500的操作。注意,这个时候磁盘中的数据是不受影响的。
- 在执行完减少500这个操作后,重做日志会被加上这句命令。
- 而回滚日志会被加上这句命令的逆命令,也就是给id为1的客户余额加上500:
UPDATE Account SET balance = balance +500 WHERE id =1
- 当执行到
命令3
,数据库又会从磁盘把对应的数据加载到内存,并进行修改。操作完成之后给重做日志和回滚日志加上对应的命令。 - 当执行到
命令4
,数据库会先把重做命令写入磁盘,然后再开始按照顺序,把内存的值存到磁盘中,每成功存完一条,就会把已经在磁盘中的重做日志对应的命令做标记,说明这一条命令已经执行完成。
- 当重做日志所有命令都被标记已完成,则重做日志已经完成使命,数据库会视情况删除。
- 回滚日志也会被存入磁盘,他的使命也已经完成,数据库会视情况删除。
注意:因为重做日志的特殊处理,所以重做日志只会被完整写入磁盘,或者全部都不被写入,不会出现只写了一半还有一半没写的情况。
InnoDB中,重做日志都是以512B的块形式储存的,因为磁盘的扇取也是512B,所以重做日志的写入就保证了原子性,即便机器断电也不会出现日志仅仅写入一半而留下脏数据的情况。
做到一半发现逻辑不对的情况
假如命令变成这样:钱转到一半发现有问题,不能转了!
-- 模拟用户1给用户2转了500块钱,转到一半java调用回滚:
1. START TRANSACTION;
2. UPDATE Account SET balance = balance - 500 WHERE id = 1;
3. ROLLBACK;
当执行到命令3
要求回滚,则会把回滚日志的逆命令从头到尾执行:
UPDATE Account SET balance = balance + 500 WHERE id=1;
这样就可以让内存变回开始事务之前的状态了。
== 注意 ==
执行回滚操作的命令,也是会在重做日志中记录的。以保证执行存盘命令的正确:
不把回滚操作命令写入重做日志会发生如下问题:
1. 用户1的钱少了500,写入重做日志
2. 回滚,用户1的钱加上500,不写入重做日志
3. 重新开始事务,用户1的钱少了200,写入重做日志
4. 用户2的钱多了200,写入重做日志
5. 提交事务,把重做日志从内存存进磁盘,
6. 根据重做日志把内存数据写进硬盘:
用户1的钱少500,
用户1的钱少200,
用户2的钱多200.
结果就是用户1平白无故少了500块钱。所以执行回滚操作的命令也得写入重做日志的。
提交事务之前断电、系统崩溃的情况
内存数据消失,磁盘没有任何写入操作,一切自动回归初始状态。
提交事务以后马上断电、系统崩溃的情况
回看正常情况中的第3步:
当执行到
命令4
,数据库会先把重做命令写入磁盘,然后再开始按照顺序,把内存的值存到磁盘中,每成功存完一条,就会把已经在磁盘中的重做日志对应的命令做标记,说明这一条命令已经执行完成。
如果数据库一开始运行,发现重做日志有还没标记为已完成的命令,数据库就会先执行这些任务,并把数据存进磁盘,依然是每执行一条,做一条的标记。这样就能保证事务只要提交了就一定能完成。
总结
事务的原子性比较好理解,事务的操作要不一起成功,要不一起失败,具体来说就是
- 利用回滚日志保证任务失败以后,包括内存和硬盘,都能恢复到之前的状态。
- 及利用重做日志,保证已提交的事务一定会被写入硬盘。
如何理解事务的一致性?
那么事务的一致性又如何理解呢?可以用以下一句话概括:
事务开始和结束之间的中间状态不会被其他事务看到,
既然事务是原子的,对程序和用户来说,无论由多少行语句组成的事务,都应该只是一个操作。无论在事务进行到哪个阶段,别的程序查询到的事务所处理的数据,都应该只有两个状态:
- 事务执行之前的状态
- 事务执行以后的状态
在事务期间,数据处于不一致的状态,在事务没开始之前,或者事务已经结束以后,数据才会处于一致的状态。
说得具体一点,回到之前的例子:
-- 模拟用户1给用户2转了500块钱:
1. START TRANSACTION;
2. UPDATE Account SET balance = balance - 500 WHERE id = 1;
3. UPDATE Account SET balance = balance + 500 WHERE id = 2;
4. COMMIT;
假设用户1和用户2本来各有1000元,现在当事务执行完命令2,还没执行到命令3,就有线程访问这两个用户的balance数值:
- 如果线程看到的是用户1:500元,用户2:1000元,那事务就是不一致的,因为事务中间的过程被看到了。
- 如果通过某种方法,让这个访问balance的线程看到的数值是原来的数据。那么就可以认为这个事务实现了一致性。因为别人永远只能看到事务执行之前和事务执行之后两种状态。
- 通常把事务隔离级别设置到
read committed
或者以上,就能保证事务的一致性了。
如何理解事务的隔离性?
如果把事务的所有命令,都看成一个命令,那么在事务过程中,他所接触到的数据,应该是一样的,说具体点就是:
在同一个事务的任何位置调用
select *
,都应该读到一样的信息。
然而数据库针对不同的情况为事务的隔离性做保证。
随着隔离等级的提高,数据库的效率会逐渐降低。
READ UNCOMMITTED
- 在一定情况下(事务隔离等级为
read uncommitted
)对同一个表的操作,不同的事务会互相干扰。
-- 模拟同一个事务中查询两次数值
1. START TRANSACTION;
2. SELECT balance FROM account WHERE id=1; -- 查看用户1的余额
3. -- 一大串操作,但是没有操作用户1的balance
4. SELECT balance FROM account WHERE id=1; -- 查看用户1的余额
5. commit;
这时,如果在命令3执行的期间,有其他事务操作了用户1的balance,命令4获得的会是被其他线程编辑过的信息,自然跟命令2获得的信息不一样。这就叫脏读:
一个事务能读取到另外一个事务没提交的数据。
很明显这就有违事务的隔离性。而READ UNCOMMITTED也是隔离等级最低的一类。
如果我们的事物都是查询,完全不涉及增删改,用READ UNCOMMITTED是处理事务效率是最高的的。
READ COMMITTED
- 把事务隔离等级设为**READ COMMITTED,**可以防止脏读。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED
把事务隔离等级设为READ COMMITTED以后,事务内部就不能读到其他事务未提交的信息,具体是什么原理我也不知道。但是如果其他事务提交了,本事务就又会读到不同的数据了。这叫不可重复读。
一个事务能读取到另一个事务提交过的数据。
REPEATABLE READ
- 把事务隔离等级设为REPEATABLE READ的话,可以防止不可重复读。
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ
把事务隔离等级设为REPEATBALE READ之后,事务内部就不能读到其他事务update后commit的数据了(虽然update是实时发生的,但数据库通过某种技术让事务读不到新的数据)。一般用这个就能已经能保证事物的一致性了。
但如果外部事务要涉及增删操作,还是会影响到本事务。
SERIALIZABLE
- 把事务隔离等级设为SERIALIZABLE能防止一切导致事务不一致的问题
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE
但是代价就非常慢。一般不用的,最多就REPEATABLE READ就行了。
设计表的关键思想:
在一对多关系时
当两个表关系为一对多,例如一个部门有多个员工,那么应该在“多”一方增加外键,指向"一"方
在多对多关系时
当两个表关系为多对多,例如多个教师教育多个学生,应该在新建一个中间表来维护关系,中间的这个表有两个外键,一个指向A表,另一个指向B表,而且这两个外键应该组成联合主键。
设计表时需要遵守的几大范式
- 范式1:每个数据都是原子数据,不能再分割
- 范式2:每个表只讲一件事,每一行都有主键
- 范式3:外键只能引用另一个表的主键
- 一般数据库设计能遵守这三个范式就已经很好了,但是经常要在数据冗余和范式之间做平衡:
- 比如一个人的姓名严格来说其实还能拆分为 “姓” 和 "名"两部分,把两个属性组合起来可能是有违范式1:数据原子性的,但是实际工作中还是会把姓名放在一起。
- 比如无限分类表,把所有可分类的内容集合在一张表,也是有点违背每个表只讲一件事的要求,但我们就经常这样做了。
- 总结:我们要在数据冗余和范式之间取好平衡,把范式要求为我们所用。
MySQL 设计的原则:
核心原则
- 不在数据库做运算;
- cpu计算务必移至业务层;
- 控制列数量(字段少而精,字段数建议在20以内);
- 平衡范式与冗余(效率优先;往往牺牲范式)
- 拒绝3B(拒绝大sql语句:big sql、拒绝大事物:big transaction、拒绝大批量:big batch);
字段类原则
- 用好数值类型(用合适的字段类型节约空间);
- 字符转化为数字(能转化的最好转化,同样节约空间、提高查询性能);
- 避免使用NULL字段(NULL字段很难查询优化、NULL字段的索引需要额外空间、NULL字段的复合索引无效);
- 少用text类型(尽量使用varchar代替text字段)
索引类原则
- 合理使用索引(改善查询,减慢更新,索引一定不是越多越好);
- 字符字段必须建前缀索引;
- 不在索引做列运算;
- innodb主键推荐使用自增列(主键建立聚簇索引,主键不应该被修改,字符串不应该做主键)(理解Innodb的索引保存结构就知道了);
- 不用外键(由程序保证约束)
SQL类原则
- sql语句尽可能简单(一条sql只能在一个cpu运算,大语句拆小语句,减少锁时间,一条大sql可以堵死整个库);
- 简单的事务;
- 避免使用trig/func(触发器、函数不用客户端程序取而代之);
- 不用select (消耗cpu,io,内存,带宽,这种程序不具有扩展性);
- OR改写为IN(or的效率是n级别);
- OR改写为UNION(mysql的索引合并很弱智);
SELECT id FROM t WHERE phone = ’159′ or name = ‘john’;
=>
SELECT id FROM t WHERE phone=’159′
UNION
SELECT id FROM t WHERE name=’jonh’
- 避免负向%;
- 慎用count(*);
- limit高效分页(limit越大,效率越低);
- 使用union all替代union(union有去重开销);
- 少用连接join;
- 使用group by;
- 请使用同类型比较;
- 打散批量更新;