如何通过PHP Cron任务提升数据库表批量插入数据的速度

1. 替换OFFSET分页为游标分页，解决翻页耗时递增问题 #

你当前用的LIMIT offset, 10000分页逻辑存在典型的深度分页问题：当offset值越大，MySQL需要扫描并丢弃的前置无效行数越多，直接导致越往后翻页耗时越长。
优化方式：用自增主键igems_item_id作为游标标记处理进度，每次查询仅筛选大于上次处理的最大id的记录，无需扫描前置无效行，每次查询效率稳定。同时可以省去提前统计总条数的COUNT大查询，进一步降低开销。

2. 批量插入替换单条插入，大幅降低数据库交互开销 #

当前代码每条记录单独发起一次INSERT请求，10万条数据需要和数据库交互10万次，网络IO、事务提交开销占了总耗时的绝大多数。
优化方式：循环拼接批量INSERT语句，每1000_{2000条记录执行一次插入操作，交互次数直接降到几十次，插入效率可提升10}50倍。

3. 新增索引优化查询速度 #

针对你用到的查询条件，新增两个联合索引可以大幅提升查询效率：

• 给gems_item_cron_data表新增联合索引：INDEX idx_sku_itemid(gems_old_sku, gems_itemid)，优化NOT EXISTS子查询的匹配速度
• 给gems_item表新增覆盖索引：INDEX idx_query(igems_item_id, old_sku, diamond_video_url)，查询时直接从索引取数据，无需回表查询原数据

如果业务允许，还可以给gems_item表新增一个tinyint类型的冗余字段has_valid_video，写入gems_item时就判断diamond_video_url是否为合法http链接并更新该字段，查询时直接用has_valid_video = 1替代原有的模糊匹配条件，避免每次查询都做全表模糊扫描。

4. 其他细节优化 #

• 循环外提前计算固定值：你代码里的$video_url是固定值，$diamond_final_video_url可以直接在循环外提前算好，不需要每次循环重复执行urlencode
• 用事务包裹批量插入操作，减少事务提交的刷盘开销
• 对字符串字段做转义处理，避免SQL注入风险

优化后代码示例 #

$DB = new myclassi();
$batchSize = 1000; // 每批处理条数，可根据服务器性能调整为1000~2000
$lastMaxId = 0; // 游标初始值
// 提前计算固定值
$video_url = 'xxxfdafas';
$diamond_final_video_url = urlencode($video_url);
$is_processed = 0;

while (true) {
    // 游标分页查询，每次仅查大于上次处理最大id的记录
    $sqlr = "SELECT `gi`.`igems_item_id`, `gi`.`old_sku` 
    FROM `gems_item` AS `gi` 
    WHERE NOT EXISTS (
        SELECT 1 FROM `gems_item_cron_data` As `gicd` 
        WHERE `gi`.`old_sku` = `gicd`.`gems_old_sku` AND `gi`.`igems_item_id` = `gicd`.`gems_itemid`
    )
    AND `gi`.`igems_item_id` > {$lastMaxId}
    AND `gi`.`diamond_video_url` IS NOT NULL 
    AND (`gi`.`diamond_video_url` LIKE '%http%' OR  `gi`.`diamond_video_url` LIKE '%https%')  
    ORDER BY `gi`.`igems_item_id` ASC
    LIMIT {$batchSize}";
    $results = $DB->select_assoc($sqlr);
    if (empty($results)) {
        break; // 没有数据直接退出循环
    }

    // 拼接批量插入语句
    $insertValues = [];
    $created_at = date('Y-m-d H:i:s');
    foreach ($results as $row) {
        $k = intval($row['igems_item_id']);
        $old_sku = $DB->escape($row['old_sku']); // 调用你数据库类的转义方法，避免SQL注入
        $insertValues[] = "($k, '$old_sku', '$diamond_final_video_url', $is_processed, '$created_at')";
        // 更新游标
        if ($k > $lastMaxId) {
            $lastMaxId = $k;
        }
    }

    // 批量执行插入
    $insertSql = "INSERT INTO gems_item_cron_data (`gems_itemid`,`gems_old_sku`, `diamond_final_video_url`,`is_processed`,`created_at`) 
    VALUES " . implode(',', $insertValues);
    $DB->insert($insertSql);
}
$DB->close();

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