1. 引言：慢查询的代价与影响

在当今数据驱动的应用中，慢查询是系统性能的"隐形杀手"。对于Java开发者而言，理解慢查询的监控与优化至关重要：

用户体验：页面加载时间从毫秒级到秒级的恶化
系统吞吐：单个慢查询可能阻塞整个数据库连接池
资源消耗：CPU、内存、IO的异常占用
业务风险：超时导致的交易失败、数据不一致

// 真实场景：慢查询导致的级联故障
@Service
@Transactional
public class OrderService {
    
    public void processBatchOrders(List<Order> orders) {
        for (Order order : orders) {
            // 看似简单的查询，在数据量增长后变慢
            User user = userRepository.findByComplexConditions(order.getCriteria());
            if (user != null) {
                order.setUserId(user.getId());
                orderRepository.save(order);
            }
        }
        // 事务长时间不提交，导致连接池耗尽
    }
}

2. 慢查询基础概念

2.1 什么是慢查询？

定义：执行时间超过指定阈值的SQL语句。MySQL默认阈值为10秒，但生产环境通常设置为1-2秒。

-- 查看当前慢查询配置
SHOW VARIABLES LIKE 'long_query_time';
-- 通常设置为1-2秒
SET GLOBAL long_query_time = 1.0;

2.2 慢查询的根源分析

3. 慢查询监控体系搭建

3.1 慢查询日志配置

# /etc/my.cnf 慢查询配置
[mysqld]
# 开启慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60
min_examined_row_limit = 100
log_slow_admin_statements = 1
log_slow_slave_statements = 1

3.2 实时状态监控

-- 查看当前慢查询状态
SHOW STATUS LIKE 'Slow_queries';
-- 监控数据库性能指标
SHOW STATUS WHERE Variable_name IN (
    'Threads_connected',
    'Threads_running', 
    'Innodb_rows_read',
    'Innodb_rows_updated',
    'Select_scan',
    'Sort_merge_passes'
);
-- 查看正在执行的慢查询
SHOW PROCESSLIST;

3.3 Java应用层监控集成

@Component
public class QueryPerformanceMonitor {
    
    private static final Logger logger = LoggerFactory.getLogger(QueryPerformanceMonitor.class);
    
    @Autowired
    private DataSource dataSource;
    
    private ThreadLocal<Long> queryStartTime = new ThreadLocal<>();
    
    public void startMonitoring() {
        queryStartTime.set(System.currentTimeMillis());
    }
    
    public void endMonitoring(String sql, Object[] params) {
        Long startTime = queryStartTime.get();
        if (startTime != null) {
            long duration = System.currentTimeMillis() - startTime;
            if (duration > 1000) { // 1秒阈值
                logger.warn("慢查询检测 - 执行时间: {}ms, SQL: {}, 参数: {}", 
                           duration, sql, Arrays.toString(params));
                
                // 记录到专门的慢查询表
                logSlowQueryToDatabase(sql, params, duration);
            }
            queryStartTime.remove();
        }
    }
    
    @Aspect
    @Component
    public static class QueryMonitoringAspect {
        
        @Autowired
        private QueryPerformanceMonitor monitor;
        
        @Around("execution(* org.springframework.jdbc.core.JdbcTemplate.query(..))")
        public Object monitorQuery(ProceedingJoinPoint joinPoint) throws Throwable {
            monitor.startMonitoring();
            try {
                return joinPoint.proceed();
            } finally {
                String sql = (String) joinPoint.getArgs()[0];
                Object[] params = (Object[]) joinPoint.getArgs()[1];
                monitor.endMonitoring(sql, params);
            }
        }
    }
}

4. 慢查询日志分析实战

4.1 慢查询日志格式解析

# Time: 2023-10-01T10:00:00.123456Z
# User@Host: app_user[app_user] @  [192.168.1.100]  Id: 12345
# Query_time: 2.345678  Lock_time: 0.000123 Rows_sent: 10  Rows_examined: 100000
SET timestamp=1696156800;
SELECT * FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE u.email LIKE '%@example.com' 
AND o.create_time > '2023-01-01' 
ORDER BY o.amount DESC 
LIMIT 10;

关键字段解读：

Query_time: 查询执行总时间
Lock_time: 锁等待时间
Rows_sent: 返回给客户端的行数
Rows_examined: 扫描的行数（关键性能指标）

4.2 使用mysqldumpslow分析

