計(jì)與查詢優(yōu)化：從建表規(guī)范到慢查詢實(shí)戰(zhàn))
1. 先把“表”這件事想清楚字段、類型與約束只要是接觸過MySQL的人應(yīng)該都有過這種經(jīng)歷剛開始建表時(shí)特別隨意字段類型憑感覺選約束能不加就不加等業(yè)務(wù)跑了大半年數(shù)據(jù)量上來之后才發(fā)現(xiàn)一堆歷史遺留問題——查詢慢、亂碼、數(shù)據(jù)對(duì)不上、沒法統(tǒng)計(jì)。再回頭改表結(jié)構(gòu)又怕鎖表影響線上業(yè)務(wù)。所以聊MySQL表操作和查詢之前我想先說說建表階段那些容易被忽略、但影響極其深遠(yuǎn)的基礎(chǔ)設(shè)計(jì)問題。畢竟表結(jié)構(gòu)設(shè)計(jì)得是否合理直接決定后面所有查詢語句好不好寫、跑得快不快。1.1 字段類型選錯(cuò)了后面全是淚字段類型是表結(jié)構(gòu)里最基礎(chǔ)但也最容易出錯(cuò)的地方。很多新手喜歡“省事”所有字符串一律用varchar(255)所有數(shù)值一律用int日期就放一個(gè)字符串字段存進(jìn)去。短期看確實(shí)沒問題等數(shù)據(jù)量到了幾百萬行各種問題就來了字符串類型的日期沒法直接用日期函數(shù)做范圍統(tǒng)計(jì)int存不下手機(jī)號(hào)直接溢出varchar(255)在utf8mb4字符集下單字段索引長(zhǎng)度超限導(dǎo)致索引失效。這里我直接給一套常用的選型參考整數(shù)能用int就別用bigint能用tinyint就別用int。狀態(tài)值、開關(guān)值用tinyint主鍵用bigint還是int取決于業(yè)務(wù)量預(yù)估但不要一拍腦袋就給所有表都上bigint。小數(shù)不要用float和double存金額精度丟失的問題在實(shí)際項(xiàng)目中非?？?。用decimal比如decimal(10,2)10表示總位數(shù)2表示小數(shù)位。字符串定長(zhǎng)的用char比如身份證號(hào)、手機(jī)號(hào)這種長(zhǎng)度固定的變長(zhǎng)的用varchar但要給合理的最大長(zhǎng)度別一上來就255。長(zhǎng)文本用text但要注意text字段不能有默認(rèn)值索引也只能前綴索引。日期時(shí)間這個(gè)特別值得多說一句。強(qiáng)烈建議用datetime不要用timestamp因?yàn)閠imestamp有2038年的上限問題。另外不要用字符串存日期雖然看起來方便但丟了時(shí)間函數(shù)的便利性也無法利用索引做高效的區(qū)間查詢。有人可能會(huì)問datetime和timestamp到底差在哪。簡(jiǎn)單說timestamp存儲(chǔ)的是UTC時(shí)間存的時(shí)候會(huì)轉(zhuǎn)時(shí)區(qū)取的時(shí)候也會(huì)轉(zhuǎn)回來受時(shí)區(qū)設(shè)置影響datetime存的就是字面時(shí)間不涉及時(shí)區(qū)轉(zhuǎn)換。對(duì)于業(yè)務(wù)系統(tǒng)來說大多數(shù)情況下datetime更直觀、更可控避免因?yàn)閿?shù)據(jù)庫時(shí)區(qū)和應(yīng)用時(shí)區(qū)不一致造成數(shù)據(jù)錯(cuò)亂。1.2 約束與索引建表時(shí)一次想好別等數(shù)據(jù)量大了再回頭約束是很多人建表時(shí)最容易忽略的東西。常見的主鍵約束、唯一約束、非空約束、默認(rèn)值約束以及外鍵約束我發(fā)現(xiàn)很多項(xiàng)目干脆一個(gè)外鍵都不用。不用的原因也簡(jiǎn)單外鍵在寫入的時(shí)候有額外的校驗(yàn)開銷分布式拆分表的時(shí)候也麻煩所以業(yè)務(wù)系統(tǒng)基本都靠應(yīng)用層來保證關(guān)聯(lián)數(shù)據(jù)的完整性。這可以理解但至少主鍵、唯一鍵、非空、默認(rèn)值這些基礎(chǔ)約束該加就得加。主鍵的選擇上我建議用自增int/bigint作為主鍵業(yè)務(wù)字段不要直接當(dāng)主鍵。原因有兩個(gè)一是InnoDB是聚簇索引組織表主鍵是物理存儲(chǔ)的順序依據(jù)自增主鍵能保證插入時(shí)順序?qū)懭霚p少頁分裂二是業(yè)務(wù)字段的變更不可控拿手機(jī)號(hào)當(dāng)主鍵哪天允許用戶改綁手機(jī)號(hào)整個(gè)索引結(jié)構(gòu)都得跟著動(dòng)。唯一約束這塊要注意它和普通索引的區(qū)別。唯一約束本身就是索引只是額外加了唯一性校驗(yàn)。所以如果業(yè)務(wù)上需要保證某個(gè)字段唯一直接加unique key不要用普通索引之后再在應(yīng)用層做查詢校驗(yàn)既慢又容易有并發(fā)漏洞。還有個(gè)很實(shí)用的習(xí)慣每張表都加上創(chuàng)建時(shí)間和更新時(shí)間兩個(gè)字段類型用datetime更新時(shí)間可以靠業(yè)務(wù)代碼維護(hù)也可以建表時(shí)用ON UPDATE CURRENT_TIMESTAMP自動(dòng)更新。這兩個(gè)字段在排查數(shù)據(jù)問題時(shí)幾乎是救命稻草尤其是出現(xiàn)臟數(shù)據(jù)、重復(fù)數(shù)據(jù)、異常數(shù)據(jù)的時(shí)候能迅速定位出問題時(shí)間點(diǎn)。1.3 字符集與排序規(guī)則亂碼和排序不對(duì)的頭號(hào)原因字符集問題可以說是MySQL新手時(shí)期最常遇到的坑之一。我記得第一次接手一個(gè)老項(xiàng)目明明代碼里都是UTF-8數(shù)據(jù)庫里卻出現(xiàn)一串問號(hào)和亂碼排查了半天最后發(fā)現(xiàn)庫的默認(rèn)字符集是latin1。這種問題一旦上線之后再想改代價(jià)非常大。所以在建庫建表的時(shí)候第一件事就是明確字符集?，F(xiàn)在的統(tǒng)一標(biāo)準(zhǔn)就是utf8mb4注意不是utf8。MySQL里的utf8是utf8mb3的別名只支持最多3字節(jié)的字符像emoji這種4字節(jié)字符就存不了會(huì)報(bào)錯(cuò)或者變成亂碼。utf8mb4才是真正的“完整UTF-8”兼容所有Unicode字符。排序規(guī)則也要一并確認(rèn)。utf8mb4_general_ci和utf8mb4_unicode_ci是兩種常見選擇。前者排序快但精度略差后者排序更準(zhǔn)確但稍微慢一點(diǎn)。絕大多數(shù)業(yè)務(wù)系統(tǒng)其實(shí)用哪個(gè)差別都不大需要重點(diǎn)注意的是別出現(xiàn)表、庫、連接層字符集不一致的情況。判斷起來也簡(jiǎn)單執(zhí)行一次查詢?nèi)绻l(fā)現(xiàn)中文亂碼先別急著改數(shù)據(jù)從客戶端連接、到數(shù)據(jù)庫連接、再到表和字段的字符集一層層排查。最常見的坑是連接層用了utf8而表和字段是utf8mb4中文正常遇到特殊字符就出問題。2. 建表、改表、刪表一張表的完整生命周期管理前面說了設(shè)計(jì)層面的問題接下來就進(jìn)入實(shí)際操作環(huán)節(jié)。表操作無非就是建表、改表、刪表這三類但每一類里都有不少細(xì)節(jié)值得展開。很多人寫SQL的時(shí)候只關(guān)心能不能跑通忽略了操作帶來的影響面尤其是生產(chǎn)環(huán)境里一條ALTER TABLE就可能把整個(gè)業(yè)務(wù)拖垮。2.1 建表從需求到DDL落地的實(shí)操寫法先看一個(gè)實(shí)際的建表示例。假設(shè)要建一張用戶訂單表包含訂單號(hào)、用戶ID、商品名稱、訂單金額、訂單狀態(tài)、創(chuàng)建時(shí)間、更新時(shí)間這些基本字段。第一版DDL可以這樣寫CREATE TABLE orders ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主鍵ID, order_no VARCHAR(64) NOT NULL COMMENT 訂單號(hào), user_id BIGINT NOT NULL COMMENT 用戶ID, product_name VARCHAR(128) NOT NULL COMMENT 商品名稱, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 訂單金額, status TINYINT NOT NULL DEFAULT 0 COMMENT 訂單狀態(tài)0待支付 1已支付 2已取消, 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 uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT用戶訂單表;這個(gè)建表語句里的幾個(gè)細(xì)節(jié)值得展開說明一下。首先是ENGINEInnoDBMySQL 5.7之后默認(rèn)就是InnoDB但寫出來更明確。InnoDB支持事務(wù)、行級(jí)鎖、崩潰恢復(fù)是絕大多數(shù)業(yè)務(wù)場(chǎng)景的唯一選擇。有些人可能聽說過MyISAM查詢快但它不支持事務(wù)、不支持行鎖寫并發(fā)一高就全線阻塞真不適合業(yè)務(wù)系統(tǒng)用。其次是每個(gè)字段都加了COMMENT注釋。這個(gè)習(xí)慣非常好。一個(gè)項(xiàng)目壽命往往遠(yuǎn)超想象兩三年后你自己回頭維護(hù)這張表如果沒有注釋真的會(huì)想不起來某個(gè)字段是干什么用的。更不用說團(tuán)隊(duì)協(xié)作別人接手你的表結(jié)構(gòu)時(shí)良好的注釋能省下大量溝通成本。再注意到status字段用的是TINYINT而不是VARCHAR或者ENUM。用數(shù)字表示狀態(tài)擴(kuò)展性好0、1、2不夠用隨時(shí)可以加。但這里有個(gè)小建議數(shù)字狀態(tài)值一定要配注釋說明含義否則時(shí)間長(zhǎng)了沒人知道1代表什么。不過更推薦的做法是在應(yīng)用層代碼里定義狀態(tài)常量注釋輔助解釋兩邊對(duì)應(yīng)著看。2.2 ALTER TABLE加字段、改字段、加索引的常見操作業(yè)務(wù)迭代過程中表結(jié)構(gòu)幾乎一定會(huì)變。ALTER TABLE的語法比較簡(jiǎn)單但生產(chǎn)環(huán)境里要特別注意執(zhí)行成本和鎖表風(fēng)險(xiǎn)。加字段是最常見的操作。比如給訂單表加一個(gè)支付時(shí)間字段ALTER TABLE orders ADD COLUMN paid_at DATETIME DEFAULT NULL COMMENT 支付時(shí)間 AFTER status;這里有一個(gè)非常實(shí)用的習(xí)慣把新字段用AFTER關(guān)鍵字放在相關(guān)字段旁邊而不是默認(rèn)加到最后。雖然最終效果不影響查詢但看表結(jié)構(gòu)時(shí)邏輯會(huì)更清晰。另外注意在數(shù)據(jù)量很大的表上執(zhí)行ALTER TABLEInnoDB需要重建表會(huì)鎖住寫操作耗時(shí)可能非常長(zhǎng)甚至導(dǎo)致主從延遲。直到MySQL 5.6引入了在線DDL加上MySQL 8.0的進(jìn)一步優(yōu)化很多ALTER操作已經(jīng)不會(huì)完全鎖表了但字段類型變更、修改字符集這類操作仍然可能很重。所以在生產(chǎn)環(huán)境做表結(jié)構(gòu)變更前我一般會(huì)先確認(rèn)表的行數(shù)和當(dāng)前實(shí)例的負(fù)載盡量挑業(yè)務(wù)低峰期操作。修改字段類型和默認(rèn)值也是高頻操作。語法如下ALTER TABLE orders MODIFY COLUMN status TINYINT NOT NULL DEFAULT 1 COMMENT 訂單狀態(tài)0待支付 1已支付 2已取消 3已退款;同樣一個(gè)MODIFY操作看似簡(jiǎn)單但如果表很大執(zhí)行期間同樣是有代價(jià)的。而且MODIFY COLUMN會(huì)改變字段的整條定義如果你只寫了一部分屬性其他屬性會(huì)被重置所以執(zhí)行前最好先用SHOW CREATE TABLE把原有定義完整看一下再確認(rèn)要改的信息避免誤把已有的NOT NULL、DEFAULT等屬性弄丟。加索引的常見操作ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);這是一個(gè)典型的聯(lián)合索引能夠同時(shí)優(yōu)化“查某個(gè)用戶的訂單按時(shí)間倒序”這類高頻查詢。聯(lián)合索引的核心設(shè)計(jì)原則是區(qū)分度高的字段放前面等值查詢字段放前面范圍查詢字段放后面。這個(gè)后面講到查詢優(yōu)化時(shí)還會(huì)再展開。2.3 刪表與清空DROP / TRUNCATE / DELETE的本質(zhì)區(qū)別刪表有三種方式很多人分不清它們的適用場(chǎng)景。DROP TABLE是整張表直接刪掉表結(jié)構(gòu)、數(shù)據(jù)、索引全部消失不可恢復(fù)。TRUNCATE TABLE是清空數(shù)據(jù)但保留表結(jié)構(gòu)相當(dāng)于重置這張表不可按條件刪也不能回滾。DELETE FROM是逐行刪除可以帶WHERE條件且走事務(wù)可以回滾。所以日常開發(fā)中真正不要這張表了才用DROP想要快速清空一張臨時(shí)表或日志表的數(shù)據(jù)用TRUNCATE刪除符合條件的部分?jǐn)?shù)據(jù)用DELETE。這里特別提一個(gè)問題很多人誤以為DELETE之后數(shù)據(jù)量還是沒降下來。這是因?yàn)镮nnoDB刪除的行標(biāo)記為“已刪除”但物理空間沒有被立即釋放后續(xù)有新數(shù)據(jù)插入時(shí)才會(huì)復(fù)用這些空間。如果只是跑了一次DELETE FROM大表磁盤空間不變是很正常的不要慌張。想要真正收縮表空間可以執(zhí)行OPTIMIZE TABLE或者ALTER TABLE ... ENGINEInnoDB來重建表但這又是一個(gè)重操作生產(chǎn)環(huán)境慎用。3. 單表查詢SELECT的核心邏輯與執(zhí)行順序聊完表操作進(jìn)入查詢部分。查詢是MySQL里最日常、也最考驗(yàn)功力的操作。很多人寫了幾年的SQL遇到慢查詢還是只會(huì)加索引其實(shí)很多問題的根源不在于缺索引而在于沒有真正理解SELECT的執(zhí)行邏輯。3.1 SELECT語法順序與執(zhí)行順序是兩碼事這是個(gè)老生常談卻又極其重要的話題。一條完整的SELECT語句書寫的順序是SELECT 字段 FROM 表 WHERE 條件 GROUP BY 分組字段 HAVING 分組后條件 ORDER BY 排序字段 LIMIT 偏移量, 行數(shù)但SQL真正執(zhí)行的順序不是這個(gè)。MySQL會(huì)先走FROM找到數(shù)據(jù)源然后WHERE過濾原始數(shù)據(jù)再GROUP BY分組之后HAVING過濾分組結(jié)果接著SELECT投影目標(biāo)字段然后ORDER BY排序最后LIMIT截取。理解這個(gè)執(zhí)行順序最直接的好處是能解釋很多“奇怪”的現(xiàn)象。比如WHERE條件里不能用SELECT中定義的別名因?yàn)閃HERE執(zhí)行時(shí)SELECT還沒執(zhí)行別名自然不存在。而ORDER BY可以用別名因?yàn)榕判虬l(fā)生在SELECT之后。再比如為什么GROUP BY之后SELECT能用的字段要么是分組字段要么是聚合函數(shù)的結(jié)果因?yàn)榉纸M后每組只保留一行其他字段的值可能有多條MySQL無法確定取哪個(gè)在嚴(yán)格模式下會(huì)直接報(bào)錯(cuò)。我見過一個(gè)很典型的案例某個(gè)統(tǒng)計(jì)報(bào)表里SQL寫得特別長(zhǎng)里面在WHERE里判斷了一個(gè)用CASE WHEN生成的別名結(jié)果一直報(bào)錯(cuò)。我當(dāng)時(shí)給出的修改方案就是把那個(gè)判斷挪到HAVING里或者在外面再包一層子查詢問題立刻解決。這種問題如果不懂執(zhí)行順序光看報(bào)錯(cuò)信息很難定位。3.2 WHERE過濾條件的類型陷阱和經(jīng)驗(yàn)寫法WHERE是查詢中最常用、也最容易埋坑的子句。最常見的一個(gè)坑是類型不一致導(dǎo)致索引失效。比如表里user_id是VARCHAR類型但查詢時(shí)傳了一個(gè)數(shù)字MySQL會(huì)把字符串類型字段隱式轉(zhuǎn)換成數(shù)字來比較導(dǎo)致該字段上的索引無法使用。反過來也一樣如果字段是INT類型查詢條件里傳了字符串同樣存在隱式轉(zhuǎn)換。所以一條基礎(chǔ)經(jīng)驗(yàn)是字段類型和條件值類型保持一致別讓MySQL幫你做隱式轉(zhuǎn)換。還有一個(gè)常見問題是函數(shù)包裹字段。比如在創(chuàng)建時(shí)間字段上寫了WHERE DATE(created_at) 2024-01-01乍一看沒毛病但實(shí)際上DATE()函數(shù)讓created_at無法直接使用索引MySQL不得不對(duì)每一行執(zhí)行函數(shù)計(jì)算然后再過濾。正確寫法是改成范圍條件WHERE created_at 2024-01-01 AND created_at 2024-01-02這是一個(gè)非常高頻的優(yōu)化點(diǎn)凡是出現(xiàn)“查詢慢但明明加了索引還是慢”的情況第一反應(yīng)就應(yīng)該是檢查WHERE條件里有沒有對(duì)索引字段使用函數(shù)或者做了隱式轉(zhuǎn)換。如果要表達(dá)得更嚴(yán)謹(jǐn)一點(diǎn)想讓索引生效就要讓索引字段保持“裸狀態(tài)”不做任何包裹計(jì)算。3.3 GROUP BY與HAVING分組統(tǒng)計(jì)的正確姿勢(shì)GROUP BY用于對(duì)數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì)是報(bào)表類需求的基石。比如統(tǒng)計(jì)每天訂單數(shù)SELECT DATE(created_at) AS day, COUNT(*) AS order_count FROM orders WHERE created_at 2024-01-01 GROUP BY day ORDER BY day;注意這里的DAY這個(gè)別名在GROUP BY和ORDER BY里都能用因?yàn)檫@兩步都發(fā)生在SELECT之后。這個(gè)寫法在MySQL里是允許的而且很常見。GROUP BY和HAVING經(jīng)常一起出現(xiàn)HAVING用于對(duì)分組后的結(jié)果再做過濾。很多人會(huì)把HAVING和WHERE搞混記一個(gè)簡(jiǎn)單的區(qū)別WHERE在分組之前過濾行HAVING在分組之后過濾分組。WHERE不能使用聚合函數(shù)HAVING可以。比如要找出訂單數(shù)超過100的用戶IDSELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id HAVING order_count 100;這條SQL是先按用戶分組統(tǒng)計(jì)再過濾出訂單數(shù)大于100的用戶。如果把這個(gè)條件放到WHERE里就會(huì)直接報(bào)錯(cuò)因?yàn)镃OUNT的值在WHERE階段根本還沒算出來。還有一點(diǎn)要注意HAVING的效率通常比WHERE差因?yàn)镠AVING要等分組完成才能過濾所以能用WHERE提前過濾的數(shù)據(jù)盡量在WHERE階段就過濾掉減少分組壓力。3.4 ORDER BY與LIMIT排序、分頁與深分頁優(yōu)化排序和分頁是日常接口開發(fā)里離不開的操作。最簡(jiǎn)單的查詢SELECT * FROM orders WHERE user_id 10001 ORDER BY created_at DESC LIMIT 20;這里有一個(gè)聯(lián)合索引設(shè)計(jì)的最佳實(shí)踐如果這個(gè)查詢是高頻的可以建一個(gè)(user_id, created_at)的聯(lián)合索引這樣WHERE定位到用戶后索引內(nèi)部已經(jīng)按created_at排好序了直接取前20條就行省去排序環(huán)節(jié)。這就是前面建表部分提到聯(lián)合索引設(shè)計(jì)原則的應(yīng)用。LIMIT的分頁寫法也很常見但深分頁是個(gè)隱藏的大坑。比如SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;這條SQL會(huì)把前100萬條數(shù)據(jù)都查出來然后丟棄只留下最后的20條。偏移量越大查詢?cè)铰沂蔷€性增長(zhǎng)的。解決思路有幾種。第一種是記錄上一次查詢的最大ID下次查詢帶上WHERE id 上次最大ID的條件用延遲關(guān)聯(lián)或索引覆蓋的方式去翻頁。第二種是子查詢先取偏移段的ID列表再回表拿完整數(shù)據(jù)SELECT * FROM orders WHERE id ( SELECT id FROM orders ORDER BY id LIMIT 1000000, 1 ) ORDER BY id LIMIT 20;這種方式先用索引快速定位到第1000001條的位置再往后取20條比直接LIMIT 1000000, 20高效得多。但要注意這種優(yōu)化方案只適用于有自增主鍵且數(shù)據(jù)沒有頻繁物理刪除的場(chǎng)景如果數(shù)據(jù)有大量隨機(jī)刪除ID之間的空洞會(huì)讓分頁結(jié)果不一致。實(shí)際項(xiàng)目中需要結(jié)合業(yè)務(wù)場(chǎng)景權(quán)衡。4. 多表查詢JOIN、子查詢、UNION怎么選單表查詢解決的是單個(gè)數(shù)據(jù)源的問題但業(yè)務(wù)需求往往需要關(guān)聯(lián)多張表。多表查詢的實(shí)現(xiàn)方式有三種JOIN、子查詢、UNION。每種方式都有各自的適用場(chǎng)景沒有絕對(duì)的好壞關(guān)鍵是要理解它們的執(zhí)行邏輯。4.1 JOIN家族INNER、LEFT、RIGHT的區(qū)別與場(chǎng)景JOIN是關(guān)聯(lián)查詢最核心的寫法。最常見的三種是INNER JOIN、LEFT JOIN、RIGHT JOIN。網(wǎng)上有很多圖解釋它們的區(qū)別這里我用實(shí)際業(yè)務(wù)來舉例說明。假設(shè)有兩張表用戶表users和訂單表orders。想知道“哪些用戶下過單”用INNER JOIN只返回兩邊都匹配的行SELECT users.id, users.name, orders.order_no FROM users INNER JOIN orders ON users.id orders.user_id;想知道“每個(gè)用戶和他的所有訂單沒下過單的用戶也要顯示出來”用LEFT JOIN左表數(shù)據(jù)全部保留右表無匹配就以NULL填充SELECT users.id, users.name, orders.order_no FROM users LEFT JOIN orders ON users.id orders.user_id;這個(gè)場(chǎng)景最常見的就是列表頁需要展示主表數(shù)據(jù)即使副表沒數(shù)據(jù)也要顯示一行。RIGHT JOIN的用法和LEFT JOIN對(duì)稱以右表為主但實(shí)際開發(fā)中用得很少。我個(gè)人的建議是統(tǒng)一用LEFT JOIN就好不要混用RIGHT JOIN否則SQL的可讀性會(huì)變差團(tuán)隊(duì)同事看代碼也不舒服。關(guān)聯(lián)條件上有一個(gè)極其重要的坑LEFT JOIN中如果在WHERE里對(duì)右表字段加過濾條件會(huì)導(dǎo)致“左表保留全部行”的語義被破壞。舉例SELECT users.id, users.name, orders.order_no FROM users LEFT JOIN orders ON users.id orders.user_id WHERE orders.status 1;這條SQL的意圖可能是“查所有用戶中已支付訂單的關(guān)聯(lián)情況”但實(shí)際執(zhí)行時(shí)因?yàn)閃HERE在JOIN之后過濾已經(jīng)把左表中沒有匹配訂單的用戶行全部剔除了LEFT JOIN變成了INNER JOIN的效果。想要保持左表全部保留應(yīng)該把過濾條件寫在ON子句里SELECT users.id, users.name, orders.order_no FROM users LEFT JOIN orders ON users.id orders.user_id AND orders.status 1;這個(gè)細(xì)節(jié)我已經(jīng)在真實(shí)項(xiàng)目中碰到不止一次每次都是面試題級(jí)別的經(jīng)典陷阱而且業(yè)務(wù)上一旦踩中定位問題往往要花不少時(shí)間。4.2 子查詢與JOIN的取舍子查詢是嵌套在主查詢里的SELECT語句可以分為FROM子句里的子查詢、 WHERE子句里的子查詢、SELECT子句里的標(biāo)量子查詢幾種。比如要查“每個(gè)用戶最近一筆訂單”可以用關(guān)聯(lián)子查詢的方式SELECT o.* FROM orders o WHERE o.id ( SELECT MAX(id) FROM orders o2 WHERE o2.user_id o.user_id );這個(gè)寫法理解起來比較簡(jiǎn)單但它對(duì)每條訂單都會(huì)執(zhí)行一次子查詢?nèi)绻麛?shù)據(jù)量大性能會(huì)很差。還有一類常見的子查詢是用IN比如SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount 100);這種寫法的隱患是如果子查詢返回的結(jié)果集特別大IN列表會(huì)非常長(zhǎng)效率不高。而且某些MySQL版本對(duì)IN子查詢的優(yōu)化做得不夠好執(zhí)行計(jì)劃可能會(huì)退化成全表掃描。更穩(wěn)健的做法是用EXISTS改寫或者改成JOINSELECT DISTINCT users.* FROM users INNER JOIN orders ON users.id orders.user_id AND orders.amount 100;用JOIN的語義更直白執(zhí)行計(jì)劃可控性也更高。當(dāng)然JOIN之后如果主表數(shù)據(jù)有重復(fù)關(guān)聯(lián)需要加上DISTINCT去重這時(shí)要自己確認(rèn)是否會(huì)產(chǎn)生重復(fù)行。子查詢和JOIN的選擇沒有絕對(duì)標(biāo)準(zhǔn)我的經(jīng)驗(yàn)是小數(shù)據(jù)量、語義直觀的子查詢可以用大數(shù)據(jù)量、性能敏感的查詢優(yōu)先考慮JOIN和EXISTS然后通過EXPLAIN確認(rèn)執(zhí)行計(jì)劃。4.3 UNION與UNION ALL別讓去重拖慢查詢UNION和UNION ALL都是將多個(gè)SELECT的結(jié)果集合并輸出。區(qū)別在于UNION會(huì)去重UNION ALL不去重。這一字之差性能差距可能非常明顯因?yàn)槿ブ匾馕吨枰獙?duì)整個(gè)結(jié)果集排序或走哈希去重額外開銷極大。業(yè)務(wù)上如果明確兩個(gè)結(jié)果集不可能有重復(fù)或者重復(fù)無害直接用UNION ALL就沒問題了。舉一個(gè)實(shí)際場(chǎng)景要查“訂單金額大于1000的訂單”和“最近7天創(chuàng)建的訂單”的合并列表。兩個(gè)條件有交叉的可能但最終展示訂單列表時(shí)重復(fù)也沒關(guān)系所以SELECT order_no, created_at FROM orders WHERE amount 1000 UNION ALL SELECT order_no, created_at FROM orders WHERE created_at NOW() - INTERVAL 7 DAY;這樣比UNION快不少而且邏輯更符合業(yè)務(wù)預(yù)期。另外還有一個(gè)容易被忽略的事項(xiàng)UNION的子句之間各SELECT返回的列數(shù)和數(shù)據(jù)類型必須一致否則MySQL會(huì)報(bào)錯(cuò)。比如第一個(gè)SELECT返回了兩列第二個(gè)SELECT返回了三列直接編譯失敗。如果各表中列名不一致第一個(gè)SELECT的列名會(huì)作為結(jié)果集的列名。5. 提升查詢性能的關(guān)鍵索引與執(zhí)行計(jì)劃前面講了很多查詢寫法但真正跑線上查詢的時(shí)候性能怎么樣不能靠猜得靠數(shù)據(jù)說話。這一部分重點(diǎn)說索引的坑和EXPLAIN執(zhí)行計(jì)劃的用法。5.1 索引失效的幾種常見姿勢(shì)索引失效是慢查詢的頭號(hào)原因。我總結(jié)過幾條高頻踩坑場(chǎng)景基本覆蓋了日常開發(fā)里90%的問題。第一對(duì)索引列使用函數(shù)或表達(dá)式計(jì)算。前面已經(jīng)提過WHERE DATE(created_at) 2024-01-01會(huì)導(dǎo)致索引失效。同樣地WHERE amount 1 100這種寫法也會(huì)讓amount字段的索引失效。第二隱式類型轉(zhuǎn)換。varchar字段和數(shù)字比較、int字段和字符串比較都會(huì)讓MySQL放棄索引。第三LIKE模糊匹配以%開頭。比如WHERE name LIKE %張%因?yàn)闊o法確定前綴B樹無法定位只能全表掃。反過來WHERE name LIKE 張%可以走索引。第四聯(lián)合索引不使用最左前綴。建了(a, b, c)聯(lián)合索引條件里直接查b1跳過a索引用不上。這是最左前綴原則決定的。在設(shè)計(jì)聯(lián)合索引時(shí)一定要把最高頻的等值查詢字段放最左邊。第五OR條件導(dǎo)致索引失效。比如WHERE a 1 OR b 2如果只有a字段有索引MySQL可能選擇全表掃描。優(yōu)化方式是把OR改成UNION或者給b也加上索引。第六對(duì)NULL值的判斷。WHERE name IS NULL不一定導(dǎo)致索引失效但WHERE name IS NOT NULL在很多場(chǎng)景下優(yōu)化器會(huì)認(rèn)為返回?cái)?shù)據(jù)量太大直接放棄索引。這里沒有絕對(duì)結(jié)論實(shí)際操作中以EXPLAIN結(jié)果為準(zhǔn)。第七ORDER BY和GROUP BY的字段順序與索引順序不一致。比如聯(lián)合索引(a, b)但SQL里ORDER BY b, a索引順序不匹配排序走文件排序性能下降。5.2 EXPLAIN怎么看重點(diǎn)字段速查EXPLAIN是排查慢查詢的核心工具。用法很簡(jiǎn)單在SELECT前面加個(gè)EXPLAIN關(guān)鍵字就行EXPLAIN SELECT * FROM orders WHERE user_id 10001 AND created_at 2024-01-01;返回結(jié)果里重點(diǎn)看這幾個(gè)字段type訪問類型。從好到差依次是const、eq_ref、ref、range、index、ALL。出現(xiàn)ALL表示全表掃描要高度警惕。range說明掃描了一個(gè)索引范圍比全表掃描好得多。key實(shí)際用到的索引名。如果為NULL說明沒走索引。rows預(yù)估掃描的行數(shù)。這個(gè)數(shù)越大查詢通常越慢。Extra額外信息。出現(xiàn)Using filesort或Using temporary說明排序或分組用了臨時(shí)文件性能往往不佳出現(xiàn)Using index則說明是覆蓋索引性能很好。舉個(gè)實(shí)際排查案例某次線上接口變慢EXPLAIN結(jié)果顯示一條查詢的type為ALLrows達(dá)到500多萬。打開SQL一看WHERE條件里對(duì)create_time用了DATE()函數(shù)。改成范圍條件后type變成rangerows降到幾千接口響應(yīng)時(shí)間從接近2秒降到幾十毫秒。這就是EXPLAIN的價(jià)值——用數(shù)據(jù)定位問題而不是拍腦袋猜。5.3 一個(gè)真實(shí)慢查詢優(yōu)化案例之前處理過某業(yè)務(wù)系統(tǒng)的一個(gè)慢查詢是訂單列表接口。SQL大概長(zhǎng)這樣SELECT id, order_no, user_id, amount, status, created_at FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 10;表里訂單量接近千萬user_id和created_at各自都是獨(dú)立索引但查詢依然慢。EXPLAIN之后發(fā)現(xiàn)優(yōu)化器在兩個(gè)索引之間選擇了一個(gè)但Extra里出現(xiàn)了Using filesort也就是排序沒有完全走索引。原因很簡(jiǎn)單優(yōu)化器走的是idx_user_id先定位到該用戶的所有訂單再對(duì)這些訂單按created_at排序。因?yàn)閏reated_at不滿足索引順序排序操作只能在內(nèi)存或磁盤上做數(shù)據(jù)量一大就慢。解決方案就是前面提到的聯(lián)合索引ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);這個(gè)聯(lián)合索引既能通過user_id快速定位又能在索引內(nèi)部天然按created_at排好序完美匹配ORDER BY的需求。加上索引之后EXPLAIN顯示Using filesort消失查詢時(shí)間從幾百毫秒降到個(gè)位數(shù)毫秒。這個(gè)案例值得記住的不是SQL寫法本身而是索引設(shè)計(jì)要兼顧WHERE和ORDER BY。很多人在聯(lián)合索引上只考慮過濾字段忽略了排序字段結(jié)果WHERE很快但ORDER BY把整個(gè)查詢拖慢。6. 常見問題與排查技巧實(shí)錄最后把這些年在MySQL表操作和查詢這塊遇到的高頻問題統(tǒng)一整理成一張速查表方便大家在實(shí)際開發(fā)中對(duì)號(hào)入座。癥狀可能原因排查思路解決方向中文亂碼字符集不一致或連接層字符集不對(duì)檢查庫、表、字段、連接的字符集統(tǒng)一為utf8mb4重建連接并確認(rèn)character_set_server查詢?cè)絹碓铰彼饕⑺饕Щ驋呙栊袛?shù)過多執(zhí)行EXPLAIN看type、rows、key補(bǔ)聯(lián)合索引、改寫條件、避免函數(shù)包裹加了索引還是慢索引列被函數(shù)包裹、隱式類型轉(zhuǎn)換或OR條件檢查WHERE里索引列是否“裸狀態(tài)”去掉函數(shù)/轉(zhuǎn)換OR改為UNION或補(bǔ)索引排序慢索引順序與ORDER BY不匹配看Extra是否有Using filesort設(shè)計(jì)聯(lián)合索引時(shí)把排序字段放進(jìn)去LIMIT翻頁到后面很慢深分頁在大偏移量下掃描大量無用行分析偏移量大小、掃描行數(shù)用ID定位、延遲關(guān)聯(lián)或減少偏移量DELETE后表空間不減少InnoDB的已刪除行空間未立即釋放查看表大小、碎片情況定期OPTIMIZE TABLE或接受空間復(fù)用機(jī)制同一查詢不同環(huán)境結(jié)果不同字符集、排序規(guī)則或數(shù)據(jù)分布差異對(duì)比兩邊的表結(jié)構(gòu)定義和數(shù)據(jù)分布用小數(shù)據(jù)量復(fù)現(xiàn)并對(duì)比執(zhí)行計(jì)劃GROUP BY報(bào)錯(cuò)沒開啟嚴(yán)格模式或SELECT字段與GROUP BY字段不匹配查看sql_mode配置和SQL寫法把非分組字段改為聚合函數(shù)或加入GROUP BY關(guān)聯(lián)查詢數(shù)據(jù)量暴增JOIN條件產(chǎn)生笛卡爾積檢查ON條件是否缺失或條件過寬確認(rèn)關(guān)聯(lián)字段有索引條件收窄查詢結(jié)果包含重復(fù)數(shù)據(jù)JOIN一對(duì)一關(guān)系變一對(duì)多檢查關(guān)聯(lián)表的唯一性、關(guān)聯(lián)鍵是否有重復(fù)加DISTINCT或調(diào)整關(guān)聯(lián)SQL排查問題的時(shí)候有一個(gè)實(shí)用心法不要一上來就改SQL。先花兩分鐘看表結(jié)構(gòu)再執(zhí)行EXPLAIN看執(zhí)行計(jì)劃最后才動(dòng)手改。大多數(shù)慢查詢問題在執(zhí)行計(jì)劃階段就能露出馬腳改SQL只是收尾動(dòng)作。還有一個(gè)經(jīng)驗(yàn)之談排查過程中把當(dāng)時(shí)的表結(jié)構(gòu)、數(shù)據(jù)量、SQL語句、執(zhí)行計(jì)劃這四個(gè)信息記錄下來問題解決之后再回頭復(fù)盤一段時(shí)間下來自己解決問題的能力會(huì)提升一大截。表操作和查詢是MySQL最基礎(chǔ)的能力但也是最值得反復(fù)打磨的能力。工作中見到過太多“上線前沒問題跑了一段時(shí)間之后突然慢得出奇”的案例究其原因多數(shù)不是SQL本身的問題而是表結(jié)構(gòu)設(shè)計(jì)和查詢方式對(duì)數(shù)據(jù)增長(zhǎng)的適應(yīng)性不足。比如索引設(shè)計(jì)沒考慮到數(shù)據(jù)規(guī)模的增長(zhǎng)、表結(jié)構(gòu)變更沒有預(yù)留彈性、深分頁沒有提前做容量規(guī)劃。所以我的經(jīng)驗(yàn)是寫建表DDL和查詢SQL的時(shí)候多問自己一句這個(gè)表和這條SQL在數(shù)據(jù)量再漲十倍、漲百倍之后還能不能撐住帶著這個(gè)意識(shí)去寫每一行SQL能避掉絕大部分性能坑。最后再分享一個(gè)小技巧每張業(yè)務(wù)表都留一個(gè)自增主鍵和創(chuàng)建時(shí)間字段這兩個(gè)字段雖然平淡卻是后續(xù)排查問題時(shí)最可靠的抓手。 MySQL表設(shè)計(jì)與查詢優(yōu)化：從建表規(guī)范到慢查詢實(shí)戰(zhàn)
1. 先把“表”這件事想清楚字段、類型與約束只要是接觸過MySQL的人應(yīng)該都有過這種經(jīng)歷剛開始建表時(shí)特別隨意字段類型憑感覺選約束能不加就不加等業(yè)務(wù)跑了大半年數(shù)據(jù)量上來之后才發(fā)現(xiàn)一堆歷史遺留問題——查詢慢、亂碼、數(shù)據(jù)對(duì)不上、沒法統(tǒng)計(jì)。再回頭改表結(jié)構(gòu)又怕鎖表影響線上業(yè)務(wù)。所以聊MySQL表操作和查詢之前我想先說說建表階段那些容易被忽略、但影響極其深遠(yuǎn)的基礎(chǔ)設(shè)計(jì)問題。畢竟表結(jié)構(gòu)設(shè)計(jì)得是否合理直接決定后面所有查詢語句好不好寫、跑得快不快。1.1 字段類型選錯(cuò)了后面全是淚字段類型是表結(jié)構(gòu)里最基礎(chǔ)但也最容易出錯(cuò)的地方。很多新手喜歡“省事”所有字符串一律用varchar(255)所有數(shù)值一律用int日期就放一個(gè)字符串字段存進(jìn)去。短期看確實(shí)沒問題等數(shù)據(jù)量到了幾百萬行各種問題就來了字符串類型的日期沒法直接用日期函數(shù)做范圍統(tǒng)計(jì)int存不下手機(jī)號(hào)直接溢出varchar(255)在utf8mb4字符集下單字段索引長(zhǎng)度超限導(dǎo)致索引失效。這里我直接給一套常用的選型參考整數(shù)能用int就別用bigint能用tinyint就別用int。狀態(tài)值、開關(guān)值用tinyint主鍵用bigint還是int取決于業(yè)務(wù)量預(yù)估但不要一拍腦袋就給所有表都上bigint。小數(shù)不要用float和double存金額精度丟失的問題在實(shí)際項(xiàng)目中非?？?。用decimal比如decimal(10,2)10表示總位數(shù)2表示小數(shù)位。字符串定長(zhǎng)的用char比如身份證號(hào)、手機(jī)號(hào)這種長(zhǎng)度固定的變長(zhǎng)的用varchar但要給合理的最大長(zhǎng)度別一上來就255。長(zhǎng)文本用text但要注意text字段不能有默認(rèn)值索引也只能前綴索引。日期時(shí)間這個(gè)特別值得多說一句。強(qiáng)烈建議用datetime不要用timestamp因?yàn)閠imestamp有2038年的上限問題。另外不要用字符串存日期雖然看起來方便但丟了時(shí)間函數(shù)的便利性也無法利用索引做高效的區(qū)間查詢。有人可能會(huì)問datetime和timestamp到底差在哪。簡(jiǎn)單說timestamp存儲(chǔ)的是UTC時(shí)間存的時(shí)候會(huì)轉(zhuǎn)時(shí)區(qū)取的時(shí)候也會(huì)轉(zhuǎn)回來受時(shí)區(qū)設(shè)置影響datetime存的就是字面時(shí)間不涉及時(shí)區(qū)轉(zhuǎn)換。對(duì)于業(yè)務(wù)系統(tǒng)來說大多數(shù)情況下datetime更直觀、更可控避免因?yàn)閿?shù)據(jù)庫時(shí)區(qū)和應(yīng)用時(shí)區(qū)不一致造成數(shù)據(jù)錯(cuò)亂。1.2 約束與索引建表時(shí)一次想好別等數(shù)據(jù)量大了再回頭約束是很多人建表時(shí)最容易忽略的東西。常見的主鍵約束、唯一約束、非空約束、默認(rèn)值約束以及外鍵約束我發(fā)現(xiàn)很多項(xiàng)目干脆一個(gè)外鍵都不用。不用的原因也簡(jiǎn)單外鍵在寫入的時(shí)候有額外的校驗(yàn)開銷分布式拆分表的時(shí)候也麻煩所以業(yè)務(wù)系統(tǒng)基本都靠應(yīng)用層來保證關(guān)聯(lián)數(shù)據(jù)的完整性。這可以理解但至少主鍵、唯一鍵、非空、默認(rèn)值這些基礎(chǔ)約束該加就得加。主鍵的選擇上我建議用自增int/bigint作為主鍵業(yè)務(wù)字段不要直接當(dāng)主鍵。原因有兩個(gè)一是InnoDB是聚簇索引組織表主鍵是物理存儲(chǔ)的順序依據(jù)自增主鍵能保證插入時(shí)順序?qū)懭霚p少頁分裂二是業(yè)務(wù)字段的變更不可控拿手機(jī)號(hào)當(dāng)主鍵哪天允許用戶改綁手機(jī)號(hào)整個(gè)索引結(jié)構(gòu)都得跟著動(dòng)。唯一約束這塊要注意它和普通索引的區(qū)別。唯一約束本身就是索引只是額外加了唯一性校驗(yàn)。所以如果業(yè)務(wù)上需要保證某個(gè)字段唯一直接加unique key不要用普通索引之后再在應(yīng)用層做查詢校驗(yàn)既慢又容易有并發(fā)漏洞。還有個(gè)很實(shí)用的習(xí)慣每張表都加上創(chuàng)建時(shí)間和更新時(shí)間兩個(gè)字段類型用datetime更新時(shí)間可以靠業(yè)務(wù)代碼維護(hù)也可以建表時(shí)用ON UPDATE CURRENT_TIMESTAMP自動(dòng)更新。這兩個(gè)字段在排查數(shù)據(jù)問題時(shí)幾乎是救命稻草尤其是出現(xiàn)臟數(shù)據(jù)、重復(fù)數(shù)據(jù)、異常數(shù)據(jù)的時(shí)候能迅速定位出問題時(shí)間點(diǎn)。1.3 字符集與排序規(guī)則亂碼和排序不對(duì)的頭號(hào)原因字符集問題可以說是MySQL新手時(shí)期最常遇到的坑之一。我記得第一次接手一個(gè)老項(xiàng)目明明代碼里都是UTF-8數(shù)據(jù)庫里卻出現(xiàn)一串問號(hào)和亂碼排查了半天最后發(fā)現(xiàn)庫的默認(rèn)字符集是latin1。這種問題一旦上線之后再想改代價(jià)非常大。所以在建庫建表的時(shí)候第一件事就是明確字符集?，F(xiàn)在的統(tǒng)一標(biāo)準(zhǔn)就是utf8mb4注意不是utf8。MySQL里的utf8是utf8mb3的別名只支持最多3字節(jié)的字符像emoji這種4字節(jié)字符就存不了會(huì)報(bào)錯(cuò)或者變成亂碼。utf8mb4才是真正的“完整UTF-8”兼容所有Unicode字符。排序規(guī)則也要一并確認(rèn)。utf8mb4_general_ci和utf8mb4_unicode_ci是兩種常見選擇。前者排序快但精度略差后者排序更準(zhǔn)確但稍微慢一點(diǎn)。絕大多數(shù)業(yè)務(wù)系統(tǒng)其實(shí)用哪個(gè)差別都不大需要重點(diǎn)注意的是別出現(xiàn)表、庫、連接層字符集不一致的情況。判斷起來也簡(jiǎn)單執(zhí)行一次查詢?nèi)绻l(fā)現(xiàn)中文亂碼先別急著改數(shù)據(jù)從客戶端連接、到數(shù)據(jù)庫連接、再到表和字段的字符集一層層排查。最常見的坑是連接層用了utf8而表和字段是utf8mb4中文正常遇到特殊字符就出問題。2. 建表、改表、刪表一張表的完整生命周期管理前面說了設(shè)計(jì)層面的問題接下來就進(jìn)入實(shí)際操作環(huán)節(jié)。表操作無非就是建表、改表、刪表這三類但每一類里都有不少細(xì)節(jié)值得展開。很多人寫SQL的時(shí)候只關(guān)心能不能跑通忽略了操作帶來的影響面尤其是生產(chǎn)環(huán)境里一條ALTER TABLE就可能把整個(gè)業(yè)務(wù)拖垮。2.1 建表從需求到DDL落地的實(shí)操寫法先看一個(gè)實(shí)際的建表示例。假設(shè)要建一張用戶訂單表包含訂單號(hào)、用戶ID、商品名稱、訂單金額、訂單狀態(tài)、創(chuàng)建時(shí)間、更新時(shí)間這些基本字段。第一版DDL可以這樣寫CREATE TABLE orders ( id BIGINT NOT NULL AUTO_INCREMENT COMMENT 主鍵ID, order_no VARCHAR(64) NOT NULL COMMENT 訂單號(hào), user_id BIGINT NOT NULL COMMENT 用戶ID, product_name VARCHAR(128) NOT NULL COMMENT 商品名稱, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 訂單金額, status TINYINT NOT NULL DEFAULT 0 COMMENT 訂單狀態(tài)0待支付 1已支付 2已取消, 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 uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_created_at (created_at) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_general_ci COMMENT用戶訂單表;這個(gè)建表語句里的幾個(gè)細(xì)節(jié)值得展開說明一下。首先是ENGINEInnoDBMySQL 5.7之后默認(rèn)就是InnoDB但寫出來更明確。InnoDB支持事務(wù)、行級(jí)鎖、崩潰恢復(fù)是絕大多數(shù)業(yè)務(wù)場(chǎng)景的唯一選擇。有些人可能聽說過MyISAM查詢快但它不支持事務(wù)、不支持行鎖寫并發(fā)一高就全線阻塞真不適合業(yè)務(wù)系統(tǒng)用。其次是每個(gè)字段都加了COMMENT注釋。這個(gè)習(xí)慣非常好。一個(gè)項(xiàng)目壽命往往遠(yuǎn)超想象兩三年后你自己回頭維護(hù)這張表如果沒有注釋真的會(huì)想不起來某個(gè)字段是干什么用的。更不用說團(tuán)隊(duì)協(xié)作別人接手你的表結(jié)構(gòu)時(shí)良好的注釋能省下大量溝通成本。再注意到status字段用的是TINYINT而不是VARCHAR或者ENUM。用數(shù)字表示狀態(tài)擴(kuò)展性好0、1、2不夠用隨時(shí)可以加。但這里有個(gè)小建議數(shù)字狀態(tài)值一定要配注釋說明含義否則時(shí)間長(zhǎng)了沒人知道1代表什么。不過更推薦的做法是在應(yīng)用層代碼里定義狀態(tài)常量注釋輔助解釋兩邊對(duì)應(yīng)著看。2.2 ALTER TABLE加字段、改字段、加索引的常見操作業(yè)務(wù)迭代過程中表結(jié)構(gòu)幾乎一定會(huì)變。ALTER TABLE的語法比較簡(jiǎn)單但生產(chǎn)環(huán)境里要特別注意執(zhí)行成本和鎖表風(fēng)險(xiǎn)。加字段是最常見的操作。比如給訂單表加一個(gè)支付時(shí)間字段ALTER TABLE orders ADD COLUMN paid_at DATETIME DEFAULT NULL COMMENT 支付時(shí)間 AFTER status;這里有一個(gè)非常實(shí)用的習(xí)慣把新字段用AFTER關(guān)鍵字放在相關(guān)字段旁邊而不是默認(rèn)加到最后。雖然最終效果不影響查詢但看表結(jié)構(gòu)時(shí)邏輯會(huì)更清晰。另外注意在數(shù)據(jù)量很大的表上執(zhí)行ALTER TABLEInnoDB需要重建表會(huì)鎖住寫操作耗時(shí)可能非常長(zhǎng)甚至導(dǎo)致主從延遲。直到MySQL 5.6引入了在線DDL加上MySQL 8.0的進(jìn)一步優(yōu)化很多ALTER操作已經(jīng)不會(huì)完全鎖表了但字段類型變更、修改字符集這類操作仍然可能很重。所以在生產(chǎn)環(huán)境做表結(jié)構(gòu)變更前我一般會(huì)先確認(rèn)表的行數(shù)和當(dāng)前實(shí)例的負(fù)載盡量挑業(yè)務(wù)低峰期操作。修改字段類型和默認(rèn)值也是高頻操作。語法如下ALTER TABLE orders MODIFY COLUMN status TINYINT NOT NULL DEFAULT 1 COMMENT 訂單狀態(tài)0待支付 1已支付 2已取消 3已退款;同樣一個(gè)MODIFY操作看似簡(jiǎn)單但如果表很大執(zhí)行期間同樣是有代價(jià)的。而且MODIFY COLUMN會(huì)改變字段的整條定義如果你只寫了一部分屬性其他屬性會(huì)被重置所以執(zhí)行前最好先用SHOW CREATE TABLE把原有定義完整看一下再確認(rèn)要改的信息避免誤把已有的NOT NULL、DEFAULT等屬性弄丟。加索引的常見操作ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);這是一個(gè)典型的聯(lián)合索引能夠同時(shí)優(yōu)化“查某個(gè)用戶的訂單按時(shí)間倒序”這類高頻查詢。聯(lián)合索引的核心設(shè)計(jì)原則是區(qū)分度高的字段放前面等值查詢字段放前面范圍查詢字段放后面。這個(gè)后面講到查詢優(yōu)化時(shí)還會(huì)再展開。2.3 刪表與清空DROP / TRUNCATE / DELETE的本質(zhì)區(qū)別刪表有三種方式很多人分不清它們的適用場(chǎng)景。DROP TABLE是整張表直接刪掉表結(jié)構(gòu)、數(shù)據(jù)、索引全部消失不可恢復(fù)。TRUNCATE TABLE是清空數(shù)據(jù)但保留表結(jié)構(gòu)相當(dāng)于重置這張表不可按條件刪也不能回滾。DELETE FROM是逐行刪除可以帶WHERE條件且走事務(wù)可以回滾。所以日常開發(fā)中真正不要這張表了才用DROP想要快速清空一張臨時(shí)表或日志表的數(shù)據(jù)用TRUNCATE刪除符合條件的部分?jǐn)?shù)據(jù)用DELETE。這里特別提一個(gè)問題很多人誤以為DELETE之后數(shù)據(jù)量還是沒降下來。這是因?yàn)镮nnoDB刪除的行標(biāo)記為“已刪除”但物理空間沒有被立即釋放后續(xù)有新數(shù)據(jù)插入時(shí)才會(huì)復(fù)用這些空間。如果只是跑了一次DELETE FROM大表磁盤空間不變是很正常的不要慌張。想要真正收縮表空間可以執(zhí)行OPTIMIZE TABLE或者ALTER TABLE ... ENGINEInnoDB來重建表但這又是一個(gè)重操作生產(chǎn)環(huán)境慎用。3. 單表查詢SELECT的核心邏輯與執(zhí)行順序聊完表操作進(jìn)入查詢部分。查詢是MySQL里最日常、也最考驗(yàn)功力的操作。很多人寫了幾年的SQL遇到慢查詢還是只會(huì)加索引其實(shí)很多問題的根源不在于缺索引而在于沒有真正理解SELECT的執(zhí)行邏輯。3.1 SELECT語法順序與執(zhí)行順序是兩碼事這是個(gè)老生常談卻又極其重要的話題。一條完整的SELECT語句書寫的順序是SELECT 字段 FROM 表 WHERE 條件 GROUP BY 分組字段 HAVING 分組后條件 ORDER BY 排序字段 LIMIT 偏移量, 行數(shù)但SQL真正執(zhí)行的順序不是這個(gè)。MySQL會(huì)先走FROM找到數(shù)據(jù)源然后WHERE過濾原始數(shù)據(jù)再GROUP BY分組之后HAVING過濾分組結(jié)果接著SELECT投影目標(biāo)字段然后ORDER BY排序最后LIMIT截取。理解這個(gè)執(zhí)行順序最直接的好處是能解釋很多“奇怪”的現(xiàn)象。比如WHERE條件里不能用SELECT中定義的別名因?yàn)閃HERE執(zhí)行時(shí)SELECT還沒執(zhí)行別名自然不存在。而ORDER BY可以用別名因?yàn)榕判虬l(fā)生在SELECT之后。再比如為什么GROUP BY之后SELECT能用的字段要么是分組字段要么是聚合函數(shù)的結(jié)果因?yàn)榉纸M后每組只保留一行其他字段的值可能有多條MySQL無法確定取哪個(gè)在嚴(yán)格模式下會(huì)直接報(bào)錯(cuò)。我見過一個(gè)很典型的案例某個(gè)統(tǒng)計(jì)報(bào)表里SQL寫得特別長(zhǎng)里面在WHERE里判斷了一個(gè)用CASE WHEN生成的別名結(jié)果一直報(bào)錯(cuò)。我當(dāng)時(shí)給出的修改方案就是把那個(gè)判斷挪到HAVING里或者在外面再包一層子查詢問題立刻解決。這種問題如果不懂執(zhí)行順序光看報(bào)錯(cuò)信息很難定位。3.2 WHERE過濾條件的類型陷阱和經(jīng)驗(yàn)寫法WHERE是查詢中最常用、也最容易埋坑的子句。最常見的一個(gè)坑是類型不一致導(dǎo)致索引失效。比如表里user_id是VARCHAR類型但查詢時(shí)傳了一個(gè)數(shù)字MySQL會(huì)把字符串類型字段隱式轉(zhuǎn)換成數(shù)字來比較導(dǎo)致該字段上的索引無法使用。反過來也一樣如果字段是INT類型查詢條件里傳了字符串同樣存在隱式轉(zhuǎn)換。所以一條基礎(chǔ)經(jīng)驗(yàn)是字段類型和條件值類型保持一致別讓MySQL幫你做隱式轉(zhuǎn)換。還有一個(gè)常見問題是函數(shù)包裹字段。比如在創(chuàng)建時(shí)間字段上寫了WHERE DATE(created_at) 2024-01-01乍一看沒毛病但實(shí)際上DATE()函數(shù)讓created_at無法直接使用索引MySQL不得不對(duì)每一行執(zhí)行函數(shù)計(jì)算然后再過濾。正確寫法是改成范圍條件WHERE created_at 2024-01-01 AND created_at 2024-01-02這是一個(gè)非常高頻的優(yōu)化點(diǎn)凡是出現(xiàn)“查詢慢但明明加了索引還是慢”的情況第一反應(yīng)就應(yīng)該是檢查WHERE條件里有沒有對(duì)索引字段使用函數(shù)或者做了隱式轉(zhuǎn)換。如果要表達(dá)得更嚴(yán)謹(jǐn)一點(diǎn)想讓索引生效就要讓索引字段保持“裸狀態(tài)”不做任何包裹計(jì)算。3.3 GROUP BY與HAVING分組統(tǒng)計(jì)的正確姿勢(shì)GROUP BY用于對(duì)數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì)是報(bào)表類需求的基石。比如統(tǒng)計(jì)每天訂單數(shù)SELECT DATE(created_at) AS day, COUNT(*) AS order_count FROM orders WHERE created_at 2024-01-01 GROUP BY day ORDER BY day;注意這里的DAY這個(gè)別名在GROUP BY和ORDER BY里都能用因?yàn)檫@兩步都發(fā)生在SELECT之后。這個(gè)寫法在MySQL里是允許的而且很常見。GROUP BY和HAVING經(jīng)常一起出現(xiàn)HAVING用于對(duì)分組后的結(jié)果再做過濾。很多人會(huì)把HAVING和WHERE搞混記一個(gè)簡(jiǎn)單的區(qū)別WHERE在分組之前過濾行HAVING在分組之后過濾分組。WHERE不能使用聚合函數(shù)HAVING可以。比如要找出訂單數(shù)超過100的用戶IDSELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id HAVING order_count 100;這條SQL是先按用戶分組統(tǒng)計(jì)再過濾出訂單數(shù)大于100的用戶。如果把這個(gè)條件放到WHERE里就會(huì)直接報(bào)錯(cuò)因?yàn)镃OUNT的值在WHERE階段根本還沒算出來。還有一點(diǎn)要注意HAVING的效率通常比WHERE差因?yàn)镠AVING要等分組完成才能過濾所以能用WHERE提前過濾的數(shù)據(jù)盡量在WHERE階段就過濾掉減少分組壓力。3.4 ORDER BY與LIMIT排序、分頁與深分頁優(yōu)化排序和分頁是日常接口開發(fā)里離不開的操作。最簡(jiǎn)單的查詢SELECT * FROM orders WHERE user_id 10001 ORDER BY created_at DESC LIMIT 20;這里有一個(gè)聯(lián)合索引設(shè)計(jì)的最佳實(shí)踐如果這個(gè)查詢是高頻的可以建一個(gè)(user_id, created_at)的聯(lián)合索引這樣WHERE定位到用戶后索引內(nèi)部已經(jīng)按created_at排好序了直接取前20條就行省去排序環(huán)節(jié)。這就是前面建表部分提到聯(lián)合索引設(shè)計(jì)原則的應(yīng)用。LIMIT的分頁寫法也很常見但深分頁是個(gè)隱藏的大坑。比如SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;這條SQL會(huì)把前100萬條數(shù)據(jù)都查出來然后丟棄只留下最后的20條。偏移量越大查詢?cè)铰沂蔷€性增長(zhǎng)的。解決思路有幾種。第一種是記錄上一次查詢的最大ID下次查詢帶上WHERE id 上次最大ID的條件用延遲關(guān)聯(lián)或索引覆蓋的方式去翻頁。第二種是子查詢先取偏移段的ID列表再回表拿完整數(shù)據(jù)SELECT * FROM orders WHERE id ( SELECT id FROM orders ORDER BY id LIMIT 1000000, 1 ) ORDER BY id LIMIT 20;這種方式先用索引快速定位到第1000001條的位置再往后取20條比直接LIMIT 1000000, 20高效得多。但要注意這種優(yōu)化方案只適用于有自增主鍵且數(shù)據(jù)沒有頻繁物理刪除的場(chǎng)景如果數(shù)據(jù)有大量隨機(jī)刪除ID之間的空洞會(huì)讓分頁結(jié)果不一致。實(shí)際項(xiàng)目中需要結(jié)合業(yè)務(wù)場(chǎng)景權(quán)衡。4. 多表查詢JOIN、子查詢、UNION怎么選單表查詢解決的是單個(gè)數(shù)據(jù)源的問題但業(yè)務(wù)需求往往需要關(guān)聯(lián)多張表。多表查詢的實(shí)現(xiàn)方式有三種JOIN、子查詢、UNION。每種方式都有各自的適用場(chǎng)景沒有絕對(duì)的好壞關(guān)鍵是要理解它們的執(zhí)行邏輯。4.1 JOIN家族INNER、LEFT、RIGHT的區(qū)別與場(chǎng)景JOIN是關(guān)聯(lián)查詢最核心的寫法。最常見的三種是INNER JOIN、LEFT JOIN、RIGHT JOIN。網(wǎng)上有很多圖解釋它們的區(qū)別這里我用實(shí)際業(yè)務(wù)來舉例說明。假設(shè)有兩張表用戶表users和訂單表orders。想知道“哪些用戶下過單”用INNER JOIN只返回兩邊都匹配的行SELECT users.id, users.name, orders.order_no FROM users INNER JOIN orders ON users.id orders.user_id;想知道“每個(gè)用戶和他的所有訂單沒下過單的用戶也要顯示出來”用LEFT JOIN左表數(shù)據(jù)全部保留右表無匹配就以NULL填充SELECT users.id, users.name, orders.order_no FROM users LEFT JOIN orders ON users.id orders.user_id;這個(gè)場(chǎng)景最常見的就是列表頁需要展示主表數(shù)據(jù)即使副表沒數(shù)據(jù)也要顯示一行。RIGHT JOIN的用法和LEFT JOIN對(duì)稱以右表為主但實(shí)際開發(fā)中用得很少。我個(gè)人的建議是統(tǒng)一用LEFT JOIN就好不要混用RIGHT JOIN否則SQL的可讀性會(huì)變差團(tuán)隊(duì)同事看代碼也不舒服。關(guān)聯(lián)條件上有一個(gè)極其重要的坑LEFT JOIN中如果在WHERE里對(duì)右表字段加過濾條件會(huì)導(dǎo)致“左表保留全部行”的語義被破壞。舉例SELECT users.id, users.name, orders.order_no FROM users LEFT JOIN orders ON users.id orders.user_id WHERE orders.status 1;這條SQL的意圖可能是“查所有用戶中已支付訂單的關(guān)聯(lián)情況”但實(shí)際執(zhí)行時(shí)因?yàn)閃HERE在JOIN之后過濾已經(jīng)把左表中沒有匹配訂單的用戶行全部剔除了LEFT JOIN變成了INNER JOIN的效果。想要保持左表全部保留應(yīng)該把過濾條件寫在ON子句里SELECT users.id, users.name, orders.order_no FROM users LEFT JOIN orders ON users.id orders.user_id AND orders.status 1;這個(gè)細(xì)節(jié)我已經(jīng)在真實(shí)項(xiàng)目中碰到不止一次每次都是面試題級(jí)別的經(jīng)典陷阱而且業(yè)務(wù)上一旦踩中定位問題往往要花不少時(shí)間。4.2 子查詢與JOIN的取舍子查詢是嵌套在主查詢里的SELECT語句可以分為FROM子句里的子查詢、 WHERE子句里的子查詢、SELECT子句里的標(biāo)量子查詢幾種。比如要查“每個(gè)用戶最近一筆訂單”可以用關(guān)聯(lián)子查詢的方式SELECT o.* FROM orders o WHERE o.id ( SELECT MAX(id) FROM orders o2 WHERE o2.user_id o.user_id );這個(gè)寫法理解起來比較簡(jiǎn)單但它對(duì)每條訂單都會(huì)執(zhí)行一次子查詢?nèi)绻麛?shù)據(jù)量大性能會(huì)很差。還有一類常見的子查詢是用IN比如SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount 100);這種寫法的隱患是如果子查詢返回的結(jié)果集特別大IN列表會(huì)非常長(zhǎng)效率不高。而且某些MySQL版本對(duì)IN子查詢的優(yōu)化做得不夠好執(zhí)行計(jì)劃可能會(huì)退化成全表掃描。更穩(wěn)健的做法是用EXISTS改寫或者改成JOINSELECT DISTINCT users.* FROM users INNER JOIN orders ON users.id orders.user_id AND orders.amount 100;用JOIN的語義更直白執(zhí)行計(jì)劃可控性也更高。當(dāng)然JOIN之后如果主表數(shù)據(jù)有重復(fù)關(guān)聯(lián)需要加上DISTINCT去重這時(shí)要自己確認(rèn)是否會(huì)產(chǎn)生重復(fù)行。子查詢和JOIN的選擇沒有絕對(duì)標(biāo)準(zhǔn)我的經(jīng)驗(yàn)是小數(shù)據(jù)量、語義直觀的子查詢可以用大數(shù)據(jù)量、性能敏感的查詢優(yōu)先考慮JOIN和EXISTS然后通過EXPLAIN確認(rèn)執(zhí)行計(jì)劃。4.3 UNION與UNION ALL別讓去重拖慢查詢UNION和UNION ALL都是將多個(gè)SELECT的結(jié)果集合并輸出。區(qū)別在于UNION會(huì)去重UNION ALL不去重。這一字之差性能差距可能非常明顯因?yàn)槿ブ匾馕吨枰獙?duì)整個(gè)結(jié)果集排序或走哈希去重額外開銷極大。業(yè)務(wù)上如果明確兩個(gè)結(jié)果集不可能有重復(fù)或者重復(fù)無害直接用UNION ALL就沒問題了。舉一個(gè)實(shí)際場(chǎng)景要查“訂單金額大于1000的訂單”和“最近7天創(chuàng)建的訂單”的合并列表。兩個(gè)條件有交叉的可能但最終展示訂單列表時(shí)重復(fù)也沒關(guān)系所以SELECT order_no, created_at FROM orders WHERE amount 1000 UNION ALL SELECT order_no, created_at FROM orders WHERE created_at NOW() - INTERVAL 7 DAY;這樣比UNION快不少而且邏輯更符合業(yè)務(wù)預(yù)期。另外還有一個(gè)容易被忽略的事項(xiàng)UNION的子句之間各SELECT返回的列數(shù)和數(shù)據(jù)類型必須一致否則MySQL會(huì)報(bào)錯(cuò)。比如第一個(gè)SELECT返回了兩列第二個(gè)SELECT返回了三列直接編譯失敗。如果各表中列名不一致第一個(gè)SELECT的列名會(huì)作為結(jié)果集的列名。5. 提升查詢性能的關(guān)鍵索引與執(zhí)行計(jì)劃前面講了很多查詢寫法但真正跑線上查詢的時(shí)候性能怎么樣不能靠猜得靠數(shù)據(jù)說話。這一部分重點(diǎn)說索引的坑和EXPLAIN執(zhí)行計(jì)劃的用法。5.1 索引失效的幾種常見姿勢(shì)索引失效是慢查詢的頭號(hào)原因。我總結(jié)過幾條高頻踩坑場(chǎng)景基本覆蓋了日常開發(fā)里90%的問題。第一對(duì)索引列使用函數(shù)或表達(dá)式計(jì)算。前面已經(jīng)提過WHERE DATE(created_at) 2024-01-01會(huì)導(dǎo)致索引失效。同樣地WHERE amount 1 100這種寫法也會(huì)讓amount字段的索引失效。第二隱式類型轉(zhuǎn)換。varchar字段和數(shù)字比較、int字段和字符串比較都會(huì)讓MySQL放棄索引。第三LIKE模糊匹配以%開頭。比如WHERE name LIKE %張%因?yàn)闊o法確定前綴B樹無法定位只能全表掃。反過來WHERE name LIKE 張%可以走索引。第四聯(lián)合索引不使用最左前綴。建了(a, b, c)聯(lián)合索引條件里直接查b1跳過a索引用不上。這是最左前綴原則決定的。在設(shè)計(jì)聯(lián)合索引時(shí)一定要把最高頻的等值查詢字段放最左邊。第五OR條件導(dǎo)致索引失效。比如WHERE a 1 OR b 2如果只有a字段有索引MySQL可能選擇全表掃描。優(yōu)化方式是把OR改成UNION或者給b也加上索引。第六對(duì)NULL值的判斷。WHERE name IS NULL不一定導(dǎo)致索引失效但WHERE name IS NOT NULL在很多場(chǎng)景下優(yōu)化器會(huì)認(rèn)為返回?cái)?shù)據(jù)量太大直接放棄索引。這里沒有絕對(duì)結(jié)論實(shí)際操作中以EXPLAIN結(jié)果為準(zhǔn)。第七ORDER BY和GROUP BY的字段順序與索引順序不一致。比如聯(lián)合索引(a, b)但SQL里ORDER BY b, a索引順序不匹配排序走文件排序性能下降。5.2 EXPLAIN怎么看重點(diǎn)字段速查EXPLAIN是排查慢查詢的核心工具。用法很簡(jiǎn)單在SELECT前面加個(gè)EXPLAIN關(guān)鍵字就行EXPLAIN SELECT * FROM orders WHERE user_id 10001 AND created_at 2024-01-01;返回結(jié)果里重點(diǎn)看這幾個(gè)字段type訪問類型。從好到差依次是const、eq_ref、ref、range、index、ALL。出現(xiàn)ALL表示全表掃描要高度警惕。range說明掃描了一個(gè)索引范圍比全表掃描好得多。key實(shí)際用到的索引名。如果為NULL說明沒走索引。rows預(yù)估掃描的行數(shù)。這個(gè)數(shù)越大查詢通常越慢。Extra額外信息。出現(xiàn)Using filesort或Using temporary說明排序或分組用了臨時(shí)文件性能往往不佳出現(xiàn)Using index則說明是覆蓋索引性能很好。舉個(gè)實(shí)際排查案例某次線上接口變慢EXPLAIN結(jié)果顯示一條查詢的type為ALLrows達(dá)到500多萬。打開SQL一看WHERE條件里對(duì)create_time用了DATE()函數(shù)。改成范圍條件后type變成rangerows降到幾千接口響應(yīng)時(shí)間從接近2秒降到幾十毫秒。這就是EXPLAIN的價(jià)值——用數(shù)據(jù)定位問題而不是拍腦袋猜。5.3 一個(gè)真實(shí)慢查詢優(yōu)化案例之前處理過某業(yè)務(wù)系統(tǒng)的一個(gè)慢查詢是訂單列表接口。SQL大概長(zhǎng)這樣SELECT id, order_no, user_id, amount, status, created_at FROM orders WHERE user_id 12345 ORDER BY created_at DESC LIMIT 10;表里訂單量接近千萬user_id和created_at各自都是獨(dú)立索引但查詢依然慢。EXPLAIN之后發(fā)現(xiàn)優(yōu)化器在兩個(gè)索引之間選擇了一個(gè)但Extra里出現(xiàn)了Using filesort也就是排序沒有完全走索引。原因很簡(jiǎn)單優(yōu)化器走的是idx_user_id先定位到該用戶的所有訂單再對(duì)這些訂單按created_at排序。因?yàn)閏reated_at不滿足索引順序排序操作只能在內(nèi)存或磁盤上做數(shù)據(jù)量一大就慢。解決方案就是前面提到的聯(lián)合索引ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at);這個(gè)聯(lián)合索引既能通過user_id快速定位又能在索引內(nèi)部天然按created_at排好序完美匹配ORDER BY的需求。加上索引之后EXPLAIN顯示Using filesort消失查詢時(shí)間從幾百毫秒降到個(gè)位數(shù)毫秒。這個(gè)案例值得記住的不是SQL寫法本身而是索引設(shè)計(jì)要兼顧WHERE和ORDER BY。很多人在聯(lián)合索引上只考慮過濾字段忽略了排序字段結(jié)果WHERE很快但ORDER BY把整個(gè)查詢拖慢。6. 常見問題與排查技巧實(shí)錄最后把這些年在MySQL表操作和查詢這塊遇到的高頻問題統(tǒng)一整理成一張速查表方便大家在實(shí)際開發(fā)中對(duì)號(hào)入座。癥狀可能原因排查思路解決方向中文亂碼字符集不一致或連接層字符集不對(duì)檢查庫、表、字段、連接的字符集統(tǒng)一為utf8mb4重建連接并確認(rèn)character_set_server查詢?cè)絹碓铰彼饕⑺饕Щ驋呙栊袛?shù)過多執(zhí)行EXPLAIN看type、rows、key補(bǔ)聯(lián)合索引、改寫條件、避免函數(shù)包裹加了索引還是慢索引列被函數(shù)包裹、隱式類型轉(zhuǎn)換或OR條件檢查WHERE里索引列是否“裸狀態(tài)”去掉函數(shù)/轉(zhuǎn)換OR改為UNION或補(bǔ)索引排序慢索引順序與ORDER BY不匹配看Extra是否有Using filesort設(shè)計(jì)聯(lián)合索引時(shí)把排序字段放進(jìn)去LIMIT翻頁到后面很慢深分頁在大偏移量下掃描大量無用行分析偏移量大小、掃描行數(shù)用ID定位、延遲關(guān)聯(lián)或減少偏移量DELETE后表空間不減少InnoDB的已刪除行空間未立即釋放查看表大小、碎片情況定期OPTIMIZE TABLE或接受空間復(fù)用機(jī)制同一查詢不同環(huán)境結(jié)果不同字符集、排序規(guī)則或數(shù)據(jù)分布差異對(duì)比兩邊的表結(jié)構(gòu)定義和數(shù)據(jù)分布用小數(shù)據(jù)量復(fù)現(xiàn)并對(duì)比執(zhí)行計(jì)劃GROUP BY報(bào)錯(cuò)沒開啟嚴(yán)格模式或SELECT字段與GROUP BY字段不匹配查看sql_mode配置和SQL寫法把非分組字段改為聚合函數(shù)或加入GROUP BY關(guān)聯(lián)查詢數(shù)據(jù)量暴增JOIN條件產(chǎn)生笛卡爾積檢查ON條件是否缺失或條件過寬確認(rèn)關(guān)聯(lián)字段有索引條件收窄查詢結(jié)果包含重復(fù)數(shù)據(jù)JOIN一對(duì)一關(guān)系變一對(duì)多檢查關(guān)聯(lián)表的唯一性、關(guān)聯(lián)鍵是否有重復(fù)加DISTINCT或調(diào)整關(guān)聯(lián)SQL排查問題的時(shí)候有一個(gè)實(shí)用心法不要一上來就改SQL。先花兩分鐘看表結(jié)構(gòu)再執(zhí)行EXPLAIN看執(zhí)行計(jì)劃最后才動(dòng)手改。大多數(shù)慢查詢問題在執(zhí)行計(jì)劃階段就能露出馬腳改SQL只是收尾動(dòng)作。還有一個(gè)經(jīng)驗(yàn)之談排查過程中把當(dāng)時(shí)的表結(jié)構(gòu)、數(shù)據(jù)量、SQL語句、執(zhí)行計(jì)劃這四個(gè)信息記錄下來問題解決之后再回頭復(fù)盤一段時(shí)間下來自己解決問題的能力會(huì)提升一大截。表操作和查詢是MySQL最基礎(chǔ)的能力但也是最值得反復(fù)打磨的能力。工作中見到過太多“上線前沒問題跑了一段時(shí)間之后突然慢得出奇”的案例究其原因多數(shù)不是SQL本身的問題而是表結(jié)構(gòu)設(shè)計(jì)和查詢方式對(duì)數(shù)據(jù)增長(zhǎng)的適應(yīng)性不足。比如索引設(shè)計(jì)沒考慮到數(shù)據(jù)規(guī)模的增長(zhǎng)、表結(jié)構(gòu)變更沒有預(yù)留彈性、深分頁沒有提前做容量規(guī)劃。所以我的經(jīng)驗(yàn)是寫建表DDL和查詢SQL的時(shí)候多問自己一句這個(gè)表和這條SQL在數(shù)據(jù)量再漲十倍、漲百倍之后還能不能撐住帶著這個(gè)意識(shí)去寫每一行SQL能避掉絕大部分性能坑。最后再分享一個(gè)小技巧每張業(yè)務(wù)表都留一個(gè)自增主鍵和創(chuàng)建時(shí)間字段這兩個(gè)字段雖然平淡卻是后續(xù)排查問題時(shí)最可靠的抓手。