优化SQLite+SQLAlchemy大规模URL模糊查询速度的技术求助

方案1：预提取纯净域名+轻量索引 #

• 新增domain字段，专门存储从url中提取的纯净域名（比如从https://www.website.com/App里提取出website.com）
• 用SQLite内置函数批量处理现有数据（可分批执行，避免单批压力过大）：

  -- 第一步：剥离协议前缀（http:///https://）
  UPDATE urldata SET domain = SUBSTR(url, INSTR(url, '://') + 3);
  -- 第二步：去掉www.前缀
  UPDATE urldata SET domain = SUBSTR(domain, INSTR(domain, 'www.') + 4) WHERE domain LIKE 'www.%';
  -- 第三步：剥离域名后的路径部分
  UPDATE urldata SET domain = SUBSTR(domain, 1, INSTR(domain, '/') - 1) WHERE INSTR(domain, '/') > 0;
  

• 给domain字段建普通索引——该字段数据量远小于原url字段，索引构建速度快，不会出现数据库损坏的问题
• 后续新增数据时，在插入前就处理好domain字段，避免批量更新的开销
• 查询直接匹配domain字段，效率拉满：

  UrlData.query.filter(UrlData.domain == 'website.com').all()
  

方案2：FTS5全文索引加速 #

• 利用SQLite的FTS5引擎创建虚拟表，专门针对url做全文检索：

  CREATE VIRTUAL TABLE urldata_fts USING fts5(url, content='urldata', content_rowid='rowid');
  

• 分批同步现有数据到虚拟表（避免一次性锁表）：

  -- 示例：每次同步100万条，循环执行直到完成
  INSERT INTO urldata_fts(rowid, url) SELECT rowid, url FROM urldata LIMIT 1000000 OFFSET [偏移量];
  

• 建触发器实现增量同步，新增数据自动同步到虚拟表：

  CREATE TRIGGER urldata_after_insert AFTER INSERT ON urldata
  BEGIN
    INSERT INTO urldata_fts(rowid, url) VALUES (new.rowid, new.url);
  END;
  

• 查询用FTS5的MATCH语法，比contains快几个数量级：

  query = 'website.com'
  results = db.session.query(UrlData).join(urldata_fts, UrlData.rowid == urldata_fts.rowid).filter(urldata_fts.url.match(query)).all()
  

方案3：分表分区分散压力 #

• 按url的哈希值或域名首字符拆分数据到多个子表，比如按哈希模100分成100个表（urldata_0到urldata_99）
• 插入数据时根据url的哈希值路由到对应子表，避免单表数据量过载
• 查询时可根据目标域名的特征缩小遍历范围（比如域名首字母是w就只查对应子表），每个子表数据量小，查询速度自然提升
• 这种方案能分散索引维护压力，不会出现单表建索引导致的数据库损坏问题

临时应急优化（无需改结构） #

• 避免用contains（本质是LIKE '%xxx%'），根据域名出现的位置调整查询条件，利用SQLite的部分索引优化：

  -- 匹配域名作为结尾的情况
  UrlData.query.filter(UrlData.url.endswith('website.com') | UrlData.url.endswith('website.com/')).all()
  -- 匹配域名在协议后的情况（更精准）
  UrlData.query.filter(UrlData.url.contains('//website.com/')).all()
  

内容的提问来源于stack exchange，提问作者Lacorte