猴子都能懂的資料庫避坑指南

前言

工作的這些年發現一個比較奇怪的現象就是身邊無論是工作十多年的老兵，還是初級剛入行的程式員，在高談闊論技術和趨勢的時候都是人工智慧，大數據，區塊鏈，各種框架，語言，演算法，AI，BI，CI，DI…… 等等，倒是發現很少有人關註資料庫，不知道是因為資料庫感覺太低端還是太低調，總是不容易被人提起

技術就是這樣，不太關註的地方就不會重視，越是不被重視的地方，掉進坑裡的概率就會越大，所以就在這裡給大家簡單聊聊在使用資料庫過程中有哪些防掉坑指南，也可以對剛入行的小朋友有一個提醒的作用，萬丈高樓平地起，一定要先打好基礎再去考慮上層的建築，不要捨本逐末

本章主要分以下四個小節（預計讀完 5 分鐘左右）：

1. 資料庫為什麼重要
2. 資料庫有哪些使用技巧
3. 資料庫有哪些容易掉進去的坑？
4. 深入學習資料庫的建議

資料庫為什麼重要

很多人在開發過程中不太關註資料庫，對於表結構的設計也沒什麼講究大多屬於“能用就行”，但是根據作者將近十年的開發經驗來看的話，只要你是從事 Web 相關領域開發你就無法避免不和資料庫打交道，在Web開發中大多功能操作本質上都是對資料庫進行操作，不管你用是 Pythod，Java，Ruby 等語言進行 Web 開發，你其實都是在面向資料庫進行編程，很多 Web 框架作者為了避免程式員接觸資料庫的相關知識甚至還封裝了一層 ORM （Object Relational Mapping 對象關係映射），把資料庫當做一個黑盒子，然後通過操作對象的形式來操作資料庫

雖然某種意義上是簡化的開發，對此我是持有保留意見的，因為對於程式員來說很有必要瞭解你的 SQL 語言在資料庫是怎麼執行的，你不僅需要使用 explain 執行計劃來查看你的 SQL 是否高效（掃描行數，命中索引，回表，排序等），對比不同 SQL 的寫法外，你還需要知道如何使用 show index 來查看你的索引是否高效（通過 Cardinality 由資料庫評估），這些技巧很大程度依賴你對 SQL 的瞭解，SQL 對於程式員來說也是一門非常重要的技能，沒錯 SQL 就是操作資料庫的語言，據我瞭解大多數的公司在面試的時候都會考察程式員的 SQL 功底，扎實的 SQL 功底不僅可以讓你寫出高性能的查詢語言外，對於數據分析，報表統計也是有非常大的幫助

大多數商業公司的核心資產其實就是資料庫裡面的數據，是非常寶貴的財富，程式和系統掛了，最多就是一段時間不可用，大多是情況重啟就可以恢復，但是是資料庫不小心被誤刪了，如果是運維能力差的中小企業可能會面臨倒閉的地步，從商業角度上來說資料庫大多數軟體公司的核心

很多程式員從菜鳥成長到高手，接觸的項目從學校的"某某管理系統"到剛加入公司內部系統，然後再到大型分散式系統，在大型系統中，大多數人程式員通常遇到的第一個問題通常不是線程不夠用，不是CPU負載過高，不是記憶體不夠快，通常都是資料庫扛不住壓力了，為什麼呢？資料庫本身就基於磁碟的文件系統，每次讀取數據都是通過 I/O 去訪問磁碟，瞭解電腦原理的同學應該都知道，在馮諾依曼電腦體繫結構里磁碟 I/O 號稱是最慢的 I/O （毫秒級），通常在你的系統只有幾千上萬的數據量時，全表掃描通常不會有很大的延遲感，但是當你的存量數據達到百萬千萬時，那麼一次普通的查詢就會把你的資料庫伺服器撐爆，做過應用的人都知道，資料庫掛了，不管是什麼分散式，微服務的牛逼架構都基本沒啥用了，嘮嘮叨叨說到這裡，相信大家應該已經知道資料庫的重要性的，後面我們再從資料庫設計的角度來看下問題

