一、问题背景:为什么需要优化模糊查询?

周末在家用Flutter开发购物清单App时,我遇到一个头疼的问题:当用户在搜索框输入"apple"时,联想功能要同时匹配"Apple手机"、"红苹果"、"苹果醋"等包含该关键词的记录,但随着数据量突破万级,传统的LIKE查询变得异常卡顿。这让我意识到必须对SQLite的模糊查询做深度优化。

二、传统方案的痛点分析

-- 基础表结构(SQLite语法)
CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT
);

-- 典型LIKE查询(耗时113ms@10000条数据)
SELECT * 
FROM products
WHERE name LIKE '%apple%' 
ORDER BY name 
LIMIT 20;

-- 使用索引前缀的改进查询(耗时29ms)
CREATE INDEX idx_name_prefix ON products(name COLLATE NOCASE);
SELECT * 
FROM products
WHERE name LIKE 'apple%';

传统方案存在三个致命伤:

  1. 通配符前置LIKE '%apple%'使索引失效,万级数据扫描需29ms以上
  2. 大小写敏感:默认配置无法匹配"Apple"和"apple"
  3. 排序压力:ORDER BY会使临时表尺寸暴增

三、FTS5解决方案深度解析

3.1 搭建全文搜索引擎

-- 创建虚拟表(需开启SQLite编译选项)
CREATE VIRTUAL TABLE products_fts USING fts5(name, category);

-- 插入示例数据
INSERT INTO products_fts VALUES 
    ('iPhone 13 Pro Max', '手机'),
    ('红苹果礼盒装', '水果'),
    ('苹果硅胶手机壳', '配件');

-- 基础查询(耗时0.8ms@10000条)
SELECT * 
FROM products_fts 
WHERE name MATCH 'apple' 
ORDER BY rank;

实际测试显示,万级数据下响应速度提升40倍,且支持以下高级特性:

3.2 扩展功能示例

-- 多字段联合搜索(AND逻辑)
SELECT * 
FROM products_fts 
WHERE products_fts MATCH 'name:apple AND category:水果';

-- 前缀匹配优化(速度提升3倍)
SELECT * 
FROM products_fts 
WHERE name MATCH 'app*';

-- 权重调整(提升标题匹配度)
SELECT *, 
    CASE 
        WHEN name MATCH 'apple' THEN 10 
        WHEN category MATCH 'apple' THEN 5 
    END AS relevance
FROM products_fts 
ORDER BY relevance DESC;

四、关联技术:预计算字段优化

-- 添加首字母字段
ALTER TABLE products ADD COLUMN initials TEXT;

-- 生成首字母(Python示例)
def generate_initials(name):
    return ''.join([pinyin.get(w[0], '')[0] for w in name])

-- 创建组合索引
CREATE INDEX idx_name_initials ON products(initials, name);

-- 复合查询(响应时间9ms)
SELECT *
FROM products
WHERE initials = 'pg' AND name LIKE '%苹果%'
LIMIT 20;

这种方法结合了空间换时间的思路,适合中文字段的快速首字母联想。

五、核心技术对比分析

方案 查询速度 存储开销 开发难度 灵活度
LIKE 简单
FTS5 大(2-3倍) 中等 中等
预计算字段 较快 中等 复杂

六、实战应用场景模拟

假设我们正在开发一个电商App,用户输入"苹果"时的处理流程:

  1. 输入监听:用户输入"pg"触发联想
  2. 首字母匹配:优先匹配initials='pg'的记录
  3. FTS5扩展:查找MATCH '苹果 OR pingguo'
  4. 混合排序:结合销量、价格、匹配度动态排序
-- 综合查询示例(电商场景)
SELECT p.*,
    (s.sales_count * 0.3 + 
     (1.0 - p.price / 10000) * 0.2 +
     fts.rank * 0.5) AS composite_score
FROM products_fts fts
JOIN products p ON fts.rowid = p.id
JOIN sales_stats s ON p.id = s.product_id
WHERE fts.name MATCH '苹果 OR pingguo'
ORDER BY composite_score DESC
LIMIT 15;

七、关键注意事项

  1. 数据量阈值:当数据超过50万行时需考虑分片存储
  2. 中文分词:需要自定义分词器处理"iPhone12Pro"类混合文本
  3. 索引策略:联合索引字段顺序影响查询效率
  4. 内存管理:避免频繁更新导致FTS表膨胀
  5. 测试策略:在真实数据分布下进行压力测试

八、总结与最佳实践

经过两周的实践验证,最终形成以下实施原则:

  1. 优先采用FTS5处理核心搜索功能
  2. 使用预计算字段优化首字母匹配
  3. 对价格等数值字段建立独立索引
  4. 通过定期VACUUM维护数据库性能
  5. 异步线程处理搜索任务避免UI卡顿