laravel mongodb拓展包 count耗时过长及优化

mongodb jenssegers/mongodb count()数据量时耗时明显增加

laravel 引用拓展包jenssegers/mongodb 后count()在数据量比较大时，耗时极大

起源

1.在mongoBooster中直接count(),11w+用时0.005

2.用laravel console命令执行

\App\Models\Order::query()->count()

用时

mongodb jenssegers/mongodb count()数据量时耗时明显增加

laravel 引用拓展包jenssegers/mongodb 后count()在数据量比较大时，耗时极大

是什么原因导致差异如此巨大呢？

源码探究

1. Illuminate\Database\Query\Builder.php

 /**
     * Retrieve the "count" result of the query.
     *
     * @param  string  $columns
     * @return int
     */
    public function count($columns = '*')
    {
        return (int) $this->aggregate(__FUNCTION__, array_wrap($columns));
    }

2. jenssegers/mongodb 的builder中重写aggregate()方法

Jenssegers\Mongodb\Query\Builder.php

public function aggregate($function, $columns = [])
    {
        $this->aggregate = compact('function', 'columns');

        $previousColumns = $this->columns;

        // We will also back up the select bindings since the select clause will be
        // removed when performing the aggregate function. Once the query is run
        // we will add the bindings back onto this query so they can get used.
        $previousSelectBindings = $this->bindings['select'];

        $this->bindings['select'] = [];

        $results = $this->get($columns);

        // Once we have executed the query, we will reset the aggregate property so
        // that more select queries can be executed against the database without
        // the aggregate value getting in the way when the grammar builds it.
        $this->aggregate = null;
        $this->columns = $previousColumns;
        $this->bindings['select'] = $previousSelectBindings;

        if (isset($results[0])) {
            $result = (array) $results[0];

            return $result['aggregate'];
        }
    }

通过$this->aggregate = compact(‘function’, ‘columns’); 添加聚合字段

通过$results = $this->get($columns);实现底层聚合

