Shopware6集成Elasticsearch后全量搜索结果页加载慢问题咨询

问题背景 #

• 运行环境：初始版本为Shopware 6.4.6.1，已升级至6.4.13，集成Elasticsearch作为搜索引擎，站点共约5万件商品，商品关联大量属性配置
• 正常表现：站点首页、商品列表页、商品详情页访问流畅，模糊搜索/AJAX实时搜索响应速度符合预期
• 异常表现：用户按下回车键或点击「显示全部搜索结果」进入搜索结果页时，加载速度极慢，需等待30-40秒才能完成渲染
• 已执行操作：通过Tideways完成性能链路调试，参考官方同类问题工单升级版本后问题仍存在

已采集调试信息 #

• 性能调试截图：
  Tideways 调试界面
• 对应控制器：Shopware\Storefront\Controller\SearchController::search
• 核心调用栈：

#1 PDOStatement::execute
#2 Doctrine\DBAL\Driver\PDOStatement::execute
#3 Doctrine\DBAL\Connection::executeQuery
#4 Doctrine\DBAL\Query\QueryBuilder::execute
#5 Shopware\Core\Framework\DataAbstractionLayer\Dbal\EntityAggregator::fetchAggregation
#6 Shopware\Core\Framework\DataAbstractionLayer\Dbal\EntityAggregator::aggregate
#7 Shopware\Elasticsearch\Framework\DataAbstractionLayer\ElasticsearchEntityAggregator::aggregate
#8 Shopware\Core\System\SalesChannel\Entity\SalesChannelRepository::aggregate
#9 Shopware\Core\Content\Product\SalesChannel\Listing\ProductListingLoader::load
#10 Shopware\Core\Content\Product\SalesChannel\Search\ProductSearchRoute::load
#11 Shopware\Core\Content\Product\SalesChannel\Search\CachedProductSearchRoute::Shopware\Core\Content\Product\SalesChannel\Search\{closure}
#12 Shopware\Core\System\SystemConfig\SystemConfigService::trace
#13 Shopware\Core\Framework\Adapter\Cache\CacheTracer::Shopware\Core\Framework\Adapter\Cache\{closure}
#14 Shopware\Core\Framework\Adapter\Translation\Translator::trace
#15 Shopware\Core\Framework\Adapter\Cache\CacheTracer::Shopware\Core\Framework\Adapter\Cache\{closure}
#16 Shopware\Core\Framework\Adapter\Cache\CacheTagCollection::trace
#17 Shopware\Core\Framework\Adapter\Cache\CacheTracer::trace
#18 Shopware\Storefront\Framework\Cache\CacheTracer::Shopware\Storefront\Framework\Cache\{closure}
#19 Shopware\Storefront\Theme\ThemeConfigValueAccessor::trace
#20 Shopware\Storefront\Framework\Cache\CacheTracer::trace

慢SQL信息 #

慢SQL标记为search*page::aggregation::price，核心逻辑为计算搜索匹配得分、聚合商品价格区间，SQL片段如下：

