20211028 开课吧T31 day3 数据库设计规约

day 3:
本次课， 主要是讲数据库建表相关的规范及索引相关的知识概括。

在表、字段的命名时，

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

强制的建表规约
在数据类型上，

1. 小数类型为decimal
2. 货币数据使用最小货币单位，数据类型为bigint
3. 字符串长度几乎相等的字符串字段， 数据类型用char
4. varchar可变长度的最大长度不要超过5000

建表推荐规约：

1. 表的命名最好是遵循“业务名称_表的作用”这个模式
2. 库名与应用名称尽量一致
3. 如果修改了表结构，或者追加了状态字段的字典，要及时更新字段的注释
4. 字段允许适当冗余， 以提高查询性能， 但冗余的字段在程序处理上， 要处理好数据一致性的问题
5. 单表行数超过500万或者单表容量超过2GB，才推荐分库分表。

索引规约
索引是提高查询效率的有效手段，但是我们需要合理的分析和使用索引。索引的特性是持久性和有效性，
索引是什么？为了提高查询性能， 创建的一个独立的数据结构。
优势：
​ 可以提供数据检索的效率， 降低数据库的IO成本， 类似于书的目录。
​ 通过索引对数据进行排序， 降低数据排序成本， 降低CPU消耗
劣势：
​ 索引占用磁盘空间
​ 提高查询效率， 但会降低更新表的效率

索引的分类:
按存储形式分：聚簇索引与非聚簇索引
按数据约束分：主键索引、唯一索引、非唯一索引
按索引列的数量分：单例索引、组合索引
innoDB可以创建的索引：主键索引、唯一索引、普通索引

索引的存储数据结构 b+tree，关系型数据库的索引结构， 都是 b+tree
为了便于快速查询，比如树的深度为3，那么深度1、2的节点 只存键值（key,类似于数据指针，标志数据value在哪个磁盘块），深度为3的叶子节点存数据。所有数据(value)都存在叶子节点上。 并且叶子节点还构成了双向链表，这样的话， 不仅满足了满足等值查询， 又满足了范围查询。

对于索引的使用还有一套顺口溜：

1. 全职匹配我最爱，最左前缀要遵守
   ​ 编写sql查询条件时最好是等值查询， 如果是等值查询查询条件中的多个字段都可以应用索引。
   ​ 如果使用范围查询或者不等于查询条件导致后面的条件不能应所索引。

2. 带头大哥不能死， 中间兄弟不能断（组合索引）
   ​ 查询条件中第一个字段必须存在， 如果不存在不能应用组合索引。中间字段如果缺失， 导致后面的字段都无法应用索引。

3. 索引列上不计算， 范围之后全失效；
   查询条件包含字段的计算 例如 a+1=10 substr 等， 隐式数据类型转换也会导致索引失效

4. like百分写最右， 覆盖索引不写星；
   使用like查询时也可以应用索引， 但要求“%”不能出现在最左边，
   ​ 可以应用索引的案例：
   ​ where name like ‘张%’
   ​ 不可以的例子：
   ​ like ‘%z’
   ​ like ‘%z%’
   ​

5. 不等控制还有OR， 索引失效要少用。
   ​ 相当于范围查询
   ​ is null 可以应用索引
   ​ is not null 导致索引失效。

当然我们什么时候需要创建索引， 如何优化sql， mysql有内置的sql分析工具：
explain
具体使用方法这里就不再详细述说。

在组合索引的使用上， 有一个名词叫做“覆盖索引”，那么什么是覆盖索引呢 ？
因为主索引和数据是存在一起的， 索引的叶子节点存储的就是数据。 覆盖索引 就是我们创建的索引里面包含了所有我们需要查询的字段， 这样的话， 我们在查询的时候， 通过索引的应用， 我们就可以一次得到我们需要的全部字段

例个栗子：
有个表 id, a,b,c,d 我们在a/b/c上有组合索引
explain select * from t_nultiple_index where a=1 and b=2 and c=‘3’;
这时候以上的select * 的返回结果是通过回表（查到主键键值， 再通过主键索引获取数据）查出来的，产生多次磁盘io

如果我们这么写，select id, a,b,c from t_nultiple_index where a=1 and b=2 and c=‘3’;
这时候就是覆盖索引 索引里面包含了所有你需要查询的字段数据，不再需要通过主键索引去获取数据， 提升了查询效率

因此， 我们在组合索引的使用上， 在select的时候，尽量指定我们需要查询的列， 不要写 *。

sql编写规约：
count

• 拒绝替代 不要使用select count(列名) 或者 count(常量) 来替代 count(*)
• 使用count(distinct col) 计算除null之外的不重复行
• 当某一列全是null时， count(col) 的返回结果是0， 而sum(col) 的返回结果为null

分页

• 若count为0， 应直接返回
• 利用延迟关联或者子查询优化超多分页的场景
  例如一个大表和另一个表的join查询的分页， 我们是先join 再分页效率高呢？ 还是 先对大表进行分页， 再join效率高呢？ 显而易见是后者。

避坑指南

• 不得在数据库表中使用外键与级联， 但E-R图中要体现， 外键与级联的功能放在代码里面实现
• 禁止使用存储过程， 难以调试及扩展， 更没有移植性
• 做数据修改时， 先对条件进行select查询确认无误后， 再执行， 避免误更新或者删除
• 对于多个表的关联查询， 在查询结果列前均需要加上表的别名进行限定
• sql语句中的表的别名定义， 加上as以便于阅读
• in里面的集合元素，控制在1000个以内

ORM映射规约-mybatis

• 在查询时， 不要用*作为查询的字段列表
• pojo的布尔属性不能加is，而数据库字段必须加“is_”
• 查询返回结果都需要使用ResultMap映射
• 不要使用${} ， 使用#{} 防止sql注入
• 不要使用mybatis自带的queryForList方法
• 不允许直接使用HashMap或HashTable接收结果集
• 更新数据库表记录时， 必须同时更新update_time
• 不要写一个大而全的数据更新接口

数据库设计实战- 见DAY4