字符串型id_product转数值ID后关联大数据集的内存优化方案咨询

云原生阿岚

2026-4-29

如何优化大型数据集下字符串ID转数值ID并关联的内存消耗？

问题描述

我正在将一组唯一的id_product（由字母和数字组成的字符串）转换为数值型ID（采用其在数据集内的行号），随后将该新的数值型列关联至一个包含多类ID的大型数据集，具体实现的SQL语句如下：
with cte as (select distinct id_product, row_number() over () as id_product2 from tb_market_data)
select t1.id_customer, t1.id_product, t2.id_product2 from tb_market_data as t1 left join cte as t2 on t1.id_product = t2.id_product
该方法可实现需求，但由于数据集规模庞大，以字符串作为关联键进行表关联操作时会耗尽系统内存。请问是否存在能够降低内存消耗的处理方式？补充说明：我无法直接移除id_product中的所有字母，因为这会导致不同产品的ID重复（例如X001与B001会变为相同的001）。

优化解决方案

针对大表字符串关联内存占用过高的问题，我整理了几个经过实践验证的优化方向，你可以根据自己使用的数据库类型选择合适的方案：

1. 用唯一哈希值替代原字符串做临时关联键

字符串作为关联键的内存开销大，我们可以先对id_product生成一个唯一的数值哈希值，用哈希值来做关联（内存占用远低于字符串），同时保留原字符串的校验避免哈希冲突。

以PostgreSQL为例，代码示例如下：

-- 先创建临时映射表，预计算哈希值和数值ID
CREATE TEMP TABLE product_mapping AS
SELECT 
  distinct id_product,
  row_number() over () as id_product2,
  -- 将SHA256哈希的前8字节转成32位整数，冲突概率极低
  ('x' || substr(digest(id_product, 'sha256'), 1, 8))::bit(32)::int as product_hash
FROM tb_market_data;

-- 给哈希列建索引，加速关联查询
CREATE INDEX idx_product_hash ON product_mapping(product_hash);

-- 关联时先计算原表的哈希值，再关联映射表，同时加原ID校验防冲突
SELECT 
  t1.id_customer, 
  t1.id_product, 
  t2.id_product2 
FROM tb_market_data as t1 
LEFT JOIN product_mapping as t2 
  ON ('x' || substr(digest(t1.id_product, 'sha256'), 1, 8))::bit(32)::int = t2.product_hash
  AND t1.id_product = t2.id_product;

如果是MySQL，可以用MD5()函数生成哈希后转成整数；SQL Server则用HASHBYTES('SHA2_256', id_product)来处理。

2. 物化映射表并建立索引，避免CTE的内存开销

原CTE的distinct和row_number()在超大表上会让数据库在内存中构建临时数据集，导致内存耗尽。可以把映射关系物化到临时表，并给关联键id_product建立索引，让数据库走索引扫描而非全表哈希连接。

示例：

-- 将映射关系物化到临时表
CREATE TEMP TABLE product_mapping AS
SELECT distinct id_product, row_number() over () as id_product2 FROM tb_market_data;

-- 给关联键创建索引，大幅降低关联时的内存消耗
CREATE INDEX idx_id_product ON product_mapping(id_product);

-- 使用带索引的临时表做关联
SELECT 
  t1.id_customer, 
  t1.id_product, 
  t2.id_product2 
FROM tb_market_data as t1 
LEFT JOIN product_mapping as t2 
  ON t1.id_product = t2.id_product;

临时表的索引会让数据库采用更高效的嵌套循环连接或合并连接，避免在内存中构建巨大的哈希表。

3. 分批处理数据，拆分内存压力

如果整个数据集实在太大，无法一次性处理，可以按id_product的特征（比如首字母范围、哈希值区间）拆分批次，每批生成映射并关联，最后合并结果。

示例（按首字母分段处理）：

-- 处理首字母A-F的批次
WITH cte AS (
  SELECT distinct id_product, row_number() over () as id_product2 
  FROM tb_market_data 
  WHERE id_product LIKE 'A%' OR id_product LIKE 'B%' OR id_product LIKE 'C%' OR id_product LIKE 'D%' OR id_product LIKE 'E%' OR id_product LIKE 'F%'
)
SELECT t1.id_customer, t1.id_product, t2.id_product2 
FROM tb_market_data as t1 
LEFT JOIN cte as t2 ON t1.id_product = t2.id_product
WHERE id_product LIKE 'A%' OR id_product LIKE 'B%' OR id_product LIKE 'C%' OR id_product LIKE 'D%' OR id_product LIKE 'E%' OR id_product LIKE 'F%'

UNION ALL

-- 处理首字母G-L的批次
WITH cte AS (
  SELECT distinct id_product, row_number() over () as id_product2 
  FROM tb_market_data 
  WHERE id_product LIKE 'G%' OR id_product LIKE 'H%' OR id_product LIKE 'I%' OR id_product LIKE 'J%' OR id_product LIKE 'K%' OR id_product LIKE 'L%'
)
SELECT t1.id_customer, t1.id_product, t2.id_product2 
FROM tb_market_data as t1 
LEFT JOIN cte as t2 ON t1.id_product = t2.id_product
WHERE id_product LIKE 'G%' OR id_product LIKE 'H%' OR id_product LIKE 'I%' OR id_product LIKE 'J%' OR id_product LIKE 'K%' OR id_product LIKE 'L%';

-- 继续处理后续批次...

每批的数据量大幅减小后，内存压力会显著降低，最后用UNION ALL合并所有批次的结果即可（注意如果需要全局唯一的id_product2，可以先统计总distinct数量再分配序号区间，或者合并后重新编号）。

4. 利用数据库特定优化参数或算法

不同数据库有针对大表关联的专属优化：

MySQL：可以适当调大join_buffer_size（但不要超过系统可用内存），或者用STRAIGHT_JOIN强制让小表（product_mapping）先被加载，减少内存占用；
SQL Server：可以在查询末尾加OPTION (MERGE JOIN)，强制使用合并连接算法（内存消耗比哈希连接低很多，前提是关联键有序）；
PostgreSQL：调整work_mem参数（比如SET work_mem = '64MB'）给排序和哈希操作分配更多内存，但要注意不要超过系统可用内存的1/4，避免OOM。

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