建表规约
表、字段命名
- 必须使用小写字母或数字
- 禁止出现数字开头
- 禁止两个下划线中间出现数字
- 不使用复数名词
- 禁用保留字
- boolean的字段,必须使用is_xxx的方式命名
这里借用PPT中举的反例
数据类型
- 小数类型为decimal
- 货币数据使用最小货币单位,数据类型为bigint(读取后先除以100,存储时先乘以100)
- 字符串长度几乎相等的应该使用char(不足的会自动用空白补齐)
- varchar长度不要超过5000(这样便于建索引,超过5000应考虑用text或者blob)
表必备三字段
id,create_time, update_time
建表推荐规约
- 表的命名最好遵循“业务名称_表的作用”
- 库名与应用名称尽量一致
- 如果修改字段含义或对字段表示的状态追加时,需要及时更新字段注释
- 字段允许适当冗余,以提高查询性能,但必须考虑数据一致
- 单表行数超过500万行或者单表容量超过2GB,才推荐进行分库分表
索引
- 索引的特性
持久性,有序性
- 索引的分类
存储形式
1. 聚簇索引 (索引和数据存在一起)
2. 非聚簇索引(索引和主键存在一起,但数据在其他地方)
数据约束
1. 主键索引
2. 唯一索引
3. 非唯一索引
索引列的数量
1. 单列索引
2. 组合索引
innoDB可以创建的索引
1. 主键索引
2. 唯一索引
3. 普通索引(就是非唯一索引)
nnnoDB不可以建的索引:覆盖索引(通过组合索引来实现)
什么是覆盖索引(covering index)?往下看
创建索引规约
- 有唯一特性的字段必须建成唯一索引
- 在varchar字段上建立索引时,必须指定索引长度
- 建组合索引的时候,区分度最高的在最左边
创建索引避免有如下极端误解
- 索引宁滥勿缺(认为一个查询就需要建一个索引)
- 吝啬索引创建(认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度)
- 抵制唯一索引(认为唯一索引一律需要在应用层通过“先查后插”方式解决)
SQL规约
索引避坑指南
- 注意字段类型
防止因字段类型不同造成的隐式转换,导致索引失效
比如主键是varchar,但在where中被当成int来比较,这样查询就不会走索引。
- 利用覆盖索引
利用覆盖引来进行查询操作,避免回表
什么是回表?就是先走一遍非主键索引找到主键,然后再走一遍主键索引找到数据。这样需要查两遍数据库。
什么是覆盖索引?
比如表X上有id,a,b,c四个列,然后有一个a, b, c三个列组成的组合索引。
SELECT * FROM X WHERE a = 1;
这么写就需要回表(先用组合索引拿到主键,然后用主键索引拿到数据),相反,
SELECT id,a, b, c FROM X WHERE a = 1;
这么写就不需要回表,因为所有的列在组合索引里面都有了(id,即主键,就在组合索引的叶子节点里)
- 利用有序性
如果有order by的场景,请注意利用索引的有序性
- 禁模糊
页面搜索严禁左模糊或者全模糊,如果需要请走搜索引擎来解决。
count使用指南
- 拒绝替代
不要使用count(列名)或count(常量)来替代count(*)
- 计算不重复行数
count(distinct col)计算该列除NULL之外的不重复行数
- 当值全是NULL时
当某一列的值全是NULL时,count(col)的返回结果为0,但sum(col)的返回结果为NULL,但count(*)会返回行数。NULL参与的运算结果都是NULL。
NULL值 (NULL参与的运算结果都是NULL)
- NULL <> NULL
NULL <> NULL的返回结果是NULL,而不是false
- NULL = NULL
NULL = NULL的返回结果是NULL,而不是true
- NULL <> 1
NULL <> 1的返回结果是NULL,而不是true
分页指南
- 若count为0
分页查询逻辑时,若count为0应直接返回
- 优化超多分页场景
利用延迟关联或者子查询优化超多分页场景
延迟关联,就是延迟join,先缩小查询范围,在进行join
比如
Select * from (select id from a where col1='haha')as a1, b where a1.id = b.id
子查询会通过覆盖索引拿到需要的数据,然后再和b关联,获取数据。这比先把a和b关联再返回数据要快得多。
避坑指南
- 不得使用外键与级联,一切外键概念必须在应用层解决(外键效率低,对数据库造成压力)
- 禁止使用存储过程(stored procedure),存储过程难以调试和扩展,更没有移植性
- 数据订正时,要先select,避免出现误删除,确认无误才能执行更新语句
- 只要涉及多个表,都需要在列名前加表的别名(或表名)进行限定
- SQL语句中表的别名前加 as,并且以 t1、t2、t3、...的顺序依次命名
- in 后边的集合元素数量,控制在1000 个之内
ORM映射规约
- 在表查询中,一律不要使用 * 作为查询的字段列表(罗列列名)
- POJO 类的布尔属性不能加 is,而数据库字段必须加“is_”(有些框架会自动对is_开头的字段进行处理,造成问题)
- 查询返回结果都需要使用ResultMap映射
- 不要使用${} (防止SQL注入,用#{},即占位符)
- 不要使用MyBatis自带的queryForList方法(把所有数据都取来,然后做sub list来分页)
- 不允许直接使用HashMap与Hashtable接收结果集
- 更新数据表记录时,必须同时更新update_time
- 不要写一个大而全的数据更新接口(一个表对应一个类)