2025年MySQL数据库考试实操题试题及答案

2025年MySQL数据库考试实操题试题及答案一、基础操作类（共2题，总分20分）实操题1（12分）【实操要求】基于MySQL8.0LTS版本完成以下操作：1.创建名为ecommerce_2025的业务数据库，字符集设置为utf8mb4，排序规则为utf8mb4_0900_ai_ci，禁用数据库区分表名大小写。2.在该库下创建用户表`users`，字段要求如下：user_id：BIGINTUNSIGNED类型，主键、自增、非空username：VARCHAR(32)类型，非空、唯一索引phone：CHAR(11)类型，非空、唯一索引email：VARCHAR(64)类型，允许为空balance：DECIMAL(10,2)类型，默认值0.00，约束值≥0reg_time：DATETIME类型，默认值为当前时间status：TINYINT类型，默认值1，1代表正常，0代表封禁3.在该库下创建订单表`orders`，字段要求如下：order_id：BIGINTUNSIGNED类型，主键、非空（采用雪花算法生成，无需自增）user_id：BIGINTUNSIGNED类型，非空，外键关联`users.user_id`，约束为删除用户时若存在关联订单则禁止删除，更新用户ID时同步更新订单关联的user_idorder_amount：DECIMAL(10,2)类型，非空，约束值≥0.01pay_time：DATETIME类型，允许为空order_status：TINYINT类型，默认值0，0=待支付、1=已支付、2=已发货、3=已完成、4=已取消create_time：DATETIME类型，默认值为当前时间4.创建业务运维用户`dev_ops`，密码为`Dev@2025_MySQL`，仅允许从/24网段登录，授予该用户`ecommerce_2025`库所有表的SELECT、INSERT、UPDATE权限，禁止授予ALTER、DROP、DELETE等高风险权限，且该用户无法将自身权限授予其他用户。【参考答案】```sql-1.创建数据库CREATEDATABASEIFNOTEXISTSecommerce_2025CHARACTERSETutf8mb4COLLATEutf8mb4_0900_ai_ci;-注：禁用表名大小写需在f配置lower_case_table_names=1，初始化库时配置生效-2.创建users表USEecommerce_2025;CREATETABLEIFNOTEXISTSusers(user_idBIGINTUNSIGNEDNOTNULLAUTO_INCREMENTPRIMARYKEY,usernameVARCHAR(32)NOTNULL,phoneCHAR(11)NOTNULL,emailVARCHAR(64),balanceDECIMAL(10,2)DEFAULT0.00CHECK(balance>=0),reg_timeDATETIMEDEFAULTCURRENT_TIMESTAMP,statusTINYINTDEFAULT1,UNIQUEKEYidx_username(username),UNIQUEKEYidx_phone(phone))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;-3.创建orders表CREATETABLEIFNOTEXISTSorders(order_idBIGINTUNSIGNEDNOTNULLPRIMARYKEY,user_idBIGINTUNSIGNEDNOTNULL,order_amountDECIMAL(10,2)NOTNULLCHECK(order_amount>=0.01),pay_timeDATETIME,order_statusTINYINTDEFAULT0,create_timeDATETIMEDEFAULTCURRENT_TIMESTAMP,CONSTRAINTfk_orders_user_idFOREIGNKEY(user_id)REFERENCESusers(user_id)ONDELETERESTRICTONUPDATECASCADE)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;-4.创建运维用户并授权CREATEUSER'dev_ops'@'192.168.1.%'IDENTIFIEDBY'Dev@2025_MySQL';GRANTSELECT,INSERT,UPDATEONecommerce_2025.*TO'dev_ops'@'192.168.1.%'WITHGRANTOPTIONNO;FLUSHPRIVILEGES;```【注意事项】MySQL8.0默认启用CHECK约束，无需通过触发器实现字段值范围校验；外键约束`ONDELETERESTRICT`可避免误删有订单关联的用户，`ONUPDATECASCADE`保证用户ID变更时订单关联数据一致性；网段`192.168.1.%`匹配/24所有IP，`WITHGRANTOPTIONNO`禁止用户转授权限，符合最小权限原则。实操题2（8分）【实操要求】基于上述已创建的表结构完成数据操作：1.插入5条用户测试数据，用户名为user001~user005，手机号13800000005，邮箱为user00X@（X为1~5），其中user005状态设置为0，user001余额初始为1000.00，其余用户余额初始为500.00。2.插入8条订单测试数据，规则为：user001有3条订单（2条已支付、1条待支付，金额分别为299.99、199.99、99.99），user002有2条已完成订单（金额159.00、329.50），user003有1条已取消订单（金额89.90），user004有2条已发货订单（金额459.00、219.90），订单ID采用示例雪花ID：1798765432100000001~1798765432100000008。3.将所有创建时间超过30分钟的待支付订单状态批量更新为已取消。4.物理删除状态为0且无任何非取消状态订单的用户。【参考答案】```sql-1.插入用户数据INSERTINTOusers(username,phone,email,balance,status)VALUES('user001',,'user001@',1000.00,1),('user002',,'user002@',500.00,1),('user003',,'user003@',500.00,1),('user004',,'user004@',500.00,1),('user005',,'user005@',500.00,0);-2.插入订单数据INSERTINTOorders(order_id,user_id,order_amount,pay_time,order_status)VALUES(1798765432100000001,1,299.99,'2025-06-0110:05:00',1),(1798765432100000002,1,199.99,'2025-06-0110:10:00',1),(1798765432100000003,1,99.99,NULL,0),(1798765432100000004,2,159.00,'2025-06-0109:20:00',3),(1798765432100000005,2,329.50,'2025-06-0109:30:00',3),(1798765432100000006,3,89.90,NULL,4),(1798765432100000007,4,459.00,'2025-06-0111:00:00',2),(1798765432100000008,4,219.90,'2025-06-0111:05:00',2);-3.批量更新超时待支付订单UPDATEordersSETorder_status=4WHEREorder_status=0ANDcreate_time<DATE_SUB(NOW(),INTERVAL30MINUTE);-4.删除无效用户DELETEuFROMusersuLEFTJOINordersoONu.user_id=o.user_idANDo.order_status!=4WHEREu.status=0ANDo.order_idISNULL;```【注意事项】删除用户时采用左连接判断无有效订单，避免误删有未完成订单的封禁用户；批量更新时添加WHERE条件限制操作范围，避免全表更新引发的性能问题。二、进阶查询实操类（共2题，总分25分）实操题3（12分）【实操要求】基于上述表数据完成多表关联查询：1.查询所有已完成订单对应的用户名、手机号、订单ID、订单金额、支付时间，结果按支付时间倒序排列。2.统计每个用户的订单总数、已完成订单数、累计消费金额，无订单的用户统计值显示为0，结果按累计消费金额倒序排列。3.查询订单金额排名前3的订单所属用户的用户名、手机号，以及该用户所有订单的平均金额。4.查询同时存在已支付订单和已取消订单的用户ID和用户名。【参考答案】```sql-1.已完成订单关联查询SELECTu.username,u.phone,o.order_id,o.order_amount,o.pay_timeFROMusersuINNERJOINordersoONu.user_id=o.user_idWHEREo.order_status=3ORDERBYo.pay_timeDESC;-2.用户订单统计SELECTu.user_id,u.username,COUNT(o.order_id)ASorder_total,SUM(CASEWHENo.order_status=3THEN1ELSE0END)ASfinish_order_cnt,SUM(CASEWHENo.order_status=3THENo.order_amountELSE0END)AStotal_consumeFROMusersuLEFTJOINordersoONu.user_id=o.user_idGROUPBYu.user_id,u.usernameORDERBYtotal_consumeDESC;-3.Top3订单所属用户平均消费查询-写法1：窗口函数（推荐，性能更优）WITHorder_rankAS(SELECTorder_id,user_id,order_amount,RANK()OVER(ORDERBYorder_amountDESC)ASrkFROMorders)SELECTu.username,u.phone,AVG(o.order_amount)ASavg_order_amountFROMusersuINNERJOINorder_rankrONu.user_id=r.user_idANDr.rk<=3INNERJOINordersoONu.user_id=o.user_idGROUPBYu.user_id,u.username,u.phone;-写法2：传统子查询SELECTu.username,u.phone,AVG(o.order_amount)ASavg_order_amountFROMusersuINNERJOINordersoONu.user_id=o.user_idWHEREu.user_idIN(SELECTDISTINCTuser_idFROMordersWHEREorder_amountIN(SELECTDISTINCTorder_amountFROMordersORDERBYorder_amountDESCLIMIT3))GROUPBYu.user_id,u.username,u.phone;-4.同时存在已支付和已取消订单的用户SELECTu.user_id,u.usernameFROMusersuINNERJOINordersoONu.user_id=o.user_idGROUPBYu.user_id,u.usernameHAVINGSUM(CASEWHENo.order_status=1THEN1ELSE0END)>=1ANDSUM(CASEWHENo.order_status=4THEN1ELSE0END)>=1;```【注意事项】MySQL8.0默认开启`ONLY_FULL_GROUP_BY`模式，分组字段需包含所有非聚合列，避免出现语法错误；窗口函数`RANK()`可处理金额相同的并列排名场景，比LIMIT子查询适用性更强。实操题4（13分）【实操要求】使用窗口函数完成以下统计需求：1.为每个用户的订单按创建时间升序生成序号，展示用户ID、订单ID、创建时间、订单序号。2.统计每个订单金额占所属用户总消费金额的百分比，保留2位小数。3.查询每个用户消费金额最高的前2条订单记录。4.按支付日期维度统计每日销售额，以及截至当日的累计销售额。【参考答案】```sql-1.用户订单序号生成SELECTuser_id,order_id,create_time,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYcreate_timeASC)ASorder_seqFROMorders;-2.订单金额占比统计SELECTorder_id,user_id,order_amount,ROUND(order_amount*100/SUM(order_amount)OVER(PARTITIONBYuser_id),2)ASamount_ratioFROMordersWHEREorder_statusIN(1,3);-3.每个用户Top2高金额订单WITHuser_order_rankAS(SELECTuser_id,order_id,order_amount,ROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYorder_amountDESC)ASrkFROMorders)SELECT*FROMuser_order_rankWHERErk<=2;-4.每日累计销售额统计WITHdaily_salesAS(SELECTDATE(pay_time)ASpay_date,SUM(order_amount)ASday_amountFROMordersWHEREpay_timeISNOTNULLGROUPBYDATE(pay_time))SELECTpay_date,day_amount,SUM(day_amount)OVER(ORDERBYpay_dateASCROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AScumulative_amountFROMdaily_salesORDERBYpay_dateASC;```【注意事项】`PARTITIONBY`关键字指定窗口分组维度，`ROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW`指定累计计算范围为从首行到当前行，是累计统计的标准写法。三、事务与锁实操类（共2题，总分20分）实操题5（12分）【实操要求】实现用户支付的原子操作：用户user001支付金额为99.99元的待支付订单（order_id=1798765432100000003），需同时完成以下操作，任意一步失败则全部回滚：1.扣减user001的账户余额99.99元，确保扣减后余额不小于0。2.将对应待支付订单的状态更新为已支付，pay_time设置为当前时间。3.写入支付日志表`pay_log`，表结构为：pay_idBIGINTUNSIGNED主键自增，order_idBIGINTUNSIGNED非空，pay_amountDECIMAL(10,2)非空，pay_timeDATETIME默认当前时间。4.设置当前会话事务隔离级别为可重复读，避免脏读、不可重复读问题，并发场景下禁止出现余额超扣。【参考答案】```sql-先创建支付日志表CREATETABLEIFNOTEXISTSpay_log(pay_idBIGINTUNSIGNEDNOTNULLAUTO_INCREMENTPRIMARYKEY,order_idBIGINTUNSIGNEDNOTNULL,pay_amountDECIMAL(10,2)NOTNULL,pay_timeDATETIMEDEFAULTCURRENT_TIMESTAMP)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;-设置事务隔离级别SETSESSIONTRANSACTIONISOLATIONLEVELREPEATABLEREAD;-开启事务STARTTRANSACTION;-加行锁查询用户余额，避免并发更新SELECTbalanceFROMusersWHEREuser_id=1FORUPDATE;-校验余额充足，不足则回滚-若余额<99.99则执行ROLLBACK;UPDATEusersSETbalance=balance-99.99WHEREuser_id=1;-更新订单状态UPDATEordersSETorder_status=1,pay_time=NOW()WHEREorder_id=1798765432100000003ANDorder_status=0;-写入支付日志INSERTINTOpay_log(order_id,pay_amount)VALUES(1798765432100000003,99.99);-提交事务COMMIT;```【注意事项】`FORUPDATE`语句为查询行添加排他锁，事务提交前其他事务无法修改该行数据，避免并发场景下的余额超扣问题；更新订单状态时添加`order_status=0`的条件，避免重复支付问题。实操题6（8分）【实操要求】业务反馈订单更新操作提示锁等待超时，完成以下排查操作：1.查询当前实例中存在的行锁等待关系。2.查询持有锁的事务ID、执行SQL、客户端IP、线程ID。3.手动终止持有锁的空闲事务，解决锁等待问题。【参考答案】```sql-1.查询行锁等待SELECT*FROMperformance_schema.data_lock_waits;-2.查询持有锁的事务信息SELECTt.trx_id,t.trx_state,t.trx_mysql_thread_id,t.trx_query,c.host,c.ipFROMinformation_schema.innodb_trxtJOINinformation_cesslistcONt.trx_mysql_thread_id=c.idWHEREt.trx_idIN(SELECTblocking_engine_transaction_idFROMperformance_schema.data_lock_waits);-3.终止持有锁的事务，替换为实际查询到的线程IDKILL1234;```【注意事项】MySQL8.0的`performance_schema.data_lock_waits`表可直观展示锁等待的阻塞关系，比`SHOWENGINEINNODBSTATUS`排查效率更高；禁止kill系统线程，仅终止业务侧产生的空闲长事务。四、性能优化实操类（共2题，总分20分）实操题7（12分）【实操要求】业务侧反馈如下查询响应时长超过5秒，完成优化：```sqlSELECTu.username,o.order_id,o.order_amountFROMusersuJOINordersoONu.user_id=o.user_idWHEREo.pay_timeBETWEEN'2025-01-01'AND'2025-06-30'ANDo.order_status=3ANDu.phoneLIKE'138%'ORDERBYo.order_amountDESCLIMIT100;```1.分析慢查询原因，输出执行计划分析结果。2.创建合适的索引优化查询，避免回表和文件排序。3.验证索引生效，输出优化后的执行计划核心指标。【参考答案】1.执行计划分析：执行`EXPLAIN`语句后可见，`orders`表的type列为`ALL`（全表扫描），Extra列存在`Usingwhere;Usingfilesort`，无有效索引命中，且排序阶段触发文件排序，是性能瓶颈核心原因。2.索引创建：```sql-orders表创建联合覆盖索引，符合最左匹配原则，覆盖过滤、排序、关联字段CREATEINDEXidx_order_status_pay_amountONorders(order_status,pay_time,order_amount,user_id);-users表user_id为主键，phone前缀匹配无需额外建索引，若用户量超过100万可创建前缀索引CREATEINDEXidx_phone_prefixONusers(phone(3));```3.验证：优化后执行`EXPLAIN`，`orders`表type列为`range`，key列为`idx_order_status_pay_amount`，Extra列无`Usingfilesort`和`UsingMRR`，查询响应时长可降至100ms以内。【注意事项】联合索引顺序遵循等值匹配字段在前、范围匹配字段居中、排序/关联字段在后的原则，可避免回表和文件排序；前缀索引`phone(3)`仅存储手机号前3位，索引体积更小，适合前缀匹配场景。实操题8（8分）【实操要求】完成慢查询日志配置与分析：1.临时开启当前实例慢查询日志，设置慢查询阈值为1秒，日志路径为`/var/log/mysql/slow.log`。2.永久配置上述参数，MySQL重启后依然生效。3.用mysqldumpslow工具统计出现次数最多的前10条慢SQL。【参考答案】1.临时配置：```sqlSETGLOBALslow_query_log='ON';SETGLOBALlong_query_time=1;SETGLOBALslow_query_log_file='/var/log/mysql/slow.log';```2.永久配置：修改f（Linux）或my.ini（Windows）的`[mysqld]`段，添加如下内容后重启MySQL：```inislow_query_log=ONlong_query_time=1slow_query_log_file=/var/log/mysql/slow.log```3.慢日志统计命令：```bashmysqldumpslow-sc-t10/var/log/mysql/slow.log```【注意事项】`long_query_time`支持小数配置，如设置为0.5则捕获执行时长超过500ms的SQL；`mysqldumpslow`参数`-sc`代表按出现次数排序，`-t10`代表取前10条。五、高可用与运维实操类（共2题，总分15分）实操题9（8分）【实操要求】两台MySQL8.0服务器，主库IP0，从库IP1，配置基于GTID的主从复制：1.主库配置。2.从库配置。3.启动复制并验证同步状态正常。【参考答案】1.主库配置：修改主库f后重启：```ini[mysqld]server-id=1gtid_mode=ONenforce_gtid_consistency=ONlog_bin=mysql-binbinlog_format=ROWexpire_logs_days=7```主库创建复制用户：```sqlCREATEUSER'repl'@'1'IDENTIFIEDBY'Repl@2025_Op';GRANTREPLICATIONSLAVEON*.*TO'repl'@'1';FLUSHPRIVILEGES;```2.从库配置：修改从库f后重启：```ini[mysqld]server-id=2gtid_mode=ONenforce_gtid_consistency=ONrelay_log=relay-binread_only=ONsuper_read_only=ON```