SQLite3 正则表达式性能

Question

SQLite3 REGEXP 运算符的性能如何？

为简单起见，假设一个具有单列的简单表 pattern和一个索引

CREATE TABLE `foobar` (`pattern` TEXT);
CREATE UNIQUE INDEX `foobar_index` ON `foobar`(`pattern`);

和一个像这样的查询

SELECT * FROM `foobar` WHERE `pattern` REGEXP 'foo.*'

我一直在尝试比较和理解 EXPLAIN 的输出它似乎类似于使用 LIKE除了它将使用正则表达式进行匹配。但是，我不完全确定如何读取 EXPLAIN 的输出。而且我不知道它的性能如何。

我知道与索引 WHERE `pattern` = 'foo' 相比它会很慢查询，但它是否较慢/类似于 LIKE ?

Answer 1

sqlite 没有优化 WHERE ... REGEXP ...使用索引。 x REGEXP y只是一个函数调用；相当于regexp(x,y) .另请注意，并非所有安装的 sqlite 都有 regexp因此使用它(或 REGEXP 运算符)定义的函数不是很便携。 LIKE/GLOB另一方面，可以利用前缀查询的索引，前提是 some additional conditions are met :

1. The right-hand side of the LIKE or GLOB must be either a string literal or a parameter bound to a string literal that does not begin with a wildcard character.
2. It must not be possible to make the LIKE or GLOB operator true by having a numeric value (instead of a string or blob) on the left-hand side. This means that either: the left-hand side of the LIKE or GLOB operator is the name of an indexed column with TEXT affinity, or the right-hand side pattern argument does not begin with a minus sign ("-") or a digit. This constraint arises from the fact that numbers do not sort in lexicographical order. For example: 9<10 but '9'>'10'.
3. The built-in functions used to implement LIKE and GLOB must not have been overloaded using the sqlite3_create_function() API.
4. For the GLOB operator, the column must be indexed using the built-in BINARY collating sequence.
5. For the LIKE operator, if case_sensitive_like mode is enabled then the column must indexed using BINARY collating sequence, or if case_sensitive_like mode is disabled then the column must indexed using built-in NOCASE collating sequence.
6. If the ESCAPE option is used, the ESCAPE character must be ASCII, or a single-byte character in UTF-8.