SQL面试十大高频问题实战复盘：从JOIN到索引优化的完整指南

我帮团队整理过好几轮面试题库也陪着不少候选人做过复盘。一个特别明显的现象是简历上敢写“精通SQL”的人很多真到了面试现场能把一道关联查询讲清楚、能把一条慢SQL分析到执行计划层面的人少之又少。这篇文章我想把过去几年在面试里反复出现的十大 SQL 问题整理成一份实战复盘每个问题都会讲清楚面试官在考什么、怎么回答才算答到点上以及真正的生产环境里这些题是怎么演变成事故的。适合准备数据开发、后端开发、数据分析岗面试的朋友也想给正在出题的面试官一点参考。1. 面试官到底在考什么先想明白题目的考察意图1.1 为什么SQL题最能拉开差距我的经验是SQL题几乎每个人都觉得自己会但十个人里能答完整、答出层次的可能只有两三个。原因很简单SQL的语法手册谁都能翻真正拉开差距的是你有没有在真实数据上跑过、有没有被慢查询教育过、有没有因为一个NULL判断把线上数据算错过。面试官的考察逻辑通常集中在四层第一层基础概念是否扎实比如JOIN、GROUP BY、NULL这些最基础的东西有没有理解偏差第二层是否理解数据在数据库里是怎么流转的比如SQL执行顺序、索引结构、执行计划第三层有没有生产环境的踩坑直觉比如深分页、隐式类型转换、重复数据这些日常事故高发点第四层面对一个没见过的题能不能把它拆解成过滤、分组、排序、取数这些基本动作。四层能力不是靠背题能练出来的。所以下文我不会只扔答案而是把每个题目背后的推导逻辑、常见误区、以及回答时的加分点都拆开讲。你把这十个问题吃透应付大部分SQL面试是够用的更重要的是能建立一套稳定的思考框架。1.2 十大问题总览与考察维度先给一张总览表把十个问题按考察方向列清楚后面再逐一展开。序号面试问题主要考察点一句话考点1JOIN 一定比子查询快吗执行计划、优化器别把二选一当结论2找出连续N天登录的用户窗口函数、去重日期减序号生成连续区间3WHERE 为什么不能写聚合条件SQL执行顺序行过滤和组过滤要分清4窗口函数与 GROUP BY 差在哪聚合模型折叠行 vs 保留行5SQL的逻辑执行顺序虚拟表推导别名为什么在WHERE里不可用6索引建了为什么还是慢索引结构、执行计划失效场景与覆盖索引7NULL 和空字符串到底有什么区别三值逻辑NOT IN 的经典陷阱8深分页为什么越来越慢分页查询优化offset 要先扫过再扔掉9每个部门工资前三怎么取窗口函数排名先问并列规则再写SQL10动态拼接SQL怎么防注入安全编码参数化与白名单校验这十个问题前三个更偏基础逻辑中间三个更偏性能优化最后两个偏边界和安全。实际面试里不一定按这个顺序问但你心里要有数每一道题都能往深了问。2. 关联、子查询与连续登录基础题里的三个分水岭2.1 题一JOIN 一定比子查询快吗这道题我几乎每次面试都会问因为能看到候选人有没有真正和优化器打过交道。很多人的第一反应是“JOIN快子查询慢”但只要我追问一句“在你的生产环境里JOIN改成子查询后反而更快你怎么解释”大多数人就答不上来了。先说结论JOIN和子查询没有天然的优劣关系它们是两种表达方式最终都可能被优化器改写为相似的执行计划。子查询分两种普通子查询和相关子查询。普通子查询比如WHERE id IN (SELECT id FROM b)很多数据库会把它改写成半连接 semijoin性能不一定差相关子查询比如WHERE amount (SELECT AVG(amount) FROM orders o2 WHERE o2.user_id o1.user_id)这种查询会针对外层表的每一行重复执行内层查询数据量一大确实容易慢。那JOIN就一定稳吗也不是。JOIN可能因为优化器选错了驱动表导致小表被大表带偏可能在关联列上没有索引引发嵌套循环全表扫可能因为多表关联后产生中间结果集膨胀甚至触发临时表和文件排序。我见过一个真实案例三张表做JOIN查询优化器选了一张过滤性很差的表当驱动表结果查询从几秒变成了几十秒最后靠手动调整关联顺序才救回来。所以这道题的正确回答路径应该是先承认“不一定”然后说清楚关键要看执行计划。用 EXPLAIN 观察每一张表的访问方式是 ALL、range 还是 ref/eq_ref扫描行数 rows 大概多少Extra 里有没有 Using filesort、Using temporary。数据量、索引情况、过滤条件选择性、是否需要去重排序这些因素都比“用JOIN还是子查询”更重要。还有一个小坑如果子查询里有 DISTINCT 或者业务上需要去重直接改写成 JOIN 可能会出现重复行反而要额外加 DISTINCT 处理。这类问题在面试里考的不是你能不能背出结论而是你有没有养成“先看数据、再看执行计划、再写SQL”的习惯。如果你能主动说出“我会先 EXPLAIN 看扫描行数再决定是否改写”这道题就已经得分了。2.2 题二找出连续N天登录的用户连续登录问题是数据分析岗的高频题后端岗也经常拿来考察窗口函数的掌握程度。题目描述通常很简单有一张登录记录表每个用户每天可能登录多次现在要找出连续登录了至少7天的用户。我第一次遇到这道题时第一反应是用自连接但连续7天意味着要自连接6次SQL写出来又长又难维护而且执行效率很差。后来换了窗口函数一句话就能解决核心问题。思路是这样的先把同一用户同一天的多次登录去重得到每个用户每一天只保留一条记录然后按用户分组按日期排序用 ROW_NUMBER 给每一天编一个序号关键一步把登录日期减去序号得到一个字段这个字段在连续日期段内是不变的一旦中断就会变化最后按用户和这个字段分组统计天数过滤出大于等于N的组。我贴一个可直接跑的示例以某数据库的语法为例WITH login_dedup AS ( SELECT DISTINCT user_id, DATE(login_time) AS login_date FROM user_login_log ), daily AS ( SELECT user_id, login_date, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY login_date) AS rn FROM login_dedup ) SELECT user_id, DATE_SUB(login_date, INTERVAL rn DAY) AS group_flag, COUNT(*) AS consecutive_days FROM daily GROUP BY user_id, group_flag HAVING COUNT(*) 7;为什么日期减序号能识别连续性想象一个用户连续登录三天1号、2号、3号对应序号1、2、3相减之后全是0号如果中间断了一天比如1号、3号、4号序号分别是1、2、3相减得到0号、1号、1号前两行连续段结束后面又开了新段。这就是整个算法的核心逻辑理解这一点比背SQL更值钱。这道题的另外一个坑是“去重”。如果登录记录表里同一天有多次登录你没加 DISTINCT连续天数的统计就会被同一日的多条记录撑大结果直接出错。面试时我会主动先说“第一步先去重”这个细节能看出你有没有处理脏数据的意识。如果面试官追问“数据库不支持窗口函数怎么办”你就回答可以用相关子查询或者多层自连接但要强调可读性和性能都很差这也是为什么现代SQL里窗口函数成为标配的原因。2.3 题三WHERE 后面为什么不能写聚合条件这道题看着简单但每次面试都有人掉坑。面试官会问我想筛选出订单数超过5个的用户为什么要用 HAVING 而不是在 WHERE 里写 COUNT(*)很多人单纯记结论“聚合条件要放HAVING”但答不出根本原因。这里的关键是SQL的逻辑执行顺序WHERE 在 GROUP BY 之前执行它处理的是“行”是从表里把符合条件的行挑出来GROUP BY 把这些行分成组然后才执行聚合计算。也就是说在 WHERE 执行的那个时刻COUNT(*) 的值根本还不存在自然不能作为过滤条件。举个例子订单表要统计每个用户的有效订单数只保留订单数大于5的用户。最自然的写法是这样SELECT user_id, COUNT(*) AS order_cnt FROM orders WHERE status PAID GROUP BY user_id HAVING COUNT(*) 5;这里 WHERE 先剔除掉非PAID状态的订单只让有效订单进入分组聚合结果再把订单数不足5个的用户过滤掉。两个过滤条件各司其职能提前过滤行的条件优先用 WHERE因为可以减少参与分组的数据量性能更好必须基于聚合结果才能判断的条件才用 HAVING。有些候选人会把 HAVING 当成万能过滤条件所有条件都往里塞这也是不对的。行级过滤放 HAVING 的代价是让很多根本不需要参与分组的行也进来一起聚合白白浪费计算。回答这道题时建议顺带提一下执行顺序并且给出一个“把条件分级处理”的思路面试官会觉得你是有意识在优化查询而不是只背语法。3. 窗口函数与Top N能拉开差距的两类考题3.1 题四窗口函数和 GROUP BY 都做聚合到底差在哪儿窗口函数是近些年SQL面试的必考内容而这道对比题问的是你有没有真正理解聚合模型而不是只会写SUM() OVER PARTITION BY。一句话说清楚关键区别GROUP BY 会折叠行窗口函数不会折叠行。GROUP BY 把多行聚合成一组结果里每组只输出一行明细数据没了窗口函数则是在保留每一行明细的同时给每一行附上一列聚合计算的结果。举个例子有一个订单明细表你想统计每个用户的总金额。用 GROUP BY 会输出每个用户一行看完统计结果就看不到具体订单了用窗口函数每一行订单都还在只是多了一列user_total既能看总数也能看明细。窗口函数的另一层关键特点是它的执行时机。标准SQL的逻辑执行顺序大致是 FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY窗口函数是在 SELECT 阶段计算的。所以你不能在 WHERE 里直接引用一个窗口函数的计算结果想过滤就得先包一层子查询。这个特性很多人踩过坑。日常业务里窗口函数最大的价值在于处理“组内排行”“累计值”“移动平均”“同环比”这类场景。比如算“每个用户在当月的累计消费”用SUM(amount) OVER(PARTITION BY user_id ORDER BY pay_date)就能拿到一个随时间递增的累计值这在 GROUP BY 的世界里要写自连接才能实现。回答这道题时可以主动延伸一下窗口函数三兄弟ROW_NUMBER、RANK、DENSE_RANK。它们都是排名但处理并列的方式不同。ROW_NUMBER 不管是否并列都生成连续编号1、2、3、4RANK 遇到并列会跳号比如成绩是100、100、90排名是1、1、3DENSE_RANK 不跳号排名是1、1、2。很多业务场景用的是 DENSE_RANK因为并列名次不应该让下一位空出来。3.2 题九每个部门工资前三先问需求再给方案“每个部门工资前三的员工”是Top N问题的经典代表几乎每个数据岗面试都会出现。这道题最大的陷阱不在SQL本身而在需求定义工资并列怎么处理是有三个并列第一就都不要第四还是只取三行数据要不要包含并列第三名大多数时候业务希望“并列前三都要”这种情况 DENSE_RANK 是最合适的因为并列第三也能被保留。如果业务严格只取前三条记录那就用 ROW_NUMBER。所以我的建议是面试时先开口问一句“并列怎么算”这句话本身就是加分项因为它说明你有需求分析意识而不是拿到题就闷头写。以“包含并列前三”为例标准写法如下WITH ranked AS ( SELECT emp_id, dept_id, salary, DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rk FROM employee ) SELECT dept_id, emp_id, salary FROM ranked WHERE rk 3;如果面试官接着问“你们数据库如果不支持窗口函数怎么办”你可以给出相关子查询的版本这也是理解排名本质的好方法。思路是统计同一部门里工资严格高于当前员工工资的不同工资档位有多少个。这个数字小于3说明当前员工排在前三档。SELECT dept_id, emp_id, salary FROM employee e WHERE ( SELECT COUNT(DISTINCT e2.salary) FROM employee e2 WHERE e2.dept_id e.dept_id AND e2.salary e.salary ) 3;这个写法要特别注意 DISTINCT如果部门里很多人工资相同不用 DISTINCT 会重复计数把并列工资的人错误地挤出前三。讲到这里面试官基本就会对你的水平有个明确判断了。Top N问题在工程里的变体很多比如“每个分类下销量前10的商品”“每个区域最近7天的订单”核心思路一样先分区、再排序、再过滤。4. 执行顺序、索引与深分页慢SQL问题的完整回答框架4.1 题五SQL 的逻辑执行顺序到底是什么这题特别有意思因为它看起来是个送分题但问细一点就能筛掉一批人。面试官会问SQL语句的书写顺序和逻辑执行顺序有什么区别为什么 SELECT 里的别名不能在 WHERE 里用但可以在 ORDER BY 里用逻辑执行顺序可以理解成一个流水线每一步都基于前一步的结果生成一张虚拟表。大致的顺序是FROM 先确定数据源JOIN 完成表关联WHERE 对关联后的行逐个过滤GROUP BY 把过滤后的行分组HAVING 对分组后的组做过滤SELECT 计算需要输出的列窗口函数也在这一步执行ORDER BY 对最终结果排序最后 LIMIT/OFFSET 做分页截取。书写顺序和这个顺序是完全相反的所以很多“为什么报错”的问题都能从这个顺序里找到答案。举三个最常见的应用场景。第一个SELECT amount * 0.9 AS discounted FROM orders WHERE discounted 100会报错因为 WHERE 在 SELECT 之前执行别名还不存在。第二个ORDER BY discounted则可以正常使用因为 ORDER BY 在 SELECT 之后执行。第三个窗口函数产生的列不能在 WHERE 里过滤需要包一层子查询再过滤也是同一个原因。回答时不用背太深重点是把“这一步基于上一步的结果”这个流水线模型讲清楚然后落到别名和HAVING这两个高频问题上。我一般还会补一句“数据库优化器实际执行时会基于逻辑顺序调整但逻辑顺序决定了SQL的语义合法性。”这句话能让回答立刻变专业。4.2 题六索引明明建了为什么全表扫还那么慢索引相关的问题是SQL性能面试的重头戏而这个问题几乎是必问的。“我给查询字段建了索引为什么 EXPLAIN 里的 type 还是 ALL”如果你直接背“索引失效的场景”面试官会觉得你只看了八股文要答好需要先弄明白索引为什么在某些情况下帮不上忙。B树索引的核心优势是能根据索引值快速定位或者利用索引有序性做排序。一旦查询条件破坏了“索引值本身的可比较性”索引就派不上用场了。常见场景我列一下场景原因改写思路对索引列用了函数比如DATE(created_at) 2024-01-01索引存储的是原始值不是函数结果无法直接定位改成范围条件如created_at 2024-01-01 AND created_at 2024-01-02字符串字段与整数比较隐式类型转换优化器可能放弃索引统一字段类型或者查询时也传字符串LIKE %keyword前缀不确定无法利用B树按前缀查找考虑全文索引或搜索引擎OR 条件里包含无索引列需要合并多路扫描代价高优化器经常选全表扫拆成两条SQL或用 UNION联合索引没按最左前缀顺序索引第一列没出现在条件下后面列无法命中调条件顺序或建合适的联合索引还有一个概念要澄清索引一般不会“凭空失效”而是优化器综合计算后觉得走全表扫描更快。比如一张只有几千行的小表索引扫描加上回表的代价比直接全表扫还大优化器当然选全表扫这不算失效是正常选择。这个认知能避免你在面试里说出“索引失效是因为引擎版本问题”这种话。真正要答出层次最后一定要落到 EXPLAIN 的读法上。关注几个关键列type 从高到低大致是 ALL、index、range、ref、eq_ref看到 ALL 就要警惕key 表示实际用了哪个索引rows 是预估扫描行数对比实际表行数能判断过滤性Extra 里的 Using filesort 和 Using temporary 往往比走了全表扫更危险因为它们意味着额外的排序和临时表开销。另外覆盖索引是很多面试官在追问环节特别喜欢的点。如果查询需要的列全部包含在索引里数据库可以直接从索引读取结果不需要回表这就是覆盖索引能极大减少随机IO。反过来SELECT *配合一个选择性不高的索引回表次数一多查询反而比全表扫还慢。这也是为什么我建议日常SQL里不要无脑取全部字段。4.3 题八LIMIT 1000000, 20 为什么越来越慢深分页问题在面试里出现频率很高因为它在业务里特别真实尤其是后台管理系统里翻到几十页之后接口突然变慢基本就是这个问题。题目通常是这样的SELECT * FROM orders ORDER BY id LIMIT 1000000, 20为什么越翻越慢原因是 LIMIT 的 offset 机制决定了数据库必须先扫描到第100万行然后把前面100万行全部丢弃只留下最后20行返回。offset 越大扫描的无效行数就越多成本自然线性上升。很多人知道这个结论但不知道怎么改。最常用的优化方案有两个。第一个叫延迟关联先只查主键段再回原表取明细SELECT t.* FROM orders t JOIN ( SELECT id FROM orders ORDER BY id LIMIT 1000000, 20 ) tmp ON t.id tmp.id;因为子查询里只取主键可以走覆盖索引扫描避免把大量无关字段都拉到内存里显著降低IO。第二个方案更彻底叫基于游标的分页适合移动端下拉刷新这类“只往后翻”的场景SELECT * FROM orders WHERE id :last_id ORDER BY id LIMIT 20;每次请求带上上一页最后一条记录的 id利用主键索引直接定位到下一页起始位置性能非常稳定。这个方案的代价是不能自由跳到任意页所以适合固定递进式浏览不适合翻页到指定页码的报表场景。如果你非要支持任意页码还有第三种思路按业务维度分段。比如按月份或按某个维度先切块每个分页请求只扫描目标时间段的数据避免全表一直到很大的 offset。这个方案在报表系统里很实用。最后提醒一个容易忽略的排序稳定性问题。如果分页排序字段不是唯一的比如ORDER BY create_time同时间戳的多行在两次查询里可能顺序不稳定导致翻页时数据重复或遗漏。工程上的做法是加一个唯一字段做二级排序比如ORDER BY create_time DESC, id DESC保证排序顺序是确定性的。面试里能主动提到这个点说明你在线上真实处理过分页逻辑。5. NULL陷阱与SQL注入最容易被忽视的边界题5.1 题七NULL 和空字符串到底有什么区别这题看起来基础实际上很多人栽过跟头。空字符串是一个长度为0的值比如手机号没填写时存了一个空串NULL的意思是“没有值、未知”它不参与任何比较。这两者的区别本质上在于数据库用的是三值逻辑除了 TRUE 和 FALSE还有 UNKNOWN。NULL 参与任何比较运算结果都是 UNKNOWN而不是 TRUE 或 FALSE。一个非常经典的坑是WHERE name NULL。很多人以为这样能筛选出 name 为空的记录实际上任何一行和 NULL 做等值比较结果都是 UNKNOWN查出来的结果永远是空集。正确写法必须是name IS NULL。另一个更隐蔽的坑是 NOT IN。假设你写WHERE user_id NOT IN (1, 2, NULL)直觉上应该是“不等于1、不等于2、不等于NULL”但实际结果是整个查询一行数据都查不出来。因为user_id NULL的结果是 UNKNOWNAND 连接时只要有一个 UNKNOWN整个条件就是 UNKNOWN行就被过滤了。所以用 NOT IN 时子查询结果一定先要排除 NULL。聚合函数对 NULL 的处理也经常被问道。COUNT() 统计的是行数NULL 行也计入COUNT(col) 统计的是 col 列非 NULL 的行数NULL 不会计入。所以如果某列经常出现 NULLCOUNT(col) 和 COUNT() 的结果可能差很多。SUM(col)、AVG(col) 默认会忽略 NULL但如果所有参与聚合的值都是 NULLSUM 的结果是 NULL不是0。AVG 就更微妙比如三个订单金额分别是100、NULL、200AVG 是150而不是把 NULL 当0算出的100。这关系到业务口径如果需求要求 NULL 按0处理必须在聚合前用 COALESCE(col, 0) 包一层。再把 NULL 联想到数据质量上。实际表里 NULL 和空字符串经常混用排查线上数据的时候要么统一在写入层处理要么查询时用 COALESCE 兜底。面试时能把“聚合、NOT IN、GROUP BY、去重”四个地方的 NULL 行为都讲一遍这道题基本就满分了。还有一个冷门细节GROUP BY 会把所有 NULL 分到同一个组里COUNT(DISTINCT col) 不算 NULL但 DISTINCT col 会把 NULL 作为一列值输出。5.2 题十动态拼接SQL如何防注入最后这题虽然传统但后端岗面试基本必问因为它考察的是写代码时有没有安全底线。场景通常是用户搜索关键字、排序字段、筛选条件通过前端传到后端开发人员把参数直接拼进 SQL 字符串。这种做法最大的风险就是注入——用户输入的内容被当成SQL代码执行轻则查询报错重则数据泄露。先说正确的做法能参数化就参数化。参数化的本质是把SQL结构和数据分开数据库对SQL结构做预编译参数只作为值传入。以 JDBC 为例// 错误字符串拼接输入本身就是SQL的一部分 String sql SELECT * FROM users WHERE name name ; // 正确预编译加参数绑定 PreparedStatement ps conn.prepareStatement(SELECT * FROM users WHERE name ?); ps.setString(1, name);MyBatis 这类 ORM 框架里也一样#{}会被解析成预编译占位符${}则直接把内容拼进SQL。所以默认情况下能用#{}绝不用${}select idqueryUser resultTypeUser SELECT * FROM users WHERE name #{name} /select但参数化不是万能的。SQL语句里有两类东西无法参数化一个是表名、列名等结构标识符另一个是ORDER BY的排序方向和排序字段。这些场景如果必须动态传入唯一可靠的方案是白名单校验。比如排序字段先定义一个允许的字段集合外部传入的值不在集合里就强制使用默认值而不是把字符串直接拼进去。排序的 ASC/DESC 也只接受枚举里的两个值其他一律拒绝。还有一个很多人忽略的点模糊查询。即使你用了参数化如果写法是LIKE % keyword %并且拼接过程在SQL字符串里完成仍然可能出问题。更稳的写法是使用参数占位符在数据库里拼出完整的模式串比如LIKE CONCAT(%, #{keyword}, %)这样关键字里出现的引号、通配符都只是数据不会被当成SQL结构。最后强调数据库账号的最小权限。给应用系统里跑查询的数据库账号只授予它业务需要的 SELECT、INSERT、UPDATE 权限不分配 DROP、DDL 权限即使出现注入攻击者能做的事情也极其有限。面试时能答到这里通常会得到很好的评价因为这说明你不仅知道常规套路还理解纵深防御的思路。准备SQL面试如果让我总结一条最实在的经验那就是别把题目当题目背而是把每个问题还原成“真实数据在数据库里怎么流动”的场景。拿到一道题先花十秒拆分要取哪些字段、先过滤哪些行、是否需要分组聚合、是否要排名或排序、有没有NULL和重复数据的干扰拆完再动手写基本不会错。十大问题只是一个起点真正值钱的是你在排除线上故障、分析慢查询时建立起来的那套直觉。