背景

騰訊云MongoDB當(dāng)前已服務(wù)于游戲、電商、社交、教育、新聞資訊、金融、物聯(lián)網(wǎng)、軟件服務(wù)、汽車出行、音視頻等多個(gè)行業(yè)。

騰訊MongoDB團(tuán)隊(duì)在配合用戶分析問題過程中，發(fā)現(xiàn)云上用戶存在如下索引共性問題，主要集中在如下方面：

本文重點(diǎn)分析總結(jié)騰訊云上用戶索引創(chuàng)建不合理相關(guān)的問題，通過本文可以學(xué)習(xí)到MongoDB的以下知識(shí)點(diǎn)：

本文總結(jié)的《最優(yōu)索引規(guī)則創(chuàng)建大全》不僅僅適用于MongoDB，很多規(guī)則同樣適用于MySQL等關(guān)系型數(shù)據(jù)庫。

MongoDB執(zhí)行計(jì)劃

判斷索引選擇及不同索引執(zhí)行家伙信息可以通過explain操作獲取，MongoDB通過explain來獲取SQL執(zhí)行過程信息，當(dāng)前持續(xù)explain的請(qǐng)求命令包含以下幾種：

aggregate, count, distinct, find, findAndModify, delete, mapReduce,and update。

詳見explain官網(wǎng)連接：

https://docs.MongoDB.com/manual/reference/command/explain/

explain可以攜帶以下幾個(gè)參數(shù)信息，各參數(shù)信息功能如下：

2.1.queryPlanner信息

獲取MongoDB查詢優(yōu)化器選擇的最優(yōu)索引和拒絕掉的非最優(yōu)索引，并給出各個(gè)候選索引的執(zhí)行階段信息，queryPlanner輸出信息如下：

cmgo-xxxx:PRIMARY> db.test4.find({xxxx}).explain("queryPlanner")

{

"queryPlanner" : {

"parsedQuery" : {

......；//查詢條件對(duì)應(yīng)的expression Tree

},

"winningPlan" : {

//查詢優(yōu)化器選擇的最優(yōu)索引及其該索引對(duì)應(yīng)的執(zhí)行階段信息

......;

},

"rejectedPlans" : [

//查詢優(yōu)化器拒絕掉的非最優(yōu)索引及其該索引對(duì)應(yīng)的執(zhí)行階段信息

......;

]

},

}

queryPlanner輸出主要包括如下信息：

內(nèi)核對(duì)查詢條件進(jìn)行序列化，生成一棵expression tree信息，便于候選索引查詢匹配。

"winningPlan" : {

"stage" : ,

"inputStage" : {

"stage" : ,

"inputStage" : {

"stage" : ,

}

}

}

winningPlan提供查詢優(yōu)化器選出的最優(yōu)索引及其查詢通過該索引的執(zhí)行階段信息，子stage傳遞該節(jié)點(diǎn)獲取的文檔或者索引信息給父stage，其輸出項(xiàng)中幾個(gè)重點(diǎn)字段需要關(guān)注：

字段名

功能說明

stage

表示SQL運(yùn)行所處階段信息，根據(jù)不同SQL及其不同候選索引，stage不同，常用stage字段包括以下幾種：

COLLSCAN：該階段為掃表操作

IXSCAN：索引掃描階段，表示查詢走了該索引

FETCH：filter獲取滿足條件的doc

SHARD_MERGE：分片集群，如果mongos獲取到多個(gè)分片的數(shù)據(jù)，則聚合操作在該階段實(shí)現(xiàn)

SHARDING_FILTER ：filter獲取分片集群滿足條件的doc

SORT：內(nèi)存排序階段

OR：$orexpression類查詢對(duì)應(yīng)stage

……

輸出信息和winningPlan類似，記錄這些拒絕掉索引的執(zhí)行stage信息。

2.2.executionStats信息

explain的executionStats參數(shù)除了提供上面的queryPlanner信息外，還提供了最優(yōu)索引的執(zhí)行過程信息，如下：

db.test4.find({xxxx}).explain("executionStats")

"executionStats" : {

"executionSuccess" : ,

"nReturned" : ,

"executionTimeMillis" : ,

"totalKeysExamined" : ,

"totalDocsExamined" : ,

"executionStages" : {

"stage" :

"nReturned" : ,

"executionTimeMillisEstimate" : ,

"works" : ,

"advanced" : ,

"needTime" : ,

"needYield" : ,

"saveState" : ,

"restoreState" : ,

"isEOF" : ,

"inputStage" : {

"stage" : ,

"nReturned" : ,

"executionTimeMillisEstimate" : ,

"inputStage" : {

}

}

},

}

上面是通過executionStats獲取執(zhí)行過程的詳細(xì)信息，其中字段信息較多，平時(shí)分析索引問題最常用的幾個(gè)字段如下：

字段名

功能說明

Stage

Stage字段和queryPlanner信息中stage意思一致，用戶表示執(zhí)行計(jì)劃的階段信息

nReturned

本stage滿足查詢條件的數(shù)據(jù)索引數(shù)據(jù)或者doc數(shù)據(jù)條數(shù)

executionTimeMillis

整個(gè)查詢執(zhí)行時(shí)間

totalKeysExamined

索引key掃描行數(shù)

totalDocsExamined

Doc掃描行數(shù)

executionTimeMillisEstimate

本stage階段執(zhí)行時(shí)間

executionStats輸出字段較多，其他字段將在后續(xù)《MongoDB內(nèi)核index索引模塊實(shí)現(xiàn)原理》中進(jìn)行進(jìn)一步說明。

在實(shí)際分析索引問題是否最優(yōu)的時(shí)候，主要查看executionStats.totalKeysExamined、

