引用
#1025 8 亿行日志表怎么归档：DELETE 47 分钟、DROP PARTITION 0.6 秒、拆库重锤的取舍账本#420 数据从千万涨到亿级，历史数据到底该归档、分区、还是冷热分离？#79 千万级大表到底该不该上分区表？三种路线的取舍
【结论】分区表承载量临界条件为：分区数>200、行数>10亿、QPS>5000时需分库。分库可减少MDL锁竞争但需配合其他优化，运维成本增幅约30%。
【依据与步骤】
-
量化分区表极限：
- 使用
SHOW CREATE TABLE t_log查看当前分区数（已156），执行SELECT COUNT(*) FROM information_schema.partitions WHERE table_name='t_log'监控增长 - 当分区数>200时，执行
ALTER TABLE t_log REORGANIZE PARTITION p1 TO (PARTITION p1 VALUES LESS THAN (MAXVALUE))合并分区 - 行数监控：
SELECT TABLE_ROWS FROM information_schema.tables WHERE table_name='t_log'，超过10亿时启动分库
- 使用
-
MDL锁优化：
- 调整
innodb_lock_wait_timeout=120（默认50），避免长时间阻塞 - 对DROP PARTITION操作加锁：
LOCK TABLES t_log WRITE; ALTER TABLE t_log DROP PARTITION p202310; UNLOCK TABLES（需评估业务影响）
- 调整
-
分库分表策略：
- 按业务线拆分6库，每库保留按天分区（减少管理复杂度）
- 使用
PARTITION BY HASH (user_id) PARTITIONS 6实现业务隔离 - 归档策略保持
DROP PARTITION，配合mysqldump冷备
【验证手段】
- 执行
SELECT * FROM performance_schema.table_lock_waits监控锁等待 - 使用
pt-query-digest分析慢查询是否来自特定库 - 对比分库前后
SHOW ENGINE INNODB STATUS中的MDL锁等待次数
【工具推荐】
- [1025] 文中提供的
drop_partition.sh自动化脚本 - [420] 讨论帖中的冷热分离方案对比
- [79] 提到的
ALTER TABLE ... REORGANIZE分区合并技巧
赞 0