WordPress/WooCommerce查询优化求助：解读EXPLAIN并指导提速

原查询语句 #

SELECT
    p.ID AS order_id
    ,DATE(p.post_date) AS order_date
    ,SUBSTR(comment_content,17) AS csr
    ,SUBSTR(p.post_status,4) AS order_status
    ,UCASE(CONCAT((SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE meta_key = '_billing_first_name' and wp_postmeta.post_id = p.ID),' ',(SELECT wp_postmeta.meta_value FROM wp_postmeta WHERE meta_key = '_billing_last_name' and wp_postmeta.post_id = p.ID))) AS customer
    ,(SELECT GROUP_CONCAT(DISTINCT order_item_name ORDER BY order_item_name ASC SEPARATOR ', ') FROM wp_woocommerce_order_items WHERE order_id = p.ID AND order_item_type = 'line_item' GROUP BY order_id) AS products
    ,(SELECT GROUP_CONCAT(CONCAT(serial_number,'',serial_feature_code)) FROM wp_custom_serial WHERE wp_custom_serial.order_id = p.ID GROUP BY wp_custom_serial.order_id) AS serials 
FROM
    wp_posts AS p
    INNER JOIN wp_comments AS c ON p.ID = c.comment_post_ID
    INNER JOIN wp_postmeta AS pm ON p.ID = pm.post_id
    
WHERE
    p.post_type = 'shop_order'
    AND comment_content LIKE 'Order placed by%'
GROUP BY p.ID
ORDER BY SUBSTR(comment_content,17) ASC, p.post_date DESC;

问题需求 #

需要优化上述查询，同时理解EXPLAIN输出中的性能瓶颈，明确问题点和优化方向。

EXPLAIN输出结果 #

EXPLAIN结果中的问题点解析 #

1. wp_comments表全表扫描

   • type列显示为ALL，说明执行了全表扫描，未使用possible_keys中的comment_post_ID索引。
   • Extra列的Using temporary和Using filesort：临时表和文件排序会大幅拖慢性能，这是因为GROUP BY p.ID和ORDER BY SUBSTR(comment_content,17)需要额外计算和排序逻辑。

2. wp_custom_serial表无索引支持

   • type列是ALL且possible_keys为NULL，说明该表没有针对order_id的索引，每次子查询都要扫描5160行数据。
   • Extra列的Using filesort：GROUP_CONCAT需要排序，没有索引支撑只能依赖文件排序。

3. 重复的wp_postmeta子查询

   • 两个独立子查询分别获取_billing_first_name和_billing_last_name，每个订单都会触发两次wp_postmeta查询，额外增加了查询开销。

4. 无效的wp_postmeta关联

   • 主查询中INNER JOIN wp_postmeta AS pm但未用到该表的任何字段，属于无效关联，会额外增加数据读取量。

优化方向与解决办法 #

1. 给wp_comments添加复合索引

   • 创建包含comment_post_ID和comment_content的复合索引，同时针对LIKE前缀查询添加前缀索引：

     CREATE INDEX idx_comment_post_content ON wp_comments(comment_post_ID, comment_content);
     CREATE INDEX idx_comment_content_prefix ON wp_comments(comment_content(20));
     

   • 这能避免全表扫描，同时消除Using temporary和Using filesort。

2. 给wp_custom_serial添加覆盖索引

   • 针对order_id创建索引，同时包含查询用到的字段，减少回表操作：

     CREATE INDEX idx_custom_serial_order ON wp_custom_serial(order_id, serial_number, serial_feature_code);
     

   • 这样子查询可以直接通过索引获取数据，避免全表扫描和文件排序。

3. 重构子查询为JOIN，减少重复查询

   • 将获取客户姓名的子查询改成LEFT JOIN，一次性聚合订单商品和序列号数据：

     SELECT
         p.ID AS order_id
         ,DATE(p.post_date) AS order_date
         ,SUBSTR(c.comment_content,17) AS csr
         ,SUBSTR(p.post_status,4) AS order_status
         ,UCASE(CONCAT(billing_first.meta_value, ' ', billing_last.meta_value)) AS customer
         ,products.product_list AS products
         ,serials.serial_list AS serials
     FROM wp_posts AS p
     INNER JOIN wp_comments AS c ON p.ID = c.comment_post_ID
     LEFT JOIN wp_postmeta AS billing_first ON p.ID = billing_first.post_id AND billing_first.meta_key = '_billing_first_name'
     LEFT JOIN wp_postmeta AS billing_last ON p.ID = billing_last.post_id AND billing_last.meta_key = '_billing_last_name'
     LEFT JOIN (
         SELECT order_id, GROUP_CONCAT(DISTINCT order_item_name ORDER BY order_item_name ASC SEPARATOR ', ') AS product_list
         FROM wp_woocommerce_order_items
         WHERE order_item_type = 'line_item'
         GROUP BY order_id
     ) AS products ON p.ID = products.order_id
     LEFT JOIN (
         SELECT order_id, GROUP_CONCAT(CONCAT(serial_number, '', serial_feature_code)) AS serial_list
         FROM wp_custom_serial
         GROUP BY order_id
     ) AS serials ON p.ID = serials.order_id
     WHERE p.post_type = 'shop_order'
       AND c.comment_content LIKE 'Order placed by%'
     ORDER BY SUBSTR(c.comment_content,17) ASC, p.post_date DESC;
     

4. 移除无效关联

   • 直接删除主查询中INNER JOIN wp_postmeta AS pm的关联，减少不必要的数据读取。

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