sql新增自增id_一次数据落地的sql设计

0. 说明

本文是最近公司有一项很简单的需求，只是为了方便日常的BI统计数据，以及日常的问题排查，将一些数据落地到sql中(有些冗余)。

我先讲一下大背景，首先是业务中需要客户做一些信息的填写和认证，对于未完成的项目(暂且叫做item吧)，在前端页面中显示，但是这些底层的数据却是依赖于集团的另一个子公司的服务，所以我们项目中的数据并不是实时的，对于查询的时候发现是未完成的需要请求子公司的服务，而这对于BI来说，统计用户到底完成了那些信息的登记，是一个很不方便的事情，于是就有了这样一个需求，那就是每次去查询后把已经完成的项落地到我们的数据库中，显然这样数据有一定的冗余。但是为了方便我们的查询。设计后的查询架构如下：

信息落地服务架构

当然，拿到这样的一个需求，其实很简单，就是建一个数据表，在查询的时候将需要写入的数据落地。但是这却有不同的设计方式。

1. 原始版

因为需求就是客户的某些信息是否完整，数据库只需记录对应用户的对应的信息是否存在，首先想到的最原始的版本，数据库设计如下：

CREATE TABLE user_info ( id bigint auto_increment comment '自增主键', info_no varchar(50) not null comment '记录编号', user_no varchar(50) not null comment '用户编号', info_1 tinyint not null default 0 comment '用户状态1信息, 0不完善，1完善', info_2 tinyint not null default 0 comment '用户状态2信息, 0不完善，1完善', info_3 tinyint not null default 0 comment '用户状态3信息, 0不完善，1完善', info_4 tinyint not null default 0 comment '用户状态4信息, 0不完善，1完善', remark varchar(200) comment '备注信息', create_time datetime comment '创建时间', update_time datetime comment '更新时间', primary key (`id`), unique key uniq_info(`info_no`), key idx_user(`user_no`))Engine=InnoDB

而存储的数据根式如下图：

原始版数据

很显然，这种设计方案的扩展性非常的差，当我们需要吸收用户的其他信息的时候，就需要更新数据表的结构，增加`info_5`字段，每次需要新增信息的时候都需要修改数据表结构，除了修改数据表的结构外，这中间有两个比较让笔者不喜欢的地方：

• 1. 需要修改项目中对应ORM相关的代码，比如mybatis的xml或者是对应Entity。
• 2. 当数据库中数据量比较大的时候，修改表结构会造成数据库性能的下降，可能会影响线上服务的正常运行。

综上， 对于这种设计还是有很大的需要优化空间。

2. 同事版

首先声明，没有鄙视同事的意思，同事大概也是没想太多，认为功能很简单。

同事拿到需求后直接就开始问我都有哪些信息需要保存，每个客户需要保存多少，这样他要设计数据表格中字段的最大长度，一听我很郁闷，就问他是打算怎么设计的，大概是这样设计的表：

CREATE TABLE user_info ( id bigint auto_increment comment '自增主键', info_no varchar(50) not null comment '记录编号', user_no varchar(50) not null comment '用户编号', value varchar(2000) not null comment '用户状态信息', remark varchar(200) comment '备注信息', create_time datetime comment '创建时间', update_time datetime comment '更新时间', primary key (`id`), unique key uniq_info(`info_no`), key idx_user(`user_no`))Engine=InnoDB

同事的方案解决了原始版方案新增用户数据字段的弊端，将info_ns以json字符串的格式保存在value字段中。存储的数据格式如下图：

同事版数据

这种方案在新增信息类型的时候，不用新增字段，对于扩展性有一定的提升，但是有一个致命的缺陷就是会导致单条记录占用空间过大，当用户的信息类型较多时，比如info_n(n = 100),那么假设单个信息的key.length = 6, value.length = 1,加上记录之间的逗号和分号，大概需要(6+1+1+1)* 100 -1 = 899。所以在存储空间上有很大的优化空间。

3. 优化版

对于同事的方案，除了记录字段本身最大长度的限度之外，当数据量比较大的时候对空间的浪费也很多，所以我们这个优化版方案主要是为了尽可能的减少记录的长度。首先是建立一个字典表：

CREATE TABLE info_type (id int auto_increment comment '自增主键',type_name varchar(50) not null comment '类型名称',type_key tinyint not null comment '类型的key',remark varchar(50) comment '备注信息',primary key (`id`),unique key uniq_type_name(`type_name`),unique key uniq_type_key(`type_key`))Engine=InnoDB

字典存储格式：

字段字典数据

业务数据的存储格式：

优化版数据

这样在value字段中就可以将本来的`info_1:1,info_2:0,info_3:0`存储为`1:1,2:0,3:0`，明显降低了value字段的长度，在节省了存储空间的同时，还增加了value字段最大可扩展字段数量。这里说明下，可能info_type表会增加部门的存储，但是这种字典类型的数据，毕竟是不会很多，非常有限，而当真正的业务记录比较多的时候，节省的空间远远比字典表占用的空间要多。这里还有一个问题，就是对应的type_key要在程序(代码)中转换为业务需要的字段。

4. 高扩展版

为了提高扩展性，设计出如下的高扩展版(理论上可以无限的扩展)。这里就直接复用上面的info_type数据，同时设计业务记录表为：

CREATE TABLE user_info (id bigint auto_increment comment '自增主键',id bigint auto_increment comment '自增主键',info_no varchar(50) not null comment '记录编号',user_no varchar(50) not null comment '用户编号',info_key tinyint not null comment '记录的key',info_value tinyint not null comment '记录的值',remark varchar(200) comment '备注信息',create_time datetime comment '创建时间',update_time datetime comment '更新时间',primary key (`id`),unique key uniq_info(`info_no`),unique key uniq_user_key(`user_no`, `info_key`))Engine=InnoDB

存储的表格如下图所示：

高扩展版数据

显然这种方案，不用担心记录行的最大长度限制，同时对于扩展，可以任意的新增信息字段，理论上可以无限的增加，好处很明显，唯一一点缺点就是前面的版本只用记录一次user_no,这种方案可能需要记录很多次，浪费了一定的存储空间。但是这种方案扩展性非常好，同时当数据量大的时候还可以使用分库分表的方式扩展(应该达不到这个数量级)。

希望本文对您在设计业务需求设计sql阶段有所帮助或者启发。

限于笔者知识有限，如果不足之处请帮忙指正，不喜勿喷！

更多文章，请关注微信公众号 CS_Toper之路，或者头条号 CS_Toper。