2021.10.28 孤尽训练营D3--mysql规约

无规矩不成方圆，使用mysql时也一样，团队协作时使用同一套规约，能使团队的代码看起来有如一个人在开发，阅读、修改代码的效率也能相应提高。mysql规约除了能使阅读更统一与方便，还有性能上的规约，这些规约都是前人的多年经验总结，对于代码效能和数据库性能都有很大的提升，应铭记于心。笔记如下：

---

大纲：建表规约、索引规约、SQL与ORM映射规约、数据库设计实战

（一）建表规约

表、字段命名规约：

• 必须使用小写字母或数字，不用驼峰命名
• 禁止出现数字开头
• 禁止两个下划线中间只出现数字
• 不使用复数名词
• 禁用保留字
• 是与否概念的字段，必须使用is_xxx的方式命名

选择字段数据类型时候的强制规约

• 小数类型为decimal，不能选择float、double
• 货币数据使用最小货币单位，数据类型为bigint（比如人民币用分作为单位进行存储）
• 字符串长度几乎相等使用char（比如身份证号，定长。好处：在数据库存储中比varchar节约2个字符，节省空间；建索引的时候不用计算长度；
• varchar长度不要超过5000（varchar最长可存储65535，超过5000最好选择text，因为varchar一般要建索引，太长了的话建索引就没意义了）

表必备三字段：id，create_time，update_time

建表推荐规约：

1. 表的命名最好是遵循“业务名称_表的作用”
2. 库名与应用名称尽量一致
3. 如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释
4. 字段允许适当冗余，以提高查询性能，但必须考虑数据一致
5. 单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表（如果对业务影响不大，则没必要分库分表）

（二）索引规约

索引的维护图解：https://www.cs.usfca.edu/ ~galles/visualization/Algorithms.html

索引的特性：

• 持久性（如果查询性能不能满足业务需求的话才考虑建索引，索引占用的存储空间与数据库几乎是1:1）（保存到磁盘，占用磁盘空间）
• 有序性（查询效率）

索引的分类：

• 存储形式（与存储引擎有关，innoDB）：聚簇索引（即主键）、非聚簇索引（除了主键之外都是）
• 数据约束：主键索引、唯一索引（该字段不能有重复的值）、非唯一索引
• 索引列的数量：单列索引、组合索引（一个索引可以包含多个字段）
• innoDB可以创建的索引：主键索引、唯一索引、普通索引
• 不能创建的索引？-> 覆盖索引（不是创建的，是一种索引的使用形式）

索引的数据结构：

1. 二叉查找树（等值查询效率高，范围查询效率也很高）
2. 当二叉查找树很高时，需要对其进行优化——btree（n叉树，特点：在中间节点中也会有数据，等值查询性能很好，但不太能满足范围查询）
3. 进一步优化—— b+tree 中间节点只存储键值，所有数据都只保存到叶子节点，叶子节点之间有指针，构成双向链表，因此可以满足范围查询（等值查询满足，范围查询也满足）

索引名称规约：

• 主键索引名为pk_字段名
• 唯一索引名为uk_字段名
• 普通索引名为idx_字段名

创建索引规约：（查询性能有问题时再去建索引）

1. 有唯一特性的字段必须建成唯一索引
2. 在varchar字段上建立索引时，必须指定索引长度
3. 建组合索引的时候，区分度最高的（字段）在最左边

（三）SQL规约

SQL规约-索引

• 注意字段类型：防止因字段类型不同造成的隐式转换，导致索引失效
• 利用覆盖索引：利用覆盖索引来进行查询操作，避免回表（回表：走辅助索引查到主键，再根据主键走一遍查到数据）

例子：建立(a,b,c)三个字段为组合索引

select * from table where a=1————这种方法会走辅助索引

改进：select id,a,b,c from table where a=1————这种方法走覆盖索引（查询字段跟索引的字段完全一致时）

• 利用有序性：如果有order by的场景，请注意利用索引的有序性
• 禁模糊：页面搜索禁止左模糊或者全模糊，如果需要请走搜索搜索引擎来解决
• 超过三个表禁止join

SQL规约-count

• 拒绝替代：不要使用count（列名）或count（常量）来替代count（*）
• 计算不重复行数：count（distinct col）计算该列除NULL之外的不重复行数
• 当值全是NULL值时：当某一列的值全是NULL时，count（col）的返回结果为0，但sum（col）的返回结果为NULL

SQL规约-null值

• 使用ISNULL()来判断是否为NULL值

SQL规约-分页

• 若count为0：分页查询逻辑时，若count为0应直接返回
• 优化超多分页场景：利用延迟关联或者子查询优化超多分页场景（加where id>****、先limit再join)

SQL规约-避坑指南

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

SQL性能优化的目标

• explain工具使用技巧（type和Extra字段最重要）
• 至少要达到range级别

（四）ORM映射规约

ORM映射：对象关系映射（数据库与代码中的对象进行关系映射）

• 在表查询中，一律不要使用*作为查询的字段列表
• POJO类的布尔属性不能加is，而数据库字段必须加“is_"
• 查询返回结果都需要使用ResultMap映射，不允许直接使用HashMap、Hashtable
• 不要使用${}，防止sql注入
• 不要使用MyBatis自带的queryForList方法（是查出所有数据再分页）
• 更新数据表记录时，必须同时更新update_time
• 不要写一个大而全的数据更新接口

（五）数据库实战

• 第一范式：每列属性不可拆分（比如家庭住址“XX省XX市XX区”可以拆）
• 第二范式：表中的每列都和主键相关（比如用户表，字段姓名、年龄。。。都跟用户相关）
• 第三范式：每列都和主键列直接相关，而不是间接相关

---

画ER图工具：

• powerDesigner
• https://gitee.com/robergroup/chiner轻量级 CHINER （前pdMan）https://gitee.com/robergroup/chiner
• processOn