SQLite中->>操作符创建的索引为何不被LIKE查询使用？

问题描述 #

在SQLite 3.41.2中创建了如下表：

CREATE TABLE node (
    id         TEXT NOT NULL,
    label      TEXT NOT NULL,
    properties TEXT NOT NULL, -- containing JSON
    PRIMARY KEY (
        id
    )
);

因访问properties子字段的查询性能低下，为$.string属性创建了索引：

CREATE INDEX node_properties_string_nocase ON node (properties ->> '$.string' COLLATE NOCASE);

但执行以下查询的执行计划显示SQLite未使用该索引：

EXPLAIN QUERY PLAN SELECT * FROM node WHERE label = 'x' AND properties ->> '$.string' LIKE 'y%';
-- 执行计划结果：SCAN node

改用JSON_EXTRACT(...)重写查询和索引后，SQLite能正常使用索引。现疑问：SQLite是否真的不支持使用->>操作符创建的索引，还是存在遗漏的配置或细节？

问题解答 #

SQLite 3.41.2中并非完全不支持用->>操作符创建索引，而是查询优化器在处理该操作符时存在局限性：

• ->>本质是JSON_EXTRACT()的语法糖，但SQLite的查询优化器在3.41.2版本中，无法自动识别查询语句里的->>操作符与索引定义中的->>操作符是完全等价的表达式，因此不会触发索引匹配。
• 当改用JSON_EXTRACT(properties, '$.string')显式函数调用后，优化器能直接匹配索引中对应的函数表达式，从而正确使用索引。

在3.41.2版本中，若要让索引生效，需要保证查询语句中的表达式与索引定义中的表达式使用完全一致的语法形式——实际测试中仅显式使用JSON_EXTRACT函数的形式能被优化器正确识别，而->>操作符的等价匹配逻辑尚未完善。后续版本的SQLite可能修复了这一问题。

内容的提问来源于stack exchange，提问作者Raphael Müller