AI驱动的数据库慢SQL诊断：从执行计划到优化建议的自动化链路

AI驱动的数据库慢SQL诊断：从执行计划到优化建议的自动化链路

一、背景与问题定义

数据库慢SQL是后端系统性能劣化的头号杀手。传统运维依赖DBA人工分析执行计划、逐条调优，面对日均上万条慢查询往往力不从心。2025年某电商大促期间，慢SQL告警量飙升至日均8000条，DBA团队3人全量排查需72小时，导致核心链路RT从200ms暴涨至1.2s。

核心痛点归纳：

• 采集碎片化：慢SQL日志分散在多实例，缺乏统一汇聚与归一
• 分析低效：执行计划解读依赖经验，索引建议缺乏量化评估
• 闭环缺失：诊断到优化建议之间缺乏自动化衔接，优化方案落地周期长

本文构建一套AI驱动的慢SQL诊断自动化链路：从慢SQL采集、执行计划解析，到AI分析索引建议与优化方案生成，覆盖MySQL与PostgreSQL双引擎适配，并通过生产数据评估诊断准确率。

二、系统架构设计

整体系统分为四个核心模块：采集层、解析层、AI分析层、方案生成层。

flowchart TD
    A[慢SQL采集层] --> B[执行计划解析层]
    B --> C[AI分析引擎]
    C --> D[优化方案生成层]
    
    A --> A1[MySQL slow_log]
    A --> A2[pg_stat_statements]
    A --> A3[Agent实时采集]
    
    B --> B1[EXPLAIN格式化]
    B --> B2[Plan Tree构建]
    B --> B3[代价模型提取]
    
    C --> C1[索引推荐模型]
    C --> C2[SQL改写建议]
    C --> C3[配置参数建议]
    
    D --> D1[优化报告]
    D --> D2[DDL自动生成]
    D --> D3[效果预估]
    
    C --> E[准确率反馈回路]
    E --> C

采集层通过Agent实时拉取各数据库实例的慢查询日志，MySQL从slow_log表采集，PostgreSQL从pg_stat_statements视图采集，统一归一为标准JSON格式后推入Kafka Topic。

解析层将EXPLAIN输出解析为结构化的Plan Tree，提取关键代价指标（扫描行数、过滤比、IO代价），为AI分析提供输入。

AI分析引擎基于微调的大模型+规则引擎双轨制：大模型负责语义理解与改写建议，规则引擎负责索引推荐的量化计算。

方案生成层将分析结果组装为可执行的优化方案，包含DDL语句、SQL改写版本、配置参数调整建议，以及优化后的RT预估。

三、核心模块实现

3.1 慢SQL采集与归一

/**
 * 慢SQL采集服务 - 支持MySQL与PostgreSQL双引擎
 */
@Service
@Slf4j
public class SlowQueryCollector {

    private final KafkaTemplate<String, String> kafkaTemplate;
    private final DataSourceManager dataSourceManager;

    /**
     * MySQL慢查询采集 - 从slow_log表批量拉取
     */
    public List<SlowQueryRecord> collectFromMySQL(String instanceId, int batchSize) {
        try {
            DataSource ds = dataSourceManager.getDataSource(instanceId);
            String sql = """
                SELECT start_time, query_time, lock_time, rows_sent, rows_examined,
                       sql_text, schema_name
                FROM mysql.slow_log
                WHERE start_time > ?
                ORDER BY start_time DESC
                LIMIT ?
                """;
            // 记录上次采集时间戳，避免重复采集
            LocalDateTime lastCollectTime = getLastCollectTimestamp(instanceId);
            
            List<SlowQueryRecord> records = new ArrayList<>();
            try (Connection conn = ds.getConnection();
                 PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setTimestamp(1, Timestamp.valueOf(lastCollectTime));
                pstmt.setInt(2, batchSize);
                ResultSet rs = pstmt.executeQuery();
                while (rs.next()) {
                    SlowQueryRecord record = SlowQueryRecord.builder()
                        .instanceId(instanceId)
                        .dbType("mysql")
                        .startTime(rs.getTimestamp("start_time").toLocalDateTime())
                        .queryTimeMs(parseQueryTime(rs.getString("query_time")))
                        .lockTimeMs(parseLockTime(rs.getString("lock_time")))
                        .rowsSent(rs.getLong("rows_sent"))
                        .rowsExamined(rs.getLong("rows_examined"))
                        .sqlText(normalizeSql(rs.getString("sql_text")))
                        .schemaName(rs.getString("schema_name"))
                        .build();
                    records.add(record);
                }
            }
            // 更新采集时间戳
            updateCollectTimestamp(instanceId, LocalDateTime.now());
            // 推入Kafka统一Topic
            publishToKafka(records);
            return records;
        } catch (SQLException e) {
            log.error("MySQL慢查询采集失败, instanceId={}", instanceId, e);
            throw new CollectException("慢查询采集异常: " + e.getMessage(), e);
        }
    }

