MongoDB性能问题:单个大型集合与多个小型集合 [英] MongoDB performance issue: Single Huge collection vs Multiple Small Collections

问题描述

我测试了两种情况:单个大集合"与多个小集合"，并在查询时发现性能存在巨大差异.这就是我所做的.

I tested two scenarios Single Huge collection vs Multiple Small Collections and found huge difference in performance while querying. Here is what I did.

案例1:我创建了一个产品集合，其中包含10种不同类型产品的1000万条记录，在这种情况下，每种产品类型恰好具有100万条记录，并在ProductType上创建了索引.当我运行条件为ProductType = 1且ProductPrice> 100且limit(10)的示例查询以返回10条ProductType = 1且价格大于100的记录时，集合中有很多产品的价格约为35毫秒大于100，并且当我们在ProductType = 1中价格很少超过100的产品数量很少时，相同的查询就花费了大约8000毫秒(8秒).

Case 1: I created a product collection containing 10 million records for 10 different types of product, and in this exactly 1 million records for each product type, and I created index on ProductType. When I ran a sample query with condition ProductType=1 and ProductPrice>100 and limit(10) to return 10 records of ProductType=1 and whose price is greater than 100, it took about 35 milliseconds when the collection has lot of products whose price is more than 100, and the same query took about 8000 millisecond (8 second) when we have very less number of products in ProductType=1 whose price is greater than 100.

案例2:我为每个ProductType创建了10个不同的Product表，每个表包含一百万条记录.在包含productType 1记录的集合1中，当我使用条件ProductPrice> 100和limit(10)运行相同的示例查询以返回价格大于100的产品的10条记录时，当集合中有很多商品时，花费了约2.5毫秒的时间价格超过100的产品，而当我们查询的价格高于100的产品数量很少时，相同的查询大约需要1500毫秒(1.5秒).

Case 2: I created 10 different Product table for each ProductType each containing 1 million records. In collection 1 which contains records for productType 1, when I ran the same sample query with condition ProductPrice>100 and limit(10) to return 10 records of products whose price is greater than 100, it took about 2.5 milliseconds when the collection has lot of products whose price is more than 100, and the same query took about 1500 millisecond (1.5 second) when we have very less number of products whose price is greater than 100.

那为什么会有如此大的差异呢?案例一和案例二之间的唯一区别是一个巨大的集合与多个较小的集合，但是我在第一个案例中创建了ProductType的索引一个单个的巨大集合.我猜性能差异是由第一种情况下的索引引起的，而我在第一种情况下需要该索引，否则它的性能会更差.由于索引，我希望在第一种情况下性能会有所下降，但我没想到在第一种情况下性能会出现约10倍的巨大差异.

So why there is so much difference? The only difference between the case one and case two is one huge collection vs multiple smaller collection, but I have created index of ProductType in the first case one single huge collection. I guess the performance difference is caused by the Index in the first case, and I need that index in the first case otherwise it will be more worst in performance. I expected some performance slow in the first case due to the Index but I didn't expect the huge difference about 10 times slow in the first case.

因此，一个大型集合与多个小型集合的比较，分别是8000毫秒和1500毫秒.为什么?

So 8000 milliseconds vs 1500 milliseconds on one huge collection vs multiple small collection. Why?

推荐答案

分离集合将为您提供自由索引，而没有任何实际开销.索引扫描会产生开销，尤其是在索引不能真正帮助您减少必须扫描的结果数的情况下(如果索引中包含一百万个结果，但是您必须全部扫描并检查它们，不会对您有多大帮助).

Separating the collections gives you a free index without any real overhead. There is overhead for an index scan, especially if the index is not really helping you cut down on the number of results it has to scan (if you have a million results in the index, but you have to scan them all and inspect them, it's not going to help you much).

简而言之，将它们分开是一种有效的优化方法，但是在实际决定采用该路线之前，应该使索引更好地适合您的查询，我认为这是一种严厉的措施(产品价格指数可能会在很大程度上帮助您这种情况).

In short, separating them out is a valid optimization, but you should make your indexes better for your queries before you actually decide to take that route, which I consider a drastic measure (an index on product price might help you more in this case).

使用explain()可以帮助您了解查询的工作方式.一些基本知识是:理想情况下，您希望nscanned与n的比率低.通常，您不需要scanAndOrder = true，也不需要BasicCursor(这意味着您根本不使用索引).

Using explain() can help you understand how queries work. Some basics are: You want a low nscanned to n ratio, ideally. You don't want scanAndOrder = true, and you don't want BasicCursor, usually (this means you're not using an index at all).

这篇关于MongoDB性能问题:单个大型集合与多个小型集合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！