Mysql第四天 数据库设计

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

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

  • 存储引擎 一般用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进制表示再进行存储或者计算所以可能造成精度问题
    比方
update biz_pay_task set order_price = 131.07232;
// 查出的值将会是 131.07233
  • decimal用于存储精确的小数，相同情况下会比浮点型的占用个多存储范围。计算的时候也会转化为double,因此非必要不用
  • 再设计上还能够考虑使用bigint取代decimal.

字符串类型

  • CHAR是定长的，因此在频繁更新的时候不easy产生碎片
  • 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次
  • easy更新错误，比方假设改动了credit，就须要改动非常多行
  • 假设新开了一门课程，假设没人选修的话就不能插入
  • 假设一个课程没人选修，那么会造成课程也被删除了。
    改动之后的设计：
    Student(studentId, studentName, collegeId, collegeName)
    Course( courseId, courseName, credit1)
    Student_Course(studentId, courseId)

第三范式 消除传递依赖

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

进一步分割之后：
Student(studentId, studentName, collegeId)
Collegue(collegeId, collegeName)

范式与反范式

范式的优缺点：
- 降低反复
- 更快的更新
- 更少的须要group by等语句
- 缺点：查询时会涉及很多其它的关联

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

一些取舍
有时是须要混用范式和反范式的。 特别在一些须要额外的字段进行索引，统计及排序的情况下。


这样可能会带来更新上的麻烦。须要依据实际情况详细权衡。

其它一些应用

  • 汇总表 通常是定时的计算一些汇总信息，报表系统使用比較多
  • 缓存表 比方使用MyISAM引擎建立该表。留作创建索引。这样就能够把全部可能用作索引的字段单独提出在一个表中，加快索引。

    这种情况分表的技术中也可能会用到

  • 计数器表
CREATE TABLE counter(
    cnt int unsigned not null DEFAULT 0
) ENGINE = InnoDB;

假设是插入的时候每次递增1。这样就会每次都会对这一行进行排他锁。比較好的解决方案：

CREATE TABLE counter(
    slot tinyint unsigned not null primary key,
    cnt int unsigned not null DEFAULT 0
) ENGINE = InnoDB;
UPDATE hit_counter SET cnt = cnt + 1 where slot = FLOOR(RAND() * 100);

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

假设须要每天都计算的话，那么可能的表结构为：

CREATE TABLE counter(
    day date not null;
    slot tinyint unsigned not null,
    cnt int unsigned not null DEFAULT 0,
    primary key(day, slot)
) ENGINE = InnoDB;

INSERT INTO counter VALUES(CURRENT_DATE, FLOAT(RAND() * 100), 1) ON DUPLICATE KEY UPDATE cnt = cnt + 1;

ON DUPLICATE KEY,假设出现了反复的key则更新则不是新增。

##加快DDL
DDL会堵塞服务。因此应该越快越好。
一般的方式有该备库切库。


又一次创建一个表，该表之后重名民
能够通过物化视图facebook的工具来动态的改动

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/gcczhongduan/p/5207843.html