Lampio 知识库

主题

数据库性能优化记录 ​

版本: 详见 schema.md最后更新: 2026-03-07

📊 优化总览 ​

优化项版本日期效果
反规范化phonetic字段v1.12025-12-31JOIN减少60%，查询提升30-50%
新增复合索引v1.12025-12-31查询提升20-40%
移除缓存表，启用3层回填v62026-01-01架构简化，命中率90%+

⚡ v1.1 优化详情 ​

优化1: 反规范化phonetic字段 ​

问题分析 ​

原设计:

sql
notebook_entries {
  phonetic TEXT  -- 常为空
  vocabulary_id TEXT FK  -- 可空
}

-- 查询时需要LEFT JOIN获取音标
SELECT ne.*, v.ipa_pronunciation as phonetic
FROM notebook_entries ne
LEFT JOIN vocabulary_items v ON ne.vocabulary_id = v.id

问题:

  1. vocabulary_items 表有3万+行
  2. 每次查询都需要JOIN大表，即使只是为了获取音标
  3. 5表JOIN导致查询复杂度高

解决方案 ​

数据迁移:

sql
-- 填充phonetic字段
UPDATE notebook_entries
SET phonetic = (
  SELECT v.ipa_pronunciation
  FROM vocabulary_items v
  WHERE v.id = notebook_entries.vocabulary_id
)
WHERE phonetic IS NULL AND vocabulary_id IS NOT NULL;

优化后查询:

sql
-- 移除vocabulary_items JOIN
SELECT ne.*  -- phonetic已在表中，无需JOIN
FROM notebook_entries ne
INNER JOIN word_mastery_info wm ON ne.id = wm.notebook_entry_id
WHERE wm.next_review_date <= date('now');

效果对比 ​

指标优化前优化后提升
JOIN表数5表2-4表减少20-60%
getDueReviewsByGroup(null)5表JOIN2表JOIN⬆️ 30-50%
getDueReviewsByGroup(groupId)5表JOIN4表JOIN⬆️ 20-30%
数据冗余0KB+40KB可接受

代码位置 ​

文件：lib/features/vocabulary_notebook/data/datasources/notebook_datasource.dart

优化前:

dart
// getDueReviewsByGroup(null) - 5表JOIN
LEFT JOIN vocabulary_items v ON ne.vocabulary_id = v.id

优化后:

dart
// 移除vocabulary_items JOIN
// 直接使用 ne.phonetic

优化2: 新增复合索引 ​

问题分析 ​

现有索引:

sql
CREATE INDEX idx_word_source_notebook ON word_source_relations(notebook_entry_id);
CREATE INDEX idx_word_source_reading ON word_source_relations(reading_source_id);

问题: 查询同时使用 reading_source_id 和 notebook_entry_id 时，单列索引效率不高：

sql
SELECT * FROM word_source_relations
WHERE reading_source_id = ? AND notebook_entry_id = ?;

解决方案 ​

新增复合索引:

sql
-- 索引1: 优化word_source_relations的联合查询
CREATE INDEX idx_word_source_combined
  ON word_source_relations(reading_source_id, notebook_entry_id);

-- 索引2: 优化按添加日期排序
CREATE INDEX idx_notebook_added_date
  ON notebook_entries(added_at);

效果对比 ​

查询场景优化前优化后提升
WHERE + JOIN查询单列索引复合索引⬆️ 20-40%
getGroupSummaries()5表JOIN4表JOIN+复合索引⬆️ 20-40%
按日期排序全表扫描索引扫描⬆️ 50-70%

索引策略 ​

复合索引优势:

  1. 覆盖查询: WHERE条件的多个字段在同一索引中
  2. 减少回表: 索引直接包含所需数据
  3. 排序优化: 索引顺序与ORDER BY一致

选择复合索引的原则:

  • 高频WHERE条件组合
  • 字段选择性高（区分度大）
  • 考虑查询的字段顺序

🎯 索引策略总结 ​

当前索引列表（20个） ​

核心查询索引 ​

  1. 学习复习:

    sql
    idx_mastery_next_review ON word_mastery_info(next_review_date)
    • 用途：查询待复习单词
    • 频率：极高（每次学习会话）

  2. 单词查找:

    sql
    idx_notebook_word ON notebook_entries(word)
    • 用途：按单词搜索
    • 频率：高（用户搜索）

  3. 分组查询:

    sql
    idx_notebook_group ON notebook_entries(notebook_group)
idx_notebook_mastery_status ON notebook_entries(mastery_status)
    • 用途：按分组/状态筛选
    • 频率：高（分组视图）

  4. 来源关联:

    sql
    idx_word_source_combined ON word_source_relations(reading_source_id, notebook_entry_id)
    • 用途：优化JOIN查询
    • 频率：高（查询单词来源）

  5. OCR位置:

    sql
    idx_ocr_source_word ON ocr_word_positions(reading_source_id, word)
    • 用途：查找单词在原图位置
    • 频率：中（查看原图高亮）

辅助索引 ​

  • idx_learning_completed - 学习历史按时间查询
  • idx_reading_name - 按来源名称搜索

索引维护 ​

何时添加索引:

  1. 慢查询日志显示全表扫描
  2. WHERE/JOIN字段没有索引
  3. 排序字段(ORDER BY)没有索引

