Flutter中SQLite 50万条数据多列模糊搜索及相关性排序求助

方案一：纯SQL内置函数实现+预过滤（最快优化路径） #

放弃Dart自定义函数，改用SQLite原生的INSTR函数计算匹配位置，先通过WHERE条件过滤掉不包含所有关键词的记录，再对剩余数据排序，能大幅降低计算量。

实现代码： #

单列搜索（以name列为例）

SELECT
  id,
  name,
  article,
  MIN(
    INSTR(LOWER(name), 'land'),
    INSTR(LOWER(name), 'chaos')
  ) AS rank
FROM products
WHERE
  INSTR(LOWER(name), 'land') > 0
  AND INSTR(LOWER(name), 'chaos') > 0
ORDER BY rank ASC
LIMIT 50; -- 分页减少数据传输压力

多列搜索（name或article包含所有关键词）

SELECT
  id,
  name,
  article,
  MIN(
    LEAST(INSTR(LOWER(name), 'land'), INSTR(LOWER(article), 'land')),
    LEAST(INSTR(LOWER(name), 'chaos'), INSTR(LOWER(article), 'chaos'))
  ) AS rank
FROM products
WHERE
  (INSTR(LOWER(name), 'land') > 0 OR INSTR(LOWER(article), 'land') > 0)
  AND (INSTR(LOWER(name), 'chaos') > 0 OR INSTR(LOWER(article), 'chaos') > 0)
ORDER BY rank ASC
LIMIT 50;

性能优化： #

• 加表达式索引：如果主要搜索name列，给LOWER(name)建索引，能加速INSTR的过滤过程：

CREATE INDEX idx_products_lower_name ON products(LOWER(name));

• 强制分页：用LIMIT控制返回条数，避免一次性处理几十万条数据。

方案二：FTS5+自定义排序逻辑（兼顾过滤速度和排序需求） #

针对你之前用FTS5遇到的问题，调整tokenizer配置，再结合offsets()函数获取匹配位置，就能实现符合要求的排序。

改进步骤： #

1. 重建FTS5表：用unicode61 tokenizer，支持任意长度子串搜索，还能保留特殊字符：

CREATE VIRTUAL TABLE IF NOT EXISTS products_fts
USING fts5(
  id,
  article,
  name,
  content='products', -- 关联原表，不存冗余数据
  tokenize='unicode61 remove_diacritics 1 tokenchars "-_."'
);

2. 同步数据：

INSERT INTO products_fts (rowid, id, article, name)
SELECT ROWID, id, article, name FROM products;

3. 查询并计算排名：
   用offsets()提取每个关键词的匹配位置，取最小值作为排名：

SELECT
  p.id,
  p.name,
  p.article,
  MIN(
    CAST(SUBSTR(offsets(products_fts), INSTR(offsets(products_fts), ',')+1, INSTR(SUBSTR(offsets(products_fts), INSTR(offsets(products_fts), ',')+1), ',')-1) AS INTEGER)
  ) AS rank
FROM products p
JOIN products_fts fts ON p.rowid = fts.rowid
WHERE fts MATCH 'land chaos' -- 多关键词用空格分隔，默认是AND逻辑
GROUP BY p.id
ORDER BY rank ASC
LIMIT 50;

解决原FTS5问题： #

• 不再有3字符限制，短词比如“19”也能搜索到结果；
• 通过offsets()获取真实匹配位置，排序逻辑和你要求的完全一致。

方案三：自定义函数+预过滤（兼容原逻辑） #

如果不想改原有的自定义函数逻辑，先通过FTS5过滤出候选记录，再对候选集计算排名，避免全表扫描：

SELECT
  p.id,
  p.name,
  p.article,
  get_search_result_rank(p.name, 'land', 'chaos') AS rank
FROM products p
JOIN products_fts fts ON p.rowid = fts.rowid
WHERE fts MATCH 'land chaos'
AND rank >= 0
ORDER BY rank ASC
LIMIT 50;

这样自定义函数只需要处理FTS5筛选后的少量数据，速度会比全表扫描快很多。

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