mongodb優(yōu)化查詢

查詢優(yōu)化器

MongoDB的查詢計(jì)劃會將多個(gè)索引并行去執(zhí)行孩饼，最先返回101的結(jié)果就是勝者，其他查詢計(jì)劃就會被終止国觉，執(zhí)行優(yōu)勝的查詢計(jì)劃
這個(gè)查詢計(jì)劃將會被緩存猾普，接下來相同的語句查詢條件都會使用它

  • 何時(shí)查詢計(jì)劃才會變
  1. 建立索引時(shí)
  2. 每執(zhí)行1000次查詢之后，查詢優(yōu)化器就會重新評估查詢計(jì)劃
  3. 較大的數(shù)據(jù)波動

explain 使用

db.getCollection('db_name').explain('executionStats').aggregate([....])
// 得到的結(jié)果
{
    "stages" : [ 
        {
            "$cursor" : {
                "query" : {
                    .....
                },
                "fields" : {
                    ......
                },
                "queryPlanner" : {
                    ......
                    },
                    "winningPlan" : {                  // 優(yōu)勝的方案
                        "stage" : "FETCH",
                        "filter" : {
                            "$and" : [ 
                                {
                                    "is_deleted" : {
                                        "$eq" : 0.0
                                    }
                                }
                            ]
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "acc_opening_date" : 1
                            },
                            "indexName" : "acc_opening_date_1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "acc_opening_date" : []
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "acc_opening_date" : [ 
                                    "[new Date(1493625600000), new Date(1609350406000)]"
                                ]
                            }
                        }
                    },
                    "rejectedPlans" : [    // 拒絕的方案
                        {
                            "stage" : "FETCH",
                            "filter" : {
                                "$and" : [ 
                                    {
                                        "is_deleted" : {
                                            "$eq" : 0.0
                                        }
                                    }, 
                                    {
                                        "acc_opening_date" : {
                                            "$lte" : ISODate("2020-12-30T17:46:46.000Z")
                                        }
                                    }, 
                                    {
                                        "acc_opening_date" : {
                                            "$gte" : ISODate("2017-05-01T08:00:00.000Z")
                                        }
                                    }
                                ]
                            },
                     
                        }, 
                        {
                            "stage" : "FETCH",
                            "filter" : {
                                "$and" : [ 
                                    {
                                        "acc_opening_date" : {
                                            "$lte" : ISODate("2020-12-30T17:46:46.000Z")
                                        }
                                    }, 
                                    {
                                        "acc_opening_date" : {
                                            "$gte" : ISODate("2017-05-01T08:00:00.000Z")
                                        }
                                    }
                                ]
                            },
                            "inputStage" : {
                                "stage" : "IXSCAN",  
                                "keyPattern" : {
                                    "is_deleted" : 1  //  is_deteted顆粒度這么大的索引不應(yīng)該建立锰扶，難怪要被拒絕
                                },
                                "indexName" : "is_deleted_1",  
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                    "is_deleted" : []
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                    "is_deleted" : [ 
                                        "[0.0, 0.0]"
                                    ]
                                }
                            }
                        }
                    ]
                },
                "executionStats" : {
                    "executionSuccess" : true,
                    "nReturned" : 1,     // 返回的結(jié)果數(shù)量
                    "executionTimeMillis" : 1,  // 運(yùn)行的時(shí)間
                    "totalKeysExamined" : 1,  // 掃描的索引數(shù)量
                    "totalDocsExamined" : 1,  // 掃描的文檔數(shù)量
                    "executionStages" : {
                        "stage" : "FETCH",                           //  step2: 用is_deleted字段從上一階段的結(jié)果中過濾出相應(yīng)結(jié)果
                        "filter" : {
                            "$and" : [ 
                                {
                                    "is_deleted" : {
                                        "$eq" : 0.0
                                    }
                                },
                            ]
                        },
                        "nReturned" : 1,
                        "executionTimeMillisEstimate" : 0,
                        "works" : 3,
                        "advanced" : 1,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 1,
                        "restoreState" : 1,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "docsExamined" : 1,
                        "alreadyHasObj" : 0,
                        "inputStage" : {
                            "stage" : "IXSCAN",                        //  step1: 用acc_opening_date字段索引搜索出相應(yīng)結(jié)果
                            "nReturned" : 1,
                            "executionTimeMillisEstimate" : 0,
                            "works" : 2,
                            "advanced" : 1,
                            "needTime" : 0,
                            "needYield" : 0,
                            "saveState" : 1,
                            "restoreState" : 1,
                            "isEOF" : 1,
                            "invalidates" : 0,
                            "keyPattern" : {
                                "acc_opening_date" : 1                                    
                            },
                            "indexName" : "acc_opening_date_1",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "acc_opening_date" : []
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "acc_opening_date" : [ 
                                    "[new Date(1493625600000), new Date(1609350406000)]"
                                ]
                            },
                            "keysExamined" : 1,
                            "seeks" : 1,
                            "dupsTested" : 0,
                            "dupsDropped" : 0,
                            "seenInvalidated" : 0
                        }
                    }
                }
            }
        }, 
        {
            "$lookup" : {
                "from" : "clients",
                "as" : "clients",
                "localField" : "idp_user_id",
                "foreignField" : "idp_user_id",
                "unwinding" : {
                    "preserveNullAndEmptyArrays" : false
                }
            }
        }, 
        {
            "$project" : {
               ......
        }
    ],
    "ok" : 1.0,
    "operationTime" : Timestamp(1604133360, 3),
    "$clusterTime" : {
        "clusterTime" : Timestamp(1604133360, 3),
        "signature" : {
            "hash" : { "$binary" : "RbWJfLtWiuIthJ5C3oiKbGIt1iY=", "$type" : "00" },
            "keyId" : NumberLong(6854528299859705857)
        }
    }
}
  • 查看方式：嵌套最內(nèi)層往外的順序看献酗，不是從上到下。

