MySQL 簡介

1.1：MySQL是什么?

????MySQL 是由 MySQL AB 公司（目前已經被 Oracle 公司收歸麾下）自主研發(fā)的，目前

IT 行業(yè)最流行的開放源代碼的數據庫管理系統之一，它同時也是一個支持多線程高并發(fā)多用戶的關系型數據庫管理系統。

????MySQL所使用的SQL語言是用于訪問數據庫的最常用標準化語言。MySQL軟件采用了雙授權政策，分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網站的開發(fā)都選擇 MySQL 作為網站數據庫。

1.2：MySQL 通常應用在哪些場景？

????MySQL 是目前最為流行的開源數據庫管理系統軟件之一。與其他的大型數據庫例：Oracle、DB2、SQL Server等相比，MySQL 有它的不足之處，但是這絲毫也沒有減少它受歡迎的程度。對于一般的個人使用者和中小型企業(yè)來說，MySQL提供的功能已經綽綽有余，而且由于 MySQL是開放源碼軟件，因此可以大大降低總體擁有成本。MySQL 主要應用于如下場景：

?? Web站點系統；

?? 數據倉儲系統；

?? 嵌入式系統；

任何產品都不可能是萬能的，也不可能適用于所有的應用場景。

MySQL邏輯架構?

2.1：?SQL 語句的執(zhí)行邏輯是怎樣的？

????我們用一條 SQL SELECT 語句的執(zhí)行軌跡是怎樣的，如圖所示：

其中：

①通過客戶端/服務器通信協議與 MySQL 建立連接。

②查詢緩存，這是 MySQL 的一個可優(yōu)化查詢的地方，如果開啟了 Query Cache 且在查詢緩存過程中查詢到完全相同的 SQL 語句，則將查詢結果直接返回給客戶端；如果沒有開啟Query Cache 或者沒有查詢到完全相同的 SQL 語句則會由解析器進行語法語義解析，并生成解析樹。

③預處理器生成新的解析樹。

④查詢優(yōu)化器生成執(zhí)行計劃。

⑤執(zhí)行引擎執(zhí)行 SQL 語句，此時執(zhí)行引擎會根據 SQL 語句中表的存儲引擎類型，以

及對應的 API 接口與底層存儲引擎緩存或者物理文件的交互情況，得到查詢結果，由MySQL Server 過濾后將查詢結果緩存并返回給客戶端。若開啟了 Query Cache，這時也會將SQL 語句和結果完整地保存到 Query Cache 中，以后若有相同的 SQL 語句執(zhí)行則直接返回結果。

2.2：MySQL 邏輯架構是怎樣的？

????MySQL是一種典型的C/S架構設計。接下來我們先先鳥瞰其全貌（注意，千萬不要直接陷入細節(jié)里），從更高維度對MySQL有一個基本認知，如圖所示：

圖中，MySQL 體系結構由 Client Connectors 層、MySQL Server 層及存儲引擎層組成。

??? 連接層：負責接收客戶端的連接請求，與服務端創(chuàng)建連接。目前 MySQL 幾乎支持所有的連接類型，例如常見的 JDBC、Python、Go 等。

??? 服務層：MySQL Server 層主要包括 Connection Pool、Service & utilities、SQL interface、Parser解析器、Optimizer 查詢優(yōu)化器、Caches 緩存等模塊。

??? SQL interface，負責接收客戶端發(fā)送的各種 SQL 語句，比如 DML、DDL 和存

儲過程等。

??? Parser 解析器會對 SQL 語句進行語法解析、語義解析生成語法樹。

??? Optimizer 查詢優(yōu)化器會根據解析樹生成執(zhí)行計劃，并選擇合適的索引，然后按照執(zhí)行計劃執(zhí)行 SQL 語言并與各個存儲引擎交互。

??? Caches 緩存包括各個存儲引擎的緩存部分，比如：InnoDB 存儲的 Buffer Pool、

MyISAM 存儲引擎的 key buffer 等，Caches 中也會緩存一些權限，也包括一些 Session 級別的緩存。從MySQL 5.7.20開始，不推薦使用查詢緩存，并在

MySQL 8.0中刪除 。

??? 存儲引擎層：存儲引擎真正的負責了MySQL中數據的存儲和提取，服務器通過API與存儲引擎進行通信。不同的存儲引擎具有的功能不同，常用的存儲引擎包括 MyISAM、InnoDB，以及支持歸檔的 Archive 和內存的 Memory 等。