executionStats.totalDocsExamined、executionStats .nReturned三個(gè)統(tǒng)計(jì)項(xiàng)，如果存在以下情況則說明索引存在問題，可能索引不是最優(yōu)的：

2.3.allPlansExecution信息

allPlansExecution參數(shù)對(duì)應(yīng)輸出信息和executionStats輸出信息類似，只是多了所有候選索引(包括reject拒絕的非最優(yōu)索引)的執(zhí)行過程，這里不在詳述。

2.4.總結(jié)

從上面的幾個(gè)explain執(zhí)行計(jì)劃參數(shù)輸出信息可以看出，各個(gè)參數(shù)功能各不相同，總結(jié)如下：

輸出索引的候選索引，包括最優(yōu)索引及其執(zhí)行stage過程(winningPlan)+其他非最優(yōu)候選索引及其執(zhí)行stage過程。

注意：queryPlanner沒有真正在表中執(zhí)行整個(gè)SQL，只做了查詢優(yōu)化器獲取候選索引過程，因此可以很快返回。

相比queryPlanner參數(shù)，executionStats會(huì)記錄查詢優(yōu)化器根據(jù)所選最優(yōu)索引執(zhí)行SQL的整個(gè)過程信息，會(huì)真正執(zhí)行整個(gè)SQL。

和executionStats類似，只是多了所有候選索引的執(zhí)行過程。

云上用戶建索引常見問題及優(yōu)化方法

在和用戶一起優(yōu)化騰訊云上MongoDB集群索引過程中，通過和頭部用戶的交流過程中，發(fā)現(xiàn)很多用戶對(duì)如何創(chuàng)建最優(yōu)索引有較驗(yàn)證的錯(cuò)誤認(rèn)識(shí)，并且很多是大部分用戶的共性問題，這些問題總結(jié)匯總?cè)缦拢?/p>

3.1.等值類查詢常見索引錯(cuò)誤創(chuàng)建方法及如何創(chuàng)建最優(yōu)索引

3.1.1. 同一類查詢創(chuàng)建多個(gè)索引問題

如下三個(gè)查詢：

db.test4.find({"a":"xxx", "b":"xxx", "c":"xxx"})

db.test4.find({"b":"xxx", "a":"xxx", "c":"xxx"})

db.test4.find({"c":"xxx", "a":"xxx", "b":"xxx"})

用戶創(chuàng)建了如下3個(gè)索引：

{a:1, b:1, c:1}

{b:1, a:1, c:1}

{c:1, a:1, b:1}

實(shí)際上這3個(gè)查詢屬于同一類查詢，只是查詢字段順序不一樣，因此只需創(chuàng)建任一個(gè)索引即可滿足要求。驗(yàn)證過程如下：

MongoDB_4.4_shard2:PRIMARY>

MongoDB_4.4_shard2:PRIMARY> db.test.find({"a" : 1, "b" : 1, "c" : 1}).explain("executionStats").queryPlanner.winningPlan

{

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "a_1_b_1_c_1",

}

}

MongoDB_4.4_shard2:PRIMARY>

MongoDB_4.4_shard2:PRIMARY> db.test.find({"b" : 1, "a" : 1, "c" : 1}).explain("executionStats").queryPlanner.winningPlan

{

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "a_1_b_1_c_1",

}

}

MongoDB_4.4_shard2:PRIMARY>

MongoDB_4.4_shard2:PRIMARY> db.test.find({"c" : 1, "a" : 1, "b" : 1}).explain("executionStats").queryPlanner.winningPlan

{

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "a_1_b_1_c_1",

}

}

MongoDB_4.4_shard2:PRIMARY>

MongoDB_4.4_shard2:PRIMARY>

從上面的expalin輸出可以看出，3個(gè)查詢都走了同一個(gè)索引。

3.1.2. 多字段等值查詢組合索引順序非最優(yōu)

例如test表有多條數(shù)據(jù)，每條數(shù)據(jù)有3個(gè)字段，分別為a、b、c。其中a字段有10種取值，b字段有100種取值，c字段有1000種取值，稱為各個(gè)字段值的“區(qū)分度”。

用戶查詢條件為db.test.find({"a":"xxx", "b":"xxx", "c":"xxx"})，創(chuàng)建的索引為{a:1, b:1, c:1}。如果只是針對(duì)這個(gè)查詢，該查詢可以創(chuàng)建a，b，c三字段的任意組合，并且其SQL執(zhí)行代價(jià)一樣，通過hint強(qiáng)制走不通索引，驗(yàn)證過程如下：

MongoDB_4.4_shard2:PRIMARY> db.test.find({"a" : 1, "b" : 1, "c" : 1}).hint({"a" : 1, b:1, c:1}).explain("executionStats").executionStats

{

"nReturned" : 1,

"executionTimeMillis" : 0,

"totalKeysExamined" : 1,

"totalDocsExamined" : 1,

"executionStages" : {

"stage" : "FETCH",

"nReturned" : 1,

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "a_1_c_1_b_1",

}

}

}

MongoDB_4.4_shard2:PRIMARY>

MongoDB_4.4_shard2:PRIMARY> db.test.find({"a" : 1, "b" : 1, "c" : 1}).hint({"a" : 1, c:1, b:1}).explain("executionStats").executionStats

{

"nReturned" : 1,

"executionTimeMillis" : 0,

"totalKeysExamined" : 1,

"totalDocsExamined" : 1,

"executionStages" : {

"stage" : "FETCH",

"nReturned" : 1,

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "a_1_c_1_b_1",

}

}

}

MongoDB_4.4_shard2:PRIMARY>

