skip to content
Liu Yang's Blog

LangAC：实训项目复盘——一个「零 SQL」的英语学习平台

/ 16 min read

View more blogs with the tag development , View more blogs with the tag java , View more blogs with the tag vue , View more blogs with the tag devops
Table of Contents

大四第八学期实训期间，我和三位同学组成四人团队，历时约三个月，设计并实现了 LangAC（Language Academy 缩写）——一个面向英语学习的多角度学习平台。项目包含用户端移动 Web、管理员后台、完整后端服务，以及一套跨地域的 CI/CD 自动化部署链路。

这篇文章是项目答辩后的技术复盘，记录我认为值得一提的设计决策和实现细节。

项目概览

LangAC 的核心是「多角度」学习：不只是背单词，还提供听力音乐、英文短文阅读、笔记、社区互动等多个学习维度。主要模块：

  • 单词学习：词本管理、每日记忆任务、打卡、收藏
  • 听力音乐：上传/播放音频，VIP 权限控制
  • 英文短文：阅读、评论（带 IP 归属地）、收藏、笔记
  • 社交：好友系统、实时聊天、P2P 视频通话
  • AI 对话：集成 OpenAI 兼容接口，基于学习内容给出建议
  • 管理后台：用户管理、内容审核、数据看板（折线图/柱状图/饼图）

技术选型：

层级技术
后端Spring Boot、MyBatis-Plus、MyBatis-Plus-Join、JWT、JustAuth
存储MySQL、Redis、MongoDB、腾讯云 COS
实时通信WebSocket、PeerJS（WebRTC）
前端（用户端）Vue 3、Vant UI、Pinia
前端（管理端）Vue 3、Element Plus
部署Docker、Docker Compose、Nginx、Jenkins、SSL/CDN

技术亮点

1. 零业务 SQL

这是整个项目最有意思的约束：业务代码中没有一行手写 SQL。

  • 单表操作：MyBatis-Plus 的 Lambda 封装
  • 多表关联查询：MyBatis-Plus-Join 插件（MPJLambdaWrapper）
  • 无参数复杂聚合（如”查询每个词本的总单词数”）：MySQL 视图
  • 有参数的超复杂查询（如”查询指定用户在指定词本中已记忆/未记忆/今日未复习的单词”）：存储过程

以”查询词本同时显示其总词数”为例，后端只需调用视图，而视图内部用 GROUP BY + COUNT 聚合实现，业务层完全不感知 SQL 复杂度。好友功能、消息已读状态等复杂逻辑同样全部通过存储过程实现。

这个约束倒逼我们把 SQL 优化压到数据库层，也让 Service 层代码可读性更高。

2. 自研单词记忆算法

每日记忆任务不是简单地从词本头部取词，而是混合两类单词：从未记忆的新词和已记忆但今天尚未复习的旧词，按 1:1 交替排列，并对每类打乱顺序：

while (j > 0 && (!unMemoryWords.isEmpty() || !memoryWords.isEmpty())) {
    List<Memory> currList = (i == 0) ? unMemoryWords : memoryWords;
    if (!currList.isEmpty()) {
        Memory item = currList.get(0);
        currList.remove(0);
        finalWords.add(item);
        j--;
    }
    i = (i + 1) % 2;
}

算法完全在服务端执行，前端只拿结果。后续如果要接入机器学习模型做个性化推荐，只需在 Memory 或 Word 表里增加特征列，替换这段逻辑即可，接口不变。

3. WebSocket 集成 JWT + 多类型消息

WebSocket 连接通过 URL 路径参数传 Token（/chat/{token}），服务端在 @OnOpen 时解析 JWT 获取用户 ID，并完成以下动作：

  1. 推送该用户的所有历史消息（含未读标记）
  2. 查询在线好友列表并推送

消息格式统一为类似 REST 的 WebSocketResult 结构，通过 code 字段区分消息类型（历史消息、实时消息、在线好友、视频通话信令），前端按 code 分派处理逻辑。

消息功能支持：未读消息查看、基于时间戳的已读状态、消息持久化（MySQL）、单向删除。

4. P2P 视频通话

基于 PeerJS（WebRTC 封装），信令通过已有的 WebSocket 信道传递，后端不参与媒体流转发，纯 P2P。WebSocket 层增加了视频通话信令的发起/接受/拒绝状态码，前端用同一个 WebSocket 连接同时处理聊天和视频控制。

