如何提高SQLITE LIKE语句性能

2022-05-23 00:00:00 performance sql sql-like sqlite

我使用这样的模式创建表:

CREATE TABLE wordIndex(id integer primary key, word varchar(128), offset integer, length integer);
CREATE INDEX word_idx on wordIndex(word);
现在表大约有450,000行记录。当我在ipod4上使用下面的LIKE语句时,性能不是很好: SELECT*FORM WORK WORE WORK‘TEST ACCESS%’; 使用解释输出:

explain select * from wordIndex where word like 'test acces%';
0|Trace|0|0|0||00|
1|Goto|0|16|0||00|
2|OpenRead|0|2|0|4|00|
3|Rewind|0|14|0||00|
4|String8|0|2|0|test acces%|00|
5|Column|0|1|3||00|
6|Function|1|2|1|like(2)|02|
7|IfNot|1|13|1||00|
8|Rowid|0|4|0||00|
9|Column|0|1|5||00|
10|Column|0|2|6||00|
11|Column|0|3|7||00|
12|ResultRow|4|4|0||00|
13|Next|0|4|0||01|
14|Close|0|0|0||00|
15|Halt|0|0|0||00|
16|Transaction|0|0|0||00|
17|VerifyCookie|0|2|0||00|
18|TableLock|0|2|0|wordIndex|00|
19|Goto|0|2|0||00|

我可能需要构建一个额外的倒排索引来提高性能,或者...? 谢谢预支!


解决方案

索引和like在大多数数据库中相处不好。如果可能,最好的方法是将查询重写为范围查询,因为随后将使用索引:

select *
from wordIndex
where word between 'test acces' and 'test acces{'

(左大括号是紧跟在‘z’后面的ASCII字符。)

如果您正在查找单词开头的模式(例如‘%test’),则您可能不得不接受全表扫描。

编辑:

索引和like*在当今大多数数据库中,当模式以常量开始时,like和*确实相处得很好,所以您可以这样做:

select *
from wordIndex
where word like 'test acces%' ;

不过,我对SQLite不是100%有把握,所以请检查执行计划,看看它是否使用了索引。

相关文章