雲上MongoDB常見索引問題及最優索引規則大全
本文乾貨較多，建議收藏學習。先將 文 章結構速覽奉上：
一、背景
二、MongoDB執行計劃
2.1 queryPlanner資訊
2.2 executionStats資訊
2.3 allPlansExecution資訊
三、雲上使用者建索引常見問題及優化方法
3.1 等值類查詢常見問題及優化方法
3.1.1 同一類查詢建立多個索引問題
3.1.2 多欄位等值查詢組合索引順序非最優
3.1.3 最左原則包含關係引起的重複索引
3.1.4 唯一欄位和其他欄位組合引起的無用重複索引
3.2 非等值類查詢常見問題及優化方法
3.2.1 非等值組合查詢索引不合理建立
3.2.2 等值+非等值組合查詢索引欄位順序不合理
3.2.3 不同型別非等值查詢優先順序問題
3.3 OR類查詢常見問題及優化方法
3.3.1 普通OR類查詢優化方法
3.3.2 複雜OR類查詢優化方法
3.4 SORT類排序查詢常見問題及優化方法
3.4.1 單欄位正反序排序查詢引起的重複索引
3.4.2 多欄位排序查詢正反序問題引起索引無效
3.4.3 等值查詢+多欄位排序組合查詢
3.4.4 等值查詢+非等值查詢+SORT排序查詢
3.4.5 OR+SORT組合排序查詢
3.5 無用索引優化方法
四、MongoDB不同型別查詢最優索引總結
一、背景
騰訊雲MongoDB當前已服務於遊戲、電商、社交、教育、新聞資訊、金融、物聯網、軟體服務、汽車出行、音影片等多個行業。
騰訊MongoDB團隊在配合使用者分析問題過程中，發現 雲上使用者存在如下索引共性問題 ，主要集中在如下方面：
-
無用索引；
-
重複索引；
-
索引不是最優；
-
對索引理解有誤等。
本文 重點分析總結騰訊雲上使用者索引建立不合理相關的問題 ，通過本文可以學習到MongoDB的以下知識點：
-
如何理解MongoDB執行計劃？
-
如何確認查詢索引是不是最優索引？
-
雲上使用者對索引的一些錯誤建立方法；
-
如何建立最優索引？
-
建立最優索引的規則彙總。
本文總結的 《最優索引規則建立大全》 不僅僅適用於MongoDB，很多規則 同樣適用於MySQL等關係型資料庫 。
二、 執行計劃
判斷索引選擇及不同索引執行傢伙資訊可以通過explain操作獲取， MongoDB通過explain來獲取SQL執行過程資訊 ，當前持續explain的請求命令包含以下幾種：
aggregate, count, distinct, find, findAndModify, delete, mapReduce, and update。
詳見explain官網連結：
http://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" : <STAGE1>,..."inputStage" : {"stage" : <STAGE2>,..."inputStage" : {"stage" : <STAGE3>,...}}},
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" : <boolean>,"nReturned" : <int>,"executionTimeMillis" : <int>,"totalKeysExamined" : <int>,"totalDocsExamined" : <int>,"executionStages" : {"stage" : <STAGE1>"nReturned" : <int>,"executionTimeMillisEstimate" : <int>,"works" : <int>,"advanced" : <int>,"needTime" : <int>,"needYield" : <int>,"saveState" : <int>,"restoreState" : <int>,"isEOF" : <boolean>,..."inputStage" : {"stage" : <STAGE2>,"nReturned" : <int>,"executionTimeMillisEstimate" : <int>,..."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
如果存在以下情況則說明索引存在問題，可能索引不是最優的：
1. executionStats.totalKeysExamine遠大於executionStats .nReturned
2. executionStats. totalDocsExamined遠大於executionStats .nReturned
2.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}) //查詢1db.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排序查詢//查詢1db.test.find( {"f":3, g:2, $or: [{ b: 0, d:0 }, {"c":1, "a":6} ] } )
上面的查詢可以轉換為如下兩個查詢：
------db.test.find( {"f":3, g:2, b: 0, d:0 } ) //查詢2or--|------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組合 查詢1db.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}) //查詢2or--|-----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}) //查詢2or--｜------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不同型別查詢最優索引總結
|
查詢大類 |
子類 |
生成候選索引規則 |
|
普通查詢 |
單欄位查詢 |
無需計算，直接輸出索引 |
|
多欄位等值查詢 |
說明：本身多欄位等值查詢，最優索引和欄位組合順序無關，但是這裡一般有個不成文歸檔，把區分度最高的欄位放在最左邊，這樣有利於帶有該欄位新查詢的快速排他性 |
|
|
多欄位非等值查詢 |
非等值查詢，通過優先順序確定候選索引，非等值操作符優先順序順序如下：
如果欄位優先順序一樣，則會對應多個候選索引，例如{a>1, b>1,c >1}查詢，候選索引是以下3箇中的一個：
這時候就需要根據資料分佈評估3個候選索引中那個更好。 |
|
|
等值與非等值組合 |
等值與非等值組合，候選索引規則步驟如下：
舉例： 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排序 |
該場景候選索引包括：
舉例： 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} 假設等值部分按照區分度最優索引為{a:1, b:1},非等值最優索引為{d:1}，則整個查詢最優索引=等值部分最優索引_sort排序最優索引_非等值部分最優索引，也就是{a:1,b:1,e:1,f:-1d:1} |
|
|
OR類查詢 (可拆分為多個普通查詢) |
一個子tree |
候選索引就是該子tree對應候選索引，參考《普通查詢》對應候選索引推薦演算法 |
|
多個子tree (無交集欄位) |
對每個tree對應普通查詢生成一個最優索引，多個子tree會有多個候選索引，每個tree對應候選索引規則參考《普通查詢》 |
|
|
更多查詢彙總資訊 |
參考第三章 |
參考第三章 |
說明：
本文總結的《最優索引規則大全》中的規則適用於絕大部分查詢場景，但是一些特殊資料分佈場景可能會有一定偏差，請根據實際資料分佈進行查詢計劃分析。
DBbrain for MongoDB
最後，本文中所介紹的優化原理即將整合到騰訊雲DBbrain for MongoDB的智慧索引推薦(規則+代價計算)功能中，屆時可幫助使用者一鍵優化索引，無需親自反覆推敲驗證，歡迎體驗。
關於作者—— 騰訊雲MongoDB團隊
騰訊雲MongoDB當前服務於遊戲、電商、社交、教育、新聞資訊、金融、物聯網、軟體服務等多個行業；MongoDB團隊(簡稱CMongo)致力於對開源MongoDB核心進行深度研究及持續性優化(如百萬庫表、物理備份、免密、審計等)，為使用者提供高效能、低成本、高可用性的安全資料庫儲存服務。後續持續分享MongoDB在騰訊內部及外部的典型應用場景、踩坑案例、效能優化、核心模組化分析。
﹀
﹀
﹀
-- 更多精彩 --
騰訊雲資料庫核心叢集索引優化實踐
↓ ↓ 點選 閱讀原文 ，瞭解更多優惠
- 自研能力再獲認可，騰訊雲資料庫入選 Forrester Translytical 報告
- 如何像用自來水一樣使用資料庫？
- DTS搭載全新自研核心，突破兩地三中心架構的關鍵技術
- 節省30%磁碟空間的同時如何保障資料安全？｜DB·洞見
- TDSQL PG版再升級，深度參與開源生態建設
- 微服務 分散式再上一“城”，騰訊雲資料庫助力海峽銀行新一代核心系統上線
- 資料接入平臺(DIP)系列文章之一|功能及架構淺析
- 騰訊雲原生資料庫 TDSQL-C 架構探索和實踐
- Redis如何實現多可用區？
- Redis如何實現多可用區？
- 穩字當頭抓落實｜夯實數字“底座”，工信部加快推進算力產業發展
- 加快企業級能力國產化，騰訊雲資料庫做了這些事情
- 騰訊雲TDSQL-C重磅升級，效能全面領跑雲原生資料庫市場
- 一些有趣的B 樹優化實驗
- 一些有趣的B 樹優化實驗
- 大咖論道 | 基礎軟體的過去、現在、未來
- 一些有趣的B 樹優化實驗
- 騰訊雲Redis全面升級，效能提升400%，可用性高達5個9
- 騰訊雲資料庫TDSQL兩大引擎全新升級，分析能力和Oracle相容能力大幅提升
- 連續三年！我們做到了