5. 多区域自动化部署

项目最终部署在三个节点：

  • 阿里云杭州：主数据中心
  • 阿里云香港：面向用户的资源访问（规避大陆未备案域名无法 HTTPS 回调的问题）
  • DigitalOcean 东京 + 美国：Jenkins CI/CD 计算节点、Git 管理

大陆和香港两套后端通过 CI/CD 工具同步自动化部署，推送代码后由 Jenkins 触发 Maven 编译、Docker 镜像构建、docker-compose 编排启动，整套流程一键完成。Nginx 在香港节点做反向代理和负载均衡，全程 HTTPS（Cloudflare + 阿里云 SSL）。

# docker-compose.yml 节选
langac_backend:
  build:
    context: ./LangAC_backend
    dockerfile: Dockerfile
  environment:
    - SPRING_REDIS_HOST=lang_redis
    - SPRING_DATASOURCE_URL=jdbc:mysql://lang_mysql:3306/langac
    - SPRING_PROFILES_ACTIVE=compose

前端同样容器化，通过环境变量注入后端地址，镜像可直接在不同环境复用。

6. 三库混合存储

数据类型存储原因
用户、单词、词本、好友MySQL关系型，需要事务和复杂关联
短文、笔记、评论MongoDB文档结构灵活，评论支持按 IP 归属地查询
点赞计数、在线人数、接口限流Redis高频读写，原子计数
音频、图片腾讯云 COS对象存储，CDN 加速

评论功能使用 MongoDB 存储，同时记录请求 IP，配合 IP 解析库显示评论者的归属地（省份/国家）。

7. OAuth 第三方登录

通过 JustAuth 集成了 GitHub 和华为账号的 OAuth 登录，在香港节点处理回调（解决大陆回调域名的问题）。

8. 数据库设计细节

数据库设计阶段用的是 DBML（Database Markup Language）——一种专门用来描述表结构的标记语言，可以用版本控制追踪 schema 的变更历史，也可以直接导出 SQL DDL 或渲染成 ER 图。相比直接在数据库里改表、靠文档对齐结构，DBML 让数据库设计本身也变成了可迭代、可复用的代码。

好友关系单表设计——和微信本地 SQLite 撞上了

tb_friend 只有一张表，每对好友只存一行（user_id_a, user_id_b），不区分谁先加谁。查询时用 CASE WHEN 在 SQL 层把方向抹平：

SELECT
    CASE WHEN user_id_a = uid THEN user_id_b
         ELSE user_id_a END AS friend_id,
    CASE WHEN user_id_a = uid THEN friend_b_alias
         ELSE friend_a_alias END AS alias
FROM tb_friend
WHERE friend_is_friend = 1
  AND (user_id_a = uid OR user_id_b = uid)

设计完成后偶然翻到微信本地数据库（SQLite）的逆向分析资料，发现微信的联系人存储逻辑和这个如出一辙——rcontact 表里每个联系人只存一条记录，会话标识也以其中一方的 ID 为 key，不存冗余的反向关系。一个学生项目和世界级 IM 产品在这个问题上选了同一个解法，感觉还是挺有意思的。

本质原因是：双向好友关系是一个无向图中的边，存两行是在用有向边表达无向边，查询时还要去重或 UNION，不如一开始就用单行 + 查询时解方向。

双 EXISTS 定义「待学单词」

「指定词本中还没背过的单词」用两个半连接条件叠加：

NOT EXISTS (SELECT * FROM tb_memory WHERE user_id = uid AND word_id = w.word_id)
AND EXISTS  (SELECT * FROM tb_wordlist_word WHERE list_id = lid AND word_id = w.word_id)

比 LEFT JOIN ... IS NULL 可读性更好，两个条件和业务语义一一对应，看 SQL 就能还原出业务规则。

补零月份视图

管理后台折线图要展示近 6 个月的打卡/注册趋势，但某些月份可能确实没数据，直接 GROUP BY 会出现断点。视图用 UNION 预先生成 6 个月的锚点行，再 LEFT JOIN 实际数据，保证每个月都有一行（数量为 0 时也不会消失）：

(SELECT date_format(now() - INTERVAL 6 MONTH, '%Y-%m')
 UNION SELECT date_format(now() - INTERVAL 5 MONTH, '%Y-%m')
 ...
) AS m
LEFT JOIN tb_card c ON date_format(c.card_date, '%Y-%m') = m.month
GROUP BY m.month