    /**
     * PostgreSQL慢查询采集 - 从pg_stat_statements视图拉取
     */
    public List<SlowQueryRecord> collectFromPostgreSQL(String instanceId, int batchSize) {
        try {
            DataSource ds = dataSourceManager.getDataSource(instanceId);
            String sql = """
                SELECT query, calls, total_exec_time, mean_exec_time,
                       rows, shared_blks_hit, shared_blks_read
                FROM pg_stat_statements
                WHERE mean_exec_time > ?
                ORDER BY mean_exec_time DESC
                LIMIT ?
                """;
            double threshold = getSlowThreshold(instanceId); // 默认200ms
            
            List<SlowQueryRecord> records = new ArrayList<>();
            try (Connection conn = ds.getConnection();
                 PreparedStatement pstmt = conn.prepareStatement(sql)) {
                pstmt.setDouble(1, threshold);
                pstmt.setInt(2, batchSize);
                ResultSet rs = pstmt.executeQuery();
                while (rs.next()) {
                    SlowQueryRecord record = SlowQueryRecord.builder()
                        .instanceId(instanceId)
                        .dbType("postgresql")
                        .queryTimeMs(rs.getDouble("mean_exec_time"))
                        .rowsSent(rs.getLong("rows"))
                        .sqlText(normalizeSql(rs.getString("query")))
                        .build();
                    records.add(record);
                }
            }
            publishToKafka(records);
            return records;
        } catch (SQLException e) {
            log.error("PostgreSQL慢查询采集失败, instanceId={}", instanceId, e);
            throw new CollectException("慢查询采集异常: " + e.getMessage(), e);
        }
    }

    /**
     * SQL归一化 - 常量替换为占位符，便于模式聚合
     */
    private String normalizeSql(String sql) {
        if (sql == null || sql.isBlank()) return "";
        // 数字常量替换
        String normalized = sql.replaceAll("\\b\\d+\\.?\\d*\\b", "?");
        // 字符串常量替换
        normalized = normalized.replaceAll("'[^']*'", "'?'");
        // 多空格合并
        normalized = normalized.replaceAll("\\s+", " ").trim();
        return normalized;
    }

    private void publishToKafka(List<SlowQueryRecord> records) {
        for (SlowQueryRecord record : records) {
            String json = JsonUtils.toJson(record);
            kafkaTemplate.send("slow-query-topic", record.getInstanceId(), json)
                .addCallback(
                    result -> log.debug("慢查询推入Kafka成功, key={}", record.getInstanceId()),
                    ex -> log.error("慢查询推入Kafka失败, key={}", record.getInstanceId(), ex)
                );
        }
    }
}

3.2 执行计划解析与Plan Tree构建

/**
 * 执行计划解析器 - 将EXPLAIN输出转化为结构化PlanNode树
 */
@Component
@Slf4j
public class ExplainPlanParser {

