2025年mysql面试题sql及答案

2025年mysql面试题sql及答案一、基础SQL操作类真题1.核心业务统计题题干：现有3张核心业务表，结构如下：用户表`user`：`user_idINTPRIMARYKEYAUTO_INCREMENTCOMMENT'用户ID'`，`user_nameVARCHAR(32)NOTNULLCOMMENT'用户名'`，`register_timeDATETIMENOTNULLCOMMENT'注册时间'`，`cityVARCHAR(32)COMMENT'所在城市'`订单表`order_info`：`order_idBIGINTPRIMARYKEYAUTO_INCREMENTCOMMENT'订单ID'`，`user_idINTNOTNULLCOMMENT'下单用户ID'`，`order_amountDECIMAL(10,2)NOTNULLCOMMENT'订单实付金额'`，`order_timeDATETIMENOTNULLCOMMENT'下单时间'`，`order_statusTINYINTNOTNULLDEFAULT0COMMENT'订单状态：0待支付1已支付2已取消3已完成'`订单商品关联表`order_item`：`item_idBIGINTPRIMARYKEYAUTO_INCREMENTCOMMENT'明细ID'`，`order_idBIGINTNOTNULLCOMMENT'关联订单ID'`，`goods_idINTNOTNULLCOMMENT'商品ID'`，`goods_numINTNOTNULLDEFAULT1COMMENT'购买数量'`，`goods_priceDECIMAL(10,2)NOTNULLCOMMENT'商品单价'`问题1：统计2024年每个城市的新增用户数、已支付订单的累计下单用户数、已支付订单总金额，过滤掉总金额小于1000元的城市，输出字段为`city`、`new_user_cnt`、`pay_user_cnt`、`total_pay_amount`。参考答案：```sqlSELECTu.city,COUNT(DISTINCTCASEWHENu.register_timeBETWEEN'2024-01-0100:00:00'AND'2024-12-3123:59:59'THENu.user_idEND)ASnew_user_cnt,COUNT(DISTINCTo.user_id)ASpay_user_cnt,SUM(o.order_amount)AStotal_pay_amountFROMuseruLEFTJOINorder_infooONu.user_id=o.user_idANDo.order_status=1ANDo.order_timeBETWEEN'2024-01-0100:00:00'AND'2024-12-3123:59:59'GROUPBYu.cityHAVINGtotal_pay_amount>=1000;```逻辑说明：将订单过滤条件放在`LEFTJOIN`的`ON`子句而非`WHERE`子句，避免过滤掉2024年有新增用户但无下单的城市，保证新增用户数统计准确；用`COUNT(DISTINCT)`避免同一用户多次下单导致的计数重复；`HAVING`子句完成最终的金额过滤。问题2：查询2024年连续3天及以上产生已支付订单的用户ID和用户名，输出`user_id`、`user_name`。参考答案：```sqlWITHt1AS(SELECTDISTINCTuser_id,DATE(order_time)ASorder_dateFROMorder_infoWHEREorder_status=1ANDorder_timeBETWEEN'2024-01-01'AND'2024-12-31'),t2AS(SELECTuser_id,order_date,DATE_SUB(order_date,INTERVALROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYorder_date)DAY)ASgroup_flagFROMt1)SELECTDISTINCTt2.user_id,u.user_nameFROMt2JOINuseruONt2.user_id=u.user_idGROUPBYt2.user_id,t2.group_flagHAVINGCOUNT(*)>=3;```逻辑说明：第一步对用户的下单日期去重，避免单日多单导致的计数误差；第二步用日期减去该用户下单日期的排序位次，得到分组标识`group_flag`，连续日期的`group_flag`值完全相同；第三步按用户和`group_flag`分组，组内行数≥3即为符合要求的连续下单用户。二、索引优化类真题2.慢查询优化实操题题干：现有1200万行的`order_info`表，结构同第一题，现有慢查询SQL如下：```sqlSELECTuser_id,order_amount,order_timeFROMorder_infoWHEREorder_time>='2024-06-01'ANDorder_status=1ORDERBYorder_timeDESCLIMIT20000,20;```问题1：该SQL执行缓慢的可能原因是什么？如何优化？写出优化后的索引定义和改写后的SQL。参考答案：执行缓慢原因：①无合适的联合索引，查询时要么走全表扫描，要么走`order_time`单列索引后需要回表查询`order_status`、`user_id`、`order_amount`字段，IO开销极高；②`LIMIT20000,20`需要扫描20020行数据，大偏移量导致排序和扫描开销翻倍。优化方案：首先创建覆盖联合索引，遵循“等值匹配在前、范围匹配在后、覆盖查询字段”的原则：```sql-MySQL8.0.13及以上版本支持INCLUDE语法，减少联合索引长度CREATEINDEXidx_status_time_coverONorder_info(order_status,order_timeDESC)INCLUDE(user_id,order_amount);-低版本直接创建联合索引CREATEINDEXidx_status_time_coverONorder_info(order_status,order_timeDESC,user_id,order_amount);```索引合理性说明：`order_status`是等值查询条件放在最左，`order_time`是范围查询且需要排序，放在第二位，后续包含查询需要的业务字段，构成覆盖索引避免回表，同时索引中`order_time`是有序的，完全避免了`filesort`排序开销。针对大偏移量的SQL改写，采用游标分页方案：```sqlSELECTuser_id,order_amount,order_timeFROMorder_infoWHEREorder_status=1ANDorder_time<(SELECTorder_timeFROMorder_infoWHEREorder_status=1ORDERBYorder_timeDESCLIMIT20000,1)ORDERBYorder_timeDESCLIMIT20;```改写逻辑：通过子查询先定位到偏移量位置的`order_time`值，再基于索引的有序性直接扫描该值之后的20条数据，避免扫描前面的20000条无效数据，性能可提升10倍以上。问题2：现有用户查询SQL：`SELECT*FROMuserWHEREcity='上海'ANDregister_timeBETWEEN'2024-01-01'AND'2024-12-31'ANDuser_nameLIKE'李%';`请问最优联合索引的字段顺序是什么？为什么？参考答案：最优联合索引顺序为`idx_city_name_register(city,user_name,register_time)`。原理：联合索引遵循最左前缀匹配原则，等值匹配条件优先放在最左侧，`city`是等值查询放在第一位；`user_name`的模糊查询是前缀匹配，属于可利用索引的范围匹配，放在第二位；`register_time`是范围查询放在最后，三个字段均可命中索引；如果将`register_time`放在`user_name`前面，范围查询之后的索引字段会失效，`user_name`无法命中索引。三、事务与一致性类真题3.库存扣减场景题题干：电商库存扣减场景，库存表`stock`结构为：`goods_idINTPRIMARYKEYCOMMENT'商品ID'`，`stock_numINTNOTNULLDEFAULT0COMMENT'可用库存'`，`versionINTNOTNULLDEFAULT0COMMENT'乐观锁版本号'`，`update_timeDATETIMENOTNULLCOMMENT'更新时间'`。问题1：现有扣减库存的SQL：`UPDATEstockSETstock_num=stock_num-1WHEREgoods_id=1001ANDstock_num>=1;`请问该SQL能否解决超卖问题？为什么？如果业务要求支持并发量1000+的扣减场景，有哪些优化方案？参考答案：该SQL可以解决超卖问题。原因：InnoDB引擎下，UPDATE语句会对满足条件的行加排他行锁，事务提交前锁不会释放；多个并发事务同时执行该SQL时，只有第一个拿到行锁的事务可以执行成功，后续事务拿到锁后会再次判断`stock_num>=1`的条件，若库存已为0则不会执行更新，完全避免超卖。高并发场景优化方案：①乐观锁方案：降低锁粒度，减少行锁等待时间，SQL改写为`UPDATEstockSETstock_num=stock_num-1,version=version+1WHEREgoods_id=1001ANDstock_num>=1ANDversion=[查询到的当前版本号];`若返回影响行数为0则说明库存已被修改，返回重试即可，适合并发量中等的场景；②库存分片：将单个商品的库存拆分为多个分片，比如`goods_id=1001`拆分为10个分片，每个分片存储10%的库存，扣减时随机选择分片，降低锁冲突概率，并发量可提升数倍；③缓存扣减：将库存放到Redis中，利用Redis的单线程原子性操作扣减库存，异步同步到MySQL，适合并发量极高、一致性要求为最终一致的场景。问题2：现有两个事务，事务隔离级别分别为RC（读已提交）和RR（可重复读），执行顺序如下：①初始数据：`order_info`表中`order_id=1`的`order_amount`值为100；②事务A执行`STARTTRANSACTION;UPDATEorder_infoSETorder_amount=200WHEREorder_id=1;`；③事务B执行`STARTTRANSACTION;SELECTorder_amountFROMorder_infoWHEREorder_id=1;`；④事务A执行`COMMIT;`；⑤事务B再次执行`SELECTorder_amountFROMorder_infoWHEREorder_id=1;`。请问RC和RR隔离级别下，事务B两次查询的结果分别是什么？解释原因。参考答案：RC隔离级别下：事务B第一次查询结果为100，第二次查询结果为200。原因：RC级别下每次查询都会生成新的ReadView，事务A提交之后，事务B的第二次查询的ReadView可以看到事务A的修改，因此读取到最新值。RR隔离级别下：事务B两次查询结果都是100。原因：RR级别下事务启动后第一次查询会生成全局的ReadView，整个事务生命周期内复用该ReadView，即使事务A提交，事务B的ReadView依然看不到事务A的修改，保证可重复读。底层基于MVCC（多版本并发控制）实现：每行数据都有隐藏的事务ID字段和`roll_pointer`指针，`roll_pointer`指向undolog中的历史版本，ReadView通过判断事务ID的可见性决定读取哪个版本的数据，RC和RR的核心差异就是ReadView的生成时机不同。四、锁机制类真题4.临键锁实操题题干：现有测试表`t1`结构为：`idINTPRIMARYKEY,aINT,bINT,INDEXidx_a(a);`表中已存在数据：`(1,1,1),(3,3,3),(5,5,5),(7,7,7),(9,9,9)`，事务隔离级别为RR。问题1：事务A执行`SELECT*FROMt1WHEREa=3FORUPDATE;`此时事务B执行`INSERTINTOt1VALUES(2,2,2);`会不会被阻塞？执行`INSERTINTOt1VALUES(4,4,4);`会不会被阻塞？解释原因。参考答案：两个插入语句都会被阻塞。原因：RR隔离级别下，InnoDB对于普通二级索引的等值查询会加临键锁（Next-KeyLock），临键锁是行锁+间隙锁的组合，锁范围是索引上的左开右闭区间，用于解决幻读问题。事务A查询`a=3`走`idx_a`二级索引，临键锁的范围是`(1,3]`和`(3,5)`，即a值在(1,5)区间的所有间隙都会被加锁，插入`a=2`和`a=4`都属于锁范围内的间隙插入，因此都会被阻塞。问题2：线上出现死锁，怎么排查？写出相关的SQL命令和排查步骤。参考答案：排查步骤：①查看最近的死锁日志：执行`SHOWENGINEINNODBSTATUS;`命令，输出内容中的`LATESTDETECTEDDEADLOCK`部分会记录死锁发生的事务、持有锁、等待锁、执行的SQL等核心信息；②查询当前正在执行的事务和持有锁的信息：```sql-查询正在运行的事务SELECT*FROMinformation_schema.INNODB_TRX;-查询当前持有锁的信息SELECT*FROMinformation_schema.INNODB_LOCKS;-查询锁等待关系SELECT*FROMinformation_schema.INNODB_LOCK_WAITS;```③分析死锁日志中的两个事务的执行SQL和加锁顺序，死锁的核心原因是两个事务加锁顺序相反，互相等待对方持有的锁，优化时统一加锁顺序即可解决。五、高阶语法与8.0新特性类真题5.窗口函数与递归CTE实操题问题1：用户行为表`user_behavior`结构为：`user_idINTCOMMENT'用户ID'`，`behavior_typeTINYINTCOMMENT'1浏览2加购3下单'`，`behavior_timeDATETIMECOMMENT'行为时间'`，`amountDECIMAL(10,2)COMMENT'下单金额'`，`PRIMARYKEY(user_id,behavior_type,behavior_time)`。要求用窗口函数实现以下统计：①每个用户的下单次数在所有用户中的排名；②每个用户每天的下单金额和近7天累计下单金额；③每个用户第一次下单的时间。写出对应的SQL。参考答案：```sql-1.每个用户下单次数排名WITHuser_order_cntAS(SELECTuser_id,COUNT(*)ASorder_cntFROMuser_behaviorWHEREbehavior_type=3GROUPBYuser_id)SELECTuser_id,order_cnt,RANK()OVER(ORDERBYorder_cntDESC)ASorder_rank,--排名相同则并列，跳过后续位次DENSE_RANK()OVER(ORDERBYorder_cntDESC)ASdense_order_rank--排名相同则并列，不跳位次FROMuser_order_cnt;-2.每个用户每天下单金额和近7天累计金额WITHuser_daily_payAS(SELECTuser_id,DATE(behavior_time)ASdt,SUM(amount)ASdaily_amountFROMuser_behaviorWHEREbehavior_type=3GROUPBYuser_id,DATE(behavior_time))SELECTuser_id,dt,daily_amount,SUM(daily_amount)OVER(PARTITIONBYuser_idORDERBYdtROWSBETWEEN6PRECEDINGANDCURRENTROW)ASlast_7d_amountFROMuser_daily_pay;-3.每个用户第一次下单时间SELECTDISTINCTuser_id,FIRST_VALUE(behavior_time)OVER(PARTITIONBYuser_idORDERBYbehavior_timeASC)ASfirst_order_timeFROMuser_behaviorWHEREbehavior_type=3;```问题2：现有树形部门表`dept`结构为`dept_idINTPRIMARYKEY,parent_idINTCOMMENT'上级部门ID，顶级部门parent_id=0',dept_nameVARCHAR(32)COMMENT'部门名称'`。用MySQL8.0的递归CTE查询ID为1的部门的所有子部门，以及部门的完整路径（比如“总公司-研发部-后端组”）。参考答案：```sqlWITHRECURSIVEdept_treeAS(-递归起始节点：顶级部门SELECTdept_id,parent_id,dept_name,CAST(dept_nameASCHAR(200))ASdept_pathFROMdeptWHEREdept_id=1UNIONALL-递归迭代：关联子部门SELECTd.dept_id,d.parent_id,d.dept_name,CONCAT(dt.dept_path,'-',d.dept_name)ASdept_pathFROMdeptdJOINdept_treedtONd.parent_id=dt.dept_id)SELECT*FROMdept_tree;```递归CTE优势：相比派生表和嵌套查询，递归CTE语法更简洁，可读性更高，支持无限层级的树形结构查询，执行效率也高于传统的自定义函数实现的树形查询，是MySQL8.0最常用的新特性之一。六、大表优化与性能调优类真题6.历史数据删除场景题问题1：现有2.3亿行的历史订单表`order_info`，需要删除2020年1月1日之前的所有历史数据，约占总数据量的60%，直接执行`DELETEFROMorder_infoWHEREorder_time<'2020-01-01'`会有什么问题？写出优化方案和对应的SQL。参考答案：直接执行DELETE的问题：①DELETE是逐行删除，会产生大量的undolog和redolog，导致数据库IO飙升，影响线上业务；②删除操作会加表级意向锁和行锁，导致该表的写入操作全部阻塞，线上订单业务不可用；③删除完成后不会释放磁盘空间，只会产生数据空洞，需要执行`OPTIMIZETABLE`才能回收空间，OPTIMIZE期间也会锁表。优化方案：方案1：分批次小批量删除，每次删除1000条，分批提交，避免长事务和锁表：```sqlSELECTMIN(order_id)ASmin_id,MAX(order_id)ASmax_idFROMorder_infoWHEREorder_time<'2020-01-01';-循环执行，每次删除1000条，直到全部删除完成WHILEmin_id<=max_idDODELETEFROMorder_infoWHEREorder_idBETWEENmin_idANDmin_id+999ANDorder_time<'2020-01-01';SETmin_id=min_id+1000;-每次删除后休眠100ms，降低IO压力SELECTSLEEP(0.1);ENDWHILE;```方案2：分区表方案，如果该表已经按`order_time`按年/月分区，直接删除分区即可，毫秒级完成：```sqlALTERTABLE