@Repository
public class SlowQueryAnalyzer {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public List<Map<String, Object>> getSlowQueries() {
        String sql = "SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 100";
        return jdbcTemplate.queryForList(sql);
    }
    
    public void analyzeSlowQueries() {
        String sql = "SELECT COUNT(*) as count, db, sql_text, AVG(query_time) as avg_time " +
                     "FROM mysql.slow_log " +
                     "GROUP BY db, sql_text " +
                     "ORDER BY avg_time DESC " +
                     "LIMIT 10";
        List<Map<String, Object>> result = jdbcTemplate.queryForList(sql);
        
        // 處理結(jié)果...
    }
}

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-aop</artifactId>
</dependency>

@Aspect
@Component
@Slf4j
public class SlowQueryAspect {
    
    @Value("${slow.query.threshold:500}")
    private long slowQueryThreshold; // 默認(rèn)閾值500毫秒
    
    @Around("execution(* com.example.repository.*.*(..))")
    public Object around(ProceedingJoinPoint joinPoint) throws Throwable {
        String methodName = joinPoint.getSignature().toShortString();
        long startTime = System.currentTimeMillis();
        
        Object result = joinPoint.proceed();
        
        long executionTime = System.currentTimeMillis() - startTime;
        
        if (executionTime > slowQueryThreshold) {
            String args = Arrays.toString(joinPoint.getArgs());
            log.warn("Slow Query detected: {} with args {}, execution time: {} ms", 
                    methodName, args, executionTime);
            
            // 可以將慢查詢信息保存到數(shù)據(jù)庫(kù)或發(fā)送告警
            saveSlowQueryInfo(methodName, args, executionTime);
        }
        
        return result;
    }
    
    private void saveSlowQueryInfo(String methodName, String args, long executionTime) {
        // 保存慢查詢信息到數(shù)據(jù)庫(kù)或發(fā)送到監(jiān)控系統(tǒng)
    }
}

@Component
@Slf4j
public class SlowQueryEventListener {
    
    @Autowired
    private ApplicationEventPublisher eventPublisher;
    
    public void onSlowQuery(String methodName, String args, long executionTime) {
        SlowQueryEvent event = new SlowQueryEvent(this, methodName, args, executionTime);
        eventPublisher.publishEvent(event);
    }
    
    @EventListener
    public void handleSlowQueryEvent(SlowQueryEvent event) {
        // 處理慢查詢事件，如發(fā)送告警郵件、存儲(chǔ)到時(shí)序數(shù)據(jù)庫(kù)等
        log.warn("Handling slow query event: {}", event);
    }
}

@Getter
public class SlowQueryEvent extends ApplicationEvent {
    private final String methodName;
    private final String args;
    private final long executionTime;
    
    public SlowQueryEvent(Object source, String methodName, String args, long executionTime) {
        super(source);
        this.methodName = methodName;
        this.args = args;
        this.executionTime = executionTime;
    }
}

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-actuator</artifactId>
</dependency>
<dependency>
    <groupId>io.micrometer</groupId>
    <artifactId>micrometer-registry-prometheus</artifactId>
</dependency>

# 開(kāi)啟所有Actuator端點(diǎn)
management.endpoints.web.exposure.include=*
# 啟用數(shù)據(jù)庫(kù)指標(biāo)收集
management.metrics.enable.jdbc=true
# 配置Prometheus端點(diǎn)
management.metrics.export.prometheus.enabled=true

@Configuration
public class DataSourceProxyConfig {
    
