生产环境Laravel应用firstOrNew触发MariaDB超时优化求助

问题概述 #

生产环境Laravel应用用于记录视频播放量，使用firstOrNew处理devices表（约2000万条记录）时，无论流量高峰/平稳期均持续出现数据库超时，部分请求耗时超30秒，数据库为MariaDB。

现有核心代码 #

public function store(Request $request){
    // define message for when view is already registered
    $mssg = 'view already exist';

    try{
        // Start database transaction
        // Retry 3 times if first attempt fails (this is done to avoid db deadlock issues)
        DB::transaction(function () use ($request, $mssg) {
            // get request data
            $ip = $request->ip();
            $videoId = $request->input('video_id');
            $hostname = $request->has('embed_source') ? $request->input('embed_source') : NULL;

            // check if view already counted
            if(View::where(['ip' => $ip, 'video_id' => $videoId])->count()) throw new Exception($mssg);

            // Register new view
            // Firstly, retrieve or create device
            $agent = new Agent();
            $position = Location::get($ip);
            $device = Device::firstOrNew(
                [
                    'type' => $agent->isDesktop() ? 'desktop' : ($agent->isTablet() ? 'tablet' : ($agent->isPhone() ? 'phone' : 'unknown')),
                    'browser' => strtolower($agent->browser()),
                    'country' => $position && $position->countryName ? strtolower($position->countryName) : null,
                    'region' => $position && $position->regionName ? strtolower($position->regionName) : null,
                    'city' => $position && $position->cityName ? strtolower($position->cityName) : null,
                    'hostname' => $hostname,
                ],
                [
                    'long' => $position && $position->longitude ? $position->longitude : null,
                    'lat' => $position && $position->latitude ? $position->latitude : null,
                ]
            );
            $device->save();

            // Secondly, save new view with device ID
            $view = new View();
            $view->video_id = $videoId;
            $view->device_id = $device->id;
            $view->ip = $ip;
            $view->watched_duration = 1;
            $view->save();
            // Lastly, increment views count in videos table
            Video::where('id', $videoId)->increment('total_views');

        }, 3);
        
        return response(['viewed' => true], 200);
        
    }catch(\Throwable $e){
        if($e->getMessage() == $mssg) return response(['message' => $mssg], 200);

        info($e->getMessage(), [
            'title' => 'COULD NOT REGISTER VIEW',
        ]);
        return response(['error' => $e->getMessage()], 500);
    }
}

优化方案 #

1. 数据库查询与结构优化 #

• 添加复合索引：针对devices表的查询条件创建联合索引，这是解决firstOrNew查询超时的核心。执行以下SQL：

  CREATE INDEX idx_devices_query ON devices (type, browser, country, region, city, hostname);
  

  注意：若部分字段（如country/region）存在大量NULL值，可调整索引字段顺序，将非空/高频匹配字段前置，提升索引命中率。
• 优化重复播放检查：将View::where(...)->count()替换为View::where(...)->exists()，exists()只需判断记录是否存在，无需统计总数，性能提升显著。
• 添加唯一约束：在views表创建(ip, video_id)唯一索引，替代代码层面的重复检查，由数据库原子性拦截重复请求，同时避免并发场景下的竞态问题：

  CREATE UNIQUE INDEX idx_views_ip_video ON views (ip, video_id);
  

• 数据库配置调优：
  • 增大innodb_buffer_pool_size（建议设置为服务器内存的50%-70%），让更多devices表数据缓存到内存，减少磁盘IO。
  • 调整连接超时参数：wait_timeout、interactive_timeout设为合理值（如300秒），避免无效连接占用资源；适当调高max_connections应对并发。

2. 代码逻辑优化 #

• 缩小事务范围：将地理位置获取（Location::get($ip)）、Agent解析等无状态读操作移到事务外部，事务仅包裹device/view写入、视频播放量递增等写操作，减少事务持有时间，降低锁竞争概率。
• 缓存外部依赖结果：
  • 用Redis缓存IP地理位置信息，以ip:{ip_address}为key，缓存有效期设为7天（IP归属地极少变动），避免每次请求调用地理位置接口。
  • 缓存User-Agent解析结果，以ua:{md5(user_agent)}为key，缓存设备类型、浏览器信息，减少Agent类的解析开销。
• 移除冗余事务重试：若已通过唯一约束解决并发问题，可减少事务重试次数（或仅保留1次），避免无效重试加剧数据库压力。

3. 替代firstOrNew的方案 #

使用Laravel的updateOrInsert方法，它基于数据库INSERT ... ON DUPLICATE KEY UPDATE实现原子操作，仅需1次数据库请求，替代firstOrNew的“查询+插入/更新”两次请求，大幅提升效率：

// 先在事务外完成Agent和地理位置解析
$agent = new Agent();
$position = Location::get($ip); // 已做缓存优化
$deviceType = $agent->isDesktop() ? 'desktop' : ($agent->isTablet() ? 'tablet' : ($agent->isPhone() ? 'phone' : 'unknown'));
$browser = strtolower($agent->browser());
$country = $position && $position->countryName ? strtolower($position->countryName) : null;
$region = $position && $position->regionName ? strtolower($position->regionName) : null;
$city = $position && $position->cityName ? strtolower($position->cityName) : null;
$long = $position && $position->longitude ? $position->longitude : null;
$lat = $position && $position->latitude ? $position->latitude : null;

// 原子化创建/更新设备记录
$device = Device::updateOrInsert(
    [
        'type' => $deviceType,
        'browser' => $browser,
        'country' => $country,
        'region' => $region,
        'city' => $city,
        'hostname' => $hostname,
    ],
    [
        'long' => $long,
        'lat' => $lat,
        'updated_at' => Carbon::now(),
    ]
);

注意：需确保devices表的查询字段组合有唯一索引，否则updateOrInsert无法触发更新逻辑。

4. 应用扩容与架构优化 #

• 引入队列异步处理：将播放记录写入逻辑放到Laravel队列（如Redis队列），用户请求仅需将任务推入队列后立即返回响应，异步执行数据库写入操作，削峰填谷，避免数据库瞬间压力过载。
• 缓存播放量统计：用Redis的INCR命令递增视频播放量，定时（如每分钟）通过队列将Redis中的统计数据同步到videos表，彻底减少数据库写请求频率。
• 应用层水平扩容：部署多个Laravel应用实例，通过Nginx或负载均衡器分发请求，分散应用层压力。
• 数据库读写分离：将读操作（如设备查询、重复播放检查）路由到MariaDB从库，写操作保留在主库，减轻主库负载。
• 大数据量存储替换：若devices和views表持续增长，可考虑将播放记录迁移到ClickHouse等时序数据库，它针对高并发写入、大数据量统计场景做了深度优化，性能远超传统关系型数据库。

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