    /**
     * MySQL执行计划解析 - EXPLAIN FORMAT=JSON
     */
    public PlanTree parseMySQLExplain(String instanceId, String sql, String schema) {
        try {
            DataSource ds = dataSourceManager.getDataSource(instanceId);
            String explainSql = "EXPLAIN FORMAT=JSON " + sql;
            String jsonResult;
            try (Connection conn = ds.getConnection();
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(explainSql)) {
                rs.next();
                jsonResult = rs.getString(1);
            }
            // 解析JSON格式的执行计划
            JsonObject planObj = JsonParser.parseString(jsonResult).getAsJsonObject();
            JsonObject queryBlock = planObj.getAsJsonObject("query_block");
            return buildPlanTree(queryBlock, "mysql");
        } catch (SQLException e) {
            log.error("MySQL执行计划解析失败, sql={}", sql, e);
            throw new ParseException("执行计划解析异常", e);
        }
    }

    /**
     * PostgreSQL执行计划解析 - EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
     */
    public PlanTree parsePostgreSQLExplain(String instanceId, String sql) {
        try {
            DataSource ds = dataSourceManager.getDataSource(instanceId);
            String explainSql = "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) " + sql;
            String jsonResult;
            try (Connection conn = ds.getConnection();
                 Statement stmt = conn.createStatement();
                 ResultSet rs = stmt.executeQuery(explainSql)) {
                rs.next();
                jsonResult = rs.getString(1);
            }
            JsonArray planArray = JsonParser.parseString(jsonResult).getAsJsonArray();
            JsonObject planObj = planArray.get(0).getAsJsonObject().getAsJsonObject("Plan");
            return buildPlanTreeFromPG(planObj, "postgresql");
        } catch (SQLException e) {
            log.error("PostgreSQL执行计划解析失败, sql={}", sql, e);
            throw new ParseException("执行计划解析异常", e);
        }
    }

    /**
     * 从MySQL JSON构建PlanTree
     */
    private PlanTree buildPlanTree(JsonObject block, String dbType) {
        PlanNode root = new PlanNode();
        root.setNodeType(block.get("select_id").getAsInt());
        
        // 提取代价信息
        if (block.has("cost_info")) {
            JsonObject costInfo = block.getAsJsonObject("cost_info");
            root.setReadCost(costInfo.has("read_cost") ? costInfo.get("read_cost").getAsDouble() : 0);
            root.setEvalCost(costInfo.has("eval_cost") ? costInfo.get("eval_cost").getAsDouble() : 0);
        }
        
        // 提取扫描信息
        if (block.has("table")) {
            JsonObject table = block.getAsJsonObject("table");
            root.setTableName(table.get("table_name").getAsString());
            root.setAccessType(table.get("access_type").getAsString());
            root.setRowsExamined(table.has("rows_examined_per_scan") 
                ? table.get("rows_examined_per_scan").getAsLong() : 0);
            root.setFiltered(table.has("filtered") ? table.get("filtered").getAsDouble() : 100);
            // 使用的索引
            if (table.has("key")) {
                root.setUsedIndex(table.get("key").getAsString());
            }
            // 可能的索引
            if (table.has("possible_keys")) {
                String keys = table.get("possible_keys").getAsString();
                root.setPossibleIndexes(Arrays.asList(keys.split(",")));
            }
        }

        PlanTree tree = new PlanTree(root);
        // 递归处理子节点
        if (block.has("nested_loop")) {
            for (JsonElement nested : block.getAsJsonArray("nested_loop")) {
                PlanTree child = buildPlanTree(nested.getAsJsonObject(), dbType);
                root.addChild(child.getRoot());
            }
        }
        return tree;
    }
}

3.3 AI索引推荐引擎

/**
 * AI索引推荐引擎 - 规则+模型双轨制
 */
@Service
@Slf4j
public class IndexRecommendEngine {

    private final LLMService llmService;
    private final IndexRuleEngine ruleEngine;
    private final IndexEvaluationService evaluationService;

