2026年數(shù)據(jù)庫性能優(yōu)化與SQL調(diào)優(yōu)筆試題目一、單選題（共10題，每題2分，共20分）1.在MySQL中，以下哪種索引類型最適合用于經(jīng)常需要全文搜索的場景？A.普通索引B.唯一索引C.全文索引D.組合索引2.在Oracle數(shù)據(jù)庫中，以下哪個(gè)參數(shù)主要用于控制內(nèi)存中PL/SQL緩存的命中率？A.PGA_AGGREGATE_TARGETB.DB_CACHE_SIZEC.SQLnet.service_nameD.LOG_BUFFER3.當(dāng)數(shù)據(jù)庫中出現(xiàn)鎖等待問題時(shí)，以下哪種方法最能有效識(shí)別鎖的持有者？A.`EXPLAIN`語句B.`SHOWPROCESSLIST`（MySQL）C.`DBCCSQLPERF`（SQLServer）D.`ANALYZE`命令4.在SQLServer中，以下哪種索引優(yōu)化策略適用于大量插入、刪除操作的場景？A.超大表使用堆表B.創(chuàng)建覆蓋索引C.使用索引提示D.索引分頁5.當(dāng)查詢中存在多個(gè)JOIN操作時(shí)，以下哪種優(yōu)化方法最能有效減少數(shù)據(jù)掃描量？A.增加索引B.使用子查詢C.優(yōu)化JOIN順序D.使用臨時(shí)表6.在PostgreSQL中，以下哪種方法可以用于臨時(shí)提升特定查詢的并行度？A.設(shè)置`work_mem`參數(shù)B.使用`EXPLAINANALYZE`C.調(diào)整`max_parallel_workers_per_query`D.創(chuàng)建并行索引7.在數(shù)據(jù)庫分區(qū)表中，以下哪種場景最適合使用范圍分區(qū)？A.按主鍵分區(qū)B.按時(shí)間范圍分區(qū)C.按哈希分區(qū)D.按列表分區(qū)8.當(dāng)查詢中頻繁使用LIKE'%keyword%'時(shí)，以下哪種優(yōu)化方法最有效？A.創(chuàng)建全文索引B.使用普通索引C.將LIKE條件改為LIKE'keyword%'D.使用OR條件拆分查詢9.在MySQL中，以下哪種方法可以用于減少事務(wù)日志的寫入量？A.開啟InnoDB_flush_log_at_trx_commit=2B.增加innodb_buffer_pool_sizeC.使用批量插入D.創(chuàng)建更復(fù)雜的索引10.在SQLServer中，以下哪種索引類型最適合用于高基數(shù)列（唯一值多）？A.B-Tree索引B.Hash索引C.Clustered索引D.Filtered索引二、多選題（共5題，每題3分，共15分）1.在數(shù)據(jù)庫性能優(yōu)化中，以下哪些方法可以有效減少慢查詢？A.增加數(shù)據(jù)庫緩存B.優(yōu)化查詢邏輯C.使用分區(qū)表D.增加硬件資源E.忽略低頻查詢2.當(dāng)數(shù)據(jù)庫出現(xiàn)死鎖時(shí)，以下哪些方法可以用于排查？A.查看系統(tǒng)表（如MySQL的`INNODB_TRX`）B.使用`DBCCSQLLOCK`（SQLServer）C.調(diào)整事務(wù)隔離級別D.禁用索引E.增加事務(wù)超時(shí)時(shí)間3.在SQLServer中，以下哪些參數(shù)與內(nèi)存優(yōu)化相關(guān)？A.`maxservermemory`B.`tempdb`文件設(shè)置C.`querygovernor`D.`bufferpoolextension`E.`maxdegreeofparallelism`4.在PostgreSQL中，以下哪些方法可以提高查詢性能？A.使用CTE（公用表表達(dá)式）B.創(chuàng)建物化視圖C.調(diào)整`work_mem`參數(shù)D.使用索引覆蓋E.禁用統(tǒng)計(jì)信息收集5.在Oracle數(shù)據(jù)庫中，以下哪些操作可能導(dǎo)致索引失效？A.表結(jié)構(gòu)變更B.大量INSERT操作C.使用函數(shù)計(jì)算列值D.索引重建E.更新索引列的值三、簡答題（共5題，每題5分，共25分）1.簡述數(shù)據(jù)庫索引失效的常見原因，并舉例說明。2.解釋什么是數(shù)據(jù)庫分區(qū)，并列舉至少三種分區(qū)類型及其適用場景。3.在SQLServer中，如何使用`SETSTATISTICSIO`命令分析查詢的I/O消耗？4.簡述事務(wù)隔離級別及其對性能的影響，并說明SQLServer的默認(rèn)隔離級別。5.當(dāng)數(shù)據(jù)庫出現(xiàn)全表掃描時(shí)，如何通過SQL語句定位問題？四、計(jì)算題（共2題，每題10分，共20分）1.假設(shè)一個(gè)SQL查詢的執(zhí)行計(jì)劃如下：sqlSELECTa.id,FROMtable_aASaJOINtable_bASbONa.id=b.a_idWHEREa.status='active'ANDb.type='type1';-table_a有10萬行數(shù)據(jù)，索引在`status`列上；-table_b有5萬行數(shù)據(jù)，索引在`a_id`和`type`列上；-查詢中`status='active'`的占比為20%，`type='type1'`的占比為30%；-JOIN操作假設(shè)為嵌套循環(huán)，不考慮索引合并。請計(jì)算：a.若`status`和`type`的索引未被有效利用，預(yù)估掃描行數(shù)。b.若`table_b`的`a_id`和`type`索引被合并使用，預(yù)估掃描行數(shù)。2.假設(shè)一個(gè)表`orders`有500萬行數(shù)據(jù)，其中`order_date`列無索引，查詢?nèi)缦拢簊qlSELECTCOUNT()FROMordersWHEREorder_dateBETWEEN'2023-01-01'AND'2023-12-31';-數(shù)據(jù)庫為MySQL，假設(shè)表掃描每次IO讀取1MB數(shù)據(jù)，服務(wù)器每秒IO能力為100MB。請計(jì)算：a.若表掃描無索引，預(yù)估執(zhí)行時(shí)間（秒）。b.若`order_date`列添加索引，預(yù)估執(zhí)行時(shí)間（假設(shè)索引掃描效率為表掃描的10%）。五、綜合分析題（共2題，每題10分，共20分）1.某公司數(shù)據(jù)庫出現(xiàn)頻繁的鎖等待，日志顯示以下SQL語句長時(shí)間阻塞：sqlSELECTFROMordersWHEREorder_id=1000FORUPDATE;-表`orders`有大量并發(fā)寫入操作；-查詢涉及索引`order_id`，但存在鎖升級問題。請分析可能的原因，并提出至少三種優(yōu)化建議。2.某電商平臺(tái)的訂單表`orders`查詢性能下降，主要表現(xiàn)為：-查詢`SELECTFROMordersWHEREuser_id=?`響應(yīng)時(shí)間增加；-`user_id`列有索引，但執(zhí)行計(jì)劃顯示全表掃描。請分析可能的原因，并提出優(yōu)化方案。答案與解析一、單選題答案與解析1.C-解析：全文索引專為文本搜索設(shè)計(jì)，如MySQL的`FULLTEXT`索引、Oracle的`INDEXTYPEISFULLTEXT`等。普通索引和唯一索引不支持全文搜索，組合索引用于多列組合查詢。2.B-解析：`DB_CACHE_SIZE`控制Oracle的數(shù)據(jù)庫緩沖區(qū)大小，用于緩存數(shù)據(jù)塊和PL/SQL代碼，直接影響緩存命中率。PGA_AGGREGATE_TARGET控制PGA大小，SQLnet.service_name用于網(wǎng)絡(luò)配置，LOG_BUFFER控制日志緩沖區(qū)。3.B-解析：`SHOWPROCESSLIST`（MySQL）和`sp_who2`（SQLServer）可實(shí)時(shí)查看鎖等待和阻塞進(jìn)程。`EXPLAIN`分析查詢計(jì)劃，`ANALYZE`更新統(tǒng)計(jì)信息。4.A-解析：堆表（無主鍵索引）適用于高并發(fā)寫入場景，避免索引維護(hù)開銷。覆蓋索引、索引提示和索引分頁更適用于讀優(yōu)化。5.C-解析：優(yōu)化JOIN順序（如先連接小表）可減少中間結(jié)果集大小，降低掃描量。其他選項(xiàng)效果有限或錯(cuò)誤。6.C-解析：`max_parallel_workers_per_query`控制并行查詢線程數(shù)，臨時(shí)提升并行度。`work_mem`用于內(nèi)存臨時(shí)表，`EXPLAINANALYZE`分析執(zhí)行計(jì)劃，并行索引需先創(chuàng)建。7.B-解析：范圍分區(qū)適用于時(shí)間、ID等有序數(shù)據(jù)，如按月份、年份分區(qū)。其他分區(qū)類型如列表分區(qū)（固定值）、哈希分區(qū)（隨機(jī)分布）適用場景不同。8.C-解析：LIKE'%keyword%'無法利用索引，而`LIKE'keyword%'`可索引。全文索引和普通索引對前綴匹配效果較好。9.A-解析：`InnoDB_flush_log_at_trx_commit=2`延遲寫入日志，減少寫入量但可能丟失數(shù)據(jù)。其他選項(xiàng)與日志寫入無關(guān)。10.B-解析：Hash索引適用于高基數(shù)列（唯一值多），如`PRIMARYKEY`列。B-Tree索引適用于范圍查詢，Clustered索引為物理排序，F(xiàn)iltered索引為條件過濾。二、多選題答案與解析1.A,B,C,D-解析：增加緩存、優(yōu)化邏輯、分區(qū)表和硬件資源都是有效方法。忽略低頻查詢可能導(dǎo)致問題被掩蓋。2.A,B,C,E-解析：系統(tǒng)表、鎖診斷工具、隔離級別調(diào)整和超時(shí)設(shè)置可排查死鎖。禁用索引會(huì)加劇問題。3.A,B,D,E-解析：內(nèi)存參數(shù)包括服務(wù)器總內(nèi)存、臨時(shí)表文件、緩沖池?cái)U(kuò)展和并行度。`querygovernor`是SQLServer的查詢超時(shí)設(shè)置。4.A,B,C,D-解析：CTE、物化視圖、`work_mem`和索引覆蓋可優(yōu)化查詢。禁用統(tǒng)計(jì)信息會(huì)導(dǎo)致查詢計(jì)劃錯(cuò)誤。5.A,C,E-解析：表結(jié)構(gòu)變更、函數(shù)計(jì)算列值和更新索引列值會(huì)觸發(fā)索引失效。重建索引會(huì)臨時(shí)失效但最終生效。三、簡答題答案與解析1.索引失效原因及示例-原因：1.范圍查詢（如`>`、`<`）不利用索引；2.函數(shù)計(jì)算列值（如`UPPER(column)`）；3.OR條件拆分索引（如`WHEREa=1ORb=2`）；4.表結(jié)構(gòu)變更（如刪除索引列）。-示例：sql--范圍查詢失效SELECTFROMusersWHEREage>30;--age無索引--函數(shù)計(jì)算失效SELECTFROMproductsWHEREUPPER(name)='APPLE';--name無索引2.數(shù)據(jù)庫分區(qū)及類型-分區(qū)：按規(guī)則將表數(shù)據(jù)拆分到多個(gè)物理部分，提高管理性和性能。-類型：1.范圍分區(qū)：按列值范圍（如時(shí)間、ID）；2.列表分區(qū)：按列值固定列表（如地區(qū)）；3.哈希分區(qū)：按列值哈希值（隨機(jī)分布）；4.復(fù)合分區(qū)：結(jié)合多種規(guī)則。3.`SETSTATISTICSIO`分析-命令：sqlSETSTATISTICSIOON;SELECTFROMordersWHEREuser_id=1;-輸出：顯示`logicalreads`（邏輯讀取次數(shù)）和`physicalreads`（物理讀取次數(shù)），幫助定位I/O瓶頸。4.事務(wù)隔離級別及影響-級別：1.讀未提交：最低，可見未提交數(shù)據(jù)（臟讀）；2.讀已提交：防止臟讀，但仍可見未提交寫（不可重復(fù)讀）；3.可重復(fù)讀：防止臟讀和不可重復(fù)讀，但可見幻讀；4.串行化：最高，完全隔離（但性能最低）。-SQLServer默認(rèn)：`READCOMMITTED`（讀已提交）。5.全表掃描定位方法-方法：1.`EXPLAIN`分析執(zhí)行計(jì)劃；2.查看慢查詢?nèi)罩荆ㄈ鏜ySQL的`slow_query_log`）；3.監(jiān)控工具（如SQLServerProfiler）抓取執(zhí)行計(jì)劃。四、計(jì)算題答案與解析1.執(zhí)行計(jì)劃預(yù)估a.無索引掃描行數(shù)：-table_a：10萬行（20%被過濾，掃描8萬行）；-table_b：5萬行（30%被過濾，掃描3.5萬行）；-JOIN：8萬×3.5萬=28億行（實(shí)際不可能，需考慮索引）。b.索引合并掃描行數(shù)：-table_a：10萬×30%=3萬行（按`a_id`過濾）；-JOIN：3萬行（直接匹配）。2.表掃描時(shí)間預(yù)估a.無索引：-掃描量：500萬×1MB=500GB；-時(shí)間：500GB/100MB/s=50秒。b.有索引：-掃描量：500萬×0.1MB=50GB；-時(shí)間：50GB/100MB/s=5秒。五、綜合分析題答案與解析1.鎖等待優(yōu)化-原因：1.`FORUPDATE`鎖定大量行；2.索引選擇不當(dāng)（如非主鍵）；3.事務(wù)