T31-Day3数据库规约

Day3-数据库

建表规约

表、字段的命名规范

1. 必须使用小写字母或者数字
2. 禁止开头出现数字
3. 禁止两个下划线中间只出现数字
4. 不使用复数名词
5. 禁用保留字
6. 是否概念的字段，必须使用is_xxx的方式命名

字段名错误的反例

1. 驼峰命名法 userName
2. 下划线中间有数字 updae_1_ime
3. 数字开头 3day
4. 复数 users members
5. 保留字 order

数据类型要求

1. 小数类型为decimal
   因为float、double类型存在精度损失问题
2. 货币数据使用最小货币单位，数据类型为bigint
   比如$10.32 用分为单位 ，存储1032 展示时再除以100，这样避免了浮点运算精准问题。
3. 字符串长度几乎相等使用char
   1. 节约字符长度
   2. 便于建立索引
4. varchar长度不要超过5000
   如果超过使用text并独立出一张新表用主键进行关联，避免影响整个表的效率（会降低页存储数，提高IO次数）

表必备字段

id：主键，bigint unsigned类型，单表时可采用步长1的自增。
create_time/update*time：datetime类型 *在创建数据和更新数据时进行记录

推荐规约

1. 表名最好遵循业务名称_表的作用
   例如 ：t31_order alipay_task
2. 库名与应用名称尽量一致
3. 如果修改字段含义或者对字段表示的 状态追加时，需要及时更新字段注释
   例如某个枚举字段 有值a,b,c 现在增加d 需要加注释
4. 字段允许适当冗余，亿提高查询性能，但必须考虑数据一致
   比如电商系统里 商品名称 可以适当冗余避免关联查询减少join，但是如果遇到修改需要同步修改所有存储商品名称的地方。需要注意冗余的字段不能是频繁修改字段、唯一索引以及超长字段varchar/text。
5. 单表行数超过500万行或单表容量超过2G，才推荐进行分库分表。
   分库分表需要谨慎，因为这会引入更多的中间件，会带来系统的复杂性，也会使数据库操作更加复杂。决定分库分表时需要根据业务趋势判断表的数据级别来决策，未来数年都可能达到这量级的表完全不需要分。

索引

提高查询效率的有效手段，建索引时要考虑它的 持久性，有序性。

索引的分类

存储形式：聚簇索引，非聚簇索引

数据约束：主键索引，唯一索引，非唯一索引

索引列的数量：单列索引，组合索引

innoDB可以创建的索引：主键索引，唯一索引，普通索引

索引的数据结构：B tree

SQL索引规约

1.索引命名

主键索引名为pk字段名，唯一索引名为uk_字段名，普通索引名为idx_字段名

2.新建索引

• 有唯一特性的字段必须建成唯一索引
• 在varchar字段上建立索引时，必须指定索引长度。
  根据实际文本的区分度确定索引长度 （这一块我还不是很理解）
• 建组合索引的时候，区分度最高的在左边
  最左最前原则

3.创建索引要避免几个误区

• 认为一个查询就需要建一个索引
  索引是需要磁盘存储空间的，并且过多的索引会影响插入/修改/删除的速度。试想每次修改一个字段都要同时修改和它有关的各个索引。
• 认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度
  其实这个和上一条是相反的两个极端，索引确实会影响增删改速度但是如果涉及合理这个可以忽略不记（通常只需要几次IO操作）。
• 认为唯一索引需要在应用层通过“先查后插方式解决”
  唯一索引内部原理暂时不懂，还需要研究一下

4.索引的规约

1. 注意字段类型，防止因字段类型不同造成的隐式转换，导致索引失效
   经典例子：表存储varchar/char字符串但是where条件中用不带引号数字查询，能查询出结果有但是通过全表扫描方式。
2. 利用索引覆盖：利用覆盖索引避免回表操作
3. 利用有序性：如果有order by的场景，请注意利用索引的有序性
4. 禁止模糊查询：页面搜索严谨左模糊或者全模糊查询（无法走索引）

5.超过三个表禁止join

需要join的字段，数据必须保证数据类型一直，多表关联查询要保证倍关联的字段又索引

6.count

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

7.分页

1. 分页查询逻辑时，如果count为0应该直接返回
2. 超多分页的场景应该利用延迟关联或者子查询优化超多分页场景
   比如确定前一页的最大主键为100000 直接通过条件定位>100000 在limit页数

8.null值

1. NULL<> NULL:返回结果是null 而不是false
2. NULL=NULL 返回结果也是NULL 而不是true
3. NULL <> 1 返回结果是null
   总结：NULL值比较结果只会是NULL 所以需要使用**ISNULL()**来判断是否为NULL值

9.避坑指南

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

SQL与ORM映射规约

映射规约

1. 在表查询中，一律不要使用*座位查询的字段列表
   避免拿不到值
2. POJO类的布尔属性不能加is，而数据库字段必须加“is_"
3. 查询返回结果都需要使用ResultMap映射
4. 不要使用${ }
   容易引起sql注入
5. 不要使用MyBatis自带的queryForList方法
   性能太差，这个方法把所有的数据取到内存再sublist
6. 不允许直接使用HashMap与Hashtable接受结果集
7. 更新数据表记录时，必须同时更新 update_time
8. 不要写一个大而全的数据更新接口