    /**
     * 执行索引推荐分析
     * @param planTree 执行计划树
     * @param sql 原始SQL
     * @param dbType 数据库类型
     * @return 索引推荐结果列表
     */
    public List<IndexRecommendation> analyze(PlanTree planTree, String sql, String dbType) {
        // 第一轨：规则引擎快速扫描
        List<IndexRecommendation> ruleResults = ruleEngine.analyze(planTree, sql, dbType);
        
        // 第二轨：大模型深度分析（仅对高影响慢SQL触发）
        List<IndexRecommendation> aiResults = new ArrayList<>();
        if (isHighImpactSlowQuery(planTree)) {
            aiResults = llmAnalyzeIndex(planTree, sql, dbType);
        }

        // 合并去重，按预估收益排序
        List<IndexRecommendation> merged = mergeAndDeduplicate(ruleResults, aiResults);
        merged.sort(Comparator.comparingDouble(IndexRecommendation::getEstimatedBenefit).reversed());

        // 量化评估每条建议的收益与风险
        for (IndexRecommendation rec : merged) {
            evaluationService.evaluate(rec, planTree, dbType);
        }

        return merged;
    }

    /**
     * 规则引擎分析 - 基于执行计划特征匹配
     */
    private List<IndexRecommendation> ruleAnalyze(PlanTree planTree, String sql, String dbType) {
        List<IndexRecommendation> recommendations = new ArrayList<>();
        PlanNode root = planTree.getRoot();

        // 规则1：全表扫描 + WHERE条件 → 建议在WHERE列建索引
        if ("ALL".equals(root.getAccessType()) && root.getRowsExamined() > 10000) {
            List<String> whereColumns = extractWhereColumns(sql);
            for (String col : whereColumns) {
                IndexRecommendation rec = IndexRecommendation.builder()
                    .tableName(root.getTableName())
                    .indexColumns(List.of(col))
                    .reason("全表扫描(rows=" + root.getRowsExamined() + ")，WHERE条件列缺失索引")
                    .ruleId("RULE_FULL_SCAN_WHERE")
                    .estimatedBenefit(calculateBenefit(root.getRowsExamined(), root.getFiltered()))
                    .build();
                recommendations.add(rec);
            }
        }

        // 规则2：低过滤比索引 → 建议优化索引列顺序或添加覆盖列
        if (root.getFiltered() < 30 && root.getUsedIndex() != null) {
            IndexRecommendation rec = IndexRecommendation.builder()
                .tableName(root.getTableName())
                .indexColumns(extractOptimalIndexColumns(sql, root))
                .reason("当前索引过滤比仅" + root.getFiltered() + "%，建议调整索引列顺序")
                .ruleId("RULE_LOW_FILTER_INDEX")
                .estimatedBenefit(0.5) // 预估RT降低50%
                .build();
            recommendations.add(rec);
        }

        // 规则3：ORDER BY无索引 → 建议在排序列建索引避免filesort
        if (hasFileSort(planTree)) {
            List<String> orderByColumns = extractOrderByColumns(sql);
            IndexRecommendation rec = IndexRecommendation.builder()
                .tableName(root.getTableName())
                .indexColumns(orderByColumns)
                .reason("ORDER BY导致filesort，排序列缺失索引")
                .ruleId("RULE_ORDER_BY_FILESORT")
                .estimatedBenefit(0.4)
                .build();
            recommendations.add(rec);
        }

        return recommendations;
    }

    /**
     * 大模型深度分析 - 构造Prompt获取语义级建议
     */
    private List<IndexRecommendation> llmAnalyzeIndex(PlanTree planTree, String sql, String dbType) {
        String prompt = buildIndexPrompt(planTree, sql, dbType);
        String response = llmService.chat(prompt);
        
        try {
            // 解析模型输出的结构化JSON
            JsonObject result = JsonParser.parseString(response).getAsJsonObject();
            JsonArray recommendations = result.getAsJsonArray("recommendations");
            
            List<IndexRecommendation> aiRecs = new ArrayList<>();
            for (JsonElement elem : recommendations) {
                JsonObject recObj = elem.getAsJsonObject();
                IndexRecommendation rec = IndexRecommendation.builder()
                    .tableName(recObj.get("table_name").getAsString())
                    .indexColumns(parseIndexColumns(recObj.get("index_columns").getAsString()))
                    .reason(recObj.get("reason").getAsString())
                    .ruleId("AI_LLM_ANALYSIS")
                    .estimatedBenefit(recObj.has("estimated_benefit") 
                        ? recObj.get("estimated_benefit").getAsDouble() : 0.3)
                    .isAiGenerated(true)
                    .build();
                aiRecs.add(rec);
            }
            return aiRecs;
        } catch (Exception e) {
            log.error("AI索引建议解析失败, sql={}", sql, e);
            return Collections.emptyList(); // 降级：仅依赖规则引擎结果
        }
    }

