MySQL 8大表索引优化困境：Laravel 12项目PingtreeTransaction索引选型

基础背景 #

基于PingtreeTransaction表（日增50万条），核心查询场景分两类：

1. 单模型关联查询：按application_id/buyer_id筛选，可附带日期范围
2. 报表多条件组合查询：支持company_id/buyer_id/result+日期范围的任意组合分页

当前痛点：单字段索引会被MySQL优先选择日期字段导致查询超时；复合索引适配性差易膨胀；分区未与索引有效结合。

核心索引设计方案 #

场景1：单模型关联查询优化 #

针对application_id/buyer_id的关联+日期查询，创建等值在前、范围在后的复合索引：

-- 适配application_id+日期的查询
CREATE INDEX idx_application_date ON pingtree_transactions (application_id, processing_started_at);

-- 适配buyer_id+日期的查询
CREATE INDEX idx_buyer_date ON pingtree_transactions (buyer_id, processing_started_at);

• 逻辑：等值条件（application_id/buyer_id）前置能快速缩小数据集，后续日期范围过滤直接在小范围内执行，避免全表扫描。

场景2：报表多条件组合查询优化 #

针对任意组合的等值筛选+日期范围，创建前缀兼容的复合索引，同时支持覆盖索引减少回表：

-- 核心组合索引，适配company_id/buyer_id/result的任意组合+日期查询
CREATE INDEX idx_report_core ON pingtree_transactions (company_id, buyer_id, result, processing_started_at);

-- 可选：添加覆盖索引，包含报表常用返回字段，避免回表
CREATE INDEX idx_report_covering ON pingtree_transactions (company_id, buyer_id, result, processing_started_at)
INCLUDE (commission, processing_duration, request_response_code);

• 逻辑：高频等值字段（company_id/buyer_id/result）按优先级排序前置，日期范围放在最后，MySQL会自动匹配前缀字段的组合查询（比如仅company_id+日期、buyer_id+result+日期都能命中索引）。

解决MySQL选错索引问题 #

1. 强制指定索引：在Laravel查询中，明确指定目标索引避免优化器误选：

PingtreeTransaction::where('buyer_id', $buyerId)
    ->whereBetween('processing_started_at', [$startDate, $endDate])
    ->useIndex('idx_buyer_date')
    ->paginate();

2. 更新表统计信息：定期执行命令让优化器获取最新数据分布：

ANALYZE TABLE pingtree_transactions;

3. 清理冗余索引：如果单字段processing_started_at索引几乎不单独使用，直接删除该索引，消除优化器的选择干扰。

分区与索引的协同优化 #

利用partition_id（YYYYMMDD格式）的RANGE分区，配合查询自动添加分区过滤：

1. 确保表按partition_id做RANGE分区：

ALTER TABLE pingtree_transactions PARTITION BY RANGE (partition_id) (
    PARTITION p202401 VALUES LESS THAN (20240201),
    PARTITION p202402 VALUES LESS THAN (20240301),
    -- 按需添加后续分区
);

2. Laravel全局作用域自动注入分区过滤：

// 在PingtreeTransaction模型中添加全局作用域
protected static function booted()
{
    static::addGlobalScope('partition_filter', function ($query) {
        $request = request();
        if ($request->has(['start_date', 'end_date'])) {
            $startPartition = date('Ymd', strtotime($request->start_date));
            $endPartition = date('Ymd', strtotime($request->end_date));
            $query->whereBetween('partition_id', [$startPartition, $endPartition]);
        }
    });
}

• 逻辑：先通过分区裁剪排除无关日期的数据，再用索引在目标分区内查询，双重缩小查询范围。

索引维护建议 #

• 定期清理历史数据：按分区批量删除过期数据（比如保留6个月），避免表体积持续膨胀。
• 监控索引使用率：通过sys.schema_unused_indexes排查未使用的索引，及时删除减少维护成本。
• 避免过度索引：仅针对高频查询场景创建索引，拒绝为低频组合单独建索引。

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