TLG Search数据库设计分析：从核心表到语义搜索的架构实践

在TLG Search的系统设计中，数据库是支撑实时消息检索、语义匹配、多媒资管理的核心底层。本文将通过拆解项目中的 7 张核心表（含 1 个视图），分析其结构设计、字段用途及表间关系，揭示如何通过数据库设计满足高效搜索、多媒资关联、统计分析的需求。

一、数据库整体架构概述

该数据库的设计围绕 “聊天会话-消息-多媒资” 的核心逻辑展开，通过关系型表存储结构化数据，向量字段支持语义搜索，视图优化统计查询，最终实现“快速检索、精准匹配、便捷管理”的目标。整体架构如图所示：

joined_chats（聊天会话） ←─ 1:N ── chat_messages（聊天消息） ── 1:N ── photos（图片）
                                      chat_messages ── 1:N ── stickers（贴纸）
                                      chat_messages ── 1:N ── recent_sent_stickers（最近发送贴纸）
sticker_packs（贴纸包） ←─ 1:N ── stickers（贴纸）

二、核心表结构与设计意图

以下按重要性排序，逐一分析每张表的结构、字段用途及设计逻辑。

1. ​joined_chats​​ ：聊天会话主表

作用：记录机器人加入的所有 TLG 聊天会话（用户、群组、频道），是所有聊天相关数据的入口。
结构：

字段名类型约束用途说明
​id​uuid主键会话唯一标识（自动生成）
​platform​text非空、默认空平台标识（如TLG，支持多平台扩展）
​chat_id​text非空、唯一TLG 聊天 ID（如用户 ID、群组 ID），核心关联键
​chat_name​text非空、默认空聊天名称（如用户名、群组名）
​chat_type​text非空、默认user会话类型（user​：私聊；channel​：频道；group：群组），用于业务逻辑区分
​created_at​bigint非空、默认当前时间会话创建时间（机器人加入时间）
​updated_at​bigint非空、默认当前时间会话信息更新时间（如群名修改）

设计亮点：

  • 唯一约束：chat_id设为唯一，确保每个 TLG 聊天会话在系统中只有一条记录。
  • 多平台支持：platform​字段预留了扩展空间（如未来支持 WhatsApp、Discord），符合可扩展性设计原则。
  • 类型区分：chat_type字段区分会话类型，方便后续针对不同类型（如群组 vs 私聊）做差异化处理（如权限控制、消息展示）。

2. ​chat_messages​​ ：聊天消息核心表

作用：存储所有 TLG 聊天消息的结构化数据，是搜索功能的核心数据源。
结构：

字段名类型约束用途说明
​id​uuid主键消息唯一标识（自动生成）
​platform​text非空、默认空平台标识（同joined_chats）
​platform_message_id​text非空、默认空TLG 原生消息 ID（如message_id），用于关联平台消息
​from_id​text非空、默认空发送者 TLG ID
​from_name​text非空、默认空发送者名称（如用户名、群昵称）
​in_chat_id​text非空、默认空所属聊天会话 ID（关联joined_chats.chat_id）
​content​text非空、默认空消息文本内容（如文字、链接、命令）
​is_reply​boolean非空、默认false是否为回复消息
​reply_to_name​text非空、默认空回复对象名称
​reply_to_id​text非空、默认空回复对象 TLG ID
​platform_timestamp​bigint非空、默认0TLG 消息发送时间戳（毫秒级）
​created_at​bigint非空、默认当前时间消息入库时间
​updated_at​bigint非空、默认当前时间消息更新时间（如内容修改）
​deleted_at​bigint非空、默认0软删除标记（0​表示未删除，非0表示删除时间）
​content_vector_1536​vector(1536)可选消息内容的 1536 维语义向量（如 OpenAI text-embedding-3-small）
​content_vector_1024​vector(1024)可选消息内容的 1024 维语义向量（如开源模型all-MiniLM-L6-v2）
​content_vector_768​vector(768)可选消息内容的 768 维语义向量（如text-embedding-ada-002）
​jieba_tokens​jsonb非空、默认[]中文分词结果（如[" TLG ", " 搜索 "]），用于关键词搜索

