|
準(zhǔn)備工作 準(zhǔn)備2個(gè)集合的數(shù)據(jù)，后面視圖和索引都會(huì)用到 var orders = new Array(); var shipping = new Array(); var addresses = ["廣西省玉林市", "湖南省岳陽(yáng)市", "湖北省荊州市", "甘肅省蘭州市", "吉林省松原市", "江西省景德鎮(zhèn)", "遼寧省沈陽(yáng)市", "福建省廈門市", "廣東省廣州市", "北京市朝陽(yáng)區(qū)"]; 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);
視圖 概述
Mongodb的視圖基本上和SQL的視圖一樣
1. 創(chuàng)建視圖 有兩種方法創(chuàng)建視圖 db.createCollection( "<viewName>", { "viewOn" : "<source>", "pipeline" : [<pipeline>], "collation" : { <collation> } } ) db.createView( "<viewName>", "<source>", [<pipeline>], { "collation" : { <collation> } } ) 一般使用db.createView viewName : 必須，視圖名稱 source : 必須，數(shù)據(jù)源，集合/視圖 [<pipeline>] : 可選，一組管道，可見(jiàn)管道是Mongodb比較重要的一環(huán)
1.1 單個(gè)集合創(chuàng)建視圖 假設(shè)現(xiàn)在查看當(dāng)天最高的10筆訂單視圖，例如后臺(tái)某個(gè)地方需要實(shí)時(shí)顯示金額最高的訂單 db.createView( "orderInfo", //視圖名稱 "order", //數(shù)據(jù)源 [ //篩選符合條件的訂單，大于當(dāng)天，這里要注意時(shí)區(qū) { $match: { "orderTime": { $gte: ISODate("2020-04-13T16:00:00.000Z") } } }, //按金額倒序 { $sort: { "price": -1 } }, //限制10個(gè)文檔 { $limit: 10 }, //選擇要顯示的字段 //0: 排除字段，若字段上使用（_id除外），就不能有其他包含字段 //1: 包含字段 { $project: { _id: 0, orderNo: 1, price: 1, orderTime: 1 } } ] ) 然后就可以直接使用orderInfo這個(gè)視圖查詢數(shù)據(jù) db.orderInfo.find({})
返回結(jié)果 { "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 多個(gè)集合創(chuàng)建視圖 其實(shí)跟單個(gè)是集合是一樣，只是多了$lookup連接操作符，視圖根據(jù)管道最終結(jié)果顯示，所以可以關(guān)聯(lián)多個(gè)集合（若出現(xiàn)這種情況就要考慮集合設(shè)計(jì)是否合理，mongodb本來(lái)就是文檔型數(shù)據(jù)庫(kù)） db.orderDetail.drop() db.createView( "orderDetail", "order", [ { $lookup: { from: "shipping", localField: "orderNo", foreignField: "orderNo", as: "shipping" } }, { $project: { "orderNo": 1, "price": 1, "shipping.address": 1 } } ] ) 查詢視圖，得到如下結(jié)果 { "_id" : ObjectId("5e95af8c4ef6faf974b4a6c3"), "orderNo" : "1000039782", "price" : 85.94, "shipping" : [ { "address" : "北京市朝陽(yáng)區(qū)" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6c4"), "orderNo" : "1000102128", "price" : 29.04, "shipping" : [ { "address" : "吉林省松原市" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6c5"), "orderNo" : "1000214514", "price" : 90.69, "shipping" : [ { "address" : "湖南省岳陽(yáng)市" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6c6"), "orderNo" : "1000337987", "price" : 75.05, "shipping" : [ { "address" : "遼寧省沈陽(yáng)市" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6c7"), "orderNo" : "1000468969", "price" : 76.84, "shipping" : [ { "address" : "江西省景德鎮(zhèn)" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6c8"), "orderNo" : "1000572219", "price" : 60.25, "shipping" : [ { "address" : "江西省景德鎮(zhèn)" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6c9"), "orderNo" : "1000611743", "price" : 19.14, "shipping" : [ { "address" : "廣東省廣州市" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6ca"), "orderNo" : "1000773917", "price" : 31.5, "shipping" : [ { "address" : "北京市朝陽(yáng)區(qū)" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6cb"), "orderNo" : "1000879146", "price" : 76.16, "shipping" : [ { "address" : "吉林省松原市" } ] }
{ "_id" : ObjectId("5e95af8c4ef6faf974b4a6cc"), "orderNo" : "1000945977", "price" : 93.98, "shipping" : [ { "address" : "遼寧省沈陽(yáng)市" } ] }
可以看到，mongodb不是像SQL那樣把連接的表當(dāng)成列列出，而是把連接結(jié)果放在數(shù)組里面，這很符合Mongodb文檔型結(jié)構(gòu)。
2. 修改視圖 假設(shè)現(xiàn)在需要增加一個(gè)數(shù)量的字段 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 } } ] }) 當(dāng)然，也可以刪除視圖，重新用db.createView()創(chuàng)建視圖
3. 刪除視圖 db.orderInfo.drop();
索引 概述
索引能提供高效的查詢，沒(méi)有索引的查詢，mongole執(zhí)行集合掃描，相當(dāng)于SQL SERVER的全表掃描，掃描每一個(gè)文檔。 數(shù)據(jù)存在存儲(chǔ)介質(zhì)上，大多數(shù)情況是為了查詢，查詢的快慢直接影響用戶體驗(yàn)，mongodb索引也是空間換時(shí)間，添加索引，CUD操作都會(huì)導(dǎo)致索引重新生成，影響速度。
1. 準(zhǔn)備工作 1.1 準(zhǔn)備200W條數(shù)據(jù) 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))) }; } //不需寫(xiě)入確認(rèn) db.order.insert(orders, { writeConcern: { w: 0 } }); }
1.2 mongodb的查詢計(jì)劃 db.collection.explain().<method(...)>
一般使用執(zhí)行統(tǒng)計(jì)模式，例如 db.order.explain("executionStats").find({orderNo:1000000}) 返回的executionStats對(duì)象字段說(shuō)明 部分字段說(shuō)明
查看未加索引前查詢計(jì)劃 db.order.explain("executionStats").find({orderNo:1000000}) 截取部分返回結(jié)果,可以看出
"executionStats" : { "executionSuccess" : true, "nReturned" : 1, "executionTimeMillis" : 1437, "totalKeysExamined" : 0, "totalDocsExamined" : 2000000, "executionStages" : { "stage" : "COLLSCAN",
1.3 查看當(dāng)前集合統(tǒng)計(jì)信息 db.order.stats() 截取部分信息，可以看出現(xiàn)在存儲(chǔ)文件大小大概為72M { "ns" : "mongo.order", "size" : 204000000, "count" : 2000000, "avgObjSize" : 102, "storageSize" : 74473472,
2. 創(chuàng)建索引 db.order.createIndex({ orderNo: 1 }, { name: "ix_orderNo" })
索引名稱不是必須，若不指定，按 字段名稱_排序類型組合自動(dòng)生成，索引名稱一旦創(chuàng)建不能修改，若要修改，只能刪除索引重新生成索引，建議還是建索引的時(shí)候就把索引名稱設(shè)置好。
2.1 執(zhí)行查詢計(jì)劃 db.order.explain("executionStats").find({orderNo:1000000}) 截取部分結(jié)果，直觀就可以感覺(jué)查詢速度有了質(zhì)的提升，再看查詢計(jì)劃更加驚訝
"executionStats" : { "executionSuccess" : true, "nReturned" : 1, "executionTimeMillis" : 0, "totalKeysExamined" : 1, "totalDocsExamined" : 1, "executionStages" : { "stage" : "FETCH"
這里只介紹最簡(jiǎn)單的單個(gè)字段索引，mongodb還有很多索引
轉(zhuǎn)發(fā)請(qǐng)標(biāo)明出處：https://www.cnblogs.com/WilsonPan/p/12704474.html
|
|
|
