AI 辅助的 SQL 性能诊断与索引推荐：从慢查询到智能优化

一、慢查询的"暗箱"：DBA 的直觉与自动化的鸿沟

数据库慢查询是后端服务性能问题的头号元凶。一个未命中索引的查询，在数据量 100 万行时可能只需 50ms，但数据增长到 1000 万行后可能飙升到 5 秒。传统的慢查询优化依赖 DBA 的经验：查看执行计划、分析索引使用情况、调整查询写法。但这种人工方式存在三个瓶颈：一是响应慢，从发现慢查询到给出优化方案通常需要数小时；二是覆盖窄，DBA 只能处理被主动发现的慢查询，大量"不够慢但可以更快"的查询被忽略；三是知识传承难，优化经验高度依赖个人，DBA 离职后优化能力断档。

AI 辅助的 SQL 性能诊断方案，可以自动分析慢查询日志、解析执行计划、推荐索引方案，将优化周期从数小时压缩到数分钟。

二、智能 SQL 诊断的架构设计

AI SQL 诊断系统分为三层：数据采集层收集慢查询和执行计划，规则分析层基于启发式规则识别常见问题，AI 推理层结合表结构和数据分布生成索引推荐。

flowchart TD
    A[慢查询日志] --> B[查询特征提取]
    C[执行计划 EXPLAIN] --> B
    D[表结构 DDL] --> E[索引覆盖率分析]
    B --> F[规则引擎：常见反模式检测]
    E --> F
    F --> G[AI 索引推荐]
    D --> G
    B --> G
    G --> H[索引方案评估]
    H --> I[生成优化建议]
    I --> J[在线验证]

规则引擎负责检测常见的 SQL 反模式：SELECT *、隐式类型转换、OR 条件导致索引失效、子查询可优化为 JOIN 等。AI 层则负责更复杂的场景：联合索引的列顺序选择、覆盖索引的可行性分析、查询重写建议。

三、工程化实现

3.1 慢查询采集与特征提取

// SlowQueryCollector.java
@Data
public class SlowQueryRecord {
    private String sql;
    private long executionTimeMs;
    private long rowsExamined;
    private long rowsReturned;
    private String explainResult;
    private LocalDateTime timestamp;
    private String schema;
}

@Component
public class SlowQueryCollector {

    private final JdbcTemplate jdbcTemplate;

    // 从 MySQL slow_log 表采集慢查询
    public List<SlowQueryRecord> collectSlowQueries(
        Duration since,
        long minExecutionTimeMs
    ) {
        String sql = """
            SELECT sql_text, query_time, rows_examined, rows_sent
            FROM mysql.slow_log
            WHERE start_time > ?
              AND query_time > ?
            ORDER BY query_time DESC
            LIMIT 100
            """;

        return jdbcTemplate.query(sql,
            (rs, rowNum) -> {
                var record = new SlowQueryRecord();
                record.setSql(rs.getString("sql_text"));
                record.setExecutionTimeMs(
                    rs.getTime("query_time").getTime()
                );
                record.setRowsExamined(rs.getLong("rows_examined"));
                record.setRowsReturned(rs.getLong("rows_sent"));
                record.setTimestamp(LocalDateTime.now());
                return record;
            },
            LocalDateTime.now().minus(since),
            new Time(minExecutionTimeMs)
        );
    }

    // 获取查询的执行计划
    public String getExplainResult(String querySql) {
        var results = jdbcTemplate.queryForList(
            "EXPLAIN " + querySql
        );
        return results.stream()
            .map(row -> String.format(
                "type=%s, key=%s, rows=%s, Extra=%s",
                row.get("type"),
                row.get("key"),
                row.get("rows"),
                row.get("Extra")
            ))
            .collect(Collectors.joining("\n"));
    }
}

3.2 规则引擎：常见反模式检测

// SqlRuleEngine.java
@Component
public class SqlRuleEngine {

