MySQL的大表优化方案 (实战分享)

2026-01-22 / 2026-01-25 / MySQL

今天咱们聊聊一个在数据库运维中最常见也最头疼的问题：大表优化。

大表的"甜蜜负担"

在我们的日常开发和运维工作中，经常会遇到这样的场景：

订单表数据量突破千万，查询响应时间从几十毫秒变成几秒钟
日志表数据积累到亿级，连简单的COUNT查询都让数据库不堪重负
历史数据表占用大量存储空间，备份和恢复时间长达数小时
分页查询在深分页时性能急剧下降

当数据量达到百万、千万甚至亿级规模时，传统的SQL优化手段往往显得力不从心。今天我们就来聊聊MySQL大表优化的实战方案。

大表的判断标准

什么是大表？通常我们认为：

单表数据量超过500万行
表大小超过10GB
单次查询响应时间超过1秒

当然，这只是一个相对概念，具体还要看硬件配置和业务需求。

优化方案思路

今天我们要解决的，就是如何系统性地优化MySQL大表。

核心思路是：

分库分表：从物理层面拆分大表
索引优化：提升查询效率
查询优化：改进SQL语句和查询逻辑
架构优化：引入缓存、读写分离等

索引优化策略

1. 合理设计索引

-- 避免全表扫描，创建合适的索引
CREATE INDEX idx_order_status_time ON orders(status, create_time);

-- 覆盖索引减少回表
SELECT order_id, status FROM orders WHERE status = 'paid';

-- 复合索引遵循最左前缀原则
-- WHERE status = 'paid' AND user_id = 123 (✅)
-- WHERE user_id = 123 (❌)

2. 索引维护

-- 定期检查索引使用情况
SHOW INDEX FROM orders;

-- 重建索引优化碎片
ALTER TABLE orders DROP INDEX idx_order_status_time, 
ADD INDEX idx_order_status_time (status, create_time);

-- 统计信息更新
ANALYZE TABLE orders;

查询优化技巧

1. 避免深分页

-- 传统分页（性能差）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- 优化方案：游标分页
SELECT * FROM orders WHERE id > 1000000 ORDER BY id LIMIT 20;

-- 或者使用子查询优化
SELECT * FROM orders 
WHERE id >= (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 1
) 
ORDER BY id LIMIT 20;

2. 条件优化

-- 避免在WHERE条件中使用函数
-- 错误：WHERE DATE(create_time) = '2023-01-01'
-- 正确：WHERE create_time >= '2023-01-01' AND create_time < '2023-01-02'

-- 避免SELECT *
SELECT order_id, status, amount FROM orders WHERE status = 'paid';

-- 使用EXISTS替代IN
SELECT * FROM users u 
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid'
);

分库分表策略

1. 水平分表

-- 按时间分表
CREATE TABLE orders_202301 LIKE orders;
CREATE TABLE orders_202302 LIKE orders;

-- 按ID范围分表
-- orders_0: id % 4 = 0
-- orders_1: id % 4 = 1
-- orders_2: id % 4 = 2
-- orders_3: id % 4 = 3

2. 分表实现

@Service
public class OrderService {
    
    public Order getOrderById(Long orderId) {
        int tableIndex = (int)(orderId % 4); // 分4张表
        String tableName = "orders_" + tableIndex;
        
        String sql = "SELECT * FROM " + tableName + " WHERE order_id = ?";
        return jdbcTemplate.queryForObject(sql, Order.class, orderId);
    }
    
    public List<Order> getOrdersByUserId(Long userId) {
        List<Order> result = new ArrayList<>();
        
        // 遍历所有分表查询
        for (int i = 0; i < 4; i++) {
            String tableName = "orders_" + i;
            String sql = "SELECT * FROM " + tableName + " WHERE user_id = ?";
            List<Order> orders = jdbcTemplate.query(sql, 
                new BeanPropertyRowMapper<>(Order.class), userId);
            result.addAll(orders);
        }
        
        // 按时间排序
        result.sort((o1, o2) -> o2.getCreateTime().compareTo(o1.getCreateTime()));
        return result;
    }
}

