MySQL实战手记：从单表千万到分库分表的性能跃迁
一次真实的性能危机
上周三凌晨2:17，监控系统告警把我从睡梦中惊醒——核心业务库的QPS从正常的1200骤降到不足300，查询延迟从5ms飙升到800ms。登录服务器后，一个简单的SELECT COUNT(*) FROM order_table竟然需要12秒！
检查表状态时发现了问题根源：
-- 查看表数据量
SELECT table_name, table_rows, data_length/1024/1024 as data_mb
FROM information_schema.tables
WHERE table_schema = 'business_db' AND table_name = 'order_table';
-- 结果令人震惊
table_name | table_rows | data_mb
order_table | 18,743,292 | 12,847这张订单表已经积累了近1900万条记录，数据量达到12GB，完全超出了单表的最佳处理范围。
单表瓶颈的深度诊断
索引失效分析
使用EXPLAIN分析慢查询时，发现了典型的全表扫描：
EXPLAIN SELECT * FROM order_table
WHERE user_id = 12345 AND create_time BETWEEN '2023-01-01' AND '2023-12-31';
-- 执行计划显示
id | select_type | table | type | key | rows | Extra
1 | SIMPLE | order_table | ALL | NULL | 18743292 | Using where问题在于我们虽然有单独的user_id和create_time索引，但缺少复合索引。根据MySQL 8.0官方文档，复合索引的选择性计算至关重要。
性能基准测试
在决定解决方案前，我进行了系统性的基准测试：
-- 测试不同数据量下的查询性能
SET @start_time = NOW();
SELECT COUNT(*) FROM order_table WHERE user_id = 12345;
SET @end_time = NOW();
SELECT TIMEDIFF(@end_time, @start_time) as execution_time;
-- 结果对比
数据量(万) | 查询时间(ms)
100 | 23
500 | 187
1000 | 842
1800 | 12,300数据显示，当数据量超过1000万后，性能呈现指数级下降。
分库分表实战方案
方案选择：水平分表
基于我们的业务特点——订单按用户维度查询频繁，决定采用基于用户ID的Hash分表方案。
-- 创建分表，共16张
CREATE TABLE order_table_0 LIKE order_table;
CREATE TABLE order_table_1 LIKE order_table;
-- ... 创建16张分表
-- 添加分表规则
ALTER TABLE order_table_0 COMMENT = 'SHARDING:0';
ALTER TABLE order_table_1 COMMENT = 'SHARDING:1';分表路由算法
在应用层实现分表路由逻辑：
// 分表路由核心代码
public class ShardingRouter {
private static final int SHARD_COUNT = 16;
public static String getTableName(Long userId, String baseTable) {
int shardIndex = Math.abs(userId.hashCode()) % SHARD_COUNT;
return baseTable + "_" + shardIndex;
}
// 对于范围查询，需要查询所有分表
public static List<String> getAllTableNames(String baseTable) {
List<String> tables = new ArrayList<>();
for (int i = 0; i < SHARD_COUNT; i++) {
tables.add(baseTable + "_" + i);
}
return tables;
}
}数据迁移实战
在线迁移策略
为了不影响线上业务，采用双写方案进行数据迁移：
-- 1. 建立临时同步机制
CREATE TABLE order_table_new LIKE order_table;
-- 2. 分批迁移数据（每次50万条）
DELIMITER //
CREATE PROCEDURE migrate_orders()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE start_id, end_id BIGINT;
DECLARE cur CURSOR FOR SELECT MIN(id), MAX(id) FROM order_table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO start_id, end_id;
IF done THEN
LEAVE read_loop;
END IF;
-- 分批处理
SET @batch_size = 500000;
WHILE start_id <= end_id DO
INSERT INTO order_table_new
SELECT * FROM order_table
WHERE id BETWEEN start_id AND start_id + @batch_size - 1;
SET start_id = start_id + @batch_size;
END WHILE;
END LOOP;
CLOSE cur;
END//
DELIMITER ;数据一致性验证
迁移完成后，必须验证数据一致性：
-- 统计对比
SELECT
(SELECT COUNT(*) FROM order_table) as old_count,
(SELECT COUNT(*) FROM order_table_new) as new_count,
(SELECT SUM(amount) FROM order_table) as old_amount,
(SELECT SUM(amount) FROM order_table_new) as new_amount;
-- 抽样验证
SELECT
COUNT(*) as mismatch_count
FROM (
SELECT id FROM order_table
EXCEPT
SELECT id FROM order_table_new
) as diff;分表后的性能优化
查询改造
所有查询都需要适配分表结构：
-- 单用户查询（路由到特定分表）
SELECT * FROM order_table_5
WHERE user_id = 12345 AND create_time >= '2023-01-01';
-- 跨分表统计查询
SELECT SUM(amount) as total_amount
FROM (
SELECT amount FROM order_table_0 WHERE create_time >= '2023-01-01'
UNION ALL
SELECT amount FROM order_table_1 WHERE create_time >= '2023-01-01'
-- ... 所有分表
) as all_orders;监控体系建设
建立分表监控看板，关键指标包括：
- 各分表数据分布均匀度
- 跨分表查询比例
- 单分表最大数据量
- 查询响应时间P99
成果与反思
经过一周的迁移和优化，系统性能得到显著提升：
- 查询延迟：从800ms降低到15ms（下降98%）
- QPS：从300恢复到1500（提升5倍）
- 存储效率：单表大小从12GB降低到800MB
- 维护性：备份时间从4小时缩短到20分钟
这次实战让我深刻体会到：MySQL单表数据量超过千万后，分库分表不是可选项，而是必选项。关键在于提前规划、平滑迁移和持续监控。
进阶思考
未来我们计划引入ShardingSphere等中间件来简化分片逻辑，同时探索基于时间序列的分表策略，进一步优化历史数据查询性能。数据库架构的演进永远在路上，下一个挑战可能是跨分表的事务一致性保障。
暂无评论