Tối ưu truy vấn bình luận WordPress: từ slowlog 8.55s xuống “bay” nhờ bỏ JOIN, cache 2 lớp và index đúng chỗ

Bài này kể lại hành trình kỹ thuật tối ưu truy vấn bình luận từ mysql_slow.log: bắt lỗi do get_comments() mặc định kéo JOIN với wp_posts, tái cấu trúc truy vấn để tận dụng PK comment_ID, bọc cache đúng lớp, xử lý case user_id bằng cache 2 tầng (object cache + user_meta), rồi chốt bằng composite index và lệnh WP-CLI dễ triển khai.

Mục tiêu: lần đầu build thông minh, các lần sau “bay” như gió.

0) Khi website bắt đầu chậm

Với 95.624 thành viên, 74.619 bài viết và hơn 2.281.152 bình luận, website bắt đầu có dấu hiệu chậm thấy rõ. Trang bình luận tải lâu bất thường, truy vấn MySQL ăn CPU, và mysql_slow.log bắt đầu “đỏ rực”. Từ đó, hành trình tối ưu hóa hiệu năng chính thức bắt đầu.

1) Vì sao chậm? — Do get_comments() mặc định “lôi” thêm việc

Nói thẳng: chậm là do get_comments() mặc định kéo thêm ràng buộc không cần thiết, cụ thể là kiểm tra post_status của bài viết nên MySQL phải JOIN wp_posts với wp_comments. Khi dữ liệu lớn, việc này khiến số dòng xét duyệt & sắp xếp phình to.

# Query_time: 8.55s  Rows_sent: 10  Rows_examined: 4,655,080
SELECT wp_comments.comment_ID
FROM wp_comments
JOIN wp_posts ON wp_posts.ID = wp_comments.comment_post_ID
WHERE comment_approved = '1'
  AND comment_type IN ('', 'comment')
  AND wp_posts.post_status IN ('publish')
ORDER BY wp_comments.comment_ID DESC
LIMIT 0,10;

Nhìn kỹ vào log: Rows_sent: 10 nhưng Rows_examined: 4,655,080 — tức là MySQL chỉ trả về 10 kết quả, nhưng để tìm được 10 đó, nó phải dò qua toàn bộ hơn 4,6 triệu bản ghi trong bảng bình luận. Lý do nằm ở chỗ: truy vấn có ORDER BY wp_comments.comment_ID DESC và kèm JOIN với wp_posts.

• Không dùng được index hiệu quả: vì MySQL phải kiểm tra thêm điều kiện từ bảng wp_posts (post_status='publish'), nên nó không thể chỉ dựa vào index PRIMARY (comment_ID) của wp_comments để sort.
• Filesort toàn bảng: khi ORDER BY không tối ưu bằng index, MySQL phải sắp xếp dữ liệu trong bộ nhớ tạm hoặc đĩa (“filesort”), dẫn đến phải quét hầu như toàn bộ các dòng trước khi lọc ra 10 dòng cuối cùng.
• LIMIT không giúp được gì: vì LIMIT chỉ được áp dụng sau khi MySQL đã lọc và sắp xếp xong. Nên dù cần 10 dòng, nó vẫn phải xử lý hàng triệu dòng để xác định 10 dòng đúng thứ tự.

Kết quả là: Query_time 8.55s chỉ để lấy 10 bình luận! Đây là ví dụ điển hình cho vấn đề “ORDER BY + JOIN + LIMIT” mà không có index phù hợp — chỉ cần cắt JOIN và sort trên cột PK comment_ID thôi, thời gian truy vấn giảm hàng chục lần.

Hai vấn đề chính từ mặc định của get_comments():

• JOIN không cần thiết: chỉ để đảm bảo post_status = 'publish'. Với feed bình luận công khai, đa số site có thể bỏ kiểm tra này ở tầng truy vấn để tránh JOIN (hoặc xử lý ở layer khác).
• ORDER BY trên tập rất lớn: sắp xếp theo comment_ID DESC trên hàng triệu dòng mà không có index “đúng pattern” thì MySQL phải quét & filesort.

