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

前言

作業的這些年發現一個比較奇怪的現象就是身邊無論是作業十多年的老兵，還是初級剛入行的程式員，在高談闊論技術和趨勢的時候都是人工智能，大資料，區塊鏈，各種框架，語言，演算法，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 !

標籤：架構設計

上一篇：Springboot vue 前后分離 跨域 Activiti6 作業流 集成代碼生成器 shiro權限

下一篇：《從0開始學架構》學習筆記（一）