优化10万+商品的WordPress+WooCommerce站点加载速度求助

环境说明 #

• 部署在32GB内存的Nginx云服务器上
• 使用Porto主题+WP Bakery编辑器（测试切换Storefront主题并移除编辑器后，加载速度无变化）
• 用WPML实现多语言（导出WPML字符串到独立语言文件操作繁琐）

当前现状 #

• 未使用缓存/优化工具时，页面加载耗时3-5秒
• 启用WP Rocket+Redis后，缓存命中时加载耗时低于1秒，但缓存未命中（如刚清空缓存）时，用户仍需等待3-5秒完成初始渲染

已尝试的无缓存优化措施 #

• 用Query Monitor（QM）排查影响性能的插件/脚本
• 按教程完成数据库索引优化与清理
• 图片优化

QM显示，最慢的店铺页面中，WP Posts主查询耗时超1秒，数据渲染额外耗时2-4秒。用Scalability PRO插件尝试限制查询执行时间，仅缩短约1秒，优化效果甚微。现寻求非缓存方案的性能优化方法，或提升用户感知速度的方案。

慢查询详情 #

慢查询1：WP_Query->get_posts() #

• 返回行数：12
• 耗时：1.4356秒

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
JOIN wp_icl_translations wpml_translations ON wp_posts.ID = wpml_translations.element_id
AND wpml_translations.element_type = CONCAT('post_', wp_posts.post_type)
WHERE 1=1
  AND (wp_posts.ID NOT IN
         (SELECT object_id
          FROM wp_term_relationships
          WHERE term_taxonomy_id IN (7,
                                     9) ))
  AND ((wp_postmeta.meta_key = '_price'
        AND wp_postmeta.meta_value > '0'))
  AND ((wp_posts.post_type = 'product'
        AND (wp_posts.post_status = 'publish'
             OR wp_posts.post_status = 'private')))
  AND (((wpml_translations.language_code = 'en'
         OR (wpml_translations.language_code = 'en'
             AND wp_posts.post_type IN ('post',
                                        'page',
                                        'custom_css',
                                        'customize_changeset',
                                        'oembed_cache',
                                        'user_request',
                                        'product',
                                        'product_variation',
                                        'shop_order_refund')
             AND ((
                     (SELECT COUNT(element_id)
                      FROM wp_icl_translations
                      WHERE trid = wpml_translations.trid
                        AND language_code = 'en' ) = 0)
                  OR (
                        (SELECT COUNT(element_id)
                         FROM wp_icl_translations t2
                         JOIN wp_posts p ON p.id = t2.element_id
                         WHERE t2.trid = wpml_translations.trid
                           AND t2.language_code = 'en'
                           AND (p.post_status = 'publish'
                                OR p.post_type='attachment'
                                AND p.post_status = 'inherit') ) = 0))))
        AND wp_posts.post_type IN ('post',
                                   'page',
                                   'attachment',
                                   'custom_css',
                                   'customize_changeset',
                                   'oembed_cache',
                                   'user_request',
                                   'wp_block',
                                   'wp_template',
                                   'wp_template_part',
                                   'wp_navigation',
                                   'product',
                                   'product_variation',
                                   'shop_order_refund',
                                   'faq',
                                   'porto_builder'))
       OR wp_posts.post_type NOT IN ('post',
                                     'page',
                                     'attachment',
                                     'custom_css',
                                     'customize_changeset',
                                     'oembed_cache',
                                     'user_request',
                                     'wp_block',
                                     'wp_template',
                                     'wp_template_part',
                                     'wp_navigation',
                                     'product',
                                     'product_variation',
                                     'shop_order_refund',
                                     'faq',
                                     'porto_builder'))
GROUP BY wp_posts.ID
ORDER BY wp_posts.menu_order ASC,
         wp_posts.post_title ASC
LIMIT 0,
      12

慢查询2：_pad_term_counts() #

• 返回行数：247746
• 耗时：1.0853秒