Định hướng khắc phục: Bỏ điều kiện post_status trong truy vấn bình luận (nếu business cho phép) để tránh JOIN; tận dụng PK comment_ID để sort; kết hợp cache để giảm tần suất truy vấn nặng; thêm composite index nếu cần.

2) Bọc cache cho get_comments(): nhanh – gọn – không phá API

Giữ nguyên API get_comments() nhưng bọc một lớp cache object bền bỉ (khuyến nghị Redis/Memcached).

<?php
/**
 * Lấy danh sách bình luận với cache object.
 *
 * @param array $args Tham số như get_comments().
 * @param int   $ttl  TTL cache (giây). Mặc định 1 giờ.
 * @return array<WP_Comment>
 */
function init_html_get_comments_cached( $args = [], $ttl = HOUR_IN_SECONDS ) {
    $cache_group = 'init_html_get_comments';
    $cache_key   = 'gc_' . md5( maybe_serialize( $args ) );

    $cached = wp_cache_get( $cache_key, $cache_group );
    if ( false !== $cached ) {
        return $cached;
    }

    $comments = get_comments( $args );

    wp_cache_set( $cache_key, $comments, $cache_group, max( 60, (int) $ttl ) );
    return $comments;
}

Cách gọi khuyến nghị (tránh JOIN wp_posts):

$recent_comments = init_html_get_comments_cached([
    'number'                    => 10,
    'status'                    => 'approve',
    'type__in'                  => ['', 'comment'],
    'orderby'                   => 'comment_ID',
    'order'                     => 'DESC',
    'no_found_rows'             => true,
    'update_comment_meta_cache' => false,
    'fields'                    => 'all',
], 15 * MINUTE_IN_SECONDS);

3) “Trùm cuối” theo user_id: cache 2 lớp (object cache + user_meta)

Slowlog khi lọc theo user_id:

# Query_time: 6.30s  Rows_examined: 2,280,035
SELECT vcm_comments.comment_ID
FROM vcm_comments
WHERE (comment_approved = '1')
  AND comment_type IN ('', 'comment')
  AND user_id = 89592
ORDER BY comment_ID DESC
LIMIT 0,50;

Thấy quen không? Tình huống này giống hệt truy vấn ở trên: chỉ muốn lấy 50 bình luận của một người, mà MySQL phải lọc hơn 2,280,035 dòng.
Nguyên nhân vẫn là sự kết hợp giữa ORDER BY comment_ID DESC và việc thiếu index tối ưu theo đúng pattern WHERE user_id = ? AND comment_approved = '1' AND comment_type IN ('', 'comment').
Khi không có index hỗ trợ toàn bộ các cột lọc và sắp xếp, MySQL không thể đi “đúng đường” mà phải quét rất nhiều dòng, sau đó mới sắp xếp rồi cắt LIMIT 50 ở cuối.
Giống hệt bài toán 4,6 triệu dòng ở trên — chỉ khác là lần này, phạm vi bị ràng bởi user_id.

Khi lọc theo user_id, bảng có thể phình rất lớn. Chiến lược: lần đầu build danh sách comment_ID mới nhất (tối đa 100) lưu vào user_meta; lần sau chỉ cần nạp theo ID (cực nhanh). Toàn bộ hàm đã chuẩn hoá prefix init_html_*.

<?php
/**
 * Lấy comments của user với cache 2 lớp (object cache + user_meta)
 */
