如何基于计算列对大型数据库表实现分页排序？

问题背景 #

我有一个大型user表，结构如下：

业务需求是：通过no_per_page和page参数实现分页查询，同时需要根据events计算出total_event_hours列（前端需展示并支持排序）。直接查询全表后计算排序会导致性能暴跌，需要一种高效的实现方式。

当前代码逻辑是先分页查询数据，再循环计算total_event_hours，但无法基于该列排序：

$no_per_page = $param['no_per_page'];
$start = $param['page'] > 1 ? ($param['page'] - 1) * $no_per_page : 0;

$query = "SELECT * FROM user LIMIT :start, :no_per_page"; 
$get_query = $this->db->prepare($query);
$get_query->bindParam(':start', $start);
$get_query->bindParam(':no_per_page', $no_per_page);
$get_query->execute();
$data = $get_query->fetchAll(PDO::FETCH_ASSOC);

foreach ($data as &$_data) {
  $_data['total_event_hours'] = $this->_getTotalEventHours($_data['events']); 
  // 无法基于该列排序，因为分页是在计算前做的
}

return $data;

高效解决方案 #

方案1：将计算逻辑迁移到数据库层（推荐） #

核心思路是让数据库直接计算total_event_hours，这样可以在SQL中完成排序后再分页，避免全表扫描。

情况A：events是JSON类型（MySQL/PostgreSQL支持）

假设events是JSON数组，每个元素包含hours字段（如[{"hours": 2.5}, {"hours": 3}]）：

MySQL实现

SELECT 
  *,
  -- 解析JSON数组并求和时长
  (SELECT SUM(JSON_UNQUOTE(JSON_EXTRACT(event, '$.hours'))) 
   FROM JSON_TABLE(events, '$[*]' COLUMNS (event JSON PATH '$')) AS jt) 
  AS total_event_hours
FROM user
-- 根据前端传入的排序方向调整ASC/DESC
ORDER BY total_event_hours DESC
LIMIT :start, :no_per_page;

对应的PHP代码修改：

$no_per_page = $param['no_per_page'];
$start = $param['page'] > 1 ? ($param['page'] - 1) * $no_per_page : 0;
// 新增排序参数，由前端传入（比如sort_by=total_event_hours&sort_dir=desc）
$sortDir = $param['sort_dir'] ?? 'desc';
// 过滤参数避免SQL注入
$sortDir = in_array($sortDir, ['asc', 'desc']) ? $sortDir : 'desc';

$query = "SELECT 
            *,
            (SELECT SUM(JSON_UNQUOTE(JSON_EXTRACT(event, '$.hours'))) 
             FROM JSON_TABLE(events, '$[*]' COLUMNS (event JSON PATH '$')) AS jt) 
            AS total_event_hours
          FROM user
          ORDER BY total_event_hours $sortDir
          LIMIT :start, :no_per_page"; 

$get_query = $this->db->prepare($query);
$get_query->bindParam(':start', $start, PDO::PARAM_INT);
$get_query->bindParam(':no_per_page', $no_per_page, PDO::PARAM_INT);
$get_query->execute();
$data = $get_query->fetchAll(PDO::FETCH_ASSOC);

// 无需再循环计算，直接返回数据
return $data;

PostgreSQL实现

SELECT
  u.*,
  -- 解析JSONB数组并求和，COALESCE处理空数组情况
  COALESCE(SUM((event->>'hours')::numeric), 0) AS total_event_hours
FROM user u
LEFT JOIN LATERAL jsonb_array_elements(u.events) AS event ON true
GROUP BY u.id
ORDER BY total_event_hours DESC
LIMIT :no_per_page OFFSET :start;

性能优化：使用生成列+索引

如果total_event_hours排序频繁，可以将其设为持久化生成列，并添加索引，彻底解决性能问题：

MySQL示例：

-- 添加持久化生成列，自动计算总时长
ALTER TABLE user 
ADD COLUMN total_event_hours DECIMAL(10,2) AS (
  (SELECT SUM(JSON_UNQUOTE(JSON_EXTRACT(event, '$.hours'))) 
   FROM JSON_TABLE(events, '$[*]' COLUMNS (event JSON PATH '$')) AS jt)
) STORED;

-- 给生成列加索引
CREATE INDEX idx_user_total_event_hours ON user(total_event_hours);

之后查询时直接用该列排序，性能和普通字段一致：

SELECT * FROM user ORDER BY total_event_hours DESC LIMIT :start, :no_per_page;

方案2：无法修改数据库结构时的妥协方案 #

如果events是PHP序列化等数据库无法解析的格式，只能在应用层计算，但可以优化流程减少数据传输：

$no_per_page = $param['no_per_page'];
$page = $param['page'];
$sortDir = $param['sort_dir'] ?? 'desc';

// 第一步：只查询id和events，减少数据量
$query = "SELECT id, events FROM user";
$stmt = $this->db->prepare($query);
$stmt->execute();
$allItems = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 计算总时长并整理成id映射
$totalMap = [];
foreach ($allItems as $item) {
    $totalMap[$item['id']] = $this->_getTotalEventHours($item['events']);
}

// 按总时长排序id数组
uksort($totalMap, function($a, $b) use ($totalMap, $sortDir) {
    return $sortDir === 'asc' 
        ? $totalMap[$a] <=> $totalMap[$b] 
        : $totalMap[$b] <=> $totalMap[$a];
});

// 第二步：分页截取id
$start = ($page - 1) * $no_per_page;
$paginatedIds = array_slice(array_keys($totalMap), $start, $no_per_page);

// 第三步：根据id查询完整数据
if (empty($paginatedIds)) {
    return [];
}
$placeholders = implode(',', array_fill(0, count($paginatedIds), '?'));
$query = "SELECT * FROM user WHERE id IN ($placeholders)";
$stmt = $this->db->prepare($query);
$stmt->execute($paginatedIds);
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

// 给结果补上总时长
foreach ($data as &$item) {
    $item['total_event_hours'] = $totalMap[$item['id']];
}

return $data;

注意：该方案仍需扫描全表的id和events，数据量极大时性能仍有瓶颈，仅作为无法修改数据库时的备选。

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