记一次大数据量表数据分表
记一次大数据量表数据分表
- 迁移步骤
- 历史年份分表数据拆分同步
- 25年年份分表数据拆分同步
- 问题记录和解决办法
- ERROR 1206
- 📌 原因
- 解决办法
- 数据库卡死
- 解决办法
- 方法 1：临时关闭（无需重启，重启失效）
- 方法 2：永久关闭（需修改配置文件并重启）
- 方法 3：仅关闭某个数据库的 binlog（不可行）
- ⚠️ 注意事项
由于MySQL数据量增大导致单表查询很慢，同时会导致 磁盘IO飙升，· 整体系统性能下降，影响用户体验和服务可用性。有两张大表，一张表占用存储空间120G，另一张表占用存储空间150G，数据量都在5000万左右，所以进行分表处理。由于只有两张表，分表逻辑比较简单，所以直接采用SQL的方式。
迁移步骤
历史年份分表数据拆分同步
-
把物理机主库数据同步到一个空闲的物理机虚拟机，避免影响主库物理机性能和IO开销。
-
针对需要分表的表添加年份相关索引。
-
根据原表结构创建新的年份表结构。
-
按年份把历史数据导入到对应的年份表（历史表数据可以提前操作同步，25年的新表数据需要停服时操作）。
具体SQL脚本明细：
-- 分表操作start ---
-- 添加字段和索引
ALTER TABLE `table_name`
ADD INDEX `idx_create_time`(`create_time`);ALTER TABLE `table_name`
ADD COLUMN `data_year` char(4) NULL COMMENT '年份' AFTER `remark`,
ADD INDEX `idx_data_year`(`data_year`) USING BTREE;-- 设置分表字段值
update table_name set data_year =year(create_time);-- 创建新表和迁移数据
create table table_name_2021 like table_name;
create table table_name_2022 like table_name;
create table table_name_2023 like table_name;
create table table_name_2024 like table_name;
insert into table_name_2021 select * from table_name where data_year='2021';
insert into table_name_2022 select * from table_name where data_year='2022';
insert into table_name_2023 select * from table_name where data_year='2023';
insert into table_name_2024 select * from table_name where data_year='2024';
25年年份分表数据拆分同步
- 核对数据无误后清理原表25年以前数据；
- 同步25年数据到25年分表中；
问题记录和解决办法
ERROR 1206
执行设置分表字段值时，执行一段时间后，数据库报如下错误：
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
📌 原因
InnoDB 引擎在执行大批量操作（如 UPDATE、DELETE、INSERT … SELECT时，锁定的行数超出了 InnoDB 的锁表内存限制（由 innodb_buffer_pool_size控制）。
解决办法
在 my.cnf 或 my.ini中增加锁等待超时（治标不治本）：
SET innodb_lock_wait_timeout = 120; -- 单位：秒
锁等待超时会报如下错误：
ERROR 1205 : Lock wait timeout exceeded; try restarting transaction
- 调整 InnoDB 缓冲池大小（需重启 MySQL）：
[mysqld]
innodb_buffer_pool_size = 2G # 根据服务器内存调整（建议 50-70%）
- <font style="color:rgba(0, 0, 0, 0.9);">检查当前值：</font>**<font style="color:rgba(0, 0, 0, 0.9);"></font>**
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
PS：SHOW VARIABLES LIKE 'innodb_buffer_pool_size'; 返回的值单位是 字节（Bytes）。
例如：
mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name | Value |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
表示当前缓冲池大小为 134,217,728 字节 = 128 MB（默认）。
快速换算（常见值对照）：
| 字节（Bytes） | MB |
|---|---|
| 134,217,728 | 128 MB |
| 268,435,456 | 256 MB |
| 536,870,912 | 512 MB |
| 1,073,741,824 | 1 GB |
| 2,147,483,648 | 2 GB |
数据库卡死
调整参数后继续执行分表相关操作，由于MySQL虚拟机分配磁盘和CPU资源有限导致数据库异常卡死，执行update操作时，数据量较大，又开启的binlog日志，导致磁盘空间不足和IO过高导致数据库假死。
解决办法
暂时关闭binlog日志。
关闭 MySQL binlog 日志的方法
方法 1：临时关闭（无需重启，重启失效）
-- 仅当前会话生效，适用于临时调试
SET sql_log_bin = 0;
注意：仅影响当前会话的写入操作，其他连接仍正常记录 binlog。
方法 2：永久关闭（需修改配置文件并重启）
- 编辑 MySQL 配置文件my.cnf 或 my.ini：
[mysqld]
# 关闭 binlog（MySQL 8.0+）
disable-log-bin # 或 skip-log-bin# 确保未设置 log-bin 参数
# log-bin=mysql-bin # 注释掉这一行
- 重启 MySQL 服务：
sudo systemctl restart mysqld # Linux
net stop MySQL && net start MySQL # Windows
- 验证是否关闭：
SHOW VARIABLES LIKE 'log_bin'; -- 结果应为 OFF
方法 3：仅关闭某个数据库的 binlog（不可行）
❌ binlog 是实例级设置，无法针对单个数据库关闭。
⚠️ 注意事项
- 生产环境慎用：binlog 用于主从复制和 点时间恢复，关闭后无法增量备份。
- MySQL 8.0+：推荐使用disable-log-bin（更明确）。
- 云数据库（如 AWS RDS）：需通过控制台参数组修改，无法直接操作配置文件。
本文内容到此结束了，
如有收获欢迎点赞👍收藏💖关注✔️，您的鼓励是我最大的动力。
如有错误❌疑问💬欢迎各位指出。
主页：共饮一杯无的博客汇总👨💻保持热爱，奔赴下一场山海。🏃🏃🏃