MongoDB_4.4_shard2:PRIMARY> db.test.find({"c" : 1, "a" : 1, "b" : 1}).hint({"a" : 1, c:1, b:1}).explain("executionStats").executionStats

{

"nReturned" : 1,

"executionTimeMillis" : 0,

"totalKeysExamined" : 1,

"totalDocsExamined" : 1,

"executionStages" : {

"stage" : "FETCH",

"nReturned" : 1,

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "a_1_c_1_b_1",

}

}

}

從上面的執(zhí)行計(jì)劃可以看出，多字段等值查詢各個(gè)字段的組合順序?qū)?yīng)執(zhí)行計(jì)劃代價(jià)一樣。絕大部分用戶在創(chuàng)建索引的時(shí)候，都是直接按照查詢字段索引組合對(duì)應(yīng)字段。

但是，單就這一個(gè)查詢，這里有個(gè)不成文的建議，把區(qū)分度更高的字段放在組合索引左邊，區(qū)分度低的字段放到右邊。這樣做有個(gè)好處，數(shù)據(jù)庫組合索引遵從最左原則，就是當(dāng)其他查詢里面帶有區(qū)分度最高的字段時(shí)，就可以快速排除掉更多不滿足條件的數(shù)據(jù)。

3.1.3. 最左原則包含關(guān)系引起的重復(fù)索引

例如用戶有如下兩個(gè)查詢：

db.test.find({"b" : 2, "c" : 1}) //查詢1

db.test.find({"a" : 10, "b" : 5, "c" : 1}) //查詢2

用戶創(chuàng)建了如下兩個(gè)索引：

{b:1, c:1}

{a:1,b:1,c:1}

這兩個(gè)查詢中，查詢2中包含有查詢1中的字段，因此可以用一個(gè)索引來滿足這兩個(gè)查詢要求，按照最左原則，查詢1字段放左邊即可，該索引可以優(yōu)化為：b, c字段索引+a字段索引，b,c字段順序可以根據(jù)區(qū)分排序，加上c字段區(qū)分度比b高，則這兩個(gè)查詢可以合并為一個(gè){c:1, b:1, a:1}。兩個(gè)查詢可以走同一個(gè)索引驗(yàn)證過程如下：

MongoDB_4.4_shard2:PRIMARY> db.test.find({"b" : 2, "c" : 1}).explain("executionStats")

{

"winningPlan" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "c_1_b_1_a_1",

}

}

}

MongoDB_4.4_shard2:PRIMARY>

MongoDB_4.4_shard2:PRIMARY> db.test.find({"a" : 10, "b" : 5, "c" : 1}).explain("executionStats")

{

"winningPlan" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "c_1_b_1_a_1",

}

}

}

從上面輸出可以看出，這兩個(gè)查詢都走了同一個(gè)索引。

3.1.4. 唯一字段和其他字段組合引起的無用重復(fù)索引

例如用戶有以下兩個(gè)查詢：

db.test.find({a:1,b:1})

db.test.find({a:1,c:1})

用戶為這兩個(gè)查詢創(chuàng)建了兩個(gè)索引，{a:1, b:1}和{a:1, c:1}，但是a字段取值是唯一的，因此這兩個(gè)查詢中a以外的字段無用，一個(gè){a:1}索引即可滿足要求。

3.2.非等值類查詢常見索引錯(cuò)誤創(chuàng)建方法及如何創(chuàng)建最優(yōu)索引

3.2.1. 非等值組合查詢索引不合理創(chuàng)建

假設(shè)用戶有如下查詢：

//兩字段非等值查詢

db.test.find({a:{$gte:1}, c:{$lte:1}})

a,c兩個(gè)字段都是非等值查詢，很多用戶直接添加了{(lán)a:1, c:1}索引，實(shí)際上多個(gè)字段的非等值查詢，只有最左邊的字段才能走索引，例如這里只會(huì)走a字段索引，驗(yàn)證過程如下：

MongoDB_4.4_shard1:PRIMARY>

MongoDB_4.4_shard1:PRIMARY> db.test.find({a:{$gte:1}, c:{$lte:1}}).explain("executionStats")

