MySQL慢查询优化实战-从800ms到120ms

问题背景

在电商平台订单模块中，订单查询接口响应缓慢，高峰期甚至出现超时。通过慢查询日志分析，发现核心查询平均耗时 800ms，急需优化。

问题分析

慢查询定位

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- 分析慢查询
SELECT 
    query_time,
    lock_time,
    rows_sent,
    rows_examined,
    sql_text
FROM mysql.slow_log
WHERE start_time > DATE_SUB(NOW(), INTERVAL 1 DAY)
ORDER BY query_time DESC
LIMIT 10;

问题 SQL 示例

-- 原慢查询：订单列表查询
SELECT 
    o.id,
    o.order_no,
    o.user_id,
    o.total_amount,
    o.status,
    o.create_time,
    oi.product_id,
    oi.product_name,
    oi.quantity,
    oi.unit_price
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 12345
  AND o.create_time >= '2024-01-01'
  AND o.create_time < '2024-02-01'
  AND o.status IN (1, 2, 3)
ORDER BY o.create_time DESC
LIMIT 20 OFFSET 0;

EXPLAIN 分析

EXPLAIN SELECT ...;

结果分析：

• type: ALL（全表扫描）
• rows: 1,200,000（扫描行数过多）
• Extra: Using where; Using temporary; Using filesort
• key: NULL（未使用索引）

优化方案

1. 索引优化

联合索引设计

-- 分析查询条件，创建最优联合索引
-- 最左前缀原则：user_id + create_time + status

ALTER TABLE orders ADD INDEX 
idx_user_time_status (user_id, create_time, status);

-- 覆盖索引优化
ALTER TABLE orders ADD INDEX 
idx_user_time_status_covering (user_id, create_time, status, order_no, total_amount);

索引选择原则

-- 查看索引使用情况
SHOW INDEX FROM orders;

-- 分析索引选择性
SELECT 
    COUNT(DISTINCT user_id) / COUNT(*) as user_selectivity,
    COUNT(DISTINCT status) / COUNT(*) as status_selectivity
FROM orders;

2. SQL 重写优化

优化后的查询

-- 优化方案1：延迟关联
SELECT 
    o.id,
    o.order_no,
    o.user_id,
    o.total_amount,
    o.status,
    o.create_time
FROM orders o
WHERE o.user_id = 12345
  AND o.create_time >= '2024-01-01'
  AND o.create_time < '2024-02-01'
  AND o.status IN (1, 2, 3)
ORDER BY o.create_time DESC
LIMIT 20;

-- 再查询订单详情
SELECT * FROM order_items 
WHERE order_id IN (?, ?, ?, ...);

Java 代码实现

@Service
public class OptimizedOrderService {
    
    @Autowired
    private OrderMapper orderMapper;
    
    @Autowired
    private OrderItemMapper orderItemMapper;
    
    /**
     * 优化后的订单查询
     */
    public PageResult<OrderVO> queryOrders(OrderQueryDTO query) {
        // 1. 查询订单ID列表（使用覆盖索引）
        List<Long> orderIds = orderMapper.selectOrderIds(
            query.getUserId(),
            query.getStartTime(),
            query.getEndTime(),
            query.getStatusList(),
            query.getOffset(),
            query.getLimit()
        );
        
        if (orderIds.isEmpty()) {
            return PageResult.empty();
        }
        
        // 2. 查询订单基本信息
        List<Order> orders = orderMapper.selectByIds(orderIds);
        
        // 3. 批量查询订单商品
        List<OrderItem> items = orderItemMapper.selectByOrderIds(orderIds);
        
        // 4. 组装结果
        Map<Long, List<OrderItem>> itemMap = items.stream()
            .collect(Collectors.groupingBy(OrderItem::getOrderId));
        
        List<OrderVO> result = orders.stream()
            .map(order -> convertToVO(order, itemMap.get(order.getId())))
            .collect(Collectors.toList());
        
        return PageResult.of(result, query.getPage(), query.getSize());
    }
}

3. 分页优化

深分页问题

-- 深分页性能问题
SELECT * FROM orders 
WHERE user_id = 12345
ORDER BY create_time DESC
LIMIT 20 OFFSET 10000;  -- 越往后越慢

优化方案

@Service
public class PaginationOptimization {
    
    /**
     * 基于游标的分页（推荐）
     */
    public List<Order> cursorPagination(Long userId, Long lastOrderId, int limit) {
        return orderMapper.selectByCursor(userId, lastOrderId, limit);
    }
    
    // SQL
    // SELECT * FROM orders 
    // WHERE user_id = ? AND id < ?
    // ORDER BY id DESC
    // LIMIT ?
    
    /**
     * 基于时间范围的分页
     */
    public List<Order> timeRangePagination(
            Long userId, 
            LocalDateTime startTime, 
            LocalDateTime endTime) {
        return orderMapper.selectByTimeRange(userId, startTime, endTime);
    }
}

4. 数据归档策略

@Component
public class OrderArchiveService {
    
    /**
     * 归档3个月前的已完成订单
     */
    @Scheduled(cron = "0 0 3 * * ?")
    public void archiveOldOrders() {
        LocalDateTime archiveTime = LocalDateTime.now().minusMonths(3);
        
        // 1. 查询待归档订单
        List<Long> orderIds = orderMapper.selectCompletedOrdersBefore(archiveTime);
        
        if (orderIds.isEmpty()) {
            return;
        }
        
        // 2. 迁移到历史表
        orderMapper.batchInsertToHistory(orderIds);
        orderItemMapper.batchInsertToHistory(orderIds);
        
        // 3. 删除原表数据
        orderMapper.batchDelete(orderIds);
        
        log.info("归档订单 {} 条", orderIds.size());
    }
}

优化效果对比

优化项	优化前	优化后	提升
平均耗时	800ms	120ms	85%
扫描行数	120万	200	99.9%
索引使用	无	覆盖索引	-
并发能力	50 QPS	500 QPS	900%

监控与持续优化

慢查询监控

@Component
public class SlowQueryMonitor {
    
    @Autowired
    private MeterRegistry meterRegistry;
    
    public void recordQueryTime(String sql, long millis) {
        meterRegistry.timer("db.query.time", 
                "table", extractTable(sql),
                "type", extractType(sql))
            .record(millis, TimeUnit.MILLISECONDS);
        
        if (millis > 100) {
            log.warn("慢查询: {}ms, SQL: {}", millis, sql);
        }
    }
}

索引使用监控

-- 查看未使用索引的查询
SELECT 
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME,
    CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_db'
  AND INDEX_NAME NOT IN (
      SELECT INDEX_NAME 
      FROM performance_schema.table_io_waits_summary_by_index_usage
      WHERE COUNT_STAR > 0
  );

总结

MySQL 优化的核心思路：

1. 定位问题：慢查询日志 + EXPLAIN 分析
2. 索引优化：联合索引、覆盖索引、最左前缀
3. SQL 优化：避免 SELECT *、减少 JOIN、延迟关联
4. 分页优化：游标分页、时间范围分页
5. 数据治理：冷热数据分离、定期归档

优化是一个持续的过程，需要结合业务场景和监控数据不断调整。

---

本文基于电商平台订单系统优化实践，日均 5000+ 订单处理场景