何时避免索引:

  1. 表数据量小（<1000行）
  2. 字段选择性低（如性别、布尔值）
  3. 频繁INSERT/UPDATE的表

索引监控:

sql
-- 查看索引使用情况
EXPLAIN QUERY PLAN
SELECT ... ;

-- 查看所有索引
SELECT * FROM sqlite_master WHERE type='index';

📈 性能测试基准 ​

测试环境 ​

  • 设备: Android真机
  • 数据量:
    • notebook_entries: ~500条
    • vocabulary_items: ~7,800词
    • learning_records: ~2,000条

关键查询性能 ​

查询函数数据量v1.0v1.1提升
getDueReviewsByGroup(null)5条~80ms~50ms⬆️ 37%
getGroupSummaries()5分组~120ms~70ms⬆️ 42%
addNotebookEntry()1条~30ms~30ms-
getVocabulariesByWords()100词~40ms~40ms-

注意: 实际性能受设备、数据量影响

🚀 未来优化方向 ​

1. 聚合缓存表（计划中） ​

问题:

  • getGroupSummaries() 实时聚合，包含复杂的COUNT/AVG计算
  • 每次查询都需要多表JOIN + GROUP BY

方案:

sql
-- 新增统计缓存表
CREATE TABLE reading_source_stats (
  source_id TEXT PRIMARY KEY,
  total_words INTEGER NOT NULL DEFAULT 0,
  due_words INTEGER NOT NULL DEFAULT 0,
  mastery_percentage REAL NOT NULL DEFAULT 0.0,
  last_studied_at TEXT,
  updated_at TEXT NOT NULL,
  FOREIGN KEY (source_id) REFERENCES reading_sources(id) ON DELETE CASCADE
);

-- 触发器或应用层维护
-- 每次学习完成后更新统计

预期效果:

  • 查询时间：120ms → <10ms (减少90%)
  • 代价：实时性略降（可接受）

2. 远程词库方案（v6已实现） ​

详见：docs/database-optimization-report.md

v6架构:

  • vocabulary_items 表统一在 english_learning.db
  • 3层回填架构：本地 → Supabase → Edge Function API
  • 预装核心词 + 动态回填
  • source字段区分：'preinstalled' / 'backfilled'

查询流程:

1. 查询本地 vocabulary_items（90%+ 命中，<20ms）
   ├─ 命中 → 返回
   └─ 未命中 ↓
2. 查询 Supabase 云端词库（8% 命中，<2s）
   ├─ 命中 → 回填本地 → 返回
   └─ 未命中 ↓
3. Edge Function API（2% 命中，<5s）
   └─ 命中 → 回填Supabase → 回填本地 → 返回

已实现效果:

  • 本地词库可动态增长（预装 + 回填）
  • 词库更新：预装词全量覆盖，回填词逐步积累
  • 离线场景：本地词库依然可用
  • 查询速度：本地<20ms，云端<2s，API<5s

未来扩展（可选）:

  • 应用体积优化：减少预装词数量（当前预装 18,916 词）
  • 批量下载：用户手动下载特定CEFR级别词库
  • LRU淘汰：回填词过多时自动清理（last_accessed_at）

3. 分页和虚拟滚动 ​

问题:

  • 大列表一次性加载所有数据
  • 内存占用高，滑动卡顿

方案:

sql
-- 使用LIMIT和OFFSET分页
SELECT * FROM notebook_entries
ORDER BY added_at DESC
LIMIT 20 OFFSET 0;

-- 虚拟滚动：只渲染可见区域

预期效果:

  • 初始加载：减少70%内存占用
  • 滑动流畅度：60fps

🔍 性能调试工具 ​

SQLite查询分析 ​

sql
-- 查询计划分析
EXPLAIN QUERY PLAN
SELECT ne.*, wm.*
FROM notebook_entries ne
INNER JOIN word_mastery_info wm ON ne.id = wm.notebook_entry_id
WHERE wm.next_review_date <= date('now');

-- 输出示例：
-- SEARCH wm USING INDEX idx_mastery_next_review (next_review_date<?)
-- SEARCH ne USING INTEGER PRIMARY KEY (rowid=?)

Flutter性能分析 ​

dart
// 使用Timeline记录查询时间
Timeline.startSync('getDueReviewsByGroup');
final results = await getDueReviewsByGroup(groupId);
Timeline.finishSync();

// DevTools中查看性能

慢查询日志 ​

dart
// 自定义日志
final stopwatch = Stopwatch()..start();
final results = await db.rawQuery(sql);
stopwatch.stop();

if (stopwatch.elapsedMilliseconds > 50) {
  print('[SLOW QUERY] ${stopwatch.elapsedMilliseconds}ms: $sql');
}

📝 优化检查清单 ​

性能优化时，检查以下项目：

  • [ ] 慢查询是否有索引？
  • [ ] JOIN是否必要？能否移除？
  • [ ] 是否可以反规范化字段减少JOIN？
  • [ ] 复杂聚合是否可以缓存？
  • [ ] 大列表是否需要分页？
  • [ ] 索引是否覆盖WHERE/ORDER BY字段？
  • [ ] 是否有未使用的索引（浪费空间）？
  • [ ] 数据冗余是否可接受？

📚 参考资料 ​

维护者: 数据库变更时，请同步更新此文档的优化记录！