{

"executionStats" : {

"nReturned" : 4,

"executionTimeMillis" : 0,

"totalKeysExamined" : 10,

"totalDocsExamined" : 4,

"inputStage" : {

"indexName" : "a_1_c_1",

}

}

從上面執(zhí)行計(jì)劃可以看出，索引數(shù)據(jù)掃描了10行(也就是a字段滿足a:{$gte:1}條件的數(shù)據(jù)多少)，但是實(shí)際上只返回了4條滿足{a:{$gte:1}, c:{$lte:1}}條件的數(shù)據(jù)，可以看出c字段無法走索引。

同理，當(dāng)查詢中包含多個(gè)字段的范圍查詢的適合，除了最左邊第一個(gè)字段可以走索引，其他字段都無法走索引。因此，上面例子中的查詢候選索引為{a:1}或者{b:1}中任何一個(gè)就可以了，組合索引中字段太多會(huì)占用更多存儲(chǔ)成本、同時(shí)暫用更多IO資源引起寫放大。

3.2.2. 等值+非等值組合查詢索引字段順序不合理

例如下面查詢：

//兩字段非等值查詢

db.test.find({"d":{$gte:4}, "e":1})

如上查詢，d字段為非等值查詢，e字段為等值查詢，很多用戶遇到該類查詢直接創(chuàng)建了{(lán)d:1, e:1}索引，由于d字段為非等值查詢，因此e字段無法走索引，驗(yàn)證過程如下：

MongoDB_4.4_shard1:PRIMARY>

MongoDB_4.4_shard1:PRIMARY> db.test.find({"d":{$gte:4}, "e":1}).hint({d:1, e:1}).explain("executionStats")

{

"executionStats" : {

……

"totalKeysExamined" : 5,

"totalDocsExamined" : 3,

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "d_1_e_1",

}

}

MongoDB_4.4_shard1:PRIMARY> db.test.find({"d":{$gte:4}, "e":1}).hint({e:1, d:1}).explain("executionStats")

{

"executionStats" : {

"totalKeysExamined" : 3,

"totalDocsExamined" : 3,

"inputStage" : {

"indexName" : "e_1_d_1",

}

從上面驗(yàn)證過程可以看出，等值類和非等值類組合查詢對(duì)應(yīng)組合索引，最優(yōu)索引應(yīng)該優(yōu)先把等值查詢放到左邊，上面查詢對(duì)應(yīng)最優(yōu)索引{e:1, d:1}。

3.2.3. 不同類型非等值查詢優(yōu)先級(jí)問題

前面用到的非等值查詢操作符只提到了比較類操作符，實(shí)際上非等值查詢還有其他操作符。常用非等值查詢包括：$gt、$gte、$lt、$lte、$in、$nin、$ne、$exists、$type等，這些非等值查詢?cè)诮^大部分情況下存在如下優(yōu)先級(jí)：

從上到下優(yōu)先級(jí)更高，例如下面的查詢：

//等值+多個(gè)不同優(yōu)先級(jí)非等值查詢

db.test.find({"a":1, "b":1, "c":{$ne:5}, "e":{$type:"string"}, "f":{$gt:5},"g":{$in:[3,4]}) 查詢1

如上，該查詢等值部分查詢最優(yōu)索引{a:1,b:1}(假設(shè)a區(qū)分度比b高)；非等值部分，因?yàn)?in操作符優(yōu)先級(jí)最高，排他性更好，加上多個(gè)字段非等值查詢只會(huì)有一個(gè)字段走索引，因此非等值部分最優(yōu)索引為{g:1}。

最終該查詢最優(yōu)索引為：”等值部分最優(yōu)索引”與”非等值部分最優(yōu)索引”拼接，也就是{a:1,b:1, g:1}。

3.3.OR類查詢常見索引錯(cuò)誤創(chuàng)建方法及如何創(chuàng)建最優(yōu)索引

3.3.1. 普通OR類查詢

例如如下or查詢：

//or中包含兩個(gè)查詢

db.test.find( { $or: [{ b: 0,d:0 }, {"c":1, "a":{$gte:4}} ] } )

該查詢很多用戶直接創(chuàng)建了{(lán)b:1,d:1, c:1, a:1}，用戶創(chuàng)建該索引后，發(fā)現(xiàn)用戶還是全表掃描。

Or類查詢需要給數(shù)組中每個(gè)查詢添加索引，例如上面or數(shù)組中實(shí)際包含{ b: 0, d:0 }和{"c":1, "a":{$gte:4}}查詢，需要?jiǎng)?chuàng)建兩個(gè)查詢的最優(yōu)索引，也就是{b:1, d:1}和{c:1, a:1}，執(zhí)行計(jì)劃驗(yàn)證過程如下(該測(cè)試表總10條數(shù)據(jù))：

MongoDB_4.4_shard1:PRIMARY> db.test.find( { $or: [{ b: 0,d:0 }, {"c":1, "a":{$gte:4}}]}).hint({b:1, d:1, c:1, a:1}).explain("executionStats")

{

"executionStats" : {

"totalKeysExamined" : 10,

"totalDocsExamined" : 10,

"inputStage" : {

"indexName" : "b_1_d_1_c_1_a_1",

}

}

//創(chuàng)建{b:1,d:1}和{c:1, a:1}兩個(gè)索引后，優(yōu)化器選擇這兩個(gè)索引做為最優(yōu)索引

MongoDB_4.4_shard1:PRIMARY>

MongoDB_4.4_shard1:PRIMARY> db.test.find( { $or: [{ b: 0,d:0 }, {"c":1, "a":{$gte:4}}]}).explain("executionStats")

{

"executionStats" : {

"totalKeysExamined" : 2,

"totalDocsExamined" : 2,

"executionStages" : {

"stage" : "SUBPLAN",

"inputStage" : {

"stage" : "OR",

"inputStages" : [

{

"stage" : "IXSCAN",

"indexName" : "b_1_d_1",

},

{

"stage" : "IXSCAN",

"indexName" : "c_1_a_1",

}

]

} }

}

},

從上面執(zhí)行計(jì)劃可以看出，如果該OR類查詢走{b:1, d:1, c:1, a:1}索引，則實(shí)際上做了全表掃描。如果同時(shí)創(chuàng)建{b:1, d:1}、{c:1, a:1}索引，則直接走兩個(gè)索引，其執(zhí)行key和doc掃描行數(shù)遠(yuǎn)遠(yuǎn)小于全表掃描。

3.3.2. 復(fù)雜OR類查詢

這里在提升一下OR查詢難度，例如下面的查詢：

//等值查詢+or類查詢+sort排序查詢

db.test.find( {"f":3, g:2, $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ) 查詢1

上面的查詢可以轉(zhuǎn)換為如下兩個(gè)查詢：

------db.test.find( {"f":3, g:2, b: 0, d:0 } ) //查詢2

or--|

------db.test.find( {"f":3, g:2, "c":1, "a":6} ) //查詢3

如上圖，查詢1拆分后的兩個(gè)查詢2和查詢3組成or關(guān)系，因此對(duì)應(yīng)最優(yōu)所有需要?jiǎng)?chuàng)建兩個(gè)，分表是：{f:1, g:1, b:1, d:1}和 {f:1, g:1, b:1, d:1}。對(duì)應(yīng)執(zhí)行計(jì)劃如下：

