通用数据库对象设计
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)
BEGIN
SET @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)
BEGIN
SET @table_name = '';
SELECT attribute_table INTO @table_name
FROM entity
LEFT JOIN entity_type ON entity.entity_type_id=entity_type.id
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. 多元关系
关系模型可以提供良好的数据独立性，但基于集合的运算难以在大部分业务中直接使用。应用在进行实际计算时，往往需要将数据组织成树或网络结构。这两种结构在微观层面存在“一对多”、“多对一”、“多对多”三种节点关系。在树中，一个父节点可以拥有多个子节点。在网络中，每个节点可以拥有多个父节点，也可以拥有多个子节点。考虑用关系模型保存一颗树。通常的方法是记录父节点和子节点的关系：
父子节点关系 = (父节点编号,子节点编号)
为了处理“从根节点查询全部子节点”问题，可以将根节点编号加入关系。
父子节点关系 = (根节点编号,子节点编号,父节点编号)
这样可以快速查询出一颗树的所有节点，在应用程序中重组成树。这种方法也隐含的产生了一个“组”，即直接或间接依赖于同一根节点的全部节点。我们可以扩展这个概念，来表达一下三种实体之间的依赖关系：
- 集合。各实体间没有依赖关系。
- 列表。各实体间存在线性依赖关系。
- 树。各实体间存在树形依赖结构。
各个实体还可能构成网络依赖结构。这种结构难以用关系模型高效表达，因此本文不考虑这种结构。为了支持组之间的依赖关系，我们把组当作一种特殊实体。
兄弟实体之间也可能存在排序。我们增加一个字段来支持这种同一层级内的序结构。
| 列 | 标识 | 数据类型 | 说明 |
|---|---|---|---|
| 组标识高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://blog.csdn.net/tq1086/article/details/142977067
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！