数据库设计注意事项

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

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

存储引擎一般用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的工具来动态的修改

秒客网

数据库设计注意事项

字段的数据类型选择

三个原则：

整数

实数

字符串类型

时间类型

主外键

主键

外键

三大范式

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

第二范式消除部分依赖

第三范式消除传递依赖

范式与反范式

其他一些应用

相关文章

数据库设计注意事项

字段的数据类型选择

三个原则：

整数

实数

字符串类型

时间类型

主外键

主键

外键

三大范式

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

第二范式 消除部分依赖

第三范式 消除传递依赖

范式与反范式

其他一些应用

相关文章

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

第二范式消除部分依赖

第三范式消除传递依赖