    @Bean
    @Primary
    public DataSource dataSource(DataSource originalDataSource, MeterRegistry meterRegistry) {
        return ProxyDataSourceBuilder
                .create(originalDataSource)
                .name("metrics-ds")
                .listener(new QueryExecutionListener() {
                    @Override
                    public void beforeQuery(ExecutionInfo execInfo, List<QueryInfo> queryInfoList) {
                        // 查詢執(zhí)行前的操作
                    }
                    
                    @Override
                    public void afterQuery(ExecutionInfo execInfo, List<QueryInfo> queryInfoList) {
                        long elapsedTime = execInfo.getElapsedTime();
                        
                        // 記錄查詢時(shí)間指標(biāo)
                        Timer.builder("datasource.query.time")
                                .tag("success", String.valueOf(execInfo.isSuccess()))
                                .tag("query", getSafeQueryName(queryInfoList))
                                .register(meterRegistry)
                                .record(elapsedTime, TimeUnit.MILLISECONDS);
                        
                        // 檢測(cè)慢查詢并記錄
                        if (elapsedTime > 500) { // 500ms閾值
                            Counter.builder("datasource.slow.queries")
                                    .tag("query", getSafeQueryName(queryInfoList))
                                    .register(meterRegistry)
                                    .increment();
                            
                            // 可以記錄慢查詢?nèi)罩?
                            logSlowQuery(execInfo, queryInfoList);
                        }
                    }
                    
                    private String getSafeQueryName(List<QueryInfo> queryInfoList) {
                        if (queryInfoList.isEmpty()) {
                            return "unknown";
                        }
                        
                        String sql = queryInfoList.get(0).getQuery();
                        // 簡(jiǎn)化SQL以避免過(guò)多的唯一標(biāo)簽
                        return DigestUtils.md5DigestAsHex(sql.getBytes()).substring(0, 8);
                    }
                    
                    private void logSlowQuery(ExecutionInfo execInfo, List<QueryInfo> queryInfoList) {
                        // 記錄慢查詢?cè)斍?
                    }
                })
                .build();
    }
}

<dependency>
    <groupId>net.ttddyy</groupId>
    <artifactId>datasource-proxy</artifactId>
    <version>1.9</version>
</dependency>

@Component
@Endpoint(id = "slowqueries")
public class SlowQueryEndpoint {
    
    @Autowired
    private MeterRegistry meterRegistry;
    
    @ReadOperation
    public Map<String, Object> slowQueries() {
        Map<String, Object> result = new HashMap<>();
        
        // 獲取慢查詢計(jì)數(shù)器
        List<Meter> meters = meterRegistry.getMeters().stream()
                .filter(m -> m.getId().getName().equals("datasource.slow.queries"))
                .collect(Collectors.toList());
        
        Map<String, Double> queryCounts = new HashMap<>();
        
        for (Meter meter : meters) {
            String query = meter.getId().getTag("query");
            double count = ((Counter) meter).count();
            queryCounts.put(query, count);
        }
        
        result.put("counts", queryCounts);
        
        // 獲取慢查詢時(shí)間分布
        List<Meter> timers = meterRegistry.getMeters().stream()
                .filter(m -> m.getId().getName().equals("datasource.query.time"))
                .collect(Collectors.toList());
        
        Map<String, Map<String, Object>> queryTimes = new HashMap<>();
        
        for (Meter meter : timers) {
            String query = meter.getId().getTag("query");
            Timer timer = (Timer) meter;
            
            Map<String, Object> stats = new HashMap<>();
            stats.put("count", timer.count());
            stats.put("max", timer.max(TimeUnit.MILLISECONDS));
            stats.put("mean", timer.mean(TimeUnit.MILLISECONDS));
            stats.put("percentile95", timer.takeSnapshot().percentileValues()[0].value(TimeUnit.MILLISECONDS));
            
            queryTimes.put(query, stats);
        }
        
        result.put("times", queryTimes);
        
        return result;
    }
}

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
    <version>3.9.1</version>
</dependency>

# 原始配置
#spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#spring.datasource.url=jdbc:mysql://localhost:3306/test

# P6Spy配置
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver
spring.datasource.url=jdbc:p6spy:mysql://localhost:3306/test

# 指定日志輸出模塊
appender=com.p6spy.engine.spy.appender.Slf4JLogger
# 日志格式
logMessageFormat=com.example.config.CustomP6SpyLogFormat
# 是否開(kāi)啟慢SQL記錄
outagedetection=true
# 慢SQL閾值（毫秒）
outagedetectioninterval=2000
# 設(shè)置 p6spy driver 代理
deregisterdrivers=true
# 日期格式
dateformat=yyyy-MM-dd HH:mm:ss
# 實(shí)際驅(qū)動(dòng)
driverlist=com.mysql.cj.jdbc.Driver

