mongodb 聚合查询添加索引后查询反而变慢?

Question

9.58K 浏览2023/01/12性能问题

0

xueliandan 16 2023/01/06 0条评论

小弟不才，mongodb 优化时间紧任务重，因此向各位大佬提问。
目前 mongodb 中有 130w 条数据，现在要统计当月 appName 的数量。然而 130w 条数据就需要 5s 以上的查询。

查询脚本如下：

db.<span class="hljs-property">UserBehaviorOnApp</span>.<span class="hljs-title function_">aggregate</span>([
    <span class="hljs-comment">// 匹配日期</span>
   {
       <span class="hljs-attr">$match</span>: {
           <span class="hljs-string">"accessTime"</span> : { 
               <span class="hljs-string">"$gte"</span> : <span class="hljs-title class_">ISODate</span>(<span class="hljs-string">"2022-12-06T06:04:53Z"</span>), 
               <span class="hljs-string">"$lt"</span> : <span class="hljs-title class_">ISODate</span>(<span class="hljs-string">"2023-01-06T06:04:53Z"</span>)
           }
       }
   },
   <span class="hljs-comment">// 根据 appName 进行分组，并且统计 appName 出现的次数。 $sum: 1 表示累计+1</span>
   { <span class="hljs-attr">$group</span>: { <span class="hljs-attr">_id</span>: <span class="hljs-string">"$appName"</span>,  <span class="hljs-attr">count</span>: { <span class="hljs-attr">$sum</span>: <span class="hljs-number">1</span> } } },
   <span class="hljs-comment">// 根据 count 进行倒序排列</span>
   { <span class="hljs-attr">$sort</span>: {<span class="hljs-attr">count</span>:-<span class="hljs-number">1</span>} },
   <span class="hljs-comment">// 取前五条记录</span>
   { $limit : <span class="hljs-number">5</span> }
])

explain结果如下

