據(jù)庫(kù)入門核心：從表結(jié)構(gòu)、索引到事務(wù)的實(shí)戰(zhàn)解析)
我在帶新人入門的時(shí)候最常聽到的一句話是“數(shù)據(jù)庫(kù)到底是個(gè)啥感覺(jué)就是個(gè)存數(shù)據(jù)的軟件唄。”這話沒(méi)毛病但只對(duì)了一半。如果只是存數(shù)據(jù)Excel也能干為啥要搞出個(gè)專門的“王國(guó)”出來(lái)因?yàn)閿?shù)據(jù)庫(kù)解決的從來(lái)不是“怎么存”的問(wèn)題而是“怎么存得穩(wěn)、查得快、改得準(zhǔn)、掛了還能恢復(fù)”的問(wèn)題。今天這篇我就用“數(shù)據(jù)王國(guó)”這個(gè)比喻把數(shù)據(jù)庫(kù)底層的運(yùn)作邏輯掰開揉碎講一遍再配5道我篩選過(guò)的經(jīng)典題幫你把腦子里那些零散概念串成一條線。這篇文章不挑基礎(chǔ)哪怕你完全沒(méi)寫過(guò)SQL也能看懂如果你已經(jīng)會(huì)用增刪改查了重點(diǎn)看后半部分的題目解析和避坑心得那里面有不少是我在實(shí)際項(xiàng)目里踩出來(lái)的經(jīng)驗(yàn)。數(shù)據(jù)庫(kù)這個(gè)東西難起來(lái)可以研究一輩子但入門的時(shí)候只要把“表、關(guān)系、約束、索引、事務(wù)”這幾個(gè)核心概念吃透你就已經(jīng)甩開絕大多數(shù)“只會(huì)背命令”的人了。1. 由表入道數(shù)據(jù)王國(guó)的地基是“二維表”不是“數(shù)據(jù)庫(kù)軟件”1.1 為什么是“表”而不是“文件柜”先想一個(gè)問(wèn)題你手里有一堆客戶信息、訂單信息、商品信息最原始的做法是拿個(gè)本子記或者塞進(jìn)Excel。Excel和數(shù)據(jù)庫(kù)的表看起來(lái)都是“行和列”但本質(zhì)上有個(gè)天大的區(qū)別——Excel是給“人”看的數(shù)據(jù)庫(kù)的表是給“機(jī)器”和“規(guī)則”看的。機(jī)器看數(shù)據(jù)講究的是“嚴(yán)格”。每一列存什么類型長(zhǎng)度多少能不能為空有沒(méi)有默認(rèn)值這些都在建表的時(shí)候定死了。你在Excel里可以隨手在一個(gè)數(shù)字列里敲個(gè)“abc”數(shù)據(jù)庫(kù)里這么干直接被拒絕執(zhí)行。這不是死板這叫“約束”。約束保證了數(shù)據(jù)的質(zhì)量數(shù)據(jù)質(zhì)量是整個(gè)數(shù)據(jù)王國(guó)的命根子。在數(shù)據(jù)庫(kù)的世界里一張表的結(jié)構(gòu)被稱作“關(guān)系模式”表本身就是“關(guān)系”。你把一張表想象成一個(gè)王國(guó)的戶籍本每個(gè)公民行都有唯一的身份證號(hào)主鍵每項(xiàng)登記內(nèi)容列都有明確規(guī)范。沒(méi)有規(guī)矩不成方圓沒(méi)有主鍵的表就是黑戶遍地查數(shù)據(jù)、改數(shù)據(jù)、刪數(shù)據(jù)都容易亂套。1.2 主鍵數(shù)據(jù)王國(guó)的身份證主鍵Primary Key這塊我得多說(shuō)兩句。它是每張表里最核心的字段作用就是唯一標(biāo)識(shí)一行記錄。選主鍵有兩條鐵律第一不能為空第二不能重復(fù)。你可能會(huì)想“那我用身份證號(hào)當(dāng)主鍵不就行了嗎”行但如果你的業(yè)務(wù)里沒(méi)有天然唯一字段呢比如一張訂單表沒(méi)有訂單號(hào)只能靠“用戶ID下單時(shí)間”來(lái)拼。這種情況我建議你用自增ID或者一串隨機(jī)生成的唯一編號(hào)。我在實(shí)際項(xiàng)目里見(jiàn)過(guò)有人用“姓名手機(jī)號(hào)”做主鍵結(jié)果兩個(gè)同事的手機(jī)號(hào)錄錯(cuò)了撞了主鍵整個(gè)表寫不進(jìn)去數(shù)據(jù)排查半天才發(fā)現(xiàn)是臟數(shù)據(jù)問(wèn)題。記住主鍵最好用無(wú)業(yè)務(wù)含義的純ID業(yè)務(wù)字段隨時(shí)可能變變了主鍵就崩了。1.3 字段類型的坑別把什么都存成字符串建表的時(shí)候字段類型的選型是新手最愛(ài)忽略的。我見(jiàn)過(guò)最典型的操作是把手機(jī)號(hào)存成INT結(jié)果號(hào)碼顯示成“138****”后面被抹位其實(shí)不是顯示問(wèn)題是數(shù)值溢出后被自動(dòng)轉(zhuǎn)了。還有把日期存成字符串然后查“最近7天下單用戶”的時(shí)候用字符串比較大小格式一不統(tǒng)一就漏數(shù)據(jù)。常用的字段類型幾句話講明白INT/BIGINT整數(shù)類型BIGINT比INT范圍大得多自增主鍵建議直接BIGINT別省。DECIMAL(M,D)定點(diǎn)小數(shù)存錢用這個(gè)絕對(duì)不要用FLOAT/DOUBLE浮點(diǎn)數(shù)有精度丟失賬算不平。VARCHAR(N)變長(zhǎng)字符串存姓名、地址這種長(zhǎng)度不固定的內(nèi)容。CHAR(N)定長(zhǎng)字符串存身份證號(hào)、手機(jī)號(hào)這種固定長(zhǎng)度的性能比VARCHAR好一點(diǎn)。DATETIME/TIMESTAMP存時(shí)間TIMESTAMP有2038年問(wèn)題能用DATETIME盡量用DATETIME。TEXT/BLOB存大文本和二進(jìn)制能用文件路徑代替就別直接往庫(kù)里塞大文件庫(kù)會(huì)變得又肥又慢。這里補(bǔ)一個(gè)很多新手不知道的細(xì)節(jié)VARCHAR括號(hào)里的N是“字符數(shù)”而不是“字節(jié)數(shù)”。如果庫(kù)的字符集是UTF-8一個(gè)漢字占3個(gè)字節(jié)VARCHAR(10)能存下10個(gè)漢字但如果你用VARCHAR(10)去存一個(gè)包含數(shù)字字母混合的字符串長(zhǎng)度上限是10個(gè)字符不是10個(gè)字節(jié)。很多報(bào)錯(cuò)“Data too long for column”就是這么來(lái)的。2. 數(shù)據(jù)王國(guó)的通用語(yǔ)言SQL的四大基石操作2.1 建庫(kù)建表先規(guī)劃后動(dòng)手學(xué)習(xí)階段沒(méi)關(guān)系隨便建。工作里建表之前一定要先規(guī)劃清楚這張表要存什么誰(shuí)要讀誰(shuí)要寫大概多大的數(shù)據(jù)量這些想清楚了再寫CREATE TABLE否則后面改表結(jié)構(gòu)極度痛苦尤其是表里有數(shù)據(jù)了之后ALTER TABLE在大表上可能直接鎖表業(yè)務(wù)就停了。我建議每個(gè)入門的人練習(xí)的時(shí)候建一個(gè)“學(xué)生管理系統(tǒng)”的庫(kù)三個(gè)表學(xué)生表、課程表、選課表。這個(gè)場(chǎng)景足夠經(jīng)典把三個(gè)表建出來(lái)你就理解了什么叫“實(shí)體”和“關(guān)系”。一份基本的學(xué)生表創(chuàng)建語(yǔ)句CREATE TABLE student ( id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 學(xué)生ID, name VARCHAR(50) NOT NULL COMMENT 學(xué)生姓名, gender CHAR(1) DEFAULT 男 COMMENT 性別, birth_date DATE COMMENT 出生日期, phone VARCHAR(20) UNIQUE COMMENT 手機(jī)號(hào), enrollment_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 入學(xué)時(shí)間 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT學(xué)生信息表;這里有幾個(gè)點(diǎn)值得研究一下AUTO_INCREMENT是自增不需要你手動(dòng)傳IDNOT NULL和DEFAULT一起用既保證字段必須存在又允許自動(dòng)填默認(rèn)值UNIQUE約束保證手機(jī)號(hào)不重復(fù)ENGINEInnoDB是當(dāng)前最主流的存儲(chǔ)引擎后面講事務(wù)的時(shí)候會(huì)專門提。2.2 增刪改查四個(gè)動(dòng)作覆蓋九成需求增刪改查對(duì)應(yīng)SQL里的INSERT、DELETE、UPDATE、SELECT。這四個(gè)單詞背下來(lái)你已經(jīng)會(huì)一半SQL了。查詢SELECT是所有操作里最常用的也是把新手和老手區(qū)分開的分水嶺。最簡(jiǎn)單的查詢就是SELECT * FROM student但面試和實(shí)際項(xiàng)目里沒(méi)人會(huì)滿意你每次都全表掃描。你要學(xué)會(huì)WHERE條件過(guò)濾、ORDER BY排序、LIMIT分頁(yè)、GROUP BY分組、聚合函數(shù)COUNT/SUM/AVG/MAX/MIN、JOIN多表連接。這些知識(shí)點(diǎn)我不在這里展開第4節(jié)的5道經(jīng)典題里會(huì)逐個(gè)過(guò)一遍。寫入INSERT要注意的一點(diǎn)是批量插入。一次插一條在數(shù)據(jù)量小的時(shí)候無(wú)所謂但如果你要導(dǎo)幾萬(wàn)條數(shù)據(jù)一條條insert能把人的心態(tài)搞崩。直接上批量語(yǔ)法INSERT INTO student (name, gender, birth_date, phone) VALUES (張三, 男, 2000-01-01, 13800000001), (李四, 女, 2001-02-02, 13800000002), (王五, 男, 2002-03-03, 13800000003);改成UPDATE和刪除DELETE是風(fēng)險(xiǎn)最大的操作。我就犯過(guò)一個(gè)經(jīng)典錯(cuò)誤給一張用戶表更新狀態(tài)忘了加WHERE條件一條UPDATE下去全表的用戶狀態(tài)全變成了同一個(gè)值。那幾秒鐘腦子是空白的心跳是加速的。從那以后我的習(xí)慣是先寫SELECT把要改的數(shù)據(jù)查出來(lái)看一遍確認(rèn)WHERE條件沒(méi)寫錯(cuò)再改成UPDATE執(zhí)行。這個(gè)習(xí)慣也推薦給你。2.3 WHERE條件的執(zhí)行邏輯別被NULL坑了WHERE這一節(jié)我要重點(diǎn)講NULL。很多新手在看“等值匹配”的時(shí)候會(huì)默認(rèn)“空值也能等于空值”但SQL里NULL是“未知”不是“空字符串”。拿WHERE phone NULL去查永遠(yuǎn)查不到任何東西正確寫法是WHERE phone IS NULL。這里有個(gè)坑中坑在JOIN連接的時(shí)候如果連接字段里有NULL這個(gè)連接會(huì)直接失敗記錄會(huì)被悄悄丟棄。我在處理用戶表和訂單表關(guān)聯(lián)時(shí)踩過(guò)這個(gè)結(jié)果訂單統(tǒng)計(jì)數(shù)一直對(duì)不上查了一上午才發(fā)現(xiàn)是有幾個(gè)訂單的user_id存了NULL。生成環(huán)境里遇到這種“數(shù)據(jù)莫名變少”的問(wèn)題第一個(gè)懷疑方向就是連接字段里有NULL。3. 數(shù)據(jù)王國(guó)的管理法則約束、關(guān)系與范式3.1 外鍵表與表之間的合同建表時(shí)我們把學(xué)生、課程、選課分成三張表那這三張表怎么產(chǎn)生聯(lián)系靠外鍵FOREIGN KEY。外鍵的意思就是某一列的值必須來(lái)自另一張表的某個(gè)字段值或者必須是NULL。選課表的設(shè)計(jì)CREATE TABLE course_selection ( id BIGINT AUTO_INCREMENT PRIMARY KEY, student_id BIGINT NOT NULL, course_id BIGINT NOT NULL, score DECIMAL(5,2), CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES student(id), CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES course(id) );有了外鍵約束你往選課表里插一個(gè)不存在的student_id數(shù)據(jù)庫(kù)直接報(bào)錯(cuò)根本插不進(jìn)去。從數(shù)據(jù)王國(guó)治理的角度講這就是“法律條文”——誰(shuí)都不能往選課記錄里塞一個(gè)不在籍的學(xué)生。但外鍵在互聯(lián)網(wǎng)業(yè)務(wù)高并發(fā)場(chǎng)景下是“奢侈品”。因?yàn)橥怄I會(huì)影響寫入性能每次插入都要去校驗(yàn)關(guān)聯(lián)表在大流量寫入場(chǎng)景下很多公司會(huì)放棄數(shù)據(jù)庫(kù)外鍵改在應(yīng)用層做校驗(yàn)。這是理論知識(shí)和實(shí)際工程的矛盾點(diǎn)之一面試的時(shí)候如果被問(wèn)到“你平時(shí)寫外鍵嗎”答“看場(chǎng)景低并發(fā)實(shí)時(shí)性要求高的場(chǎng)景用物理外鍵高并發(fā)大流量場(chǎng)景用邏輯外鍵”就非常加分。3.2 三大范式數(shù)據(jù)冗余的克星范式Normal Form是數(shù)據(jù)庫(kù)設(shè)計(jì)的規(guī)范一共六種級(jí)別實(shí)際工作中掌握到第三范式就夠用了。第一范式1NF每列都不可再分。比如你把興趣寫成“籃球,足球,羽毛球”存在一個(gè)字段里這就違反了1NF應(yīng)該拆成三行或者單拆一張興趣表。第二范式2NF在1NF基礎(chǔ)上每一列都完全依賴主鍵而不是依賴主鍵的一部分。舉個(gè)例子一個(gè)選課表的主鍵如果是(student_id, course_id)那成績(jī)字段score依賴整個(gè)主鍵沒(méi)問(wèn)題但如果你把“課程老師”也塞進(jìn)選課表它就只依賴course_id不依賴student_id這就是部分依賴違反2NF。結(jié)果就是同一個(gè)課程老師的名字會(huì)在幾十行里重復(fù)改一次老師名要改幾十行。第三范式3NF在2NF基礎(chǔ)上非主鍵字段不能依賴其他非主鍵字段。比如訂單表里有“用戶ID”和“用戶手機(jī)號(hào)”手機(jī)號(hào)由用戶ID決定不是直接依賴訂單號(hào)這就是傳遞依賴。應(yīng)該把手機(jī)號(hào)留在用戶表訂單表只存用戶ID。范式的本質(zhì)是消除冗余但現(xiàn)實(shí)中見(jiàn)過(guò)很多項(xiàng)目為了性能刻意反范式比如同步一份冗余字段到查詢頻繁的表里避免JOIN。這算工程權(quán)衡不犯法前提是你清楚自己付出了維護(hù)一致性的代價(jià)。3.3 綜合案例設(shè)計(jì)一個(gè)簡(jiǎn)單的博客系統(tǒng)把前面的知識(shí)點(diǎn)揉在一起我?guī)阍O(shè)計(jì)一個(gè)迷你博客系統(tǒng)。這個(gè)場(chǎng)景我在講解時(shí)經(jīng)常用因?yàn)樗N近生活邏輯也不復(fù)雜。需要四張表用戶表user、文章表article、分類表category、評(píng)論表comment。用戶表id、用戶名、密碼密文、昵稱、注冊(cè)時(shí)間。 文章表id、標(biāo)題、正文、作者ID外鍵指向用戶表的id、分類ID外鍵指向分類表的id、發(fā)布時(shí)間、狀態(tài)。 分類表id、名稱、排序值。 評(píng)論表id、內(nèi)容、文章ID外鍵指向文章表id、用戶ID外鍵指向用戶表id、評(píng)論時(shí)間。這個(gè)設(shè)計(jì)里文章表不直接存作者名字只存作者ID想知道作者名就通過(guò)ID去用戶表查。為什么因?yàn)樽髡呙秩绻牧宋恼卤砝锏臄?shù)據(jù)不用動(dòng)始終查出來(lái)是最新的。如果你寫死“作者名”字段作者改名以后歷史文章全部要跟著改。這個(gè)理念是數(shù)據(jù)庫(kù)外鍵關(guān)系最核心的價(jià)值單一事實(shí)來(lái)源。4. 進(jìn)階修煉索引與事務(wù)數(shù)據(jù)王國(guó)的兩大護(hù)法4.1 索引讓數(shù)據(jù)王國(guó)不再“翻箱倒柜”沒(méi)有索引的時(shí)候數(shù)據(jù)庫(kù)查一張100萬(wàn)行的表要一行一行從頭掃到尾這叫全表掃描耗時(shí)可能幾百毫秒甚至秒級(jí)。加了索引之后數(shù)據(jù)庫(kù)相當(dāng)于給字段建了一棵查找樹查一次只需要走樹的高度10幾層就找到了耗時(shí)瞬間到毫秒級(jí)。索引的選型思路用不用背B樹原理面試會(huì)問(wèn)但實(shí)際操作里你只需要知道索引是排好序的數(shù)據(jù)結(jié)構(gòu)它用額外的存儲(chǔ)空間換查詢速度。對(duì)哪個(gè)字段建索引取決于你的WHERE條件、ORDER BY、JOIN字段。我建議新手的建索引標(biāo)準(zhǔn)主鍵默認(rèn)就是唯一索引別重復(fù)建。WHERE常用的條件字段優(yōu)先建索引。JOIN連接的字段建議建索引否則多表關(guān)聯(lián)會(huì)變成災(zāi)難。區(qū)分度低的字段不要建索引比如“性別”只有男女兩個(gè)值索引根本沒(méi)有過(guò)濾效果還耗空間。不要在索引列上做函數(shù)運(yùn)算比如WHERE DATE(birth_date) 2001-01-01這會(huì)讓索引失效。說(shuō)到索引失效這是新手排查慢查詢的第一大頭疼點(diǎn)。常見(jiàn)的三個(gè)坑隱式類型轉(zhuǎn)換字符串列不引號(hào)或數(shù)字列存成字符串去比較、前模糊查詢LIKE %abc導(dǎo)致索引失效、聯(lián)合索引不按最左前綴原則匹配。這三個(gè)記住了你排查SQL慢查詢的思路就有了。4.2 事務(wù)要么全成要么全敗你把兩個(gè)賬戶之間轉(zhuǎn)賬這個(gè)業(yè)務(wù)想一下從A賬戶扣100塊往B賬戶加100塊。如果扣完A的錢數(shù)據(jù)庫(kù)突然斷電B的錢沒(méi)加上這事就沒(méi)法交代。事務(wù)Transaction就是干這個(gè)用的它把一串操作打包成一個(gè)整體要么全部成功要么全部回滾。事務(wù)的ACID特性是面試必考但實(shí)際理解起來(lái)并不難原子性Atomicity一個(gè)事務(wù)里的操作要么全部完成要么全部不做。一致性Consistency事務(wù)完成后數(shù)據(jù)庫(kù)從一個(gè)合法狀態(tài)進(jìn)入另一個(gè)合法狀態(tài)。比如轉(zhuǎn)賬后AB的總金額不變。隔離性Isolation多個(gè)事務(wù)同時(shí)操作同一批數(shù)據(jù)時(shí)互不干擾。持久性Durability事務(wù)提交以后即使數(shù)據(jù)庫(kù)重啟了數(shù)據(jù)也還在。InnoDB引擎支持事務(wù)MyISAM引擎不支持這也是我之前建議ENGINEInnoDB的原因。事務(wù)的隔離級(jí)別有四種讀未提交、讀已提交、可重復(fù)讀、串行化。MySQL默認(rèn)是“可重復(fù)讀”。隔離級(jí)別低并發(fā)性能好但可能出現(xiàn)臟讀、不可重復(fù)讀、幻讀的問(wèn)題。這部分內(nèi)容初學(xué)者了解概念即可真正糾結(jié)隔離級(jí)別是DBA的活。4.3 實(shí)操示例用事務(wù)保護(hù)一筆轉(zhuǎn)賬操作舉個(gè)例子我們用事務(wù)把轉(zhuǎn)賬的完整過(guò)程寫出來(lái)START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; -- 檢查更新是否成功如果余額不足ROLLBACK UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;在你寫完第一個(gè)UPDATE之后如果發(fā)現(xiàn)user_id1的賬戶余額不夠扣你可以立刻ROLLBACK回滾全程不會(huì)產(chǎn)生任何臟數(shù)據(jù)。這在實(shí)際業(yè)務(wù)代碼里往往是通過(guò)程序先查詢余額、再判斷是否足夠、再?zèng)Q定提交還是回滾來(lái)完成的。5. 5道經(jīng)典題從表結(jié)構(gòu)到查詢優(yōu)化的進(jìn)階之路5.1 第一題找出每個(gè)學(xué)生的最高分課程假設(shè)有三張表student(id, name)、course(id, name)、score(student_id, course_id, score)。問(wèn)題是“每個(gè)學(xué)生的最高分課程”。這個(gè)題的核心是子查詢和關(guān)聯(lián)。先找到每個(gè)學(xué)生的最高分再把這個(gè)結(jié)果和原表關(guān)聯(lián)起來(lái)取課程信息。我教你一個(gè)實(shí)用的思路拿一張臨時(shí)結(jié)果去JOIN原始表。臨時(shí)結(jié)果結(jié)構(gòu)是(student_id, max_score)和score表按這兩個(gè)字段關(guān)聯(lián)就能得到對(duì)應(yīng)的course_id再連course表取課程名。這個(gè)思路理解了以后很多“找前幾條”“找最大一條”的題都通吃。5.2 第二題統(tǒng)計(jì)各課程的平均分只保留平均分大于60的這題考GROUP BY和HAVING的組合。新手常見(jiàn)的錯(cuò)誤是把過(guò)濾條件寫在WHERE里但WHERE的執(zhí)行時(shí)機(jī)是在分組之前分組之后的篩選必須用HAVING。這個(gè)知識(shí)點(diǎn)背下來(lái)不如踩一次坑記得牢我面試的時(shí)候問(wèn)過(guò)好幾個(gè)人能準(zhǔn)確講出HAVING用途的不超過(guò)四成。5.3 第三題查詢沒(méi)有選任何課程的學(xué)生這題有三種解法NOT EXISTS、NOT IN、LEFT JOIN IS NULL。面試官通常希望看到你至少寫出兩種。核心邏輯是先找到“有選課的學(xué)生ID集合”然后從學(xué)生表里排除。需要提醒的是如果子查詢結(jié)果集里出現(xiàn)NULLNOT IN的結(jié)果會(huì)意外為空這是個(gè)大坑。用NOT EXISTS或者LEFT JOIN更安全。5.4 第四題刪除重復(fù)數(shù)據(jù)只保留最小ID的那條這題在實(shí)際工作中經(jīng)常會(huì)遇到屬于“數(shù)據(jù)清洗”場(chǎng)景。原理是先把重復(fù)數(shù)據(jù)的最小ID查出來(lái)然后刪除“重復(fù)但不是最小ID”的記錄。寫DELETE之前一定先寫一條SELECT驗(yàn)證一下你的條件不要上來(lái)就刪。我親眼見(jiàn)過(guò)同事刪重復(fù)數(shù)據(jù)把整張表清空的場(chǎng)景恢復(fù)數(shù)據(jù)花了三個(gè)小時(shí)教訓(xùn)夠深刻。5.5 第五題訂單表的分頁(yè)查詢優(yōu)化訂單表1000萬(wàn)條數(shù)據(jù)執(zhí)行“SELECT * FROM orders ORDER BY create_time LIMIT 800000, 20”越來(lái)越慢。原因很簡(jiǎn)單隨著偏移量增大數(shù)據(jù)庫(kù)要掃描的記錄數(shù)越來(lái)越多。優(yōu)化方案有幾種用覆蓋索引簡(jiǎn)化排序記錄上一頁(yè)最后一條的create_time用WHERE條件代替LIMIT偏移或者先查ID再JOIN回原表。第三種方案效果最好但SQL最復(fù)雜。這個(gè)問(wèn)題在生產(chǎn)環(huán)境太常見(jiàn)了值得記下。6. 避坑實(shí)錄新手最常見(jiàn)的錯(cuò)誤與排查心得6.1 經(jīng)典錯(cuò)誤速查表錯(cuò)誤現(xiàn)象常見(jiàn)原因解決方案查詢結(jié)果莫名變少JOIN連接的字段有NULL檢查關(guān)聯(lián)字段的空值或用LEFT JOIN并過(guò)濾UPDATE/DELETE影響大量行WHERE條件忘記寫或?qū)戝e(cuò)先用SELECT驗(yàn)證條件再執(zhí)行索引沒(méi)生效查詢依舊慢列上用了函數(shù)或隱式類型轉(zhuǎn)換改寫SQL條件避免左側(cè)函數(shù)運(yùn)算數(shù)據(jù)寫入報(bào)“Data too long”字段長(zhǎng)度不夠字符集是UTF-8擴(kuò)大字段長(zhǎng)度或拆表處理大批量導(dǎo)入時(shí)速度極慢一條條INSERT太頻繁改成批量INSERT或使用LOAD DATA事務(wù)提交后數(shù)據(jù)卻不見(jiàn)了用了不支持事務(wù)的引擎確認(rèn)ENGINEInnoDB檢查隔離級(jí)別6.2 慢SQL排查的原點(diǎn)思維遇到慢查詢第一個(gè)反應(yīng)不是“加索引”而是“先看執(zhí)行計(jì)劃”。執(zhí)行計(jì)劃是數(shù)據(jù)庫(kù)分析你的SQL后給出的執(zhí)行方案它會(huì)告訴你走了哪張表的哪個(gè)索引、掃描了多少行。在MySQL里用EXPLAIN關(guān)鍵字在Oracle里是執(zhí)行計(jì)劃函數(shù)。很多新手分析慢查詢上來(lái)就加索引加到第五個(gè)索引還沒(méi)解決再回頭一看其實(shí)是表的數(shù)據(jù)分布有問(wèn)題或者SQL寫法有缺陷。排查步驟我整理成固定的順序先看SQL邏輯是否合理再看執(zhí)行計(jì)劃選擇了哪個(gè)索引再看是否出現(xiàn)了隱式類型轉(zhuǎn)換或函數(shù)運(yùn)算最后考慮優(yōu)化表結(jié)構(gòu)和索引。按這個(gè)順序走下來(lái)絕大部分慢查詢半小時(shí)內(nèi)能定位。6.3 備份是救命的不是走形式的最后必須講一句數(shù)據(jù)庫(kù)學(xué)習(xí)階段養(yǎng)成備份習(xí)慣。我見(jiàn)過(guò)太多“手滑清空了表”的事故不管你是新手還是老手意外永遠(yuǎn)在路上。建議至少每周做一次全量備份重要數(shù)據(jù)可以開啟定期自動(dòng)備份。在備份的生命里要順便測(cè)試一下恢復(fù)流程——很多團(tuán)隊(duì)備份做了三年真出事的時(shí)候才發(fā)現(xiàn)備份文件損壞那才是最絕望的場(chǎng)景?；謴?fù)流程不演練備份等于白做。7. 寫在最后的一點(diǎn)個(gè)人體會(huì)數(shù)據(jù)庫(kù)這門課入門不難難的是“知其然還知其所以然”。同樣的SQL不同的人寫出來(lái)性能可能差幾十倍。想真正上手給自己布置個(gè)小項(xiàng)目比如搭一個(gè)博客系統(tǒng)、做一個(gè)記賬小程序把建表、約束、查詢、事務(wù)、索引全部用上一遍。踩幾個(gè)坑再回來(lái)看理論知識(shí)你會(huì)發(fā)現(xiàn)每個(gè)概念都活了。我自己帶新人的時(shí)候常說(shuō)這么一句話“操作數(shù)據(jù)庫(kù)之前多問(wèn)自己一遍——這條SQL在真實(shí)業(yè)務(wù)里會(huì)不會(huì)給別的查詢帶來(lái)壓力這個(gè)表結(jié)構(gòu)下個(gè)月需求一變更還能不能撐住”養(yǎng)成這種習(xí)慣你就不只是會(huì)寫SQL的人而是真正懂?dāng)?shù)據(jù)王國(guó)運(yùn)作的人。 數(shù)據(jù)庫(kù)入門核心：從表結(jié)構(gòu)、索引到事務(wù)的實(shí)戰(zhàn)解析
我在帶新人入門的時(shí)候最常聽到的一句話是“數(shù)據(jù)庫(kù)到底是個(gè)啥感覺(jué)就是個(gè)存數(shù)據(jù)的軟件唄。”這話沒(méi)毛病但只對(duì)了一半。如果只是存數(shù)據(jù)Excel也能干為啥要搞出個(gè)專門的“王國(guó)”出來(lái)因?yàn)閿?shù)據(jù)庫(kù)解決的從來(lái)不是“怎么存”的問(wèn)題而是“怎么存得穩(wěn)、查得快、改得準(zhǔn)、掛了還能恢復(fù)”的問(wèn)題。今天這篇我就用“數(shù)據(jù)王國(guó)”這個(gè)比喻把數(shù)據(jù)庫(kù)底層的運(yùn)作邏輯掰開揉碎講一遍再配5道我篩選過(guò)的經(jīng)典題幫你把腦子里那些零散概念串成一條線。這篇文章不挑基礎(chǔ)哪怕你完全沒(méi)寫過(guò)SQL也能看懂如果你已經(jīng)會(huì)用增刪改查了重點(diǎn)看后半部分的題目解析和避坑心得那里面有不少是我在實(shí)際項(xiàng)目里踩出來(lái)的經(jīng)驗(yàn)。數(shù)據(jù)庫(kù)這個(gè)東西難起來(lái)可以研究一輩子但入門的時(shí)候只要把“表、關(guān)系、約束、索引、事務(wù)”這幾個(gè)核心概念吃透你就已經(jīng)甩開絕大多數(shù)“只會(huì)背命令”的人了。1. 由表入道數(shù)據(jù)王國(guó)的地基是“二維表”不是“數(shù)據(jù)庫(kù)軟件”1.1 為什么是“表”而不是“文件柜”先想一個(gè)問(wèn)題你手里有一堆客戶信息、訂單信息、商品信息最原始的做法是拿個(gè)本子記或者塞進(jìn)Excel。Excel和數(shù)據(jù)庫(kù)的表看起來(lái)都是“行和列”但本質(zhì)上有個(gè)天大的區(qū)別——Excel是給“人”看的數(shù)據(jù)庫(kù)的表是給“機(jī)器”和“規(guī)則”看的。機(jī)器看數(shù)據(jù)講究的是“嚴(yán)格”。每一列存什么類型長(zhǎng)度多少能不能為空有沒(méi)有默認(rèn)值這些都在建表的時(shí)候定死了。你在Excel里可以隨手在一個(gè)數(shù)字列里敲個(gè)“abc”數(shù)據(jù)庫(kù)里這么干直接被拒絕執(zhí)行。這不是死板這叫“約束”。約束保證了數(shù)據(jù)的質(zhì)量數(shù)據(jù)質(zhì)量是整個(gè)數(shù)據(jù)王國(guó)的命根子。在數(shù)據(jù)庫(kù)的世界里一張表的結(jié)構(gòu)被稱作“關(guān)系模式”表本身就是“關(guān)系”。你把一張表想象成一個(gè)王國(guó)的戶籍本每個(gè)公民行都有唯一的身份證號(hào)主鍵每項(xiàng)登記內(nèi)容列都有明確規(guī)范。沒(méi)有規(guī)矩不成方圓沒(méi)有主鍵的表就是黑戶遍地查數(shù)據(jù)、改數(shù)據(jù)、刪數(shù)據(jù)都容易亂套。1.2 主鍵數(shù)據(jù)王國(guó)的身份證主鍵Primary Key這塊我得多說(shuō)兩句。它是每張表里最核心的字段作用就是唯一標(biāo)識(shí)一行記錄。選主鍵有兩條鐵律第一不能為空第二不能重復(fù)。你可能會(huì)想“那我用身份證號(hào)當(dāng)主鍵不就行了嗎”行但如果你的業(yè)務(wù)里沒(méi)有天然唯一字段呢比如一張訂單表沒(méi)有訂單號(hào)只能靠“用戶ID下單時(shí)間”來(lái)拼。這種情況我建議你用自增ID或者一串隨機(jī)生成的唯一編號(hào)。我在實(shí)際項(xiàng)目里見(jiàn)過(guò)有人用“姓名手機(jī)號(hào)”做主鍵結(jié)果兩個(gè)同事的手機(jī)號(hào)錄錯(cuò)了撞了主鍵整個(gè)表寫不進(jìn)去數(shù)據(jù)排查半天才發(fā)現(xiàn)是臟數(shù)據(jù)問(wèn)題。記住主鍵最好用無(wú)業(yè)務(wù)含義的純ID業(yè)務(wù)字段隨時(shí)可能變變了主鍵就崩了。1.3 字段類型的坑別把什么都存成字符串建表的時(shí)候字段類型的選型是新手最愛(ài)忽略的。我見(jiàn)過(guò)最典型的操作是把手機(jī)號(hào)存成INT結(jié)果號(hào)碼顯示成“138****”后面被抹位其實(shí)不是顯示問(wèn)題是數(shù)值溢出后被自動(dòng)轉(zhuǎn)了。還有把日期存成字符串然后查“最近7天下單用戶”的時(shí)候用字符串比較大小格式一不統(tǒng)一就漏數(shù)據(jù)。常用的字段類型幾句話講明白INT/BIGINT整數(shù)類型BIGINT比INT范圍大得多自增主鍵建議直接BIGINT別省。DECIMAL(M,D)定點(diǎn)小數(shù)存錢用這個(gè)絕對(duì)不要用FLOAT/DOUBLE浮點(diǎn)數(shù)有精度丟失賬算不平。VARCHAR(N)變長(zhǎng)字符串存姓名、地址這種長(zhǎng)度不固定的內(nèi)容。CHAR(N)定長(zhǎng)字符串存身份證號(hào)、手機(jī)號(hào)這種固定長(zhǎng)度的性能比VARCHAR好一點(diǎn)。DATETIME/TIMESTAMP存時(shí)間TIMESTAMP有2038年問(wèn)題能用DATETIME盡量用DATETIME。TEXT/BLOB存大文本和二進(jìn)制能用文件路徑代替就別直接往庫(kù)里塞大文件庫(kù)會(huì)變得又肥又慢。這里補(bǔ)一個(gè)很多新手不知道的細(xì)節(jié)VARCHAR括號(hào)里的N是“字符數(shù)”而不是“字節(jié)數(shù)”。如果庫(kù)的字符集是UTF-8一個(gè)漢字占3個(gè)字節(jié)VARCHAR(10)能存下10個(gè)漢字但如果你用VARCHAR(10)去存一個(gè)包含數(shù)字字母混合的字符串長(zhǎng)度上限是10個(gè)字符不是10個(gè)字節(jié)。很多報(bào)錯(cuò)“Data too long for column”就是這么來(lái)的。2. 數(shù)據(jù)王國(guó)的通用語(yǔ)言SQL的四大基石操作2.1 建庫(kù)建表先規(guī)劃后動(dòng)手學(xué)習(xí)階段沒(méi)關(guān)系隨便建。工作里建表之前一定要先規(guī)劃清楚這張表要存什么誰(shuí)要讀誰(shuí)要寫大概多大的數(shù)據(jù)量這些想清楚了再寫CREATE TABLE否則后面改表結(jié)構(gòu)極度痛苦尤其是表里有數(shù)據(jù)了之后ALTER TABLE在大表上可能直接鎖表業(yè)務(wù)就停了。我建議每個(gè)入門的人練習(xí)的時(shí)候建一個(gè)“學(xué)生管理系統(tǒng)”的庫(kù)三個(gè)表學(xué)生表、課程表、選課表。這個(gè)場(chǎng)景足夠經(jīng)典把三個(gè)表建出來(lái)你就理解了什么叫“實(shí)體”和“關(guān)系”。一份基本的學(xué)生表創(chuàng)建語(yǔ)句CREATE TABLE student ( id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 學(xué)生ID, name VARCHAR(50) NOT NULL COMMENT 學(xué)生姓名, gender CHAR(1) DEFAULT 男 COMMENT 性別, birth_date DATE COMMENT 出生日期, phone VARCHAR(20) UNIQUE COMMENT 手機(jī)號(hào), enrollment_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT 入學(xué)時(shí)間 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT學(xué)生信息表;這里有幾個(gè)點(diǎn)值得研究一下AUTO_INCREMENT是自增不需要你手動(dòng)傳IDNOT NULL和DEFAULT一起用既保證字段必須存在又允許自動(dòng)填默認(rèn)值UNIQUE約束保證手機(jī)號(hào)不重復(fù)ENGINEInnoDB是當(dāng)前最主流的存儲(chǔ)引擎后面講事務(wù)的時(shí)候會(huì)專門提。2.2 增刪改查四個(gè)動(dòng)作覆蓋九成需求增刪改查對(duì)應(yīng)SQL里的INSERT、DELETE、UPDATE、SELECT。這四個(gè)單詞背下來(lái)你已經(jīng)會(huì)一半SQL了。查詢SELECT是所有操作里最常用的也是把新手和老手區(qū)分開的分水嶺。最簡(jiǎn)單的查詢就是SELECT * FROM student但面試和實(shí)際項(xiàng)目里沒(méi)人會(huì)滿意你每次都全表掃描。你要學(xué)會(huì)WHERE條件過(guò)濾、ORDER BY排序、LIMIT分頁(yè)、GROUP BY分組、聚合函數(shù)COUNT/SUM/AVG/MAX/MIN、JOIN多表連接。這些知識(shí)點(diǎn)我不在這里展開第4節(jié)的5道經(jīng)典題里會(huì)逐個(gè)過(guò)一遍。寫入INSERT要注意的一點(diǎn)是批量插入。一次插一條在數(shù)據(jù)量小的時(shí)候無(wú)所謂但如果你要導(dǎo)幾萬(wàn)條數(shù)據(jù)一條條insert能把人的心態(tài)搞崩。直接上批量語(yǔ)法INSERT INTO student (name, gender, birth_date, phone) VALUES (張三, 男, 2000-01-01, 13800000001), (李四, 女, 2001-02-02, 13800000002), (王五, 男, 2002-03-03, 13800000003);改成UPDATE和刪除DELETE是風(fēng)險(xiǎn)最大的操作。我就犯過(guò)一個(gè)經(jīng)典錯(cuò)誤給一張用戶表更新狀態(tài)忘了加WHERE條件一條UPDATE下去全表的用戶狀態(tài)全變成了同一個(gè)值。那幾秒鐘腦子是空白的心跳是加速的。從那以后我的習(xí)慣是先寫SELECT把要改的數(shù)據(jù)查出來(lái)看一遍確認(rèn)WHERE條件沒(méi)寫錯(cuò)再改成UPDATE執(zhí)行。這個(gè)習(xí)慣也推薦給你。2.3 WHERE條件的執(zhí)行邏輯別被NULL坑了WHERE這一節(jié)我要重點(diǎn)講NULL。很多新手在看“等值匹配”的時(shí)候會(huì)默認(rèn)“空值也能等于空值”但SQL里NULL是“未知”不是“空字符串”。拿WHERE phone NULL去查永遠(yuǎn)查不到任何東西正確寫法是WHERE phone IS NULL。這里有個(gè)坑中坑在JOIN連接的時(shí)候如果連接字段里有NULL這個(gè)連接會(huì)直接失敗記錄會(huì)被悄悄丟棄。我在處理用戶表和訂單表關(guān)聯(lián)時(shí)踩過(guò)這個(gè)結(jié)果訂單統(tǒng)計(jì)數(shù)一直對(duì)不上查了一上午才發(fā)現(xiàn)是有幾個(gè)訂單的user_id存了NULL。生成環(huán)境里遇到這種“數(shù)據(jù)莫名變少”的問(wèn)題第一個(gè)懷疑方向就是連接字段里有NULL。3. 數(shù)據(jù)王國(guó)的管理法則約束、關(guān)系與范式3.1 外鍵表與表之間的合同建表時(shí)我們把學(xué)生、課程、選課分成三張表那這三張表怎么產(chǎn)生聯(lián)系靠外鍵FOREIGN KEY。外鍵的意思就是某一列的值必須來(lái)自另一張表的某個(gè)字段值或者必須是NULL。選課表的設(shè)計(jì)CREATE TABLE course_selection ( id BIGINT AUTO_INCREMENT PRIMARY KEY, student_id BIGINT NOT NULL, course_id BIGINT NOT NULL, score DECIMAL(5,2), CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES student(id), CONSTRAINT fk_course FOREIGN KEY (course_id) REFERENCES course(id) );有了外鍵約束你往選課表里插一個(gè)不存在的student_id數(shù)據(jù)庫(kù)直接報(bào)錯(cuò)根本插不進(jìn)去。從數(shù)據(jù)王國(guó)治理的角度講這就是“法律條文”——誰(shuí)都不能往選課記錄里塞一個(gè)不在籍的學(xué)生。但外鍵在互聯(lián)網(wǎng)業(yè)務(wù)高并發(fā)場(chǎng)景下是“奢侈品”。因?yàn)橥怄I會(huì)影響寫入性能每次插入都要去校驗(yàn)關(guān)聯(lián)表在大流量寫入場(chǎng)景下很多公司會(huì)放棄數(shù)據(jù)庫(kù)外鍵改在應(yīng)用層做校驗(yàn)。這是理論知識(shí)和實(shí)際工程的矛盾點(diǎn)之一面試的時(shí)候如果被問(wèn)到“你平時(shí)寫外鍵嗎”答“看場(chǎng)景低并發(fā)實(shí)時(shí)性要求高的場(chǎng)景用物理外鍵高并發(fā)大流量場(chǎng)景用邏輯外鍵”就非常加分。3.2 三大范式數(shù)據(jù)冗余的克星范式Normal Form是數(shù)據(jù)庫(kù)設(shè)計(jì)的規(guī)范一共六種級(jí)別實(shí)際工作中掌握到第三范式就夠用了。第一范式1NF每列都不可再分。比如你把興趣寫成“籃球,足球,羽毛球”存在一個(gè)字段里這就違反了1NF應(yīng)該拆成三行或者單拆一張興趣表。第二范式2NF在1NF基礎(chǔ)上每一列都完全依賴主鍵而不是依賴主鍵的一部分。舉個(gè)例子一個(gè)選課表的主鍵如果是(student_id, course_id)那成績(jī)字段score依賴整個(gè)主鍵沒(méi)問(wèn)題但如果你把“課程老師”也塞進(jìn)選課表它就只依賴course_id不依賴student_id這就是部分依賴違反2NF。結(jié)果就是同一個(gè)課程老師的名字會(huì)在幾十行里重復(fù)改一次老師名要改幾十行。第三范式3NF在2NF基礎(chǔ)上非主鍵字段不能依賴其他非主鍵字段。比如訂單表里有“用戶ID”和“用戶手機(jī)號(hào)”手機(jī)號(hào)由用戶ID決定不是直接依賴訂單號(hào)這就是傳遞依賴。應(yīng)該把手機(jī)號(hào)留在用戶表訂單表只存用戶ID。范式的本質(zhì)是消除冗余但現(xiàn)實(shí)中見(jiàn)過(guò)很多項(xiàng)目為了性能刻意反范式比如同步一份冗余字段到查詢頻繁的表里避免JOIN。這算工程權(quán)衡不犯法前提是你清楚自己付出了維護(hù)一致性的代價(jià)。3.3 綜合案例設(shè)計(jì)一個(gè)簡(jiǎn)單的博客系統(tǒng)把前面的知識(shí)點(diǎn)揉在一起我?guī)阍O(shè)計(jì)一個(gè)迷你博客系統(tǒng)。這個(gè)場(chǎng)景我在講解時(shí)經(jīng)常用因?yàn)樗N近生活邏輯也不復(fù)雜。需要四張表用戶表user、文章表article、分類表category、評(píng)論表comment。用戶表id、用戶名、密碼密文、昵稱、注冊(cè)時(shí)間。 文章表id、標(biāo)題、正文、作者ID外鍵指向用戶表的id、分類ID外鍵指向分類表的id、發(fā)布時(shí)間、狀態(tài)。 分類表id、名稱、排序值。 評(píng)論表id、內(nèi)容、文章ID外鍵指向文章表id、用戶ID外鍵指向用戶表id、評(píng)論時(shí)間。這個(gè)設(shè)計(jì)里文章表不直接存作者名字只存作者ID想知道作者名就通過(guò)ID去用戶表查。為什么因?yàn)樽髡呙秩绻牧宋恼卤砝锏臄?shù)據(jù)不用動(dòng)始終查出來(lái)是最新的。如果你寫死“作者名”字段作者改名以后歷史文章全部要跟著改。這個(gè)理念是數(shù)據(jù)庫(kù)外鍵關(guān)系最核心的價(jià)值單一事實(shí)來(lái)源。4. 進(jìn)階修煉索引與事務(wù)數(shù)據(jù)王國(guó)的兩大護(hù)法4.1 索引讓數(shù)據(jù)王國(guó)不再“翻箱倒柜”沒(méi)有索引的時(shí)候數(shù)據(jù)庫(kù)查一張100萬(wàn)行的表要一行一行從頭掃到尾這叫全表掃描耗時(shí)可能幾百毫秒甚至秒級(jí)。加了索引之后數(shù)據(jù)庫(kù)相當(dāng)于給字段建了一棵查找樹查一次只需要走樹的高度10幾層就找到了耗時(shí)瞬間到毫秒級(jí)。索引的選型思路用不用背B樹原理面試會(huì)問(wèn)但實(shí)際操作里你只需要知道索引是排好序的數(shù)據(jù)結(jié)構(gòu)它用額外的存儲(chǔ)空間換查詢速度。對(duì)哪個(gè)字段建索引取決于你的WHERE條件、ORDER BY、JOIN字段。我建議新手的建索引標(biāo)準(zhǔn)主鍵默認(rèn)就是唯一索引別重復(fù)建。WHERE常用的條件字段優(yōu)先建索引。JOIN連接的字段建議建索引否則多表關(guān)聯(lián)會(huì)變成災(zāi)難。區(qū)分度低的字段不要建索引比如“性別”只有男女兩個(gè)值索引根本沒(méi)有過(guò)濾效果還耗空間。不要在索引列上做函數(shù)運(yùn)算比如WHERE DATE(birth_date) 2001-01-01這會(huì)讓索引失效。說(shuō)到索引失效這是新手排查慢查詢的第一大頭疼點(diǎn)。常見(jiàn)的三個(gè)坑隱式類型轉(zhuǎn)換字符串列不引號(hào)或數(shù)字列存成字符串去比較、前模糊查詢LIKE %abc導(dǎo)致索引失效、聯(lián)合索引不按最左前綴原則匹配。這三個(gè)記住了你排查SQL慢查詢的思路就有了。4.2 事務(wù)要么全成要么全敗你把兩個(gè)賬戶之間轉(zhuǎn)賬這個(gè)業(yè)務(wù)想一下從A賬戶扣100塊往B賬戶加100塊。如果扣完A的錢數(shù)據(jù)庫(kù)突然斷電B的錢沒(méi)加上這事就沒(méi)法交代。事務(wù)Transaction就是干這個(gè)用的它把一串操作打包成一個(gè)整體要么全部成功要么全部回滾。事務(wù)的ACID特性是面試必考但實(shí)際理解起來(lái)并不難原子性Atomicity一個(gè)事務(wù)里的操作要么全部完成要么全部不做。一致性Consistency事務(wù)完成后數(shù)據(jù)庫(kù)從一個(gè)合法狀態(tài)進(jìn)入另一個(gè)合法狀態(tài)。比如轉(zhuǎn)賬后AB的總金額不變。隔離性Isolation多個(gè)事務(wù)同時(shí)操作同一批數(shù)據(jù)時(shí)互不干擾。持久性Durability事務(wù)提交以后即使數(shù)據(jù)庫(kù)重啟了數(shù)據(jù)也還在。InnoDB引擎支持事務(wù)MyISAM引擎不支持這也是我之前建議ENGINEInnoDB的原因。事務(wù)的隔離級(jí)別有四種讀未提交、讀已提交、可重復(fù)讀、串行化。MySQL默認(rèn)是“可重復(fù)讀”。隔離級(jí)別低并發(fā)性能好但可能出現(xiàn)臟讀、不可重復(fù)讀、幻讀的問(wèn)題。這部分內(nèi)容初學(xué)者了解概念即可真正糾結(jié)隔離級(jí)別是DBA的活。4.3 實(shí)操示例用事務(wù)保護(hù)一筆轉(zhuǎn)賬操作舉個(gè)例子我們用事務(wù)把轉(zhuǎn)賬的完整過(guò)程寫出來(lái)START TRANSACTION; UPDATE account SET balance balance - 100 WHERE user_id 1; -- 檢查更新是否成功如果余額不足ROLLBACK UPDATE account SET balance balance 100 WHERE user_id 2; COMMIT;在你寫完第一個(gè)UPDATE之后如果發(fā)現(xiàn)user_id1的賬戶余額不夠扣你可以立刻ROLLBACK回滾全程不會(huì)產(chǎn)生任何臟數(shù)據(jù)。這在實(shí)際業(yè)務(wù)代碼里往往是通過(guò)程序先查詢余額、再判斷是否足夠、再?zèng)Q定提交還是回滾來(lái)完成的。5. 5道經(jīng)典題從表結(jié)構(gòu)到查詢優(yōu)化的進(jìn)階之路5.1 第一題找出每個(gè)學(xué)生的最高分課程假設(shè)有三張表student(id, name)、course(id, name)、score(student_id, course_id, score)。問(wèn)題是“每個(gè)學(xué)生的最高分課程”。這個(gè)題的核心是子查詢和關(guān)聯(lián)。先找到每個(gè)學(xué)生的最高分再把這個(gè)結(jié)果和原表關(guān)聯(lián)起來(lái)取課程信息。我教你一個(gè)實(shí)用的思路拿一張臨時(shí)結(jié)果去JOIN原始表。臨時(shí)結(jié)果結(jié)構(gòu)是(student_id, max_score)和score表按這兩個(gè)字段關(guān)聯(lián)就能得到對(duì)應(yīng)的course_id再連course表取課程名。這個(gè)思路理解了以后很多“找前幾條”“找最大一條”的題都通吃。5.2 第二題統(tǒng)計(jì)各課程的平均分只保留平均分大于60的這題考GROUP BY和HAVING的組合。新手常見(jiàn)的錯(cuò)誤是把過(guò)濾條件寫在WHERE里但WHERE的執(zhí)行時(shí)機(jī)是在分組之前分組之后的篩選必須用HAVING。這個(gè)知識(shí)點(diǎn)背下來(lái)不如踩一次坑記得牢我面試的時(shí)候問(wèn)過(guò)好幾個(gè)人能準(zhǔn)確講出HAVING用途的不超過(guò)四成。5.3 第三題查詢沒(méi)有選任何課程的學(xué)生這題有三種解法NOT EXISTS、NOT IN、LEFT JOIN IS NULL。面試官通常希望看到你至少寫出兩種。核心邏輯是先找到“有選課的學(xué)生ID集合”然后從學(xué)生表里排除。需要提醒的是如果子查詢結(jié)果集里出現(xiàn)NULLNOT IN的結(jié)果會(huì)意外為空這是個(gè)大坑。用NOT EXISTS或者LEFT JOIN更安全。5.4 第四題刪除重復(fù)數(shù)據(jù)只保留最小ID的那條這題在實(shí)際工作中經(jīng)常會(huì)遇到屬于“數(shù)據(jù)清洗”場(chǎng)景。原理是先把重復(fù)數(shù)據(jù)的最小ID查出來(lái)然后刪除“重復(fù)但不是最小ID”的記錄。寫DELETE之前一定先寫一條SELECT驗(yàn)證一下你的條件不要上來(lái)就刪。我親眼見(jiàn)過(guò)同事刪重復(fù)數(shù)據(jù)把整張表清空的場(chǎng)景恢復(fù)數(shù)據(jù)花了三個(gè)小時(shí)教訓(xùn)夠深刻。5.5 第五題訂單表的分頁(yè)查詢優(yōu)化訂單表1000萬(wàn)條數(shù)據(jù)執(zhí)行“SELECT * FROM orders ORDER BY create_time LIMIT 800000, 20”越來(lái)越慢。原因很簡(jiǎn)單隨著偏移量增大數(shù)據(jù)庫(kù)要掃描的記錄數(shù)越來(lái)越多。優(yōu)化方案有幾種用覆蓋索引簡(jiǎn)化排序記錄上一頁(yè)最后一條的create_time用WHERE條件代替LIMIT偏移或者先查ID再JOIN回原表。第三種方案效果最好但SQL最復(fù)雜。這個(gè)問(wèn)題在生產(chǎn)環(huán)境太常見(jiàn)了值得記下。6. 避坑實(shí)錄新手最常見(jiàn)的錯(cuò)誤與排查心得6.1 經(jīng)典錯(cuò)誤速查表錯(cuò)誤現(xiàn)象常見(jiàn)原因解決方案查詢結(jié)果莫名變少JOIN連接的字段有NULL檢查關(guān)聯(lián)字段的空值或用LEFT JOIN并過(guò)濾UPDATE/DELETE影響大量行WHERE條件忘記寫或?qū)戝e(cuò)先用SELECT驗(yàn)證條件再執(zhí)行索引沒(méi)生效查詢依舊慢列上用了函數(shù)或隱式類型轉(zhuǎn)換改寫SQL條件避免左側(cè)函數(shù)運(yùn)算數(shù)據(jù)寫入報(bào)“Data too long”字段長(zhǎng)度不夠字符集是UTF-8擴(kuò)大字段長(zhǎng)度或拆表處理大批量導(dǎo)入時(shí)速度極慢一條條INSERT太頻繁改成批量INSERT或使用LOAD DATA事務(wù)提交后數(shù)據(jù)卻不見(jiàn)了用了不支持事務(wù)的引擎確認(rèn)ENGINEInnoDB檢查隔離級(jí)別6.2 慢SQL排查的原點(diǎn)思維遇到慢查詢第一個(gè)反應(yīng)不是“加索引”而是“先看執(zhí)行計(jì)劃”。執(zhí)行計(jì)劃是數(shù)據(jù)庫(kù)分析你的SQL后給出的執(zhí)行方案它會(huì)告訴你走了哪張表的哪個(gè)索引、掃描了多少行。在MySQL里用EXPLAIN關(guān)鍵字在Oracle里是執(zhí)行計(jì)劃函數(shù)。很多新手分析慢查詢上來(lái)就加索引加到第五個(gè)索引還沒(méi)解決再回頭一看其實(shí)是表的數(shù)據(jù)分布有問(wèn)題或者SQL寫法有缺陷。排查步驟我整理成固定的順序先看SQL邏輯是否合理再看執(zhí)行計(jì)劃選擇了哪個(gè)索引再看是否出現(xiàn)了隱式類型轉(zhuǎn)換或函數(shù)運(yùn)算最后考慮優(yōu)化表結(jié)構(gòu)和索引。按這個(gè)順序走下來(lái)絕大部分慢查詢半小時(shí)內(nèi)能定位。6.3 備份是救命的不是走形式的最后必須講一句數(shù)據(jù)庫(kù)學(xué)習(xí)階段養(yǎng)成備份習(xí)慣。我見(jiàn)過(guò)太多“手滑清空了表”的事故不管你是新手還是老手意外永遠(yuǎn)在路上。建議至少每周做一次全量備份重要數(shù)據(jù)可以開啟定期自動(dòng)備份。在備份的生命里要順便測(cè)試一下恢復(fù)流程——很多團(tuán)隊(duì)備份做了三年真出事的時(shí)候才發(fā)現(xiàn)備份文件損壞那才是最絕望的場(chǎng)景?；謴?fù)流程不演練備份等于白做。7. 寫在最后的一點(diǎn)個(gè)人體會(huì)數(shù)據(jù)庫(kù)這門課入門不難難的是“知其然還知其所以然”。同樣的SQL不同的人寫出來(lái)性能可能差幾十倍。想真正上手給自己布置個(gè)小項(xiàng)目比如搭一個(gè)博客系統(tǒng)、做一個(gè)記賬小程序把建表、約束、查詢、事務(wù)、索引全部用上一遍。踩幾個(gè)坑再回來(lái)看理論知識(shí)你會(huì)發(fā)現(xiàn)每個(gè)概念都活了。我自己帶新人的時(shí)候常說(shuō)這么一句話“操作數(shù)據(jù)庫(kù)之前多問(wèn)自己一遍——這條SQL在真實(shí)業(yè)務(wù)里會(huì)不會(huì)給別的查詢帶來(lái)壓力這個(gè)表結(jié)構(gòu)下個(gè)月需求一變更還能不能撐住”養(yǎng)成這種習(xí)慣你就不只是會(huì)寫SQL的人而是真正懂?dāng)?shù)據(jù)王國(guó)運(yùn)作的人。