我在帶新人入門(mén)的時(shí)候最常聽(tīng)到的一句話是“數(shù)據(jù)庫(kù)到底是個(gè)啥感覺(jué)就是個(gè)存數(shù)據(jù)的軟件唄。”這話沒(méi)毛病但只對(duì)了一半。如果只是存數(shù)據(jù)Excel也能干為啥要搞出個(gè)專(zhuān)門(mén)的“王國(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)作邏輯掰開(kāi)揉碎講一遍再配5道我篩選過(guò)的經(jīng)典題幫你把腦子里那些零散概念串成一條線。這篇文章不挑基礎(chǔ)哪怕你完全沒(méi)寫(xiě)過(guò)SQL也能看懂如果你已經(jīng)會(huì)用增刪改查了重點(diǎn)看后半部分的題目解析和避坑心得那里面有不少是我在實(shí)際項(xiàng)目里踩出來(lái)的經(jīng)驗(yàn)。數(shù)據(jù)庫(kù)這個(gè)東西難起來(lái)可以研究一輩子但入門(mén)的時(shí)候只要把“表、關(guān)系、約束、索引、事務(wù)”這幾個(gè)核心概念吃透你就已經(jīng)甩開(kāi)絕大多數(shù)“只會(huì)背命令”的人了。1. 由表入道數(shù)據(jù)王國(guó)的地基是“二維表”不是“數(shù)據(jù)庫(kù)軟件”1.1 為什么是“表”而不是“文件柜”先想一個(gè)問(wèn)題你手里有一堆客戶(hù)信息、訂單信息、商品信息最原始的做法是拿個(gè)本子記或者塞進(jìn)Excel。Excel和數(shù)據(jù)庫(kù)的表看起來(lái)都是“行和列”但本質(zhì)上有個(gè)天大的區(qū)別——Excel是給“人”看的數(shù)據(jù)庫(kù)的表是給“機(jī)器”和“規(guī)則”看的。機(jī)器看數(shù)據(jù)講究的是“嚴(yán)格”。每一列存什么類(lèi)型長(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)被稱(chēng)作“關(guān)系模式”表本身就是“關(guān)系”。你把一張表想象成一個(gè)王國(guó)的戶(hù)籍本每個(gè)公民行都有唯一的身份證號(hào)主鍵每項(xiàng)登記內(nèi)容列都有明確規(guī)范。沒(méi)有規(guī)矩不成方圓沒(méi)有主鍵的表就是黑戶(hù)遍地查數(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)只能靠“用戶(hù)ID下單時(shí)間”來(lái)拼。這種情況我建議你用自增ID或者一串隨機(jī)生成的唯一編號(hào)。我在實(shí)際項(xiàng)目里見(jiàn)過(guò)有人用“姓名手機(jī)號(hào)”做主鍵結(jié)果兩個(gè)同事的手機(jī)號(hào)錄錯(cuò)了撞了主鍵整個(gè)表寫(xiě)不進(jìn)去數(shù)據(jù)排查半天才發(fā)現(xiàn)是臟數(shù)據(jù)問(wèn)題。記住主鍵最好用無(wú)業(yè)務(wù)含義的純ID業(yè)務(wù)字段隨時(shí)可能變變了主鍵就崩了。1.3 字段類(lèi)型的坑別把什么都存成字符串建表的時(shí)候字段類(lèi)型的選型是新手最?lèi)?ài)忽略的。我見(jiàn)過(guò)最典型的操作是把手機(jī)號(hào)存成INT結(jié)果號(hào)碼顯示成“138****”后面被抹位其實(shí)不是顯示問(wèn)題是數(shù)值溢出后被自動(dòng)轉(zhuǎn)了。還有把日期存成字符串然后查“最近7天下單用戶(hù)”的時(shí)候用字符串比較大小格式一不統(tǒng)一就漏數(shù)據(jù)。常用的字段類(lèi)型幾句話講明白INT/BIGINT整數(shù)類(lèi)型BIGINT比INT范圍大得多自增主鍵建議直接BIGINT別省。DECIMAL(M,D)定點(diǎn)小數(shù)存錢(qián)用這個(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í)要寫(xiě)大概多大的數(shù)據(jù)量這些想清楚了再寫(xiě)CREATE TABLE否則后面改表結(jié)構(gòu)極度痛苦尤其是表里有數(shù)據(jù)了之后ALTER TABLE在大表上可能直接鎖表業(yè)務(wù)就停了。我建議每個(gè)入門(mén)的人練習(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ì)專(zhuān)門(mén)提。2.2 增刪改查四個(gè)動(dòng)作覆蓋九成需求增刪改查對(duì)應(yīng)SQL里的INSERT、DELETE、UPDATE、SELECT。這四個(gè)單詞背下來(lái)你已經(jīng)會(huì)一半SQL了。查詢(xún)SELECT是所有操作里最常用的也是把新手和老手區(qū)分開(kāi)的分水嶺。最簡(jiǎn)單的查詢(xú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)我不在這里展開(kāi)第4節(jié)的5道經(jīng)典題里會(huì)逐個(gè)過(guò)一遍。寫(xiě)入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ò)誤給一張用戶(hù)表更新?tīng)顟B(tài)忘了加WHERE條件一條UPDATE下去全表的用戶(hù)狀態(tài)全變成了同一個(gè)值。那幾秒鐘腦子是空白的心跳是加速的。從那以后我的習(xí)慣是先寫(xiě)SELECT把要改的數(shù)據(jù)查出來(lái)看一遍確認(rèn)WHERE條件沒(méi)寫(xiě)錯(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)查不到任何東西正確寫(xiě)法是WHERE phone IS NULL。這里有個(gè)坑中坑在JOIN連接的時(shí)候如果連接字段里有NULL這個(gè)連接會(huì)直接失敗記錄會(huì)被悄悄丟棄。我在處理用戶(hù)表和訂單表關(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ì)影響寫(xiě)入性能每次插入都要去校驗(yàn)關(guān)聯(lián)表在大流量寫(xiě)入場(chǎng)景下很多公司會(huì)放棄數(shù)據(jù)庫(kù)外鍵改在應(yīng)用層做校驗(yàn)。這是理論知識(shí)和實(shí)際工程的矛盾點(diǎn)之一面試的時(shí)候如果被問(wèn)到“你平時(shí)寫(xiě)外鍵嗎”答“看場(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每列都不可再分。比如你把興趣寫(xiě)成“籃球,足球,羽毛球”存在一個(gè)字段里這就違反了1NF應(yīng)該拆成三行或者單拆一張興趣表。第二范式2NF在1NF基礎(chǔ)上每一列都完全依賴(lài)主鍵而不是依賴(lài)主鍵的一部分。舉個(gè)例子一個(gè)選課表的主鍵如果是(student_id, course_id)那成績(jī)字段score依賴(lài)整個(gè)主鍵沒(méi)問(wèn)題但如果你把“課程老師”也塞進(jìn)選課表它就只依賴(lài)course_id不依賴(lài)student_id這就是部分依賴(lài)違反2NF。結(jié)果就是同一個(gè)課程老師的名字會(huì)在幾十行里重復(fù)改一次老師名要改幾十行。第三范式3NF在2NF基礎(chǔ)上非主鍵字段不能依賴(lài)其他非主鍵字段。比如訂單表里有“用戶(hù)ID”和“用戶(hù)手機(jī)號(hào)”手機(jī)號(hào)由用戶(hù)ID決定不是直接依賴(lài)訂單號(hào)這就是傳遞依賴(lài)。應(yīng)該把手機(jī)號(hào)留在用戶(hù)表訂單表只存用戶(hù)ID。范式的本質(zhì)是消除冗余但現(xiàn)實(shí)中見(jiàn)過(guò)很多項(xiàng)目為了性能刻意反范式比如同步一份冗余字段到查詢(xún)頻繁的表里避免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ù)雜。需要四張表用戶(hù)表user、文章表article、分類(lèi)表category、評(píng)論表comment。用戶(hù)表id、用戶(hù)名、密碼密文、昵稱(chēng)、注冊(cè)時(shí)間。 文章表id、標(biāo)題、正文、作者ID外鍵指向用戶(hù)表的id、分類(lèi)ID外鍵指向分類(lèi)表的id、發(fā)布時(shí)間、狀態(tài)。 分類(lèi)表id、名稱(chēng)、排序值。 評(píng)論表id、內(nèi)容、文章ID外鍵指向文章表id、用戶(hù)ID外鍵指向用戶(hù)表id、評(píng)論時(shí)間。這個(gè)設(shè)計(jì)里文章表不直接存作者名字只存作者ID想知道作者名就通過(guò)ID去用戶(hù)表查。為什么因?yàn)樽髡呙秩绻牧宋恼卤砝锏臄?shù)據(jù)不用動(dòng)始終查出來(lái)是最新的。如果你寫(xiě)死“作者名”字段作者改名以后歷史文章全部要跟著改。這個(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)于給字段建了一棵查找樹(shù)查一次只需要走樹(shù)的高度10幾層就找到了耗時(shí)瞬間到毫秒級(jí)。索引的選型思路用不用背B樹(shù)原理面試會(huì)問(wèn)但實(shí)際操作里你只需要知道索引是排好序的數(shù)據(jù)結(jié)構(gòu)它用額外的存儲(chǔ)空間換查詢(xún)速度。對(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ō)到索引失效這是新手排查慢查詢(xún)的第一大頭疼點(diǎn)。常見(jiàn)的三個(gè)坑隱式類(lèi)型轉(zhuǎn)換字符串列不引號(hào)或數(shù)字列存成字符串去比較、前模糊查詢(xún)LIKE %abc導(dǎo)致索引失效、聯(lián)合索引不按最左前綴原則匹配。這三個(gè)記住了你排查SQL慢查詢(xún)的思路就有了。4.2 事務(wù)要么全成要么全敗你把兩個(gè)賬戶(hù)之間轉(zhuǎn)賬這個(gè)業(yè)務(wù)想一下從A賬戶(hù)扣100塊往B賬戶(hù)加100塊。如果扣完A的錢(qián)數(shù)據(jù)庫(kù)突然斷電B的錢(qián)沒(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ò)程寫(xiě)出來(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;在你寫(xiě)完第一個(gè)UPDATE之后如果發(fā)現(xiàn)user_id1的賬戶(hù)余額不夠扣你可以立刻ROLLBACK回滾全程不會(huì)產(chǎn)生任何臟數(shù)據(jù)。這在實(shí)際業(yè)務(wù)代碼里往往是通過(guò)程序先查詢(xún)余額、再判斷是否足夠、再?zèng)Q定提交還是回滾來(lái)完成的。5. 5道經(jīng)典題從表結(jié)構(gòu)到查詢(xún)優(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è)題的核心是子查詢(xún)和關(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ò)濾條件寫(xiě)在WHERE里但WHERE的執(zhí)行時(shí)機(jī)是在分組之前分組之后的篩選必須用HAVING。這個(gè)知識(shí)點(diǎn)背下來(lái)不如踩一次坑記得牢我面試的時(shí)候問(wèn)過(guò)好幾個(gè)人能準(zhǔn)確講出HAVING用途的不超過(guò)四成。5.3 第三題查詢(xún)沒(méi)有選任何課程的學(xué)生這題有三種解法NOT EXISTS、NOT IN、LEFT JOIN IS NULL。面試官通常希望看到你至少寫(xiě)出兩種。核心邏輯是先找到“有選課的學(xué)生ID集合”然后從學(xué)生表里排除。需要提醒的是如果子查詢(xún)結(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”的記錄。寫(xiě)DELETE之前一定先寫(xiě)一條SELECT驗(yàn)證一下你的條件不要上來(lái)就刪。我親眼見(jiàn)過(guò)同事刪重復(fù)數(shù)據(jù)把整張表清空的場(chǎng)景恢復(fù)數(shù)據(jù)花了三個(gè)小時(shí)教訓(xùn)夠深刻。5.5 第五題訂單表的分頁(yè)查詢(xún)優(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)原因解決方案查詢(xún)結(jié)果莫名變少JOIN連接的字段有NULL檢查關(guān)聯(lián)字段的空值或用LEFT JOIN并過(guò)濾UPDATE/DELETE影響大量行WHERE條件忘記寫(xiě)或?qū)戝e(cuò)先用SELECT驗(yàn)證條件再執(zhí)行索引沒(méi)生效查詢(xún)依舊慢列上用了函數(shù)或隱式類(lèi)型轉(zhuǎn)換改寫(xiě)SQL條件避免左側(cè)函數(shù)運(yùn)算數(shù)據(jù)寫(xiě)入報(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)思維遇到慢查詢(xún)第一個(gè)反應(yīng)不是“加索引”而是“先看執(zhí)行計(jì)劃”。執(zhí)行計(jì)劃是數(shù)據(jù)庫(kù)分析你的SQL后給出的執(zhí)行方案它會(huì)告訴你走了哪張表的哪個(gè)索引、掃描了多少行。在MySQL里用EXPLAIN關(guān)鍵字在Oracle里是執(zhí)行計(jì)劃函數(shù)。很多新手分析慢查詢(xún)上來(lái)就加索引加到第五個(gè)索引還沒(méi)解決再回頭一看其實(shí)是表的數(shù)據(jù)分布有問(wèn)題或者SQL寫(xiě)法有缺陷。排查步驟我整理成固定的順序先看SQL邏輯是否合理再看執(zhí)行計(jì)劃選擇了哪個(gè)索引再看是否出現(xiàn)了隱式類(lèi)型轉(zhuǎn)換或函數(shù)運(yùn)算最后考慮優(yōu)化表結(jié)構(gòu)和索引。按這個(gè)順序走下來(lái)絕大部分慢查詢(xún)半小時(shí)內(nèi)能定位。6.3 備份是救命的不是走形式的最后必須講一句數(shù)據(jù)庫(kù)學(xué)習(xí)階段養(yǎng)成備份習(xí)慣。我見(jiàn)過(guò)太多“手滑清空了表”的事故不管你是新手還是老手意外永遠(yuǎn)在路上。建議至少每周做一次全量備份重要數(shù)據(jù)可以開(kāi)啟定期自動(dòng)備份。在備份的生命里要順便測(cè)試一下恢復(fù)流程——很多團(tuán)隊(duì)備份做了三年真出事的時(shí)候才發(fā)現(xiàn)備份文件損壞那才是最絕望的場(chǎng)景。恢復(fù)流程不演練備份等于白做。7. 寫(xiě)在最后的一點(diǎn)個(gè)人體會(huì)數(shù)據(jù)庫(kù)這門(mén)課入門(mén)不難難的是“知其然還知其所以然”。同樣的SQL不同的人寫(xiě)出來(lái)性能可能差幾十倍。想真正上手給自己布置個(gè)小項(xiàng)目比如搭一個(gè)博客系統(tǒng)、做一個(gè)記賬小程序把建表、約束、查詢(xún)、事務(wù)、索引全部用上一遍。踩幾個(gè)坑再回來(lái)看理論知識(shí)你會(huì)發(fā)現(xiàn)每個(gè)概念都活了。我自己帶新人的時(shí)候常說(shuō)這么一句話“操作數(shù)據(jù)庫(kù)之前多問(wèn)自己一遍——這條SQL在真實(shí)業(yè)務(wù)里會(huì)不會(huì)給別的查詢(xún)帶來(lái)壓力這個(gè)表結(jié)構(gòu)下個(gè)月需求一變更還能不能撐住”養(yǎng)成這種習(xí)慣你就不只是會(huì)寫(xiě)SQL的人而是真正懂?dāng)?shù)據(jù)王國(guó)運(yùn)作的人。