如何用SchemaCrawler结合Spring Boot检测MySQL/PGS数据库查询性能

一、修复EXPLAIN结果获取问题 #

你当前代码看不到EXPLAIN输出的核心原因是：Statement.execute()返回的是布尔值，仅表示是否存在结果集，而非结果内容。要获取EXPLAIN的具体输出，需要处理返回的ResultSet。

修正后的代码片段 #

替换你原代码中的try-catch块部分：

// 使用SchemaCrawler提供的方法获取带限定名的表，避免SQL语法错误
var sql = "EXPLAIN SELECT * FROM " + table.getQualifiedName();
try (Statement statement = dataSource.get().createStatement()) {
    // 直接用executeQuery获取结果集
    ResultSet rs = statement.executeQuery(sql);
    ResultSetMetaData metaData = rs.getMetaData();
    int columnCount = metaData.getColumnCount();
    
    // 遍历结果集，适配不同数据库的EXPLAIN输出格式
    while (rs.next()) {
        StringBuilder explainResult = new StringBuilder();
        for (int i = 1; i <= columnCount; i++) {
            explainResult.append(rs.getString(i)).append(" ");
        }
        System.out.println("EXPLAIN结果: " + explainResult.toString().trim());
    }
} catch (Exception e) {
    System.err.println("执行EXPLAIN失败: " + e.getMessage());
}

关键注意点 #

• 用table.getQualifiedName()代替直接拼接table：SchemaCrawler的Table对象toString输出可能缺少模式名或引号，导致跨数据库的SQL语法错误。
• 使用try-with-resources：自动关闭Statement，避免资源泄漏。
• 适配多数据库：MySQL和PG的EXPLAIN输出列结构不同，遍历所有列可兼容两种数据库的结果。

二、基于SchemaCrawler的数据库性能检测建议 #

1. 加载表统计与索引元数据 #

SchemaCrawler可直接获取数据库的核心性能相关元数据，无需手动查询系统表：

SchemaCrawlerOptions options = new SchemaCrawlerOptions();
options.setTableInclusionRule(new IncludeAll());
options.setLoadTableStats(true); // 加载表的行数、数据大小等统计信息
options.setLoadIndexes(true); // 强制加载所有索引信息

Catalog catalog = SchemaCrawlerUtility.getCatalog(connection, options);

拿到元数据后可直接分析：

• 检查大表（table.getSizeInBytes()）是否缺少索引。
• 验证索引列是否与常用查询的过滤条件匹配。

2. 结合EXPLAIN与元数据做针对性分析 #

遍历表时，用SchemaCrawler获取的索引列表，对比EXPLAIN结果：

• 如果EXPLAIN出现Seq Scan（PG）或ALL（MySQL），说明执行了全表扫描，再检查表是否存在对应查询条件的索引。
• 通过table.getIndexes()获取索引列，判断是否有可以优化的索引（比如复合索引顺序是否合理）。

3. 执行数据库原生性能查询 #

用SchemaCrawler的QueryExecutor工具，直接执行数据库自带的性能分析语句：

QueryExecutor executor = new QueryExecutor(connection);
// PG示例：查询执行时间超过1秒的语句
ResultSet slowQueries = executor.executeQuery("SELECT queryid, query, total_time FROM pg_stat_statements WHERE total_time > 1000");
// MySQL示例：查询慢查询日志
ResultSet mysqlSlowLogs = executor.executeQuery("SELECT * FROM mysql.slow_log WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 HOUR)");

4. 生成元数据快照做趋势对比 #

定期用SchemaCrawler生成数据库元数据快照，对比不同时间点的变化：

SchemaCrawlerUtility.writeCatalog(catalog, options, new FileWriter("schema-snapshot.json"));

通过快照对比可发现：

• 新增的无索引大表。
• 被删除或修改的关键索引。
• 表结构变更导致的性能隐患。

内容的提问来源于stack exchange，提问作者user2473907