一、建表规约
设计规约的时候,是为了同步沟通语言,也是为了避免踩坑。
(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)三大范式
-
第一范式:每列属性不可拆分
-
第二范式:表中的每列都和主键相关
-
第三范式:每列都和主键列直接相关,而不是间接相关