曾經(jīng)有一位開(kāi)發(fā)者說(shuō)過(guò)一句話，很有道理：一個(gè)功能的實(shí)現(xiàn)，在數(shù)據(jù)量或并發(fā)量比較小的時(shí)候，怎么樣都可以，但一旦將數(shù)據(jù)量和并發(fā)量逐步擴(kuò)大，問(wèn)題就會(huì)逐步顯現(xiàn)出來(lái)了。

這篇文章，就帶大家梳理一下，在MySQL的數(shù)據(jù)庫(kù)表設(shè)計(jì)中常見(jiàn)的三類錯(cuò)誤。

選擇適合數(shù)據(jù)類型和長(zhǎng)度

當(dāng)我們把一個(gè)表的ID列定義為INT數(shù)據(jù)類型時(shí)，那么就要思考一個(gè)問(wèn)題，這個(gè)表的數(shù)據(jù)量是否會(huì)隨著業(yè)務(wù)的發(fā)展而快速增長(zhǎng)。

當(dāng)這個(gè)表只是一個(gè)簡(jiǎn)單的配置表或基礎(chǔ)數(shù)據(jù)表時(shí)，設(shè)置為INT類型或許沒(méi)有問(wèn)題。當(dāng)一個(gè)表存儲(chǔ)的是歷史記錄或日志記錄時(shí)，數(shù)據(jù)量必然會(huì)隨著業(yè)務(wù)的發(fā)展而快速增長(zhǎng)，該列的空間會(huì)很快被耗盡，導(dǎo)致系統(tǒng)部分功能不可用。

針對(duì)此種情況，可提前預(yù)測(cè)業(yè)務(wù)發(fā)展，將ID字段設(shè)置為BIGINT類型。

這一條規(guī)則不僅適用于數(shù)值類型，還適用于字符串類型。例如，如果試圖在一個(gè)VARCHAR(255) 的列中寫入一個(gè)包含300個(gè)字符的字符串，而MySQL正處于嚴(yán)格模式（Strict Mode，默認(rèn)為開(kāi)啟狀態(tài)），那么MySQL會(huì)返回錯(cuò)誤并拒絕寫入。如果MySQL沒(méi)有開(kāi)啟嚴(yán)格模式，超過(guò)長(zhǎng)度的數(shù)據(jù)會(huì)被截?cái)啵瑥亩鴮?dǎo)致可能重要的數(shù)據(jù)丟失。

與數(shù)據(jù)不足問(wèn)題相反，列數(shù)據(jù)的存儲(chǔ)空間過(guò)多也可能是問(wèn)題所在。雖然不會(huì)像存儲(chǔ)空間不足的情況那樣嚴(yán)重，但過(guò)度預(yù)留空間會(huì)帶來(lái)存儲(chǔ)和性能方面的影響。

比如，假設(shè)我們需要存儲(chǔ)一個(gè)五位的編碼，這種情況可以使用 INT 數(shù)據(jù)類型（存儲(chǔ) 32 位整數(shù)）來(lái)存儲(chǔ)，但這樣分配的存儲(chǔ)遠(yuǎn)遠(yuǎn)超過(guò)實(shí)際需求。此時(shí)，使用 SMALLINT 會(huì)是更好的選擇，因?yàn)樗鎯?chǔ)的是 16 位整數(shù)，足夠存儲(chǔ)這個(gè)五位的編碼。

存儲(chǔ)分配過(guò)多會(huì)引起的兩方面問(wèn)題：

• 存儲(chǔ)空間：過(guò)度分配類型對(duì)單個(gè)字段來(lái)說(shuō)存儲(chǔ)成本變化可能不是非常顯著，但對(duì)于大型表、高頻讀取或?qū)懭氲膱?chǎng)景來(lái)說(shuō)，會(huì)對(duì)磁盤 I/O 和緩存效率產(chǎn)生影響。
• 索引性能：當(dāng)字段被用于索引時(shí)，字段的存儲(chǔ)類型大小直接影響索引的存儲(chǔ)和查詢性能。使用較小的數(shù)據(jù)類型能提高索引效率。

