BigQuery中使用LIKE运算符的内连接查询优化咨询

针对你用LIKE '%' || T2.ID || '%'做跨表部分匹配时全量查询成本过高的问题，给你几个不用拆分数据集的优化方案：

• 用全文索引替代LIKE模糊匹配
  BigQuery的全文索引专门针对文本检索优化，比通配符LIKE效率高很多。先给T1的ID列建索引：

  CREATE OR REPLACE INDEX idx_t1_id_fulltext
  ON T1(ID)
  OPTIONS(analyzer='standard');
  

  然后改写查询语句：

  SELECT T1.ID, T2.ID
  FROM T1
  JOIN T2 ON MATCH(T1.ID) AGAINST(T2.ID IN BOOLEAN MODE);
  

  这个方案对短子串匹配的场景适配性很好，正好符合你的需求。

• 把T2的ID合并成正则批量匹配
  把T2里所有ID拼成一个正则表达式，一次性过滤T1中符合条件的数据，再关联T2，避免直接做N:N的JOIN：

  -- 先把T2的ID合并成正则模式
  DECLARE match_pattern STRING;
  SET match_pattern = (SELECT STRING_AGG('(' || ID || ')', '|') FROM T2);
  
  -- 先过滤T1再关联T2
  SELECT T1.origin_id, T2.ID
  FROM (
    SELECT 
      ID AS origin_id,
      REGEXP_EXTRACT_ALL(ID, match_pattern) AS matched_subs
    FROM T1
    WHERE REGEXP_CONTAINS(ID, match_pattern)
  ) T1
  CROSS JOIN UNNEST(T1.matched_subs) AS sub_id
  JOIN T2 ON T2.ID = sub_id;
  

  这种方式能大幅减少无效的JOIN计算，降低查询的资源消耗。

• 给T1做分区/分簇缩小扫描范围
  如果T1数据量特别大，先按ID的长度或者前缀做分簇处理：

  -- 创建按ID长度分簇的表
  CREATE OR REPLACE TABLE T1_clustered
  CLUSTER BY LENGTH(ID)
  AS SELECT * FROM T1;
  

  查询时先过滤掉不可能匹配的短ID，BigQuery会只扫描符合条件的簇：

  SELECT T1.ID, T2.ID
  FROM T1_clustered T1
  JOIN T2 ON T1.ID LIKE '%' || T2.ID || '%'
  WHERE LENGTH(T1.ID) >= LENGTH(T2.ID);
  

  分簇后能减少不必要的数据扫描，降低查询成本。

• 预计算子串哈希做精准匹配
  如果T2的ID长度相对固定，可以预生成T1所有可能子串的哈希，再和T2 ID的哈希匹配：

  -- 预生成T1所有子串的哈希表
  CREATE OR REPLACE TABLE T1_sub_hash AS
  SELECT 
    ID,
    SUBSTR(ID, pos, len) AS sub_id,
    FARM_FINGERPRINT(SUBSTR(ID, pos, len)) AS sub_hash
  FROM T1,
       UNNEST(GENERATE_ARRAY(1, LENGTH(ID))) pos,
       UNNEST(GENERATE_ARRAY(1, LENGTH(ID) - pos + 1)) len;
  
  -- 预生成T2 ID的哈希表
  CREATE OR REPLACE TABLE T2_id_hash AS
  SELECT ID, FARM_FINGERPRINT(ID) AS id_hash FROM T2;
  
  -- 通过哈希关联匹配结果
  SELECT T1.ID, T2.ID
  FROM T1_sub_hash T1
  JOIN T2_id_hash T2 ON T1.sub_hash = T2.id_hash;
  

  哈希匹配是精准比对，比模糊匹配的计算成本低很多，适合子串长度固定的场景。

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