1、建表规约

强制规约

1.1 表、字段命名

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

1.2 数据类型

1. 小数类型为decimal
2. 货币数据使用最小货币单位，数据类型为bigint
3. 字符串长度几乎相等使用char（好处1 省了长度的字节空间 好处2 建索引时候，不需要考虑长度）
4. varchar长度不要超过5000

1.3 建表必备三字段

1. id
2. create_time
3. update_time

推荐规约

1. 表的命名最好是遵循“业务名称_表的作用”
2. 库名与应用名称尽量一致
3. 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释
4. 字段允许适当冗余，以提高查询性能，但必须考虑数据一致
5. 单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表

2、索引规约

提高查询效率的有效手段
合理的分析及使用索引

2.1 索引的特性

1. 持久性
2. 有序性

2.2 索引的分类

通过存储形式分类：

1. 聚簇索引
2. 非聚簇索引 通过数据约束分类：
3. 主键索引
4. 唯一索引
5. 非唯一索引 通过索引列的数量分类：
6. 单列索引
7. 组合索引 innoDB可以创建的索引：
8. 主键索引
9. 唯一索引
10. 普通索引 覆盖索引是组合索引的一种使用方式，不需要回表查询

2.3 索引的数据结构

2.3.1 二叉查找树

1. 二叉查找树，不理想状态，会退化成链表
2. 平衡二叉查找树，左右两个子树的层级最多相差1
3. 查找一个数据的的次数，取决于树的高度，时间复杂度为O(logn),当数据量大的时候，二叉树的高度会很大，磁盘IO的次数也会很大，所以尽可能的减少树的高度，这时候，B树就出现了

2.3.2 B树

MYsql在从磁盘读数据的时候，一次读取的单位是一页，16k的大小，所以需要一种数据结构，尽可能一个节点，存储一个页大小的数据

1. B树的一个非叶子节点的结构有三部分，指针、键值、数据，叶子节点，只有键值、数据
2. 一个磁盘块默认16k，以一个磁盘块读取。一个磁盘块能够存储的数据，就取决于数据的大小。例子：一个指针、主键、数据，是16个字节。一个磁盘块，就是1024个数据。约取1000个数据。如果一个树的高度是2，那么这个B树，可以存储的数据，就是1000*1000。在百万量级的数据时，树的高度，也就是2，3，只需要2到3次的磁盘IO，每次在内存中遍历一次，就可以查到数据
3. B树的结构，对于等值查询的效率高，但是对于范围查询，就需要回溯，需要不断的节点直接跳转，范围查询效率低

2.3.4 B+树

1. 改善B树，所有的数据只保存在叶子节点
2. 将B树中，非叶子节点的磁盘块的数据所占用的空间释放出来，用于存储指针和键值，那么会比B树，存放更多的指针和键值
3. 叶子节点，使用双向链表结构存储，这样范围查询时候，效率就会很高，只需要找到开始位置，结束位置就可以，通过链表读取

2.4 索引命名规约

1. 主键索引名为pk_字段名
2. 唯一索引名为uk_字段名
3. 普通索引名为idx_字段名

2.5 创建索引规约

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

2.6 创建索引的误解

1. 索引宁缺毋滥，认为一个查询就需要创建一个索引
2. 吝啬索引创建，认为索引会消耗空间、严重拖慢记录的更新以及行的更新速度
3. 抵制唯一索引，认为唯一索引一律需要再应用层通过“先查后插”方式解决

3、SQL与ORM映射规约

3.1 SQL规约

1. 注意字段类型，防止因字段类型不同造成的隐式数据转换，造成的数据索引失效
2. 利用覆盖索引，避免回表
3. 利用有序性，如果有order by 的场景，请注意利用索引的有序性
4. 禁止模糊查询，禁止左模糊，全模糊查询。如果需要请走搜索引擎来解决
5. 禁止超过三个表join

3.2 count

1. 拒绝替代 ，不要使用count(列名)、count(常量)替代count(*)
2. 计算不重复行数， count(distinct col)计算该列出NUll之外的不重复行数
3. 当值全是null时，count(col)的返回结果是0， 但是sum(col)返回结果是null

3.3 NULL值

null值参与运算的时候，结果都是null

3.4 分页

1. 若count为0，直接返回，不用再次查询
2. 优化超多分页场景，利用延迟关联或者子查询优化超多分页场景

3.5 避坑指南

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

3.6 EXPLAN 执行计划

要优化到range级别

3.7 ORM映射规约

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

4、数据库设计实战

4.1 数据库设计三大范式

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

4.2 需求分析

4.3 T31数据库设计E-R图