據(jù)庫系統(tǒng)實(shí)驗(yàn)三實(shí)戰(zhàn)：SQL復(fù)雜查詢、索引優(yōu)化與事務(wù)并發(fā)控制)
數(shù)據(jù)庫系統(tǒng)實(shí)驗(yàn)三在很多學(xué)校的人才培養(yǎng)方案里都是一道分水嶺。某高校軟件學(xué)院的這門實(shí)驗(yàn)題目看起來只是幾張表、幾條SQL但真正動(dòng)手后才會(huì)發(fā)現(xiàn)它把關(guān)系代數(shù)、查詢優(yōu)化、事務(wù)控制這些課堂理論全部揉在了一起。這個(gè)實(shí)驗(yàn)?zāi)軒湍愦蛲◤摹皶?huì)寫SQL”到“會(huì)用數(shù)據(jù)庫”的那一步尤其是復(fù)雜查詢和性能分析能力是后續(xù)做應(yīng)用開發(fā)的底層功。適合正在趕實(shí)驗(yàn)報(bào)告的同學(xué)也適合想系統(tǒng)鞏固數(shù)據(jù)庫實(shí)操的人。1. 實(shí)驗(yàn)全貌與設(shè)計(jì)思路拆解1.1 實(shí)驗(yàn)三到底在考查什么數(shù)據(jù)庫系統(tǒng)實(shí)驗(yàn)三通常不會(huì)只讓你做增刪改查而是把復(fù)雜查詢、索引、事務(wù)三個(gè)模塊串起來。先說復(fù)雜查詢一般會(huì)通過學(xué)生選課這種經(jīng)典場(chǎng)景要求你完成多表連接、子查詢、分組聚合、集合運(yùn)算甚至要處理“選修了全部課程”這類關(guān)系代數(shù)除法問題。這部分考查的是你能否把自然語言描述的業(yè)務(wù)需求準(zhǔn)確翻譯成沒有邏輯漏洞的SQL語句。索引模塊是對(duì)查詢性能的進(jìn)一步要求。你不僅要會(huì)寫查詢還要能判斷一條慢查詢?yōu)槭裁绰⑶彝ㄟ^建立索引、改寫SQL來讓它變快。這一步會(huì)涉及執(zhí)行計(jì)劃分析很多同學(xué)第一次看到EXPLAIN的輸出會(huì)懵但實(shí)際上它只是把優(yōu)化器的決策攤開給你看。事務(wù)與并發(fā)控制是實(shí)驗(yàn)三里最容易被忽視的部分因?yàn)椴幌袷恰皩懸粭lSQL出結(jié)果”那么直接。它需要你打開多個(gè)數(shù)據(jù)庫會(huì)話去制造臟讀、不可重復(fù)讀、幻讀再通過隔離級(jí)別或加鎖來規(guī)避。這個(gè)過程看起來像在做實(shí)驗(yàn)演示但恰恰是理解數(shù)據(jù)庫一致性的關(guān)鍵。設(shè)計(jì)者把這些環(huán)節(jié)放在實(shí)驗(yàn)三也是循序漸進(jìn)的。前面實(shí)驗(yàn)要求你建表、插數(shù)據(jù)、寫基礎(chǔ)查詢到實(shí)驗(yàn)三才開始模擬真實(shí)業(yè)務(wù)中的“統(tǒng)計(jì)、報(bào)表、并發(fā)更新”等場(chǎng)景。說白了前兩次實(shí)驗(yàn)是教你說單詞實(shí)驗(yàn)三是教你怎么寫句子和段落。1.2 數(shù)據(jù)庫選型與實(shí)驗(yàn)環(huán)境準(zhǔn)備實(shí)驗(yàn)本身對(duì)數(shù)據(jù)庫沒有硬性限制但選對(duì)工具能省很多事。我個(gè)人建議使用MySQL 8.0原因是它語法通用、網(wǎng)上的資料和踩坑記錄最多而且EXPLAIN輸出清晰默認(rèn)隔離級(jí)別是REPEATABLE READ很適合做事務(wù)實(shí)驗(yàn)。如果你學(xué)校機(jī)房預(yù)裝的是SQL Server或PostgreSQL問題也不大核心SQL語法基本一致只有少量差異。安裝MySQL時(shí)建議把默認(rèn)字符集設(shè)置為utf8mb4避免后面插入中文數(shù)據(jù)出現(xiàn)亂碼。到這一步很多同學(xué)會(huì)疑惑界面工具不是更直觀嗎我的態(tài)度是DataGrip、DBeaver這些工具可以用來看表結(jié)構(gòu)和執(zhí)行計(jì)劃的圖形化展示但實(shí)驗(yàn)報(bào)告里要求的是能直接運(yùn)行的SQL文本。所以至少一半的調(diào)試過程要在命令行或代碼編輯器里完成避免過度依賴自動(dòng)補(bǔ)全。我在本機(jī)一般會(huì)同時(shí)準(zhǔn)備兩種環(huán)境一個(gè)MySQL 8.0實(shí)例用來跑查詢一個(gè)Navicat或DBeaver連接作為輔助工具。要注意的是做事務(wù)實(shí)驗(yàn)時(shí)需要開多個(gè)會(huì)話窗口圖形工具通常也支持多標(biāo)簽頁不一定非要開多個(gè)命令行窗口。2. 核心細(xì)節(jié)解析與實(shí)操要點(diǎn)2.1 多表連接別讓結(jié)果集失控多表連接最容易犯的錯(cuò)就是忘記寫連接條件導(dǎo)致笛卡爾積。比如三張表學(xué)生表20條記錄、課程表10條、選課表50條如果不加任何WHERE或ON條件直接JOIN結(jié)果集會(huì)有20乘以10乘以50等于一萬行。我見過有同學(xué)統(tǒng)計(jì)“每門課的平均分”就是因?yàn)镴OIN條件寫錯(cuò)把每一個(gè)學(xué)生和每一門課都匹配了一遍最后平均值完全不對(duì)。寫多表連接時(shí)要形成肌肉記憶先確認(rèn)需要從哪些表取字段再畫出表之間的關(guān)聯(lián)鍵最后在ON子句里寫等值條件。對(duì)于外連接要明確主表是哪一張。LEFT JOIN會(huì)保留左邊表的全部行RIGHT JOIN反之。自連接是難點(diǎn)本質(zhì)上是給同一張表起兩個(gè)別名然后當(dāng)成兩張不同的表來操作。比如查詢“年齡比某位同學(xué)大的所有學(xué)生”就可以把student表與自己連接。還有一個(gè)細(xì)節(jié)外連接加上WHERE條件時(shí)容易踩坑。舉個(gè)例子你希望列出所有學(xué)生以及他們選修某門課的成績(jī)?nèi)绻萌缦聦懛⊿ELECT s.s_name, sc.score FROM student s LEFT JOIN sc ON s.s_id sc.s_id WHERE sc.c_id 1;表面看沒問題但如果某個(gè)學(xué)生沒有選任何課sc.c_id是NULLWHERE條件就把這行過濾掉了最終效果跟內(nèi)連接一樣。正確做法是把篩選放在子查詢里或者直接作為一個(gè)派生表SELECT s.s_name, t.score FROM student s LEFT JOIN ( SELECT s_id, score FROM sc WHERE c_id 1 ) t ON s.s_id t.s_id;這種細(xì)節(jié)在實(shí)驗(yàn)報(bào)告里非常加分因?yàn)樗w現(xiàn)的是對(duì)SQL邏輯的理解而不是只會(huì)調(diào)API。2.2 子查詢與集合運(yùn)算的易錯(cuò)點(diǎn)子查詢可以出現(xiàn)在SELECT子句、FROM子句和WHERE子句每種位置都有各自規(guī)則。SELECT子句里的標(biāo)量子查詢必須返回一行一列否則直接報(bào)錯(cuò)FROM子句里的派生表必須有別名WHERE子句里的IN和EXISTS要重點(diǎn)辨析。很多人以為IN和EXISTS只是寫法不同性能差不多但實(shí)驗(yàn)里要寫出合理的查詢就必須知道它們的差異。IN適合驅(qū)動(dòng)表數(shù)據(jù)量小、子查詢結(jié)果集也小的場(chǎng)景EXISTS更像循環(huán)匹配適合外表行數(shù)多、子查詢走索引的場(chǎng)景?，F(xiàn)代優(yōu)化器有時(shí)候會(huì)互相改寫但你不能依賴這一點(diǎn)。更關(guān)鍵的是NULL的三值邏輯陷阱。以“查詢沒有選修任何課程的學(xué)生”為例如果用NOT INSELECT s_id, s_name FROM student WHERE s_id NOT IN (SELECT s_id FROM sc);如果sc表中的s_id本身就是外鍵一般不會(huì)有NULL但如果子查詢里混入了NULLNOT IN會(huì)返回空集。這是因?yàn)镾QL中NULL既不是真也不是假而是未知導(dǎo)致最終結(jié)果無法確定為真。穩(wěn)妥方案是用NOT EXISTSSELECT s_id, s_name FROM student s WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.s_id s.s_id );在實(shí)驗(yàn)報(bào)告里可以把這兩種寫法及其結(jié)果都展示出來加上一句“為避免NULL對(duì)三值邏輯的影響推薦使用NOT EXISTS”老師會(huì)知道你真正踩過這個(gè)坑。集合運(yùn)算方面UNION會(huì)去重UNION ALL不會(huì)去重且性能更好。如果題目只是要求合并兩個(gè)結(jié)果集且明確要包含重復(fù)項(xiàng)就必須用UNION ALL。還有一個(gè)容易忽略的點(diǎn)集合運(yùn)算要求兩個(gè)查詢的列數(shù)相同對(duì)應(yīng)列的數(shù)據(jù)類型也要兼容不然會(huì)報(bào)錯(cuò)。2.3 索引優(yōu)化不要盲目建索引索引優(yōu)化的核心不是“建了索引查詢就會(huì)快”而是要理解B樹索引能加速什么。簡(jiǎn)單來說B樹可以讓數(shù)據(jù)庫快速定位到目標(biāo)數(shù)據(jù)而不用整表掃。WHERE中的等值條件、范圍條件、JOIN的關(guān)聯(lián)字段、ORDER BY和GROUP BY的字段都是適合建索引的候選。但索引不是越多越好。每次插入、更新、刪除時(shí)數(shù)據(jù)庫都要同步維護(hù)索引索引太多寫入性能就會(huì)變差。此外如果字段區(qū)分度太低比如性別只有“男”“女”建了索引也可能不被優(yōu)化器使用因?yàn)樗阆聛砣頀呙韪鼊澦?。?shí)驗(yàn)里通常要求你對(duì)比加索引前后的掃描行數(shù)和執(zhí)行時(shí)間所以數(shù)據(jù)量要足夠大。只插幾十行的話性能差異根本看不出來。組合索引要遵循最左前綴原則。比如建立索引(a, b, c)它可以加速包含a條件、包含a和b條件、包含a和b和c條件的查詢但不會(huì)加速只包含b或c的查詢。這好比查字典你只能按照字母順序從前到后翻跳過第一個(gè)字母直接查第二個(gè)字母是行不通的。EXPLAIN是最重要的調(diào)試工具。執(zhí)行EXPLAIN SELECT ...后重點(diǎn)看type列從好到差大致是const、eq_ref、ref、range、index、ALL。還有key列表示實(shí)際用到的索引rows列表示預(yù)估掃描行數(shù)。實(shí)驗(yàn)報(bào)告里可以貼上優(yōu)化前后的EXPLAIN輸出再用表格對(duì)比比寫一大段文字更直觀。3. 實(shí)操過程與核心環(huán)節(jié)實(shí)現(xiàn)3.1 選課系統(tǒng)的建表與數(shù)據(jù)準(zhǔn)備下面我們用一個(gè)可復(fù)現(xiàn)的模擬項(xiàng)目X來演示。表結(jié)構(gòu)設(shè)計(jì)如下學(xué)生表student、課程表course、選課表sc。選課表以(s_id, c_id)作為復(fù)合主鍵并設(shè)置外鍵保證完整性。成績(jī)字段score允許為空模擬“還沒考試”的情況。CREATE DATABASE IF NOT EXISTS db_lab3 CHARACTER SET utf8mb4; USE db_lab3; CREATE TABLE student ( s_id INT PRIMARY KEY AUTO_INCREMENT, s_name VARCHAR(50) NOT NULL, s_gender ENUM(M,F), s_age TINYINT, s_major VARCHAR(50) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE course ( c_id INT PRIMARY KEY AUTO_INCREMENT, c_name VARCHAR(100) NOT NULL, c_credit DECIMAL(3,1) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE sc ( s_id INT NOT NULL, c_id INT NOT NULL, score DECIMAL(4,1), PRIMARY KEY (s_id, c_id), CONSTRAINT fk_sc_student FOREIGN KEY (s_id) REFERENCES student(s_id), CONSTRAINT fk_sc_course FOREIGN KEY (c_id) REFERENCES course(c_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;插入示例數(shù)據(jù)時(shí)要注意外鍵順序先插入student和course再插入sc。如果偷懶用工具可視化操作后面做外鍵級(jí)聯(lián)時(shí)會(huì)很麻煩所以建議手工維護(hù)。INSERT INTO student (s_name, s_gender, s_age, s_major) VALUES (張同,M, 20, 軟件工程), (李茜,F, 21, 數(shù)據(jù)科學(xué)), (王磊,M, 22, 人工智能), (趙欣,F, 20, 網(wǎng)絡(luò)工程), (陳宇,M, 21, 軟件工程); INSERT INTO course (c_name, c_credit) VALUES (數(shù)據(jù)庫系統(tǒng), 4.0), (操作系統(tǒng), 3.5), (計(jì)算機(jī)網(wǎng)絡(luò), 3.0), (數(shù)據(jù)結(jié)構(gòu), 4.0), (軟件工程, 2.5); INSERT INTO sc (s_id, c_id, score) VALUES (1, 1, 91.0), (1, 2, 82.5), (2, 1, 88.0), (2, 3, 95.0), (3, 1, 76.0), (3, 4, 68.5), (4, 5, NULL), (5, 1, 79.0), (5, 2, 86.0);實(shí)際實(shí)驗(yàn)時(shí)這些數(shù)據(jù)遠(yuǎn)遠(yuǎn)不夠看索引效果。我當(dāng)時(shí)寫了一個(gè)存儲(chǔ)過程往sc表里插入了上萬條隨機(jī)記錄再把student和course也擴(kuò)充到幾百行這樣EXPLAIN的輸出才有說服力。存儲(chǔ)過程不是實(shí)驗(yàn)重點(diǎn)但值得寫在報(bào)告“附加工作”里。3.2 典型查詢題的SQL實(shí)現(xiàn)與解釋第一題查詢每個(gè)學(xué)生的選課數(shù)和平均分。注意使用LEFT JOIN保證沒選課的學(xué)生也能顯示出來平均分如果NULL就顯示為0SELECT s.s_id, s.s_name, COUNT(sc.c_id) AS course_num, ROUND(AVG(COALESCE(sc.score, 0)), 2) AS avg_score FROM student s LEFT JOIN sc ON s.s_id sc.s_id GROUP BY s.s_id, s.s_name;這里有一個(gè)細(xì)節(jié)COUNT(sc.c_id)只統(tǒng)計(jì)非NULL的選課記錄所以沒選課的學(xué)生course_num會(huì)是0而AVG(COALESCE(sc.score, 0))會(huì)把NULL當(dāng)成0分計(jì)算。這個(gè)寫法在業(yè)務(wù)上是否合理需要看題目要求但實(shí)驗(yàn)報(bào)告里可以解釋清楚。第二題查詢每門課的選課人數(shù)、最高分、最低分。同樣用LEFT JOIN列出無人選的課程SELECT c.c_id, c.c_name, COUNT(sc.s_id) AS stu_num, MAX(sc.score) AS max_score, MIN(sc.score) AS min_score FROM course c LEFT JOIN sc ON c.c_id sc.c_id GROUP BY c.c_id, c.c_name;第三題“查詢選修了數(shù)據(jù)庫系統(tǒng)課程且成績(jī)高于該課程平均分的學(xué)生”。我的第一版SQL是這樣寫的SELECT s.s_id, s.s_name, sc.score FROM student s JOIN sc ON s.s_id sc.s_id JOIN course c ON sc.c_id c.c_id WHERE c.c_name 數(shù)據(jù)庫系統(tǒng) AND sc.score ( SELECT AVG(sc2.score) FROM sc sc2 JOIN course c2 ON sc2.c_id c2.c_id WHERE c2.c_name 數(shù)據(jù)庫系統(tǒng) );這個(gè)查詢會(huì)先子查詢算出平均分再比較每條記錄。如果數(shù)據(jù)量大可以考慮用窗口函數(shù)但實(shí)驗(yàn)要求基礎(chǔ)SQL的話子查詢版本完全夠用。窗口函數(shù)版本是這樣SELECT s.s_id, s.s_name, sc.score FROM ( SELECT s_id, c_id, score, AVG(score) OVER (PARTITION BY c_id) AS avg_score FROM sc JOIN course ON sc.c_id course.c_id WHERE course.c_name 數(shù)據(jù)庫系統(tǒng) ) t JOIN student s ON t.s_id s.s_id WHERE t.score t.avg_score;兩個(gè)版本都能跑但是窗口函數(shù)可讀性更好在MySQL 8.0中性能也不錯(cuò)。實(shí)驗(yàn)報(bào)告里我建議主推子查詢版本并把窗口函數(shù)作為“進(jìn)一步優(yōu)化”提一下顯得思考全面。第四題是“查詢選修了全部課程的學(xué)生”。這是關(guān)系代數(shù)里的除法問題。一個(gè)常用解法是統(tǒng)計(jì)課程總數(shù)再按學(xué)生分組統(tǒng)計(jì)去重選課數(shù)兩者相等SELECT s.s_id, s.s_name FROM student s JOIN sc ON s.s_id sc.s_id GROUP BY s.s_id, s.s_name HAVING COUNT(DISTINCT sc.c_id) (SELECT COUNT(*) FROM course);注意COUNT(DISTINCT sc.c_id)要去重防止同一個(gè)人重復(fù)選修同一門課導(dǎo)致計(jì)數(shù)虛高。還有另一種“雙重NOT EXISTS”寫法可以解決零除問題但做實(shí)驗(yàn)時(shí)這種不使用聚合的寫法更經(jīng)典SELECT s.s_id, s.s_name FROM student s WHERE NOT EXISTS ( SELECT c.c_id FROM course c WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.s_id s.s_id AND sc.c_id c.c_id ) );這個(gè)邏輯需要一點(diǎn)耐心理解內(nèi)層NOT EXISTS選出“該學(xué)生沒選的課程”外層NOT EXISTS則保證不存在這樣的課程也就是學(xué)生選了所有課。3.3 索引優(yōu)化的完整對(duì)比索引優(yōu)化題通常會(huì)給一條慢查詢要求你通過加索引或改寫SQL來提升性能。我們可以拿這個(gè)查詢做例子EXPLAIN SELECT * FROM sc WHERE score BETWEEN 70 AND 90;沒有索引時(shí)EXPLAIN的type是ALLrows是全部數(shù)據(jù)行。加上普通索引ALTER TABLE sc ADD INDEX idx_score (score);再執(zhí)行EXPLAINtype會(huì)變成rangerows會(huì)明顯減少。但要注意如果數(shù)據(jù)量不大或者范圍覆蓋的數(shù)據(jù)占比太高優(yōu)化器可能仍然選擇全表掃描。這不代表索引沒用而是優(yōu)化器認(rèn)為訪問整表比走索引回表更快。所以在實(shí)驗(yàn)報(bào)告里“沒有優(yōu)化效果”也可能是一條有效結(jié)論關(guān)鍵是你能解釋清楚為什么。還有一個(gè)常見操作在student表的s_major字段上建索引來優(yōu)化“查詢某專業(yè)所有學(xué)生的選課信息”這類查詢。這里有個(gè)陷阱如果s_major的區(qū)分度不高索引效果可能不明顯。我建議用組合索引比如(s_major, s_id)這樣既能快速定位專業(yè)又能通過s_id再連接選課表。這個(gè)選擇背后的原理就是盡量讓索引覆蓋查詢條件減少回表次數(shù)。如果還想深入可以嘗試EXPLAIN ANALYZEMySQL 8.0.18以后支持會(huì)輸出實(shí)際執(zhí)行時(shí)間。但在提交實(shí)驗(yàn)報(bào)告之前建議先確認(rèn)老師是否接受這種高級(jí)工具因?yàn)橛行┰u(píng)分標(biāo)準(zhǔn)只要EXPLAIN輸出。3.4 事務(wù)與并發(fā)控制實(shí)驗(yàn)事務(wù)部分需要開多個(gè)會(huì)話窗口。先演示最基礎(chǔ)的事務(wù)提交和回滾-- 會(huì)話A START TRANSACTION; UPDATE sc SET score 99 WHERE s_id 1 AND c_id 1; ROLLBACK; SELECT score FROM sc WHERE s_id 1 AND c_id 1;如果ROLLBACK生效查出來的分?jǐn)?shù)應(yīng)該還是原來的91.0。然后演示臟讀。先把會(huì)話A的隔離級(jí)別設(shè)為READ UNCOMMITTED更新后不提交另一個(gè)會(huì)話也設(shè)為READ UNCOMMITTED就能讀到未提交的修改。等會(huì)話A回滾那個(gè)會(huì)話再查就是舊值。這種先讀到后消失的數(shù)據(jù)就是臟讀。不可重復(fù)讀需要在兩個(gè)隔離級(jí)別下對(duì)比。把會(huì)話A設(shè)為READ COMMITTED然后在同一個(gè)事務(wù)里兩次查詢同一條記錄中間讓另一個(gè)會(huì)話修改并提交該記錄第一次讀和第二次讀的值不一樣這就是不可重復(fù)讀。注意在MySQL默認(rèn)的REPEATABLE READ下普通SELECT是快照讀兩次查詢結(jié)果一致所以這個(gè)現(xiàn)象必須在READ COMMITTED下演示?；米x更復(fù)雜一些往往涉及范圍查詢。比如事務(wù)A按條件SELECT ... FOR UPDATE鎖住一部分記錄事務(wù)B插入一條滿足條件的新記錄并提交然后事務(wù)A再查發(fā)現(xiàn)自己鎖住的范圍內(nèi)出現(xiàn)了本不存在的記錄。這種問題通?？块g隙鎖解決但這也是死鎖容易發(fā)生的地方。實(shí)驗(yàn)報(bào)告里只需要記錄現(xiàn)象和結(jié)論并不需要寫太底層的東西。鎖等待的實(shí)驗(yàn)相對(duì)簡(jiǎn)單會(huì)話A執(zhí)行SELECT * FROM sc WHERE s_id 1 FOR UPDATE;不提交會(huì)話B執(zhí)行同一行更新會(huì)一直等待直到超時(shí)。用SHOW ENGINE INNODB STATUS可以看到鎖等待信息。這個(gè)是加分項(xiàng)顯示出你真正操作過并發(fā)場(chǎng)景。4. 常見問題與排查技巧實(shí)錄4.1 查詢結(jié)果出現(xiàn)重復(fù)行統(tǒng)計(jì)類查詢最怕結(jié)果翻倍。有一次我寫“每個(gè)學(xué)生的選課數(shù)”用了三表連接結(jié)果學(xué)生選了兩門課就出現(xiàn)兩行COUNT數(shù)變成了2、4看起來完全不對(duì)。后來我單獨(dú)對(duì)sc表和student表分組查詢?cè)賹?duì)比JOIN后的明細(xì)才發(fā)現(xiàn)問題出在course表被錯(cuò)誤引入。排查重復(fù)行的思路很簡(jiǎn)單先去掉非必要的表確定最小表集再逐步加表觀察行數(shù)變化。如果最終確實(shí)需要多表再考慮使用DISTINCT或者子查詢來消除重復(fù)但根本解法是理清業(yè)務(wù)關(guān)系和連接條件。4.2 GROUP BY報(bào)錯(cuò) only_full_group_by很多同學(xué)在MySQL 5.7以上環(huán)境運(yùn)行這樣的SQL會(huì)報(bào)錯(cuò)SELECT s_name, COUNT(*) FROM sc JOIN student ON sc.s_id student.s_id GROUP BY student.s_id;因?yàn)閟_name沒有被包含在GROUP BY中。嚴(yán)格模式下選擇列表里出現(xiàn)的非聚合列必須被GROUP BY子句包含或者被聚合函數(shù)包裹。如果確定s_name函數(shù)依賴于s_id即一個(gè)s_id只會(huì)有一個(gè)s_name可以加ANY_VALUE(s_name)繞過。但是實(shí)驗(yàn)報(bào)告不建議用這個(gè)手法因?yàn)槿菀籽谏w邏輯問題。最穩(wěn)妥的寫法是把s_name也寫進(jìn)GROUP BY代價(jià)是多一點(diǎn)分組開銷但語義清楚。4.3 NOT IN 永遠(yuǎn)查不出結(jié)果之前說過NOT IN和NULL的問題。實(shí)際排查時(shí)可以先運(yùn)行子查詢單獨(dú)檢查是否有NULLSELECT s_id FROM sc WHERE s_id IS NULL;如果有NULL就會(huì)導(dǎo)致整個(gè)NOT IN為假。最好的替代方案是NOT EXISTS。這個(gè)坑在很多生產(chǎn)環(huán)境的SQL里也會(huì)遇到實(shí)驗(yàn)里能自己發(fā)現(xiàn)并解決說明你對(duì)三值邏輯的理解是到位的。4.4 事務(wù)實(shí)驗(yàn)中的鎖等待超時(shí)事務(wù)實(shí)驗(yàn)中最常見的問題是“Lock wait timeout exceeded”。遇到這種提示先別急著殺進(jìn)程。用SHOW PROCESSLIST;看看哪個(gè)連接在等鎖再用SHOW ENGINE INNODB STATUS;查看最近的死鎖或鎖等待詳情。我遇到過一次死鎖兩個(gè)事務(wù)一個(gè)先更新student再更新sc另一個(gè)先更新sc再更新studentInnoDB檢測(cè)到了死鎖并回滾了其中一個(gè)事務(wù)。解決方式就是讓所有事務(wù)按照相同的表順序加鎖保持事務(wù)短小不在事務(wù)里做不必要的外部查詢。另外鎖等待超時(shí)時(shí)間默認(rèn)50秒可以在客戶端設(shè)置成更短比如5秒這樣實(shí)驗(yàn)演示時(shí)不用等那么久。命令是SET innodb_lock_wait_timeout 5;這個(gè)參數(shù)只對(duì)當(dāng)前會(huì)話生效做演示足夠了。4.5 字符集和排序規(guī)則導(dǎo)致的比較問題中文數(shù)據(jù)最容易踩字符集坑。兩個(gè)表的字符集不一致連接時(shí)可能報(bào)錯(cuò)或者比較時(shí)不相等。建議建表時(shí)都明確指定CHARSETutf8mb4排序規(guī)則統(tǒng)一用utf8mb4_general_ci或utf8mb4_unicode_ci。另外字符串含有尾隨空格也會(huì)造成比較結(jié)果異常用LIKE或者TRIM()處理。實(shí)驗(yàn)里如果遇到“明明數(shù)據(jù)一樣卻查不出來”優(yōu)先檢查字符集和空格。數(shù)據(jù)庫系統(tǒng)實(shí)驗(yàn)三做完之后我個(gè)人最大的收獲不是背會(huì)了多少SQL語法而是養(yǎng)成了一個(gè)習(xí)慣每次查詢都先EXPLAIN每次結(jié)果不對(duì)都先懷疑連接條件。這個(gè)習(xí)慣讓我在后來的很多項(xiàng)目里避免了不少線上事故。如果你正在做這個(gè)實(shí)驗(yàn)請(qǐng)一定自己親手把慢查詢調(diào)優(yōu)一遍不要直接復(fù)制別人的答案。等你看到EXPLAIN里type從ALL變成ref的時(shí)候那種感覺比拿滿分還爽。 數(shù)據(jù)庫系統(tǒng)實(shí)驗(yàn)三實(shí)戰(zhàn)：SQL復(fù)雜查詢、索引優(yōu)化與事務(wù)并發(fā)控制
數(shù)據(jù)庫系統(tǒng)實(shí)驗(yàn)三在很多學(xué)校的人才培養(yǎng)方案里都是一道分水嶺。某高校軟件學(xué)院的這門實(shí)驗(yàn)題目看起來只是幾張表、幾條SQL但真正動(dòng)手后才會(huì)發(fā)現(xiàn)它把關(guān)系代數(shù)、查詢優(yōu)化、事務(wù)控制這些課堂理論全部揉在了一起。這個(gè)實(shí)驗(yàn)?zāi)軒湍愦蛲◤摹皶?huì)寫SQL”到“會(huì)用數(shù)據(jù)庫”的那一步尤其是復(fù)雜查詢和性能分析能力是后續(xù)做應(yīng)用開發(fā)的底層功。適合正在趕實(shí)驗(yàn)報(bào)告的同學(xué)也適合想系統(tǒng)鞏固數(shù)據(jù)庫實(shí)操的人。1. 實(shí)驗(yàn)全貌與設(shè)計(jì)思路拆解1.1 實(shí)驗(yàn)三到底在考查什么數(shù)據(jù)庫系統(tǒng)實(shí)驗(yàn)三通常不會(huì)只讓你做增刪改查而是把復(fù)雜查詢、索引、事務(wù)三個(gè)模塊串起來。先說復(fù)雜查詢一般會(huì)通過學(xué)生選課這種經(jīng)典場(chǎng)景要求你完成多表連接、子查詢、分組聚合、集合運(yùn)算甚至要處理“選修了全部課程”這類關(guān)系代數(shù)除法問題。這部分考查的是你能否把自然語言描述的業(yè)務(wù)需求準(zhǔn)確翻譯成沒有邏輯漏洞的SQL語句。索引模塊是對(duì)查詢性能的進(jìn)一步要求。你不僅要會(huì)寫查詢還要能判斷一條慢查詢?yōu)槭裁绰⑶彝ㄟ^建立索引、改寫SQL來讓它變快。這一步會(huì)涉及執(zhí)行計(jì)劃分析很多同學(xué)第一次看到EXPLAIN的輸出會(huì)懵但實(shí)際上它只是把優(yōu)化器的決策攤開給你看。事務(wù)與并發(fā)控制是實(shí)驗(yàn)三里最容易被忽視的部分因?yàn)椴幌袷恰皩懸粭lSQL出結(jié)果”那么直接。它需要你打開多個(gè)數(shù)據(jù)庫會(huì)話去制造臟讀、不可重復(fù)讀、幻讀再通過隔離級(jí)別或加鎖來規(guī)避。這個(gè)過程看起來像在做實(shí)驗(yàn)演示但恰恰是理解數(shù)據(jù)庫一致性的關(guān)鍵。設(shè)計(jì)者把這些環(huán)節(jié)放在實(shí)驗(yàn)三也是循序漸進(jìn)的。前面實(shí)驗(yàn)要求你建表、插數(shù)據(jù)、寫基礎(chǔ)查詢到實(shí)驗(yàn)三才開始模擬真實(shí)業(yè)務(wù)中的“統(tǒng)計(jì)、報(bào)表、并發(fā)更新”等場(chǎng)景。說白了前兩次實(shí)驗(yàn)是教你說單詞實(shí)驗(yàn)三是教你怎么寫句子和段落。1.2 數(shù)據(jù)庫選型與實(shí)驗(yàn)環(huán)境準(zhǔn)備實(shí)驗(yàn)本身對(duì)數(shù)據(jù)庫沒有硬性限制但選對(duì)工具能省很多事。我個(gè)人建議使用MySQL 8.0原因是它語法通用、網(wǎng)上的資料和踩坑記錄最多而且EXPLAIN輸出清晰默認(rèn)隔離級(jí)別是REPEATABLE READ很適合做事務(wù)實(shí)驗(yàn)。如果你學(xué)校機(jī)房預(yù)裝的是SQL Server或PostgreSQL問題也不大核心SQL語法基本一致只有少量差異。安裝MySQL時(shí)建議把默認(rèn)字符集設(shè)置為utf8mb4避免后面插入中文數(shù)據(jù)出現(xiàn)亂碼。到這一步很多同學(xué)會(huì)疑惑界面工具不是更直觀嗎我的態(tài)度是DataGrip、DBeaver這些工具可以用來看表結(jié)構(gòu)和執(zhí)行計(jì)劃的圖形化展示但實(shí)驗(yàn)報(bào)告里要求的是能直接運(yùn)行的SQL文本。所以至少一半的調(diào)試過程要在命令行或代碼編輯器里完成避免過度依賴自動(dòng)補(bǔ)全。我在本機(jī)一般會(huì)同時(shí)準(zhǔn)備兩種環(huán)境一個(gè)MySQL 8.0實(shí)例用來跑查詢一個(gè)Navicat或DBeaver連接作為輔助工具。要注意的是做事務(wù)實(shí)驗(yàn)時(shí)需要開多個(gè)會(huì)話窗口圖形工具通常也支持多標(biāo)簽頁不一定非要開多個(gè)命令行窗口。2. 核心細(xì)節(jié)解析與實(shí)操要點(diǎn)2.1 多表連接別讓結(jié)果集失控多表連接最容易犯的錯(cuò)就是忘記寫連接條件導(dǎo)致笛卡爾積。比如三張表學(xué)生表20條記錄、課程表10條、選課表50條如果不加任何WHERE或ON條件直接JOIN結(jié)果集會(huì)有20乘以10乘以50等于一萬行。我見過有同學(xué)統(tǒng)計(jì)“每門課的平均分”就是因?yàn)镴OIN條件寫錯(cuò)把每一個(gè)學(xué)生和每一門課都匹配了一遍最后平均值完全不對(duì)。寫多表連接時(shí)要形成肌肉記憶先確認(rèn)需要從哪些表取字段再畫出表之間的關(guān)聯(lián)鍵最后在ON子句里寫等值條件。對(duì)于外連接要明確主表是哪一張。LEFT JOIN會(huì)保留左邊表的全部行RIGHT JOIN反之。自連接是難點(diǎn)本質(zhì)上是給同一張表起兩個(gè)別名然后當(dāng)成兩張不同的表來操作。比如查詢“年齡比某位同學(xué)大的所有學(xué)生”就可以把student表與自己連接。還有一個(gè)細(xì)節(jié)外連接加上WHERE條件時(shí)容易踩坑。舉個(gè)例子你希望列出所有學(xué)生以及他們選修某門課的成績(jī)?nèi)绻萌缦聦懛⊿ELECT s.s_name, sc.score FROM student s LEFT JOIN sc ON s.s_id sc.s_id WHERE sc.c_id 1;表面看沒問題但如果某個(gè)學(xué)生沒有選任何課sc.c_id是NULLWHERE條件就把這行過濾掉了最終效果跟內(nèi)連接一樣。正確做法是把篩選放在子查詢里或者直接作為一個(gè)派生表SELECT s.s_name, t.score FROM student s LEFT JOIN ( SELECT s_id, score FROM sc WHERE c_id 1 ) t ON s.s_id t.s_id;這種細(xì)節(jié)在實(shí)驗(yàn)報(bào)告里非常加分因?yàn)樗w現(xiàn)的是對(duì)SQL邏輯的理解而不是只會(huì)調(diào)API。2.2 子查詢與集合運(yùn)算的易錯(cuò)點(diǎn)子查詢可以出現(xiàn)在SELECT子句、FROM子句和WHERE子句每種位置都有各自規(guī)則。SELECT子句里的標(biāo)量子查詢必須返回一行一列否則直接報(bào)錯(cuò)FROM子句里的派生表必須有別名WHERE子句里的IN和EXISTS要重點(diǎn)辨析。很多人以為IN和EXISTS只是寫法不同性能差不多但實(shí)驗(yàn)里要寫出合理的查詢就必須知道它們的差異。IN適合驅(qū)動(dòng)表數(shù)據(jù)量小、子查詢結(jié)果集也小的場(chǎng)景EXISTS更像循環(huán)匹配適合外表行數(shù)多、子查詢走索引的場(chǎng)景?，F(xiàn)代優(yōu)化器有時(shí)候會(huì)互相改寫但你不能依賴這一點(diǎn)。更關(guān)鍵的是NULL的三值邏輯陷阱。以“查詢沒有選修任何課程的學(xué)生”為例如果用NOT INSELECT s_id, s_name FROM student WHERE s_id NOT IN (SELECT s_id FROM sc);如果sc表中的s_id本身就是外鍵一般不會(huì)有NULL但如果子查詢里混入了NULLNOT IN會(huì)返回空集。這是因?yàn)镾QL中NULL既不是真也不是假而是未知導(dǎo)致最終結(jié)果無法確定為真。穩(wěn)妥方案是用NOT EXISTSSELECT s_id, s_name FROM student s WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.s_id s.s_id );在實(shí)驗(yàn)報(bào)告里可以把這兩種寫法及其結(jié)果都展示出來加上一句“為避免NULL對(duì)三值邏輯的影響推薦使用NOT EXISTS”老師會(huì)知道你真正踩過這個(gè)坑。集合運(yùn)算方面UNION會(huì)去重UNION ALL不會(huì)去重且性能更好。如果題目只是要求合并兩個(gè)結(jié)果集且明確要包含重復(fù)項(xiàng)就必須用UNION ALL。還有一個(gè)容易忽略的點(diǎn)集合運(yùn)算要求兩個(gè)查詢的列數(shù)相同對(duì)應(yīng)列的數(shù)據(jù)類型也要兼容不然會(huì)報(bào)錯(cuò)。2.3 索引優(yōu)化不要盲目建索引索引優(yōu)化的核心不是“建了索引查詢就會(huì)快”而是要理解B樹索引能加速什么。簡(jiǎn)單來說B樹可以讓數(shù)據(jù)庫快速定位到目標(biāo)數(shù)據(jù)而不用整表掃。WHERE中的等值條件、范圍條件、JOIN的關(guān)聯(lián)字段、ORDER BY和GROUP BY的字段都是適合建索引的候選。但索引不是越多越好。每次插入、更新、刪除時(shí)數(shù)據(jù)庫都要同步維護(hù)索引索引太多寫入性能就會(huì)變差。此外如果字段區(qū)分度太低比如性別只有“男”“女”建了索引也可能不被優(yōu)化器使用因?yàn)樗阆聛砣頀呙韪鼊澦?。?shí)驗(yàn)里通常要求你對(duì)比加索引前后的掃描行數(shù)和執(zhí)行時(shí)間所以數(shù)據(jù)量要足夠大。只插幾十行的話性能差異根本看不出來。組合索引要遵循最左前綴原則。比如建立索引(a, b, c)它可以加速包含a條件、包含a和b條件、包含a和b和c條件的查詢但不會(huì)加速只包含b或c的查詢。這好比查字典你只能按照字母順序從前到后翻跳過第一個(gè)字母直接查第二個(gè)字母是行不通的。EXPLAIN是最重要的調(diào)試工具。執(zhí)行EXPLAIN SELECT ...后重點(diǎn)看type列從好到差大致是const、eq_ref、ref、range、index、ALL。還有key列表示實(shí)際用到的索引rows列表示預(yù)估掃描行數(shù)。實(shí)驗(yàn)報(bào)告里可以貼上優(yōu)化前后的EXPLAIN輸出再用表格對(duì)比比寫一大段文字更直觀。3. 實(shí)操過程與核心環(huán)節(jié)實(shí)現(xiàn)3.1 選課系統(tǒng)的建表與數(shù)據(jù)準(zhǔn)備下面我們用一個(gè)可復(fù)現(xiàn)的模擬項(xiàng)目X來演示。表結(jié)構(gòu)設(shè)計(jì)如下學(xué)生表student、課程表course、選課表sc。選課表以(s_id, c_id)作為復(fù)合主鍵并設(shè)置外鍵保證完整性。成績(jī)字段score允許為空模擬“還沒考試”的情況。CREATE DATABASE IF NOT EXISTS db_lab3 CHARACTER SET utf8mb4; USE db_lab3; CREATE TABLE student ( s_id INT PRIMARY KEY AUTO_INCREMENT, s_name VARCHAR(50) NOT NULL, s_gender ENUM(M,F), s_age TINYINT, s_major VARCHAR(50) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE course ( c_id INT PRIMARY KEY AUTO_INCREMENT, c_name VARCHAR(100) NOT NULL, c_credit DECIMAL(3,1) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; CREATE TABLE sc ( s_id INT NOT NULL, c_id INT NOT NULL, score DECIMAL(4,1), PRIMARY KEY (s_id, c_id), CONSTRAINT fk_sc_student FOREIGN KEY (s_id) REFERENCES student(s_id), CONSTRAINT fk_sc_course FOREIGN KEY (c_id) REFERENCES course(c_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;插入示例數(shù)據(jù)時(shí)要注意外鍵順序先插入student和course再插入sc。如果偷懶用工具可視化操作后面做外鍵級(jí)聯(lián)時(shí)會(huì)很麻煩所以建議手工維護(hù)。INSERT INTO student (s_name, s_gender, s_age, s_major) VALUES (張同,M, 20, 軟件工程), (李茜,F, 21, 數(shù)據(jù)科學(xué)), (王磊,M, 22, 人工智能), (趙欣,F, 20, 網(wǎng)絡(luò)工程), (陳宇,M, 21, 軟件工程); INSERT INTO course (c_name, c_credit) VALUES (數(shù)據(jù)庫系統(tǒng), 4.0), (操作系統(tǒng), 3.5), (計(jì)算機(jī)網(wǎng)絡(luò), 3.0), (數(shù)據(jù)結(jié)構(gòu), 4.0), (軟件工程, 2.5); INSERT INTO sc (s_id, c_id, score) VALUES (1, 1, 91.0), (1, 2, 82.5), (2, 1, 88.0), (2, 3, 95.0), (3, 1, 76.0), (3, 4, 68.5), (4, 5, NULL), (5, 1, 79.0), (5, 2, 86.0);實(shí)際實(shí)驗(yàn)時(shí)這些數(shù)據(jù)遠(yuǎn)遠(yuǎn)不夠看索引效果。我當(dāng)時(shí)寫了一個(gè)存儲(chǔ)過程往sc表里插入了上萬條隨機(jī)記錄再把student和course也擴(kuò)充到幾百行這樣EXPLAIN的輸出才有說服力。存儲(chǔ)過程不是實(shí)驗(yàn)重點(diǎn)但值得寫在報(bào)告“附加工作”里。3.2 典型查詢題的SQL實(shí)現(xiàn)與解釋第一題查詢每個(gè)學(xué)生的選課數(shù)和平均分。注意使用LEFT JOIN保證沒選課的學(xué)生也能顯示出來平均分如果NULL就顯示為0SELECT s.s_id, s.s_name, COUNT(sc.c_id) AS course_num, ROUND(AVG(COALESCE(sc.score, 0)), 2) AS avg_score FROM student s LEFT JOIN sc ON s.s_id sc.s_id GROUP BY s.s_id, s.s_name;這里有一個(gè)細(xì)節(jié)COUNT(sc.c_id)只統(tǒng)計(jì)非NULL的選課記錄所以沒選課的學(xué)生course_num會(huì)是0而AVG(COALESCE(sc.score, 0))會(huì)把NULL當(dāng)成0分計(jì)算。這個(gè)寫法在業(yè)務(wù)上是否合理需要看題目要求但實(shí)驗(yàn)報(bào)告里可以解釋清楚。第二題查詢每門課的選課人數(shù)、最高分、最低分。同樣用LEFT JOIN列出無人選的課程SELECT c.c_id, c.c_name, COUNT(sc.s_id) AS stu_num, MAX(sc.score) AS max_score, MIN(sc.score) AS min_score FROM course c LEFT JOIN sc ON c.c_id sc.c_id GROUP BY c.c_id, c.c_name;第三題“查詢選修了數(shù)據(jù)庫系統(tǒng)課程且成績(jī)高于該課程平均分的學(xué)生”。我的第一版SQL是這樣寫的SELECT s.s_id, s.s_name, sc.score FROM student s JOIN sc ON s.s_id sc.s_id JOIN course c ON sc.c_id c.c_id WHERE c.c_name 數(shù)據(jù)庫系統(tǒng) AND sc.score ( SELECT AVG(sc2.score) FROM sc sc2 JOIN course c2 ON sc2.c_id c2.c_id WHERE c2.c_name 數(shù)據(jù)庫系統(tǒng) );這個(gè)查詢會(huì)先子查詢算出平均分再比較每條記錄。如果數(shù)據(jù)量大可以考慮用窗口函數(shù)但實(shí)驗(yàn)要求基礎(chǔ)SQL的話子查詢版本完全夠用。窗口函數(shù)版本是這樣SELECT s.s_id, s.s_name, sc.score FROM ( SELECT s_id, c_id, score, AVG(score) OVER (PARTITION BY c_id) AS avg_score FROM sc JOIN course ON sc.c_id course.c_id WHERE course.c_name 數(shù)據(jù)庫系統(tǒng) ) t JOIN student s ON t.s_id s.s_id WHERE t.score t.avg_score;兩個(gè)版本都能跑但是窗口函數(shù)可讀性更好在MySQL 8.0中性能也不錯(cuò)。實(shí)驗(yàn)報(bào)告里我建議主推子查詢版本并把窗口函數(shù)作為“進(jìn)一步優(yōu)化”提一下顯得思考全面。第四題是“查詢選修了全部課程的學(xué)生”。這是關(guān)系代數(shù)里的除法問題。一個(gè)常用解法是統(tǒng)計(jì)課程總數(shù)再按學(xué)生分組統(tǒng)計(jì)去重選課數(shù)兩者相等SELECT s.s_id, s.s_name FROM student s JOIN sc ON s.s_id sc.s_id GROUP BY s.s_id, s.s_name HAVING COUNT(DISTINCT sc.c_id) (SELECT COUNT(*) FROM course);注意COUNT(DISTINCT sc.c_id)要去重防止同一個(gè)人重復(fù)選修同一門課導(dǎo)致計(jì)數(shù)虛高。還有另一種“雙重NOT EXISTS”寫法可以解決零除問題但做實(shí)驗(yàn)時(shí)這種不使用聚合的寫法更經(jīng)典SELECT s.s_id, s.s_name FROM student s WHERE NOT EXISTS ( SELECT c.c_id FROM course c WHERE NOT EXISTS ( SELECT 1 FROM sc WHERE sc.s_id s.s_id AND sc.c_id c.c_id ) );這個(gè)邏輯需要一點(diǎn)耐心理解內(nèi)層NOT EXISTS選出“該學(xué)生沒選的課程”外層NOT EXISTS則保證不存在這樣的課程也就是學(xué)生選了所有課。3.3 索引優(yōu)化的完整對(duì)比索引優(yōu)化題通常會(huì)給一條慢查詢要求你通過加索引或改寫SQL來提升性能。我們可以拿這個(gè)查詢做例子EXPLAIN SELECT * FROM sc WHERE score BETWEEN 70 AND 90;沒有索引時(shí)EXPLAIN的type是ALLrows是全部數(shù)據(jù)行。加上普通索引ALTER TABLE sc ADD INDEX idx_score (score);再執(zhí)行EXPLAINtype會(huì)變成rangerows會(huì)明顯減少。但要注意如果數(shù)據(jù)量不大或者范圍覆蓋的數(shù)據(jù)占比太高優(yōu)化器可能仍然選擇全表掃描。這不代表索引沒用而是優(yōu)化器認(rèn)為訪問整表比走索引回表更快。所以在實(shí)驗(yàn)報(bào)告里“沒有優(yōu)化效果”也可能是一條有效結(jié)論關(guān)鍵是你能解釋清楚為什么。還有一個(gè)常見操作在student表的s_major字段上建索引來優(yōu)化“查詢某專業(yè)所有學(xué)生的選課信息”這類查詢。這里有個(gè)陷阱如果s_major的區(qū)分度不高索引效果可能不明顯。我建議用組合索引比如(s_major, s_id)這樣既能快速定位專業(yè)又能通過s_id再連接選課表。這個(gè)選擇背后的原理就是盡量讓索引覆蓋查詢條件減少回表次數(shù)。如果還想深入可以嘗試EXPLAIN ANALYZEMySQL 8.0.18以后支持會(huì)輸出實(shí)際執(zhí)行時(shí)間。但在提交實(shí)驗(yàn)報(bào)告之前建議先確認(rèn)老師是否接受這種高級(jí)工具因?yàn)橛行┰u(píng)分標(biāo)準(zhǔn)只要EXPLAIN輸出。3.4 事務(wù)與并發(fā)控制實(shí)驗(yàn)事務(wù)部分需要開多個(gè)會(huì)話窗口。先演示最基礎(chǔ)的事務(wù)提交和回滾-- 會(huì)話A START TRANSACTION; UPDATE sc SET score 99 WHERE s_id 1 AND c_id 1; ROLLBACK; SELECT score FROM sc WHERE s_id 1 AND c_id 1;如果ROLLBACK生效查出來的分?jǐn)?shù)應(yīng)該還是原來的91.0。然后演示臟讀。先把會(huì)話A的隔離級(jí)別設(shè)為READ UNCOMMITTED更新后不提交另一個(gè)會(huì)話也設(shè)為READ UNCOMMITTED就能讀到未提交的修改。等會(huì)話A回滾那個(gè)會(huì)話再查就是舊值。這種先讀到后消失的數(shù)據(jù)就是臟讀。不可重復(fù)讀需要在兩個(gè)隔離級(jí)別下對(duì)比。把會(huì)話A設(shè)為READ COMMITTED然后在同一個(gè)事務(wù)里兩次查詢同一條記錄中間讓另一個(gè)會(huì)話修改并提交該記錄第一次讀和第二次讀的值不一樣這就是不可重復(fù)讀。注意在MySQL默認(rèn)的REPEATABLE READ下普通SELECT是快照讀兩次查詢結(jié)果一致所以這個(gè)現(xiàn)象必須在READ COMMITTED下演示?；米x更復(fù)雜一些往往涉及范圍查詢。比如事務(wù)A按條件SELECT ... FOR UPDATE鎖住一部分記錄事務(wù)B插入一條滿足條件的新記錄并提交然后事務(wù)A再查發(fā)現(xiàn)自己鎖住的范圍內(nèi)出現(xiàn)了本不存在的記錄。這種問題通?？块g隙鎖解決但這也是死鎖容易發(fā)生的地方。實(shí)驗(yàn)報(bào)告里只需要記錄現(xiàn)象和結(jié)論并不需要寫太底層的東西。鎖等待的實(shí)驗(yàn)相對(duì)簡(jiǎn)單會(huì)話A執(zhí)行SELECT * FROM sc WHERE s_id 1 FOR UPDATE;不提交會(huì)話B執(zhí)行同一行更新會(huì)一直等待直到超時(shí)。用SHOW ENGINE INNODB STATUS可以看到鎖等待信息。這個(gè)是加分項(xiàng)顯示出你真正操作過并發(fā)場(chǎng)景。4. 常見問題與排查技巧實(shí)錄4.1 查詢結(jié)果出現(xiàn)重復(fù)行統(tǒng)計(jì)類查詢最怕結(jié)果翻倍。有一次我寫“每個(gè)學(xué)生的選課數(shù)”用了三表連接結(jié)果學(xué)生選了兩門課就出現(xiàn)兩行COUNT數(shù)變成了2、4看起來完全不對(duì)。后來我單獨(dú)對(duì)sc表和student表分組查詢?cè)賹?duì)比JOIN后的明細(xì)才發(fā)現(xiàn)問題出在course表被錯(cuò)誤引入。排查重復(fù)行的思路很簡(jiǎn)單先去掉非必要的表確定最小表集再逐步加表觀察行數(shù)變化。如果最終確實(shí)需要多表再考慮使用DISTINCT或者子查詢來消除重復(fù)但根本解法是理清業(yè)務(wù)關(guān)系和連接條件。4.2 GROUP BY報(bào)錯(cuò) only_full_group_by很多同學(xué)在MySQL 5.7以上環(huán)境運(yùn)行這樣的SQL會(huì)報(bào)錯(cuò)SELECT s_name, COUNT(*) FROM sc JOIN student ON sc.s_id student.s_id GROUP BY student.s_id;因?yàn)閟_name沒有被包含在GROUP BY中。嚴(yán)格模式下選擇列表里出現(xiàn)的非聚合列必須被GROUP BY子句包含或者被聚合函數(shù)包裹。如果確定s_name函數(shù)依賴于s_id即一個(gè)s_id只會(huì)有一個(gè)s_name可以加ANY_VALUE(s_name)繞過。但是實(shí)驗(yàn)報(bào)告不建議用這個(gè)手法因?yàn)槿菀籽谏w邏輯問題。最穩(wěn)妥的寫法是把s_name也寫進(jìn)GROUP BY代價(jià)是多一點(diǎn)分組開銷但語義清楚。4.3 NOT IN 永遠(yuǎn)查不出結(jié)果之前說過NOT IN和NULL的問題。實(shí)際排查時(shí)可以先運(yùn)行子查詢單獨(dú)檢查是否有NULLSELECT s_id FROM sc WHERE s_id IS NULL;如果有NULL就會(huì)導(dǎo)致整個(gè)NOT IN為假。最好的替代方案是NOT EXISTS。這個(gè)坑在很多生產(chǎn)環(huán)境的SQL里也會(huì)遇到實(shí)驗(yàn)里能自己發(fā)現(xiàn)并解決說明你對(duì)三值邏輯的理解是到位的。4.4 事務(wù)實(shí)驗(yàn)中的鎖等待超時(shí)事務(wù)實(shí)驗(yàn)中最常見的問題是“Lock wait timeout exceeded”。遇到這種提示先別急著殺進(jìn)程。用SHOW PROCESSLIST;看看哪個(gè)連接在等鎖再用SHOW ENGINE INNODB STATUS;查看最近的死鎖或鎖等待詳情。我遇到過一次死鎖兩個(gè)事務(wù)一個(gè)先更新student再更新sc另一個(gè)先更新sc再更新studentInnoDB檢測(cè)到了死鎖并回滾了其中一個(gè)事務(wù)。解決方式就是讓所有事務(wù)按照相同的表順序加鎖保持事務(wù)短小不在事務(wù)里做不必要的外部查詢。另外鎖等待超時(shí)時(shí)間默認(rèn)50秒可以在客戶端設(shè)置成更短比如5秒這樣實(shí)驗(yàn)演示時(shí)不用等那么久。命令是SET innodb_lock_wait_timeout 5;這個(gè)參數(shù)只對(duì)當(dāng)前會(huì)話生效做演示足夠了。4.5 字符集和排序規(guī)則導(dǎo)致的比較問題中文數(shù)據(jù)最容易踩字符集坑。兩個(gè)表的字符集不一致連接時(shí)可能報(bào)錯(cuò)或者比較時(shí)不相等。建議建表時(shí)都明確指定CHARSETutf8mb4排序規(guī)則統(tǒng)一用utf8mb4_general_ci或utf8mb4_unicode_ci。另外字符串含有尾隨空格也會(huì)造成比較結(jié)果異常用LIKE或者TRIM()處理。實(shí)驗(yàn)里如果遇到“明明數(shù)據(jù)一樣卻查不出來”優(yōu)先檢查字符集和空格。數(shù)據(jù)庫系統(tǒng)實(shí)驗(yàn)三做完之后我個(gè)人最大的收獲不是背會(huì)了多少SQL語法而是養(yǎng)成了一個(gè)習(xí)慣每次查詢都先EXPLAIN每次結(jié)果不對(duì)都先懷疑連接條件。這個(gè)習(xí)慣讓我在后來的很多項(xiàng)目里避免了不少線上事故。如果你正在做這個(gè)實(shí)驗(yàn)請(qǐng)一定自己親手把慢查詢調(diào)優(yōu)一遍不要直接復(fù)制別人的答案。等你看到EXPLAIN里type從ALL變成ref的時(shí)候那種感覺比拿滿分還爽。