WooCommerce后台订单列表自定义列引发慢请求问题排查

你的代码导致慢请求的核心原因是重复且低效的数据库查询，具体问题如下：

• 订单列表每一行都会触发这个函数，而你在同一个列判断里写了3个独立的if块，每个块里又做了1-2次get_posts查询，一页20条订单的话，就会产生20*(1+1+2)=80次数据库请求，而且每次get_posts都会拉取所有匹配的订单数据（哪怕你只需要计数），极大消耗数据库和内存资源。
• 重复获取同一个订单的手机号，三个if块里都重新调用get_post_meta拿_billing_phone，完全没必要。
• 重复判断$column === 'order_count'，三个if都是同一个条件，属于冗余代码。

把所有查询合并，用高效的方式获取计数，减少数据库请求次数：

add_action('manage_shop_order_posts_custom_column', 'display_order_count_column');
function display_order_count_column($column) {
    global $post;

    if ($column !== 'order_count') {
        return; // 不是目标列直接返回，减少后续判断
    }

    // 只获取一次手机号并处理
    $customer_phone = get_post_meta($post->ID, '_billing_phone', true);
    $customer_phone = str_replace(' ', '', $customer_phone);

    if (empty($customer_phone)) {
        return;
    }

    // 用WP_Query直接获取各状态的订单数，只查计数不加载帖子对象
    // 合并查询，一次获取多个状态组的计数
    $completed_rdy_query = new WP_Query(array(
        'post_type'      => 'shop_order',
        'post_status'    => array('wc-completed', 'wc-order-rdy'),
        'meta_key'       => '_billing_phone',
        'meta_value'     => $customer_phone,
        'posts_per_page' => 1, // 只需要计数，不用拉取所有
        'fields'         => 'ids', // 只返回ID，减少数据传输
        'no_found_rows'  => false, // 开启found_posts计数
    ));
    $completed_rdy_count = $completed_rdy_query->found_posts;

    $processing_hold_rdy_query = new WP_Query(array(
        'post_type'      => 'shop_order',
        'post_status'    => array('wc-on-hold', 'wc-processing', 'wc-order-rdy'),
        'meta_key'       => '_billing_phone',
        'meta_value'     => $customer_phone,
        'posts_per_page' => 1,
        'fields'         => 'ids',
        'no_found_rows'  => false,
    ));
    $processing_hold_rdy_count = $processing_hold_rdy_query->found_posts;

    $completed_processing_rdy_query = new WP_Query(array(
        'post_type'      => 'shop_order',
        'post_status'    => array('wc-completed', 'wc-processing', 'wc-order-rdy'),
        'meta_key'       => '_billing_phone',
        'meta_value'     => $customer_phone,
        'posts_per_page' => 1,
        'fields'         => 'ids',
        'no_found_rows'  => false,
    ));
    $completed_processing_rdy_count = $completed_processing_rdy_query->found_posts;

    $cancelled_query = new WP_Query(array(
        'post_type'      => 'shop_order',
        'post_status'    => 'wc-cancelleddonttake',
        'meta_key'       => '_billing_phone',
        'meta_value'     => $customer_phone,
        'posts_per_page' => 1,
        'fields'         => 'ids',
        'no_found_rows'  => false,
    ));
    $cancelled_count = $cancelled_query->found_posts;

    // 重置查询，避免影响后续WP查询
    wp_reset_postdata();

    // 输出对应内容
    if ($completed_rdy_count >= 2) {
        $url = admin_url("edit.php?s={$customer_phone}&post_status=all&post_type=shop_order&action=-1&m=0&_customer_user&paged=1&action2=-1");
        echo "<a href='{$url}' class='Button_all_status' title='Числото показва броя на успешно завършените поръчки, които клиента има.'>{$completed_rdy_count}</a>";
    }

    if ($processing_hold_rdy_count >= 2) {
        $url = admin_url("edit.php?s={$customer_phone}&post_status=all&post_type=shop_order&action=-1&m=0&_customer_user&paged=1&action2=-1");
        echo "<a href='{$url}' class='Button_in_process' title='Числото показва броя на поръчките, които клиента има в обработка.'>{$processing_hold_rdy_count}</a>";
    }

    if ($cancelled_count > $completed_processing_rdy_count) {
        $url = admin_url("edit.php?s={$customer_phone}&post_status=all&post_type=shop_order&action=-1&m=0&_customer_user&paged=1&action2=-1");
        echo "<a href='{$url}' class='Button_bad_client' title='Рисков клиент. Моля, проверете поръчките на клиента.Клиента има повече провалени от колкото изпълнени поръчки'>{$cancelled_count}</a>";
    }
}