所以，在選擇數(shù)據(jù)類型時(shí)我們需要考慮：不僅要滿足當(dāng)前數(shù)據(jù)量的需求，還要能支持未來(lái)潛在的增長(zhǎng)。

缺失索引或冗余索引

索引在 MySQL 中通過(guò)構(gòu)建一個(gè)經(jīng)過(guò)優(yōu)化的結(jié)構(gòu)來(lái)加速數(shù)據(jù)訪問(wèn)，使查詢更快地返回符合條件的數(shù)據(jù)。如果沒(méi)有利用索引，當(dāng)執(zhí)行未分頁(yè)或未定義LIMIT的查詢時(shí)，MySQL 會(huì)對(duì)表執(zhí)行掃描操作。這意味著它會(huì)從表的第一行開(kāi)始逐行讀取，直到找到匹配條件的所有行。如果某個(gè)被頻繁訪問(wèn)的大表沒(méi)有使用索引，從表掃描會(huì)帶來(lái)巨大的性能損失。

但與此同時(shí)，如果索引過(guò)多也會(huì)帶來(lái)另一方面的問(wèn)題。

創(chuàng)建的每個(gè)索引都會(huì)占用額外的存儲(chǔ)空間，因此冗余或未使用的索引會(huì)直接增加存儲(chǔ)成本。此外，當(dāng)表中的數(shù)據(jù)被更新或插入時(shí)，MySQL 會(huì)更新這些索引及其相關(guān)統(tǒng)計(jì)信息，以確保索引的準(zhǔn)確性。這可能是一項(xiàng)耗時(shí)的操作，可能導(dǎo)致不良的用戶體驗(yàn)。

選擇合適的存儲(chǔ)結(jié)構(gòu)

過(guò)去十多年，越來(lái)越多的公司選擇使用NoSQL來(lái)存儲(chǔ)半結(jié)構(gòu)化數(shù)據(jù)，以滿足快速處理大量數(shù)據(jù)的需求。這類數(shù)據(jù)存儲(chǔ)已經(jīng)有很多專業(yè)解決方案，但實(shí)際上 MySQL 在這方面也很有能力。當(dāng)隔壁字段需要采用半結(jié)構(gòu)化數(shù)據(jù)存儲(chǔ)，又沒(méi)必要引入NoSQL時(shí)，可考慮采用MySQL提供的能力來(lái)存儲(chǔ)。

大多數(shù)存儲(chǔ)在數(shù)據(jù)庫(kù)中的半結(jié)構(gòu)化數(shù)據(jù)通常是以JSON的形式表示。最簡(jiǎn)單的方式是將JSON字符串存儲(chǔ)在TEXT類型的列中，但這并不是最佳選擇。

MySQL支持一種專門用于存儲(chǔ)JSON的列類型：JSON。這種類型會(huì)將JSON數(shù)據(jù)以高效的二進(jìn)制格式存儲(chǔ)。

使用JSON而非TEXT類型有兩個(gè)關(guān)鍵好處：

• 第一，最為廣泛使用的MySQL存儲(chǔ)引擎 InnoDB 原生支持基于JSON對(duì)象內(nèi)容的查詢和過(guò)濾，這避免了在應(yīng)用代碼中手動(dòng)過(guò)濾結(jié)果的需求。
• 第二，MySQL還支持基于JSON數(shù)據(jù)創(chuàng)建索引，使得查詢更加高效，可以加速基于JSON數(shù)據(jù)返回結(jié)果的操作。

小結(jié)

有一定開(kāi)發(fā)經(jīng)驗(yàn)的朋友都知道，數(shù)據(jù)庫(kù)是系統(tǒng)最容易形成性能瓶頸問(wèn)題的點(diǎn)，因此，針對(duì)數(shù)據(jù)庫(kù)的設(shè)計(jì)和深思熟慮絕對(duì)是值得在前期投入的事。否則，一旦有大量線上數(shù)據(jù)，再進(jìn)行修改將是一件非常復(fù)雜和有風(fēng)險(xiǎn)的事。希望這篇文章能夠?yàn)榇蠹姨峁椭瑫r(shí)也能啟發(fā)大家去思考更多的數(shù)據(jù)庫(kù)設(shè)計(jì)優(yōu)化。