案例：改写SQL消除子查询与使用并行查询
- 现象描述
- 优化分析

案例：改写SQL消除子查询与使用并行查询

现象描述

如下复杂连表查询语句，存在多个子查询，存在性能问题，耗时23秒多。

1. SELECT
2. count( a.id ) AS newsCount,
3. a.type1 AS type,
4. a.NAME AS typeName,
5. ifnull( a.parentType, '0' ) AS parentType,
6. a.columnType AS columnType,
7. ( SELECT NAME FROM t_column WHERE type = a.parentType ) AS parentTypeName,
8. SUM( a.countRead1 ) AS countRead,
9. SUM( a.countComment1 ) AS countComment,
10. SUM( a.countShare1 ) AS countShare,
11. SUM( a.countCollect1 ) AS countCollect
12. FROM (
13. SELECT
14. i.id AS id,
15. i.type AS type1,
16. m.NAME AS NAME,
17. m.parent_type AS parentType,
18. m.column_type AS columnType,
19. ( SELECT count(*) FROM t_new_bor_hi h WHERE h.STATUS = 'Y' AND h.new_id = i.id ) countRead1,
20. ( SELECT count(*) FROM t_news_comment c WHERE c.STATUS = 'Y' AND c.newsId = i.id ) countComment1,
21. ( SELECT count(*) FROM t_news_share s WHERE s.STATUS = 'Y' AND s.new_id = i.id ) countShare1,
22. ( SELECT count(*) FROM t_collect co WHERE co.STATUS = 'Y' AND co.news_id = i.id ) countCollect1
23. FROM t_news_info i
24. JOIN t_column m ON m.type = i.type AND m.STATUS = 'Y'
25. WHERE NAME IS NOT NULL AND i.STATUS = 'Y' and i.type is not null ) a
26. GROUP BY a.type1;

优化分析

原始SQL执行计划如下：

根据执行计划可知，此SQL存在大量子查询，且大部分耗时为以下部分：

1. SELECT
2. i.id AS id,
3. i.type AS type1,
4. m.NAME AS NAME,
5. m.parent_type AS parentType,
6. m.column_type AS columnType,
7. ( SELECT count(*) FROM t_new_bor_hi h WHERE h.STATUS = 'Y' AND h.new_id = i.id ) countRead1,
8. ( SELECT count(*) FROM t_news_comment c WHERE c.STATUS = 'Y' AND c.newsId = i.id ) countComment1,
9. ( SELECT count(*) FROM t_news_share s WHERE s.STATUS = 'Y' AND s.new_id = i.id ) countShare1,
10. ( SELECT count(*) FROM t_collect co WHERE co.STATUS = 'Y' AND co.news_id = i.id ) countCollect1
11. FROM t_news_info i
12. JOIN t_Column m ON m.type = i.type AND m.STATUS = 'Y'
13. WHERE NAME IS NOT NULL AND i.STATUS = 'Y' and i.type is not null

子查询执行计划：

在这种场景下，改写消除子查询无明显性能提升，由于并行查询（SMP）不支持子查询算子，因此原 SQL 也无法直接使用并行查询。此时需要先改造SQL，消除子查询才能使用并行特性。

将外层的 FROM 子句里面的以下子查询：

1. ( SELECT count(*) FROM t_new_bor_hi h WHERE h.STATUS = 'Y' AND h.new_id = i.id ) countRead1,
2. ( SELECT count(*) FROM t_news_comment c WHERE c.STATUS = 'Y' AND c.newsId = i.id ) countComment1,
3. ( SELECT count(*) FROM t_news_share s WHERE s.STATUS = 'Y' AND s.new_id = i.id ) countShare1,
4. ( SELECT count(*) FROM t_collect co WHERE co.STATUS = 'Y' AND co.news_id = i.id ) countCollect1

调整到外层 SELECT 部分后，得到新的 SQL 如下（为了方便比较结果，加上了order by）：

1. SELECT
2. count( a.id ) AS newsCount,
3. a.type1 AS type,
4. a.NAME AS typeName,
5. ifnull( a.parentType, '0' ) AS parentType,
6. a.columnType AS columnType,
7. ( SELECT NAME FROM t_column WHERE type = a.parentType ) AS parentTypeName,
8. SUM( (SELECT count(*) FROM t_new_bor_hi h WHERE h.STATUS = 'Y' AND h.new_id = a.id) ) AS countRead,
9. SUM( (SELECT count( c.id ) FROM t_news_comment c WHERE c.STATUS = 'Y' AND c.newsId = a.id) ) AS countComment,
10. SUM( (SELECT count( s.id ) FROM t_news_share s WHERE s.STATUS = 'Y' AND s.new_id = a.id) ) AS countShare,
11. SUM( (SELECT count( co.id ) FROM t_collect co WHERE co.STATUS = 'Y' AND co.news_id = a.id) ) AS countCollect
12. FROM (
13. SELECT
14. i.id AS id,
15. i.type AS type1,
16. m.NAME AS NAME,
17. m.parent_type AS parentType,
18. m.column_type AS columnType
19. FROM t_news_info i
20. LEFT JOIN t_column m ON m.type = i.type AND m.STATUS = 'Y'
21. WHERE NAME IS NOT NULL AND i.STATUS ='Y' and i.type is not null ) a
22. GROUP BY a.type1 order by newsCount;

同时作以下参数配置：

设置会话级并⾏参数，开启并行：

1. set query_dop = 4;

设置会话级SQL rewirte参数：

1. set rewrite_rule='magicset,intargetlist';

最终执行计划如下：

相比原SQL，加上了 order by 后，耗时也只有4秒左右，性能提升约 5 倍。经比较验证，结果正确。