系统中mysql设计过程_某系统 数据库设计过程记录

数据库设计文档(MySQL)

XXX 项目 MySQL + Elasticsearch 数据库架构设计

What & Why

What

现在需要一个

能够暂时/临时承担系统检索需求，

长期承担系统存储数据需求，

以存储亿级别数据为最终存储体量，

包含部分一对一(O2O)，一对多(O2M)，多对多(M2M)

需要与 ES 进行数据同步，

多人协同使用的，

的 MySQL 数据库设计

Why

1. 为什么抛弃了原有的基于 JSON 的多级索引的设计

原有设计以 MySQL 为中心，使用 MySQL 承担绝大部分索引与存取需求，与现阶段设计需求不相符。

之前过多的索引会影响数据存储性能

对于 JSON 格式的支持要求 MySQL-ver > 5.7 bj 服务器上 MySQL-ver = 5.5

过于繁琐的设计不利于后续的维护、使用与开发

2. 为什么写这个文档

积累经验

主要是记录设计思路过程与对部分设计反复推定的过程以及过程中翻阅到的优秀文档、整理与积累这个过程的收获的内容、反范式及划分模块的过程以及供日后反思的错误与经验教训(毕竟这种有一定规模的数据库设计任务在学生时代很少)

DataBase design

5.7.18 数据库安装 及 参数&配置优化

命名规范

详参考附录[1]

水平拆分

以域名的首字母作为分表依据 分 10 表 每张表数据约为 10m

详细分表情况 (预估数据以之前某类似系统数据作为参考)

分表序号

组成

预估数据量

标准差

1

S+Q+X

10753746

0.570%

2

C+V

10915943

2.087%

3

M+N

10408399

-2.660%

4

A+I

10712761

0.187%

5

T+H

10705091

0.115%

6

B+J+O

10847940

1.451%

7

P+G+other

10756745

0.598%

8

D+L+NUM

10762837

0.655%

9

F+W+U+Y+Z

10644276

-0.454%

10

E+R+K

11202010

4.762%

* 标准差为以完全均分的表记录数所相差的数据量的百分比

此分表策略的优劣 (相较于之前的 hash 分表)

+ 简洁的标准，不受平台、操作系统及语言的影响+ 其他开发人员可以方便的调用+ 基于数据本身特点的分隔- 表与表的数据量上下限约有-1~4%的 差距且表数据量标准差为 1.89% 而hash分表的标准差为 1.186%,hash分表的分布更为平均- 由于数据并非最新的，之前用于参考的数据可能不能反映当前的分布情况，甚至于将来表与表之间的数据量差距可能进一步加大(以过去为参照的预测不能保证很好的预测将来)

垂直拆分

一个域名的相关内容被拆分成了 4 张表

全域名 FQDN-(fqdn,domain,insert_time)

域名数据 DOMAIN-(domain，whois_flag，whowas_flag，TLD，top_whois_sec)

域名 whois/whowas 数据 WHOIS/WHOWAS-(domain,(record_ID),sec_whois_sec,status,sp,reg_name,...name_service,create_time,...)

域名原始 whois 数据 WHOIS_raw-(domain,raw_whois)

部分问题与思路

0. 为什么以域名(char(64))作为主键而不适用自增型 ID

首先、除了 whowas 的数据表外，其他所有数据表的 domain 都要求唯一，其次都经常作为查询的条件，非常适合做主键的特性。

其次、个人认为对于非 Web 型应用，不存在所谓将关键信息暴露给第三方或者用户的可能，即使使用自增 int 作为主键，也需要再添加 domain 字段的 index，浪费了资源，数字型主键所带来的性能优势也将与额外设置 domian 字段的 index 所相抵消，若使用 uuid 作为主键则明显不如直接设置域名作为主键。主键的唯一性和非空性也不会与业务逻辑相抵触

作为主键 char 的性能要优于 varchar

详见：参考资料[1]

1. 为什么要讲原始 whois 数据单独分一张表

在原本的数据库结构中 一条原始 whois 数据大约 3000 个字符(3KB) ，而其他信息一共约为 200~220 字符个字符(0.2KB)，也就是说原始 whois 数据约占一条记录的

存储空间的 93%，而与其在系统中的地位极不相符，即使使用 text 格式存储，在检索和维护缓存表时也将付出相当一部分性能。而实际上原始 whois 数据是几乎不再会被使用和索引。

2. 并没有原始 WHOWAS 记录表

部分理由同上，主要是由于原始 whois 记录价值较低，在将 whois 转化为 whowas 时即使丢弃这一部分数据被认为是可以接受的。当然这样做带来了将降低系统的鲁棒性：即系统将失去通过之前的 whowas 数据来修正或完善相对应的 whowas 记录的能力。

3. 将 TLD,top_whois 移到 DOMAIN 表中

TLD，top_whois 是由域名决定的且不会因为一个域名 whois 信息的变化而变化，故没有必要将其放在 WHOIS 表中被记录多次，只需在 Domain 表中被记录一次。

4. 部分反范式设计

为了实际使用的高效，数据库违反了部分范式：

数据库设计范式 见 参考资料[2]

1. DOMAIN 表中 tld 完全由 domain 决定 也就是 domain 字段不具有原子性(违反了 1NF)

2. DOMAIN 表中 存在 domain->tld->top_whois_ser 的依赖关系，即有非主键依赖于其他非主键 (违反了 3NF)

3. WHOWAS 表 domain 有多值 (违反了 4NF)

5. 为什么注册者信息等字段没有设计索引

因为考虑到将来大部分检索性能将由 ES 承担，故不再在几个反查常用字段设置索引。日常维护部分缓存数据表即使不通过索引或通过 es 检索也能满足要求。

总览

系统数据库分为四个区

Domain 区 - FQDN 表与 Domain 表群

WHOIS_INFO 区 - WHOIS 表群、WHOWAS 表群与 WHOIS_raw 表群

WHOIS_SUPPORT 区 - tld 表，whois_sec_ip 表, 代理 socks 表与 whowas_cookie 表

Website & Cache - 网站用户表，缓存数据表

详见附录 2 数据设计模型

Feature

与 ElasticSeach 相结合

基于数据特征的分表机制

垂直拆分

水平拆分

部分反范式设计

How to use

1. 一个 FQDN 的插入与处理过程

1\. 前台读取文件 获得 FQDN 并转化为 domain 并插入FQDN表,并自动填充record\_time 字段

2\. 通过外键自动创建domain表记录

3\. whois/whowas获取引擎通过flag位轮询探测获取数据

4\. 将获取的数据插入 whois whowas whois-raw 表

5\. 最后更新domain表中的flag记录

2. WHOIS 转化为 WHOWAS 过程

1\. whois获取引擎检测到update_time 字段发生变化

2\. 在whowas表中插入当前记录

3\. 使用新的whois数据覆盖之前的whois数据

4\. 覆盖whois-raw whois原始记录

3. 部分查询过程

创建一个覆盖所有 whois/whowas 表的视图 用于目前的查询及统计工作，简化操作流程

Contrbute

《自己动手设计数据库》 — Michael J. Hernandez

数据库设计时的一些细节的东西如何处理? - 知乎

数据库设计五大范式

Doc

附录 1 WHOIS 格式.txt

附录 2 数据库设计模型

Contact

`13

z.g.13@163.com/h.j.13.new@gmail.com

Harbin Institute of Technology at Weihai