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