mongodb 联合索引和单个字段索引性能那个好些。

Question

现在遇到一个性能问题，解决办法就是给字段加索引，现在纠结的是字段组合索引还是单个索引查询效率问题？

场景
现在查询字段是parentId，key ,两个字段同时查询。

现在见索引的方案是
1 分别给 parentId,key添加索引

2 建一个组合索引 {parentId:1,key:1}这样的方式：

这两个查询性能是不是差不多啊？

求证

Answer 1

In terms of index efficiency, the joint index is definitely more efficient. In many cases, if you use multiple fields to query, you should consider using the joint index.
But things are not completely absolute, and the overhead of indexing must also be taken into consideration.
Take your conditions as an example. Assuming that key can uniquely determine a record, is it unnecessary to add parentId? key能够唯一确定一条记录，parentId是不是就没有必要加上了呢？
退一步，即使key不能唯一确定一条，如果它能够把结果集确定在一定的小范围内，比如5条记录，10条记录，那parentIdTake a step back, even if key cannot uniquely determine one, if it can determine the result set within a certain small range, such as 5 records or 10 records, then parentId This condition is nothing more than scanning these 10 records again to find the appropriate record. Compared with the writing, storage, and memory overhead caused by adding it to the index, I may choose not to put it into the joint index at all.
The more records a condition can filter out, the better its "selectivity" is. Generally speaking, when we build an index, we should put the conditions with better selectivity at the front and the conditions with poor selectivity at the back. If it's bad enough, don't let it in.
This is actually a balance of time and space. Conditions put into the index save time (including CPU time, query time) and space (including storage space, memory space); conditions not put into the index cost time and save space. Most of the time we expect the former. When to choose the latter depends on your own assessment of the actual situation.