Mongodb view & Index

Time:2021-6-4

 

preparation

Prepare the data of the two sets, which will be used later in view and index
One order set and one collection information set

var orders = new Array();
var shipping = new Array();
Var addresses = ["Yulin City, Guangxi Province", "Yueyang City, Hunan Province", "Jingzhou City, Hubei Province", "Lanzhou City, Gansu Province", "Songyuan City, Jilin Province", "Jingdezhen City, Jiangxi Province", "Shenyang City, Liaoning Province", "Xiamen City, Fujian Province", "Guangzhou City, Guangdong Province", "Chaoyang District, Beijing city];

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);

 

view

summary

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.

The view of mongodb is basically the same as that of SQL

  • Data source (set or view)
  • Provide query
  • The hard disk is not physically stored
  • It is calculated when the client initiates the request query

1. Create view

There are two ways to create a view

db.createCollection(
  "",
  {
    "viewOn" : "",
    "pipeline" : [],
    "collation" : {  }
  }
)
db.createView(
  "",
  "",
  [],
  {
    "collation" : {  }
  }
)

Generally, db.createview is used

VIEWNAME: required, view name

Source: must, data source, set / view

[]: optional, a group of pipes. It can be seen that pipes are an important part of mongodb

 

1.1 creating views from a single collection

Suppose you now view the top 10 orders of the day. For example, a place in the background needs to display the orders with the highest amount in real time

db.createView(
    "Orderinfo", // view name
    "Order", // data source   
    [
        //Filter the qualified order, which is larger than the current day, here we should pay attention to the time zone
        { $match: { "orderTime": { $gte: ISODate("2020-04-13T16:00:00.000Z") } } },
        //By amount in reverse order
        { $sort: { "price": -1 } },
        //Limit 10 documents
        { $limit: 10 },
        //Select the fields to display
        //0: exclude fields. If (_ Except ID), there can be no other containing fields
        //1: Include fields
        { $project: { _id: 0, orderNo: 1, price: 1, orderTime: 1 } }
    ]
)

Then you can directly use the view orderinfo to query the data

db.orderInfo.find({})

Return results