????MySQL提供了插件式存儲引擎層（ Storage Engines），真正的負責了MySQL中數據的存儲和提取。服務器通過API與存儲引擎進行通信。不同的存儲引擎具有的功能不同，常用的存儲引擎包括 MyISAM、InnoDB，以及支持歸檔的 Archive 和內存的 Memory 等。

我們可以根據自己的實際需要進行選取。

MySQL 8.0.25默認支持的存儲引擎如下：

??? 物理存儲層：物理數據存儲層，主要是將數據存儲在運行于該設備的文件系統中，這些文件包括二進制日志、數據文件、錯誤日志、慢查詢日志、全日志、redo/undo 日志等。

MySQL數據類型分析

3.1：MySQL 中有哪些數據類型?

????????數據類型定義了 MySQL 列中可以存儲什么數據以及當前數據存儲的基本規(guī)則。在使用MySQL 的過程中，一定會根據數據類型的限制、特性有所取舍。MySQL 中可選的數據類型有很多，每一種數據類型都會有其使用限制與適合的使用場景

????通常，我們會將 MySQL 的數據類型分為四類，即字符串、日期 / 時間、數值以及二進制。顯然，根據這些分類的名稱可以知道，分類是按照存儲數據的類型來做的。那么，這些分類中又包含了哪些數據類型呢 ？

?? 字符串類型：以 char、varchar、text 為代表，用于存儲字符、字符串數據.

?? 日期/時間類型：以 date、time、datetime、timestamp 為代表，用于存儲日期或時間，這種數據類型也是比較難抉擇的。

?? 數值類型：以 tinyint、int、bigint、float、double、decimal 為代表，用于存儲整數或小數。

???二進制類型：以 tinyblob、blob、mediumblob、longblob 為代表，用于存儲二進制數據，適用場景最為受限。

????說明，對數據類型的分類并不是絕對的，這取決于對存儲數據的限制程度。例如對于數值類型又可以再去細分為整數型（int、bigint 等）、浮點型（float、double 等）、定點（decimal 等）。所以，并不需要把過多的精力花在類型分類上，更多的是應該搞清楚這些類型怎么用，又為什么這樣用。

3..2：如何查看類型的具體信息?

????對于平時寫代碼的你來說，Linux/Unix 環(huán)境一定不會陌生，當然，也就對 man 和help 這樣的命令不會陌生了。類似于這樣的 “幫助命令” 在 MySQL 中也是有的。例如，你想知道 int 這種數據類型的使用范圍，可以執(zhí)行命令：

可以看到，help 打印了 int 數據類型的描述信息以及官方文檔的鏈接地址。這對于學習使用數據類型來說，是非常方便的。當然，我們也可以在 help 后面加上 char、 varchar 等等 MySQL 支持的數據類型。此時，你可能會有疑問：這些打印的信息是從哪里來的 ？難道也是保存在 MySQL 表中的嗎 ？確實，正如猜測的那樣，MySQL 提供了 4 張表用于保存幫助信息（help 語法打印的即為幫助信息）。這些表位于 mysql 系統字典庫中，且表名都以 help_ 開頭。如下所示：

這些表是在數據庫初始化時通過內建腳本創(chuàng)建而成，其中：

??? help_category：存儲關于幫助主題類別的信息

??? help_keyword：存儲與幫助主題相關的關鍵字信息

??? help_relation：存儲幫助關鍵字信息和主題信息之間的映射

??? help_topic：存儲幫助主題的詳細內容

由此，可以知道，我們之前的 help int 信息來自于 mysql.help_topic 表中，也就可以通過查詢表記錄信息來獲取幫助信息了。如下所示：

關于其他的 “幫助表” 這里不再過多介紹，有興趣的同學可以自行查詢 MySQL 官網或其他渠道了解信息。

3.3：?你是如何理解 MySQL 中一些常見類型的？

????字符串 ：

?? Char 類型：char 數據類型用于定義一個固定長度的字符串，長度范圍處于 1 ~ 255 之間，且必須是在創(chuàng)建表時指定。它有一個特殊的情況是，存儲字符串時，如果未達到指定長度，則會使用空格填充到指定長度。所以，如果我們想要存儲不同記錄的字符串長度差別較大，會造成較大的空間浪費。根據對 char 類型的描述可以知道，當我們需要存儲一些長度固定的數據列時，使用 char 是非常合適的。例如：手機號碼、身份證號等等。

