作者：Gundy

1. 場景與目標

UGC 社區典型特點：內容量大、更新快、長尾重。推薦系統要同時兼顧“眼下的即時興趣”和“用户的穩定偏好”，並在一次請求內完成多路候選召回與融合，保證毫秒級延遲。 本文給出一套雙向量用户興趣 + 一次 SQL 多路召回的實踐方案，數據庫層使用 OceanBase 原生向量能力，結構化與向量同庫，避免“兩庫同步/一致性坑”。

2. 為什麼選 OceanBase（直説三點）

一體化：結構化表 + VECTOR 列 + HNSW/IVF 向量索引在同一庫裏，天然支持事務一致性（更新瀏覽計數與短期向量可放在一個事務裏）。

兼容 MySQL 技術棧：接入/運維成本低，遷移平滑。

分佈式彈性：內容庫增長和 QPS 抖動時，水平擴展更從容。

3. 表結構（貼近你 PPT 的建模）

內容表（帖子/短視頻等）

CREATE TABLE posts (
  post_id        BIGINTPRIMARY KEY,
  author_id      BIGINT,
  title          VARCHAR(255),
  content        TEXT,
  created_at     DATETIME,
  status         TINYINT DEFAULT1,          -- 1上線
  pop_7d         FLOATDEFAULT0,            -- 近7日人氣
  content_vector VECTOR(768),                -- 原生向量
  VECTOR KEY idx_vec (content_vector)
    WITH (DISTANCE = COSINE, TYPE = HNSW, M=16, EF_CONSTRUCTION=200, EF_SEARCH=64)
);

用户表（雙向量：短期 + 長期）

CREATE TABLE users (
  user_id            BIGINT PRIMARY KEY,
  short_term_vector  VECTOR(768),  -- 即時興趣（秒級更新）
  long_term_vector   VECTOR(768),  -- 穩定偏好（天級/小時級更新）
  region             VARCHAR(32),
  updated_at         DATETIME
);

行為表（去重/特徵）

CREATE TABLE user_actions (
  user_id  BIGINT,
  post_id  BIGINT,
  action   ENUM('view','like','collect','comment','share'),
  ts       DATETIME,
  PRIMARY KEY(user_id, post_id, action, ts)
);

4. 雙向量興趣：怎麼產出 & 怎麼更新

4.1 訓練與產出（簡述）

長期向量：雙塔/對比學習，將歷史多天行為聚合（mean/attention pooling），日更或小時更。

短期向量：會話級序列（最近 N 次曝光/點擊）用輕量 Transformer/SASRec 產出，實時/秒級更新。

4.2 在線更新

同一事務中更新瀏覽計數與短期向量，避免“計數+畫像不同步”：

BEGIN;
UPDATE posts SET view_count = view_count + 1 WHERE post_id = ?;
UPDATE users SET short_term_vector = ? WHERE user_id = ?;
COMMIT;

短期向量的在線融合

def update_short_term_vector(user_id, post_vec, action):
    w = {'view':0.1, 'like':0.3, 'collect':0.5}.get(action, 0.1)
    new_vec = 0.85 * current_short_vec(user_id) + 0.15 * w * post_vec
    sql("UPDATE users SET short_term_vector=? WHERE user_id=?", [new_vec, user_id])

直觀點：短期追新，長期守穩；兩者同時存在才不會“越推越窄”或“越推越慢”。

5. 一次查詢，多路召回（核心 SQL）

目標：一次請求同時召回“短期興趣近鄰”“長期興趣近鄰”，併疊加新鮮度與人氣，再統一排序。

關鍵點：強過濾先行（上架、時間窗、地域/類目等），不做過濾等着 P95 爆炸。

