2026年数据库系统工程师应用技术真题

2026年数据库系统工程师应用技术真题试题一：数据库设计与规范化【场景描述】某大型物流公司为了提升运营效率，决定开发一套“智慧物流综合管理系统”。该系统旨在管理货物、车辆、司机、仓库以及客户之间的复杂流转关系。系统分析阶段收集到以下需求：1.部门管理：公司设有多个部门，每个部门有唯一的部门编号（DeptID）、部门名称和办公电话。2.员工管理：部门下有多名员工，包括司机和仓库管理员。每位员工有唯一的员工编号（EmpID）、姓名、性别、入职日期和职位。一个员工只能属于一个部门。3.车辆管理：公司拥有多辆运输车辆，每辆车有唯一的车辆编号（VehicleID）、车牌号、品牌、载重（吨）和购买日期。车辆需要定期维护，维护记录包括维护日期、维护内容和费用。4.仓库管理：公司在不同地点设有仓库，每个仓库有唯一的仓库编号（WarehouseID）、名称、地址和最大容量。仓库由特定的仓库管理员负责管理。5.客户管理：系统维护客户信息，包括客户编号（CustID）、名称、类型（企业/个人）、联系电话和地址。6.物流订单：客户提交物流订单，订单包含订单编号（OrderID）、下单时间、发货地、目的地、货物重量、运费和状态（待处理、运输中、已签收）。一个订单只能关联一个客户。7.运输调度：订单生成后，需要调度车辆和司机进行运输。一次运输任务（任务ID：TaskID）关联一个订单、一辆车和一名司机。一个司机在同一时间只能执行一个运输任务，但一辆车在不同时间段可以执行多个任务。8.库存记录：货物在仓库中流转会产生库存记录，记录编号（RecordID）、入库/出库时间、数量变化及关联的订单。根据以上需求，逻辑结构设计阶段初步设计了如下关系模式（主键加粗，外键未标注）：部门(部门编号,部门名称,办公电话)员工(员工编号,姓名,性别,入职日期,职位,部门编号)车辆(车辆编号,车牌号,品牌,载重,购买日期)维护记录(记录ID,车辆编号,维护日期,维护内容,费用)仓库(仓库编号,名称,地址,最大容量,管理员员工编号)客户(客户编号,名称,类型,联系电话,地址)订单(订单编号,下单时间,发货地,目的地,货物重量,运费,状态,客户编号)运输任务(任务ID,订单编号,车辆编号,司机员工编号,开始时间,结束时间)库存记录(记录ID,仓库编号,订单编号,时间,数量,类型)【问题1】（4分）根据实体-联系图（E-R图）的设计原则，请补充“运输任务”实体与“车辆”、“员工”、“订单”实体之间的联系类型。即补充以下联系：(1)“运输任务”与“车辆”之间的联系类型是\_\_\_\_\_\_(1:1,1:N或M:N)。(2)“运输任务”与“员工（司机）”之间的联系类型是\_\_\_\_\_\_(1:1,1:N或M:N)。(3)“运输任务”与“订单”之间的联系类型是\_\_\_\_\_\_(1:1,1:N或M:N)。(4)“车辆”与“维护记录”之间的联系类型是\_\_\_\_\_\_(1:1,1:N或M:N)。【问题2】（6分）在关系模式设计中，外键是保证数据参照完整性的重要约束。请指出上述关系模式中包含的外键属性（请指出属性所属的关系模式及属性名）。例如：员工.部门编号。【问题3】（5分）假设“订单”关系中经常需要查询“客户名称”和“客户类型”，但目前的模式需要通过连接操作才能获得。为了提高查询效率，设计人员考虑将“客户名称”和“客户类型”冗余存储到“订单”关系中。(1)这种修改可能会导致什么数据异常问题？(2)若既要提高查询效率又要尽可能减少数据冗余，除了上述冗余方法外，通常还可以采用什么数据库技术手段？【问题4】（5分）给定“库存记录”关系模式：库存记录(记录ID,仓库编号,订单编号,时间,数量,类型)。假设一个订单可以在多个仓库进行出入库操作（例如分批入库），一个仓库可以处理多个订单。(1)请说明该关系模式的主键是什么？(2)判断该关系模式是否达到2NF？请简要说明理由。【答案与解析】【问题1】(1)1:N(2)1:N(3)1:1(4)1:N解析：(1)一辆车在不同时间段可以执行多个任务，一个任务在同一时间只对应一辆车。在实体联系中，通常如果不考虑时间维度的复杂约束，从“车辆”角度看是1对多，即1:N。如果考虑特定时间点的状态，可能更复杂，但一般E-R设计中，车辆对任务是1:N。(2)一名司机在同一时间只能执行一个任务，但一名司机在不同时间段可以执行多个任务。因此，司机对任务是1:N。(3)一个订单对应一次运输任务（假设订单不被拆分运输），一次运输任务只服务于一个订单。因此是1:1联系。(4)一辆车可以有多次维护记录，一条维护记录只属于一辆车。因此是1:N。【问题2】员工.部门编号维护记录.车辆编号仓库.管理员员工编号订单.客户编号运输任务.订单编号运输任务.车辆编号运输任务.司机员工编号库存记录.仓库编号库存记录.订单编号解析：外键是指向其他关系模式主键的属性。员工属于部门，故员工.部门编号外键指向部门.部门编号。维护记录属于车辆，故维护记录.车辆编号外键指向车辆.车辆编号。仓库由管理员负责，故仓库.管理员员工编号外键指向员工.员工编号。订单属于客户，故订单.客户编号外键指向客户.客户编号。运输任务关联订单、车辆、司机，故运输任务.订单编号、运输任务.车辆编号、运输任务.司机员工编号均为外键。库存记录关联仓库和订单，故库存记录.仓库编号、库存记录.订单编号均为外键。【问题3】(1)更新异常（或修改异常）。当客户信息（如名称或类型）发生变化时，如果该客户有多个订单，则需要修改所有关联订单中的冗余数据，如果遗漏会导致数据不一致。(2)创建视图或创建物化视图。解析：(1)数据冗余直接导致的问题是更新异常。因为客户名称存储在客户表和订单表两处，修改时必须同步。(2)为了平衡查询效率和数据规范性，数据库中常用的技术是建立视图。视图是虚表，可以将连接查询封装起来，用户查询时像查单表一样，但物理上数据不冗余。对于频繁查询且数据更新不频繁的场景，可以使用物化视图，它物理存储查询结果，效率更高，但需要刷新策略。【问题4】(1)主键是：记录ID(2)是2NF。解析：(1)题目中明确给出了“记录ID”作为记录的唯一标识，因此主键是记录ID。虽然(仓库编号,订单编号,时间)可能也能唯一确定一次操作，但既然题目设计了记录ID，通常作为代理主键使用。(2)2NF（第二范式）要求关系模式是1NF，且非主属性完全函数依赖于主键。在这个关系中，主键是记录ID（单属性）。对于单属性主键，不存在部分依赖的问题（部分依赖仅存在于复合主键的情况）。因此，该模式自然满足2NF。(注：如果主键被定义为(仓库编号,订单编号,时间)，那么需要检查非主属性如“数量”是否依赖于这三个属性的整体。通常“数量”依赖于具体的某次操作，即依赖于整个主键，因此也是2NF。但鉴于题目给出了记录ID，基于单属性主键判断更为直接。)试题二：SQL应用与查询优化【场景描述】某电商公司使用MySQL数据库管理其交易数据。数据库中包含三个核心表：1.商品表：存储商品基本信息。`ProductID`(INT,PK):商品ID`ProductName`(VARCHAR):商品名称`CategoryID`(INT):类别ID`Price`(DECIMAL):单价`Stock`(INT):库存量2.用户表：存储注册用户信息。`UserID`(INT,PK):用户ID`UserName`(VARCHAR):用户名`Level`(VARCHAR):用户等级（普通,VIP,SVIP）`RegisterDate`(DATE):注册日期3.订单表：存储订单记录。`OrderID`(INT,PK):订单ID`UserID`(INT):下单用户ID`ProductID`(INT):购买的商品ID`Quantity`(INT):购买数量`OrderDate`(DATETIME):下单时间`Amount`(DECIMAL):订单总金额【问题1】（10分）请用SQL语句完成以下查询需求：(1)查询2025年11月11日当天，每个用户的订单总金额，并按总金额降序排列，只显示总金额超过10000元的用户ID和总金额。(2)查询购买了“电子产品”类别下所有商品的用户名称。（假设CategoryID=5代表电子产品）。(3)统计每个类别的商品销售总量（即销量），如果某类别没有销售记录，则显示为0。【问题2】（6分）系统运行中发现以下SQL语句执行非常缓慢：```sqlSELECT*FROMOrdersWHEREUserID=1005ANDOrderDate>'2025-01-01';```经分析，`Orders`表的数据量已达到5000万行，目前仅在`UserID`上建立了索引。(1)请解释为什么该查询在现有索引下效率依然不高？(2)为了优化该查询，应该如何建立索引？请写出建索引的SQL语句。【问题3】（4分）在统计销售报表时，经常需要计算“客单价”（平均每个订单的金额）。现有的SQL如下：```sqlSELECTAVG(Amount)FROMOrders;```如果订单表数据量极大，且`Amount`字段存在索引，请简述数据库引擎（如InnoDB）如何利用索引来优化该AVG查询？如果`Amount`字段没有索引，查询过程会有什么不同？【问题4】（5分）数据库管理员注意到系统中存在大量的“死锁”现象，尤其是在高并发下单和库存扣减的场景。请给出两种可以有效减少死锁发生的策略或操作建议。【答案与解析】【问题1】(1)```sqlSELECTUserID,SUM(Amount)ASTotalAmountFROMOrdersWHEREOrderDateBETWEEN'2025-11-1100:00:00'AND'2025-11-1123:59:59'GROUPBYUserIDHAVINGSUM(Amount)>10000ORDERBYTotalAmountDESC;```(2)```sqlSELECTUserNameFROMUsersWHERENOTEXISTS(SELECT*FROMProductsWHERECategoryID=5ANDNOTEXISTS(SELECT*FROMOrdersWHEREOrders.ProductID=Products.ProductIDANDOrders.UserID=Users.UserID));```或者使用关系除法的另一种写法（双重NOTEXISTS）：```sqlSELECTUserNameFROMUsersuWHERENOTEXISTS(SELECT*FROMProductspWHEREp.CategoryID=5ANDNOTEXISTS(SELECT*FROMOrdersoWHEREo.UserID=u.UserIDANDo.ProductID=p.ProductID));```(3)```sqlSELECTp.CategoryID,COALESCE(SUM(o.Quantity),0)ASTotalSalesFROMProductspLEFTJOINOrdersoONp.ProductID=o.ProductIDGROUPBYp.CategoryID;```解析：(1)考察聚合函数、GROUPBY、HAVING子句以及日期范围过滤。HAVING用于在分组后过滤聚合结果。(2)考察关系除法。查询“购买了某集合中所有”元素的典型SQL写法是双重NOTEXISTS。逻辑是：不存在这样的商品（它是电子产品且该用户没买过）。(3)考察LEFTJOIN处理无匹配的情况。要显示销售量为0的类别，必须以商品表为驱动表进行左连接，并使用COALESCE或IFNULL将NULL转换为0。【问题2】(1)虽然在`UserID`上建立了索引，但查询条件是`UserID=1005ANDOrderDate>'2025-01-01'`。在MySQL的InnoDB引擎中，辅助索引是B+树结构，存储的是索引列的值和主键。如果只建立了`UserID`索引，数据库通过索引找到UserID=1005的所有记录的主键ID，然后需要回表去聚簇索引中查询完整的行数据来判断`OrderDate`条件。如果UserID=1005的记录非常多（例如该用户是老客户，有数万条订单），则需要回表数万次，产生大量的随机I/O，效率极低。(2)应该建立联合索引（复合索引），将`OrderDate`也包含在内。因为查询是等值查询后跟范围查询，将`OrderDate`放在`UserID`之后可以利用索引的最左前缀原则。SQL语句：```sqlCREATEINDEXidx_user_dateONOrders(UserID,OrderDate);```解析：联合索引`(UserID,OrderDate)`的B+树节点是先按UserID排序，UserID相同时按OrderDate排序。查询时可以直接定位到UserID=1005的起始位置，然后顺序向后扫描，直到OrderDate不满足条件为止。这利用了索引的有序性，避免了大量回表（如果是覆盖索引优化，甚至不需要回表），极大提升了效率。【问题3】有索引时：如果`Amount`字段存在索引，数据库引擎可以直接扫描`Amount`索引的B+树叶子节点。因为索引数据通常比完整的数据行小得多，且在叶子节点上是顺序存储的。引擎可以顺序读取索引页，快速累加总和并统计行数，从而计算出平均值。这属于“索引扫描”，避免了访问数据表（聚簇索引）的开销，I/O成本显著降低。无索引时：如果`Amount`字段没有索引，数据库引擎必须执行“全表扫描”。它需要读取表（聚簇索引）的所有数据页，将每一行数据加载到内存中，提取`Amount`值进行累加。这涉及大量的磁盘I/O，尤其是表很大时，速度会慢很多。【问题4】1.保持事务简短：尽量减少事务中SQL语句的数量和执行时间，特别是避免在事务中进行长耗时的网络交互或用户等待操作，尽快提交事务以释放锁。2.按一致的顺序访问资源：在多个事务中，如果涉及对多个表（或多个行）的加锁，应确保它们以相同的顺序（例如按表名或主键ID升序）申请锁。例如，事务A先锁订单表再锁库存表，事务B也应先锁订单表再锁库存表，这样可以避免循环等待条件。3.提高隔离级别或使用乐观锁：在某些场景下，可以使用乐观锁（如版本号机制）代替悲观锁，或者在应用层先查询再判断，减少数据库锁的持有时间。（注：前两点是最核心的策略）。试题三：事务处理与并发控制【场景描述】某银行核心数据库系统采用两阶段锁（2PL）协议来实现并发控制。现有两个事务和，它们对账户A和账户B（初始余额均为1000元）进行操作。操作序列如下：时间$T_1$$T_2$$t_1$Lock-X(A)$t_2$Read(A)$t_3$A:=A-100$t_4$Write(A)$t_5$Lock-X(B)$t_6$Read(B)$t_7$B:=B-200$t_8$Write(B)$t_9$Lock-X(B)$t_{10}$Unlock(A)$t_{11}$Lock-X(A)$t_{12}$Read(A)$t_{13}$A:=A+200$t_{14}$Write(A)$t_{15}$Unlock(B)$t_{16}$Unlock(A)$t_{17}$Read(B)$t_{18}$B:=B+100$t_{19}$Write(B)$t_{20}$Unlock(B)【问题1】（4分）上述调度中，和是否发生了死锁？如果是，请指出构成等待环路的节点（事务）。【问题2】（5分）假设系统采用“等待-图”来进行死锁检测。请画出在时刻执行时的等待图（Wait-forGraph），并判断此时系统是否需要采取措施。【问题3】（6分）若系统采用“严格两阶段锁协议”，请指出上述调度中哪些Unlock操作是不符合严格2PL要求的？（只需写出时间点，如）。并说明严格2PL协议的主要优点是什么。【问题4】（5分）假设事务的隔离级别设置为“ReadCommitted”（读已提交）。在此隔离级别下，能否解决“不可重复读”和“幻读”问题？请简述理由。【答案与解析】【问题1】是，发生了死锁。等待环路：→→解析：在时刻，申请Lock-X(B)，但持有B的锁（在获得），所以等待。在时刻，申请Lock-X(A)，但持有A的锁（在获得，且在只释放了A？不，注意看序列，是Unlock(A)。如果执行了，那么在应该能拿到锁。这里需要仔细看题目给出的序列）。修正分析：仔细看题目给出的操作序列：:Unlock(A)。:Lock-X(A)。如果按照这个时间顺序，在释放了A的锁，那么在申请A锁时应该能成功，不会发生死锁。但是，通常死锁题目中，序列设计会导致死锁。让我们重新审视的逻辑。在申请B锁。此时持有B锁（）。所以阻塞。继续执行，在申请A锁。此时还持有A锁吗？Lock-X(A)...Unlock(A)。如果在阻塞了，它还能执行到吗？在并发执行模型中，一旦在阻塞，它的后续操作（等）通常无法推进（除非调度器允许，但通常阻塞即挂起）。如果在阻塞，那么它依然持有A锁（因为它还没执行到Unlock(A)）。因此，在，申请A锁，而持有A锁。于是等待。此时：等待（释放B），等待（释放A）。结论：发生了死锁。环路是→→（注：题目表格中的列在下，但在被阻塞的情况下，实际执行顺序中发生在之后，或者根本没发生。如果严格按照表格时间线解释，在释放了A，那么就不会死锁。但这类题目通常考察阻塞状态。这里假设题目意图是在阻塞，导致无法执行，从而形成死锁。这是标准的死锁考察模式。）【问题2】在时刻执行时：等待图节点：,。边：→（因为在等待释放B）边：→（因为在等待释放A）图中存在环路→→系统需要采取措施（如选择牺牲一个事务进行回滚）来解除死锁。【问题3】不符合严格2PL要求的Unlock操作有：。解析：严格两阶段锁协议要求：事务持有的所有锁必须直到事务结束（提交或中止）时才释放。在上述调度中，在释放了A锁，但此时还没有结束（它还在申请B锁且未提交）。这违反了严格2PL。严格2PL的主要优点是：它产生的调度都是可串行化的，并且能够保证级联回滚不会发生（即避免了脏读带来的级联中止），因为其他事务只能读取已提交的数据。【问题4】在“ReadCommitted”隔离级别下：1.不能解决“不可重复读”。理由：ReadCommitted只允许读取已提交的数据。如果一个事务在两次读取同一数据之间，另一个事务修改并提交了该数据，当前事务会读取到新的值，导致同一事务内两次读取结果不同。2.不能解决“幻读”。理由：幻读是指基于范围查询时，其他事务插入了新行导致再次查询结果集变化。ReadCommitted允许读取其他事务已提交的新插入行，因此无法防止幻读。试题四：数据库备份与恢复【场景描述】某关键业务数据库采用预写日志（WAL）机制进行恢复管理。系统运行过程中发生了系统崩溃，重启时需要进行恢复。数据库管理系统采用检查点技术来加速恢复过程。假设系统日志记录如下（LSN为日志序列号，PrevLSN为同一事务上一条日志的LSN，PageID为数据页ID）：LSNPrevLSNTransIDTypePageIDDataL1NULLT1UpdateP1A=50L2NULLT2UpdateP2B=200L3L1T1UpdateP3C=30L4L2T2CommitNULLNULLL5NULLT3UpdateP1A=80L6L3T1AbortNULLNULL最近一次检查点记录包含以下信息：CheckpointLSN=L2事务表：T1(LastLSN=L1),T2(LastLSN=L2),T3(未开始)崩溃发生时，磁盘上的数据页状态可能未完全同步。系统采用ARIES风格的恢复算法。【问题1】（6分）在分析阶段，系统需要确定哪些事务需要Undo（撤销），哪些需要Redo（重做）。请根据上述日志和检查点信息，列出需要Undo的事务列表和需要Redo的事务列表。【问题2】（6分）在Redo阶段，系统按照LSN顺序扫描日志。请写出Redo阶段实际需要执行重做操作的日志记录LSN序列，并说明理由。（假设：数据页P1的RecoveryLSN为L1，P2的RecoveryLSN为L2，P3的RecoveryLSN为L0）。【问题3】（3分）在Undo阶段，系统需要按照事务回滚的逆序处理日志。请写出Undo阶段处理事务T1时，依次需要处理的日志记录LSN序列。【问题4】（5分）为了确保介质故障（如磁盘损坏）后的数据恢复，数据库管理员需要制定备份策略。假设数据库大小为1TB，每天数据变化量约为10GB，业务允许丢失的数据量为15分钟。(1)应采用什么类型的备份策略（如完全备份、差异备份、增量备份）？(2)结合日志转储，请简述一种可行的备份与恢复方案。【答案与解析】【问题1】需要Undo的事务：T1,T3需要Redo的事务：T1,T2,T3解析：1.确定Undo列表：从Checkpoint开始，活跃事务有T1,T2。扫描Checkpoint之后的日志：L3(T1):T1已在列表。L4(T2):T2Commit，从Undo列表移除。L5(T3):T3加入Undo列表。L6(T1):T1Abort，但Abort记录本身意味着需要清理，T1仍在Undo列表直到处理完Abort。最终，没有Commit的事务是T1和T3，需要撤销。2.确定Redo列表：在ARIES中，Redo列表包含所有具有Commit记录的事务，以及在Checkpoint时活跃且未Abort的事务（通常为了简化，ARIESRedo所有从CheckpointLSN开始到日志末尾的所有更新记录，除了某些优化）。更通用的教材算法：从Checkpoint开始，T1(Active),T2(Active)。扫描日志：L3(T1),L4(T2-Commit),L5(T3),L6(T1-Abort)。T2已提交，需Redo。T1和T3未提交，但在ARIES中，对于从Checkpoint开始的日志，通常会对所有更新操作进行Redo（除非通过PageLSN优化判断不需要）。因为物理上可能未刷盘。严格来说，需要Redo的事务是：T2(已提交)，T1(活跃但中止，需Redo为了后续Undo的正确性或物理一致性)，T3(活跃)。简单回答：T1,T2,T3。【问题2】需要Redo的日志序列：L3,L5解析：Redo阶段从LSN=L2+1(即L3)开始扫描到日志末尾。对于每条日志，判断是否需要Redo：如果`LSN>=Page.RecoveryLSN`且`Page.LSN!=LSN`（即页面上没有该更新），则执行Redo。L3(T1,UpdateP3):L3(L3)>=P3.RecoveryLSN(L0)。需要Redo。L4(T2,Commit):非更新操作，跳过。L5(T3,UpdateP1):L5(L5)>=P1.RecoveryLSN(L1)。需要Redo。L6(T1,Abort):非更新操作，跳过。所以实际执行Redo的是L3和L5。【问题3】UndoT1的LSN序列：L3,L1解析：Undo阶段从事务T1的LastLSN开始（L6是Abort，LastLSN通常指最后一个更新记录L3）。1.定位到L3。执行L3的逆操作（Undo）。L3的PrevLSN是L1。2.定位到L1。执行L1的逆操作（Undo）。L1的PrevLSN是NULL。3.结束T1的Undo。序列顺序：L3->L1。【问题4】(1)应采用完全备份+增量备份（或事务日志备份）相结合的策略。(2)方案：完全备份：每周（或每两周）进行一次数据库完全备份。日志备份：由于允许业务丢失15分钟的数据，因此每隔15分钟进行一次事务日志备份。恢复流程：1.还原最近的完全数据库备份。2.依次还原完全备份之后产生的所有日志备份（按时间顺序）。3.如果有活动的日志，还需还原未备份的活动日志部分，直到故障点。这样可以将数据丢失控制在15分钟以内，且恢复时间相对较短。试题五：NoSQL与分布式数据库【场景描述】某短视频平台开发了“用户动态与评论系统”，该系统需要处理海量的并发读写请求。主要需求如下：1.用户信息：存储用户ID、昵称、头像等，读多写少。2.动态内容：存储用户发布的动态，包括文本、图片URL、视频URL、发布时间戳、点赞数、评论数等。写入量极大，且主要是按时间线追加写入。3.评论数据：存储对动态的评论，包括评论内容、评论者、时间。查询场景主要是查询某条动态下的所有评论。【问题1】（4分）针对上述需求，若采用关系型数据库（RDBMS），在“动态内容”存储上可能会遇到什么瓶颈？请列举两点。【问题2】（6分）为了解决性能瓶颈，系统决定引入Redis和MongoDB。(1)Redis：适合存储上述哪类数据？请说明理由。(2)MongoDB：适合存储上述哪类数据？请说明其文档模型设计的优势。【问题3】（5分）在设计分布式数据库架构时，需要考虑数据的一致性级别。根据CAP理论，该系统在“动态内容”写入和“点赞数”更新时，更倾向于选择CP（一致性+分区容错性）还是AP（可用性+分区容错性）？请说明理由。【问题4】（5分）MongoDB采用分片技术来实现水平扩展。假设选择`UserID`作为分片键。(1)请简述以`UserID`作为分片键对“查询某用户的所有动态”这一操作的好处。(2)如果查询需求变为“查询全站最新的动态流”，以`UserID`作为分片键会有什么问题？应如何优化？【问题5】（5分）在分布式事务管理中，为了解决跨服务操作的一致性问题，常采用Saga模式。请简述Saga模式的基本原理，并指出它与两阶段提交（2PC）的主要区别。【答案与解析】【问题1】1.写入性能瓶颈：关系型数据库采用B+树结构，高并发插入时会产生频繁的页分裂和锁竞争，无法满足海量动态的实时写入需求。2.表结构变更困难：动态内容可能随业务发展增加新字段（如“话题标签”、