indexing - 在没有提示的情况下不使用 MongoDB 聚合索引

问题描述

我有一个水果车的集合，数百万行（更改名称以保护有罪者。）

这些推车中的每一个都有所有者、品牌和型号，以及关于推车是否包含某些类型的水果的几个 Y 或 N 字段。

我想生成一个模型列表，并计算每个具有特定类型水果的推车数量。

所以数据会是这样的：

fruit.cart.insert(
{ 'owner' => 'Fred',   'make' => 'Toshiba', 'model' => 'fruitmaster 9000', 'have_apples' => 'Y', 'have_grapes' => 'N', 'have_peaches' => 'Y', ... },
{ 'owner' => 'Wilma',  'make' => 'Toshiba', 'model' => 'fruitmaster 9000', 'have_apples' => 'Y', 'have_grapes' => 'N', 'have_peaches' => 'N', ... },
{ 'owner' => 'Betty',  'make' => 'Toshiba', 'model' => 'fruitmaster 9000', 'have_apples' => 'N', 'have_grapes' => 'Y', 'have_peaches' => 'Y', ... },
{ 'owner' => 'Barney', 'make' => 'Toshiba', 'model' => 'fruitmaster X5',   'have_apples' => 'N', 'have_grapes' => 'N', 'have_peaches' => 'Y', ... },
{ 'owner' => 'Fred',   'make' => 'Honda',   'model' => 'T-1000',           'have_apples' => 'Y', 'have_grapes' => 'Y', 'have_peaches' => 'Y', ... },
{ 'owner' => 'Wilma',  'make' => 'Honda',   'model' => 'T-1000',           'have_apples' => 'N', 'have_grapes' => 'N', 'have_peaches' => 'N', ... },

我希望输出是：

{ 'make' => 'Toshiba', 'model' => 'fruitmaster 9000', 'count' => 3, 'apples_count' => 2, 'grapes_count' => 1, 'peaches_count' => 2, ... },
{ 'make' => 'Toshiba', 'model' => 'fruitmaster X5',   'count' => 1, 'apples_count' => 0, 'grapes_count' => 0, 'peaches_count' => 1, ... },
{ 'make' => 'Honda',   'model' => 'T-1000',           'count' => 2, 'apples_count' => 1, 'grapes_count' => 1, 'peaches_count' => 1, ... },

所以这是我的汇总查询：

{ '$group' =>
  {
    _id             =>  { 'model'  => '$model', },
    model           =>  { '$first' => '$model' },
    make            =>  { '$first' => '$make'  },
    count           =>  { '$sum'   =>  1 },
    oranges_count   =>  { '$sum'   => { '$cond' => [ { '$eq' => [ '$have_oranges', 'Y' ] }, 1, 0 ] }, },
    grapes_count    =>  { '$sum'   => { '$cond' => [ { '$eq' => [ '$have_grapes',  'Y' ] }, 1, 0 ] }, },
    peaches_count   =>  { '$sum'   => { '$cond' => [ { '$eq' => [ '$have_peaches', 'Y' ] }, 1, 0 ] }, },
    apples_count    =>  { '$sum'   => { '$cond' => [ { '$eq' => [ '$have_apples',  'Y' ] }, 1, 0 ] }, },
    pears_count     =>  { '$sum'   => { '$cond' => [ { '$eq' => [ '$have_pears',   'Y' ] }, 1, 0 ] }, },
  },
},

带索引：

db.cart.createIndex (
 {
   'have_pears'    : 1,
   'have_apples'   : 1,
   'have_oranges'  : 1,
   'have_grapes'   : 1,
   'have_peaches'  : 1,
   'make'          : 1,
   'model'         : 1,
 }, { background : true, name : 'fruit_counts' } );

如果我在不提供索引提示的情况下运行它，那么它就不会使用它并且查询需要永远：

  ...
  'queryPlanner' => {
    'indexFilterSet' => bless( do{\(my $o = 0)}, 'boolean' ),
    'namespace' => 'fruit.cart',
    'parsedQuery' => {},
    'plannerVersion' => 1,
    'rejectedPlans' => [],
    'winningPlan' => {
      'direction' => 'forward',
      'stage' => 'COLLSCAN'
    }
  }
  ...

提示它很快：

  {
    'ok' => '1',
    'stages' => [
      {
        '$cursor' => {
          'fields' => {
            '_id' => 0,
            'have_peaches' => 1,
            'have_apples' => 1,
            'have_oranges' => 1,
            'have_grapes' => 1,
            'have_pears' => 1,
            'model' => 1,
            'make' => 1
          },
          'query' => {},
          'queryPlanner' => {
            'indexFilterSet' => bless( do{\(my $o = 0)}, 'boolean' ),
            'namespace' => 'fruit.cart',
            'parsedQuery' => {},
            'plannerVersion' => 1,
            'rejectedPlans' => [],
            'winningPlan' => {
              'inputStage' => {
                'direction' => 'forward',
                'indexBounds' => {
                  'have_peaches' => [ '[MinKey, MaxKey]' ],
                  'have_apples' => [ '[MinKey, MaxKey]' ],
                  'have_oranges' => [ '[MinKey, MaxKey]' ],
                  'have_grapes' => [ '[MinKey, MaxKey]' ],
                  'have_pears' => [ '[MinKey, MaxKey]' ],
                  'model' => [ '[MinKey, MaxKey]' ],
                  'make' => [ '[MinKey, MaxKey]' ]
                },
                'indexName' => 'fruit_counts',
                'indexVersion' => 2,
                'isMultiKey' => $VAR1->[0]{'stages'}[0]{'$cursor'}{'queryPlanner'}{'indexFilterSet'},
                'isPartial' => $VAR1->[0]{'stages'}[0]{'$cursor'}{'queryPlanner'}{'indexFilterSet'},
                'isSparse' => $VAR1->[0]{'stages'}[0]{'$cursor'}{'queryPlanner'}{'indexFilterSet'},
                'isUnique' => $VAR1->[0]{'stages'}[0]{'$cursor'}{'queryPlanner'}{'indexFilterSet'},
                'keyPattern' => {
                  'have_peaches' => '1',
                  'have_apples' => '1',
                  'have_oranges' => '1',
                  'have_grapes' => '1',
                  'have_pears' => '1',
                  'model' => '1',
                  'make' => '1'
                },
                'multiKeyPaths' => {
                  'have_peaches' => [],
                  'have_apples' => [],
                  'have_oranges' => [],
                  'have_grapes' => [],
                  'have_pears' => [],
                  'model' => [],
                  'make' => []
                },
                'stage' => 'IXSCAN'
              },
              'stage' => 'PROJECTION',
              'transformBy' => {
                '_id' => 0,
                'have_peaches' => 1,
                'have_apples' => 1,
                'have_oranges' => 1,
                'have_grapes' => 1,
                'have_pears' => 1,
                'model' => 1,
                'make' => 1
              }
            }
          }
        }
      },

...

没有提示的documentdb：

'queryPlanner' => {
  'namespace' => 'fruit.cart',
  'plannerVersion' => 1,
  'winningPlan' => {
    'inputStage' => {
      'inputStage' => {
        'stage' => 'COLLSCAN'
      },
      'stage' => 'SORT'
    },
    'stage' => 'SORT_AGGREGATE'
  }
},

带有提示的文档数据库：

MongoDB::DatabaseError: Cannot use Hint for this Query. Index is multi key index or sparse index and query is not optimized to use this index.

噢噢噢！！！

那么我该怎么做才能使它甚至不需要提示，然后让它在 DocumentDB 中工作呢？

标签： indexingaggregateaws-documentdb

indexing - 在没有提示的情况下不使用 MongoDB 聚合索引

问题描述

解决方案

推荐阅读