ITPub博客

首页 > 数据库 > MySQL > 重新学习MySQL数据库开篇:数据库的前世今生

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

MySQL 作者:a724888 时间:2019-08-24 16:14:33 0 删除 编辑
本文内容出自刘欣的“码农翻身”公众号,强烈推荐刘欣大大的文章。
 

数据库的前世今生
小李的数据库之旅
无纸化办公
小李是这个大学计算机科学与技术系的知名学生,他的编程能力了得,使用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程序员面试指南等干货资源


添加描述


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69906029/viewspace-2654814/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
微信公众号【黄小斜】 大厂程序员,互联网职场新知,终身学习践行者,懂技术,懂职场,更想懂你。

注册时间:2019-01-14

  • 博文量
    255
  • 访问量
    155479