2025年數(shù)據(jù)庫優(yōu)化考研專業(yè)課專項(xiàng)訓(xùn)練模擬試卷（含答案）考試時(shí)間：______分鐘總分：______分姓名：______一、選擇題（每題2分，共20分。請將正確選項(xiàng)的首字母填入括號內(nèi)）1.下列關(guān)于數(shù)據(jù)庫索引優(yōu)化的描述中，錯誤的是：(A)聚集索引可以顯著提高特定查詢的讀取速度(B)哈希索引適用于等值查詢，但不能用于范圍查詢(C)全文索引主要用于對文本內(nèi)容進(jìn)行模糊匹配(D)索引維護(hù)操作（如重建、整理碎片）一定會降低數(shù)據(jù)庫的寫入性能2.在數(shù)據(jù)庫并發(fā)控制中，導(dǎo)致“死鎖”的主要條件之一是：(A)事務(wù)隔離級別設(shè)置過高(B)兩個(gè)或以上事務(wù)持有資源并請求持有對方已持有的資源(C)事務(wù)執(zhí)行時(shí)間過長(D)數(shù)據(jù)庫并發(fā)用戶數(shù)過多3.下列哪種查詢優(yōu)化技術(shù)屬于邏輯優(yōu)化階段？(A)選擇更適合的索引來執(zhí)行查詢(B)將連接操作改寫為嵌套循環(huán)或哈希連接(C)使用更高效的磁盤I/O策略讀取數(shù)據(jù)(D)對查詢語句中的表達(dá)式進(jìn)行簡化4.在SQL查詢執(zhí)行計(jì)劃中，“SelectList”階段主要完成的工作是：(A)應(yīng)用WHERE子句的過濾條件(B)從基礎(chǔ)表或索引中檢索滿足條件的元組(C)對檢索出的元組進(jìn)行投影，選出目標(biāo)列(D)對檢索出的元組進(jìn)行排序5.事務(wù)的隔離級別從低到高依次是：(A)READCOMMITTED,REPEATABLEREAD,SERIALIZABLE,READUNCOMMITTED(B)READUNCOMMITTED,READCOMMITTED,SERIALIZABLE,REPEATABLEREAD(C)READCOMMITTED,SERIALIZABLE,REPEATABLEREAD,READUNCOMMITTED(D)READUNCOMMITTED,REPEATABLEREAD,READCOMMITTED,SERIALIZABLE6.以下哪種情況可能導(dǎo)致數(shù)據(jù)庫索引失效？(A)對索引列進(jìn)行了函數(shù)運(yùn)算(B)索引被刪除后重新創(chuàng)建(C)事務(wù)提交后釋放了持有的鎖(D)表中的數(shù)據(jù)量大幅增加7.采用“兩階段鎖協(xié)議”（2PL）的兩段鎖協(xié)議，是為了保證數(shù)據(jù)庫狀態(tài)的一致性，其核心思想是：(A)事務(wù)必須先獲取所有需要的鎖，才能開始執(zhí)行(B)事務(wù)在執(zhí)行過程中可以隨意釋放已持有的鎖(C)事務(wù)只能在一個(gè)階段（增長階段）獲取鎖，在另一個(gè)階段（縮減階段）釋放鎖(D)事務(wù)的鎖請求和鎖釋放操作必須是有序的8.在進(jìn)行數(shù)據(jù)庫表結(jié)構(gòu)設(shè)計(jì)時(shí)，過度規(guī)范化可能導(dǎo)致：(A)數(shù)據(jù)冗余度降低，插入、刪除、更新異常減少(B)查詢效率提高，因?yàn)楸砀?C)并發(fā)寫操作的性能提升(D)數(shù)據(jù)庫存儲空間需求顯著增加9.數(shù)據(jù)庫查詢優(yōu)化器的主要目標(biāo)是：(A)最小化數(shù)據(jù)庫管理員的管理工作量(B)生成執(zhí)行時(shí)間最短的查詢執(zhí)行計(jì)劃(C)生成掃描最少行數(shù)的查詢執(zhí)行計(jì)劃(D)自動完成數(shù)據(jù)庫表的索引創(chuàng)建和維護(hù)10.以下關(guān)于BufferPool（緩沖池）管理的描述中，正確的是：(A)BufferPool越大，數(shù)據(jù)庫的隨機(jī)讀性能一定越好(B)BufferPool管理器通常采用LRU（最近最少使用）算法替換不再使用的頁(C)BufferPool中的數(shù)據(jù)與磁盤上的數(shù)據(jù)完全一致，無需同步(D)BufferPool只用于緩存索引頁，不緩存數(shù)據(jù)頁二、填空題（每空2分，共20分。請將答案填入橫線處）1.數(shù)據(jù)庫查詢優(yōu)化的主要目標(biāo)是在保證結(jié)果正確的前提下，__________查詢的執(zhí)行時(shí)間。2.在B+樹索引中，葉子節(jié)點(diǎn)通常包含鍵值和指向?qū)?yīng)數(shù)據(jù)記錄的__________。3.為了避免臟讀，事務(wù)必須至少被設(shè)置為__________隔離級別。4.并發(fā)事務(wù)T1和T2若要并發(fā)執(zhí)行而不產(chǎn)生丟失更新問題，它們對同一數(shù)據(jù)的讀寫操作必須滿足__________順序。5.索引選擇是查詢物理優(yōu)化的關(guān)鍵步驟，常用的評估指標(biāo)包括選擇度、__________和索引維護(hù)成本。6.SQL查詢中的連接操作（JOIN）是數(shù)據(jù)庫查詢處理的核心，其效率對整體查詢性能有重要影響。7.數(shù)據(jù)庫的寫操作性能不僅取決于CPU速度，還與磁盤的__________性能密切相關(guān)。8.物化視圖是一種預(yù)先計(jì)算并存儲的結(jié)果集，可以顯著加速__________的查詢。9.在數(shù)據(jù)庫鎖機(jī)制中，共享鎖（S鎖）允許多個(gè)事務(wù)對同一數(shù)據(jù)項(xiàng)進(jìn)行__________操作。10.對于復(fù)雜查詢，優(yōu)化器可能會采用__________（一種基于代價(jià)的優(yōu)化方法）來生成最優(yōu)的執(zhí)行計(jì)劃。三、簡答題（每題8分，共24分）1.簡述數(shù)據(jù)庫索引的作用及其主要開銷。至少列舉兩種常見的索引類型。2.解釋什么是事務(wù)的“不可重復(fù)讀”現(xiàn)象，并說明在何種隔離級別下會出現(xiàn)該現(xiàn)象。3.簡述數(shù)據(jù)庫查詢優(yōu)化器通常包含哪些主要階段？并簡述邏輯優(yōu)化和物理優(yōu)化的區(qū)別。四、計(jì)算題（10分）假設(shè)有一個(gè)學(xué)生表Student(SID,SName,Major,Age)，和一個(gè)選課表Course(CID,CName,Grade)。其中Student表有1000條記錄，Course表有5000條記錄。現(xiàn)在要執(zhí)行以下SQL查詢：```sqlSELECTSName,CNameFROMStudentS,CourseCWHERES.SID=C.SIDANDC.Grade>90;```假設(shè)優(yōu)化器估計(jì)此查詢使用嵌套循環(huán)連接（NestedLoopJoin）的成本為5000，使用哈希連接（HashJoin）的成本為2000。索引S.SID、C.SID和C.Grade的選擇度分別為0.1、0.1和0.05。如果使用基于索引的掃描來獲取Grade>90的記錄，并認(rèn)為其成本為1000。請計(jì)算此查詢在不同連接策略下的總成本，并說明優(yōu)化器應(yīng)選擇哪種連接策略。假設(shè)讀取索引頁和基礎(chǔ)表頁的成本相同。五、論述題（26分）數(shù)據(jù)庫的查詢優(yōu)化是一個(gè)復(fù)雜的過程，涉及多個(gè)階段和多種技術(shù)。請結(jié)合你所學(xué)的知識，論述數(shù)據(jù)庫查詢優(yōu)化器如何工作？在邏輯優(yōu)化和物理優(yōu)化階段，分別可以采用哪些主要的優(yōu)化技術(shù)來提升查詢性能？并討論影響索引選擇的關(guān)鍵因素有哪些。試卷答案一、選擇題1.D2.B3.D4.C5.A6.A7.C8.D9.B10.B二、填空題1.最小化2.指針3.READCOMMITTED4.相同5.索引長度（或I/O成本）6.連接7.I/O8.頻繁執(zhí)行9.讀取10.代價(jià)模型（或Cost-Based）三、簡答題1.作用：索引可以顯著提高數(shù)據(jù)庫表的查詢速度，特別是對于大型表和復(fù)雜查詢。它通過創(chuàng)建額外的數(shù)據(jù)結(jié)構(gòu)（如B+樹、哈希表）來存儲數(shù)據(jù)的鍵值和指向原始數(shù)據(jù)記錄的指針，使得數(shù)據(jù)庫引擎能夠快速定位到滿足特定查詢條件的數(shù)據(jù)行，從而避免對整個(gè)表進(jìn)行全表掃描。主要開銷：索引雖然加速了查詢，但也帶來了額外的開銷：*存儲空間：索引本身需要占用磁盤空間。*維護(hù)成本：插入、刪除、更新數(shù)據(jù)時(shí)，需要同時(shí)更新索引，這增加了寫操作的開銷。索引頁的分裂、合并、碎片整理等也是維護(hù)成本。兩種常見索引類型：*B+樹索引：最常見的索引類型，葉子節(jié)點(diǎn)包含鍵值和指向數(shù)據(jù)記錄的指針，非葉子節(jié)點(diǎn)僅包含鍵值。支持范圍查詢和排序。*哈希索引：基于哈希函數(shù)將鍵值映射到特定位置，適用于等值查詢，查找速度快，但不支持范圍查詢。2.不可重復(fù)讀現(xiàn)象：指在一個(gè)事務(wù)內(nèi)，對同一數(shù)據(jù)項(xiàng)多次讀取，但兩次讀取的結(jié)果不一致，并且這種不一致是由于另一個(gè)并發(fā)事務(wù)在此期間修改了數(shù)據(jù)導(dǎo)致的。出現(xiàn)隔離級別：在數(shù)據(jù)庫的“可重復(fù)讀”（REPEATABLEREAD）隔離級別下會出現(xiàn)不可重復(fù)讀現(xiàn)象。例如，事務(wù)T1在讀取某個(gè)數(shù)據(jù)值后，事務(wù)T2對該數(shù)據(jù)進(jìn)行了修改并提交，當(dāng)事務(wù)T1再次讀取同一數(shù)據(jù)時(shí)，會得到不同的值。雖然它避免了臟讀（讀取未提交數(shù)據(jù)），但仍然允許事務(wù)T2的修改影響事務(wù)T1后續(xù)的讀取。3.查詢優(yōu)化器主要階段：數(shù)據(jù)庫查詢優(yōu)化器通常包含以下主要階段：*解析（Parsing）：分析SQL語句的語法，檢查其是否符合語法規(guī)則。*語義分析（SemanticAnalysis）：檢查SQL語句中的表、列、別名等是否存在于數(shù)據(jù)庫中，檢查數(shù)據(jù)類型匹配等。*邏輯優(yōu)化（LogicalOptimization）：生成等價(jià)但更有效的邏輯查詢計(jì)劃，通常以表達(dá)式樹或關(guān)系代數(shù)的形式表示。主要技術(shù)包括：查詢重寫（如連接順序選擇、謂詞下推）、謂詞下推、視圖展開等。*物理優(yōu)化（PhysicalOptimization）：將邏輯查詢計(jì)劃轉(zhuǎn)換為具體的物理執(zhí)行計(jì)劃，選擇具體的操作策略和數(shù)據(jù)訪問方法。主要技術(shù)包括：選擇合適的掃描方法（全表掃描、索引掃描）、選擇合適的連接算法（嵌套循環(huán)、哈希連接、排序合并連接）、選擇合適的索引、考慮并行執(zhí)行等。*生成執(zhí)行計(jì)劃（PlanGeneration）：將選定的物理執(zhí)行計(jì)劃生成可執(zhí)行的內(nèi)部形式。*成本評估與選擇（CostEvaluationandSelection）：為每個(gè)可能的執(zhí)行計(jì)劃估算其執(zhí)行成本（如CPU時(shí)間、I/O次數(shù)），選擇成本最低的計(jì)劃作為最終執(zhí)行計(jì)劃。邏輯優(yōu)化vs物理優(yōu)化：*邏輯優(yōu)化關(guān)注查詢邏輯的等價(jià)變換，目標(biāo)是生成更簡潔、更符合數(shù)據(jù)庫邏輯結(jié)構(gòu)的表達(dá)式或關(guān)系操作，主要在內(nèi)存中進(jìn)行，不涉及具體的I/O操作。*物理優(yōu)化關(guān)注執(zhí)行操作的效率，選擇具體的訪問方法、連接算法、掃描策略等，主要涉及I/O操作和CPU計(jì)算，目標(biāo)是找到執(zhí)行代價(jià)最低的物理實(shí)現(xiàn)方式。四、計(jì)算題計(jì)算過程：1.嵌套循環(huán)連接（NestedLoopJoin）成本：基礎(chǔ)成本=Student表掃描成本（1000頁）+Course表掃描成本（5000頁）*讀取Course表每一行都需要檢查Student表（假設(shè)為S表大?。┛偝杀?1000+5000*(1000/0.1)*(I/O成本)+5000*(1000/0.1)*(I/O成本)=1000+5000*10000*I/O+5000*10000*I/O=1000+100000000*I/O+100000000*I/O=1000+200000000*I/O*注意：此計(jì)算假設(shè)NestLoop成本估算包含了表掃描和行比較，這里簡化為按行數(shù)估算。更標(biāo)準(zhǔn)的成本估算會考慮索引查找成本。但題目給定了總成本為5000。*根據(jù)題目，NestLoop成本=5000。2.哈希連接（HashJoin）成本：基礎(chǔ)成本=讀取Student表并建立哈希表成本+讀取Course表并執(zhí)行哈希匹配成本總成本=1000*(I/O成本)+5000*(1000/0.1)*(I/O成本)=1000*I/O+5000*10000*I/O=1000*I/O+50000000*I/O=51000000*I/O*同樣，此計(jì)算方式簡化了。題目給定了總成本為2000。*根據(jù)題目，HashJoin成本=2000。3.基于索引的掃描成本：假設(shè)使用C.Grade索引來查找Grade>90的記錄。成本=讀取C.Grade索引成本+對于每個(gè)Grade>90的索引項(xiàng)，通過指針回表查找對應(yīng)Course記錄并判斷是否滿足S.SID匹配成本。讀取索引成本≈1000/0.05=20000頁索引(估算)回表成本≈20000頁索引*(5000/0.1)頁Course表/20000=5000*500=2500000頁Course表(估算)總成本≈1000+2500000*I/O≈250001000*I/O*再次強(qiáng)調(diào)，這是非常簡化的估算。題目給定了總成本為1000。*根據(jù)題目，IndexScan成本=1000。4.選擇最優(yōu)策略：比較三種策略的成本：*HashJoin:2000*NestedLoopJoin:5000*IndexScan:1000顯然，基于索引的掃描（IndexScan）成本最低。因此，優(yōu)化器應(yīng)選擇使用基于C.Grade索引的掃描策略來執(zhí)行此查詢。五、論述題數(shù)據(jù)庫查詢優(yōu)化器是一個(gè)自動化的系統(tǒng)組件，其核心目標(biāo)是針對用戶提交的SQL查詢，在保證查詢結(jié)果正確的前提下，找到并生成一個(gè)執(zhí)行代價(jià)最低（通常指CPU時(shí)間、磁盤I/O次數(shù)等）的物理執(zhí)行計(jì)劃，以提升數(shù)據(jù)庫查詢性能。優(yōu)化器的工作過程通常遵循解析、語義分析、邏輯優(yōu)化、物理優(yōu)化、執(zhí)行計(jì)劃生成和成本評估與選擇等階段。在邏輯優(yōu)化階段，優(yōu)化器主要關(guān)注查詢邏輯的等價(jià)變換，目的是將輸入的SQL查詢轉(zhuǎn)換成一個(gè)在邏輯上等價(jià)但更優(yōu)化的邏輯查詢計(jì)劃。主要采用的優(yōu)化技術(shù)包括：1.查詢重寫（QueryRewrite）：將一個(gè)查詢表達(dá)式等價(jià)地轉(zhuǎn)換為另一個(gè)可能更高效的查詢表達(dá)式。例如，將某些連接操作轉(zhuǎn)換為嵌套循環(huán)、哈希連接或排序合并連接；將嵌套查詢轉(zhuǎn)換為連接查詢；應(yīng)用反規(guī)范化技術(shù)；利用視圖展開等。2.謂詞下推（PredicatePushdown）：將查詢條件（WHERE子句或連接條件）盡可能早地應(yīng)用到靠近數(shù)據(jù)源的查詢部分。例如，如果一個(gè)連接操作是`AJOINBONA.x=B.y`，如果A表較小，可以將`A.x=B.y`下推到先掃描A表時(shí)用于過濾。這可以減少需要與B表連接的A表元組數(shù)量，或者減少需要與A表連接的B表元組數(shù)量。3.常量折疊（ConstantFolding）：在查詢編譯時(shí)計(jì)算常量表達(dá)式的值，如`SELECT*FROMEmployeeWHEREAge>30`可以在邏輯優(yōu)化時(shí)判斷出過濾條件為`Age>30`，如果已知Age是整數(shù)類型，可以進(jìn)一步處理（雖然此例較簡單）。4.表達(dá)式簡化（ExpressionSimplification）：簡化查詢中出現(xiàn)的選擇列表、連接條件或WHERE子句中的表達(dá)式，如移除無用的函數(shù)調(diào)用、簡化算術(shù)表達(dá)式等。在物理優(yōu)化階段，優(yōu)化器在邏輯查詢計(jì)劃的基礎(chǔ)上，選擇具體的、可執(zhí)行的物理操作步驟和策略。主要采用的優(yōu)化技術(shù)包括：1.掃描方法選擇：選擇如何讀取數(shù)據(jù)。對于基礎(chǔ)表，可以選擇全表掃描（FullTableScan）或索引掃描（IndexScan）。索引掃描根據(jù)索引類型（B+樹、哈希等）和查詢條件選擇合適的索引（如范圍掃描、點(diǎn)查詢）。對于結(jié)果集，可以選擇排序合并連接（Sort-MergeJoin）的結(jié)果集進(jìn)行掃描。2.連接算法選擇：選擇如何執(zhí)行表之間的連接操作。常見的連接算法有：*嵌套循環(huán)連接（NestedLoopJoin,NLJ）：外循環(huán)表中的每一行，內(nèi)循環(huán)掃描內(nèi)表查找匹配行。適用于內(nèi)表較小或連接條件可以高效地通過索引滿足。*哈希連接（HashJoin）：分為建立階段（讀取一個(gè)表，建立哈希表）和查找階段（讀取另一個(gè)表，在哈希表中查找匹配）。適用于較大的表，連接條件可以通過索引高效地獲取行。*排序合并連接（Sort-MergeJoin,SMJ）：先對兩個(gè)表分別進(jìn)行排序（基于連接鍵），然后按順序合并。適用于兩個(gè)表都很大，且可以通過索引或排序操作高效地獲取排序后的數(shù)據(jù)。3.索引選擇：決定是否使用索引以及使用哪個(gè)索引來加速查詢。這需要評估使用索引帶來的好處（減少I/O次數(shù)）與壞處（索引維護(hù)開銷、索引查找開銷）。4.并行執(zhí)行：對于大查詢或大數(shù)據(jù)集，優(yōu)化器可能會選擇并行執(zhí)行查詢的某些部分或全部，以利用多核CPU或多臺機(jī)器提高性能。影響索引選擇的關(guān)鍵因素主要包括：1.查詢的選擇度（Selectivity