MyBatis 日志与调试技巧：让 SQL 执行过程完全透明

🔍 MyBatis 日志与调试技巧：让 SQL 执行过程完全透明

🧠 一、MyBatis 日志体系概览

💡 MyBatis 日志模块架构

​​MyBatis 日志核心特点​​：

• 🔌 ​​插件化架构​​：支持多种日志框架
• 📝 ​​分级输出​​：不同级别显示不同信息
• 🔍 ​​参数可视化​​：支持SQL参数占位符替换
• ⚡ ​​性能可控​​：避免日志输出影响性能

📊 日志级别与信息对应关系

⚙️ 二、日志工厂配置详解

💡 日志工厂配置方式

MyBatis 支持多种日志实现，以下是常见配置方式：

​​1. mybatis-config.xml 配置​​：

<configuration>
    <settings>
        <!-- 设置日志实现 -->
        <setting name="logImpl" value="SLF4J"/>
    </settings>
</configuration>

​​2. Spring Boot 配置​​：

# application.yml
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl

​​3. 代码配置​​：

SqlSessionFactory factory = new SqlSessionFactoryBuilder()
    .build(inputStream);
    
org.apache.ibatis.logging.LogFactory.useSlf4jLogging();

🔧 各日志框架配置示例

​​SLF4J + Logback 配置​​：

<!-- logback.xml -->
<configuration>
    <!-- MyBatis SQL日志单独配置 -->
    <logger name="com.example.mapper" level="DEBUG" additivity="false">
        <appender-ref ref="SQL_APPENDER"/>
    </logger>
    
    <appender name="SQL_APPENDER" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>%d{HH:mm:ss.SSS} [%thread] %-5level %logger{36} - %msg%n</pattern>
        </encoder>
    </appender>
    
    <root level="INFO">
        <appender-ref ref="SQL_APPENDER"/>
    </root>
</configuration>

​​Log4J2 配置​​：

<!-- log4j2.xml -->
<Configuration>
    <Loggers>
        <!-- MyBatis Mapper层日志 -->
        <Logger name="com.example.mapper" level="debug" additivity="false">
            <AppenderRef ref="SqlConsole"/>
        </Logger>
        
        <Root level="info">
            <AppenderRef ref="Console"/>
        </Root>
    </Loggers>
    
    <Appenders>
        <Console name="SqlConsole" target="SYSTEM_OUT">
            <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n"/>
        </Console>
    </Appenders>
</Configuration>

🔍 三、SQL 打印与参数还原

💡 开启完整SQL日志输出

# application.properties
# 开启MyBatis SQL日志
logging.level.com.example.mapper=DEBUG

# 显示SQL参数（Log4J2特有）
logging.level.org.apache.ibatis.transaction.jdbc.JdbcTransaction=DEBUG
logging.level.org.apache.ibatis.executor.BaseExecutor=DEBUG

🎯 日志输出效果对比

​​默认输出（参数占位符）​​：

DEBUG: ==>  Preparing: SELECT * FROM users WHERE id = ? AND status = ? 
DEBUG: ==> Parameters: 1(Integer), 1(Integer)
DEBUG: <==      Total: 1

​​参数替换后输出​​（需要额外配置）：

DEBUG: Executing SQL: SELECT * FROM users WHERE id = 1 AND status = 1
DEBUG: Execution time: 45ms

🔧 配置SQL参数美化输出

