sql - SQLite 可以优化开头查询吗?

> create table T (ID integer primary key autoincrement, Path text not null);
> create index T_Path on T(Path);

我想选择 Path'abcd' 开头的所有行。

> explain select * from T where Path >= 'abcd' and Path < 'abce';
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     11    0                    0   Start at 11
1     OpenRead       1     4     0     k(2,,)         0   root=4 iDb=0; T_Path
2     String8        0     1     0     abcd           0   r[1]='abcd'
3     SeekGE         1     10    1     1              0   key=r[1]
4     String8        0     1     0     abce           0   r[1]='abce'
5       IdxGE          1     10    1     1              0   key=r[1]
6       IdxRowid       1     2     0                    0   r[2]=rowid
7       Column         1     0     3                    0   r[3]=T.Path
8       ResultRow      2     2     0                    0   output=r[2..3]
9     Next           1     5     0                    0
10    Halt           0     0     0                    0
11    Transaction    0     0     6     0              1   usesStmtJournal=0
12    Goto           0     1     0                    0

工作正常。通过使用 SeekGE 我们可以看到正在完成索引查找。通过进一步分析,我们可以看到这是打开索引,寻找 'abcd',顺序读取行直到它到达 abce,然后停止。干得好,查询优化器。

但是我们必须写两次前缀有点烦人。 SQLite 不能为我们这样做吗?

> explain select * from T where substr(Path,1,4) = 'abcd';
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     11    0                    0   Start at 11
1     OpenRead       0     2     0     2              0   root=2 iDb=0; T
2     Rewind         0     10    0                    0
3       Column         0     1     2                    0   r[2]=T.Path
4       Function       6     2     1     substr(3)      0   r[1]=func(r[2..4])
5       Ne             5     9     1                    80  if r[1]!=r[5] goto 9
6       Rowid          0     6     0                    0   r[6]=rowid
7       Column         0     1     7                    0   r[7]=T.Path
8       ResultRow      6     2     0                    0   output=r[6..7]
9     Next           0     3     0                    1
10    Halt           0     0     0                    0
11    Transaction    0     0     6     0              1   usesStmtJournal=0
12    Integer        1     3     0                    0   r[3]=1
13    Integer        4     4     0                    0   r[4]=4
14    String8        0     5     0     abcd           0   r[5]='abcd'
15    Goto           0     1     0                    0

不,它不能。这是一个完整的 table 扫描。

问题:有没有一种好方法可以让 SQLite 返回以某个字符串开头的行?

Path LIKE 'abcd%' 不是答案 - 它不区分大小写,因此它会执行 table 扫描。

期望的结果:类似

select * from T where starts_with(Path, 'abcd');

回答1

正如 https://sqlite.org/optoverview.html#the_like_optimization 中所述,您可以使用运算符 https://www.sqlite.org/lang_expr.html#the_like_glob_regexp_match_and_extract_operators

对于这两个运算符,首先必须确保使用内置的 BINARY 排序序列对列 Path 进行索引(如果在索引的定义中未提及排序规则,则使用 table 的排序规则并且如果在 table 的定义中没有提到排序规则,则使用 BINARY)。

对于 LIKE 您还必须打开 case_sensitive_like pragma:

PRAGMA case_sensitive_like=ON;

这使得 LIKE 区分大小写。

如果您不想更改 case_sensitive_like 杂注,请使用 GLOB (区分大小写):

select * from T where Path GLOB 'abcd*';

请参阅https://dbfiddle.uk/?rdbms=sqlite_3.27&fiddle=af61c6ab0f2644c71bb68fbe97e244ec

相似文章

最新文章