數(shù)據(jù)庫(kù)系統(tǒng)這門課做實(shí)驗(yàn)的心態(tài)往往分兩個(gè)階段。前三次實(shí)驗(yàn)大家基本都在跟 SQL 語(yǔ)法纏斗——建表、增刪改查、寫子查詢跑出正確結(jié)果就很有成就感。但到了實(shí)驗(yàn)四味道突然變了它不再只問(wèn)你“這條查詢?cè)趺磳憽倍情_始問(wèn)“這條查詢憑什么快”“這筆業(yè)務(wù)憑什么不會(huì)出錯(cuò)”“這個(gè)規(guī)則憑什么自動(dòng)執(zhí)行”。說(shuō)白了實(shí)驗(yàn)四要把你的視野從“寫對(duì)”拉到“寫好”從單條語(yǔ)句拉到數(shù)據(jù)庫(kù)運(yùn)行機(jī)制的整體理解。這篇文章我就拿某高校軟件學(xué)院數(shù)據(jù)庫(kù)系統(tǒng)實(shí)驗(yàn)四的完整過(guò)程來(lái)講把視圖、索引、存儲(chǔ)過(guò)程、事務(wù)和觸發(fā)器這五個(gè)點(diǎn)串進(jìn)一條業(yè)務(wù)線里逐步拆解每一步為什么要這么做、怎么做才能得分、實(shí)驗(yàn)室里最容易翻車的地方在哪。正在啃這個(gè)實(shí)驗(yàn)的同學(xué)可以直接照做想補(bǔ)短板或者以后準(zhǔn)備做助教的人也可以把它當(dāng)一份現(xiàn)成的備課筆記。1. 實(shí)驗(yàn)四到底在考什么需求拆解與整體思路1.1 從課程主線看實(shí)驗(yàn)四的定位數(shù)據(jù)庫(kù)系統(tǒng)的實(shí)驗(yàn)安排一般有一條很清晰的遞進(jìn)線。實(shí)驗(yàn)一建庫(kù)建表練的是 DDL核心是熟悉字段類型、主鍵外鍵約束實(shí)驗(yàn)二做增刪改查練的是 DML核心是單表的 CURD實(shí)驗(yàn)三開始上難度多表連接、子查詢、聚合函數(shù)核心是“把復(fù)雜查詢寫出來(lái)”到了實(shí)驗(yàn)四重點(diǎn)就變成了“把數(shù)據(jù)庫(kù)用明白”。這里的“用明白”包含幾個(gè)層面。第一能不能創(chuàng)建視圖來(lái)簡(jiǎn)化高頻查詢第二能不能用索引讓查詢真正變快而不是停留在“理論上該快”第三能不能用存儲(chǔ)過(guò)程把多步業(yè)務(wù)操作打包成一個(gè)可調(diào)用的單元第四能不能用事務(wù)保證數(shù)據(jù)在并發(fā)和出錯(cuò)環(huán)境下依然一致第五能不能用觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)的自動(dòng)約束和審計(jì)。這五個(gè)點(diǎn)都不是新語(yǔ)法那么簡(jiǎn)單它們的共同特征是每一個(gè)都是在回答“為什么”。我見(jiàn)過(guò)不少同學(xué)實(shí)驗(yàn)四做得稀里糊涂代碼都是從網(wǎng)上抄來(lái)的運(yùn)行能出結(jié)果就提交了。老師驗(yàn)收時(shí)隨口問(wèn)一句“你這個(gè)視圖為什么比直接查表方便”就卡住了。所以做這個(gè)實(shí)驗(yàn)之前先別急著敲代碼把五件事的邏輯關(guān)系想清楚比多寫十條 SQL 有用得多。1.2 一條業(yè)務(wù)線串起五個(gè)知識(shí)點(diǎn)實(shí)驗(yàn)四最好的做法是找一條業(yè)務(wù)線把這五個(gè)知識(shí)點(diǎn)全部串起來(lái)。我當(dāng)時(shí)帶學(xué)生的時(shí)候推薦他們統(tǒng)一采用“學(xué)生成績(jī)管理系統(tǒng)”的場(chǎng)景因?yàn)檎n程所有同學(xué)都能理解而且非常自然。場(chǎng)景是這樣設(shè)計(jì)的學(xué)生選課、考試、成績(jī)錄入然后教務(wù)需要經(jīng)常按課程查看班級(jí)成績(jī)分布。圍繞這個(gè)場(chǎng)景五個(gè)知識(shí)點(diǎn)的出場(chǎng)順序非常順經(jīng)常會(huì)寫“某門課所有學(xué)生的成績(jī)和平均分”這種長(zhǎng) SQL于是封裝成視圖成績(jī)表數(shù)據(jù)量大了以后查詢?cè)絹?lái)越慢于是給高頻查詢字段建索引成績(jī)錄錯(cuò)要修改但不能改一半于是用事務(wù)保證原子性再用存儲(chǔ)過(guò)程把“檢查記錄-更新-提交/回滾”封裝起來(lái)學(xué)生成績(jī)一變就要留痕人工在應(yīng)用里寫日志容易漏于是靠觸發(fā)器自動(dòng)寫入審計(jì)表。五個(gè)點(diǎn)被一條業(yè)務(wù)線串起來(lái)之后實(shí)驗(yàn)報(bào)告也不再是五個(gè)孤立的小題而是“我要在什么場(chǎng)景下解決什么問(wèn)題、為什么選這個(gè)手段、效果如何”的完整閉環(huán)。這個(gè)思路本身就是拿高分的底子。2. 環(huán)境與數(shù)據(jù)準(zhǔn)備動(dòng)手寫 SQL 之前的必修課2.1 數(shù)據(jù)庫(kù)版本和客戶端怎么選實(shí)驗(yàn)四用的數(shù)據(jù)庫(kù)管理系統(tǒng)不同學(xué)校規(guī)定不同。如果你所在課程用的是課內(nèi)統(tǒng)一安裝的版本那沒(méi)什么好挑的如果沒(méi)規(guī)定我的建議是選 MySQL 8.0原因很實(shí)在開源免費(fèi)、安裝包小、出問(wèn)題搜索引擎一搜一大堆答案助教也最熟悉?？蛻舳朔矫?DBeaver 和 Navicat 都可以。DBeaver 是免費(fèi)的跨平臺(tái)適合學(xué)生Navicat 對(duì)初學(xué)者更友好圖形界面直觀查看表結(jié)構(gòu)和執(zhí)行計(jì)劃的時(shí)候體驗(yàn)不錯(cuò)。我這里多說(shuō)一句有些同學(xué)偷懶直接在某在線 SQL 網(wǎng)站上做實(shí)驗(yàn)我不太推薦。實(shí)驗(yàn)四不像前面幾次實(shí)驗(yàn)它需要反復(fù)切換會(huì)話去復(fù)現(xiàn)事務(wù)并發(fā)問(wèn)題還需要看執(zhí)行計(jì)劃和鎖等待信息在線網(wǎng)站根本提供不了這些功能。安裝 MySQL 時(shí)有兩個(gè)坑提前說(shuō)一下。一是端口沖突3306 經(jīng)常被之前殘留的舊版本占用裝完連不上先檢查端口二是字符集安裝后默認(rèn)可能是 latin1后面中文亂碼基本都是從這里埋下的我建議建庫(kù)時(shí)直接顯式指定 utf8mb4。2.2 基礎(chǔ)表結(jié)構(gòu)設(shè)計(jì)與樣本數(shù)據(jù)構(gòu)造實(shí)驗(yàn)四的表結(jié)構(gòu)不需要很復(fù)雜三張核心表就夠了學(xué)生表 student、課程表 course、選課成績(jī)表 sc。結(jié)構(gòu)參考如下CREATE DATABASE db_exp4 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; CREATE TABLE student ( sno CHAR(9) PRIMARY KEY, sname VARCHAR(20) NOT NULL, sex CHAR(1) DEFAULT 男, dept VARCHAR(30), enroll_year YEAR ); CREATE TABLE course ( cno CHAR(4) PRIMARY KEY, cname VARCHAR(40) NOT NULL, credit DECIMAL(3,1), semester VARCHAR(10) ); CREATE TABLE sc ( sno CHAR(9), cno CHAR(4), score DECIMAL(5,2), PRIMARY KEY (sno, cno), FOREIGN KEY (sno) REFERENCES student(sno), FOREIGN KEY (cno) REFERENCES course(cno) );注意兩個(gè)細(xì)節(jié)一是主鍵用學(xué)號(hào)、課程號(hào)這種有業(yè)務(wù)含義的字段實(shí)驗(yàn)場(chǎng)景里沒(méi)問(wèn)題生產(chǎn)環(huán)境我會(huì)建議用自增 ID但這里沒(méi)必要繞遠(yuǎn)二是成績(jī)用 DECIMAL(5,2)表示三位整數(shù)加兩位小數(shù)比用 INT 精確得多也方便后面算平均分。樣本數(shù)據(jù)這塊很多人不重視覺(jué)得隨便插幾十條就行。但我可以明確告訴你實(shí)驗(yàn)四里“數(shù)據(jù)量”直接決定你索引優(yōu)化的效果能不能體現(xiàn)出來(lái)。只有幾十條記錄全表掃描也在一毫秒以內(nèi)你建索引前后的對(duì)比根本看不出來(lái)。我當(dāng)時(shí)會(huì)讓同學(xué)寫一個(gè)小腳本生成幾百到上千條選課記錄分?jǐn)?shù)分布要盡量自然別全是 90 分以上否則后面的視圖統(tǒng)計(jì)和成績(jī)分布分析會(huì)失真。數(shù)據(jù)里還應(yīng)該故意制造一些“不完美”的情況比如某些學(xué)生沒(méi)有選課有些課程沒(méi)有任何學(xué)生選。這些數(shù)據(jù)在后面驗(yàn)證視圖的外連接、聚合函數(shù)時(shí)非常有用能讓你發(fā)現(xiàn)“用內(nèi)連接統(tǒng)計(jì)會(huì)丟數(shù)據(jù)”這個(gè)經(jīng)典問(wèn)題。2.3 腳本組織與命名約定實(shí)驗(yàn)四的代碼量明顯比前三大多了如果全堆在一個(gè) SQL 文件里執(zhí)行的時(shí)候報(bào)錯(cuò)都不知道錯(cuò)在哪一行。我習(xí)慣把腳本按功能拆成多個(gè)文件01_schema.sql建庫(kù)建表、建約束02_data.sql插入樣本數(shù)據(jù)03_view_index.sql創(chuàng)建視圖和索引04_proc_trigger.sql存儲(chǔ)過(guò)程、觸發(fā)器、事務(wù)操作05_query.sql驗(yàn)證用的查詢語(yǔ)句06_test.sql完整的驗(yàn)收測(cè)試流程這樣做的第一個(gè)好處是調(diào)試定位快哪個(gè)文件報(bào)錯(cuò)就改哪個(gè)文件不需要在一大坨代碼里翻來(lái)翻去。第二個(gè)好處是驗(yàn)收的時(shí)候助教要求“從零開始執(zhí)行一遍”你按文件順序跑一次就能過(guò)不會(huì)因?yàn)槟扯未a在后面引用了前面還沒(méi)創(chuàng)建的視圖而報(bào)錯(cuò)。命名規(guī)范同樣值得養(yǎng)成習(xí)慣視圖用 v_ 開頭存儲(chǔ)過(guò)程用 sp_ 開頭觸發(fā)器用 trg_ 開頭函數(shù)用 fn_ 開頭。學(xué)校不會(huì)強(qiáng)制要求這些但這是行業(yè)里通行的經(jīng)驗(yàn)寫了以后報(bào)告看起來(lái)專業(yè)很多也給自己省不少麻煩。3. 視圖和索引最基礎(chǔ)也最容易丟分的地方3.1 視圖的本質(zhì)與創(chuàng)建要點(diǎn)視圖很多人第一次接觸時(shí)會(huì)覺(jué)得很玄乎其實(shí)它就是一個(gè)命名的查詢。你每次都要寫一遍“學(xué)生選課統(tǒng)計(jì)”的長(zhǎng) SQL不如把它存成一個(gè)虛擬表以后直接查它。視圖不占物理存儲(chǔ)空間數(shù)據(jù)還是存在原來(lái)的表里所以它不會(huì)讓查詢本身變快但能讓查詢語(yǔ)句變短、邏輯變清晰還能隱藏底層表的敏感字段。我建議實(shí)驗(yàn)里至少創(chuàng)建一個(gè)“學(xué)生平均成績(jī)”的視圖CREATE OR REPLACE VIEW v_stu_avg AS SELECT s.sno, s.sname, COUNT(sc.cno) AS course_count, ROUND(AVG(sc.score), 2) AS avg_score FROM student s LEFT JOIN sc ON s.sno sc.sno GROUP BY s.sno, s.sname;這里必須用 LEFT JOIN不能是 INNER JOIN。原因剛才提到過(guò)有些學(xué)生一門課都沒(méi)選內(nèi)連接會(huì)把他們?cè)诮Y(jié)果里抹掉統(tǒng)計(jì)就丟失了這部分記錄。這就是一個(gè)典型的“看起來(lái)全對(duì)、實(shí)際邏輯有漏洞”的點(diǎn)實(shí)驗(yàn)報(bào)告里能主動(dòng)寫出為什么選外連接印象分馬上不一樣。視圖還有一個(gè)容易踩的坑寫視圖定義時(shí)用了SELECT *。剛開始圖省事后來(lái)你給原表加了一個(gè)字段視圖也跟著變但有些老代碼早就寫死了字段順序結(jié)果接口輸出全亂。實(shí)驗(yàn)里不會(huì)鬧這么大動(dòng)靜但養(yǎng)成習(xí)慣視圖里永遠(yuǎn)顯式列出字段。3.2 索引設(shè)計(jì)的取舍索引為什么能讓查詢變快我常用一個(gè)生活類比一本幾千頁(yè)的工具書你要找一個(gè)關(guān)鍵詞一頁(yè)一頁(yè)翻就是全表掃描看目錄定位到頁(yè)碼就是索引查找。數(shù)據(jù)庫(kù)里的索引通常用 B 樹實(shí)現(xiàn)查詢從“順序掃全表”變成“樹里跳轉(zhuǎn)”復(fù)雜度從 O(n) 降到 O(log n)數(shù)據(jù)量越大差距越明顯。但索引不是越多越好這是新手最容易走極端的地方。每建一個(gè)索引寫入時(shí)都要多維護(hù)一棵樹等于每次插入和更新都變慢了。所以建索引的原則是高頻查詢條件字段、外連接字段、排序字段才值得建寫多讀少的表要克制。實(shí)驗(yàn)里我會(huì)讓同學(xué)做一組對(duì)比先不建索引跑一個(gè)“統(tǒng)計(jì)某門課程 90 分以上人數(shù)”的查詢?nèi)缓蠼ㄋ饕鼵REATE INDEX idx_sc_cno_score ON sc(cno, score);再去跑同一句記錄耗時(shí)對(duì)比。復(fù)合索引 (cno, score) 同時(shí)覆蓋了“課程號(hào)過(guò)濾”和“分?jǐn)?shù)范圍判斷”查詢只需要在某個(gè)課程范圍的索引區(qū)間內(nèi)做掃描不需要回表碰成績(jī)數(shù)據(jù)。這個(gè)對(duì)比實(shí)驗(yàn)寫進(jìn)報(bào)告比空談“索引能優(yōu)化查詢”有說(shuō)服力太多。3.3 用執(zhí)行計(jì)劃驗(yàn)證索引而不是靠感覺(jué)很多同學(xué)建完索引以后就默認(rèn)“它生效了”這不對(duì)。索引生效與否執(zhí)行計(jì)劃說(shuō)了才算。MySQL 里在 SQL 前面加個(gè) EXPLAIN 關(guān)鍵字就能看到執(zhí)行計(jì)劃EXPLAIN SELECT s.sname, sc.score FROM sc JOIN student s ON s.sno sc.sno WHERE sc.cno C001 AND sc.score 90;執(zhí)行計(jì)劃里重點(diǎn)看幾列。type 這一行最好不是 ALLALL 就是全表掃描說(shuō)明索引沒(méi)起作用key 列顯示用到了哪個(gè)索引是 NULL 就說(shuō)明沒(méi)用到rows 是預(yù)估掃描的行數(shù)數(shù)字越小越好Extra 出現(xiàn) Using filesort 說(shuō)明排序在內(nèi)存里額外做了通常意味著索引沒(méi)能支持排序。有一個(gè)終極經(jīng)典的坑對(duì)索引列做函數(shù)運(yùn)算。比如WHERE YEAR(enroll_year) 2023哪怕你在 enroll_year 上建了索引執(zhí)行計(jì)劃照樣給你全表掃描。原因是數(shù)據(jù)庫(kù)沒(méi)法對(duì)一列做完函數(shù)后的結(jié)果直接走索引必須把每一行的值都算一遍才知道合不滿足。正確的寫法是寫成范圍條件WHERE enroll_year BETWEEN 2023-01-01 AND 2023-12-31我自己的習(xí)慣是實(shí)驗(yàn)里每驗(yàn)證一個(gè)索引都把 EXPLAIN 的結(jié)果截圖存好然后寫一句“從執(zhí)行計(jì)劃可以看到key 從 NULL 變成了 idx_sc_cno_scorerows 從 XXX 降到了 YYY驗(yàn)證了索引生效”。這句話寫下來(lái)老師很清楚你是真的理解而不是只會(huì)敲代碼。4. 存儲(chǔ)過(guò)程、事務(wù)與觸發(fā)器把業(yè)務(wù)邏輯寫進(jìn)數(shù)據(jù)庫(kù)4.1 存儲(chǔ)過(guò)程的參數(shù)、變量與異常處理存儲(chǔ)過(guò)程本質(zhì)上就是把一段業(yè)務(wù)邏輯封裝在數(shù)據(jù)庫(kù)端需要的時(shí)候 CALL 一下就行。實(shí)驗(yàn)里我會(huì)做一個(gè)“按學(xué)號(hào)和課程號(hào)修改成績(jī)”的存儲(chǔ)過(guò)程它比裸寫一條 UPDATE 多了一層價(jià)值能校驗(yàn)記錄是否存在能在出錯(cuò)時(shí)回滾還能返回處理結(jié)果。DELIMITER // CREATE PROCEDURE sp_update_score( IN p_sno CHAR(9), IN p_cno CHAR(4), IN p_new_score DECIMAL(5,2), OUT p_message VARCHAR(100) ) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SET p_message 更新失敗已回滾; END; START TRANSACTION; UPDATE sc SET score p_new_score WHERE sno p_sno AND cno p_cno; IF ROW_COUNT() 0 THEN SET p_message 記錄不存在已回滾; ROLLBACK; ELSE COMMIT; SET p_message 更新成功; END IF; END // DELIMITER ;先說(shuō) DELIMITER 這個(gè)看起來(lái)很怪的東西。客戶端默認(rèn)用分號(hào)作為一條語(yǔ)句的結(jié)束標(biāo)志而存儲(chǔ)過(guò)程內(nèi)部每一行 UPDATE 后都有分號(hào)如果不臨時(shí)把分隔符改掉客戶端會(huì)在第一個(gè)分號(hào)處把“語(yǔ)句”斷開后面全部報(bào)錯(cuò)。所以我先把分隔符改成 //等整個(gè)存儲(chǔ)過(guò)程體結(jié)束再用 // 收尾最后再改回分號(hào)。這個(gè)細(xì)節(jié)十個(gè)報(bào)錯(cuò)的同學(xué)有八個(gè)是因?yàn)樗?。再講異常處理DECLARE EXIT HANDLER FOR SQLEXCEPTION 是這份代碼的靈魂。沒(méi)有它事務(wù)中途遇到約束沖突或語(yǔ)法錯(cuò)誤時(shí)已經(jīng)執(zhí)行的語(yǔ)句不會(huì)自動(dòng)回滾數(shù)據(jù)可能處于“改了一半”的狀態(tài)。有了它一旦捕獲異常就整體 ROLLBACK保證原子性。這個(gè)模式的行業(yè)里叫“先聲明異常哨兵再開啟事務(wù)”熟練掌握后寫任何數(shù)據(jù)修改類存儲(chǔ)過(guò)程都能套用。還有一點(diǎn)容易被忽略參數(shù)類型要和表字段完全一致。有一次我見(jiàn)同學(xué)把 p_new_score 聲明成 INT調(diào)用后傳 95.5結(jié)果誤差悄悄就出現(xiàn)了因?yàn)閿?shù)據(jù)庫(kù)把 95.5 轉(zhuǎn)成了 96 再做更新。這種“看起來(lái)沒(méi)問(wèn)題數(shù)據(jù)卻變了”的 bug 最折磨人提前聲明成 DECIMAL(5,2) 就不會(huì)有這回事。4.2 事務(wù)隔離級(jí)別與并發(fā)問(wèn)題的復(fù)現(xiàn)事務(wù)的 ACID 四個(gè)特性背概念容易真正理解要靠動(dòng)手。實(shí)驗(yàn)要求里通常有一條是“演示并發(fā)環(huán)境下事務(wù)的隔離性”這里我給一個(gè)實(shí)際可操作的流程模擬兩個(gè)客戶端同時(shí)處理同一門課的選課人數(shù)。假設(shè)課程容量只剩 1 個(gè)名額兩個(gè)學(xué)生同時(shí)選課。如果不加鎖兩個(gè)事務(wù)都先查“剩余名額”都看到 1都執(zhí)行成功最后容量變成 -1這就叫超賣。解決辦法是查詢名額時(shí)加一把行鎖START TRANSACTION; SELECT quota FROM course WHERE cno C001 FOR UPDATE; -- 業(yè)務(wù)判斷剩余名額是否大于 0 UPDATE course SET quota quota - 1 WHERE cno C001; COMMIT;FOR UPDATE 的作用是把這一行鎖住第二個(gè)事務(wù)同樣執(zhí)行這條 SELECT 時(shí)會(huì)阻塞住直到第一個(gè)事務(wù) COMMIT 或 ROLLBACK它才讀到最新的剩余名額。這個(gè)過(guò)程不親手跑一遍很難體會(huì)“鎖”的含義。隔離級(jí)別是另一個(gè)高頻考點(diǎn)我建議畫一張表來(lái)記讀未提交會(huì)有臟讀和幻讀讀已提交能防臟讀但仍有不可重復(fù)讀和幻讀可重復(fù)讀防了前兩者但可能幻讀串行化全防但并發(fā)性能最差。MySQL 8.0 默認(rèn)是可重復(fù)讀。實(shí)驗(yàn)里復(fù)現(xiàn)這些并發(fā)問(wèn)題有一個(gè)技巧在兩個(gè)會(huì)話里手動(dòng)控制提交時(shí)機(jī)而不是讓程序瞬間跑完。比如在事務(wù)里故意加上 SELECT SLEEP(5); 模擬業(yè)務(wù)處理時(shí)間然后趁這個(gè)窗口在另一個(gè)會(huì)話里去讀同一條數(shù)據(jù)就能清楚觀察到另一個(gè)事務(wù)到底看到的是舊值還是新值。這個(gè)方法很土但非常有效比純講理論直觀得多。4.3 觸發(fā)器的適用場(chǎng)景與邊界觸發(fā)器最有價(jià)值的使用場(chǎng)景是那些“不管從哪個(gè)入口改數(shù)據(jù)都必須伴隨發(fā)生”的邏輯。成績(jī)修改審計(jì)就是一個(gè)標(biāo)準(zhǔn)例子不管成績(jī)是被哪個(gè)應(yīng)用、哪個(gè)用戶改的都必須留下一條審計(jì)記錄。寫代碼的人可能忘了寫日志但觸發(fā)器不會(huì)忘。CREATE TABLE score_audit ( id INT AUTO_INCREMENT PRIMARY KEY, sno CHAR(9) NOT NULL, cno CHAR(4) NOT NULL, old_score DECIMAL(5,2), new_score DECIMAL(5,2), op_time DATETIME DEFAULT CURRENT_TIMESTAMP ); CREATE TRIGGER trg_score_audit AFTER UPDATE ON sc FOR EACH ROW BEGIN INSERT INTO score_audit(sno, cno, old_score, new_score) VALUES (OLD.sno, OLD.cno, OLD.score, NEW.score); END;AFTER UPDATE 表示行更新完成后觸發(fā)OLD 和 NEW 分別代表更新前、更新后的行。觸發(fā)器里把舊分和新分都寫進(jìn)審計(jì)表事后想查“誰(shuí)把成績(jī)從 58 改成了 88”一條 SELECT 就查出來(lái)了。但觸發(fā)器這個(gè)東西必須克制。我在實(shí)際項(xiàng)目中見(jiàn)過(guò)不少“觸發(fā)器災(zāi)難”有人為了讓系統(tǒng)自動(dòng)同步某張表在表 A 的觸發(fā)器里更新表 B表 B 又有觸發(fā)器更新表 A結(jié)果形成循環(huán)調(diào)用數(shù)據(jù)庫(kù)直接報(bào)錯(cuò)或者死鎖。實(shí)驗(yàn)要求做一個(gè)觸發(fā)器就夠了千萬(wàn)別為了炫技做過(guò)度的級(jí)聯(lián)設(shè)計(jì)翻車率非常高。還有一點(diǎn)要注意同一張表的 AFTER UPDATE 觸發(fā)器里如果再更新同一張表MySQL 會(huì)報(bào)遞歸觸發(fā)錯(cuò)誤ERROR 1442。原因是 AFTER 階段行的最終狀態(tài)已經(jīng)確定了不允許再改動(dòng)這張表。如果確實(shí)要修改應(yīng)該用 BEFORE 觸發(fā)器直接改 NEW 字段或者把邏輯挪到存儲(chǔ)過(guò)程里做。5. 常見(jiàn)問(wèn)題與排查技巧實(shí)錄5.1 中文亂碼字符集問(wèn)題亂碼是實(shí)驗(yàn)四里出現(xiàn)頻率最高的問(wèn)題沒(méi)有之一尤其在一開始建庫(kù)的時(shí)候就沒(méi)管字符集的情況下。亂碼的本質(zhì)是“寫入用的編碼”和“讀取用的編碼”不一致。我用中文“實(shí)驗(yàn)”兩個(gè)字舉例如果服務(wù)器按 utf8mb4 存客戶端卻按 GBK 解讀每個(gè)中文字符的字節(jié)序列拆開再拼起來(lái)就變成了完全不通的字符。排查步驟很簡(jiǎn)單。第一步看建庫(kù)語(yǔ)句有沒(méi)有指定字符集沒(méi)有的話執(zhí)行下面的命令看當(dāng)前是什么SHOW VARIABLES LIKE character_set_%;第二步看客戶端連接設(shè)置DBeaver 或 Navicat 里連接配置都有字符集選項(xiàng)統(tǒng)一選 utf8mb4。第三步看表的字符集SHOW TABLE STATUS LIKE student;能看到默認(rèn)字符集。三處一致亂碼就消失了。我見(jiàn)過(guò)很多“查了很多方法都沒(méi)用”的案例最后都是因?yàn)檫B接串里沒(méi)指定 characterEncodingutf8卡了半天才解決。5.2 觸發(fā)器遞歸和死鎖觸發(fā)器相關(guān)的報(bào)錯(cuò)里ERROR 1442 是最典型的一種。場(chǎng)景通常是同學(xué)想“成績(jī)更新后順手把總分表也更新了”于是在 sc 表的 AFTER UPDATE 觸發(fā)器里寫了 UPDATE sc 的語(yǔ)句結(jié)果直接報(bào)錯(cuò)。原因前面說(shuō)過(guò)AFTER 階段不能再改這張表本身。死鎖則是并發(fā)場(chǎng)景的高發(fā)問(wèn)題。最常見(jiàn)的死鎖原因是兩個(gè)事務(wù)以不同順序加鎖事務(wù) A 先鎖成績(jī)表再鎖課程表事務(wù) B 先鎖課程表再鎖成績(jī)表雙方各拿一把鎖等對(duì)方釋放誰(shuí)也等不到數(shù)據(jù)庫(kù)檢測(cè)到后主動(dòng)把其中一個(gè)事務(wù)回滾釋放資源。排查死鎖有一個(gè)萬(wàn)能命令SHOW ENGINE INNODB STATUS;輸出里找到 LATEST DETECTED DEADLOCK 段落能看到兩個(gè)事務(wù)各自持有哪些鎖、在等待哪個(gè)鎖據(jù)此就能調(diào)整加鎖順序。生產(chǎn)中避免死鎖的核心原則就是“所有事務(wù)按照相同的順序訪問(wèn)表和行”這個(gè)原則做實(shí)驗(yàn)的時(shí)候同樣適用。5.3 索引失靈的幾類典型場(chǎng)景很多同學(xué)跑完對(duì)比實(shí)驗(yàn)發(fā)現(xiàn)“建了索引和沒(méi)建一樣慢”第一反應(yīng)是數(shù)據(jù)庫(kù)有問(wèn)題其實(shí)十有八九是查詢寫法把索引廢了。最常見(jiàn)的幾類對(duì)索引列套函數(shù)比如 YEAR(create_time) 2023前面已經(jīng)說(shuō)過(guò)隱式類型轉(zhuǎn)換比如索引列是 VARCHAR條件里寫 WHERE sno 20230001沒(méi)加引號(hào)數(shù)據(jù)庫(kù)會(huì)先把列轉(zhuǎn)成數(shù)值再比較索引同樣失效前導(dǎo)模糊查詢LIKE %張三% 無(wú)法利用索引因?yàn)樗饕龢涫前辞熬Y組織的而 LIKE 張三% 可以在索引列上用 OR 連接了兩個(gè)非索引條件優(yōu)化器權(quán)衡后可能直接放棄索引。這些場(chǎng)景每一條都可以在實(shí)驗(yàn)里做個(gè)小驗(yàn)證建索引 → 用失效寫法跑 EXPLAIN → 看 key 列變?yōu)?NULL → 改用正確寫法 → 看 key 列恢復(fù)。把這組對(duì)比寫進(jìn)報(bào)告你整篇實(shí)驗(yàn)報(bào)告的專業(yè)程度立刻上一個(gè)臺(tái)階。5.4 常見(jiàn)問(wèn)題速查表現(xiàn)象可能原因解決辦法插入中文數(shù)據(jù)后顯示亂碼庫(kù)表字符集或客戶端連接編碼不一致統(tǒng)一使用 utf8mb4檢查連接配置存儲(chǔ)過(guò)程 CALL 報(bào)語(yǔ)法錯(cuò)誤未用 DELIMITER 修改分隔符將分隔符臨時(shí)改為 // 再創(chuàng)建事務(wù)執(zhí)行一半報(bào)錯(cuò)后數(shù)據(jù)沒(méi)變?nèi)碑惓Ｌ幚砥魑椿貪L聲明 EXIT HANDLER FOR SQLEXCEPTION觸發(fā)器執(zhí)行報(bào) ERROR 1442AFTER 觸發(fā)器內(nèi)更新了同一張表改為 BEFORE 改 NEW或把邏輯搬到存儲(chǔ)過(guò)程建了索引 EXPLAIN 仍是全表掃描對(duì)索引列做了函數(shù)運(yùn)算或類型轉(zhuǎn)換改寫為范圍條件或統(tǒng)一類型再比較兩個(gè)會(huì)話互相等鎖超時(shí)加鎖順序不一致統(tǒng)一事務(wù)內(nèi)的表訪問(wèn)順序視圖查出來(lái)的數(shù)據(jù)少了誤用了 INNER JOIN 導(dǎo)致無(wú)選課記錄被丟改用 LEFT JOIN 保留主表全量6. 實(shí)驗(yàn)報(bào)告怎么寫才能拿高分6.1 驗(yàn)收時(shí)老師到底在看什么實(shí)驗(yàn)四的驗(yàn)收和前面幾次有很大區(qū)別前面可能看結(jié)果有沒(méi)有跑出來(lái)到實(shí)驗(yàn)四更看重的是一句話“你能不能講清楚為什么”。我自己參與過(guò)幾次驗(yàn)收老師常問(wèn)的問(wèn)題大致就這幾類第一你這個(gè)視圖解決的是什么問(wèn)題它和直接寫長(zhǎng) SQL 有什么區(qū)別第二你的索引為什么選這幾個(gè)字段建EXPLAIN 給你什么信息第三存儲(chǔ)過(guò)程里為什么要用事務(wù)沒(méi)有事務(wù)會(huì)怎樣第四觸發(fā)器為什么不用存儲(chǔ)過(guò)程替代兩者的邊界在哪。這些問(wèn)題全部指向“理解”而不是“代碼能跑”。所以準(zhǔn)備驗(yàn)收的時(shí)候別背代碼背邏輯。把每個(gè)知識(shí)點(diǎn)對(duì)應(yīng)的業(yè)務(wù)場(chǎng)景、為什么選這個(gè)手段、效果怎么驗(yàn)證這三件事理順老師問(wèn)什么你都能從場(chǎng)景出發(fā)講清楚。6.2 報(bào)告結(jié)構(gòu)與加分寫法報(bào)告結(jié)構(gòu)我建議按這個(gè)順序組織實(shí)驗(yàn)?zāi)康?→ 實(shí)驗(yàn)環(huán)境 → 需求與整體設(shè)計(jì) → 表結(jié)構(gòu)和數(shù)據(jù)說(shuō)明 → 各知識(shí)點(diǎn)實(shí)現(xiàn)過(guò)程 → 對(duì)比實(shí)驗(yàn)與結(jié)果分析 → 遇到的問(wèn)題與解決 → 實(shí)驗(yàn)心得。前兩部分控制在半頁(yè)以內(nèi)別寫廢話。需求與整體設(shè)計(jì)部分把“為什么選成績(jī)管理場(chǎng)景、五個(gè)知識(shí)點(diǎn)怎么串起來(lái)”講明白這部分是拉開分?jǐn)?shù)的第一個(gè)機(jī)會(huì)。各知識(shí)點(diǎn)實(shí)現(xiàn)部分代碼要完整運(yùn)行結(jié)果要有截圖關(guān)鍵操作要有執(zhí)行計(jì)劃這是拉開分?jǐn)?shù)的第二個(gè)機(jī)會(huì)。對(duì)比實(shí)驗(yàn)是最大的加分項(xiàng)。無(wú)索引和有索引的查詢耗時(shí)對(duì)比、不同隔離級(jí)別下并發(fā)讀的結(jié)果對(duì)比、不帶異常處理和帶異常處理的存儲(chǔ)過(guò)程執(zhí)行對(duì)比這三組對(duì)比只要做了報(bào)告的分析深度立刻和別人的“代碼堆砌”拉開距離。我自己?？吹揭粋€(gè)有意思的現(xiàn)象同樣兩個(gè)知識(shí)點(diǎn)都完成了的同學(xué)寫對(duì)比實(shí)驗(yàn)的人普遍比只貼代碼的人分?jǐn)?shù)高一截。還有一個(gè)細(xì)節(jié)報(bào)告里的每個(gè)截圖都要配一兩句解釋說(shuō)明“從這張圖能看到什么、結(jié)果驗(yàn)證了什么”。只貼圖不解釋等于沒(méi)貼解釋了才證明圖里的信息你真的看懂了。寫在最后這套實(shí)驗(yàn)做完我最大的感受是它把數(shù)據(jù)庫(kù)從“存數(shù)據(jù)的地方”變成了“幫你把數(shù)據(jù)管好的工具”。以前寫一條 UPDATE 我從不考慮后果現(xiàn)在看到任何對(duì)數(shù)據(jù)的修改腦子里會(huì)自動(dòng)彈出三個(gè)問(wèn)題要不要包事務(wù)、要不要留審計(jì)、要不要控制并發(fā)。這個(gè)思維習(xí)慣比記住那些 SQL 語(yǔ)法值錢得多。最后分享一個(gè)小技巧實(shí)驗(yàn)做完之后把全部腳本從頭到尾重新執(zhí)行一遍完全模擬助教驗(yàn)收現(xiàn)場(chǎng)。這一步能提前暴露九成以上的意外問(wèn)題——視圖建錯(cuò)順序、觸發(fā)器重復(fù)創(chuàng)建、樣本數(shù)據(jù)沒(méi)清理干凈全是這種“平時(shí)感覺(jué)沒(méi)事、驗(yàn)收突然爆炸”的隱藏雷。在宿舍里先爆掉總比在老師面前爆掉體面這個(gè)道理做多了實(shí)驗(yàn)自然就懂了。