2025年大学mysqlp试题及答案

2025年大学mysqlp试题及答案第一部分单项选择题（每题2分，共40分）MySQL关系模型中，要求外键的取值要么为空，要么等于关联表的主键取值，该规则属于（）A.实体完整性B.参照完整性C.用户定义完整性D.域完整性MySQL8.0版本的默认存储引擎是（）A.MyISAMB.InnoDBC.MemoryD.Archive数据库设计第三范式（3NF）的核心要求是（）A.属性不可拆分B.消除非主属性对主键的部分函数依赖C.消除非主属性对主键的传递函数依赖D.消除主属性对主键的部分和传递函数依赖事务ACID特性中，隔离性是指（）A.事务执行的结果必须是使数据库从一个一致性状态转变到另一个一致性状态B.事务一旦提交，对数据库的修改就是永久的，后续故障不会影响其执行结果C.一个事务的执行不能被其他事务干扰，事务内部操作和使用的数据对其他并发事务是隔离的D.事务中包含的所有操作要么全部执行成功，要么全部失败回滚InnoDB存储引擎默认使用的索引数据结构是（）A.B树B.B+树C.哈希表D.跳表若表A有10条记录，表B有8条记录，执行SELECT*FROMALEFTJOINBONA.id=B.a_id，最终返回的记录数不可能是（）A.8B.10C.15D.18下列聚合函数中，不会忽略列的NULL值的是（）A.SUM(列名)B.AVG(列名)C.COUNT(*)D.MAX(列名)MySQLInnoDB存储引擎中，以下哪个事务隔离级别可以完全避免幻读问题（）A.读未提交（READUNCOMMITTED）B.读已提交（READCOMMITTED）C.可重复读（REPEATABLEREAD）D.串行化（SERIALIZABLE）MySQLInnoDB处理死锁的默认机制是（）A.等待超时后自动回滚所有事务B.主动检测死锁，回滚代价最小的事务C.主动检测死锁，回滚所有涉及死锁的事务D.不处理死锁，由应用层自行解决MySQL中用于配置慢查询阈值的参数是（）A.slow_query_logB.slow_query_log_fileC.long_query_timeD.log_queries_not_using_indexesMySQL8.0中用于实现通用表表达式（CTE）的关键字是（）A.VIEWB.PROCEDUREC.WITHD.TEMPORARY下列窗口函数中，排名结果不会出现跳跃的是（）A.RANK()B.DENSE_RANK()C.ROW_NUMBER()D.NTILE()MySQL中查询JSON字段info中key为age的取值，语法正确的是（）A.info->‘$.age’B.info[‘age’]C.info.ageD.JSON_GET(info,‘age’)

