高性能业务表结构设计和索引知识深化

超、凢脫俗 2024-02-25 02:07 36阅读 0赞

#### 文章目录 ####

*  一. 范式化设计
 *   *  1. 简介
     *  2. 第一范式（1NF）
     *  3. 第二范式（2NF）
     *  4. 第三范式
     *  五. 反范式化设计
 *  二. InnoDB索引优化
 *   *  1. MRR（多范围读取）
     *  2. 联合索引B+树使用情况
     *  3. 自适应hash索引
     *  4. 索引的代价
     *  5. 高性能的索引创建策略

## 一. 范式化设计 ##

### 1. 简介 ###

范式来自英文Normal Form，简称NF。要想设计一个好的关系，必须使关系满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求得严格。满足这些规范的数据库时简洁的，结构清晰的。  
![在这里插入图片描述][643ddf5470a34e6ea09b62b08210fdd2.png]

*  第一范式（1NF）
 *  第二范式（2NF）
 *  第三范式（3NF）
 *  巴斯-科德范式（BCNF）
 *  第四范式（4NF）
 *  第五范式（5NF，又称完美范式）

（重点是前面的3个范式）

### 2. 第一范式（1NF） ###

*  是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性

> 在任何一个关系数据库中，第一范式（1NF）  
> 是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之，`第一范式就是无重复的列`。

### 3. 第二范式（2NF） ###

*  第二范式是在第一范式的基础上建立起来的
 *  第二范式每个表必须有主关键字(Primary key),其他数据元素与主关键字一一对应

> 是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性。如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言之，`第二范式就是非主属性完全依赖于主关键字`。

### 4. 第三范式 ###

*  第三范式就是指表中的所有数据元素不但要能唯一地被主关键字所标识,而且它们之间还必须相互独立,不存在其他的函数关系。也就是说，对于一个满足2nd NF 的数据结构来说，表中有可能存在某些数据元素依赖于其他非关键字数据元素的现象,必须消除。（传递依赖）

> 满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept\_id）、部门名称、部门简介等信息。那么在图3-2的员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，`第三范式就是属性不依赖于其它非主属性`。

### 五. 反范式化设计 ###

所谓反范式化设计，就是针对范式化设计而言的，为了性能和读取效率而适当的违反对数据库设计范式的要求。为了查询的性能允许存在部分（少量）冗余设局。换句话来说，`反范式化设计就是使用空间换时间`。

**实际工作中反范式化总结**

*  性能提升-冗余、缓存和汇总
 *  性能提升-计数器表
 *  反范式设计-分库分表中的查询

## 二. InnoDB索引优化 ##

### 1. MRR（多范围读取） ###

我们知道当我们查询数据按照飞聚集索引查询时，会将每次查询到的数据进行一次回表操作（因为非聚集索引的B+树只存储了相应的索引字段以及对应的字段ID），注意其存储的主键ID就是我们回表查询数据的依据，而这个ID存储在非聚集索引树中是无序的，可能查询就需要多次随机IO在磁盘上查找该数据所在的页面。MRR操作就是将要查询的多个数据的ID在内存中排序，然后进行顺序IO在磁盘中查找数据，这样回表效率就大大提高了。

### 2. 联合索引B+树使用情况 ###

如下图这种情况，索引情况是index(a,b,c,d)

![在这里插入图片描述][2796782de9504c69afa2a3b252aeec65.png]

> 首先对a进行排序，然后对b排序，接着对c有序，最后对b排序，其中a只绝对有序，b，c和d都是局部有序（在前一个索引相同的情况下有序）

### 3. 自适应hash索引 ###

在读取索引的B+索引树结构时，当我们需要定位到一条数据时，每次都需要首先读取根节点需要一次IO，读取中间的非叶子结点需要一次或多次IO，最后读取叶子结点还需要一次IO，整个读取过程需要多次IO。于是Mysql会监控用户查询对索引的使用情况，对多次访问的数据定义为热数据，然后对该数据进行hash所以，称为自适应hash索引，下次要查询这条数据时只需要通过该hash所以快速定位到该条数据。（自适应hash索引使用的数组+链表的方式维护，是Mysql自动维护的）

> `innodb三大特性？`  
> 答：自适应hash索引、双写缓存以及bufferpool  
> `什么是密集索引和稀疏索引？`  
> 答：密集索引和稀疏索引的区别是在于是否存储了整行数据，如果存储了整行数据就是密集索引而只是存储了该行数据的一部分就是稀疏索引。所以聚集索引就是密集索引而非聚集索引就是稀疏索引。

### 4. 索引的代价 ###

*  空间上的代价

> 一个索引就是一个B+树，所以每创建一个新的索引就需要更多的空间来存储该B+树

*  时间上的代价

> 索引是为了加快数据的查询速度，这对读是很有利的，当对于增、删、改时，对B+树的维护（如分裂等操作）都需要付出时间的代价

### 5. 高性能的索引创建策略 ###

*  索引列的类型尽量小

> 1.  数据类型越小，在查询时进行的比较操作越快
> 2.  数据类型越小，索引占用的存储空间就越少，在一个数据页就可以放下更多的记录，从而减少磁盘IO带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率

*  创建索引时利用好索引的选择性/离散性和前缀索引

> `不重复的值/总值`这个值越高越好，查询效率就越高

*  只为用于搜索、排序或分组的列创建索引

> 只为出现在Where子句中的列、连接子句中的连接列创建锁索，而出现在查询列表中的列一般就没必要创建索引了，除非是需要使用覆盖索引；又或者出现在Order By或Group By子句中的列创建索引。

*  多列索引（联合索引）

> 怎么选择合适的索引列顺序？
> 
> 1.  经验法则：讲选择性高的列放在索引最前列
> 2.  需要根据那些运行频率最高的查询来调整索引列的顺序
> 3.  在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求

*  设计三星索引

三星索引其实是衡量一个索引是否达到最佳表现的三个维度：  
**第一星**：如果与一个查询相关的索引行是相邻的，或者至少相距足够靠近的话，那这个索引就可以标记上一颗星（搜索范围在B+树上尽可能小）  
**第二星**：如果索引中的数据顺序和查找中的排列顺序一致则获得二星（排序星）  
**第三星**：如果索引中的列包含了查询中需要的全部列则获得三星（宽索引星）—可以理解为覆盖索引

*  主键是很好改变的列

> 行是按照聚集索引物理排序的，如果主键频繁改变，物理顺序会改变，性能会急剧降低

*  不要使用冗余和重复索引

[643ddf5470a34e6ea09b62b08210fdd2.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/25/128ce993694c4a0383bc7827034fe5c1.png
[2796782de9504c69afa2a3b252aeec65.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/25/ad20b003fee24001adc477a4b3f339e0.png