对于 collection 中数据只有部分记录包含的字段,相对于普通索引在没有相应字段会以 null 值来代替,MongoDB 3.2 版本后可以使用 partial index , 来降低索引存储空间, 比 sparse index 功能更多更灵活。

$ cat ~/createindex.js
var db=db.getSisterDB("xdb");
db.T1.createIndex(
   { roleIds: 1 ,state:1},
   { partialFilterExpression: { roleIds: { $exists: true } } },
   { background:true}
);

log:

2019-03-21T23:40:01.087+0800 I NETWORK  [conn173656] received client metadata from 127.0.0.1:47414 conn173656: { application: { name: "MongoDB Shell" }, driver: { name: "MongoDB Internal Cl
ient", version: "3.4.2" }, os: { type: "Linux", name: "CentOS release 6.6 (Final)", architecture: "x86_64", version: "Kernel 2.6.32-504.30.3.el6.x86_64" } }
2019-03-21T23:40:01.103+0800 I INDEX    [conn173656] build index on: xdb.T1 properties: { v: 1, key: { roleIds: 1.0, state: 1.0 }, name: "roleIds_1_state_1", ns: "xdb.T1", partialFilterExpression: { roleIds: { $exists: true } } }
2019-03-21T23:40:01.103+0800 I INDEX    [conn173656]      building index using bulk method; build may temporarily use up to 500 megabytes of RAM
2019-03-21T23:40:04.000+0800 I -        [conn173656]   Index Build: 2253700/258607871 0%
2019-03-21T23:40:07.000+0800 I -        [conn173656]   Index Build: 4479000/258607871 1%
2019-03-21T23:40:10.000+0800 I -        [conn173656]   Index Build: 6702300/258607871 2%
2019-03-21T23:40:13.000+0800 I -        [conn173656]   Index Build: 9005100/258607871 3%
2019-03-21T23:40:16.000+0800 I -        [conn173656]   Index Build: 11329000/258607871 4%
2019-03-21T23:40:19.000+0800 I -        [conn173656]   Index Build: 13404500/258607871 5%
2019-03-21T23:40:22.000+0800 I -        [conn173656]   Index Build: 15425600/258607871 5%
2019-03-21T23:40:25.000+0800 I -        [conn173656]   Index Build: 17590300/258607871 6%
2019-03-21T23:40:28.000+0800 I -        [conn173656]   Index Build: 19548500/258607871 7%
2019-03-21T23:40:31.000+0800 I -        [conn173656]   Index Build: 21543600/258607871 8%
2019-03-21T23:40:34.000+0800 I -        [conn173656]   Index Build: 23499400/258607871 9%
2019-03-21T23:40:37.001+0800 I -        [conn173656]   Index Build: 25005300/258607871 9%
2019-03-21T23:40:40.000+0800 I -        [conn173656]   Index Build: 26616100/258607871 10%
2019-03-21T23:40:43.000+0800 I -        [conn173656]   Index Build: 28663200/258607871 11%
2019-03-21T23:40:46.000+0800 I -        [conn173656]   Index Build: 30730400/258607871 11%
2019-03-21T23:40:49.000+0800 I -        [conn173656]   Index Build: 32255600/258607871 12%
2019-03-21T23:40:52.000+0800 I -        [conn173656]   Index Build: 34027700/258607871 13%
2019-03-21T23:40:55.000+0800 I -        [conn173656]   Index Build: 35713700/258607871 13%
2019-03-21T23:40:58.000+0800 I -        [conn173656]   Index Build: 37766400/258607871 14%
2019-03-21T23:41:01.000+0800 I -        [conn173656]   Index Build: 39181600/258607871 15%
2019-03-21T23:41:04.000+0800 I -        [conn173656]   Index Build: 40742600/258607871 15%
2019-03-21T23:41:07.000+0800 I -        [conn173656]   Index Build: 42544600/258607871 16%
2019-03-21T23:41:10.000+0800 I -        [conn173656]   Index Build: 44678700/258607871 17%
2019-03-21T23:41:13.000+0800 I -        [conn173656]   Index Build: 46542500/258607871 17%
2019-03-21T23:41:16.000+0800 I -        [conn173656]   Index Build: 48355300/258607871 18%
2019-03-21T23:41:19.000+0800 I -        [conn173656]   Index Build: 50467900/258607871 19%
2019-03-21T23:41:22.000+0800 I -        [conn173656]   Index Build: 52483200/258607871 20%
2019-03-21T23:41:25.000+0800 I -        [conn173656]   Index Build: 54556100/258607871 21%
2019-03-21T23:41:28.000+0800 I -        [conn173656]   Index Build: 56541100/258607871 21%
2019-03-21T23:41:31.000+0800 I -        [conn173656]   Index Build: 58228500/258607871 22%
2019-03-21T23:41:34.000+0800 I -        [conn173656]   Index Build: 60427100/258607871 23%
2019-03-21T23:41:37.000+0800 I -        [conn173656]   Index Build: 61901800/258607871 23%
2019-03-21T23:41:40.000+0800 I -        [conn173656]   Index Build: 63391800/258607871 24%
2019-03-21T23:41:43.000+0800 I -        [conn173656]   Index Build: 65153700/258607871 25%
2019-03-21T23:41:46.000+0800 I -        [conn173656]   Index Build: 67061100/258607871 25%
2019-03-21T23:41:49.000+0800 I -        [conn173656]   Index Build: 69113800/258607871 26%
2019-03-21T23:41:52.000+0800 I -        [conn173656]   Index Build: 70944300/258607871 27%
2019-03-21T23:41:55.000+0800 I -        [conn173656]   Index Build: 72858900/258607871 28%
2019-03-21T23:41:58.000+0800 I -        [conn173656]   Index Build: 74973800/258607871 28%
2019-03-21T23:42:01.000+0800 I -        [conn173656]   Index Build: 76631700/258607871 29%
2019-03-21T23:42:04.000+0800 I -        [conn173656]   Index Build: 78191900/258607871 30%
2019-03-21T23:42:07.000+0800 I -        [conn173656]   Index Build: 79659100/258607871 30%
2019-03-21T23:42:10.000+0800 I -        [conn173656]   Index Build: 81291700/258607871 31%
2019-03-21T23:42:13.000+0800 I -        [conn173656]   Index Build: 83209700/258607871 32%
2019-03-21T23:42:16.000+0800 I -        [conn173656]   Index Build: 84776000/258607871 32%
2019-03-21T23:42:19.000+0800 I -        [conn173656]   Index Build: 86782300/258607871 33%
2019-03-21T23:42:22.000+0800 I -        [conn173656]   Index Build: 88719000/258607871 34%
2019-03-21T23:42:25.044+0800 I -        [conn173656]   Index Build: 90386400/258607871 34%
2019-03-21T23:42:28.000+0800 I -        [conn173656]   Index Build: 91778700/258607871 35%
2019-03-21T23:42:31.000+0800 I -        [conn173656]   Index Build: 93506300/258607871 36%
2019-03-21T23:42:34.000+0800 I -        [conn173656]   Index Build: 95172700/258607871 36%
2019-03-21T23:42:37.000+0800 I -        [conn173656]   Index Build: 97162600/258607871 37%
2019-03-21T23:42:40.000+0800 I -        [conn173656]   Index Build: 98919900/258607871 38%
2019-03-21T23:42:43.000+0800 I -        [conn173656]   Index Build: 100657400/258607871 38%
2019-03-21T23:42:46.000+0800 I -        [conn173656]   Index Build: 102813100/258607871 39%
2019-03-21T23:42:49.000+0800 I -        [conn173656]   Index Build: 104842100/258607871 40%
2019-03-21T23:42:52.000+0800 I -        [conn173656]   Index Build: 106704400/258607871 41%
2019-03-21T23:42:55.000+0800 I -        [conn173656]   Index Build: 108537400/258607871 41%
2019-03-21T23:42:58.000+0800 I -        [conn173656]   Index Build: 110224300/258607871 42%
2019-03-21T23:43:01.000+0800 I -        [conn173656]   Index Build: 112243600/258607871 43%
2019-03-21T23:43:04.000+0800 I -        [conn173656]   Index Build: 114221200/258607871 44%
2019-03-21T23:43:07.000+0800 I -        [conn173656]   Index Build: 116288000/258607871 44%
2019-03-21T23:43:10.000+0800 I -        [conn173656]   Index Build: 117968200/258607871 45%
2019-03-21T23:43:13.000+0800 I -        [conn173656]   Index Build: 119807100/258607871 46%
2019-03-21T23:43:16.000+0800 I -        [conn173656]   Index Build: 121574400/258607871 47%
2019-03-21T23:43:19.000+0800 I -        [conn173656]   Index Build: 123417500/258607871 47%
2019-03-21T23:43:22.000+0800 I -        [conn173656]   Index Build: 125190400/258607871 48%
2019-03-21T23:43:25.000+0800 I -        [conn173656]   Index Build: 127160100/258607871 49%
2019-03-21T23:43:28.000+0800 I -        [conn173656]   Index Build: 129129300/258607871 49%
2019-03-21T23:43:31.000+0800 I -        [conn173656]   Index Build: 130954200/258607871 50%
2019-03-21T23:43:34.000+0800 I -        [conn173656]   Index Build: 132826300/258607871 51%
2019-03-21T23:43:37.000+0800 I -        [conn173656]   Index Build: 134969400/258607871 52%
2019-03-21T23:43:40.000+0800 I -        [conn173656]   Index Build: 136888200/258607871 52%
2019-03-21T23:43:43.000+0800 I -        [conn173656]   Index Build: 138781300/258607871 53%
2019-03-21T23:43:46.000+0800 I -        [conn173656]   Index Build: 140845800/258607871 54%
2019-03-21T23:43:49.000+0800 I -        [conn173656]   Index Build: 142954400/258607871 55%
2019-03-21T23:43:52.000+0800 I -        [conn173656]   Index Build: 144941100/258607871 56%
2019-03-21T23:43:55.000+0800 I -        [conn173656]   Index Build: 147100600/258607871 56%
2019-03-21T23:43:58.000+0800 I -        [conn173656]   Index Build: 149083600/258607871 57%
2019-03-21T23:44:01.000+0800 I -        [conn173656]   Index Build: 150806300/258607871 58%
2019-03-21T23:44:04.000+0800 I -        [conn173656]   Index Build: 152803800/258607871 59%
2019-03-21T23:44:07.000+0800 I -        [conn173656]   Index Build: 155081000/258607871 59%
2019-03-21T23:44:10.000+0800 I -        [conn173656]   Index Build: 157280800/258607871 60%
2019-03-21T23:44:13.000+0800 I -        [conn173656]   Index Build: 159447000/258607871 61%
2019-03-21T23:44:16.000+0800 I -        [conn173656]   Index Build: 161664900/258607871 62%
2019-03-21T23:44:19.000+0800 I -        [conn173656]   Index Build: 163785800/258607871 63%
2019-03-21T23:44:22.000+0800 I -        [conn173656]   Index Build: 165935200/258607871 64%
2019-03-21T23:44:25.000+0800 I -        [conn173656]   Index Build: 168056000/258607871 64%
2019-03-21T23:44:28.000+0800 I -        [conn173656]   Index Build: 170213000/258607871 65%
2019-03-21T23:44:31.000+0800 I -        [conn173656]   Index Build: 172413200/258607871 66%
2019-03-21T23:44:34.000+0800 I -        [conn173656]   Index Build: 174671400/258607871 67%
2019-03-21T23:44:37.000+0800 I -        [conn173656]   Index Build: 176987000/258607871 68%
2019-03-21T23:44:40.000+0800 I -        [conn173656]   Index Build: 179213100/258607871 69%
2019-03-21T23:44:43.000+0800 I -        [conn173656]   Index Build: 181446000/258607871 70%
2019-03-21T23:44:46.000+0800 I -        [conn173656]   Index Build: 183767900/258607871 71%
2019-03-21T23:44:49.000+0800 I -        [conn173656]   Index Build: 185917800/258607871 71%
2019-03-21T23:44:52.000+0800 I -        [conn173656]   Index Build: 188073700/258607871 72%
2019-03-21T23:44:55.000+0800 I -        [conn173656]   Index Build: 190360900/258607871 73%
2019-03-21T23:44:58.000+0800 I -        [conn173656]   Index Build: 192678800/258607871 74%
2019-03-21T23:45:01.000+0800 I -        [conn173656]   Index Build: 194785400/258607871 75%
2019-03-21T23:45:04.000+0800 I -        [conn173656]   Index Build: 196422800/258607871 75%
2019-03-21T23:45:07.000+0800 I -        [conn173656]   Index Build: 198510900/258607871 76%
2019-03-21T23:45:10.000+0800 I -        [conn173656]   Index Build: 200783200/258607871 77%
2019-03-21T23:45:13.000+0800 I -        [conn173656]   Index Build: 202879500/258607871 78%
2019-03-21T23:45:16.000+0800 I -        [conn173656]   Index Build: 204974100/258607871 79%
2019-03-21T23:45:19.000+0800 I -        [conn173656]   Index Build: 207299700/258607871 80%
2019-03-21T23:45:22.000+0800 I -        [conn173656]   Index Build: 209466600/258607871 80%
2019-03-21T23:45:25.003+0800 I -        [conn173656]   Index Build: 211699800/258607871 81%
2019-03-21T23:45:28.000+0800 I -        [conn173656]   Index Build: 213717400/258607871 82%
2019-03-21T23:45:31.000+0800 I -        [conn173656]   Index Build: 215788300/258607871 83%
2019-03-21T23:45:34.000+0800 I -        [conn173656]   Index Build: 217941200/258607871 84%
2019-03-21T23:45:37.000+0800 I -        [conn173656]   Index Build: 220189800/258607871 85%
2019-03-21T23:45:40.000+0800 I -        [conn173656]   Index Build: 222499900/258607871 86%
2019-03-21T23:45:43.000+0800 I -        [conn173656]   Index Build: 224791400/258607871 86%
2019-03-21T23:45:46.000+0800 I -        [conn173656]   Index Build: 227017700/258607871 87%
2019-03-21T23:45:49.001+0800 I -        [conn173656]   Index Build: 229138000/258607871 88%
2019-03-21T23:45:52.000+0800 I -        [conn173656]   Index Build: 231403300/258607871 89%
2019-03-21T23:45:55.000+0800 I -        [conn173656]   Index Build: 233670700/258607871 90%
2019-03-21T23:45:58.000+0800 I -        [conn173656]   Index Build: 235926700/258607871 91%
2019-03-21T23:46:01.000+0800 I -        [conn173656]   Index Build: 238099400/258607871 92%
2019-03-21T23:46:04.000+0800 I -        [conn173656]   Index Build: 240270300/258607871 92%
2019-03-21T23:46:07.000+0800 I -        [conn173656]   Index Build: 242581500/258607871 93%
2019-03-21T23:46:10.000+0800 I -        [conn173656]   Index Build: 244820700/258607871 94%
2019-03-21T23:46:13.000+0800 I -        [conn173656]   Index Build: 247053400/258607871 95%
2019-03-21T23:46:16.000+0800 I -        [conn173656]   Index Build: 249338500/258607871 96%
2019-03-21T23:46:19.000+0800 I -        [conn173656]   Index Build: 251612600/258607871 97%
2019-03-21T23:46:22.001+0800 I -        [conn173656]   Index Build: 253744600/258607871 98%
2019-03-21T23:46:25.000+0800 I -        [conn173656]   Index Build: 255874500/258607871 98%
2019-03-21T23:46:28.000+0800 I -        [conn173656]   Index Build: 257982600/258607871 99%
2019-03-21T23:46:30.008+0800 I INDEX    [conn173656] build index done.  scanned 258607995 total records. 388 secs
2019-03-21T23:46:30.012+0800 I COMMAND  [conn173656] command xdb.$cmd appName: "MongoDB Shell" command: createIndexes { createIndexes: "T1", indexes: [ { key: { rol
eIds: 1.0, state: 1.0 }, name: "roleIds_1_state_1", partialFilterExpression: { roleIds: { $exists: true } } } ] } numYields:0 reslen:98 locks:{ Global: { acquireCount: { r: 2, w: 2 } }, Dat
abase: { acquireCount: { w: 1, W: 1 } }, Collection: { acquireCount: { w: 1 } }, Metadata: { acquireCount: { w: 1, W: 1 } }, oplog: { acquireCount: { w: 1 } } } protocol:op_command 388922ms
2019-03-21T23:46:30.015+0800 I -        [conn173656] end connection 127.0.0.1:47414 (16748 connections now open)

查看:

xdb:PRIMARY> db.T1.stats()
{
    "ns" : "xdb.T1",
    "size" : 136578507109,
    "count" : 258608354,
    "avgObjSize" : 528,
    "storageSize" : 39808692224,
    "capped" : false,
    "wiredTiger" : {
        "metadata" : {
            "formatVersion" : 1
        },
        "creationString" : "access_pattern_hint=none,allocation_size=4KB,app_metadata=(formatVersion=1),block_allocation=best,block_compressor=snappy,cache_resident=false,checksum=on,colgroups=,collator=,columns=,dictionary=0,encryption=(keyid=,name=),exclusive=false,extractor=,format=btree,huffman_key=,huffman_value=,ignore_in_memory_cache_size=false,immutable=false,internal_item_max=0,internal_key_max=0,internal_key_truncate=true,internal_page_max=4KB,key_format=q,key_gap=10,leaf_item_max=0,leaf_key_max=0,leaf_page_max=32KB,leaf_value_max=64MB,log=(enabled=true),lsm=(auto_throttle=true,bloom=true,bloom_bit_count=16,bloom_config=,bloom_hash_count=8,bloom_oldest=false,chunk_count_limit=0,chunk_max=5GB,chunk_size=10MB,merge_max=15,merge_min=0),memory_page_max=10m,os_cache_dirty_max=0,os_cache_max=0,prefix_compression=false,prefix_compression_min=4,source=,split_deepen_min_child=0,split_deepen_per_child=0,split_pct=90,type=file,value_format=u",
        "type" : "file",
        "uri" : "statistics:table:xdb/collection-238-3456123453573298727",
        "LSM" : {
            "bloom filter false positives" : 0,
            "bloom filter hits" : 0,
            "bloom filter misses" : 0,
            "bloom filter pages evicted from cache" : 0,
            "bloom filter pages read into cache" : 0,
            "bloom filters in the LSM tree" : 0,
            "chunks in the LSM tree" : 0,
            "highest merge generation in the LSM tree" : 0,
            "queries that could have benefited from a Bloom filter that did not exist" : 0,
            "sleep for LSM checkpoint throttle" : 0,
            "sleep for LSM merge throttle" : 0,
            "total size of bloom filters" : 0
        },
        "block-manager" : {
            "allocations requiring file extension" : 4248880,
            "blocks allocated" : 6311145,
            "blocks freed" : 1387579,
            "checkpoint size" : 39808647168,
            "file allocation unit size" : 4096,
            "file bytes available for reuse" : 28672,
            "file magic number" : 120897,
            "file major version number" : 1,
            "file size in bytes" : 39808692224,
            "minor version number" : 0
        },
        "btree" : {
            "btree checkpoint generation" : 395941,
            "column-store fixed-size leaf pages" : 0,
            "column-store internal pages" : 0,
            "column-store variable-size RLE encoded values" : 0,
            "column-store variable-size deleted values" : 0,
            "column-store variable-size leaf pages" : 0,
            "fixed-record size" : 0,
            "maximum internal page key size" : 368,
            "maximum internal page size" : 4096,
            "maximum leaf page key size" : 2867,
            "maximum leaf page size" : 32768,
            "maximum leaf page value size" : 67108864,
            "maximum tree depth" : 5,
            "number of key/value pairs" : 0,
            "overflow pages" : 0,
            "pages rewritten by compaction" : 0,
            "row-store internal pages" : 0,
            "row-store leaf pages" : 0
        },
        "cache" : {
            "bytes currently in the cache" : 95685536626,
            "bytes read into cache" : 488796324028,
            "bytes written from cache" : 129705453169,
            "checkpoint blocked page eviction" : 4,
            "data source pages selected for eviction unable to be evicted" : 294136,
            "hazard pointer blocked page eviction" : 30298,
            "in-memory page passed criteria to be split" : 19666,
            "in-memory page splits" : 9808,
            "internal pages evicted" : 2274952,
            "internal pages split during eviction" : 327,
            "leaf pages split during eviction" : 61800,
            "modified pages evicted" : 191123,
            "overflow pages read into cache" : 0,
            "overflow values cached in memory" : 0,
            "page split during eviction deepened the tree" : 0,
            "page written requiring lookaside records" : 0,
            "pages read into cache" : 19293356,
            "pages read into cache requiring lookaside entries" : 0,
            "pages requested from the cache" : 1457582381,
            "pages written from cache" : 5730705,
            "pages written requiring in-memory restoration" : 1503,
            "unmodified pages evicted" : 17596298
        },
        "cache_walk" : {
            "Average difference between current eviction generation when the page was last considered" : 0,
            "Average on-disk page image size seen" : 0,
            "Clean pages currently in cache" : 0,
            "Current eviction generation" : 0,
            "Dirty pages currently in cache" : 0,
            "Entries in the root page" : 0,
            "Internal pages currently in cache" : 0,
            "Leaf pages currently in cache" : 0,
            "Maximum difference between current eviction generation when the page was last considered" : 0,
            "Maximum page size seen" : 0,
            "Minimum on-disk page image size seen" : 0,
            "On-disk page image sizes smaller than a single allocation unit" : 0,
            "Pages created in memory and never written" : 0,
            "Pages currently queued for eviction" : 0,
            "Pages that could not be queued for eviction" : 0,
            "Refs skipped during cache traversal" : 0,
            "Size of the root page" : 0,
            "Total number of pages currently in cache" : 0
        },
        "compression" : {
            "compressed pages read" : 17002665,
            "compressed pages written" : 4588594,
            "page written failed to compress" : 0,
            "page written was too small to compress" : 1142111,
            "raw compression call failed, additional data available" : 0,
            "raw compression call failed, no additional data available" : 0,
            "raw compression call succeeded" : 0
        },
        "cursor" : {
            "bulk-loaded cursor-insert calls" : 0,
            "create calls" : 615158,
            "cursor-insert key and value bytes inserted" : 113579680903,
            "cursor-remove key bytes removed" : 5,
            "cursor-update value bytes updated" : 0,
            "insert calls" : 212557485,
            "next calls" : 565142437,
            "prev calls" : 1,
            "remove calls" : 1,
            "reset calls" : 184547265,
            "restarted searches" : 44646,
            "search calls" : 836252823,
            "search near calls" : 126550562,
            "truncate calls" : 0,
            "update calls" : 0
        },
        "reconciliation" : {
            "dictionary matches" : 0,
            "fast-path pages deleted" : 0,
            "internal page key bytes discarded using suffix compression" : 23018190,
            "internal page multi-block writes" : 680760,
            "internal-page overflow keys" : 0,
            "leaf page key bytes discarded using prefix compression" : 0,
            "leaf page multi-block writes" : 295663,
            "leaf-page overflow keys" : 0,
            "maximum blocks required for a page" : 69,
            "overflow values written" : 0,
            "page checksum matches" : 32350627,
            "page reconciliation calls" : 1437207,
            "page reconciliation calls for eviction" : 33550,
            "pages deleted" : 1242
        },
        "session" : {
            "object compaction" : 0,
            "open cursor count" : 81
        },
        "transaction" : {
            "update conflicts" : 0
        }
    },
    "nindexes" : 3,
    "totalIndexSize" : 6706860032,
    "indexSizes" : {
        "formTemplateId_1_state_1_codeId_1" : 4080517120,
        "_id_" : 2624847872,
        "roleIds_1_state_1" : 1495040
    },
    "ok" : 1
}

可以看到新建这个索引非常快,且占用空间非常小,1M多。

建完索引就可以统计包含 roleIds 字段有多少记录了。

xdb:SECONDARY> db.T1.find({ roleIds: { $exists: true } }).count()
248007

可以看到不到25万(该表总记录数超过2.5亿),可以看出 partial indexes(sparse indexes)的优势了。

0 回复
需要 登录 后方可回复, 如果你还没有账号你可以 注册 一个帐号。