MySQL如何对含多个逗号分隔ID的字段分组统计并优化查询性能

涉及表结构说明 #

• 表qtlsdmx（交易明细表）
  字段如下：
  • Id：明细ID
  • pid：小票ID（Receipt Id）
  • qrrq：交易Unix时间戳
  • sl：商品购买数量
  • je：商品行总金额（无需额外乘以数量）
  • spmc：商品名称
  • zdcx_ids：关联促销ID，多值以英文逗号分隔
    表内置5条样例数据。
• 表zdcxd（促销主表，主键Id关联qtlsdmx.zdcx_ids）
  字段如下：
  • Id：促销ID（Promo Id）
  • hdmc：促销名称
    表内置167、175、177、179四个促销ID及对应名称。
• 表qtlsd（小票主表，主键Id关联qtlsdmx.pid）
  字段如下：
  • Id：小票ID（Receipt Id）
  • zddm：所属门店名称
    表内置1、2、3三个小票ID及对应门店信息。

问题背景 #

需基于上述三张表开展促销效果数据分析，核心难点为qtlsdmx.zdcx_ids字段以逗号分隔存储多个促销ID，需按单个促销ID维度分组统计。
此前尝试拆分zdcx_ids生成拆分后促销ID列的方案，会造成明细记录重复、主键ID不唯一，导致数量、金额等指标统计错误。
当前表数据量超800万条，查询实现需避免使用IN、LIKE "%175%"这类低性能写法，降低查询耗时。

预期统计口径 #

• 输出1：按促销ID维度统计关联的交易明细行数，无关联记录的促销ID计数为0。样例校验值：促销167对应计数5、175对应计数2、177/179对应计数0。
• 输出2：按促销ID维度统计仅直接关联该促销的明细行je金额总和，无关联则金额为0。样例校验值：促销167对应总金额727、175对应总金额212.1、177/179对应总金额0。
• 输出3：按促销ID维度统计，若同一张小票（同pid）下任意明细关联该促销，则汇总该小票下所有明细行的je金额总和，无关联则金额为0。样例校验值：促销167、175均对应总金额727、177/179对应总金额0。

原有代码问题 #

原有实现全部存在性能缺陷与逻辑错误：

1. 时间条件直接在qrrq字段套用FROM_UNIXTIME()函数，导致该字段索引无法生效，全表扫描性能极差。
2. 使用LIKE '%xxx%'做促销ID匹配，既会出现ID误匹配（如促销175会匹配到1751、2175这类ID），又无法走索引。
3. 使用IN子查询关联，执行效率低，且输出2、输出3的代码逻辑完全重复，和统计口径不匹配。

原有错误代码如下：

-- 输出1原有错误代码
SET @tDate := '2022-05-29';
SELECT COUNT(*) FROM (
    SELECT COUNT(*) FROM qtlsdmx
        WHERE FROM_UNIXTIME(qrrq) >= @tDate AND FROM_UNIXTIME(qrrq) < DATE_ADD(@tDate, INTERVAL 1 DAY)
                and zdcx_ids like '%175%' group by pid 
) a ;

-- 输出2原有错误代码
select sum(je) 
        from qtlsdmx 
    where FROM_UNIXTIME(qrrq) >= @tDate and FROM_UNIXTIME(qrrq) < DATE_ADD(@tDate, INTERVAL 1 DAY) 
                and pid in (select pid from qtlsdmx where zdcx_ids like '%175%');

-- 输出3原有错误代码
SET @tDate := '2022-05-29';  
SELECT SUM(je) AS TOTALPRICE
FROM qtlsdmx 
WHERE FROM_UNIXTIME(qrrq) >= @tDate AND FROM_UNIXTIME(qrrq) < DATE_ADD(@tDate, INTERVAL 1 DAY)
and pid in (select pid from qtlsdmx where zdcx_ids like '%175%');

高性能实现方案 #

前置优化：提前将日期转换为Unix时间戳范围，直接用时间戳字段做范围匹配，完全触发qrrq字段索引；使用FIND_IN_SET()做逗号分隔字段的精准匹配，避免LIKE的误匹配问题，性能远高于模糊查询。
建议提前为qtlsdmx表创建(qrrq, pid, zdcx_ids, je)联合索引，覆盖所有查询字段，避免回表。

输出1实现：按促销ID统计关联明细行数 #

SET @tDate := '2022-05-29';
SET @startTs := UNIX_TIMESTAMP(@tDate);
SET @endTs := UNIX_TIMESTAMP(DATE_ADD(@tDate, INTERVAL 1 DAY));

SELECT 
  z.Id AS promo_id,
  z.hdmc AS promo_name,
  COUNT(q.Id) AS related_detail_count
FROM zdcxd z
LEFT JOIN qtlsdmx q 
  ON q.qrrq >= @startTs AND q.qrrq < @endTs
  AND FIND_IN_SET(z.Id, q.zdcx_ids)
GROUP BY z.Id, z.hdmc;

逻辑说明：左连接促销主表保证无关联的促销也会返回结果，关联阶段直接过滤时间范围、匹配促销ID，不会产生重复计数，无关联时自然返回计数0。

输出2实现：按促销ID统计直接关联明细金额总和 #

SET @tDate := '2022-05-29';
SET @startTs := UNIX_TIMESTAMP(@tDate);
SET @endTs := UNIX_TIMESTAMP(DATE_ADD(@tDate, INTERVAL 1 DAY));

SELECT 
  z.Id AS promo_id,
  z.hdmc AS promo_name,
  IFNULL(SUM(q.je), 0) AS direct_related_amount
FROM zdcxd z
LEFT JOIN qtlsdmx q 
  ON q.qrrq >= @startTs AND q.qrrq < @endTs
  AND FIND_IN_SET(z.Id, q.zdcx_ids)
GROUP BY z.Id, z.hdmc;

逻辑说明：与输出1逻辑一致，仅将计数改为汇总直接关联明细行的je字段，用IFNULL处理无关联场景，返回0值。

输出3实现：按促销ID统计关联小票全量金额总和 #

SET @tDate := '2022-05-29';
SET @startTs := UNIX_TIMESTAMP(@tDate);
SET @endTs := UNIX_TIMESTAMP(DATE_ADD(@tDate, INTERVAL 1 DAY));

SELECT 
  z.Id AS promo_id,
  z.hdmc AS promo_name,
  IFNULL(SUM(q_all.je), 0) AS receipt_total_amount
FROM zdcxd z
-- LATERAL关联支持引用外层表字段，先筛选出所有关联当前促销的去重小票ID
LEFT JOIN LATERAL (
  SELECT DISTINCT pid 
  FROM qtlsdmx 
  WHERE qrrq >= @startTs AND qrrq < @endTs
    AND FIND_IN_SET(z.Id, zdcx_ids)
) related_p ON 1=1
-- 再关联这些小票下的所有明细行汇总金额
LEFT JOIN qtlsdmx q_all 
  ON q_all.pid = related_p.pid
  AND q_all.qrrq >= @startTs AND q_all.qrrq < @endTs
GROUP BY z.Id, z.hdmc;

长期优化建议：对于多值关联场景，建议新增中间关联表qtlsdmx_promo_rel(detail_id, promo_id)存储明细与促销的一对一对应关系，完全替代逗号分隔字段存储，查询性能可提升10倍以上。

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