SQL 面试题：WHERE、HAVING 与 ON 的执行时机与性能影响-北京尧图网络

1. 为什么面试官总爱问WHERE、HAVING和ON的区别每次面试数据库相关岗位这三个关键词就像固定节目一样准时出现。我刚入行时也纳闷它们不都是用来过滤数据的吗直到有次在千万级数据表上写错条件导致查询超时才真正理解执行顺序的差异对性能的影响有多大。举个例子去年我们电商系统做促销活动分析时有个同事写了这样的SQLSELECT user_id, COUNT(order_id) FROM orders GROUP BY user_id HAVING create_time 2023-01-01结果查询跑了20分钟还没出结果。后来改成WHERE条件后3秒就返回了数据。这就是典型的分不清过滤时机导致的性能问题。2. WHERE与HAVING的本质区别2.1 执行时机的关键差异想象你是个快递站站长WHERE就像在包裹入库时直接拒收不符合要求的快递比如到付件而HAVING是所有包裹按区域分好堆之后再把不符合标准的整堆包裹扔掉。具体来说WHERE在数据库引擎读取数据时立即过滤相当于原料质检HAVING在所有数据分组聚合完成后过滤相当于成品检验2.2 性能对比实测我用MySQL的100万条订单数据做了组对照实验查询类型执行时间扫描行数WHERE条件0.12s153,291HAVING条件1.87s1,000,000当使用WHERE statuspaid时引擎先用索引过滤掉85%的未支付订单再对剩余数据分组。而HAVING statuspaid会先扫描全表分组最后才丢弃无效数据。2.3 实际开发中的黄金法则能用WHERE就别用HAVING特别是当过滤条件不依赖聚合结果时HAVING专用场景筛选聚合结果如HAVING AVG(score)80组合使用范例-- 查询2023年消费超过5次的VIP用户 SELECT user_id, COUNT(*) as order_count FROM orders WHERE user_levelVIP AND create_time BETWEEN 2023-01-01 AND 2023-12-31 GROUP BY user_id HAVING COUNT(*) 53. ON条件的特殊行为3.1 内连接时的等价性在inner join时下面两种写法其实等效-- 写法1条件全放在ON SELECT * FROM users u JOIN orders o ON u.ido.user_id AND o.amount1000 -- 写法2连接条件放ON过滤条件放WHERE SELECT * FROM users u JOIN orders o ON u.ido.user_id WHERE o.amount1000但建议采用写法2因为语义更清晰ON只管连接WHERE负责过滤某些数据库优化器对WHERE条件优化更好3.2 外连接时的陷阱左连接时把右表过滤条件放错位置结果可能天差地别-- 查询所有部门及其中薪资1万的员工错误写法 SELECT d.name, e.name FROM departments d LEFT JOIN employees e ON d.ide.dept_id AND e.salary10000 -- 正确写法 SELECT d.name, e.name FROM departments d LEFT JOIN employees e ON d.ide.dept_id WHERE e.salary10000 OR e.id IS NULL第一个查询会返回所有部门但薪资条件可能不生效第二个才是真正筛选高薪员工同时保留无员工部门。3.3 执行计划解读用EXPLAIN分析上述两个查询错误写法的过滤条件(e.salary10000)出现在JOIN的Extra列正确写法的过滤条件出现在WHERE子句这说明ON条件在连接时逐行判断WHERE条件在连接完成后整体过滤4. 高级优化技巧4.1 索引利用的差异WHERE子句能充分利用索引而HAVING通常无法使用索引。我曾优化过一个统计查询通过把HAVING create_date2023-01-01改为WHERE条件同时给create_date字段加索引查询时间从8秒降到0.2秒。4.2 子查询优化策略对于复杂聚合查询可以分阶段处理-- 原始低效写法 SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) (SELECT AVG(salary) FROM employees) -- 优化后写法 WITH dept_avg AS ( SELECT department, AVG(salary) as avg_sal FROM employees GROUP BY department ) SELECT * FROM dept_avg WHERE avg_sal (SELECT AVG(salary) FROM employees)4.3 分区表特别注意事项当使用分区表时WHERE条件要包含分区键才能触发分区裁剪。有次我们查询按月分区的日志表忘记在WHERE加月份条件导致全表扫描。5. 常见面试问题破解面试官可能会追问为什么这个HAVING条件要改成WHERE从执行顺序、中间结果集大小、索引利用三个维度回答LEFT JOIN时ON和WHERE放错会怎样用具体例子说明结果差异最好能画数据流图示如何优化这个包含HAVING的慢查询建议改为WHERE条件考虑使用临时表分步处理检查相关字段索引有次面试我遇到个刁钻问题HAVING里能不能用窗口函数正确答案是-- 合法的HAVING使用窗口函数 SELECT department, AVG(salary) as avg_sal FROM employees GROUP BY department HAVING AVG(salary) ( SELECT AVG(avg_sal) OVER() FROM (SELECT AVG(salary) as avg_sal FROM employees GROUP BY department) t )但实际开发中应该避免这种写法可读性和性能都很差。

SQL 面试题：WHERE、HAVING 与 ON 的执行时机与性能影响

相关推荐

D14 | Agent 是什么？ReAct 模式让你看懂所有 Agent 框架

Python数据分析实战：Pandas分组统计与Matplotlib饼图可视化

HoRain云--Node.js 全局对象

更多资讯

特种工最新消息内部消息：怕考不过白交培训费？这些避坑技巧必须知道

电工证中级天津哪里考？千万别踩坑的全流程避坑指南

考电工证怎么选择学校郑州报考避坑指南

淮安疫情电工证复审怎么报名才能顺利上岗？

拿到电工证有什么好处郑州报考避坑指南

高压电工证在手，上岗必备怎么考？工地太忙根本没时间复习考试？

今日更新

高压电工证哪里可以挂多久拿证全攻略：实操考试心里没底怕挂科别慌

电工证的理论题目在哪考，外省证怎么转过来

工地太忙根本没时间复习考试，职业学校的电工证值不值得考？

本周精选

安监局电工证分数查询怎么查才能知道含金量高不高

新疆电工作业干活累吗良心建议助你避开考不过的坑

中牟县电工证怎么报名外省证能转吗

本月热门

面向复杂光照与角度的宠物面部识别鲁棒性增强策略

基于DRV8818与STM32的双极步进电机驱动方案实战解析

DRV8818PWPR+PIC18F45K80双极步进电机控制方案实战

读完文章，想聊聊您的网站？