慢SQL查询优化：耗时查询的问题排查及改写方案咨询

一、存在的性能问题 #

• 子查询全表扫描开销大：嵌套子查询对t_deactivatedproduct按a_productid分组取最大a_deactivatedproductid，若该表数据量较大且无对应索引，会触发全表扫描+分组排序，IO和CPU开销极高。
• 过滤条件位置不合理：t_product.a_shopid IN(...)被放在t_productlang的连接条件中，导致无法在关联t_productlang前提前过滤不符合店铺ID的产品，会关联大量不必要的数据。
• OR条件限制索引利用：WHERE子句中的多分支OR逻辑，会导致t_product表无法有效利用联合索引，大概率触发全表或全索引扫描。
• 排序开销高：ORDER BY的t_deactivatedproduct.a_deactivatedproductid若无索引支撑，关联后的数据需生成临时表排序，即便有LIMIT 700，也需先对大量数据完成排序才能取前700条。
• 函数运算增加CPU负载：trim(substring_index(a_reference, '_',-1))的字符串运算，虽不影响索引，但会增加每条结果的CPU处理时间。

二、优化改写方案 #

1. 索引优化（核心前提） #

• 给t_deactivatedproduct建联合索引：CREATE INDEX idx_dp_productid_deactiveid ON t_deactivatedproduct(a_productid, a_deactivatedproductid DESC);，让子查询/窗口函数的分组取最大值操作直接走索引，避免全表扫描。
• 给t_product建联合索引：CREATE INDEX idx_p_published_shopid_productid ON t_product(a_ispublished, a_shopid, a_productid, a_active, a_mpactive);，覆盖WHERE和连接条件的所有字段，快速过滤出符合要求的产品。
• 给t_productlang建索引：CREATE INDEX idx_pl_productid_name ON t_productlang(a_productid, a_name);，关联时直接通过索引获取a_name，避免回表查询。

2. SQL语句改写（以支持窗口函数的数据库为例，如MySQL8+、PostgreSQL） #

SELECT 
    p.a_productid, 
    p.a_mpactive, 
    p.a_active, 
    TRIM(SUBSTRING_INDEX(p.a_reference, '_', -1)) AS a_reference, 
    p.a_shopid, 
    pl.a_name, 
    dp.a_reason 
FROM (
    -- 用窗口函数取每个产品最新的停用记录，避免两次扫描t_deactivatedproduct
    SELECT 
        a_deactivatedproductid,
        a_productid,
        a_reason,
        ROW_NUMBER() OVER(PARTITION BY a_productid ORDER BY a_deactivatedproductid DESC) AS rn
    FROM t_deactivatedproduct
) dp
INNER JOIN t_product p 
    ON p.a_productid = dp.a_productid 
    AND p.a_shopid IN(2, 3, 5, 6, 7, 10, 8, 15, 12, 16, 17, 26, 27, 28)
INNER JOIN t_productlang pl 
    ON pl.a_productid = p.a_productid 
WHERE 
    p.a_ispublished = 1 
    -- 简化OR条件，逻辑等价且更易被索引优化
    AND NOT (p.a_active = 1 AND p.a_mpactive = 1)
    AND dp.rn = 1  -- 筛选每个产品的最新停用记录
ORDER BY dp.a_deactivatedproductid DESC 
LIMIT 700;

3. 兼容旧版本数据库的改写方案（不支持窗口函数） #

SELECT 
    p.a_productid, 
    p.a_mpactive, 
    p.a_active, 
    TRIM(SUBSTRING_INDEX(p.a_reference, '_', -1)) AS a_reference, 
    p.a_shopid, 
    pl.a_name, 
    dp.a_reason 
FROM t_deactivatedproduct dp
INNER JOIN (
    SELECT a_productid, MAX(a_deactivatedproductid) AS max_deactive_id
    FROM t_deactivatedproduct
    GROUP BY a_productid
) dp_max 
    ON dp.a_deactivatedproductid = dp_max.max_deactive_id
INNER JOIN t_product p 
    ON p.a_productid = dp.a_productid 
    AND p.a_shopid IN(2, 3, 5, 6, 7, 10, 8, 15, 12, 16, 17, 26, 27, 28)
INNER JOIN t_productlang pl 
    ON pl.a_productid = p.a_productid 
WHERE 
    p.a_ispublished = 1 
    AND NOT (p.a_active = 1 AND p.a_mpactive = 1)
ORDER BY dp.a_deactivatedproductid DESC 
LIMIT 700;

额外优化建议 #

• 若t_productlang存在多语言记录，需添加语言过滤条件（如pl.a_lang = 'zh-CN'），避免返回重复产品数据，减少数据处理量。

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