【筑基之石】DBA成长之路-数据库设计避坑之路(持续更新)

写在前面

最新更新： 2023--07-17
更新内容：新增索引设计

笔者最近正在享受成为一名DBA带来的快感, 同时总结一些自己在设计过程中的理念和一些避坑指南,适用于对数据库有一定了解和应用的童鞋

注: 以下提到的数字均来自笔者多次参与数据库的设计经验，哦对了是从阿里巴巴数据库开发规范中一些

数据库设计过程

作为一名DBA 必要的理论基础必不可少, 也就说在我们准备设计数据库时,有些理论是必须掌握的, 相信已经有不少童鞋已经学过或者看过6大设计步骤, 我以具体实际过程阐述这前5个步骤 (第6个步骤笔者需要一个时间累积,方能给出结果)

理论上DB设计过程:

• 需求分析阶段 - 文档

• 概念结构设计阶段

  这个部分占总体设计的40%-50% 假设数据库设计需要一个周 那么需求分析需要3-4天, 这只是粗略指标, 如果你的需求分析或者说沟通成本高,那么可能还要再延长

   分享一下我的设计思路:
   1. 将你和客户沟通的需求梳理成文档(用写流水账方式记录客户想法
   	当然这一部分多半都让产品经理做好了,你直接可以进行第二步)
   3. 抽离流水账的的动词和名词, 比如角色 拥有 权限 授权等
   4. 基于OOP进行需求归纳构造实体, 想想自己曾经构造java 类的时候
   	是如何组合类的属性的, 就和搭积木似的
  

  这三步其实就是前两个步骤的混合, 有了这个准备工作到了逻辑结构阶段就显得事半功倍

• 逻辑结构设计阶段 - ER图

  有了前面的oop需求整理 可以直接用来进行实体的联系, 第一步是细化,这一步要求我们宏观思考 实体的联系 占用时间1-5%

  这一部分我的思路是: 找动词
  在一个数据库中, 实体之间一定存在必然的动作关联, 
  那么就可以通过动词来进行划分拆解, 
  比如旅游出行设计 一定包括   酒店表  房间表  订单表  ...  
  所以他们之间的关系如下图:
  

  
  动词一定要准确形容关联实体的关系 不能一蹴而就 应该多多打磨

• 物理结构设计阶段 - 表设计

  如果说上一阶段是考你的语文功底, 这一part就得看你的English是不是excellent, 当然见名知意最好, 不能为了装逼而自嗨, 另外这一部分几乎是整个数据库设计过程坑最多, 操作最令人窒息的 详见下面的避坑指南

• 数据库实施阶段

  这应该是6个阶段中最为轻松的, 轻松到你可以用数据库管理工具 一键生成上一阶段准备的所有数据表, 当然这个轻松是牺牲一定时间的, 因为需要为数据库初始化准备一些初始数据, 这个过程就涉及大量对表的insert操作

• 数据库维护阶段
  待续…

---

物理结构设计-避坑指南

1. 字段设计
   1. 未雨绸缪 如果你需求分析阶段准备的很充足 那这一部分就像是一个中英互译的过程, 但是一旦有纰漏 那么它就像一颗定时炸弹

      笔者曾经就因为出现对某个表的设计不够充分, 匆忙开发 结果开发到一半时 猛然发现需要补充一个字段, 有些朋友应该能体会其中的恐怖之处, 返工是开发过程中最忌讳, 最要命的
      解决思路是: 如果你能确定某个实体的所有属性 那么放心设计就好, 如果但凡你有一点不确定的地方那么 你至少要准备一个扩展字段以备不时之需(自信是程序员的必修课哈哈哈哈)

   2. 必须字段 其实有时候设计表设计多了 你会发现 总会有这么几个字段都是通用的: id, create_time, modify_time, create_by, modify_by, 不过有时候也很苦恼 无论你维护几个字段都需要这5个字段去协助, DBA也很苦呀…
   3. 尽可能使用编码格式为utf8mb4, 怎么说呢, 其实也是未雨绸缪, 因为现在有些场景会用到emoji表情

      笔者曾经写cms 时候用到过emoji 当时设计的是utf8编码 导致sql解析出错, 忙活了好一阵子,原因在于emoji 是占用四个字节的
      平常使用uf8是占用1-3个字节, 芳名utf8mb3 而utf8mb4是它的超集 支持1-4字节 所以能够有效解决表情存储问题,

   4. 给create_time 和 modify _time添加默认值

      这个是笔者苦逼的开发经历总结得出, CRUD的业务有时候需要记录操作的时间, 之前没有注意Mysql的默认值, 自己徒手更新时间 有时候还经常忘记, 痛苦好一阵子
      首先这两个字段的存在意义你会下面看到, 另外他们都是datetime类型有了这个前提, 我们再说具体设置 create_time 最好设置成: DEFAULT CURRENT_TIMESTAMPmodify_time 设计成 DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP有了这两个默认值 开发直接起飞, 完全不需要我们去考虑维护, mysql自动更新 非常实用!!!

   5. 按需设计，切勿过多考虑，无论是数值类还是字符类字段，在合理预估范围内，最多2倍大小足矣，比如你的地址，撑死也就100来个字符，256足够，再比如你的状态撑死也就十几个，tinyint也完全够了。

