数据库设计注意事项

时间：2021-05-24 15:04:29

不考虑主备，集群等方案，基于业务上的设计主要是表结构及表间关系的设计。

而关于表中字段主要是根据业务来进行定义，我们可以指定的大概有这么几项：

  • 存储引擎 一般用InnoDB,特殊需求特殊选用
  • 字符集和校验规则 
    特别说一下校验规则是指两个字符之间的比较规则， 比如A=a的话就是不区分大小写，会影响order by等。 bin一般是区分大小写的， 一般用general
  • 字段定义 字段怎么选取类型
  • 索引 后面再说
  • 特殊用途表 比如做缓存，汇总等

字段的数据类型选择

三个原则：

  • 更小的数据类型， 比如能用tiny int就不用int
  • 更简单的数据类型， int比varchar要简单，会用到更少的磁盘以及操作时所需要的CPU。再比如用int来存储ip
  • 尽量避免null. 尽量用not null语句， null会带来额外的存储空间，加索引后也需要特殊的处理。

整数

  • [UNSIGNED] TINYINT, SMALLINT, INT,BIGINT. 范围越来越大。 
    显然越小的越省空间
  • 可以指定宽度 INT(11). 这个只是交互工具的显示宽度，跟实际范围无关，定义时可以不指定，还能提升效率
  • 建表是可以选择zerofill的

实数

  • float和double是不精确的类型
  • 可以指定精度 double(12,4)是全位数和小数位数，因为在插入的时候超过部分会进行四舍五入，因此建议不指定。
  • 另外，使用浮点数因为要转化为2进制表示再进行存储或者计算所以可能造成精度问题 
    比如
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background: transparent; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">update</span> biz_pay_task <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">set</span> order_price = <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">131.07232</span>;</span>
// 查出的值将会是 131.07233</code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li></ul>
  • decimal用于存储精确的小数，相同情况下会比浮点型的占用个多存储范围，计算的时候也会转化为double,因此非必要不用
  • 再设计上还可以考虑使用bigint代替decimal.

字符串类型

  • CHAR是定长的，因此在频繁更新的时候不容易产生碎片
  • CHAR适合存储MD5这种结果是定长的数据
  • CHAR适合存储小字节，比如标志位等，比VARCHAR更省空间
  • VCHAR是变长的，频繁更新会有碎片
  • BINARY 是二进制字符串，其中是二进制的字面表达，排序等等会转化为二进制数进行
  • IP地址， 这个可以特殊对待，使用INET_ATON()和INET_NTOA()来保存ip地址为无符号数

时间类型

  • DATETIME 19位标准显示， 可以使用date_format进行结构化查询
  • TIMESTAMP 19位显示，范围比DATETIME小，但是省空间，不能为NULL。
  • TIMESTAMP可以设置自动更新，这样很适合做为updatetime这样的字段

主外键

主键

  • 因为主键回作为索引，越紧凑越小越好，其实也就是越好排序越好。
  • 有的人可能会想使用uuid,但是因为较长，最好使用UNHEX()函数改为数字，存入BINARY中，检索的时候使用HEX()方法再转为十六进制格式

外键

  • 可以设置删除外键的约束行为 默认报错， cascade同样删除， no action 什么也不做，但是会破坏一致性。
  • 另外可以使用set foreign_key_checks=0 可以暂时关闭检查，这样在诸如备份这样的特殊操作的时候可以加快性能。

表字段外，如何对表进行切割以及划分是范式主要讨论的问题

三大范式

因为五范式的实用性太低，只考虑三大范式 
来一张学生选课表 
Student_Course(studentId, studentName, collegeId, collegeName, courseId, courseName, credit)

第一范式 列中的值不可切割

上面如果一个学生选了多门课，我们有如下的办法： courseName中用,号分割。 这显然不能满足第一范式了。 
我们还有个办法就是使用(studentId， courseId)来作为这个联合主键，这样就会有很多重复行了。 
这个也是经典的多对多关系引起的问题。

第二范式 消除部分依赖

可以认为是拆分一个多对多为两个1对多 
上面的数据studentName 部分依赖于(studentId, courseId) 
会引入如下的问题：

  • 数据冗余： 如果一个人选择了N门课，就会造成studentName, collegueId, collegueName,courseName, credit这些都重复n次
  • 容易更新错误，比如如果修改了credit，就需要修改很多行
  • 如果新开了一门课程，如果没人选修的话就不能插入
  • 如果一个课程没人选修，那么会造成课程也被删除了。 
    修改之后的设计： 
    Student(studentId, studentName, collegeId, collegeName) 
    Course( courseId, courseName, credit1) 
    Student_Course(studentId, courseId)

第三范式 消除传递依赖

