mysql 大表连接小表 mysql大表在线ddl

如题，今天厂商伙伴们碰到的问题，老大给了个大致的方向，让研究研究

目前在测试环境下确认方案还可以，有两套方案，一套比较麻烦，但是能保证无缝隙进行DDL操作，线上服务正常运行

另一套方案会存在操作的3秒期间出现电话打入的风险，导致电话进来不弹屏

网上有成熟的方案：https://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html#pt-online-schema-change

也可以看看找找facebook的解决方式（看他们的新的方案，老的滚动式更新太坑了）

下面针对于两种方案进行说明：

例子：

create table tb_emp(
 id int primary key auto_increment,
 stu_name varchar(18),
 stu_sex varchar(2),
 stu_age int,
 stu_address varchar(200),
 stu_email varchar(100)
 );create table tb_emp_target(
 id int primary key auto_increment,
 stu_name varchar(18),
 stu_sex varchar(2),
 stu_age int,
 stu_address varchar(200),
 stu_email varchar(100)
 );

alter table `tb_emp_target` Add column stu_test VARCHAR(255) AFTER `stu_email`

两种方案都需要同样的操作，首先要针对于老表的结构，新建一个表，这个表结构就是老表的，但是这个时候对这张空表新建一列并且针对于某一列建立索引，这都没影响。

然后要弄个触发器，监听老表的insert操作

delimiter $$
 CREATE TRIGGER tb_emp_target AFTER INSERT ON tb_emp FOR EACH ROW 
     BEGIN
         INSERT INTO tb_emp_target VALUES(NEW.id,NEW.stu_name,NEW.stu_sex,NEW.stu_age,NEW.stu_address,NEW.stu_email,haha);
     END;$$

这样，不管老表插入啥我新表都有一份相同的数据，新插入的列的值自己看着办

然后用查询插入语句，把老表的值放入到新表里

INSERT INTO 新表(字段1,字段2,…….)
SELECT 字段1,字段2,…… FROM 旧表

这个期间，老表的查询或者插入操作都不会受影响，系统正常运行

方案到这里就要分叉了

第一种方案，我们需要在最短时间内疯狂输出，先删除老表，再改新表表名为老表，我们这边200万数据的表进行这两部操作一共用了3秒左右，删除表就用了2秒多，这就存在风险，删除改名过程中，如果有查询插入来了就完蛋了，程序就完蛋了，当然半夜弄风险也还行。。。

第二种方案，因为是分布式部署的集群，好几台机器被负载去跑这一个程序呢，所以，可以一台一台搞，不过这就要改代码了，把查询插入等等都要改成新的表名，由于有触发器监听老表的查询插入操作，所以不管是负载到新程序还是老程序，两个表都会有相同的数据，直到所有机器全换成新程序，老表就可以删除了，要是觉得表名被改了不好，那可以再重复这个过程，再搞成老表的名字，这种方案就能保证服务始终正常运行，对数据库的操作都会成功

 

我这里其实没有针对update操作考虑，所以触发器只监听了插入操作，如果有需要大家可以再想想触发器的设计，这里要跟大家说下，如果觉得有问题，可以看看mysql在RR级别下的行锁和MVCC机制