Ubuntu导入程序执行附件查询SQL时CPU占满 优化reset_attachments函数

Ubuntu服务器运行导入程序，执行查询附件ID并排队删除的SQL操作时，CPU被打满至100%、服务器过载，导致网站宕机。排查根因的流程如下：

• 执行top -i找到CPU占用持续超过90%的异常进程
• 使用pidstat -t -p {PROCESS_ID} 1命令查看该高占用进程的详情
• 停止pidstat追踪后，获取mysqld进程对应的TID，在MySQL命令行执行如下语句查询对应线程信息：

mysql> select * from performance_schema.threads where THREAD_OS_ID = {PROCESS_ID} \G
*************************** 1. row ***************************
     THREAD_ID: 61
        NAME: thread/sql/one_connection
        TYPE: FOREGROUND
   PROCESSLIST_ID: 36
  PROCESSLIST_USER: {USER}
  PROCESSLIST_HOST: localhost
   PROCESSLIST_DB: {DB_NAME}
PROCESSLIST_COMMAND: Query
  PROCESSLIST_TIME: 0
 PROCESSLIST_STATE: Sending data
  PROCESSLIST_INFO: SELECT ID
            FROM wp_posts
            WHERE post_title = 'https://med05.example.co.uk/in4glestates/{SERIAL_NUMBER}/{SERIAL_NUMBER}/main/LOGO-MA-Roof-Seating.jpg'
            AND post_type = 'attachment'
  PARENT_THREAD_ID: NULL
        ROLE: NULL
    INSTRUMENTED: YES
      HISTORY: YES
  CONNECTION_TYPE: Socket
    THREAD_OS_ID: 8189
1 row in set (0.00 sec)

通过上述查询确认引发高CPU负载的问题SQL，结合返回结果中的PROCESSLIST_INFO字段判断，高负载由如下代码块触发：

private static function reset_attachments($new_property)
{
    global $wpdb;
    $sql = "SELECT ID FROM {$wpdb->prefix}posts WHERE post_parent = {$new_property} AND post_type='attachment'";
    $res = $wpdb->get_results($sql);
    foreach($res as $row) {
         wp_delete_attachment($row->ID, true);
    }
    return null;
}

补充说明：排查中观察到的PROCESSLIST_STATE: Sending data状态，代表MySQL线程已进入结果读取/传输阶段，该状态下通常伴随全表扫描、磁盘IO、内存拷贝等重资源操作，是数据库负载过高的典型特征。

• 该函数运行时为何会占用极高的CPU资源？
• 有没有性能更优的reset_attachments()函数编写方式？
• 如何修改reset_attachments()函数，采用分批错峰的方式处理查询与删除操作？可接受更长的总执行时长，只要能有效降低CPU负载即可。

1. SQL无索引触发全表扫描：WordPress默认的wp_posts表索引仅覆盖post_type、post_status、post_date等核心字段，post_parent、post_title字段默认没有建立索引。当库内附件量达到数万/数十万级别时，按post_parent + post_type查询附件ID的语句会逐行遍历全表做匹配，单次查询就会把CPU占满，这是故障的核心诱因。排查中捕获的按post_title查询附件的语句存在完全相同的问题。
2. 删除逻辑无节流触发请求风暴：wp_delete_attachment($id, true)是WordPress的重量级函数，单次调用会连带删除附件对应的元数据、评论、关联物理文件、修订版本、分类关系等数据，默认会触发10~30次额外的SQL查询。原有逻辑一次性拉取所有符合条件的附件ID无间隔循环删除，短时间内会堆积数千条SQL执行请求，直接打满数据库CPU和连接资源。
3. 大结果集额外开销：原有逻辑一次性把所有附件ID加载到PHP内存，结果集传输、内存拷贝的过程也会额外消耗CPU和内存资源，进一步放大负载。

基础必做优化 #

首先从数据库层解决全表扫描问题，业务低峰期给wp_posts表加联合索引，执行前务必备份表数据：

ALTER TABLE wp_posts ADD INDEX idx_posttype_postparent (post_type, post_parent);
-- 如果业务中频繁按post_title查询附件，可额外加前缀索引
-- ALTER TABLE wp_posts ADD INDEX idx_posttitle (post_title(191));

加完索引后，按post_type + post_parent的查询会直接走索引检索，单次查询耗时从数秒/数十秒降到毫秒级，CPU占用会下降90%以上。

分批错峰版本实现 #

以下优化版本通过小批量拉取、操作间隔休眠、临时禁用非必要计数逻辑的方式，把负载峰值压到最低，总执行时长会变长，但不会出现CPU打满的情况：

private static function reset_attachments($new_property, $batch_size = 20, $sleep_us = 50000)
{
    global $wpdb;
    $offset = 0;

    do {
        // 每次只拉取指定批次数量的附件ID，避免大结果集开销
        $sql = $wpdb->prepare(
            "SELECT ID FROM {$wpdb->prefix}posts 
            WHERE post_parent = %d AND post_type = 'attachment' 
            LIMIT %d OFFSET %d",
            $new_property,
            $batch_size,
            $offset
        );
        $list = $wpdb->get_results($sql);

        if (empty($list)) {
            break;
        }

        foreach ($list as $item) {
            // 临时禁用非必要的计数、缓存刷新逻辑，减少单次删除的SQL开销
            wp_defer_term_counting(true);
            wp_defer_comment_counting(true);
            wp_suspend_cache_invalidation(true);

            wp_delete_attachment($item->ID, true);

            wp_suspend_cache_invalidation(false);
            wp_defer_comment_counting(false);
            wp_defer_term_counting(false);

            // 单条删除后休眠，默认50毫秒，主动让出CPU资源给前台业务
            usleep($sleep_us);
        }

        $offset += $batch_size;
        // 每批次处理完后额外休眠200毫秒，削平负载峰值
        usleep(200000);
        // 重置运行时缓存，避免内存持续占用
        wp_cache_flush_runtime();

    } while (!empty($list));

    return null;
}

参数调整规则 #

• $batch_size：单批处理的附件数量，服务器配置越低、业务访问量越高，该值应设置得越小，最低可设为5，最高不建议超过50
• $sleep_us：单条删除后的休眠时长，单位为微秒（1秒=1000000微秒），默认值为50000即0.05秒，如果运行时CPU仍然偏高，可调整为100000即0.1秒

内容的提问来源于stack exchange，提问作者Jason Is My Name