原因：
explain 結(jié)果將查詢計(jì)劃以階段樹的形式呈現(xiàn)坷牛。
每個(gè)階段將其結(jié)果傳遞給父節(jié)點(diǎn)罕偎，中間節(jié)點(diǎn)操作由子節(jié)點(diǎn)產(chǎn)生的文檔或索引

  • 索引使用情況解讀

stage 主要分為以下幾種：
COLLSACN: 全盤掃描
IXSACN: 索引掃描
FETCH: 根據(jù)前面節(jié)點(diǎn)掃描出的文檔，進(jìn)一步過濾抓取
SORT: 內(nèi)存進(jìn)行排序
SORT_KEY_GENERATOR: 獲取每一個(gè)文檔排序所用的鍵值
LIMIT: 使用limit限制返回?cái)?shù)
SKIP: 使用skip進(jìn)行跳過
IDHACK: 針對_id進(jìn)行查詢
COUNTSCAN: count不使用index進(jìn)行count
COUNT_SCACN: count使用index進(jìn)行count
TEXT: 使用全文索引進(jìn)行查詢
SUBPLA：未使用到索引的$or查詢
PROJECTION：限定返回字段

  • 所以不希望看到explain分析出現(xiàn)如下的stage:

COLLSCAN
SORT
COUNTSCAN
SUBPLA

  • 最好是如下的組合：

FETCH + IXSCAN
FETCH + IDHACK
LIMIT + ( FETCH + IXSCAN)
PROJECTION + IXSCAN
COUNT_SCAN

效率極低的操作符

  1. $where和$exists：這兩個(gè)操作符京闰，完全不能使用索引颜及。
  2. $ne和$not: 通常來說取反和不等于,可以使用索引，但是效率極低蹂楣，不是很有效器予，往往也會退化成掃描全表。
  3. $nin: 不包含捐迫，這個(gè)操作符也總是會全表掃描
  4. 對于管道中的索引，也很容易出現(xiàn)意外爱葵，只有在管道最開始時(shí)的match sort可以使用到索引施戴，一旦發(fā)生過project投射，group分組萌丈，lookup表關(guān)聯(lián)赞哗，unwind打散等操作后，就完全無法使用索引辆雾。

