数据与搜索
本页总结系统在 SQLite 上的查询和搜索设计。核心思路是把并发基线、标准化、拼音预计算、FTS 和普通索引拆开，各自负责最适合的查询路径。
查询基线
SQLite 是主存储。app/database.py 会在连接建立时强制执行 PRAGMA journal_mode=WAL 与 PRAGMA foreign_keys=ON，init_db() 启动阶段还会顺序完成：
SQLModel.metadata.create_all(engine)ensure_sqlite_performance_indexesensure_sqlite_inventory_ftscheck_sqlite_schema_consistencyANALYZE与PRAGMA optimize
因此，索引、FTS 和一致性检查都属于运行时初始化流程，而不是事后补救步骤。
标准化与拼音预计算
系统的搜索先统一输入，再决定查询路径。当前实现中：
- CAS 通过
cas_utils.py做空格清理、大小写归一和格式校验。 - 规格、位置、货架等格式性字段都在后端完成清洗。
pinyin_utils.py会为用户、库存、试剂订单和耗材订单写入*_pinyin与*_pinyin_initials字段。
这些字段不是展示冗余，而是排序、筛选和搜索直接依赖的查询字段。
FTS 表与触发器
系统维护了四张 FTS5 虚拟表：
inventory_ftsreagent_order_ftsconsumable_order_ftsusers_fts
它们都使用 trigram 分词，并通过 INSERT、UPDATE、DELETE 触发器与主表同步。启动时系统会检查触发器是否齐全、FTS 行数是否与主表一致；若发现缺口，会自动执行 rebuild SQL 重建内容。
搜索选路
搜索不会强制走同一条路径。app/services/search_matchers.py、app/services/inventory_fts.py 和 app/services/order_fts.py 会根据查询特征动态选路：
- CAS 精确匹配优先走
=，尽量利用 B-Tree 索引。 - CAS 前缀匹配走
LIKE xxx%。 - 短关键字或
fuzzy=true场景优先走LIKE。 - 足够长、适合全文召回的关键字才会走 trigram FTS。
- FTS 失败时自动回退到
LIKE，优先保证可用性。
对库存和订单类查询，系统通常先拿到候选 rowid 或 id，再回到 ORM 拉实体，避免把全部业务逻辑塞进单条复杂 SQL。
普通库存与常用货架
库存和常用货架共用 Inventory 模型，但查询路径并不混用。app/services/inventory_queries.py 提供 regular_inventory_clause 与 common_inventory_clause，大部分索引和 FTS 查询也都带有 is_common 维度。写查询或加索引时，必须先确认场景属于普通库存还是常用货架。
当前实现里的优化点
当前系统已经落地的查询优化主要包括：
- 复合索引覆盖库存状态、借用记录、用户会话、订单筛选等高频路径。
- 拼音字段和拼音首字母字段直接参与排序与搜索。
- FTS5 与普通索引并存，而不是强行只选一种方案。
- 列表首页在无搜索条件时允许命中后端短 TTL 内存缓存，减少重复查询。
- 搜索或分页请求会绕过首页缓存，避免旧数据误命中。
目标不是把所有查询压到一条最快路径，而是让常见查询稳定、复杂查询可接受、异常时可降级。
变更检查
- 新字段是否参与搜索、排序或聚合。
- 是否需要新增标准化逻辑或拼音预计算字段。
- 是否需要同步更新
SQLITE_*_FTS_SETUP、触发器和 rebuild SQL。 - 是否需要补充索引，而不是继续依赖全表扫描。
- FTS 失败回退到
LIKE后，结果是否仍然正确。
验证建议
- 核对
PRAGMA journal_mode;与PRAGMA foreign_keys;。 - 对比四张 FTS 表与主表
COUNT(*)是否一致。 - 分别用中文、全拼、首字母、CAS 精确值和短关键字测试库存与订单搜索。
- 临时破坏一个 FTS 触发器后重启，确认系统会自动重建。