2025年數(shù)據(jù)庫(kù)工程師考試數(shù)據(jù)庫(kù)設(shè)計(jì)與優(yōu)化專項(xiàng)訓(xùn)練試卷考試時(shí)間：______分鐘總分：______分姓名：______一、選擇題（每題2分，共20分）1.以下哪個(gè)不是數(shù)據(jù)庫(kù)設(shè)計(jì)的第一范式（1NF）的要求？A.每個(gè)屬性都是原子值，不可再分B.表中的每一行都是唯一的C.屬性的值域是有限集合D.表的列順序是固定的2.在數(shù)據(jù)庫(kù)設(shè)計(jì)中，將一個(gè)違反第三范式（3NF）的關(guān)系模式分解為多個(gè)關(guān)系模式，其主要目的是為了消除哪種異常？A.插入異常B.刪除異常C.更新異常D.查詢異常3.以下哪種索引結(jié)構(gòu)最適合用于全文本搜索？A.B-Tree索引B.Hash索引C.GIN索引D.GiST索引4.當(dāng)數(shù)據(jù)庫(kù)中出現(xiàn)大量并發(fā)的寫操作時(shí)，哪種事務(wù)隔離級(jí)別可能導(dǎo)致讀臟數(shù)據(jù)？A.讀未提交（ReadUncommitted）B.讀已提交（ReadCommitted）C.可重復(fù)讀（RepeatableRead）D.串行化（Serializable）5.以下哪個(gè)SQL語(yǔ)句中的操作最有可能導(dǎo)致索引失效？A.`SELECT*FROMordersWHEREcustomer_id=1001;`B.`SELECT*FROMordersWHERElower(order_date)='2023-01-01';`C.`SELECT*FROMordersWHEREorder_total>(SELECTAVG(total)FROMorders);`D.`SELECT*FROMordersWHEREorder_idIN(1,2,3,4,5);`6.在進(jìn)行SQL查詢優(yōu)化時(shí)，以下哪個(gè)做法通常是不推薦的？A.為經(jīng)常作為查詢條件的列創(chuàng)建索引B.盡量使用`EXISTS`代替`IN`進(jìn)行子查詢C.在`WHERE`子句中使用復(fù)雜的計(jì)算或函數(shù)D.選擇合適的索引列順序以提高索引下推效率7.數(shù)據(jù)庫(kù)分區(qū)（Partitioning）的主要目的是什么？A.提高小表查詢性能B.簡(jiǎn)化索引管理C.提高大型數(shù)據(jù)集的管理和查詢效率D.減少數(shù)據(jù)庫(kù)存儲(chǔ)空間占用8.以下哪個(gè)不是數(shù)據(jù)庫(kù)緩沖池（BufferPool）的主要作用？A.存儲(chǔ)頻繁訪問的數(shù)據(jù)頁(yè)B.減少對(duì)磁盤的I/O操作C.管理數(shù)據(jù)庫(kù)事務(wù)日志D.提高數(shù)據(jù)讀取速度9.當(dāng)兩個(gè)事務(wù)因爭(zhēng)奪相同資源而阻塞，且無法推進(jìn)，這種現(xiàn)象稱為？A.數(shù)據(jù)庫(kù)死鎖（Deadlock）B.資源競(jìng)爭(zhēng)（ResourceContention）C.并發(fā)沖突（ConcurrencyConflict）D.鎖超時(shí)（LockTimeout）10.在關(guān)系數(shù)據(jù)庫(kù)中，外鍵（ForeignKey）的主要作用是？A.保證數(shù)據(jù)的唯一性B.實(shí)現(xiàn)數(shù)據(jù)的完整性約束C.提高查詢性能D.定義數(shù)據(jù)類型二、判斷題（每題1分，共10分）1.第二范式（2NF）要求關(guān)系模式必須滿足第一范式，并且所有非主屬性都完全函數(shù)依賴于所有主屬性。（）2.B-Tree索引和Hash索引都可以用于范圍查詢。（）3.“可重復(fù)讀”隔離級(jí)別可以防止臟讀，但可能出現(xiàn)不可重復(fù)讀。（）4.創(chuàng)建索引一定會(huì)降低數(shù)據(jù)庫(kù)的插入、刪除、更新操作的性能。（）5.`EXPLAIN`語(yǔ)句可以用來分析SQL查詢的執(zhí)行計(jì)劃。（）6.表分區(qū)可以提高某些類型的查詢性能，但會(huì)犧牲數(shù)據(jù)管理復(fù)雜度。（）7.數(shù)據(jù)庫(kù)的“鎖粒度”通常有行鎖、頁(yè)鎖、表鎖三種。（）8.事務(wù)的ACID特性中的“原子性”（Atomicity）保證了事務(wù)要么全部成功，要么全部失敗。（）9.使用分庫(kù)分表可以解決所有數(shù)據(jù)庫(kù)性能問題。（）10.規(guī)范化設(shè)計(jì)的目標(biāo)是盡可能減少數(shù)據(jù)冗余，但可能會(huì)犧牲查詢性能。（）三、簡(jiǎn)答題（每題5分，共20分）1.簡(jiǎn)述數(shù)據(jù)庫(kù)“范式”的概念及其意義。2.簡(jiǎn)述數(shù)據(jù)庫(kù)索引的作用及其主要開銷。3.簡(jiǎn)述數(shù)據(jù)庫(kù)“事務(wù)”的四個(gè)基本特性（ACID）及其含義。4.簡(jiǎn)述造成SQL查詢性能低下的常見原因有哪些。四、設(shè)計(jì)題（每題10分，共20分）1.假設(shè)有一個(gè)“學(xué)生選課”系統(tǒng)，包含學(xué)生（Student）和課程（Course）兩個(gè)實(shí)體。每個(gè)學(xué)生可以選擇多門課程，每門課程可以被多個(gè)學(xué)生選擇。學(xué)生有學(xué)號(hào)（student_id，主鍵）、姓名（name）、專業(yè)（major）。課程有課程號(hào)（course_id，主鍵）、課程名稱（course_name）、學(xué)分（credits）。請(qǐng)繪制該系統(tǒng)的ER圖（使用文字描述實(shí)體、屬性、關(guān)系及基數(shù)），并將其轉(zhuǎn)換為至少滿足第二范式（2NF）的關(guān)系模式。2.假設(shè)有一個(gè)“訂單”表（orders），包含訂單ID（order_id，主鍵）、客戶ID（customer_id）、訂單日期（order_date）、總金額（order_total）。該表目前存在性能問題，查詢特定客戶的訂單列表時(shí)響應(yīng)緩慢。請(qǐng)分析可能的原因，并提出至少兩種具體的優(yōu)化建議。五、優(yōu)化題（每題10分，共20分）1.給定以下SQL查詢語(yǔ)句：```sqlSELECTproduct_name,category_nameFROMproductspJOINcategoriescONp.category_id=c.category_idWHEREp.price>(SELECTAVG(price)FROMproducts)ANDp.category_id=5;```請(qǐng)分析此查詢語(yǔ)句的執(zhí)行計(jì)劃可能存在的問題，并提出至少兩種優(yōu)化此查詢性能的方法。2.假設(shè)你正在維護(hù)一個(gè)電商數(shù)據(jù)庫(kù)，其中“用戶行為”表（user_actions）記錄了用戶的點(diǎn)擊、加購(gòu)、購(gòu)買等行為，每天產(chǎn)生大量數(shù)據(jù)，且查詢用戶近期行為頻率時(shí)性能較差。請(qǐng)考慮使用哪些數(shù)據(jù)庫(kù)優(yōu)化技術(shù)（如索引、分區(qū)、統(tǒng)計(jì)信息等）來改善該表的查詢性能，并說明理由。試卷答案一、選擇題1.D2.C3.C4.A5.B6.C7.C8.C9.A10.B二、判斷題1.√2.×3.√4.√5.√6.√7.√8.√9.×10.√三、簡(jiǎn)答題1.簡(jiǎn)述數(shù)據(jù)庫(kù)“范式”的概念及其意義。答：范式是數(shù)據(jù)庫(kù)設(shè)計(jì)中的規(guī)范化理論，是一系列用于指導(dǎo)關(guān)系數(shù)據(jù)庫(kù)設(shè)計(jì)的規(guī)則。其核心概念是將數(shù)據(jù)存儲(chǔ)在多個(gè)相關(guān)聯(lián)的表中，并通過主鍵和外鍵建立表與表之間的關(guān)系，以減少數(shù)據(jù)冗余、避免數(shù)據(jù)更新異常、插入異常和刪除異常，確保數(shù)據(jù)的邏輯一致性。常見范式有第一范式（1NF）、第二范式（2NF）、第三范式（3NF）和BCNF等。應(yīng)用范式有助于構(gòu)建結(jié)構(gòu)更合理、更穩(wěn)定的數(shù)據(jù)庫(kù)模式。解析思路：首先回答范式的定義，然后說明其核心概念（分解、減少冗余、避免異常），最后列舉常見范式并點(diǎn)明其意義（保證數(shù)據(jù)一致性）。2.簡(jiǎn)述數(shù)據(jù)庫(kù)索引的作用及其主要開銷。答：數(shù)據(jù)庫(kù)索引是幫助數(shù)據(jù)庫(kù)快速定位數(shù)據(jù)的數(shù)據(jù)結(jié)構(gòu)（如B-Tree、Hash等），它通過創(chuàng)建索引列的排序列和指向數(shù)據(jù)行的指針，可以顯著提高數(shù)據(jù)檢索速度，尤其是在大型數(shù)據(jù)集中進(jìn)行查詢操作時(shí)。其主要作用是加速查詢。但索引也帶來開銷：增加存儲(chǔ)空間占用；降低數(shù)據(jù)修改（INSERT、UPDATE、DELETE）的性能，因?yàn)槊看螖?shù)據(jù)變更都需要同步更新索引；可能導(dǎo)致查詢計(jì)劃選擇不當(dāng)，增加維護(hù)成本（如索引重建、重組）。解析思路：先說明索引的定義和主要作用（加速查詢），然后列舉其主要的開銷（存儲(chǔ)、寫操作性能、維護(hù)）。3.簡(jiǎn)述數(shù)據(jù)庫(kù)“事務(wù)”的四個(gè)基本特性（ACID）及其含義。答：ACID是事務(wù)必須滿足的四個(gè)基本特性：*原子性（Atomicity）：事務(wù)是一個(gè)不可分割的工作單元，事務(wù)中的所有操作要么全部成功提交，要么全部失敗回滾，不會(huì)停留在中間某個(gè)狀態(tài)。*一致性（Consistency）：事務(wù)必須保證數(shù)據(jù)庫(kù)從一個(gè)一致性狀態(tài)轉(zhuǎn)變到另一個(gè)一致性狀態(tài)，即事務(wù)執(zhí)行的結(jié)果必須符合數(shù)據(jù)庫(kù)的完整性約束。*隔離性（Isolation）：一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾，即一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對(duì)并發(fā)的其他事務(wù)是隔離的，并發(fā)執(zhí)行的事務(wù)之間不會(huì)相互影響。*持久性（Durability）：一個(gè)事務(wù)一旦提交，它對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的改變就是永久性的，即使系統(tǒng)發(fā)生故障也不會(huì)丟失。解析思路：逐一解釋ACID的四個(gè)字母分別代表的含義，確保每個(gè)特性都清晰準(zhǔn)確。4.簡(jiǎn)述造成SQL查詢性能低下的常見原因有哪些。答：造成SQL查詢性能低下的常見原因包括：*沒有為查詢條件涉及的列創(chuàng)建合適的索引。*SQL語(yǔ)句本身效率低下，如使用了復(fù)雜的子查詢、不合理的JOIN順序、函數(shù)在WHERE子句中、SELECT*而不是指定具體列等。*數(shù)據(jù)庫(kù)表結(jié)構(gòu)設(shè)計(jì)不合理，如存在冗余數(shù)據(jù)、非規(guī)范化設(shè)計(jì)導(dǎo)致查詢需要掃描大量數(shù)據(jù)。*數(shù)據(jù)量過大，導(dǎo)致全表掃描。*數(shù)據(jù)統(tǒng)計(jì)信息過時(shí)或不準(zhǔn)確，使得數(shù)據(jù)庫(kù)查詢優(yōu)化器無法選擇最佳執(zhí)行計(jì)劃。*系統(tǒng)資源瓶頸，如CPU、內(nèi)存、磁盤I/O或網(wǎng)絡(luò)帶寬不足。*鎖競(jìng)爭(zhēng)嚴(yán)重，導(dǎo)致查詢等待。解析思路：從索引、SQL語(yǔ)句本身、表結(jié)構(gòu)設(shè)計(jì)、數(shù)據(jù)量、統(tǒng)計(jì)信息、系統(tǒng)資源和鎖競(jìng)爭(zhēng)等多個(gè)角度列出常見原因。四、設(shè)計(jì)題1.繪制“學(xué)生選課”系統(tǒng)ER圖（文字描述）并轉(zhuǎn)換關(guān)系模式。答：*ER圖描述：*實(shí)體：學(xué)生（Student），屬性：學(xué)號(hào)（student_id,主鍵），姓名（name），專業(yè)（major）。*實(shí)體：課程（Course），屬性：課程號(hào)（course_id,主鍵），課程名稱（course_name），學(xué)分（credits）。*關(guān)系：選課（Selects），描述學(xué)生和課程之間的多對(duì)多關(guān)系。*關(guān)系屬性：無。*基數(shù)：學(xué)生實(shí)體到選課關(guān)系的基數(shù)是多對(duì)多（M:N），選課關(guān)系到課程實(shí)體也是多對(duì)多（M:N）。學(xué)生實(shí)體到選課關(guān)系可以添加“選課時(shí)間”等屬性（如果需要）。*關(guān)系模式（滿足2NF）：*`Student`(student_idPK,name,major)*`Course`(course_idPK,course_name,credits)*`Student_Course`(student_idFKreferencesStudent,course_idFKreferencesCourse,選課時(shí)間,PK(student_id,course_id))//假設(shè)添加選課時(shí)間作為關(guān)系屬性解析思路：首先識(shí)別核心實(shí)體及其屬性。根據(jù)題目描述“每個(gè)學(xué)生可以選擇多門課程，每門課程可以被多個(gè)學(xué)生選擇”確定是多對(duì)多關(guān)系。繪制ER圖時(shí)明確實(shí)體、屬性、關(guān)系及基數(shù)。轉(zhuǎn)換為關(guān)系模式時(shí)，將每個(gè)實(shí)體轉(zhuǎn)換為一張表，主鍵設(shè)為PK。對(duì)于多對(duì)多關(guān)系，創(chuàng)建一個(gè)連接表，其主鍵由參與關(guān)系實(shí)體的主鍵組合而成，同時(shí)在該連接表中添加外鍵（FK）指向相關(guān)實(shí)體表，確保每個(gè)表都滿足2NF（消除非主屬性對(duì)主鍵的部分依賴）。2.分析“訂單”表查詢性能問題原因及優(yōu)化建議。答：*可能原因分析：1.缺乏合適的索引：可能缺少對(duì)`customer_id`和`order_date`（如果查詢常帶有日期范圍）的復(fù)合索引，或者`order_total`列沒有索引，導(dǎo)致查詢需要全表掃描或進(jìn)行代價(jià)較高的聚合操作。2.查詢條件效率低：如果`customer_id`是高基數(shù)（即客戶數(shù)量多），使用`=`可能比`IN`（如果用`IN`的話）更優(yōu)；如果`order_date`范圍很大，可能導(dǎo)致索引選擇性不高。3.表統(tǒng)計(jì)信息過時(shí)：數(shù)據(jù)庫(kù)優(yōu)化器無法獲取準(zhǔn)確的行數(shù)估計(jì)，導(dǎo)致選擇次優(yōu)的執(zhí)行計(jì)劃（如全表掃描）。4.數(shù)據(jù)量巨大：訂單表數(shù)據(jù)量本身很大，即使有索引，查詢也可能因數(shù)據(jù)量大而緩慢。5.系統(tǒng)負(fù)載高：CPU、內(nèi)存或I/O資源繁忙也可能影響查詢響應(yīng)時(shí)間。*優(yōu)化建議：1.創(chuàng)建復(fù)合索引：為`customer_id`創(chuàng)建索引，如果查詢常涉及日期范圍，可創(chuàng)建`(customer_id,order_date)`或`(order_date,customer_id)`的復(fù)合索引，甚至包含`price`的索引如`(customer_id,order_date,price)`，以覆蓋索引（CoveringIndex）。2.優(yōu)化查詢語(yǔ)句：確保`customer_id`的值是有效的，如果`customer_id`基數(shù)很高，確認(rèn)`=`是合適的選擇。考慮是否可以使用更有效的子查詢或連接方式（如果適用）。3.更新統(tǒng)計(jì)信息：執(zhí)行ANALYZETABLE或DBCCUPDATESTATS等命令，確保數(shù)據(jù)庫(kù)優(yōu)化器有最新的統(tǒng)計(jì)信息。4.考慮分區(qū)：如果訂單表數(shù)據(jù)量非常大，可以考慮按`customer_id`或`order_date`進(jìn)行表分區(qū)，將數(shù)據(jù)分散存儲(chǔ)，提高查詢效率。5.分析執(zhí)行計(jì)劃：使用`EXPLAIN`或類似工具分析查詢的執(zhí)行計(jì)劃，找出具體的瓶頸（如全表掃描、文件排序等）并進(jìn)行針對(duì)性優(yōu)化。解析思路：先分析可能導(dǎo)致查詢慢的常見因素（索引、SQL、統(tǒng)計(jì)、數(shù)據(jù)量、負(fù)載）。針對(duì)`WHERE`子句中的條件，思考如何通過索引來加速。提出具體的索引創(chuàng)建建議、SQL微調(diào)、統(tǒng)計(jì)信息更新、分區(qū)等優(yōu)化手段，并說明理由。五、優(yōu)化題1.分析并優(yōu)化SQL查詢性能。```sqlSELECTproduct_name,category_nameFROMproductspJOINcategoriescONp.category_id=c.category_idWHEREp.price>(SELECTAVG(price)FROMproducts)ANDp.category_id=5;```答：*可能存在的問題：1.子查詢`SELECTAVG(price)FROMproducts`：這個(gè)子查詢對(duì)于每一行都需要執(zhí)行一次，并且會(huì)被重復(fù)執(zhí)行，如果`products`表很大，會(huì)成為性能瓶頸。2.缺乏索引：可能缺少`products.price`、`products.category_id`或`(products.category_id,products.price)`的復(fù)合索引，導(dǎo)致連接操作或WHERE條件判斷效率低。*優(yōu)化方法：1.使用臨時(shí)表或變量存儲(chǔ)平均值：將子查詢的結(jié)果先計(jì)算一次并存儲(chǔ)在臨時(shí)表或變量中，然后在主查詢中引用。```sql--偽代碼，具體語(yǔ)法依DBMS而定CREATETEMPORARYTABLEtemp_avg_priceAS(SELECTAVG(price)ASavg_priceFROMproducts);SELECTduct_name,c.category_nameFROMproductsp,temp_avg_pricetmpJOINcategoriescONp.category_id=c.category_idWHEREp.price>tmp.avg_priceANDp.category_id=5;--或使用變量SET@avg_price=(SELECTAVG(price)FROMproducts);SELECTduct_name,c.category_nameFROMproductsp,categoriescWHEREp.price>@avg_priceANDp.category_id=c.category_idANDc.category_id=5;```2.創(chuàng)建合適的索引：為`products(category_id,price)`創(chuàng)建復(fù)合索引，這樣在執(zhí)行連接和價(jià)格比較時(shí)，數(shù)據(jù)庫(kù)可以利用索引快速定位數(shù)據(jù)。同時(shí)確保`categories.category_id`有索引（通常作為外鍵會(huì)自動(dòng)創(chuàng)建）。3.考慮查詢重寫（如果DBMS支持）：有些數(shù)據(jù)庫(kù)系統(tǒng)可能支持將相關(guān)聯(lián)的子查詢轉(zhuǎn)換為連接操作，例如使用`EXISTS`或`JOIN`來實(shí)現(xiàn)類似效果，可能更優(yōu)。解析思路：首先識(shí)別出子查詢是重復(fù)執(zhí)行的關(guān)鍵瓶頸。提出將子查詢結(jié)果暫存的解決方案（臨時(shí)表或變量）。然后指出索引的重要性，并建議創(chuàng)建能同時(shí)滿足過濾條件`category_id=5`和`price>avg_price`的復(fù)合索引。最后，考慮是否有其他查詢重寫方式可能更優(yōu)。2.優(yōu)化“用戶行為”表查詢