千万规格商品搜索优化方案

Hi，我是阿昌，今天记录一个电商商品搜索的性能优化案例。

背景是螺丝品类：**商品 5w+，规格（SKU）2000w+**，在后台管理界面做商品搜索时响应超时。

数据量大 + 多表联查 + 联合 LIKE → 扫描行数爆炸。

一、问题定位

核心搜索 SQL 长这样（简化版）：

SELECT i.num_iid
FROM item i
  LEFT JOIN sku s ON i.num_iid = s.num_iid
  LEFT JOIN relation b ON b.sku_id = s.sku_id AND b.num_iid = s.num_iid
  LEFT JOIN sys_sku ss ON ss.id = b.sys_sku_id AND ss.sys_item_id = b.sys_item_id
WHERE i.user_id = 123456
  AND i.approve_status = 'onsale'
  AND i.platform IN ('fxg')
  AND i.seller_id IN (60+ 个店铺 ID)
  AND (
    CONCAT_WS('@', REPLACE(s.title,' ',''), REPLACE(ss.sys_item_alias,' ',''), i.outer_id)
      LIKE CONCAT('%', '搜索关键词', '%')
    OR s.num_iid = '搜索关键词'
  )
GROUP BY i.num_iid
ORDER BY i.id DESC
LIMIT 0, 10;

一眼就能看出问题：

4 表 JOIN：item、sku、relation、sys_sku，数据量级层层放大；
**CONCAT_WS + REPLACE + LIKE ‘%keyword%’**：完全无法走索引，每一行都要做字符串拼接后再模糊匹配；
60+ seller_id IN 条件：过滤条件多且分散；
大商家规格量巨大：一个店铺可能有几万商品、几十万 SKU，零库存规格占比还很高。

EXPLAIN 出来的结果：扫描行数几十万级，Using where; Using temporary; Using filesort，超时是必然的。

二、方案探索

沿着”能不能在现有架构上改”到”要不要换架构”这条线，陆续试了几个方向。

方案一：分店铺查询聚合（❌）

思路是把大商家的多店铺一次查询，拆成按单个店铺循环查，最后在应用层聚合。

-- 拆成单店铺查询
WHERE i.user_id = 123456
  AND i.seller_id = 777   -- 单个店铺
  AND (... LIKE ...)

结果：单店 4.4s，60 个店铺串行下来更慢。IOPS 次数反而增多，总数据量等价，耗时更长。

方案二：MySQL 规格升级（❌）

把 MySQL 实例从低配升到 16c32g，提升 IOPS。

结果是查询 timeout 从硬超时变成 30s 内能返回，但做不到 5s 以内。用户体验仍然差，成本还高，ROI 不行。

方案三：MySQL → PG 直迁（⚠️ 不彻底）

把同样的表结构和查询逻辑平移到 PostgreSQL。

单表 LIKE 查询：PG 原生性能好很多，3s 内能返回；
但 4 表 JOIN + LIKE 的场景：PG 一样扛不住，30s 左右。

结论：只换数据库不换数据模型，解决不了根本问题。

方案四：MySQL 打平大宽表 + DTS → PG（✅ 选定）

核心思路：

在 MySQL 侧把 item、sku、relation、sys_sku 打成一张大宽表（join_item_sku）；
通过 DTS 实时同步到 PG；
在 PG 上对搜索字段建 GIN 三元组索引（pg_trgm）；
搜索服务查 PG 拿到 num_iid 列表，再回 MySQL 查完整商品信息。

-- PG 上的 GIN 索引
CREATE INDEX trgm_idx_join_item_sku ON join_item_sku
  USING gin (
    title gin_trgm_ops,
    sys_item_alias gin_trgm_ops,
    sys_sku_name gin_trgm_ops,
    sku_outer_id gin_trgm_ops,
    sku_name gin_trgm_ops
  );

数据量	搜索耗时
最大用户 1163w 行	7s 内（组合条件 ≥ 4 个时）
1000w 规格明细	平均 6s（DISTINCT）

方案五：大宽表 + ES（🤔 备选）

理论上是搜索性能最好的方案，但：

需要额外维护 ES 集群，成本高；
团队对 ES 的运维经验不足；
DTS 同步到 ES 的链路也需要额外搭建。

当前阶段不选，但作为后续演进方向保留。

三、方案对比总览

方案	优点	缺点
MySQL 当前逻辑	简单，现有模式	数据量上去就超时
分店铺查询聚合	业务改造小	IOPS 多，总耗时反而更长
MySQL 升配 16c32g	改动最小	做不到 5s 内，成本高
MySQL → PG 直迁	迁移简单	多表 JOIN 依然慢（30s）
大宽表 + DTS → PG	宽表查询快，GIN 索引效果好	维护成本增加，同步有延迟
大宽表 + ES	搜索性能最优	成本高，运维门槛高

四、最终架构

选了大宽表 + PG 这条路，架构如下：

                   ┌──────────────────┐
                   │   搜索服务 (PG)    │
                   │  GIN 索引加速 LIKE │
                   └────────┬─────────┘
                            │ num_iid 列表
                   ┌────────▼─────────┐
                   │   商品服务 (MySQL) │
                   │  回表查完整信息    │
                   └──────────────────┘

MySQL 写入 ──DTS──► PG 大宽表 (join_item_sku)
                        │
                        ├── btree 索引: user_id, seller_id, platform
                        └── GIN 索引: title, sku_name, sys_item_alias 等搜索字段

两个关键设计：

PG 只存搜索需要的字段，不做完整数据存储，降低宽表宽度和同步延迟；
num_iid 回表 MySQL：PG 返回命中的商品 ID 列表，前端展示的完整信息仍从 MySQL 拿，保证数据一致性。

六、总结

这个案例的核心教训是：数据量大到一定程度后，换数据库解决不了模型问题。

MySQL 直迁 PG 的尝试就是个典型——同样的 JOIN + LIKE 模型，PG 只是从”超时”变成了”30 秒超时”，没有质的改变。

真正起作用的改动是把多表 JOIN 打成大宽表，然后利用 PG 的 pg_trgm GIN 索引加速模糊搜索。这一步把查询模式从”运行时 JOIN + 计算”变成了”写入时 JOIN + 查询时走索引”。

用写入成本换查询性能，在搜索场景下是划算的——毕竟写入只有一次，查询却有无数次。

把运行时算力，前置到写入时。

以上就是这次千万规格商品搜索优化的完整方案，感谢观看；