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