14.MySQL主从复制架构中，从库负责读取主库二进制日志并写入中继日志的线程是（）A.BinlogDump线程B.IO线程C.SQL线程D.Worker线程MySQLInnoDB中用于实现事务原子性、支持数据回滚的日志是（）A.RedoLogB.UndoLogC.BinlogD.RelayLog若执行的查询字段全部包含在索引字段中，无需回表查询聚簇索引数据，该索引被称为（）A.唯一索引B.联合索引C.覆盖索引D.前缀索引现有联合索引idx_name_age_class(name,age,class)，下列查询语句中无法用到该联合索引的是（）A.SELECT*FROMstudentWHEREname=‘张三’ANDage=18B.SELECT*FROMstudentWHEREname=‘张三’ANDclass=‘高三1班’C.SELECT*FROMstudentWHEREage=18ANDclass=‘高三1班’D.SELECT*FROMstudentWHEREname=‘张三’ANDage>20ANDclass=‘高三1班’下列SQL语句中属于DDL（数据定义语言）范畴的是（）A.INSERTB.UPDATEC.DELETED.ALTER原子DDL特性是从哪个MySQL版本开始正式支持的（）A.MySQL5.6B.MySQL5.7C.MySQL8.0D.MySQL8.2下列关于数据库垂直拆分的描述，错误的是（）A.按照业务维度将不同业务的表拆分到不同的数据库实例B.可以降低单库的数据量和访问压力C.拆分后可以针对不同业务做独立的扩容和优化D.拆分后可以有效解决单表数据量过大的问题第二部分多项选择题（每题3分，共30分，多选、少选、错选均不得分）事务ACID特性包含以下哪些选项（）A.原子性（Atomicity）B.一致性（Consistency）C.隔离性（Isolation）D.持久性（Durability）下列关于InnoDB和MyISAM存储引擎的描述，正确的有（）A.InnoDB支持事务、外键，MyISAM不支持B.InnoDB支持行级锁，MyISAM仅支持表级锁C.InnoDB采用聚簇索引结构，MyISAM采用非聚簇索引结构D.InnoDB支持崩溃安全恢复，MyISAM崩溃后易出现数据损坏下列场景中适合创建索引的有（）A.经常作为WHERE查询条件的字段B.重复值极高的性别字段C.经常作为JOIN关联条件的字段D.经常需要排序（ORDERBY）和分组（GROUPBY）的字段MySQLInnoDB的锁机制中，属于表级锁的有（）A.意向共享锁（IS）B.意向排他锁（IX）C.记录锁（RecordLock）D.间隙锁（GapLock）下列属于MySQLSQL注入防范措施的有（）A.使用预处理语句（PreparedStatement）B.对用户输入的特殊字符进行转义C.避免使用动态拼接SQLD.最小化数据库账号权限，禁止普通账号访问系统表下列属于MySQL8.0新增特性的有（）A.窗口函数B.通用表表达式（CTE）C.原子DDLD.InnoDB存储引擎MySQL主从复制的核心流程包含以下哪些步骤（）A.主库将数据修改记录写入二进制日志（Binlog）B.从库IO线程连接主库，请求拉取指定位置之后的BinlogC.主库通过BinlogDump线程将Binlog内容发送给从库IO线程D.从库SQL线程重放中继日志（RelayLog）的内容，将数据变更同步到从库本地MySQL的EXPLAIN执行计划中，以下type列的取值性能优于range的有（）A.ALLB.refC.eq_refD.constMySQL触发器的触发时机包含以下哪些选项（）A.BEFOREB.DURINGC.AFTERD.INSTEADOF下列属于MySQL常见数据备份方式的有（）A.冷备：关停MySQL实例后直接拷贝数据文件B.热备：实例运行状态下通过xtrabackup等工具备份C.逻辑备份：通过mysqldump等工具导出SQL语句备份D.增量备份：仅备份上一次全量备份之后发生变更的数据第三部分判断题（每题1分，共10分，正确填√，错误填×）CHAR类型是定长字符串，VARCHAR类型是变长字符串，相同长度下VARCHAR的存储空间利用率更高。（）COUNT(列名)统计结果会忽略该列的NULL值，COUNT(*)统计结果不会忽略NULL值。（）MyISAM存储引擎支持事务和行级锁，适合高并发写操作的场景。（）唯一索引允许列中存在多个NULL值，因为NULL不等于任何值（包括NULL本身）。（）标准SQL定义的可重复读（REPEATABLEREAD）隔离级别可以完全避免幻读问题。（）表的索引数量越多，查询性能越好，因此应尽可能多创建索引。（）TRUNCATETABLE属于DDL语句，执行后无法回滚；DELETE属于DML语句，执行后可以通过事务回滚。（）存储过程中可以调用其他存储过程，也可以调用自定义函数。（）MySQL中JSON类型字段无法直接创建普通B+树索引，需通过函数索引实现查询加速。（）MySQL主从复制默认是半同步复制，主库必须等待至少一个从库收到Binlog并写入中继日志后才会返回客户端提交成功。（）第四部分实操题（每题15分，共30分）现有三张表结构如下：学生表：student(s_idINTPRIMARYKEYCOMMENT‘学生ID’,s_nameVARCHAR(20)NOTNULLCOMMENT‘学生姓名’,class_idINTCOMMENT‘班级ID’,birthdayDATECOMMENT‘出生日期’)课程表：course(c_idINTPRIMARYKEYCOMMENT‘课程ID’,c_nameVARCHAR(30)NOTNULLCOMMENT‘课程名称’,teacher_nameVARCHAR(20)NOTNULLCOMMENT‘授课教师姓名’)成绩表：score(s_idINTCOMMENT‘学生ID’,c_idINTCOMMENT‘课程ID’,scoreINTCOMMENT‘考试成绩’,PRIMARYKEY(s_id,c_id),CONSTRAINTfk_sidFOREIGNKEY(s_id)REFERENCESstudent(s_id),CONSTRAINTfk_cidFOREIGNKEY(c_id)REFERENCEScourse(c_id))根据以上表结构，编写SQL语句实现以下需求：（1）查询所有选修了课程ID为1001的学生姓名和对应的成绩，按成绩降序排序。（2）查询平均成绩大于85分的学生ID、学生姓名和平均成绩，按平均成绩降序排序。（3）查询所有没有选修课程“高等数学”的学生姓名。（4）使用窗口函数查询每个班级内总分排名前三的学生ID、姓名、班级ID和总分，排名相同的并列显示。现有慢查询语句：SELECTs.s_name,sc.scoreFROMstudentsJOINscorescONs.s_id=sc.s_idWHEREsc.c_id=1003ANDsc.score>90ORDERBYsc.scoreDESC;经EXPLAIN分析，该语句type列为ALL（全表扫描），Extra列显示Usingfilesort。请分析慢查询原因，给出优化方案，写出索引创建语句和优化后的SQL语句。第五部分案例分析题（每题20分，共40分）某电商平台订单系统当前单库单表的订单表数据量已突破5000万，查询性能持续下降，高并发下经常出现接口超时。请完成以下设计：（1）设计符合第三范式的订单相关表结构（至少包含用户表、订单表、订单明细表、商品表），给出核心字段和约束。（2）给出索引设计方案，说明每个索引的设计逻辑。（3）说明高并发场景下订单库存扣减的优化方案，避免超卖和性能瓶颈。某生产环境MySQL8.0实例突然出现CPU占用率100%，业务接口大面积超时。请给出完整的排查流程和对应的解决方案。参考答案及解析第一部分单项选择题B解析：参照完整性要求外键的取值必须匹配关联表主键的取值，或为空。B解析：MySQL5.5之后默认存储引擎为InnoDB，8.0延续该默认配置。C解析：A是第一范式要求，B是第二范式要求，C是第三范式要求，D是BC范式要求。C解析：A是一致性，B是持久性，C是隔离性，D是原子性。B解析：InnoDB默认索引结构为B+树，哈希索引为自适应索引，需手动开启。A解析：左连接返回左表所有记录，匹配右表记录，最少返回10条，最多返回10*8=80条，因此8条不可能。C解析：COUNT(*)统计的是行数，不关心列是否为NULL，其余聚合函数均忽略NULL值。C解析：标准SQL的RR级别无法避免幻读，但MySQLInnoDB通过MVCC+临键锁在RR级别实现了幻读防护，串行化虽然也能避免但性能极低，因此最优答案为C。B解析：InnoDB默认开启死锁检测，主动检测到死锁后会回滚代价最小的事务。C解析：long_query_time用于配置慢查询阈值，单位为秒，超过该阈值的查询会被记录到慢查询日志。C解析：WITH关键字用于声明CTE，支持递归和非递归两种写法。B解析：RANK()排名会跳跃（如两个第1之后直接是第3），DENSE_RANK()排名连续（两个第1之后是第2），ROW_NUMBER()生成唯一连续排名。A解析：MySQL中JSON字段的取值语法为字段名->‘.keB解析：BinlogDump是主库线程，IO线程负责拉取主库Binlog写入中继日志，SQL线程负责重放中继日志。B解析：UndoLog记录数据修改前的快照，用于事务回滚和MVCC快照读，RedoLog用于实现事务持久性。C解析：覆盖索引的查询字段全部包含在索引中，无需回表查询聚簇索引，性能更高。C解析：联合索引遵循最左前缀匹配原则，C选项查询条件不包含最左字段name，无法用到该索引。D解析：INSERT、UPDATE、DELETE属于DML，ALTER、CREATE、DROP属于DDL。C解析：MySQL8.0开始支持原子DDL，DDL操作要么全部成功要么全部失败，不会出现半完成状态。D解析：垂直拆分是按业务拆分库，水平拆分才是解决单表数据量过大的方案。第二部分多项选择题ABCD解析：事务四大特性为原子性、一致性、隔离性、持久性。ABCD解析：四个选项均为InnoDB和MyISAM的核心区别。ACD解析：重复值极高的字段（如性别仅2种取值）创建索引的收益极低，反而会增加维护开销，不适合建索引。AB解析：意向锁是表级锁，记录锁、间隙锁、临键锁是行级锁。ABCD解析：四个选项均为SQL注入的有效防范措施。ABC解析：InnoDB从MySQL5.5开始成为默认存储引擎，不属于8.0新增特性。ABCD解析：四个选项均为主从复制的核心流程。BCD解析：type列性能从高到低为：system>const>eq_ref>ref>range>index>ALL，因此BCD性能优于range。AC解析：MySQL触发器的触发时机为BEFORE和AFTER，不支持DURING和INSTEADOF。ABCD解析：四个选项均为MySQL常见的备份方式。第三部分判断题√解析：CHAR定长会预留空间，VARCHAR仅占用实际存储的字符长度+1/2字节的长度标识，存储空间利用率更高。√解析：COUNT(列名)仅统计非NULL的记录数，COUNT(*)统计所有行数。×解析：MyISAM不支持事务和行级锁，仅支持表级锁，写性能差，不适合高并发写场景。√解析：NULL在SQL中不等于任何值，因此唯一索引允许多个NULL值存在。×解析：标准SQL的RR级别仅解决脏读和不可重复读，无法避免幻读，MySQLInnoDB对RR级别做了扩展才解决了幻读问题。×解析：索引过多会增加增删改操作的索引维护开销，降低写性能，应按需创建索引。√解析：TRUNCATE是DDL，执行后立即生效无法回滚，DELETE是DML，支持事务回滚。√解析：存储过程支持嵌套调用，也可以调用自定义函数。√解析：JSON是半结构化类型，无法直接创建普通B+树索引，需通过JSON_EXTRACT等函数创建函数索引实现加速。×解析：MySQL主从复制默认是异步复制，半同步复制需要手动开启。第四部分实操题（1）SQL语句：