MongoDB_4.4_shard1:PRIMARY> db.test.find( {"f":3, g:2, $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ).explain("executionStats")

{

"executionStats" : {

"totalKeysExamined" : 7,

"totalDocsExamined" : 7,

"executionStages" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "OR",

"inputStages" : [

{

"stage" : "IXSCAN",

"indexName" : "f_1_g_1_c_1_a_1",

},

{

"stage" : "IXSCAN",

"indexName" : "f_1_g_1_b_1_d_1",

}

]

}

}

},

}

同理，不管怎么增加難度，OR查詢最終可轉(zhuǎn)換為多個(gè)等值、非等值或者等值與非等值組合類查詢，通過如上變換最終可以做到舉一反三的作用。

說明：這個(gè)例子中可能在一些特殊數(shù)據(jù)分布場(chǎng)景，最優(yōu)索引也可能是{f:1, g:1}或者{f:1, g:1, b:1, d:-1}或者{ f:1, g:1, c:1, a:1}，這里我們只考慮大部分通用場(chǎng)景。

3.4.Sort類排序查詢常見索引錯(cuò)誤創(chuàng)建方法及如何創(chuàng)建最優(yōu)索引

3.4.1. 單字段正反序排序查詢引起的重復(fù)索引

例如用戶有以下兩個(gè)查詢：

db.test.find({}).sort({a:1}).limit(2)

db.test.find({}).sort({a:-1}).limit(2)

這兩個(gè)查詢都不帶條件，排序方式不一樣，因此很多創(chuàng)建了兩個(gè)索引{a:1}和{a:-1}，實(shí)際上這兩個(gè)索引中的任何一個(gè)都可以滿足兩種查詢要求，驗(yàn)證過程如下：

MongoDB_4.4_shard1:PRIMARY>

MongoDB_4.4_shard1:PRIMARY> db.test.find({}).sort({a:1}).limit(2).explain("executionStats")

{

"winningPlan" : {

"stage" : "LIMIT",

"limitAmount" : 2,

"inputStage" : {

"indexName" : "a_1",

}

}

},

}

MongoDB_4.4_shard1:PRIMARY>

MongoDB_4.4_shard1:PRIMARY> db.test.find({}).sort({a:-1}).limit(2).explain("executionStats")

{

"winningPlan" : {

"stage" : "LIMIT",

"limitAmount" : 2,

"inputStage" : {

"indexName" : "a_1",

}

}

},

},

3.4.2. 多字段排序查詢正反序問題引起索引無效

假設(shè)有如下查詢：

//兩字段排序查詢

db.test.find().sort({a:1, b:-1}).limit(5)

其中a字段為正序，b字段為反序排序，很多用戶直接創(chuàng)建{a:1, b:1}索引，這時(shí)候b字段內(nèi)容就存在內(nèi)存排序情況。多字段排序索引，如果沒有攜帶查詢條件，則最優(yōu)索引即為排序字段對(duì)應(yīng)索引，這里切記保持每個(gè)字段得正反序和sort完全一致，否則可能存在部分字段內(nèi)存排序的情況，執(zhí)行計(jì)劃驗(yàn)證過程如下：

//{a:1, b:1}只會(huì)有一個(gè)字段走索引，另一個(gè)字段內(nèi)存排序

MongoDB_4.4_shard1:PRIMARY>

MongoDB_4.4_shard1:PRIMARY> db.test.find().sort({a:1, b:-1}).hint({a:1, b:1}).explain("executionStats")

{

"executionStats" : {

"totalKeysExamined" : 15,

"totalDocsExamined" : 15,

"inputStage" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "SORT",

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "a_1_b_1",

}

}

}

}

},

//{a:1, b:-1}兩個(gè)字段走索引，不存在內(nèi)存排序

MongoDB_4.4_shard1:PRIMARY>

MongoDB_4.4_shard1:PRIMARY> db.test.find().sort({a:1, b:-1}).hint({a:1, b:-1}).explain("executionStats")

{

"executionStats" : {

"totalKeysExamined" : 15,

"totalDocsExamined" : 15,

"inputStage" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "a_1_b_-1",

}

}

}

},

}

3.4.3. 等值查詢+多字段排序組合查詢

例如如下查詢：

//多字段等值查詢+多字段排序查詢

db.test.find({ "a" : 3, "b" : 1}).sort({c:-1, d:1})

該類查詢很多人直接創(chuàng)建{a:1,b:1, c:1, d:1}，結(jié)果造成內(nèi)存排序。這種組合查詢最優(yōu)索引=“多字段等值查詢最優(yōu)索引_多字段排序類組合最優(yōu)索引”，例如該查詢：

{ "a" : 3, "b" : 1}等值查詢假設(shè)a區(qū)分度比b高，則對(duì)應(yīng)最優(yōu)索引為：{a：1, b：1}

{ c:-1, d:1}排序類查詢最優(yōu)索引保持正反序一致，也就是:{ c:-1, d:1}

因此整個(gè)查詢就是這兩個(gè)查詢對(duì)應(yīng)最優(yōu)索引拼接，也就是{a:1, b:1, c:-1, d:1}，對(duì)應(yīng)執(zhí)行計(jì)劃過程驗(yàn)證如下：

//非最優(yōu)索引執(zhí)行計(jì)劃，存在內(nèi)存排序

MongoDB_4.4_shard1:PRIMARY>

MongoDB_4.4_shard1:PRIMARY> db.test.find({ "a" : 3, "b" : 1}).sort({c:-1, d:1}).hint({a:1, b:1, c:1, d:1}).explain("executionStats")