?? Varchar 類型：

相對于 char 來說，varchar 的 “出場率” 要稍微高一些。它定義了一個可變長度的字符串，創(chuàng)建時指定它所允許的最大長度。例如，如果創(chuàng)建時聲明了 varchar (x)，則只能存儲不超過 x 個字符的數據，且 x 的最大值是 65535。對于長度不固定的數據列，使用 varchar 就是最合適的。例如：姓名、郵箱地址等等。

????char 和 varchar 是非常相似且常見的字符串類型，想要把它們用對、用好，不僅要能夠理解它們各自的含義、特性，還要知道它們在使用上的區(qū)別：

??? 定義了 char (x)，如果存入的字符個數小于 x，則以空格填充，查詢時再將空格去掉（類似于 trim 操作）。所以，char 類型存儲的字符串末尾不能有空格，而varchar 則沒有這一限制。

??? char (x) 長度是固定的，不論存入什么，都會占用 x 個字節(jié)。但是 varchar 占用的字節(jié)數是存入的字符數 +1（x <= 255）或 + 2（x > 255）。

??? char 由于長度固定，不需要考慮邊界問題，檢索速度要快于 varchar

???文本?類型：text是文本數據類型，它分為四類，都是變長字符串，最大的區(qū)別是存儲空間的不同，其中：

??? tinytext：最大長度是（2^8 - 1）個字符。

??? text：最大長度是（2^16 - 1）個字符。

??? mediumtext：最大長度是（2^24 - 1）個字符。

??? longtext：最大長度是（2^32 - 1）個字符。

????最簡單的對文件數據類型的理解是：當我們要存儲的數據量比較大，就應該考慮使用文本。這里，我建議當你的數據量超過 500 個字符時，就應該考慮使用文本。另外，文本類型不能有默認值，且在創(chuàng)建索引時需要指定前多少個字符。

????日期 ：

?? Date 類型

正如這種數據類型的名稱一樣，它用于存儲日期，存儲范圍是 ‘1000-01-01’ 到‘9999-12-31’。這種數據類型比較簡單，但同時適用場景也比較有限，因為它只能存儲 “年月日”。比較常見的用途是存儲出生日期。

?? Datetime 類型

????它用于存儲時間，不僅可以表示一天中的時間，也可以用于表示兩個時間的時間間隔。它的取值范圍是‘-838:59:59’ to ‘838:59:59’。乍看起來，它的小時取值太特殊了，正常不應該是 [0, 23] 嗎 ？這是因為 time 可以表示特殊的時間間隔，MySQL 將 time 的小時范圍擴大了，而且支持負值。

????除了基本的存儲一天中的時間之外，time 允許以 “D HH:MM:SS” 的格式存儲。其中，D 的取值是 0 ~ 34。如果要存儲時間間隔，time 則會以（時間間隔 * 小時）作為小時進行存儲。它的計算公式是：D * 24 + HH。例如，插入了 “2 19:20:00”，相當于插入 “67:20:00”。

?? Timestamp 類型

????同樣用于存儲日期時間數據，與 datetime 存儲的數據格式是一樣的，它的取值范是：‘1970-01-0100:00:01.000000’ UTC 到 ‘2038-01-19 03:14:07.999999’ UTC。它與 datetime 的主要區(qū)別在于時間范圍要小一些。另外，timestamp 是與時區(qū)相關的，能夠反映 “當前時間”。當插入時間時，會先轉換為本地時區(qū)后再存儲；查詢時間時，會轉換為本地時區(qū)后再顯示。所以，不同時區(qū)的人看到的同一時間是不一樣的。

????在 MySQL 表中存儲時間（可以是日期、時間或日期時間）是非常常見的需求，但是如何合理的選擇數據類型卻也是個難題。這里我給出一個建議：通常 datetime 是最佳選擇。

理由如下：

??? 時間范圍跨度足夠大，能夠滿足所有的時間需求。

??? 即使是只用于存儲日期或時間，也可以存儲日期時間，只需要在代碼中處理即可。

避免將來需求變更時對數據表的 Schema 有所變動。