SELECTs.s_name,sc.score

FROMstudents

JOINscorescONs.s_id=sc.s_id

WHEREsc.c_id=1001

ORDERBYsc.scoreDESC;（2）SQL语句：

SELECTs.s_id,s.s_name,AVG(sc.score)ASavg_score

FROMstudents

JOINscorescONs.s_id=sc.s_id

GROUPBYs.s_id,s.s_name

HAVINGAVG(sc.score)>85

ORDERBYavg_scoreDESC;（3）SQL语句：

SELECTs.s_name

FROMstudents

WHEREs.s_idNOTIN(

SELECTsc.s_id

FROMscoresc

JOINcoursecONsc.c_id=c.c_id

WHEREc.c_name='高等数学'

);（4）SQL语句：

WITHstu_totalAS(

SELECTs.s_id,s.s_name,s.class_id,SUM(sc.score)AStotal_score

FROMstudents

JOINscorescONs.s_id=sc.s_id

GROUPBYs.s_id,s.s_name,s.class_id

)

SELECTs_id,s_name,class_id,total_score

FROM(

SELECT*,DENSE_RANK()OVER(PARTITIONBYclass_idORDERBYtotal_scoreDESC)ASrk

FROMstu_total

)t

WHERErk<=3;慢查询原因分析：①score表未针对c_id和score字段创建索引，查询时对score表执行全表扫描；②排序字段score无索引，查询时触发文件排序（Usingfilesort），增加CPU开销。优化方案：为score表创建联合索引idx_cid_score(c_id,score)，该索引满足c_id的等值查询条件，同时score字段在联合索引的第二位，排序时可以直接使用索引的有序性，避免文件排序，且索引包含查询需要的s_id、c_id、score字段，实现覆盖索引，无需回表。索引创建语句：

