【Mongodb】視圖 && 索引_ZenDei技術網路在線

Mongodb視圖可以讓查詢數據變的更加方便，索引讓查詢數據變得更加快捷，本文介紹如何使用Mongodb的視圖功能和索引功能 ...

準備工作

準備2個集合的數據，後面視圖和索引都會用到
1個訂單集合，一個收款信息集合

var orders = new Array();
var shipping = new Array();
var addresses = ["廣西省玉林市", "湖南省岳陽市", "湖北省荊州市", "甘肅省蘭州市", "吉林省松原市", "江西省景德鎮", "遼寧省沈陽市", "福建省廈門市", "廣東省廣州市", "北京市朝陽區"];

for (var i = 10000; i < 20000; i++) {
    var orderNo = i + Math.random().toString().substr(2, 5);
    orders[i] = { orderNo: orderNo, userId: i, price: Math.round(Math.random() * 10000) / 100, qty: Math.floor(Math.random() * 10) + 1, orderTime: new Date(new Date().setSeconds(Math.floor(Math.random() * 10000))) };

    var address = addresses[Math.floor(Math.random() * 10)];
    shipping[i] = { orderNo: orderNo, address: address, recipienter: "Wilson", province: address.substr(0, 3), city: address.substr(3, 3) }
}
db.order.insert(orders);
db.shipping.insert(shipping);

視圖

概述

A MongoDB view is a queryable object whose contents are defined by an aggregation pipeline on other collections or views. MongoDB does not persist the view contents to disk. A view’s content is computed on-demand when a client queries the view. MongoDB can require clients to have permission to query the view. MongoDB does not support write operations against views.

Mongodb的視圖基本上和SQL的視圖一樣

數據源（集合或視圖）
提供查詢
不實際存儲硬碟
客戶端發起請求查詢時計算而得

1. 創建視圖

有兩種方法創建視圖

db.createCollection(
  "<viewName>",
  {
    "viewOn" : "<source>",
    "pipeline" : [<pipeline>],
    "collation" : { <collation> }
  }
)

db.createView(
  "<viewName>",
  "<source>",
  [<pipeline>],
  {
    "collation" : { <collation> }
  }
)

一般使用db.createView

viewName : 必須，視圖名稱

source : 必須，數據源，集合/視圖

[<pipeline>] : 可選，一組管道，可見管道是Mongodb比較重要的一環

1.1 單個集合創建視圖

假設現在查看當天最高的10筆訂單視圖，例如後臺某個地方需要實時顯示金額最高的訂單

db.createView(
    "orderInfo",         //視圖名稱
    "order",             //數據源   
    [
        //篩選符合條件的訂單，大於當天，這裡要註意時區
        { $match: { "orderTime": { $gte: ISODate("2020-04-13T16:00:00.000Z") } } },
        //按金額倒序
        { $sort: { "price": -1 } },
        //限制10個文檔
        { $limit: 10 },
        //選擇要顯示的欄位
        //0: 排除欄位，若欄位上使用（_id除外），就不能有其他包含欄位
        //1: 包含欄位
        { $project: { _id: 0, orderNo: 1, price: 1, orderTime: 1 } }
    ]
)

然後就可以直接使用orderInfo這個視圖查詢數據

db.orderInfo.find({})

返回結果

{ "orderNo" : "1755149436", "price" : 100, "orderTime" : ISODate("2020-04-14T13:49:42.220Z") }
{ "orderNo" : "1951423853", "price" : 99.99, "orderTime" : ISODate("2020-04-14T15:08:07.240Z") }
{ "orderNo" : "1196303215", "price" : 99.99, "orderTime" : ISODate("2020-04-14T15:15:41.158Z") }
{ "orderNo" : "1580069456", "price" : 99.98, "orderTime" : ISODate("2020-04-14T13:41:07.199Z") }
{ "orderNo" : "1114480559", "price" : 99.98, "orderTime" : ISODate("2020-04-14T13:31:58.150Z") }
{ "orderNo" : "1229542817", "price" : 99.98, "orderTime" : ISODate("2020-04-14T15:15:35.162Z") }
{ "orderNo" : "1208031402", "price" : 99.94, "orderTime" : ISODate("2020-04-14T14:13:02.160Z") }
{ "orderNo" : "1680622670", "price" : 99.93, "orderTime" : ISODate("2020-04-14T15:17:25.210Z") }
{ "orderNo" : "1549824953", "price" : 99.92, "orderTime" : ISODate("2020-04-14T13:09:41.196Z") }
{ "orderNo" : "1449930147", "price" : 99.92, "orderTime" : ISODate("2020-04-14T15:16:15.187Z") }

1.2 多個集合創建視圖

其實跟單個是集合是一樣，只是多了$lookup連接操作符，視圖根據管道最終結果顯示，所以可以關聯多個集合（若出現這種情況就要考慮集合設計是否合理，mongodb本來就是文檔型資料庫）

db.orderDetail.drop()
db.createView(
    "orderDetail",
    "order",
    [
        { $lookup: { from: "shipping", localField: "orderNo", foreignField: "orderNo", as: "shipping" } },
        { $project: { "orderNo": 1, "price": 1, "shipping.address": 1 } }
    ]
)

查詢視圖，得到如下結果

