使用DeepSeek辅助优化SQL关联查询ON条件字段为空问题的实践

SELECT o.order_id, o.amount, u.user_name, u.email
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE o.create_time > '2023-01-01';

-- 分析数据分布
SELECT 
    COUNT(*) as total_orders,
    SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) as null_user_id_count,
    ROUND(SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) as null_percentage
FROM orders
WHERE create_time > '2023-01-01';

-- 优化后的查询
SELECT o.order_id, o.amount, u.user_name, u.email
FROM orders o
LEFT JOIN users u ON COALESCE(o.user_id, -1) = COALESCE(u.user_id, -1)
WHERE o.create_time > '2023-01-01';

-- 为优化查询创建相应索引
CREATE INDEX idx_orders_user_id ON orders(COALESCE(user_id, -1));
CREATE INDEX idx_users_user_id ON users(COALESCE(user_id, -1));

-- 拆分查询：先查询user_id不为空的记录
SELECT o.order_id, o.amount, u.user_name, u.email
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id
WHERE o.create_time > '2023-01-01'
AND o.user_id IS NOT NULL

UNION ALL

-- 再查询user_id为空的记录
SELECT o.order_id, o.amount, NULL as user_name, NULL as email
FROM orders o
WHERE o.create_time > '2023-01-01'
AND o.user_id IS NULL;

-- 创建函数索引
CREATE INDEX idx_orders_nullable_user ON orders((COALESCE(user_id, -1)));

-- 配合索引的查询
SELECT o.order_id, o.amount, u.user_name, u.email
FROM orders o
LEFT JOIN users u ON COALESCE(o.user_id, -1) = COALESCE(u.user_id, -1)
WHERE o.create_time > '2023-01-01';

<!-- 使用拆分查询方案的MyBatis配置 -->
<select id="getOrdersWithUsers" resultMap="orderUserMap">
    <include refid="getOrdersWithUserInfo"/>
    UNION ALL
    <include refid="getOrdersWithoutUserInfo"/>
</select>

<sql id="getOrdersWithUserInfo">
    SELECT o.order_id, o.amount, u.user_name, u.email
    FROM orders o
    INNER JOIN users u ON o.user_id = u.user_id
    WHERE o.create_time > #{startDate}
    AND o.user_id IS NOT NULL
</sql>

<sql id="getOrdersWithoutUserInfo">
    SELECT o.order_id, o.amount, NULL as user_name, NULL as email
    FROM orders o
    WHERE o.create_time > #{startDate}
    AND o.user_id IS NULL
</sql>