簡介這份文檔是面向 Oracle 數(shù)據(jù)庫學(xué)習(xí)者的圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)完整方案適合高校數(shù)據(jù)庫課程設(shè)計(jì)、畢業(yè)設(shè)計(jì)或自學(xué)參考。文檔從系統(tǒng)分析入手依次展開需求分析、設(shè)計(jì)目標(biāo)與項(xiàng)目規(guī)劃并系統(tǒng)介紹數(shù)據(jù)庫概念結(jié)構(gòu)設(shè)計(jì)、邏輯結(jié)構(gòu)設(shè)計(jì)與物理結(jié)構(gòu)設(shè)計(jì)再落到表空間、數(shù)據(jù)表、視圖、序列、索引、存儲過程和觸發(fā)器的創(chuàng)建管理以及數(shù)據(jù)查詢、更新、合并等訪問操作幾乎覆蓋數(shù)據(jù)庫設(shè)計(jì)全流程。資源僅包含1個doc文件共319KB文件雖小但目錄結(jié)構(gòu)完整從系統(tǒng)分析到數(shù)據(jù)庫訪問共四章可作為數(shù)據(jù)庫課程設(shè)計(jì)的模板與實(shí)驗(yàn)參考。目前已有281人學(xué)習(xí)下載適合需要快速理解 Oracle 數(shù)據(jù)庫建模與實(shí)現(xiàn)步驟的初學(xué)者。文檔整體邏輯清晰尤其適合作為 Oracle 課程設(shè)計(jì)的起步參考。1. Oracle圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)與實(shí)現(xiàn)這不是一張表是一套數(shù)據(jù)契約oracle圖書管理系統(tǒng)數(shù)據(jù)庫設(shè)計(jì)與實(shí)現(xiàn)這六個字放進(jìn)需求文檔里是一句話落到你桌面上就是一套數(shù)據(jù)契約。它解決的不是“Oracle能不能跑圖書館業(yè)務(wù)”而是借書、還書、續(xù)借、逾期、統(tǒng)計(jì)這些動作如何對應(yīng)到表結(jié)構(gòu)、約束、索引、存儲過程和分頁查詢里。適合正在做課程設(shè)計(jì)、要給別人交付設(shè)計(jì)文檔、或者接手老系統(tǒng)準(zhǔn)備重構(gòu)的工程師。設(shè)計(jì)文檔里值錢的不是ER圖那張圖而是每個字段的長度、每個約束的邊界、每條SQL在特定數(shù)據(jù)量下能不能走對執(zhí)行計(jì)劃。2. 先建模再建庫從ER圖到Oracle數(shù)據(jù)字典的落地映射設(shè)計(jì)文檔的第一步永遠(yuǎn)是實(shí)體關(guān)系模型。很多教程畫完ER圖直接開寫CREATE TABLE中間跳過了兩件事實(shí)體之間的約束關(guān)系怎么落到數(shù)據(jù)庫對象上以及設(shè)計(jì)完怎么回頭看Oracle數(shù)據(jù)字典驗(yàn)證一致性。這兩件事不做文檔就是墻上的畫庫里是另一套。2.1 核心實(shí)體與關(guān)系讀者、圖書、借閱三張主表不能省圖書管理系統(tǒng)再復(fù)雜主干也是三張業(yè)務(wù)表讀者表、圖書表、借閱記錄表。讀者和圖書之間是多對多關(guān)系一個讀者可以借多本書一本書可以被不同讀者在多個時間點(diǎn)借出所以借閱記錄不能直接掛在圖書表的某個字段下它必須是一張獨(dú)立的關(guān)系實(shí)體表。常見字段設(shè)計(jì)是這樣的實(shí)體核心字段約束/說明讀者表reader_id, reader_no, name, user_type, statusreader_no 唯一user_type 區(qū)分學(xué)生/教師圖書表book_id, isbn, title, author, category_id, stockisbn 不唯一同書多名副本所以要 book_id借閱記錄borrow_id, reader_id, book_id, borrow_date, due_date, return_date外鍵指向讀者和圖書狀態(tài)字段控制生命周期最容易犯的錯是把“讀者當(dāng)前借了哪本書”直接存成讀者表的一個字段比如 reader.current_book_id。這樣做的代價(jià)是續(xù)借和還書時要去改讀者表一次借三本書就沒法建模更談不上查歷史記錄。借閱記錄表一定只存一次借閱動作的開始和結(jié)束而不是“現(xiàn)狀”。設(shè)計(jì)文檔里還應(yīng)寫明業(yè)務(wù)規(guī)則借閱周期是30天還是15天能否續(xù)借逾期每天罰多少。這些規(guī)則最后要落成CHECK約束或存儲過程里的判斷不能只寫在文檔的“需求分析”段落里。Oracle做這件事的優(yōu)勢是約束和事務(wù)都由數(shù)據(jù)庫兜住應(yīng)用層偶然漏掉一次判斷數(shù)據(jù)也不會錯。2.2 用數(shù)據(jù)字典反向校驗(yàn)設(shè)計(jì)USER_TABLES 和 USER_CONSTRAINTS 的用途設(shè)計(jì)文檔寫得再整齊實(shí)際庫里有沒有照著建要靠Oracle數(shù)據(jù)字典來驗(yàn)證。我們常說“Oracle數(shù)據(jù)庫”和“數(shù)據(jù)庫設(shè)計(jì)”之間的橋梁就是USER_TABLES、USER_TAB_COLUMNS、USER_CONSTRAINTS這些視圖。每次交付前我都會跑下面這段SQL把文檔里的表名單和執(zhí)行結(jié)果對一遍-- 查看當(dāng)前用戶下所有業(yè)務(wù)表num_rows 是統(tǒng)計(jì)信息里的行數(shù)不代表實(shí)時數(shù)據(jù) SELECT table_name, num_rows, last_analyzed FROM user_tables ORDER BY table_name; -- 查看約束名、類型、狀態(tài)重點(diǎn)看 R 外鍵是否生效 SELECT constraint_name, constraint_type, table_name, status, r_constraint_name FROM user_constraints WHERE table_name IN (READER, BOOK, BORROW_RECORD) ORDER BY table_name, constraint_name;說明一下第二段SQL里的constraint_typeP代表主鍵R代表外鍵U代表唯一鍵C代表CHECK約束。設(shè)計(jì)文檔里寫了“借閱記錄必須引用存在的讀者”庫里就對應(yīng)到BORROW_RECORD上的外鍵約束狀態(tài)必須是ENABLED。如果實(shí)際庫里沒有這個外鍵哪怕是程序一直正常驗(yàn)收時DBA看一眼數(shù)據(jù)字典就翻車了。另一個實(shí)用習(xí)慣是給表加注釋和字段注釋。Oracle里的COMMENT ON TABLE和COMMENT ON COLUMN會把說明寫進(jìn)數(shù)據(jù)字典查詢時通過USER_TAB_COMMENTS、USER_COL_COMMENTS就能看到。文檔能丟數(shù)據(jù)字典不會丟。2.3 主鍵方案序列加觸發(fā)器還是直接用 Oracle 12c 標(biāo)識列主鍵生成是圖書管理系統(tǒng)設(shè)計(jì)里最常見的分叉路。Oracle 12c以前標(biāo)準(zhǔn)做法是序列加觸發(fā)器常見于很多老項(xiàng)目。12c以后可以用GENERATED BY DEFAULT AS IDENTITYOracle 19c單實(shí)例也一樣支持。兩種我都用過建議按目標(biāo)庫版本選。-- 方式一Oracle 12c 起支持建表直接聲明標(biāo)識列 CREATE TABLE book ( book_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, isbn VARCHAR2(20) NOT NULL, title VARCHAR2(200) NOT NULL, author VARCHAR2(100), category_id NUMBER(4), stock NUMBER(6) DEFAULT 1, created_time DATE DEFAULT SYSDATE ); -- 方式二老庫兼容用序列 觸發(fā)器 CREATE SEQUENCE seq_book_id START WITH 1 INCREMENT BY 1 NOCACHE; CREATE OR REPLACE TRIGGER tri_book_id BEFORE INSERT ON book FOR EACH ROW WHEN (NEW.book_id IS NULL) BEGIN :NEW.book_id : seq_book_id.NEXTVAL; END; /需要注意標(biāo)識列和序列生成的數(shù)字都可能有間隙。比如事務(wù)回滾或觸發(fā)器執(zhí)行失敗序列已經(jīng)取走了一個值不會回填。所以業(yè)務(wù)規(guī)則上不能把book_id當(dāng)成“第幾本書”的編號展示給讀者它只是內(nèi)部主鍵。對外編號應(yīng)該用獨(dú)立的book_no字段。我更推薦在目標(biāo)版本是Oracle 19c或更高時直接使用IDENTITY列代碼更短少一個觸發(fā)器。但如果你交付的文檔里寫了“兼容11g”那老老實(shí)實(shí)保留序列和觸發(fā)器。設(shè)計(jì)文檔必須把版本選擇寫明確不能寫“使用Oracle數(shù)據(jù)庫”就完事。3. 從設(shè)計(jì)文檔到可運(yùn)行庫建表語句、初始化數(shù)據(jù)與權(quán)限隔離設(shè)計(jì)文檔的驗(yàn)收現(xiàn)場不是看有沒有一張ER圖而是看能不能在干凈的環(huán)境里按文檔步驟把庫建出來。真正可落地的設(shè)計(jì)文檔建表語句必須能復(fù)制執(zhí)行初始化數(shù)據(jù)必須能重復(fù)跑權(quán)限必須和業(yè)務(wù)賬號分離。3.1 建表語句怎么寫才算過得了 DBA 的眼DBA看建表語句最在意三件事字段類型是否合理約束是否完整是否預(yù)留了擴(kuò)展空間。很多入門項(xiàng)目把圖書價(jià)格用VARCHAR2存把借閱狀態(tài)用中文“未還”/“已還”存我在評審時都會打回。Oracle里應(yīng)該用NUMBER存數(shù)值用CHAR(1)存狀態(tài)碼再用CHECK約束把取值范圍鎖死。-- 讀者表 CREATE TABLE reader ( reader_id NUMBER(8) NOT NULL, reader_no VARCHAR2(20) NOT NULL, name VARCHAR2(50) NOT NULL, phone VARCHAR2(20), email VARCHAR2(100), user_type CHAR(1) DEFAULT 0 NOT NULL, -- 0學(xué)生 1教師 status CHAR(1) DEFAULT 1 NOT NULL, -- 1正常 0凍結(jié) created_time DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT pk_reader PRIMARY KEY (reader_id), CONSTRAINT uk_reader_no UNIQUE (reader_no), CONSTRAINT ck_reader_status CHECK (status IN (0, 1)), CONSTRAINT ck_reader_type CHECK (user_type IN (0, 1)) ); -- 借閱記錄表 CREATE TABLE borrow_record ( borrow_id NUMBER(10) NOT NULL, reader_id NUMBER(8) NOT NULL, book_id NUMBER(8) NOT NULL, borrow_date DATE DEFAULT SYSDATE NOT NULL, due_date DATE NOT NULL, return_date DATE, renew_count NUMBER(2) DEFAULT 0 NOT NULL, status CHAR(1) DEFAULT B NOT NULL, -- B借閱中 R已歸還 CONSTRAINT pk_borrow PRIMARY KEY (borrow_id), CONSTRAINT fk_borrow_reader FOREIGN KEY (reader_id) REFERENCES reader(reader_id), CONSTRAINT fk_borrow_book FOREIGN KEY (book_id) REFERENCES book(book_id), CONSTRAINT ck_borrow_status CHECK (status IN (B, R)) );這里的VARCHAR2長度不是隨便定的。phone給20位是要容納手機(jī)號前面帶國家碼VARCHAR2單位是字符不是字節(jié)所以中文字段也放得下。status列用CHAR(1)而不是NUMBER是為了后面代碼里讀寫一眼能看出含義也避免狀態(tài)碼和數(shù)字主鍵混淆。due_date是借出時算出來的應(yīng)還日期不依賴應(yīng)用層臨時計(jì)算。借閱表上的外鍵是必須的。有人為了插入性能去掉外鍵結(jié)果是應(yīng)用層代碼私自插入一條不存在的reader_id查報(bào)表時關(guān)聯(lián)出空值還得回來補(bǔ)臟數(shù)據(jù)。這個教訓(xùn)我見過不止一次。3.2 初始化數(shù)據(jù)管理員賬號、圖書分類、測試數(shù)據(jù)怎么造設(shè)計(jì)文檔一般會留一章“系統(tǒng)初始數(shù)據(jù)”。常見做法是建一張圖書分類字典表再放幾個管理員賬號。管理員賬號不要和讀者表混在一起業(yè)務(wù)上兩者權(quán)限不同硬塞進(jìn)同一張表會讓角色控制非常別扭。-- 圖書分類字典 CREATE TABLE book_category ( category_id NUMBER(4) NOT NULL, category_name VARCHAR2(100) NOT NULL, parent_id NUMBER(4), CONSTRAINT pk_book_category PRIMARY KEY (category_id) ); -- 管理員表 CREATE TABLE sys_user ( user_id NUMBER(8) NOT NULL, login_name VARCHAR2(50) NOT NULL, password VARCHAR2(200) NOT NULL, -- 存哈希值不存明文 user_name VARCHAR2(50) NOT NULL, status CHAR(1) DEFAULT 1 NOT NULL, CONSTRAINT pk_sys_user PRIMARY KEY (user_id), CONSTRAINT uk_sys_user_login UNIQUE (login_name) );初始化數(shù)據(jù)時我最煩的是腳本不能重復(fù)執(zhí)行。第一次跑成功第二次跑報(bào)主鍵沖突。所以初始化腳本里我習(xí)慣用MERGE而不是裸INSERT。MERGE的意思是“存在就更新不存在就插入”。比如初始分類MERGE INTO book_category t USING (SELECT 1 AS category_id, 文學(xué) AS category_name FROM dual) s ON (t.category_id s.category_id) WHEN NOT MATCHED THEN INSERT (category_id, category_name) VALUES (s.category_id, s.category_name);這個寫法看著啰嗦但在演示環(huán)境反復(fù)初始化時就是后悔藥。測試數(shù)據(jù)也一樣造讀者、造圖書、造借閱記錄腳本跑三遍都不會重復(fù)。Oracle的dual表在這里派上大用場它保證每一條MERGE只針對一條初始記錄。3.3 權(quán)限與同義詞業(yè)務(wù)賬號和 DBA 賬號的邊界很多課程設(shè)計(jì)圖省事全程用system或者sys建表。這樣做在單機(jī)練習(xí)環(huán)境沒問題一旦要交付到真實(shí)項(xiàng)目等保和審計(jì)會直接拒絕。業(yè)務(wù)應(yīng)用應(yīng)該連一個只擁有業(yè)務(wù)表權(quán)限的賬號而不是sys。-- 創(chuàng)建業(yè)務(wù)管理賬號 CREATE USER library_mgr IDENTIFIED BY 你的復(fù)雜密碼; GRANT CONNECT, RESOURCE TO library_mgr; GRANT UNLIMITED TABLESPACE TO library_mgr; -- 創(chuàng)建只讀報(bào)表賬號 CREATE USER library_report IDENTIFIED BY 報(bào)表賬號密碼; GRANT CONNECT TO library_report; GRANT SELECT ON library_mgr.reader TO library_report; GRANT SELECT ON library_mgr.book TO library_report; GRANT SELECT ON library_mgr.borrow_record TO library_report;如果不想讓應(yīng)用側(cè)記住“l(fā)ibrary_mgr.book”這種帶模式名的寫法可以建同義詞。Oracle里同義詞就是給對象起一個短別名應(yīng)用連接后能直接用BOOK訪問。CREATE SYNONYM app_reader FOR library_mgr.reader; CREATE SYNONYM app_book FOR library_mgr.book;權(quán)限隔離的意義不只是安全。它還能讓你在賬號密碼泄露時快速定位影響范圍不需要把所有表都暴露給同一個連接。設(shè)計(jì)文檔里單獨(dú)寫一節(jié)“權(quán)限矩陣”是很加分的部分。4. 圖書管理系統(tǒng)的Oracle查詢與存儲過程分頁、逾期、統(tǒng)計(jì)三件套設(shè)計(jì)文檔除了建表還要回答“功能怎么實(shí)現(xiàn)”。圖書管理系統(tǒng)里最高頻的功能就是查詢借閱記錄、處理還書、統(tǒng)計(jì)報(bào)表對應(yīng)到Oracle里就是分頁、存儲過程和常用函數(shù)。這部分寫得越具體后面開發(fā)越省事。4.1 分頁查詢ROWNUM陷阱與標(biāo)準(zhǔn)寫法圖書列表、借閱記錄列表都要分頁。很多入門的Oracle寫法是這樣先ORDER BY再ROWNUM結(jié)果發(fā)現(xiàn)前幾頁正常越翻越亂。原因是ROWNUM在排序之前就給結(jié)果集編號了直接加ROWNUM條件會先截?cái)嘣倥判?。穩(wěn)妥的分頁寫法是用ROW_NUMBER窗口函數(shù)生成行號外層再過濾。-- 任意 Oracle 版本可用的分頁寫法 SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.borrow_date DESC) AS rn FROM borrow_record t WHERE t.status B ) WHERE rn BETWEEN 1 AND 20;這里第一次查詢先按借出日期倒序再給每行生成從1開始的序號外層截取第1到20行。頁碼變了BETWEEN后面的兩個數(shù)字就跟著變。Oracle 12c以上可以用OFFSET FETCH比如OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY但為了兼容舊庫我一般保留ROW_NUMBER寫法。分頁查詢還有一個隱藏問題如果借閱記錄表達(dá)到幾十萬行排序字段必須有索引否則翻到后面的頁碼會明顯變慢。這個坑在下一章單獨(dú)說。4.2 存儲過程還書業(yè)務(wù)和逾期罰款別讓應(yīng)用層算還書不是一個UPDATE就能完成的。它要改借閱記錄狀態(tài)、算應(yīng)還日期和實(shí)還日期的差值、可能生成罰款。這些邏輯放在存儲過程里比放在PHP、Java或者任何應(yīng)用代碼里都更安全。因?yàn)閿?shù)據(jù)庫事務(wù)邊界清晰一個存儲過程就是一個原子操作。CREATE OR REPLACE PROCEDURE proc_return_book ( p_borrow_id IN NUMBER, p_operator IN VARCHAR2, p_fine OUT NUMBER ) IS v_due_date DATE; v_overdue_days NUMBER; BEGIN SELECT due_date INTO v_due_date FROM borrow_record WHERE borrow_id p_borrow_id AND status B FOR UPDATE; UPDATE borrow_record SET return_date SYSDATE, status R, operator p_operator WHERE borrow_id p_borrow_id; v_overdue_days : TRUNC(SYSDATE) - TRUNC(v_due_date); IF v_overdue_days 0 THEN p_fine : v_overdue_days * 0.1; ELSE p_fine : 0; END IF; COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN ROLLBACK; RAISE_APPLICATION_ERROR(-20001, 借閱記錄不存在或已歸還); END proc_return_book; /說明幾點(diǎn)。FOR UPDATE是給這條借閱記錄加鎖防止兩個人同時點(diǎn)擊還書。TRUNC(SYSDATE)把時間歸零只按天數(shù)算不會因?yàn)椤岸噙€了幾個小時”多罰一天錢。p_fine是OUT參數(shù)應(yīng)用層拿到它展示“本次還書產(chǎn)生罰款”。Oracle存儲過程不難寫難在參數(shù)命名和事務(wù)提交位置。我的習(xí)慣是存儲過程里只做業(yè)務(wù)不塞打印日志日志另寫一張表。4.3 統(tǒng)計(jì)報(bào)表借閱排行、類別分布和常用函數(shù)設(shè)計(jì)文檔最后總要配上幾個統(tǒng)計(jì)場景熱門圖書排行、讀者借閱次數(shù)、逾期清單。這里用到的Oracle函數(shù)并不多翻來覆去就是TRUNC、TO_CHAR、RANK、NVL、DECODE這幾個。網(wǎng)上搜“oracle函數(shù)大全及舉例”很容易看花眼但圖書管理系統(tǒng)里真正需要手寫的是帶窗口函數(shù)的聚合查詢。-- 熱門圖書排行按借閱次數(shù)排名取前10 SELECT * FROM ( SELECT b.book_id, b.title, COUNT(*) AS borrow_count, RANK() OVER (ORDER BY COUNT(*) DESC) AS rank_no FROM borrow_record br JOIN book b ON b.book_id br.book_id WHERE br.status R GROUP BY b.book_id, b.title ) WHERE rank_no 10;RANK函數(shù)允許并列名次比如第三名有兩本書下個名次是第五名這符合大多數(shù)榜單預(yù)期。如果業(yè)務(wù)要求不許并列就換ROW_NUMBER。GROUP BY后面列必須和SELECT里的非聚合列一致這在Oracle里報(bào)錯很直接看ORA-00979就能定位。再比如統(tǒng)計(jì)每個月的借閱量按TO_CHAR(borrow_date, YYYY-MM)分組即可SELECT TO_CHAR(borrow_date, YYYY-MM) AS borrow_month, COUNT(*) AS total_count FROM borrow_record GROUP BY TO_CHAR(borrow_date, YYYY-MM) ORDER BY borrow_month;到了這一步設(shè)計(jì)文檔就不再只是“表結(jié)構(gòu)說明書”而是把查詢邏輯也定下來了。5. Oracle圖書管理系統(tǒng)避坑與排查從字符集、監(jiān)聽到分頁慢查詢每個Oracle項(xiàng)目都有幾個固定坑位圖書管理系統(tǒng)也不例外。下面這幾條都是實(shí)操中容易踩的每條我都按“現(xiàn)象→原因→解決”寫方便你直接對照。5.1 字符集不一致導(dǎo)致中文亂碼現(xiàn)象PL/SQL Developer里中文顯示正常Java應(yīng)用插入后查出來是問號或者反過來客戶端看著是亂碼庫里其實(shí)是對的。原因數(shù)據(jù)庫字符集、客戶端NLS_LANG、應(yīng)用連接字符集三者不一致。最常見的是庫用了AL32UTF8客戶端還是ZHS16GBK或者服務(wù)端環(huán)境變量沒導(dǎo)入。解決先確認(rèn)數(shù)據(jù)庫字符集再用同一套字符集配置客戶端。SELECT USERENV(language) FROM dual;輸出類似SIMPLIFIED CHINESE_CHINA.AL32UTF8。如果確認(rèn)庫是AL32UTF8Linux客戶端就設(shè)置export NLS_LANGAMERICAN_AMERICA.AL32UTF8Windows客戶端也要在系統(tǒng)環(huán)境變量里改成一致。建庫前最好就把字符集定成AL32UTF8換庫不是小事。5.2 監(jiān)聽出問題連接時快時慢甚至ORA-12541現(xiàn)象sqlplus登錄Oracle數(shù)據(jù)庫出現(xiàn)緩慢或者直接報(bào)ORA-12541、ORA-12537監(jiān)聽服務(wù)無法啟動按system用戶登錄沒有反應(yīng)。原因Oracle監(jiān)聽依賴主機(jī)名解析。服務(wù)器hostname改過了/etc/hosts里沒有對應(yīng)條目或者監(jiān)聽端口被防火墻擋住。還有一種常見原因是listener.log已經(jīng)漲到幾個G監(jiān)聽日志寫入慢連接自然跟著慢。解決按先后順序執(zhí)行下面三件事。# 檢查監(jiān)聽狀態(tài) lsnrctl status # 看監(jiān)聽日志是否異常 tail -n 200 $ORACLE_HOME/network/log/listener.log # hostname 對應(yīng)關(guān)系梳理 cat /etc/hosts如果hostname變了把127.0.0.1 主機(jī)名寫進(jìn)/etc/hosts再用lsnrctl reload。如果日志過大可以定期清理或者在監(jiān)聽配置里打開日志輪轉(zhuǎn)。這個排查順序能覆蓋八成連接問題。5.3 借閱記錄分頁越翻越慢執(zhí)行計(jì)劃走了全表掃描現(xiàn)象第一頁秒開翻到100頁要等好幾秒EXPLAIN PLAN發(fā)現(xiàn)sort order by和table access by rowid后面跟著全表掃描。原因分頁主子段是borrow_date但沒有對應(yīng)索引。每次翻頁都要把所有符合條件的記錄抓出來排序再截取。數(shù)據(jù)量只有一萬條時感覺不明顯十萬條以后體感明顯。解決給排序和過濾條件建復(fù)合索引。CREATE INDEX idx_borrow_status_date ON borrow_record (status, borrow_date DESC);status列做前綴是因?yàn)閃HERE里先按狀態(tài)過濾再把borrow_date倒序拿出來排序。圖書管理系統(tǒng)里“正在借閱的記錄”通常只占少部分這個索引在還書和分頁場景都能用上。建完索引立刻看執(zhí)行計(jì)劃EXPLAIN PLAN FOR SELECT * FROM ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY t.borrow_date DESC) rn FROM borrow_record t WHERE t.status B ) WHERE rn BETWEEN 1 AND 20; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);執(zhí)行計(jì)劃里出現(xiàn)INDEX RANGE SCAN就說明索引生效了。5.4 觸發(fā)器里做太多事批量插入性能被拖垮現(xiàn)象單條插入正常用PL/SQL批量插入一萬條圖書數(shù)據(jù)特別慢像卡住一樣。原因每一行觸發(fā)序列觸發(fā)器觸發(fā)器里還有多余的SELECT查詢和DBMS_OUTPUT行級觸發(fā)器被放大了一萬倍。解決行級觸發(fā)器只做必要賦值不要查表不要打印輸出。如果要批量初始化測試數(shù)據(jù)關(guān)掉DBMS_OUTPUT或者直接放棄觸發(fā)器用IDENTITY列。-- 初始化大批量測試數(shù)據(jù)時先關(guān)掉會話輸出 SET SERVEROUTPUT OFF; DECLARE TYPE t_book_id IS TABLE OF NUMBER; v_ids t_book_id; BEGIN SELECT book_id BULK COLLECT INTO v_ids FROM book; -- 這里只是演示批量業(yè)務(wù)過程盡量用數(shù)組操作 END; /口訣是觸發(fā)器的代碼越短越好長業(yè)務(wù)放存儲過程。6. 收尾給“數(shù)據(jù)庫設(shè)計(jì)與實(shí)現(xiàn).doc”加一份能說服驗(yàn)收的驗(yàn)證清單設(shè)計(jì)文檔寫到最后我會單獨(dú)放一節(jié)“驗(yàn)證清單”不是空話而是能在新庫上重復(fù)執(zhí)行的檢查腳本。這樣驗(yàn)收方不用肉眼找跑一遍SQL就能確認(rèn)設(shè)計(jì)落地了。驗(yàn)證清單至少包含四類檢查表是否存在、約束是否啟用、基礎(chǔ)數(shù)據(jù)是否完整、關(guān)鍵查詢是否能在合理時間返回。下面這段SQL是我常用的開場SELECT READER AS table_name, COUNT(*) AS cnt FROM reader UNION ALL SELECT BOOK, COUNT(*) FROM book UNION ALL SELECT BORROW_RECORD, COUNT(*) FROM borrow_record;如果三張表都是0行說明初始化腳本沒跑。如果借閱記錄有值但讀者表沒有說明外鍵可能沒生效回到USER_CONSTRAINTS查。最后一件事是給連接賬號做一次最小權(quán)限確認(rèn)。用報(bào)表賬號登錄試著執(zhí)行UPDATE和DELETE應(yīng)該報(bào)權(quán)限不足。能用SELECT查到業(yè)務(wù)數(shù)據(jù)但改不了這才是交付狀態(tài)。我自己的習(xí)慣是把這個驗(yàn)證清單和建表腳本放在同一個目錄命名成verify.sql交付時一起給。因?yàn)榭陬^說“庫沒問題”沒人信腳本能跑出結(jié)果才有說服力。希望這個思路幫你在下一個Oracle圖書管理系統(tǒng)項(xiàng)目里少翻幾次車。本文還有配套的精品資源點(diǎn)擊獲取