?數值：

? 整數類型：MySQL 主要支持 5 個整數類型：tinyint、smallint、mediumint、int、bigint。這些數據類型我們基本上認為它們有共同的特性，不同之處只在于存儲空間，即存儲數值的取值范圍。同時，在定義時可以使用 UNSIGNED 關鍵字規(guī)定字段只保存正值。下面，我將這幾種整數類型的特性用表格展示出來。

????由于這幾種數據類型除了取值范圍不同之外，并沒有其他的不同，所以，在使用上，根

據需要選擇 “足夠大” 的空間就可以了。另外，關于整數類型還有一個特性：顯示寬度。

例如，我們在定義 Schema 時，常常會看到類似這樣的寫法：

其中，20 和 11 就是可選的顯示寬度，這會讓 MySQL 對 SQL 標準進行擴展，當從數據庫檢索一個值時，可以把這個值延長到指定的寬度。例如，這里的 b 定義的類型為 int(11)，就可以保證 b 這一列少于 11 個字符寬度時自動使用空格填充。但同時，需要注意，定義寬度并不會影響字段的大小和存儲值的取值范圍。

?? 浮點類型

MySQL 支持兩個浮點類型：float、double。其中，float 用于表示單精度浮點數值，占用 4 個字節(jié)；double 用于表示雙精度浮點數值，占用 8 個字節(jié)。因為它們只能保存近似值（不精確的值），所以，通常也叫做非標準類型。

????float 相較于 double 類型來說，由于占據的空間小，精度較低，取值范圍也相對較小。它們的定義格式及說明如下：

??? float (M, D)：其中 M 定義顯示長度，D 定義小數位數。但是它們是可選的，且默認值是 float (10, 2)，2 是小數的位數，10 是數字的總長（包括小數）。它的小數精度可以到 24 個浮點。

??? double (M, D)：M 和 D 的含義與 float 是相同的，默認值是 double (16,4)。它的小數精度可以達到 53 位。

?? 定點類型

MySQL 中的 decimal 被稱為定點數據類型，由于它保存的是精確值，所以它通常用于精度要求非常高的計算中。另外，也可以利用 decimal 去保存比 bigint 還要大的整數值。CPU 并不支持對 decimal 的直接計算，而是 MySQL 自身實現了對 decimal 的高精度計算。底層存儲方面，MySQL 將 decimal 類型的數字使用二進制字符串存儲，每 4 個字節(jié)可以存儲 9 個數字。假如我們定義了decimal (18, 9)。

??? 則代表不包含小數點的數字總數（整數位數 + 小數位數）位數是 18，不指定的情況下默認是 10

??? 9 則代表小數的位數，如果不指定，默認是 0。

由于小數點兩邊各有 9 個數字，所以占據 2 * 4 = 8 個字節(jié)，小數點自身占用一個字節(jié)，最終，decimal (18, 9) 一共占用 9 個字節(jié)。需要注意，如果存儲的位數不夠，則小數末尾會補零。但是，如果超出了聲明的位數，則會報錯。

????由于 decimal 需要比較大的空間和計算開銷，它的計算效率也就沒有 float 和 double 那么高，所以應該只有要求精確計算的場景下才考慮去使用 decimal。

二進制：二進制數據類型理論上可以存儲任何數據，可以是文本數據，也可以存儲圖像或者其他多媒體數據。二進制數據類型相對于其他的數據類型來說，使用頻率是比較低的。MySQL一共提供了四種二進制類型：tityblob、blob、mediumblob、longblob，它們的區(qū)別只在于存儲范圍的不同。

??? tinyblob：最大支持 255 字節(jié)

??? blob：最大支持 64KB

??? mediumblob：最大支持 16MB

??? longblob：最大支持 4GB

需要注意，雖然 MySQL 提供并支持大文件存儲，但是這樣會急劇降低數據庫的性能。所以，應該謹慎使用這些數據類型，能不用的情況下盡量不用。

?3.4：?你是如何使用 MySQL 數據類型的？

????mysql數據類型應用時有一些技巧性經驗，但是這些經驗并不一定適用于所有的情況。

在做實際的選擇時，我們不僅要考慮這些技巧，也要對應到具體的需求。

????使用Not Null 和 Comment：建議所有字段類型的定義，都要指定Not null 和 Comment，為什么呢？

