通用数据库对象设计

1. 公共属性

这里的数据模型以陈品山的实体-关系模型为基础，增加了两点修改。一是用“组”的概念表达实体间关系，并将组作为一种特殊实体。二是采用继承的思想，将实体的公共属性提取出来，放到统一表中。实体的特有属性保存在单独的表中。根据这两点，我们建立一个实体表，记录全部实体的公共属性。为了跟踪实体的变化，需要为实体分配唯一标识。为了支持分布式应用，索引不能使用数据库自增字段。而唯一标识是OLTP业务检索时最常用的索引，需要支持高性能查询。我们建议采用类似雪花算法的机制，由应用生成128位整数作为实体标识，再分成两个64位整数保存在数据库中。实体的特有属性保存在单独的表中。我们必须记录特有属性表名，让应用可以找到实体特有属性。考虑到数据表可能需要同步，需要记录数据的创建时间和修改时间。这样我们得到了实体表的第一个版本。

表1  实体表

列	标识	数据类型	说明
实体标识高64位	id_high	整数
实体标识低64位	id_low	整数
特有属性表	attribute_table	字符串	实体特有属性表
创建时间	create_time	日期和时间	新增记录时间
修改时间	modify_time	日期和时间	修改记录时间

要查询属性对象时，可以分别从实体表和特定属性表中查询数据，拼接到一起。这个操作可以在数据库中完成，也可以在应用中完成。

代码1  在数据库中查询实体属性

CREATE TABLE entity (id_high BIGINT NOT NULL,id_low BIGINT NOT NULL,attribute_table VARCHAR(200) NULL,create_time DATETIME NOT NULL,modify_time DATETIME NOT NULL,PRIMARY KEY (id_high,id_low)
);CREATE TABLE user (id_high BIGINT NOT NULL,id_low BIGINT NOT NULL,name VARCHAR(200) NOT NULL,PRIMARY KEY (id_high,id_low)
);INSERT INTO entity (id_high,id_low,attribute_table,create_time,modify_time) VALUES (0, 1, 'user', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);INSERT INTO user (id_high,id_low,name) VALUES(0, 1, 'root');-- 查询
DELIMITER //
CREATE PROCEDURE query_entity(IN id_high BIGINT, IN id_low BIGINT)
BEGINSET @table_name = '';SELECT attribute_table INTO @table_name FROM entity WHERE id_high=0 AND id_low=1;SET @query = CONCAT('SELECT * FROM entity a LEFT JOIN ', @table_name, ' b ON a.id_high=b.id_high AND a.id_low=b.id_low');PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
END //
DELIMITER ;CALL query_entity(0, 1);

为了节省存储空间，可以把属性表名从实体表中分拆出来，建立一个元数据表。

表2  实体类型表

列	标识	数据类型	说明
实体类型标识	id	整数	唯一标识实体类型
实体类型名字	name	字符串
特殊属性表	attribute_table	字符串
新增时间	create_time	日期和时间	新增记录时间
更新时间	modify_time	日期和时间	修改记录时间

表3  实体表

列	标识	数据类型	说明
实体标识高64位	id_high	整数
实体标识低64位	id_low	整数
实体类型	entity_type_id	整数	实体类型
创建时间	create_time	日期和时间	新增记录时间
修改时间	modify_time	日期和时间	修改记录时间

代码2  查询实体属性

