- 本地nl2sql对应nl2sql-final数据库 - 在线nl2sql对应nl2sql-online数据库 ``` PS D:\wks\nl2sql> .\start_dev.bat ```

## 背景 阅读 .config/skills/nl2sql这个skill，它用于生成 nl2sql项目 阅读 docs/03_业务需求/jnan/需求沟通确认文档_智能问数_V1.0_0907.md ## Qdrant向量库信息 远程Qdrant向量库挂载本地以方便开发 - 在本地windows系统的git bash中执行 - 如果远程Qdrant向量库存在问题，可直接登录 ssh -p 26225 'xt@144.34.185.72' 重启即可 ``` itoracle@9gkkptpfxkjk9ag MINGW64 / $ ssh -f -N \ -L 6333:127.0.0.1:6333 \ -L 6334:127.0.0.1:6334 \ -p 26225 \ -o ServerAliveInterval=30 \ -o ExitOnForwardFailure=yes \ xt@144.34.185.72 itoracle@9gkkptpfxkjk9ag MINGW64 / $ curl -s http://127.0.0.1:6333 {"title":"qdrant - vector search engine","version":"1.19.1","commit":"8a36a08fb6 d475b601ecf488cd766a2e013ede3f"} ```

## 使用PG暂时替代DWS数据库测试 - 使用PG数据库替代DWS数据库进行验证 - PG数据库在ssh xt@39.106.147.125 ，如果数据库存在问题，可直接登录 该服务器重启PG数据库 ``` # PostgreSQL 目标库（用于迁移导入） PG_DST_HOST="39.106.147.125" PG_DST_PORT="5432" PG_DST_USER="postgres" PG_DST_PASS="JptNt_2023" PG_DST_DATABASE="testdb" ```

ssh xt@101.43.140.244

**背景（2026-09-14 最终目录远程实测）** 在 2G 内存 + 3 核 + 1GB swap（近满）的 Linux 机器上，本地跑 `bge-base-zh-v1.5`（768 维）做全量向量同步（表+示例千级规模），进程连续三次在模型加载后/写入期被 OOM killer 击杀： 1. **chroma 后端同步期间死亡**：进程在 bge 编码完成后、chroma 批量 upsert 时被杀； 2. **重启后仍死**：线程限制后仍死在编码峰值； 3. **qdrant 后端同步期间也死**：最终定位到"千表一次编码"把内存打满。 进程 RSS 实测峰值 ~1.9GB（bge 模型 ~500MB + torch arena + 编码中间态 + qdrant/chroma 客户端）。

**chroma 为何吃内存** 1. **HNSW 索引常驻内存**：chroma 的 ANN 索引（hnswlib）为图结构，插入时要把现有点邻居关系重算/链接，N 增长时内存随图规模陡增；批量 upsert 一次性 64 点会触发尖峰（实测 64 点批比 32 点批峰值高约 40%）。 2. **PyPI chromadb 包体大**：默认依赖含 onnxruntime/transformers 拉入（sentence-transformers 已装则叠加 torch），进程基线内存就高。 3. **本地目录模式同机部署**：chroma 与业务进程同机，其内存预算挤占本就紧张的 2G。 关键结论：**chroma 的内存问题在"写入尖峰"而非"稳定占用"**——分块小一点即可压平尖峰。

**bge 编码为何 OOM** "把整库一次编码"——`model.encode(1000+ texts)` 一次性构建 1000+ 序列的 attention 矩阵，768 维下中间激活内存随样本数线性增长；叠加 torch CPU 多线程（默认按核数开 3 线程）各自的线程本地 arena，内存翻倍。模型加载本身（~500MB weights + torch runtime）已经占了近半预算，编码再翻倍即越线。 误区：`normalize_embeddings=True` 只影响数值范围，不影响内存峰值；OOM 发生在编码器前向阶段。

