原创

MySQL表设计之拓展列?

缘起:

由于需求变更,线上某些千万级表需要增加字段,而且这种需求还挺常见,怎么搞?

  • alter table add column?不太可行,锁表时间长
  • 新表+触发器?如果数据量太大,新表不一定装得下,何况触发器对数据库性能的影响比较高
  • 让dba来搞?新表,迁移数据,一致性校验,rename?dba真苦逼

本文介绍工作中使用的两种小技巧

方案一:主键+通用json字符串列

user_extend_info(uid, create_time,update_time, ext)

uid Create_time Update_time ext
111 1577151832830 1577151832830 {"name":"lisa","age":66}
112 1577151832830 1577151832830 {"name":"lisi","age":26}

由于拓展字段是一个json串,java中可以通过fastJson工具解析,通过对应的bean来解析,如果版本升级,某些key不使用,可以直接将bean的这些字段删掉,解析来的对象是不包含这些额外字段的,再序列化到数据库。

反序列化:JSON.parseObject(str, clazz)

序列化:JSON.toJSONString(object)

优点:

  • 可以随时动态扩展属性
  • 新旧两种数据可以同时存在
  • 迁移数据方便

缺点:

  • ext里的key值有大量冗余,建议key短一些
  • ext里的字段无法建立索引
  • ext里面的字段太过开放,导致多个业务合并的时候这些数据会大量冗余,(例如:业务A:ext:{"name":"lisa","age":66} 业务B:ext:{"school":"beijing","class":4},如果AB业务合并之后,则ext字段为{"school":"beijing","class":4,"name":"lisa","age":66}),最终的结果就是A业务本来不用关心school、class等属性,但是他在序列化的时候还是需要这些字段,导致最终入库的时候数据冗余了。

方案二:主键+业务code+通用json字符串列

由于方案一的各种缺点,笔者升级了数据结构,如下

user_extend_info(uid, create_time,update_time,code, ext)

uid Create_time Update_time code ext
111 1577151832830 1577151832830 100 {"value":100}
111 1577151832830 1577151832830 101 {"value2":99}
112 1577151832830 1577151832830 100 {"value":10}
112 1577151832830 1577151832830 102 {"value3":"nice"}

同样,ext字段还是json串,不一样的是新增了业务code,每个业务对应自己的javaBean解析,而且可以快速通过code定位到数据。

优点:

  • 可以随时动态扩展属性
  • 新旧两种数据可以同时存在
  • 迁移数据方便,写个小程序可以将新增的属性加上
  • 通过业务code分割每个业务之间的差异,完美的解决方案一的问题

不足:

  • 本来一条记录很多属性,会变成多条记录,行数会增加很多(可以接受)

总结

拓展表设计有两种方案,分别为 主键+通用json字符串列 主键+业务code+通用json字符串列 ,希望该文章对读者有帮助。

正文到此结束