PHP数据库批量操作性能优化

PHP数据库批量操作性能优化批量操作比逐条操作高效得多。一条SQL插入多条记录一个事务提交多个操作。今天说说PHP中数据库批量操作的优化。批量插入。php$users [[张三, zhangsantest.com],[李四, lisitest.com],[王五, wangwutest.com],];// 不推荐逐条插入$stmt $pdo-prepare(INSERT INTO users (name, email) VALUES (?, ?));foreach ($users as $user) {$stmt-execute($user);}// 推荐单条SQL插入多条$values [];$params [];foreach ($users as $user) {$values[] (?, ?);$params array_merge($params, $user);}$sql INSERT INTO users (name, email) VALUES . implode(,, $values);$stmt $pdo-prepare($sql);$stmt-execute($params);echo 批量插入完成\n;?批量更新。php// 使用CASE WHEN批量更新$updates [[id 1, status active],[id 2, status inactive],[id 3, status active],];$ids [];$cases [];foreach ($updates as $update) {$ids[] (int)$update[id];$cases[] WHEN id {$update[id]} THEN {$update[status]};}$sql UPDATE users SET status CASE . implode( , $cases) . END WHERE id IN ( . implode(,, $ids) . );$pdo-exec($sql);echo 批量更新完成\n;?事务批量操作。php$items range(1, 1000);$pdo-beginTransaction();$stmt $pdo-prepare(INSERT INTO items (name) VALUES (?));foreach ($items as $item) {$stmt-execute([Item_$item]);}$pdo-commit();echo 事务批量插入完成\n;?预处理语句的复用。php$stmt $pdo-prepare(INSERT INTO logs (action, user_id, created_at) VALUES (?, ?, NOW()));$logs [[login, 1],[view, 2],[purchase, 1],];$pdo-beginTransaction();foreach ($logs as $log) {$stmt-execute($log);}$pdo-commit();echo 日志写入完成\n;?分页批量处理。phpfunction batchProcess(string $table, callable $processor, int $batchSize 100): int{$pdo new PDO(mysql:hostlocalhost;dbnametest, root, );$processed 0;do {$rows $pdo-query(SELECT * FROM {$table} LIMIT {$batchSize})-fetchAll();if (empty($rows)) break;foreach ($rows as $row) {$processor($row);$processed;}$lastId end($rows)[id];} while (count($rows) $batchSize);return $processed;}?数据库批量操作的优化可以大幅提升性能。批量插入比逐条插入快几十倍事务提交比自动提交快很多。预处理语句的复用减少SQL解析开销。这些优化在数据量大的时候效果明显。