{

"executionStats" : {

"executionStages" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "SORT",

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "a_1_b_1_c_1_d_1",

}

}

}

},

}

//最優(yōu)索引執(zhí)行計(jì)劃，直接走排序索引

MongoDB_4.4_shard1:PRIMARY>

MongoDB_4.4_shard1:PRIMARY> db.test.find({ "a" : 3, "b" : 1}).sort({c:-1, d:1}).hint({a:1, b:1, c:-1, d:1}).explain("executionStats")

{

"executionStats" : {

"executionStages" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "a_1_b_1_c_-1_d_1",

}

}

},

}

3.4.4. 等值查詢+非等值查詢+sort排序查詢

假設(shè)有下面的查詢：

//等值+非等值+sort排序查詢

db.test.find({"a":3, "b":1, "c":{$gte:1}}).sort({d:-1, e:1})

騰訊云很多用戶看到該查詢直接創(chuàng)建{a:1,b:1, c:1, d:-1, e:1}索引，發(fā)現(xiàn)存在內(nèi)存排序。等值+非等值+sort排序組合查詢，由于非等值查詢右邊的字段不能走索引，因此如果把d, e放到c的右邊，則d，e字段索引無效。

等值+非等值+sort排序最優(yōu)索引組合字段順序?yàn)椋旱戎礯sort排序_非等值，因此上面查詢最優(yōu)索引為:{a:1, b:1, d:-1, e:1, c:1}。執(zhí)行計(jì)劃驗(yàn)證過程如下：

//走部分索引，然后內(nèi)存排序

MongoDB_4.4_shard1:PRIMARY> db.test.find({"a":3, "b":1, "c":{$gte:1}}).sort({d:-1, e:1}).hint({"a":1, b:1, c:1, d:-1, e:1}).explain("executionStats")

{

"executionStats" : {

"totalKeysExamined" : 9,

"totalDocsExamined" : 9,

"executionStages" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "SORT", //內(nèi)存排序

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "a_1_b_1_c_1_d_-1_e_1",

}

}

}

},

}

//直接走排序索引

MongoDB_4.4_shard1:PRIMARY> db.test.find({"a":3, "b":1, "c":{$gte:1}}).sort({d:-1, e:1}).hint({"a":1, b:1, d:-1, e:1, c:1}).explain("executionStats")

{

"executionStats" : {

"totalKeysExamined" : 10,

"totalDocsExamined" : 9,

"executionStages" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "a_1_b_1_d_-1_e_1_c_1",

}

}

},

}

3.4.5. OR +SORT組合排序查詢

例如如下查詢：

//or+sort組合 查詢1

db.test.find( { $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ).sort({e:-1})

上面組合很多人直接創(chuàng)建{b:1, d:1, c:1, a:1, e:1}，該索引創(chuàng)建后還是會(huì)掃表和內(nèi)存排序，實(shí)際上OR+SORT組合查詢可以轉(zhuǎn)換為下面兩個(gè)查詢：

//查詢1等價(jià)轉(zhuǎn)換為如下查詢

-----db.test.find({ b: 3, d:5 }).sort({e:-1}) //查詢2

or--|

-----db.test.find( {"c":1, "a":6} ).sort({e:-1}) //查詢3

所以這個(gè)復(fù)雜查詢就可以拆分為等值組合查詢+sort排序查詢，拆分為上面的兩個(gè)查詢，這樣我們只需要同時(shí)創(chuàng)建查詢2和查詢3對(duì)應(yīng)最優(yōu)索引即可。該查詢最終拆分后對(duì)應(yīng)最優(yōu)索引需要添加如下兩個(gè)：

{b:1, d:1, e:-1}和{c:1,a:1, e:-1}

非最優(yōu)索引和最優(yōu)索引執(zhí)行計(jì)劃驗(yàn)證過程如下：

//走{b:1, d:1, c:1, a:1, e:-1}索引，全表掃描加內(nèi)存排序

MongoDB_4.4_shard1:PRIMARY>

MongoDB_4.4_shard1:PRIMARY> db.test.find( { $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ).sort({e:-1}).hint({b:1, d:1, c:1, a:1, e:-1}).explain("executionStats")

{

"executionStats" : {

//測(cè)試構(gòu)造表中23條數(shù)據(jù)，總數(shù)據(jù)23條

"totalKeysExamined" : 23,

"totalDocsExamined" : 23,

"executionStages" : {

"stage" : "SORT",

"inputStage" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "IXSCAN",

"indexName" : "b_1_d_1_c_1_a_1_e_-1",

}

}

}

},

}

//走{b:1, d:1, e:-1}和{c:1, a:1, e:-1}兩個(gè)最優(yōu)索引的執(zhí)行計(jì)劃，無內(nèi)存排序

MongoDB_4.4_shard1:PRIMARY>

MongoDB_4.4_shard1:PRIMARY> db.test.find( { $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ).sort({e:-1}).explain("executionStats")

{

"executionStats" : {

"totalKeysExamined" : 2,

"totalDocsExamined" : 2,

"inputStage" : {

"stage" : "FETCH",

"inputStage" : {

"stage" : "SORT_MERGE",

"inputStages" : [

{

"stage" : "IXSCAN",

"indexName" : "b_1_d_1_e_1",

},

{

"stage" : "IXSCAN",

"indexName" : "c_1_a_1_e_1",

}

]

}

}

}

},

}

OR+SORT類查詢，最終可以《參考前面的OR類查詢常見索引錯(cuò)誤創(chuàng)建方法》把OR查詢轉(zhuǎn)換為多個(gè)等值、非等值或者等值與非等值組合查詢，然后與sort排序?qū)?yīng)索引字段拼接。例如下面查詢：

