重新學習MySQL數據庫開篇：數據庫的前世今生
本文內容出自劉欣的“碼農翻身”公眾號，強烈推薦劉欣大大的文章。
小李是這個大學計算機科學與技術系的知名學生，他的編程能力了得，使用Pascal 爐火純青，這都是高中期間參加全國青少年信息學奧林匹克競賽打下的底子， 雖然沒有獲過獎，但在80年代末，90年代初很多人都不知道計算機是何物的時候，人家就可以在上面寫程序了， 是非常讓人敬佩的事情。
所以一入學，輔導員就找到小李讓他幫忙給系里開發個信息系統， 記錄系里的學生信息，課程信息， 還有選課， 這樣的話就可以無紙化辦公了 。
小李覺得這只是一個基于命令行的程序， 無非是增刪改查嘛，就滿口應承下來， 然后祭出Pascal ，準備大干一場。
輔導員把相關的資料也送來了， 這學生信息無非是[學號，姓名，性別，身份證號，入學日期，班級] 等信息。
課程信息也就是[課程號，課程名，授課老師] , 選課是[學號，課程號，成績]
有了基本的數據結構， 小李決定用三個獨立的文本文件來存儲這些信息， 比如說student.txt 中的內容是這樣：
編程工作進展的非常順利， 最重要的部分無非就是用Pascal讀寫文件而已， 一周不到就完工了， 現在程序架構是這個樣子的：
這個單機版的信息系統就這么運行了起來，效果還不錯。
商學院的主任聽說計科系有了這么一個系統， 不由的也打起來注意， 輔導員就讓小李用軟盤拷貝了一份過去， 商學院也順利用來起了。
可是有些計科系的學生到商學院去選修經濟學的課程時， 發現還得再輸入一遍學生信息， 這實在是太煩人了。
小李也沒辦法， 畢竟這是兩套系統啊， 只有采用土辦法， 把計科系的student.txt 復制了一份到商學院。
這樣一來數據的重復難于避免了， 更有可能出現數據不一致的地方， 比如地址信息在計科系改了， 但是商學院沒改。
后來輔導員說數學系自己也搞了一個類似的系統， 不是用Pascal而是用C寫的， 數據格式和小李定義的還不一樣， 小李想把Student.txt復制過去也不可能了。
小李想要是學校所有的院系都用這么一套系統就好了。 其實學校領導也看到了這個問題， 只是現在的校內局域網還沒有建立起來， 大家用同一套系統并不現實。
到了期末， 計科系和商學院的老師紛紛給小李打電話：
“小李，我想統計一下這個學期操作系統課有哪些人沒及格， 多少人在80分以上， 你能幫忙弄弄嗎？”
“小李，我想算一下經濟學的平均分， 能不能程序實現一下？ 學生太多，手工算太麻煩了 ”
為了應付這些“變態”的需求， 小李假期幾乎沒怎么休息， 不停的用PASCAL寫各種各樣的功能。
可是這種需求似乎無窮無盡， 總結一下，無非就是對這些文件的各種各樣的查詢而已。
難道讓老師們直接去文件中查找和計算嗎？ 顯然不行。
小李想起了一句話： “ 所有計算機的問題都可以通過增加一個中間層來解決”
那提供一個中間層吧， 把文件層屏蔽掉， 讓老師們在這個中間層用自己熟悉的術語進行查詢。
學生信息：[學號，姓名，性別，入學日期，班級，地址]
小李決定把這些東西稱為“表” ，其中的每一項稱為“列”/“字段”/“屬性”， 每一列都有類型，例如字符型，日期型，數字型等等
FROM 學生信息 s , 課程信息 c， 選課 sc
ON s.學號=sc.學號 AND c.課程號=sc.課程號
WHERE 課程名='操作系統' AND 成績<60
很明顯小李需要寫一個解析器， 把這樣的語句變成內部對文件的操作， 還好小李已經有一點編譯原理的基礎了， 努力一下還是能寫出來的。
小李把查詢規則給各個老師做了個簡單的培訓， 從此以后， 只要不是超級復雜的查詢， 老師們自己就搞定了，再也不用騷擾小李了。
無心插柳柳成蔭，小李忽然發現，自己的程序也可以調用這樣的抽象層來編程啊， 也不用直接操作文件了， 簡化了好多。
小李得意的把這套查詢稱為“李氏查詢” ， 李氏查詢用起來簡便快捷， 最大的好處是用戶完全不用考慮物理層的那些文件的結構，只需要關注邏輯層的“表”就可以了。
可是小李一直是隱隱覺得不安， 不知道這種查詢方式有沒有漏洞， 后來看到埃德加·弗蘭克·科德 的論文 “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等
然后就可以把這些權限授予某個用戶了， 很多時候，還需要把表附加上， 像這樣：
GRANT SELECT on 財務表 to 系主任
GRANT CREATE_TABLE to 張老師
解決了如此多棘手的問題以后， 小李的信息系統已經非常復雜了，實際上，這個系統的中間層完全可以剝離出來，形成一個完整的軟件了， 小李把它稱為：數據庫
數據庫村有個特點， 很多數據支持共享操作，多個程序可以同時讀寫，他們倆經常會為了讀寫同一個數據， 爭奪的不可開交。
這一天，當旺財和小強對同一個銀行賬戶A進行寫操作時候， 出現了這么一個錯誤：
同樣的事情發生的多了， 他倆給這種情況起了一個名字，叫“丟失修改”， 其實說白了就是倆人都去寫一個數據， 一個人的數據把另外一個給覆蓋了。
村里的Mysql說： “你們兩個小家伙，寫數據的時候連加鎖都不做，肯定會出大亂子！"
“來來來， 我教你們一個排他鎖（Exclusive Lock） , 簡稱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鎖， 讀完立馬釋放， 問題就出現在這里了。”
“看來在讀數據的時候， 也需要一直鎖定了， 直到事務提交。”
1. 寫數據時加上X鎖，直到事務結束， 讀的時候不加鎖。
雖然能夠避免丟失數據， 但是可以讀到沒有提交或者回滾的內容 （臟數據）， 這其實就是數據庫最低的事務隔離級別 --- Read uncommitted
2. 寫數據的時候加上X鎖， 直到事務結束， 讀的時候加上S鎖， 讀完數據立刻釋放。
這能避免“丟失數據”和“臟數據”， 但是會出現“不可重復讀”的問題 ， 這是第二級的事務隔離級別 -- Read committed
3. 寫數據的時候加上X鎖， 直到事務結束， 讀數據的時候加S鎖， 也是直到事務結束。
這能避免“丟失數據”和“臟數據”， “不可重復讀”三個問題 ， 這是數據庫常用的隔離級別 --
有一次旺財對一個“學生表”進行操作，選取了年齡是18歲的所有行， 用X鎖鎖住， 并且做了修改。
改完以后旺財再次選擇所有年齡是18歲的行， 想做一個確認， 沒想到有一行竟然沒有修改！
原來就在旺財查詢并修改的的時候， 小強也對學生表進行操作， 他插入了一個新的行，其中的年齡也是18歲！ 雖然兩個人的修改都沒有問題， 互不影響， 但從最終效果看， 還是出了事。
（碼農翻身注： 正是小強的操作， 讓旺財出現了“幻讀”）
旺財說： “沒轍了， 我們倆非得串行執行不可， 你必須得等我執行完。 ”
這就是數據庫事務隔離級別的終極大招：Serializable
最后， 為了方便記憶， 他們倆倒騰了半天， 整出了一張表， 用于記錄各種情況：
兩個人看著這張表， 感慨的說：“唉， 這數據庫村的事務隔離級別可真是不容易啊！”
Mysql 不屑一顧的說： “這都嫌麻煩了， 你們還沒遇到死鎖呢....”
微信公眾號【黃小斜】作者是螞蟻金服 JAVA 工程師，專注于 JAVA 后端技術棧：SpringBoot、SSM全家桶、MySQL、分布式、中間件、微服務，同時也懂點投資理財，堅持學習和寫作，相信終身學習的力量！關注公眾號后回復”架構師“即可領取 Java基礎、進階、項目和架構師等免費學習資料，更有數據庫、分布式、微服務等熱門技術學習視頻，內容豐富，兼顧原理和實踐，另外也將贈送作者原創的Java學習指南、Java程序員面試指南等干貨資源