aggregate優(yōu)化

  1. 我認(rèn)為的準(zhǔn)則是盡可能先縮小文檔大蟹舅瘛（例如：$match,）， 然后再排序（ $sort, $limit, $skip），最后進(jìn)行其他復(fù)雜操作（$lookup, $project, $group, $unwind）,因?yàn)檫@些操作打散后藤乙，完全無法使用索引.
    最佳順序： $match + $sort + $limit + ...
  2. 千萬別忘了$lookup連表的字段猜揪，兩張表一定要建立索引
  • 優(yōu)化案例1：limit提前縮小文檔大小，減少內(nèi)存計(jì)算
// 使用時(shí)間: 0.044 S
db.getCollection('clients').explain("executionStats").aggregate([ 
    { '$match': { is_deleted: 0 } },
    { '$sort': { gmt_create: -1 } }, 
    { '$lookup': 
        { from: 'client_infos', 
            localField: 'client_info_ids', 
            foreignField: '_id', as: 'client_infos' }
    }, 
    { '$lookup': 
        { from: 'accounts', 
        localField: 'account_id', 
        foreignField: '_id', as: 'account' } 
    }, 
    { '$unwind': '$account' }, 
    { '$skip': 0 }, 
    { '$limit': 10 }], {})

// 使用時(shí)間： 0.021s
db.getCollection('clients').explain("executionStats").aggregate([ 
    { '$match': { is_deleted: 0 } },
    { '$sort': { gmt_create: -1 } }, 
    { '$skip': 0 }, 
    { '$limit': 10 },
    { '$lookup': 
        { from: 'client_infos', 
            localField: 'client_info_ids', 
            foreignField: '_id', as: 'client_infos' }
    }, 
    { '$lookup': 
        { from: 'accounts', 
        localField: 'account_id', 
        foreignField: '_id', as: 'account' } 
    }, 
    { '$unwind': '$account' }, 
    ], {})
  • 優(yōu)化案例2： 轉(zhuǎn)換搜索的主表坛梁，使索引生效
// 使用時(shí)間: 3.64s
db.getCollection('clients').explain("executionStats").aggregate([ 
    { '$lookup': 
        { from: 'client_infos', 
            localField: 'client_info_ids', 
            foreignField: '_id', as: 'client_infos' }
    }, 
    { '$match': { 'client_infos.phone': 110 } },
    { '$lookup': 
        {
            from: 'accounts', 
            localField: 'account_id', 
            foreignField: '_id', 
            as: 'account',
        }
    }, 
    { '$unwind': '$account' },
    { '$skip': 0 }, 
    { '$limit': 10 }, 
    ], {})
    
// 使用時(shí)間：0.065s
db.getCollection('client_infos').aggregate([ 
    { '$match': { phone: 110, is_deleted: 0} },
    { '$skip': 0 }, 
    { '$limit': 10 }, 
    { '$lookup': 
        { from: 'clients', 
            localField: '_id', 
            foreignField: 'client_info_ids', as: 'clients' }
    }, 
    { '$unwind': '$clients' },
    { '$lookup': 
        { 
            from: 'accounts', 
            localField: 'clients.account_id', 
            foreignField: '_id', 
            as: 'account',
        }
    }, 
    { '$unwind': '$account' },
    ], {})

索引設(shè)計(jì)原則

  • 索引字段顆粒度越小越好

顆粒度為結(jié)果集在原集合中所占的比例
顆粒度小的垫挨，例如身份證號等唯一性質(zhì)的状蜗，索引掃描能夠很快定位出位置
相反字段顆粒度大的，例如枚舉，例如布爾值育特，索引定位出的位置不夠精準(zhǔn)，到頭來還得大部分掃描蹂匹，因?yàn)槎嗔怂饕龗呙璧憾迹詈笏俣瓤赡苓€不如全盤掃描。

  • 字段更新頻率小

索引的缺點(diǎn)之一就是修改時(shí)還需要維護(hù)索引队魏，所以最好選擇字段更新比較小的字段

  • 適當(dāng)冗余設(shè)計(jì)

aggregate連表查詢公般。如果查詢字段在副表中，就無法使用到索引器躏，如果這種連表查詢頻率較高俐载，可考慮冗余設(shè)計(jì)。如上訴案例2登失，可通過冗余phone的字段提高查詢效率遏佣，以及增加代碼通用性

  • 索引數(shù)量控制

