【Mongodb】视图 && 索引

宁静的世界 發表於 2020-4-15 13:20:00

准备工作
<div class="ws-content">
准备2个集合的数据，后面视图和索引都会用到 1个订单集合，一个收款信息集合
<div class="cnblogs_code">
<pre>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 = { 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;
shipping = { orderNo: orderNo, address: address, recipienter: "Wilson", province: address.substr(0, 3), city: address.substr(3, 3) }
}
db.order.insert(orders);
db.shipping.insert(shipping);</pre>
</div>
<img src="https://img2020.cnblogs.com/blog/413875/202004/413875-20200415125003256-1419603784.png" alt="">
 
</div>
视图
<div class="ws-content">
概述
<blockquote>
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.
</blockquote>
Mongodb的视图基本上和SQL的视图一样
<ul>
<li>数据源（集合或视图）</li>
<li>提供查询</li>
<li>不实际存储硬盘</li>
<li>客户端发起请求查询时计算而得</li>
</ul>
1. 创建视图
有两种方法创建视图
<div class="cnblogs_code">
<pre>db.createCollection(
"<viewName>",
{
"viewOn" : "<source>",
"pipeline" : [<pipeline>],
"collation" : { <collation> }
}
)</pre>
</div>
<div class="cnblogs_code">
<pre>db.createView(
"<viewName>",
"<source>",
[<pipeline>],
{
"collation" : { <collation> }
}
)</pre>
</div>
<div class="_2Uzcx_">
一般使用db.createView
viewName : 必须，视图名称
source : 必须，数据源，集合/视图
[<pipeline>] : 可选，一组管道，可见管道是Mongodb比较重要的一环
 
</div>
1.1 单个集合创建视图
假设现在查看当天最高的10笔订单视图，例如后台某个地方需要实时显示金额最高的订单
<div class="cnblogs_code">
<pre>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 } }
]
)</pre>
</div>
然后就可以直接使用orderInfo这个视图查询数据
<div class="cnblogs_code">
<pre>db.orderInfo.find({})</pre>
</div>
返回结果
<div class="cnblogs_code">
<pre>{ "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") }</pre>
</div>
<div class="_2Uzcx_"> </div>
1.2 多个集合创建视图
其实跟单个是集合是一样，只是多了$lookup连接操作符，视图根据管道最终结果显示，所以可以关联多个集合（若出现这种情况就要考虑集合设计是否合理，mongodb本来就是文档型数据库）
<div class="cnblogs_code">
<pre>db.orderDetail.drop()
db.createView(
"orderDetail",
"order",
[
 { $lookup: { from: "shipping", localField: "orderNo", foreignField: "orderNo", as: "shipping" } },
 { $project: { "orderNo": 1, "price": 1, "shipping.address": 1 } }
]
)</pre>
</div>
<div class="_2Uzcx_">
查询视图，得到如下结果
<div class="cnblogs_code">
<pre>{ "_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" : "辽宁省沈阳市" } ] }</pre>
</div>
可以看到，mongodb不是像SQL那样把连接的表当成列列出，而是把连接结果放在数组里面，这很符合Mongodb文档型结构。
 
</div>
2. 修改视图
假设现在需要增加一个数量的字段
<div class="cnblogs_code">
<pre>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 } }
]
})</pre>
</div>
当然，也可以删除视图，重新用db.createView()创建视图
 
3. 删除视图
<div class="cnblogs_code">
<pre>db.orderInfo.drop();</pre>
</div>
 
</div>
索引
<div class="ws-content">
概述
<blockquote>
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.
</blockquote>
索引能提供高效的查询，没有索引的查询，mongole执行集合扫描，相当于SQL SERVER的全表扫描，扫描每一个文档。
数据存在存储介质上，大多数情况是为了查询，查询的快慢直接影响用户体验，mongodb索引也是空间换时间，添加索引，CUD操作都会导致索引重新生成，影响速度。
 
1. 准备工作
1.1 准备200W条数据
<div class="cnblogs_code">
<pre>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 = { 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 } });
}</pre>
</div>
 
1.2 mongodb的查询计划
<div class="cnblogs_code">
<pre>db.collection.explain().<method(...)></pre>
</div>
 