package com.example.config;

import com.p6spy.engine.spy.appender.MessageFormattingStrategy;
import org.springframework.util.StringUtils;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

public class CustomP6SpyLogFormat implements MessageFormattingStrategy {
    
    private static final DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    
    @Override
    public String formatMessage(int connectionId, String now, long elapsed, String category,
                               String prepared, String sql, String url) {
        return StringUtils.hasText(sql) ? 
                LocalDateTime.now().format(formatter) + 
                " | " + elapsed + "ms | " + category + 
                " | connection " + connectionId + 
                " | " + sql : "";
    }
}

package com.example.config;

import com.p6spy.engine.common.ConnectionInformation;
import com.p6spy.engine.event.JdbcEventListener;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.sql.SQLException;
import java.util.concurrent.TimeUnit;

@Slf4j
@Component
public class SlowQueryListener extends JdbcEventListener {
    
    @Value("${sql.slow.threshold:500}")
    private long slowThreshold; // 默認(rèn)500毫秒
    
    @Override
    public void onAfterAnyExecute(ConnectionInformation connectionInformation, long timeElapsedNanos, SQLException e) {
        long timeElapsedMillis = TimeUnit.NANOSECONDS.toMillis(timeElapsedNanos);
        
        if (timeElapsedMillis > slowThreshold) {
            String query = connectionInformation.getSqlWithValues();
            log.warn("Slow SQL detected: {} ms, SQL: {}", timeElapsedMillis, query);
            
            // 可以記錄到數(shù)據(jù)庫(kù)或發(fā)送告警
            saveSlowQuery(query, timeElapsedMillis);
        }
    }
    
    private void saveSlowQuery(String query, long timeElapsed) {
        // 保存慢查詢記錄到數(shù)據(jù)庫(kù)或告警系統(tǒng)
    }
}

java -javaagent:/path/to/skywalking-agent.jar -Dskywalking.agent.service_name=your-service-name -jar your-application.jar

# application.yml
spring:
  application:
    name: your-service-name

# 設(shè)置后端服務(wù)地址
collector.backend_service=localhost:11800
# 啟用SQL跟蹤
plugin.jdbc.trace_sql=true
# 設(shè)置慢SQL閾值
plugin.jdbc.slow_sql_threshold=1000

<dependency>
    <groupId>org.apache.skywalking</groupId>
    <artifactId>apm-toolkit-trace</artifactId>
    <version>8.7.0</version>
</dependency>

import org.apache.skywalking.apm.toolkit.trace.ActiveSpan;
import org.apache.skywalking.apm.toolkit.trace.TraceContext;

@Service
public class UserService {
    
    @Autowired
    private UserRepository userRepository;
    
    public User findUserById(Long id) {
        // 添加自定義Tag
        ActiveSpan.tag("userId", id.toString());
        
        // 獲取traceId，可用于日志關(guān)聯(lián)
        String traceId = TraceContext.traceId();
        log.info("Processing user query with traceId: {}", traceId);
        
        return userRepository.findById(id).orElse(null);
    }
}

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid-spring-boot-starter</artifactId>
    <version>1.2.8</version>
</dependency>

# 數(shù)據(jù)源類型
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.druid.url=jdbc:mysql://localhost:3306/test
spring.datasource.druid.username=root
spring.datasource.druid.password=password

# 連接池配置
spring.datasource.druid.initial-size=5
spring.datasource.druid.min-idle=5
spring.datasource.druid.max-active=20
spring.datasource.druid.max-wait=60000

# 慢SQL監(jiān)控配置
spring.datasource.druid.filter.stat.enabled=true
spring.datasource.druid.filter.stat.log-slow-sql=true
spring.datasource.druid.filter.stat.slow-sql-millis=1000
spring.datasource.druid.filter.stat.merge-sql=true