function init_html_get_user_comments_optimized( $user_id, $limit = 50, $ttl = HOUR_IN_SECONDS ) {
    $user_id = (int) $user_id;
    if ( $user_id <= 0 ) return [];

    $limit = min( $limit, 100 );

    $cache_group = 'init_html_comments';
    $cache_key   = 'user_cmts_' . $user_id . '_' . $limit;
    $cached      = wp_cache_get( $cache_key, $cache_group );
    if ( false !== $cached ) {
        return $cached;
    }

    $comment_ids = get_user_meta( $user_id, '_init_html_comment_ids_cache', true );
    if ( empty( $comment_ids ) || ! is_array( $comment_ids ) ) {
        $comment_ids = init_html_build_user_comment_ids_cache( $user_id );
    }

    $comment_ids = array_slice( (array) $comment_ids, 0, $limit );
    if ( empty( $comment_ids ) ) {
        wp_cache_set( $cache_key, [], $cache_group, $ttl );
        return [];
    }

    $comments = array_filter( array_map( 'get_comment', $comment_ids ) );
    $comments = array_values( $comments );
    wp_cache_set( $cache_key, $comments, $cache_group, $ttl );
    return $comments;
}

/** Build danh sách comment_ID mới nhất (tối đa 100) vào user_meta */
function init_html_build_user_comment_ids_cache( $user_id ) {
    global $wpdb;
    $comment_ids = $wpdb->get_col( $wpdb->prepare(
        "SELECT comment_ID
         FROM {$wpdb->comments}
         WHERE user_id = %d
           AND comment_approved = '1'
           AND comment_type IN ('', 'comment')
         ORDER BY comment_ID DESC
         LIMIT 100",
         $user_id
    ) );
    $comment_ids = array_map( 'intval', (array) $comment_ids );
    update_user_meta( $user_id, '_init_html_comment_ids_cache', $comment_ids );
    return $comment_ids;
}

/** Xoá object cache theo các limit phổ biến */
function init_html_clear_user_comments_object_cache( $user_id ) {
    $cache_group   = 'init_html_comments';
    $common_limits = [10, 20, 50, 100];
    foreach ( $common_limits as $limit ) {
        wp_cache_delete( 'user_cmts_' . (int) $user_id . '_' . $limit, $cache_group );
    }
}

/** Thêm comment vào đầu cache user_meta và clear object cache */
function init_html_add_comment_to_user_cache( $user_id, $comment_id ) {
    $user_id = (int) $user_id;
    if ( $user_id <= 0 ) return;

    $comment_ids = get_user_meta( $user_id, '_init_html_comment_ids_cache', true );
    if ( ! is_array( $comment_ids ) ) $comment_ids = [];

    $comment_ids = array_diff( $comment_ids, [ (int) $comment_id ] );
    array_unshift( $comment_ids, (int) $comment_id );
    $comment_ids = array_slice( $comment_ids, 0, 100 );

    update_user_meta( $user_id, '_init_html_comment_ids_cache', $comment_ids );
    init_html_clear_user_comments_object_cache( $user_id );
}

/** Gỡ comment khỏi cache user_meta và clear object cache */
function init_html_remove_comment_from_user_cache( $user_id, $comment_id ) {
    $user_id = (int) $user_id;
    if ( $user_id <= 0 ) return;

    $comment_ids = get_user_meta( $user_id, '_init_html_comment_ids_cache', true );
    if ( ! is_array( $comment_ids ) ) return;

    $comment_ids = array_values( array_diff( $comment_ids, [ (int) $comment_id ] ) );
    update_user_meta( $user_id, '_init_html_comment_ids_cache', $comment_ids );
    init_html_clear_user_comments_object_cache( $user_id );
}

/** Hooks đồng bộ cache */
add_action( 'comment_post', function( $comment_id, $comment_approved, $commentdata ) {
    if ( $comment_approved != 1 && $comment_approved !== '1' ) return;
    $comment = get_comment( $comment_id );
    if ( ! $comment || ! $comment->user_id ) return;
    if ( in_array( $comment->comment_type, [ 'trackback', 'pingback' ], true ) ) return;
    init_html_add_comment_to_user_cache( $comment->user_id, $comment_id );
}, 10, 3 );

