構(gòu)設(shè)計(jì)實(shí)戰(zhàn)：從字段選型到索引策略的避坑指南)
1. 表結(jié)構(gòu)設(shè)計(jì)為什么值得單獨(dú)拎出來(lái)講做了這么多年后端開(kāi)發(fā)我越來(lái)越覺(jué)得表結(jié)構(gòu)設(shè)計(jì)這件事被嚴(yán)重低估了。很多人剛?cè)胄械臅r(shí)候覺(jué)得建表嘛不就是CREATE TABLE寫幾個(gè)字段選個(gè)主鍵加個(gè)索引就完事了但真正在線上跑過(guò)幾年業(yè)務(wù)之后你會(huì)發(fā)現(xiàn)一個(gè)糟糕的表結(jié)構(gòu)后期帶來(lái)的維護(hù)成本是設(shè)計(jì)階段的幾十倍甚至上百倍。改字段類型要鎖表、拆字段要寫遷移腳本、查詢慢到業(yè)務(wù)方天天找你、數(shù)據(jù)對(duì)不上還得寫腳本修——這些坑我?guī)缀醪攘藗€(gè)遍。這篇文章想聊的就是MySQL表結(jié)構(gòu)設(shè)計(jì)這件事。它不是什么高深的技術(shù)但絕對(duì)是最考驗(yàn)基本功和業(yè)務(wù)理解能力的一環(huán)。適合誰(shuí)看我覺(jué)得三類人最需要一是剛?cè)胄胁痪?、還沒(méi)獨(dú)立設(shè)計(jì)過(guò)核心業(yè)務(wù)表的開(kāi)發(fā)者二是正在做系統(tǒng)重構(gòu)、需要重新梳理數(shù)據(jù)模型的工程師三是帶團(tuán)隊(duì)的技術(shù)負(fù)責(zé)人需要給團(tuán)隊(duì)定一套建表規(guī)范。不管你用的是MySQL 5.7還是8.0是InnoDB還是其他存儲(chǔ)引擎這里面的核心思路都是通用的。我打算從設(shè)計(jì)思路、字段選型、索引策略、實(shí)操流程、問(wèn)題排查幾個(gè)維度把表結(jié)構(gòu)設(shè)計(jì)這件事掰開(kāi)揉碎講清楚。每一塊我都會(huì)給出具體的做法和背后的理由而不是只丟一個(gè)結(jié)論。畢竟知道“怎么做”只能解決眼前的問(wèn)題知道“為什么這么做”才能應(yīng)對(duì)千變?nèi)f化的業(yè)務(wù)場(chǎng)景。2. 表結(jié)構(gòu)設(shè)計(jì)的整體思路與核心原則2.1 先想清楚業(yè)務(wù)再動(dòng)手寫DDL我見(jiàn)過(guò)太多人拿到需求就開(kāi)始建表字段名隨手起類型隨便選等表建完了才發(fā)現(xiàn)漏了字段、類型不對(duì)、關(guān)系沒(méi)理清。表結(jié)構(gòu)設(shè)計(jì)的第一步永遠(yuǎn)不是打開(kāi)數(shù)據(jù)庫(kù)客戶端而是把業(yè)務(wù)邏輯在紙上或者文檔里畫清楚。具體怎么做我一般會(huì)先梳理三件事實(shí)體有哪些、實(shí)體之間的關(guān)系是什么、每個(gè)實(shí)體的生命周期是怎樣的。比如做一個(gè)訂單系統(tǒng)核心實(shí)體至少有用戶、商品、訂單、訂單明細(xì)、支付記錄。訂單和用戶是多對(duì)一訂單和商品通過(guò)訂單明細(xì)形成多對(duì)多支付記錄和訂單是一對(duì)一或者一對(duì)多。這些關(guān)系理清楚了表該怎么拆、外鍵怎么放、要不要冗余字段基本就有譜了。這里有個(gè)經(jīng)驗(yàn)?zāi)墚婨R圖就畫ER圖哪怕畫得丑一點(diǎn)也沒(méi)關(guān)系。圖形化的東西能幫你發(fā)現(xiàn)很多文字描述里注意不到的問(wèn)題比如循環(huán)依賴、缺失的關(guān)聯(lián)表、一對(duì)多方向搞反了等等。我習(xí)慣用紙筆先畫一版然后跟產(chǎn)品經(jīng)理或者業(yè)務(wù)方對(duì)一遍確認(rèn)沒(méi)有理解偏差再進(jìn)入下一步。2.2 三大范式與反范式的取舍邏輯教科書上講的三大范式——原子性、唯一性、無(wú)冗余——是設(shè)計(jì)的理論基礎(chǔ)但實(shí)際工作中你如果嚴(yán)格照著做性能往往會(huì)出問(wèn)題。范式解決的是數(shù)據(jù)一致性和存儲(chǔ)效率問(wèn)題反范式解決的是查詢性能問(wèn)題兩者之間需要根據(jù)業(yè)務(wù)場(chǎng)景做權(quán)衡。舉個(gè)例子訂單表里要不要冗余用戶昵稱嚴(yán)格按范式來(lái)說(shuō)不應(yīng)該因?yàn)橛脩絷欠Q在用戶表里已經(jīng)有了訂單表只需要存用戶ID就行。但實(shí)際業(yè)務(wù)中訂單列表頁(yè)幾乎每次都要顯示用戶昵稱如果每次都去關(guān)聯(lián)用戶表在高并發(fā)場(chǎng)景下就是額外的IO開(kāi)銷。這時(shí)候在訂單表里冗余一個(gè)用戶昵稱字段用空間換時(shí)間就是合理的反范式設(shè)計(jì)。我的判斷標(biāo)準(zhǔn)是這樣的如果某個(gè)字段被高頻查詢、且更新頻率極低、且冗余帶來(lái)的數(shù)據(jù)不一致風(fēng)險(xiǎn)可控那就值得冗余。反過(guò)來(lái)如果字段更新頻繁或者一致性要求極高比如金額、庫(kù)存那就老老實(shí)實(shí)按范式來(lái)該關(guān)聯(lián)關(guān)聯(lián)該事務(wù)事務(wù)。2.3 命名規(guī)范別讓后來(lái)的自己罵現(xiàn)在的你命名這件事看起來(lái)小但影響面極大。我接手過(guò)一個(gè)老項(xiàng)目表名有叫t_user的有叫user_info的還有叫UserData的字段名更是五花八門有下劃線風(fēng)格的有駝峰風(fēng)格的有縮寫的有全拼的。每次寫查詢都得先去翻表結(jié)構(gòu)效率極低。我的建議是在項(xiàng)目啟動(dòng)階段就定死一套命名規(guī)范所有人嚴(yán)格遵守。具體來(lái)說(shuō)表名統(tǒng)一用小寫字母加下劃線比如order_detail、user_profile不用復(fù)數(shù)形式不用前綴t_除非團(tuán)隊(duì)有歷史約定。字段名同樣用小寫加下劃線布爾類型用is_開(kāi)頭時(shí)間字段用_at結(jié)尾或者_(dá)time結(jié)尾保持統(tǒng)一。索引命名要有規(guī)律普通索引用idx_字段名唯一索引用uniq_字段名這樣看到索引名就知道它建在哪個(gè)字段上。避免使用MySQL保留字作為表名或字段名比如order、group、key這些雖然加反引號(hào)能用但容易在ORM框架里出問(wèn)題。提示命名規(guī)范一旦定下來(lái)就要寫進(jìn)團(tuán)隊(duì)的開(kāi)發(fā)規(guī)范文檔里并且在代碼Review環(huán)節(jié)嚴(yán)格檢查?？孔杂X(jué)是靠不住的必須有機(jī)制保障。3. 字段類型選擇的門道與實(shí)操細(xì)節(jié)3.1 整數(shù)類型不是越大越好MySQL的整數(shù)類型有TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT占用的存儲(chǔ)空間分別是1、2、3、4、8個(gè)字節(jié)。很多人建表的時(shí)候不管三七二十一主鍵一律BIGINT其他整數(shù)一律INT這其實(shí)是一種浪費(fèi)。選擇整數(shù)類型的核心依據(jù)是字段的取值范圍和未來(lái)的增長(zhǎng)預(yù)期。比如狀態(tài)字段一般就幾種狀態(tài)用TINYINT足夠了取值范圍-128到127無(wú)符號(hào)的話0到255完全夠用。再比如年齡字段TINYINT UNSIGNED就夠0到255歲誰(shuí)也不可能活到256歲。但如果是用戶ID或者訂單ID這種可能增長(zhǎng)到很大的字段那就得用BIGINT因?yàn)镮NT UNSIGNED最大也就42億多對(duì)于大型系統(tǒng)來(lái)說(shuō)可能幾年就耗盡了。這里有個(gè)細(xì)節(jié)INT(11)里的11不是存儲(chǔ)長(zhǎng)度而是顯示寬度在MySQL 8.0里已經(jīng)廢棄了這個(gè)顯示寬度的概念。所以別再糾結(jié)INT后面括號(hào)里寫幾了寫不寫都一樣不影響存儲(chǔ)和取值范圍。3.2 字符串類型CHAR、VARCHAR和TEXT怎么選字符串類型的選擇是表結(jié)構(gòu)設(shè)計(jì)里最容易出問(wèn)題的地方。我見(jiàn)過(guò)用VARCHAR(255)存手機(jī)號(hào)的也見(jiàn)過(guò)用TEXT存用戶名的都是不合理的。先說(shuō)明確的結(jié)論定長(zhǎng)字符串用CHAR比如MD5哈希值、UUID如果去掉橫線的話、國(guó)家代碼這種長(zhǎng)度固定的。CHAR的優(yōu)點(diǎn)是讀取快因?yàn)殚L(zhǎng)度固定不需要額外的長(zhǎng)度前綴。變長(zhǎng)字符串用VARCHAR比如用戶名、郵箱、地址。VARCHAR需要額外的1到2個(gè)字節(jié)來(lái)記錄實(shí)際長(zhǎng)度但只占用實(shí)際字符所需的存儲(chǔ)空間。大文本用TEXT或LONGTEXT比如文章內(nèi)容、商品詳情、日志信息。但要注意TEXT字段不能有默認(rèn)值而且查詢時(shí)如果用到TEXT字段排序或者分組性能會(huì)很差。關(guān)于VARCHAR的長(zhǎng)度選擇我的經(jīng)驗(yàn)是在滿足業(yè)務(wù)需求的前提下盡量小但要留出合理的余量。比如用戶名如果業(yè)務(wù)規(guī)定最長(zhǎng)20個(gè)字符那VARCHAR(32)或者VARCHAR(64)都可以沒(méi)必要直接上VARCHAR(255)。因?yàn)閂ARCHAR的長(zhǎng)度會(huì)影響索引的長(zhǎng)度限制特別是在聯(lián)合索引中過(guò)長(zhǎng)的VARCHAR會(huì)導(dǎo)致索引創(chuàng)建失敗或者效率降低。還有一個(gè)坑MySQL的VARCHAR長(zhǎng)度指的是字符數(shù)不是字節(jié)數(shù)。在utf8mb4字符集下一個(gè)VARCHAR(255)的字段最大可能占用255乘以4等于1020個(gè)字節(jié)。而InnoDB索引的單列長(zhǎng)度限制是767字節(jié)在COMPACT行格式下或者3072字節(jié)在DYNAMIC行格式下。所以如果你要在VARCHAR字段上建索引長(zhǎng)度不能太大。3.3 時(shí)間類型DATETIME和TIMESTAMP的抉擇時(shí)間字段的選擇也是高頻問(wèn)題。MySQL里主要用DATETIME和TIMESTAMP兩種類型它們有幾個(gè)關(guān)鍵區(qū)別對(duì)比項(xiàng)DATETIMETIMESTAMP存儲(chǔ)空間5到8字節(jié)取決于精度4到7字節(jié)取決于精度取值范圍1000-01-01到9999-12-311970-01-01到2038-01-19時(shí)區(qū)處理不受時(shí)區(qū)影響存什么讀什么受時(shí)區(qū)影響存儲(chǔ)時(shí)轉(zhuǎn)為UTC讀取時(shí)轉(zhuǎn)為當(dāng)前時(shí)區(qū)默認(rèn)值可以設(shè)置默認(rèn)值可以設(shè)置默認(rèn)值和自動(dòng)更新適用場(chǎng)景業(yè)務(wù)時(shí)間、生日、有效期等創(chuàng)建時(shí)間、更新時(shí)間等系統(tǒng)時(shí)間我的建議是業(yè)務(wù)相關(guān)的時(shí)間字段用DATETIME系統(tǒng)自動(dòng)維護(hù)的時(shí)間字段用TIMESTAMP。比如訂單創(chuàng)建時(shí)間如果用TIMESTAMP2038年之后就會(huì)溢出雖然現(xiàn)在離2038年還有十幾年但誰(shuí)也不想給自己的系統(tǒng)埋一個(gè)定時(shí)炸彈。而created_at和updated_at這種字段用TIMESTAMP可以自動(dòng)更新省去手動(dòng)維護(hù)的麻煩。注意MySQL 8.0.19之后支持DATETIME的默認(rèn)值和自動(dòng)更新所以如果你用的是較新版本也可以統(tǒng)一用DATETIME通過(guò)DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP來(lái)實(shí)現(xiàn)自動(dòng)維護(hù)。3.4 金額字段為什么不能用FLOAT和DOUBLE金額字段用浮點(diǎn)數(shù)存儲(chǔ)是一個(gè)經(jīng)典錯(cuò)誤。FLOAT和DOUBLE是二進(jìn)制浮點(diǎn)數(shù)無(wú)法精確表示十進(jìn)制小數(shù)比如0.1在二進(jìn)制里是無(wú)限循環(huán)的。這會(huì)導(dǎo)致什么后果你存進(jìn)去100.1讀出來(lái)可能是100.09999999999999累加幾次之后誤差會(huì)越來(lái)越大對(duì)賬的時(shí)候能把你逼瘋。金額字段的正確做法是用DECIMAL類型或者用整數(shù)存儲(chǔ)最小貨幣單位。比如金額精確到分那就用INT或者BIGINT存儲(chǔ)單位是分顯示的時(shí)候除以100就行。如果一定要用DECIMAL建議用DECIMAL(M,2)M根據(jù)業(yè)務(wù)規(guī)模來(lái)定一般DECIMAL(12,2)可以支持到百億級(jí)別夠用了。DECIMAL的存儲(chǔ)方式?jīng)Q定了它是精確的不會(huì)出現(xiàn)浮點(diǎn)數(shù)那種精度丟失問(wèn)題。但DECIMAL的運(yùn)算速度比整數(shù)慢所以如果對(duì)性能要求極高用整數(shù)存分也是很好的選擇。4. 索引設(shè)計(jì)與主鍵策略的實(shí)戰(zhàn)經(jīng)驗(yàn)4.1 主鍵自增還是業(yè)務(wù)主鍵主鍵的選擇是表結(jié)構(gòu)設(shè)計(jì)中最關(guān)鍵的決策之一。MySQL的InnoDB存儲(chǔ)引擎是索引組織表數(shù)據(jù)按照主鍵順序存儲(chǔ)在B樹(shù)的葉子節(jié)點(diǎn)上。這意味著主鍵的選擇直接影響數(shù)據(jù)的物理存儲(chǔ)順序和插入性能。自增主鍵AUTO_INCREMENT的好處是插入時(shí)總是追加到B樹(shù)的最右邊不會(huì)引起頁(yè)分裂寫入性能高而且主鍵長(zhǎng)度小二級(jí)索引占用的空間也小。業(yè)務(wù)主鍵比如訂單號(hào)、用戶ID的好處是全局唯一、方便分庫(kù)分表但如果是無(wú)序的比如UUID插入時(shí)會(huì)導(dǎo)致隨機(jī)寫入引起大量的頁(yè)分裂和碎片性能很差。我的建議是絕大多數(shù)場(chǎng)景下用自增BIGINT做主鍵業(yè)務(wù)上的唯一標(biāo)識(shí)加唯一索引。這樣既保證了寫入性能又滿足了業(yè)務(wù)唯一性要求。如果確實(shí)需要分布式ID可以用雪花算法生成有序的BIGINT既保證了全局唯一又保證了插入順序。提示自增主鍵在分庫(kù)分表場(chǎng)景下會(huì)有問(wèn)題因?yàn)椴煌制淖栽鯥D可能重復(fù)。這時(shí)候可以考慮用步長(zhǎng)不同的自增ID或者直接用雪花算法。4.2 聯(lián)合索引的最左前綴原則聯(lián)合索引是提高多條件查詢性能的利器但用不好也會(huì)適得其反。最核心的原則就是最左前綴原則聯(lián)合索引(a, b, c)可以支持a、ab、abc的查詢條件但不支持b、c、bc的查詢條件。這個(gè)原則背后的原理是B樹(shù)的排序方式。聯(lián)合索引按照第一個(gè)字段排序第一個(gè)字段相同的情況下按第二個(gè)字段排序以此類推。所以如果你查詢的時(shí)候沒(méi)有用到第一個(gè)字段就無(wú)法利用索引的有序性。設(shè)計(jì)聯(lián)合索引的時(shí)候我一般會(huì)考慮幾個(gè)因素字段的區(qū)分度、查詢的頻率、字段的長(zhǎng)度。區(qū)分度高的字段放在前面查詢頻率高的字段放在前面字段長(zhǎng)度短的放在前面。但這也不是絕對(duì)的有時(shí)候?yàn)榱烁采w更多的查詢場(chǎng)景需要做一些權(quán)衡。舉個(gè)例子假設(shè)有一個(gè)用戶表經(jīng)常有這兩種查詢按手機(jī)號(hào)查用戶、按手機(jī)號(hào)加狀態(tài)查用戶。那聯(lián)合索引(phone, status)就能同時(shí)覆蓋這兩種查詢。但如果查詢是status 1 AND phone xxx雖然條件順序反了MySQL的優(yōu)化器會(huì)自動(dòng)調(diào)整順序仍然能用上索引。但如果查詢是status 1那就用不上這個(gè)聯(lián)合索引了因?yàn)樘^(guò)了最左的phone字段。4.3 覆蓋索引減少回表操作的利器覆蓋索引是指查詢的所有字段都包含在索引中不需要回表查詢聚簇索引。覆蓋索引能顯著減少IO操作提升查詢性能特別是在范圍查詢和排序場(chǎng)景下效果明顯。比如有一個(gè)訂單表經(jīng)常需要按用戶ID查詢訂單號(hào)和訂單金額。如果建一個(gè)聯(lián)合索引(user_id, order_no, amount)那么SELECT order_no, amount FROM orders WHERE user_id 123這個(gè)查詢就可以直接從索引中獲取數(shù)據(jù)不需要回表。但覆蓋索引也不是越多越好因?yàn)樗饕旧硪舱加么鎯?chǔ)空間而且會(huì)降低寫入性能。每增加一個(gè)索引插入、更新、刪除操作都需要維護(hù)這個(gè)索引。所以索引的數(shù)量要控制一般單表索引不超過(guò)5到6個(gè)具體要根據(jù)查詢頻率和寫入頻率來(lái)權(quán)衡。4.4 索引失效的常見(jiàn)場(chǎng)景建了索引不代表查詢就一定能用上有很多情況會(huì)導(dǎo)致索引失效。我整理了幾個(gè)最常見(jiàn)的在索引字段上做運(yùn)算或函數(shù)操作比如WHERE YEAR(created_at) 2024這會(huì)導(dǎo)致索引失效應(yīng)該改成WHERE created_at 2024-01-01 AND created_at 2025-01-01。隱式類型轉(zhuǎn)換比如字段是VARCHAR類型查詢時(shí)傳了數(shù)字MySQL會(huì)做隱式轉(zhuǎn)換導(dǎo)致索引失效。WHERE phone 13800138000和WHERE phone 13800138000的結(jié)果可能完全不同。使用不等于條件WHERE status ! 1通常會(huì)導(dǎo)致全表掃描因?yàn)椴坏扔跅l件無(wú)法利用索引的有序性。LIKE以通配符開(kāi)頭WHERE name LIKE %張三無(wú)法使用索引但WHERE name LIKE 張三%可以。OR條件連接非索引字段如果OR的一邊是索引字段另一邊不是整個(gè)查詢可能走全表掃描。注意索引失效的判斷不能只靠理論最終還是要看EXPLAIN的執(zhí)行計(jì)劃。MySQL的優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息做決策有時(shí)候理論上應(yīng)該走索引的查詢優(yōu)化器可能因?yàn)閿?shù)據(jù)分布的原因選擇全表掃描。5. 完整建表實(shí)操流程與案例拆解5.1 從需求到DDL的完整推演過(guò)程光講理論不夠直觀我拿一個(gè)簡(jiǎn)化的電商訂單場(chǎng)景來(lái)完整走一遍設(shè)計(jì)流程。假設(shè)需求是這樣的用戶可以下單購(gòu)買商品一個(gè)訂單可以包含多個(gè)商品需要記錄訂單狀態(tài)、支付信息、收貨地址還要支持按用戶查詢訂單列表、按狀態(tài)篩選訂單、按時(shí)間范圍統(tǒng)計(jì)訂單。第一步梳理實(shí)體和關(guān)系。核心實(shí)體有用戶、商品、訂單、訂單明細(xì)。訂單和用戶是多對(duì)一訂單和商品通過(guò)訂單明細(xì)形成多對(duì)多。支付信息和收貨地址可以作為訂單的屬性也可以單獨(dú)建表這里為了簡(jiǎn)化先作為訂單表的字段。第二步確定每個(gè)實(shí)體的字段。用戶表需要用戶ID、用戶名、手機(jī)號(hào)、郵箱、創(chuàng)建時(shí)間等。商品表需要商品ID、商品名稱、價(jià)格、庫(kù)存、狀態(tài)等。訂單表需要訂單ID、用戶ID、訂單號(hào)、總金額、訂單狀態(tài)、支付方式、收貨人信息、創(chuàng)建時(shí)間、更新時(shí)間等。訂單明細(xì)表需要明細(xì)ID、訂單ID、商品ID、商品名稱冗余、單價(jià)冗余、數(shù)量、小計(jì)金額等。第三步選擇字段類型。訂單ID用BIGINT自增訂單號(hào)用VARCHAR(32)加唯一索引總金額用DECIMAL(12,2)訂單狀態(tài)用TINYINT收貨人姓名用VARCHAR(32)手機(jī)號(hào)用VARCHAR(20)地址用VARCHAR(255)。訂單明細(xì)里的商品名稱冗余一份用VARCHAR(128)單價(jià)冗余一份用DECIMAL(10,2)。第四步設(shè)計(jì)索引。訂單表的主鍵是訂單ID唯一索引在訂單號(hào)上普通索引有idx_user_id、idx_status_created_at、idx_created_at。訂單明細(xì)表的主鍵是明細(xì)ID普通索引有idx_order_id、idx_product_id。5.2 建表語(yǔ)句的完整示例與逐行解讀基于上面的設(shè)計(jì)完整的建表語(yǔ)句大概長(zhǎng)這樣CREATE TABLE order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 訂單ID, order_no VARCHAR(32) NOT NULL COMMENT 訂單號(hào), user_id BIGINT UNSIGNED NOT NULL COMMENT 用戶ID, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 訂單總金額, status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 訂單狀態(tài)0待支付1已支付2已發(fā)貨3已完成4已取消, pay_method TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 支付方式0未支付1支付寶2微信3銀行卡, receiver_name VARCHAR(32) NOT NULL DEFAULT COMMENT 收貨人姓名, receiver_phone VARCHAR(20) NOT NULL DEFAULT COMMENT 收貨人手機(jī)號(hào), receiver_address VARCHAR(255) NOT NULL DEFAULT COMMENT 收貨地址, remark VARCHAR(255) NOT NULL DEFAULT COMMENT 訂單備注, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時(shí)間, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新時(shí)間, PRIMARY KEY (id), UNIQUE KEY uniq_order_no (order_no), KEY idx_user_id (user_id), KEY idx_status_created_at (status, created_at), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT訂單表;逐行解讀一下幾個(gè)關(guān)鍵點(diǎn)。id用BIGINT UNSIGNED因?yàn)橛唵瘟靠赡芎艽骍NSIGNED可以讓取值范圍翻倍。order_no用VARCHAR(32)一般訂單號(hào)不會(huì)超過(guò)32個(gè)字符加唯一索引保證不重復(fù)。total_amount用DECIMAL(12,2)支持到百億級(jí)別精確到分。status用TINYINT UNSIGNED0到255足夠表示所有狀態(tài)。created_at和updated_at用DATETIME配合默認(rèn)值和自動(dòng)更新省去手動(dòng)維護(hù)。索引方面idx_status_created_at是一個(gè)聯(lián)合索引支持按狀態(tài)篩選并按時(shí)間排序的查詢比如“查詢所有待支付的訂單按創(chuàng)建時(shí)間倒序”。idx_created_at單獨(dú)建一個(gè)支持純時(shí)間范圍的統(tǒng)計(jì)查詢。idx_user_id支持按用戶查訂單列表。5.3 建表后的自查清單建完表之后別急著往生產(chǎn)環(huán)境推先做一輪自查。我一般會(huì)檢查這幾項(xiàng)所有字段是否都有NOT NULL約束和默認(rèn)值除了TEXT和BLOB類型其他字段盡量都設(shè)NOT NULL避免NULL值帶來(lái)的索引和查詢問(wèn)題。所有字段和表是否有COMMENT注釋是給后來(lái)人看的也是給自己的備忘特別是狀態(tài)字段的枚舉值一定要寫清楚。字符集是否統(tǒng)一為utf8mb4utf8mb4支持完整的Unicode字符包括emoji而utf8實(shí)際上是utf8mb3不支持。統(tǒng)一字符集可以避免亂碼和隱式轉(zhuǎn)換問(wèn)題。主鍵是否自增是否有業(yè)務(wù)唯一鍵主鍵用自增BIGINT業(yè)務(wù)唯一鍵加唯一索引這是標(biāo)配。索引是否覆蓋了主要查詢場(chǎng)景用EXPLAIN驗(yàn)證一下核心查詢的執(zhí)行計(jì)劃確保走了索引。表名和字段名是否符合命名規(guī)范對(duì)照?qǐng)F(tuán)隊(duì)的命名規(guī)范文檔逐項(xiàng)檢查。6. 常見(jiàn)問(wèn)題排查與避坑經(jīng)驗(yàn)實(shí)錄6.1 表結(jié)構(gòu)變更引發(fā)的線上事故表結(jié)構(gòu)變更是一個(gè)高風(fēng)險(xiǎn)操作特別是在數(shù)據(jù)量大的表上。我經(jīng)歷過(guò)一次事故在一個(gè)幾千萬(wàn)行的表上加了一個(gè)字段用的是ALTER TABLE ADD COLUMN結(jié)果鎖表鎖了十幾分鐘期間所有寫入全部阻塞業(yè)務(wù)方電話直接打爆。后來(lái)我總結(jié)了幾個(gè)安全變更的原則大表加字段盡量用ALGORITHMINPLACEMySQL 5.6之后支持在線DDL但并不是所有操作都支持。加字段、加索引一般可以在線進(jìn)行但改字段類型、改字符集通常需要拷貝表。變更前先在從庫(kù)或者測(cè)試環(huán)境驗(yàn)證確認(rèn)執(zhí)行時(shí)間和鎖表情況?？梢杂胮t-online-schema-change或者gh-ost這類工具來(lái)做在線變更它們通過(guò)創(chuàng)建影子表、逐步同步數(shù)據(jù)的方式避免長(zhǎng)時(shí)間鎖表。變更盡量安排在低峰期并且準(zhǔn)備好回滾方案。如果變更過(guò)程中出現(xiàn)問(wèn)題要能快速回滾。提示MySQL 8.0的在線DDL能力比5.7強(qiáng)了不少但依然有局限性。在執(zhí)行DDL之前一定要查清楚當(dāng)前版本支持哪些在線操作別想當(dāng)然。6.2 字段類型選錯(cuò)導(dǎo)致的性能問(wèn)題字段類型選錯(cuò)是另一個(gè)高頻問(wèn)題。我見(jiàn)過(guò)一個(gè)案例用戶表的手機(jī)號(hào)字段用了BIGINT存儲(chǔ)查詢的時(shí)候傳的是字符串導(dǎo)致隱式類型轉(zhuǎn)換索引失效查詢從毫秒級(jí)變成了秒級(jí)。改成VARCHAR之后索引正常使用性能恢復(fù)。還有一個(gè)案例訂單表的訂單號(hào)用了VARCHAR(255)聯(lián)合索引里包含這個(gè)字段結(jié)果索引長(zhǎng)度超過(guò)了限制創(chuàng)建失敗。后來(lái)把訂單號(hào)改成VARCHAR(32)問(wèn)題解決。字段類型選錯(cuò)的影響是多方面的可能導(dǎo)致索引失效、可能浪費(fèi)存儲(chǔ)空間、可能導(dǎo)致數(shù)據(jù)精度丟失、可能影響排序和比較的結(jié)果。所以建表的時(shí)候一定要根據(jù)業(yè)務(wù)的實(shí)際需求來(lái)選擇類型不要圖省事一律用最大的類型。6.3 索引過(guò)多導(dǎo)致的寫入性能下降索引不是越多越好這個(gè)道理很多人知道但實(shí)際工作中還是容易犯。我見(jiàn)過(guò)一個(gè)表建了十幾個(gè)索引查詢確實(shí)快了但插入和更新的性能慘不忍睹因?yàn)槊看螌懭攵家S護(hù)所有索引。索引的數(shù)量需要根據(jù)讀寫比例來(lái)權(quán)衡。如果是讀多寫少的表比如商品表、配置表可以適當(dāng)多建幾個(gè)索引。如果是寫多讀少的表比如日志表、流水表索引就要精簡(jiǎn)只保留最核心的查詢所需的索引。另外聯(lián)合索引可以替代多個(gè)單列索引。比如(a, b)這個(gè)聯(lián)合索引可以支持a的查詢也可以支持ab的查詢就不需要單獨(dú)再建一個(gè)a的索引了。但要注意如果b也需要單獨(dú)查詢那(a, b)就不能替代b的索引因?yàn)椴粷M足最左前綴原則。6.4 常見(jiàn)問(wèn)題速查表問(wèn)題現(xiàn)象可能原因排查方法解決方案查詢突然變慢索引失效、數(shù)據(jù)量增長(zhǎng)、統(tǒng)計(jì)信息過(guò)期用EXPLAIN查看執(zhí)行計(jì)劃優(yōu)化查詢語(yǔ)句、重建索引、更新統(tǒng)計(jì)信息插入性能下降索引過(guò)多、主鍵無(wú)序、鎖競(jìng)爭(zhēng)查看寫入QPS和鎖等待時(shí)間精簡(jiǎn)索引、改用自增主鍵、優(yōu)化事務(wù)粒度表結(jié)構(gòu)變更鎖表DDL操作不支持在線執(zhí)行查看MySQL版本和DDL算法使用在線DDL工具、低峰期執(zhí)行數(shù)據(jù)精度丟失用了FLOAT/DOUBLE存金額對(duì)比存儲(chǔ)值和讀取值改用DECIMAL或整數(shù)存儲(chǔ)索引創(chuàng)建失敗索引長(zhǎng)度超過(guò)限制查看字段類型和字符集縮短字段長(zhǎng)度、改用前綴索引字符亂碼字符集不統(tǒng)一查看表、字段、連接的字符集統(tǒng)一為utf8mb46.5 幾個(gè)容易被忽略的細(xì)節(jié)最后分享幾個(gè)我在實(shí)際工作中踩過(guò)的坑都是些小細(xì)節(jié)但影響不小。第一個(gè)是NULL值的問(wèn)題。MySQL的索引對(duì)NULL值的處理比較特殊IS NULL和IS NOT NULL的查詢?cè)谟行┣闆r下無(wú)法有效利用索引。而且NULL值參與運(yùn)算和比較時(shí)結(jié)果往往是NULL容易導(dǎo)致邏輯錯(cuò)誤。所以我的建議是除了確實(shí)需要表示“未知”的字段其他字段盡量設(shè)NOT NULL并給默認(rèn)值。第二個(gè)是自增ID的回繞問(wèn)題。INT UNSIGNED的最大值是42億多如果表的數(shù)據(jù)量增長(zhǎng)很快幾年就可能耗盡。一旦耗盡插入就會(huì)報(bào)錯(cuò)。所以對(duì)于可能快速增長(zhǎng)的表主鍵直接用BIGINT UNSIGNED別省那4個(gè)字節(jié)。第三個(gè)是字符集的選擇。utf8mb4是現(xiàn)在的主流選擇但要注意排序規(guī)則的選擇。utf8mb4_general_ci性能稍好但不支持某些語(yǔ)言的精確排序utf8mb4_unicode_ci或者utf8mb4_0900_ai_ciMySQL 8.0默認(rèn)排序更準(zhǔn)確但性能稍差。一般業(yè)務(wù)用utf8mb4_general_ci就夠了如果涉及多語(yǔ)言排序再考慮用unicode的排序規(guī)則。第四個(gè)是表注釋和字段注釋。這東西平時(shí)沒(méi)人看但出問(wèn)題的時(shí)候就是救命稻草。特別是狀態(tài)字段的枚舉值一定要在注釋里寫清楚每個(gè)值代表什么。我接手過(guò)一個(gè)項(xiàng)目狀態(tài)字段的注釋只寫了“狀態(tài)”具體0、1、2、3分別是什么全靠猜最后只能去翻代碼。第五個(gè)是預(yù)留字段的問(wèn)題。有些團(tuán)隊(duì)喜歡在建表的時(shí)候預(yù)留幾個(gè)字段比如ext1、ext2、ext3想著以后擴(kuò)展用。我的經(jīng)驗(yàn)是預(yù)留字段大概率用不上或者用上了也不知道當(dāng)初是干嘛的。與其預(yù)留字段不如把表結(jié)構(gòu)設(shè)計(jì)得靈活一點(diǎn)該拆表拆表該加字段加字段現(xiàn)在在線DDL已經(jīng)比較成熟了加字段的成本沒(méi)有想象中那么高。我在實(shí)際項(xiàng)目中的體會(huì)是表結(jié)構(gòu)設(shè)計(jì)這件事經(jīng)驗(yàn)比理論重要但理論是基礎(chǔ)。沒(méi)有理論指導(dǎo)經(jīng)驗(yàn)就是瞎試只有理論沒(méi)有經(jīng)驗(yàn)設(shè)計(jì)出來(lái)的表往往中看不中用。最好的方式是在項(xiàng)目中不斷實(shí)踐、不斷復(fù)盤每次遇到問(wèn)題都記錄下來(lái)慢慢就形成自己的設(shè)計(jì)直覺(jué)了。 MySQL表結(jié)構(gòu)設(shè)計(jì)實(shí)戰(zhàn)：從字段選型到索引策略的避坑指南
1. 表結(jié)構(gòu)設(shè)計(jì)為什么值得單獨(dú)拎出來(lái)講做了這么多年后端開(kāi)發(fā)我越來(lái)越覺(jué)得表結(jié)構(gòu)設(shè)計(jì)這件事被嚴(yán)重低估了。很多人剛?cè)胄械臅r(shí)候覺(jué)得建表嘛不就是CREATE TABLE寫幾個(gè)字段選個(gè)主鍵加個(gè)索引就完事了但真正在線上跑過(guò)幾年業(yè)務(wù)之后你會(huì)發(fā)現(xiàn)一個(gè)糟糕的表結(jié)構(gòu)后期帶來(lái)的維護(hù)成本是設(shè)計(jì)階段的幾十倍甚至上百倍。改字段類型要鎖表、拆字段要寫遷移腳本、查詢慢到業(yè)務(wù)方天天找你、數(shù)據(jù)對(duì)不上還得寫腳本修——這些坑我?guī)缀醪攘藗€(gè)遍。這篇文章想聊的就是MySQL表結(jié)構(gòu)設(shè)計(jì)這件事。它不是什么高深的技術(shù)但絕對(duì)是最考驗(yàn)基本功和業(yè)務(wù)理解能力的一環(huán)。適合誰(shuí)看我覺(jué)得三類人最需要一是剛?cè)胄胁痪?、還沒(méi)獨(dú)立設(shè)計(jì)過(guò)核心業(yè)務(wù)表的開(kāi)發(fā)者二是正在做系統(tǒng)重構(gòu)、需要重新梳理數(shù)據(jù)模型的工程師三是帶團(tuán)隊(duì)的技術(shù)負(fù)責(zé)人需要給團(tuán)隊(duì)定一套建表規(guī)范。不管你用的是MySQL 5.7還是8.0是InnoDB還是其他存儲(chǔ)引擎這里面的核心思路都是通用的。我打算從設(shè)計(jì)思路、字段選型、索引策略、實(shí)操流程、問(wèn)題排查幾個(gè)維度把表結(jié)構(gòu)設(shè)計(jì)這件事掰開(kāi)揉碎講清楚。每一塊我都會(huì)給出具體的做法和背后的理由而不是只丟一個(gè)結(jié)論。畢竟知道“怎么做”只能解決眼前的問(wèn)題知道“為什么這么做”才能應(yīng)對(duì)千變?nèi)f化的業(yè)務(wù)場(chǎng)景。2. 表結(jié)構(gòu)設(shè)計(jì)的整體思路與核心原則2.1 先想清楚業(yè)務(wù)再動(dòng)手寫DDL我見(jiàn)過(guò)太多人拿到需求就開(kāi)始建表字段名隨手起類型隨便選等表建完了才發(fā)現(xiàn)漏了字段、類型不對(duì)、關(guān)系沒(méi)理清。表結(jié)構(gòu)設(shè)計(jì)的第一步永遠(yuǎn)不是打開(kāi)數(shù)據(jù)庫(kù)客戶端而是把業(yè)務(wù)邏輯在紙上或者文檔里畫清楚。具體怎么做我一般會(huì)先梳理三件事實(shí)體有哪些、實(shí)體之間的關(guān)系是什么、每個(gè)實(shí)體的生命周期是怎樣的。比如做一個(gè)訂單系統(tǒng)核心實(shí)體至少有用戶、商品、訂單、訂單明細(xì)、支付記錄。訂單和用戶是多對(duì)一訂單和商品通過(guò)訂單明細(xì)形成多對(duì)多支付記錄和訂單是一對(duì)一或者一對(duì)多。這些關(guān)系理清楚了表該怎么拆、外鍵怎么放、要不要冗余字段基本就有譜了。這里有個(gè)經(jīng)驗(yàn)?zāi)墚婨R圖就畫ER圖哪怕畫得丑一點(diǎn)也沒(méi)關(guān)系。圖形化的東西能幫你發(fā)現(xiàn)很多文字描述里注意不到的問(wèn)題比如循環(huán)依賴、缺失的關(guān)聯(lián)表、一對(duì)多方向搞反了等等。我習(xí)慣用紙筆先畫一版然后跟產(chǎn)品經(jīng)理或者業(yè)務(wù)方對(duì)一遍確認(rèn)沒(méi)有理解偏差再進(jìn)入下一步。2.2 三大范式與反范式的取舍邏輯教科書上講的三大范式——原子性、唯一性、無(wú)冗余——是設(shè)計(jì)的理論基礎(chǔ)但實(shí)際工作中你如果嚴(yán)格照著做性能往往會(huì)出問(wèn)題。范式解決的是數(shù)據(jù)一致性和存儲(chǔ)效率問(wèn)題反范式解決的是查詢性能問(wèn)題兩者之間需要根據(jù)業(yè)務(wù)場(chǎng)景做權(quán)衡。舉個(gè)例子訂單表里要不要冗余用戶昵稱嚴(yán)格按范式來(lái)說(shuō)不應(yīng)該因?yàn)橛脩絷欠Q在用戶表里已經(jīng)有了訂單表只需要存用戶ID就行。但實(shí)際業(yè)務(wù)中訂單列表頁(yè)幾乎每次都要顯示用戶昵稱如果每次都去關(guān)聯(lián)用戶表在高并發(fā)場(chǎng)景下就是額外的IO開(kāi)銷。這時(shí)候在訂單表里冗余一個(gè)用戶昵稱字段用空間換時(shí)間就是合理的反范式設(shè)計(jì)。我的判斷標(biāo)準(zhǔn)是這樣的如果某個(gè)字段被高頻查詢、且更新頻率極低、且冗余帶來(lái)的數(shù)據(jù)不一致風(fēng)險(xiǎn)可控那就值得冗余。反過(guò)來(lái)如果字段更新頻繁或者一致性要求極高比如金額、庫(kù)存那就老老實(shí)實(shí)按范式來(lái)該關(guān)聯(lián)關(guān)聯(lián)該事務(wù)事務(wù)。2.3 命名規(guī)范別讓后來(lái)的自己罵現(xiàn)在的你命名這件事看起來(lái)小但影響面極大。我接手過(guò)一個(gè)老項(xiàng)目表名有叫t_user的有叫user_info的還有叫UserData的字段名更是五花八門有下劃線風(fēng)格的有駝峰風(fēng)格的有縮寫的有全拼的。每次寫查詢都得先去翻表結(jié)構(gòu)效率極低。我的建議是在項(xiàng)目啟動(dòng)階段就定死一套命名規(guī)范所有人嚴(yán)格遵守。具體來(lái)說(shuō)表名統(tǒng)一用小寫字母加下劃線比如order_detail、user_profile不用復(fù)數(shù)形式不用前綴t_除非團(tuán)隊(duì)有歷史約定。字段名同樣用小寫加下劃線布爾類型用is_開(kāi)頭時(shí)間字段用_at結(jié)尾或者_(dá)time結(jié)尾保持統(tǒng)一。索引命名要有規(guī)律普通索引用idx_字段名唯一索引用uniq_字段名這樣看到索引名就知道它建在哪個(gè)字段上。避免使用MySQL保留字作為表名或字段名比如order、group、key這些雖然加反引號(hào)能用但容易在ORM框架里出問(wèn)題。提示命名規(guī)范一旦定下來(lái)就要寫進(jìn)團(tuán)隊(duì)的開(kāi)發(fā)規(guī)范文檔里并且在代碼Review環(huán)節(jié)嚴(yán)格檢查?？孔杂X(jué)是靠不住的必須有機(jī)制保障。3. 字段類型選擇的門道與實(shí)操細(xì)節(jié)3.1 整數(shù)類型不是越大越好MySQL的整數(shù)類型有TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT占用的存儲(chǔ)空間分別是1、2、3、4、8個(gè)字節(jié)。很多人建表的時(shí)候不管三七二十一主鍵一律BIGINT其他整數(shù)一律INT這其實(shí)是一種浪費(fèi)。選擇整數(shù)類型的核心依據(jù)是字段的取值范圍和未來(lái)的增長(zhǎng)預(yù)期。比如狀態(tài)字段一般就幾種狀態(tài)用TINYINT足夠了取值范圍-128到127無(wú)符號(hào)的話0到255完全夠用。再比如年齡字段TINYINT UNSIGNED就夠0到255歲誰(shuí)也不可能活到256歲。但如果是用戶ID或者訂單ID這種可能增長(zhǎng)到很大的字段那就得用BIGINT因?yàn)镮NT UNSIGNED最大也就42億多對(duì)于大型系統(tǒng)來(lái)說(shuō)可能幾年就耗盡了。這里有個(gè)細(xì)節(jié)INT(11)里的11不是存儲(chǔ)長(zhǎng)度而是顯示寬度在MySQL 8.0里已經(jīng)廢棄了這個(gè)顯示寬度的概念。所以別再糾結(jié)INT后面括號(hào)里寫幾了寫不寫都一樣不影響存儲(chǔ)和取值范圍。3.2 字符串類型CHAR、VARCHAR和TEXT怎么選字符串類型的選擇是表結(jié)構(gòu)設(shè)計(jì)里最容易出問(wèn)題的地方。我見(jiàn)過(guò)用VARCHAR(255)存手機(jī)號(hào)的也見(jiàn)過(guò)用TEXT存用戶名的都是不合理的。先說(shuō)明確的結(jié)論定長(zhǎng)字符串用CHAR比如MD5哈希值、UUID如果去掉橫線的話、國(guó)家代碼這種長(zhǎng)度固定的。CHAR的優(yōu)點(diǎn)是讀取快因?yàn)殚L(zhǎng)度固定不需要額外的長(zhǎng)度前綴。變長(zhǎng)字符串用VARCHAR比如用戶名、郵箱、地址。VARCHAR需要額外的1到2個(gè)字節(jié)來(lái)記錄實(shí)際長(zhǎng)度但只占用實(shí)際字符所需的存儲(chǔ)空間。大文本用TEXT或LONGTEXT比如文章內(nèi)容、商品詳情、日志信息。但要注意TEXT字段不能有默認(rèn)值而且查詢時(shí)如果用到TEXT字段排序或者分組性能會(huì)很差。關(guān)于VARCHAR的長(zhǎng)度選擇我的經(jīng)驗(yàn)是在滿足業(yè)務(wù)需求的前提下盡量小但要留出合理的余量。比如用戶名如果業(yè)務(wù)規(guī)定最長(zhǎng)20個(gè)字符那VARCHAR(32)或者VARCHAR(64)都可以沒(méi)必要直接上VARCHAR(255)。因?yàn)閂ARCHAR的長(zhǎng)度會(huì)影響索引的長(zhǎng)度限制特別是在聯(lián)合索引中過(guò)長(zhǎng)的VARCHAR會(huì)導(dǎo)致索引創(chuàng)建失敗或者效率降低。還有一個(gè)坑MySQL的VARCHAR長(zhǎng)度指的是字符數(shù)不是字節(jié)數(shù)。在utf8mb4字符集下一個(gè)VARCHAR(255)的字段最大可能占用255乘以4等于1020個(gè)字節(jié)。而InnoDB索引的單列長(zhǎng)度限制是767字節(jié)在COMPACT行格式下或者3072字節(jié)在DYNAMIC行格式下。所以如果你要在VARCHAR字段上建索引長(zhǎng)度不能太大。3.3 時(shí)間類型DATETIME和TIMESTAMP的抉擇時(shí)間字段的選擇也是高頻問(wèn)題。MySQL里主要用DATETIME和TIMESTAMP兩種類型它們有幾個(gè)關(guān)鍵區(qū)別對(duì)比項(xiàng)DATETIMETIMESTAMP存儲(chǔ)空間5到8字節(jié)取決于精度4到7字節(jié)取決于精度取值范圍1000-01-01到9999-12-311970-01-01到2038-01-19時(shí)區(qū)處理不受時(shí)區(qū)影響存什么讀什么受時(shí)區(qū)影響存儲(chǔ)時(shí)轉(zhuǎn)為UTC讀取時(shí)轉(zhuǎn)為當(dāng)前時(shí)區(qū)默認(rèn)值可以設(shè)置默認(rèn)值可以設(shè)置默認(rèn)值和自動(dòng)更新適用場(chǎng)景業(yè)務(wù)時(shí)間、生日、有效期等創(chuàng)建時(shí)間、更新時(shí)間等系統(tǒng)時(shí)間我的建議是業(yè)務(wù)相關(guān)的時(shí)間字段用DATETIME系統(tǒng)自動(dòng)維護(hù)的時(shí)間字段用TIMESTAMP。比如訂單創(chuàng)建時(shí)間如果用TIMESTAMP2038年之后就會(huì)溢出雖然現(xiàn)在離2038年還有十幾年但誰(shuí)也不想給自己的系統(tǒng)埋一個(gè)定時(shí)炸彈。而created_at和updated_at這種字段用TIMESTAMP可以自動(dòng)更新省去手動(dòng)維護(hù)的麻煩。注意MySQL 8.0.19之后支持DATETIME的默認(rèn)值和自動(dòng)更新所以如果你用的是較新版本也可以統(tǒng)一用DATETIME通過(guò)DEFAULT CURRENT_TIMESTAMP和ON UPDATE CURRENT_TIMESTAMP來(lái)實(shí)現(xiàn)自動(dòng)維護(hù)。3.4 金額字段為什么不能用FLOAT和DOUBLE金額字段用浮點(diǎn)數(shù)存儲(chǔ)是一個(gè)經(jīng)典錯(cuò)誤。FLOAT和DOUBLE是二進(jìn)制浮點(diǎn)數(shù)無(wú)法精確表示十進(jìn)制小數(shù)比如0.1在二進(jìn)制里是無(wú)限循環(huán)的。這會(huì)導(dǎo)致什么后果你存進(jìn)去100.1讀出來(lái)可能是100.09999999999999累加幾次之后誤差會(huì)越來(lái)越大對(duì)賬的時(shí)候能把你逼瘋。金額字段的正確做法是用DECIMAL類型或者用整數(shù)存儲(chǔ)最小貨幣單位。比如金額精確到分那就用INT或者BIGINT存儲(chǔ)單位是分顯示的時(shí)候除以100就行。如果一定要用DECIMAL建議用DECIMAL(M,2)M根據(jù)業(yè)務(wù)規(guī)模來(lái)定一般DECIMAL(12,2)可以支持到百億級(jí)別夠用了。DECIMAL的存儲(chǔ)方式?jīng)Q定了它是精確的不會(huì)出現(xiàn)浮點(diǎn)數(shù)那種精度丟失問(wèn)題。但DECIMAL的運(yùn)算速度比整數(shù)慢所以如果對(duì)性能要求極高用整數(shù)存分也是很好的選擇。4. 索引設(shè)計(jì)與主鍵策略的實(shí)戰(zhàn)經(jīng)驗(yàn)4.1 主鍵自增還是業(yè)務(wù)主鍵主鍵的選擇是表結(jié)構(gòu)設(shè)計(jì)中最關(guān)鍵的決策之一。MySQL的InnoDB存儲(chǔ)引擎是索引組織表數(shù)據(jù)按照主鍵順序存儲(chǔ)在B樹(shù)的葉子節(jié)點(diǎn)上。這意味著主鍵的選擇直接影響數(shù)據(jù)的物理存儲(chǔ)順序和插入性能。自增主鍵AUTO_INCREMENT的好處是插入時(shí)總是追加到B樹(shù)的最右邊不會(huì)引起頁(yè)分裂寫入性能高而且主鍵長(zhǎng)度小二級(jí)索引占用的空間也小。業(yè)務(wù)主鍵比如訂單號(hào)、用戶ID的好處是全局唯一、方便分庫(kù)分表但如果是無(wú)序的比如UUID插入時(shí)會(huì)導(dǎo)致隨機(jī)寫入引起大量的頁(yè)分裂和碎片性能很差。我的建議是絕大多數(shù)場(chǎng)景下用自增BIGINT做主鍵業(yè)務(wù)上的唯一標(biāo)識(shí)加唯一索引。這樣既保證了寫入性能又滿足了業(yè)務(wù)唯一性要求。如果確實(shí)需要分布式ID可以用雪花算法生成有序的BIGINT既保證了全局唯一又保證了插入順序。提示自增主鍵在分庫(kù)分表場(chǎng)景下會(huì)有問(wèn)題因?yàn)椴煌制淖栽鯥D可能重復(fù)。這時(shí)候可以考慮用步長(zhǎng)不同的自增ID或者直接用雪花算法。4.2 聯(lián)合索引的最左前綴原則聯(lián)合索引是提高多條件查詢性能的利器但用不好也會(huì)適得其反。最核心的原則就是最左前綴原則聯(lián)合索引(a, b, c)可以支持a、ab、abc的查詢條件但不支持b、c、bc的查詢條件。這個(gè)原則背后的原理是B樹(shù)的排序方式。聯(lián)合索引按照第一個(gè)字段排序第一個(gè)字段相同的情況下按第二個(gè)字段排序以此類推。所以如果你查詢的時(shí)候沒(méi)有用到第一個(gè)字段就無(wú)法利用索引的有序性。設(shè)計(jì)聯(lián)合索引的時(shí)候我一般會(huì)考慮幾個(gè)因素字段的區(qū)分度、查詢的頻率、字段的長(zhǎng)度。區(qū)分度高的字段放在前面查詢頻率高的字段放在前面字段長(zhǎng)度短的放在前面。但這也不是絕對(duì)的有時(shí)候?yàn)榱烁采w更多的查詢場(chǎng)景需要做一些權(quán)衡。舉個(gè)例子假設(shè)有一個(gè)用戶表經(jīng)常有這兩種查詢按手機(jī)號(hào)查用戶、按手機(jī)號(hào)加狀態(tài)查用戶。那聯(lián)合索引(phone, status)就能同時(shí)覆蓋這兩種查詢。但如果查詢是status 1 AND phone xxx雖然條件順序反了MySQL的優(yōu)化器會(huì)自動(dòng)調(diào)整順序仍然能用上索引。但如果查詢是status 1那就用不上這個(gè)聯(lián)合索引了因?yàn)樘^(guò)了最左的phone字段。4.3 覆蓋索引減少回表操作的利器覆蓋索引是指查詢的所有字段都包含在索引中不需要回表查詢聚簇索引。覆蓋索引能顯著減少IO操作提升查詢性能特別是在范圍查詢和排序場(chǎng)景下效果明顯。比如有一個(gè)訂單表經(jīng)常需要按用戶ID查詢訂單號(hào)和訂單金額。如果建一個(gè)聯(lián)合索引(user_id, order_no, amount)那么SELECT order_no, amount FROM orders WHERE user_id 123這個(gè)查詢就可以直接從索引中獲取數(shù)據(jù)不需要回表。但覆蓋索引也不是越多越好因?yàn)樗饕旧硪舱加么鎯?chǔ)空間而且會(huì)降低寫入性能。每增加一個(gè)索引插入、更新、刪除操作都需要維護(hù)這個(gè)索引。所以索引的數(shù)量要控制一般單表索引不超過(guò)5到6個(gè)具體要根據(jù)查詢頻率和寫入頻率來(lái)權(quán)衡。4.4 索引失效的常見(jiàn)場(chǎng)景建了索引不代表查詢就一定能用上有很多情況會(huì)導(dǎo)致索引失效。我整理了幾個(gè)最常見(jiàn)的在索引字段上做運(yùn)算或函數(shù)操作比如WHERE YEAR(created_at) 2024這會(huì)導(dǎo)致索引失效應(yīng)該改成WHERE created_at 2024-01-01 AND created_at 2025-01-01。隱式類型轉(zhuǎn)換比如字段是VARCHAR類型查詢時(shí)傳了數(shù)字MySQL會(huì)做隱式轉(zhuǎn)換導(dǎo)致索引失效。WHERE phone 13800138000和WHERE phone 13800138000的結(jié)果可能完全不同。使用不等于條件WHERE status ! 1通常會(huì)導(dǎo)致全表掃描因?yàn)椴坏扔跅l件無(wú)法利用索引的有序性。LIKE以通配符開(kāi)頭WHERE name LIKE %張三無(wú)法使用索引但WHERE name LIKE 張三%可以。OR條件連接非索引字段如果OR的一邊是索引字段另一邊不是整個(gè)查詢可能走全表掃描。注意索引失效的判斷不能只靠理論最終還是要看EXPLAIN的執(zhí)行計(jì)劃。MySQL的優(yōu)化器會(huì)根據(jù)統(tǒng)計(jì)信息做決策有時(shí)候理論上應(yīng)該走索引的查詢優(yōu)化器可能因?yàn)閿?shù)據(jù)分布的原因選擇全表掃描。5. 完整建表實(shí)操流程與案例拆解5.1 從需求到DDL的完整推演過(guò)程光講理論不夠直觀我拿一個(gè)簡(jiǎn)化的電商訂單場(chǎng)景來(lái)完整走一遍設(shè)計(jì)流程。假設(shè)需求是這樣的用戶可以下單購(gòu)買商品一個(gè)訂單可以包含多個(gè)商品需要記錄訂單狀態(tài)、支付信息、收貨地址還要支持按用戶查詢訂單列表、按狀態(tài)篩選訂單、按時(shí)間范圍統(tǒng)計(jì)訂單。第一步梳理實(shí)體和關(guān)系。核心實(shí)體有用戶、商品、訂單、訂單明細(xì)。訂單和用戶是多對(duì)一訂單和商品通過(guò)訂單明細(xì)形成多對(duì)多。支付信息和收貨地址可以作為訂單的屬性也可以單獨(dú)建表這里為了簡(jiǎn)化先作為訂單表的字段。第二步確定每個(gè)實(shí)體的字段。用戶表需要用戶ID、用戶名、手機(jī)號(hào)、郵箱、創(chuàng)建時(shí)間等。商品表需要商品ID、商品名稱、價(jià)格、庫(kù)存、狀態(tài)等。訂單表需要訂單ID、用戶ID、訂單號(hào)、總金額、訂單狀態(tài)、支付方式、收貨人信息、創(chuàng)建時(shí)間、更新時(shí)間等。訂單明細(xì)表需要明細(xì)ID、訂單ID、商品ID、商品名稱冗余、單價(jià)冗余、數(shù)量、小計(jì)金額等。第三步選擇字段類型。訂單ID用BIGINT自增訂單號(hào)用VARCHAR(32)加唯一索引總金額用DECIMAL(12,2)訂單狀態(tài)用TINYINT收貨人姓名用VARCHAR(32)手機(jī)號(hào)用VARCHAR(20)地址用VARCHAR(255)。訂單明細(xì)里的商品名稱冗余一份用VARCHAR(128)單價(jià)冗余一份用DECIMAL(10,2)。第四步設(shè)計(jì)索引。訂單表的主鍵是訂單ID唯一索引在訂單號(hào)上普通索引有idx_user_id、idx_status_created_at、idx_created_at。訂單明細(xì)表的主鍵是明細(xì)ID普通索引有idx_order_id、idx_product_id。5.2 建表語(yǔ)句的完整示例與逐行解讀基于上面的設(shè)計(jì)完整的建表語(yǔ)句大概長(zhǎng)這樣CREATE TABLE order ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 訂單ID, order_no VARCHAR(32) NOT NULL COMMENT 訂單號(hào), user_id BIGINT UNSIGNED NOT NULL COMMENT 用戶ID, total_amount DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT 訂單總金額, status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 訂單狀態(tài)0待支付1已支付2已發(fā)貨3已完成4已取消, pay_method TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT 支付方式0未支付1支付寶2微信3銀行卡, receiver_name VARCHAR(32) NOT NULL DEFAULT COMMENT 收貨人姓名, receiver_phone VARCHAR(20) NOT NULL DEFAULT COMMENT 收貨人手機(jī)號(hào), receiver_address VARCHAR(255) NOT NULL DEFAULT COMMENT 收貨地址, remark VARCHAR(255) NOT NULL DEFAULT COMMENT 訂單備注, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 創(chuàng)建時(shí)間, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 更新時(shí)間, PRIMARY KEY (id), UNIQUE KEY uniq_order_no (order_no), KEY idx_user_id (user_id), KEY idx_status_created_at (status, created_at), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT訂單表;逐行解讀一下幾個(gè)關(guān)鍵點(diǎn)。id用BIGINT UNSIGNED因?yàn)橛唵瘟靠赡芎艽骍NSIGNED可以讓取值范圍翻倍。order_no用VARCHAR(32)一般訂單號(hào)不會(huì)超過(guò)32個(gè)字符加唯一索引保證不重復(fù)。total_amount用DECIMAL(12,2)支持到百億級(jí)別精確到分。status用TINYINT UNSIGNED0到255足夠表示所有狀態(tài)。created_at和updated_at用DATETIME配合默認(rèn)值和自動(dòng)更新省去手動(dòng)維護(hù)。索引方面idx_status_created_at是一個(gè)聯(lián)合索引支持按狀態(tài)篩選并按時(shí)間排序的查詢比如“查詢所有待支付的訂單按創(chuàng)建時(shí)間倒序”。idx_created_at單獨(dú)建一個(gè)支持純時(shí)間范圍的統(tǒng)計(jì)查詢。idx_user_id支持按用戶查訂單列表。5.3 建表后的自查清單建完表之后別急著往生產(chǎn)環(huán)境推先做一輪自查。我一般會(huì)檢查這幾項(xiàng)所有字段是否都有NOT NULL約束和默認(rèn)值除了TEXT和BLOB類型其他字段盡量都設(shè)NOT NULL避免NULL值帶來(lái)的索引和查詢問(wèn)題。所有字段和表是否有COMMENT注釋是給后來(lái)人看的也是給自己的備忘特別是狀態(tài)字段的枚舉值一定要寫清楚。字符集是否統(tǒng)一為utf8mb4utf8mb4支持完整的Unicode字符包括emoji而utf8實(shí)際上是utf8mb3不支持。統(tǒng)一字符集可以避免亂碼和隱式轉(zhuǎn)換問(wèn)題。主鍵是否自增是否有業(yè)務(wù)唯一鍵主鍵用自增BIGINT業(yè)務(wù)唯一鍵加唯一索引這是標(biāo)配。索引是否覆蓋了主要查詢場(chǎng)景用EXPLAIN驗(yàn)證一下核心查詢的執(zhí)行計(jì)劃確保走了索引。表名和字段名是否符合命名規(guī)范對(duì)照?qǐng)F(tuán)隊(duì)的命名規(guī)范文檔逐項(xiàng)檢查。6. 常見(jiàn)問(wèn)題排查與避坑經(jīng)驗(yàn)實(shí)錄6.1 表結(jié)構(gòu)變更引發(fā)的線上事故表結(jié)構(gòu)變更是一個(gè)高風(fēng)險(xiǎn)操作特別是在數(shù)據(jù)量大的表上。我經(jīng)歷過(guò)一次事故在一個(gè)幾千萬(wàn)行的表上加了一個(gè)字段用的是ALTER TABLE ADD COLUMN結(jié)果鎖表鎖了十幾分鐘期間所有寫入全部阻塞業(yè)務(wù)方電話直接打爆。后來(lái)我總結(jié)了幾個(gè)安全變更的原則大表加字段盡量用ALGORITHMINPLACEMySQL 5.6之后支持在線DDL但并不是所有操作都支持。加字段、加索引一般可以在線進(jìn)行但改字段類型、改字符集通常需要拷貝表。變更前先在從庫(kù)或者測(cè)試環(huán)境驗(yàn)證確認(rèn)執(zhí)行時(shí)間和鎖表情況?？梢杂胮t-online-schema-change或者gh-ost這類工具來(lái)做在線變更它們通過(guò)創(chuàng)建影子表、逐步同步數(shù)據(jù)的方式避免長(zhǎng)時(shí)間鎖表。變更盡量安排在低峰期并且準(zhǔn)備好回滾方案。如果變更過(guò)程中出現(xiàn)問(wèn)題要能快速回滾。提示MySQL 8.0的在線DDL能力比5.7強(qiáng)了不少但依然有局限性。在執(zhí)行DDL之前一定要查清楚當(dāng)前版本支持哪些在線操作別想當(dāng)然。6.2 字段類型選錯(cuò)導(dǎo)致的性能問(wèn)題字段類型選錯(cuò)是另一個(gè)高頻問(wèn)題。我見(jiàn)過(guò)一個(gè)案例用戶表的手機(jī)號(hào)字段用了BIGINT存儲(chǔ)查詢的時(shí)候傳的是字符串導(dǎo)致隱式類型轉(zhuǎn)換索引失效查詢從毫秒級(jí)變成了秒級(jí)。改成VARCHAR之后索引正常使用性能恢復(fù)。還有一個(gè)案例訂單表的訂單號(hào)用了VARCHAR(255)聯(lián)合索引里包含這個(gè)字段結(jié)果索引長(zhǎng)度超過(guò)了限制創(chuàng)建失敗。后來(lái)把訂單號(hào)改成VARCHAR(32)問(wèn)題解決。字段類型選錯(cuò)的影響是多方面的可能導(dǎo)致索引失效、可能浪費(fèi)存儲(chǔ)空間、可能導(dǎo)致數(shù)據(jù)精度丟失、可能影響排序和比較的結(jié)果。所以建表的時(shí)候一定要根據(jù)業(yè)務(wù)的實(shí)際需求來(lái)選擇類型不要圖省事一律用最大的類型。6.3 索引過(guò)多導(dǎo)致的寫入性能下降索引不是越多越好這個(gè)道理很多人知道但實(shí)際工作中還是容易犯。我見(jiàn)過(guò)一個(gè)表建了十幾個(gè)索引查詢確實(shí)快了但插入和更新的性能慘不忍睹因?yàn)槊看螌懭攵家S護(hù)所有索引。索引的數(shù)量需要根據(jù)讀寫比例來(lái)權(quán)衡。如果是讀多寫少的表比如商品表、配置表可以適當(dāng)多建幾個(gè)索引。如果是寫多讀少的表比如日志表、流水表索引就要精簡(jiǎn)只保留最核心的查詢所需的索引。另外聯(lián)合索引可以替代多個(gè)單列索引。比如(a, b)這個(gè)聯(lián)合索引可以支持a的查詢也可以支持ab的查詢就不需要單獨(dú)再建一個(gè)a的索引了。但要注意如果b也需要單獨(dú)查詢那(a, b)就不能替代b的索引因?yàn)椴粷M足最左前綴原則。6.4 常見(jiàn)問(wèn)題速查表問(wèn)題現(xiàn)象可能原因排查方法解決方案查詢突然變慢索引失效、數(shù)據(jù)量增長(zhǎng)、統(tǒng)計(jì)信息過(guò)期用EXPLAIN查看執(zhí)行計(jì)劃優(yōu)化查詢語(yǔ)句、重建索引、更新統(tǒng)計(jì)信息插入性能下降索引過(guò)多、主鍵無(wú)序、鎖競(jìng)爭(zhēng)查看寫入QPS和鎖等待時(shí)間精簡(jiǎn)索引、改用自增主鍵、優(yōu)化事務(wù)粒度表結(jié)構(gòu)變更鎖表DDL操作不支持在線執(zhí)行查看MySQL版本和DDL算法使用在線DDL工具、低峰期執(zhí)行數(shù)據(jù)精度丟失用了FLOAT/DOUBLE存金額對(duì)比存儲(chǔ)值和讀取值改用DECIMAL或整數(shù)存儲(chǔ)索引創(chuàng)建失敗索引長(zhǎng)度超過(guò)限制查看字段類型和字符集縮短字段長(zhǎng)度、改用前綴索引字符亂碼字符集不統(tǒng)一查看表、字段、連接的字符集統(tǒng)一為utf8mb46.5 幾個(gè)容易被忽略的細(xì)節(jié)最后分享幾個(gè)我在實(shí)際工作中踩過(guò)的坑都是些小細(xì)節(jié)但影響不小。第一個(gè)是NULL值的問(wèn)題。MySQL的索引對(duì)NULL值的處理比較特殊IS NULL和IS NOT NULL的查詢?cè)谟行┣闆r下無(wú)法有效利用索引。而且NULL值參與運(yùn)算和比較時(shí)結(jié)果往往是NULL容易導(dǎo)致邏輯錯(cuò)誤。所以我的建議是除了確實(shí)需要表示“未知”的字段其他字段盡量設(shè)NOT NULL并給默認(rèn)值。第二個(gè)是自增ID的回繞問(wèn)題。INT UNSIGNED的最大值是42億多如果表的數(shù)據(jù)量增長(zhǎng)很快幾年就可能耗盡。一旦耗盡插入就會(huì)報(bào)錯(cuò)。所以對(duì)于可能快速增長(zhǎng)的表主鍵直接用BIGINT UNSIGNED別省那4個(gè)字節(jié)。第三個(gè)是字符集的選擇。utf8mb4是現(xiàn)在的主流選擇但要注意排序規(guī)則的選擇。utf8mb4_general_ci性能稍好但不支持某些語(yǔ)言的精確排序utf8mb4_unicode_ci或者utf8mb4_0900_ai_ciMySQL 8.0默認(rèn)排序更準(zhǔn)確但性能稍差。一般業(yè)務(wù)用utf8mb4_general_ci就夠了如果涉及多語(yǔ)言排序再考慮用unicode的排序規(guī)則。第四個(gè)是表注釋和字段注釋。這東西平時(shí)沒(méi)人看但出問(wèn)題的時(shí)候就是救命稻草。特別是狀態(tài)字段的枚舉值一定要在注釋里寫清楚每個(gè)值代表什么。我接手過(guò)一個(gè)項(xiàng)目狀態(tài)字段的注釋只寫了“狀態(tài)”具體0、1、2、3分別是什么全靠猜最后只能去翻代碼。第五個(gè)是預(yù)留字段的問(wèn)題。有些團(tuán)隊(duì)喜歡在建表的時(shí)候預(yù)留幾個(gè)字段比如ext1、ext2、ext3想著以后擴(kuò)展用。我的經(jīng)驗(yàn)是預(yù)留字段大概率用不上或者用上了也不知道當(dāng)初是干嘛的。與其預(yù)留字段不如把表結(jié)構(gòu)設(shè)計(jì)得靈活一點(diǎn)該拆表拆表該加字段加字段現(xiàn)在在線DDL已經(jīng)比較成熟了加字段的成本沒(méi)有想象中那么高。我在實(shí)際項(xiàng)目中的體會(huì)是表結(jié)構(gòu)設(shè)計(jì)這件事經(jīng)驗(yàn)比理論重要但理論是基礎(chǔ)。沒(méi)有理論指導(dǎo)經(jīng)驗(yàn)就是瞎試只有理論沒(méi)有經(jīng)驗(yàn)設(shè)計(jì)出來(lái)的表往往中看不中用。最好的方式是在項(xiàng)目中不斷實(shí)踐、不斷復(fù)盤每次遇到問(wèn)題都記錄下來(lái)慢慢就形成自己的設(shè)計(jì)直覺(jué)了。