一般使用执行统计模式，例如
<div class="cnblogs_code">
<pre>db.order.explain("executionStats").find({orderNo:1000000})</pre>
</div>
返回的executionStats对象字段说明
部分字段说明
<div class="_2Uzcx_">
<table>
<thead>
<tr><th>字段</th><th>说明</th></tr>
</thead>
<tbody>
<tr>
<td>executionSuccess</td>
<td>是否执行成功</td>
</tr>
<tr>
<td>nReturned</td>
<td>返回匹配文档数量</td>
</tr>
<tr>
<td>executionTimeMillis</td>
<td>执行时间，单位：毫秒</td>
</tr>
<tr>
<td>totalKeysExamined</td>
<td>索引检索数目</td>
</tr>
<tr>
<td>totalDocsExamined</td>
<td>文档检索数目</td>
</tr>
</tbody>
</table>
</div>
查看未加索引前查询计划
<div class="cnblogs_code">
<pre>db.order.explain("executionStats").find({orderNo:1000000})</pre>
</div>
<div class="_2Uzcx_">
截取部分返回结果,可以看出
<ul>
<li>executionTimeMillis : 用时1437毫秒</li>
<li>totalDocsExamined : 扫描文档200W</li>
<li>executionStages.stage : 集合扫描</li>
</ul>
</div>
<div class="_2Uzcx_">
<div class="cnblogs_code">
<pre>"executionStats" : {
"executionSuccess" : true,
"nReturned" : 1,
"executionTimeMillis" : 1437,
"totalKeysExamined" : 0,
"totalDocsExamined" : 2000000,
"executionStages" : {
 "stage" : "COLLSCAN",</pre>
</div>
 
1.3 查看当前集合统计信息
<div class="cnblogs_code">
<pre>db.order.stats()</pre>
</div>
<div class="_2Uzcx_">
截取部分信息，可以看出现在存储文件大小大概为72M
<div class="cnblogs_code">
<pre>{
 "ns" : "mongo.order",
 "size" : 204000000,
 "count" : 2000000,
 "avgObjSize" : 102,
 "storageSize" : 74473472,</pre>
</div>
 
</div>
</div>
2. 创建索引
<div class="cnblogs_code">
<pre>db.order.createIndex({ orderNo: 1 }, { name: "ix_orderNo" })</pre>
</div>
索引名称不是必须，若不指定，按字段名称_排序类型组合自动生成，索引名称一旦创建不能修改，若要修改，只能删除索引重新生成索引，建议还是建索引的时候就把索引名称设置好。
 
2.1 执行查询计划
<div class="cnblogs_code">
<pre>db.order.explain("executionStats").find({orderNo:1000000})</pre>
</div>
截取部分结果，直观就可以感觉查询速度有了质的提升，再看查询计划更加惊讶
<ul>
<li>nReturned : 匹配到1个文档</li>
<li>executionTimeMillis : 0，呃。。</li>
<li>totalKeysExamined : 总共检索了1个索引</li>
<li>totalDocsExamined : 总共检索了1个文档</li>
<li>executionStages.stage : FETCH，根据索引去检索指定文档，像SQL的Index Seek</li>
</ul>
<div class="cnblogs_code">
<pre> "executionStats" : {
 "executionSuccess" : true,
 "nReturned" : 1,
 "executionTimeMillis" : 0,
 "totalKeysExamined" : 1,
 "totalDocsExamined" : 1,
 "executionStages" : {
 "stage" : "FETCH"</pre>
</div>
 
这里只介绍最简单的单个字段索引，mongodb还有很多索引
<ul>
<li>复合索引（Compound Indexes）：对多个字段做索引</li>
<li>多键索引（Multikey Indexes）：一个字段多个值做索引，通常是数组</li>
<li>全文索引（Text Indexes）：对文本检索，可以对字段设置不同权重</li>
<li>通配符索引（Wildcard Indexes）：可以将对象的所有/指定的值做索引</li>
<li>更多</li>
</ul>
</div>

<div class="ws-content">
 
参考文章
<hr>
Views — MongoDB Manual 
Indexes — MongoDB Manual

</div>
<div class="ws-copyright">
转发请标明出处：https://www.cnblogs.com/WilsonPan/p/12704474.html

</div>
<div class="ws-content"> </div>
 

</audio> 
来源：https://www.cnblogs.com/WilsonPan/p/12704474.html

頁: [1]

琼殿技术论坛's Archiver

【Mongodb】视图 && 索引