如何高效查询450万条记录的MySQL表中近5分钟数据？

你的现有方案：双查询跳转法 #

你的思路完全正确，利用id和created_at同步递增的特性，先定位5分钟前对应的最大ID，再通过主键索引快速过滤后续数据，这是避免全表扫描且无需额外索引的优质临时方案。在Laravel中可以这样落地：

// 第一步：获取5分钟前记录的最大ID
$thresholdId = DB::table('activity_logs')
    ->where('created_at', '<', now()->subMinutes(5))
    ->orderBy('id', 'desc')
    ->value('id');

// 第二步：利用主键索引快速查询近5分钟记录
$recentLogs = DB::table('activity_logs')
    ->where('id', '>', $thresholdId ?? 0)
    ->get();

注意处理$thresholdId为null的情况（比如表中无5分钟前数据），用?? 0兜底避免查询报错。

标准方案1：给created_at添加索引（适配你的场景） #

你之前担心created_at值分布广不适合加索引，这是个误区：

• InnoDB的B+树索引对**范围查询（如>/<）**效率极高，尤其是查询最新数据时，索引叶子节点末尾就是最新时间数据，引擎无需遍历全索引，定位速度很快；
• 450万条数据的时间索引占用空间极小，按每条8字节计算，仅约36MB，完全在可接受范围内。

在Laravel中通过迁移添加索引：

// 生成迁移文件
php artisan make:migration add_index_to_created_at_on_activity_logs

// 迁移文件内的执行逻辑
public function up()
{
    Schema::table('activity_logs', function (Blueprint $table) {
        $table->index('created_at');
    });
}

public function down()
{
    Schema::table('activity_logs', function (Blueprint $table) {
        $table->dropIndex(['created_at']);
    });
}

添加索引后，你最初的where("created_at", ">=", now()->subMinutes(5))查询会直接走created_at索引，无需全表扫描，代码更简洁。

标准方案2：按时间分区表 #

如果未来表数据持续增长至千万级以上，可考虑将activity_logs按时间分区（如按天/小时），查询近5分钟数据时，数据库会直接定位到对应分区，无需扫描其他分区数据。

MySQL中按天分区的示例SQL：

ALTER TABLE activity_logs 
PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p20240101 VALUES LESS THAN (TO_DAYS('2024-01-02')),
    PARTITION p20240102 VALUES LESS THAN (TO_DAYS('2024-01-03')),
    PARTITION p_current VALUES LESS THAN MAXVALUE
);

Laravel本身不直接支持分区表创建，需手动执行SQL或借助第三方扩展（如spatie/laravel-partition）实现。

标准方案3：归档旧数据 #

由于应用已运行数年，大部分旧API调用记录无需频繁查询，可将超过一定时长（如3个月）的数据归档到历史表（如activity_logs_historical），主表仅保留近期数据，从根源减少查询数据量。

Laravel中编写自动归档的命令行脚本：

// 创建命令文件
php artisan make:command ArchiveOldLogs

// 命令类内的归档逻辑
public function handle()
{
    $cutoffDate = now()->subMonths(3);
    
    // 分批迁移旧数据到历史表
    DB::table('activity_logs')
        ->where('created_at', '<', $cutoffDate)
        ->orderBy('id')
        ->chunk(1000, function ($logs) {
            DB::table('activity_logs_historical')->insert($logs->toArray());
            DB::table('activity_logs')->whereIn('id', $logs->pluck('id'))->delete();
        });
    
    $this->info('旧日志归档完成');
}

之后可通过Laravel任务调度（Cron）每天自动执行该归档命令。

总结 #

• 短期内不想改动表结构，你的双查询方案高效可行；
• 从长期维护和代码简洁性考虑，给created_at加索引是最优标准方案；
• 数据量增长至千万级以上时，分区表+归档是更彻底的性能优化方案。

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