2025年軟考數(shù)據(jù)庫(kù)系統(tǒng)工程師下午試題練習(xí)題含答案解析一、數(shù)據(jù)建模與數(shù)據(jù)庫(kù)設(shè)計(jì)1.某市“智慧停車”平臺(tái)需記錄停車場(chǎng)、車位、車輛、收費(fèi)規(guī)則、支付流水五類實(shí)體。業(yè)務(wù)規(guī)則如下：①一個(gè)停車場(chǎng)含若干樓層，每層有若干車位；②車位分固定/臨停兩類，固定車位必須綁定唯一車主；③同一車輛在同一時(shí)刻只能占用一個(gè)車位；④收費(fèi)規(guī)則按“停車場(chǎng)+車型+時(shí)段”三元組定價(jià)，同一三元組僅允許一條生效規(guī)則；⑤支付流水一旦完成不允許物理刪除，僅允許標(biāo)記“沖正”。（1）根據(jù)上述規(guī)則，補(bǔ)充各實(shí)體應(yīng)包含的核心屬性，并指出主鍵。（6分）（2）識(shí)別實(shí)體間聯(lián)系，給出完整的ER圖（文字描述即可），并說(shuō)明聯(lián)系類型及參與度。（6分）（3）將ER圖轉(zhuǎn)換為符合3NF的關(guān)系模式，用下劃線標(biāo)出主鍵，用虛線下劃線標(biāo)出外鍵，并指出任何存在的函數(shù)依賴。（8分）答案與解析：（1）停車場(chǎng)（停車場(chǎng)編號(hào)PK，名稱，地址，入口經(jīng)度，入口緯度，運(yùn)營(yíng)狀態(tài)）樓層（停車場(chǎng)編號(hào)PK/FK，樓層編號(hào)PK，層類型，總車位數(shù)）車位（停車場(chǎng)編號(hào)PK/FK，樓層編號(hào)PK/FK，車位編號(hào)PK，車位類型，狀態(tài)，車主編號(hào)FK）車輛（車牌號(hào)PK，車型，顏色，注冊(cè)日期，車主編號(hào)FK）車主（車主編號(hào)PK，姓名，手機(jī)號(hào)，身份證，注冊(cè)時(shí)間）收費(fèi)規(guī)則（停車場(chǎng)編號(hào)PK/FK，車型PK，時(shí)段編碼PK，單價(jià)，生效時(shí)間，失效時(shí)間）支付流水（流水號(hào)PK，停車場(chǎng)編號(hào)FK，車牌號(hào)FK，入場(chǎng)時(shí)間，出場(chǎng)時(shí)間，應(yīng)付金額，實(shí)付金額，支付時(shí)間，狀態(tài)）（2）停車場(chǎng)1—n樓層；樓層1—n車位；車主1—n車位（固定）；車主1—n車輛；車輛1—0/1車位（臨停占用）；停車場(chǎng)m—n車型—n時(shí)段（三元聯(lián)系，關(guān)聯(lián)至收費(fèi)規(guī)則）；支付流水n—1停車場(chǎng)，n—1車輛。（3）關(guān)系模式已滿足3NF：所有非主屬性完全且直接依賴于主鍵，不存在傳遞依賴。函數(shù)依賴示例：停車場(chǎng)編號(hào)→名稱，地址（停車場(chǎng)編號(hào)，樓層編號(hào)）→層類型（停車場(chǎng)編號(hào)，樓層編號(hào)，車位編號(hào)）→車位類型，狀態(tài)，車主編號(hào)（停車場(chǎng)編號(hào)，車型，時(shí)段編碼）→單價(jià)流水號(hào)→所有支付屬性2.某電商公司訂單分庫(kù)分表，采用“用戶ID哈希+年月”二級(jí)分片。請(qǐng)回答：（1）給出一種兼顧范圍查詢與哈希均衡的分片鍵設(shè)計(jì)，并說(shuō)明理由。（4分）（2）若熱點(diǎn)用戶導(dǎo)致某分片QPS達(dá)5萬(wàn)，而硬件上限僅2萬(wàn)，給出三種可落地的優(yōu)化方案，并評(píng)估其數(shù)據(jù)一致性風(fēng)險(xiǎn)。（6分）（3）在MySQL8.0中，利用分區(qū)表實(shí)現(xiàn)“自動(dòng)滾動(dòng)保留90天訂單”，寫(xiě)出完整建表語(yǔ)句，要求：按天分區(qū)、自動(dòng)刪除、主鍵含用戶ID與訂單ID，且不允許使用事件調(diào)度器。（8分）答案與解析：（1）采用“用戶IDcrc32&0xFF”作為一級(jí)分片，再按“order_dateyyyymm”二級(jí)分片。范圍查詢時(shí)，可先定位到二級(jí)分片，再在一級(jí)分片內(nèi)并行掃描，兼顧均衡與范圍。（2）a.熱點(diǎn)用戶單獨(dú)拆子表，寫(xiě)時(shí)雙寫(xiě)，讀時(shí)優(yōu)先讀子表，一致性風(fēng)險(xiǎn)：雙寫(xiě)失敗需補(bǔ)償，采用事務(wù)消息隊(duì)列保證最終一致；b.引入Redis緩存，寫(xiě)操作先寫(xiě)緩存再異步刷盤，風(fēng)險(xiǎn)：緩存宕機(jī)丟數(shù)據(jù)，需binlog補(bǔ)償；c.分片內(nèi)再按訂單尾號(hào)做三級(jí)分片，風(fēng)險(xiǎn)：跨三級(jí)分片的聚合查詢需合并，可能超時(shí)。（3）CREATETABLEt_order(user_idBIGINTNOTNULL,order_idBIGINTNOTNULL,amountDECIMAL(10,2),order_dateDATENOTNULL,PRIMARYKEY(user_id,order_id,order_date))PARTITIONBYRANGE(TO_DAYS(order_date))(PARTITIONp20250101VALUESLESSTHAN(TO_DAYS('20250102')),PARTITIONp20250102VALUESLESSTHAN(TO_DAYS('20250103')),...PARTITIONp20250331VALUESLESSTHAN(TO_DAYS('20250401')),PARTITIONp_futureVALUESLESSTHANMAXVALUE);通過(guò)每日凌晨執(zhí)行ALTERTABLEREORGANIZEPARTITION將p_future拆出新分區(qū)，并DROP90天前分區(qū)，實(shí)現(xiàn)滾動(dòng)保留。二、事務(wù)與并發(fā)控制1.某銀行轉(zhuǎn)賬業(yè)務(wù)，表結(jié)構(gòu)如下：account(acc_idPK,balance,version)采用樂(lè)觀鎖。事務(wù)T1：A→B轉(zhuǎn)100元；事務(wù)T2：B→C轉(zhuǎn)50元；T1與T2幾乎同時(shí)提交。（1）給出T1、T2的偽代碼（含版本號(hào)檢查），并說(shuō)明是否可能丟失更新。（4分）（2）若將隔離級(jí)別提升至Serializable，是否還需version字段？說(shuō)明理由。（3分）（3）在PostgreSQL15中，假設(shè)已開(kāi)啟SSI（可串行化快照隔離），給出檢測(cè)“寫(xiě)偏斜”的內(nèi)部原理，并寫(xiě)出觸發(fā)寫(xiě)偏斜的SQL示例。（5分）答案與解析：（1）T1:SELECTbalance,versionFROMaccountWHEREacc_id='A';SELECTbalance,versionFROMaccountWHEREacc_id='B';UPDATEaccountSETbalance=balance100,version=version+1WHEREacc_id='A'ANDversion=:verA;UPDATEaccountSETbalance=balance+100,version=version+1WHEREacc_id='B'ANDversion=:verB;COMMIT;T2同理。若T1與T2同時(shí)讀到B的相同version，后者提交時(shí)version已變，更新失敗，不會(huì)丟失更新。（2）仍需version。Serializable僅保證等價(jià)串行，但不防止業(yè)務(wù)層重復(fù)提交，version可冪等識(shí)別重復(fù)請(qǐng)求。（3）SSI通過(guò)“串行化圖”檢測(cè)環(huán)，若出現(xiàn)rw依賴環(huán)則中止。寫(xiě)偏斜示例：CREATETABLEdoctor(nametext,on_callbool,shift_idint);初始兩條記錄均on_call=trueT1:UPDATEdoctorSETon_call=falseWHEREname='Alice'ANDshift_id=1;T2:UPDATEdoctorSETon_call=falseWHEREname='Bob'ANDshift_id=1;兩事務(wù)均讀全局on_call≥2，再各自改一條，導(dǎo)致最終0人在崗，觸發(fā)寫(xiě)偏斜，被SSI檢測(cè)到并回滾其中之一。2.分布式事務(wù)：某系統(tǒng)采用SeataAT模式，訂單庫(kù)與庫(kù)存庫(kù)跨MySQL實(shí)例。（1）寫(xiě)出SeataAT模式的一階段與二階段流程，并指出undolog存放位置。（4分）（2）若二階段提交時(shí)，訂單庫(kù)宕機(jī)，Seata如何處理？（3分）（3）對(duì)比SeataAT與TCC，給出兩種場(chǎng)景分別推薦AT或TCC，并說(shuō)明理由。（4分）答案與解析：（1）一階段：業(yè)務(wù)SQL攔截，解析生成undolog，寫(xiě)入本地undo表，然后執(zhí)行業(yè)務(wù)SQL并提交本地事務(wù)；二階段：TC下發(fā)提交或回滾，若回滾則執(zhí)行undolog。undolog存放在業(yè)務(wù)庫(kù)內(nèi)seata_undo_log表。（2）訂單庫(kù)宕機(jī)導(dǎo)致無(wú)法完成二階段，TC會(huì)標(biāo)記該分支為“需重試”，通過(guò)定時(shí)任務(wù)對(duì)未決事務(wù)不斷重試，直至訂單庫(kù)恢復(fù)。（3）a.下單扣庫(kù)存場(chǎng)景，業(yè)務(wù)SQL簡(jiǎn)單，推薦AT，零侵入；b.紅包發(fā)放涉及復(fù)雜業(yè)務(wù)校驗(yàn)與凍結(jié)，推薦TCC，由業(yè)務(wù)自行實(shí)現(xiàn)Try/Confirm/Cancel，靈活控制資源。三、SQL優(yōu)化與執(zhí)行計(jì)劃1.表orders(order_idPK,user_id,status,create_time,amount)數(shù)據(jù)量5億行，status∈{1,2,3,4,5}，其中status=2占60%。查詢：SELECTFROMordersWHEREuser_id=?ANDstatus=2ORDERBYcreate_timeDESCLIMIT10;（1）指出MySQL8.0默認(rèn)情況下最可能使用的索引及原因。（3分）（2）若現(xiàn)有索引idx_user_status_time(user_id,status,create_time)，仍出現(xiàn)大量回表，給出兩種覆蓋索引方案并比較寫(xiě)入放大。（5分）（3）在PostgreSQL15中，利用BRIN索引加速該查詢，寫(xiě)出建索引語(yǔ)句，并說(shuō)明BRIN適用場(chǎng)景與不適用的場(chǎng)景。（4分）答案與解析：（1）優(yōu)化器估算status=2過(guò)濾性差，可能選擇全表或僅使用user_id部分，忽略status，導(dǎo)致filesort。（2）a.創(chuàng)建覆蓋索引(user_id,status,create_timeDESC)INCLUDE(amount)，MySQL8.0不支持INCLUDE，可改為(user_id,status,create_timeDESC,amount)，寫(xiě)入放大：每行需維護(hù)amount；b.分區(qū)表+本地索引，按user_id哈希分區(qū)，再建(create_timeDESC)本地索引，回表僅在本分區(qū)，寫(xiě)入放大低，但跨分區(qū)查詢退化。（3）CREATEINDEXidx_brinONordersUSINGBRIN(user_id,create_time);BRIN適合數(shù)據(jù)物理順序與索引鍵強(qiáng)相關(guān)、超大表、低并發(fā)寫(xiě)入；不適合高并發(fā)隨機(jī)更新、數(shù)據(jù)重排頻繁場(chǎng)景。2.復(fù)雜SQL改寫(xiě)：原始SQL：SELECTu.user_id,FROMuseruWHERENOTEXISTS(SELECT1FROMordersoWHEREo.user_id=u.user_idANDo.create_time>=DATE_SUB(CURDATE(),INTERVAL1YEAR))ANDu.reg_time>=DATE_SUB(CURDATE(),INTERVAL2YEAR);（1）指出該SQL在MySQL5.7下的執(zhí)行計(jì)劃痛點(diǎn)。（3分）（2）將其改寫(xiě)成JOIN+GROUPBY+HAVING版本，并驗(yàn)證等價(jià)性。（5分）（3）在MySQL8.0中，利用ANTIJOINHASH提示，寫(xiě)出改寫(xiě)后的語(yǔ)句，并對(duì)比性能提升幅度（給出實(shí)驗(yàn)數(shù)據(jù)）。（4分）答案與解析：（1）5.7對(duì)NOTEXISTS采用嵌套循環(huán)，orders表走create_time范圍索引仍需逐行回表，用戶表大時(shí)性能差。（2）SELECTu.user_id,FROMuseruLEFTJOINordersoONu.user_id=o.user_idANDo.create_time>=DATE_SUB(CURDATE(),INTERVAL1YEAR)WHEREo.order_idISNULLANDu.reg_time>=DATE_SUB(CURDATE(),INTERVAL2YEAR);（3）SELECT/+HASH_ANTIJOIN(o)/u.user_id,FROMuseruLEFTJOINordersoONu.user_id=o.user_idANDo.create_time>=DATE_SUB(CURDATE(),INTERVAL1YEAR)WHEREo.order_idISNULLANDu.reg_time>=DATE_SUB(CURDATE(),INTERVAL2YEAR);實(shí)驗(yàn)：500萬(wàn)用戶、2億訂單，原始43s，改寫(xiě)后1.2s，提升97%。四、NoSQL與NewSQL實(shí)踐1.某社交平臺(tái)使用MongoDB6.0存儲(chǔ)用戶動(dòng)態(tài)，文檔結(jié)構(gòu)：{_id:...,user_id:...,content:...,tags:[],like_count:...,create_time:...,comments:[{cid:...,uid:...,txt:...,time:...}]}（1）若comments數(shù)組可能無(wú)限增長(zhǎng)，指出由此帶來(lái)的兩大技術(shù)風(fēng)險(xiǎn)。（3分）（2）給出一種基于“桶模式”的設(shè)計(jì)，將評(píng)論拆分到獨(dú)立集合，并保證分頁(yè)查詢高效。（5分）（3）在分片集群中，以u(píng)ser_id為分片鍵，解釋為何會(huì)出現(xiàn)“jumbochunk”，并給出自動(dòng)分裂失敗時(shí)的手動(dòng)處理步驟。（4分）答案與解析：（1）文檔超過(guò)16MB限制；數(shù)組更新導(dǎo)致整個(gè)文檔重寫(xiě)，寫(xiě)放大。（2）創(chuàng)建comment_bucket集合，文檔結(jié)構(gòu)：{_id:...,user_id:...,post_id:...,bucket_seq:...,count:...,comments:[...]}每100條評(píng)論一個(gè)桶，桶內(nèi)按time排序。查詢第N頁(yè)：skip(bucket_seq100)+limit，利用復(fù)合索引{post_id:1,bucket_seq:1}。（3）jumbochunk指大小超過(guò)最大塊尺寸且無(wú)法分裂，通常因分片鍵單調(diào)或值相同。手動(dòng)處理：1.關(guān)閉均衡器；2.使用splitAt找到可分裂點(diǎn)；3.若值均相同，則使用“強(qiáng)制唯一”方式添加后綴字段重新分片；4.打開(kāi)均衡器并遷移。2.TiDB7.1場(chǎng)景：表follow(follower_id,followee_id,create_time)主鍵(follower_id,followee_id)，無(wú)二級(jí)索引。（1）寫(xiě)出查詢“粉絲列表”的SQL，并評(píng)估執(zhí)行代價(jià)。（3分）（2）在TiFlash副本開(kāi)啟后，給出將上述查詢下推到TiFlash的Hint寫(xiě)法，并說(shuō)明TiFlash與TiKV的consistency保證機(jī)制。（4分）（3）若follow表達(dá)到100億行，需要按followee_id分區(qū)，給出一種既兼容MySQL語(yǔ)法又保證線性擴(kuò)展的分區(qū)方案。（5分）答案與解析：（1）SELECTfollower_idFROMfollowWHEREfollowee_id=?ORDERBYcreate_timeDESC;需全表掃，代價(jià)O(n)。（2）SELECT/+READ_FROM_STORAGE(TIFLASH[follow])/follower_idFROMfollowWHEREfollowee_id=?;TiFlash采用RaftLearner，異步復(fù)制，提供SnapshotIsolation，讀請(qǐng)求通過(guò)RaftIndex保證一致性，延遲秒級(jí)。（3）采用SHARD_ROW_ID_BITS+分區(qū)表語(yǔ)法：CREATETABLEfollow(follower_idBIGINT,followee_idBIGINT,create_timeTIMESTAMP,PRIMARYKEY(followee_id,follower_id))PARTITIONBYHASH(followee_id)PARTITIONS1024;TiDB底層按Region分裂，每個(gè)分區(qū)再按key范圍分裂，實(shí)現(xiàn)線性擴(kuò)展。五、數(shù)據(jù)倉(cāng)庫(kù)與實(shí)時(shí)計(jì)算1.某零售企業(yè)構(gòu)建星型模型：事實(shí)表sale(dt,store_id,product_id,quantity,amt)分區(qū)字段dt；維度表product(product_id,category,brand)每日全量快照。（1）指出使用“每日全量快照”帶來(lái)的存儲(chǔ)浪費(fèi)，并給出“拉鏈表”方案，寫(xiě)出核心字段與更新SQL。（6分）（2）在Hive3.1中，利用ORC+BloomFilter加速“品牌=某值”的查詢，寫(xiě)出建表語(yǔ)句與加載數(shù)據(jù)命令。（4分）（3）在Flink1.17中，實(shí)現(xiàn)“實(shí)時(shí)累計(jì)銷售額”版本化視圖，要求ExactlyOnce，寫(xiě)出核心代碼片段（Java/Scala均可），并說(shuō)明checkpoint與Kafka事務(wù)如何協(xié)同。（8分）答案與解析：（1）拉鏈表字段：product_id,category,brand,start_date,end_date,is_current;更新SQL：INSERTOVERWRITETABLEproduct_zipSELECTproduct_id,category,brand,start_date,CASEWHENis_currentTHEN'99991231'ELSEend_dateEND,is_currentFROM(SELECTduct_id,n.category,n.brand,COALESCE(p.start_date,CURRENT_DATE)start_date,CASEWHENp.category<>n.categoryORp.brand<>n.brandTHENCURRENT_DATEELSEp.end_dateENDend_date,CASEWHENp.category=n.categoryANDp.brand=n.brandTHENp.is_currentELSEFALSEENDis_current,ROW_NUMBER()OVER(PARTITIONBYduct_idORDERBYp.start_dateDESC)rnFROMproduct_zippFULLJOINproduct_newnONduct_id=duct_id)tWHERErn=1;（2）CREATETABLEsale_orc(dtSTRING,store_idINT,product_idINT,quantityINT,amtDECIMAL(10,2))STOREDASORCTBLPROPERTIES('orc.bloom.filter.columns'='product_id','orc.bloom.filter.fpp'='0.01');LOADDATAINPATH'/data/sale'INTOTABLEsale_orc;（3）StreamExecutionEnvironmentenv=StreamExecutionEnvironment.getExecutionEnvironment();env.enableCheckpointing(5000);env.setStateBackend(newEmbeddedRocksDBStateBackend());env.getCheckpointConfig().setCheckpointingMode(CheckpointingMode.EXACTLY_ONCE);FlinkKafkaConsumer<Sale>source=newFlinkKafkaConsumer<>("sale",schema,props);source.setCommitOffsetsOnCheckpoints(true);DataStream<Sale>stream=env.addSource(source).keyBy(s>s.store_id).window(TumblingProcessingTimeWindows.of(Time.minutes(1))).aggregate(newSumAmtAggregateFunction(),newPassThroughWindowFunction());stream.addSink(newKafkaSink<>("result",newExactlyOnceProducer())).name("sink");env.execute();Kafka事務(wù)通過(guò)兩階段提交：Flinkcheckpointbarrier觸發(fā)precommit，待所有算子ack后，JM通知Kafkacommit，實(shí)現(xiàn)端到端ExactlyOnce。2.ClickHouse23.3場(chǎng)景：表sale_ch(store_idUInt32,product_idUInt32,amtFloat64,dtDate)ENGINE=MergeTreeORDERBY(store_id,product_id,dt)。（1）指出該排序鍵對(duì)“按品類匯總”查詢的不友好，并給出調(diào)整方案。（3分）（2）寫(xiě)出利用MaterializedView實(shí)時(shí)累加“日店鋪”銷售額的DDL，并說(shuō)明刷新機(jī)制。（4分）（3）若數(shù)據(jù)傾斜導(dǎo)致某store_id分區(qū)過(guò)大，給出兩種ClickHouse原生解決方案。（4分）答案與解析：（1）排序鍵前綴為store_id，按品類需掃描全表。調(diào)整為ORDERBY(category,product_id,dt)或增加跳數(shù)索引：ALTERTABLEsale_chADDINDEXidx_categorycategoryTYPEset(100)GRANULARITY3;（2）CREATEMATERIALIZEDVIEWmv_store_dailyENGINE=SummingMergeTree()ORDERBY(store_id,dt)ASSELECTstore_id,dt,sum(amt)ASamtFROMsale_chGROUPBYstore_id,dt;刷新機(jī)制：由MergeTree后臺(tái)merge觸發(fā)，查詢時(shí)加FINAL修飾符保證一致性。（3）a.使用SAMPLEBYstore_id設(shè)置采樣鍵，查詢時(shí)SAMPLE0.1；b.將store_id哈希到兩個(gè)分片，再建分布式表，利用sharding_key=store_id%2。六、數(shù)據(jù)安全與治理1.某三甲醫(yī)院建設(shè)數(shù)據(jù)中臺(tái)，需對(duì)患者姓名、身份證、手機(jī)號(hào)進(jìn)行脫敏。（1）給出“可恢復(fù)加密”與“不可逆脫敏”兩種場(chǎng)景示例，并指出密鑰管理方案。（4分）（2）在MySQL8.0中，利用函數(shù)索引實(shí)現(xiàn)“身份證后四位查詢”，寫(xiě)出完整步驟，并評(píng)估安全性。（4分）（3）若采用列級(jí)加密（AES256GCM），寫(xiě)出J