<span class="hljs-punctuation">{</span>
    <span class="hljs-attr">"explainVersion"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"1"</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"stages"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span>
        <span class="hljs-punctuation">{</span>
            <span class="hljs-attr">"$cursor"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"queryPlanner"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"namespace"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"user_behavior_log.UserBehaviorOnApp"</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"indexFilterSet"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"parsedQuery"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                        <span class="hljs-attr">"$and"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span>
                            <span class="hljs-punctuation">{</span>
                                <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                    <span class="hljs-attr">"$lt"</span> <span class="hljs-punctuation">:</span> ISODate(<span class="hljs-string">"2023-01-06T14:04:53.000+08:00"</span>)
                                <span class="hljs-punctuation">}</span>
                            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-punctuation">{</span>
                                <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                    <span class="hljs-attr">"$gte"</span> <span class="hljs-punctuation">:</span> ISODate(<span class="hljs-string">"2022-12-06T14:04:53.000+08:00"</span>)
                                <span class="hljs-punctuation">}</span>
                            <span class="hljs-punctuation">}</span>
                        <span class="hljs-punctuation">]</span>
                    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"queryHash"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"BDCC37AF"</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"planCacheKey"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"F85B6289"</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"maxIndexedOrSolutionsReached"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"maxIndexedAndSolutionsReached"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"maxScansToExplodeReached"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"winningPlan"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                        <span class="hljs-attr">"stage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"PROJECTION_SIMPLE"</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"transformBy"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                            <span class="hljs-attr">"appName"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"_id"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span>
                        <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"inputStage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                            <span class="hljs-attr">"stage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"COLLSCAN"</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"filter"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                <span class="hljs-attr">"$and"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span>
                                    <span class="hljs-punctuation">{</span>
                                        <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                            <span class="hljs-attr">"$lt"</span> <span class="hljs-punctuation">:</span> ISODate(<span class="hljs-string">"2023-01-06T14:04:53.000+08:00"</span>)
                                        <span class="hljs-punctuation">}</span>
                                    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                                    <span class="hljs-punctuation">{</span>
                                        <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                            <span class="hljs-attr">"$gte"</span> <span class="hljs-punctuation">:</span> ISODate(<span class="hljs-string">"2022-12-06T14:04:53.000+08:00"</span>)
                                        <span class="hljs-punctuation">}</span>
                                    <span class="hljs-punctuation">}</span>
                                <span class="hljs-punctuation">]</span>
                            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"direction"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"forward"</span>
                        <span class="hljs-punctuation">}</span>
                    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"rejectedPlans"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span> <span class="hljs-punctuation">]</span>
                <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                <span class="hljs-attr">"executionStats"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"executionSuccess"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">true</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"nReturned"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"executionTimeMillis"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">5769</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"totalKeysExamined"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"totalDocsExamined"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333343</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"executionStages"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                        <span class="hljs-attr">"stage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"PROJECTION_SIMPLE"</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"nReturned"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"executionTimeMillisEstimate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">344</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"works"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333345</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"advanced"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"needTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">145</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"needYield"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"saveState"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1379</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"restoreState"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1379</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"isEOF"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"transformBy"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                            <span class="hljs-attr">"appName"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"_id"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span>
                        <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"inputStage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                            <span class="hljs-attr">"stage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"COLLSCAN"</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"filter"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                <span class="hljs-attr">"$and"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span>
                                    <span class="hljs-punctuation">{</span>
                                        <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                            <span class="hljs-attr">"$lt"</span> <span class="hljs-punctuation">:</span> ISODate(<span class="hljs-string">"2023-01-06T14:04:53.000+08:00"</span>)
                                        <span class="hljs-punctuation">}</span>
                                    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                                    <span class="hljs-punctuation">{</span>
                                        <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                            <span class="hljs-attr">"$gte"</span> <span class="hljs-punctuation">:</span> ISODate(<span class="hljs-string">"2022-12-06T14:04:53.000+08:00"</span>)
                                        <span class="hljs-punctuation">}</span>
                                    <span class="hljs-punctuation">}</span>
                                <span class="hljs-punctuation">]</span>
                            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"nReturned"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"executionTimeMillisEstimate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">250</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"works"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333345</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"advanced"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"needTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">145</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"needYield"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"saveState"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1379</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"restoreState"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1379</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"isEOF"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"direction"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"forward"</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"docsExamined"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333343</span>
                        <span class="hljs-punctuation">}</span>
                    <span class="hljs-punctuation">}</span>
                <span class="hljs-punctuation">}</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"nReturned"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"executionTimeMillisEstimate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">2222</span>
        <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
        <span class="hljs-punctuation">{</span>
            <span class="hljs-attr">"$group"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"_id"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"$appName"</span><span class="hljs-punctuation">,</span>
                <span class="hljs-attr">"count"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"$sum"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                        <span class="hljs-attr">"$const"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span>
                    <span class="hljs-punctuation">}</span>
                <span class="hljs-punctuation">}</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"maxAccumulatorMemoryUsageBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"count"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">74767392</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"totalOutputDataSizeBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">237801844</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"usedDisk"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"nReturned"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1038436</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"executionTimeMillisEstimate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">5632</span>
        <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
        <span class="hljs-punctuation">{</span>
            <span class="hljs-attr">"$sort"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"sortKey"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"count"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">-1</span>
                <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                <span class="hljs-attr">"limit"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">5</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"totalDataSizeSortedBytesEstimate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">12005</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"usedDisk"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"nReturned"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">5</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"executionTimeMillisEstimate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">5768</span>
        <span class="hljs-punctuation">}</span>
    <span class="hljs-punctuation">]</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"serverInfo"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
        <span class="hljs-attr">"host"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"02a8a2b6c8dc"</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"port"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">27017</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"version"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"5.0.5"</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"gitVersion"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"d65fd89df3fc039b5c55933c0f71d647a54510ae"</span>
    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"serverParameters"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
        <span class="hljs-attr">"internalQueryFacetBufferSizeBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalQueryFacetMaxOutputDocSizeBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalLookupStageIntermediateDocumentMaxSizeBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalDocumentSourceGroupMaxMemoryBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalQueryMaxBlockingSortMemoryUsageBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalQueryProhibitBlockingMergeOnMongoS"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalQueryMaxAddToSetBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalDocumentSourceSetWindowFieldsMaxMemoryBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span>
    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"command"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
        <span class="hljs-attr">"aggregate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"UserBehaviorOnApp"</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"pipeline"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span>
            <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"$match"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                        <span class="hljs-attr">"$gte"</span> <span class="hljs-punctuation">:</span> ISODate(<span class="hljs-string">"2022-12-06T14:04:53.000+08:00"</span>)<span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"$lt"</span> <span class="hljs-punctuation">:</span> ISODate(<span class="hljs-string">"2023-01-06T14:04:53.000+08:00"</span>)
                    <span class="hljs-punctuation">}</span>
                <span class="hljs-punctuation">}</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"$group"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"_id"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"$appName"</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"count"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                        <span class="hljs-attr">"$sum"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span>
                    <span class="hljs-punctuation">}</span>
                <span class="hljs-punctuation">}</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"$sort"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"count"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">-1</span>
                <span class="hljs-punctuation">}</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"$limit"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">5</span>
            <span class="hljs-punctuation">}</span>
        <span class="hljs-punctuation">]</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"cursor"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>

        <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"$db"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"user_behavior_log"</span>
    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"ok"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"$clusterTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
        <span class="hljs-attr">"clusterTime"</span> <span class="hljs-punctuation">:</span> Timestamp(<span class="hljs-number">1672991848</span><span class="hljs-punctuation">,</span> <span class="hljs-number">1</span>)<span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"signature"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
            <span class="hljs-attr">"hash"</span> <span class="hljs-punctuation">:</span> BinData(<span class="hljs-number">0</span><span class="hljs-punctuation">,</span><span class="hljs-string">"AAAAAAAAAAAAAAAAAAAAAAAAAAA="</span>)<span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"keyId"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span>
        <span class="hljs-punctuation">}</span>
    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"operationTime"</span> <span class="hljs-punctuation">:</span> Timestamp(<span class="hljs-number">1672991848</span><span class="hljs-punctuation">,</span> <span class="hljs-number">1</span>)
