2025年MySQL数据库设计试题及答案

2025年MySQL数据库设计试题及答案一、单项选择题（每题3分，共30分，每题只有1个正确答案）以下关于MySQL8.4LTS版本默认特性的描述，错误的是（）A.默认认证插件为caching_sha2_password，支持更高的密码安全性B.透明数据加密（TDE）默认开启，无需额外配置即可实现数据文件加密C.InnoDB缓冲池自动调整粒度优化，支持运行时动态调整缓冲池大小无需重启D.新增不可见列特性，可在不影响业务SQL的前提下新增冗余字段用于审计答案：B。解析：MySQL8.4LTS将TDE纳入社区版核心功能，但默认处于关闭状态，需在f中配置early-plugin-load=keyring_file.so等参数后开启，仅商业版此前默认支持TDE，8.4版本优化了TDE的加密性能，相比8.0版本加密overhead降低15%左右，但仍未默认开启。

2.某订单表存在联合索引idx_uid_ctime(uid,create_time)，以下SQL无法触发覆盖索引的是（）A.SELECTuidFROMorder_infoWHEREuid=123ORDERBYcreate_timeDESCLIMIT10;B.SELECTcreate_timeFROMorder_infoWHEREuid=123;C.SELECTorder_sn,uidFROMorder_infoWHEREuid=123ANDcreate_time>‘2025-01-01’;

D.SELECTCOUNT(*)FROMorder_infoWHEREuid=123ANDcreate_timeBETWEEN‘2025-01-01’AND‘2025-06-01’;