传递依赖跟部分依赖很容易混淆。会跟本表适用于做什么的有很大的关系 
这部分的主要目的是进一步去除重复数据，提出1对多 
比如上面的学生课程表， 其主码显然是studentId和courseId。 这样很容易判断出部分依赖 
在第二范式分解之后的student表中， 学生信息的主键应该是studentId,另外除他以外有一个不能作为主键，但是却有可能是另外字段所以来的码为的字段：collegeId。这样StudentId->collegeId->collegeName。这就是传递依赖。进一步切割之后： 
Student(studentId, studentName, collegeId) 
Collegue(collegeId, collegeName)

范式与反范式

范式的优缺点： 
- 减少重复 
- 更快的更新 
- 更少的需要group by等语句 
- 缺点：查询时会涉及更多的关联

反范式优缺点： 
- 缺点：冗余行及有可能更新错误 
- 不需要关联

一些取舍 
有时是需要混用范式和反范式的。 特别在一些需要额外的字段进行索引，统计及排序的情况下。 
这样可能会带来更新上的麻烦，需要根据实际情况具体权衡。

其他一些应用

  • 汇总表 通常是定时的计算一些汇总信息，报表系统使用比较多
  • 缓存表 比如使用MyISAM引擎建立该表，留作创建索引。这样就可以把所有可能用作索引的字段单独提出在一个表中，加快索引。这种情况分表的技术中也可能会用到
  • 计数器表
<code class="hljs sql has-numbering" style="display: block; padding: 0px; background: transparent; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TABLE</span> counter(
    cnt <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> unsigned <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">not</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">DEFAULT</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>
) ENGINE = InnoDB;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li></ul>

如果是插入的时候每次递增1，这样就会每次都会对这一行进行排他锁。比较好的解决方式：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; background: transparent; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TABLE</span> counter(
    slot tinyint unsigned <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">not</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">primary</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">key</span>,
    cnt <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">int</span> unsigned <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">not</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">DEFAULT</span> <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">0</span>
) ENGINE = InnoDB;</span>
<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">UPDATE</span> hit_counter <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">SET</span> cnt = cnt + <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">where</span> slot = FLOOR(RAND() * <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">100</span>);</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li></ul>

然后插入100行默认的数据 
然后更新的时候就可以尽量少的避免并发锁行 
就能够使用SUM字段算出总的点击量

如果需要每天都计算的话，那么可能的表结构为：

<code class="hljs sql has-numbering" style="display: block; padding: 0px; background: transparent; color: inherit; box-sizing: border-box; font-family: "Source Code Pro", monospace;font-size:undefined; white-space: pre; border-radius: 0px; word-wrap: normal;"><span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CREATE</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">TABLE</span> counter(
    <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">day</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">date</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">not</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">null</span>;</span>
    slot tinyint unsigned not null,
    cnt int unsigned not null DEFAULT 0,
    primary key(day, slot)
) ENGINE = InnoDB;

<span class="hljs-operator" style="box-sizing: border-box;"><span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INSERT</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">INTO</span> counter <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">VALUES</span>(<span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">CURRENT_DATE</span>, <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">FLOAT</span>(RAND() * <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">100</span>), <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>) <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">ON</span> DUPLICATE <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">KEY</span> <span class="hljs-keyword" style="color: rgb(0, 0, 136); box-sizing: border-box;">UPDATE</span> cnt = cnt + <span class="hljs-number" style="color: rgb(0, 102, 102); box-sizing: border-box;">1</span>;</span></code><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li></ul><ul class="pre-numbering" style="box-sizing: border-box; position: absolute; width: 50px; background-color: rgb(238, 238, 238); top: 0px; left: 0px; margin: 0px; padding: 6px 0px 40px; border-right: 1px solid rgb(221, 221, 221); list-style: none; text-align: right;"><li style="box-sizing: border-box; padding: 0px 5px;">1</li><li style="box-sizing: border-box; padding: 0px 5px;">2</li><li style="box-sizing: border-box; padding: 0px 5px;">3</li><li style="box-sizing: border-box; padding: 0px 5px;">4</li><li style="box-sizing: border-box; padding: 0px 5px;">5</li><li style="box-sizing: border-box; padding: 0px 5px;">6</li><li style="box-sizing: border-box; padding: 0px 5px;">7</li><li style="box-sizing: border-box; padding: 0px 5px;">8</li></ul>

ON DUPLICATE KEY,如果出现了重复的key则更新则不是新增。

##加快DDL 
DDL会阻塞服务，因此应该越快越好。 
一般的方式有该备库切库。 
重新创建一个表，该表之后重名民 
可以通过物化视图facebook的工具来动态的修改

标签：

相关文章