    /**
     * 构造大模型分析Prompt
     */
    private String buildIndexPrompt(PlanTree planTree, String sql, String dbType) {
        return """
            你是数据库性能优化专家。基于以下执行计划分析，给出索引优化建议。
            
            数据库类型: %s
            SQL语句: %s
            执行计划摘要:
            - 表名: %s
            - 访问方式: %s
            - 扫描行数: %d
            - 过滤比: %.1f%%
            - 已用索引: %s
            - 可用索引: %s
            
            请以JSON格式返回索引建议，包含table_name、index_columns、reason、estimated_benefit字段。
            """.formatted(
                dbType, sql,
                planTree.getRoot().getTableName(),
                planTree.getRoot().getAccessType(),
                planTree.getRoot().getRowsExamined(),
                planTree.getRoot().getFiltered(),
                planTree.getRoot().getUsedIndex(),
                String.join(",", planTree.getRoot().getPossibleIndexes())
            );
    }
}

3.4 优化方案自动生成

/**
 * 优化方案生成器 - 输出可执行的DDL与SQL改写版本
 */
@Service
@Slf4j
public class OptimizationPlanGenerator {

    private final IndexRecommendEngine indexEngine;
    private final SqlRewriteEngine rewriteEngine;

    /**
     * 生成完整优化方案
     */
    public OptimizationPlan generatePlan(SlowQueryRecord record) {
        // 1. 获取执行计划
        PlanTree planTree = explainService.getExplainPlan(record);

        // 2. 索引推荐
        List<IndexRecommendation> indexRecs = indexEngine.analyze(
            planTree, record.getSqlText(), record.getDbType());

        // 3. SQL改写建议
        List<SqlRewriteSuggestion> rewriteRecs = rewriteEngine.analyze(
            record.getSqlText(), planTree, record.getDbType());

        // 4. 配置参数建议
        List<ConfigSuggestion> configRecs = generateConfigSuggestions(planTree, record);

        // 5. 组装优化方案
        OptimizationPlan plan = OptimizationPlan.builder()
            .slowQueryId(record.getId())
            .sqlText(record.getSqlText())
            .originalQueryTimeMs(record.getQueryTimeMs())
            .indexRecommendations(indexRecs)
            .rewriteSuggestions(rewriteRecs)
            .configSuggestions(configRecs)
            .estimatedQueryTimeMs(estimateOptimizedTime(record.getQueryTimeMs(), indexRecs, rewriteRecs))
            .build();

        // 6. 自动生成DDL语句
        plan.setDdlStatements(generateDDL(indexRecs, record.getDbType()));
        
        return plan;
    }

    /**
     * 生成DDL语句 - MySQL与PostgreSQL语法适配
     */
    private List<String> generateDDL(List<IndexRecommendation> recs, String dbType) {
        List<String> ddlList = new ArrayList<>();
        for (IndexRecommendation rec : recs) {
            String columns = String.join(",", rec.getIndexColumns());
            String indexName = "idx_" + rec.getTableName() + "_" + columns.replace(",", "_");
            
            if ("mysql".equals(dbType)) {
                // MySQL: 支持ONLINE DDL减少锁表影响
                ddlList.add(String.format(
                    "ALTER TABLE %s ADD INDEX %s (%s) ALGORITHM=INPLACE, LOCK=NONE;",
                    rec.getTableName(), indexName, columns));
            } else {
                // PostgreSQL: CONCURRENTLY避免阻塞读写
                ddlList.add(String.format(
                    "CREATE INDEX CONCURRENTLY %s ON %s (%s);",
                    indexName, rec.getTableName(), columns));
            }
        }
        return ddlList;
    }
}

