平台开发中,欢迎参与测试。你可以在 QQ群:1046115671 中与我们交流,或是直接在社区发帖。(PS:翻译平台暂不可用,如需参与翻译请仍前往 wp-china.org )

    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
正在查看 0 条回复
  • 哎呀,回复话题必需登录。