[SQL] 生产环境的 SQL 优化思路一、问题描述初步写了一段查询 SQL，目的是选出已绑定某个 tableId 的

一、问题描述

初步写了一段查询 SQL，目的是选出已绑定某个 tableId 的全部 strategy .使用子查询勉强完成功能。交给 DeepSeek-R1 修改后好多了，记录下优化思路和最终成果。

select
    s.id，
    s.strategy_id
    GROUP_CONCAT(stm.table_id) as reductionTableIds,
    GROUP_CONCAT(stm.topic_id) as reductionTopicIds,
    s.status
from s
left join stm on s.id = stm.strategy_id
where id in (
        select strategy_id from stm where stm.table_id = #{tableId}
    )
group by s.id
order by FIELD(s.status, 1, 0, 2) desc

二、推理

好的，我现在需要帮用户优化他们提供的SQL查询。首先，我得仔细看一下他们的原始SQL，找出可能存在的性能问题。然后结合搜索到的资料，看看有哪些优化技巧可以应用。

首先，用户提供的SQL主要涉及两个表的连接：s 和 stm，使用了LEFT JOIN。查询的目的是获取某个特定tableId相关的策略信息，并按状态和创建时间排序。

1 SELECT: 检查冗余项

用户用了 GROUP_CONCAT(stm.table_id) 和 GROUP_CONCAT(stm.table_name)，但注意到s.reduction_table_names也被选中，这可能存在重复或者冗余，需要确认是否需要同时使用这两个字段。

2 GROUP: 要以主表字段为准

GROUP BY stm.strategy_id，这里可能有问题，因为主表是 s，按 stm的字段分组可能导致数据不准确，应该按 s.id分组更合理。

3 子查询 → join 查询，提升查询效率

然后是 WHERE子句中的子查询，这里用了 IN 和一个子查询来获取 strategy_id。根据搜索到的资料[1][2][5][7]，使用IN子查询可能效率较低，特别是当子查询结果集大时，可以考虑用EXISTS或者JOIN来替代。例如，改为JOIN可能会更高效，因为MySQL在处理JOIN时优化得更好，尤其是当相关字段有索引时。

4 ORDER

接下来是 ORDER BY 部分，使用了 FIELD(s.status, 1, 0, 2) 进行自定义排序。根据文献[10]，虽然FIELD()方便，但在大数据量时可能影响性能，因为它无法利用索引。不过，如果status 字段有索引，或许可以部分缓解问题，但更好的方法是用CASE WHEN来替代，这样可能更高效，或者确保status字段有合适的索引。

5 索引方面

需要检查s.id 和 stm.strategy_id以及 stm.table_id 是否有索引。确保连接字段和WHERE条件中的字段有索引是优化的关键。例如，在 stm.table_id 上建立索引，可以加快子查询的速度。同时，s.status和s.created_at的索引可能有助于排序操作。

6 字符串函数

GROUP_CONCAT的使用需要注意，如果数据量很大，可能需要调整 group_concat_max_len 参数，避免截断，同时确保相关字段有索引以减少处理时间。

另外，原始SQL中的 GROUP BY stm.strategy_id 可能有问题，因为主表是s，而 stm 是 LEFT JOIN 的表，如果 stm.strategy_id 为NULL（即没有匹配的记录），那么分组会不准确。应该改为GROUP BY s.id，这样即使没有匹配的stm记录，也能正确分组主表的记录。

7 性能方面

使用 EXPLAIN 分析执行计划，查看是否有全表扫描或临时表的使用，避免全表扫描和利用覆盖索引是关键。此外，考虑将子查询改为 JOIN，例如使用 INNER JOIN 替代IN 子查询，可能会更高效。

8排序部分

如果 s.status 和 s.created_at 没有索引，可以考虑添加复合索引 (status, created_at)，这样 ORDER BY 可以利用索引排序，避免 filesort。

总结

需要优化的点：

修正 GROUP BY 为 s.id，确保正确分组。
将 IN 子查询改为 JOIN，提升查询效率。
确保相关字段（如 s.id, stm.strategy_id, stm.table_id）有索引。
处理重复的列别名，避免错误。
考虑使用 CASE WHEN 替代 FIELD() 函数，或为 status 字段建立索引。
调整 GROUP_CONCAT 的参数或确保其效率。
检查 SELECT 字段是否存在冗余，减少数据传输量。

三、回答

以下是针对该SQL的优化建议，结合查询逻辑和性能优化原则：

SELECT
    s.id,
    s.template_id AS templateId,
    s.strategy_name AS strategyName,
    s.reduction_dim AS reductionDim,
    s.reduction_object_type AS reductionObjectType,
    GROUP_CONCAT(DISTINCT stm.table_id) AS reductionTableIds, -- 去重优化
    MAX(s.reduction_table_names) AS reductionTableNames, -- 修正重复字段
    GROUP_CONCAT(DISTINCT stm.table_name SEPARATOR ',') AS mappedTableNames, -- 修正别名冲突
    s.rule_type AS ruleType,
    s.reduction_choose_type AS reductionChooseType,
    s.reduction_details AS reductionDetails,
    s.status,
    s.creator,
    s.reviser,
    s.created_at AS createdAt,
    s.updated_at AS updatedAt
FROM reduction_strategy s
INNER JOIN ( -- 改用JOIN替代IN子查询
    SELECT DISTINCT strategy_id 
    FROM reduction_strategy_table_mapping 
    WHERE table_id = #{tableId}
) filter ON s.id = filter.strategy_id
LEFT JOIN reduction_strategy_table_mapping stm 
    ON s.id = stm.strategy_id
GROUP BY s.id -- 按主表ID分组更准确
ORDER BY 
    CASE s.status -- 用CASE替代FIELD提升性能
        WHEN 1 THEN 1
        WHEN 0 THEN 2
        WHEN 2 THEN 3
        ELSE 4
    END,
    s.created_at DESC;

主要优化点说明：

索引优化
- 为reduction_strategy_table_mapping.table_id创建索引
- 为reduction_strategy.id创建主键/唯一索引
- 添加复合索引(status, created_at)优化排序
执行逻辑优化
- 将IN子查询改为JOIN，利用索引加速关联
- 使用DISTINCT减少GROUP_CONCAT重复值处理
- 按主表s.id分组避免数据错乱
排序优化
- 用CASE WHEN替代FIELD()函数，提升排序性能
- 复合索引(status, created_at)覆盖排序需求
字段优化
- 修正重复的reductionTableNames字段别名
- 使用MAX()聚合函数处理单值字段更规范
其他建议
- 大数据量时设置SET SESSION group_concat_max_len = 1000000;
- 使用EXPLAIN分析执行计划验证优化效果

可通过以下复合索引进一步提升性能：

-- 策略表主索引
ALTER TABLE reduction_strategy 
  ADD INDEX idx_core(status, created_at, id);

-- 映射表查询索引
ALTER TABLE reduction_strategy_table_mapping
  ADD INDEX idx_search(table_id, strategy_id);