项目规约之数据库设计规约

数据库设计规约

建表强制规约

1. 必须使用小写字母或数字、
2. 禁止出现数字开头
3. 禁止两个下划线中间只出现数字
4. 不使用负数名词
5. 禁用保留字
6. 是与否概念的字段，必须使用is_xxx的方式命名（现在不能有）

反例：id、userName、update_1_time、3day、members、order、accept

数据类型：

1. 小数类型为decimal
2. 货币数据使用最小货币单位，数据类型为bigint
3. 字符串长度几乎相等使用char
4. varchar长度不要超过5000

索引的分类

存储形式	聚簇、非聚簇
数据约束	主键、唯一、非唯一
索引列的数量	单列、组合
innoDB可以创建的索引	主键、唯一、普通

普通索引就是非唯一索引，如果几个字段的组合是唯一的，那么我们也要创造唯一索引

索引的特性

1. 持久性：把数据保存到磁盘上，占有磁盘空间
2. 有序性：适合于快速查询，能够提高查询效率

索引的数据结构：哈希，二叉查找树，btree、b+tree

当数据量过大的时候，二叉查找树不太方便，需要使用btree来存储索引，因为二叉树中每个节点只保存一个数据，这对于mysql来说是浪费，因为mysql在数据读写的时候，有一个最小的读写单元，就是数据页，这个数据页有16k，而在btree中，一个16k大的磁盘块里面可以放很多数据，并且每一个数据左右两边各方一个指针，都指向下一个磁盘块。btree是一颗又矮又胖的树，可以有效减少查找过程中的io次数。

当需要查找范围数据的时候，btree需要在节点指针不断来回跳转才能找到数据，而且btree中的每一个磁盘块除了键值之外还存有数据，也会占有一定存储空间，也不能满足需求，此时我们就需要使用b+tree存储索引，b+tree中把数据都存在叶子数据节点中，叶子节点中每个数据都是有序的，并且每个叶子节点磁盘块之间有一个双向指针，可以在磁盘之间来回跳转，构成一个双向链表，这样使得它既适合等值查询又适合范围查询

数据插入的过程是如何维护b+tree的？

创建索引规约

1. 有唯一特性的字段必须建成唯一索引
2. 在varchar字段上建立索引时，必须指定索引长度
3. 建组合索引的时候，区分度最高的在最左边

SQL规约-索引

1. 注意字段类型：防止因字段类型不同造成的隐式转换，导致索引失效
2. 利用覆盖索引：避免行查询操作，避免回表
3. 利用有序性：如果有order by的场景，请注意利用索引的有序性
4. 禁模糊：页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决

**回表：**在InnoDB里面，主索引和数据是聚簇存储到一起的，像上面那个b+tree那样索引的叶子节点都是数据，如果是普通索引，那它的叶子节点就对应这条数据的主键。而这种根据辅助索引走一遍查到主键，再根据主键走一遍主索引查找到记录，就叫做回表。很明显，回表需要走一遍辅助索引和一遍主索引，这样就会导致产生磁盘io比较多，查询效率比较低。

超过三个表禁止join

SQL-规约-count

1. 拒绝替代：不要使用count(列名)或count(常量)来替代count(*)
2. 计算不重复行数：count(distinct col)计算该列除NULL之外的不重复行数
3. 当值全是NULL时：当某一列的值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为NULL

**SQL-规约-null值：**使用ISNULL()来判断是否为NULL值

**SQL-规约-分页：**若count为0应直接返回，利用延迟关联或者子查询优化超多分页场景

SQL-规约-避坑指南：

1. 不得使用外键与级联，一切外键概念必须在应用层解决
2. 禁止使用存储过程，存储过程难以调试和扩展，更没有移植性
3. 数据修订时，要先select，避免出现误删除，确认无误才能执行更新语句
4. 只要涉及多个表，都需要在列名前加表的别名（或表名）进行限定
5. SQL语句中表的别名前加as，并且以t1、t2、t3...的顺序依次命名
6. in后边的集合元素数量，控制在1000个之内

SQL性能优化的目标：

1. Explain工具使用技巧
2. 达到range级别

ORM映射规约

1. 在表查询中，一律不要使用*作为查询的字段列表
2. POJO类的布尔属性不能加is，而数据库字段必须加"is_"
3. 查询返回结果都需要使用ResultMap映射
4. 不要使用${}——容易引起SQL注入
5. 不要使用MyBatis自带的queryForList方法
6. 不允许直接使用HashMap与Hashtable接收结果集
7. 更新数据表记录时，必须同时更新update_time
8. 不要写一个大而全的数据更新接口

数据库设计三大范式

1. 每列属性不可拆分
2. 表中的每列都和主键相关
3. 每列都和主键列直接相关，而不是间接相关

分析设计 -> 画ER图 -> 画物理图