????MySQL在定義索引值為NULL的列時，需要額外的存儲空間。另外，在進行比較和計算時，MySQL 要對NULL值做特別的處理，使用效率較低。

????COMMENT 用于定義列的注釋信息，就好像我們在寫代碼一樣，把重要的或者不易理解的地方，加上一些注釋，方便以后查閱。

????選擇簡單數據類型：這里的 “簡單” 二字聽上去會比較奇怪，我以一個例子去說明。假如說我想在一列中存儲 10、100、201 這樣的數據，我們可以選擇使用 int 或 varchar 來存儲。但是整型要比字符型的操作復雜度小太多，那么，選擇整型（例如int）就是最簡單的數據類型。

????應用最小數據類型：這里所說的最小數據類型并不是直接選擇最小的，而是在滿足需求的同時選擇最小的。例如，要存儲事件狀態(tài)，可以選擇 tinyint；要存儲班級人數，可以選擇 smallint 等等。關于最小數據類型，它有兩大優(yōu)勢：

??? 越小的數據類型占用的磁盤、內存、CPU 緩存都會更小，存取速度也會更快。

????小的數據類型建立索引時所需要的空間也相對較小，這樣一頁中所能存儲的索引節(jié)點數量也就越多，遍歷時IO 次數就會越少，索引的性能也就越好。

????基于Decimal類型存儲小數 ：雖然我并不建議在數據庫中存儲小數，但是，在一些場景中小數不可避免，最常見的例子就是訂單的金額。由于小數本身在計算時就很復雜，而且很多時候你需要去考慮精度問題。所以，最直接的方式就是把這種管理交給數據庫。

????盡量避免使用text和blob類型：MySQL 內存臨時表并不支持 text、blob 這樣的大數據類型，如果查詢時包含有這樣的數據，則排序操作必須使用磁盤臨時表，性能會下降很多。而且對于這種數據，MySQL 還要做二次查詢（因為 MySQL 實際保存的是指針，而不是真實數據），會使 SQL 性能變得很差。

????但是，也并不是說我們一定就不能用 text 和 blob。如果確實有需求需要使用這樣的數據類型，那么在查詢時一定不要直接SELECT *，而是取出需要的列。這樣MySQL就不會去主動查詢這些數據列，也是提高性能的一種慣用手段。

????最后，還需要注意，因為 MySQL 對索引長度的限制，text 類型只能用到前綴索引，并且由于存儲的是指針，text列上不能有默認值。

總結：數據類型是 MySQL 的基礎，看起來也比較簡單，但常常也就是覺得簡單才會忽略它們的特性與限制。可以肯定的說，想要選擇正確的、合理的數據類型并不是一件簡單的事。不過，也并不需要追求完美的選型。能夠解決實際的問題，或多或少存在一些瑕疵，當然也是可以接受的。

4 MySQL數據庫及表設計

4.1： 什么是數據庫設計？

????我們所說的數據庫設計一般指的是對庫和表的設計。也就是，在對 MySQL 基本的使用中，如何根據業(yè)務需求去創(chuàng)建數據庫、創(chuàng)建數據表。

4.2：數據庫設計的目標是怎樣的？

????通俗的說，不論是 MySQL 還是其他工具也好，最基本的設計目標肯定是可用。如果可以，就在可用的基礎之上，再去追求好用。下面，我來詳細的對可用和好用的設計目標進行解讀。

????如果你設計的數據庫和數據表能夠支撐當前的業(yè)務需求，且在技術實現上沒有太大的弊端，那么，我們就可以說它是可用的。更深層的看，這個設計目標的核心其實是對需求的理解。確實，理清了需求，你會得出結論：應該存儲哪些數據、這些數據是什么類型、在代碼中怎樣使用這些數據等等。余下的建庫建表也自然就是水到渠成了。需求也許不會變化，但是隨著業(yè)務量的增長觸發(fā)數據和并發(fā)的增長，數據庫是否還能保持相對較高的性能是個值得思考的問題，同時也是衡量設計目標是否好用的重要指標。無論什么時候，我們對 MySQL（數據庫）的使用都肯定是圍繞數據的增刪改查。而這些基本的操作，當數據量加速膨脹的過程中，也會引起性能瓶頸。所以，好用的設計目標講究能夠 “預見未來”，能夠對未來做出預判。例如：將通用信息單獨使用一張表存儲、建立適當的索引等等。

