3. MySQL 表设计

1. 三范式

要想设计一个好的关系，必须使关系满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求得严格。满足这些规范的数据库是简洁的、结构明晰的。
一般满足三范式就可以了。

1.1 第一范式

1. 每一列属性都是不可再分的属性值，确保每一列的原子性；
2. 两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据；
3. 单一属性的列为基本数据类型构成；
4. 设计出来的表都是简单的二维表。

满足第一范式。

1.2 第二范式

第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。

要求表中只具有一个业务主键，也就是说符合第二范式的表不能存在非主键列只对部分主键的依赖关系。

左侧表的主键是 订单 ID 与产品 ID 的联合主键。

1.3 第三范式

第三范式（3NF）要求一个数据库表中不包含已在其它表中包含的非主关键字信息，即数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。

以下不满足第三范式：

完全符合范式化的设计有时并不能得到良好得SQL查询性能。

1.4 反范式设计

所谓的反范式化设计，就是针对范式化设计而言的：

1. 为了性能和读取效率而适当的违反对数据库设计范式的要求；
2. 为了查询的性能，允许存在部分冗余数据。换句话说，反范式化设计就是使用空间换时间。

反范式设计目的：减少关联的表数量、更好地进行索引优化。

反范式设计可用的手段：

1. 冗余
2. 缓存：经常使用的数据放到 Redis 中
3. 汇总：经常 count、group by 的数据放到另外一张表中，定时器定时更新
4. 计数器表

2. 字段优化

选择字段的基本原则：

1. 在业务允许的范围内，更小的更好。
2. 简单更好。字符串与整数都可以用时，优先选择整数。
3. 尽量避免 NULL。

具体字段：

1. 整数
   TINYINT，SMALLINT，MEDIUMINT，INT，BIGINT，分别使用 8，16，24，32，64 位存储空间，也就是 1、2、3、4、8 个字
   节。同时整数类型有可选的 UNSIGNED 属性，表示不允许负值，这大致可以使正数的上限提高一倍。INT(11) 是没有意义的，只是为了显示。
2. 实数
   float（4 个字节）、double（8 个字节） 是不精确的；decimal 是精确的。
3. 字符串
   包括 VARCHAR（变长） 和 CHAR（定长） 类型、BLOB（二进制存储） 和 TEXT（字符方式存储） 类型、ENUM（枚举）和 SET 类型。
4. 日期
   

3. 索引

InnoDB 支持 B+ 树索引、全文索引、哈希索引。
HashMap 不适合做索引：不适合排序；hash 适合匹配，不能实现范围查找。

3.1 B+ 树

与 B 树的区别：

1. B 树中间节点也放数据，B+ 树只有在叶子节点才放数据。
2. B+ 树叶子节点还有相邻指针。

B+ 树中一个节点放一页的数据，一页一般是 16K。在数据库中一个索引一棵 B+ 树。
InnoDB 一棵 B+树可以存放多少行数据？
假设一行数据的大小是 1k，那么一个页可以存放 16 行这样的数据。
假设主键 ID 为 常用的 bigint 类型，长度为 8 字节，而指针大小在 InnoDB 源码中设置为 6 字节，这样一共 14 字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即 16384/14=1170 个。
那么可以算出一棵高度为 2 的 B+树，存在一个根节点和若干个叶子节点能存放 1170*16=18720 条这样的数据记录。

3.2 InnoDB 索引类型

1. 聚集索引/聚簇索引：将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。如果没有创建主键，InnoDB 使用 rowId 生成聚集索引。
   

2. 辅助索引/二级索引：我们自己建立的索引
   
   回表：当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为回表。也就是根据辅助索引的值查询一条完整的用户记录需要使用到 2 棵 B+树——一次辅助索引，一次聚集索引。
   MRR：多范围读取，优化回表时每次回表一条记录，我们无法控制，并且条件比较苛刻。
   

3. 联合索引：将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引。联合索引只有一棵 B+ 树。
   

4. 覆盖索引：从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

5. 哈希索引：InnoDB 存储引擎内部监控索引热数据，然后内部创建一个hash 索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，我们无法控制。

6. 倒排索引：用在全文检索时。
   

3.3 扫描区间

一个 select 查询语句在执行过程中，一般最多能使用一个二级索引。
SELECT * FROM order_exp WHERE id in(3,9) OR (id>=23 AND id<= 99); id 是聚集索引。
有 3 个扫描区间。 SELECT * FROM order_exp WHERE expire_time> ‘2021-03-22 18:35:09’ AND order_note = ‘abc’; 可以使用索引。

SELECT * FROM order_exp WHERE expire_time> ‘2021-03-22 18:35:09’ OR order_note = ‘abc’;无法使用索引。

3.4 高性能索引创建策略

增删改数据都要相应修改索引，因此有代价。
高性能的索引创建策略：

1. 索引列的类型尽量小
   数据类型越小，在查询时进行的比较操作越快；数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以放下更多的记录，从而减少磁盘 I/O 带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率。
2. 选择离散性高的列作为索引
3. 前缀索引。有时候需要索引很长的字符列，通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。ALTER TABLE order_exp ADD KEY (order_note(14)); 前缀索引的建立方式。
4. 只为用于搜索、排序或分组的列创建索引。只为出现在 WHERE 子句中的列、连接子句中的连接列创建索引，而出现在查询列表中的列一般就没必要建立索引了，除非是需要使用覆盖索引；又或者为出现在 ORDER BY 或 GROUP BY 子句中的列创建索引。
5. 多列索引选择合适的索引顺序。将选择性最高的列放到索引最前列；根据那些运行频率最高的查询来调整索引列的顺序；在优化性能时，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。
6. 优先保证 3 星，再选择 1 星或 2 星中的一个，很难保证完全满足 1/2/3 星。
7. 去除冗余、重复的索引。
8. 主键是很少改变的列。行是按照聚集索引物理排序的，如果主键频繁改变，物理顺序会改变，性能会急剧降低。

三星索引：
索引将相关的记录放到一起则获得一星；（索引扫描范围越小越好）；
如果索引中的数据顺序和查找中的排列顺序一致则获得二星（排序星）；
如果索引中的列包含了查询中需要的全部列则获得三星（宽索引星）；

MySQL 在索引中认为所有 NULL 是一个值，放在索引的前面，如果 NULL 值太多，MySQL 认为离散度低，倾向于走全表扫描。

NULL：

1. NULL 是独一无二的，select null == null，结果为 null。
2. NULL 是相等的，在索引中。
3. 在处理过程中忽略 NULL。count(col) 忽略 NULL

4. 数据库表物理设计

1. 定义数据库、表及字段的命名规范
2. 选择合适的存储引擎
3. 为表中的字段选择合适的数据类型
4. 建立数据库结构

当一个列可以选择多种数据类型时：

1. 优先考虑数字类型
2. 其次是日期、时间类型
3. 最后是字符类型
4. 对于相同级别的数据类型，应该优先选择占用空间小的数据类型