5.MySQL数据库规约

5.MySQL数据库

5.1 建表规约

1.命名规范

a) 表名、字段名、索引名都是小写字母或数字，不允许数字开头；禁用保留字命名
b) 表名以单数命名，与DO保持一致
c) 索引命名，pk_xxx/uk_xxx/idx_xxx -> 主键、唯一、普通索引

2.特别注意的字段类型

a) 表示是与否的字段，命名方式 is_xxx，字段类型为 tinyint unsigned(无符号，只表示正数)；
举栗：is_deleted 0：未删除，1：删除
POJO: deleted，避免命名isDeleted框架序列化失败
b) char 类型,当字符串长度几乎相等时考虑使用
c) varchar 可变长字符串，长度不要超过5000，否则考虑使用text，并单独拆分一张表存储
d) decimal 存储小数类型，不要使用double/float，存在精度丢失的问题(二进制无法精确表示十进制小数)

3.表必备三个字段

id/create_time/update_time
id 为 bigint unsinged、单表时自增、步长为1

5.2 索引规约

1.唯一索引

业务上具有唯一性的单个或多个字段需要建立唯一索引约束，不能仅通过应用来保证唯一性，根据墨菲定律，必然有脏数据产生

2.覆盖索引

利用覆盖索引查询操作，避免回表

3.组合索引

创建组合索引时，选择性强、区分度高的字段在左侧，遵循最左原则

4.前缀索引

- 禁止左模糊、全模糊，无法利用索引，如果需要，请使用搜索引擎
- 在varchar字段建立索引，需要指定索引长度(没有必要对全字段建立索引)，对于一般字符串类型，索引长度20能够保证90%的区分度
  区分度计算方法：count(distinct(left(col,length)))/count(*)

5.字段类型不同造成隐式转换，导致索引失效

- 三个表以上禁止 join，join 的字段类型需要保持绝对一致
- 字段类型不匹配，隐式转换会导致索引失效(开发中常见情况)

6.优化

- 深度分页优化，MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后放弃前 offset 行，返回 N 行，这样就导致深度分页性能差
  利用自增 id 避免深度分页
    where id > 100000 and id < 100010
  利用子查询，快速定位 id，然后再关联
    SELECT a.* FROM  t1 a, (select id from  t1 where  条件  LIMIT 100000,10 ) b where a.id=b.id
- SQL 性能优化至少达到 range 级别
  consts：点查(只匹配一条数据)，主键或唯一索引
  ref：普通索引查询
  range：对索引进行范围查询

5.3 SQL语句

1.常用函数

count：
- count(*) 是 SQL92 定义的标准统计行数的函数，跟数据库无关，跟 NULL 和非 NULL 无关
- count(col) 无法统计列 col 是 NULL 的行

sum：
当一列都是 NULL 时
- count(col) = 0
- sum(col) = NULL，可以使用 SELECT IFNULL(SUM(column), 0) FROM table 避免 NPE

ISNULL：
使用 `ISNULL(col)`来判断是否为NULL值，既简洁易懂、标识一个整体，又执行效率较高

2.数据库特性

外键级联：禁止使用外键级联，外键概念在应用层实现；外键适合于单体系统、低并发，不适合分布式、高并发系统，级联更新是强阻塞
存储过程：禁止使用存储过程，难以调试、扩展、更不可移植

3.多表关联操作

多表关联，列前加表的别名进行限制、别名使用 as，select stu.name from student as stu ... 

4.查询列

在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
- 增加查询分析器解析成本。
- 增减字段容易与 resultMap 配置不一致。
- 无用字段增加网络消耗，尤其是 text 类型的字段。

5.更新数据表记录时，必须同时更新记录对应的 update_time 字段值为当前时间

5.4 ORM映射

1.POJO 类的布尔属性不能加 is，而数据库字段必须加 is_，要求在 resultMap 中进行字段与属性之间的映射。

2.sql.xml 配置参数使用：#{}，#param# 不要使用 ${} 此种方式容易出现 SQL 注入

3.Mybatis 自带的 queryForList(String statementName,int start,int size)不推荐使用，会查询所有记录，subList 取子集