T31Day03Mysql规约

首先，什么是规约？

规约经过相互协议规定下来的共同遵守的条款。

为什么要制订规约？

为了方便多人合作，及降低沟通成本。

一、建表规约

作用：解决数据库相关名称的纠结，选择合适的数据类型及长度。

1.表和字段命名：

        必须使用小写字母或数字

        禁止出现数字开头

        禁止两个下划线中间只出现数字

        不使用复数名词

        禁用保留字

        是与否概念的字段，必须使用 is_xxx 的方式命名

注：对应的在Java中命名该字段是不应为isXxx，直接命名xxx即可然后通过字段映射绑定。

2.数据类型：

        小数类型为decimal

        货币数据使用最小货币单位，数据类型为bigint

注：货币使用BigInt存储时应将数据扩大至整数，如精确到小数点后2位即存储时*100，使用时计算完毕在除以100；

        字符串长度几乎相等使用CHAR

注：这是因为Varchar比Char在存储时需要多2个字节存储数据长度，

        varchar长度不要超过5000

注：若超过5000，最好新建一个表通过建一个text字段存储。

3.表必备三字段：

id：一般为主键自增，并添加主键索引，方便查询。

create_time:创建时间

update_time:更新时间

2个时间字段方便查询数据的变更情况。

4.数据库建议及避坑指南：

表的命名最好遵循“业务名称_表的作用”：如产品类型可建表“product_type”

数据库的名称应尽量与应用名称保持一致。

如果修改字段含义或对字段表示的状态追加时，需要及时更新字段注释

字段允许适当冗余，以提高查询性能，但必须考虑数据一致。

单表行数超过500w行或单表容量超过2GB，才推荐进行分库分表

二、索引规约

作用：提高查询效率的有效手段，合理的分析及使用索引。

1.索引的特性

持久性：创建索引需要在磁盘存储。

有序性：字面意思，有顺序。

2.索引的分类

①按存储形式分：

        聚簇索引

        非聚簇索引

②按数据约束分：

        主键索引

        唯一索引

        非唯一索引

③按索引列数量分：

        单列索引

        组合索引

注：innoDB可以创建的索引：主键索引、唯一索引、普通索引、不可以创建的索引：覆盖索引

3.索引的数据结构

①二叉查找树

特点：树高太高，节点从磁盘存取，IO次数太多。

②bTree

特点：每次从磁盘读取的数据变多，优化了每次读取的数量，降低树高 ，但是循环查询不方便。

③B+Tree

 特点：键值不存储数据集，所有数据都存在叶子节点上，非叶子磁盘块可以存储的键值变得更多，降低树高，将数据磁盘块形成一个双向链表，优化了范围查询。

4.索引名称规约

索引命名：

        主键索引命名为pk_xxx

        唯一索引命名为pk_xxx

        普通索引命名为idx_xxx

5.创建索引规约

有唯一特性的字段必须建成唯一索引

在Varchar字段上建立索引时，必须指定索引长度（短的话无所谓）

建组合索引的时候，区分度最好的字段放在最左边。

6.避坑指南

索引宁缺勿滥：不要认为一个查询就需要建 一个索引

吝啬索引创建：不要认为索引会消耗空间、严 重拖慢记录的更新以及 行的新增速度

抵制惟一索引：不要认为惟一索引一律需要在 应用层通过“先查后插” 方式解决

三、SQL与ORM映射规约

作用：优化SQL语句的要点，Explain的使用指南

1.SQL规约-索引

注意字段类型：防止因字段类型不同造成的隐式转换，导致索引失效。

利用覆盖索引：利用覆盖索引来进行查询操作，避免回表。

利用有序性：如果有order by的场景，请注意使用索引的有序性

禁模糊：页面搜索严禁左模糊或者全模糊，如果业务需要请走搜索引擎来解决

2.SQL规约-count

拒绝替代：不要使用count（列名）或count（常量）来替代count（*）;(mysql官方好像说count（*）有特殊优化。)

计算不重复行数：count（distinct col）计算该列除null之外的不重复行数

当值全是null时：count（col）的返回结果是0，但是sum（col）的返回结果为null

3.SQL规约-分页

若Count为0：分页查询逻辑时，若count为0应直接返回。

优化超多分页场景：利用延迟关联或子查询优化超多分页场景。

例：若ID为自增索引，可在查询分页时传入之前查询过的数据的最大ID或最小ID，加上Where条件缩小数据范围。

4.SQL规约-null值

使用isNUll（）来判断是否为null值

NULL<>NULL返回结果是NULL，而不是false；

NULL=NULL返回结果是NULL，而不是true；

NULL<>1返回结果是NULL，而不是true；

5.SQL规约-避坑指南

        不得使用外键与级联，一切外键概念必须在应用层解决。

        禁止使用存储过程，存储过程难以调试和扩展，更没有移植性

        数据订正时，要先select，避免出现误删除，确认无误才能执行更新语句

        只要涉及多个表，都需要在列名前加表的列名（或表名）进行限定

        SQL语句中的表前加AS，并且以t1、t2、t3的顺序依次命名

        in后边的集合元素数量，控制在1000个以内。

6.SQL性能优化的目标：

至少要达到Range的级别

尽量不要使用filesort

避免全索引扫描

7.ORM映射规约

        在表查询中，一律不要使用 * 作为查询的字段列表 

        POJO 类的布尔属性不能加 is，而数据库字段 必须加“is_” 

        查询返回结果都需要使 用ResultMap映射 

        不要使用${} 

        不要使用MyBatis 自带的 queryForList方法 

        不允许直接使用 HashMap 与 Hashtable接收结果集 

        更新数据表记录时，必须同时更新 update_time 

        不要写一个大而全的数据更新接口