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

2025年MySQL数据库设计原则试题及答案一、单项选择题（共10题，每题3分，共30分）1.设计用户表存储中国大陆地区11位手机号，以下字段类型最符合设计原则的是？A.INTUNSIGNEDB.BIGINTUNSIGNEDC.CHAR(11)D.VARCHAR(20)答案：C解析：手机号属于无需参与数值运算的字符串类标识，使用数值类型存储会存在两个问题：一是若后续需存储带国际区号、+号前缀的手机号时无法兼容；二是查询时若传入字符串类型的手机号会触发隐式类型转换，导致索引失效。CHAR(11)为固定长度存储，相比VARCHAR可节省长度标识的1字节空间，查询性能更优，且符合字段类型与业务属性匹配的设计原则。2.关系型数据库第三范式（3NF）的核心要求是？A.消除非主属性对主键的部分函数依赖B.消除非主属性对主键的传递函数依赖C.消除主属性对主键的部分和传递函数依赖D.消除多值依赖答案：B解析：1NF要求字段原子性不可拆分；2NF在1NF基础上消除非主属性对主键的部分函数依赖，适用于联合主键场景；3NF在2NF基础上消除非主属性对主键的传递函数依赖，避免数据冗余和更新异常；BCNF消除主属性对主键的部分和传递依赖；4NF消除多值依赖。核心交易场景通常要求满足3NF，减少数据不一致风险。3.以下场景中，最适合为对应字段创建索引的是？A.字段仅出现在SELECT返回列表中，从未作为WHERE、JOIN、ORDERBY条件B.性别字段，区分度约50%C.订单表中与用户表关联的user_id字段D.频繁执行UPDATE操作的库存字段答案：C解析：索引设计的核心原则是为查询过滤、关联、排序字段创建，JOIN关联字段创建索引可大幅降低嵌套循环关联的成本。A场景创建索引无任何收益，反而增加写操作的索引维护成本；B场景区分度低于30%的字段索引收益极低，优化器大概率会选择全表扫描；D场景频繁更新的字段会导致索引频繁重组，增加IO开销，索引收益远低于成本。MySQL8.0+支持不可见索引，可在删除索引前设置为不可见验证业务影响，避免误删索引导致故障。4.以下主键设计方案中，最符合MySQLInnodb引擎设计原则的是？A.使用用户身份证号作为主键B.使用无序UUID作为主键C.使用自增BIGINTUNSIGNED作为单机场景主键D.使用手机号作为主键答案：C解析：Innodb是聚簇索引组织表，主键顺序直接影响数据写入性能，有序主键可避免页分裂，提升写入效率。A、D选项属于敏感业务字段，一旦业务规则变更（比如手机号换绑、身份证号升位）需要修改主键，成本极高，且存在数据泄露风险；B选项无序UUID会导致数据随机写入，产生大量页分裂，空间利用率低，写入性能差。分布式场景下可采用雪花ID、美团Leaf等有序分布式唯一ID作为主键，兼顾全局唯一性和写入性能。5.以下反范式设计方案中，合理性最高的是？A.在订单表中冗余存储商品名称、商品单价字段B.在用户表中冗余存储用户所有历史订单的总金额字段C.在商品表中冗余存储商品的详情页大文本字段D.在支付表中冗余存储用户的银行卡号字段答案：A解析：反范式设计的核心是空间换时间，仅适用于查询频率高、更新频率低、数据一致性要求可通过方案兜底的场景。A场景订单创建后商品名称、单价不会再变更，冗余后可避免每次查询订单都关联商品表，减少JOIN操作，性能提升明显；B场景用户每新增一笔订单都需要更新总金额字段，更新频率极高，容易出现数据不一致，可通过实时数仓汇总查询替代冗余；C场景大文本字段会大幅增加单表数据量，降低查询性能，应拆分到独立附属表；D场景银行卡号属于敏感字段，冗余会增加数据泄露风险。6.关于MySQL8.0+JSON字段的设计原则，以下说法正确的是？A.所有扩展属性都应存储在JSON字段中，避免频繁ALTERTABLE加字段B.经常需要作为查询过滤条件的属性，应单独拆分为结构化字段，不要存储在JSON中C.JSON字段无法创建索引，不适合存储需要查询的属性D.JSON字段存储的属性越多，查询性能越高答案：B解析：JSON字段适合存储非固定结构、查询频率低的扩展属性，其查询性能远低于结构化字段。A选项若把高频查询的属性也存入JSON，会导致查询性能下降；C选项MySQL8.0支持JSON函数索引、虚拟列索引，可对JSON内部的属性创建索引，但性能仍低于直接结构化存储；D选项JSON嵌套层级越深、属性越多，解析成本越高，查询性能越低。7.以下数据库设计方案中，最能有效降低死锁概率的是？A.事务中按随机顺序访问多张表B.将长事务拆分为多个短事务，减少锁持有时间C.对大表执行全表更新操作时不加WHERE条件D.关联查询字段不建索引，触发表锁答案：B解析：死锁的四个必要条件为互斥、持有并等待、不可剥夺、循环等待，破坏任一条件即可避免死锁。B选项短事务持有锁的时间大幅缩短，锁冲突概率显著降低，可有效减少死锁；A选项随机顺序访问表会触发循环等待，提升死锁概率；C、D选项会触发表锁，锁粒度变大，锁冲突概率大幅提升。8.以下分库分表设计中，属于水平分表的是？A.将用户表的基础信息和扩展信息拆分为user_base和user_ext两张表B.按用户ID哈希取模，将订单表拆分为1024张结构完全相同的order_0到order_1023表C.将电商系统的用户表、订单表、商品表拆分到不同的数据库实例D.将订单表中的大文本字段remark拆分到独立的order_remark表答案：B解析：水平分表是指按照分片规则将同一张表的数据拆分到多张结构相同的表中，解决单表数据量过大的问题；A、D属于垂直分表，按字段维度拆分表；C属于垂直分库，按业务模块拆分数据库。水平分表的分片键需选择最高频的查询字段，避免跨分片查询。9.存储包含emoji表情的多语言用户昵称，以下字符集最符合设计原则的是？A.utf8B.utf8mb4C.gbkD.latin1答案：B解析：MySQL中的utf8是utf8mb3的别名，最多支持3字节存储，无法存储4字节的emoji表情和部分生僻汉字；utf8mb4支持4字节存储，完全兼容所有Unicode字符，是MySQL8.0的默认字符集，排序规则推荐使用utf8mb4_0900_ai_ci，性能优于旧版的utf8mb4_general_ci，且支持不区分大小写、不区分重音的排序，适配多语言场景。10.关于MySQL8.0+DDL设计原则，以下说法正确的是？A.大表加字段必须锁表，需在业务低峰期执行B.推荐使用ONLINEDDL的INSTANT算法加字段，可瞬间完成，不锁表C.业务高峰期可直接执行ALTERTABLE操作，无需控制执行时间D.DDL执行失败会留下中间表，需要手动清理答案：B解析：MySQL8.0支持INSTANT算法的ONLINEDDL，在表末尾加允许NULL、带默认值的字段时无需重建表，可瞬间完成，不影响业务读写。A选项仅适用于MySQL5.7及更早版本；C选项高峰期执行DDL会导致IO资源占用过高，影响业务；D选项MySQL8.0支持原子DDL，DDL执行失败会自动回滚所有变更，不会残留中间文件。二、多项选择题（共5题，每题4分，共20分）1.以下属于MySQL索引设计正确原则的是？A.联合索引设计遵循最左前缀匹配原则B.尽量设计覆盖索引，减少回表操作C.避免在索引字段上执行函数、运算操作，避免隐式类型转换D.索引越多越好，覆盖所有可能的查询场景答案：ABC解析：索引的维护成本与索引数量正相关，每执行一次INSERT、UPDATE、DELETE操作都需要更新所有关联索引，索引过多会大幅降低写性能，因此仅需为高频查询场景创建索引，D选项错误。2.关于范式与反范式的适用场景，以下说法正确的是？A.核心交易系统（比如支付、订单核心表）应尽量遵循3NF，减少数据冗余，避免更新异常B.报表系统、数据仓库可大量采用反范式设计，减少JOIN操作，提升查询性能C.高并发查询的前台业务可适当采用反范式设计，通过冗余字段降低关联成本D.所有业务系统都必须严格遵循BCNF，保证数据完全无冗余答案：ABC解析：严格遵循高级范式会导致表拆分过细，查询时需要多次JOIN，性能极差，非核心交易场景可根据业务需求合理采用反范式设计，通过业务层双写、CDC同步、定时校验等方式保证数据一致性，D选项错误。3.以下字段设计中属于错误做法的是？A.允许字符串类型字段默认值为NULLB.使用BIGINT存储固定长度的11位手机号C.使用ENUM类型存储用户性别（男/女/未知）D.使用TEXT类型存储长度不超过200字的用户收货地址答案：ABD解析：NULL值会导致索引失效、COUNT统计时忽略NULL值、数值运算结果异常，所有字段应设置为NOTNULL并指定默认值，A选项错误；手机号无需参与数值运算，使用数值类型存储会触发隐式转换，B选项错误；TEXT类型存储成本高、无法设置默认值、性能低于VARCHAR，短文本应使用VARCHAR存储，D选项错误；ENUM类型适合存储状态类固定枚举值，占用空间小，查询性能高，C选项正确。4.以下属于事务设计正确原则的是？A.事务粒度尽量小，避免在事务中执行RPC调用、文件IO等外部操作B.所有事务按固定顺序访问资源，避免循环等待C.尽量使用短事务，减少锁持有时间D.尽量使用长事务，保证多个操作的原子性答案：ABC解析：长事务会导致锁持有时间过长、MVCC的UNDO日志膨胀、主从复制延迟等问题，应尽量避免，原子性要求可通过分布式事务、最终一致性方案实现，D选项错误。5.高可用分布式架构下的MySQL设计原则包括？A.所有表必须显式指定主键，避免无主键表导致的主从复制、MGR集群性能问题B.避免使用外键约束，降低锁冲突概率和业务耦合度C.避免使用存储过程、触发器、自定义函数，降低数据库迁移、扩缩容的复杂度D.所有表只要数据量超过100万就必须做分库分表答案：ABC解析：分库分表会大幅提升业务复杂度，仅在单表数据量超过千万、查询性能出现瓶颈时才考虑实施，D选项错误。三、判断题（共10题，每题2分，共20分）1.设计表时所有字段都应设置为NOTNULL并指定默认值。（√）解析：NULL值会引发索引失效、统计异常、运算错误等问题，设置默认值可避免插入数据时的字段缺失错误。2.联合索引的字段顺序不影响索引使用效率。（×）解析：联合索引需遵循最左前缀原则，应将区分度高、查询频率高的字段放在最左侧，可提升索引过滤效率。MySQL8.0支持索引跳跃扫描，仅在左侧字段枚举值少的场景下可跳过左侧字段使用索引，但性能仍低于按最左原则设计的索引。3.反范式设计必然会导致数据不一致。（×）解析：可通过业务层双写、CDC数据同步、定时对账校验等方式保证冗余字段的数据一致性，仅当兜底方案缺失时才会出现不一致问题。4.TIMESTAMP类型的存储成本低于DATETIME类型，优先选择TIMESTAMP存储时间。（×）解析：TIMESTAMP为4字节存储，仅支持1970-2038年的时间范围，存在2038年溢出问题，若需存储超过2038年的时间，应选择8字节的DATETIME(6)类型，支持微秒级精度。5.覆盖索引是指查询所需的所有字段都包含在索引中，无需回表访问聚簇索引。（√）解析：覆盖索引可将查询性能提升数倍，是高频查询场景的核心优化手段。6.分片键可以任意选择，只要能将数据拆分均匀即可。（×）解析：分片键必须选择高频查询字段，否则会导致大量跨分片查询，性能甚至低于不分表的单表查询。7.Innodb表的主键长度越短越好，可降低二级索引的存储成本。（√）解析：Innodb的二级索引叶子节点存储主键值，主键越短，每个二级索引页可存储的索引条目越多，IO效率越高。8.大表删除历史数据时应直接执行DELETEFROMtableWHEREcreate_time<'xxx'。（×）解析：大批量DELETE会产生长事务、锁大量行、导致主从延迟，应采用分批删除的方式，每次删除1000-2000行，分多次执行。9.业务表中必须预留3-5个备用字段，避免后续业务迭代时执行DDL。（×）解析：备用字段没有明确语义，维护成本高，MySQL8.0支持INSTANT算法加字段，可瞬间完成，无需预留备用字段，非固定扩展属性可通过JSON字段存储。10.外键约束是保证数据一致性的最优方案，应尽量多用。（×）解析：外键会增加写操作的锁冲突概率、提升业务耦合度、影响主从复制性能，数据一致性应通过业务层实现。四、简答题（共3题，每题10分，共30分）1.请简述MySQL数据库设计的三大核心原则及适用场景。答案：MySQL设计的三大核心原则为规范性、性能优先、可扩展性：（1）规范性原则：即遵循数据库范式要求，核心目标是减少数据冗余，避免更新、插入、删除异常，适用于核心交易场景，比如支付、订单、用户核心表，要求数据一致性高、更新逻辑复杂，遵循3NF可大幅降低数据不一致的风险。（2）性能优先原则：核心目标是提升查询和写入性能，允许适当采用反范式设计、合理创建索引、拆分冷热数据、分库分表等方案，通过空间换时间，适用于高并发查询的前台业务、报表业务，比如电商商品详情页、用户订单列表查询场景，减少JOIN操作、避免回表可将性能提升数倍。（3）可扩展性原则：核心目标是降低后续业务迭代的改造成本，比如采用JSON字段存储非固定扩展属性、兼容分布式高可用架构、避免使用数据库特有的语法特性，适用于快速迭代的互联网业务，避免频繁DDL、数据库迁移导致的业务故障。2.请简述联合索引的设计方法，以及常见的索引失效场景。答案：联合索引的设计方法：（1）最左前缀优先：查询条件从左到右匹配索引字段，不可跳过中间字段，设计时将查询频率最高的字段放在最左侧；（2）高区分度优先：将区分度高的字段放在左侧，可在索引扫描阶段过滤更多数据，减少回表次数，区分度计算方式为COUNT(DISTINCT字段)/COUNT(*)，通常要求区分度不低于30%；（3）覆盖索引优先：将查询需要返回的字段包含在联合索引中，无需回表访问聚簇索引，大幅提升查询性能；（4）排序/groupby优先：将需要排序、分组的字段放在联合索引的右侧，利用索引的有序性避免文件排序。常见索引失效场景：（1）索引字段上执行函数、算术运算、类型转换；（2）字符串字段查询时不加引号，触发隐式类型转换；（3）LIKE查询采用左模糊或全模糊匹配；（4）OR条件两侧有一个字段没有索引；（5）联合索引查询时跳过左侧字段，且不满足索引跳跃扫描条件；（6）优化器判断全表扫描性能优于索引扫描时，主动放弃使用索引。3.请简述分布式高并发场景下MySQL表设计的特殊注意事项。答案：（1）主键设计：避免使用单机自增ID的单点问题，采用有序分布式唯一ID（雪花ID、Leaf等）作为主键，保证聚簇索引有序写入，避免页分裂，提升写入性能；（2）字段设计：避免存储大文本、大BLOB字段，将低频访问的大字段拆分到附属表，核心表仅存储高频访问的小字段，提升缓存命中率和查询性能；（3）关联设计：尽量减少JOIN操作，合理冗余高频查询的关联字段，避免跨库关联，关联查询的性能问题可通过宽表、实时数仓汇总的方式解决；（4）分库分表：合理选择分片键，保证90%以上的查询都落在单个分片上，避免跨分片事务和跨分片查询，控制单表数据量在千万级别以内；（5）高可用兼容：避免使用外键、存储过程、触发器、自定义函数等数据库特性，兼容MGR集群、主从切换、云数据库自动扩缩容等架构要求；（6）冷热分离：将创建时间超过3-6个月的历史冷数据归档到对象存储、OLAP数据库，核心表仅存储热数据，降低单表数据量，提升查询性能。五、实操设计题（共2题，每题25分，共50分）1.某电商平台需设计用户订单表，需求如下：①存储字段包括用户ID、订单ID、商品ID、商品名称、商品单价、购买数量、实付金额、订单状态、支付时间、发货时间、收货地址、用户手机号、关联优惠券ID；②高频查询场景：a.用户查询自己的订单列表，按支付时间倒序分页；b.商家查询单个商品的下单记录；c.运营按订单状态统计每日订单量；③3年预计总数据量5000万。请给出表结构设计、索引设计、优化方案，并说明设计理由。答案：表结构设计```sqlCREATETABLE`order_info`(`order_id`bigintunsignedNOTNULLCOMMENT'分布式有序订单ID',`user_id`bigintunsignedNOTNULLCOMMENT'用户ID',`goods_id`bigintunsignedNOTNULLCOMMENT'商品ID',`goods_name`varchar(128)NOTNULLCOMMENT'冗余商品名称，避免关联商品表',`goods_price`decimal(10,2)NOTNULLCOMMENT'冗余商品单价，避免关联商品表',`buy_num`smallintunsignedNOTNULLCOMMENT'购买数量',`pay_amount`decimal(10,2)NOTNULLCOMMENT'实付金额',`order_status`tinyintunsignedNOTNULLDEFAULT'0'COMMENT'订单状态：0待支付1已支付2已发货3已完成4已取消',`pay_time`datetimeNOTNULLDEFAULT'1970-01-0100:00:00'COMMENT'支付时间',`deliver_time`datetimeNOTNULLDEFAULT'1970-01-0100:00:00'COMMENT'发货时间',`address`varchar(256)NOTNULLCOMMENT'收货地址',`mobile`char(11)NOTNULLCOMMENT'用户手机号',`coupon_id`bigintunsignedNOTNULLDEFAULT'0'COMMENT'优惠券ID，无则为0',`create_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',`update_time`datetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',`ext`jsonDEFAULTNULLCOMMENT'扩展属性，存储发票信息、备注等非固定字段',PRIMARYKEY(`order_id`))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ciCOMMENT='订单表';```设计理由：①采用分布式有序ID作为主键，兼顾全局唯一性和写入性能；②冗余goods_name、goods_price字段，订单创建后该字段不会变更，无需关联商品表，减少JOIN操作；③用tinyint存储订单状态，节省存储空间，查询性能优于字符串；④ext字段存储非固定扩展属性，避免频繁DDL加字段；⑤所有字段设置为NOTNULL，避免NULL值引发的异常。索引设计```sql-覆盖用户查询个人订单场景，最左匹配user_id，按pay_time倒序，无需额外排序CREATEINDEX`idx_uid_ptime`ON`order_info`(`user_id`,`pay_time`DESC);-覆盖商家查询商品下单记录场景CREATEINDEX`idx_gid_ctime`ON`order_info`(`goods_id`,`create_time`);-覆盖运营按状态统计订单场景CREATEINDEX`idx_status_ctime`ON`order_info`(`order_status`,`create_time`);```设计理由：所有索引均为覆盖高频查询场景设计，避免回表，性能最优。优化方案①分表设计：总数据量5000万，按create_time按月水平分表，控制单表数据量在500万以内，查询性能更高，历史冷数据可直接归档删除；②一致性兜底：商品名称、单价变更时，若需同步历史订单的冗余字段，可通过MQ异步更新，每日定时对账校验冗余字段一致性；③读写分离：用户查询订单、商家查询下单记录的读请求走从库，写入请求走主库，提升并发能力。2.某社交平台需设计用户评论表，需求如下：①存储字段包括评论ID、用户ID、帖子ID、评论内容、点赞数、父评论ID、评论时间；②高频查询场景：a.按帖子ID查询评论列表，按点赞数倒序分页；b.用户查询自己发布的所有评论；③预计总数据量10亿级。请给出完整设计方案，并说明遵循的设计原则。答案：表结构设计```sqlCREATETABLE`comment_info`(`comment_id`bigintunsignedNOTNULLCOMMENT'分布式有序评论ID',`user_id`bigintunsignedNOTNULLCOMMENT'发布用户ID',`post_id`bigintunsignedNOTNULLCOMMENT'帖子ID',`content`varchar(512)NOTNULLCOMMENT'评论内容',`like_num`intunsignedNOTNULLDEFAULT'0'COMMENT'点赞数，冗余存储避免count查询',`parent_comment_id`bigintunsignedNOTNULLDEFAULT'0'COMMENT'父评论ID