# 安装MySQL客户端工具
sudo apt-get install mysql-client
# 分析慢查询日志
mysqldumpslow -s t /var/log/mysql/slow.log
# 按执行时间排序，显示前10条
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
# 分析特定模式的查询
mysqldumpslow -g "ORDER BY" /var/log/mysql/slow.log
# 输出示例：
# Count: 25  Time=2.34s (58s)  Lock=0.00s (0s)  Rows=10.0 (250), app_user[app_user]@[192.168.1.100]
#   SELECT * FROM orders ...

4.3 使用pt-query-digest高级分析

bash

# 安装Percona Toolkit
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
sudo apt-get update
sudo apt-get install percona-toolkit
# 分析慢查询日志
pt-query-digest /var/log/mysql/slow.log
# 输出详细报告到文件
pt-query-digest /var/log/mysql/slow.log --output slowlog_analysis.html
# 监控实时慢查询
pt-query-digest --processlist h=localhost --interval 0.1 --print --no-continue

5. 慢查询优化核心技术

5.1 索引优化策略

5.1.1 索引诊断与创建

-- 查看表索引情况
SHOW INDEX FROM orders;
-- 分析索引使用情况
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND status = 'COMPLETED';
-- 创建复合索引
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);
-- 创建覆盖索引
ALTER TABLE orders ADD INDEX idx_covering (user_id, status, amount, create_time);

5.1.2 索引失效场景分析

public class IndexFailureScenarios {
    
    /**
     * 常见的索引失效场景
     */
    public void commonIndexFailures() {
        // 1. 隐式类型转换
        // user_id是VARCHAR，但传入数字 → 索引失效
        String sql1 = "SELECT * FROM users WHERE user_id = 12345";
        
        // 2. 前导通配符LIKE
        String sql2 = "SELECT * FROM users WHERE email LIKE '%@example.com'";
        
        // 3. 对索引列进行函数操作
        String sql3 = "SELECT * FROM orders WHERE DATE(create_time) = '2023-10-01'";
        
        // 4. OR条件使用不当
        String sql4 = "SELECT * FROM products WHERE category_id = 1 OR price > 100";
        
        // 5. 不满足最左前缀原则
        // 索引 (category_id, status)
        String sql5 = "SELECT * FROM products WHERE status = 'ACTIVE'";
    }
    
    /**
     * 优化方案
     */
    public void optimizationSolutions() {
        // 1. 类型匹配
        String sql1 = "SELECT * FROM users WHERE user_id = '12345'";
        
        // 2. 避免前导通配符
        String sql2 = "SELECT * FROM users WHERE email LIKE 'user%@example.com'";
        
        // 3. 避免索引列函数操作
        String sql3 = "SELECT * FROM orders WHERE create_time >= '2023-10-01' AND create_time < '2023-10-02'";
        
        // 4. OR优化为UNION
        String sql4 = "SELECT * FROM products WHERE category_id = 1 " +
                     "UNION SELECT * FROM products WHERE price > 100";
        
        // 5. 调整索引或查询条件
        String sql5 = "SELECT * FROM products WHERE category_id IN (1,2,3) AND status = 'ACTIVE'";
    }
}

5.2 SQL重写优化

5.2.1 分页查询优化

-- 原始慢查询（偏移量大时变慢）
SELECT * FROM orders ORDER BY create_time DESC LIMIT 10000, 20;
-- 优化方案1：游标分页（基于ID）
SELECT * FROM orders 
WHERE id > 10000 
ORDER BY id ASC 
LIMIT 20;
-- 优化方案2：延迟关联
SELECT * FROM orders o
JOIN (
    SELECT id FROM orders 
    ORDER BY create_time DESC 
    LIMIT 10000, 20
) AS tmp ON o.id = tmp.id;

5.2.2 JOIN查询优化

-- 原始慢JOIN
SELECT u.*, o.*, p.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN payments p ON o.id = p.order_id
WHERE u.create_time > '2023-01-01';
-- 优化：分阶段查询 + 应用层组合
-- 第一阶段：获取用户ID列表
SELECT id FROM users WHERE create_time > '2023-01-01' LIMIT 1000;
-- 第二阶段：批量查询关联数据
SELECT * FROM orders WHERE user_id IN (?, ?, ...);
SELECT * FROM payments WHERE order_id IN (?, ?, ...);

5.3 Java代码层面的优化

