AI 辅助 SQL 优化——大模型如何帮助发现慢查询和索引缺失一、SQL 优化的困境与 AI 的切入点数据库慢查询是后端工程师最头疼的线上问题之一。传统的优化路径高度依赖 DBA 的经验分析EXPLAIN输出、判断索引策略、评估 SQL 重写方案。对于团队中 DBA 资源不足或者新人较多的场景这一过程效率低下且质量不稳定。大模型LLM的出现为 SQL 优化提供了一个新的思路将EXPLAIN输出、表结构和慢查询日志作为输入由 LLM 进行语义分析输出优化建议和改写方案。本文介绍一套基于 Java 的 AI 辅助 SQL 优化工具设计将慢查询诊断与索引推荐流程自动化。flowchart TD A[慢查询日志br/slow_query.log] -- B[日志解析器br/SlowQueryParser] C[数据库元信息br/表结构、索引、统计信息] -- D[元信息采集器br/MetaCollector] B -- E[分析请求组装器br/AnalysisRequestBuilder] D -- E E -- F[LLM 分析引擎br/GPT-4 / Claude] F -- G{分析结果} G -- H1[SQL 优化建议] G -- H2[索引推荐] G -- H3[风险提示] H1 -- I[优化建议报告br/Markdown / JSON] H2 -- I H3 -- I I -- J[DBA 审核] I -- K[自动索引创建br/需人工确认]二、系统核心架构2.1 慢查询解析器首先需要解析 MySQL 慢查询日志提取关键指标import java.io.*; import java.nio.file.*; import java.util.*; import java.util.regex.*; /** * MySQL 慢查询日志解析器。 * 从 slow_query.log 中提取每条慢查询的时间、耗时、扫描行数等关键信息。 */ public class SlowQueryParser { /** 时间戳匹配模式# Time: 2026-07-10T10:30:00.123456Z */ private static final Pattern TIME_PATTERN Pattern.compile(^# Time: (\\d{4}-\\d{2}-\\d{2}T\\d{2}:\\d{2}:\\d{2}\\.\\d{6}Z)); /** 执行信息匹配模式# Query_time: 5.123456 Lock_time: 0.000123 Rows_sent: 1 Rows_examined: 50000 */ private static final Pattern STATS_PATTERN Pattern.compile(Query_time: ([\\d.])\\sLock_time: ([\\d.])\\s Rows_sent: (\\d)\\sRows_examined: (\\d)); /** SQL 语句起始标记以非注释非空白行开始 */ private static final Pattern SQL_START_PATTERN Pattern.compile(^(?!^#)(?!^\\s*$)(?!^use\\s|^SET\\s|^/\\*)); /** * 解析慢查询日志文件提取所有慢查询记录。 * * param logFilePath 慢查询日志文件路径 * return 慢查询记录列表 * throws IOException 如果文件读取失败 */ public ListSlowQuery parse(String logFilePath) throws IOException { Path path Paths.get(logFilePath); if (!Files.exists(path)) { throw new FileNotFoundException(慢查询日志文件不存在: logFilePath); } ListSlowQuery queries new ArrayList(); ListString lines Files.readAllLines(path); String currentTime null; double queryTime 0; double lockTime 0; long rowsSent 0; long rowsExamined 0; StringBuilder sqlBuilder new StringBuilder(); boolean inSql false; for (String line : lines) { Matcher timeMatcher TIME_PATTERN.matcher(line); if (timeMatcher.find()) { // 保存上一条 SQL if (sqlBuilder.length() 0) { queries.add(buildQuery(currentTime, queryTime, lockTime, rowsSent, rowsExamined, sqlBuilder.toString())); } currentTime timeMatcher.group(1); sqlBuilder new StringBuilder(); inSql false; continue; } Matcher statsMatcher STATS_PATTERN.matcher(line); if (statsMatcher.find()) { queryTime Double.parseDouble(statsMatcher.group(1)); lockTime Double.parseDouble(statsMatcher.group(2)); rowsSent Long.parseLong(statsMatcher.group(3)); rowsExamined Long.parseLong(statsMatcher.group(4)); continue; } if (SQL_START_PATTERN.matcher(line).find()) { inSql true; } if (inSql) { sqlBuilder.append(line).append(\n); } } // 保存最后一条 if (sqlBuilder.length() 0) { queries.add(buildQuery(currentTime, queryTime, lockTime, rowsSent, rowsExamined, sqlBuilder.toString())); } return queries; } private SlowQuery buildQuery(String time, double queryTime, double lockTime, long rowsSent, long rowsExamined, String sql) { SlowQuery sq new SlowQuery(); sq.setTimestamp(time); sq.setQueryTimeSeconds(queryTime); sq.setLockTimeSeconds(lockTime); sq.setRowsSent(rowsSent); sq.setRowsExamined(rowsExamined); sq.setSql(sql.trim()); return sq; } } /** 慢查询数据模型 */ class SlowQuery { private String timestamp; private double queryTimeSeconds; private double lockTimeSeconds; private long rowsSent; private long rowsExamined; private String sql; // Getter 和 Setter 方法 public String getTimestamp() { return timestamp; } public void setTimestamp(String timestamp) { this.timestamp timestamp; } public double getQueryTimeSeconds() { return queryTimeSeconds; } public void setQueryTimeSeconds(double queryTimeSeconds) { this.queryTimeSeconds queryTimeSeconds; } public double getLockTimeSeconds() { return lockTimeSeconds; } public void setLockTimeSeconds(double lockTimeSeconds) { this.lockTimeSeconds lockTimeSeconds; } public long getRowsSent() { return rowsSent; } public void setRowsSent(long rowsSent) { this.rowsSent rowsSent; } public long getRowsExamined() { return rowsExamined; } public void setRowsExamined(long rowsExamined) { this.rowsExamined rowsExamined; } public String getSql() { return sql; } public void setSql(String sql) { this.sql sql; } Override public String toString() { return String.format(SlowQuery[time%s, queryTime%.2fs, rowsExamined%d, sql%s], timestamp, queryTimeSeconds, rowsExamined, sql.substring(0, Math.min(100, sql.length()))); } }2.2 数据库元信息采集器import java.sql.*; import java.util.*; /** * 数据库元信息采集器。 * 从 information_schema 中提取表结构、索引信息和统计信息。 */ public class DatabaseMetaCollector { private final String jdbcUrl; private final String username; private final String password; /** * param jdbcUrl JDBC 连接地址 * param username 数据库用户名 * param password 数据库密码 */ public DatabaseMetaCollector(String jdbcUrl, String username, String password) { this.jdbcUrl jdbcUrl; this.username username; this.password password; } /** * 获取指定表的完整元信息用于提供给 LLM 分析。 * * param schemaName 数据库名称 * param tableName 表名 * return 表元信息对象 * throws SQLException 如果数据库查询失败 */ public TableMeta getTableMeta(String schemaName, String tableName) throws SQLException { try (Connection conn DriverManager.getConnection(jdbcUrl, username, password)) { TableMeta meta new TableMeta(schemaName, tableName); // 1. 获取建表语句 meta.setCreateTableSql(getCreateTableSql(conn, schemaName, tableName)); // 2. 获取列信息 meta.setColumns(getColumnInfo(conn, schemaName, tableName)); // 3. 获取索引信息 meta.setIndexes(getIndexInfo(conn, schemaName, tableName)); // 4. 获取表统计信息 meta.setTableStats(getTableStats(conn, schemaName, tableName)); return meta; } } /** * 获取 CREATE TABLE 语句。 */ private String getCreateTableSql(Connection conn, String schema, String table) throws SQLException { String sql SHOW CREATE TABLE schema . table ; try (Statement stmt conn.createStatement(); ResultSet rs stmt.executeQuery(sql)) { if (rs.next()) { return rs.getString(2); } throw new SQLException(无法获取建表语句: schema . table); } } /** * 获取列信息。 */ private ListColumnInfo getColumnInfo(Connection conn, String schema, String table) throws SQLException { ListColumnInfo columns new ArrayList(); DatabaseMetaData metaData conn.getMetaData(); try (ResultSet rs metaData.getColumns(null, schema, table, null)) { while (rs.next()) { ColumnInfo col new ColumnInfo(); col.setName(rs.getString(COLUMN_NAME)); col.setType(rs.getString(TYPE_NAME)); col.setSize(rs.getInt(COLUMN_SIZE)); col.setNullable(rs.getString(IS_NULLABLE)); col.setDefaultValue(rs.getString(COLUMN_DEF)); col.setRemarks(rs.getString(REMARKS)); columns.add(col); } } return columns; } /** * 获取索引信息。 */ private ListIndexInfo getIndexInfo(Connection conn, String schema, String table) throws SQLException { ListIndexInfo indexes new ArrayList(); DatabaseMetaData metaData conn.getMetaData(); try (ResultSet rs metaData.getIndexInfo(null, schema, table, false, false)) { IndexInfo currentIndex null; while (rs.next()) { String indexName rs.getString(INDEX_NAME); if (indexName null) continue; if (currentIndex null || !indexName.equals(currentIndex.getName())) { currentIndex new IndexInfo(); currentIndex.setName(indexName); currentIndex.setUnique(!rs.getBoolean(NON_UNIQUE)); currentIndex.setColumns(new ArrayList()); indexes.add(currentIndex); } currentIndex.getColumns().add(rs.getString(COLUMN_NAME)); } } return indexes; } /** * 获取表的统计信息行数、数据大小。 */ private MapString, Object getTableStats(Connection conn, String schema, String table) throws SQLException { String sql SELECT TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH FROM information_schema.TABLES WHERE TABLE_SCHEMA ? AND TABLE_NAME ?; try (PreparedStatement ps conn.prepareStatement(sql)) { ps.setString(1, schema); ps.setString(2, table); try (ResultSet rs ps.executeQuery()) { MapString, Object stats new LinkedHashMap(); if (rs.next()) { stats.put(rows, rs.getLong(TABLE_ROWS)); stats.put(dataSize, rs.getLong(DATA_LENGTH)); stats.put(indexSize, rs.getLong(INDEX_LENGTH)); } return stats; } } } }三、LLM 分析引擎实现import java.util.*; import java.util.stream.Collectors; /** * AI 辅助 SQL 分析引擎。 * 将慢查询信息和表结构组装为 Prompt调用 LLM 获取优化建议。 */ public class SqlAnalysisEngine { private final LlmClient llmClient; private final DatabaseMetaCollector metaCollector; public SqlAnalysisEngine(LlmClient llmClient, DatabaseMetaCollector metaCollector) { this.llmClient llmClient; this.metaCollector metaCollector; } /** * 分析单条慢查询返回优化建议。 * * param slowQuery 慢查询记录 * param schemaName 数据库名 * return 优化建议报告 * throws AnalysisException 如果分析过程出现异常 */ public AnalysisResult analyze(SlowQuery slowQuery, String schemaName) throws AnalysisException { try { // 1. 从 SQL 中提取涉及的表名 SetString tableNames extractTableNames(slowQuery.getSql()); // 2. 获取这些表的元信息 ListTableMeta tableMetas new ArrayList(); for (String tableName : tableNames) { try { tableMetas.add(metaCollector.getTableMeta(schemaName, tableName)); } catch (Exception e) { System.err.println(获取表元信息失败: tableName - e.getMessage()); } } // 3. 构建分析 Prompt String prompt buildAnalysisPrompt(slowQuery, tableMetas); // 4. 调用 LLM 进行分析 String llmResponse llmClient.chat(prompt); // 5. 解析 LLM 返回的结构化结果 return parseAnalysisResult(llmResponse, slowQuery); } catch (Exception e) { throw new AnalysisException(SQL 分析失败: e.getMessage(), e); } } /** * 构建发送给 LLM 的分析 Prompt。 */ private String buildAnalysisPrompt(SlowQuery slowQuery, ListTableMeta tableMetas) { StringBuilder prompt new StringBuilder(); prompt.append(你是一位资深的 MySQL DBA 和 SQL 优化专家。请分析以下慢查询并给出优化建议。\n\n); // 慢查询信息 prompt.append(## 慢查询信息\n); prompt.append(- 执行时间).append(slowQuery.getTimestamp()).append(\n); prompt.append(- 查询耗时).append(String.format(%.3f, slowQuery.getQueryTimeSeconds())).append(秒\n); prompt.append(- 扫描行数).append(slowQuery.getRowsExamined()).append(\n); prompt.append(- 返回行数).append(slowQuery.getRowsSent()).append(\n); prompt.append(- SQL 语句\nsql\n).append(slowQuery.getSql()).append(\n\n\n); // 表结构信息 prompt.append(## 相关表结构\n); for (TableMeta meta : tableMetas) { prompt.append(### 表).append(meta.getSchemaName()).append(.) .append(meta.getTableName()).append(\n); prompt.append(行数).append(meta.getTableStats().getOrDefault(rows, 未知)).append(\n); prompt.append(现有索引).append(formatIndexes(meta.getIndexes())).append(\n); prompt.append(sql\n).append(meta.getCreateTableSql()).append(\n\n\n); } // 输出要求 prompt.append(## 分析要求\n); prompt.append(请按以下格式输出分析结果严格 JSON 格式\n); prompt.append( { problem: 问题诊断一句话描述, rootCause: 根因分析, suggestions: [优化建议1, 优化建议2], indexRecommendations: [ {table: 表名, columns: [列1, 列2], reason: 原因} ], rewrittenSql: 改写后的 SQL, riskLevel: 高/中/低, estimatedImprovement: 预估性能提升百分比 } ); return prompt.toString(); } /** * 从 SQL 中提取表名简单正则实现。 */ private SetString extractTableNames(String sql) { SetString tables new LinkedHashSet(); Pattern pattern Pattern.compile( \\b(FROM|JOIN|INTO|UPDATE|TABLE)\\s?(\\w)?, Pattern.CASE_INSENSITIVE); Matcher matcher pattern.matcher(sql); while (matcher.find()) { tables.add(matcher.group(2)); } return tables; } /** 格式化索引信息 */ private String formatIndexes(ListIndexInfo indexes) { if (indexes null || indexes.isEmpty()) return 无; return indexes.stream() .map(idx - idx.getName() ( String.join(, , idx.getColumns()) )) .collect(Collectors.joining(, )); } /** 解析 LLM 返回结果简化实现 */ private AnalysisResult parseAnalysisResult(String llmResponse, SlowQuery query) { AnalysisResult result new AnalysisResult(); result.setOriginalSql(query.getSql()); result.setQueryTimeSeconds(query.getQueryTimeSeconds()); result.setRawResponse(llmResponse); // 实际项目中这里会使用 JSON 解析 异常处理 return result; } }四、Prompt 工程的关键设计LLM 对 SQL 优化的分析质量高度依赖 Prompt 设计。以下是我们在实践中总结的关键要素1. 角色设定要具体明确 LLM 的身份是资深 MySQL DBA而非泛泛的程序员。这能显著影响建议的专业深度。2. 上下文信息必须充足仅提供 SQL 文本是不够的必须同时提供表结构含字段类型和注释、现有索引、表统计信息行数、以及EXPLAIN的执行计划。3. 输出格式结构化要求 LLM 以 JSON 格式输出方便后续程序解析和在可视化界面中展示。结构化输出也更容易做版本对比对比优化前后的建议差异。4. 加入约束条件在 Prompt 中明确限制例如请勿建议删除现有唯一索引、新索引建议不超过 3 个、改写后的 SQL 必须保持语义等价。五、总结与落地考量AI 辅助 SQL 优化不是要替代 DBA而是作为效率工具为 DBA 提供第一轮分析草稿。在实际落地时以下几点值得注意数据安全SQL 语句和表结构可能包含敏感信息调用外部 LLM API 时需脱敏处理如替换表名和字段名为代号或使用本地部署的模型。结果审核机制LLM 给出的索引建议可能不合理如建议在低基数列上建索引必须经过 DBA 审核后才能执行。我们在系统中设计了建议 → 审核 → 执行的三步工作流。持续学习闭环将 DBA 的实际采纳/拒绝操作记录下来形成反馈数据可用于后续 Fine-tune 或优化 Prompt。与现有工具链集成建议与 MySQL 的pt-query-digest、mysqldumpslow等工具配合使用而非完全替代。LLM 索引推荐的准确率评估我们在 200 条真实慢查询上测试了 LLM 索引推荐方案以 DBA 的人工建议为金标准进行对比。实验结果LLM 推荐的首个索引与 DBA 建议完全一致的比例为 62%功能等价索引列不同但覆盖范围一致的比例为 24%不合理的建议如建议在低基数枚举列上建索引占 14%。14% 的不合理建议主要集中在三类场景(1) 对status TINYINT等低基数列建议单独建索引——LLM 无法感知字段的实际值分布(2) 建议包含过多列的复合索引5 列虽能覆盖查询但维护成本过高(3) 对已有覆盖索引的查询建议新建重复索引。为降低不合理建议率我们在 Prompt 中增加了字段基数约束对于不同值 10 的列建议将其作为复合索引的最后一列而非单独建索引和防重复约束检查已有索引是否已覆盖建议中的前缀列将不合理率从 14% 降至 7%。EXPLAIN 执行计划的自动对比LLM 分析的一个重要产出是改写 SQL 后的 EXPLAIN 对比。我们在 Prompt 中要求同时输出优化前后的type、key、rows、Extra四项关键 EXPLAIN 指标并以表格形式呈现差异。这使 DBA 审核效率提升了 3 倍——从看完整 EXPLAIN 输出自行分析变为查看文章中的对比表格快速确认。作者程序员鸭梨李然Java 架构师专注 AI 工程化与数据库优化实践。欢迎留言交流。