数据库表拓展方案

数据库表拓展方案

需求缘起

产品第一版：用户有用户名、密码、昵称等三个属性,对应表设计：

user(uid, name, passwd, nick)

第二版，产品经理增加了年龄，性别两个属性,表结构可能要变成：

user(uid, name, passwd, nick, age, sex)

特别说明是数据量大，并发量高场景

业界公认方案

新表+触发器+迁移数据+rename 即pt-online-schema-change

以user(uid, name, passwd)

扩展到user(uid, name, passwd, age, sex)为例

基本原理是：

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

（2）在原表user上创建三个触发器，对原表user进行的所有insert/delete/update操作，都会对新表user_new进行相同的操作

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

（4）删掉触发器，把原表移走（默认是drop掉）

（5）把新表user_new重命名（rename）成原表user

扩充字段完成。

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

操作过程中需要注意：

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

（2）变更过程中，写操作需要建立触发器，所以如果原表已经有很多触发器，方案就不行（互联网大数据高并发的在线业务，一般都禁止使用触发器）

（3）触发器的建立，会影响原表的性能，所以这个操作建议在流量低峰期进行

一、哪些方案一定是不行的

（1）直接在表中添加新列

alter table add column

要坚持这个方案的，也不多解释了，大数据高并发情况下此举锁表时间太长，一定不可行

（2）通过增加表的方式扩展，通过外键join来查询

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

（3）通过增加表的方式扩展，通过视图来对外

一定不可行。大数据高并发情况下，互联网不怎么使用视图

（4）必须遵循“第x范式”的方案

一定不可行。互联网的主要矛盾之一是吞吐量，为了保证吞吐量甚至可能牺牲一些事务性和一致性，通过反范式的方式来确保吞吐量的设计是很常见的，例如：冗余数据。互联网的主要矛盾之二是可用性，为了保证可用性，常见的技术方案也是数据冗余。在互联网数据库架构设计中，第x范式真的没有这么重要

（5）打产品经理

朋友，这是段子么，这一定不可行

二、哪些方案可行

方案一：版本号+通用列

以上面的用户表为例，假设只有uid和name上有查询需求，表可以设计为

user(uid, name, version, ext)

（1）uid和name有查询需求，必须设计为单独的列并建立索引

（2）version是版本号字段，它对ext进行了版本解释

（3）ext采用可扩展的字符串协议载体，承载被查询的属性

例如，最开始上线的时候，版本为0，此时只有passwd和nick两个属性，那么数据为：

uid	name	version	ext
1	apple	0	{“passwd”:“123”,“nick”:NULL}
2	banana	0	{“passwd”:“456”,“nick”:“ba”}

当产品经理需要扩展属性时，新数据将版本变为1，此时新增了age和sex两个数据，数据变为：

uid	name	version	ext
1	apple	0	{“passwd”:“123”,“nick”:NULL}
2	banana	0	{“passwd”:“456”,“nick”:“ba”}
3	orange	1	{“passwd”:“789”,“nick”:NULL,“age”:3,sex:“1”}

优点：

（1）可以随时动态扩展属性

（2）新旧两种数据可以同时存在

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

不足：

（1）ext里的字段无法建立索引

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

改进：

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

方案二：通过扩展行的方式来扩展属性

以上面的用户表为例，可以设计为

user(uid, key, value)

初期有name, passwd, nick三个属性，那么数据为：

uid	key	value
1	name	apple
1	passwd	123
1	nick	NULL
2	name	banana
2	passwd	456
2	nick	ba

未来扩展了age和sex两个属性，数据变为：

uid	key	value
1	name	apple
1	passwd	123
1	nick	NULL
2	name	banana
2	passwd	456
2	nick	ba
3	name	orange
3	passwd	789
3	nick	NULL
3	age	3
3	sex	1

优点：
（1）可以随时动态扩展属性

（2）新旧两种数据可以同时存在

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

（4）各个属性上都可以查询

不足：

（1）key值有大量冗余，建议key短一些

（2）本来一条记录很多属性，会变成多条记录，行数会增加很多

方案三：提前预留一些reserved字段

这个是可以的。但如果预留过多，会造成空间浪费，预留过少，不一定达得到扩展效果。

方案四：通过增加表的方式扩展列，上游通过service来屏蔽底层的细节

再新增一个表UserExt(uid, newCol1, newCol2)，与原表同时使用。

参考：
啥，又要为表增加一列属性？
这才是真正的表扩展方案