MySQL中给一张千万甚至更大量级的表添加字段一直是比较头疼的问题,遇到此情况通常该如果处理?本文通过常见的三种场景进行案例说明。
1、 环境准备
数据库版本: 5.7.25-28(Percona 分支)
服务器配置: 3台centos 7虚拟机,配置均为2CPU 2G内存
数据库架构: 1主2从的MHA架构(为了方便主从切换场景的演示,如开启GTID,则两节点即可),关于MHA搭建可参考此文 MySQL高可用之MHA集群部署
准备测试表: 创建一张2kw记录的表,快速创建的方法可以参考快速创建连续数
本次对存储过程稍作修改,多添加几个字段,存储过程如下:
DELIMITER $$CREATE PROCEDURE `sp_createNum`(cnt INT)BEGIN
DECLARE i INT DEFAULT 1;DROP TABLE if existstb_add_columns;CREATE TABLE if not exists tb_add_columns(id int primary key,col1 int,col2 varchar(32));INSERT INTO tb_add_columns(id,col1,col2) SELECT i as id ,i%7 as col1,md5(i) ascol2;WHILE i
INSERT INTO tb_add_columns(id,col1,col2) SELECT id + i as id ,( id + i) %7 as col1,md5( id + i) as col2 FROM tb_add_columns WHERE id <=cnt -i ;SET i = i*2;END;END WHILE;END$$
DELIMITER ;
调用存储过程,完成测试表及测试数据的创建。
mysql> call sp_createNum(20000000);
2. 直接添加字段
使用场景: 在系统不繁忙或者该表访问不多的情况下,如符合ONLINE DDL的情况下,可以直接添加。
模拟场景: 创建一个测试脚本,每10s访问该表随机一条记录,然后给该表添加字段
访问脚本如下:
#!/bin/bash
# gjcfor i in {..1000000000} # 访问次数1000000000,按需调整即可do
id=$RANDOM #生成随机数
mysql-uroot -p'123456' --socket=/data/mysql3306/tmp/mysql.sock -e "select a.*,now() from testdb.tb_add_columns a where id ="$id# 访问数据sleep # 暂停10sdone