SELECT object_id, term_taxonomy_id
FROM wp_term_relationships
INNER JOIN wp_posts
ON object_id = ID
WHERE term_taxonomy_id IN (180862,181291,181328,181362,181541,182258,182825,180902,181307,181341,181373,181552,182698,181281,181318,181352,181384,181563,182752,180863,181292,181329,181363,181542,182259,182826,180927,181308,181342,181374,181553,182699,180853,181282,181319,181353,181385,181564,182753,180864,181293,181330,181364,181543,182260,182829,180928,181309,181343,181375,181554,182700,180854,181283,181320,181354,181386,181565,182754,180865,181294,181331,181365,181544,182261,183389,180929,181310,181344,181376,181555,182701,180855,181284,181321,181355,181387,181566,182778,180866,181295,181332,181366,181545,182645,183390,180930,181311,181345,181377,181556,182743,180856,181285,181322,181356,181388,181784,182779,180867,181301,181335,181367,181546,182646,183637,181243,181312,181346,181378,181557,182746,180857,181286,181323,181357,181389,181807,182780,180868,181302,181336,181368,181547,182647,184049,181275,181313,181347,181379,181558,182747,180858,181287,181324,181358,181390,182190,182781,180872,181303,181337,181369,181548,182648,208562,181276,181314,181348,181380,181559,182748,180859,181288,181325,181359,181391,182208,182782,180889,181304,181338,181370,181549,182649,208563,181277,181315,181349,181381,181560,182749,180860,181289,181326,181360,181539,182248,182783,180890,181305,181339,181371,181550,182696,181278,181316,181350,181382,181561,182750,180861,181290,181327,181361,181540,182249,182784,180896,181306,181340,181372,181551,182697,181279,181317,181351,181383,181562,182751)
AND post_type IN ('product')
AND post_status = 'publish'

优化建议 #

针对慢查询1的优化 #

1. 简化WPML查询逻辑：当前SQL中WPML的条件嵌套复杂，存在冗余子查询。可以用WPML提供的wpml_object_id函数或调整查询参数，改用JOIN替代IN子查询，去掉多层嵌套的COUNT计算，减少数据库负载。
2. 优化元数据查询：给wp_postmeta添加联合索引(post_id, meta_key, meta_value)，加速价格筛选；同时改用EXISTS替代INNER JOIN，避免因重复元数据触发的分组操作。
3. 移除SQL_CALC_FOUND_ROWS：如果页面不需要显示总页数等统计数据，直接去掉该语句——它会强制数据库扫描全表计算总行数，大幅增加查询耗时。
4. 优化排序索引：添加联合索引(post_type, post_status, menu_order, post_title)，让数据库直接通过索引完成排序，避免文件排序操作。

针对慢查询2的优化 #

1. 禁用实时分类计数填充：该查询由_pad_term_counts触发，用于实时计算分类下的产品数量。如果不需要实时更新计数，可以通过pre_term_count钩子或WooCommerce性能选项禁用该计算，改用缓存的计数结果。
2. 添加联合索引：给wp_term_relationships添加(term_taxonomy_id, object_id)联合索引，加速大集合的IN查询；给wp_posts添加(ID, post_type, post_status)索引，提升JOIN操作效率。

非缓存性能优化 #

1. 数据库层面：
   • 调整MySQL配置，将innodb_buffer_pool_size设置为服务器内存的50%-70%（即16-22GB），提升InnoDB缓存效率
   • 用WP Redis插件将数据库查询结果缓存到Redis，减少重复查询
2. 代码层面：
   • 改用WooCommerce原生的wc_get_products函数替代WP_Query，它针对产品查询做了专门的性能优化
   • 排查QM的Hook面板，移除产品查询后耗时高的不必要钩子回调
   • 将产品属性、价格等动态计算结果缓存到post_meta，避免重复计算
3. 用户感知优化：
   • 实现页面骨架屏，在内容加载完成前显示占位结构，降低用户等待焦虑
   • 对非首屏内容（如侧边栏、推荐产品）采用AJAX异步加载，优先渲染核心内容
   • 启用产品内容懒加载，减少首屏资源加载量

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