答案：C。解析：覆盖索引要求查询所需的所有字段均存在于索引树中，无需回表查询聚簇索引。InnoDB二级索引的叶子节点默认存储对应主键值，因此题干中联合索引idx_uid_ctime包含的字段为uid、create_time、主键id，选项C的order_sn字段未在索引中，需回表获取完整行数据，因此无法触发覆盖索引。以下关于MySQLInnoDB事务隔离级别的描述，正确的是（）A.读未提交级别会出现不可重复读、幻读，但不会出现脏读B.可重复读（RR）级别通过MVCC完全解决了幻读问题C.读提交（RC）级别下，每次查询都会生成新的ReadViewD.串行化级别会禁用MVCC，所有查询均加共享锁，不会出现任何并发问题但性能最低答案：C。解析：A选项读未提交会读取其他事务未提交的修改，存在脏读、不可重复读、幻读三类并发问题；B选项RR级别仅通过MVCC解决快照读的幻读问题，当前读（SELECT…FORUPDATE/INSHAREMODE、UPDATE、DELETE）场景需结合next-keylock才能避免幻读；D选项串行化级别下普通查询会被隐式转换为SELECT…FORSHARE，加共享读锁，高并发下会出现大量锁等待甚至死锁，仅解决了数据一致性问题，并非完全无并发问题。某用户表扩展字段为JSON类型，存储用户的偏好配置数组，需统计所有用户中偏好品类包含“3C数码”的用户数量，以下SQL性能最优的是（）A.SELECTCOUNT()FROMuser_infoWHEREJSON_CONTAINS(preference->‘$.favorite_cate','"3C数码"');B.SELECTCOUNT(*)FROMuser_infoWHEREpreference->>'$.favorite_cate’LIKE‘%3C数码%’;

C.为preference->‘.favoritecate′创答案：C。解析：MySQL8.0+支持JSON多值索引，专为JSON数组类型的等值、包含查询优化，性能比无索引的JSON函数查询高10~100倍；选项A无索引时需全表扫描；选项B的LIKE模糊查询无法利用任何索引；选项D的全文索引仅适用于非结构化文本匹配，对JSON结构化查询适配性差，易出现匹配错误。某业务执行UPDATEorder_infoSETstatus=2WHEREuid=123ANDcreate_time>’2025-06-01’时出现锁等待，以下排查思路错误的是（）

A.检查是否存在未提交的长事务，已占用对应uid的行锁B.确认uid字段是否存在索引，无索引时会触发全表扫描加表级锁C.查看innodb_lock_wait_timeout参数设置，确认是否阈值设置过小D.执行SHOWENGINEINNODBSTATUS查看死锁日志，确认是否存在循环锁依赖答案：B。解析：InnoDB无索引时会对聚簇索引的所有记录加行锁，同时加间隙锁，最终等效于表级锁，但本质仍是行锁的集合，并非直接加表锁，MyISAM存储引擎才会对写入操作直接加表级锁。以下关于MySQL分区表的描述，正确的是（）A.分区表对业务层透明，无需修改SQL即可提升查询性能B.HASH分区适合按时间范围查询的场景，比如订单表按创建时间分区C.RANGE分区支持动态增删分区，适合冷热数据分离场景D.分区表可以避免锁等待，提升并发写入性能答案：C。解析：A选项分区表需要查询条件包含分区键才能提升性能，若查询不带分区键会扫描所有分区，性能反而低于普通表；B选项RANGE分区适合时间范围查询场景，HASH分区适合散列查询场景；D选项分区表仅将数据拆分到不同分区，锁机制和普通表一致，无法避免锁等待。MySQL8.0+支持动态权限，以下权限中不属于动态权限的是（）A.BACKUP_ADMIN（备份权限）B.ROLE_ADMIN（角色管理权限）C.SELECT（查询权限）D.AUDIT_ADMIN（审计管理权限）答案：C。解析：动态权限是MySQL8.0新增的可灵活配置的细粒度权限，SELECT、INSERT、UPDATE等属于静态全局权限，不属于动态权限范畴。以下关于大字段存储设计的描述，不符合2025年MySQL设计规范的是（）A.文本类型字段长度超过2000时，建议存储为TEXT类型而非VARCHARB.商品详情、合同文本等大文本字段建议拆分到独立扩展表，避免影响主表查询性能C.图片、视频等二进制资源建议存储在MySQL的BLOB字段，方便统一管理D.大字段查询时避免使用SELECT*，需明确指定查询字段减少IO开销答案：C。解析：二进制资源存储在BLOB字段会导致数据文件急剧膨胀，备份、恢复速度变慢，2025年企业级场景下均将二进制资源存储在对象存储（OSS、S3），MySQL仅存储资源的URL地址。某业务要求用户敏感信息查询时自动脱敏，无需修改业务代码，以下方案最优的是（）A.业务代码中对敏感字段做脱敏处理B.创建视图，对敏感字段套用内置mask_inner()脱敏函数，给业务侧开放视图查询权限C.数据库中存储脱敏后的数据，单独存储明文数据到加密表D.中间件层拦截SQL，对返回结果做脱敏处理答案：B。解析：MySQL8.0.19+内置mask_inner、mask_outer等脱敏函数，通过视图实现脱敏无需修改业务代码，权限控制粒度更细，不同角色可配置不同的脱敏规则，相比其他方案开发、维护成本最低。MySQL8.4半同步复制默认开启rpl_semi_sync_master_wait_point=AFTER_SYNC，该配置的作用是（）A.主库写入binlog后就返回客户端成功，无需等待从库应答B.主库提交事务到存储引擎后等待从库应答，再返回客户端成功C.主库将binlog同步到从库relaylog后，再提交本地事务返回客户端成功D.主库等待所有从库写入binlog后再返回客户端成功，保证数据零丢失答案：C。解析：AFTER_SYNC是MySQL8.0+默认的半同步等待点，主库将binlog同步到至少一个从库的relaylog并收到应答后，才提交本地事务返回客户端成功，既保证了数据不丢失，又平衡了性能，AFTER_COMMIT模式会出现主库提交后崩溃、从库未收到数据导致的数据不一致问题。二、填空题（每题4分，共20分）InnoDB聚簇索引的叶子节点存储_，二级索引的叶子节点存储_。答案：完整的行数据、主键值。解析：聚簇索引按主键顺序组织数据存储，因此叶子节点直接存储整行数据；二级索引仅存储索引字段值和对应的主键值，查询时需通过主键回表获取完整行数据（覆盖索引场景除外），因此主键长度越小，二级索引的存储空间越小。MySQL慢查询日志默认的阈值参数long_query_time的默认值为_秒，需记录未使用索引的查询时需开启_参数。答案：10、log_queries_not_using_indexes。解析：生产环境通常建议将long_query_time调整为1秒以下，高并发场景可调整为0.5秒，同时开启log_queries_not_using_indexes可捕获全表扫描的慢SQL，但需注意过滤频繁执行的小表全表扫描，避免慢查询日志过大。MySQL8.0+首次支持____特性，执行DROPTABLE、ALTERTABLE等操作时若出现崩溃不会产生中间数据，保证DDL操作的原子性。答案：原子DDL。解析：此前版本的DDL操作是非原子的，崩溃后可能残留临时文件或数据字典不一致，8.0+的原子DDL通过事务性数据字典实现了DDL的原子性、一致性，删除表时会同步删除所有关联的文件、索引，无残留。MySQL8.0+并行复制默认基于____模式，可大幅提升主从同步的吞吐量，减少主从延迟。答案：LOGICAL_CLOCK。解析：此前版本的并行复制仅支持基于库级别的并行，LOGICAL_CLOCK模式基于事务提交的逻辑时间戳实现同组无冲突事务的并行回放，主从延迟可降低90%以上，适合高并发写入场景。

5.MySQL8.4新增____参数，可限制单个查询的最大执行时间，超过阈值自动终止查询，避免慢SQL占用资源。答案：max_execution_time。解析：该参数可全局配置也可针对会话配置，生产环境建议设置为30秒，避免异常长查询耗尽CPU、IO资源。三、简答题（每题10分，共40分）请对比InnoDB和MyISAM存储引擎的核心差异，并说明2025年企业级场景下两者的适用场景。答案：（1）核心差异：①事务支持：InnoDB支持ACID事务，支持行级锁、MVCC，可满足高并发读写场景；MyISAM不支持事务，仅支持表级锁，写入性能极差。②数据可靠性：InnoDB支持redolog、undolog、崩溃安全恢复，数据可靠性达99.999%；MyISAM无事务日志，崩溃后易出现数据损坏，无法修复。③外键支持：InnoDB支持外键约束，可保证关联数据的一致性；MyISAM不支持外键。④索引结构：InnoDB采用聚簇索引，主键查询性能极高；MyISAM采用非聚簇索引，索引和数据分离存储，主键查询需两次IO。⑤全文索引：InnoDB8.0+已支持全文索引，性能和MyISAM相当。

（2）适用场景：2025年企业级场景下99%的业务均应使用InnoDB存储引擎，仅两类极端场景可考虑MyISAM：①只读静态数据场景，比如全站公共配置字典、3年以上的历史归档数据，无写入需求、对查询性能要求极高且可容忍数据损坏后重建；②轻量全文检索场景，且未接入Elasticsearch等搜索引擎，需依赖MyISAM的全文索引实现简单文本检索，但该场景已逐步被InnoDB内置全文索引替代，MyISAM已处于淘汰边缘，不建议在核心业务中使用。

2.请简述数据库设计的三范式核心要求，结合电商订单场景说明反范式设计的适用场景及注意事项。答案：（1）三范式核心要求：①第一范式（1NF）：字段具有原子性，不可拆分，比如用户地址字段不可同时存储省、市、区、详细地址，需拆分4个独立字段，满足按地域统计、物流分拣的需求。②第二范式（2NF）：消除部分依赖，所有非主键字段完全依赖主键，不能仅依赖联合主键的部分字段，比如订单商品表的联合主键为（订单ID、商品ID），商品名称仅依赖商品ID，需拆分到商品主表，不能存储在订单商品表。③第三范式（3NF）：消除传递依赖，非主键字段不能依赖其他非主键字段，比如用户表不能存储用户等级名称，用户等级名称仅依赖等级ID，需拆分等级字典表存储等级属性。（2）反范式适用场景：为提升查询性能，在数据一致性可接受的前提下允许适当冗余字段，电商订单场景的典型反范式设计包括：①订单表冗余用户昵称、收货人手机号、省市区等字段，避免查询订单时关联用户表、地址表，单表查询性能提升50%以上；②订单商品表冗余商品名称、商品图片、商品单价等字段，避免关联商品表，同时保证订单生成后商品信息不会随商品表更新而变化，满足历史订单快照的业务需求。

（3）注意事项：①冗余字段需明确更新机制，比如用户修改昵称后是否同步更新历史订单的冗余昵称，需根据业务需求确定最终一致策略，可通过消息队列异步同步冗余字段，避免同步阻塞主业务；②冗余字段需避免频繁更新，若字段更新频率高于每周1次则不适合冗余，会导致大量更新操作反而降低性能；③需定期核对冗余字段和源表的数据一致性，每月执行一次一致性校验脚本，修复异常数据。某金融转账业务要求数据强一致、高可用，请说明事务隔离级别的选择思路，结合该场景说明选择的隔离级别需解决的并发问题。答案：金融业务核心优先保证数据一致性，因此默认选择可重复读（RR）隔离级别，日终对账、批量清算等低并发高一致场景可选择串行化级别，选择思路如下：（1）隔离级别适配性：读未提交会出现脏读，可能读取到其他事务未提交的转账金额，完全不适合金融场景；读提交（RC）会出现不可重复读，同一事务内多次查询账户余额结果可能不一致，导致转账金额计算错误，无法满足对账需求；RR级别通过MVCC+next-keylock可解决脏读、不可重复读、绝大多数幻读问题，同时性能仅比RC低5%~10%，平衡了一致性和性能；串行化级别性能过低，仅适合低并发的清算场景。

（2）RR级别在金融场景的价值：①转账场景下同一事务内多次查询账户余额结果一致，避免出现转账金额计算错误、透支等问题；②批量扣款场景下通过next-keylock避免幻读，保证扣款范围的记录不会新增或删除，避免漏扣或多扣；③配合XA分布式事务可实现跨库、跨机构的强一致，满足多账户转账、跨行清算的需求。（3）注意事项：需严格控制事务长度，金融场景需将事务执行时间控制在1秒以内，峰值期控制在500毫秒以内，长事务会导致undolog无法清理、MVCC版本链过长，同时占用行锁导致大量锁等待，甚至引发死锁。请简述MySQL索引设计的核心原则，列举3种索引失效的典型场景。答案：（1）核心设计原则：①优先选择区分度高的字段作为索引，区分度=count(distinctcol)/count(*)，区分度低于0.1的字段不建议单独建索引，比如性别、状态字段；②联合索引遵循最左匹配原则，过滤频率高、区分度高的字段放在最左侧，比如订单表的(uid,create_time)联合索引，uid过滤频率更高放在左侧；③避免冗余索引，比如已有(uid,create_time)联合索引就无需再单独建uid的单列索引；④控制索引数量，单表索引数量建议不超过5个，避免过多索引降低写入性能，写入量大的表索引数量需控制在3个以内；⑤长字符串字段建议使用前缀索引，比如邮箱字段可建前10位的前缀索引，减少索引存储空间，降低IO开销。（2）索引失效典型场景：①对索引字段使用函数、运算操作，比如WHEREDATE(create_time)=‘2025-06-01’，无法触发create_time字段的索引，可改为WHEREcreate_timeBETWEEN‘2025-06-0100:00:00’AND’2025-06-0123:59:59’适配索引；②字符串字段不加引号触发隐式类型转换，比如WHEREorder_sn=123456，order_sn为varchar类型，MySQL会将order_sn转换为数值类型比较，导致索引失效；③联合索引不满足最左匹配原则，比如联合索引(a,b,c)，查询条件仅包含b、c时无法触发索引，需调整查询条件或索引顺序。

四、实操设计题（共60分）需求背景某跨境电商平台2025年计划上线履约管理模块，核心需求如下：1、用户订单关联履约单，支持多商品拆分发货，一个订单对应1~N个履约单；2、物流轨迹实时同步，支持国内外10+物流商对接，单履约单最多同步50条物流轨迹；3、售后履约支持退换货、部分退款的链路追踪，一个履约单对应1个售后单；4、数据需满足等保2.0三级要求，用户敏感信息加密存储，操作留痕；5、预估峰值日订单量100万单，单表数据量3年预计突破5亿条，需支持高可用、高并发查询，履约单查询响应时间需低于200ms。要求完成以下设计并说明设计思路：1、设计核心ER模型（10分）；2、设计履约单、物流轨迹、售后单3张核心表的表结构（含字段类型、约束、注释，15分）；3、设计核心索引策略（10分）；4、设计分库分表方案，支撑3年5亿条数据的存储查询（15分）；5、设计安全合规方案，满足等保2.0三级要求（10分）。标准答案核心ER模型：核心实体包含4类：①用户实体：存储用户ID、昵称等基础信息；②订单实体：存储订单ID、下单时间、总金额等订单基础信息；③履约单实体：存储履约单ID、关联订单ID、用户ID、收货信息、物流信息、履约状态等；④物流轨迹实体：存储轨迹ID、关联履约单ID、物流节点、时间、描述等；⑤售后单实体：存储售后单ID、关联履约单ID、售后类型、状态、退款金额等。实体关系为：用户1:N订单，订单1:N履约单，履约单1:N物流轨迹，履约单1:1售后单。评分标准：覆盖所有核心实体得5分，实体关系正确得3分，包含所有核心属性得2分。核心表结构设计：（1）履约单表t_fulfillment字段名类型约束注释fulfill_idbigintunsignedNOTNULLPRIMARYKEY履约单ID（采用雪花ID/UUIDv7有序主键，避免索引碎片化）order_idbigintunsignedNOTNULL关联订单IDuser_idbigintunsignedNOTNULL所属用户IDreceiver_namevarchar(64)NOTNULL收货人姓名receiver_phonevarchar(64)NOTNULL收货人手机号（AES加密存储）receiver_addressvarchar(255)NOTNULL收货详细地址（AES加密存储）logistics_corpvarchar(32)DEFAULTNULL物流商编码logistics_novarchar(64)DEFAULTNULL物流单号statustinyintunsignedNOTNULLDEFAULT0履约状态：0待发货1已发货2已签收3已取消4售后中create_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMP创建时间update_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP更新时间is_deletedtinyintunsignedNOTNULLDEFAULT0软删标记：0未删除1已删除（2）物流轨迹表t_fulfillment_logistics字段名类型约束注释log_idbigintunsignedNOTNULLPRIMARYKEY轨迹IDfulfill_idbigintunsignedNOTNULL关联履约单IDlogistics_nodevarchar(32)NOTNULL物流节点编码：0揽收1中转2清关3派送4签收node_descvarchar(255)NOTNULL节点描述node_timedatetimeNOTNULL节点时间create_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMP创建时间（3）售后单表t_fulfillment_aftersale字段名类型约束注释aftersale_idbigintunsignedNOTNULLPRIMARYKEY售后单IDfulfill_idbigintunsignedNOTNULLUNIQUE关联履约单IDtypetinyintunsignedNOTNULL售后类型：1仅退款2退货退款3换货statustinyintunsignedNOTNULLDEFAULT0售后状态：0待审核1已同意2已寄回3已退款4已完成5已拒绝refund_amountdecimal(10,2)NOTNULLDEFAULT0退款金额apply_reasonvarchar(255)DEFAULTNULL申请原因create_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMP创建时间update_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP更新时间评分标准：表结构覆盖所有核心字段得8分，字段类型、约束合理得4分，主键设计、加密设计符合要求得3分。核心索引策略：①履约单表创建联合索引idx_user_ctime(user_id,create_time,status)，支撑用户端查询我的履约单，覆盖所有查询字段无需回表；创建唯一索引idx_fulfill_order(fulfill_id,order_id)，支撑按订单ID查询关联履约单；创建联合索引idx_logistics_no(logistics_corp,logistics_no)，支撑物流商回调同步物流轨迹时查询履约单。②物流轨迹表创建联合索引idx_fulfill_log(fulfill_id,node_time)，支撑按履约单ID查询物流轨迹，按时间排序无需额外排序。③售后单表创建唯一索引idx_fulfill_aftersa