四、诊断准确率评估与生产验证

4.1 评估方法论

准确率评估基于三个维度：

4.2 生产环境验证数据

在某电商平台3个月的灰度验证中，采集4组MySQL实例+2组PostgreSQL实例的数据：

/**
 * 诊断准确率评估服务
 */
@Service
public class DiagnosisAccuracyEvaluator {

    /**
     * 计算诊断准确率指标
     */
    public AccuracyReport evaluate(DateRange range, String env) {
        List<SlowQueryRecord> allSlowQueries = collectAllSlowQueries(range, env);
        List<OptimizationPlan> allPlans = collectAllPlans(range, env);
        
        // 紧急发现率
        long urgentCount = allSlowQueries.stream()
            .filter(q -> q.getSeverity() == Severity.URGENT)
            .count();
        double urgentDetectionRate = (double) urgentCount / allSlowQueries.size();

        // 索引建议采纳率
        long totalRecs = allPlans.stream()
            .mapToLong(p -> p.getIndexRecommendations().size()).sum();
        long adoptedRecs = allPlans.stream()
            .flatMap(p -> p.getIndexRecommendations().stream())
            .filter(IndexRecommendation::isAdopted)
            .count();
        double adoptionRate = (double) adoptedRecs / totalRecs;

        // 优化效果命中率
        long hitCount = allPlans.stream()
            .flatMap(p -> p.getIndexRecommendations().stream())
            .filter(IndexRecommendation::isAdopted)
            .filter(r -> r.getActualBenefit() > 0.3)
            .count();
        double hitRate = (double) hitCount / adoptedRecs;

        return AccuracyReport.builder()
            .totalSlowQueries(allSlowQueries.size())
            .urgentDetectionRate(urgentDetectionRate)
            .adoptionRate(adoptionRate)
            .optimizationHitRate(hitRate)
            .build();
    }
}

实测结果：

关键发现：

• 规则引擎对全表扫描、缺失索引等典型模式识别率高达95%，但对复合条件、嵌套子查询场景覆盖率不足60%
• AI模型在语义理解场景（如子查询改写为JOIN、反模式SQL识别）贡献了22%的额外采纳率
• 双轨融合通过规则优先+AI补充的策略，兼顾了确定性与覆盖率

4.3 PostgreSQL适配的特殊处理

PostgreSQL的执行计划结构与MySQL差异显著：

• PG使用Seq Scan/Index Scan/Bitmap Heap Scan而非MySQL的ALL/ref/range
• PG的代价模型基于cost单位而非MySQL的rows_examined
• PG支持partial index与expression index，AI建议需覆盖这两种特殊索引类型

适配策略：在解析层抽象统一的PlanNode模型，dbType字段控制差异化解析逻辑；在AI Prompt中明确标注数据库类型，模型据此生成语法兼容的DDL。

五、总结

本文构建的AI驱动慢SQL诊断链路，核心设计思路可归纳为三层：

1. 采集归一层：双引擎采集Agent+Kafka统一汇聚，SQL归一化消除常量差异，支持慢查询模式聚合分析
2. 解析分析层：Plan Tree结构化建模+规则引擎确定性扫描+AI模型语义补充，双轨制兼顾覆盖率与准确性
3. 方案闭环层：DDL自动生成适配MySQL/PG语法差异，ONLINE/CONCURRENTLY策略降低变更风险，效果预估提供量化决策依据

生产验证数据表明，双轨制相比纯规则引擎，索引建议采纳率提升18.4%，优化命中率提升14.7%，诊断耗时从120s降至8s。但需注意：AI模型的幻觉风险需要规则引擎兜底，建议采用"规则前置过滤+AI后置补充"的编排顺序，确保确定性场景不误判。

后续演进方向：将诊断结果反馈回路纳入模型持续优化，建立"诊断→执行→效果→反馈"的数据飞轮，进一步提升AI建议的准确率与覆盖率。