登录后台

页面导航

本文编写于 216 天前,最后修改于 214 天前,其中某些信息可能已经过时。

Laravel里chunkById的坑

=============================
线上生产环境可能非常大,一次性取出来遍历可能爆掉服务器内存.因此需要用到chunkById,但是chunkById在使用时会默认添加orderBy,使用id主键索引.导致有些场景不会触发其他索引.
查看源码:

     * Chunk the results of a query by comparing numeric IDs.
     *
     * @param  int  $count
     * @param  callable  $callback
     * @param  string  $column
     * @param  string|null  $alias
     * @return bool
     */
    public function chunkById($count, callable $callback, $column = null, $alias = null)
    {
        $column = is_null($column) ? $this->getModel()->getKeyName() : $column;

        $alias = is_null($alias) ? $column : $alias;

        $lastId = 0;

        do {
            $clone = clone $this;

            // We'll execute the query for the given page and get the results. If there are
            // no results we can just break and return from here. When there are results
            // we will call the callback with the current chunk of these results here.
            $results = $clone->forPageAfterId($count, $lastId, $column)->get();

            $countResults = $results->count();

            if ($countResults == 0) {
                break;
            }

            // On each chunk result set, we will pass them to the callback and then let the
            // developer take care of everything within the callback, which allows us to
            // keep the memory low for spinning through large result sets for working.
            if ($callback($results) === false) {
                return false;
            }

            $lastId = $results->last()->{$alias};
        } while ($countResults == $count);

        return true;
    }

第三个参数$column 在没有传入时是默认使用主键的,
再看forPageAfterId 这个方法:

/**
     * Constrain the query to the next "page" of results after a given ID.
     *
     * @param  int  $perPage
     * @param  int  $lastId
     * @param  string  $column
     * @return \Illuminate\Database\Query\Builder|static
     */
    public function forPageAfterId($perPage = 15, $lastId = 0, $column = 'id')
    {
        $this->orders = $this->removeExistingOrdersFor($column);
        if (! is_null($lastId)) {
              $this->where($column, '>', $lastId);
          }
        return $this->orderBy($column, 'asc')
                    ->take($perPage);
    }

能看到如果lastId不为0则自动添加where语句,还会自动添加order by column。

看到这里就明白了。上文的chunkById没有添加column参数,所以底层自动添加了order by id。走了主键索引,导致查询效率非常低。

chunkById的源码显示了我们可以传递一个column字段来让底层使用这个字段来order by。


column字段通过源码查看,只适用于int类型或者纯数字字符串.

总结

ChunkById里,你没有传递column参数时,会默认添加order by id。可能会遇到索引失效的问题。解决办法就是传递column参数即可。