含REPLACE函数的MySQL慢查询性能优化求助

本地环境下某MySQL查询执行时间超过1分钟，但移除子查询中的REPLACE函数部分后，耗时不足1秒。

原查询语句 #

SELECT s0_.sku                                                                                           AS sku_2,
       (SELECT count(s1_.id) AS sclr_45
        FROM store_product s1_
        WHERE s1_.sku IS NOT NULL AND s1_.sku <> '' AND s1_.store_id = s0_.store_id AND s1_.sku = s0_.sku
           OR s1_.sku = REPLACE(REPLACE(REPLACE(REPLACE(s1_.sku, '-', ''), '.', ''), '/', ''), ' ', '')) AS sclr_44
FROM store_product s0_
WHERE s0_.store_id = 5
GROUP BY s0_.id
HAVING sclr_44 > 5
ORDER BY s0_.sku ASC

问题核心 #

性能瓶颈出在子查询的OR条件中：s1_.sku = REPLACE(REPLACE(REPLACE(REPLACE(s1_.sku, '-', ''), '.', ''), '/', ''), ' ', '')。需要通过REPLACE匹配格式不同但实质重复的SKU（如111与11.1），但多层REPLACE导致索引失效，触发全表扫描，拖慢查询速度。

执行计划 #

优化方案 #

方案1：新增标准化SKU字段并建立索引 #

• 给store_product表新增normalized_sku字段，存储去掉特殊字符后的标准化SKU：

ALTER TABLE store_product ADD COLUMN normalized_sku VARCHAR(255);

• 批量初始化字段值：

UPDATE store_product 
SET normalized_sku = REPLACE(REPLACE(REPLACE(REPLACE(sku, '-', ''), '.', ''), '/', ''), ' ', '')
WHERE sku IS NOT NULL AND sku <> '';

• 建立store_id与normalized_sku的联合索引：

CREATE INDEX idx_store_normalized_sku ON store_product(store_id, normalized_sku);

• 修改查询语句，直接使用标准化字段匹配：

SELECT s0_.sku AS sku_2,
       (SELECT count(s1_.id) AS sclr_45
        FROM store_product s1_
        WHERE s1_.store_id = s0_.store_id 
          AND s1_.sku IS NOT NULL AND s1_.sku <> ''
          AND (s1_.sku = s0_.sku OR s1_.normalized_sku = s0_.normalized_sku)) AS sclr_44
FROM store_product s0_
WHERE s0_.store_id = 5
GROUP BY s0_.id
HAVING sclr_44 > 5
ORDER BY s0_.sku ASC;

• 后续新增/更新数据时，通过触发器或业务逻辑自动同步normalized_sku的值，保证数据一致性。

方案2：使用持久化生成列（MySQL 5.7+） #

无需手动维护标准化字段，让数据库自动生成并存储：

ALTER TABLE store_product 
ADD COLUMN normalized_sku VARCHAR(255) 
GENERATED ALWAYS AS (REPLACE(REPLACE(REPLACE(REPLACE(sku, '-', ''), '.', ''), '/', ''), ' ', '')) STORED;

• 同样建立联合索引：

CREATE INDEX idx_store_normalized_sku ON store_product(store_id, normalized_sku);

• 查询写法与方案1一致，数据库会自动维护normalized_sku的值。

方案3：JOIN替代子查询（临时过渡方案） #

如果暂时无法修改表结构，可尝试将子查询改为JOIN，但仅适合小数据量场景：

SELECT s0_.sku AS sku_2, COUNT(s1_.id) AS sclr_44
FROM store_product s0_
LEFT JOIN store_product s1_ 
  ON s1_.store_id = s0_.store_id
  AND s1_.sku IS NOT NULL AND s1_.sku <> ''
  AND (s1_.sku = s0_.sku 
       OR REPLACE(REPLACE(REPLACE(REPLACE(s1_.sku, '-', ''), '.', ''), '/', ''), ' ', '') 
         = REPLACE(REPLACE(REPLACE(REPLACE(s0_.sku, '-', ''), '.', ''), '/', ''), ' ', ''))
WHERE s0_.store_id = 5
GROUP BY s0_.id
HAVING sclr_44 > 5
ORDER BY s0_.sku ASC;

• 注意：该方案仍需对每行SKU执行REPLACE计算，大表下性能提升有限。

内容的提问来源于stack exchange，提问作者p.l