如何优化SQL查询，高效准确获取两表最新数据的差异对象？

一、提升查询效率 #

由于table_b数据量远大于table_a，核心优化思路是减少大表扫描范围、避免重复计算、让索引正常生效：

• 预计算并复用最大日期：原查询每次执行NOT EXISTS都会重复计算table_b的最大日期，改用CTE一次性算出两张表的最新日期，后续直接引用即可：

  WITH max_dates AS (
      SELECT 
          (SELECT MAX("Date Collected") FROM table_a) AS max_a_date,
          (SELECT MAX("date_collected") FROM table_b) AS max_b_date
  )
  

• 预过滤大表的最新数据：提前从table_b中提取最新日期的子集，避免每次匹配都扫描全量数据，可将子集存入CTE或临时表：

  latest_b AS (
      SELECT UPPER("computer_name") AS comp_name
      FROM table_b, max_dates
      WHERE "date_collected" = max_dates.max_b_date
  )
  

• 修复索引失效问题：原查询用UPPER()包裹字段会导致常规索引无法使用，可通过两种方式解决：

  1. 业务侧统一将Name和computer_name存储为大写/小写，查询时直接匹配无需转换；
  2. 创建函数索引，让转换后的字段能用上索引：

     CREATE INDEX idx_b_comp_name_upper ON table_b (UPPER("computer_name"), "date_collected");
     

• 替换NOT EXISTS为LEFT JOIN（可选）：部分数据库的查询优化器对LEFT JOIN + IS NULL的处理更高效，尤其是大表已预过滤时：

  SELECT COUNT(DISTINCT latest_a."Name")
  FROM latest_a
  LEFT JOIN latest_b ON latest_b.comp_name LIKE CONCAT('%', UPPER(latest_a."Name"), '%')
  WHERE latest_b.comp_name IS NULL;
  

二、修复结果准确性 #

结果不准确通常源于子串匹配误判或大小写处理不一致：

• 统一大小写转换逻辑：确保两张表的字段转换规则完全一致，比如都用UPPER()或都用LOWER()，避免数据库对特殊字符（如带重音的字符）的转换差异导致匹配失败。

• 精准控制子串匹配范围：原查询的%xxx%会匹配任意包含子串的内容，若需求是Name作为独立单词存在于computer_name中，需调整匹配规则：

  • 匹配前后带空格的情况：LIKE CONCAT('% ', UPPER(latest_a."Name"), ' %')（注意处理字符串首尾的单词）；
  • 使用正则表达式匹配单词边界：比如PostgreSQL用REGEXP_MATCHES，MySQL用REGEXP：

    WHERE latest_b.comp_name REGEXP CONCAT('[[:<:]]', UPPER(latest_a."Name"), '[[:>:]]')
    

• 提前去重减少误统计：在table_a的最新数据中先对Name去重，避免重复的Name被多次统计，同时减少后续匹配的次数：

  latest_a AS (
      SELECT DISTINCT "Name"
      FROM table_a, max_dates
      WHERE "Date Collected" = max_dates.max_a_date
  )
  

优化后的完整示例SQL #

WITH max_dates AS (
    SELECT 
        (SELECT MAX("Date Collected") FROM table_a) AS max_a_date,
        (SELECT MAX("date_collected") FROM table_b) AS max_b_date
),
latest_a AS (
    SELECT DISTINCT UPPER("Name") AS name_upper
    FROM table_a
    WHERE "Date Collected" = (SELECT max_a_date FROM max_dates)
),
latest_b AS (
    SELECT UPPER("computer_name") AS comp_name_upper
    FROM table_b
    WHERE "date_collected" = (SELECT max_b_date FROM max_dates)
)
SELECT COUNT(*)
FROM latest_a
WHERE NOT EXISTS (
    SELECT 1
    FROM latest_b
    WHERE latest_b.comp_name_upper LIKE CONCAT('%', latest_a.name_upper, '%')
);

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