2026年软考《数据库系统工程师》案例分析真题

2026年软考《数据库系统工程师》案例分析真题试题一（20分）阅读下列说明，回答问题1至问题4，将解答填入答题纸的对应栏内。【说明】某大型物流公司为了提升物流效率和管理水平，决定开发一套“智慧物流配送管理系统”。该系统涵盖了订单管理、车辆调度、仓储管理、配送路径规划以及客户反馈等核心功能。系统后端采用关系型数据库，当前处于数据库设计阶段。根据需求分析，系统主要涉及以下实体：1.客户：属性包括客户ID、姓名、电话、地址、信用等级。2.仓库：属性包括仓库ID、名称、位置、容量、当前存储量。3.车辆：属性包括车牌号、类型、载重、当前状态（空闲/运输中/维修中）、所属车队ID。4.司机：属性包括工号、姓名、电话、驾驶证号、驾照类型。5.配送单：属性包括单号、客户ID、仓库ID、车牌号、司机工号、下单时间、预计送达时间、实际送达时间、配送状态、优先级。6.配送明细：每个配送单包含多个包裹，属性包括明细ID、配送单号、货物名称、重量、体积。系统有如下业务规则和约束：每个客户可以下多个配送单，每个配送单属于一个客户。每个配送单必须由一个车辆和一个司机负责，车辆和司机之间存在多对多关系（即同一车辆在不同时间可由不同司机驾驶，同一司机在不同时间可驾驶不同车辆），但在特定一次配送任务中，车辆和司机是绑定的。每个配送单从指定的仓库发出。配送单的优先级分为“普通”、“加急”、“特急”，系统需根据优先级进行排序处理。在初步设计的关系模式中，部分属性如下所示：配送单（单号，客户ID，仓库ID，车牌号，司机工号，下单时间，预计送达时间，实际送达时间，配送状态，优先级，客户姓名，客户地址，仓库位置，司机姓名）配送明细（明细ID，配送单号，货物名称，重量，体积）【问题1】（5分）根据上述说明，请补充“配送单”关系模式的主键和候选键（用下划线标出）。如果不存在候选键，请说明理由。【问题2】（6分）根据数据库规范化理论，判断“配送单”关系模式属于第几范式？请说明理由。并将其规范化为满足3NF的关系模式，请给出修改后的关系模式。【问题3】（4分）E-R图是数据库设计的重要工具。请根据上述说明，画出“配送单”、“车辆”、“司机”三个实体之间的联系类型，并说明联系的类型（1:1,1:N,M:N）。注意：需考虑“特定一次配送任务中”这一约束条件。【问题4】（5分）在系统运行过程中，经常需要查询“某辆车在某个月份的总行驶里程”以及“某个司机完成的加急配送单数量”。为了提高这些查询的性能，数据库管理员（DBA）考虑在这些字段上建立索引。（1）请说明在“配送单”表上，针对“车牌号”和“下单时间”建立联合索引（车牌号，下单时间）是否合理？请从索引选择性（Selectivity）和最左前缀原则的角度进行分析。（2）如果查询条件经常是`WHERE司机工号='xxx'AND优先级='加急'`，应如何建立索引？请写出索引定义语句（假设使用SQL标准语法）。试题二（25分）阅读下列说明，回答问题1至问题3，将解答填入答题纸的对应栏内。【说明】某电商平台的后台订单处理系统采用MySQL数据库，数据量日益庞大。系统包含一张核心的大表`order_records`，用于记录所有的交易流水。表结构如下：```sqlCREATETABLEorder_records(order_idBIGINTPRIMARYKEY,user_idBIGINTNOTNULL,product_idBIGINTNOTNULL,order_amountDECIMAL(10,2)NOTNULL,order_timeDATETIMENOTNULL,statusTINYINTNOTNULL,--0:未支付,1:已支付,2:发货,3:完成,4:退款region_idINTNOTNULL,--地区IDINDEXidx_user_time(user_id,order_time),INDEXidx_status(status))ENGINE=InnoDB;```随着业务发展，出现了以下性能瓶颈：1.表数据量超过5000万行，查询速度明显变慢。2.系统经常需要按照`order_time`（下单时间）进行范围查询，以生成月度报表，但该字段不是主键，也没有单独的索引。3.在高并发场景下，插入订单时出现偶尔的锁等待超时。DBA决定对数据库进行优化，主要措施包括：调整索引策略、实施表分区、以及优化事务处理逻辑。【问题1】（10分）为了解决报表查询慢的问题，DBA计划对`order_records`表进行范围分区（RangePartitioning）。（1）假设按照`order_time`字段进行年度分区，请写出创建分区表的SQL语句片段（只需写出PARTITIONBY及分区定义部分，假设数据从2022年开始）。（2）在进行范围查询时，如果查询条件为`WHEREorder_timeBETWEEN'2023-06-01'AND'2023-06-30'`，MySQL查询优化器如何利用分区机制（PartitionPruning，分区裁剪）来提升性能？请简要描述原理。（3）如果此时需要查询`WHEREuser_id=10001ANDorder_timeBETWEEN'2023-06-01'AND'2023-06-30'`，现有的分区策略配合索引`idx_user_time`是否高效？请说明原因，并给出改进建议。【问题2】（8分）在订单处理逻辑中，存在如下存储过程片段用于处理用户下单：```sqlSTARTTRANSACTION;1.检查库存SELECTstockFROMproductsWHEREproduct_id=p_pidFORUPDATE;2.扣减库存UPDATEproductsSETstock=stock-p_countWHEREproduct_id=p_pid;3.插入订单INSERTINTOorder_records(...)VALUES(...);4.更新用户积分UPDATEusersSETpoints=points+p_pointsWHEREuser_id=p_uid;COMMIT;```（1）上述事务中，步骤1使用了`FORUPDATE`，其作用是什么？如果去掉该子句，在并发环境下可能出现什么问题？（2）假设`products`表的主键是`product_id`，`users`表的主键是`user_id`。在InnoDB存储引擎中，这两个UPDATE语句产生的锁分别是什么类型的锁（记录锁RecordLock/间隙锁GapLock/临键锁Next-KeyLock）？（3）为了减少锁的持有时间，提高并发度，应如何优化上述事务逻辑？【问题3】（7分）系统引入了Redis作为缓存层，以减轻数据库压力。对于热点商品的数据（如价格、库存），采用了“旁路缓存策略”。（1）请简述标准的CacheAside（旁路缓存）模式的读操作和写操作流程。（2）在高并发写入场景下，采用CacheAside模式可能会遇到“缓存穿透”或“缓存击穿”问题。请解释“缓存击穿”的概念，并给出两种解决方案。（3）假设使用Redis集群存储缓存数据，为了防止数据倾斜，采用了一致性哈希算法。请画出一致性哈希环的示意图（文字描述节点和数据Key的映射关系），并说明当增加一个缓存节点时，数据迁移的范围是如何确定的。试题三（15分）阅读下列说明，回答问题1至问题3，将解答填入答题纸的对应栏内。【说明】某科研机构正在构建一个“全球气象数据分析平台”。该平台需要存储和处理来自全球各地气象站采集的海量传感器数据（温度、湿度、气压、风速等）。数据具有以下特征：1.写吞吐量极高：每秒写入数百万条记录。2.数据量大：累积数据达到PB级别。3.查询模式：主要查询是针对特定气象站、特定时间范围的历史数据检索，以及基于时间序列的聚合计算（如计算某地区过去24小时的平均温度）。4.数据结构：结构化程度高，但很少进行更新或删除操作。技术团队选用了基于LSM-Tree（Log-StructuredMergeTree）存储引擎的分布式数据库（如HBase或BigTable）作为核心存储方案。【问题1】（5分）LSM-Tree是很多NoSQL数据库（如HBase,RocksDB）的核心数据结构。请解释LSM-Tree将随机写转化为顺序写的基本原理。请结合“MemTable（内存表）”和“SSTable（排序字符串表）”进行描述。【问题2】（6分）在LSM-Tree架构中，读取操作可能比传统的B+树结构更慢，尤其是在数据未Compaction（合并/压缩）的情况下。（1）请分析导致读取操作变慢的原因。（2）为了优化读取性能并回收空间，LSM-Tree引入了Compaction机制。请简述Compaction的主要作用。（3）假设SSTable的大小配置如下：Level0:10MB,Level1:100MB,Level2:1GB。如果此时Level0有5个SSTable（共50MB），触发了Compaction，请计算Level1中参与合并的SSTable的总大小范围，以及合并后生成的SSTable将写入哪个Level？（请基于LeveledCompaction策略回答）。【问题3】（4分）针对该气象数据平台，团队决定对“气象站ID”和“采集时间”作为联合主键（RowKey）进行设计。RowKey设计格式为：`[气象站ID]_[反向时间戳]`。例如：Station_A_1625097600（时间戳为2021-06-30）。（1）请解释为什么要在时间戳前加上气象站ID？（2）请解释为什么要使用“反向时间戳”（例如：`Max_Timestamp-Current_Timestamp`）？参考答案及解析试题一【问题1】主键：单号候选键：无。理由：在“配送单”关系中，“单号”是唯一标识一个配送单的属性。虽然“客户ID”、“仓库ID”等可以组合起来唯一确定一个元组（假设一个客户在同一仓库同一时间只能有一个单子，但题目未明确此强约束），通常情况下，单号是系统生成的唯一标识符。题目中未给出其他能唯一标识元组且不包含多余属性的最小属性集，因此不存在其他候选键。【问题2】属于1NF（第一范式）。理由：虽然关系模式是原子的，满足1NF，但它存在非主属性对码的传递函数依赖。主键是“单号”。存在函数依赖：单号→客户ID，客户ID→客户姓名，客户ID→客户地址。因此，单号→客户姓名（传递），单号→客户地址（传递）。同理，单号→仓库ID→仓库位置（传递）；单号→司机工号→司机姓名（传递）。由于存在传递依赖，所以不满足3NF。由于所有非主属性都完全依赖于码（单号），所以满足2NF。综上，该模式满足2NF，但不满足3NF。（注：如果题目认为非原子属性或存在重复组，则连1NF都不满足，但根据描述通常指规范化程度未达3NF）。修改后的3NF关系模式如下：1.配送单（单号，客户ID，仓库ID，车牌号，司机工号，下单时间，预计送达时间，实际送达时间，配送状态，优先级）2.客户（客户ID，姓名，电话，地址，信用等级）3.仓库（仓库ID，名称，位置，容量，当前存储量）4.司机（工号，姓名，电话，驾驶证号，驾照类型）（注：配送明细已单独列出，此处不再重复）【问题3】在“特定一次配送任务中”，一辆车对应一个司机，一个司机对应一辆车。因此，配送单、车辆、司机三者之间的联系类型是1:1（一对一）。（注：虽然在全局范围内，车辆和司机是多对多，但在“配送单”这个实体的上下文中，它记录的是一次具体的任务绑定，此时该联系表现为1:1。或者可以描述为：配送单与车辆是N:1，配送单与司机是N:1，而配送单通过外键将两者关联，形成了一种三元绑定关系。若严格画E-R图，通常是“配送单”实体分别指向“车辆”和“司机”的N:1联系。如果题目问的是这三者构成的聚合关系，通常在配送单中体现。若必须回答三者间的联系，基于“配送单”作为纽带，在单次任务中是1:1绑定。）【问题4】（1）合理。分析：索引选择性：如果某辆车在一个月内的记录数相对于全表记录数较少，那么“车牌号”的选择性较高，能过滤掉大量数据。最左前缀原则：联合索引`(车牌号,下单时间)`遵循最左前缀原则。查询条件如果是`WHERE车牌号='A12345'AND下单时间BETWEEN'...'AND'...'`，索引可以完全生效，先通过车牌号定位到该车的数据索引段，再通过时间范围进行扫描。这比仅使用时间索引或全表扫描效率高得多。（2）索引定义语句：```sqlCREATEINDEXidx_driver_priorityON配送单(司机工号,优先级);```或者```sqlCREATEINDEXidx_driver_priorityON配送单(司机工号,优先级)INCLUDE(配送状态);--如果使用SQLServer等支持包含列的语法```标准SQL通用写法：```sqlCREATEINDEXidx_driver_priorityON配送单(司机工号,优先级);```试题二【问题1】（1）创建分区表SQL片段：```sqlPARTITIONBYRANGE(YEAR(order_time))(PARTITIONp2022VALUESLESSTHAN(2023),PARTITIONp2023VALUESLESSTHAN(2024),PARTITIONp2024VALUESLESSTHAN(2025),PARTITIONp2025VALUESLESSTHAN(2026),PARTITIONp2026VALUESLESSTHAN(2027),PARTITIONp_futureVALUESLESSTHANMAXVALUE);```（注：也可以直接使用TO_DAYS或者UNIX_TIMESTAMP，但YEAR是较常用的方式）（2）分区裁剪原理：MySQL查询优化器会解析查询条件中的`order_time`范围，并将其与分区的定义边界进行比较。由于查询限定在2023年6月，优化器能够识别出这些数据只存在于`p2023`分区中。因此，在执行查询时，MySQL只会扫描`p2023`分区的数据，而忽略其他所有分区（如p2022,p2024等）。这种“排除无关分区，只扫描相关分区”的技术就是分区裁剪，它极大地减少了I/O扫描的数据量，从而提升性能。（3）是否高效及改进建议：分析：现有的分区策略是按`order_time`范围分区，索引`idx_user_time`是`(user_id,order_time)`。当查询条件为`WHEREuser_id=10001ANDorder_timeBETWEEN'...'AND'...'`时：1.分区裁剪：首先通过`order_time`裁剪到`p2023`分区。2.B+树索引查找：在`p2023`分区内，使用`idx_user_time`索引。由于索引的最左前缀是`user_id`，查询条件正好提供了`user_id`，所以索引可以高效定位到`user_id=10001`的数据段，然后再根据时间范围过滤。结论：该策略是比较高效的。因为分区裁剪缩小了搜索范围，且索引的前导列匹配查询条件。改进建议：虽然当前策略尚可，但如果用户ID的分布极其不均匀（某些用户数据量极大），或者查询更多是单点查询而非范围查询，可以考虑使用Hash分区或Key分区，分区键选为`user_id`。这样可以将特定用户的数据分散到不同物理文件，减少单分区的索引树深度和锁竞争。但对于“月度报表”这类典型的按时间查询，Range分区是最优的。因此，若必须优化，可以考虑建立覆盖索引（CoveringIndex），将`status`或`region_id`也包含进索引，避免回表查询。【问题2】（1）`FORUPDATE`的作用是加行级排他锁（X锁）。如果去掉该子句：`SELECT`默认使用MVCC快照读，不会阻塞其他事务的修改。在并发环境下，假设两个事务同时读取到库存为10。事务A执行`UPDATE...SETstock=9`。事务B也执行`UPDATE...SETstock=9`（因为它读取到的也是10）。最终数据库库存为9，但实际卖出了2件，发生了丢失更新问题，导致超卖。（2）在InnoDB中，针对主键或唯一索引进行精确匹配的UPDATE：`UPDATEproductsSETstock=stock-p_countWHEREproduct_id=p_pid_pid;`锁类型：记录锁。因为`product_id`是主键，且是等值查询，直接锁定主键索引对应的记录。`UPDATEusersSETpoints=points+p_pointsWHEREuser_id=p_uid;`锁类型：记录锁。同理，`user_id`是主键，直接锁定对应记录。（注：如果`user_id`是二级索引，则会先锁住二级索引记录，再锁住聚簇索引记录。如果查询条件是范围查询，则可能退化为临键锁。）（3）优化建议：减少锁粒度/时间：将非核心操作移出事务。例如，“更新用户积分”可以异步处理（通过消息队列），或者放在订单插入之后、提交之前的一个独立小事务中（如果业务允许积分更新失败不影响订单）。优化代码逻辑：确保事务内的代码逻辑尽可能简洁，避免进行RPC调用等耗时操作。乐观锁替代：对于高并发热点商品，可以使用CAS（CompareAndSwap）机制，即`UPDATEproductsSETstock=stock-1WHEREproduct_id=123ANDstock>0`，根据受影响行数来判断是否成功，从而避免使用`SELECTFORUPDATE`带来的阻塞。【问题3】（1）CacheAside模式流程：读操作：1.先读缓存。2.如果缓存命中，直接返回数据。3.如果缓存未命中，从数据库读取数据。4.将读到的数据写入缓存。5.返回数据。写操作：1.先更新数据库。2.然后删除缓存（而不是更新缓存，防止并发写入导致脏数据）。（2）缓存击穿：概念：缓存击穿是指某个极度热点的Key（如秒杀商品），在缓存过期的瞬间，海量的并发请求同时击穿缓存（即缓存中没有，但数据库里有），直接请求到数据库，导致数据库负载瞬间飙升，甚至宕机。解决方案：1.互斥锁：当缓存未命中时，只允许一个线程去查数据库并回写缓存，其他线程等待并重试或休眠。2.热点数据永不过期：对于逻辑上可以接受延迟的热点数据，不设置TTL，或者在后台异步更新缓存，前端展示时允许一定的短时脏读。3.布隆过滤器：在缓存层前置布隆过滤器，判断Key是否存在（防止恶意攻击不存在的Key，但这更多是解决穿透，对于击穿主要是防止DB压力）。（3）一致性哈希与数据迁移：示意图描述：想象一个闭合的环（0到−1例如：环上有节点A、B、C。Key1落在A和B之间，则存储在B。Key2落在C和A之间，则存储在A。数据迁移范围：当增加一个新节点（如NodeD）插入到环上的NodeA和NodeB之间时：只有原本属于NodeB的那一部分数据（即Hash值落在A到D之间的数据）需要迁移到NodeD。其他节点（A,C）的数据不受影响。这保证了最小化的数据迁移量，只需迁移受影响区间内的数据。试题三【问题1】LSM-Tree通过将内存中的随机写操作转换为磁盘上的顺序写操作来优化写入性能。原理：1.写入MemTable：所有的写操作（插入、更新、删除）首先写入内存中的MemTable（通常是一个有序结构，如跳表SkipList）。这一步是内存操作，速度极快。2.顺序写入SSTable：当MemTable达到阈值大小后，它会变为不可变的ImmutableMemTable，然后后台线程将其刷新到磁盘，生成一个SSTable。因为MemTable本身是有序的，所以写入磁盘的SSTable也是有序的，这避免了磁盘随机寻址，实现了高效的顺序写。3.后台合并：多个小的SSTable会在后台逐渐合并成更大的SSTable，以优化读性能。【问题2】（1）读取变慢的原因：LSM-Tree的数据分散在不同的SSTable中（包括内存中的MemTable和磁盘上不同层级的SSTables）。读取一个Key时，需要先查MemTable，然后查ImmutableMemTable，最后按从新到旧的顺序查找磁盘上的SSTables（Level0,Level1...）。如果数据在较老的层级（LevelN）或者没有经过Compaction，可能需要遍历多个SSTable文件才能找到数据（或者确定数据不存在），这增加了I/O开销和CPU开销（布隆过滤器虽能减少无效读，但无法消除多文件查找的路径）。（2）Compaction的主要作用：1.合并重复/过期数据：将多个SSTable中相同Key的数据合并，保留最新的版本，丢弃旧版本或被标记删除的数据。2.回收存储空间：清理掉无效的旧数据，释放磁盘空间。3.优化读性能：减少SSTable文件的数量，将小文件合并成大文件，减少读取时需要查找的文件数量，提高查找效率。（3）计算：基于LeveledCompaction策略：Level0：文件之间可能有重叠Key，总大小通常受限于阈值（如40MB或50MB）。题目中Level0有50MB，触发了Compaction。Level1：在LeveledCompaction中，Level1及以上的各层级，所有SSTable的Key范围互不重叠。Level1的总大小通常固定为配置值（例如10*Level0的大小）。参与合并的数据：当Level0的文件被刷出或合并时，会选取Level1中所有与Level0文件Key范围重叠的文件进行合并。由于Level1的文件互不重叠，Level0的文件通常覆盖整个Key空间（或者范围较广），因此通常需要选取Level1中的所有文件参与合并。具体计算：如果Level1的总大小配置为100MB（根据题目描述Level1:100MB）。参与合并的SSTable总大小范围：Level1的总大小，即100MB（假设Level0的数据覆盖了Level1的范围）。合并后生成的SSTable将写入：Level2。解析：在LeveledCompaction中，Level0的数据会被合并到Level1。如果合并后的数据超过了Level1的容量，Level1的数据会被进一步挑选并推送到Level2。但针对题目中“Level0触发Compaction”这一步，主要是将Level0数据与Level1重叠部分合并，