首页 › MongoDB论坛 › 005.MongoDB索引及聚合

口袋里的糖糖 發表於 2019-6-5 17:35:00

005.MongoDB索引及聚合

<h2 align="left">一 MongoDB 索引</h2>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">索引通常能够极大的提高查询的效率，如果没有索引，MongoDB在读取数据时必须扫描集合中的每个文件并选取那些符合查询条件的记录。</span></div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">这种扫描全集合的查询效率是非常低的，特别在处理大量的数据时，查询可以要花费几十秒甚至几分钟，这对网站的性能是非常致命的。</span></div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">索引是特殊的数据结构，索引存储在一个易于遍历读取的数据集合中，索引是对数据库表中一列或多列的值进行排序的一种结构。</span></div>
<h3 align="left">1.1 createIndex() 方法</h3>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">MongoDB使用 createIndex() 方法来创建索引。</span></div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">语法格式：</span></div>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> &gt; db.collection.createIndex(keys, options)</pre>
</div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">参数说明：</span></div>
<ul>
<li>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">Key ：key值为你要创建的索引字段；</span></div>
</li>
</ul>
<ul>
<li>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">options：options为1 表示按升序创建索引，为-1表示按降序来创建索引。</span></div>
</li>
</ul>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> # mongo --host 172.24.9.225 --port 27017 -u useradmin -p useradmin
<span style="color: rgba(0, 128, 128, 1)">2</span> &gt; use mydb
<span style="color: rgba(0, 128, 128, 1)">3</span> &gt; db.age01.createIndex({age: 1})
<span style="color: rgba(0, 128, 128, 1)">4</span> {
<span style="color: rgba(0, 128, 128, 1)">5</span>         "createdCollectionAutomatically" : false,
<span style="color: rgba(0, 128, 128, 1)">6</span>         "numIndexesBefore" : 1,
<span style="color: rgba(0, 128, 128, 1)">7</span>         "numIndexesAfter" : 2,
<span style="color: rgba(0, 128, 128, 1)">8</span>         "ok" : 1
<span style="color: rgba(0, 128, 128, 1)">9</span> }</pre>
</div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left">&nbsp;</div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">createIndex() 方法中你也可以设置使用多个字段创建索引（关系型数据库中称作复合索引）。</span></div>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> &gt; db.age01.createIndex({age: 1, tel: -1})
<span style="color: rgba(0, 128, 128, 1)">2</span> {
<span style="color: rgba(0, 128, 128, 1)">3</span>         "createdCollectionAutomatically" : false,
<span style="color: rgba(0, 128, 128, 1)">4</span>         "numIndexesBefore" : 2,
<span style="color: rgba(0, 128, 128, 1)">5</span>         "numIndexesAfter" : 3,
<span style="color: rgba(0, 128, 128, 1)">6</span>         "ok" : 1
<span style="color: rgba(0, 128, 128, 1)">7</span> }</pre>
</div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left">&nbsp;</div>
<h3 align="left">1.2 createIndex() 可选参数</h3>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">可选参数列表如下：</span></div>
<div style="line-height: normal; overflow: auto; -ms-word-break: normal">
<table style="overflow: visible; display: table; white-space: nowrap; border-collapse: collapse; table-layout: fixed; max-width: none; border-spacing: 2px 2px"><colgroup><col width="147"><col width="104"><col width="348"></colgroup>
<tbody>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-0-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">Parameter</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-0-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">Type</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-0-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">Description</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-1-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">background</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-1-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">Boolean</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="4735-1559016029386-cell-1-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">建索引过程会阻塞其它数据库操作，background可指定以后台方式创建索引，即增加 "background" 可选参数。 "background" 默认值为false。</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-2-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">unique</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-2-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">Boolean</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="4735-1559016029386-cell-2-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">建立的索引是否唯一。指定为true创建唯一索引。默认值为false.</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-3-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">name</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-3-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">string</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="4735-1559016029386-cell-3-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">索引的名称。如果未指定，MongoDB的通过连接索引的字段名和排序顺序生成一个索引名称。</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-4-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">sparse</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-4-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">Boolean</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="4735-1559016029386-cell-4-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">对文档中不存在的字段数据不启用索引；这个参数需要特别注意，如果设置为true的话，在索引字段中不会查询出不包含对应字段的文档.。默认值为 false.</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-5-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">expireAfterSeconds</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-5-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">integer</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="4735-1559016029386-cell-5-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">指定一个以秒为单位的数值，完成 TTL设定，设定集合的生存时间。</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-6-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">v</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-6-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">index version</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="4735-1559016029386-cell-6-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">索引的版本号。默认的索引版本取决于mongod创建索引时运行的版本。</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-7-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">weights</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-7-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">document</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="4735-1559016029386-cell-7-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">索引权重值，数值在 1 到 99,999 之间，表示该索引相对于其他索引字段的得分权重。</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-8-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">default_language</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-8-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">string</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="4735-1559016029386-cell-8-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">对于文本索引，该参数决定了停用词及词干和词器的规则的列表。 默认为英语</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-9-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">language_override</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="4735-1559016029386-cell-9-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">string</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="4735-1559016029386-cell-9-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">对于文本索引，该参数指定了包含在文档中的字段名，语言覆盖默认的language，默认值为 language.</span></div>
</td>
</tr>
</tbody>
</table>
</div>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> &gt; db.age01.createIndex({age: 1, tel: -1},{background: 'true'})
<span style="color: rgba(0, 128, 128, 1)">2</span> {
<span style="color: rgba(0, 128, 128, 1)">3</span>         "numIndexesBefore" : 3,
<span style="color: rgba(0, 128, 128, 1)">4</span>         "numIndexesAfter" : 3,
<span style="color: rgba(0, 128, 128, 1)">5</span>         "note" : "all indexes already exist",
<span style="color: rgba(0, 128, 128, 1)">6</span>         "ok" : 1
<span style="color: rgba(0, 128, 128, 1)">7</span> }</pre>
</div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left">&nbsp;</div>
<h3 align="left">1.3 查看索引</h3>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> &gt; db.age01.getIndexes()</pre>
</div>
<h3 align="left">1.4 查看集合索引大小</h3>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> &gt; db.age01.totalIndexSize()</pre>
</div>
<h3 align="left">1.5 删除指定集合</h3>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> &gt; db.age01.dropIndex('age_1_tel_-1')
<span style="color: rgba(0, 128, 128, 1)">2</span> { "nIndexesWas" : 3, "ok" : 1 }</pre>
</div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left">&nbsp;</div>
<h3 align="left">1.6 删除所有索引</h3>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> &gt; db.age01.dropIndexes()
<span style="color: rgba(0, 128, 128, 1)">2</span> {
<span style="color: rgba(0, 128, 128, 1)">3</span>         "nIndexesWas" : 2,
<span style="color: rgba(0, 128, 128, 1)">4</span>         "msg" : "non-_id indexes dropped for collection",
<span style="color: rgba(0, 128, 128, 1)">5</span>         "ok" : 1
<span style="color: rgba(0, 128, 128, 1)">6</span> }</pre>
</div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left">&nbsp;</div>
<h2 align="left">二 MongoDB聚合</h2>
<h3 align="left">2.1 aggregate() 方法</h3>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">MongoDB中聚合(aggregate)主要用于处理数据(诸如统计平均值,求和等)，并返回计算后的数据结果。有点类似sql语句中的 count(*)。</span></div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">MongoDB中聚合的方法使用aggregate()。</span></div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">语法格式：</span></div>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> &gt; db.COLLECTION_NAME.aggregate(AGGREGATE_OPERATION)
<span style="color: rgba(0, 128, 128, 1)">2</span> # mongo --host 172.24.9.225 --port 27017 -u useradmin -p useradmin
<span style="color: rgba(0, 128, 128, 1)">3</span> &gt; use mydb
<span style="color: rgba(0, 128, 128, 1)">4</span> &gt; db.age01.aggregate([{$group: {_id: "$tel", tel: {$sum: 1}}}])
<span style="color: rgba(0, 128, 128, 1)">5</span> { "_id" : "123456784", "tel" : 1 }
<span style="color: rgba(0, 128, 128, 1)">6</span> { "_id" : "188888888", "tel" : 2 }
<span style="color: rgba(0, 128, 128, 1)">7</span> { "_id" : "155555555", "tel" : 1 }</pre>
</div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left">&nbsp;</div>
<div style="line-height: 1.6666; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="color: rgba(255, 0, 0, 1); font-family: 微软雅黑; font-size: small"><strong>提示：以上操作为统计所有各个tel的个数，类似select tel count(*) from age01 group by tel。</strong></span></div>
<h3 align="left">2.2 聚合表达式</h3>
<div style="line-height: normal; overflow: auto; -ms-word-break: normal">
<table style="overflow: visible; display: table; white-space: nowrap; border-collapse: collapse; table-layout: fixed; max-width: none; border-spacing: 2px 2px"><colgroup><col width="73"><col width="283"><col width="479"></colgroup>
<tbody>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-0-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">表达式</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-0-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">描述</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-0-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">实例</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-1-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">$sum</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-1-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">计算总和。</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="8948-1559022516260-cell-1-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$sum : "$likes"}}}])</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-2-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">$avg</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-2-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">计算平均值</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="8948-1559022516260-cell-2-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$avg : "$likes"}}}])</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-3-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">$min</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-3-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">获取集合中所有文档对应值得最小值。</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="8948-1559022516260-cell-3-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$min : "$likes"}}}])</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-4-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">$max</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-4-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">获取集合中所有文档对应值得最大值。</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="8948-1559022516260-cell-4-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$max : "$likes"}}}])</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-5-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">$push</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-5-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">在结果文档中插入值到一个数组中。</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="8948-1559022516260-cell-5-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">db.mycol.aggregate([{$group : {_id : "$by_user", url : {$push: "$url"}}}])</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-6-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">$addToSet</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-6-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">在结果文档中插入值到一个数组中，但不创建副本。</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="8948-1559022516260-cell-6-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">db.mycol.aggregate([{$group : {_id : "$by_user", url : {$addToSet : "$url"}}}])</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-7-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">$first</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-7-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">根据资源文档的排序获取第一个文档数据。</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="8948-1559022516260-cell-7-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">db.mycol.aggregate([{$group : {_id : "$by_user", first_url : {$first : "$url"}}}])</span></div>
</td>
</tr>
<tr style="height: 40px">
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-8-0">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">$last</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="center" data-cell-id="8948-1559022516260-cell-8-1">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">根据资源文档的排序获取最后一个文档数据</span></div>
</td>
<td style="padding: 1px; border: 1px solid rgba(167, 167, 167, 1); border-image: none; overflow: hidden; vertical-align: middle; white-space: pre-wrap; -ms-word-wrap: break-word" align="left" data-cell-id="8948-1559022516260-cell-8-2">
<div class="table-cell-line"><span style="font-family: 微软雅黑; font-size: small">db.mycol.aggregate([{$group : {_id : "$by_user", last_url : {$last : "$url"}}}])</span></div>
</td>
</tr>
</tbody>
</table>
</div>
<h2 align="left">三 管道</h2>
<h3 align="left">3.1 管道的概念</h3>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">管道在Unix和Linux中一般用于将当前命令的输出结果作为下一个命令的参数。</span></div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">MongoDB的聚合管道将MongoDB文档在一个管道处理完毕后将结果传递给下一个管道处理。管道操作是可以重复的。</span></div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">表达式：处理输入文档并输出。表达式是无状态的，只能用于计算当前聚合管道的文档，不能处理其它的文档。</span></div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">聚合框架常用操作：</span></div>
<div style="line-height: normal; -ms-word-break: normal">
<ul style="margin: 0; padding-left: 30pt">
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$project：修改输入文档的结构。可以用来重命名、增加或删除域，也可以用于创建计算结果以及嵌套文档。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$match：用于过滤数据，只输出符合条件的文档。$match使用MongoDB的标准查询操作。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$limit：用来限制MongoDB聚合管道返回的文档数。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$skip：在聚合管道中跳过指定数量的文档，并返回余下的文档。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$unwind：将文档中的某一个数组类型字段拆分成多条，每条包含数组中的一个值。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$group：将集合中的文档分组，可用于统计结果。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$sort：将输入文档排序后输出。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$geoNear：输出接近某一地理位置的有序文档。</span></li>
</ul>
</div>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> # mongo --host 172.24.9.225 --port 27017 -u useradmin -p useradmin
<span style="color: rgba(0, 128, 128, 1)">2</span> &gt; use mydb
<span style="color: rgba(0, 128, 128, 1)">3</span> &gt; db.age01.aggregate({$project: {name: 1, tel: 1,}}).pretty()</pre>
</div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left">&nbsp;</div>
<div style="line-height: normal; float: none; -ms-word-break: normal" align="left"><img style="border: 0 currentColor; border-image: none; margin-right: auto; margin-left: auto; float: none; display: block; background-image: none" title="001" src="https://img2018.cnblogs.com/blog/680719/201906/680719-20190605173432539-1041967333.png" alt="001" width="613" height="379" border="0"></div>
<div style="line-height: 1.6666; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="color: rgba(255, 0, 0, 1); font-family: 微软雅黑; font-size: small"><strong>提示：_id默认为输出，可通过_id: 0关闭_id的输出。</strong></span></div>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> &gt; db.age01.aggregate([{$match : {age: {$gt: '10', $lt: '20'}}},{$group: {_id: null, count: {$sum: 1}}}])
<span style="color: rgba(0, 128, 128, 1)">2</span> { "_id" : null, "count" : 2 }                        #$match过滤出符合条件的数据，然后$group进行再次处理。</pre>
</div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left">&nbsp;</div>
<h3 align="left">3.2 时间聚合</h3>
<div class="cnblogs_code">
<pre><span style="color: rgba(0, 128, 128, 1)">1</span> db.getCollection('m_msg_tb').aggregate(
<span style="color: rgba(0, 128, 128, 1)">2</span> [
<span style="color: rgba(0, 128, 128, 1)">3</span>   {$match:{m_id:10001,mark_time:{$gt:new Date(2017,8,0)}}},
<span style="color: rgba(0, 128, 128, 1)">4</span>   {$group: {
<span style="color: rgba(0, 128, 128, 1)">5</span>      _id: {$dayOfMonth:'$mark_time'},
<span style="color: rgba(0, 128, 128, 1)">6</span>         pv: {$sum: 1}
<span style="color: rgba(0, 128, 128, 1)">7</span>         }
<span style="color: rgba(0, 128, 128, 1)">8</span>   },
<span style="color: rgba(0, 128, 128, 1)">9</span>   {$sort: {"_id": 1}}
<span style="color: rgba(0, 128, 128, 1)"> 10</span> ])</pre>
</div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left">&nbsp;</div>
<div style="line-height: 1.75; white-space: pre-wrap; -ms-word-break: normal" align="left"><span style="font-family: 微软雅黑; font-size: small">时间关键字如下：</span></div>
<div style="line-height: normal; -ms-word-break: normal">
<ul style="margin: 0; padding-left: 30pt">
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$dayOfYear: 返回该日期是这一年的第几天（全年 366 天）。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$dayOfMonth: 返回该日期是这一个月的第几天（1到31）。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$dayOfWeek: 返回的是这个周的星期几（1：星期日，7：星期六）。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$year: 返回该日期的年份部分。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$month： 返回该日期的月份部分（ 1 到 12）。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$week： 返回该日期是所在年的第几个星期（ 0 到 53）。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$hour： 返回该日期的小时部分。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$minute: 返回该日期的分钟部分。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$second: 返回该日期的秒部分（以0到59之间的数字形式返回日期的第二部分，但可以是60来计算闰秒）。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span style="font-family: 微软雅黑; font-size: small">$millisecond：返回该日期的毫秒部分（ 0 到 999）。</span></li>
<li style="text-align: left; line-height: 1.75; list-style-type: disc; list-style-position: inside; white-space: pre-wrap; background-color: rgba(0, 0, 0, 0)"><span><span style="font-family: 微软雅黑; font-size: small">$dateToString： { $dateToString: { format: , date: } }。</span></span></li>
</ul>
</div>

</div>
<div id="MySignature" role="contentinfo">
    <div style="background: #f7acbc; color: #0; font-size: small">
<p>
作者：木二
</p>
<p>
出处：http://www.cnblogs.com/itzgr/
</p>
<p>
关于作者：云计算、虚拟化，Linux，多多交流！
</p>
<p>
本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文链接!如有其他问题，可邮件（xhy@itzgr.com）咨询。
</p>
</div><br><br>
来源：https://www.cnblogs.com/itzgr/p/10980919.html

查看完整版本: 005.MongoDB索引及聚合