浅谈MySQL的一些原理与建表原则

最新推荐文章于 2022-09-30 11:28:41 发布

小鹏头

最新推荐文章于 2022-09-30 11:28:41 发布

阅读量526

点赞数

分类专栏：学习笔记文章标签： MySQL 数据库建表学习笔记

本文链接：https://blog.csdn.net/q876651731/article/details/88935742

版权

学习笔记专栏收录该内容

7 篇文章 0 订阅

订阅专栏

通过重做日志和回滚日志保证事务的原子性与一致性

假设有一个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;
请使用同类型比较;
打散批量更新;

小鹏头

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
浅谈MySQL的一些原理与建表原则

假设有一个Account表，记录了用户的余额，对Account表有以下操作
复制链接

扫一扫

专栏目录