    public List<SqlIssue> analyze(SlowQueryRecord record) {
        var issues = new ArrayList<SqlIssue>();
        String sql = record.getSql().toLowerCase();

        // 规则 1：SELECT * 导致不必要的数据传输
        if (sql.startsWith("select *")) {
            issues.add(SqlIssue.builder()
                .rule("SELECT_STAR")
                .severity("MODERATE")
                .description("使用 SELECT * 读取了不必要的列，"
                    + "增加网络传输和内存消耗")
                .suggestion("明确指定需要的列名，"
                    + "可能使查询命中覆盖索引")
                .build());
        }

        // 规则 2：WHERE 条件中对索引列使用函数
        if (sql.matches(".*where.*\\w+\\s*\\(.*\\).*=.*")) {
            issues.add(SqlIssue.builder()
                .rule("INDEX_FUNCTION")
                .severity("HIGH")
                .description("WHERE 条件中对列使用函数，"
                    + "导致索引无法使用")
                .suggestion("将函数应用到常量端，"
                    + "如 WHERE date_col >= '2024-01-01' "
                    + "而非 WHERE YEAR(date_col) = 2024")
                .build());
        }

        // 规则 3：扫描行数远大于返回行数
        if (record.getRowsExamined() > record.getRowsReturned() * 100
            && record.getRowsExamined() > 10000) {
            issues.add(SqlIssue.builder()
                .rule("LOW_SELECTIVITY")
                .severity("HIGH")
                .description(String.format(
                    "扫描 %d 行仅返回 %d 行，选择性极低",
                    record.getRowsExamined(),
                    record.getRowsReturned()))
                .suggestion("检查 WHERE 条件的索引覆盖率，"
                    + "考虑添加更精确的索引")
                .build());
        }

        // 规则 4：EXPLAIN 显示全表扫描
        if (record.getExplainResult() != null
            && record.getExplainResult().contains("type=ALL")) {
            issues.add(SqlIssue.builder()
                .rule("FULL_TABLE_SCAN")
                .severity("CRITICAL")
                .description("执行计划显示全表扫描")
                .suggestion("为 WHERE/JOIN 条件中的列添加索引")
                .build());
        }

        return issues;
    }
}

3.3 AI 索引推荐

// AiIndexAdvisor.java
@Component
@RequiredArgsConstructor
public class AiIndexAdvisor {

    private final JdbcTemplate jdbcTemplate;

    public IndexRecommendation recommend(
        SlowQueryRecord record,
        List<SqlIssue> issues
    ) {
        // 获取表结构信息
        String tableStructure = getTableStructure(record.getSql());

        String prompt = String.format("""
            你是一位数据库性能优化专家。请为以下慢查询推荐索引方案。

            SQL 查询：%s
            执行时间：%d ms
            扫描行数：%d
            返回行数：%d
            执行计划：%s
            已识别问题：%s

            表结构：
            %s

            请分析：
            1. 推荐添加的索引（包含列名和顺序）
            2. 是否可以构建覆盖索引避免回表
            3. 索引对写入性能的影响评估
            4. 查询重写建议（如有）

            输出 JSON 格式。
            """,
            record.getSql(),
            record.getExecutionTimeMs(),
            record.getRowsExamined(),
            record.getRowsReturned(),
            record.getExplainResult(),
            issues.stream().map(SqlIssue::getDescription)
                .collect(Collectors.joining("; ")),
            tableStructure
        );

        String response = callLLM(prompt);
        return parseRecommendation(response);
    }

    private String getTableStructure(String sql) {
        // 从 SQL 中提取表名，查询 CREATE TABLE 语句
        // 简化实现：正则提取
        var matcher = Pattern.compile(
            "from\\s+(\\w+)", Pattern.CASE_INSENSITIVE
        ).matcher(sql);
        if (matcher.find()) {
            String tableName = matcher.group(1);
            try {
                return jdbcTemplate.queryForObject(
                    "SHOW CREATE TABLE " + tableName,
                    (rs, rowNum) -> rs.getString(2)
                );
            } catch (Exception e) {
                return "无法获取表结构";
            }
        }
        return "无法识别表名";
    }
}

四、AI SQL 诊断的 Trade-offs

索引推荐的准确性：AI 推荐的索引基于静态分析，无法考虑运行时的数据分布。一个在测试环境中表现优异的索引，在生产环境中可能因为数据倾斜而效果不佳。建议在 AI 推荐后，使用生产数据的匿名化副本进行验证，确认索引的实际效果。

索引数量与写入性能的矛盾：每个额外的索引都会降低 INSERT/UPDATE/DELETE 的性能。AI 可能推荐 5 个索引来优化查询，但这 5 个索引的写入开销可能抵消查询优化的收益。建议设置索引数量上限（通常每表不超过 5 个），AI 推荐时需要评估写入影响。

查询重写的语义等价性：AI 可能建议将子查询改写为 JOIN，或将 OR 改写为 UNION，但改写后的查询在语义上可能不完全等价（如 NULL 值处理、重复行处理）。所有查询重写建议必须经过人工审核和测试验证。

慢查询阈值的设定：阈值过高会遗漏大量可优化的查询，过低会产生大量噪音。建议采用动态阈值：基于查询类型和历史执行时间的百分位数设定，而非固定值。

五、总结

AI 辅助的 SQL 性能诊断将慢查询优化从"依赖 DBA 经验"推进到"规则检测 + AI 推荐"的自动化模式。落地路线上，建议先部署慢查询采集和规则引擎，覆盖最常见的反模式，再接入 AI 索引推荐处理复杂场景。关键原则：AI 推荐是起点而非终点，所有索引变更必须经过验证和灰度发布，写入性能的代价必须纳入评估。