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大表优化是一个系统工程，需要从多个维度综合考虑。关键是要根据具体的业务场景选择合适的优化策略，同时要做好监控和维护工作。
记住，最好的优化是在设计阶段就考虑到数据量增长的问题，避免后期的痛苦重构。
希望这篇文章对你有所帮助！如果你觉得有用，欢迎关注【服务端技术精选】公众号，获取更多后端技术干货。
- 大表的"甜蜜负担"
- 大表的判断标准
- 优化方案思路
- 索引优化策略
- 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 评论