CREATEINDEXidx_cid_scoreONscore(c_id,score);优化后的SQL语句无需调整语法，创建索引后优化器会自动选择该索引执行，查询性能可提升10~100倍。第五部分案例分析题（1）表结构设计：①用户表

CREATETABLE`user`(

user_idBIGINTPRIMARYKEYAUTO_INCREMENTCOMMENT'用户ID',

user_nameVARCHAR(30)NOTNULLCOMMENT'用户名',

phoneCHAR(11)UNIQUENOTNULLCOMMENT'手机号',

create_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'注册时间'

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;②商品表

CREATETABLEproduct(

product_idBIGINTPRIMARYKEYAUTO_INCREMENTCOMMENT'商品ID',

product_nameVARCHAR(100)NOTNULLCOMMENT'商品名称',

priceDECIMAL(10,2)NOTNULLCOMMENT'商品价格',

stockINTNOTNULLDEFAULT0COMMENT'库存',

create_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间'

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;③订单表

CREATETABLEorder_info(

order_idBIGINTPRIMARYKEYAUTO_INCREMENTCOMMENT'订单ID',

order_snCHAR(32)UNIQUENOTNULLCOMMENT'订单编号',

user_idBIGINTNOTNULLCOMMENT'用户ID',

total_amountDECIMAL(10,2)NOTNULLCOMMENT'订单总金额',

order_statusTINYINTNOTNULLDEFAULT0COMMENT'订单状态：0待支付1已支付2已发货3已完成4已取消',

create_timeDATETIMEDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',

update_timeDATETIMEDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',

INDEXidx_user_id(user_id),

INDEXidx_order_sn(order_sn),

INDEXidx_create_time(create_time)

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;④订单明细表

CREATETABLEorder_item(

item_idBIGINTPRIMARYKEYAUTO_INCREMENTCOMMENT'明细ID',

order_idBIGINTNOTNULLCOMMENT'订单ID',

product_idBIGINTNOTNULLCOMMENT'商品ID',

buy_numINTNOTNULLCOMMENT'购买数量',

buy_priceDECIMAL(10,2)NOTNULLCOMMENT'购买单价',

INDEXidx_order_id(order_id),

INDEXidx_product_id(product_id)

)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;该设计符合第三范式，无冗余字段，非主属性完全依赖主键，无传递函数依赖。（2）索引设计方案：①用户表phone字段创建唯一索引，满足手机号登录的快速查询；②订单表user_id普通索引，满足用户查询自己的订单列表的需求；order_sn唯一索引，满足订单号查询订单的需求；create_time普通索引，满足按时间范围筛选订单的需求；③订单明细表order_id普通索引，满足查询订单下的商品明细需求；product_id普通索引，满足统计商品销量的需求；④商品表无需额外索引，主键索引已满足商品查询需求。（3）库存扣减优化方案：①采用乐观锁实现库存扣减，SQL语句为UPDATEproductSETstock=s