-- 子查询版本（自然但可能慢）
SELECT * FROM order
WHERE user_id IN (SELECT id FROM user WHERE status = 1);

-- JOIN 版本（通常更快）
SELECT o.* FROM order o
JOIN user u ON o.user_id = u.id
WHERE u.status = 1;

-- 子查询的执行方式：
-- 1. 先执行外部查询：SELECT * FROM order（扫描 100 万行）
-- 2. 对每一行，执行子查询：SELECT id FROM user WHERE status = 1
-- 3. 如果子查询结果集包含当前行的 user_id，则加入结果
-- 问题：子查询被重复执行多次

-- 子查询在 MySQL 5.5 及更早版本中的执行：
-- 被"逐行执行"——对 order 的每一行都执行一次子查询
-- 这在 order 表很大时是灾难

-- 某些子查询类型需要创建临时表
SELECT * FROM order
WHERE user_id IN (SELECT DISTINCT user_id FROM order_item WHERE quantity > 10);
-- 子查询需要先构建 DISTINCT 的结果集，再生成临时表
-- JOIN 改写后在 ORDER BY 阶段才需要去重

-- 原始：查询有过订单的用户
SELECT * FROM user
WHERE id IN (SELECT user_id FROM order);

-- 改写为 JOIN（注意去重）
SELECT DISTINCT u.* FROM user u
JOIN order o ON u.id = o.user_id;

-- 原始：查询没有订单的用户
SELECT * FROM user
WHERE id NOT IN (SELECT user_id FROM order);

-- 改写为 LEFT JOIN
SELECT u.* FROM user u
LEFT JOIN order o ON u.id = o.user_id
WHERE o.id IS NULL;

-- 改写前
SELECT * FROM user u
WHERE EXISTS (SELECT 1 FROM order o 
              WHERE o.user_id = u.id AND o.amount > 1000);

-- 改写后（MySQL 优化器可能自动转换为其内部形式）
-- 如果 EXPLAIN 中显示 "Start temporary" 和 "End temporary"
-- 说明已经用了 Semi-Join 优化

-- 原始：查询每个用户的最新订单
SELECT u.*, 
    (SELECT MAX(amount) FROM order WHERE user_id = u.id) AS max_amount
FROM user u;

-- 改写为 JOIN + GROUP BY
SELECT u.*, o.max_amount
FROM user u
LEFT JOIN (
    SELECT user_id, MAX(amount) AS max_amount
    FROM order
    GROUP BY user_id
) o ON u.id = o.user_id;

-- 标量子查询的问题：每行 user 都会执行一次子查询
-- 改写后只需要 GROUP BY 一次所有订单，然后 LEFT JOIN

-- 原始：查询价格高于平均价的商品
SELECT * FROM product p
WHERE price > (SELECT AVG(price) FROM product WHERE category_id = p.category_id);

-- 改写
SELECT p.* FROM product p
JOIN (
    SELECT category_id, AVG(price) AS avg_price
    FROM product
    GROUP BY category_id
) avg_p ON p.category_id = avg_p.category_id
WHERE p.price > avg_p.avg_price;

-- 子查询：IN 自动去重
SELECT * FROM user WHERE id IN (SELECT user_id FROM order);

-- JOIN（有重复行！）
SELECT u.* FROM user u JOIN order o ON u.id = o.user_id;
-- 如果一个用户有 10 个订单，这个用户会被返回 10 次

-- 正确改写：加 DISTINCT
SELECT DISTINCT u.* FROM user u JOIN order o ON u.id = o.user_id;

SELECT * FROM order
WHERE amount > (SELECT AVG(amount) FROM order);
-- 子查询只执行一次，结果固定
-- 改写时要小心 GROUP BY 带来的语义变化

EXPLAIN SELECT DISTINCT u.* FROM user u JOIN order o ON u.id = o.user_id;
-- 查看 type 列：ref/eq_ref 或 ALL？
-- 查看 Extra 列：Using temporary 还是 Using index？
-- 比较改前后的 rows 和 key