MySQL数据库表结构的设计

超、凢脫俗 2022-01-26 06:31 595阅读 0赞

#### 1.1 关系型数据库 ####

数据库关系型模型的概念最早由“关系数据库之父”之称的埃德加·弗兰克·科德（Edgar Frank Codd或E. F. Codd）博士提出，1970年，身为IBM的研究员的他在刊物《Communication of the ACM》上发表了题为大型共享数据库的关系模型的论文，首次提出了数据库的关系模型的概念，奠定了关系模型的理论基础。

本文以MySQL为例介绍数据库表结构的设计。

#### 1.2 数据库的优化模型 ####

![MySQL优化][MySQL]

一般来说，日常工作中**硬件和系统配置**不是我们关注的重点，这些钱都就可以解决，我们关注最多的是**数据库表结构的设计**和**SQL级索引的优化**。在大数据时代，这些理论同样适用，在Hive和HBase中分别有表结构设计和RowKey设计，无论使用其他第三方SQL语言，比如，HQL、Phoenix、SparkSQL或Presto等，SQL及索引的优化是永恒的话题，这里我们主要讨论**数据库表结构的设计**。

#### 1.3 关系型数据库设计的思路 ####

一般地，设计思路包括如下两个主要思路：

*  二维表
 *  四个范式
 *  业务相关的问题

其中，二维表很容易理解，二维即指`行`和`列`，不做细述，以下将详细讨论四个范式和工作中的业务问题。

#### 2.1 第一范式：不可再分 ####

当表中字段存在可再分情况时，对其进行拆分。

**举例**  
![不可再分范式举例][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5nY2hlbmc5NTU4OA_size_16_color_FFFFFF_t_70]

#### 2.2 第二范式：属性完全依赖于某个候选键 ####

简单地说，第二范式满足如下两点要求：

*  每个表必须有唯一键；
 *  主键是唯一标识。

#### 2.3 第三范式：属性不依赖于其它非主属性 ####

**非主属性**是指不在任何码（主码和候选码）中出现的属性。

当属性依赖于其他非主属性时，应进行拆分。

**举例**  
![不依赖于非主属性范式举例][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5nY2hlbmc5NTU4OA_size_16_color_FFFFFF_t_70 1]

未拆分前，属性Level依赖于非主属性Score，无法适应多变的业务，这种设计较为死板，拆分后可灵活更改各个科目对应的level属性，较为灵活，适用于多变的业务。

#### 2.4 第四范式：禁止非主键列和其他非主键列一对多关系 ####

在同一张表中，非主键的列与非主键列存在一对多的关系会造成存储大量的冗余字段，当出现这种状况时，应进行合理拆表。

**举例**  
![禁止非主键列和其他非主键列一对多关系范式举例][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5nY2hlbmc5NTU4OA_size_16_color_FFFFFF_t_70 2]  
在`考试分数等级表`中，Grade和Subject存在一对多的关系，浪费了存储空间，且会降低查询效率，拆分后业务的可扩展性更大，也节省了不必要的冗余信息存储所浪费的空间。

<table> 
 <tbody> 
  <tr> 
   <td><font> 三、业务相关问题（撕逼）</font></td> 
  </tr> 
 </tbody> 
</table>

#### 3.1 产品经理那些不能相信的话 ####

*  这个属性值`只有一个`，这个`只有一种`情况…
 *  这就是个`小项目`，不用搞那么麻烦，赶紧做好上线，后面再说…

建表后随着项目的迭代，后期重新设计表结构成本较高，开发需要修改代码逻辑，数据库中已存在的数据要做迁移和二次导入。良好的表结构设计可以帮我们避免很多问题，也会使整个项目更健壮。

#### 3.2 有些还是能信的 ####

*  这个项目每月访问量预计`百万`，甚至`千万`
 *  日活`上亿`

如果听到这种话，应该庆幸，这个是锻炼的机会，珍惜这样的机会，做好表结构的设计及其他优化工作，这些以后都是财富。

综上所述，表结构的设计是数据库优化中至关重要的环节，需要认真谨慎对待，在遵守四大范式的前提下，充分考虑业务未来的可扩展性，适应未来业务变化，促进系统更加健康健壮。

1.  本文借鉴了公司内DBA的分享，感谢DBA的指导；
2.  能力有限，欢迎指错交流。

欢迎关注个人微信公众号WaltSmithML或新浪微博WaltSmith，公众号提供机器学习、深度学习、Hadoop、Spark、Python、数学知识等免费视频教程。本人目前工作主要负责智能推荐和NLP。非常欢迎一起交流学习哈，除了学习，还可免费帮忙download论文或者书籍

♥♥♥欢迎关注微信公众号: **数学与机器学习**♥♥♥![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5nY2hlbmc5NTU4OA_size_16_color_FFFFFF_t_70 3]  
♥♥Python机器学习QQ群♥♥  
![Python机器学习QQ群][Python_QQ]

### 参考文献 ###

1.  [候选码 主码 主属性 非主属性 外码][_ _ _]

[MySQL]: /images/20220126/3227ebea96b24928a2b53283df6d0882.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5nY2hlbmc5NTU4OA_size_16_color_FFFFFF_t_70]: /images/20220126/5f4af8da33ec4b1daca6786752c998ea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5nY2hlbmc5NTU4OA_size_16_color_FFFFFF_t_70 1]: /images/20220126/afdc5bc89c5044ea920aa12de9474da6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5nY2hlbmc5NTU4OA_size_16_color_FFFFFF_t_70 2]: /images/20220126/0af2b6568ec34f15a45e3de2b50a1c0b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5nY2hlbmc5NTU4OA_size_16_color_FFFFFF_t_70 3]: /images/20220126/61bb27d29e0e4496a371c81098e61e53.png
[Python_QQ]: /images/20220126/2348bce654d845eea6b9365a41bad99e.png
[_ _ _]: https://blog.csdn.net/dayun555/article/details/79463292