重新学习MySQL数据库开篇：数据库的前世今生

本文内容出自刘欣的“码农翻身”公众号，强烈推荐刘欣大大的文章。

 

数据库的前世今生

小李的数据库之旅

无纸化办公

小李是这个大学计算机科学与技术系的知名学生，他的编程能力了得，使用pascal 炉火纯青，这都是高中期间参加全国青少年信息学奥林匹克竞赛打下的底子，  虽然没有获过奖，但在80年代末，90年代初很多人都不知道计算机是何物的时候，人家就可以在上面写程序了， 是非常让人敬佩的事情。

 

所以一入学，辅导员就找到小李让他帮忙给系里开发个信息系统， 记录系里的学生信息，课程信息， 还有选课， 这样的话就可以无纸化办公了 。

 

小李觉得这只是一个基于命令行的程序， 无非是增删改查嘛，就满口应承下来， 然后祭出pascal 大法，准备大干一场。 

 

辅导员把相关的资料也送来了， 这学生信息无非是[学号，姓名，性别，身份证号，入学日期，班级] 等信息。 

课程信息也就是[课程号，课程名，授课老师] ,    选课是[学号，课程号，成绩]

 

有了基本的数据结构， 小李决定用三个独立的文本文件来存储这些信息， 比如说student.txt 中的内容是这样：

第一行是表头， 其他行是内容，都用逗号分开 。 

剩下的两个文件的格式和这个差不多。 

 

编程工作进展的非常顺利， 最重要的部分无非就是用pascal读写文件而已， 一周不到就完工了， 现在程序架构是这个样子的：

这个单机版的信息系统就这么运行了起来，效果还不错。

 

数据的冗余和不一致

商学院的主任听说计科系有了这么一个系统， 不由的也打起来注意， 辅导员就让小李用软盘拷贝了一份过去， 商学院也顺利用来起了。

 

可是有些计科系的学生到商学院去选修经济学的课程时， 发现还得再输入一遍学生信息， 这实在是太烦人了。 

 

小李也没办法， 毕竟这是两套系统啊， 只有采用土办法， 把计科系的student.txt 复制了一份到商学院。 

 

这样一来数据的重复难于避免了， 更有可能出现数据不一致的地方， 比如地址信息在计科系改了， 但是商学院没改。 

 

后来辅导员说数学系自己也搞了一个类似的系统， 不是用pascal而是用c写的， 数据格式和小李定义的还不一样， 小李想把student.txt复制过去也不可能了。 

 

小李想要是学校所有的院系都用这么一套系统就好了。 其实学校领导也看到了这个问题， 只是现在的校内局域网还没有建立起来， 大家用同一套系统并不现实。

 

李氏查询

到了期末， 计科系和商学院的老师纷纷给小李打电话：

“小李，我想统计一下这个学期操作系统课有哪些人没及格， 多少人在80分以上， 你能帮忙弄弄吗？”

 

“小李，我想算一下经济学的平均分， 能不能程序实现一下？ 学生太多，手工算太麻烦了 ”

......

 

为了应付这些“变态”的需求， 小李假期几乎没怎么休息， 不停的用pascal写各种各样的功能。 

 

可是这种需求似乎无穷无尽， 总结一下，无非就是对这些文件的各种各样的查询而已。 

 

难道让老师们直接去文件中查找和计算吗？ 显然不行。  

 

小李想起了一句话： “ 所有计算机的问题都可以通过增加一个中间层来解决” 

 

那提供一个中间层吧， 把文件层屏蔽掉， 让老师们在这个中间层用自己熟悉的术语进行查询。 

 

中间层上要有逻辑的数据结构，其实就是这些东西：

学生信息：[学号，姓名，性别，入学日期，班级，地址] 

 

课程信息：[课程号，课程名，授课老师] 

 

选课 ：[学号，课程号，成绩]

 

小李决定把这些东西称为“表” ，其中的每一项称为“列”/“字段”/“属性”， 每一列都有类型，例如字符型，日期型，数字型等等

 

查询的话是用类似这样进行的： 

select  学号，姓名 

from 学生信息 

where  入学日期='1991-9-1'

 

想把几个表连接起来查询也可以：

 