CREATE TABLE entity_type (id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT "实体类型数字标识",name VARCHAR(20) NULL COMMENT "实体属性名字",attribute_table VARCHAR(200) NULL COMMENT "实体特有属性表",create_time DATETIME NOT NULL COMMENT "创建时间",modify_time DATETIME NOT NULL COMMENT "修改时间"
);CREATE TABLE entity (id_high BIGINT NOT NULL,id_low BIGINT NOT NULL,entity_type_id BIGINT NULL,create_time DATETIME NOT NULL,modify_time DATETIME NOT NULL,PRIMARY KEY (id_high,id_low)
);CREATE TABLE user (id_high BIGINT NOT NULL,id_low BIGINT NOT NULL,name VARCHAR(200) NOT NULL,PRIMARY KEY (id_high,id_low)
);INSERT INTO entity_type (id,name,attribute_table,create_time,modify_time) VALUES (1, '用户', 'user', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);INSERT INTO entity (id_high,id_low,entity_type_id,create_time,modify_time) VALUES (0, 1, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);INSERT INTO user (id_high,id_low,name) VALUES(0, 1, 'root');-- 查询
DELIMITER //
CREATE PROCEDURE query_entity(IN id_high BIGINT, IN id_low BIGINT)
BEGINSET @table_name = '';SELECT attribute_table INTO @table_nameFROM entityLEFT JOIN entity_type ON entity.entity_type_id=entity_type.idWHERE id_high=0 AND id_low=1;SET @query = CONCAT('SELECT * FROM entity a LEFT JOIN ', @table_name, ' b ON a.id_high=b.id_high AND a.id_low=b.id_low');PREPARE stmt FROM @query;EXECUTE stmt;DEALLOCATE PREPARE stmt;
END //
DELIMITER ;CALL query_entity(0, 1);

上面的例子重复查询了两次实体表，性能上不是最优的。如果实体类型表是“只追加”的，即记录不会修改，可以将实体类型表缓存在应用内存中，由应用生成查询语句，提高查询效率。

2. 多元关系

关系模型可以提供良好的数据独立性，但基于集合的运算难以在大部分业务中直接使用。应用在进行实际计算时，往往需要将数据组织成树或网络结构。这两种结构在微观层面存在“一对多”、“多对一”、“多对多”三种节点关系。在树中，一个父节点可以拥有多个子节点。在网络中，每个节点可以拥有多个父节点，也可以拥有多个子节点。考虑用关系模型保存一颗树。通常的方法是记录父节点和子节点的关系：

父子节点关系 = (父节点编号,子节点编号)

为了处理“从根节点查询全部子节点”问题，可以将根节点编号加入关系。

父子节点关系 = (根节点编号,子节点编号,父节点编号)

这样可以快速查询出一颗树的所有节点，在应用程序中重组成树。这种方法也隐含的产生了一个“组”，即直接或间接依赖于同一根节点的全部节点。我们可以扩展这个概念，来表达一下三种实体之间的依赖关系：

1. 集合。各实体间没有依赖关系。
2. 列表。各实体间存在线性依赖关系。
3. 树。各实体间存在树形依赖结构。

各个实体还可能构成网络依赖结构。这种结构难以用关系模型高效表达，因此本文不考虑这种结构。为了支持组之间的依赖关系，我们把组当作一种特殊实体。

兄弟实体之间也可能存在排序。我们增加一个字段来支持这种同一层级内的序结构。

表4  组关系表

列	标识	数据类型	说明
组标识高64位	id_high	整数
组标识低64位	id_low	整数
父实体标识高64位	id_high	整数
父实体标识低64位	id_low	整数
子实体标识高64位	id_high	整数
子实体标识低64位	id_low	整数
子实体序号	child_order	整型	序号
创建时间	create_time	日期和时间	新增记录时间
修改时间	modify_time	日期和时间	修改记录时间

3. 归档和生命周期

随着记录数不断增加，数据库会出现严重的性能问题。因此需要对不常用的记录进行归档。有些对象需要在未来的特定时间生效或失效，为支持这类对象，需要记录对象的生效标志和生效、失效时间。

表5  对象表

列	标识	数据类型	说明
实体标识高64位	id_high	整数
实体标识低64位	id_low	整数
对象类型	object_type	枚举（实体、组）	对象类型：实体或组
实体类型	entity_type_id	整数	实体类型
启用标记	enable_flag	整数
启用时间	enable_time	日期和时间
停用时间	disable_time	日期和时间
归档标记	archive_flag	整数
归档时间	archive_time	日期和时间
创建时间	create_time	日期和时间	新增记录时间
修改时间	modify_time	日期和时间	修改记录时间