# 開(kāi)啟監(jiān)控頁(yè)面
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.url-pattern=/druid/*
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=admin
spring.datasource.druid.stat-view-servlet.allow=127.0.0.1
spring.datasource.druid.stat-view-servlet.deny=

# 開(kāi)啟Web應(yīng)用監(jiān)控
spring.datasource.druid.web-stat-filter.enabled=true
spring.datasource.druid.web-stat-filter.url-pattern=/*
spring.datasource.druid.web-stat-filter.exclusions=*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*

# 開(kāi)啟Spring監(jiān)控
spring.datasource.druid.aop-patterns=com.example.service.*,com.example.repository.*

@Configuration
public class DruidConfig {
    
    @Bean
    public ServletRegistrationBean<StatViewServlet> druidStatViewServlet() {
        ServletRegistrationBean<StatViewServlet> bean = new ServletRegistrationBean<>(new StatViewServlet(), "/druid/*");
        
        Map<String, String> initParams = new HashMap<>();
        initParams.put("loginUsername", "admin");
        initParams.put("loginPassword", "admin");
        initParams.put("allow", "127.0.0.1");
        
        bean.setInitParameters(initParams);
        return bean;
    }
    
    @Bean
    public FilterRegistrationBean<WebStatFilter> druidWebStatFilter() {
        FilterRegistrationBean<WebStatFilter> bean = new FilterRegistrationBean<>(new WebStatFilter());
        
        Map<String, String> initParams = new HashMap<>();
        initParams.put("exclusions", "*.js,*.css,/druid/*");
        
        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Collections.singletonList("/*"));
        
        return bean;
    }
    
    @Bean
    @ConfigurationProperties("spring.datasource.druid.filter.stat")
    public StatFilter statFilter() {
        StatFilter filter = new StatFilter();
        filter.setSlowSqlMillis(1000);
        filter.setLogSlowSql(true);
        filter.setMergeSql(true);
        return filter;
    }
    
    @Bean
    public DruidStatInterceptor druidStatInterceptor() {
        return new DruidStatInterceptor();
    }
    
    @Bean
    public BeanNameAutoProxyCreator druidStatProxyCreator() {
        BeanNameAutoProxyCreator creator = new BeanNameAutoProxyCreator();
        creator.setProxyTargetClass(true);
        creator.setBeanNames("*Service", "*ServiceImpl", "*Repository");
        creator.setInterceptorNames("druidStatInterceptor");
        return creator;
    }
}

@Component
public class DruidSlowSqlListener implements ApplicationListener<ContextRefreshedEvent> {
    
    @Autowired
    private DruidDataSource dataSource;
    
    @Override
    public void onApplicationEvent(ContextRefreshedEvent event) {
        dataSource.setConnectionProperties("druid.stat.slowSqlMillis=1000");
        
        StatFilter statFilter = new StatFilter();
        statFilter.setLogSlowSql(true);
        statFilter.setSlowSqlMillis(1000);
        statFilter.setMergeSql(true);
        
        statFilter.setSlowSqlLoggerName("SLOW_SQL_LOGGER");
        
        dataSource.getProxyFilters().add(statFilter);
    }
}

@RestController
@RequestMapping("/api/monitor")
public class DruidMonitorController {
    
    @Autowired
    private DruidDataSource dataSource;
    
    @GetMapping("/slow-sql")
    public List<Map<String, Object>> getSlowSql() {
        List<Map<String, Object>> result = new ArrayList<>();
        
        try {
            JdbcStatManager statManager = JdbcStatManager.getInstance();
            for (Object item : statManager.getDataSourceList().values()) {
                JdbcDataSourceStat dataSourceStat = (JdbcDataSourceStat) item;
                Map<String, JdbcSqlStat> sqlStatMap = dataSourceStat.getSqlStatMap();
                
                for (Map.Entry<String, JdbcSqlStat> entry : sqlStatMap.entrySet()) {
                    JdbcSqlStat sqlStat = entry.getValue();
                    
                    if (sqlStat.getExecuteMillisMax() > 1000) {
                        Map<String, Object> slowSql = new HashMap<>();
                        slowSql.put("sql", sqlStat.getSql());
                        slowSql.put("executionCount", sqlStat.getExecuteCount());
                        slowSql.put("maxTime", sqlStat.getExecuteMillisMax());
                        slowSql.put("avgTime", sqlStat.getExecuteMillisTotal() / sqlStat.getExecuteCount());
                        
                        result.add(slowSql);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        
        return result;
    }
}