select 学号，姓名, 课程名，成绩

from 学生信息 s , 课程信息 c， 选课 sc

on s.学号=sc.学号 and c.课程号=sc.课程号 

where   课程名='操作系统'  and 成绩<60 

 

很明显小李需要写一个解析器， 把这样的语句变成内部对文件的操作， 还好小李已经有一点编译原理的基础了， 努力一下还是能写出来的。

 

小李把查询规则给各个老师做了个简单的培训， 从此以后， 只要不是超级复杂的查询， 老师们自己就搞定了，再也不用骚扰小李了。 

 

无心插柳柳成荫，小李忽然发现，自己的程序也可以调用这样的抽象层来编程啊， 也不用直接操作文件了， 简化了好多。 

小李得意的把这套查询称为“李氏查询” ，  李氏查询用起来简便快捷， 最大的好处是用户完全不用考虑物理层的那些文件的结构，只需要关注逻辑层的“表”就可以了。

 

（码农翻身注：其实就是sql了）

 

可是小李一直是隐隐觉得不安， 不知道这种查询方式有没有漏洞， 后来看到埃德加·弗兰克·科德 的论文 “a relational model of data for large shared data banks（大型共享数据库的关系模型）”，

这才明白，其实这就是所谓的关系模型啊， 其背后的有着坚实的数学基础， 肯定是没有问题的。

 

有了一个中间的逻辑层， 还带来了一个额外的好处，现在小李可以对物理层的文件存储做一些优化了， 为了加快访问速度， 小李不再采用简单的逗号分隔的文件， 还增加了索引、b+树，缓存等手段。

由于有中间层的存在，这些变化对应用层没有什么影响。

 

 

接上篇《小李的数据库之旅（上）》， 上回说到小李用一个中间逻辑层解决了普通人也能查询数据的问题， 很快新的挑战就来了。

 

并发访问

校园的局域网很快就建立起来， 原来单机的软件纷纷转为支持网络访问的系统， 学校为了统一各系的信息系统管理， 要从现有的系统中择优选择一个，升级成局域网可访问的， 然后全校扩展。

 

小李的软件和数学系的，电子系的一起竞争， 相比而言，数学系的系统采用了网状的结构， 电子系的采用了层次结构， 无论是哪种结构， 使用者都需要知道精确的内部结构以后才有可能进行查询， 相比“李氏查询” 实在是太过繁琐。  小李的系统以很大的优势胜出了。 

 

小李刚学会了c语言， 觉得这种语言更加贴近硬件，效率更高，更适合写这些“系统级”的软件， 于是决定保留之前的设计， 然后用c重写。  

 

当然不仅仅是重构， 还包含了重要的功能增强：网络访问， 从单机软件变成了客户端-服务器结构(c/s)的软件。

学校购买了一个性能强劲的ibm服务器作为服务全校的中心数据节点， 小李的软件部署在了上面， 想着自己的软件被这么多教职工使用， 小李觉得很有成就感。 

 

好景不长， 小李很快就发现网络版软件的复杂度要远远超过单机版， 这不马上就有老师爆出了一个超级大问题。

 

王老师对一个学生的地址进行了更新， 张老师对另外一个学生的地址也做了更改， 后来发现王老师的修改不见了， 这是怎么回事？ 

 

小李看了代码，很快就发现在单机版的时候， 原来的操作都是基于整个文件的： 读入文件内容， 做修改， 然后写入文件， 很明显， 王老师的修改在前，张老师的修改在后， 王老师的被覆盖了。 

 

真是个严重的问题， 恰逢周末， 小李赶紧通宵达旦的修改， 升级系统，把基于文件的操作改变成基于行的操作： 每个人的修改只影响这一行。 

 

小李觉得这样应该没问题了， 可是很快就发生了两个人对同一行的修改： 

 

电子系的账户有1000元， 刘老师支取了300， 金老师支取了200 ， 最后账户的余额竟然是800元 ！ 实际应该是500元啊。 

 

这是个极为严重的错误， 系统*停止了几天专门来修复这个问题。 

 

一个解决的办法就是给这一行加锁， 在刘老师读取了1000元， 扣除300元，并且把700 写回到数据库之前， 不允许金老师操作，这样就不会乱掉了。

 