如果想要更极致的性能，可以直接用自定义SQL查询，把4次查询合并成1次，一次性获取所有需要的计数：

add_action('manage_shop_order_posts_custom_column', 'display_order_count_column');
function display_order_count_column($column) {
    global $post, $wpdb;

    if ($column !== 'order_count') {
        return;
    }

    $customer_phone = get_post_meta($post->ID, '_billing_phone', true);
    $customer_phone = str_replace(' ', '', $customer_phone);

    if (empty($customer_phone)) {
        return;
    }

    // 自定义SQL一次获取所有计数
    $sql = $wpdb->prepare("
        SELECT
            SUM(CASE WHEN post_status IN ('wc-completed', 'wc-order-rdy') THEN 1 ELSE 0 END) AS completed_rdy_count,
            SUM(CASE WHEN post_status IN ('wc-on-hold', 'wc-processing', 'wc-order-rdy') THEN 1 ELSE 0 END) AS processing_hold_rdy_count,
            SUM(CASE WHEN post_status IN ('wc-completed', 'wc-processing', 'wc-order-rdy') THEN 1 ELSE 0 END) AS completed_processing_rdy_count,
            SUM(CASE WHEN post_status = 'wc-cancelleddonttake' THEN 1 ELSE 0 END) AS cancelled_count
        FROM {$wpdb->posts} p
        JOIN {$wpdb->postmeta} pm ON p.ID = pm.post_id
        WHERE p.post_type = 'shop_order'
        AND pm.meta_key = '_billing_phone'
        AND pm.meta_value = %s
    ", $customer_phone);

    $counts = $wpdb->get_row($sql);

    // 输出内容
    if ($counts->completed_rdy_count >= 2) {
        $url = admin_url("edit.php?s={$customer_phone}&post_status=all&post_type=shop_order&action=-1&m=0&_customer_user&paged=1&action2=-1");
        echo "<a href='{$url}' class='Button_all_status' title='Числото показва броя на успешно завършените поръчки, които клиента има.'>{$counts->completed_rdy_count}</a>";
    }

    if ($counts->processing_hold_rdy_count >= 2) {
        $url = admin_url("edit.php?s={$customer_phone}&post_status=all&post_type=shop_order&action=-1&m=0&_customer_user&paged=1&action2=-1");
        echo "<a href='{$url}' class='Button_in_process' title='Числото показва броя на поръчките, които клиента има в обработка.'>{$counts->processing_hold_rdy_count}</a>";
    }

    if ($counts->cancelled_count > $counts->completed_processing_rdy_count) {
        $url = admin_url("edit.php?s={$customer_phone}&post_status=all&post_type=shop_order&action=-1&m=0&_customer_user&paged=1&action2=-1");
        echo "<a href='{$url}' class='Button_bad_client' title='Рисков клиент. Моля, проверете поръчките на клиента.Клиента има повече провалени от колкото изпълнени поръчки'>{$counts->cancelled_count}</a>";
    }
}

这个版本把每个订单行的数据库请求从5次降到1次，性能提升非常明显。

内容的提问来源于stack exchange，提问作者Ивайло Маринов