mysql - 数据库索引对MySQL中正则表达式查询的影响

Question

数据库索引旨在加快查找速度，但是在索引列的 where 子句中使用正则表达式的查询的性能如何。

假设我们有一个表 FILES，它有一个字段 FILE_NAME，我们在 FILE_NAME 上创建了一个索引。

然后我们有一个查询来搜索具有匹配名称模式的文件

SELECT * FROM FILES WHERE FILE_NAME RLIKE regexp

在 FILE_NAME 上创建索引是否有助于提高上述查询的性能？？

Answer 1

是的，它可能会提高性能 - 但不是您想象的那样。

在正常使用中，索引允许 DBMS 在不扫描每个条目的情况下查找表中的行 - 它可以跳过一些(对于范围比较、特定值和部分匹配，例如 LIKE 'ABC%' 但不是 LIKE '%XYZ' 的查询)。当您尝试使用数据的某种转换来查找行时(尽管有语法，RLIKE 是一个基于操作数而不是运算符的函数)，DBMS 必须将转换应用于表的每一行。一些 DBMS(例如 Oracle)支持基于函数的索引，因此只要您的正则表达式是常量，您就可以定义基于正则表达式匹配的索引 - MariaDB 支持可以被索引的虚拟列，这相当于同一件事。

因此，在此处使用索引不会减少 DBMS 为过滤查询而必须获取的行数。

但是，如果匹配数相对于基础数据中的行数较低，并且索引的宽度相对于表行的宽度较小，则 DBMS 可以通过读取来识别匹配行索引 - 与读取表行相比，它会更快并且需要更少的 I/O 操作。 OTOH 如果索引没有比它所代表的表更紧凑并且索引没有覆盖(即查询中的所有术语都可以从索引中得到满足)你会得到比没有索引更差的性能 - 因为 DBMS 必须执行一个在每次匹配后进行额外的查找和读取操作以获取数据 - 事实上，DBMS 很可能永远不会在没有明确提示的情况下使用这样的索引。