一、建表规约
设计规约的时候，是为了同步沟通语言，也是为了避免踩坑。
(1)目的
- 解决数据库相关名称的纠结
- 选择合适的数据类型和长度
(2)表、字段命名
- 必须使用小写字母或数字（错误示例：userName）
- 禁止出现数字开头（错误示例：3day）
- 禁止两个下划线中间只出现数字（错误示例：user_3_Name）
- 不使用复数名词（错误示例：members）
- 禁用保留字（错误示例：order）
- 是与否概念的字段，必须使用is_xxx的方式命名（is_del）
(3)建表强制规约
1、数据类型
- 小数类型为decimal
不要使用double和float，因为它们技术是科学计数法方式，在比较时可能会因为数值超过限制出错
- 货币数据使用最小货币单位，数据类型为bigint
- char适合身份证等，存储是定长的，不够后位补0，取出时记得trim
char存储长度是定长的，能够节约数据库空间。但正因为是定长，存储数据长度小于char字段长度时，它会后位补空格，取出时记得trim。另外在建索引的时候也不需要考虑多长。
- varchar长度不要超过5000。超过使用text，blob等，通过搜索引擎来，另建一张表，进行关联
2、表必备三字段
- id
- create_time
- update_time
(4)建表推荐规约
- 表的命名最好是遵循“业务名称_表的作用”
- 库名与应用名称尽量一致
如项目是T31，库名也用T31
- 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释
- 字段允许适当冗余，以提高查询性能，但必须考虑数据一致
- 单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。但实际上如果能满足查询性能，也没必要分
二、索引规约
索引的出现是为了提高查询效率。其实质是一种数据结构。按照这种数据结构，能够使得查询过程有一定的方向性。
(1)索引的特性
- 持久性，跟表存储空间几乎是1：1，需要提高查询效率时才建的。
- 有序性
(2)索引的分类
1、存储形式
- 聚簇索引（主键）
- 非聚簇索引
2、数据约束
- 主键索引
- 唯一索引
- 非唯一索引
3、索引列的数量
- 单列索引
- 组合索引
4、innoDB可以创建的索引
- 主键索引
- 唯一索引
- 普通索引
5、覆盖索引
不是创建的，是在使用组合索引的时候。select后面的字段都建了索引，这时候查询出来就不需要回表，更像是一种效果。这时候索引就得到充分利用
(3)索引的数据结构
[数据结构可视化工具](https://www.cs.usfca.edu/~galles/visualization/Algorithms.html）
1、二叉查找树
- 左子树小于根节点，右子树大于等于根节点
- 添加的第一个元素是根节点
2、平衡二叉查找树
- 左右两个子树的层级最多相差1，超过时进行左旋或者右旋
- mysql所有索引是存储在磁盘上，遍历一次就需要读磁盘一次。如果数据量很多的话，与内存io次数会过多，不合适，mysql每次读写最小值为16k（数据页为单位），若节点数据不足16k，会造成浪费。所以mysql不用平衡二叉树
3、btree
- 多叉树
- 每个磁盘块大小为16k
计算占空间的例子：长整型是8个字节，假如1个指针8个字节
磁盘块放的元素取决于大小，比如有10个字段，每个字段占10个字节，能存储元素的数量+1，就等于度。超过这个度，就会分裂成两个磁盘块。
- 中间节点存储键值和数据
- 适合等值查询，不适合范围查询
4、b+ tree
- 中间节点只存储键值，所有数据都存储在叶子节点
- 叶子节点之间有双向指针，形成链表，适合范围查询和等值查询
- 优点：树的高度降低；叶子节点中构成双向链表。
(4)索引名称规约
- 主键索引名为pk_字段名
- 唯一索引名为uk_字段名
- 普通索引名为idx_字段名
(5)创建索引规约
- 有唯一特性的字段必须简称唯一索引，包括唯一组合索引
- 在varchar字段上建立索引时，必须指定索引长度。如果长度超过500，索引长度只建前面几个就足够了，就是多长能够满足索引需要就只建多长。
- 建组合索引的时候，区分度最高的在最左边（最左原则）
(6)创建索引避免有如下极端误解
- 索引宁缺毋滥：增删改等操作都需要维护索引
- 吝啬索引创建
- 抵制唯一索引
三、SQL规约
(1)索引
- 注意字段类型：防止因字段类型不同造成的隐式转换，导致索引失效。
比如当 varchar 与数值对比时，就会导致索引效果失效。
- 利用覆盖索引来进行查询操作，避免回表
- 回表：通过辅助索引找到主键。根据主键找到目标值。
- 覆盖索引：索引的字段正好是你想查询的字段。
- 利用有序性：如果有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)三大范式
- 第一范式：每列属性不可拆分
- 第二范式：表中的每列都和主键相关
- 第三范式：每列都和主键列直接相关，而不是间接相关
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
