通配符索引:什么场景值得用

  test> db.wc.getIndexes().filter(i => i.name != "_id_")
  [
    {
      v: 2,
      key: { 'attrs.$**': 1 },
      name: 'attrs.$**_1',
      collation: { locale: 'simple' }
    }
  ]
  索引条目数: {'_id_': 100000, 'attrs.$**_1': 300000}(= 100,000 个文档 × 3 个键)

  test> db.wc.find({"attrs.k17": "v5"})
  {
    plan: 'FETCH > IXSCAN(attrs.$**_1)',
    nReturned: 291,
    keysExamined: 291,
    docsExamined: 291,
    ms: 2
  }
  # 索引键是 (字段路径, 值),所以边界是 [路径, 值]
  test> var e = db.wc.find({"attrs.k17": "v5"}).explain(); var s = e.queryPlanner.winningPlan; while (s.inputStage) s = s.inputStage; ({indexName: s.indexName, indexBounds: s.indexBounds})
  {
    indexName: 'attrs.$**_1',
    indexBounds: {
      '$_path': [ '["attrs.k17", "attrs.k17"]' ],
      'attrs.k17': [ '["v5", "v5"]' ]
    }
  }
  test> db.wc.find({"attrs.k17": {$in: [v5, v6]}})
  {
    plan: 'FETCH > IXSCAN(attrs.$**_1)',
    nReturned: 604,
    keysExamined: 605,
    docsExamined: 604,
    ms: 1
  }
  test> 对照:没有索引的 plain 集合
  {
    plan: 'COLLSCAN',
    nReturned: 291,
    keysExamined: 0,
    docsExamined: 100000,
    ms: 27
  }

  test> db.wc.find({"attrs.k33": "v0", "attrs.k22": "v7"})
  {
    plan: 'FETCH > IXSCAN(attrs.$**_1)',
    nReturned: 2,
    keysExamined: 266,
    docsExamined: 266,
    ms: 0
  }
  test> 对照:cmp 集合上 attrs.k33 + attrs.k22 的复合索引
  {
    plan: 'FETCH > IXSCAN(attrs.k33_1_attrs.k22_1)',
    nReturned: 2,
    keysExamined: 2,
    docsExamined: 2,
    ms: 1
  }

  test> find({attrs.k17: {$gte: v1}}).sort({attrs.k17: 1})
  {
    plan: 'FETCH > IXSCAN(attrs.$**_1)',
    nReturned: 5658,
    keysExamined: 5658,
    docsExamined: 5658,
    ms: 3
  }
  test> find({attrs.k17: {$ne: v5}})
  {
    plan: 'COLLSCAN',
    nReturned: 99709,
    keysExamined: 0,
    docsExamined: 100000,
    ms: 19
  }
  test> find({attrs.k17: {$exists: true}})
  {
    plan: 'FETCH > IXSCAN(attrs.$**_1)',
    nReturned: 5944,
    keysExamined: 5945,
    docsExamined: 5944,
    ms: 4
  }
  test> find({name: "item5"})    // name 不在通配符索引覆盖的 attrs.* 下
  {
    plan: 'COLLSCAN',
    nReturned: 1,
    keysExamined: 0,
    docsExamined: 100000,
    ms: 15
  }

  通配符索引(300,000 条)大小 2,625,536 字节;单字段索引 attrs.k17(约 6,000 条)大小 376,832 字节
  批量插入 100,000 个文档:无二级索引 260 ms;带通配符索引 493 ms(1.9 倍)

  test> createIndex({"attrs.$**": 1}, {unique: true})
  Index type 'wildcard' does not support the 'unique' option
  test> createIndex({"$**": 1}, {wildcardProjection: {"attrs.k1": 0, "name": 0}})    // 排除某些路径
  $**_1
  整个文档 $** 的集合(2 万个文档,每个 name + 3 个 attrs + _id):索引条目数 {'$**_1': 80000, '_id_': 20000}
  test> createIndex({"$**": 1, name: 1})
  Compound wildcard indexes on all fields must also specify 'wildcardProjection' option
  test> createIndex({"attrs.$**": 1, name: 1})    // 通配符路径加普通字段的复合索引
  attrs.$**_1_name_1
  test> db.x5.getIndexes().filter(i => i.name != "_id_")
  [
    {
      v: 2,
      key: { 'attrs.$**': 1, name: 1 },
      name: 'attrs.$**_1_name_1',
      collation: { locale: 'simple' }
    }
  ]