现在的软件都建立在数据的基础上，为了方便处理大量的数据可以利用关系型数据库，比如Oracle，Mysql，它们都提供了：
- 事务：于是并发对数据处理变的容易
- 分表：只需要改一个地方，维护数据一致性，避免冗余
- 标准SQL：便捷的对大量数据执行处理
- 索引：内部为BTree查询非常高效
虽然有很多优点，但数据库也有其能力极限，糟糕的设计不只是增加代码复杂度，在数据量特别大的时候，有的查询需要花费数分钟甚至更久，毫无用户体验，而一旦数据库设计好，但代码没进行规范分层导致和数据库绑死，想再改数据库几乎不再可能，如何设计数据库，利用数据库的优点，通过估算来衡量不同的设计，这篇文章将在一份样本数据的基础上进行不同的水平分表，然后分析各自的优缺点
数据
如下两份数据
| record | type | name | item1 | item2 | item3 |
|---|---|---|---|---|---|
| record1 | type1 | name1 | 1.1 | 2.1 | 3.1 |
| record2 | type2 | name1 | 1.2 | 2.2 | 3.2 |
| record | type | name | item2 | item3 | item4 |
|---|---|---|---|---|---|
| record3 | type1 | name3 | 2.3 | 3.3 | 4.3 |
| record4 | type2 | name4 | 2.4 | 3.4 | 4.4 |
从上面可以看到不同record有少数固定的共同字段比如type，name，还有些字段并不固定比如item，而且这里假设item可能有几十万之多，记为M，上面表格只列举了3个，另外假设record数量并不算特别多可能几千行，记为N
方案一
record表
| id | record | type | name |
|---|---|---|---|
| 1 | record1 | type1 | name1 |
| 2 | record2 | type2 | name1 |
| 3 | record3 | type1 | name3 |
| 4 | record4 | type2 | name4 |
item表
| id | item |
|---|---|
| 1 | item1 |
| 2 | item2 |
| 3 | item3 |
| 4 | item4 |
value表
| id | value | record_id | item_id |
|---|---|---|---|
| 1 | 1.1 | 1 | 1 |
| 2 | 2.1 | 1 | 2 |
| 3 | 3.1 | 1 | 3 |
| 4 | 1.2 | 2 | 1 |
| 5 | 2.2 | 2 | 2 |
| 6 | 3.2 | 2 | 3 |
| 7 | 2.3 | 3 | 2 |
| 8 | 3.3 | 3 | 3 |
| 9 | 4.3 | 3 | 4 |
| 10 | 2.4 | 4 | 2 |
| 11 | 3.4 | 4 | 3 |
| 12 | 4.4 | 4 | 4 |
分析：这种设计方案遵循了关系数据库范式，之所以分表，完全是因为item不固定导致
查询用例分析：
- 查询某一个record的一行数据，时间复杂度为O(MN)
- 查询某一个item的一列数据，时间复杂度为O(MN)
优点：查询任何数据只需要连接已知名称的三张表就行了
缺点：查询效率低下
方案二
record表
| id | record | type | name | json |
|---|---|---|---|---|
| 1 | record1 | type1 | name1 | {item1:1.1,item2:2.1,item3:3.1} |
| 2 | record2 | type2 | name1 | {item1:1.2,item2:2.2,item3:3.2} |
| 3 | record3 | type1 | name3 | {item2:2.3,item3:3.3,item4:4.3} |
| 4 | record4 | type2 | name4 | {item2:2.4,item3:3.4,item4:4.4} |
分析：这种设计方案引入了关系数据库中NoSQL功能，用json灵活的解决了item不固定的问题
查询用例分析：
- 查询某一个record的一行数据，时间复杂度为O(N)
- 查询某一个item的一列数据，时间复杂度为O(Nlog(M))
优点：只需要一张表就可以保存所有数据，而且还有不错的查询效率
缺点：由于item可能是几十个字符的长字符串，每行都保存所以非常浪费硬盘空间，当然针对这个问题可以再建立一张item表，然后用某个item的主键作为json列中的主键
方案三
record表
| id | record | type | name |
|---|---|---|---|
| 1 | record1 | type1 | name1 |
| 2 | record2 | type2 | name1 |
| 3 | record3 | type1 | name3 |
| 4 | record4 | type2 | name4 |
| item | |||
| id | item_tb_name | ||
| —- | —- | ||
| 1 | item1 | ||
| 2 | item2 | ||
| 3 | item3 | ||
| 4 | item4 |
item1表
| id | value | record_id |
|---|---|---|
| 1 | 1.1 | 1 |
| 2 | 1.2 | 2 |
| item2表 | ||
| id | value | record_id |
| —- | —- | —- |
| 1 | 2.1 | 1 |
| 2 | 2.2 | 2 |
| 3 | 2.3 | 3 |
| 4 | 2.4 | 4 |
| item3表 | ||
| id | value | record_id |
| —- | —- | —- |
| 1 | 3.1 | 1 |
| 2 | 3.2 | 2 |
| 3 | 3.3 | 3 |
| 4 | 3.4 | 4 |
| item4表 | ||
| id | value | record_id |
| —- | —- | —- |
| 1 | 4.3 | 3 |
| 2 | 4.4 | 4 |
分析：这种设计方案采用了将每个item分一张表的形式
查询用例分析：
- 查询某一个record的一行数据，时间复杂度为O(MN)
- 查询某一个item的一列数据，时间复杂度为O(N)
优点：效率高，硬盘空间没浪费
缺点：item表数量可能几十万张非常多，用户在使用的时候查询关键字需要当成表名传给SQL执行
总结
实际应用中一般只需要获取某个item一列的所有数据，所以性能上来说方案三>方案二>方案一，如果不是只有需要获得item一列这个需求以及没有对查询一列数据数据效率的极限追求，方案二也非常不错
假设M=50W，N=1000，方案三时间为O(1000)，方案一时间为O(50W*1000)，也就说方案三比方案一提升50W倍的效率
关系数据库中为了遵循范式一般而言一旦发现一对多，那么就意味作分表，而如果这个多真的非常多，除了常规意义为了遵循范式的分表实际上还可以采用NoSQL或者多的每个都独立一张表
Happy learning !!
文章知识点与官方知识档案匹配，可进一步学习相关知识MySQL入门技能树数据库组成表31415 人正在系统学习中
声明：本站部分文章及图片源自用户投稿，如本站任何资料有侵权请您尽早请联系jinwei@zod.com.cn进行处理,非常感谢！