规则模板刷新慢

[ColdWaterLW]

#632

复现环境： 1 有如下多个规则模板

2 启用mysql所有审核规则 3 sqle元数据库所用的mysql为8.0 4 刷新规则模板页面，大约需要等待3秒

排查： 打开mysql慢日志，看到如下慢日志，这条慢SQL是查询规则模板的SQL

# Time: 2022-09-02T03:15:19.208887Z
# User@Host: root[root] @  [20.20.20.1]  Id:   103
# Query_time: 3.621495  Lock_time: 0.000079 Rows_sent: 5  Rows_examined: 285901
use sqle;
SET timestamp=1662088515;
SELECT rt1.name, rt1.desc, rt1.db_type,
GROUP_CONCAT(DISTINCT COALESCE(rules.name,'')) AS rule_names,
GROUP_CONCAT(DISTINCT COALESCE(instances.name,'')) AS instance_names
FROM rule_templates AS rt1
LEFT JOIN rule_template_rule ON rt1.id = rule_template_rule.rule_template_id
LEFT JOIN rules ON rule_template_rule.rule_name = rules.name
LEFT JOIN instance_rule_template ON rt1.id = instance_rule_template.rule_template_id
LEFT JOIN instances ON instance_rule_template.instance_id = instances.id AND instances.deleted_at IS NULL
WHERE EXISTS (SELECT DISTINCT(rule_templates.id)
FROM rule_templates
LEFT JOIN rule_template_rule ON rule_templates.id = rule_template_rule.rule_template_id
LEFT JOIN rules ON rule_template_rule.rule_name = rules.name
LEFT JOIN instance_rule_template ON rule_templates.id = instance_rule_template.rule_template_id
LEFT JOIN instances ON instance_rule_template.instance_id = instances.id AND instances.deleted_at IS NULL
WHERE
rule_templates.deleted_at IS NULL
AND rt1.id = rule_templates.id
)
GROUP BY rt1.id
LIMIT 10 OFFSET 0;

原因 当启用规则的时候，rule_template_rule表里的条目会增加，当rule_template_rule数据很多的时候，再去对它做表关联，会导致SQL执行很慢。

后续待办 初步断定这个查询不需要关联表rule_template_rule，需要进一步确认。去掉关联后SQL速度会大幅提升