并行SELECT...FOR UPDATE导致MariaDB CPU占用过高问题排查

我正在测试多种无需锁定全表的百万级数据迁移方案，目标是将from_table的数据转移到to_table后标记为已处理。我编写了两个脚本：

• limitedLock.php：采用分批INSERT SELECT的方式处理，单进程运行
• parallel.php：支持多进程并行执行，利用FOR UPDATE SKIP LOCKED实现无锁竞争

测试时分别执行以下命令：

date && limitedLock.php && date
date & php parallel.php & php parallel.php & php parallel.php & wait && date

测试结果出乎意料：两个脚本的总运行时间相近（均为18秒），但运行parallel.php时，MariaDB的CPU占用率直接拉满（top命令显示达到300%）。我不清楚CPU暴涨的原因，也不知道该如何优化。

可复现代码 #

测试数据生成脚本 fixture.php #

<?php
//fixture.php
$createFrom = <<<SQL
CREATE TABLE IF NOT EXISTS from_table(
    id INT NOT NULL AUTO_INCREMENT,
    `state` VARCHAR(10),
    PRIMARY KEY(id),
    KEY state_idx (state)
)
SQL;

$createTo = <<<SQL
CREATE TABLE IF NOT EXISTS to_table(
    id INT,
    PRIMARY KEY(id)
)
SQL;

$conn = new PDO('mysql:host=mariadb;dbname=some_db', 'root', 'root');

$conn->exec('DROP TABLE IF EXISTS from_table');
$conn->exec('DROP TABLE IF EXISTS to_table');
$conn->exec($createFrom);
$conn->exec($createTo);
$values = str_repeat("('to_process'),", 1_000_123);
$insert = 'INSERT INTO from_table (`state`) VALUES ' . rtrim($values, ',');
$conn->exec($insert);

单进程处理脚本 limitedLock.php #

<?php

$conn = new PDO('mysql:host=mariadb;dbname=some_db', 'root', 'root');
$lastId = 0;
$limit = 1000;

do {
    $select = <<<SQL
SELECT id
FROM from_table
WHERE id > {$lastId}
AND state = 'to_process'
ORDER BY id
LIMIT {$limit}
SQL;
    $stmt = $conn->query($select);
    $result = $stmt->fetchAll();
    $ids = array_column($result, 'id');
    if (empty($ids)) break;
    $lastId = end($ids);
    $inClause = implode(',', $ids);

    $insert = <<<SQL
INSERT INTO to_table(id)
SELECT id
FROM from_table
WHERE id IN ({$inClause})
SQL;

    $update = <<<SQL
UPDATE from_table
SET state = 'processed'
WHERE id IN ({$inClause})
SQL;

    $conn->beginTransaction();
    $conn->exec($insert);
    $conn->exec($update);
    $conn->commit();
} while (count($ids) === $limit);

并行处理脚本 parallel.php #

<?php

$lockRows = <<<SQL
CREATE TEMPORARY TABLE ids_to_process
SELECT id
FROM from_table
WHERE state = 'to_process'
ORDER BY id
LIMIT 1000
FOR UPDATE SKIP LOCKED
SQL;

$insert = <<<SQL
INSERT INTO to_table(id)
SELECT ft.id
FROM from_table ft
JOIN ids_to_process itp ON itp.id = ft.id
SQL;

$update = <<<SQL
UPDATE from_table ft
JOIN ids_to_process itp ON itp.id = ft.id 
SET state = 'processed'
SQL;

$conn = new PDO('mysql:host=mariadb;dbname=some_db', 'root', 'root');
$stmt = $conn->query('SELECT id FROM from_table order by id desc limit 1');
$lastId = (int) $stmt->fetch()['id'];

do {
    $conn->exec('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');
    try {
        $conn->beginTransaction();
        $conn->exec($lockRows);
        $conn->exec($insert);
        $conn->exec($update);
        $conn->exec('DROP TEMPORARY TABLE IF EXISTS ids_to_process');
        $conn->commit();
    } catch (Exception $e) {
        $conn->rollBack();
        exit(getmypid() . PHP_EOL . $e->getMessage());
    }

    // 判断是否所有数据已迁移
    $stmt = $conn->query("SELECT id FROM to_table WHERE id = {$lastId} limit 1");
    $result = $stmt->fetch();
} while (empty($result));

问题原因分析 #