数据归档策略

1. 历史数据归档

-- 创建归档表
CREATE TABLE orders_archive LIKE orders;

-- 归档一年前的数据
INSERT INTO orders_archive 
SELECT * FROM orders 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 YEAR);

-- 删除已归档数据
DELETE FROM orders 
WHERE create_time < DATE_SUB(NOW(), INTERVAL 1 YEAR);

2. 自动归档脚本

@Component
public class DataArchiveJob {
    
    @Scheduled(cron = "0 0 2 * * ?") // 每天凌晨2点执行
    public void archiveOldData() {
        // 归档3个月前的数据
        String archiveSql = """
            INSERT INTO orders_archive 
            SELECT * FROM orders 
            WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH)
            AND archived = 0
            LIMIT 10000
            """;
        
        int affectedRows = jdbcTemplate.update(archiveSql);
        
        if (affectedRows > 0) {
            // 删除已归档的数据
            String deleteSql = """
                DELETE FROM orders 
                WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 MONTH)
                AND archived = 0
                LIMIT 10000
                """;
            
            jdbcTemplate.update(deleteSql);
            
            log.info("归档完成，处理了 {} 条记录", affectedRows);
        }
    }
}

读写分离优化

1. 架构设计

@Configuration
public class DataSourceConfig {
    
    @Bean
    @Primary
    public DataSource masterDataSource() {
        // 主库配置
        return new HikariDataSource(masterConfig());
    }
    
    @Bean
    public DataSource slaveDataSource() {
        // 从库配置
        return new HikariDataSource(slaveConfig());
    }
    
    @Bean
    public DataSource routingDataSource() {
        RoutingDataSource routingDataSource = new RoutingDataSource();
        
        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put("master", masterDataSource());
        dataSourceMap.put("slave", slaveDataSource());
        
        routingDataSource.setTargetDataSources(dataSourceMap);
        routingDataSource.setDefaultTargetDataSource(masterDataSource());
        
        return routingDataSource;
    }
}

2. 读写路由

@Service
public class OrderService {
    
    @Transactional(readOnly = true)
    public List<Order> getOrders(Long userId) {
        // 读操作走从库
        return orderMapper.selectByUserId(userId);
    }
    
    @Transactional
    public void createOrder(Order order) {
        // 写操作走主库
        orderMapper.insert(order);
    }
    
    @Transactional
    public Order getOrderWithMaster(Long orderId) {
        // 强制走主库（如刚插入后立即查询）
        return orderMapper.selectById(orderId);
    }
}

分区表优化

1. 表分区

-- 按月分区
CREATE TABLE orders_partitioned (
    id BIGINT AUTO_INCREMENT,
    order_id VARCHAR(50),
    user_id BIGINT,
    amount DECIMAL(10,2),
    create_time DATETIME,
    PRIMARY KEY (id, create_time)
) PARTITION BY RANGE (TO_DAYS(create_time)) (
    PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')),
    PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')),
    PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01'))
    -- 继续添加更多分区
);

2. 分区管理

-- 添加新分区
ALTER TABLE orders_partitioned 
ADD PARTITION (PARTITION p202304 VALUES LESS THAN (TO_DAYS('2023-05-01')));

-- 删除旧分区（快速删除大量数据）
ALTER TABLE orders_partitioned DROP PARTITION p202212;

缓存优化

1. Redis缓存

@Service
public class OrderCacheService {
    
    @Cacheable(value = "orders", key = "#orderId")
    public Order getOrderById(Long orderId) {
        return orderMapper.selectById(orderId);
    }
    
    @CachePut(value = "orders", key = "#order.id")
    public Order updateOrder(Order order) {
        orderMapper.updateById(order);
        return order;
    }
    
