SQLite中LIKE运算符对比=运算符性能差异问题求助

我来帮你搞定这个SQLite里LIKE查询比=慢太多的问题——这在处理文本模糊查询时是个很常见的痛点，尤其是针对Jmdict这种较大的词典数据集。咱们先拆解原因，再一步步解决。

为什么LIKE比=慢这么多？ #

当你用=运算符时，SQLite可以直接利用字段上的B-tree索引快速定位匹配的记录，所以耗时只有14ms。但如果用LIKE，默认情况下如果没有合适的索引，数据库会做全表扫描：遍历每一条记录去匹配模糊条件，这就导致了440ms的慢查询。不过你用的是前缀通配符（example%），这种情况其实是可以利用B-tree索引优化的，只是你的表可能没建对应的索引。

解决方案一：给查询字段创建B-tree索引 #

针对你用到的READING_ELEMENT和GLOSS字段，创建普通索引，这样前缀LIKE查询就能直接走索引，速度会大幅提升：

-- 给Jmdict_Reading_Element的READING_ELEMENT字段建索引
CREATE INDEX idx_reading_element ON Jmdict_Reading_Element(READING_ELEMENT);

-- 给Jmdict_Sense_Element的GLOSS字段建索引
CREATE INDEX idx_sense_gloss ON Jmdict_Sense_Element(GLOSS);

创建完索引后，再测试你的前缀LIKE查询，耗时应该会降到和=查询接近的水平。原理是SQLite的B-tree索引支持前缀匹配的LIKE查询（通配符在末尾），数据库可以通过索引快速定位所有以example开头的记录，不用全表扫描。

解决方案二：优化查询语句结构 #

你原来的查询用了IN子查询，换成EXISTS通常会更高效，因为EXISTS只要找到匹配的记录就会停止检查，而IN需要先收集所有子查询的结果再做匹配：

SELECT 
    re.ENTRY_ID, 
    GROUP_CONCAT(re.READING_ELEMENT, '§') AS read_element, 
    GROUP_CONCAT(re.FURIGANA_BOTTOM, '§') AS furigana_bottom, 
    GROUP_CONCAT(re.FURIGANA_TOP, '§') AS furigana_top, 
    GROUP_CONCAT(re.NO_KANJI, '§') AS no_kanji, 
    GROUP_CONCAT(re.READING_COMMONNESS, '§') AS read_commonness, 
    GROUP_CONCAT(re.READING_RELATION, '§') AS read_rel, 
    GROUP_CONCAT(se.SENSE_ID, '§') AS sense_id, 
    GROUP_CONCAT(se.GLOSS, '§') AS gloss, 
    GROUP_CONCAT(se.POS, '§') AS pos, 
    GROUP_CONCAT(se.FIELD, '§') AS field, 
    GROUP_CONCAT(se.DIALECT, '§') AS dialect, 
    GROUP_CONCAT(se.INFORMATION, '§') AS info 
FROM Jmdict_Reading_Element AS re 
LEFT JOIN Jmdict_Sense_Element AS se ON re.ENTRY_ID = se.ENTRY_ID 
WHERE 
    EXISTS (
        SELECT 1 FROM Jmdict_Reading_Element re_sub 
        WHERE re_sub.ENTRY_ID = re.ENTRY_ID AND re_sub.READING_ELEMENT LIKE 'example%'
    )
    OR EXISTS (
        SELECT 1 FROM Jmdict_Sense_Element se_sub 
        WHERE se_sub.ENTRY_ID = re.ENTRY_ID AND se_sub.GLOSS LIKE 'example%'
    )
GROUP BY re.ENTRY_ID;

结合上面的索引，这个查询的性能会得到双重提升。

解决方案三：用全文检索（FTS）处理复杂模糊查询 #

如果你之后需要更灵活的模糊查询（比如通配符在中间，或者需要全文匹配），普通B-tree索引就不够用了。这时候可以用SQLite的FTS5扩展，专门针对文本搜索优化：

1. 创建全文检索虚拟表 #

-- 为Jmdict_Reading_Element创建FTS虚拟表
CREATE VIRTUAL TABLE Jmdict_Reading_FTS USING fts5(READING_ELEMENT, ENTRY_ID);
-- 导入现有数据
INSERT INTO Jmdict_Reading_FTS(READING_ELEMENT, ENTRY_ID) 
SELECT READING_ELEMENT, ENTRY_ID FROM Jmdict_Reading_Element;

-- 为Jmdict_Sense_Element创建FTS虚拟表
CREATE VIRTUAL TABLE Jmdict_Sense_FTS USING fts5(GLOSS, ENTRY_ID);
-- 导入现有数据
INSERT INTO Jmdict_Sense_FTS(GLOSS, ENTRY_ID) 
SELECT GLOSS, ENTRY_ID FROM Jmdict_Sense_Element;

2. 用FTS查询替换LIKE #

SELECT 
    re.ENTRY_ID, 
    GROUP_CONCAT(re.READING_ELEMENT, '§') AS read_element, 
    -- 其他字段...
FROM Jmdict_Reading_Element AS re 
LEFT JOIN Jmdict_Sense_Element AS se ON re.ENTRY_ID = se.ENTRY_ID 
WHERE 
    EXISTS (
        SELECT 1 FROM Jmdict_Reading_FTS 
        WHERE ENTRY_ID = re.ENTRY_ID AND READING_ELEMENT MATCH 'example*'
    )
    OR EXISTS (
        SELECT 1 FROM Jmdict_Sense_FTS 
        WHERE ENTRY_ID = re.ENTRY_ID AND GLOSS MATCH 'example*'
    )
GROUP BY re.ENTRY_ID;

FTS的查询效率比普通LIKE高几个数量级，适合频繁进行文本模糊搜索的场景。

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