高效SQL查询需求：找出指定时段未在多表提交数据的用户

需求背景 #

现有AGENTS用户表、AREAS区域表，用户会根据数据类型将数据存入TBL1、TBL2…TBL6等多表。需要编写高效SQL，找出2023年1月1日至8月18日期间未向任何表提交数据的用户。当前SQL可运行，但在大数据库环境下超时，需要优化。

现有问题SQL #

Select AREA, USERID 
FROM AGENTS left outer join AREAS on AGENTS.AID=AREAS.AID
    WHERE USERID not in (Select distinct USERID FROM AGENTS 
        UNION Select Distinct USERID FROM TBL1 Where Datestamp between '1/1/2023' and '8/18/2023' 
        UNION Select Distinct USERID FROM TBL2 Where Datestamp between '1/1/2023' and '8/18/2023'
        UNION Select Distinct USERID FROM TBL3 Where Datestamp between '1/1/2023' and '8/18/2023'
        UNION Select Distinct USERID FROM TBL4 Where Datestamp between '1/1/2023' and '8/18/2023'
        UNION Select Distinct USERID FROM TBL5 Where Datestamp between '1/1/2023' and '8/18/2023'
        UNION Select Distinct USERID FROM TBL6 Where Datestamp between '1/1/2023' and '8/18/2023') Order by AREA

优化方案及改写代码 #

1. 修复逻辑错误+替换低效的NOT IN #

原查询中Select distinct USERID FROM AGENTS是完全冗余且错误的——这会把所有用户ID都加入排除列表，导致最终查询返回空结果，必须删除这一行。

同时，NOT IN在处理大数量级子查询时性能极差，改用LEFT JOIN + IS NULL的方式筛选未匹配的用户，性能提升显著：

SELECT a.AREA, ag.USERID
FROM AGENTS ag
LEFT JOIN AREAS a ON ag.AID = a.AID
LEFT JOIN (
    -- 用UNION ALL替代UNION，避免多次去重的性能开销
    SELECT USERID FROM TBL1 WHERE Datestamp BETWEEN '2023-01-01' AND '2023-08-18'
    UNION ALL
    SELECT USERID FROM TBL2 WHERE Datestamp BETWEEN '2023-01-01' AND '2023-08-18'
    UNION ALL
    SELECT USERID FROM TBL3 WHERE Datestamp BETWEEN '2023-01-01' AND '2023-08-18'
    UNION ALL
    SELECT USERID FROM TBL4 WHERE Datestamp BETWEEN '2023-01-01' AND '2023-08-18'
    UNION ALL
    SELECT USERID FROM TBL5 WHERE Datestamp BETWEEN '2023-01-01' AND '2023-08-18'
    UNION ALL
    SELECT USERID FROM TBL6 WHERE Datestamp BETWEEN '2023-01-01' AND '2023-08-18'
) submitted ON ag.USERID = submitted.USERID
WHERE submitted.USERID IS NULL
ORDER BY a.AREA;

2. 控制子查询数据量（可选） #

如果TBL1-TBL6的提交记录量极大，UNION ALL后的临时表可能占用过多内存，可以在子查询外层加一次DISTINCT，提前去重减少关联时的数据量：

SELECT a.AREA, ag.USERID
FROM AGENTS ag
LEFT JOIN AREAS a ON ag.AID = a.AID
LEFT JOIN (
    SELECT DISTINCT USERID FROM (
        SELECT USERID FROM TBL1 WHERE Datestamp BETWEEN '2023-01-01' AND '2023-08-18'
        UNION ALL
        SELECT USERID FROM TBL2 WHERE Datestamp BETWEEN '2023-01-01' AND '2023-08-18'
        UNION ALL
        SELECT USERID FROM TBL3 WHERE Datestamp BETWEEN '2023-01-01' AND '2023-08-18'
        UNION ALL
        SELECT USERID FROM TBL4 WHERE Datestamp BETWEEN '2023-01-01' AND '2023-08-18'
        UNION ALL
        SELECT USERID FROM TBL5 WHERE Datestamp BETWEEN '2023-01-01' AND '2023-08-18'
        UNION ALL
        SELECT USERID FROM TBL6 WHERE Datestamp BETWEEN '2023-01-01' AND '2023-08-18'
    ) temp
) submitted ON ag.USERID = submitted.USERID
WHERE submitted.USERID IS NULL
ORDER BY a.AREA;

3. 索引优化（关键） #

要让查询真正高效，必须配合合适的索引：

• 给TBL1-TBL6分别创建联合索引：CREATE INDEX idx_tbl_datetime_user ON TBLx(Datestamp, USERID);——这样子查询可以直接通过索引获取符合日期范围的USERID，无需全表扫描。
• 确保AGENTS表的AID、USERID字段，AREAS表的AID字段存在索引，加速表关联。
• 如果排序耗时，可以给AREAS表的AREA字段创建索引，或者在AGENTS和AREAS的关联结果上创建覆盖索引。

4. 日期格式规范 #

避免使用'1/1/2023'这种模糊的日期格式，改用数据库标准的'2023-01-01'格式，防止数据库进行隐式类型转换导致索引失效。

内容的提问来源于stack exchange，提问作者Shnozz