一個(gè)索引的字段超過7，8揽浙，需考慮合理性

查詢優(yōu)化原則

  • 減少帶寬

按需取字段状婶，避免返回大字段

  • 減少內(nèi)存計(jì)算

減少中間存儲，內(nèi)存計(jì)算

  • 減少磁盤IO

增加索引馅巷，避免全盤掃描膛虫，優(yōu)化sql

參考文檔：
https://zhuanlan.zhihu.com/p/77971681
https://docs.mongodb.com/manual/core/aggregation-pipeline/#aggregation-pipeline-operators-and-performance
https://docs.mongodb.com/manual/core/aggregation-pipeline-optimization/
https://jira.mongodb.org/browse/SERVER-28140
https://stackoverflow.com/questions/59811200/lookup-wont-use-indexes-in-second-match-how-can-we-scale

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末，一起剝皮案震驚了整個(gè)濱河市钓猬，隨后出現(xiàn)的幾起案子稍刀，更是在濱河造成了極大的恐慌，老刑警劉巖敞曹，帶你破解...
    沈念sama閱讀 217,734評論 6贊 505
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件账月，死亡現(xiàn)場離奇詭異，居然都是意外死亡澳迫，警方通過查閱死者的電腦和手機(jī)局齿，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 92,931評論 3贊 394
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來橄登，“玉大人抓歼，你說我怎么就攤上這事讥此。” “怎么了谣妻？”我有些...
    開封第一講書人閱讀 164,133評論 0贊 354
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵萄喳，是天一觀的道長。 經(jīng)常有香客問我拌禾，道長取胎，這世上最難降的妖魔是什么？ 我笑而不...
    開封第一講書人閱讀 58,532評論 1贊 293
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任湃窍，我火速辦了婚禮闻蛀，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘您市。我一直安慰自己觉痛，他們只是感情好，可當(dāng)我...
    茶點(diǎn)故事閱讀 67,585評論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布茵休。 她就那樣靜靜地躺著薪棒，像睡著了一般。 火紅的嫁衣襯著肌膚如雪榕莺。 梳的紋絲不亂的頭發(fā)上俐芯，一...
    開封第一講書人閱讀 51,462評論 1贊 302
  • 城市分裂傳說
    那天，我揣著相機(jī)與錄音钉鸯，去河邊找鬼吧史。 笑死，一個(gè)胖子當(dāng)著我的面吹牛唠雕，可吹牛的內(nèi)容都是我干的贸营。 我是一名探鬼主播，決...
    沈念sama閱讀 40,262評論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼岩睁，長吁一口氣：“原來是場噩夢啊……” “哼钞脂！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起捕儒，我...
    開封第一講書人閱讀 39,153評論 0贊 276
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對情侶失蹤冰啃，失蹤者是張志新（化名）和其女友劉穎，沒想到半個(gè)月后刘莹，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體亿笤，經(jīng)...
    沈念sama閱讀 45,587評論 1贊 314
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,792評論 3贊 336
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年栋猖，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片汪榔。...
    茶點(diǎn)故事閱讀 39,919評論 1贊 348
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡蒲拉，死狀恐怖肃拜，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情雌团，我是刑警寧澤燃领，帶...
    沈念sama閱讀 35,635評論 5贊 345
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布，位于F島的核電站锦援，受9級特大地震影響猛蔽，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜灵寺，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,237評論 3贊 329
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一曼库、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧略板，春花似錦毁枯、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,855評論 0贊 22
  • 一樁弒父案种玛，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至瓤檐，卻和暖如春赂韵，著一層夾襖步出監(jiān)牢的瞬間，已是汗流浹背挠蛉。 一陣腳步聲響...
    開封第一講書人閱讀 32,983評論 1贊 269
  • 情欲美人皮
    我被黑心中介騙來泰國打工祭示， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留，地道東北人碌秸。 一個(gè)月前我還...
    沈念sama閱讀 48,048評論 3贊 370
  • 代替公主和親
    正文 我出身青樓绍移，卻偏偏與公主長得像，于是被迫代替她去往敵國和親讥电。 傳聞我的和親對象是個(gè)殘疾皇子蹂窖，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,864評論 2贊 354