<span class="hljs-punctuation">}</span>

花了 5s 多。

然后我尝试给 accessTime 添加索引。

db.<span class="hljs-property">UserBehaviorOnApp</span>.<span class="hljs-title function_">createIndex</span>({<span class="hljs-string">"accessTime"</span>: <span class="hljs-number">1</span>})

然后再执行同样的脚本查询。发现给我干到 10 s 去了。

explain结果如下

<span class="hljs-punctuation">{</span>
    <span class="hljs-attr">"explainVersion"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"1"</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"stages"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span>
        <span class="hljs-punctuation">{</span>
            <span class="hljs-attr">"$cursor"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"queryPlanner"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"namespace"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"user_behavior_log.UserBehaviorOnApp"</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"indexFilterSet"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"parsedQuery"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                        <span class="hljs-attr">"$and"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span>
                            <span class="hljs-punctuation">{</span>
                                <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                    <span class="hljs-attr">"$lt"</span> <span class="hljs-punctuation">:</span> ISODate(<span class="hljs-string">"2023-01-06T14:04:53.000+08:00"</span>)
                                <span class="hljs-punctuation">}</span>
                            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-punctuation">{</span>
                                <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                    <span class="hljs-attr">"$gte"</span> <span class="hljs-punctuation">:</span> ISODate(<span class="hljs-string">"2022-12-06T14:04:53.000+08:00"</span>)
                                <span class="hljs-punctuation">}</span>
                            <span class="hljs-punctuation">}</span>
                        <span class="hljs-punctuation">]</span>
                    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"queryHash"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"BDCC37AF"</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"planCacheKey"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"D418D338"</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"maxIndexedOrSolutionsReached"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"maxIndexedAndSolutionsReached"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"maxScansToExplodeReached"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"winningPlan"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                        <span class="hljs-attr">"stage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"PROJECTION_SIMPLE"</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"transformBy"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                            <span class="hljs-attr">"appName"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"_id"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span>
                        <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"inputStage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                            <span class="hljs-attr">"stage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"FETCH"</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"inputStage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                <span class="hljs-attr">"stage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"IXSCAN"</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"keyPattern"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                    <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span>
                                <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"indexName"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"accessTime_1"</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"isMultiKey"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"multiKeyPaths"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                    <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span> <span class="hljs-punctuation">]</span>
                                <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"isUnique"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"isSparse"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"isPartial"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"indexVersion"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">2</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"direction"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"forward"</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"indexBounds"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                    <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span> <span class="hljs-string">"[new Date(1670306693000), new Date(1672985093000))"</span> <span class="hljs-punctuation">]</span>
                                <span class="hljs-punctuation">}</span>
                            <span class="hljs-punctuation">}</span>
                        <span class="hljs-punctuation">}</span>
                    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"rejectedPlans"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span> <span class="hljs-punctuation">]</span>
                <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                <span class="hljs-attr">"executionStats"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"executionSuccess"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">true</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"nReturned"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"executionTimeMillis"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">10107</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"totalKeysExamined"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"totalDocsExamined"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"executionStages"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                        <span class="hljs-attr">"stage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"PROJECTION_SIMPLE"</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"nReturned"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"executionTimeMillisEstimate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">2092</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"works"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333200</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"advanced"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"needTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"needYield"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"saveState"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1379</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"restoreState"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1379</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"isEOF"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"transformBy"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                            <span class="hljs-attr">"appName"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"_id"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span>
                        <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"inputStage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                            <span class="hljs-attr">"stage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"FETCH"</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"nReturned"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"executionTimeMillisEstimate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1986</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"works"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333200</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"advanced"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"needTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"needYield"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"saveState"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1379</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"restoreState"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1379</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"isEOF"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"docsExamined"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"alreadyHasObj"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
                            <span class="hljs-attr">"inputStage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                <span class="hljs-attr">"stage"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"IXSCAN"</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"nReturned"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"executionTimeMillisEstimate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">324</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"works"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333200</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"advanced"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"needTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"needYield"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"saveState"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1379</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"restoreState"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1379</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"isEOF"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"keyPattern"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                    <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span>
                                <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"indexName"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"accessTime_1"</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"isMultiKey"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"multiKeyPaths"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                    <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span> <span class="hljs-punctuation">]</span>
                                <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"isUnique"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"isSparse"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"isPartial"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"indexVersion"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">2</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"direction"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"forward"</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"indexBounds"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                    <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span> <span class="hljs-string">"[new Date(1670306693000), new Date(1672985093000))"</span> <span class="hljs-punctuation">]</span>
                                <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"keysExamined"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"seeks"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"dupsTested"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"dupsDropped"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
                                <span class="hljs-attr">"indexDef"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                    <span class="hljs-attr">"indexName"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"accessTime_1"</span><span class="hljs-punctuation">,</span>
                                    <span class="hljs-attr">"isMultiKey"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                                    <span class="hljs-attr">"multiKeyPaths"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                        <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span> <span class="hljs-punctuation">]</span>
                                    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                                    <span class="hljs-attr">"keyPattern"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                                        <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span>
                                    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                                    <span class="hljs-attr">"isUnique"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                                    <span class="hljs-attr">"isSparse"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                                    <span class="hljs-attr">"isPartial"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
                                    <span class="hljs-attr">"direction"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"forward"</span>
                                <span class="hljs-punctuation">}</span>
                            <span class="hljs-punctuation">}</span>
                        <span class="hljs-punctuation">}</span>
                    <span class="hljs-punctuation">}</span>
                <span class="hljs-punctuation">}</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"nReturned"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1333199</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"executionTimeMillisEstimate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">6492</span>
        <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
        <span class="hljs-punctuation">{</span>
            <span class="hljs-attr">"$group"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"_id"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"$appName"</span><span class="hljs-punctuation">,</span>
                <span class="hljs-attr">"count"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"$sum"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                        <span class="hljs-attr">"$const"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span>
                    <span class="hljs-punctuation">}</span>
                <span class="hljs-punctuation">}</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"maxAccumulatorMemoryUsageBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"count"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">74767392</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"totalOutputDataSizeBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">237801844</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"usedDisk"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"nReturned"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1038436</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"executionTimeMillisEstimate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">9928</span>
        <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
        <span class="hljs-punctuation">{</span>
            <span class="hljs-attr">"$sort"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"sortKey"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"count"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">-1</span>
                <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
                <span class="hljs-attr">"limit"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">5</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"totalDataSizeSortedBytesEstimate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">11760</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"usedDisk"</span> <span class="hljs-punctuation">:</span> <span class="hljs-keyword">false</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"nReturned"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">5</span><span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"executionTimeMillisEstimate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">10101</span>
        <span class="hljs-punctuation">}</span>
    <span class="hljs-punctuation">]</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"serverInfo"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
        <span class="hljs-attr">"host"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"02a8a2b6c8dc"</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"port"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">27017</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"version"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"5.0.5"</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"gitVersion"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"d65fd89df3fc039b5c55933c0f71d647a54510ae"</span>
    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"serverParameters"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
        <span class="hljs-attr">"internalQueryFacetBufferSizeBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalQueryFacetMaxOutputDocSizeBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalLookupStageIntermediateDocumentMaxSizeBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalDocumentSourceGroupMaxMemoryBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalQueryMaxBlockingSortMemoryUsageBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalQueryProhibitBlockingMergeOnMongoS"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalQueryMaxAddToSetBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"internalDocumentSourceSetWindowFieldsMaxMemoryBytes"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">104857600</span>
    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"command"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
        <span class="hljs-attr">"aggregate"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"UserBehaviorOnApp"</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"pipeline"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">[</span>
            <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"$match"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"accessTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                        <span class="hljs-attr">"$gte"</span> <span class="hljs-punctuation">:</span> ISODate(<span class="hljs-string">"2022-12-06T14:04:53.000+08:00"</span>)<span class="hljs-punctuation">,</span>
                        <span class="hljs-attr">"$lt"</span> <span class="hljs-punctuation">:</span> ISODate(<span class="hljs-string">"2023-01-06T14:04:53.000+08:00"</span>)
                    <span class="hljs-punctuation">}</span>
                <span class="hljs-punctuation">}</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"$group"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"_id"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"$appName"</span><span class="hljs-punctuation">,</span>
                    <span class="hljs-attr">"count"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                        <span class="hljs-attr">"$sum"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span>
                    <span class="hljs-punctuation">}</span>
                <span class="hljs-punctuation">}</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"$sort"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
                    <span class="hljs-attr">"count"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">-1</span>
                <span class="hljs-punctuation">}</span>
            <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
            <span class="hljs-punctuation">{</span>
                <span class="hljs-attr">"$limit"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">5</span>
            <span class="hljs-punctuation">}</span>
        <span class="hljs-punctuation">]</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"cursor"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>

        <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"$db"</span> <span class="hljs-punctuation">:</span> <span class="hljs-string">"user_behavior_log"</span>
    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"ok"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">1</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"$clusterTime"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
        <span class="hljs-attr">"clusterTime"</span> <span class="hljs-punctuation">:</span> Timestamp(<span class="hljs-number">1672991948</span><span class="hljs-punctuation">,</span> <span class="hljs-number">3</span>)<span class="hljs-punctuation">,</span>
        <span class="hljs-attr">"signature"</span> <span class="hljs-punctuation">:</span> <span class="hljs-punctuation">{</span>
            <span class="hljs-attr">"hash"</span> <span class="hljs-punctuation">:</span> BinData(<span class="hljs-number">0</span><span class="hljs-punctuation">,</span><span class="hljs-string">"AAAAAAAAAAAAAAAAAAAAAAAAAAA="</span>)<span class="hljs-punctuation">,</span>
            <span class="hljs-attr">"keyId"</span> <span class="hljs-punctuation">:</span> <span class="hljs-number">0</span>
        <span class="hljs-punctuation">}</span>
    <span class="hljs-punctuation">}</span><span class="hljs-punctuation">,</span>
    <span class="hljs-attr">"operationTime"</span> <span class="hljs-punctuation">:</span> Timestamp(<span class="hljs-number">1672991948</span><span class="hljs-punctuation">,</span> <span class="hljs-number">3</span>)
<span class="hljs-punctuation">}</span>

到底如何添加索引，或者说脚本怎么写才能优化这个查询时间。恳请走过路过的大佬看一下，非常感谢，在这里给大家先拜个早年。祝大家身体健康，万事如意，码手生花。

Mr.Mongo 更改状态以发布 2023/01/12

mongodb 聚合查询添加索引后查询反而变慢?

0 答案

回顶部

关注微信：mongoing-mongoing
复制微信号

提交工单咨询

0 答案

回顶部

关注微信：mongoing-mongoing复制微信号

提交工单咨询

关注微信：mongoing-mongoing
复制微信号