告别“先删后插”：PHP与MySQL批量更新性能优化实战

## 问题背景：低效的“先删后插”模式 在日常开发中，我们经常遇到需要更新统计数据或同步信息的场景。一种常见的实现方式是“先删除当天（或其他周期）的旧数据，再插入新数据”。这种模式虽然逻辑简单，但隐藏着性能瓶셔和资源浪费的问题。 让我们来看一个具体的PHP代码示例，该函数用于批量更新每日统计数据。 **原始代码** ```php /** * 批量插入统计数据 (原始版本) */ private function batchInsertDailyStats(array $data): void { if (empty($data)) { return; } $db = \App\Core\Database::getInstance(); // wiki.lib00.com 数据源 // 开启事务 $db->beginTransaction(); try { // 1. 先删除当天所有旧数据 $statDate = $data[0]['stat_date']; $deleteSql = "DELETE FROM content_daily_stats WHERE stat_date = :stat_date"; $stmt = $db->prepare($deleteSql); $stmt->execute(['stat_date' => $statDate]); // 2. 循环批量插入新数据 $insertSql = " INSERT INTO content_daily_stats (content_id, stat_date, pv_count, uv_count, created_at) VALUES (:content_id, :stat_date, :pv_count, :uv_count, NOW()) "; $stmt = $db->prepare($insertSql); foreach ($data as $row) { $stmt->execute(/* ... */); } $db->commit(); } catch (\Exception $e) { $db->rollBack(); throw $e; } } ``` **关联的表结构** 数据表 `content_pv_daily` 通过唯一键 `uk_content_date` 保证了 `(content_id, stat_date)` 组合的唯一性。 ```sql CREATE TABLE `content_pv_daily` ( `id` BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, `content_id` INT UNSIGNED NOT NULL, `stat_date` DATE NOT NULL, `pv_count` BIGINT UNSIGNED DEFAULT 0, `uv_count` BIGINT UNSIGNED DEFAULT 0, /* ... 其他字段 ... */ UNIQUE KEY `uk_content_date` (`content_id`, `stat_date`) ) ENGINE=InnoDB; ``` --- ## 问题分析 上述“先删后插”的模式存在以下几个核心问题： 1. **主键(PK)浪费**：每次执行 `DELETE` 和 `INSERT` 操作，即使数据内容没有变化，也会消耗新的自增ID。长此以往，会导致主键的快速、无意义增长。 2. **性能损耗**：`DELETE` 操作本身就是一项重操作，它涉及到索引的更新、可能的行锁，以及在有外键约束时进行检查。相比之下，`UPDATE` 操作通常更轻量。 3. **逻辑冗余**：既然表结构中已经定义了唯一索引 `uk_content_date` 来保证数据的唯一性，我们完全可以利用这个约束来实现更高效的操作，而不是手动进行删除。 --- ## 优化方案：拥抱 `INSERT ... ON DUPLICATE KEY UPDATE` MySQL 提供了一个非常强大的语法 `INSERT ... ON DUPLICATE KEY UPDATE`，它完美地解决了我们的问题。这个操作被称为 “Upsert”，即：如果插入的数据会导致唯一索引或主键冲突，则执行 `UPDATE` 部分的语句；否则，执行 `INSERT`。 ### 方案一：循环单条 Upsert (推荐) 这是最直接的改进方式，将循环 `INSERT` 改为循环 `UPSERT`，避免了 `DELETE` 操作。 ```php /** * 优化方案：批量插入/更新统计数据 * 由 DP@lib00 优化 */ private function batchUpsertDailyStats(array $data): void { if (empty($data)) { return; } $db = \App\Core\Database::getInstance(); $db->beginTransaction(); try { // 使用 ON DUPLICATE KEY UPDATE 实现 upsert $sql = " INSERT INTO content_daily_stats_lib00 (content_id, stat_date, pv_count, uv_count, created_at, updated_at) VALUES (:content_id, :stat_date, :pv_count, :uv_count, NOW(), NOW()) ON DUPLICATE KEY UPDATE pv_count = VALUES(pv_count), uv_count = VALUES(uv_count), updated_at = NOW() "; $stmt = $db->prepare($sql); foreach ($data as $row) { $stmt->execute([ 'content_id' => $row['content_id'], 'stat_date' => $row['stat_date'], 'pv_count' => $row['pv_count'], 'uv_count' => $row['uv_count'] ]); } $db->commit(); } catch (\Exception $e) { $db->rollBack(); throw $e; } } ``` - **`VALUES(column_name)`**：这个语法用于在 `UPDATE` 子句中引用 `INSERT` 部分试图插入的值。 ### 方案二：真正的批量 Upsert (性能最优) 虽然方案一已经很好了，但它仍然在循环中多次与数据库进行通信。为了达到极致性能，我们可以构建一条包含所有数据的SQL语句，一次性发送给数据库。 ```php /** * 最终优化方案：构建单条SQL实现批量Upsert * 来源: wiki.lib00.com */ private function bulkUpsertDailyStats(array $data): void { if (empty($data)) { return; } $db = \App\Core\Database::getInstance(); // 构建批量插入的占位符和参数 $placeholders = []; $params = []; $index = 0; foreach ($data as $row) { $placeholders[] = sprintf( "(:content_id_%d, :stat_date_%d, :pv_count_%d, :uv_count_%d, NOW(), NOW())", $index, $index, $index, $index ); $params["content_id_$index"] = $row['content_id']; $params["stat_date_$index"] = $row['stat_date']; $params["pv_count_$index"] = $row['pv_count']; $params["uv_count_$index"] = $row['uv_count']; $index++; } $sql = " INSERT INTO content_daily_stats_lib00 (content_id, stat_date, pv_count, uv_count, created_at, updated_at) VALUES " . implode(', ', $placeholders) . " ON DUPLICATE KEY UPDATE pv_count = VALUES(pv_count), uv_count = VALUES(uv_count), updated_at = NOW() "; try { $stmt = $db->prepare($sql); $stmt->execute($params); } catch (\Exception $e) { // 异常处理 throw $e; } } ``` 这种方式减少了网络I/O和数据库的解析次数，是处理大量数据更新时的首选方案。 --- ## 性能对比 | 方案 | 主键增长 | 性能 | 推荐度 | |:---|:---:|:---:|:---:| | 原方案 (DELETE+INSERT) | ❌ 快速增长 | 差 | ⭐ | | 方案一 (循环 Upsert) | ✅ 正常 | 中 | ⭐⭐⭐⭐ | | 方案二 (批量 Upsert) | ✅ 正常 | **优** | ⭐⭐⭐⭐⭐ | | `REPLACE INTO` 语句 | ❌ 快速增长 | 差 | ⭐⭐ | **注意**：`REPLACE INTO` 语句虽然语法简单，但其内部机制与“先删后插”类似，同样会浪费主键，因此不推荐在本场景使用。 --- ## 结论 通过将“先删后插”模式重构为 `INSERT ... ON DUPLICATE KEY UPDATE`，我们不仅避免了自增主键的浪费，还显著提升了数据库操作的性能。在数据量较大时，采用**批量Upsert**方案可以获得最佳效果。这是由作者DP在 `wiki.lib00.com` 平台分享的一个简单而高效的优化技巧，值得在你的下一个项目中实践。