# MongoDB教育訓練-06 [![image-1639705033770.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/t1AM4RJGX5ZBtLVf-image-1639705033770.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/t1AM4RJGX5ZBtLVf-image-1639705033770.png) [![image-1639705137852.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/khGTD0wPcEe6ELd8-image-1639705137852.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/khGTD0wPcEe6ELd8-image-1639705137852.png) mongoDB 使用Ｂ-tree索引 [![image-1639705482607.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/FahDdBDIM56kjSwr-image-1639705482607.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/FahDdBDIM56kjSwr-image-1639705482607.png) 查詢至少要用到索引，不要full table scan(所以要至少建立可用索引) [![image-1639705535799.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/IcNsjB8malFbXVe9-image-1639705535799.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/IcNsjB8malFbXVe9-image-1639705535799.png) [![image-1639705770711.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/3HGEO8hp5SuzV7VI-image-1639705770711.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/3HGEO8hp5SuzV7VI-image-1639705770711.png) 檢查執行計畫快取 -> (無) 有可能優化的索引找出來，同時跑看誰的效率快，加入快取 更新快取時機: - 當前索引變慢(系統判斷) - 當新的索引被創建 - server 重啟(memory 清除) [![image-1639705807311.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/qTqMHpE3PRcGVKSh-image-1639705807311.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/qTqMHpE3PRcGVKSh-image-1639705807311.png) - 需要指定欄位 - 欄位可以是任何類型 [![image-1639706094542.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/8w46WyVtDVVFOrOX-image-1639706094542.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/8w46WyVtDVVFOrOX-image-1639706094542.png) [![image-1639706346750.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/zrUfOeVQ03fLN8eh-image-1639706346750.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/zrUfOeVQ03fLN8eh-image-1639706346750.png) Explain 執行計畫 - queryPlanner : 只顯示執行計畫 - executionStats(最常用): 會把最優執行計畫執行一遍，並收集相關資訊 - allPlansExecution:把所有執行計畫執行一遍，並收集相關資訊 [![image-1639706656145.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/UvwFOcKDjW3wYOTv-image-1639706656145.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/UvwFOcKDjW3wYOTv-image-1639706656145.png) - nReturn: 返回數量 - totalKeyExamined ：找到索引 - totalDocExamined : 查找document數量 - stage:COLLSAN => 無index - totalKeyExamined / nReturn 最好小於1000 [![image-1639706909824.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/ANlcZf5NJFiDeENQ-image-1639706909824.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/ANlcZf5NJFiDeENQ-image-1639706909824.png) number\_of\_reviews: 1 (1: 升冪,-1:降冪) [![image-1639707147638.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/V7W991262RVWH978-image-1639707147638.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/V7W991262RVWH978-image-1639707147638.png) 通過index 取得資訊：FETCH [![image-1639707345619.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/qNaUUJCZBvCPfNua-image-1639707345619.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/qNaUUJCZBvCPfNua-image-1639707345619.png) - nReturn:totalKeyExamined:totalDocExamined 1:1:1 最佳情況 [![image-1639707445597.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/iMAC2vsFjDUcagYR-image-1639707445597.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/iMAC2vsFjDUcagYR-image-1639707445597.png) [![image-1639707511649.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/b0jijKi7hs3NW8l5-image-1639707511649.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/b0jijKi7hs3NW8l5-image-1639707511649.png) [![image-1639707565143.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/UPwVfU3YeI8ebflZ-image-1639707565143.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/UPwVfU3YeI8ebflZ-image-1639707565143.png) 取得所有索引 [![image-1639707652623.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/9CLzgoztLs1ULVyh-image-1639707652623.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/9CLzgoztLs1ULVyh-image-1639707652623.png) 刪除索引 [![image-1639707687385.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/Pru2KUGncvB033Iv-image-1639707687385.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/Pru2KUGncvB033Iv-image-1639707687385.png) [![image-1639707697169.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/azQ4hbFPVFwHFByu-image-1639707697169.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/azQ4hbFPVFwHFByu-image-1639707697169.png) 需要放得下index的記憶體（最低要求） [![image-1639707720558.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/7GAr6sU8Usp7VSP5-image-1639707720558.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/7GAr6sU8Usp7VSP5-image-1639707720558.png) db.tablename.stats(檔案除以？(1024 => KB,102481024 => MB)).indexSizes [![image-1639707935930.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/65z7pfIxCgvv4WCQ-image-1639707935930.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/65z7pfIxCgvv4WCQ-image-1639707935930.png) - storageSize : 物理檔案大小 - 刪除資料不會減少資料庫大小，只是把硬碟位置空出來，重複使用 - 釋放空間：compact [https://docs.mongodb.com/manual/reference/command/compact/](https://docs.mongodb.com/manual/reference/command/compact/) [![image-1639708000892.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/mUDxLz2Iz2xMZzn2-image-1639708000892.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/mUDxLz2Iz2xMZzn2-image-1639708000892.png) [![image-1639708353823.png](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/scaled-1680-/QLPhvFC28gLvwdnr-image-1639708353823.png)](https://bookstack.treemanou.com/uploads/images/gallery/2021-12/QLPhvFC28gLvwdnr-image-1639708353823.png)