如何存储与表示基于contacts表用户筛选条件的动态子组？

我们有一张contacts表，结构如下：

我们需要支持用户创建动态分组，分组本质是预定义的筛选条件，例如：

SELECT * FROM contacts
WHERE last_appointment_at > '2023-02-01' AND status IN ('HOT', 'WARM');

理想状态下，分组与联系人的关联需要动态实时更新：当联系人的status、last_appointment_at等字段变更时，自动加入或退出对应分组。

我们最初的方案是用contacts_groups关联表作为缓存，每小时清空并重新计算关联关系，但这种方式无法实现实时性。请问有没有更优的方案？

以下是几种不同场景下的最优方案，各有优劣：

1. 数据库视图（完全实时，无缓存） #

直接将分组的筛选条件定义为数据库视图，例如针对上述示例分组创建视图：

CREATE VIEW group_high_value_contacts AS
SELECT id AS contact_id, 1 AS group_id
FROM contacts
WHERE last_appointment_at > '2023-02-01' AND status IN ('HOT', 'WARM');

• 优点：完全实时，无需维护任何缓存表，联系人字段变更后，视图查询结果立即更新；实现成本极低，仅需创建视图。
• 缺点：如果分组筛选条件复杂（多表关联、聚合）或contacts表数据量极大，视图查询性能会显著下降，尤其是高频查询分组时。
• 适用场景：数据量较小、分组查询频率不高、对实时性要求极高的场景。

2. 触发器驱动的实时关联表 #

将contacts_groups从缓存表改为实时维护的关联表，通过数据库触发器实现自动更新：

1. 先创建groups表存储分组定义：

   CREATE TABLE groups (
       group_id INT PRIMARY KEY,
       filter_condition TEXT NOT NULL -- 存储筛选条件，例如"last_appointment_at > '2023-02-01' AND status IN ('HOT', 'WARM')"
   );
   

2. 为contacts表创建触发器，当status、last_appointment_at等分组依赖字段变更时，自动检查所有分组的筛选条件，更新contacts_groups：

   -- 示例触发器逻辑（以PostgreSQL为例）
   CREATE OR REPLACE FUNCTION update_contact_groups()
   RETURNS TRIGGER AS $$
   BEGIN
       -- 删除旧关联
       DELETE FROM contacts_groups WHERE contact_id = NEW.id;
       -- 插入符合条件的新关联
       INSERT INTO contacts_groups (contact_id, group_id)
       SELECT NEW.id, group_id
       FROM groups
       WHERE (NEW.status, NEW.last_appointment_at) IN (
           SELECT status, last_appointment_at FROM contacts WHERE id = NEW.id AND filter_condition
       );
       RETURN NEW;
   END;
   $$ LANGUAGE plpgsql;
   
   CREATE TRIGGER trigger_contact_update
   AFTER UPDATE OF status, last_appointment_at ON contacts
   FOR EACH ROW EXECUTE FUNCTION update_contact_groups();
   

• 优点：关联表数据完全实时，查询分组时直接查contacts_groups，性能最优；无需额外架构组件。
• 缺点：触发器会增加contacts表写操作的开销，尤其是当分组数量较多时，每次更新需要遍历所有分组检查条件；如果筛选条件复杂，触发器执行效率会下降。
• 适用场景：分组数量不多、contacts表写操作频率适中、对查询性能要求极高的场景。

3. CDC变更捕获+异步实时处理 #

通过CDC（变更数据捕获）工具捕获contacts表的变更事件，异步处理并更新contacts_groups：

1. 用CDC工具（如Debezium、MySQL Binlog Consumer）监听contacts表的INSERT/UPDATE/DELETE事件，将事件发送到消息队列（如Kafka）。
2. 编写消费端服务，读取消息队列中的变更事件，根据groups表中的筛选条件判断联系人是否属于各个分组，实时更新contacts_groups表。

• 优点：完全实时，且不会阻塞contacts表的写操作（异步处理）；支持大规模分组和高并发写操作。
• 缺点：架构复杂度高，需要引入CDC、消息队列等组件，维护成本较高；需要编写额外的消费端业务逻辑。
• 适用场景：数据量极大、分组数量多、contacts表写操作频繁的场景。

4. 物化视图（准实时，平衡性能与维护） #

如果数据库支持物化视图（如PostgreSQL、Oracle），可以将分组定义为物化视图，并配置自动刷新：

-- PostgreSQL示例：创建物化视图
CREATE MATERIALIZED VIEW group_high_value_contacts AS
SELECT id AS contact_id, 1 AS group_id
FROM contacts
WHERE last_appointment_at > '2023-02-01' AND status IN ('HOT', 'WARM');

-- 设置定时刷新（如每分钟刷新一次）
REFRESH MATERIALIZED VIEW CONCURRENTLY group_high_value_contacts;

• 优点：查询性能接近关联表，同时无需手动维护缓存；支持增量刷新（如PostgreSQL的CONCURRENTLY），避免锁表。
• 缺点：不是完全实时（刷新间隔内存在延迟）；部分数据库不支持物化视图或增量刷新。
• 适用场景：对实时性要求不是极端严格（允许分钟级延迟）、数据量较大的场景。

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