$lt和$gt查询在无索引的数字字段上明显比$eq快

Question

const result = await sandbox
.aggregate([
{
$match: {
B: { $eq: 500 },
// B: { $lt: 500 }, faster
// B: { $gt: 500 }, faster
},
},
{ $limit: 1000 },
])

B是一个未索引的数字，范围从1到1000，是1.5m文档集合中的字段。

$gt和$lt的查询速度比$eq快5 ~ 10倍。我已经反复测试过了。

如何解释这种差异?

Answer 1

数据的分布和匹配查询的文档的(完整)大小。

我相信你所描述的行为是可以预料到的。如果有的话，我希望性能差异实际上比上面提到的要大。

由于数据库没有索引可用于此操作，因此它将被迫执行集合扫描。现在让我们考虑一下集合扫描在执行过程中会是什么样子。对于1.5 million文档和1,000不同的(随机的)值，我们可以预期大约有1,500文档包含每个单独的值。基于此，我们预计集合中大约有1,500个文档与相等条件的查询匹配，但大约有一半的文档(750,000)与范围条件的其他查询匹配。

假设一个完全随机分布，其中每个值在每个1,000文档中只出现一次，当数据库开始扫描每个查询的集合时，将发生以下情况:

• 相等性-数据库扫描的每个文档将有一个0.1%(1/1000)匹配过滤器的机会。由于查询请求的结果集大小为1,000文档，我们可以预期数据库在完成之前必须扫描result_set_size / chance_of_matching = 1,000 / .001 = 1 million文档(集合的完整2/3s)。
• Range -数据库扫描的每个文档将有大约一个50%的机会(500/1000)匹配过滤器。使用前面的公式，我们现在期望数据库在完成之前扫描result_set_size / chance_of_matching = 1,000 / .5 = 2,000文档。

这意味着数据库可能比范围查询多做3个数量级的工作来满足相等性查询。这就是为什么我感到惊讶的是，查询持续时间的差异不超过10倍。

我无法在mongoplayground上创建足够的文档来进行适当的演示，所以我在本地环境中做了以下操作。首先，我通过:

创建集合

var bulk = db.foo.initializeUnorderedBulkOp();
for(i = 0; i < 1500000; i++){bulk.insert({_id:i, B: Math.floor(Math.random()*1000)})}
bulk.execute();

这导致数据的如下分布:

> db.foo.count({B:500})
1592
> db.foo.count({B:{$gt:500}})
747873
> db.foo.count({B:{$lt:500}})
750535

然后我们可以使用explain()和executionStats来确定数据库执行这些查询所要做的工作量。对于等式:

> db.foo.aggregate([ { $match: { B: { $eq: 500 }  } }, { $limit: 1000 }]).explain("executionStats").executionStats
{
nReturned: 1000,
executionTimeMillis: 332,
totalKeysExamined: 0,
totalDocsExamined: 915535,
...

对于范围查询:

> db.foo.aggregate([ { $match: { B: { $gt: 500 }  } }, { $limit: 1000 }]).explain("executionStats").executionStats
{
nReturned: 1000,
executionTimeMillis: 1,
totalKeysExamined: 0,
totalDocsExamined: 2047,
...
> db.foo.aggregate([ { $match: { B: { $lt: 500 }  } }, { $limit: 1000 }]).explain("executionStats").executionStats
{
nReturned: 1000,
executionTimeMillis: 1,
totalKeysExamined: 0,
totalDocsExamined: 1957,

所有这些数字都与我们基于早期思考的预期相符。

除了

顺便说一下，这个配置有助于说明为什么索引在检索一小部分结果时最有帮助。如果要在B字段上添加索引，我希望发生以下两种情况:

1. 相等性查询的性能大幅度提高。
2. 范围查询的性能比没有索引时差。

在检索相关数据之前遍历索引是有成本的。通常情况下，如果查询(非常粗略地)匹配20%或更多的集合，则通过扫描整个集合而不是使用索引来执行得更好。