大数据量、高并发下的表字段扩展方案（转）

节选和总结自：沈剑大佬的 啥，又要为表增加一列属性？ 、这才是真正的表扩展方案 两篇文章和文章评论

高并发且数据量大情况下的表字段扩展

实际中用的最多是的是方案三和方案五

方案一：版本号 + 通用列

表新增 2 列，一列是扩展字段，一列是版本号，版本号是扩展字段属性的迭代版本。扩展字段值是一个json，每次新增字段，添加一个key就行。

每个业务一个版本或者每次新增一个字段是一个新版本。

优点：

(1)可以随时动态扩展属性

(2)新旧两种数据可以同时存在

(3)迁移数据方便，写个小程序将旧版本ext的改为新版本的ext，并修改version

不足：

(1)ext里的字段无法建立索引

(2)ext里的key值有大量冗余，建议key短一些

改进：

(1)如果ext里的属性有索引需求，可能Nosql的如MongoDB会更适合

(2)新增一张扩展字段key映射表，把每个key映射到一个整数，这样扩展字段里只需要用对应的整数来代替原来的key，节省空间。

方案二：竖表

设计表时，思考下表字段发生扩展的场景多不多，概率大不大，如果较大，可以考虑将表设计成竖表，而不是横表。

优点

(1)可以随时动态扩展属性，添加一行记录就行

(2)新旧两种数据可以同时存在

(3)迁移数据方便，写个小程序可以将新增的属性加上

(4)各个属性上都可以查询

不足

(1)本来一条记录很多属性，会变成多条记录，行数会增加很多，比较适合配置类的业务场景。

(2)key值有大量冗余，建议key短一些

方案三：设计表时预留几个字段或者使用扩展表

方案四：在表上建立视图，通过扩展视图来扩展字段

方案五：pt-online-schema-change(新表+触发器+迁移数据+rename)

扩展字段的整个过程如下：

(1)先创建一个扩充字段后的新表user_new(uid, name, passwd, age, sex)

(2)在原表user上创建三个触发器，对原表user进行的所有insert/delete/update操作，都会对新表user_new进行相同的操作，对于尚未迁移到新表的数据如果执行了这些操作会执行成功，只是影响行数为0。

(3)分批将原表user中的数据insert到新表user_new，直至数据迁移完成

(4)删掉触发器，把原表移走(可以 rename 成其他表，或者直接 drop 掉)

(5)把新表user_new重命名(rename)成原表user

扩充字段完成。(其中删除旧表，remane新表不可中断，需要加锁，但是时间非常短，工具非常成熟，几乎没有影响)

优点：

整个过程不需要锁表，可以持续对外提供服务

(1)变更过程中，最重要的是冲突的处理，一条原则，以触发器的新数据为准，这就要求被迁移的表必须有主键(这个要求基本都满足)

(2)变更过程中，写操作需要建立触发器，触发器的建立，会影响原表的性能，所以这个操作建议在流量低峰期进行；而且如果原表已经有很多触发器，方案就不行(互联网大数据高并发的在线业务，一般都禁止使用触发器)

pt-online-schema-change是DBA必备的利器，比较成熟，在互联网公司使用广泛。

问题

问：为什么不直接使用 Alter table add column

高并发，数据量大的情况下，需要长时间锁表，影响业务的可用性，充值业务，支付业务，下单业务。

问：方案一，如果想查询扩展字段里的值怎么办？

把数据同步到 ES，直接查 ES

问：方案一的扩展字段里能不能不存key

可以，每个版本记录下每个key的顺序，直接把所有值按照顺序存到一个数组中，然后转成二进制保存到数据库。如果表的数据特别多，一般不会在线查询，那么可以将表放到hive库中，用hadoop离线查询

问：提前预留字段会有什么问题

设计时无法确定需要预留多少个字段。如果预留过多，会造成空间浪费，预留过少，不一定达得到扩展效果。

比较好的方式是，预留好的前n-1个字段当单字段来用，最后一个是一个扩展字段，用来存 json 字符串。

问：如果通过增加表的方式扩展，为什么不建议通过外键join来查询

大数据高并发情况下，join性能较差，一定不可行

问：drop 大表速度慢，有什么优化方式吗

可以试试先truncate ，再去 drop

问：横表如果要select某个uid的所有key，这种方式可能会造成随机访问表，对查询性能影响较大，怎么优化

uid+key联合索引，物理上连续存储，效率会比较高

问：实际发生表结构变更时，需要等表迁移完成稳定后在上线对扩展字段的时候用

是的，扩展字段一般不影响原有业务，所以可以稍晚点使用扩展字段也行。

问：方案五，rename 旧表，rename 新表时，如果有请求到达旧表，怎么办

删除旧表，rename新表过程会加表级排他锁，读请求也会阻塞，等迁移完成，原来的请求就直接请求新表了。

问：方案五，不用触发器，而是使用双写，再通过脚本慢慢迁移老数据，老数据迁移完了再改成单写，这种方案行不行

可行，没实际对比过这两种方案的开销，双写延迟高，感觉触发器可能性能高一些，不过大部分冗余高可用设计都是双写，没怎么听过触发器，又感觉双写的性能高一些。