Mongodb正则$regex查询慢的问题

$regex查询慢的原因，大部分是没有命中索引，如果需要命中索引有两点需要注意

1） 不能区分大小写 ，不要指定"$options":"i"

"$options":"i"

2）必须指定开头情况，才能使用索引

{"text":{"$regex":"^str"}}
{"text":{"$regex":"^(?!str)"}}

下面是官方关于$regex使用索引的说明

For case sensitive regular expression queries, if an index exists for the field, then MongoDB matches the regular expression against the values in the index, which can be faster than a collection scan. Further optimization can occur if the regular expression is a “prefix expression”, which means that all potential matches start with the same string. This allows MongoDB to construct a “range” from that prefix and only match against those values from the index that fall within that range.

A regular expression is a “prefix expression” if it starts with a caret (^) or a left anchor (\A), followed by a string of simple symbols. For example, the regex /^abc.*/ will be optimized by matching only against the values from the index that start with abc.

Additionally, while /^a/, /^a.*/, and /^a.*$/ match equivalent strings, they have different performance characteristics. All of these expressions use an index if an appropriate index exists; however, /^a.*/, and /^a.*$/ are slower. /^a/ can stop scanning after matching the prefix.

Case insensitive regular expression queries generally cannot use indexes effectively. The $regex implementation is not collation-aware and is unable to utilize case-insensitive indexes.

对于区分大小写的正则表达式查询，如果字段存在索引，那么MongoDB将正则表达式与索引中的值进行匹配，这可能比集合扫描快。如果正则表达式是“prefix expression”，这意味着所有潜在的匹配都以同一字符串开头，则可以进行进一步的优化。这允许MongoDB从该前缀构造一个“范围”，并且只与索引中属于该范围的值匹配。

如果正则表达式以插入符号（^）或左锚点（\A）开头，后跟一系列简单符号，则它是“前缀表达式”。例如，正则表达式/^abc.*/将通过仅与以abc开头的索引中的值匹配来优化。

此外，/^a/、/^a.*/和/^a.*$/匹配等效字符串时，它们具有不同的性能特征。如果存在适当的索引，所有这些表达式都使用索引；但是，/^a.*/和/^a.*$/速度较慢。/^a/可以在匹配前缀后停止扫描。

不区分大小写的正则表达式查询通常不能有效地使用索引。$regex实现不支持排序规则，因此无法使用不区分大小写的索引。