MySQL 数据库规范--设计篇

目录

---

1.设计阶段
2.开发阶段(未开发，explain工具使用)
3.调优阶段(未开发，pt_query_digest、show_slow_log、查询优化等)

1.设计阶段

1.1 数据库表的设计范式(三范式&反范式)

---

为什么需要范式

优点：编程相对简单，数据量更小，更适合放入内存，更新更快，只需要更新少量的数据，
更少的冗余意味着更少的需要group distinct 之类的操作。

第一范式

数据表每一列都是不可分割的基本数据项。举例一个人有多个手机号

第二范式

数据表里的所有数据都要和该数据表里的主键有完全相依赖的关系，不能只依赖部分。
举例：用户名&用户技能 是主键，用户居住地 ，那么用户名为主键就可以了。

第三范式

缺点：非键属性都只和候选属性相关，非间属性之间没有关系。举例冠军表中冠军名&冠军生日。

范式的缺陷

查询变得相当复杂，查询时需要更多的连接join ，一些复合索引的列由于范式化的需要被分割到不同的表中，导致索引策略不佳。

反范式

优点：减少了连接，可以更好的利用索引进行筛选和排序，对查询操作可以提高性能。
缺点：要在数据一致性与查询之间找到平衡点，符合业务场景的设计才是好的设计

数据库设计准则

设计的数据库应该按照用户可能的访问路径，访问习惯进行设计，而不是严格按照数据范式来设计

1.2 存储引擎的选择

---

存储引擎分类
InnoDB:

1.灾难恢复性好
2.支持4中级别的事务，默认事务的隔离级别是Repeatable Read，事务支持是通过MVCC多版本并发控制来提供的。
3.使用行级锁，并发性能高。
4.使用此存储引擎的表，数据的物理组织形式是簇表，数据按主键来组织，即主键索引和数据是在一起的，B+树就是这样的
5.实现缓冲管理，能缓存索引也能缓存数据。
6.支持外键
7.支持热备份

MyISAM:

1.配合锁，实现操作系统下的复制备份，迁移
2.使用表记锁并发性差
3.支持全文索引
4.主机宕机后，表容易损坏，灾难恢复性不佳
5.无事务支持
6.只缓存索引，数据缓存利用操作系统缓冲区实现的，引发过多系统调用，性能不佳。
7.数据紧凑存储，可以获得更快的索引和更快的全表扫描性能。

存储引擎的选择:

设计阶段我们选用InnoDB存储引擎作为数据的存储模式,使用事务、且并发性高，支持外键，支持外键索引。

1.3 字符集选择

---

字符编码采用utf-8
字符校验采用utf-8-cgi

1.4 命名约定

---

规范的必要性P187

1.年前bug就是因为没有建立索引导致的一系列Bug，所以建立规范，刻不容缓。
2.命名没有强制约定，但在一个应用中建议风格统一。

命名约定

1.命名有意义，一眼知道这张表是干什么用的
2.数据库，表都用小写
   数据库形如：backend
   数据表形如：client_device_info(客户端设备信息)，不要缩写，字母全小写
3.索引命名以idx_为前缀
4.命名不要过长（应尽量少于25字符）
5.不要使用保留字
6.同一字段在不同的表中也应是相同的类型和长度
7.同一数据库下有不同的模块，可以考虑对表名用不同的前缀标识
8.备份表时加上时间标识

1.5 索引设计

---

直接参考MySQL 数据库索引使用场景&注意事项

1.6 数据表设计与规划

---

表设计

1.如果没有特殊情况，建议选择InooDB索引
2.每个表都应该有主键，可选择自增自断，或整形字段。例外情况，一些应用会频繁的基于某些自断进行检索，设计人员可能认为这些字段／
  字组合更适合做主键，因为更自然、更高效。
3.(不做强制要求)尽量将字段设置为NOT NULL。因为NULL值的存储需要额外的空间，且会导致比较运算更为复杂，会使得优化器更难以
  优化sql。null 值虽然会导致比较运算更加复杂，但这比因此定义了not null带来应用逻辑异要好。
4.使用更短小的列，比如整型列。整型列的执行速度往往更快。
5.存储精确浮点数必须使用DECIMAL代替float和double。
6.建议使用unsigned类型存储非负值
7.建议使用 int unsigned存储ipv4
8.整型定义中不添加显示长度的值，使用int，而不是int(4)
9.尽可能不要使用text,blob类型
10.varchar（n） n表示字符数而不是字节数，比如varchar（255）最大可存储255个汉字，需根据实际字符长度选择n的值。
11.字符集建议选择utf-8
12.存储年时使用year类型
13.存储日期时使用date类型
14.存储时间时，建议使用timestamp类型，因为timestamp使用的是4字节，datetime使用的是8字节。
15.不要在数据库中使用varbinary或blob存储图片及文件，mysql 并不适合大量存储这类型文件
16.join 操作的字段，在不同表中的类型及命名要一致
17.如果更改表结构会影响性能，需要我司后台(有DBA尽可能找DBA)进行联合评审。

数据表规划

 查看数据表大小的脚本
 select sum(data_length+index_length) from information_schema.tables where table_schema = ‘app_backend’ and table_name = ‘client_device_info’;
其中data_length是记录大总大小，index_length 为索引的大小，table_schema 是数据库名
table_name 是数据表名。

1.7 慎用外键

---

外键的使用

1.外键的优点：
外键约束使得程序员更不容易将不一致性引入数据库，而且设计合适外键有助于以文档方式记录表间关系。
2.外键的缺点
但这些优点是以服务器为执行必要的检查而花费额外的开销为代价的。服务器进行额外的检查会影响性能。
其次外键对并发性能的影响很大，因每次修改数据都需要去另外一个表检查数据，需要获取额外的锁（以确保事务完成之前，父表的记录不
会被删除）高并发环境下出现性能问题，更好的办法是在应用层实现外键约束。