資料庫設計對系統的影響

這裡我們簡單做一個對比，良好的資料庫設計可以為你帶來什麼 ？

1. 減少數據冗餘，避免數據維護異常
2. 節省存儲空間，高效的訪問速度

糟糕的設計 ？

1. 大量數據冗餘插入，更新，刪除異常
2. 浪費存儲空間，低效的訪問速度

糟糕的設計（圖）

比如說對於一個簡單的年齡欄位，嚴謹來說應該使用 tinyint（1位元組）或者 smallint(2位元組)，但是你偏偏要用 int (4位元組) 這就屬於糟糕的欄位選擇，看到這裡很多剛入門的同學就可能就會反駁了，這麼在意空間利用是不是有點矯枉過正？包括存儲已經很便宜了，還這麼斤斤計較般的選擇，反正最終實現的功能都是相同的，別人也看不出什麼差別呀。對於這種觀點其實我想反駁一下，這是典型的新手思維，你只在看到在單個欄位上的空間節省，但是沒有考慮過數據也是在持續增長，糟糕的設計越到後期增長成本會越高（這裡就類似於 Java 的經典面試題，集合類 ArrayList 和 LinkedList 在少量數據對比時看不出時間上的差距，但是隨著計算數據量的上升，消耗數據的差距也會越拉越大），等到了千萬級數據量的時候，可能你設計的表和別人設計的表是相同的內容，但是你的表無端的多出幾百G的存儲空間，如果你的應用還是多數據中心的話，那麼這種無端的空間浪費還會被拷貝幾十倍到不同的數據中心，而且只要你的應用還線上上運行，那麼這種增長所帶來的成本還會持續上升，這裡也僅僅只是說對空間的浪費，下麵在分析表結構存儲上，還會具體說一下糟糕的設計對於性能會有多大的影響，這對企業來說就是邊際成本的遞增，從技術和架構上來說就會讓你的系統不具備可擴展性

資料庫的使用技巧

存儲引擎的註意事項

MySQL 的開放性架構設計相容了很多不種類的存儲引擎（要是你足夠厲害的話，也可以自己寫一套存儲引擎），存儲引擎的設計初衷就是應對不同類型的數據倉庫，工作中有見過不管什麼表都直接用 Innodb（MySQL 5.0 的預設存儲引擎，雖然大多數場景是不錯的選擇，但不是所有類型的表結構都適用）也見過根本不知道什麼是存儲引擎的同學，如果這些同學來設計資料庫的話，那麼你的系統就很容易踩到坑，出現很多你自己的預料不到的問題，合理的存儲引擎的選擇是應該結合實際業務場景，從目前最主流的 MySQL 來說，最常用的存儲引擎主要是 MyISAM， Innodb，當然還有很多其他的存儲引擎，例如 NDB(集群存儲引擎)，Memory（基於記憶體的存儲引擎），Archive（歸檔存儲引擎），因為這些平時使用不多，並不主流，工作中也很少用得到，意義不大，所以就不展開來講，這裡主要簡單將下 MyISAM，Innodb 的區別，主要有以下特點：

MyISAM

• 無事務機制，表級鎖，自帶計數功能（count 全表毫秒級響應）
• 主要面向 OLAP 型應用，適合存儲報表日誌等類型數據

Innodb

• 行級別，高併發，支持事務，四種事務隔離級別（MySQL 5.0+ 預設是讀已提交）
• 主要面向 OLTP 型應用，適合存儲小量的事務型數據

欄位類型的註意事項

因為不瞭解資料庫的基本原理，所以很多初級程式員在選擇資料庫欄位類型的時候比較迷茫，主要還是沒有明確指導原則，工作中我見過在只有十幾條數據的基礎信息表中使用 long（8位元組）作為 id 主鍵類型，還有就像上面說的狀態類型欄位只有 0，1 值的欄位使用 int （4位元組），還見過字元類型欄位統一使用 varchar(255)，數值類型欄位統一使用 int，這種不基於資料庫原理規則去隨意選擇欄位的行為也只會出現在你 LocalHost 里的一些小項目或者玩具，基本上不了什麼大臺面