同一个视图用 UNION ALL 把打卡量和注册量合并，用 table_name 字段区分类型，前端一次查询拿到所有图表数据。

memo_update_date 的隐含约定

「今日已复习」没有专门的状态字段，判断逻辑就是 memo_update_date != CURRENT_DATE——每次复习更新这个时间，所以「今天没更新 = 今天没复习」。只要写入侧严格遵守这个约定，读取侧不需要任何额外逻辑。简单，但需要在设计时就把字段语义想清楚。

开发过程回顾

项目从 2023 年 12 月下旬启动，到 2024 年 4 月初答辩，历时约三个月。团队分工：

  • 我（杨柳）：Spring Boot 后端全部、服务器运维、网络架构、Git 分支管理
  • 高烨：用户端移动 Web、MongoDB 相关功能、Redis 缓存
  • 薛晓锋、杨文川：管理员后台前后端

印象比较深的几个决策：

不写计划表。我们没有做细粒度的项目计划，每周会议确定本周目标，当周完成。好处是灵活，功能可以随着学到的新技术随时调整；代价是任务边界模糊，成员水平差距导致协作摩擦较大。

零 SQL 约束是反向倒逼出来的。最初只是不想在代码里写大量 XML Mapper，尝试把复杂查询都转移到数据库层后，发现 Service 层代码干净很多，就坚持下来了。

香港节点的价值。大陆未备案域名无法直接解析，OAuth 回调、HTTPS、某些第三方 API 的 callback 全都需要一个”干净”的节点。香港节点承担了所有需要回调的功能，大陆节点专注数据存储，职责分离比较清晰。

没做完的部分

答辩时还有几个功能没有完全实现：

  • 视频通话前端：P2P 信令已通，但移动端 UI 没打磨
  • RabbitMQ 邮件/短信：后端 MQ 消费者写了一半
  • OAuth 2FA：GitHub OAuth 完成，二步验证没做
  • AI 对话前端：OpenAI 接口已通，前端对话界面没完成

这几个功能的后端接口都已就绪，主要卡在时间和前端资源。

课程项目做过不少，也带过团队，但 LangAC 是第一个我真正想把自己所有积累都压进去的项目——不只是把功能跑通交差，而是尽量让它成为一个真正可用的产品。从产品定义、数据库设计、团队分工、编码实现，到 DevOps 流程、部署上线成真实服务，再到同时优化国内和国际访问链路，整条路走完了一遍。网络架构上做了分流：国内用户走阿里云香港节点（绕过大陆备案限制），国际用户走 Cloudflare——设想的用户群是全球华人英语学习者，两条路都要顺畅。

某种程度上，这是我送给自己的一份毕业前答卷。

产品层面有一个我认为和目前主流商用单词软件（背词达人、墨墨等）真正不同的地方：词是用户自己的。用户直接关联词，可以导入任意词本、分析自己的学习数据，而不是被平台固定的课程和词库绑定。词本可以是 JSON 批量导入的，也可以是用户自己整理的，Memory 表里的每条记录都指向具体的用户和单词，统计和算法基于这个关联来做。这个设计让学习记录真正属于用户，而不是平台。

回头看，「零 SQL」约束和多区域部署是最有意思的两个技术决策，前者让代码更整洁，后者解决了真实的网络问题。如果重来，我会在 WebSocket 负载均衡上投入更多时间——这个问题最终没有完美解决，有状态的 WebSocket 和无状态的 Nginx 负载均衡之间的冲突，答辩时还是一个悬而未决的 TODO。

最后值得说一句：这是 2024 年初写的代码。那时候 LLM 还没有进化成今天这种 Agent 形态，没有 Cursor，没有 AI 帮你生成存储过程、设计表结构、一句话搭出 WebSocket 框架。CASE WHEN 的好友关系、双 EXISTS 的单词过滤、补零月份视图——都是查文档、看资料、一行一行手搓出来的。现在这些东西可能真的是「一句话的事」，但在当时，能想到这些解法、并且把它们跑通，我觉得还是值得记一笔的。

这篇文章写于研二下——该找工作了，是时候把过去做过的东西整理清楚。拖了三年没发，说来也巧，最终是让 Code Agent 帮我把项目翻了一遍、把亮点归纳出来才得以成文。某种意义上，用今天的 AI 工具回顾一个「AI 还不是 Agent 时代」写下的代码，也算是一种对照。