@Repository
public class OptimizedUserRepository {
    
    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    /**
     * 优化前：N+1查询问题
     */
    public List<User> findUsersWithOrdersBad() {
        List<User> users = jdbcTemplate.query(
            "SELECT * FROM users WHERE status = 'ACTIVE'", 
            new UserRowMapper());
        
        // N+1查询问题
        for (User user : users) {
            List<Order> orders = jdbcTemplate.query(
                "SELECT * FROM orders WHERE user_id = ?",
                new Object[]{user.getId()},
                new OrderRowMapper());
            user.setOrders(orders);
        }
        return users;
    }
    
    /**
     * 优化后：单次JOIN查询
     */
    public List<User> findUsersWithOrdersOptimized() {
        String sql = """
            SELECT u.*, o.id as order_id, o.amount, o.create_time as order_time
            FROM users u
            LEFT JOIN orders o ON u.id = o.user_id
            WHERE u.status = 'ACTIVE'
            ORDER BY u.id, o.create_time DESC
            """;
        
        return jdbcTemplate.query(sql, new UserWithOrdersRowMapper());
    }
    
    /**
     * 优化：批量查询减少数据库往返
     */
    public Map<Long, List<Order>> findOrdersByUserIds(List<Long> userIds) {
        if (userIds.isEmpty()) {
            return Collections.emptyMap();
        }
        
        String inClause = String.join(",", 
            Collections.nCopies(userIds.size(), "?"));
        
        String sql = String.format(
            "SELECT * FROM orders WHERE user_id IN (%s) ORDER BY create_time DESC", 
            inClause);
        
        List<Order> orders = jdbcTemplate.query(sql, userIds.toArray(), new OrderRowMapper());
        
        // 在应用层分组，减少数据库压力
        return orders.stream()
            .collect(Collectors.groupingBy(Order::getUserId));
    }
}

6. 系统级优化策略

6.1 数据库参数调优

# /etc/my.cnf 性能优化参数
[mysqld]
# 缓冲池设置（通常设置为物理内存的70-80%）
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8
# 日志设置
innodb_log_file_size = 2G
innodb_log_buffer_size = 64M
# 连接设置
max_connections = 500
thread_cache_size = 50
table_open_cache = 2000
# 查询缓存（MySQL 8.0已移除，5.7版本谨慎使用）
# query_cache_type = 0

6.2 架构层面的优化

// 读写分离架构
@Configuration
@EnableConfigurationProperties(DataSourceProperties.class)
public class ReadWriteDataSourceConfig {
    
    @Bean
    @Primary
    public DataSource routingDataSource() {
        Map<Object, Object> targetDataSources = new HashMap<>();
        
        // 主库（写）
        targetDataSources.put("master", createMasterDataSource());
        
        // 从库（读）
        targetDataSources.put("slave1", createSlaveDataSource());
        targetDataSources.put("slave2", createSlaveDataSource());
        
        ReadWriteRoutingDataSource routingDataSource = 
            new ReadWriteRoutingDataSource();
        routingDataSource.setTargetDataSources(targetDataSources);
        routingDataSource.setDefaultTargetDataSource(
            targetDataSources.get("master"));
        
        return routingDataSource;
    }
    
    // 基于注解的读写分离
    @Target({ElementType.METHOD, ElementType.TYPE})
    @Retention(RetentionPolicy.RUNTIME)
    public @interface ReadOnly {
    }
}

7. 自动化监控与告警系统

7.1 基于Prometheus的监控

# prometheus.yml 配置
scrape_configs:
  - job_name: 'mysql_slow_queries'
    static_configs:
      - targets: ['mysql-exporter:9104']
    metrics_path: /metrics
    params:
      collect[]:
        - slowlog
        - engine_innodb
        - global_status
# 告警规则
groups:
- name: mysql_slow_queries
  rules:
  - alert: MySQLSlowQueryRateHigh
    expr: rate(mysql_global_status_slow_queries[5m]) > 10
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "MySQL慢查询率过高"
      description: "当前慢查询率为 {{ $value }} 个/秒"

7.2 Java应用集成监控

@Component
public class SlowQueryAlertService {
    
    @Autowired
    private MeterRegistry meterRegistry;
    
    private final Counter slowQueryCounter;
    private final DistributionSummary queryDurationSummary;
    
    public SlowQueryAlertService() {
        this.slowQueryCounter = Counter.builder("db.query.slow")
            .description("慢查询计数")
            .register(meterRegistry);
            
        this.queryDurationSummary = DistributionSummary.builder("db.query.duration")
            .description("查询耗时分布")
            .baseUnit("milliseconds")
            .register(meterRegistry);
    }
    
