订阅表触发器价格查询实现及表结构设计合理性咨询

一、完善价格查询语句 #

要获取正确的产品价格，你需要通过subscription的customer_id关联到customer表拿到对应的user_id，再结合product和user_price表取到最终价格。这里用COALESCE函数优先使用用户覆盖价，没有覆盖价则用产品原价，具体SQL如下：

方式1：子查询关联用户ID #

SELECT COALESCE(u.price, p.price) INTO var_product_price
FROM `product` p
LEFT JOIN `user_price` u 
  ON u.`product_id` = p.`product_id` 
  AND u.`user_id` = (SELECT `user_id` FROM `customer` WHERE `customer_id` = NEW.customer_id)
WHERE p.`product_id` = NEW.product_id;

方式2：多表JOIN关联 #

如果觉得子查询可读性差，也可以用多表连接的方式：

SELECT COALESCE(u.price, p.price) INTO var_product_price
FROM `subscription` s
JOIN `customer` c ON s.`customer_id` = c.`customer_id`
JOIN `product` p ON s.`product_id` = p.`product_id`
LEFT JOIN `user_price` u 
  ON u.`product_id` = p.`product_id` 
  AND u.`user_id` = c.`user_id`
WHERE s.`customer_id` = NEW.customer_id 
  AND s.`product_id` = NEW.product_id;

两种方式核心逻辑一致：先通过NEW.customer_id找到所属的user_id，再匹配user_price中的覆盖价，没有则 fallback 到product表的原价。

二、设计效率分析与优化建议 #

1. 当前设计的效率如何？ #

只要你给关键字段建立合适的索引，当前设计的效率不会有太大问题：

• 确保customer表的customer_id是主键（通常默认会设为主键，查询速度极快）；
• 给user_price表建立复合索引(product_id, user_id)，这样关联查询时能快速定位到用户的覆盖价；
• product表的product_id作为主键，查询原价也会是毫秒级的。

但如果你的subscription表数据量极大，且触发器执行频率很高（比如每秒上千次订阅操作），每次触发器都要关联customer表会产生额外的IO开销，长期运行可能成为性能瓶颈。

2. 是否应该在subscription表中存储user_id？ #

这是典型的空间换时间的权衡，需要结合你的业务场景判断：

• ✅ 适合添加的场景：

  • 业务中需要频繁通过subscription查询价格，或者触发器执行非常频繁；
  • customer和user是一对一绑定关系，且customer.user_id几乎不会变更（比如用户注册后绑定客户，后续不会修改归属）。
    添加user_id后，触发器可以直接用NEW.user_id关联user_price，省去了查询customer表的步骤，能显著提升触发器执行效率。

• ❌ 不适合添加的场景：

  • customer.user_id可能频繁变更（比如客户归属的用户需要转移），此时你需要额外在customer表添加触发器，当user_id变更时同步更新所有关联的subscription记录，维护成本会大幅增加，还可能出现数据不一致的风险。

总结建议 #

1. 先给相关表建立合适的索引，验证当前查询的性能是否能满足业务需求；
2. 如果性能确实存在瓶颈，且customer.user_id变更频率极低，再考虑在subscription表中添加user_id字段，并做好数据一致性维护。

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