**解决方案四件套（nl2sql 实战固化）** ``` ① 分块"编码+写入"（内存有界，根治千表一次编码） for i in range(0, len(texts), chunk): vecs = model.encode(texts[i:i+chunk], normalize_embeddings=True) backend.upsert(coll, points[i:i+chunk]) # 32/64 条/块 ② torch 限 2 线程（CPU 推理内存/线程本地 arena 都降） torch.set_num_threads(2) # 进程启动时 OMP_NUM_THREADS=2 MKL_NUM_THREADS=2 ./venv/bin/uvicorn ... # 环境变量兜底 ③ chroma 写入块从 64 降 32（压平 HNSW 写入尖峰） chunk = 32 if kind == "chroma" else 64 ④ 扩 swap（小内存机器的保命符） sudo fallocate -l 2G /swapfile2 && chmod 600 /swapfile2 sudo mkswap /swapfile2 && sudo swapon /swapfile2 ``` 四层叠加后：1009 表全量同步（bge 768 维）稳定跑完，进程 RSS 稳定在 ~1.18GB。

**验证与指标** | 指标 | 优化前 | 优化后 | |------|--------|--------| | 全量同步（1009 表） | 进程被杀（OOM） | ✅ 1009 张同步成功，零错误 | | bge 模型加载 | 加载后首帧推理被杀 | ✅ 正常加载+推理 | | 峰值 RSS | ~1.9GB | ~1.18GB | | swap 使用 | 1GB 近满 | 3GB 从容 | 附：远程排障顺序（复盘）：先看 `free -m` 的 Swap 行（近满是隐性 OOM 推手）→ `dmesg | grep -i kill` 找 OOM 击杀记录 → 再定位到具体内存段（加载/编码/写入）。

### 背景 客户在使用 nl2sql 智能问数时实报：大模型生成的 SQL 中出现了**不属于表的字段**——典型如 `select a.name from a`，但 `name` 并不在表 `a` 中。 "按代码逻辑这不可能"：因为注入 prompt 的表结构（schema）是从业务数据库**实时反射**出来的，模型理应只使用给定字段。但实际排查发现存在两条真实通路： ### 根因一：取列分支漏洞（代码层） `tablesel._fetch_tables_columns` 从业务库取表字段有三个通道：python-pymysql（mysql/doris）、dws CLI script、python-psycopg2（pg/dws）。其中 **mysql/doris 的 pymysql 通道漏了"空字段守卫"**（另外两个通道都有）： ```python # script 通道 / pg 通道（有守卫）： if schema_d["columns"]: # 列非空才进 prompt enriched[table_name] = schema_d # mysql 通道（修复前，无守卫）： enriched[table_name] = schema # 列为空也进 prompt！ ``` 物理存在但反射不到列的表会带着**空字段列表**进入 prompt——模型只能凭空猜字段。 ### 根因二：模型幻觉（模型层） 即使 schema 准确，仍有两条幻觉通路： - **未选表模式**的 prompt 含全局列池（所有表字段去重合并），模型可能把 B 表的字段挪用到 A 表； - 参考示例（few-shot）/ 对话历史可能带入过期字段名，被模型模仿。 ### 修复思路 既然"源头净化"无法 100% 消除，就做**纵深防御**：源头修漏洞 + prompt 定规则 + 生成后硬校验，保证违规 SQL 永远执行不到数据库。

### 三道防线总览 | 防线 | 位置 | 作用 | 性质 | |------|------|------|------| | ① schema 准确性 | `tablesel._fetch_tables_columns` | 空字段表不进 prompt（三通道守卫对齐） | 源头净化 | | ② prompt 字段硬规则 | `router._build_system_message` 统一追加 | 降低模型幻觉发生率 | 引导 | | ③ 生成后校验 | `utils/sql_field_check.py` + 两个消费点 | hard 违规阻断执行 | 硬保证 | ### 防线①：空列守卫对齐 mysql/doris 通道补上与 script/pg 通道一致的守卫，任何通道下"字段列表为空的表"都不再进入 prompt。 ### 防线②：[字段硬规则] 在 `_build_system_message` 末尾统一追加（所有 prompt 来源——数据库版本、文件回退——统一生效，不依赖各模板自己维护）： ```text [字段硬规则] 生成的 SQL 中引用的每一个字段，必须严格来自对应表在上述表结构中列出的 真实字段——带别名限定的（如 a.字段）同样必须属于 a 对应的表； 禁止凭空创造字段名，禁止把其他表的字段挪用到当前表。 若问题所需信息不在给定字段中，请在回答中明确说明，不要猜测或编造字段名。 ``` ### 防线③：生成后校验（唯一硬保证） 规则只能降低概率，**校验才能保证兜底**（客户要求"必须包含在真实字段中才可以"）。校验器在两个消费点生效： 1. **流内告警**：SSE 生成结束后校验回答中的 SQL，违规当场在对话流里提示； 2. **执行硬闸**：`/sql_execution` 执行前校验，hard 违规直接拒绝执行。