/**
     * @inheritdoc
     */
    public function get($columns = [])
    {
        return $this->getFresh($columns);
    }

    /**
     * Execute the query as a fresh "select" statement.
     *
     * @param  array $columns
     * @return array|static[]|Collection
     */
    public function getFresh($columns = [])
    {
        // If no columns have been specified for the select statement, we will set them
        // here to either the passed columns, or the standard default of retrieving
        // all of the columns on the table using the "wildcard" column character.
        if (is_null($this->columns)) {
            $this->columns = $columns;
        }

        // Drop all columns if * is present, MongoDB does not work this way.
        if (in_array('*', $this->columns)) {
            $this->columns = [];
        }

        // Compile wheres
        $wheres = $this->compileWheres();

        // Use MongoDB's aggregation framework when using grouping or aggregation functions.
        if ($this->groups or $this->aggregate or $this->paginating) {
            $group = [];
            $unwinds = [];

            // Add grouping columns to the $group part of the aggregation pipeline.
            if ($this->groups) {
                foreach ($this->groups as $column) {
                    $group['_id'][$column] = '$' . $column;

                    // When grouping, also add the $last operator to each grouped field,
                    // this mimics MySQL's behaviour a bit.
                    $group[$column] = ['$last' => '$' . $column];
                }

                // Do the same for other columns that are selected.
                foreach ($this->columns as $column) {
                    $key = str_replace('.', '_', $column);

                    $group[$key] = ['$last' => '$' . $column];
                }
            }

            // Add aggregation functions to the $group part of the aggregation pipeline,
            // these may override previous aggregations.
            if ($this->aggregate) {
                $function = $this->aggregate['function'];

                foreach ($this->aggregate['columns'] as $column) {
                    // Add unwind if a subdocument array should be aggregated
                    // column: subarray.price => {$unwind: '$subarray'}
                    if (count($splitColumns = explode('.*.', $column)) == 2) {
                        $unwinds[] = $splitColumns[0];
                        $column = implode('.', $splitColumns);
                    }

                    // Translate count into sum.
                    if ($function == 'count') {
                        $group['aggregate'] = ['$sum' => 1];
                    } // Pass other functions directly.
                    else {
                        $group['aggregate'] = ['$' . $function => '$' . $column];
                    }
                }
            }

            // When using pagination, we limit the number of returned columns
            // by adding a projection.
            if ($this->paginating) {
                foreach ($this->columns as $column) {
                    $this->projections[$column] = 1;
                }
            }

            // The _id field is mandatory when using grouping.
            if ($group and empty($group['_id'])) {
                $group['_id'] = null;
            }

            // Build the aggregation pipeline.
            $pipeline = [];
            if ($wheres) {
                $pipeline[] = ['$match' => $wheres];
            }

            // apply unwinds for subdocument array aggregation
            foreach ($unwinds as $unwind) {
                $pipeline[] = ['$unwind' => '$' . $unwind];
            }

            if ($group) {
                $pipeline[] = ['$group' => $group];
            }

            // Apply order and limit
            if ($this->orders) {
                $pipeline[] = ['$sort' => $this->orders];
            }
            if ($this->offset) {
                $pipeline[] = ['$skip' => $this->offset];
            }
            if ($this->limit) {
                $pipeline[] = ['$limit' => $this->limit];
            }
            if ($this->projections) {
                $pipeline[] = ['$project' => $this->projections];
            }

            $options = [
                'typeMap' => ['root' => 'array', 'document' => 'array'],
            ];

            // Add custom query options
            if (count($this->options)) {
                $options = array_merge($options, $this->options);
            }

            // Execute aggregation
            $results = iterator_to_array($this->collection->aggregate($pipeline, $options));

            // Return results
            return $this->useCollections ? new Collection($results) : $results;
        } // Distinct query
        elseif ($this->distinct) {
            // Return distinct results directly
            $column = isset($this->columns[0]) ? $this->columns[0] : '_id';

            // Execute distinct
            if ($wheres) {
                $result = $this->collection->distinct($column, $wheres);
            } else {
                $result = $this->collection->distinct($column);
            }

            return $this->useCollections ? new Collection($result) : $result;
        } // Normal query
        else {
            $columns = [];

            // Convert select columns to simple projections.
            foreach ($this->columns as $column) {
                $columns[$column] = true;
            }

            // Add custom projections.
            if ($this->projections) {
                $columns = array_merge($columns, $this->projections);
            }
            $options = [];

            // Apply order, offset, limit and projection
            if ($this->timeout) {
                $options['maxTimeMS'] = $this->timeout;
            }
            if ($this->orders) {
                $options['sort'] = $this->orders;
            }
            if ($this->offset) {
                $options['skip'] = $this->offset;
            }
            if ($this->limit) {
                $options['limit'] = $this->limit;
            }
            if ($columns) {
                $options['projection'] = $columns;
            }
            // if ($this->hint)    $cursor->hint($this->hint);

            // Fix for legacy support, converts the results to arrays instead of objects.
            $options['typeMap'] = ['root' => 'array', 'document' => 'array'];

            // Add custom query options
            if (count($this->options)) {
                $options = array_merge($options, $this->options);
            }

            // Execute query and get MongoCursor
            $cursor = $this->collection->find($wheres, $options);

            // Return results as an array with numeric keys
            $results = iterator_to_array($cursor, false);
            return $this->useCollections ? new Collection($results) : $results;
        }
    }

 // Add aggregation functions to the $group part of the aggregation pipeline,
            // these may override previous aggregations.
            if ($this->aggregate) {
                $function = $this->aggregate['function'];

                foreach ($this->aggregate['columns'] as $column) {
                    // Add unwind if a subdocument array should be aggregated
                    // column: subarray.price => {$unwind: '$subarray'}
                    if (count($splitColumns = explode('.*.', $column)) == 2) {
                        $unwinds[] = $splitColumns[0];
                        $column = implode('.', $splitColumns);
                    }

                    // Translate count into sum.
                    if ($function == 'count') {
                        $group['aggregate'] = ['$sum' => 1];
                    } // Pass other functions directly.
                    else {
                        $group['aggregate'] = ['$' . $function => '$' . $column];
                    }
                }
            }

// Translate count into sum.
if ($function == 'count') {
    $group['aggregate'] = ['$sum' => 1];
}

可以看到拓展包的底层就是通过\MongoDB\Client 的aggregate $sum去实现的

优化

居然是用用聚合的实现，那么我们是不是可以通过跳过聚合用\MongoDB\Client 的count实现呢

$pipeline = [];
$count = \App\Models\Order::raw()->count();