????說明，規(guī)范是一種通用的建議，并不一定適用于所有的場景，一定要仔細分析需求再做出合適的取舍。

4.3：MySQL 應用時有哪些設計原則？

????鑒于 MySQL 的一些固有屬性（特性），在使用上我們通常都會遵守一些 “共識”，而這些是與具體的業(yè)務沒有相關性的。下面，我將會介紹一些通用的設計原則，它們有些是關于庫的，有些是關于表的。

?? 使用小寫的名稱，且只有英文字母：不論是庫、表還是數據列，應該是只包含英文字母的名稱，不要出現特殊字符或者是數字。這也比較好理解，英文字母不論是閱讀還是編碼都非常的便捷。另外，由于 MySQL 是大小寫不敏感的，選擇一律小寫的名稱能夠統一書寫規(guī)則，避免不必要的書寫錯誤。

?? 取一個有意義的名稱，單詞之間使用下劃線連接：除了基本的名稱書寫規(guī)范之外，取一

個有意義的名稱是非常有必要的。例如：我們需要創(chuàng)建學生表，表的名稱叫做 student 就會比 other 更易理解。當然，可能有些時候我們無法用一個單詞表達清楚想要的含義，此時，可以使用多個單詞，且單詞之間使用下劃線連接，例如：insert_time。最后，名稱不要過長，最長不要超過 32 個字符。

?? 記住 “夠用且盡量小” 的原則：很明顯，這條原則對應的是數據表列的數據類型選擇問題。占用空間少的數據類型最直接的優(yōu)勢就是減少了用戶數據存儲空間和索引存儲空間，這對于數據傳輸與檢索的性能提高有著巨大意義。

?? 不要使用物理外鍵：物理外鍵是說讓數據庫去管理表與表之間的關聯關系，而它相對的邏輯外鍵，則是我們自己用代碼去管理這種關系。這是因為物理外鍵存在兩個重大缺陷：消耗數據庫資源，降低數據庫實例可擴展性；母表一旦受損，子表很難恢復，造成數據丟失。

?? 表一定要有主鍵：MySQL 并不要求表一定要有主鍵，但是主鍵的作用是能夠唯一區(qū)分表中的每一行。沒有主鍵，更新或刪除表中的特定行將會很困難，因為沒有安全的方法保證只涉及相關的行。并且，主鍵能夠為方便擴展、高可用的數據庫系統做鋪墊。

?? 保持一致的字符集：庫、表、數據列的字符集都應該是一致的，統一為 utf8 或 utf8mb4。

字符集編碼不僅影響數據存儲，還會影響客戶端與數據庫之間的交互，最常見的問題就是字符集導致的亂碼。所以，相同的字符集更利于管理，也更方便去排查問題。

4.3：MySQL?應用時有哪些設計原則？

????字符集：創(chuàng)建數據庫時，建議指定字符集，例如

????表的個數：首先，我們可以使用如下的 SQL 語句查看系統庫 mysql 中定義了多少張表：

可以看到，mysql 庫（注意區(qū)分是系統庫）中定義了 31 張表。由于它是 MySQL 的系統庫，所以我們可以理所應當的認為它是合理的。換句話說，一個庫中至少是可以存儲 31 張表的。但是，得出這樣的結論僅僅是靠猜測，沒有任何站得住腳的依據。

????MySQL 自身并沒有對庫的容量做出限制，也就是說，你幾乎不用考慮表的數量上限問題。但是，當表的數量越多，越容易產生以下問題（以下所討論的都是單個庫）：

??? 表越多，需要維護的元數據（表結構、統計信息等）就會越多。即使是這些元數據只占據很少的空間，但是也會讓管理這些元數據變得很復雜，且通常也是不合理的需求分析造成的；

??? 表越多，可能存儲的數據量也會越大，這無疑會給數據庫造成壓力。且大量的數據聚集在同一個庫中也是非常危險的，一旦出現庫損壞，丟失的數據量也會更多。

綜上所述，我們討論了單庫中表太多的缺陷，再去結合日常的工作實踐來說，建議大家在一個庫中創(chuàng)建的表數量不要超過200。更常見的情況是，一個庫中只維護幾十到 100 張表。

4.5?：數據庫表的設計規(guī)范是怎樣的？