// 自定义SQL日志拦截器
@Intercepts({
    @Signature(type = Executor.class, method = "update", 
              args = {MappedStatement.class, Object.class}),
    @Signature(type = Executor.class, method = "query", 
              args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
public class SqlLogInterceptor implements Interceptor {
    
    private static final Logger logger = LoggerFactory.getLogger("SQL_LOGGER");
    
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
        Object parameter = invocation.getArgs()[1];
        
        BoundSql boundSql = ms.getBoundSql(parameter);
        String sql = boundSql.getSql();
        Object parameterObject = boundSql.getParameterObject();
        
        // 美化SQL输出
        String formattedSql = formatSql(sql, parameterObject);
        long start = System.currentTimeMillis();
        
        try {
            return invocation.proceed();
        } finally {
            long cost = System.currentTimeMillis() - start;
            logger.debug("SQL: {} | Time: {}ms", formattedSql, cost);
        }
    }
    
    private String formatSql(String sql, Object parameter) {
        // 简单的参数替换逻辑
        if (parameter instanceof Map) {
            Map<?, ?> paramMap = (Map<?, ?>) parameter;
            for (Map.Entry<?, ?> entry : paramMap.entrySet()) {
                String key = ":" + entry.getKey();
                String value = entry.getValue() != null ? entry.getValue().toString() : "NULL";
                sql = sql.replace(key, "'" + value + "'");
            }
        }
        return sql.replaceAll("\\s+", " ").trim();
    }
}

📊 四、性能追踪与慢SQL监控

💡 慢SQL检测配置

# Spring Boot Actuator 监控配置
management:
  endpoints:
    web:
      exposure:
        include: health,metrics,prometheus
  endpoint:
    health:
      show-details: always
  metrics:
    distribution:
      percentiles:
        mybatis.sql.timer: 0.5,0.95,0.99

# 数据源监控
spring:
  datasource:
    hikari:
      metrics-tracker: true

🔧 慢SQL监控实现

// 慢SQL检测拦截器
@Component
@Slf4j
public class SlowSqlInterceptor implements Interceptor {
    
    private static final long SLOW_SQL_THRESHOLD = 1000; // 1秒
    
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long startTime = System.currentTimeMillis();
        
        try {
            return invocation.proceed();
        } finally {
            long costTime = System.currentTimeMillis() - startTime;
            if (costTime > SLOW_SQL_THRESHOLD) {
                // 记录慢SQL
                log.warn("Slow SQL detected: {}ms", costTime);
                
                // 获取SQL信息
                MappedStatement ms = (MappedStatement) invocation.getArgs()[0];
                Object parameter = invocation.getArgs()[1];
                BoundSql boundSql = ms.getBoundSql(parameter);
                
                log.warn("Slow SQL: {}", boundSql.getSql());
                log.warn("Parameters: {}", boundSql.getParameterObject());
                
                // 发送告警（可选）
                alertSlowSql(boundSql.getSql(), costTime);
            }
            
            // 记录指标
            Metrics.timer("sql.execution.time").record(costTime, TimeUnit.MILLISECONDS);
        }
    }
    
    private void alertSlowSql(String sql, long costTime) {
        // 集成告警系统，如发送邮件、短信等
    }
}

📈 SQL性能监控配置

// Micrometer SQL监控
@Configuration
public class MetricsConfig {
    
    @Bean
    public MeterRegistryCustomizer<MeterRegistry> metricsCustomizer() {
        return registry -> {
            // 监控SQL执行时间
            Timer.builder("sql.execution.time")
                .description("SQL execution time")
                .register(registry);
            
            // 监控SQL错误率
            Counter.builder("sql.error.count")
                .description("SQL error count")
                .register(registry);
        };
    }
}

🛠️ 五、实战调试技巧

💡 常见问题排查指南

🔧 高级调试技巧

​​1. 使用P6Spy进行SQL监控​​：

# 使用P6Spy数据源
spring:
  datasource:
    url: jdbc:p6spy:mysql://localhost:3306/test
    driver-class-name: com.p6spy.engine.spy.P6SpyDriver

# p6spy.properties
modulelist=com.p6spy.engine.logging.P6LogFactory,com.p6spy.engine.outage.P6OutageFactory
logMessageFormat=com.p6spy.engine.spy.appender.CustomLineFormat
customLogMessageFormat=%(currentTime)|%(executionTime)|%(category)|%(sql)

2. MyBatis原生参数打印​​：

<!-- 在mybatis-config.xml中开启详细日志 -->
<settings>
    <setting name="logPrefix" value="MYBATIS_DEBUG."/>
</settings>

3. 动态调整日志级别​​：

// 运行时动态调整日志级别
@RestController
public class LogLevelController {
    
    @PostMapping("/admin/log-level")
    public String changeLogLevel(@RequestParam String level) {
        LoggerContext loggerContext = (LoggerContext) LoggerFactory.getILoggerFactory();
        
        // 修改MyBatis Mapper的日志级别
        loggerContext.getLogger("com.example.mapper").setLevel(Level.valueOf(level));
        
        return "Log level changed to: " + level;
    }
}

📊 调试工具对比

💡 六、总结与最佳实践

📚 日志配置最佳实践

​​开发环境配置​​：

# application-dev.yml
logging:
  level:
    com.example.mapper: DEBUG
    org.apache.ibatis: TRACE
  pattern:
    console: "%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger{36} - %msg%n"

​​生产环境配置​​：

# application-prod.yml
logging:
  level:
    com.example.mapper: WARN
    org.apache.ibatis: ERROR
  file:
    path: /app/logs
    name: /app/logs/app.log

🚀 性能与可读性平衡建议

🛡️ 生产环境建议

1. ​​避免过度日志​​：生产环境使用WARN级别，避免性能影响
2. 日志分离​​：将SQL日志单独输出到文件，便于分析
3. 监控告警​​：设置慢SQL告警阈值（如：>1秒） ​​
4. 定期审计​​：定期分析SQL日志，优化性能瓶颈

🔧 快速调试清单

1. ✅ 检查日志级别配置
2. ✅ 确认Mapper包路径正确
3. ✅ 验证日志框架依赖
4. ✅ 测试参数显示是否正常
5. ✅ 确认慢SQL监控生效