一、建表规约

(1)目的

• 解决数据库相关名称的纠结
• 选择合适的数据类型和长度

(2)表、字段命名

• 必须使用小写字母或数字（错误示例：userName）
• 禁止出现数字开头（错误示例：3）
• 禁止两个下划线中间只出现数字（错误示例：userName）
• 不使用复数名词（错误示例：userName）
• 禁用保留字（错误示例：userName）
• 是与否概念的字段，必须使用is_xxx的方式命名

(3)建表强制规约

1、数据类型

• 小数类型为decimal，不要使用double和float
• 货币数据使用最小货币单位，数据类型为bigint
• char适合身份证等，存储是定长的，不够后位补0，取出时记得trim
• varchar长度不要超过5000。超过使用text，blob等，通过搜索引擎来，另建一张表，进行关联

2、表必备三字段

• id
• create_time
• update_time

(4)建表推荐规约

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

二、索引规约

(1)索引的特性

• 持久性，跟表存储空间几乎是1：1，需要提高查询效率时才建的。
• 有序性

(2)索引的分类

1、存储形式

• 聚簇索引（主键）
• 非聚簇索引

2、数据约束

• 主键索引
• 唯一索引
• 非唯一索引

3、索引列的数量

• 单列索引
• 组合索引

4、innoDB可以创建的索引

• 主键索引
• 唯一索引
• 普通索引
• 覆盖索引，不是创建的，是在使用组合索引的时候。select后面的字段都建了索引，这时候查询出来就不需要回表，更像是一种效果。

(3)索引的数据结构

[数据结构可视化工具](https://www.cs.usfca.edu/~galles/visualization/Algorithms.html）

1、二叉查找树

• 左子树小于根节点，右子树大于等于根节点

2、平衡二叉查找树

• 左右两个子树的层级最多相差1，超过时进行左旋或者右旋
• 索引是存储在磁盘上，如果数据量很多的话，与内存io次数会过多，不合适，mysql每次读写最小值为16k（数据页为单位），若节点数据不足16k，会造成浪费。

3、btree

• 多叉树
• 每个磁盘块大小为16k
• 中间节点存储键值和数据
• 适合等值查询，不适合范围查询

4、btree

• 中间节点只存储键值，所有数据都存储在叶子节点
• 叶子节点之间有双向指针，形成链表，适合范围查询和等值查询

(4)索引名称规约

• 主键索引名为pk_字段名
• 唯一索引名为uk_字段名
• 普通索引名为idx_字段名

(5)创建索引规约

• 有唯一特性的字段必须简称唯一索引，包括唯一组合索引
• 在varchar字段上建立索引时，必须指定索引长度
• 建组合索引的时候，区分度最高的在最左边（最左原则）

(6)创建索引避免有如下极端误解

• 索引宁缺毋滥：增删改等操作都需要维护索引
• 吝啬索引创建
• 抵制唯一索引

三、SQL规约

(1)索引

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

• 超过三张表禁止join

  
  
  隐式转换索引失效
  
  
  未隐式转换索引生效

(2)count

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

(3)null值

• NULL<>NULL返回NULL，而不是false
• NULL=NULL返回NULL，而不是true
• NULL<>1返回NULL，而不是true

(4)分页

• 分页查询时，若count为0应直接返回
• 优化超多分页场景：利用延迟关联或者子查询优化超多分页场景。深翻页：若知道当前页的id，翻到下一页，则利用where id > 10000进行范围限定
• join小结果集在左侧

(5)避坑指南

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

(6)SQL性能优化的目标

• explain的type至少要达到range级别

(7)覆盖索引

覆盖索引

(8)全索引扫描，磁盘扫描

全索引扫描

(9)全索引扫描，磁盘扫描

filesort

创建 idx_bd(b,d)

image.png

四、ORM映射规约

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

PS：jpa是规范，mybatis实现数据库到类的映射，没实现

五、数据库设计实战

(1)三大范式

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