    @CacheEvict(value = "orders", key = "#orderId")
    public void deleteOrder(Long orderId) {
        orderMapper.deleteById(orderId);
    }
}

2. 缓存预热

@Component
public class CacheWarmupService {
    
    @EventListener(ContextRefreshedEvent.class)
    public void warmupCache() {
        // 预热热点数据
        List<Long> hotOrderIds = getHotOrderIds();
        
        for (Long orderId : hotOrderIds) {
            getOrderById(orderId); // 触发缓存
        }
    }
    
    private List<Long> getHotOrderIds() {
        // 获取最近访问频繁的订单ID
        return orderMapper.selectHotOrderIds();
    }
}

性能监控

1. 慢查询监控

-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- 记录超过1秒的查询

-- 分析慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

2. 性能分析

-- 查看执行计划
EXPLAIN SELECT * FROM orders WHERE status = 'paid';

-- 查看表统计信息
SHOW TABLE STATUS LIKE 'orders';

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

工具辅助

1. 使用pt-online-schema-change

# 在线修改表结构（避免锁表）
pt-online-schema-change \
--alter "ADD COLUMN new_field VARCHAR(50)" \
D=database,t=orders \
--execute

2. 数据迁移工具

@Component
public class DataMigrationService {
    
    public void migrateLargeTable() {
        int batchSize = 1000;
        long offset = 0;
        
        while (true) {
            // 分批迁移数据
            List<Order> batch = orderMapper.selectBatch(offset, batchSize);
            
            if (batch.isEmpty()) {
                break;
            }
            
            // 插入到新表
            newOrderMapper.batchInsert(batch);
            
            offset += batchSize;
            
            // 避免阻塞
            try {
                Thread.sleep(100);
            } catch (InterruptedException e) {
                Thread.currentThread().interrupt();
                break;
            }
        }
    }
}

最佳实践总结

1. 设计阶段考虑

合理选择分片键（高基数、业务关联、稳定性）
预估数据量增长，提前规划分表策略
设计合理的索引结构

2. 运维阶段监控

定期分析慢查询日志
监控表大小和索引使用情况
及时归档历史数据

3. 应用层面优化

避免复杂的JOIN查询
合理使用缓存
优化分页查询逻辑

注意事项

在优化大表时，需要注意以下几点：

备份策略：优化前务必备份数据
测试环境：先在测试环境验证优化效果
业务影响：评估优化操作对业务的影响
监控告警：建立完善的监控体系
回滚预案：准备回滚方案以防意外

总结

MySQL大表优化是一个系统工程，需要从多个维度综合考虑。关键是要根据具体的业务场景选择合适的优化策略，同时要做好监控和维护工作。

记住，最好的优化是在设计阶段就考虑到数据量增长的问题，避免后期的痛苦重构。

希望这篇文章对你有所帮助！如果你觉得有用，欢迎关注【服务端技术精选】公众号，获取更多后端技术干货。

标题：MySQL的大表优化方案 (实战分享)
作者：jiangyi
地址：http://www.jiangyi.space/articles/2026/01/22/1769073240580.html

大表的"甜蜜负担"
大表的判断标准
优化方案思路
索引优化策略
1. 合理设计索引
2. 索引维护
查询优化技巧
1. 避免深分页
2. 条件优化
分库分表策略
1. 水平分表
2. 分表实现
数据归档策略
1. 历史数据归档
2. 自动归档脚本
读写分离优化
1. 架构设计
2. 读写路由
分区表优化
1. 表分区
2. 分区管理
缓存优化
1. Redis缓存
2. 缓存预热
性能监控
1. 慢查询监控
2. 性能分析
工具辅助
1. 使用pt-online-schema-change
2. 数据迁移工具
最佳实践总结
1. 设计阶段考虑
2. 运维阶段监控
3. 应用层面优化
注意事项
总结

0 评论