MySQL+PHP环境下每日更新用户3个随机任务ID的最优性能方案咨询

基础信息 #

• 环境：MySQL、PHP
• 表结构（missions表）：

usermissionid  |  userid  |  missionid  | value

• 数据示例：

h5s6l4  |  h5s6l  |  4  |  0
h5s6l2  |  h5s6l  |  2  |  0
h5s6l6  |  h5s6l  |  6  |  0

核心需求 #

每个用户固定维护3条任务记录，每日需将这3条记录更新为3个不重复的随机missionid，更新完成后要获取这些新的missionid。需找出性能最优、数据库负载最低的实现方案。

现有方案分析 #

方案1：MySQL生成随机ID+批量更新+查询 #

通过MySQL直接生成随机missionid，批量更新用户的3条记录，之后查询获取新ID。

• 缺点：无法保证生成的3个missionid唯一，需要捕获错误重试，或额外处理避免RAND()重复
• 代码实现：

UPDATE 
 missions
 SET
 value = 0, missionid = CEIL((RAND() + 0.001) * (25) -0.025), usermissionid = CONCAT(userid, missionid)
 WHERE 
 userid = 'h5s6l'    

SELECT 
 missionid
FROM 
 missions
WHERE
 userid = 'h5s6l'

方案2：PHP生成随机ID+多次更新+存储旧ID #

在PHP中生成随机missionid，通过循环执行3条更新语句，额外执行1条更新语句存储旧任务ID。

• 缺点：需执行4次独立查询，数据库交互次数多
• 代码实现：

$usermissionids = ['h5s6l4', ...];
for($i=0; $i < 3; $i++){
    $newUsermissionid = 'h5s6l7';
    $newMissionid = 7; // 随机生成
    // 更新任务记录
    $sql = "UPDATE 
             missions
             SET
             value = 0, missionid = '{$newMissionid}', usermissionid = '{$newUsermissionid}'
             WHERE 
             userid = 'h5s6l'";
    // 执行SQL
}
// 更新旧任务ID
$sql = "UPDATE 
         user
         SET
         oldmissionids = '2|6|7'
         WHERE 
         userid = 'h5s6l'";
// 执行SQL

方案3：删除旧记录+PHP生成ID+多次插入 #

先删除用户的所有3条任务记录，再通过PHP生成随机ID，循环执行3条插入语句。

• 缺点：删除+多次插入，数据库写操作次数多，且存在短时间无数据的窗口
• 代码实现：

DELETE  
FROM
    missions
WHERE
    userid = 'h5s6l'

for($i=0; $i < 3; $i++){
    $newUsermissionid = 'h5s6l7';
    $newMissionid = 7; // 随机生成
    // 插入新任务记录
    $sql = "INSERT INTO
             missions (usermissionid, userid, missionid, value)
             VALUES
             ('{$newUsermissionid}', 'h5s6l', {$newMissionid}, 0)";
    // 执行SQL
}

方案4：任务组固定记录+批量重置 #

为用户预存所有任务（当前16个，可能增至30个）的记录，每日仅重置value为0。

• 缺点：冗余存储多，查询时需过滤有效任务，且若记录缺失需额外处理插入逻辑（如REPLACE或ON DUPLICATE KEY UPDATE）
• 代码实现：

UPDATE 
    missions
SET
    value = 0
WHERE 
    userid = 'h5s6l'

方案5：固定用户任务位+PHP生成ID+多次更新 #

为用户设置3个固定的usermissionid（如h5s6l1、h5s6l2、h5s6l3），PHP生成3个随机ID后循环执行3条更新语句。

• 缺点：仍需执行3次独立更新查询，数据库交互次数较多
• 代码实现：

$curset = [1,4,6]; // PHP生成的3个随机唯一ID
for($i=0; $i < 3; $i++){
    // 更新任务记录
    $sql = "UPDATE 
             missions
             SET
             value = 0, missionid = {$curset[$i]}
             WHERE 
             userid = 'h5s6l' AND usermissionid = 'h5s6l".($i+1)."'";
    // 执行SQL
}

优化疑问 #

是否存在性能更优的方案？或者有更合理的表结构设计思路？

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