### 校验器设计（utils/sql_field_check.py，零第三方依赖） 不引入 sqlglot 等 SQL 解析器（生成 SQL 跨 mysql/pg/doris/dws 四方言，正则口径行为一致且可测）。 ### 校验分级 | 级别 | 判定条件 | 处置 | |------|---------|------| | **hard（阻断）** | `别名.字段` 前缀可归属到表，且字段不在该表真实列 | 拒绝执行 + 流内告警 | | **soft（提示）** | 裸字段不在引用表列并集（经白名单排除后） | 仅提示不阻断 | | **skip（放行）** | CTE 表 / 未知前缀 / 清单外表 / `表.*` / 取数失败 | 不判定（fail-open） | hard 口径零误报的关键：**只有"表明确、字段缺失"才算违规**——`a.name` 能确定 a 是哪张表，name 不在就是铁证；裸字段说不清属于哪张表，只提示不阻断。 ### 处理流程 1. 预处理：去注释、去字符串字面量（避免 `'name'` 干扰）、反引号脱壳； 2. 解析 FROM/JOIN 表引用与别名（支持 `db.tbl a` / `as` / 反引号，关键词误捕保护）； 3. 扫描 `别名.字段` 引用，逐一对账真实列（忽略大小写，相近字段给 difflib 建议）； 4. 裸字段扫描仅在"FROM 表全部能对上结构清单"时进行，白名单排除关键词/函数/别名/表名/AS 别名。 ### 核心片段 ```python for m in re.finditer(rf"\b({_IDENT})\s*\.\s*({_IDENT}|\*)", s): prefix, field = m.group(1).lower(), m.group(2) if field == "*": continue table_key = refs.get(prefix) # 前缀 -> 别名 -> 表 if table_key is None: continue # 非别名前缀，无法归属 cols = colsets.get(table_key.lower()) if cols is None: continue # CTE/清单外表，不判定 if field.lower() not in cols: report["hard"].append(...键值对含相近字段建议...) ``` ### fail-open 原则 校验器解析不出、取数失败一律放行——**校验器自身故障不能阻断业务**，最终由数据库自身报错兜底。

### 消费点一：执行硬闸（/sql_execution） 执行用户点选的 SQL 前先校验。hard 违规返回既有 error 信封，前端按执行失败展示，**无需改造**： ```text 字段校验未通过（引用了表中不存在的字段）： 表 test_accounts 不存在字段 name；该表真实字段：account_id, account_name, account_type, balance, create_time, status ``` 手误字段还能直接给出建议（difflib 相近字段）： ```text 表 test_accounts 不存在字段 account_nam（相近字段：account_name）... ``` ### 消费点二：流内告警（/completions SSE） 主 LLM 流结束后，提取回答中的全部 SQL 逐条校验；hard 违规则**追加一条合成 chunk**（走既有 ChatCompletionChunk 协议，前端按普通增量渲染）： ```text ⚠️ 字段校验提示：上述 SQL 引用了表中不存在的字段，请勿直接执行： 表 test_accounts 不存在字段 name；该表真实字段：... 请改用列出的真实字段（或重新描述问题）。 ``` 告警同时持久化进助手消息 + chat2sql 独立日志留痕。 ### 实测结果（干净实例） | 用例 | 结果 | |------|------| | `SELECT a.name FROM test_accounts a LIMIT 3` | 拦截，列出真实 6 字段 | | `SELECT t.account_nam ...`（手误） | 拦截，提示相近字段 account_name | | `SELECT count(*) AS cnt FROM test_accounts` | 放行，rows=[[100]] | | 全真实字段 JOIN（含 `::text` 类型转换） | 放行执行，结果正常 | | 单元：CTE/反引号/别名/字符串字面量 | hard 零误报 |