    public void recordQuery(String sql, long duration, boolean isSlow) {
        queryDurationSummary.record(duration);
        
        if (isSlow) {
            slowQueryCounter.increment();
            
            // 触发告警逻辑
            if (shouldSendAlert(sql, duration)) {
                sendAlertToTeams(sql, duration);
            }
        }
    }
    
    private boolean shouldSendAlert(String sql, long duration) {
        // 基于SQL模式、执行时间、频率等判断
        return duration > 5000; // 5秒以上立即告警
    }
    
    private void sendAlertToTeams(String sql, long duration) {
        // 发送告警到Teams/Slack/钉钉
        String message = String.format(
            " 慢查询告警\n执行时间: %dms\nSQL: %s", 
            duration, abbreviateSql(sql));
        
        // 调用Webhook发送消息
        sendWebhookAlert(message);
    }
}

8. 实战案例：电商系统慢查询优化

8.1 案例背景

-- 原始慢查询：商品搜索+分页
SELECT p.*, c.name as category_name, COUNT(r.id) as review_count
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN reviews r ON p.id = r.product_id
WHERE p.status = 'ACTIVE'
AND p.price BETWEEN 100 AND 1000
AND p.stock_count > 0
AND (p.name LIKE '%手机%' OR p.description LIKE '%手机%')
GROUP BY p.id
ORDER BY p.sales_count DESC
LIMIT 0, 20;
-- 执行时间：8.2秒，扫描行数：50万+

8.2 优化步骤

步骤1：索引分析与创建

-- 分析现有索引
SHOW INDEX FROM products;
-- 创建复合索引
ALTER TABLE products ADD INDEX idx_search_composite (
    status, price, stock_count, sales_count
);
-- 创建全文索引（用于名称和描述搜索）
ALTER TABLE products ADD FULLTEXT(name, description);

步骤2：SQL重写

-- 优化后的查询
SELECT p.*, c.name as category_name, r.review_count
FROM products p
FORCE INDEX (idx_search_composite)
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN (
    SELECT product_id, COUNT(*) as review_count 
    FROM reviews 
    GROUP BY product_id
) r ON p.id = r.product_id
WHERE p.status = 'ACTIVE'
AND p.price BETWEEN 100 AND 1000
AND p.stock_count > 0
AND MATCH(p.name, p.description) AGAINST('手机' IN BOOLEAN MODE)
ORDER BY p.sales_count DESC
LIMIT 0, 20;
-- 执行时间：0.15秒，扫描行数：2000

步骤3：Java代码优化

@Service
public class ProductSearchService {
    
    /**
     * 分阶段搜索优化
     */
    public Page<ProductVO> searchProducts(ProductSearchRequest request) {
        // 第一阶段：快速获取ID列表
        List<Long> productIds = findProductIdsByConditions(request);
        
        if (productIds.isEmpty()) {
            return Page.empty();
        }
        
        // 第二阶段：批量获取详细信息
        Map<Long, Product> products = productRepository.findByIds(productIds);
        Map<Long, Category> categories = categoryRepository.findByProductIds(productIds);
        Map<Long, Long> reviewCounts = reviewRepository.countByProductIds(productIds);
        
        // 第三阶段：应用层组装
        return assembleProductVOs(products, categories, reviewCounts, request.getPageable());
    }
    
    private List<Long> findProductIdsByConditions(ProductSearchRequest request) {
        String sql = """
            SELECT id FROM products 
            WHERE status = 'ACTIVE' 
            AND price BETWEEN ? AND ?
            AND stock_count > 0
            AND MATCH(name, description) AGAINST(? IN BOOLEAN MODE)
            ORDER BY sales_count DESC
            LIMIT ?, ?
            """;
        
        return jdbcTemplate.queryForList(sql, Long.class,
            request.getMinPrice(), request.getMaxPrice(),
            request.getKeyword(),
            request.getOffset(), request.getPageSize());
    }
}

9. 预防性优化与最佳实践

9.1 开发规范

/**
 * 数据库操作最佳实践
 */
public class DatabaseBestPractices {
    
    // 1. 使用预编译语句防止SQL注入
    public User findByEmail(String email) {
        return jdbcTemplate.queryForObject(
            "SELECT * FROM users WHERE email = ?",
            new Object[]{email},
            new UserRowMapper());
    }
    