add_action( 'transition_comment_status', function( $new_status, $old_status, $comment ) {
    if ( ! $comment || ! $comment->user_id ) return;
    if ( in_array( $comment->comment_type, [ 'trackback', 'pingback' ], true ) ) return;
    $uid = (int) $comment->user_id;
    if ( $new_status === 'approved' && $old_status !== 'approved' ) {
        init_html_add_comment_to_user_cache( $uid, (int) $comment->comment_ID );
        return;
    }
    if ( $old_status === 'approved' && $new_status !== 'approved' ) {
        init_html_remove_comment_from_user_cache( $uid, (int) $comment->comment_ID );
    }
}, 10, 3 );

add_action( 'deleted_comment', function( $comment_id ) {
    $comment = get_comment( $comment_id );
    if ( ! $comment || ! $comment->user_id ) return;
    init_html_remove_comment_from_user_cache( (int) $comment->user_id, (int) $comment_id );
});

Cách gọi:

$user_comments = init_html_get_user_comments_optimized( get_current_user_id(), 50 );

4) Index đúng pattern WHERE/ORDER + WP-CLI áp dụng

Đề xuất index khớp pattern lọc/sắp xếp thực tế:

• Recent approved comments: WHERE comment_approved='1' AND comment_type IN ('','comment') ORDER BY comment_ID DESC LIMIT N
  ⇒ KEY init_html_cmt_approved_type_id (comment_approved, comment_type, comment_ID)
• Comments theo user: WHERE user_id=? AND comment_approved='1' AND comment_type IN ('','comment') ORDER BY comment_ID DESC LIMIT N
  ⇒ KEY init_html_user_approved_type_id (user_id, comment_approved, comment_type, comment_ID)

WP-CLI:

# MySQL 8.0+: IF NOT EXISTS
wp db query "
  ALTER TABLE wp_comments
  ADD INDEX IF NOT EXISTS init_html_cmt_approved_type_id
  (comment_approved, comment_type, comment_ID);
"

wp db query "
  ALTER TABLE wp_comments
  ADD INDEX IF NOT EXISTS init_html_user_approved_type_id
  (user_id, comment_approved, comment_type, comment_ID);
"

Nếu MySQL < 8.0, kiểm tra trước:

wp db query "SHOW INDEX FROM wp_comments\\G"
# Nếu thiếu mới ALTER TABLE

5) WP-CLI backfill/rebuild cache user

# Rebuild cho một user
wp eval '
require_once ABSPATH . "wp-load.php";
$user_id = (int) $argv[1];
if ($user_id > 0) {
    init_html_build_user_comment_ids_cache($user_id);
    echo "Rebuilt for user {$user_id}\n";
}
' 12345

# Rebuild hàng loạt (ví dụ 1000 user có nhiều comment nhất)
wp eval '
global $wpdb;
$ids = $wpdb->get_col("SELECT user_id FROM wp_comments WHERE user_id > 0 GROUP BY user_id ORDER BY COUNT(*) DESC LIMIT 1000");
foreach ($ids as $uid) {
    init_html_build_user_comment_ids_cache((int)$uid);
    echo "Rebuilt for user {$uid}\n";
}
'

6) Benchmark & lưu ý triển khai

• Lần đầu: build meta cache user (1 query ORDER BY + LIMIT 100, có index hỗ trợ) — nhanh.
• Lần sau: chủ yếu object cache hit & nạp theo ID — vài ms đến vài chục ms.
• Không thể bỏ JOIN? Nếu bắt buộc check post_status='publish', cân nhắc cache dày ở layer app hoặc denormalize cờ vào commentmeta (trade-off đồng bộ).
• Persistent Object Cache (Redis/Memcached) gần như bắt buộc để “cảm” tốc độ.

Kết

Hành trình tối ưu “đúng bài”: cắt JOIN thừa của get_comments(), bọc cache hợp lý, thiết kế cache 2 lớp cho case user_id, rồi chốt bằng composite index khớp WHERE/ORDER. Thực dụng, dễ deploy, và quan trọng nhất: nhanh.