设计亮点：

  • 核心关联：in_chat_id​关联joined_chats.chat_id，确保每条消息属于正确的聊天会话。

  • 多维度搜索支持：

    • 语义搜索：存储 3 种不同维度的向量（1536/1024/768），适配不同模型的语义匹配需求（如 OpenAI 模型 vs 开源模型）；
    • 关键词搜索：jieba_tokens存储中文分词结果，避免每次搜索都重新分词，提升查询效率。

  • 软删除：deleted_at字段实现软删除，保留历史数据，支持数据恢复和审计。

  • 平台兼容性：platform​和platform_message_id​字段支持多平台扩展（如未来接入 WhatsApp，只需新增platform="whatsapp"的记录）。

3. ​chat_message_stats​​ ：聊天统计视图

作用：通过预聚合减少实时统计的计算压力，快速获取每个聊天会话的关键统计信息（如消息数量、最早/最新消息时间）。
结构（视图字段，无物理存储）：

字段名类型用途说明
​platform​text平台标识（同joined_chats）
​chat_id​text聊天会话 ID（关联joined_chats.chat_id）
​chat_name​text聊天名称（同joined_chats.chat_name）
​message_count​integer该聊天的消息总数（COUNT(cm.id)）
​first_message_id​text该聊天的第一条消息的platform_message_id​（MIN(cm.platform_message_id)）
​first_message_at​bigint该聊天的第一条消息时间（MIN(cm.created_at)）
​latest_message_id​text该聊天的最新消息的platform_message_id​（MAX(cm.platform_message_id)）
​latest_message_at​bigint该聊天的最新消息时间（MAX(cm.created_at)）

设计逻辑：
通过joined_chats​和chat_messages​的LEFT JOIN，聚合每个聊天会话的消息统计数据。例如：

SELECT
  jc.platform,
  jc.chat_id,
  jc.chat_name,
  COUNT(cm.id)::int AS message_count,
  MIN(cm.platform_message_id) AS first_message_id,
  MIN(cm.created_at) AS first_message_at,
  MAX(cm.platform_message_id) AS latest_message_id,
  MAX(cm.created_at) AS latest_message_at
FROM joined_chats jc
LEFT JOIN chat_messages cm ON jc.chat_id = cm.in_chat_id
GROUP BY jc.platform, jc.chat_id, jc.chat_name

价值：避免每次查询聊天列表时都要扫描chat_messages的全表，提升前端展示（如聊天列表）的响应速度。

4. ​photos​​ ：图片消息表

作用：存储聊天消息中的图片内容，支持图片的二进制存储、路径管理、语义搜索。
结构：

字段名类型约束用途说明
​id​uuid主键图片唯一标识
​platform​text非空、默认空平台标识
​file_id​text非空、默认空TLG 图片文件 ID（如file_id）
​message_id​uuid可选关联chat_messages.id（该图片属于哪条消息）
​image_bytes​bytea可选图片二进制数据（如缩略图，用于快速预览）
​image_path​text非空、默认空图片存储路径（如/storage/photos/xxx.jpg或云存储 URL）
​caption​text非空、默认空图片 caption（消息中的文字说明）
​description​text非空、默认空图片描述（如 OCR 结果、用户输入的标签）
​created_at​bigint非空、默认当前时间图片入库时间
​updated_at​bigint非空、默认当前时间图片更新时间
​description_vector_1536​vector(1536)可选图片描述的 1536 维语义向量（用于图片语义搜索）

设计亮点：

  • 多媒资关联：message_id​关联chat_messages.id​，确保图片与消息的正确对应（如 TLG 消息可带多张图片，photos​表会有多个条目关联同一条message_id）。
  • 存储策略：image_bytes​存储小尺寸二进制数据（如缩略图），image_path​存储原始图片路径，平衡快速预览与存储成本。
  • 图片搜索支持：description_vector_1536存储图片描述的语义向量，支持“通过文字描述找图片”（如“找一张猫的图片”）。