### 踩坑一（最重要）：try/finally 双 return 陷阱 闸门最初放在端点的 try 块内 return，但该函数的 **finally 里也有兜底 return**——Python 语义下 **finally 的 return 会静默吞掉 try 内的 return**： ```text 现象：服务端日志显示"🚫 已拦截"，客户端却收到 data:null 的正常执行信封 （日志与响应"精神分裂"，极难排查） 修复：闸门移到 try/finally 结构之外，独立 try/except（fail-open） 教训：在含 return 的 finally 函数里新增提前 return，必须放 try/finally 之外 ``` ### 踩坑二：Windows 端口僵尸双绑定 杀掉 uvicorn 后 netstat 残留僵尸 LISTEN 条目；且 0.0.0.0 与 127.0.0.1 双绑定会**分流请求**——同端口新旧代码并存，一半请求走老代码，日志与响应不一致。验证闸门类功能前用 `Get-NetTCPConnection` 确认唯一监听者，必要时换端口起干净实例。 ### 检查点清单（沉淀至 nl2sql_check L135） 1. try/finally 双 return：新增提前 return 放 try/finally 之外； 2. 取列三通道守卫必须一致：空字段表禁止进 prompt； 3. prompt 规则与代码校验缺一不可：规则降概率，校验保兜底； 4. 校验器 fail-open：解析不出/取数失败放行，不因校验器故障阻断业务； 5. 闸门只管字段存在性，DB 类型错误（如 varchar = integer）是数据库域问题不拦； 6. 验证闸门类端点要区分"字段校验拦截"与"DB 类型错误"两种 error。 ### 职责边界 - ✅ 校验器管：**字段是否存在于表** - ❌ 校验器不管：类型匹配、权限、方言合法性——交给数据库自身报错

### 一句话定位 **用中文提问，系统自动写 SQL、自动查库、自动出结果**——业务人员全程不写一行 SQL。 ### 工作流水线 ``` 中文问题 → 向量检索挑表 → 组装提示词(表结构+参考示例+常用分析) → 大模型生成SQL → 字段/只读安检 → 业务库执行 → 结果表格 ``` ### 技术栈速览 | 层 | 选型 | 一句话职责 | |---|---|---| | 前端 | Vue3 + Element Plus | 问数对话页 / 配置管理页 | | 后端 | Python FastAPI + SQLModel | 接口、调度、SQL 安检 | | 流式 | SSE (sse-starlette) | 回答边生成边显示 | | 大模型 | OpenAI 兼容接口(可换) | 读懂问题、写 SQL | | 向量库 | Qdrant(主) + Chroma(兜底) | 相似度检索的"记忆" | | 向量模型 | bge-base-zh-v1.5 本地 768 维 | 中文变向量，数据不出内网 | | 元库 | MySQL | 用户/配置/模板/示例/常用分析 | ### 三个核心技术点 1. **统一向量底座**：表、参考示例、常用分析三类对象共用一套向量服务；集合按 `基名__方案_维度` 分集合，切换向量模型免重建；MySQL 是事实源，向量随时可全量重建。 2. **经验注入（越用越聪明）**：问数时自动计算问题与示例/常用分析的相似度，取最相似的前 N 条随问题上送大模型（TopN 页面可调）；好答案一键保存为常用分析，形成团队知识闭环。 3. **三道防线保正确**：prompt 字段硬规则（禁止编造字段）→ schema 空字段守卫 → SQL 执行前字段对账闸门（引用不存在的字段直接拦截并列出真实字段）。

