online db如何做字段扩充

声明：部分内容来自网络收集

需求

    线上User表目前存在四个字段user(uid, name, passwd, nick)，现在需要增加两个字段age, sex，变为user(uid, name, passwd, nick, age, sex)

背景

    目前user表数据量较大，且并发请求量较大

解决方案：

方案一、

alter table add column

优势：方案最为简单

因为背景以及交代了，数据量较大，且并发请求高，MySQL在alter table时会锁表，且数据量较大，导致锁表时间过长，无法接受

方案二、

在项目上线初期，预留字段

优势：可以在需要扩展表字段的时候，自动使用预留的字段，因为字段在第一次建表时已经创建了，因此不需要扩充，直接使用已经存在的字段

劣势：1、预留字段的数量无法确定，预留多了，浪费存储空间，预留少了，未来还是要在表上增加字段（只是推迟了时间）

2、预留的字段只能定义为varchar类型的可辩字符串，无法支持其他类型字段，如果需要检索，varchar类型建立索引非理想方案

3、预留字段，字段名称不具有语义化，例如在建立初期，可能叫ext1、ext2等，但是ext1、ext2不具有语义化，后期维护沟通成本高

 

 

方案三

使用纵表存储用户信息，例如之前用户数据

uid	key	value
1	name	张三
1	passport	123
1	nick	NULL
2	name	李四
2	passport	431
2	nick	aaa

需要扩展age, sex时，数据存储格式如下

uid	key	value
1	name	张三
1	passport	123
1	nick	NULL
2	name	李四
2	passport	431
2	nick	aaa
3	name	王五
3	passport	123
3	nick	NULL
3	age	11
3	sex	1

优势：

1、可以随意新增字段，字段可以无限扩展

2、新旧数据可以同时存在

劣势：

1、同一个用户，单行数据变为多行存储，数据量翻倍

2、在key列不能建立索引（建立索引无不能起到检索作用），只能在uid列建立索引，检索方式太单一

 

方案四、

版本号+通用列

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

uid	name	version	ext
1	张三	0	{"passport":"123",“nick”:NULL}
2	李四	0	{"passport":"431",“nick”:"aaa"}

当需要扩展新字段时，将新数据版本升级为1，新增加age, sex两个字段，数据变为

uid	name	version	ext
1	张三	0	{"passport":"123",“nick”:NULL}
2	李四	0	{"passport":"431",“nick”:"aaa"}
3	王五	1	{"passport":"431",“nick”:"aaa","age":3,"sex":1}

优势：

1、不需要做在线DDL，字段可以无限扩展（但是总长度不能超过768个字节）

2、新数据和老数据可以共存

3、迁移方便，可以线上写个脚本慢慢将老数据修改为新版本数据，并将version修改为1

劣势：

1、无法检索(虽然MySQL5.7开始支持JSON和JSON所以，目前本人未进行实际的性能测试)

2、ext列存在大量冗余的key，虽然可以将Key的值缩短，但是会降低key的语义

当然，也有一些将ext这种扩展信息存储在类似mongodb的NoSQL数据库中。

 

方案五、

新表+触发器+迁移数据+rename

基本原理是：
（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、通过提供触发器或者代码来实现两次insert/delete/update操作，如果是建立触发器，会影响原表性能。

方案六、

增加1对1关联的扩展表

例如原来user(uid, name, passwd, nick)

新建一个user_ext表user(uid, age, sex)

通过uid进行一对一的关联

优势：

1、在不对原表进行任何操作的情况下，实现字段扩展

2、可持续对外提供服务，user表中历史数据，可以通过脚本在user_ext慢慢补齐或者在代码存在添加默认值，更新的时候做merge操作补齐

劣势：

1、两张表需要做join操作，在大量数据情况下，存在性能瓶颈。（互联网公司一般严格限制join的使用）

2、user_ext表未来也存在扩展字段的问题。

 

目前，如果存在对online db进行DDL时，一般公司都会采用第五种方案，虽然操作步骤比较多，周期较长，但是其优势也比较明显，不会增长数据量、不会丢失关系型数据库特性等等