1. 低效的索引使用导致全表扫描
   parallel.php中每次循环执行的SELECT id FROM from_table WHERE state = 'to_process' ORDER BY id LIMIT 1000 FOR UPDATE SKIP LOCKED，依赖的state_idx索引基数极低（只有两个可能值），MariaDB优化器会选择全表扫描而非走索引。三个进程同时反复执行全表扫描，直接耗尽CPU资源。

2. 频繁的临时表创建销毁开销
   每次循环都执行CREATE TEMPORARY TABLE和DROP TEMPORARY TABLE，DDL操作本身会消耗大量CPU，多个进程叠加后开销更明显。

3. 终止条件的重复查询
   每个进程每次循环都要查询to_table判断是否完成，虽然是主键查询，但三个进程反复执行也会累积额外的CPU消耗。

优化方案 #

1. 创建高效的联合索引 #

在from_table上创建联合索引idx_state_id (state, id)，让查询可以直接利用索引的有序性，避免全表扫描：

ALTER TABLE from_table ADD INDEX idx_state_id (state, id);

2. 避免循环内的临时表DDL操作 #

将临时表的创建移到循环外，每次循环只清空数据，减少DDL开销：

// 连接初始化时创建临时表
$conn->exec("CREATE TEMPORARY TABLE IF NOT EXISTS ids_to_process (id INT PRIMARY KEY)");

// 循环内只清空数据
$conn->exec('TRUNCATE TABLE ids_to_process');

3. 优化终止判断逻辑 #

使用预编译语句提升查询效率，或者在无数据时短暂休眠避免空轮询：

// 预编译终止查询语句
$checkStmt = $conn->prepare("SELECT id FROM to_table WHERE id = ? limit 1");

// 循环内执行
$checkStmt->execute([$lastId]);
$result = $checkStmt->fetch();

4. 调整事务隔离级别设置时机 #

将SET TRANSACTION ISOLATION LEVEL READ COMMITTED移到连接初始化时，不需要每次循环重复设置。

优化后的parallel.php示例 #

<?php

$conn = new PDO('mysql:host=mariadb;dbname=some_db', 'root', 'root');
// 初始化时设置一次事务隔离级别
$conn->exec('SET TRANSACTION ISOLATION LEVEL READ COMMITTED');

// 提前创建临时表，避免循环内反复DDL
$conn->exec("CREATE TEMPORARY TABLE IF NOT EXISTS ids_to_process (id INT PRIMARY KEY)");

$lockRows = <<<SQL
REPLACE INTO ids_to_process(id)
SELECT id
FROM from_table
WHERE state = 'to_process'
ORDER BY id
LIMIT 1000
FOR UPDATE SKIP LOCKED
SQL;

// 直接从临时表读取数据，无需再关联原表
$insert = <<<SQL
INSERT INTO to_table(id)
SELECT itp.id
FROM ids_to_process itp
SQL;

$update = <<<SQL
UPDATE from_table ft
JOIN ids_to_process itp ON itp.id = ft.id 
SET state = 'processed'
SQL;

$stmt = $conn->query('SELECT id FROM from_table order by id desc limit 1');
$lastId = (int) $stmt->fetch()['id'];

// 预编译终止查询语句
$checkStmt = $conn->prepare("SELECT id FROM to_table WHERE id = ? limit 1");
// 预编译计数语句，判断是否有数据需要处理
$countStmt = $conn->query('SELECT COUNT(*) FROM ids_to_process');

do {
    try {
        $conn->beginTransaction();
        // 清空临时表
        $conn->exec('TRUNCATE TABLE ids_to_process');
        $conn->exec($lockRows);
        
        // 检查是否有数据需要处理
        $countStmt->execute();
        $count = (int)$countStmt->fetch()[0];
        if ($count === 0) {
            $conn->commit();
            usleep(100000); // 无数据时休眠100ms，避免空轮询浪费CPU
            continue;
        }
        
        $conn->exec($insert);
        $conn->exec($update);
        $conn->commit();
    } catch (Exception $e) {
        $conn->rollBack();
        exit(getmypid() . PHP_EOL . $e->getMessage());
    }

    // 检查迁移是否完成
    $checkStmt->execute([$lastId]);
    $result = $checkStmt->fetch();
} while (empty($result));

// 最后销毁临时表
$conn->exec('DROP TEMPORARY TABLE IF EXISTS ids_to_process');

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