{ "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 creating views from multiple sets

In fact, it’s the same as a single set, except that there is an additional $lookup connection operator. The view is displayed according to the final result of the pipeline, so multiple sets can be associated (if this happens, we need to consider whether the set design is reasonable. Mongodb is a document database)

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

Query the view and get the following results

{ "_ ID ": objectid (" 5e95af8c4ef6faf974b4a6c3 ")," orderNo ":" 1000039782 "," price ": 85.94," shipping ": [{" address ":" Chaoyang District, Beijing]}]
{ "_ ID ": objectid (" 5e95af8c4ef6faf974b4a6c4 ")," orderNo ":" 1000102128 "," price ": 29.04," shipping ": [{" address ":" Songyuan City, Jilin Province "}]}
{ "_ ID ": objectid (" 5e95af8c4ef6faf974b4a6c5 ")," orderNo ":" 1000214514 "," price ": 90.69," shipping ": [{" address ":" Yueyang City, Hunan Province "}]}
{ "_ ID ": objectid (" 5e95af8c4ef6faf974b4a6c6 ")," orderNo ":" 1000337987 "," price ": 75.05," shipping ": [{" address ":" Shenyang City, Liaoning Province "}]}
{ "_ ID ": objectid (" 5e95af8c4ef6faf974b4a6c7 ")," orderNo ":" 1000468969 "," price ": 76.84," shipping ": [{" address ":" Jingdezhen, Jiangxi Province "}]}
{ "_ ID ": objectid (" 5e95af8c4ef6faf974b4a6c8 ")," orderNo ":" 1000572219 "," price ": 60.25," shipping ": [{" address ":" Jingdezhen, Jiangxi Province "}]}
{ "_ ID ": objectid (" 5e95af8c4ef6faf974b4a6c9 ")," orderNo ":" 1000611743 "," price ": 19.14," shipping ": [{" address ":" Guangzhou City, Guangdong Province "}]}
{ "_ ID ": objectid (" 5e95af8c4ef6faf974b4a6ca ")," orderNo ":" 1000773917 "," price ": 31.5," shipping ": [{" address ":" Chaoyang District, Beijing]}]
{ "_ ID ": objectid (" 5e95af8c4ef6faf974b4a6cb ")," orderNo ":" 1000879146 "," price ": 76.16," shipping ": [{" address ":" Songyuan City, Jilin Province "}]}
{ "_ ID ": objectid (" 5e95af8c4ef6faf974b4a6cc ")," orderNo ":" 1000945977 "," price ": 93.98," shipping ": [{" address ":" Shenyang City, Liaoning Province "}]}

As you can see, mongodb does not list the connected tables as columns like SQL, but places the connection results in an array, which is very consistent with the document type structure of mongodb.

 

2. Modify the view

Suppose you need to add a number of fields now

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

Of course, you can also delete the view and re create it with DB. Createview()

 

3. Delete view

db.orderInfo.drop();

 

Indexes

summary

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.

Index can provide efficient queries. For queries without index, mongole performs set scanning, which is equivalent to SQL Server’s full table scanning, scanning every document.

Data is stored on the storage medium. In most cases, it is for query. The speed of query directly affects the user experience. Mongodb index is also space for time. Adding index and cud operation will lead to index regeneration and affect the speed.

 

1. Preparation

1.1 prepare 200W data

var orderNo = 100 * 10000;
for (var i = 0; i < 100; i++) {
    //Insert 20000 pieces in batches each time
    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))) };
    }
    //No write confirmation is required
    db.order.insert(orders, { writeConcern: { w: 0 } });
}

 

1.2 query plan of mongodb

db.collection.explain().

 

Generally, the execution statistics mode is used, such as

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

The executionstats object returnedField description

Description of some fields

field explain
executionSuccess Is the execution successful
nReturned Returns the number of matching documents
executionTimeMillis Execution time in milliseconds
totalKeysExamined Number of index searches
totalDocsExamined Number of document searches

View pre indexed query plan

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

Intercepting part of the returned results, we can see that

  • Execution time millis: 1437 MS
  • Totaldocsexamined: scan document 200W
  • Executionstages.stage: set scan
"executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 1,
    "executionTimeMillis" : 1437,
    "totalKeysExamined" : 0,
    "totalDocsExamined" : 2000000,
    "executionStages" : {
            "stage" : "COLLSCAN",

 

1.3 view current collection statistics

db.order.stats()

Intercepting part of the information, you can see that the size of the stored file is about 72m

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

 

2. Create index

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

Index name is not required. If it is not specified, by field name_ The sort type combination is generated automatically. Once the index name is created, it cannot be modified. If you want to modify it, you can only delete the index and regenerate it. It is recommended that the index name be set when the index is created.

 

2.1 execution query plan

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

By intercepting some of the results, you can intuitively feel that the query speed has improved qualitatively. It’s even more surprising to see the query plan

  • Nreturned: matches to 1 document
  • Executiontimemillis: 0, ER..
  • Totalkeysexamined: a total of 1 index was retrieved
  • Total documents examined: a total of 1 document was retrieved
  • Executionstages.stage: fetch, retrieve the specified document according to the index, such as index seek of SQL
 "executionStats" : {
                "executionSuccess" : true,
                "nReturned" : 1,
                "executionTimeMillis" : 0,
                "totalKeysExamined" : 1,
                "totalDocsExamined" : 1,
                "executionStages" : {
                        "stage" : "FETCH"

 

Only the simplest single field index is introduced here. Mongodb also has many indexes

  • Composite indexes: index multiple fields
  • Multikey indexes: a field is indexed by multiple values, usually an array
  • Text indexes: for text retrieval, different weights can be set for fields
  • Wild card indexes: you can index all / specified values of an object
  • more

Please indicate the source of forwarding: https://www.cnblogs.com/WilsonPan/p/12704474.html

 

 

Recommended Today

What is “hybrid cloud”?

In this paper, we define the concept of “hybrid cloud”, explain four different cloud deployment models of hybrid cloud, and deeply analyze the industrial trend of hybrid cloud through a series of data and charts. 01 introduction Hybrid cloud is a computing environment that integrates multiple platforms and data centers. Generally speaking, hybrid cloud is […]