20211030 mysql设计

一、建表规约

设计规约的时候，是为了同步沟通语言，也是为了避免踩坑。

(1)目的

• 解决数据库相关名称的纠结

• 选择合适的数据类型和长度

(2)表、字段命名

• 必须使用小写字母或数字（错误示例：userName）

• 禁止出现数字开头（错误示例：3day）

• 禁止两个下划线中间只出现数字（错误示例：user_3_Name）

• 不使用复数名词（错误示例：members）

• 禁用保留字（错误示例：order）

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

(3)建表强制规约

1、数据类型

• 小数类型为decimal

  不要使用double和float，因为它们技术是科学计数法方式，在比较时可能会因为数值超过限制出错

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

• char适合身份证等，存储是定长的，不够后位补0，取出时记得trim

  char存储长度是定长的，能够节约数据库空间。但正因为是定长，存储数据长度小于char字段长度时，它会后位补空格，取出时记得trim。另外在建索引的时候也不需要考虑多长。

• varchar长度不要超过5000。超过使用text，blob等，通过搜索引擎来，另建一张表，进行关联

2、表必备三字段

• id

• create_time

• update_time

(4)建表推荐规约

• 表的命名最好是遵循“业务名称_表的作用”

• 库名与应用名称尽量一致

  如项目是T31，库名也用T31

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

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

• 单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。但实际上如果能满足查询性能，也没必要分

二、索引规约

索引的出现是为了提高查询效率。其实质是一种数据结构。按照这种数据结构，能够使得查询过程有一定的方向性。

(1)索引的特性

• 持久性，跟表存储空间几乎是1：1，需要提高查询效率时才建的。

• 有序性

(2)索引的分类

1、存储形式

• 聚簇索引（主键）

• 非聚簇索引

2、数据约束

• 主键索引

• 唯一索引

• 非唯一索引

3、索引列的数量

• 单列索引

• 组合索引

4、innoDB可以创建的索引

• 主键索引

• 唯一索引

• 普通索引

5、覆盖索引

不是创建的，是在使用组合索引的时候。select后面的字段都建了索引，这时候查询出来就不需要回表，更像是一种效果。这时候索引就得到充分利用

(3)索引的数据结构

[数据结构可视化工具](https://www.cs.usfca.edu/~galles/visualization/Algorithms.html）

1、二叉查找树

• 左子树小于根节点，右子树大于等于根节点

• 添加的第一个元素是根节点

• 

2、平衡二叉查找树

• 左右两个子树的层级最多相差1，超过时进行左旋或者右旋

• mysql所有索引是存储在磁盘上，遍历一次就需要读磁盘一次。如果数据量很多的话，与内存io次数会过多，不合适，mysql每次读写最小值为16k（数据页为单位），若节点数据不足16k，会造成浪费。所以mysql不用平衡二叉树

3、btree

• 多叉树

• 每个磁盘块大小为16k

  计算占空间的例子：长整型是8个字节，假如1个指针8个字节

  磁盘块放的元素取决于大小，比如有10个字段，每个字段占10个字节，能存储元素的数量+1，就等于度。超过这个度，就会分裂成两个磁盘块。

• 中间节点存储键值和数据

• 适合等值查询，不适合范围查询

4、b+ tree

• 中间节点只存储键值，所有数据都存储在叶子节点

• 叶子节点之间有双向指针，形成链表，适合范围查询和等值查询

• 优点：树的高度降低；叶子节点中构成双向链表。

(4)索引名称规约

• 主键索引名为pk_字段名

• 唯一索引名为uk_字段名

• 普通索引名为idx_字段名

(5)创建索引规约

• 有唯一特性的字段必须简称唯一索引，包括唯一组合索引

• 在varchar字段上建立索引时，必须指定索引长度。如果长度超过500，索引长度只建前面几个就足够了，就是多长能够满足索引需要就只建多长。

• 建组合索引的时候，区分度最高的在最左边（最左原则）

(6)创建索引避免有如下极端误解

• 索引宁缺毋滥：增删改等操作都需要维护索引

• 吝啬索引创建

• 抵制唯一索引

三、SQL规约

(1)索引

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

  比如当 varchar 与数值对比时，就会导致索引效果失效。

  

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

  1. 回表：通过辅助索引找到主键。根据主键找到目标值。

  2. 覆盖索引：索引的字段正好是你想查询的字段。

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

  如果字段没有索引则利用文件索引

• 页面搜索严谨左模糊或者全模糊，如果需要请走搜索引擎来解决

• 超过三张表禁止join

  

  隐式转换索引失效

  

  未隐式转换索引生效

(2)count

• 拒绝替代：不要使用count(列名)或count(常量)来替代count(*)

• 计算不重复行数：count(distinct col)计算该列除NULL之外的不重复行数

• 当值全是NULL时，count(col)的返回结果为0，但sum(col)的返回结果为NULL

(3)null值

• NULL<>NULL返回NULL，而不是false

• NULL=NULL返回NULL，而不是true

• NULL<>1返回NULL，而不是true

(4)分页

• 分页查询时，若count为0应直接返回

• 优化超多分页场景：利用延迟关联或者子查询优化超多分页场景。深翻页：若知道当前页的id，翻到下一页，则利用where id > 10000进行范围限定

• join小结果集在左侧

(5)避坑指南

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

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

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

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

• SQL语句中表的别名前加as，并且以t1、t2、...的顺序依次命名

• in后的集合元素数量，控制在1000个之内

(6)SQL性能优化的目标

• explain的type至少要达到range级别

(7)覆盖索引

覆盖索引

(8)全索引扫描，磁盘扫描

全索引扫描

(9)全索引扫描，磁盘扫描

filesort

创建 idx_bd(b,d)

image.png

四、ORM映射规约

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

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

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

• 不要使用${}

• 不要使用MyBatis 自带的 queryForList方法

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

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

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

PS：jpa是规范，mybatis实现数据库到类的映射，没实现

五、数据库设计实战

(1)三大范式

• 第一范式：每列属性不可拆分

• 第二范式：表中的每列都和主键相关

• 第三范式：每列都和主键列直接相关，而不是间接相关