Yii2技术问询：能否合并记录查询与计数查询？AR查询优化

嘿，这个问题戳中了大数据表分页的痛点——默认的两次查询（先count再取数）在10亿级别的表上简直是性能杀手，尤其是COUNT(*)全表扫描的开销，分分钟拖垮请求。下面给你两种实用的方案，根据你的数据库类型来选：

方案一：MySQL/MariaDB专属——用SQL_CALC_FOUND_ROWS + FOUND_ROWS() #

这是MySQL官方提供的优化方案，核心是让数据库在执行分页查询的同时，悄悄统计符合条件的总条数，后续只需要一个轻量的查询就能拿到总数，避免单独的COUNT(*)全表扫描。

步骤1：自定义DataProvider（封装逻辑，复用方便） #

默认的ActiveDataProvider会先执行count查询，所以我们需要重写它的prepareTotalCount()方法，让它先执行带SQL_CALC_FOUND_ROWS的分页查询，再取总数：

namespace app\components;

use yii\data\ActiveDataProvider;

class CalcFoundRowsDataProvider extends ActiveDataProvider
{
    protected function prepareTotalCount()
    {
        // 确保查询中包含SQL_CALC_FOUND_ROWS（避免重复添加）
        $select = $this->query->select;
        $selectArray = is_array($select) ? $select : [$select];
        
        if (!in_array('SQL_CALC_FOUND_ROWS', $selectArray)) {
            array_unshift($selectArray, 'SQL_CALC_FOUND_ROWS');
            $this->query->select = $selectArray;
        }

        // 先获取分页数据（这一步会执行带SQL_CALC_FOUND_ROWS的查询）
        $this->models = $this->prepareModels();

        // 用FOUND_ROWS()快速拿到总条数（数据库已经统计好了，无需再扫描表）
        return (int)\Yii::$app->db->createCommand('SELECT FOUND_ROWS()')->queryScalar();
    }
}

步骤2：使用自定义DataProvider #

和普通的ActiveDataProvider用法几乎一样，只是替换成我们自定义的类：

use app\components\CalcFoundRowsDataProvider;
use app\models\YourBigModel;

$query = YourBigModel::find()
    ->where(['status' => 1]) // 你的查询条件
    ->orderBy(['created_at' => SORT_DESC]);

$dataProvider = new CalcFoundRowsDataProvider([
    'query' => $query,
    'pagination' => [
        'pageSize' => 20,
    ],
]);

// 后续调用getModels()和getTotalCount()就会自动走优化逻辑
$models = $dataProvider->getModels();
$total = $dataProvider->getTotalCount();

注意事项 #

• 这个方法只适用于MySQL/MariaDB，其他数据库不支持SQL_CALC_FOUND_ROWS语法；
• 确保你的查询条件和排序字段有合适的索引，否则即使是分页查询，在10亿行表上也会很慢；
• 如果你的查询包含DISTINCT或者GROUP BY，SQL_CALC_FOUND_ROWS的性能优势会减弱，这时候可能需要结合其他优化手段。

方案二：跨数据库通用——窗口函数（适合PostgreSQL、SQL Server等） #

如果你的数据库支持窗口函数（比如PostgreSQL 9.4+、SQL Server 2008+、MySQL 8.0+），可以用COUNT(*) OVER()一次查询同时拿到分页数据和总条数，完全避免两次查询：

直接使用的示例 #

use app\models\YourBigModel;

$pageSize = 20;
$offset = 0; // 根据当前页码计算

$query = YourBigModel::find()
    ->select([
        '*',
        'COUNT(*) OVER() AS total_count' // 窗口函数统计总条数
    ])
    ->where(['status' => 1])
    ->orderBy(['created_at' => SORT_DESC])
    ->limit($pageSize)
    ->offset($offset);

$models = $query->all();
$totalCount = !empty($models) ? (int)$models[0]->total_count : 0;

封装到DataProvider #

同样可以重写ActiveDataProvider的prepareTotalCount()方法，让它自动处理窗口函数的结果：

namespace app\components;

use yii\data\ActiveDataProvider;

class WindowCountDataProvider extends ActiveDataProvider
{
    protected function prepareTotalCount()
    {
        // 添加窗口函数统计总条数
        $this->query->addSelect('COUNT(*) OVER() AS total_count');
        
        // 获取分页数据
        $this->models = $this->prepareModels();
        
        // 从第一条数据中提取总条数
        return !empty($this->models) ? (int)$this->models[0]->total_count : 0;
    }
}

注意事项 #

• 窗口函数会给每条返回的记录都带上total_count字段，不需要的话可以后续在模型里忽略；
• 同样，索引优化是基础，没有合适的索引，10亿行的分页查询本身就会非常慢。

额外优化建议：Keyset分页（针对超大offset场景） #

如果你的分页页码很大（比如跳到第10000页），传统的OFFSET分页会因为需要扫描大量无关数据而变慢，这时候可以用Keyset分页（基于上一页最后一条记录的唯一标识来分页），比如：

// 假设上一页最后一条记录的created_at是1620000000，id是100000
$query = YourBigModel::find()
    ->where(['>', 'created_at', 1620000000])
    ->orWhere([
        'and',
        ['=', 'created_at', 1620000000],
        ['>', 'id', 100000]
    ])
    ->orderBy(['created_at' => SORT_DESC, 'id' => SORT_DESC])
    ->limit(20);

这种方式可以利用(created_at, id)联合索引快速定位到起始位置，完全避免offset带来的性能问题，配合上面的总数统计方案，能让10亿行表的分页性能起飞。

内容的提问来源于stack exchange，提问作者111