2025年程序员sql面试题库及答案

2025年程序员sql面试题库及答案请描述SELECT语句中各子句的执行顺序，并说明WHERE与HAVING的区别。SELECT语句的实际执行顺序与书写顺序不同，核心顺序为：FROM→ON（处理JOIN条件）→JOIN（合并表）→WHERE（过滤行）→GROUPBY（分组）→WITHCUBE/ROLLUP（分组扩展）→HAVING（过滤分组）→SELECT（选择列）→DISTINCT（去重）→ORDERBY（排序）→LIMIT（限制行数）。WHERE与HAVING的本质区别在于作用阶段：WHERE在分组前过滤原始数据行，不能引用聚合函数；HAVING在分组后过滤分组结果，可以引用聚合函数。例如统计各部门平均薪资时，WHERE用于筛选特定部门（如部门ID=10），HAVING用于筛选平均薪资高于10000的部门（如AVG(salary)>10000）。索引失效的常见场景有哪些？如何避免？索引失效的典型场景包括：①对索引列使用函数或表达式（如WHEREDATE(create_time)=’2024-01-01’）；②索引列存在隐式类型转换（如VARCHAR字段与数字直接比较）；③LIKE查询以通配符开头（如WHEREnameLIKE‘%张%’）；④OR条件中部分列未建立索引（如WHEREid=1ORname=’张三’且仅id有索引）；⑤联合索引未遵循最左匹配原则（如索引为(col1,col2)，但查询仅用col2）；⑥数据分布极端（如性别字段仅有两个值，索引效率低）。避免方法：①将函数/表达式移到等号另一侧（如WHEREcreate_time>=’2024-01-01’ANDcreate_time<’2024-01-02’）；②确保查询条件与索引列类型一致；③左模糊查询改用全文索引（如MySQL的FULLTEXT）；④OR条件转换为UNION（如WHEREid=1UNIONWHEREname=’张三’）；⑤联合索引按高频查询顺序设计（如常用col1+col2查询则索引为(col1,col2)）；⑥对低区分度字段避免单独索引，可组合其他列建立复合索引。简述事务的ACID特性，并说明MySQL中InnoDB如何实现原子性与持久性。ACID指原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。原子性保证事务要么全执行要么全回滚；一致性确保事务前后数据符合业务规则；隔离性控制多事务间的可见性；持久性保证提交的事务不丢失。InnoDB通过undolog实现原子性：事务执行时记录每行修改前的旧值，若事务回滚则利用undolog恢复数据。持久性通过redolog实现：事务提交时，先将redolog写入磁盘（WAL预写日志机制），再更新内存中的数据页。即使数据库崩溃，重启后可通过redolog重新执行已提交但未刷盘的事务，保证数据不丢失。使用窗口函数计算每个部门中薪资排名前3的员工，要求显示部门ID、员工姓名、薪资、部门内排名。假设表结构为employees（emp_idINT,dept_idINT,nameVARCHAR(20),salaryDECIMAL(10,2)），SQL示例如下：```sqlSELECTdept_id,name,salary,DENSE_RANK()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)ASrankFROMemployeesQUALIFYrank<=3;-部分数据库（如PostgreSQL）支持QUALIFY，MySQL需嵌套查询-MySQL替代方案：SELECTdept_id,name,salary,rankFROM(SELECTdept_id,name,salary,DENSE_RANK()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)ASrankFROMemployees)AStWHERErank<=3;```注：RANK()会跳过重复排名（如两人并列第1则下一人为第3），DENSE_RANK()则连续（并列第1后为第2），需根据需求选择。INNERJOIN、LEFTJOIN、RIGHTJOIN、FULLOUTERJOIN的区别是什么？当关联表存在重复键时，结果集数量会如何变化？INNERJOIN返回两表中关联键匹配的行；LEFTJOIN返回左表所有行，右表无匹配时补NULL；RIGHTJOIN与LEFTJOIN相反；FULLOUTERJOIN返回两表所有行，无匹配时补NULL（MySQL不直接支持，需用LEFTJOINUNIONRIGHTJOIN实现）。若两表关联键存在重复值（如左表有2行dept_id=10，右表有3行dept_id=10），则INNERJOIN会产生2×3=6行（笛卡尔积），LEFT/RIGHT/FULLJOIN同理。因此设计表结构时应确保关联键（如外键）的唯一性，避免因重复键导致结果集膨胀。普通子查询与CTE（公共表表达式）的区别是什么？在什么场景下优先使用CTE？普通子查询是嵌套在主查询中的SELECT语句，需重复编写或通过临时表存储；CTE通过WITH子句定义，可在后续查询中多次引用。主要区别：①可读性：CTE命名后更易理解复杂查询；②递归支持：CTE可定义递归查询（如查询层级部门关系）；③作用域：CTE仅在当前查询有效，临时表需显式创建/删除；④性能：部分数据库（如PostgreSQL）会优化CTE为子查询，MySQL8.0+支持物化CTE（可重用结果）。优先使用CTE的场景：①多层嵌套查询（如三级子查询）；②递归查询（如组织架构树）；③需要多次引用同一数据集（如同时计算总和与平均值）；④提升代码可维护性（通过命名CTE明确业务含义）。执行EXPLAIN后，哪些关键字段需要重点关注？如何根据这些字段优化查询？EXPLAIN输出的关键字段及优化策略：id：查询的执行顺序，id相同则从上到下执行，id越大优先级越高。若存在多个不同id，可能需调整查询结构减少嵌套。type：访问类型，从优到劣为system>const>eq_ref>ref>range>index>ALL。若type为ALL（全表扫描），需为WHERE条件列添加索引；若为index（索引全扫描），检查是否使用覆盖索引。key：实际使用的索引，NULL表示未使用索引。若key与预期不符，可能是索引未命中（如类型不匹配）。rows：估计扫描的行数，数值越大性能越差。通过优化索引或缩小查询范围（如添加时间条件）减少rows。Extra：额外信息，常见问题及处理：Usingfilesort：需为ORDERBY列添加索引（按排序顺序）。Usingtemporary：GROUPBY或ORDERBY涉及多列，需调整索引为(分组列,排序列)。Usingwhere：未使用索引过滤，需检查WHERE条件是否可优化。Usingindex：使用覆盖索引，无需回表，为理想状态。设计一个订单系统，包含用户表、商品表、订单表、订单详情表，要求说明各表字段及关联关系。表结构设计如下（以MySQL为例）：用户表（users）：user_id（主键，INT自增）、username（VARCHAR(50)，唯一）、mobile（VARCHAR(11)，唯一）、create_time（TIMESTAMP，默认当前时间）。商品表（products）：product_id（主键，INT自增）、product_name（VARCHAR(100)）、price（DECIMAL(10,2)）、stock（INT，库存）、category（VARCHAR(20)）。订单表（orders）：order_id（主键，BIGINT自增）、user_id（外键，INT，关联users.user_id）、total_amount（DECIMAL(12,2)，总金额）、status（TINYINT，状态：1未支付/2已支付/3已发货）、create_time（TIMESTAMP）、pay_time（TIMESTAMP，可NULL）。订单详情表（order_items）：item_id（主键，BIGINT自增）、order_id（外键，BIGINT，关联orders.order_id）、product_id（外键，INT，关联duct_id）、quantity（INT，购买数量）、unit_price（DECIMAL(10,2)，下单时单价）。关联关系：订单表通过user_id关联用户表（一对多，一个用户多个订单）；订单详情表通过order_id关联订单表（一对多，一个订单多个商品），通过product_id关联商品表（多对一，多个订单详情对应一个商品）。如何用SQL实现行列转换？以学提供绩表（学生ID、科目、成绩）为例，转换为（学生ID、语文、数学、英语）的格式。假设成绩表结构为scores（student_idINT,subjectVARCHAR(10),scoreDECIMAL(5,2)），目标是将每个学生的各科成绩转为列。通用解法（支持MySQL、PostgreSQL）使用CASEWHEN+聚合函数：```sqlSELECTstudent_id,MAX(CASEWHENsubject='语文'THENscoreEND)AS语文,MAX(CASEWHENsubject='数学'THENscoreEND)AS数学,MAX(CASEWHENsubject='英语'THENscoreEND)AS英语FROMscoresGROUPBYstudent_id;```若数据库支持PIVOT（如SQLServer、Oracle），可简化为：```sqlSELECTstudent_id,语文,数学,英语FROMscoresPIVOT(MAX(score)FORsubjectIN(语文,数学,英语))ASpvt;```注：需确保科目列（subject）的值包含所有目标列（如“语文”“数学”“英语”），否则缺失科目会显示NULL。分布式数据库中，分库分表后如何保证SQL查询的效率？需要注意哪些问题？分库分表后优化效率的关键措施：分片键选择：优先使用高频查询的过滤条件（如订单表按user_id分片），确保查询可定位到单分片（如查询某用户的订单）。避免跨库Join：将关联表按相同分片键分片（如订单表与订单详情表均按order_id分片），或通过应用层组装结果（先查订单再查详情）。全局二级索引：对需跨分片查询的字段（如商品表的category），建立全局索引（单独存储索引数据，指向分片位置），但需权衡写入性能。分页查询限制：跨分片的LIMITN查询需汇总所有分片的前N条，可能导致性能下降，可通过记录上次查询的最大ID实现“下一页”（如WHEREid>last_idLIMITN）。数据倾斜处理：避免分片键分布不均（如按时间分片导致近期数据集中），可通过哈希取模（如user_id%10）或添加随机后缀（如order_id_随机数）分散数据。需注意的问题：跨分片事务（需使用分布式事务如Seata）、跨分片统计（如SUM/COUNT需聚合所有分片结果）、全局唯一ID（需雪花算法或UUID保证）、扩容复杂度（需数据迁移与索引重建）。如何处理慢查询？请描述完整的排查与优化流程。慢查询排查优化流程：1.开启慢查询日志：设置long_query_time（如1秒），记录执行时间超过阈值的SQL。2.分析慢查询SQL：通过EXPLAIN查看执行计划，重点关注type（是否全表扫描）、key（是否用索引）、Extra（是否有Usingfilesort或Usingtemporary）。3.定位性能瓶颈：全表扫描（type=ALL）：检查WHERE条件是否无索引，添加索引或优化查询条件。索引未命中：检查是否因函数/表达式、类型转换、左模糊导致索引失效，调整查询写法。文件排序（Usingfilesort）：为ORDERBY列添加索引（顺序需与排序一致）。临时表（Usingtemporary）：优化GROUPBY或ORDERBY，使用覆盖索引避免临时表。4.优化