Ubuntu Java日志中慢查询怎么优化

Ubuntu Java日志中的慢查询优化实战

一定位与抓取慢查询

在应用侧打开SQL耗时日志，优先输出超过阈值的SQL（如500ms），便于与数据库慢日志对齐。示例（MyBatis 与 Logback/SLF4J）：

MyBatis 输出到控制台：

mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

或用拦截器记录慢SQL（超过500ms打印）：

@Component
@Intercepts({@Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class})})
public class SqlExecutionTimeInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long start = System.currentTimeMillis();
        try {
            return invocation.proceed();
        } finally {
            long cost = System.currentTimeMillis() - start;
            if (cost > 500) {
                StatementHandler sh = (StatementHandler) invocation.getTarget();
                BoundSql bs = sh.getBoundSql();
                System.out.printf("慢查询预警：SQL执行时间=%dms，SQL=%s%n", cost, bs.getSql());
            }
        }
    }
}

在数据库侧开启慢查询日志（以MySQL为例）：

动态开启（无需重启）：

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;
SET GLOBAL slow_query_log_file = '/var/lib/mysql/slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 可选：记录未走索引的SQL

永久开启（my.cnf）：

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 0.5
log_queries_not_using_indexes = ON

实时抓取与分析：
- 使用 Percona Toolkit 的 pt-query-digest 对慢日志或 processlist 做聚合分析，快速找出 Top N 高消耗 SQL：
```
pt-query-digest --processlist h=localhost,u=root,p=123456 --limit 10
```
- 结合 Performance Schema 实时监控正在执行的高消耗 SQL 与等待事件，用于应急定位。

二用 Explain 执行计划精准定位瓶颈

对抓到的慢SQL执行 EXPLAIN，重点看以下字段：
- type：访问类型，优先级从高到低为 system > const > eq_ref > ref > range > index > ALL，尽量优化到 ref/range，避免 ALL（全表扫描）。
- key / possible_keys：确认是否命中索引；possible_keys 有值但 key 为 NULL，说明索引未被使用。
- rows：扫描行数，数值越大越需优化。
- Extra：避免 Using filesort（文件排序）与 Using temporary（临时表）。
常见场景与优化要点（示例）：
- 全表扫描（type=ALL）：为 WHERE 条件建立合适的联合索引，如
```
CREATE INDEX idx_user_create ON `order`(user_id, create_time);
```
- 索引失效（对索引字段做函数）：改写为不破坏索引的形式，例如避免 UPPER(username)，在 Java 端先转大写再入参。
- 排序导致 Using filesort：建立覆盖索引，把排序字段纳入索引，如
```
CREATE INDEX idx_status_age ON user(status, age DESC);
```
- 分组导致 Using temporary：为分组字段建立索引，减少临时表与排序，如
```
CREATE INDEX idx_user_id ON `order`(user_id);
```
- 深分页性能差：避免 LIMIT 10000, 20，改用基于主键/索引键的“seek 分页”，如
```
SELECT * FROM user WHERE id > 10000 ORDER BY id LIMIT 20;
```
- 只查需要的列，避免 SELECT *，以便利用覆盖索引减少回表。

三 Java 与数据库配置层面的优化

连接池与事务治理（以 HikariCP 为例）：
- 合理设置最大连接数、最小空闲、连接超时、空闲回收，避免慢查询耗尽连接池。
- 控制事务边界，避免在事务内执行耗时查询或远程调用；批量操作使用批处理接口，减少往返与锁持有时间。
ORM 与数据访问层：
- 开启 Hibernate/JPA 的 SQL 日志与告警，或使用 p6spy 代理数据源，记录真实执行的 SQL 与耗时，便于与慢日志对齐。
- 解决 N+1 查询（如用 @EntityGraph、Fetch Join 或批量预取），减少往返次数。
JVM 与系统资源（缓解 GC 停顿、提升吞吐，间接缩短 SQL 响应时间）：
- 选择合适的 JDK 版本，优先使用稳定版 OpenJDK/Oracle JDK。
- 设置合理的堆与 GC：如 -Xms 与 -Xmx 一致，启用 G1 GC（-XX:+UseG1GC），按需调节并行/并发 GC 线程数（-XX:ParallelGCThreads、-XX:ConcGCThreads）。
- 适度开启分层编译（-XX:+TieredCompilation），提升热点代码执行效率。
- 系统层面：提升文件描述符限制（ulimit -n）、降低 vm.swappiness、使用 SSD、优化 TCP 参数（如 net.core.somaxconn、net.ipv4.tcp_max_syn_backlog），并监控系统资源（top/htop、vmstat、iostat、dstat）。

四可落地的优化闭环与验证

建立“发现→定位→修复→验证”的闭环：
- 持续收集并分析慢查询（慢日志 + pt-query-digest + Performance Schema），每周产出 Top SQL 清单与优化报告。
- 在测试环境回归验证，观察关键指标：P95/P99 延迟、错误率、QPS/TPS、连接池等待、慢查询数量、磁盘与 CPU 使用率。
- 将优化前/后的 EXPLAIN 结果、执行时间与资源占用对比，固化索引与 SQL 规范，纳入代码评审与 CI/CD。
- 对高消耗 SQL 设置基线告警，结合 Spring Boot Actuator + Prometheus + Grafana 或 APM 做可视化与趋势分析。

最新问答

相关标签