{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6c3"), "orderNo" : "1000039782", "price" : 85.94, "shipping" : [ { "address" : "北京市朝陽區" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6c4"), "orderNo" : "1000102128", "price" : 29.04, "shipping" : [ { "address" : "吉林省松原市" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6c5"), "orderNo" : "1000214514", "price" : 90.69, "shipping" : [ { "address" : "湖南省岳陽市" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6c6"), "orderNo" : "1000337987", "price" : 75.05, "shipping" : [ { "address" : "遼寧省沈陽市" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6c7"), "orderNo" : "1000468969", "price" : 76.84, "shipping" : [ { "address" : "江西省景德鎮" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6c8"), "orderNo" : "1000572219", "price" : 60.25, "shipping" : [ { "address" : "江西省景德鎮" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6c9"), "orderNo" : "1000611743", "price" : 19.14, "shipping" : [ { "address" : "廣東省廣州市" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6ca"), "orderNo" : "1000773917", "price" : 31.5, "shipping" : [ { "address" : "北京市朝陽區" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6cb"), "orderNo" : "1000879146", "price" : 76.16, "shipping" : [ { "address" : "吉林省松原市" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6cc"), "orderNo" : "1000945977", "price" : 93.98, "shipping" : [ { "address" : "遼寧省沈陽市" } ] }

可以看到，mongodb不是像SQL那樣把連接的表當成列列出，而是把連接結果放在數組裡面，這很符合Mongodb文檔型結構。

2. 修改視圖

假設現在需要增加一個數量的欄位

db.runCommand({
    collMod: "orderInfo",
    viewOn: "order",
    pipeline: [
        { $match: { "orderTime": { $gte: ISODate("2020-04-13T16:00:00.000Z") } } },
        { $sort: { "price": -1 } },
        { $limit: 10 },
        //增加qty
        { $project: { _id: 0, orderNo: 1, price: 1, qty: 1, orderTime: 1 } }
    ]
})

當然，也可以刪除視圖，重新用db.createView()創建視圖

3. 刪除視圖

db.orderInfo.drop();

索引

概述

Indexes support the efficient execution of queries in MongoDB. Without indexes, MongoDB must perform a collection scan, i.e. scan every document in a collection, to select those documents that match the query statement. If an appropriate index exists for a query, MongoDB can use the index to limit the number of documents it must inspect.

索引能提供高效的查詢，沒有索引的查詢，mongole執行集合掃描，相當於SQL SERVER的全表掃描，掃描每一個文檔。

數據存在存儲介質上，大多數情況是為了查詢，查詢的快慢直接影響用戶體驗，mongodb索引也是空間換時間，添加索引，CUD操作都會導致索引重新生成，影響速度。

1. 準備工作

1.1 準備200W條數據

var orderNo = 100 * 10000;
for (var i = 0; i < 100; i++) {
    //分批次插入，每次20000條
    var orders = new Array();
    for (var j = 0; j < 20000; j++) {
        var orderNo = orderNo++;
        orders[j] = { orderNo: orderNo, userId: i + j, price: Math.round(Math.random() * 10000) / 100, qty: Math.floor(Math.random() * 10) + 1, orderTime: new Date(new Date().setSeconds(Math.floor(Math.random() * 10000))) };
    }
    //不需寫入確認
    db.order.insert(orders, { writeConcern: { w: 0 } });
}

1.2 mongodb的查詢計劃

db.collection.explain().<method(...)>

一般使用執行統計模式，例如

db.order.explain("executionStats").find({orderNo:1000000})

返回的executionStats對象欄位說明

部分欄位說明

欄位	說明
executionSuccess	是否執行成功
nReturned	返回匹配文檔數量
executionTimeMillis	執行時間，單位：毫秒
totalKeysExamined	索引檢索數目
totalDocsExamined	文檔檢索數目

查看未加索引前查詢計劃

db.order.explain("executionStats").find({orderNo:1000000})

截取部分返回結果,可以看出

executionTimeMillis : 用時1437毫秒
totalDocsExamined : 掃描文檔200W
executionStages.stage : 集合掃描

"executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 1,
    "executionTimeMillis" : 1437,
    "totalKeysExamined" : 0,
    "totalDocsExamined" : 2000000,
    "executionStages" : {
            "stage" : "COLLSCAN",

1.3 查看當前集合統計信息

db.order.stats()

截取部分信息，可以看出現在存儲文件大小大概為72M

{
        "ns" : "mongo.order",
        "size" : 204000000,
        "count" : 2000000,
        "avgObjSize" : 102,
        "storageSize" : 74473472,

2. 創建索引

db.order.createIndex({ orderNo: 1 }, { name: "ix_orderNo" })

索引名稱不是必須，若不指定，按欄位名稱_排序類型組合自動生成，索引名稱一旦創建不能修改，若要修改，只能刪除索引重新生成索引，建議還是建索引的時候就把索引名稱設置好。

2.1 執行查詢計劃

db.order.explain("executionStats").find({orderNo:1000000})

截取部分結果，直觀就可以感覺查詢速度有了質的提升，再看查詢計劃更加驚訝

nReturned : 匹配到1個文檔
executionTimeMillis : 0，呃。。
totalKeysExamined : 總共檢索了1個索引
totalDocsExamined : 總共檢索了1個文檔
executionStages.stage : FETCH，根據索引去檢索指定文檔，像SQL的Index Seek

 "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 1,
                "executionTimeMillis" : 0,
                "totalKeysExamined" : 1,
                "totalDocsExamined" : 1,
                "executionStages" : {
                        "stage" : "FETCH"