????1.范式與反范式：相信我們在剛開始接觸數據庫的時候就聽過范式的概念，它的核心思想是數據只出現一次，不存在信息冗余。而反范式的概念也就是破壞了范式的規(guī)范，它允許出現冗余的數據。所以，對于這個問題來說，它聚焦的點在于：冗余字段是否是可取的。對于任何給定的數據來說，我們可以設計各種各樣的存儲方案，從完全范式化到完全反范式化，或者兼顧兩者。對于范式化的設計，有著這樣的優(yōu)點：

??? 使用更少的存儲空間（限制很少有人特別在乎這點存儲空間了）

??? 由于沒有冗余存儲，增刪改查的速度相對較快（有冗余時為了保證數據的一致性還要更新冗余）

但是，如果我們想要的數據出現在兩張或者多張表中，對于范式不存在冗余的設計，就不得不采用關聯查詢。而這恰恰是反范式設計最大的優(yōu)勢，適當的冗余設計，可以減少或避免表關聯，提高查詢效率。所以，沒有冗余就未必是好的，有時為了提高工作效率（對于查詢大于更新的業(yè)務），就必須采用反范式的設計，適當的讓數據存在冗余。

????2.寬表與窄表：字面意思理解，寬表就是數據列比較多的表，而窄表剛好相反，數據列比較少。MySQL對于每張表有 4096 個列的硬限制，而真正在使用上的限制又會取決于你所使用的存儲引擎。例如：對于 InnoDB 來說，一張表最多可以有1017 列。在不考慮 “寬和窄” 的問題上來說，MySQL 和存儲引擎支持的列數目肯定是足夠的了。

????在講解寬表和窄表的優(yōu)缺點之前，我這里給出一個定義：以 40 列為界，超過 40 列的表，我們可以稱之為寬表，相對的，少于 40 列的表，我們稱之為窄表。但同時，需要知道，這里的數字是人為定義的，MySQL 規(guī)范中并沒有這種定義。我這里的劃分是基于工作經驗和總結，當然，你也可以有自己的數字界限。那么，不論是寬表還是窄表，它們一定各自都會有相應的優(yōu)缺點：

?? 窄表較多，數據列會更加分散，編寫關聯查詢的難度就會很大。

?? 數據項會有不同的安全級別，寬表中涉及的列過多，數據權限的管理會帶來很大的挑戰(zhàn)。

????窄表數據量通常較少，但是等量的數據項會創(chuàng)建更多的表，管理難度大。

??? ?寬表數據量通常較大，單表占據的存儲空間過大，會降低排序、分組等查詢的性能。

綜上所述，我們應該從多個角度去分析問題，完美的選擇幾乎是不存在的，我們在擁抱

有利之處的時候，也不可避免的會攝入弊端，也正所謂魚和熊掌不可兼得。

????3.合理應用索引：我們對索引的概念一定不會陌生，它能夠加速表數據的查詢，但是相應的，它也會占據一定的存儲空間，也就是典型的以空間換時間的優(yōu)化策略。另外，索引的存在，也會使插入、刪除、更新的性能降低，因為這些操作都會伴隨著索引的修改。所以，這一條設計規(guī)范所要追求的是空間與時間的平衡，達到既不占用過多的存儲空間，也有較高的查詢性能。

索引要建的合理，就必須要知曉并理解 MySQL 中索引創(chuàng)建和使用的特性：

?? 一定要為作為搜索條件的字段創(chuàng)建索引，不是搜索條件的字段建索引反而會降低使用性能

?? 選擇區(qū)分度高的字段作為索引字段，重復性高的字段不要加索引

?? 聯合索引存在 “最左前綴” 的特性，不要建多余的索引。

最后，如果一張表中已經存在了大量的數據，再去創(chuàng)建索引的過程會相當漫長，且可能會影響線上服務。此時，應該評估是否是在原表上增加索引還是創(chuàng)建新表并遷移數據。

參考

?? 《高性能 MySQL（第三版）》

?? 《MySQL 技術內幕：InnoDB 存儲引擎（第 2 版）》

?? https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

?? https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

?? https://dev.mysql.com/doc/refman/5.7/en/innodb-in-memory-structures.html

?? https://dev.mysql.com/doc/refman/5.7/en/innodb-on-disk-structures.html