做項(xiàng)目的人對(duì)“管理系統(tǒng)”三個(gè)字應(yīng)該都不陌生學(xué)生成績(jī)管理系統(tǒng)MySQL更是課程設(shè)計(jì)、畢業(yè)設(shè)計(jì)里的常客。我這次做的這套系統(tǒng)表面上看就是記錄“哪個(gè)學(xué)生哪門(mén)課考了多少分”但真正做下來(lái)你會(huì)發(fā)現(xiàn)它幾乎能把MySQL的核心功能都串一遍三張表的關(guān)系建模、增刪改查、排序分頁(yè)、聚合統(tǒng)計(jì)、事務(wù)、存儲(chǔ)過(guò)程、視圖、索引優(yōu)化、權(quán)限管理、部署排錯(cuò)一個(gè)都不少。這篇文章我就以這個(gè)項(xiàng)目為載體把從建庫(kù)建表到優(yōu)化排錯(cuò)的全過(guò)程整理出來(lái)。它適合兩類(lèi)人一類(lèi)是準(zhǔn)備交課程設(shè)計(jì)的學(xué)生可以直接照著建表、抄SQL另一類(lèi)是剛學(xué)完MySQL基礎(chǔ)、想找個(gè)小項(xiàng)目練手的開(kāi)發(fā)者跟著走一遍對(duì)數(shù)據(jù)庫(kù)的理解會(huì)扎實(shí)很多。1. 先設(shè)計(jì)數(shù)據(jù)庫(kù)學(xué)生成績(jī)系統(tǒng)到底需要幾張表1.1 需求梳理成績(jī)系統(tǒng)核心業(yè)務(wù)就三件事我見(jiàn)過(guò)不少同學(xué)拿到“學(xué)生成績(jī)管理系統(tǒng)”這個(gè)題目后第一反應(yīng)就是打開(kāi)Navicat新建一張表把所有字段堆進(jìn)去學(xué)生姓名、學(xué)號(hào)、課程、成績(jī)、老師、班級(jí)……做出來(lái)的東西能交差但一追問(wèn)“怎么統(tǒng)計(jì)某門(mén)課的平均分”就開(kāi)始卡殼加字段、拆表、改代碼返工成本極高。其實(shí)冷靜下來(lái)想學(xué)生成績(jī)管理系統(tǒng)的業(yè)務(wù)可以拆成這么幾件事管理學(xué)生信息增刪改查、管理課程信息增刪改查、錄入成績(jī)、修改成績(jī)、查詢(xún)成績(jī)、統(tǒng)計(jì)成績(jī)平均分、排名、及格率。就這么幾件事根本不需要把表設(shè)計(jì)得天花亂墜。關(guān)鍵在于——學(xué)生和課程是兩類(lèi)獨(dú)立的實(shí)體而成績(jī)是學(xué)生和課程之間的關(guān)系。如果直接把“課程名”寫(xiě)在“學(xué)生表”里那一個(gè)學(xué)生選幾門(mén)課就要在一條記錄里塞幾個(gè)課程字段或者干脆一行一個(gè)學(xué)生一個(gè)分?jǐn)?shù)班里有五十個(gè)學(xué)生每人選五門(mén)課就要新建二百五十行學(xué)生改個(gè)手機(jī)號(hào)就得同步改五條記錄。這種平鋪式的設(shè)計(jì)在數(shù)據(jù)量小的時(shí)候看不出毛病一旦數(shù)據(jù)量上來(lái)維護(hù)成本會(huì)呈指數(shù)上升。關(guān)系型數(shù)據(jù)庫(kù)的核心優(yōu)勢(shì)就是處理實(shí)體與實(shí)體之間的關(guān)系而“學(xué)生成績(jī)管理系統(tǒng)”恰恰是最純正的關(guān)系模型場(chǎng)景學(xué)生是一類(lèi)實(shí)體課程是一類(lèi)實(shí)體成績(jī)記錄的是“哪個(gè)學(xué)生選了哪門(mén)課、考了多少分”。這個(gè)關(guān)系單獨(dú)建一張表就形成了最經(jīng)典的三表結(jié)構(gòu)后續(xù)寫(xiě)任何查詢(xún)都很順暢。1.2 三張核心表的設(shè)計(jì)與字段選擇細(xì)節(jié)說(shuō)具體的。我這次項(xiàng)目的建表語(yǔ)句如下后面每個(gè)字段都值得解釋一下為什么這么選。CREATE DATABASE student_score_system DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_unicode_ci; USE student_score_system; CREATE TABLE student ( id INT PRIMARY KEY AUTO_INCREMENT, student_no VARCHAR(20) NOT NULL UNIQUE COMMENT 學(xué)號(hào), name VARCHAR(50) NOT NULL, gender TINYINT DEFAULT 0 COMMENT 0男 1女, class_name VARCHAR(50), phone VARCHAR(15), created_at DATETIME DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT學(xué)生表; CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT, course_no VARCHAR(20) NOT NULL UNIQUE COMMENT 課程編號(hào), course_name VARCHAR(100) NOT NULL, credit DECIMAL(3,1) DEFAULT 0 COMMENT 學(xué)分, teacher VARCHAR(50), semester VARCHAR(20) COMMENT 開(kāi)課學(xué)期如2025-2026-1 ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT課程表; CREATE TABLE score ( id INT PRIMARY KEY AUTO_INCREMENT, student_id INT NOT NULL, course_id INT NOT NULL, score DECIMAL(5,1) COMMENT 成績(jī)保留1位小數(shù), exam_type VARCHAR(20) DEFAULT 期末 COMMENT 平時(shí)/期中/期末, exam_date DATE, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(id) ON DELETE CASCADE, CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course(id) ON DELETE CASCADE, UNIQUE KEY uk_student_course (student_id, course_id, exam_type) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT成績(jī)表;幾個(gè)容易踩坑的選擇學(xué)生編號(hào)用VARCHAR(20)而不是INT因?yàn)閷W(xué)號(hào)經(jīng)常會(huì)以0開(kāi)頭比如“030125”這種編號(hào)存成INT會(huì)變成30125前導(dǎo)零直接丟失而且學(xué)號(hào)本身不需要做加減運(yùn)算用數(shù)值類(lèi)型沒(méi)有任何好處。手機(jī)號(hào)、課程編號(hào)這類(lèi)字段同理。score用DECIMAL(5,1)不用FLOAT/DOUBLE。浮點(diǎn)數(shù)在二進(jìn)制里存的是一個(gè)近似值0.10.2會(huì)得到0.30000000000000004成績(jī)單里出現(xiàn)這種結(jié)果很容易讓人誤以為系統(tǒng)算錯(cuò)了。DECIMAL是定點(diǎn)數(shù)按十進(jìn)制存儲(chǔ)涉及分?jǐn)?shù)這種要精確計(jì)算的數(shù)據(jù)必須用它。gender用TINYINT而不是VARCHAR。別小看這個(gè)選擇用TINYINT存0/1比用VARCHAR存“男/女”節(jié)省存儲(chǔ)空間查詢(xún)時(shí)判斷也方便展示層再映射成文字。當(dāng)然如果使用范圍非常固定用CHAR(1)存“男”“女”也不是不行但工程上我傾向于用編碼值。1.3 外鍵、唯一約束與字符集的取舍這個(gè)項(xiàng)目的設(shè)計(jì)階段最值得講的是三個(gè)點(diǎn)外鍵要不要加、唯一約束怎么用、字符集怎么選。外鍵在社區(qū)里其實(shí)有兩種聲音。課程設(shè)計(jì)場(chǎng)景我建議加外鍵它把“不能刪除已被引用課程”“不能插入不存在的學(xué)生ID”這類(lèi)規(guī)則固化在數(shù)據(jù)庫(kù)里比業(yè)務(wù)代碼判斷可靠得多。生產(chǎn)環(huán)境高并發(fā)系統(tǒng)反而經(jīng)常不用外鍵因?yàn)橥怄I會(huì)導(dǎo)致每一次插入都要去關(guān)聯(lián)表做一致性檢查在分庫(kù)分表之后外鍵基本沒(méi)法用。所以這不是“加不加”的問(wèn)題而是場(chǎng)景決定方案。我用的ON DELETE CASCADE意思是刪除某個(gè)學(xué)生他的成績(jī)記錄自動(dòng)刪除。這個(gè)行為在實(shí)際使用里非常順手但也有人覺(jué)得危險(xiǎn)——萬(wàn)一誤刪一個(gè)學(xué)生成績(jī)?nèi)扛鴽](méi)了。作為課程設(shè)計(jì)完全沒(méi)問(wèn)題如果站在更嚴(yán)謹(jǐn)?shù)慕嵌瓤梢愿某蒓N DELETE RESTRICT禁止直接刪除有成績(jī)記錄的學(xué)生強(qiáng)制你先處理成績(jī)數(shù)據(jù)。兩種策略各有適用場(chǎng)景關(guān)鍵是你要知道它們有什么區(qū)別。唯一約束uk_student_course (student_id, course_id, exam_type)是我特意加的。沒(méi)有它程序里稍微馬虎一點(diǎn)同一個(gè)學(xué)生同一門(mén)課的期末成績(jī)就可能錄兩遍最后統(tǒng)計(jì)的時(shí)候數(shù)據(jù)翻倍還很不好排查。數(shù)據(jù)庫(kù)層把唯一性卡住再配合后面講的存儲(chǔ)過(guò)程做校驗(yàn)效果就會(huì)好很多。字符集選utf8mb4也是一個(gè)老生常談的問(wèn)題了。MySQL的utf8其實(shí)是utf8mb3最多存3個(gè)字節(jié)像emoji以及一些生僻漢字會(huì)存不進(jìn)去或者變成亂碼。成績(jī)管理系統(tǒng)里學(xué)生姓名出現(xiàn)生僻字是很正常的事所以必須用utf8mb4。排序規(guī)則我用的是utf8mb4_unicode_ci對(duì)大部分場(chǎng)景來(lái)說(shuō)比較合適。2. 成績(jī)?cè)鰟h改查的SQL實(shí)戰(zhàn)從成績(jī)單到統(tǒng)計(jì)報(bào)表2.1 錄入和修改成績(jī)CUD操作必須注意的數(shù)據(jù)校驗(yàn)表和庫(kù)建好之后第一件事就是寫(xiě)最基本的增刪改查。這些SQL看著簡(jiǎn)單但里面有幾個(gè)細(xì)節(jié)會(huì)影響系統(tǒng)的健壯性。成績(jī)錄入的SQL很簡(jiǎn)單INSERT INTO score (student_id, course_id, score, exam_type, exam_date) VALUES (1, 2, 88.5, 期末, 2025-06-30);但我還建議加上分?jǐn)?shù)范圍的數(shù)據(jù)庫(kù)約束這樣在SQL層就擋掉了不必要的臟數(shù)據(jù)。MySQL 8.0.16以上版本支持真正強(qiáng)制的CHECK約束ALTER TABLE score ADD CONSTRAINT chk_score_range CHECK (score 0 AND score 100);如果你的項(xiàng)目用的是8.0以上版本建議加上這個(gè)約束。加了之后你寫(xiě)INSERT語(yǔ)句插入120分MySQL直接報(bào)錯(cuò)不用等應(yīng)用代碼走完才發(fā)現(xiàn)。5.7及以下版本只是解析語(yǔ)法但不強(qiáng)制執(zhí)行這點(diǎn)要注意。修改成績(jī)用UPDATE注意一定要帶WHERE條件。這句廢話(huà)幾乎每個(gè)踩坑的人都會(huì)聽(tīng)到但依然很多人犯UPDATE score SET score 90 WHERE student_id 1 AND course_id 2 AND exam_type 期末;如果不帶WHERE就是把整張表所有成績(jī)都改成90了。這是個(gè)非常經(jīng)典的“生產(chǎn)事故”我在后面問(wèn)題排查部分會(huì)再提。刪除成績(jī)也類(lèi)似DELETE FROM score WHERE id 10務(wù)必確認(rèn)WHERE條件。實(shí)際業(yè)務(wù)里我更推薦邏輯刪除也就是加一個(gè)deleted字段做標(biāo)記而不是物理刪行——雖然對(duì)學(xué)生成績(jī)管理系統(tǒng)這種場(chǎng)景沒(méi)那么嚴(yán)格but這是項(xiàng)目里體現(xiàn)專(zhuān)業(yè)度的小細(xì)節(jié)。2.2 查詢(xún)與排序ORDER BY的各種坑查詢(xún)是最能體現(xiàn)SQL功力的地方。按成績(jī)從高到低排列一條SQL就能搞定SELECT student_id, course_id, score FROM score WHERE course_id 2 ORDER BY score DESC;這里要解釋一下ORDER BY的工作原理。MySQL在執(zhí)行沒(méi)有索引的排序時(shí)會(huì)把所有滿(mǎn)足條件的行讀出來(lái)放到sort buffer里做排序數(shù)據(jù)量一大就會(huì)出現(xiàn)filesort。如果查詢(xún)條件上有合適的索引MySQL可能直接按索引順序讀取避免額外的排序開(kāi)銷(xiāo)也就不會(huì)有filesort。這部分細(xì)節(jié)在后面性能優(yōu)化章節(jié)值得展開(kāi)。排序還有個(gè)實(shí)際的大坑成績(jī)字段是DECIMAL排序沒(méi)問(wèn)題但如果有人當(dāng)初把成績(jī)存成了VARCHAR排序結(jié)果會(huì)非常詭異比如90會(huì)排在100后面因?yàn)樽址判虬醋值湫虮容^100 9。這是把成績(jī)存成文本的經(jīng)典惡果建表的時(shí)候用對(duì)類(lèi)型能從源頭避開(kāi)。順便回答一個(gè)經(jīng)常被問(wèn)到的問(wèn)題OR能不能和DISTINCT一起用比如SELECT DISTINCT student_id FROM score WHERE course_id 1 OR course_id 2這個(gè)OR不會(huì)破壞DISTINCT的行為它作用于最終結(jié)果集。但真正的隱患是OR可能會(huì)讓某些索引失效尤其兩個(gè)條件不在同一個(gè)聯(lián)合索引里時(shí)MySQL容易退化成全表掃描——這個(gè)問(wèn)題我放到4.1詳細(xì)說(shuō)。2.3 多表聯(lián)查一張完整成績(jī)單的SQL寫(xiě)法成績(jī)管理系統(tǒng)的報(bào)表頁(yè)通常需要把學(xué)生的姓名、班級(jí)、課程名、學(xué)分、成績(jī)一起展示出來(lái)。這就是典型的多表JOIN。實(shí)際項(xiàng)目中一鍵生成某個(gè)班的成績(jī)單可以這么寫(xiě)SELECT s.student_no, s.name AS student_name, s.class_name, c.course_no, c.course_name, c.credit, sc.score, CASE WHEN sc.score 90 THEN 優(yōu)秀 WHEN sc.score 80 THEN 良好 WHEN sc.score 70 THEN 中等 WHEN sc.score 60 THEN 及格 ELSE 不及格 END AS grade_level FROM score sc JOIN student s ON sc.student_id s.id JOIN course c ON sc.course_id c.id WHERE s.class_name 計(jì)科2301 ORDER BY sc.score DESC;INNER JOIN在這里就夠了它只返回“有成績(jī)記錄”的行。如果還想把“沒(méi)考試”的學(xué)生也查出來(lái)就要用LEFT JOIN比如SELECT s.name, c.course_name, sc.score FROM student s CROSS JOIN course c LEFT JOIN score sc ON sc.student_id s.id AND sc.course_id c.id WHERE c.course_no CS101;這個(gè)查詢(xún)先把學(xué)生和課程做笛卡爾積然后通過(guò)LEFT JOIN去匹配成績(jī)沒(méi)考試的那些行score會(huì)是NULL正好表示缺考。這類(lèi)查詢(xún)?cè)凇翱记诖_認(rèn)”“查誰(shuí)沒(méi)交卷”的場(chǎng)景里很實(shí)用。我習(xí)慣在報(bào)表查詢(xún)里用COALESCE(sc.score, 0)把NULL轉(zhuǎn)成0再給前端這樣展示層就不用來(lái)回處理空值了UIs邏輯會(huì)清爽很多。2.4 聚合統(tǒng)計(jì)平均分、及格率與排名報(bào)表的另一個(gè)大頭是統(tǒng)計(jì)。算一門(mén)課的平均分、最高分、最低分一條SQL完成SELECT AVG(score), MAX(score), MIN(score), COUNT(*) FROM score WHERE course_id 2;注意AVG會(huì)忽略NULL如果某個(gè)學(xué)生缺考沒(méi)有記錄他不會(huì)拉低平均分這通常是我們想要的效果。但如果你把缺考錄成了0分那就會(huì)把平均分拉低所以缺考狀態(tài)的記錄方式要提前定義好。按班級(jí)分組統(tǒng)計(jì)平均分是典型的分組聚合SELECT s.class_name, AVG(sc.score) AS avg_score FROM score sc JOIN student s ON sc.student_id s.id GROUP BY s.class_name;這里有個(gè)高頻踩坑點(diǎn)MySQL 5.7及以上默認(rèn)開(kāi)啟了ONLY_FULL_GROUP_BY模式如果你select了一個(gè)不在GROUP BY里的非聚合列SQL會(huì)直接報(bào)錯(cuò)。比如上面那條SQL如果想順便select s.name抱歉報(bào)錯(cuò)。這是SQL規(guī)范層面的強(qiáng)制要求很多新手在這里卡很久。算及格率就更有實(shí)際意義了SELECT COUNT(*) AS total, SUM(CASE WHEN score 60 THEN 1 ELSE 0 END) AS passed, CONCAT(ROUND(SUM(CASE WHEN score 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), %) AS pass_rate FROM score WHERE course_id 2;COUNT是總數(shù)SUM只對(duì)及格的行加1兩者一除就是及格率。用ROUND保留兩位小數(shù)再用CONCAT拼一個(gè)百分比符號(hào)前端展示就省事了。至于排名MySQL 8.0版本可以用窗口函數(shù)一行搞定SELECT student_id, course_id, score, RANK() OVER (PARTITION BY course_id ORDER BY score DESC) AS rank_no FROM score;RANK()遇到相同分?jǐn)?shù)會(huì)并列排名且跳號(hào)比如兩個(gè)并列第一下一個(gè)就是第三名。如果不希望跳號(hào)用DENSE_RANK()希望嚴(yán)格按順序排用ROW_NUMBER()。這三個(gè)窗口函數(shù)的區(qū)別是MySQL面試?yán)锏母哳l題你親手跑一遍就記住了。3. 加一點(diǎn)高級(jí)特性存儲(chǔ)過(guò)程、視圖和觸發(fā)器3.1 用存儲(chǔ)過(guò)程封裝成績(jī)錄入邏輯很多初學(xué)者寫(xiě)系統(tǒng)所有SQL都寫(xiě)在應(yīng)用程序里數(shù)據(jù)庫(kù)只當(dāng)一個(gè)存儲(chǔ)介質(zhì)。這樣做當(dāng)然沒(méi)問(wèn)題但在“學(xué)生成績(jī)管理系統(tǒng)”這個(gè)項(xiàng)目里我強(qiáng)烈建議至少寫(xiě)一個(gè)存儲(chǔ)過(guò)程因?yàn)殇浫氤煽?jī)時(shí)會(huì)涉及到系統(tǒng)邏輯比如分?jǐn)?shù)范圍校驗(yàn)、重復(fù)記錄檢查、學(xué)生課程是否存在。把這些邏輯放在數(shù)據(jù)庫(kù)里應(yīng)用層調(diào)用只需要一行CALL維護(hù)起來(lái)非常方便。我錄成績(jī)用的存儲(chǔ)過(guò)程長(zhǎng)這樣DELIMITER $$ CREATE PROCEDURE sp_add_score( IN p_student_no VARCHAR(20), IN p_course_no VARCHAR(20), IN p_score DECIMAL(5,1), IN p_exam_type VARCHAR(20), IN p_exam_date DATE ) BEGIN DECLARE v_student_id INT DEFAULT NULL; DECLARE v_course_id INT DEFAULT NULL; SELECT id INTO v_student_id FROM student WHERE student_no p_student_no; IF v_student_id IS NULL THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 學(xué)生不存在; END IF; SELECT id INTO v_course_id FROM course WHERE course_no p_course_no; IF v_course_id IS NULL THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 課程不存在; END IF; IF p_score 0 OR p_score 100 THEN SIGNAL SQLSTATE 45000 SET MESSAGE_TEXT 成績(jī)必須在0-100之間; END IF; INSERT INTO score (student_id, course_id, score, exam_type, exam_date) VALUES (v_student_id, v_course_id, p_score, p_exam_type, p_exam_date); END$$ DELIMITER ;調(diào)用方式極其簡(jiǎn)單CALL sp_add_score(20250001, CS101, 92.5, 期末, 2025-06-30);這個(gè)存儲(chǔ)過(guò)程有三個(gè)細(xì)節(jié)值得講。SIGNAL語(yǔ)句是MySQL自定義報(bào)錯(cuò)的標(biāo)準(zhǔn)方式。SQLSTATE 45000表示用戶(hù)自定義錯(cuò)誤后面的MESSAGE_TEXT會(huì)顯示在報(bào)錯(cuò)信息里。應(yīng)用程序捕獲到這個(gè)異常之后可以直接彈一個(gè)“學(xué)生不存在”的提示給用戶(hù)編排層面非常清晰。通過(guò)學(xué)號(hào)和課程編號(hào)來(lái)查ID調(diào)用的時(shí)候就不用先查ID再拼SQL把兩層查詢(xún)封裝成一層。不過(guò)要特別注意SELECT INTO如果查不到數(shù)據(jù)并不會(huì)把變量改成NULL而是保持變量原有值。所以我在變量聲明的地方直接寫(xiě)了DEFAULT NULL不給它留舊值的機(jī)會(huì)這是存儲(chǔ)過(guò)程開(kāi)發(fā)里的一個(gè)老坑。如果重復(fù)插入唯一約束會(huì)拋異常我認(rèn)為在錄入成績(jī)這類(lèi)場(chǎng)景里直接報(bào)錯(cuò)給用戶(hù)是合理的。如果想更友好可以用INSERT ... ON DUPLICATE KEY UPDATE或者INSERT IGNORE來(lái)做冪等處理比如“重復(fù)提交時(shí)更新分?jǐn)?shù)而不是報(bào)錯(cuò)”這就要看業(yè)務(wù)怎么定義了。3.2 用視圖簡(jiǎn)化成績(jī)查詢(xún)視圖就是一個(gè)“保存的查詢(xún)”它對(duì)應(yīng)用層來(lái)說(shuō)就像一張?zhí)摂M表。在學(xué)生成績(jī)系統(tǒng)里我建了一個(gè)成績(jī)匯總視圖CREATE OR REPLACE VIEW v_student_score AS SELECT s.student_no, s.name, s.class_name, c.course_name, c.credit, sc.score, sc.exam_type, sc.exam_date FROM score sc JOIN student s ON sc.student_id s.id JOIN course c ON sc.course_id c.id;之后應(yīng)用層查詢(xún)只需要SELECT * FROM v_student_score WHERE student_no 20250001;復(fù)雜的三表聯(lián)查邏輯封裝在視圖里應(yīng)用層代碼非常干凈。視圖還有一個(gè)好處可以控制暴露哪些字段。比如我不想讓?xiě)?yīng)用開(kāi)發(fā)同學(xué)看到phone字段視圖里不select它就行。當(dāng)然視圖不是萬(wàn)能的。視圖只是一種邏輯層封裝并不存儲(chǔ)數(shù)據(jù)每查一次都要重新執(zhí)行底層查詢(xún)。對(duì)這個(gè)小項(xiàng)目來(lái)說(shuō)無(wú)所謂數(shù)據(jù)量大之后還是要考慮物化方案或者直接寫(xiě)優(yōu)化好的SQL。另外MySQL里基于多表JOIN的視圖默認(rèn)不能做插入更新操作所以視圖主要給查詢(xún)用寫(xiě)操作老老實(shí)實(shí)走表。3.3 用觸發(fā)器記錄成績(jī)變更日志觸發(fā)器平時(shí)用得少但在成績(jī)管理系統(tǒng)里有一個(gè)很自然的場(chǎng)景記錄成績(jī)變更日志。老師改了一個(gè)學(xué)生的成績(jī)我們需要知道改之前是多少、改之后是多少、什么時(shí)間改的、誰(shuí)改的。應(yīng)用層當(dāng)然可以寫(xiě)日志但數(shù)據(jù)庫(kù)觸發(fā)器能做到“無(wú)論誰(shuí)用什么途徑修改數(shù)據(jù)都會(huì)被記錄”可靠性更高。我建了一張日志表和一個(gè)UPDATE觸發(fā)器CREATE TABLE score_log ( id INT PRIMARY KEY AUTO_INCREMENT, score_id INT NOT NULL, old_score DECIMAL(5,1), new_score DECIMAL(5,1), change_time DATETIME DEFAULT CURRENT_TIMESTAMP, change_user VARCHAR(50) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT成績(jī)修改日志; DELIMITER $$ CREATE TRIGGER trg_score_update AFTER UPDATE ON score FOR EACH ROW BEGIN INSERT INTO score_log (score_id, old_score, new_score, change_user) VALUES (OLD.id, OLD.score, NEW.score, CURRENT_USER()); END$$ DELIMITER ;這里的OLD和NEW是觸發(fā)器中固定使用的兩個(gè)虛擬行OLD代表更新之前的行NEW代表更新之后的行。改成績(jī)的操作執(zhí)行后舊分?jǐn)?shù)和新分?jǐn)?shù)都會(huì)自動(dòng)落進(jìn)日志表。實(shí)際使用中有一個(gè)局限CURRENT_USER()拿到的通常是數(shù)據(jù)庫(kù)連接賬號(hào)而不是“當(dāng)前登錄系統(tǒng)的老師姓名”。在小系統(tǒng)里勉強(qiáng)能接受要更精確可以在應(yīng)用層把操作人姓名寫(xiě)進(jìn)一個(gè)會(huì)話(huà)變量比如SET op_user 張老師;然后在觸發(fā)器里用op_user拼接日志內(nèi)容。這是觸發(fā)器最常見(jiàn)的一個(gè)擴(kuò)展玩法能覆蓋審計(jì)需求。觸發(fā)器雖好也要克制。一個(gè)表上觸發(fā)器太多或者觸發(fā)器里的SQL太重會(huì)拖慢每次DML操作。日志場(chǎng)景因?yàn)橹皇荌NSERT一條記錄性能影響可以忽略反而是最推薦的觸發(fā)器使用場(chǎng)景。4. 索引、鎖與事務(wù)并發(fā)安全和性能優(yōu)化一起講4.1 索引設(shè)計(jì)思路從EXPLAIN看執(zhí)行計(jì)劃學(xué)生成績(jī)管理系統(tǒng)的數(shù)據(jù)量不大但既然要學(xué)習(xí)性能優(yōu)化這一課值得認(rèn)真做。最常見(jiàn)的性能瓶頸就是全表掃描沒(méi)有索引的情況下MySQL要一行行翻完整張表才能找到目標(biāo)數(shù)據(jù)。當(dāng)成績(jī)數(shù)據(jù)從幾千條漲到幾十萬(wàn)條時(shí)查詢(xún)時(shí)間會(huì)肉眼可見(jiàn)地變慢。我建了這些索引ALTER TABLE student ADD INDEX idx_class (class_name); ALTER TABLE score ADD INDEX idx_student (student_id); ALTER TABLE score ADD INDEX idx_course (course_id);為什么這樣建student表的student_no已經(jīng)加了UNIQUE約束本身就是一個(gè)索引主鍵id自然也有。按班級(jí)查詢(xún)是一個(gè)高頻場(chǎng)景給class_name加索引收益明顯。score表上成績(jī)查詢(xún)幾乎都是先按student_id過(guò)濾或者按course_id過(guò)濾再加上外鍵約束本身的檢查需求這兩個(gè)字段都值得加索引。至于score字段本身極少單獨(dú)按分?jǐn)?shù)范圍去查先不加。索引不是越多越好。每多一個(gè)索引插入和更新時(shí)就要多維護(hù)一棵B樹(shù)。成績(jī)系統(tǒng)的場(chǎng)景是讀多寫(xiě)少索引可以酌情多建幾個(gè)如果是高頻寫(xiě)入的日志系統(tǒng)索引太多會(huì)拖慢寫(xiě)入。實(shí)踐中先把查詢(xún)場(chǎng)景列出來(lái)針對(duì)高頻WHERE列建索引再通過(guò)EXPLAIN驗(yàn)證是否生效?？磮?zhí)行計(jì)劃是我排查SQL性能的第一動(dòng)作EXPLAIN SELECT s.name, sc.score FROM score sc JOIN student s ON sc.student_id s.id WHERE s.class_name 計(jì)科2301;重點(diǎn)關(guān)注type列。常見(jiàn)的訪(fǎng)問(wèn)類(lèi)型從好到差依次是system const eq_ref ref range index ALL。如果看到ALL說(shuō)明全表掃描大概率索引沒(méi)建對(duì)。再看key列確認(rèn)有沒(méi)有走我們預(yù)期的索引。有時(shí)候明明建了索引但SQL中用了函數(shù)、隱式類(lèi)型轉(zhuǎn)換或者前導(dǎo)模糊匹配LIKE %xx索引就會(huì)失效。還有一個(gè)我前面提到的OR條件如果OR兩邊不是同一個(gè)索引的列MySQL經(jīng)常選擇不走路直接全表掃。遇到這種情況可以用UNION改寫(xiě)SELECT * FROM score WHERE student_id 1 UNION SELECT * FROM score WHERE course_id 2;這種改寫(xiě)方式在高頻查詢(xún)里效果很明顯也是面試?yán)锍？嫉乃饕?chǎng)景之一。4.2 事務(wù)處理批量修改成績(jī)?nèi)绾伪ＷC不半途而廢成績(jī)錄入和修改通常不是一條條來(lái)的老師可能一次性把全班50個(gè)人的期末成績(jī)?nèi)繉?dǎo)入。如果逐條INSERT執(zhí)行到第30條時(shí)報(bào)錯(cuò)前29條已經(jīng)入庫(kù)數(shù)據(jù)就處于半完成狀態(tài)非常危險(xiǎn)。事務(wù)的存在就是為了解決這個(gè)問(wèn)題要么全部成功要么全部回滾沒(méi)有中間狀態(tài)。MySQL的InnoDB引擎默認(rèn)開(kāi)啟自動(dòng)提交但可以顯式開(kāi)啟事務(wù)START TRANSACTION; UPDATE score SET score 95 WHERE student_id 1 AND course_id 2 AND exam_type 期末; UPDATE score SET score 88 WHERE student_id 2 AND course_id 2 AND exam_type 期末; -- 如果某一步出錯(cuò)執(zhí)行 ROLLBACK前面的修改全部撤銷(xiāo) COMMIT;事務(wù)的ACID特性是這個(gè)系統(tǒng)穩(wěn)定性的基石。實(shí)際項(xiàng)目中我遇到過(guò)的情況是應(yīng)用層調(diào)用一個(gè)Java接口批量修改成績(jī)中途一條數(shù)據(jù)因?yàn)槲ㄒ患s束沖突拋異常業(yè)務(wù)層的事務(wù)注解rollbackFor沒(méi)有配好導(dǎo)致異常發(fā)生時(shí)沒(méi)有觸發(fā)回滾前幾條修改成功、后面幾條失敗最后數(shù)據(jù)出現(xiàn)不一致。這個(gè)教訓(xùn)說(shuō)明事務(wù)不只是數(shù)據(jù)庫(kù)層面的START TRANSACTION應(yīng)用層的事務(wù)邊界設(shè)計(jì)同樣重要。尤其要注意Java里事務(wù)默認(rèn)只回滾RuntimeException受檢異常不會(huì)觸發(fā)回滾需要顯式配置rollbackFor。事務(wù)隔離級(jí)別方面InnoDB默認(rèn)是REPEATABLE READ可重復(fù)讀對(duì)成績(jī)系統(tǒng)完全夠用。它在同一事務(wù)內(nèi)多次讀取相同記錄結(jié)果一致也能避免幻讀問(wèn)題。除非有非常明確的讀性能瓶頸否則不建議隨意調(diào)低隔離級(jí)別調(diào)成READ COMMITTED那個(gè)級(jí)別下并發(fā)控制要弱一些。4.3 鎖的分類(lèi)與死鎖排查說(shuō)到事務(wù)就繞不開(kāi)鎖。InnoDB的鎖按粒度分有表鎖和行鎖按類(lèi)型分有共享鎖S鎖和排他鎖X鎖。平時(shí)寫(xiě)普通UPDATEInnoDB會(huì)自動(dòng)對(duì)符合條件的行加排他鎖直到事務(wù)提交或回滾才釋放。兩個(gè)事務(wù)互相持有對(duì)方需要的鎖資源就會(huì)死鎖。成績(jī)系統(tǒng)里“并發(fā)修改同一條成績(jī)”的場(chǎng)景比較少見(jiàn)但“并發(fā)錄入全班成績(jī)”是有可能的。假如事務(wù)A修改了1到30號(hào)學(xué)生的成績(jī)事務(wù)B修改了25到50號(hào)學(xué)生的成績(jī)兩者在25號(hào)學(xué)生那里交叉就可能出現(xiàn)死鎖A持有25號(hào)學(xué)生的鎖B也想拿25號(hào)的鎖互相等待死鎖出現(xiàn)。死鎖的常見(jiàn)排查方式先用SHOW ENGINE INNODB STATUS; 看LATEST DETECTED DEADLOCK段里面會(huì)記錄沖突的SQL和回滾的事務(wù)。大部分死鎖可以通過(guò)幾個(gè)手段解決統(tǒng)一加鎖順序、讓事務(wù)盡量短、必要時(shí)使用SELECT ... FOR UPDATE顯式控制鎖范圍。我舉個(gè)最實(shí)用的經(jīng)驗(yàn)批量修改多條記錄時(shí)所有事務(wù)都按主鍵從小到大排序去改交叉等待的概率會(huì)大大降低。這不是什么高深理論就是實(shí)際開(kāi)發(fā)中摸出來(lái)的規(guī)律。做學(xué)生成績(jī)管理系統(tǒng)這個(gè)粒度多數(shù)表用默認(rèn)的行鎖就行。但要注意如果UPDATE語(yǔ)句的WHERE條件沒(méi)有走索引InnoDB會(huì)升級(jí)為全表掃描相當(dāng)于給整張表加鎖并發(fā)性能瞬間垮掉。這也是為什么要給WHERE條件字段建索引——它不光加速查詢(xún)還影響鎖的粒度。這條邏輯鏈捋順之后你對(duì)“為什么索引重要”的理解會(huì)上升一個(gè)層次。5. MySQL安裝部署與典型問(wèn)題排查實(shí)錄5.1 Windows、Linux和Docker三種安裝方式的注意事項(xiàng)做系統(tǒng)離不開(kāi)環(huán)境搭建。這個(gè)項(xiàng)目最常見(jiàn)的運(yùn)行環(huán)境是Windows家庭電腦和Linux云服務(wù)器兩種環(huán)境各有各的坑Docker也是現(xiàn)在很流行的跑法。Windows下安裝MySQL我建議直接下載ZIP包解壓安裝而不是用安裝向?qū)А＝鈮汉笠鋈碌谝辉谀夸浵滦陆╩y.ini配置文件指定basedir、datadir和端口第二以管理員身份運(yùn)行mysqld --initialize-insecure這一步會(huì)初始化數(shù)據(jù)目錄并生成一個(gè)空密碼的root賬號(hào)第三執(zhí)行mysqld --install把MySQL注冊(cè)成Windows服務(wù)然后net start mysql啟動(dòng)。典型的my.ini長(zhǎng)這樣[mysqld] basedirD:/mysql-8.0.xx datadirD:/mysql-8.0.xx/data port3306 character-set-serverutf8mb4 default-storage-engineINNODBLinux下用yum安裝是主流。CentOS上先裝MySQL官方源rpm包再yum install mysql-server最后systemctl start mysqld systemctl enable mysqld。裝完之后臨時(shí)密碼會(huì)寫(xiě)在/var/log/mysqld.log里執(zhí)行g(shù)rep temporary password /var/log/mysqld.log就能看到隨后用這個(gè)密碼完成首次登錄立刻修改密碼。Docker跑MySQL最省事但有個(gè)大坑要記住容器里的數(shù)據(jù)默認(rèn)不持久化容器一刪數(shù)據(jù)就全沒(méi)了。正式使用一定要掛載宿主機(jī)目錄docker run --name mysql8 \ -e MYSQL_ROOT_PASSWORD123456 \ -p 3306:3306 \ -v /home/mysql/data:/var/lib/mysql \ -d mysql:8.0如果只想本地快速驗(yàn)證項(xiàng)目docker run --name mysql8 -e MYSQL_ROOT_PASSWORD123456 -p 3306:3306 -d mysql:8.0就夠跑起來(lái)了但一定記住別在這個(gè)容器里放重要數(shù)據(jù)。5.2 連接異常排查從root密碼到遠(yuǎn)程訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)裝好了程序卻連不上這類(lèi)問(wèn)題占了排錯(cuò)的七成以上。我遇到的連接問(wèn)題基本可以歸納成四類(lèi)。第一類(lèi)是身份認(rèn)證失敗報(bào)錯(cuò)Access denied for user rootlocalhost。第一次用空密碼或臨時(shí)密碼登錄后要立刻執(zhí)行ALTER USER rootlocalhost IDENTIFIED BY 新密碼;。MySQL 8默認(rèn)的認(rèn)證插件是caching_sha2_password某些老版本的客戶(hù)端驅(qū)動(dòng)不支持程序會(huì)報(bào)Authentication plugin caching_sha2_password cannot be loaded這時(shí)候要么升級(jí)驅(qū)動(dòng)要么把root賬號(hào)改回mysql_native_password。第二類(lèi)是連接超時(shí)報(bào)錯(cuò)Cant connect to MySQL server (10060)。這通常是遠(yuǎn)程訪(fǎng)問(wèn)被擋了先看監(jiān)聽(tīng)地址。Linux上MySQL默認(rèn)只監(jiān)聽(tīng)127.0.0.1要遠(yuǎn)程訪(fǎng)問(wèn)得在my.cnf里設(shè)置bind-address 0.0.0.0或者直接注釋掉bind-address這行。然后再檢查防火墻CentOS用firewall-cmd --permanent --add-port3306/tcpWindows要檢查防火墻入站規(guī)則。我遇到過(guò)太多次“程序連不上”最后發(fā)現(xiàn)是防火墻沒(méi)放行3306端口。第三類(lèi)是服務(wù)啟動(dòng)失敗報(bào)錯(cuò)[ERROR] [MY-010273]之類(lèi)。很多情況是my.ini路徑配置不對(duì)或者datadir目錄權(quán)限問(wèn)題。Linux下還要注意data目錄屬主是不是mysql用戶(hù)權(quán)限不對(duì)一樣起不來(lái)。第四類(lèi)是SSL連接錯(cuò)誤報(bào)錯(cuò)類(lèi)似SSL connection error。MySQL 8默認(rèn)開(kāi)啟SSL如果客戶(hù)端驅(qū)動(dòng)不兼容可以在連接串里顯式加useSSLfalse先跑通業(yè)務(wù)生產(chǎn)環(huán)境建議配置證書(shū)但本地調(diào)試關(guān)掉省心。5.3 高頻報(bào)錯(cuò)速查表最后整理一個(gè)速查表都是我實(shí)際遇到過(guò)的權(quán)當(dāng)一個(gè)避坑清單報(bào)錯(cuò)信息場(chǎng)景原因與解決ERROR 1064 (42000)執(zhí)行SQL時(shí)報(bào)語(yǔ)法錯(cuò)誤一般是關(guān)鍵字、引號(hào)、逗號(hào)問(wèn)題尤其注意反引號(hào)和單引號(hào)別混用ERROR 1366 (HY000)插入中文變亂碼客戶(hù)端連接字符集沒(méi)設(shè)為utf8mb4先執(zhí)行SET NAMES utf8mb4;ERROR 1215建表時(shí)外鍵失敗兩張表的字段類(lèi)型、字符集和排序規(guī)則不一致外鍵列必須嚴(yán)格一致ERROR 1264數(shù)字超出字段精度范圍比如往DECIMAL(5,1)里寫(xiě)10000這種超出范圍的數(shù)需要先在應(yīng)用層校驗(yàn)ERROR 1418創(chuàng)建存儲(chǔ)過(guò)程報(bào)錯(cuò)開(kāi)啟binlog時(shí)需要指定DETERMINISTIC或READS SQL DATA這是存儲(chǔ)過(guò)程的經(jīng)典坑ERROR 1452插入成績(jī)時(shí)外鍵失敗學(xué)生ID或課程ID不在主表里先用SELECT確認(rèn)關(guān)聯(lián)數(shù)據(jù)存在ERROR 3719加CHECK約束報(bào)錯(cuò)MySQL版本低于8.0.16CHECK約束本身不生效或語(yǔ)法解析失敗服務(wù)無(wú)法啟動(dòng)net start mysql報(bào)錯(cuò)檢查data目錄和my.ini配置執(zhí)行mysqld --console看具體輸出做這套系統(tǒng)的時(shí)候我養(yǎng)成了一個(gè)習(xí)慣每一條SQL先單獨(dú)在命令行跑一遍確認(rèn)沒(méi)問(wèn)題再往程序里集成。這樣SQL報(bào)錯(cuò)和程序邏輯報(bào)錯(cuò)能分開(kāi)排查而不是混在一起瞎忙活。這個(gè)習(xí)慣看著笨但真的能省掉大把調(diào)試時(shí)間。學(xué)生成績(jī)管理系統(tǒng)這個(gè)項(xiàng)目做完我個(gè)人最深的體會(huì)是數(shù)據(jù)庫(kù)設(shè)計(jì)決定系統(tǒng)的上限而SQL功底決定開(kāi)發(fā)效率。很多人被“管理系統(tǒng)”三個(gè)字勸退覺(jué)得太簡(jiǎn)單沒(méi)意思但真正動(dòng)手做下來(lái)從三表設(shè)計(jì)、外鍵約束、字符集選擇到事務(wù)、鎖、索引、存儲(chǔ)過(guò)程、觸發(fā)器MySQL的核心內(nèi)容基本都過(guò)了一遍——這個(gè)項(xiàng)目的價(jià)值恰恰在于它不復(fù)雜卻足夠完整。最后再分享一個(gè)小經(jīng)驗(yàn)系統(tǒng)做完之后一定要寫(xiě)一遍備份腳本mysqldump -u root -p student_score_system backup.sql再配個(gè)定時(shí)任務(wù)每周跑一次。成績(jī)數(shù)據(jù)雖然不算金貴但真要是丟了靠記憶重新錄入的滋味絕對(duì)不好受。功能都能跑通只算及格把數(shù)據(jù)當(dāng)成“丟了會(huì)肉疼”的東西來(lái)對(duì)待才算真正邁過(guò)數(shù)據(jù)庫(kù)開(kāi)發(fā)的一道坎。