    // 2. 合理使用批量操作
    public void batchInsertUsers(List<User> users) {
        jdbcTemplate.batchUpdate(
            "INSERT INTO users (name, email) VALUES (?, ?)",
            users.stream()
                .map(user -> new Object[]{user.getName(), user.getEmail()})
                .collect(Collectors.toList()));
    }
    
    // 3. 限制查询结果集大小
    public List<User> findActiveUsers(int limit) {
        return jdbcTemplate.query(
            "SELECT * FROM users WHERE status = 'ACTIVE' LIMIT ?",
            new Object[]{limit},
            new UserRowMapper());
    }
    
    // 4. 避免SELECT *
    public List<String> findUserNames() {
        return jdbcTemplate.queryForList(
            "SELECT name FROM users WHERE status = 'ACTIVE'",
            String.class);
    }
}

9.2 定期维护任务

-- 定期分析表
ANALYZE TABLE orders, users, products;
-- 优化表（碎片整理）
OPTIMIZE TABLE orders;
-- 更新索引统计信息
UPDATE mysql.innodb_index_stats SET last_update = NOW();
-- 清理历史数据
DELETE FROM audit_logs WHERE create_time < DATE_SUB(NOW(), INTERVAL 90 DAY);

10. 总结

慢查询监控与优化是一个系统工程，需要从监控、分析、优化到预防的全链路管理：

监控体系：建立全方位的慢查询检测机制
分析工具：掌握专业的日志分析工具使用
优化技术：索引优化、SQL重写、架构调整多管齐下
预防措施：开发规范、定期维护、容量规划

通过系统化的慢查询治理，可以显著提升应用性能，保障系统稳定运行，为业务发展提供坚实的技术支撑。

《理解MySQL数据库》慢查询监控与优化实战指南

1. 引言：慢查询的代价与影响

2. 慢查询基础概念

2.1 什么是慢查询？

2.2 慢查询的根源分析

3. 慢查询监控体系搭建

3.1 慢查询日志配置

3.2 实时状态监控

3.3 Java应用层监控集成

4. 慢查询日志分析实战

4.1 慢查询日志格式解析

4.2 使用mysqldumpslow分析

4.3 使用pt-query-digest高级分析

5. 慢查询优化核心技术

5.1 索引优化策略

5.1.1 索引诊断与创建

5.1.2 索引失效场景分析

5.2 SQL重写优化

5.2.1 分页查询优化

5.2.2 JOIN查询优化

5.3 Java代码层面的优化

6. 系统级优化策略

6.1 数据库参数调优

6.2 架构层面的优化

7. 自动化监控与告警系统

7.1 基于Prometheus的监控

7.2 Java应用集成监控

8. 实战案例：电商系统慢查询优化

8.1 案例背景

8.2 优化步骤

步骤1：索引分析与创建

步骤2：SQL重写

步骤3：Java代码优化

9. 预防性优化与最佳实践

9.1 开发规范

9.2 定期维护任务

10. 总结

热门文章

最新文章

相关电子书

探索云世界

热门

云计算

大数据

云原生

人工智能

数据库

开发与运维

活动广场

任务中心

训练营

直播

乘风者计划

下载

镜像站

技术资料

《理解MySQL数据库》慢查询监控与优化实战指南

1. 引言：慢查询的代价与影响

2. 慢查询基础概念

2.1 什么是慢查询？

2.2 慢查询的根源分析

3. 慢查询监控体系搭建

3.1 慢查询日志配置

3.2 实时状态监控

3.3 Java应用层监控集成

4. 慢查询日志分析实战

4.1 慢查询日志格式解析

4.2 使用mysqldumpslow分析

4.3 使用pt-query-digest高级分析

5. 慢查询优化核心技术

5.1 索引优化策略

5.1.1 索引诊断与创建

5.1.2 索引失效场景分析

5.2 SQL重写优化

5.2.1 分页查询优化

5.2.2 JOIN查询优化

5.3 Java代码层面的优化

6. 系统级优化策略

6.1 数据库参数调优

6.2 架构层面的优化

7. 自动化监控与告警系统

7.1 基于Prometheus的监控

7.2 Java应用集成监控

8. 实战案例：电商系统慢查询优化

8.1 案例背景

8.2 优化步骤

步骤1：索引分析与创建

步骤2：SQL重写

步骤3：Java代码优化

9. 预防性优化与最佳实践

9.1 开发规范

9.2 定期维护任务

10. 总结

热门文章

最新文章

相关电子书