[原]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时,一般公司都会采用第五种方案,虽然操作步骤比较多,周期较长,但是其优势也比较明显,不会增长数据量、不会丢失关系型数据库特性等等