Day2-T3MySQL设计笔记

Day2-T3MySQL设计笔记——2021-10-28

day2：2021-10-28

一、建表规约

建表强制规约

表、字段命名

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

数据类型

• 小鼠类型为decimal
• 货币数据使用最小货币单位，数据类型为bigint
• 字符串长度几乎相等使用char
• varchar长度不要超过5000

好处 ：

在使用 char和 varchar，使用char比 varchar 节约两个字符存储

简历索引是不用纠结 varchar定的长度

char是定长的 ，不够是会在后面补零，读取出来后面就会有空格

表必备三字段

• id（主键）

• create_time（创建时间）

• update_time（更新时间）

建表推荐规约

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

注：尽量不要分库分表，因为复杂度会指数型提高

二、索引规约

索引的特性

• 持久性

  • 占据存储空间，永久保留

• 有序性

  • 提高查询的性能和效率

索引的分类

• 存储形式

  • 聚簇索引（主键）
  • 非聚簇索引

• 数据约束

  • 主键索引
  • 唯一索引
  • 非唯一索引

• 索引列的数量

  • 单列索引
  • 组合索引

• innoDB可以创建的索引

  • 主键索引
  • 唯一索引
  • 普通索引

覆盖索引：是不可创建的索引，在查询中产生的一种索引，当查询语句中的返回的字段只有主键和查询条件中的使用索引字段，则该查询会使用一种覆盖索引。

索引的数据结构：二叉查找数

1、定义

二叉排序树或者是一棵空树，或者是具有下列性质的二叉树：

• 若左子树不空，则左子树上所有结点的值均小于它的根结点的值；
• 若右子树不空，则右子树上所有结点的值均大于它的根结点的值；
• 左、右子树也分别为二叉排序树；
• 没有键值相等的节点。

2、查找

• 任何一个数据的查找过程都需要从根结点出发，沿某一个路径朝叶子结点前进。因此查找中数据比较次数与树的形态密切相关。
• 当树中每个结点左右子树高度大致相同时，树高为logN。则平均查找长度与logN成正比，查找的平均时间复杂度在O(logN)数量级上。
• 当先后插入的关键字有序时，BST退化成单支树结构。此时树高n。平均查找长度为(n+1)/2，查找的平均时间复杂度在O(N)数量级上。

3、插入与删除

• 新结点插入到树的叶子上，完全不需要改变树中原有结点的组织结构。插入一个结点的代价与查找一个不存在的数据的代价完全相同
• 当删除一个结点P，首先需要定位到这个结点P，这个过程需要一个查找的代价。然后稍微改变一下树的形态。如果被删除结点的左、右子树只有一个存在，则改变形态的代价仅为O(1)。如果被删除结点的左、右子树均存在，只需要将当P的左孩子的右孩子的右孩子的…的右叶子结点与P互换，在改变一些左右子树即可。因此删除操作的时间复杂度最大不会超过O(logN)。

4、时间复杂度

查找最好时间复杂度O(logN)，最坏时间复杂度O(N)。

索引的数据结构：btree

B+树索引是B+树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引。B+树中的B代表平衡（balance），而不是二叉（binary），因为B+树是从最早的平衡二叉树演化而来的。在讲B+树之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree），B+树即由这些树逐步优化而来。

B树的特点：
（1）所有键值分布在整个树中
（2）任何关键字出现且只出现在一个节点中
（3）搜索有可能在非叶子节点结束
（4）在关键字全集内做一次查找，性能逼近二分查找算法

索引的数据结构：b+tree

B+树与B树的不同在于：

• 所有关键字存储在叶子节点，非叶子节点不存储真正的data；
• 为所有叶子节点增加了一个链指针；
• Btree 索引是一种数据结构。每个节点一般包括 键值、数据库记录除键值外的数据，指针（指向子节点的指针）；
• B+tree 索引 的每个非叶子节点一般包括键值 ，指针，只有叶子节点才包括数据。

索引名称规约

索引命名

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

创建索引规约

• 有唯一特征的字段必须建成唯一索引
• 在varchar字段上建立索引时，必须指定索引长度
• 建组合索引的时候，区分度最高的在最左边

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

• 索引宁缺勿滥

  • 认为一个查询就需要建立一个索引

• 吝啬索引创建

  • 认为索引会消耗空间、严重拖慢记录的更新以及行的新增速度

• 抵制唯一索引

• 认为唯一索引一律需要在应用层通过“先查后插”方式解决

三、SQL与ORM映射

SQL规约

SQl规约——索引

• 注意字段类型

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

• 利用覆盖索引

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

• 利用有序表

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

• 禁模糊

  • 页面搜索严禁左模糊或者全模糊，如果需要请使用搜索引擎进行解决

拓展——超过三个表禁止join

总结： **这个规则 超过三张表禁止join ，由于数据量太大的时候，mysql根本查询不出来，导致阿里出了这样一个规定。**而公司支付系统朝着大规模高并发目标设计的，所以，遵循这个规定。

在业务层面来讲，写简单sql，把更多逻辑放到应用层，在应用层实现特定的join也容易得多。

SQl规约——count

• 拒绝代替

  • 不要使用count（列名）或count（常量）来代替count(*)

• 计算不重复行数

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

• 当值全是NULL时

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

SQl规约——分页

• 若count为0

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

• 优化超多分页场景

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

SQL规约——null值

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

• NULL<>NULL

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

• NULL=NULL

  • NULL=NULL返回的结果是NULL，而不是true

• NULL<>1

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

ORM映射规约

• 在表查询中，一律不要使用*作为查询的字段列表
• POJO类的布尔属性不能加is，而数据库字段必须加“is…”
• 查询返回结果都需要使用ResultMap映射
• 不要使用${}
• 不要使用MyBatis自带的queryForList方法
• 不允许直接使用HashMap与Hashtable接受结果集
• 更新数据表记录时，必须同时更新update_time
• 不要写一个大而全的数据更新接口

四、数据库设计

数据库设计三大范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。满足最低要求的范式是第一范式（1NF）。在第一范式的基础上进一步满足更多规范要求的称为第二范式（2NF），其余范式以次类推。一般说来，数据库只需满足第三范式(3NF）就行了。

数据库的实体属性和关系

• 实体：表；

• 属性：表中的数据（字段）；

• 关系：表与表之间的关系；

• 第一范式：每列属性不可拆分，确保每列保持原子性

  • 当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

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

  • 如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。

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

  • 设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.