find和sort同时使用时，mongodb是如何处理的？

Question

今天在mongodb索引帮助文档里发现下边的描述:
集合order有以下索引：

{ qty: 1 }
{ status: 1, ord_date: -1 }
{ status: 1 }
{ ord_date: -1 }

查询语句：

db.orders.find( { qty: { $gt: 10 } } ).sort( { status: 1 } )

That is, MongoDB does not use the { qty: 1 } index for the query, and the separate { status: 1 } or the { status: 1, ord_date: -1 } index for the sort.

sort不能使用{ status: 1 } 或 { status: 1, ord_date: -1 } 我能理解，但是为什么find不能使用{ qty: 1 } 这个索引呢？

补充：是我理解错了，查询最后使用了{qty:1}这个索引

Answer 1

First question, have you tried which index is used for this query?
Let’s talk about that piece of English again. I think you may have misunderstood its meaning. This sentence means: the query engine will not use {qty:1} to satisfy findat the same time and then use {status : 1} or {status: 1, ord_date: -1} to satisfy sort. Let me put it more clearly: the query must either use {qty: 1} to satisfy find (and then use memory sorting), or use {status: 1} or {status: 1, ord_date: -1} satisfies the sorting (but the result set can only be found through set scanning). {qty:1}去满足find的同时再使用{status: 1}或{status: 1, ord_date: -1}去满足sort。我换个明白点的说法：查询要么使用{qty: 1}去满足find（然后使用内存排序），要么使用{status: 1}或{status: 1, ord_date: -1}满足排序（但是只能通过集合扫描查找结果集）。
你现有的索引中没有能够同时满足查询和排序的索引，能够达到这个目的的是：{status: 1, qty: 1}。但问题是这也并不见得就是一个好的选择。说不定{qty: 1}+内存排序会更高效。这完全取决于你的数据分布情况。具体原理其实跟RDBMS中的索引是一个道理，或者说使用B/B+树的索引都这德性。几句话说不清楚还是查阅一下相关资料吧。
简单的办法是把刚才提到的索引都建好，然后运行一下这个查询，让它们自己去撕吧，最终查询引擎会基于采样数据为你选出一个最好的索引。
所以回到最初的问题，你有没有去试一下到底选择了哪个索引呢？因为最终哪个索引撕赢了是基于你数据的分布情况确定的，这里没法给出确定的答案。我愿意赌{status: 1, qty: 1}There is no index in your existing index that can satisfy both query and sorting. The one that can achieve this purpose is: {status: 1, qty: 1}. But the problem is that this is not necessarily a good choice. Maybe {qty: 1} + memory sorting will be more efficient. It all depends on your data distribution. The specific principle is actually the same as the index in RDBMS, or the index using B/B+ tree has this virtue. If you can’t explain it clearly in a few words, let’s look up the relevant information.

The simple way is to build all the indexes just mentioned, then run this query and let them tear it up by themselves. In the end, the query engine will select the best index for you based on the sampled data. 🎜So back to the original question, have you tried which index to choose? Because which index wins in the end is determined based on the distribution of your data, there is no definite answer here. I'm willing to bet that {status: 1, qty: 1} wins. 🎜