據我所知，主流的資料庫大多都提供非常豐富的欄位類型給開發者使用，老司機都是基於業務類型的判斷從而選擇合適的欄位類型，最終收穫的是性能（時間）和存儲（空間）都非常低的高性能資料庫，具體資料庫有哪些欄位類型，文章裡面就不多數了，這方面的資料簡直太多了，有興趣的小伙伴可以自己去搜索，例如這裡 MySQL Data Types，那麼對於新手而言如何選擇欄位類型呢？

簡單的基本原則如下：（後面會具體將原因）

1. 優先數字型欄位（比如儘量使用 int 作為資料庫主鍵 id 的類型而不是 varchar）
2. 在滿足需求的前提下，欄位類型儘量足夠的小（例如 age 欄位應該考慮使用 tinyint 而不是 int 或者 long 類型）
3. 時間欄位考慮 timestamp （4位元組，支持 UTC）而不是 datetime（8位元組，不支持 UTC）

遵循基本規範能帶來什麼好處？

1. 節省存儲的開銷，避免空間浪費（如果1條數據造成的空間開銷n，那麼隨著數據增長，浪費空間的比例也就是 n * n）
2. 最好的性能（用戶體驗，另一種角度的節省資源-算力）

為什麼要把“選擇儘可能小的欄位”作為基本原則？我們可以先看下 innodb 的邏輯存儲結構

innodb 邏輯存儲結構（圖）

innodb 的存儲結構如下：

• 表空間（Tablespace）
• 段（Segment）：表空間由多個段組成
• 區（Extent）：單個區由 64 個連續頁（Page）組成
• 頁（Page）：磁碟的最小單位，預設大小 16 KB
• 行（Row）：每條記錄，也稱行數據，數據存儲在頁中 Page

上圖可以看到讀取最小單元 Page，匹配的數據都是從 Page 裡面取出，按照這個簡單的邏輯來說頁中存儲的行數據越多，資料庫的性能就越高，怎麼算出來的呢？按最小類型 2B 來計算 Row，那麼 Page 的預設大小（16KB）是可以匹配到 7992 行記錄，相反，如果你的 Row 行數據過大，假如一行 32 KB，那麼資料庫就需要 2 個連續的 Page 來保存你一行的數據，那麼性能可想而知會有多低，前後性能差距差不多 1.6 萬倍，這塊也不深入講了，有興趣的小伙伴推薦去閱讀經典書籍，這裡的內容也只是書里的冰山一角

選擇索引的註意事項

索引是一種用空間換時間的優化手段，是資料庫最重要的優化手段，也是最後的殺手鐧，索引是否高效取決資料庫設計是否良好，欄位類型選擇是否合理，索引是一把雙刃劍，在提升檢索速度的時候，也會減低插入，修改的性能（維護索引樹的開銷），在工作中這些年面試了不下幾百人發現能把資料庫索引原理講明白的候選人非常的少，大多數情況下我們說索引通常預設指的是 BTREE 索引，BTREE 結構是特意為磁碟 I/O 這種緩慢的讀取存儲設計的數據結構，是一棵多路多叉樹，和二叉樹相反，每層的元素非常多，但是樹的高度很矮（通常不會超過三層），從而可以保證最多不超過三次磁碟 I/O 即可定位到匹配的元素，所以說 BTREE 是一種非常適合磁碟的數據結構，也是 MySQL 預設索引類型是 BREE 的原因，如果能把這塊吃透的話，那麼去面試肯定是很大的加分項，索引在資料庫可以簡單參考下圖：

簡單說了下索引的結構，那麼新手程式員在使用資料庫所以的時候可以遵循以下原則：

