由于MySQL数据量增大导致单表查询很慢，同时会导致 磁盘IO飙升，· 整体系统性能下降，影响用户体验和服务可用性。有两张大表，一张表占用存储空间120G，另一张表占用存储空间150G，数据量都在5000万左右，所以进行分表处理。由于只有两张表，分表逻辑比较简单，所以直接采用SQL的方式。

迁移步骤

历史年份分表数据拆分同步

1. 把物理机主库数据同步到一个空闲的物理机虚拟机，避免影响主库物理机性能和IO开销。

2. 针对需要分表的表添加年份相关索引。

3. 根据原表结构创建新的年份表结构。

4. 按年份把历史数据导入到对应的年份表（历史表数据可以提前操作同步，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年年份分表数据拆分同步

1. 核对数据无误后清理原表25年以前数据；
2. 同步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：永久关闭（需修改配置文件并重启）

1. 编辑 MySQL 配置文件my.cnf 或 my.ini：

[mysqld]
# 关闭 binlog（MySQL 8.0+）
disable-log-bin  # 或 skip-log-bin

# 确保未设置 log-bin 参数
# log-bin=mysql-bin  # 注释掉这一行

2. 重启 MySQL 服务：

sudo systemctl restart mysqld  # Linux
net stop MySQL && net start MySQL  # Windows

3. 验证是否关闭：

SHOW VARIABLES LIKE 'log_bin';  -- 结果应为 OFF

---

方法 3：仅关闭某个数据库的 binlog（不可行）

❌ binlog 是实例级设置，无法针对单个数据库关闭。

⚠️ 注意事项

• 生产环境慎用：binlog 用于主从复制和 点时间恢复，关闭后无法增量备份。
• MySQL 8.0+：推荐使用disable-log-bin（更明确）。
• 云数据库（如 AWS RDS）：需通过控制台参数组修改，无法直接操作配置文件。

本文内容到此结束了，
如有收获欢迎点赞👍收藏💖关注✔️，您的鼓励是我最大的动力。
如有错误❌疑问💬欢迎各位指出。
主页：共饮一杯无的博客汇总👨‍💻

保持热爱，奔赴下一场山海。🏃🏃🏃