1. 当SQL遇上套娃子查询的实战解析第一次在MySQL中看到嵌套三层的SELECT语句时我的表情就像看到俄罗斯套娃被拆开的瞬间。子查询这种查询中的查询机制本质上就是SQL版的套娃艺术。但不同于装饰品它在数据处理中能解决许多单层查询无法应对的场景。假设我们需要找出销售额高于平均值的商品传统思路可能需要先执行AVG()查询再手动输入这个值进行二次查询。而子查询让这个过程一气呵成SELECT product_name, price FROM products WHERE price (SELECT AVG(price) FROM products);注意MySQL 5.6之前对子查询优化较差建议在WHERE子句中使用JOIN替代。但8.0版本后优化器已大幅改进性能差异可以忽略。1.1 子查询的四种经典姿势根据出现位置子查询主要分为四大类型WHERE子句中的标量子查询返回单个值SELECT * FROM orders WHERE user_id (SELECT id FROM users WHERE phone13800138000);FROM子句中的派生表必须要有别名SELECT t.department, AVG(t.salary) FROM (SELECT * FROM employees WHERE hire_date 2020-01-01) AS t GROUP BY t.department;IN/NOT IN中的列子查询返回一列值SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE is_active1);SELECT子句中的关联子查询每行都执行SELECT id, name, (SELECT COUNT(*) FROM orders WHERE user_idusers.id) AS order_count FROM users;1.2 那些年我踩过的子查询坑去年双十一大促时我们有个促销活动查询突然超时最终定位到一个五层嵌套的子查询。教训是嵌套超过3层就该考虑重构。具体优化方案包括将多层子查询拆分为临时表用JOIN替代WHERE中的子查询对派生表的结果建立索引-- 错误示范性能灾难 SELECT * FROM table1 WHERE id IN ( SELECT id FROM table2 WHERE col IN ( SELECT col FROM table3 WHERE ... ) ); -- 正确姿势 WITH temp1 AS (SELECT col FROM table3 WHERE ...), temp2 AS (SELECT id FROM table2 WHERE col IN (SELECT col FROM temp1)) SELECT * FROM table1 WHERE id IN (SELECT id FROM temp2);2. 拼图的艺术UNION的妙用如果说子查询是纵向嵌套那么UNION就是横向拼接。它像玩拼图一样把结构相似的结果集合并展示。上周我帮市场部合并线上线下会员数据时就用到了这个特性-- 合并线上线下会员自动去重 SELECT id, name, online AS source FROM online_members UNION SELECT id, name, offline AS source FROM offline_members; -- 保留所有记录包括重复 SELECT * FROM jan_sales UNION ALL SELECT * FROM feb_sales;2.1 UNION的三大核心规则列数必须相同我曾经因为SELECT字段数量不一致收到ERROR 1222的报错后来养成习惯先用COUNT(*)确认SELECT COUNT(*) FROM (SELECT col1 FROM table1) AS t1 UNION SELECT COUNT(*) FROM (SELECT col1,col2 FROM table2) AS t2; -- 如果结果不同就会报错数据类型要兼容日期和字符串混用可能导致隐式转换最好显式CASTSELECT CAST(create_time AS CHAR) AS time_str FROM logs UNION SELECT 2023- month FROM reports;ORDER BY全局生效只能在最后一个查询排序且按最终结果列排序SELECT id, name FROM users WHERE status1 UNION SELECT id, nickname FROM admins ORDER BY name; -- 对合并后的name列排序2.2 性能优化实战技巧当UNION遇到大数据量时这几个方法让我的查询从30秒降到0.5秒分页优化先各表分页再UNION(SELECT * FROM large_table1 LIMIT 1000) UNION ALL (SELECT * FROM large_table2 LIMIT 1000) LIMIT 1500; -- 最终取1500条索引利用确保每个子查询都能命中索引-- 添加联合索引 ALTER TABLE products ADD INDEX idx_cate_status (category_id, status); -- 然后UNION查询能利用索引 SELECT id FROM products WHERE category_id1 AND status1 UNION SELECT id FROM products WHERE category_id2 AND status2;UNION ALL优先除非确实需要去重否则用UNION ALL避免排序开销3. 组合技当套娃遇上拼图真实业务中子查询和UNION经常配合使用。比如我们需要统计各渠道用户的首单信息SELECT user_id, MIN(create_time) AS first_order_time FROM ( SELECT user_id, create_time FROM web_orders UNION ALL SELECT user_id, create_time FROM app_orders ) AS all_orders GROUP BY user_id;3.1 报表生成实战案例上月做年度财报时这个组合查询帮我节省了5小时手工整理时间SELECT Q1 AS quarter, (SELECT SUM(amount) FROM sales WHERE quarter1) AS total_sales, (SELECT COUNT(DISTINCT user_id) FROM orders WHERE quarter1) AS users UNION SELECT Q2, (SELECT SUM(amount) FROM sales WHERE quarter2), (SELECT COUNT(DISTINCT user_id) FROM orders WHERE quarter2) UNION ... -- Q3、Q4同理 ORDER BY quarter;3.2 性能监控中的妙用这是我用在数据库监控中的模板实时对比不同时段指标SELECT 当前时段 AS period, (SELECT COUNT(*) FROM slow_queries WHERE create_time DATE_SUB(NOW(), INTERVAL 1 HOUR)) AS slow_count, (SELECT COUNT(*) FROM locked_transactions) AS lock_count UNION SELECT 昨日同期, (SELECT COUNT(*) FROM slow_queries WHERE create_time BETWEEN DATE_SUB(DATE_SUB(NOW(), INTERVAL 1 DAY), INTERVAL 1 HOUR) AND DATE_SUB(NOW(), INTERVAL 1 DAY)), (SELECT COUNT(*) FROM locked_transactions WHERE create_time BETWEEN DATE_SUB(DATE_SUB(NOW(), INTERVAL 1 DAY), INTERVAL 1 HOUR) AND DATE_SUB(NOW(), INTERVAL 1 DAY));4. 避坑指南与高阶技巧4.1 EXPLAIN是你的最佳搭档面对复杂查询我必用EXPLAIN分析执行计划。曾经有个UNION查询性能很差分析后发现第一个子查询全表扫描typeALL第二个子查询用了索引typeref临时表使用了filesort通过添加复合索引最终性能提升20倍。4.2 变量替代重复子查询当同一个子查询被多次调用时改用会话变量SET avg_price (SELECT AVG(price) FROM products); SELECT * FROM products WHERE price avg_price; SELECT COUNT(*) FROM products WHERE price avg_price;4.3 WITH子句提升可读性MySQL 8.0支持CTE(Common Table Expressions)让嵌套查询更清晰WITH hot_products AS ( SELECT * FROM products WHERE sales 1000 ), active_users AS ( SELECT * FROM users WHERE last_login 2023-01-01 ) SELECT * FROM hot_products WHERE user_id IN (SELECT id FROM active_users WHERE vip_level 3);4.4 注意NULL值的处理这是新手最容易栽跟头的地方-- 查询没有订单的用户NOT IN遇到NULL会返回空结果 SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders); -- 正确写法 SELECT * FROM users WHERE NOT EXISTS (SELECT 1 FROM orders WHERE orders.user_id users.id);5. 真实业务场景复盘去年重构电商平台订单系统时我们遇到个棘手需求要找出购买过A类商品但未买B类的高价值用户。最终方案结合了所有知识点WITH buyer_a AS ( SELECT DISTINCT user_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE categoryA) ), buyer_b AS ( SELECT DISTINCT user_id FROM orders WHERE product_id IN (SELECT id FROM products WHERE categoryB) ), vip_users AS ( SELECT id FROM users WHERE total_spend 10000 ) SELECT u.* FROM users u JOIN buyer_a a ON u.id a.user_id LEFT JOIN buyer_b b ON u.id b.user_id JOIN vip_users v ON u.id v.id WHERE b.user_id IS NULL;这个查询的精妙之处在于用CTE提高可读性子查询找出目标用户群LEFT JOINIS NULL实现反选最终JOIN筛选高价值用户执行时间从最初的28秒优化到1.3秒关键是在product_id和user_id上建立了联合索引。