1. 公共属性
这里的数据模型以陈品山的实体-关系模型为基础，增加了两点修改。一是用“组”的概念表达实体间关系，并将组作为一种特殊实体。二是采用继承的思想，将实体的公共属性提取出来，放到统一表中。实体的特有属性保存在单独的表中。根据这两点，我们建立一个实体表，记录全部实体的公共属性。为了跟踪实体的变化，需要为实体分配唯一标识。为了支持分布式应用，索引不能使用数据库自增字段。而唯一标识是OLTP业务检索时最常用的索引，需要支持高性能查询。我们建议采用类似雪花算法的机制，由应用生成128位整数作为实体标识，再分成两个64位整数保存在数据库中。实体的特有属性保存在单独的表中。我们必须记录特有属性表名，让应用可以找到实体特有属性。考虑到数据表可能需要同步，需要记录数据的创建时间和修改时间。这样我们得到了实体表的第一个版本。
| 列 | 标识 | 数据类型 | 说明 |
|---|---|---|---|
| 实体标识高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);
为了节省存储空间，可以把属性表名从实体表中分拆出来，建立一个元数据表。
| 列 | 标识 | 数据类型 | 说明 |
|---|---|---|---|
| 实体类型标识 | id | 整数 | 唯一标识实体类型 |
| 实体类型名字 | name | 字符串 | |
| 特殊属性表 | attribute_table | 字符串 | |
| 新增时间 | create_time | 日期和时间 | 新增记录时间 |
| 更新时间 | modify_time | 日期和时间 | 修改记录时间 |
| 列 | 标识 | 数据类型 | 说明 |
|---|---|---|---|
| 实体标识高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. 多元关系
关系模型可以提供良好的数据独立性，但基于集合的运算难以在大部分业务中直接使用。应用在进行实际计算时，往往需要将数据组织成树或网络结构。这两种结构在微观层面存在“一对多”、“多对一”、“多对多”三种节点关系。在树中，一个父节点可以拥有多个子节点。在网络中，每个节点可以拥有多个父节点，也可以拥有多个子节点。考虑用关系模型保存一颗树。通常的方法是记录父节点和子节点的关系：
父子节点关系 = (父节点编号,子节点编号)
为了处理“从根节点查询全部子节点”问题，可以将根节点编号加入关系。
父子节点关系 = (根节点编号,子节点编号,父节点编号)
这样可以快速查询出一颗树的所有节点，在应用程序中重组成树。这种方法也隐含的产生了一个“组”，即直接或间接依赖于同一根节点的全部节点。我们可以扩展这个概念，来表达一下三种实体之间的依赖关系：
- 集合。各实体间没有依赖关系。
- 列表。各实体间存在线性依赖关系。
- 树。各实体间存在树形依赖结构。
各个实体还可能构成网络依赖结构。这种结构难以用关系模型高效表达，因此本文不考虑这种结构。为了支持组之间的依赖关系，我们把组当作一种特殊实体。
兄弟实体之间也可能存在排序。我们增加一个字段来支持这种同一层级内的序结构。
| 列 | 标识 | 数据类型 | 说明 |
|---|---|---|---|
| 组标识高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. 归档和生命周期
随着记录数不断增加，数据库会出现严重的性能问题。因此需要对不常用的记录进行归档。有些对象需要在未来的特定时间生效或失效，为支持这类对象，需要记录对象的生效标志和生效、失效时间。
| 列 | 标识 | 数据类型 | 说明 |
|---|---|---|---|
| 实体标识高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字节。
| 列 | 标识 | 数据类型 | 字节数 |
|---|---|---|---|
| 实体标识高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 |
| 列 | 标识 | 数据类型 | 字节数 |
|---|---|---|---|
| 组标识高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 |
| 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