-- 取出用户雙向量
WITH user_vectors AS (
SELECT short_term_vector AS svec, long_term_vector AS lvec
FROM users WHERE user_id = :uid
),
-- 路1：短期興趣召回（響應快，抓當前關注）
short_pool AS (
SELECT p.post_id, p.title,
         COSINE_SIMILARITY(p.content_vector, uv.svec) AS sim,
         p.created_at, p.pop_7d, 'short'AS src
FROM posts p, user_vectors uv
WHERE p.status=1
    AND p.created_at > DATE_SUB(NOW(), INTERVAL7DAY)           -- 新鮮度強過濾
    ANDNOTEXISTS (SELECT1FROM user_actions ua
                    WHERE ua.user_id=:uid AND ua.post_id=p.post_id) -- 去已看
ORDERBY p.content_vector <-> uv.svec
  LIMIT 200
),
-- 路2：長期興趣召回（穩定偏好）
long_pool AS (
SELECT p.post_id, p.title,
         COSINE_SIMILARITY(p.content_vector, uv.lvec) AS sim,
         p.created_at, p.pop_7d, 'long'AS src
FROM posts p, user_vectors uv
WHERE p.status=1
    AND p.created_at > DATE_SUB(NOW(), INTERVAL30DAY)
ORDERBY p.content_vector <-> uv.lvec
  LIMIT 200
),
-- 融合 + 評分（語義相似 + 新鮮度 + 人氣）
merged AS (
SELECT post_id, title, src,
         (CASE src WHEN'short'THEN0.7ELSE0.3END) * sim      -- 雙向量權重（PPT思路）
         +0.1*LOG(1+ pop_7d)
         +0.2*EXP(- TIMESTAMPDIFF(HOUR, created_at, NOW()) /72.0) AS score
FROM short_pool
UNIONALL
SELECT post_id, title, src,
         (CASE src WHEN'short'THEN0.7ELSE0.3END) * sim
         +0.1*LOG(1+ pop_7d)
         +0.2*EXP(- TIMESTAMPDIFF(HOUR, created_at, NOW()) /72.0) AS score
FROM long_pool
)
SELECT post_id, title, MAX(score) AS final_score
FROM merged
GROUPBY post_id, title
ORDERBY final_score DESC
LIMIT 50;

要點

1.COSINE_SIMILARITY直接用相似度（0~1），不要 distance 再 1-distance，閾值語義清晰。

2.新鮮度、人氣做輕權重加成，防止純語義把“陳年老貼”頂上來。

3.已看過過濾必須在 DB 層完成，避免服務端側再次 Join 帶來不一致。

6. 精排與多樣性（簡化可上線）

精排：先上輕量 GBDT/LightGBM，把 sim_short、sim_long、pop_7d、age 等作為特徵；預算充足再上 DNN。

多樣性：MMR（最大邊際相關性）對相似帖子做懲罰，控制主題/作者/價格帶佔比，避免信息繭房。

業務約束：狀態、合規、黑白名單、廣告混排（統一歸一化，避免量綱衝突）。

7. 性能與運維（踩坑直説）

強過濾先行：status、時間窗、地域/類目 一定要在 KNN 之前過濾，減少參與向量搜索的數據量。

索引參數：HNSW 的 M/EF_SEARCH 做 A/B 壓測（召回@K vs P95）；內容超大時熱冷分層（熱：HNSW；冷：IVF-PQ）。

一致性：用户短期向量更新與行為寫入同事務；模型換代時記得做向量版本與灰度

度量：在線監控 CTR/CVR、P50/P95、短期/長期池貢獻佔比、已看過命中率、新鮮度指標。

8. MVP 路線（兩週能落地）

建表：posts / users / user_actions（如上）。

向量：離線產出 long_term_vector；實時流更新 short_term_vector。

召回：用上面“一次 SQL 多路召回”，直接能跑；

精排：先用 GBDT，支持快速特徵迭代；

監控：打點記錄 sim_top1、來源池（短/長）、延遲、去重率、曝光→點擊漏斗；

迭代：每週校準 HNSW 參數與權重 0.7/0.3、新鮮度衰減係數。

9. 常見問題（按坑給答案）

• Q：只用一個用户向量行不行？

A：不建議。UGC 的興趣是多峯的，單向量容易“偏科”。短期追新 + 長期守穩是你 PPT 的精華。

• Q：為什麼把融合和排序放在數據庫裏？

A：少一次網絡往返 + 數據局部性好，延遲更穩；SQL 清晰可審計，覆盤更容易。

• Q：新內容如何冷啓動？

A：內容向量 + 新鮮度加權 + 小流量探索（ε-greedy/UCB）。有創作者關係的，優先推給關注者的短期近鄰。

結語

UGC 社區的推薦不需要花哨堆棧，核心就三件事：

一是雙向量建模同時覆蓋即時興趣與穩定偏好；

二是用 OceanBase 原生向量把“結構化 + 向量 + 事務”放在同一庫裏；

三是通過 一次 SQL 多路召回把短期池、長期池、時間與人氣統一融合，端到端穩定提速。

根據本文結構化表、SQL 與更新策略上線，即可做出一個快、穩、可迭代的 UGC 個性化推薦系統。

最後為大家推薦這個 OceanBase 開源負責人老紀的公眾號「老紀的技術嘮嗑局」，會持續更新和 #數據庫、#AI、#技術架構 相關的各種技術內容。歡迎感興趣的朋友們關注！

「老紀的技術嘮嗑局」不僅希望能持續給大家帶來有價值的技術分享，也希望能和大家一起為開源社區貢獻一份力量。如果你對 OceanBase 開源社區認可，點亮一顆小星星✨吧！你的每一個Star，都是我們努力的動力。