原子性问题

小李找了几个同学，仔细的审查了程序，确保一些重要的更新操作都有行锁， 这次稍微松了一口气。 

 

可是一次非常偶然的系统故障有暴露了一个从没有想过的大问题：

 

当时电子系的账户有1000元， 数学系有2000元，  电子系要给数学系转账200元， 系统先扣除了电子系的账户钱的钱，变成了800 ， 正要往数学系上面增加余额的时候， 系统出了故障，崩溃了。 

 

重启以后，就发现电子系的余额是对的， 可是数学系还是2000元， 那200元丢了 ！

 

很明显， 转账这个操作，必须得是原子的： 要么全部发生， 要么根本不发生。 

 

小李决定把类似这样的操作叫做“事务”， 但是怎么实现呢？

 

小李苦思冥想，　终于放了一个大招：　记录日志　！　

 

在做真正的操作之前，先把要做的事记录下来形成日志（log），这个日志中包括修改的数据项标识， 数据项的旧值（修改前的值）和新值（修改后的值）， 然后再进行真正的数据库修改。   

 

刚开始的时候事务处于活动状态， 只有所有的操作都正确无误的写入了磁盘，才会进入提交状态， 否则就要回滚修改。 

 

（码农翻身注： 除了原子性之外，事务还有持久性，隔离性，一致性，这里就不展开了）

 

安全

 有一天系主任找到小李，提了一个全新的问题：

“小李啊，能不能添加一点权限控制？ 比方说系里的财务状况只能我和财务人员知道， 现在每个人都可以查询，这成什么样子？”

 

小李心想确实是这样， 一个没有权限控制的系统是非常危险的， 尤其是随意删除， 那还了得？！

 

赶紧加上一个权限系统， 小李想了想，  先定义三大类权限：

1. 对数据操作的， 例如select, update, insert等

2. 对结构操作的， 例如创建表，修改表，等

3. 做管理的， 例如备份数据， 创建用户等

 

然后就可以把这些权限授予某个用户了， 很多时候，还需要把表附加上， 像这样：

grant  select on 财务表 to  系主任

grant  create_table to 张老师

 

（码农翻身注： 这里模仿了mysql）

 

解决了如此多棘手的问题以后， 小李的信息系统已经非常复杂了，实际上，这个系统的中间层完全可以剥离出来，形成一个完整的软件了， 小李把它称为：数据库

 

 

丢失的数据

旺财是数据库村的一个程序， 小强也是。

 

数据库村有个特点， 很多数据支持共享操作，多个程序可以同时读写，他们俩经常会为了读写同一个数据， 争夺的不可开交。

 

这一天，当旺财和小强对同一个银行账户a进行写操作时候， 出现了这么一个错误：

看看， 本来旺财要加上的20元就丢掉了。  

 

同样的事情发生的多了， 他俩给这种情况起了一个名字，叫“丢失修改”， 其实说白了就是俩人都去写一个数据， 一个人的数据把另外一个给覆盖了。

 

村里的mysql说： “你们两个小家伙，写数据的时候连加锁都不做，肯定会出大乱子！"

 

旺财说：“加什么锁？”

 

“来来来， 我教你们一个排他锁（exclusive lock） ,   简称x锁， 旺财你要写数据了， 就把它用x锁锁住， 锁住后，除非你释放， 否则小强无法获得x锁。 这不就解决你们的问题了？  ”

 

小强想了想， 就把上面的操作过程用x锁改了一下：

旺财说：“果然不错， 确实可以解决两个人同时修改导致的问题。”

 

脏数据

小强说：“旺财， 我们约定，写数据的时候都用x锁吧？”

 

旺财说： “这没问题， 可是x锁只在写数据的时候用， 我们读数据是不用加锁的， 我想起了一种情况， 你看看怎么办？”

小强在旺财执行的途中读了a的值， 但是旺财把对a的修改给回滚(rollback)了, 这下小强尴尬了， 他读到了脏数据。

 

“要不我们在读取数据的时候也加个x锁 ？ ” 小强说。

 

“那样太严格了， 就是读一个数据啊， 值得吗？”

 

