Mongo查询慢日志-如何优化

Question

查询1：

db.test1 command: find { find: "test1", filter: { company: ObjectId('xyz'), author: ObjectId('abc'), aggregated: false, as_of_date: { $gte: new Date(1420070400000) } }, $db: "data", $readPreference: { mode: "primary" } } planSummary: IXSCAN { aggregated: 1 } keysExamined:78445 docsExamined:78445 cursorExhausted:1 numYields:612 nreturned:0 reslen:100 locks:{ Global: { acquireCount: { r: 1226 } }, Database: { acquireCount: { r: 613 } }, Collection: { acquireCount: { r: 613 } } } protocol:op_msg 121ms

收集计数-> 399,999,999文档( Approx值)

Query2：

db.test2 command: find { find: "test2", filter: { as_of_date: { $gte: new Date(1644969600000) }, holdings_type: "etf", issuer: "ABCD", ticker: "EFG", cusip: "HIJK", author: ObjectId('xyz'), company: ObjectId('abc') }, $db: "data", $readPreference: { mode: "primary" } } planSummary: IXSCAN { author: 1 } keysExamined:94587 docsExamined:94587 cursorExhausted:1 numYields:738 nreturned:2 reslen:576 locks:{ Global: { acquireCount: { r: 1478 } }, Database: { acquireCount: { r: 739 } }, Collection: { acquireCount: { r: 739 } } } protocol:op_msg 194ms

收集计数-> 3,399,999,999,999文档( Approx值)

你能帮我优化这个查询吗。我已经在两个集合中的每个字段上创建了索引。我没有创建复合索引。这是解决问题的办法，还是有其他解决办法？

Answer 1

以前，这只使用一个索引来查找。在我创建复合索引之后，现在的查询大约是20 is 50 is。但是，我仍然在寻找更好的解决方案，因为复合索引占用了巨大的空间。