2025年软考数据库系统工程师下午试题真题含答案解析

2025年软考数据库系统工程师下午试题练习题含答案解析一、数据建模与数据库设计1.某市“智慧停车”平台需记录停车场、车位、车辆、收费规则、支付流水五类实体。业务规则如下：①一个停车场含若干楼层，每层有若干车位；②车位分固定/临停两类，固定车位必须绑定唯一车主；③同一车辆在同一时刻只能占用一个车位；④收费规则按“停车场+车型+时段”三元组定价，同一三元组仅允许一条生效规则；⑤支付流水一旦完成不允许物理删除，仅允许标记“冲正”。（1）根据上述规则，补充各实体应包含的核心属性，并指出主键。（6分）（2）识别实体间联系，给出完整的ER图（文字描述即可），并说明联系类型及参与度。（6分）（3）将ER图转换为符合3NF的关系模式，用下划线标出主键，用虚线下划线标出外键，并指出任何存在的函数依赖。（8分）答案与解析：（1）停车场（停车场编号PK，名称，地址，入口经度，入口纬度，运营状态）楼层（停车场编号PK/FK，楼层编号PK，层类型，总车位数）车位（停车场编号PK/FK，楼层编号PK/FK，车位编号PK，车位类型，状态，车主编号FK）车辆（车牌号PK，车型，颜色，注册日期，车主编号FK）车主（车主编号PK，姓名，手机号，身份证，注册时间）收费规则（停车场编号PK/FK，车型PK，时段编码PK，单价，生效时间，失效时间）支付流水（流水号PK，停车场编号FK，车牌号FK，入场时间，出场时间，应付金额，实付金额，支付时间，状态）（2）停车场1—n楼层；楼层1—n车位；车主1—n车位（固定）；车主1—n车辆；车辆1—0/1车位（临停占用）；停车场m—n车型—n时段（三元联系，关联至收费规则）；支付流水n—1停车场，n—1车辆。（3）关系模式已满足3NF：所有非主属性完全且直接依赖于主键，不存在传递依赖。函数依赖示例：停车场编号→名称，地址（停车场编号，楼层编号）→层类型（停车场编号，楼层编号，车位编号）→车位类型，状态，车主编号（停车场编号，车型，时段编码）→单价流水号→所有支付属性2.某电商公司订单分库分表，采用“用户ID哈希+年月”二级分片。请回答：（1）给出一种兼顾范围查询与哈希均衡的分片键设计，并说明理由。（4分）（2）若热点用户导致某分片QPS达5万，而硬件上限仅2万，给出三种可落地的优化方案，并评估其数据一致性风险。（6分）（3）在MySQL8.0中，利用分区表实现“自动滚动保留90天订单”，写出完整建表语句，要求：按天分区、自动删除、主键含用户ID与订单ID，且不允许使用事件调度器。（8分）答案与解析：（1）采用“用户IDcrc32&0xFF”作为一级分片，再按“order_dateyyyymm”二级分片。范围查询时，可先定位到二级分片，再在一级分片内并行扫描，兼顾均衡与范围。（2）a.热点用户单独拆子表，写时双写，读时优先读子表，一致性风险：双写失败需补偿，采用事务消息队列保证最终一致；b.引入Redis缓存，写操作先写缓存再异步刷盘，风险：缓存宕机丢数据，需binlog补偿；c.分片内再按订单尾号做三级分片，风险：跨三级分片的聚合查询需合并，可能超时。（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);通过每日凌晨执行ALTERTABLEREORGANIZEPARTITION将p_future拆出新分区，并DROP90天前分区，实现滚动保留。二、事务与并发控制1.某银行转账业务，表结构如下：account(acc_idPK,balance,version)采用乐观锁。事务T1：A→B转100元；事务T2：B→C转50元；T1与T2几乎同时提交。（1）给出T1、T2的伪代码（含版本号检查），并说明是否可能丢失更新。（4分）（2）若将隔离级别提升至Serializable，是否还需version字段？说明理由。（3分）（3）在PostgreSQL15中，假设已开启SSI（可串行化快照隔离），给出检测“写偏斜”的内部原理，并写出触发写偏斜的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同时读到B的相同version，后者提交时version已变，更新失败，不会丢失更新。（2）仍需version。Serializable仅保证等价串行，但不防止业务层重复提交，version可幂等识别重复请求。（3）SSI通过“串行化图”检测环，若出现rw依赖环则中止。写偏斜示例：CREATETABLEdoctor(nametext,on_callbool,shift_idint);初始两条记录均on_call=trueT1:UPDATEdoctorSETon_call=falseWHEREname='Alice'ANDshift_id=1;T2:UPDATEdoctorSETon_call=falseWHEREname='Bob'ANDshift_id=1;两事务均读全局on_call≥2，再各自改一条，导致最终0人在岗，触发写偏斜，被SSI检测到并回滚其中之一。2.分布式事务：某系统采用SeataAT模式，订单库与库存库跨MySQL实例。（1）写出SeataAT模式的一阶段与二阶段流程，并指出undolog存放位置。（4分）（2）若二阶段提交时，订单库宕机，Seata如何处理？（3分）（3）对比SeataAT与TCC，给出两种场景分别推荐AT或TCC，并说明理由。（4分）答案与解析：（1）一阶段：业务SQL拦截，解析生成undolog，写入本地undo表，然后执行业务SQL并提交本地事务；二阶段：TC下发提交或回滚，若回滚则执行undolog。undolog存放在业务库内seata_undo_log表。（2）订单库宕机导致无法完成二阶段，TC会标记该分支为“需重试”，通过定时任务对未决事务不断重试，直至订单库恢复。（3）a.下单扣库存场景，业务SQL简单，推荐AT，零侵入；b.红包发放涉及复杂业务校验与冻结，推荐TCC，由业务自行实现Try/Confirm/Cancel，灵活控制资源。三、SQL优化与执行计划1.表orders(order_idPK,user_id,status,create_time,amount)数据量5亿行，status∈{1,2,3,4,5}，其中status=2占60%。查询：SELECTFROMordersWHEREuser_id=?ANDstatus=2ORDERBYcreate_timeDESCLIMIT10;（1）指出MySQL8.0默认情况下最可能使用的索引及原因。（3分）（2）若现有索引idx_user_status_time(user_id,status,create_time)，仍出现大量回表，给出两种覆盖索引方案并比较写入放大。（5分）（3）在PostgreSQL15中，利用BRIN索引加速该查询，写出建索引语句，并说明BRIN适用场景与不适用的场景。（4分）答案与解析：（1）优化器估算status=2过滤性差，可能选择全表或仅使用user_id部分，忽略status，导致filesort。（2）a.创建覆盖索引(user_id,status,create_timeDESC)INCLUDE(amount)，MySQL8.0不支持INCLUDE，可改为(user_id,status,create_timeDESC,amount)，写入放大：每行需维护amount；b.分区表+本地索引，按user_id哈希分区，再建(create_timeDESC)本地索引，回表仅在本分区，写入放大低，但跨分区查询退化。（3）CREATEINDEXidx_brinONordersUSINGBRIN(user_id,create_time);BRIN适合数据物理顺序与索引键强相关、超大表、低并发写入；不适合高并发随机更新、数据重排频繁场景。2.复杂SQL改写：原始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下的执行计划痛点。（3分）（2）将其改写成JOIN+GROUPBY+HAVING版本，并验证等价性。（5分）（3）在MySQL8.0中，利用ANTIJOINHASH提示，写出改写后的语句，并对比性能提升幅度（给出实验数据）。（4分）答案与解析：（1）5.7对NOTEXISTS采用嵌套循环，orders表走create_time范围索引仍需逐行回表，用户表大时性能差。（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);实验：500万用户、2亿订单，原始43s，改写后1.2s，提升97%。四、NoSQL与NewSQL实践1.某社交平台使用MongoDB6.0存储用户动态，文档结构：{_id:...,user_id:...,content:...,tags:[],like_count:...,create_time:...,comments:[{cid:...,uid:...,txt:...,time:...}]}（1）若comments数组可能无限增长，指出由此带来的两大技术风险。（3分）（2）给出一种基于“桶模式”的设计，将评论拆分到独立集合，并保证分页查询高效。（5分）（3）在分片集群中，以user_id为分片键，解释为何会出现“jumbochunk”，并给出自动分裂失败时的手动处理步骤。（4分）答案与解析：（1）文档超过16MB限制；数组更新导致整个文档重写，写放大。（2）创建comment_bucket集合，文档结构：{_id:...,user_id:...,post_id:...,bucket_seq:...,count:...,comments:[...]}每100条评论一个桶，桶内按time排序。查询第N页：skip(bucket_seq100)+limit，利用复合索引{post_id:1,bucket_seq:1}。（3）jumbochunk指大小超过最大块尺寸且无法分裂，通常因分片键单调或值相同。手动处理：1.关闭均衡器；2.使用splitAt找到可分裂点；3.若值均相同，则使用“强制唯一”方式添加后缀字段重新分片；4.打开均衡器并迁移。2.TiDB7.1场景：表follow(follower_id,followee_id,create_time)主键(follower_id,followee_id)，无二级索引。（1）写出查询“粉丝列表”的SQL，并评估执行代价。（3分）（2）在TiFlash副本开启后，给出将上述查询下推到TiFlash的Hint写法，并说明TiFlash与TiKV的consistency保证机制。（4分）（3）若follow表达到100亿行，需要按followee_id分区，给出一种既兼容MySQL语法又保证线性扩展的分区方案。（5分）答案与解析：（1）SELECTfollower_idFROMfollowWHEREfollowee_id=?ORDERBYcreate_timeDESC;需全表扫，代价O(n)。（2）SELECT/+READ_FROM_STORAGE(TIFLASH[follow])/follower_idFROMfollowWHEREfollowee_id=?;TiFlash采用RaftLearner，异步复制，提供SnapshotIsolation，读请求通过RaftIndex保证一致性，延迟秒级。（3）采用SHARD_ROW_ID_BITS+分区表语法：CREATETABLEfollow(follower_idBIGINT,followee_idBIGINT,create_timeTIMESTAMP,PRIMARYKEY(followee_id,follower_id))PARTITIONBYHASH(followee_id)PARTITIONS1024;TiDB底层按Region分裂，每个分区再按key范围分裂，实现线性扩展。五、数据仓库与实时计算1.某零售企业构建星型模型：事实表sale(dt,store_id,product_id,quantity,amt)分区字段dt；维度表product(product_id,category,brand)每日全量快照。（1）指出使用“每日全量快照”带来的存储浪费，并给出“拉链表”方案，写出核心字段与更新SQL。（6分）（2）在Hive3.1中，利用ORC+BloomFilter加速“品牌=某值”的查询，写出建表语句与加载数据命令。（4分）（3）在Flink1.17中，实现“实时累计销售额”版本化视图，要求ExactlyOnce，写出核心代码片段（Java/Scala均可），并说明checkpoint与Kafka事务如何协同。（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事务通过两阶段提交：Flinkcheckpointbarrier触发precommit，待所有算子ack后，JM通知Kafkacommit，实现端到端ExactlyOnce。2.ClickHouse23.3场景：表sale_ch(store_idUInt32,product_idUInt32,amtFloat64,dtDate)ENGINE=MergeTreeORDERBY(store_id,product_id,dt)。（1）指出该排序键对“按品类汇总”查询的不友好，并给出调整方案。（3分）（2）写出利用MaterializedView实时累加“日店铺”销售额的DDL，并说明刷新机制。（4分）（3）若数据倾斜导致某store_id分区过大，给出两种ClickHouse原生解决方案。（4分）答案与解析：（1）排序键前缀为store_id，按品类需扫描全表。调整为ORDERBY(category,product_id,dt)或增加跳数索引：ALTERTABLEsale_chADDINDEXidx_categorycategoryTYPEset(100)GRANULARITY3;（2）CREATEMATERIALIZEDVIEWmv_store_dailyENGINE=SummingMergeTree()ORDERBY(store_id,dt)ASSELECTstore_id,dt,sum(amt)ASamtFROMsale_chGROUPBYstore_id,dt;刷新机制：由MergeTree后台merge触发，查询时加FINAL修饰符保证一致性。（3）a.使用SAMPLEBYstore_id设置采样键，查询时SAMPLE0.1；b.将store_id哈希到两个分片，再建分布式表，利用sharding_key=store_id%2。六、数据安全与治理1.某三甲医院建设数据中台，需对患者姓名、身份证、手机号进行脱敏。（1）给出“可恢复加密”与“不可逆脱敏”两种场景示例，并指出密钥管理方案。（4分）（2）在MySQL8.0中，利用函数索引实现“身份证后四位查询”，写出完整步骤，并评估安全性。（4分）（3）若采用列级加密（AES256GCM），写出J