• 明白索引不是越多越好，過多的索引會降低讀/寫效率
• 數據小和選擇性低的列沒有必要建索引（就像沒必要為只有幾頁的書建目錄）
• 定期維護索引（移除不必要的索引，索引的最左匹配原則）
• 謹慎使用全文索引，哈希索引，謹慎使用 FORCE INDEX 強制索引（強制會幹擾優化器對索引選擇的判斷）

索引這塊可以玩的還有很多，例如如何通過 SHOW INDEX 查看資料庫為索引做出的評級（通過 Cardinality 統計），通過 Explain 查看 SQL 是否命中索引，rows 列可以看到 SQL 掃描的數據行數，Extra 列還可以查看索引匹配的類型，例如 Using index 代表完全匹配索引（無需回到 Primary Key 表查詢數據，也稱回表，甚至直接使用索引的排序，無需排序）往往說明性能不錯，Using temporary 代表查詢有使用臨時表，一般出現於排序，多表 join 的情況，查詢效率不高，建議優化

還有哪些要避開的坑？

人生總會遇到很多坑，與其自己去踩坑不如去總結別人踩過的坑，自己少走一些彎路也許可以更快的成功，這裡是最後一章，不想把文章拉的太長，所以我在這裡就直接拋出結論，不會再說明原因，如果對資料庫有興趣推薦看到最後我推薦的書籍

避免使用觸發器/存儲過程

• 用存儲過程寫邏輯會導致代碼非常的複雜難懂，並且難以定位問題
• 降低資料庫的性能（資料庫不應該執行除 SQL 外的其他邏輯操作）

避免使用預留欄位

• 無法準確預測欄位類型
• 增加後期維護成本

反範式設計

• 不必完全遵守古板的三大範式，對範式進行違反，用空間換時間
• 對數據進行有計劃的冗餘，可以達到減少關聯，提高性能和效率

儘量避免使用 Null 欄位

• Null 值會導致索引失效，讓統計函數更加複雜，另外 Null 還會占用額外的空間（資料庫需要額外標記）
• 對於 Null 值，資料庫程式通常都會進行額外的邏輯處理，獎勵資料庫性能
• 從資料庫中取出 Null 值容易造成程式出錯，還會增加很多 if != null 的重覆模板代碼

最後 end

這篇文章寫了三天（空閑時間），主要覆蓋篇幅比較廣，但是每個主題都是在幼兒園的入門水平，主要是給很多新手程式員一個簡單的參考，我個人認為看文章分享只是為了點燃興趣，就像一道開胃菜，最終的形成自己的知識體系，熟悉知識完整的結構還是推薦去閱讀經典的書籍，這才是學習的正確姿勢，資料庫的書我讀的不是很多，但還是可以簡單推薦兩本我讀過的並且感覺非常不錯的，並且本篇文章都是大量參考了書中的內容，非常值得推薦：

• 《MySQL 技術內幕 InnoDB 存儲引擎》：這本書主要偏向對存儲引擎的分析，對不同存儲引擎的性能，存儲結構和適用場景做了橫向對比，作者最後還在表分區，約束和索引等技術上給出自己的見解，我在看這本書的時候無不佩服作者對存儲引擎的瞭解程度
• 《高性能 MySQL》：這本可以說是 MySQL 的百科全書，內容覆蓋非常全面，是公認 MySQL 領域的聖經級教科書，唯一的缺點就是太厚了，第三版都已經快 800 頁了

值得推薦的書就以上兩本，如果覺得看書不過癮可以再推薦看看極客時間的 《MySQL 實戰 45 講》是由鼎鼎大名的資料庫大神丁奇所寫的專欄，如果用開藥來比喻的話，看書就是內服，看專欄就等於外敷，總結就是，內服 + 外敷 療效可能會好一些，最後打一波廣告：如果要買極客時間專欄可以加我微信，我有推薦二維碼並且返現紅包，666666
更多技術咨詢，請關註公眾號，find me !