SELECT
  SUM(
    IF(product.product_number = ?, ?, ?) + IF(product.product_number LIKE ?, ?, ?) + IF(
      IFNULL(
        product.manufacturer_number,
        product.parent.manufacturer_number
      ) = ?,
      ?,
      ?
    ) + IF(
      IFNULL(
        product.manufacturer_number,
        product.parent.manufacturer_number
      ) LIKE ?,
      ?,
      ?
    ) + IF(
      IFNULL(product.ean, product.parent.ean) = ?,
      ?,
      ?
    ) + IF(
      IFNULL(product.ean, product.parent.ean) LIKE ?,
      ?,
      ?
    ) + IF(
      COALESCE(
        product.translation.name,
        product.parent.translation.name
      ) = ?,
      ?,
      ?
    ) + IF(
      COALESCE(
        product.translation.name,
        product.parent.translation.name
      ) LIKE ?,
      ?,
      ?
    ) + IF(
      COALESCE(product.categories.translation.name) = ?,
      ?,
      ?
    ) + IF(
      COALESCE(product.categories.translation.name) LIKE ?,
      ?,
      ?
    )
  ) as _score,
  MIN(
    IFNULL(
      COALESCE(
        (
          ROUND(
            (
              ROUND(
                CAST(
                  (
                    JSON_UNQUOTE(JSON_EXTRACT(product.cheapest_price_accessor, ?)) * ?
                  ) as DECIMAL(?, ?)
                ),
                ?
              )
            ) * ?,
            ?
          ) / ?
        ),
        -- 此处省略15组重复的价格规则计算逻辑
        (
          R

开发模式报错 #

开启开发模式后抛出Elasticsearch 400错误，核心为嵌套对象查询失败：

request.CRITICAL: Uncaught PHP Exception 
Elasticsearch\Common\Exceptions\BadRequest400Exception: "{"error":
{"root_cause":[{"type":"query_shard_exception","reason":"failed to 
create query: [nested] failed to find nested object under path 
[categories]","index_uuid":"w9uwGtZqTW-
Ri1BsVKJtnQ","index":"sw6_product_2fbb5fe2e29a4d70aa5854ce7ce3e20b_16
48715824"}],"type":"search_phase_execution_exception","reason":"all 
shards failed","phase":"query","grouped":true,"failed_shards":
[{"shard":0,"index":"sw6_product_2fbb5fe2e29a4d70aa5854ce7ce3e20b_164
8715824","node":"K3_P7uQSRrOdbjjXdnhFTg","reason":
{"type":"query_shard_exception","reason":"failed to create query: 
[nested] failed to find nested object under path 
[categories]","index_uuid":"w9uwGtZqTW-
Ri1BsVKJtnQ","index":"sw6_product_2fbb5fe2e29a4d70aa5854ce7ce3e20b_16
48715824","caused_by":{"type":"illegal_state_exception","reason":"
[nested] failed to find nested object under path 
[categories]"}}}]},"status":400}"

错误核心：ES查询时尝试读取categories嵌套路径，但对应产品索引不存在该嵌套对象映射。

排查优化方案 #

核心根因判断：AJAX实时搜索响应正常、仅全量结果页慢，结合ES 400报错可确认：全量结果页加载筛选聚合时触发ES嵌套查询错误，Shopware自动降级到MySQL执行全表搜索+多字段评分+价格聚合计算，5万商品量级下这类复杂SQL必然需要几十秒响应时间。

按优先级从高到低执行以下操作：

1. 修复ES索引映射问题
   • 先执行bin/console cache:clear清除系统缓存，再执行bin/console es:index:reset重置索引，最后执行bin/console es:index:populate全量重建索引，重建过程中不要触发前台访问，确保categories字段的nested映射正确写入索引
   • 重建完成后通过ES接口校验产品索引mapping，确认categories字段类型为nested，验证搜索页不再抛出400错误、不再生成对应慢SQL，这一步可解决90%以上的性能问题
2. 裁剪不必要的搜索页聚合
   • 搜索结果页默认会加载所有属性、价格、制造商、分类的筛选聚合，若商品属性量级大，在后台筛选配置中关闭搜索页不需要展示的筛选项，减少聚合计算量
   • 价格区间聚合可提前预生成通用区间缓存，避免每次搜索实时计算
3. 补全MySQL索引兜底
   • 给product表的product_number、manufacturer_number、ean字段加普通索引，给多语言关联表的name字段加索引
   • 若使用MySQL 8.0+版本，给cheapest_price_accessorJSON字段加虚拟列索引，避免全表JSON解析开销
4. 调整缓存策略
   • 给热门搜索词的结果页加HTTP缓存，缓存TTL设置为1-6小时，减少重复查询
   • 开启搜索结果页ESI分片缓存，把不随搜索词变化的区块单独缓存

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