WooCommerce多商户站点优化商家登录态下的产品列表打开速度

[孙锡源]

问题解析

WordPress默认会在拥有编辑权限的用户登录时展示状态为“publish”和“private”的文章，于是这就出现了一个SQL的OR查询……这是大数据量下的大忌。于是在litepress.cn的应用市场7万商品的场景下，一次查询耗时3秒之多，非常影响用户体验。

解决方案就是强制只索引状态为“publish”的产品。

导致缓慢的SQL：

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_wc_product_meta_lookup wc_product_meta_lookup
ON wp_posts.ID = wc_product_meta_lookup.product_id
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (7) )
AND wp_term_relationships.term_taxonomy_id IN (15,79962,80003) )
AND wp_posts.post_type = 'product'
AND (wp_posts.post_status = 'publish'
OR wp_posts.post_status = 'private')
GROUP BY wp_posts.ID
ORDER BY wc_product_meta_lookup.total_sales DESC, wc_product_meta_lookup.product_id DESC
LIMIT 0, 12

虽说解决方案说起来很简单，但是WordPress硬编码了一个很奇怪的join查询——它会在当前WP_Query存在post_status设置且处于分类档案页面时生成以下SQL：

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
LEFT JOIN wp_posts AS p2
ON (wp_3_posts.post_parent = p2.ID)
LEFT JOIN wp_wc_product_meta_lookup wc_product_meta_lookup
ON wp_posts.ID = wc_product_meta_lookup.product_id
WHERE 1=1
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (7) )
AND wp_term_relationships.term_taxonomy_id IN (15,79962,80003) )
AND wp_posts.post_type = 'product'
AND (((wp_posts.post_status = 'publish')
OR (wp_posts.post_status = 'inherit'
AND (p2.post_status = 'publish'))))
GROUP BY wp_posts.ID
ORDER BY wc_product_meta_lookup.total_sales DESC, wc_product_meta_lookup.product_id DESC
LIMIT 0, 12

这货就比较厉害了，直接让SQL执行时间翻倍，飞上了6秒……

并且前面提到，这个新SQL的生成逻辑是被硬编码到WordPress核心代码里的，所以想修改就比较麻烦。

完整的解决方案

添加以下代码到主题的functions.php，这里强调下：只有数据量足够大（至少5万商品）的情况下才能看到优化效果，小负载的网站其实加不加都行。

if ( ! is_admin() ) {
       add_action( 'pre_get_posts', function ( WP_Query $q ) {
              if ( ! $q->is_tax( get_object_taxonomies( 'product' ) ) ) {
                     return;
              }

              $q->set( 'post_status', 'publish' );
       }, 9999 );

       add_filter( 'posts_where', function ( $where, $q ) {
              if ( ! $q->is_tax( get_object_taxonomies( 'product' ) ) ) {
                     return $where;
              }

              return str_replace( "(((wp_posts.post_status = 'publish') OR (wp_posts.post_status = 'inherit' AND (p2.post_status = 'publish'))))", "wp_posts.post_status = 'publish'", $where );
       }, 9999, 2 );

       add_filter( 'posts_join', function ( $join, $q ) {
              if ( ! $q->is_tax( get_object_taxonomies( 'product' ) ) ) {
                     return $join;
              }

              return 'LEFT JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id)';
       }, 9999, 2 );
}

优化后的效果

商家在前台获取产品列表的SQL执行时间从3秒降到1秒，但老实说还是很慢，仍需优化调整。

标签： 性能优化

#20149