使用MS SQL生成模拟数字列表查找缺失值是否存在性能问题？

现有名为Numbers的表，存储格式如下：

Number
------
00000
00001
00002
00005

需求是查找表中的缺失值。原本思路是创建包含所有可能值的Compare表，通过以下查询实现：

SELECT Number FROM Compare c 
LEFT JOIN Number n on c.Number = n.Number 
WHERE n.Number is null

但填充Compare表的10万条数据既占用内存又不够优雅，因此编写了生成模拟数字列表的查询（注：原SQL存在语法缺失，已补全CTE定义部分）：

WITH C AS (
    SELECT '0' AS Val 
    UNION SELECT '1' 
    UNION SELECT '2'
    UNION SELECT '3' 
    UNION SELECT '4'
    UNION SELECT '5' 
    UNION SELECT '6'
    UNION SELECT '7' 
    UNION SELECT '8'
    UNION SELECT '9' 
),
Zahl5Block AS (
    Select C1.Val + C2.Val + C3.Val + C4.Val + C5.Val AS Block5 from C C1
    FULL OUTER JOIN C C2 ON 1=1
    FULL OUTER JOIN C C3 ON 1=1
    FULL OUTER JOIN C C4 ON 1=1
    FULL OUTER JOIN C C5 ON 1=1
)

SELECT Block5 FROM Zahl5Block z
LEFT JOIN [Numbers] n on z.Block5 = n.Number
WHERE n.Number is null

现需确认：该查询是否会引发性能/内存问题？实际场景中数字范围将扩大至8位（而非当前的5位）。

回答 #

性能和内存肯定会出问题，尤其是8位数字的场景 #

1. 数据量直接爆炸
   5位数字时，这个查询会生成10万条记录，看起来还能扛；但到8位时，要生成1亿条记录。这可不是小数目：

   • 内存占用会猛增：数据库得把这1亿条拼接好的字符串临时存在内存里，就算每条只占8字节，也得800MB起步，再加上数据库本身的额外开销，内存压力直接拉满。
   • 磁盘IO会崩：如果内存装不下，数据库就会把临时数据写到磁盘临时表，这时候IO开销会飙升，查询速度慢到离谱，甚至可能直接超时。

2. JOIN操作的开销顶不住
   1亿条记录和Numbers表做LEFT JOIN，哪怕Numbers的Number字段有索引，匹配过程的计算量也大到吓人。数据库要在两个数据集里逐一匹配，1亿级别的数据量会让这个过程变得异常缓慢，大概率撑不到查询完成。

3. 原SQL写法还有没必要的浪费
   原查询里用FULL OUTER JOIN完全是多余的，C表只有10条记录，用CROSS JOIN（笛卡尔积）就行，效果一模一样，而且数据库对CROSS JOIN的优化通常更好。

给你几个优化方向 #

• 别拼字符串，先生成数字序列再格式化
  先生成连续的数字，再转成固定长度的字符串，比直接拼接字符串效率高多了。拿SQL Server举例子：

  WITH NumSeq AS (
      SELECT 0 AS Num
      UNION ALL
      SELECT Num + 1 FROM NumSeq WHERE Num < 99999999 -- 8位最大数
  )
  SELECT RIGHT('00000000' + CAST(Num AS VARCHAR(8)), 8) AS Block8
  FROM NumSeq
  LEFT JOIN [Numbers] n ON RIGHT('00000000' + CAST(Num AS VARCHAR(8)), 8) = n.Number
  WHERE n.Number IS NULL
  OPTION (MAXRECURSION 0) -- 关闭递归深度限制
  

  这种方式存的是数字，内存占用低，数据库处理数字的速度比字符串快得多。

• 分段查，别一次性搞完
  要是不需要一下子查出所有缺失值，可以把8位数字分成多个区间（比如按前两位分成100个段），每次只查一个区间的缺失值，这样单次查询的内存和性能压力就小多了。

• 利用现有表的索引找间隙
  如果Numbers表的Number字段有索引，可以用窗口函数对比相邻的记录，找出缺失的范围，再把范围展开成具体的缺失值。要是Numbers表的记录远少于1亿，这种方法效率会特别高，根本不用生成完整的序列。

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