---

2. 字段命名

   看似好像是英文互译, 但是也有些需要注意的地方

   1. 数据库名 表名 字段名 变量名 统一小写, 部署过web服务的童鞋应该颇有感悟, Windows的Mysql默认不区分大小写但Linux的Mysql是区分大小写的所以为了避免节外生枝, 请尽量使用小写

   2. 简明易懂, 虽然我们提倡字段的命名尽量简洁, 但是不能过于间接导致阅读障碍, 良好的习惯是 不要使用单词的简化形式 除非大家都懂 比如information -> info , student -> stu因为我们开发的数据库 毕竟要给各方面人员查看和使用 应该避免这种沟通成本

   3. 表达是否的含义尽量不要使用is做前缀 而采用_status后缀标识, 这个其实我阅读阿里巴巴开发者规范 和阿里巴巴数据库设计规范 得出的结论
      不过细心的同学就会发现一个有意思地方, 在开发者规范中提到 由于RPC在逆向解析的时候 对is开头的boolean会无法识别, 所以阿里不建议我们使用is开头, 但是在数据库规范的中 阿里强制我们使用is做boolean字段的前缀 这就很有意思了 口说无凭
      
      
      怎么样, 有没有感觉这个世界很魔幻, 魔幻在于我们在开发中要求数据库和class一 一对应 (心想: 尼玛, 我TM像做梦一样)
      所以笔者的解决想法是, 既然互相冲突, 所幸不如换一种标识方式, 想了很久决定采用xx_status后缀命名

      好处是:
      1.至少我们看到这个后缀 本能想到它是个状态字段 或者 枚举字段, 其实想想 boolean其实也是一种枚举, 当然数据库这里应该是tinyint类型
      2. 有效避免RPC逆向解析问题
      笔者自我研究的思路 诸位随意哈

   4. 避免保留字

      这也是笔者在开发早起开发过程中遇到的问题, 当时是因为desc这个字段, 从命名上似乎并没有什么问题, 但是插入数据库的时候频繁报错, 查询度娘才知道 它是MYSQL的保留字 早起笔者都是手写sql创建表 所以没有加``的习惯, 导致出错, 现在有些工具已经能够识别这些问题 并帮我们自动解决
      但是最好还是避免使用desc这样的字段 而应该采用全称!

---

3. 索引设计
在海量存储的情况下，索引设计太重要了，一个好的索引能让你的应用程序健步如飞。

1. 在建立表的时候，在未来具备一定增长的业务表是一定要给update_time 和create_time 添加索引，不单单考虑后续业务逻辑判断，更要考虑后续统计、甚至数据库大表归档时有备无患，尽管可以亡羊补牢，但就好比及时治病一样，前后总是有区别，说到底就是耗时问题。千万级的大表建立索引可不是好差事。
2. 除了1所说的必要索引，你的索引创建应该基于一定历史查询统计得出结论，而不是因为某一次数据库慢了，就简单认为是数据体量的问题。

---

最后

上面就是笔者从开始设计数据库历程中的一些真实坎坷, 希望能帮助到大家及时避坑,
这篇将持续为大家更新, 其中第六步骤还需笔者一些经验磨练 方能给出满意的结论, 老规矩 莫要白嫖 !