MySQL 8.4分区表未有效利用索引问题排查（Laravel 12场景）

背景 #

• 环境：Laravel 12 + MySQL 8.4
• 表：pingtree_transactions，每日新增/更新约50万行，需存储至少2个月数据
• 分区策略：按YYYYMMDD格式生成整数类型partition_id作为分区键，通过请求的from/to日期参数筛选对应分区ID

问题现象 #

1. 多分区查询：当WHERE子句包含3个分区ID（20250521、20250522、20250523，其中两个分区仅1行数据，一个分区40万行）时，查询未使用任何索引
2. 单分区查询：仅保留大分区20250522时，返回少量数据却耗时约300ms，而同结构未分区表仅需5ms

现有查询与代码 #

示例查询SQL #

EXPLAIN SELECT
  *
FROM
  `pingtree_transactions`
WHERE
  `partition_id` IN (20250521, 20250522)
  AND `company_id` IN (2, 1)
  AND `processing_started_at` >= '2025-05-21 23:00:00'
  AND `processing_ended_at` <= '2025-05-22 22:59:59'
ORDER BY
  `processing_started_at` ASC
LIMIT
  26 OFFSET 0

Laravel分区查询范围代码 #

/**
 * Scope a query to only include certain partitions
 */
public function scopePartitionable(Builder $query): void
{
    $from = Carbon::parse(request()->input('from', now()))->addDay()->startOfDay();
    $to = Carbon::parse(request()->input('to', now()))->addDay()->endOfDay();

    $period = CarbonPeriod::create(
        $from, CarbonInterval::day(), $to
    );

    $partitionIds = collect($period->toArray())
        ->map(fn(Carbon $date) => (int) $date->format('Ymd'))
        ->unique()
        ->values()
        ->toArray();

    $query->whereIn('partition_id', $partitionIds);
}

表结构定义 #

Schema::create('pingtree_transactions', function (Blueprint $table) {
    $table->ulid('id');
    $table->foreignId('company_id');
    $table->foreignId('application_id');
    $table->foreignId('buyer_id');
    $table->foreignId('buyer_tier_id');
    $table->foreignId('pingtree_group_id');
    $table->foreignId('pingtree_id');
    $table->mediumInteger('processing_duration')->default(0);
    $table->smallInteger('request_response_code')->default(200);
    $table->decimal('commission', 8, 2)->default(0.00);
    $table->string('result', 32)->default('unknown');
    $table->string('request_url')->nullable();
    $table->integer('partition_id');
    $table->date('processed_on');
    $table->dateTime('processing_started_at');
    $table->dateTime('processing_ended_at')->nullable();
    $table->timestamps();

    $table->primary(['id', 'partition_id']);

    $table->index(['company_id']);
    $table->index(['application_id']);
    $table->index(['buyer_id']);
    $table->index(['buyer_tier_id']);
    $table->index(['result']);
    $table->index(['partition_id']);
    $table->index(['processed_on']);
    $table->index(['processing_started_at']);
    $table->index(['processing_ended_at']);

    $table->index([
        'company_id',
        'buyer_tier_id',
        'buyer_id',
        'result',
        'processing_started_at',
        'processing_ended_at'
    ], 'composite_pingtree_transactions_all_index');
});

问题根源 #

1. 复合索引未包含分区键：
   现有复合索引composite_pingtree_transactions_all_index未将partition_id作为首字段，而MySQL分区表的索引是按分区独立维护的。查询时虽然通过partition_id做了分区裁剪，但在分区内无法利用包含company_id和时间条件的复合索引快速定位数据，只能遍历分区内的索引或全表扫描。

2. 多分区IN查询的优化器判断：
   当查询涉及多个小分区+大分区时，MySQL优化器可能认为跨分区合并索引的成本高于直接全表扫描（小分区数据量极小，扫描成本可忽略，大分区的索引遍历成本被误判），因此选择不使用索引。

3. 分区筛选逻辑的冗余：
   Laravel代码中$from和$to都调用了addDay()，可能导致分区ID列表包含了超出用户实际查询范围的分区（比如用户查5.21-5.22，代码可能生成5.22-5.23的分区ID），增加了不必要的分区扫描。

4. 单分区查询的索引效率：
   未分区表的全局索引可以直接定位到符合条件的数据，而分区表的索引仅在当前分区内有效，且现有索引未结合分区键，导致查询需要在分区内进行更多的索引遍历或数据扫描。

优化方案 #

1. 调整复合索引结构 #

创建包含partition_id的复合索引，匹配查询的过滤顺序：

CREATE INDEX idx_pt_partition_company_start_end ON pingtree_transactions (
    partition_id,
    company_id,
    processing_started_at,
    processing_ended_at
);

• 首字段用partition_id：利用分区裁剪快速定位目标分区，同时在分区内通过company_id过滤，再通过时间范围精准匹配，最后直接满足ORDER BY processing_started_at的排序需求（索引有序，无需额外排序）。
• 可以删除原有的单字段partition_id索引和冗余的复合索引，减少维护成本。

2. 修正Laravel分区筛选逻辑 #

去掉不必要的addDay()，确保分区ID列表精准匹配用户的查询日期范围：

public function scopePartitionable(Builder $query): void
{
    $from = Carbon::parse(request()->input('from', now()))->startOfDay();
    $to = Carbon::parse(request()->input('to', now()))->endOfDay();

    $period = CarbonPeriod::create(
        $from, CarbonInterval::day(), $to
    );

    $partitionIds = collect($period->toArray())
        ->map(fn(Carbon $date) => (int) $date->format('Ymd'))
        ->unique()
        ->values()
        ->toArray();

    $query->whereIn('partition_id', $partitionIds);
}

3. 强制指定索引（可选） #

如果优化器仍未选择正确的索引，可以在Laravel查询中强制指定：

$query->forceIndex('idx_pt_partition_company_start_end');

4. 验证执行计划 #

每次调整后用EXPLAIN查看执行计划，确认：

• type列显示range或ref（而非ALL）
• key列显示预期的复合索引
• rows列显示扫描的行数大幅减少

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