//原查詢

db.test.find( {"f":3, g:2, $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ).sort({e:-1}) //查詢1

拆分后的兩個(gè)查詢組成or關(guān)系，如下：

//拆分后查詢

------ db.test.find( {"f":3, g:2, b: 0, d:0} ).sort({e:-1}) //查詢2

or---

------ db.test.find( {"f":3, g:2, "c":1, "a":6}).sort({e:-1}) //查詢3

如上，查詢1 = or: [查詢2， 查詢3]，因此只需要?jiǎng)?chuàng)建查詢2和查詢3兩個(gè)最優(yōu)索引即可滿足查詢1要求，查詢2和查詢3最優(yōu)索引可以參考前面《or類查詢常見索引錯(cuò)誤創(chuàng)建方法》，該查詢最終需要?jiǎng)?chuàng)建如下兩個(gè)索引：

{f:1, g:1, b:1, d:1, e:-1}和{ f:1, g:1, c:1, a:1, e:-1}

說明：這個(gè)例子中可能在一些特殊數(shù)據(jù)分布場(chǎng)景，最優(yōu)索引也可能是{f:1, g:1}或者{f:1, g:1, b:1, d:1, e:-1}或者{ f:1, g:1, c:1, a:1, e:-1}，這里我們只考慮通用場(chǎng)景。

3.5.避免創(chuàng)建太多無用索引及無用索引分析方法

在騰訊云上，我們還發(fā)現(xiàn)另外一個(gè)問題，很多實(shí)例存在大量無用索引，無用索引會(huì)引起以下問題：

沒增加一個(gè)索引，MongoDB內(nèi)核就會(huì)創(chuàng)建一個(gè)index索引文件，記錄該表的索引數(shù)據(jù)，造成存儲(chǔ)成本增加。

用戶沒寫入一條數(shù)據(jù)，就會(huì)在對(duì)應(yīng)索引生成一條索引KV，實(shí)現(xiàn)索引與數(shù)據(jù)的一一對(duì)應(yīng)，索引KV數(shù)據(jù)寫入Index索引文件過程加劇寫入負(fù)載。

MongoDB內(nèi)核查詢優(yōu)化器原理是通過候選索引快速定位到滿足條件的數(shù)據(jù)，然后采樣評(píng)分。如果滿足條件的候選索引越多，整個(gè)評(píng)分過程就會(huì)越長，增加內(nèi)核選擇最優(yōu)索引的流程。

下面已一個(gè)真實(shí)線上實(shí)例為例，說明如何找出無用索引：

db.xxx.aggregate({"$indexStats":{}})

{ "alxxxId" : 1, "state" : -1, "updateTime" : -1, "itxxxId" : -1, "persxxal" : 1, "srcItxxxId" : -1 } "ops" : NumberLong(88518502)

{ "alxxxId" : 1, "image" : 1 } "ops" : NumberLong(293104)

{ "itexxxList.vidxxCheck" : 1, "itemType" : 1, "state" : 1 } "ops" : NumberLong(0)

{ "alxxxId" : 1, "state" : -1, "newsendTime" : -1, "itxxxId" : -1, "persxxal" : 1 } "ops" : NumberLong(33361216)

{ "_id" : 1 } "ops" : NumberLong(3987)

{ "alxxxId" : 1, "createTime" : 1, "checkStatus" : 1 } "ops" : NumberLong(20042796)

{ "alxxxId" : 1, "parentItxxxId" : -1, "state" : -1, "updateTime" : -1, "persxxal" : 1, "srcItxxxId" : -1 } "ops" : NumberLong(43042796)

{ "alxxxId" : 1, "state" : -1, "parentItxxxId" : 1, "updateTime" : -1, "persxxal" : -1 } "ops" : NumberLong(3042796)

{ "itxxxId" : -1} "ops" : NumberLong(38854593)

{ "srcItxxxId" : -1 } "ops" : NumberLong(0)

{ "createTime" : 1 } "ops" : NumberLong(62)

{ "itexxxList.boyunState" : -1, "itexxxList.wozhituUploadServerId" : -1, "itexxxList.photoQiniuUrl" : 1, "itexxxList.sourceType" : 1 } "ops" : NumberLong(0)

{ "alxxxId" : 1, "state" : 1, "digitalxxxrmarkId" : 1, "updateTime" : -1 } "ops" : NumberLong(140238342)

{ "itxxxId" : -1 } "ops" : NumberLong(38854593)

{ "alxxxId" : 1, "parentItxxxId" : 1, "parentAlxxxId" : 1, "state" : 1 } "ops" : NumberLong(132237254)