5. ​stickers​​ ：贴纸表

作用：存储 TLG 贴纸的元数据，支持贴纸的语义搜索、快速访问。
结构：

字段名类型约束用途说明
​id​uuid主键贴纸唯一标识
​platform​text非空、默认空平台标识
​name​text非空、默认空贴纸名称
​emoji​text非空、默认空贴纸对应的 emoji（如😀）
​label​text非空、默认空贴纸标签（如表情​、动物），用于分类
​file_id​text非空TLG 贴纸文件 ID
​sticker_bytes​bytea可选贴纸二进制数据（如 WebP 格式）
​sticker_path​text非空、默认空贴纸存储路径
​description​text非空、默认空贴纸描述（如“一个微笑的猫”）
​created_at​bigint非空、默认当前时间贴纸入库时间
​updated_at​bigint非空、默认当前时间贴纸更新时间
​description_vector_1536​vector(1536)可选贴纸描述的 1536 维语义向量（用于贴纸语义搜索）

设计亮点：

  • 语义搜索：description_vector_1536存储贴纸描述的语义向量，支持“通过文字找贴纸”（如“找一个开心的表情”）。
  • 快速访问：sticker_bytes​存储小尺寸二进制数据，sticker_path​存储原始路径，平衡快速加载与存储成本。

6. ​recent_sent_stickers​​ ：最近发送贴纸表

作用：记录用户最近发送的贴纸，提升常用贴纸的访问速度（如“最近使用”列表）。
结构：

字段名类型约束用途说明
​id​uuid主键记录唯一标识
​sticker_id​uuid非空关联stickers.id（最近发送的贴纸）
​created_at​bigint非空、默认当前时间发送时间
​updated_at​bigint非空、默认当前时间更新时间

设计逻辑：
每次用户发送贴纸时，向该表插入一条记录（关联stickers.id​）。查询“最近使用”列表时，只需按created_at​降序查询该表，无需扫描stickers全表，提升响应速度。

7. ​sticker_packs​​ ：贴纸包表

作用：存储 TLG 贴纸包的元数据，用于分类和组织贴纸（如“我的贴纸包”）。
结构：

字段名类型约束用途说明
​id​uuid主键贴纸包唯一标识
​platform​text非空、默认空平台标识
​platform_id​text非空、默认空TLG 贴纸包 ID（如stickerset_id）
​name​text非空、默认空贴纸包名称
​description​text非空、默认空贴纸包描述
​created_at​bigint非空、默认当前时间贴纸包入库时间
​updated_at​bigint非空、默认当前时间贴纸包更新时间

设计亮点：

  • 分类管理：通过贴纸包（sticker_packs​）组织贴纸（stickers），方便用户按包浏览（如“打开‘动物贴纸包’”）。
  • 平台兼容性：platform​和platform_id字段支持多平台扩展（如未来接入 WhatsApp 贴纸包）。

三、表间关系深度解析

表间关系是数据库设计的灵魂，直接影响数据的一致性和查询效率。以下是核心表的关系梳理：

1. ​joined_chats​​与​​chat_messages​​ ：一对多

  • 关联字段：joined_chats.chat_id​ ↔ chat_messages.in_chat_id
  • 逻辑：一个聊天会话（joined_chats​）包含多条消息（chat_messages）。
  • 示例：查询“群聊123456​的所有消息”，只需通过in_chat_id = '123456'​过滤chat_messages表。

2. ​chat_messages​​与​​photos​​ ：一对多

  • 关联字段：chat_messages.id​ ↔ photos.message_id
  • 逻辑：一条消息（chat_messages​）可以包含多张图片（photos）。
  • 示例：查询“消息uuid-xxx​的所有图片”，只需通过message_id = 'uuid-xxx'​过滤photos表。

