超10万条数据MySQL统计商品状态查询过慢，求优化方案

你当前查询慢的核心原因不是SQL端求和操作，是两个底层逻辑问题：

• listings表和递归生成的日期表生成了笛卡尔积，假设你查20天、10万条符合条件的商品，这一步直接生成200万行中间数据
• 每一行中间数据都要执行一次关联子查询查logs表，相当于要跑200万次单条子查询，开销随查询日期范围线性上升

完全可行，核心思路是先拉取两个最小必要的基础数据集，在PHP内存中做统计，避免SQL层面的笛卡尔积和重复子查询，视图层基本不需要修改。

步骤1：模型层拉取基础数据 #

// 传入查询起止日期，比如$start = '2021-09-25', $end = '2021-10-15'
public function get_base_data($start, $end) {
    // 拉取所有符合条件的商品：添加日期<=查询结束日期
    $listings = $this->db->query("SELECT refno, status, added_date FROM listings WHERE added_date <= ?", [$end])->result();
    // 拉取查询时间范围内的所有状态变更日志，按refno和时间排序
    $logs = $this->db->query("SELECT refno, logtime, status_from FROM logs WHERE logtime >= ? ORDER BY refno, logtime ASC", [$start])->result();
    // 把日志按refno分组，方便后续查找
    $log_map = [];
    foreach($logs as $log) {
        $refno = $log->refno;
        if(!isset($log_map[$refno])) $log_map[$refno] = [];
        $log_map[$refno][] = $log;
    }
    return ['listings' => $listings, 'log_map' => $log_map];
}

步骤2：控制器层做统计 #

public function count_status($start, $end) {
    $base = $this->your_model->get_base_data($start, $end);
    $listings = $base['listings'];
    $log_map = $base['log_map'];
    // 生成查询日期范围数组
    $period = new DatePeriod(new DateTime($start), new DateInterval('P1D'), new DateTime($end . ' +1 day'));
    $result = [];
    foreach($period as $date) {
        $day = $date->format('Y-m-d');
        $result[$day] = [
            'day' => $day,
            'draft' => 0,
            'publish' => 0,
            'action' => 0,
            'sold' => 0,
            'let' => 0
        ];
    }
    // 遍历每个商品统计每日状态
    foreach($listings as $goods) {
        $refno = $goods->refno;
        $init_status = $goods->status;
        $added_date = $goods->added_date;
        $goods_logs = $log_map[$refno] ?? [];
        $current_status = $init_status;
        $log_idx = 0;
        $log_count = count($goods_logs);
        foreach($period as $date) {
            $day = $date->format('Y-m-d');
            // 商品还未添加，跳过统计
            if($day < $added_date) continue;
            // 更新当前日期的状态：找当前日期之后第一个变更记录的前置状态
            while($log_idx < $log_count && $goods_logs[$log_idx]->logtime < $day) {
                $current_status = $goods_logs[$log_idx]->status_from;
                $log_idx++;
            }
            $stat = $log_idx < $log_count ? $goods_logs[$log_idx]->status_from : $current_status;
            // 累加计数
            switch($stat) {
                case 'D': $result[$day]['draft']++; break;
                case 'A': $result[$day]['action']++; break;
                case 'Y': $result[$day]['publish']++; break;
                case 'S': $result[$day]['sold']++; break;
                case 'L': $result[$day]['let']++; break;
            }
        }
    }
    // 传给视图的$data_total格式和原来完全一致，不需要改视图逻辑
    $data['data_total'] = array_values($result);
    $this->load->view('your_view', $data);
}

步骤3：视图层直接复用 #

你现有的视图代码不需要做任何调整，直接就能使用。

1. SQL层面优化（不改架构的前提下最快方案） #

• 新增索引：
  • listings表加INDEX idx_added_date(added_date)，加速符合条件商品的筛选
  • logs表加联合索引INDEX idx_refno_logtime(refno, logtime, status_from)，覆盖子查询需要的所有字段，避免回表查询，加完索引后原有SQL性能至少提升5-10倍
• 改写SQL用窗口函数替代关联子查询，彻底避免笛卡尔积，性能可以再提升10倍以上

WITH RECURSIVE dates(day) AS (
    SELECT '2021-09-25'
    UNION ALL
    SELECT day + INTERVAL 1 DAY FROM dates WHERE day < '2021-10-15'
),
goods_timeline AS (
    SELECT 
        l.refno, l.status init_status, l.added_date,
        log.logtime, log.status_from,
        ROW_NUMBER() OVER(PARTITION BY l.refno ORDER BY log.logtime) rn
    FROM listings l
    LEFT JOIN logs log ON l.refno = log.refno AND log.logtime >= '2021-09-25'
    WHERE l.added_date <= '2021-10-15'
)
SELECT 
    d.day,
    SUM(CASE WHEN COALESCE(gt.status_from, gt.init_status) = 'D' THEN 1 ELSE 0 END) Draft,
    SUM(CASE WHEN COALESCE(gt.status_from, gt.init_status) = 'A' THEN 1 ELSE 0 END) Action,
    SUM(CASE WHEN COALESCE(gt.status_from, gt.init_status) = 'Y' THEN 1 ELSE 0 END) Publish,
    SUM(CASE WHEN COALESCE(gt.status_from, gt.init_status) = 'S' THEN 1 ELSE 0 END) Sold,
    SUM(CASE WHEN COALESCE(gt.status_from, gt.init_status) = 'L' THEN 1 ELSE 0 END) Let
FROM dates d
JOIN goods_timeline gt ON d.day >= gt.added_date
WHERE (gt.rn = 1 OR gt.logtime >= d.day)
GROUP BY d.day
ORDER BY d.day;

2. 预计算汇总表（长期最优方案） #

新增一张daily_goods_status_count表，字段为day, draft, publish, action, sold, let，每天凌晨跑定时任务，统计前一天的各状态数量写入该表。查询的时候直接从这个表取数，不管数据量多大都能毫秒级返回结果，适合高频查询的业务场景。

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