“这样吧， 我们再搞一个新的锁出来， 专门用于共享数据的读取， 就叫共享锁(share lock) ，简称s锁， 这个锁和之前的排他锁x锁有区别， 主要用于读取数据，  如果一个数据加了x锁， 就没法加s锁， 同样加了s锁， 就没法加x锁”   小强想出了一个点子。

 

“那如果我加了s锁， 你还能加s锁吗？ ”  旺财问。

 

“应该可以吧，  咱们俩都是读数据， 互不影响啊。 还有为了防止长时间的锁住， 我们可以约定一下，不管我们要做的事情有多少， 读一个数据之前加s锁， 读完之后立刻释放该s锁 ! ”

果然，这样一来“脏数据”的问题就解决了 ！

没法重复读？

旺财和小强两个程序相安无事了很久， 但是s锁在读完数据后立刻释放的约定， 导致出了一个新问题。

 

旺财在一次数据处理中， 先读取了a和b的值， 相加得到了150 ，  然后小强把b改成了30

旺财再次读取a和b， 发现求和以后是130 , 刚才的不一样了！

（码农翻身注： 假定旺财的处理是在一个事务当中）

旺财说： “小强，  我在读取数据的时候你不能改啊 ， 要不然我这里会出现不一致， 你看刚开始是a+b是 150， 现在变成130了”

 

小强说： “我们之前的约定是读数据时加s锁， 读完立马释放，  问题就出现在这里了。”

 

“看来在读数据的时候， 也需要一直锁定了， 直到事务提交。”

 

 

幻觉出现

旺财和小强现在已经能灵活的使用x锁和s锁了。

他们俩总结了一下， 分为了这么几种情况：

 

1.  写数据时加上x锁，直到事务结束， 读的时候不加锁。

虽然能够避免丢失数据，  但是可以读到没有提交或者回滚的内容 （脏数据）， 这其实就是数据库最低的事务隔离级别 --- read uncommitted

 

2. 写数据的时候加上x锁， 直到事务结束，  读的时候加上s锁， 读完数据立刻释放。

这能避免“丢失数据”和“脏数据”，  但是会出现“不可重复读”的问题  ，  这是第二级的事务隔离级别 -- read committed

 

3.  写数据的时候加上x锁，  直到事务结束， 读数据的时候加s锁， 也是直到事务结束。

这能避免“丢失数据”和“脏数据”， “不可重复读”三个问题 ， 这是数据库常用的隔离级别 --

repeatable read

 

整个世界似乎清净了。

 

有一次旺财对一个“学生表”进行操作，选取了年龄是18岁的所有行， 用x锁锁住， 并且做了修改。

 

改完以后旺财再次选择所有年龄是18岁的行， 想做一个确认， 没想到有一行竟然没有修改！

这是怎么回事？  出了幻觉吗？

 

原来就在旺财查询并修改的的时候，  小强也对学生表进行操作， 他插入了一个新的行，其中的年龄也是18岁！  虽然两个人的修改都没有问题， 互不影响， 但从最终效果看， 还是出了事。

 

（码农翻身注： 正是小强的操作， 让旺财出现了“幻读”）

 

旺财说： “没辙了， 我们俩非得串行执行不可， 你必须得等我执行完。 ”

 

这就是数据库事务隔离级别的终极大招：serializable

 

最后， 为了方便记忆， 他们俩倒腾了半天， 整出了一张表， 用于记录各种情况：

（点击看大图）

 

两个人看着这张表， 感慨的说：“唉， 这数据库村的事务隔离级别可真是不容易啊！”

 

mysql 不屑一顾的说： “这都嫌麻烦了， 你们还没遇到死锁呢....”

微信公众号【黄小斜】作者是蚂蚁金服 java 工程师，专注于 java 后端技术栈：springboot、ssm全家桶、mysql、分布式、中间件、微服务，同时也懂点投资理财，坚持学习和写作，相信终身学习的力量！关注公众号后回复”架构师“即可领取 java基础、进阶、项目和架构师等免费学习资料，更有数据库、分布式、微服务等热门技术学习视频，内容丰富，兼顾原理和实践，另外也将赠送作者原创的java学习指南、java程序员面试指南等干货资源


添加描述