3. ​chat_messages​​与​​stickers​​ ：一对多

  • 关联字段：chat_messages.id​ ↔ stickers.message_id​（注：stickers​表未显式存储message_id​，但可通过recent_sent_stickers间接关联）
  • 逻辑：一条消息（chat_messages​）可以包含多个贴纸（stickers）。
  • 示例：查询“最近发送的贴纸”，通过recent_sent_stickers.sticker_id​关联stickers.id​，再关联chat_messages.id获取消息上下文。

4. ​sticker_packs​​与​​stickers​​ ：一对多

  • 关联字段：sticker_packs.id​ ↔ stickers.sticker_pack_id​（注：stickers​表未显式存储sticker_pack_id​，但可通过platform_id关联）
  • 逻辑：一个贴纸包（sticker_packs​）包含多个贴纸（stickers）。
  • 示例：查询“贴纸包pack-xxx​的所有贴纸”，只需通过sticker_pack_id = 'pack-xxx'​过滤stickers表。

5. ​recent_sent_stickers​​与​​stickers​​ ：多对一

  • 关联字段：recent_sent_stickers.sticker_id​ ↔ stickers.id
  • 逻辑：多条最近发送记录（recent_sent_stickers​）可以关联同一个贴纸（stickers）。
  • 示例：查询“最近发送的 10 个贴纸”，通过recent_sent_stickers.created_at​降序排序，再关联stickers表获取贴纸详情。

四、数据库设计的优化点

该数据库设计在性能、扩展性、可维护性上做了以下优化：

1. 向量字段与语义搜索优化

  • 多维度向量：chat_messages表存储 3 种不同维度的向量（1536/1024/768），适配不同模型的语义匹配需求（如 OpenAI 模型 vs 开源模型）。
  • 向量索引：为向量字段创建hnsw​索引（如chat_messages_content_vector_1536_index​），提升语义搜索的查询速度（hnsw是高维向量的高效索引结构）。

2. 预聚合视图优化统计查询

  • ​​chat_message_stats​​视图：通过预聚合joined_chats​和chat_messages的统计数据，避免实时计算（如消息数量、最早/最新消息时间），提升前端聊天列表的响应速度。

3. 索引优化

  • 唯一索引：为joined_chats.chat_id​、chat_messages.platform_message_id​、photos.file_id等字段创建唯一索引，确保数据唯一性，同时提升查询效率。
  • 普通索引：为chat_messages.in_chat_id​、photos.message_id​、recent_sent_stickers.sticker_id等关联字段创建普通索引，加速关联查询。

4. 软删除设计

  • ​​chat_messages.deleted_at​​字段：实现软删除，保留历史数据，支持数据恢复和审计（如“查询已删除的消息”）。

五、总结：数据库设计的核心思想

该数据库设计的核心目标是支撑高效的搜索功能，同时兼顾扩展性和可维护性。其设计思想可总结为以下几点：

  1. 以聊天会话为核心：joined_chats表作为所有聊天相关数据的入口，确保数据的一致性和关联性。
  2. 多维度搜索支持：通过向量字段（语义搜索）、分词结果（关键词搜索）、元数据（时间/类型），满足不同场景的搜索需求。
  3. 预聚合与索引优化：通过视图（chat_message_stats​）和索引（hnsw/普通索引），提升统计查询和关联查询的效率。
  4. 多平台扩展：platform字段预留了多平台支持的空间，符合未来业务扩展的需求。

六、学习启示

对于搜索类项目的数据库设计，可借鉴以下经验：

  • 语义搜索：存储向量字段并创建hnsw索引，是实现精准语义匹配的关键。
  • 多媒资管理：将图片、贴纸等多媒资与消息关联，通过二进制存储和路径管理平衡性能与成本。
  • 统计优化：通过预聚合视图减少实时计算压力，提升前端展示效率。
  • 扩展性：预留多平台、多模型的扩展空间，避免后续重构的成本。

通过以上分析，我们可以看到，该数据库设计不仅满足了当前 TLG Search工具的需求，还为未来的扩展（如多平台支持、更复杂的搜索功能）奠定了基础，是一个兼顾实用性与扩展性的优秀案例。

您可能感兴趣的文章

发现更多精彩内容

评论