### 模块地图 | 模块 | 用户看到什么 | 后台关键实现 | |---|---|---| | 智能问数 | 对话页提问、流式回答、SQL 执行、结果导出 | SSE 流式；system_prompt 三级回退(DB默认→DB最新→内置文件)+方言过滤；meta_info 记录命中表/示例/分析，前端透明可查 | | 智能选表 | 相关表 Tab、自动高亮、正反例反馈 | 问题向量 vs 表向量(表名+中文名+别名+标签)检索 Top-K；勾选表优先；反例指纹排除噪声表 | | Prompt 管理 | 模板版本 / 参考示例 / 常用分析 三 Tab | 版本化管理+设默认；示例/常用分析同构管理：全局状态(总闸)×我的使用(分闸)两级开关，属主或管理员可改 | | 数据库管理 | 业务库连接、黑白名单、表元数据、向量同步 | 连接池按方言(dbpg/dbjdbc/dbpygo)；表元数据(中文名/别名/标签)参与向量与 prompt；一键全量同步 scope=tables/examples/analyses | | 模型配置 | 大模型/向量模型增删改、设默认、测试连接 | 默认位分离(is_default 问数 / is_default_embed 向量)；bge 本地加载、hash 托底、openai 兼容三方案 | | 用户与偏好 | 登录、用户管理、TopN 偏好 | JWT；用户偏好存 sys_config (pref:用户:key) | ### 常用分析注入链路（2026-09-17 新增） ``` 保存: 问数页"保存为常用分析" → created_by=服务端登录用户名(修复显示0) → 向量 best-effort 同步 注入: /completions 带 analysis_top_k → 相似度检索 Top-N → 段落(问题/SQL/创建人/创建时间) 追加进 system_prompt 取值: 请求参数 > 用户偏好(pref:用户:analysis_top_k) > .env ANALYSIS_TOP_K_DEFAULT(默认3) ``` ### 安全设计 - 示例/常用分析仅允许 SELECT/WITH 语句（保存即校验）； - SQL 执行前字段对账 + 只读校验，写语句拦截； - 每次上送大模型的完整内容写入 chat2sql 独立日志，可回溯审计。

### 服务器布局 ``` /opt/nl2sql 应用(venv+uvicorn:9080, 前端同源托管, .env 在启动目录) /opt/nl2sql/models/ bge-base-zh-v1.5 (391MB) /opt/app/mysql:13301 元数据库(nl2sql_online) /opt/app/qdrant:6333 向量库 ``` ### 首次部署四步 ```bash # 1) 传 wheel(已含前端页面+内置prompt) 和向量模型 scp nl2sql-*.whl user@server:/tmp/ && scp -r bge-base-zh-v1.5 user@server:/tmp/ # 2) 一键部署(自动: venv→装包→import自检→写.env→建库建表种子→启动→验证) bash deploy.sh --ssh "ssh user@server" --backend-dir nl2sql/api --web-dir nl2sql/web --deploy-dir /opt/nl2sql --port 9080 --db-type mysql --mysql-host 127.0.0.1 --mysql-port 13301 --mysql-db nl2sql_online --mysql-user xx --mysql-pass 'xx' # 3) 页面配置: 模型配置(大模型+向量模型设默认) → 数据库管理(业务库+向量同步) # 4) 验证: /health 200 + 登录 200 + 问一题出 SQL ``` ### 升级三步铁律（顺序不能乱） ```bash pip install --force-reinstall --no-deps 新wheel # 同版本号 pip 会跳过, 必须 force python -m nl2sql.scripts.init_db migrate # 老库幂等补列(输出 ALTER ... 即新补列) pkill -f "uvicorn nl2sql.main:app.*--port 9080" && nohup 重启 # 按端口定界杀旧进程 ``` ### 日常维护速查 | 事项 | 命令/入口 | |---|---| | 健康 | `curl http://127.0.0.1:9080/health` | | 日志 | `tail -f /opt/nl2sql/logs/app.log`（问数明细看 logs/chat2sql.log） | | 元库没起(手工装的非自启) | `sudo /opt/app/mysql/bin/mysqld_safe --defaults-file=/opt/app/mysql/data/my_13301.cnf --user=mysql &` | | 向量重建 | 数据库管理页"向量同步" 或 `POST /api/v1/qdrant/vector/sync {"scope":"all"}` | | 备份 | 每日 `mysqldump nl2sql_online`（向量可重建无需备份） | | 回滚 | 装回旧 wheel → migrate → 重启 | ### 注意事项 - `.env` 必须放启动目录（应用读当前目录的 .env）；改端口时 vite 代理三处 target 同步改； - 2G 内存机器跑本地 bge 属下限：已内置分块编码+限线程，仍紧张先扩 swap； - 大模型/向量模型地址是单位自配（页面模型配置），换模型不影响历史数据（向量按方案分集合）。