{ "alxxxId" : 1, "videoCover" : 1 } { "ops" : NumberLong(2921857)

{ "alxxxId" : 1, "itemType" : 1 } { "ops" : NumberLong(457)

{ "alxxxId" : 1, "state" : -1, "itemType" : 1, "persxxal" : 1, " itxxxId " : 1 } "ops" : NumberLong(68730734)

{ "alxxxId" : 1, "itxxxId" : 1 } "ops" : NumberLong(232360252)

{ "itxxxId" : 1, "alxxxId" : 1 } "ops" : NumberLong(145640252)

{ "alxxxId" : 1, "parentAlxxxId" : 1, "state" : 1 } "ops" : NumberLong(689891)

{ "alxxxId" : 1, "itemTagList" : 1 } "ops" : NumberLong(2898693682)

{ "itexxxList.photoQiniuUrl" : 1, "itexxxList.boyunState" : 1, "itexxxList.sourceType" : 1, "itexxxList.wozhituUploadServerId" : 1 } "ops" : NumberLong(511303207)

{ "alxxxId" : 1, "parentItxxxId" : 1, "state" : 1 } "ops" : NumberLong(0)

{ "alxxxId" : 1, "parentItxxxId" : 1, "updateTime" : 1 } "ops" : NumberLong(0)

{ "updateTime" : 1 } "ops" : NumberLong(1397)

{ "itemPhoxxIdList" : -1 } "ops" : NumberLong(0)

{ "alxxxId" : 1, "state" : -1, "isTop" : 1 } "ops" : NumberLong(213305)

{ "alxxxId" : 1, "state" : 1, "itemResxxxIdList" : 1, "updateTime" : 1 } "ops" : NumberLong(2591780)

{ "alxxxId" : 1, "state" : 1, "itexxxList.photoQiniuUrl" : 1} "ops" : NumberLong(23505)

{ "itexxxList.qiniuStatus" : 1, "itexxxList.photoNetUrl" : 1, "itexxxList.photoQiniuUrl" : 1 } "ops" : NumberLong(0)

{ "itemResxxxIdList" : 1 } "ops" :NumberLong(7)

MongoDB默認(rèn)提供有索引統(tǒng)計(jì)命令來獲取各個(gè)索引命中的次數(shù)，該命令如下：

> db.xxxxx.aggregate({"$indexStats":{}})

{ "name" : "alxxxId_1_parentItxxxId_1_parentAlxxxId_1", "key" : { "alxxxId" : 1, "parentItxxxId" : 1, "parentAlxxxId" : 1 }, "host" : "TENCENT64.site:7014", "accesses" : { "ops" : NumberLong(11236765), "since" : ISODate("2020-08-17T06:39:43.840Z") } }

該聚合輸出中的幾個(gè)核心指標(biāo)信息如下表：

字段內(nèi)容

說明

name

索引名，代表是針對(duì)那個(gè)索引的統(tǒng)計(jì)。

ops

索引命中次數(shù)，也就是所有查詢中采用本索引作為查詢索引的次數(shù)。

上表中的ops代表命中次數(shù)，如果命中次數(shù)為0或者很小，說明該索引很少被選為最優(yōu)索引使用，因此可以認(rèn)為是無用索引，可以考慮刪除。

MongoDB不同分類查詢最優(yōu)索引總結(jié)

查詢大類

子類

生成候選索引規(guī)則

普通查詢

單字段查詢

無需計(jì)算，直接輸出索引

多字段等值查詢

說明：本身多字段等值查詢，最優(yōu)索引和字段組合順序無關(guān)，但是這里一般有個(gè)不成文歸檔，把區(qū)分度最高的字段放在最左邊，這樣有利于帶有該字段新查詢的快速排他性

多字段非等值查詢

非等值查詢，通過優(yōu)先級(jí)確定候選索引，非等值操作符優(yōu)先級(jí)順序如下：

如果字段優(yōu)先級(jí)一樣，則會(huì)對(duì)應(yīng)多個(gè)候選索引，例如：{a>1, b>1,c >1}查詢，候選索引是以下3個(gè)中的一個(gè)：

這時(shí)候就需要根據(jù)數(shù)據(jù)分布評(píng)估3個(gè)候選索引中那個(gè)更好。

等值與非等值組合

等值與非等值組合，候選索引規(guī)則步驟如下：

舉例：db.collection.find(a=1, b=2, c>3, d>4)

假設(shè)(a=1, b=2)等值查詢按照區(qū)分度最優(yōu)索引為{b:1,a:1}，則候選索引有如下兩種：

{b:1，a:1，c:1}

{b:1，a:1，d:1}

這時(shí)候就需要根據(jù)數(shù)據(jù)分布情況決定加這兩個(gè)候選索引的哪一個(gè)作為最優(yōu)索引。

排序類型

不帶查詢的排序

不帶查詢條件的排序，

例如：db.xx.find({}).sort({a:1,b:-1,c:1})，對(duì)應(yīng)候選索引直接是排序索引：

{a:1,b:-1,c:1}

普通查詢+sort排序

該場(chǎng)景候選索引包括：

舉例：db.collection.find(a=1, b=2, c>3, d>4).sort({e:1, f:-1})，該查詢候選索引：

{b:1，a:1}

{a:1，b:1}

{c:1}

{d:1}

{ e:1, f:-1}

假設(shè)等值部分按照區(qū)分度最優(yōu)索引為{a:1, b:1},非等值最優(yōu)索引為{d:1}，則整個(gè)查詢最優(yōu)索引=等值部分最優(yōu)索引_sort排序最優(yōu)索引_非等值部分最優(yōu)索引，也就是{a:1,b:1,e:1,f:-1d:1}

OR類查詢

(可拆分為多個(gè)普通查詢)

一個(gè)子tree

候選索引就是該子tree對(duì)應(yīng)候選索引，參考《普通查詢》對(duì)應(yīng)候選索引推薦算法

多個(gè)子tree

(無交集字段)

對(duì)每個(gè)tree對(duì)應(yīng)普通查詢生成一個(gè)最優(yōu)索引，多個(gè)子tree會(huì)有多個(gè)候選索引，每個(gè)tree對(duì)應(yīng)候選索引規(guī)則參考《普通查詢》

更多查詢匯總信息

參考第三章

參考第三章

說明：

本文總結(jié)的《最優(yōu)索引規(guī)則大全》中的規(guī)則適用于絕大部分查詢場(chǎng)景，但是一些特殊數(shù)據(jù)分布場(chǎng)景可能會(huì)有一定偏差，請(qǐng)根據(jù)實(shí)際數(shù)據(jù)分布進(jìn)行查詢計(jì)劃分析。

責(zé)任編輯：Rex_08