4. 性能

现在考虑这些表的性能表现。对象表和组关系表是核心，它们的记录体积不超过72字节，主键占16字节。

表6  对象表记录大小

列	标识	数据类型	字节数
实体标识高64位	id_high	整数	8
实体标识低64位	id_low	整数	8
对象类型	object_type	枚举（实体、组）	4
实体类型	entity_type_id	整数	4
启用标记	enable_flag	整数	4
启用时间	enable_time	日期和时间	8
停用时间	disable_time	日期和时间	8
归档标记	archive_flag	整数	4
归档时间	archive_time	日期和时间	8
创建时间	create_time	日期和时间	8
修改时间	modify_time	日期和时间	8
总计			72

表7  组关系表记录大小

列	标识	数据类型	字节数
组标识高64位	id_high	整数	8
组标识低64位	id_low	整数	8
父实体标识高64位	id_high	整数	8
父实体标识低64位	id_low	整数	8
子实体标识高64位	id_high	整数	8
子实体标识低64位	id_low	整数	8
子实体序号	child_order	整型	4
创建时间	create_time	日期和时间	8
修改时间	modify_time	日期和时间	8
总计			68

表8  B-树信息（估算值）

innodb_page_size		16KB	64KB
	每页记录数	179	725
	每页索引数	647	2613
高度为3的B-树	最大行数	574万	4亿
	最大数据体积	394MB	26GB
高度为4的B-树	最大行数	10亿	2763亿
	最大数据体积	69GB	18TB

5. 系统架构

实体类型表数据量较小，更新频率不高，可以采用读写分离架构。实体表数据量大，更新频繁，可以根据时间、地理位置或其他方式分拆到多个库中。实体特定属性表可以分布到不同的数据库，分散查询压力。分布信息可以保存在实体类型表中。在关联查询时，应用根据分布信息，同时向多个库查询不同实体的属性，在应用内实现表连接。

6. 附录

代码3  计算B-树信息的代码

(defun record-per-innodb-page (innodb-page-size record-size)"计算一个innodb页面可以保存的记录数
innodb-page-size innodb页面大小，单位字节
record-size 记录大小，单位字节"(let ((record-header-size 5)(transaction-id-size 6)(roll-pointer-size 7)(page-header-size 200))(/ (- innodb-page-size page-header-size)(+ record-size record-header-size transaction-id-size roll-pointer-size))))(defun key-per-innodb-page (innodb-page-size key-size)"计算一个innodb页面可以保存的键数
innodb-page-size innodb页面大小，单位字节
key-size 键大小，单位字节"(let ((record-header-size 5)(child-page-number-size 4)(page-header-size 200))(/ (- innodb-page-size page-header-size)(+ key-size record-header-size child-page-number-size))))(defun estimate-btree-info (innodb-page-size record-size key-size tree-height)"估算innodb b-树信息
innodb-page-size innodb页面大小，单位字节
record-size 记录大小，单位字节
key-size 记录大小，单位字节
tree-height b-树高度"(let* ((child-page-number-size 4)(page-header-size 200)(record-header-size 5)(roll-pointer-size 7)(transaction-id-size 6)(record-per-page (record-per-innodb-page innodb-page-size record-size))(key-per-page (key-per-innodb-page innodb-page-size key-size))(non-leaf-pages 0)(leaf-pages 0)(rows 0)(total-record-size 0)(total-key-size 0))(dotimes (h (- tree-height 1))(setf non-leaf-pages (+ non-leaf-pages (expt key-per-page h))))(setf leaf-pages (expt record-per-page (- tree-height 1)))(setf rows (* leaf-pages record-per-page))(setf total-record-size (* rows record-size))(setf total-key-size (* (+ leaf-pages non-leaf-pages) key-size))(list :non-leaf-pages non-leaf-pages:leaf-pages leaf-pages:rows rows:total-record-size total-record-size:total-key-size total-key-size)
))

7. 参考资料

• 回到原点再出发
• https://zhuanlan.zhihu.com/p/717728750