表扩展字段2种实施方案研究

目录

0、背景

1、集中式表扩展字段方案

优点：

缺点：

2、分散式表扩展字段方案

2.1 多扩展字段

优点：

缺点：

2.2 Json扩展字段

优点：

缺点：

0、背景

       当我们设计完成一张表时，随着业务变更、新功能需求的增加，经常需要增加一些字段，才能完成一些新功能，这个时候如果不加思索，直接增加字段，当然可以实现。
但是我们是否认真思考过，表中所有的数据，是否只有极其一小部分数据，这个字段在值，而其它一些业务场景下，该字段是空的，表中每条数据都需要这个字段吗？请扪心
自问一下。举一下非常普通的应用案例：
       在一个电商系统中，需要录入商品，有的商品，例如鞋子，是有大小，尺寸的，而有的商品如食品，是有规格，保质期的，商品表设计只能包含通用的字段，而将各商品数据
细分，特有的字段，就不太适合放到商品表中了，这种场景，需要考虑表的扩展字段问题。一般表的扩展字段，可以有以下两种方案：集中式、分散式。

1、集中式表扩展字段方案

表DDL脚本如下：

CREATE TABLE `sys_tab_ext_prop` (
  `id` int(8) NOT NULL COMMENT '主键',
  `table_name` varchar(255) DEFAULT NULL COMMENT '表名',
  `data_id` varchar(255) DEFAULT NULL COMMENT '表中数据Id',
  `property_name` varchar(255) DEFAULT NULL COMMENT '属性名',
  `property_value` varchar(255) DEFAULT NULL COMMENT '属性值',
  `property_remark` varchar(255) DEFAULT NULL COMMENT '属性备注',
  `created_time` datetime NOT NULL COMMENT '创建时间',
  `updated_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `data_id` (`data_id`) USING BTREE COMMENT '外键索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='表扩展属性';

表结构如下图：

优点：

1、各业务表不再关注各表的扩展字段问题，由表扩展组件提供统一数据存储、公共API接口，与业务表进行解耦
2、支持业务表根据扩展字段进行条件检索，只是多一个连表查询，性能与单个表差距不大

缺点：

1、由于所有的表扩展字段都存储到这张扩展字段表上，数据量增长的速度加快，容易成为瓶颈

2、分散式表扩展字段方案

2.1 多扩展字段

表结构如下图：

分散式表扩展字段方案，将表的扩展字段与业务表放到一起，提前设计好一定数量的扩展字段，如：pro1 、pro2、pro3

优点：

1、扩展字段与业务基本字段在同一个表上，查询性能较好，可以根据自己的需求，建立索引

缺点：

1、业务表需要关注各表的扩展字段问题，各业务表需要完成相应的代码开发工作

2、扩展字段的数量不太好确定，提前设计一定数量的扩展字段，后期可以动态增加扩展字段的数量

3、需要业务开发人员详细记录各扩展字段存储的内容类型，不可混合使用各扩展字段

2.2 Json扩展字段

表结构如下图：

分散式表扩展字段方案(Json)，将表的扩展字段与业务表放到一起，整个ext字段存储所有扩展字段的内容

优点：

1、扩展字段与业务基本字段在同一个表上，数据的读写比较方便

2、后期扩展字段的增加比较方便，无须修改数据库表结构

缺点：

1、扩展字段不支持按照某个扩展字段进行检索，如果要解决检索问题，可以将表数据加载到ElasticSearch中，并将json格式的扩展字段拆分成一个一个的字段，
在ElasticSearch中设置为keyword类型，不需要分词，这样可以实现检索的需求。这里千万不要有侥幸心理 ext like '%关键字%' (性能很差)。如果该表很大，需要
同步到ElasticSearch中，请参考另一篇文档：同步一张大表技术实现方案 ，避免走弯路。