背景
騰訊云MongoDB當前已服務于游戲、電商、社交、教育、新聞資訊、金融、物聯網、軟件服務、汽車出行、音視頻等多個行業。
騰訊MongoDB團隊在配合用戶分析問題過程中，發現云上用戶存在如下索引共性問題，主要集中在如下方面：
無用索引 重復索引 索引不是最優 對索引理解有誤等。本文重點分析總結騰訊云上用戶索引創建不合理相關的問題，通過本文可以學習到MongoDB的以下知識點：
如果理解MongoDB執行計劃 如何確認查詢索引是不是最優索引 云上用戶對索引的一些錯誤創建方法 如何創建最優索引 創建最優索引的規則匯總本文總結的《最優索引規則創建大全》不僅僅適用于MongoDB，很多規則同樣適用于MySQL等關系型數據庫。
MongoDB執行計劃
判斷索引選擇及不同索引執行家伙信息可以通過explain操作獲取，MongoDB通過explain來獲取SQL執行過程信息，當前持續explain的請求命令包含以下幾種：
aggregate, count, distinct, find, findAndModify, delete, mapReduce,and update。
詳見explain官網連接：
https://docs.MongoDB.com/manual/reference/command/explain/
explain可以攜帶以下幾個參數信息，各參數信息功能如下：
2.1.queryPlanner信息
獲取MongoDB查詢優化器選擇的最優索引和拒絕掉的非最優索引，并給出各個候選索引的執行階段信息，queryPlanner輸出信息如下：
cmgo-xxxx:PRIMARY> db.test4.find({xxxx}).explain("queryPlanner")
{
"queryPlanner" : {
"parsedQuery" : {
......；//查詢條件對應的expression Tree
},
"winningPlan" : {
//查詢優化器選擇的最優索引及其該索引對應的執行階段信息
......;
},
"rejectedPlans" : [
//查詢優化器拒絕掉的非最優索引及其該索引對應的執行階段信息
......;
]
},
}
queryPlanner輸出主要包括如下信息：
parsedQuery信息內核對查詢條件進行序列化，生成一棵expression tree信息，便于候選索引查詢匹配。
winningPlan信息"winningPlan" : {
"stage" :
"inputStage" : {
"stage" :
"inputStage" : {
"stage" :
}
}
}
winningPlan提供查詢優化器選出的最優索引及其查詢通過該索引的執行階段信息，子stage傳遞該節點獲取的文檔或者索引信息給父stage，其輸出項中幾個重點字段需要關注：
字段名
功能說明
stage
表示SQL運行所處階段信息，根據不同SQL及其不同候選索引，stage不同，常用stage字段包括以下幾種：
COLLSCAN：該階段為掃表操作
IXSCAN：索引掃描階段，表示查詢走了該索引
FETCH：filter獲取滿足條件的doc
SHARD_MERGE：分片集群，如果mongos獲取到多個分片的數據，則聚合操作在該階段實現
SHARDING_FILTER ：filter獲取分片集群滿足條件的doc
SORT：內存排序階段
OR：$orexpression類查詢對應stage
……
rejectedPlans信息輸出信息和winningPlan類似，記錄這些拒絕掉索引的執行stage信息。
2.2.executionStats信息
explain的executionStats參數除了提供上面的queryPlanner信息外，還提供了最優索引的執行過程信息，如下：
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獲取執行過程的詳細信息，其中字段信息較多，平時分析索引問題最常用的幾個字段如下：
字段名
功能說明
Stage
Stage字段和queryPlanner信息中stage意思一致，用戶表示執行計劃的階段信息
nReturned
本stage滿足查詢條件的數據索引數據或者doc數據條數
executionTimeMillis
整個查詢執行時間
totalKeysExamined
索引key掃描行數
totalDocsExamined
Doc掃描行數
executionTimeMillisEstimate
本stage階段執行時間
executionStats輸出字段較多，其他字段將在后續《MongoDB內核index索引模塊實現原理》中進行進一步說明。
在實際分析索引問題是否最優的時候，主要查看executionStats.totalKeysExamined、
executionStats.totalDocsExamined、executionStats .nReturned三個統計項，如果存在以下情況則說明索引存在問題，可能索引不是最優的：
executionStats.totalKeysExamine遠大于executionStats .nReturned executionStats. totalDocsExamined遠大于executionStats .nReturned2.3.allPlansExecution信息
allPlansExecution參數對應輸出信息和executionStats輸出信息類似，只是多了所有候選索引(包括reject拒絕的非最優索引)的執行過程，這里不在詳述。
2.4.總結
從上面的幾個explain執行計劃參數輸出信息可以看出，各個參數功能各不相同，總結如下：
queryPlanner輸出索引的候選索引，包括最優索引及其執行stage過程(winningPlan)+其他非最優候選索引及其執行stage過程。
注意：queryPlanner沒有真正在表中執行整個SQL，只做了查詢優化器獲取候選索引過程，因此可以很快返回。
executionStats相比queryPlanner參數，executionStats會記錄查詢優化器根據所選最優索引執行SQL的整個過程信息，會真正執行整個SQL。
allPlansExecution和executionStats類似，只是多了所有候選索引的執行過程。
云上用戶建索引常見問題及優化方法
在和用戶一起優化騰訊云上MongoDB集群索引過程中，通過和頭部用戶的交流過程中，發現很多用戶對如何創建最優索引有較驗證的錯誤認識，并且很多是大部分用戶的共性問題，這些問題總結匯總如下：
3.1.等值類查詢常見索引錯誤創建方法及如何創建最優索引
3.1.1. 同一類查詢創建多個索引問題
如下三個查詢：
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"})
用戶創建了如下3個索引：
{a:1, b:1, c:1}
{b:1, a:1, c:1}
{c:1, a:1, b:1}
實際上這3個查詢屬于同一類查詢，只是查詢字段順序不一樣，因此只需創建任一個索引即可滿足要求。驗證過程如下：
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個查詢都走了同一個索引。
3.1.2. 多字段等值查詢組合索引順序非最優
例如test表有多條數據，每條數據有3個字段，分別為a、b、c。其中a字段有10種取值，b字段有100種取值，c字段有1000種取值，稱為各個字段值的“區分度”。
用戶查詢條件為db.test.find({"a":"xxx", "b":"xxx", "c":"xxx"})，創建的索引為{a:1, b:1, c:1}。如果只是針對這個查詢，該查詢可以創建a，b，c三字段的任意組合，并且其SQL執行代價一樣，通過hint強制走不通索引，驗證過程如下：
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",
}
}
}
從上面的執行計劃可以看出，多字段等值查詢各個字段的組合順序對應執行計劃代價一樣。絕大部分用戶在創建索引的時候，都是直接按照查詢字段索引組合對應字段。
但是，單就這一個查詢，這里有個不成文的建議，把區分度更高的字段放在組合索引左邊，區分度低的字段放到右邊。這樣做有個好處，數據庫組合索引遵從最左原則，就是當其他查詢里面帶有區分度最高的字段時，就可以快速排除掉更多不滿足條件的數據。
3.1.3. 最左原則包含關系引起的重復索引
例如用戶有如下兩個查詢：
db.test.find({"b" : 2, "c" : 1}) //查詢1
db.test.find({"a" : 10, "b" : 5, "c" : 1}) //查詢2
用戶創建了如下兩個索引：
{b:1, c:1}
{a:1,b:1,c:1}
這兩個查詢中，查詢2中包含有查詢1中的字段，因此可以用一個索引來滿足這兩個查詢要求，按照最左原則，查詢1字段放左邊即可，該索引可以優化為：b, c字段索引+a字段索引，b,c字段順序可以根據區分排序，加上c字段區分度比b高，則這兩個查詢可以合并為一個{c:1, b:1, a:1}。兩個查詢可以走同一個索引驗證過程如下：
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",
}
}
}
從上面輸出可以看出，這兩個查詢都走了同一個索引。
3.1.4. 唯一字段和其他字段組合引起的無用重復索引
例如用戶有以下兩個查詢：
db.test.find({a:1,b:1})
db.test.find({a:1,c:1})
用戶為這兩個查詢創建了兩個索引，{a:1, b:1}和{a:1, c:1}，但是a字段取值是唯一的，因此這兩個查詢中a以外的字段無用，一個{a:1}索引即可滿足要求。
3.2.非等值類查詢常見索引錯誤創建方法及如何創建最優索引
3.2.1. 非等值組合查詢索引不合理創建
假設用戶有如下查詢：
//兩字段非等值查詢
db.test.find({a:{$gte:1}, c:{$lte:1}})
a,c兩個字段都是非等值查詢，很多用戶直接添加了{a:1, c:1}索引，實際上多個字段的非等值查詢，只有最左邊的字段才能走索引，例如這里只會走a字段索引，驗證過程如下：
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",
}
}
從上面執行計劃可以看出，索引數據掃描了10行(也就是a字段滿足a:{$gte:1}條件的數據多少)，但是實際上只返回了4條滿足{a:{$gte:1}, c:{$lte:1}}條件的數據，可以看出c字段無法走索引。
同理，當查詢中包含多個字段的范圍查詢的適合，除了最左邊第一個字段可以走索引，其他字段都無法走索引。因此，上面例子中的查詢候選索引為{a:1}或者{b:1}中任何一個就可以了，組合索引中字段太多會占用更多存儲成本、同時暫用更多IO資源引起寫放大。
3.2.2. 等值+非等值組合查詢索引字段順序不合理
例如下面查詢：
//兩字段非等值查詢
db.test.find({"d":{$gte:4}, "e":1})
如上查詢，d字段為非等值查詢，e字段為等值查詢，很多用戶遇到該類查詢直接創建了{d:1, e:1}索引，由于d字段為非等值查詢，因此e字段無法走索引，驗證過程如下：
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",
}
從上面驗證過程可以看出，等值類和非等值類組合查詢對應組合索引，最優索引應該優先把等值查詢放到左邊，上面查詢對應最優索引{e:1, d:1}。
3.2.3. 不同類型非等值查詢優先級問題
前面用到的非等值查詢操作符只提到了比較類操作符，實際上非等值查詢還有其他操作符。常用非等值查詢包括：$gt、$gte、$lt、$lte、$in、$nin、$ne、$exists、$type等，這些非等值查詢在絕大部分情況下存在如下優先級：
$In $gt $gte $lt $lte $nin $ne $type $exist從上到下優先級更高，例如下面的查詢：
//等值+多個不同優先級非等值查詢
db.test.find({"a":1, "b":1, "c":{$ne:5}, "e":{$type:"string"}, "f":{$gt:5},"g":{$in:[3,4]}) 查詢1
如上，該查詢等值部分查詢最優索引{a:1,b:1}(假設a區分度比b高)；非等值部分，因為$in操作符優先級最高，排他性更好，加上多個字段非等值查詢只會有一個字段走索引，因此非等值部分最優索引為{g:1}。
最終該查詢最優索引為：”等值部分最優索引”與”非等值部分最優索引”拼接，也就是{a:1,b:1, g:1}。
3.3.OR類查詢常見索引錯誤創建方法及如何創建最優索引
3.3.1. 普通OR類查詢
例如如下or查詢：
//or中包含兩個查詢
db.test.find( { $or: [{ b: 0,d:0 }, {"c":1, "a":{$gte:4}} ] } )
該查詢很多用戶直接創建了{b:1,d:1, c:1, a:1}，用戶創建該索引后，發現用戶還是全表掃描。
Or類查詢需要給數組中每個查詢添加索引，例如上面or數組中實際包含{ b: 0, d:0 }和{"c":1, "a":{$gte:4}}查詢，需要創建兩個查詢的最優索引，也就是{b:1, d:1}和{c:1, a:1}，執行計劃驗證過程如下(該測試表總10條數據)：
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",
}
}
//創建{b:1,d:1}和{c:1, a:1}兩個索引后，優化器選擇這兩個索引做為最優索引
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",
}
]
} }
}
},
從上面執行計劃可以看出，如果該OR類查詢走{b:1, d:1, c:1, a:1}索引，則實際上做了全表掃描。如果同時創建{b:1, d:1}、{c:1, a:1}索引，則直接走兩個索引，其執行key和doc掃描行數遠遠小于全表掃描。
3.3.2. 復雜OR類查詢
這里在提升一下OR查詢難度，例如下面的查詢：
//等值查詢+or類查詢+sort排序查詢
db.test.find( {"f":3, g:2, $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ) 查詢1
上面的查詢可以轉換為如下兩個查詢：
------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拆分后的兩個查詢2和查詢3組成or關系，因此對應最優所有需要創建兩個，分表是：{f:1, g:1, b:1, d:1}和 {f:1, g:1, b:1, d:1}。對應執行計劃如下：
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查詢最終可轉換為多個等值、非等值或者等值與非等值組合類查詢，通過如上變換最終可以做到舉一反三的作用。
說明：這個例子中可能在一些特殊數據分布場景，最優索引也可能是{f:1, g:1}或者{f:1, g:1, b:1, d:-1}或者{ f:1, g:1, c:1, a:1}，這里我們只考慮大部分通用場景。
3.4.Sort類排序查詢常見索引錯誤創建方法及如何創建最優索引
3.4.1. 單字段正反序排序查詢引起的重復索引
例如用戶有以下兩個查詢：
db.test.find({}).sort({a:1}).limit(2)
db.test.find({}).sort({a:-1}).limit(2)
這兩個查詢都不帶條件，排序方式不一樣，因此很多創建了兩個索引{a:1}和{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",
}
}
},
}
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. 多字段排序查詢正反序問題引起索引無效
假設有如下查詢：
//兩字段排序查詢
db.test.find().sort({a:1, b:-1}).limit(5)
其中a字段為正序，b字段為反序排序，很多用戶直接創建{a:1, b:1}索引，這時候b字段內容就存在內存排序情況。多字段排序索引，如果沒有攜帶查詢條件，則最優索引即為排序字段對應索引，這里切記保持每個字段得正反序和sort完全一致，否則可能存在部分字段內存排序的情況，執行計劃驗證過程如下：
//{a:1, b:1}只會有一個字段走索引，另一個字段內存排序
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}兩個字段走索引，不存在內存排序
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})
該類查詢很多人直接創建{a:1,b:1, c:1, d:1}，結果造成內存排序。這種組合查詢最優索引=“多字段等值查詢最優索引_多字段排序類組合最優索引”，例如該查詢：
{ "a" : 3, "b" : 1}等值查詢假設a區分度比b高，則對應最優索引為：{a：1, b：1}
{ c:-1, d:1}排序類查詢最優索引保持正反序一致，也就是:{ c:-1, d:1}
因此整個查詢就是這兩個查詢對應最優索引拼接，也就是{a:1, b:1, c:-1, d:1}，對應執行計劃過程驗證如下：
//非最優索引執行計劃，存在內存排序
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",
}
}
}
},
}
//最優索引執行計劃，直接走排序索引
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排序查詢
假設有下面的查詢：
//等值+非等值+sort排序查詢
db.test.find({"a":3, "b":1, "c":{$gte:1}}).sort({d:-1, e:1})
騰訊云很多用戶看到該查詢直接創建{a:1,b:1, c:1, d:-1, e:1}索引，發現存在內存排序。等值+非等值+sort排序組合查詢，由于非等值查詢右邊的字段不能走索引，因此如果把d, e放到c的右邊，則d，e字段索引無效。
等值+非等值+sort排序最優索引組合字段順序為：等值_sort排序_非等值，因此上面查詢最優索引為:{a:1, b:1, d:-1, e:1, c: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, c:1, d:-1, e:1}).explain("executionStats")
{
"executionStats" : {
"totalKeysExamined" : 9,
"totalDocsExamined" : 9,
"executionStages" : {
"stage" : "FETCH",
"inputStage" : {
"stage" : "SORT", //內存排序
"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})
上面組合很多人直接創建{b:1, d:1, c:1, a:1, e:1}，該索引創建后還是會掃表和內存排序，實際上OR+SORT組合查詢可以轉換為下面兩個查詢：
//查詢1等價轉換為如下查詢
-----db.test.find({ b: 3, d:5 }).sort({e:-1}) //查詢2
or--|
-----db.test.find( {"c":1, "a":6} ).sort({e:-1}) //查詢3
所以這個復雜查詢就可以拆分為等值組合查詢+sort排序查詢，拆分為上面的兩個查詢，這樣我們只需要同時創建查詢2和查詢3對應最優索引即可。該查詢最終拆分后對應最優索引需要添加如下兩個：
{b:1, d:1, e:-1}和{c:1,a:1, e:-1}
非最優索引和最優索引執行計劃驗證過程如下：
//走{b:1, d:1, c:1, a:1, e:-1}索引，全表掃描加內存排序
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" : {
//測試構造表中23條數據，總數據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}兩個最優索引的執行計劃，無內存排序
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類查詢常見索引錯誤創建方法》把OR查詢轉換為多個等值、非等值或者等值與非等值組合查詢，然后與sort排序對應索引字段拼接。例如下面查詢：
//原查詢
db.test.find( {"f":3, g:2, $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } ).sort({e:-1}) //查詢1
拆分后的兩個查詢組成or關系，如下：
//拆分后查詢
------ 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]，因此只需要創建查詢2和查詢3兩個最優索引即可滿足查詢1要求，查詢2和查詢3最優索引可以參考前面《or類查詢常見索引錯誤創建方法》，該查詢最終需要創建如下兩個索引：
{f:1, g:1, b:1, d:1, e:-1}和{ f:1, g:1, c:1, a:1, e:-1}
說明：這個例子中可能在一些特殊數據分布場景，最優索引也可能是{f:1, g:1}或者{f:1, g:1, b:1, d:1, e:-1}或者{ f:1, g:1, c:1, a:1, e:-1}，這里我們只考慮通用場景。
3.5.避免創建太多無用索引及無用索引分析方法
在騰訊云上，我們還發現另外一個問題，很多實例存在大量無用索引，無用索引會引起以下問題：
存儲成本增加沒增加一個索引，MongoDB內核就會創建一個index索引文件，記錄該表的索引數據，造成存儲成本增加。
影響寫性能用戶沒寫入一條數據，就會在對應索引生成一條索引KV，實現索引與數據的一一對應，索引KV數據寫入Index索引文件過程加劇寫入負載。
影響讀性能MongoDB內核查詢優化器原理是通過候選索引快速定位到滿足條件的數據，然后采樣評分。如果滿足條件的候選索引越多，整個評分過程就會越長，增加內核選擇最優索引的流程。
下面已一個真實線上實例為例，說明如何找出無用索引：
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默認提供有索引統計命令來獲取各個索引命中的次數，該命令如下：
> 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") } }
該聚合輸出中的幾個核心指標信息如下表：
字段內容
說明
name
索引名，代表是針對那個索引的統計。
ops
索引命中次數，也就是所有查詢中采用本索引作為查詢索引的次數。
上表中的ops代表命中次數，如果命中次數為0或者很小，說明該索引很少被選為最優索引使用，因此可以認為是無用索引，可以考慮刪除。
MongoDB不同分類查詢最優索引總結
查詢大類
子類
生成候選索引規則
普通查詢
單字段查詢
無需計算，直接輸出索引
多字段等值查詢
分析字段schema，得出區分度 如果某字段區分度和采樣數據條數一致，則直接添加該字段的索引即可，無需多字段組合，流程結束。 給出候選索引，按照區分度從左向右生成組合索引。 多字段等值查詢，只會有一個候選索引說明：本身多字段等值查詢，最優索引和字段組合順序無關，但是這里一般有個不成文歸檔，把區分度最高的字段放在最左邊，這樣有利于帶有該字段新查詢的快速排他性
多字段非等值查詢
非等值查詢，通過優先級確定候選索引，非等值操作符優先級順序如下：
$In $gt $gte $lt $lte $nin $ne $type $exist如果字段優先級一樣，則會對應多個候選索引，例如：{a>1, b>1,c >1}查詢，候選索引是以下3個中的一個：
{a:1} {b:1} {c: 1}這時候就需要根據數據分布評估3個候選索引中那個更好。
等值與非等值組合
等值與非等值組合，候選索引規則步驟如下：
等值按照schema區分度，獲取所有等值字段的候選索引，只會有一個候選索引 等值部分與所有非等值字段組合為候選索引，最終有多少個非等值查詢，就會有多少個候選索引舉例：db.collection.find(a=1, b=2, c>3, d>4)
假設(a=1, b=2)等值查詢按照區分度最優索引為{b:1,a:1}，則候選索引有如下兩種：
{b:1，a:1，c:1}
{b:1，a:1，d:1}
這時候就需要根據數據分布情況決定加這兩個候選索引的哪一個作為最優索引。
排序類型
不帶查詢的排序
不帶查詢條件的排序，
例如：db.xx.find({}).sort({a:1,b:-1,c:1})，對應候選索引直接是排序索引：
{a:1,b:-1,c:1}
普通查詢+sort排序
該場景候選索引包括：
等值查詢候選索引 Sort排序候選索引舉例：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}
Sort候選索引{ e:1, f:-1}
假設等值部分按照區分度最優索引為{a:1, b:1},非等值最優索引為{d:1}，則整個查詢最優索引=等值部分最優索引_sort排序最優索引_非等值部分最優索引，也就是{a:1,b:1,e:1,f:-1d:1}
OR類查詢
(可拆分為多個普通查詢)
一個子tree
候選索引就是該子tree對應候選索引，參考《普通查詢》對應候選索引推薦算法
多個子tree
(無交集字段)
對每個tree對應普通查詢生成一個最優索引，多個子tree會有多個候選索引，每個tree對應候選索引規則參考《普通查詢》
更多查詢匯總信息
參考第三章
參考第三章
說明：
本文總結的《最優索引規則大全》中的規則適用于絕大部分查詢場景，但是一些特殊數據分布場景可能會有一定偏差，請根據實際數據分布進行查詢計劃分析。
責任編輯：Rex_08
營業執照公示信息