表结构及索引设计

Dear 丶 2023-09-29 12:56 22阅读 0赞

良好的表结构设计是高性能的基础，要根据系统将要执行的业务来设计，需要权衡多种因素。但在数据库设计上，有个非常重要的设计准则，称为**范式设计**。

这篇文章将介绍数据库表设计中的范式和反范式设计以及索引的深度辨析和索引创建的策略。

### 一、范式设计 ###

在关系型数据库中，想要设计好表之间的关系，就需要使关系满足一定的约束条件，而这些约束条件就是数据库设计中的范式，数据库设计的范式分为多个等级，每一个等级都是在上一个等级上的延申。  
目前关系型数据库分为六种范式：**第一范式(1NF)、第二范式(2NF)、第三范式(3NF)、巴斯-科德范式(BCNF)、第四范式(4NF)和第五范式(5NF)**。  
一般来说，数据库只需要满足第三范式即可。

#### 1.1 第一范式 ####

**定义：属于第一范式关系的所有属性都不可再分，即数据项不可分**  
第一范式强调数据表中，每一个属性的原子性，即每个属性都是不可再分  
比如用户表，有用户名、年龄这些不可再分的属性，但是如果把用户名和年龄作为一个整体作为一个属性就违反了第一范式  
**注：第一范式基本上是所有关系型数据库最基本的要求，日常使用中都会遵守第一范式**

#### 1.2 第二范式 ####

第二范式是在第一范式上的延申，第二范式要求数据库中的每个实例或行可以被唯一的区分。

简单理解就是表中需要有一个列，作为实例的唯一标识(也就是避免联合主键)，并且实体的属性完全依赖于主键。  
以订单表和商品表为例，订单表单独存放订单的独立信息(创建时间、支付状态等)，而商品表只存放商品信息，然后通过一个中间表来对订单和商品进行关联，这样在关联的时候，只需要订单和商品表的主键作为中间表的附属属性即可。  
**简单来说就是，每个表存储各自独立信息，表之间的关联关系通过中间表来记录。**

#### 1.3 第三范式 ####

第三范式是在第二范式上的延申，第三范式要求表中的每一个非主属性，既不部分依赖也不传递依赖于业务主键。  
简单来说就是，一个表A中已经有了某个表B的主键了，就不能再有该表B的其他属性了。

第三范式的目的就是为了防止数据冗余，第二个目是为了让修改操作变得简单，不然如果要修改表B的属性值，还需要修改表A的属性值。

### 二、反范式设计 ###

完全符合范式化的设计并不是完美的，以第三范式为例，如果要满足第三范式，那么查询中势必就会出现大量的关联查询。

而在关联的表很多时，关联查询的效率就会很低。

所谓的反范式设计就是为了能提高数据的读取效率，进而违反了数据库设计的第三范式。允许存在少量冗余，换句话说就是反范式设计是使用空间换时间。

范式化和反范式化各有利弊，在实际工作中任何选择呢？  
前面介绍反范式时已经说过了，反范式设计更多的是对第三范式的违反，而对于第一范式和第二范式，我们在实际生产中是一定要尽量遵守的

范式和反范式的选择，主要以数据查询频率、修改频率以及表关联频率为参考：

比如对表A查询时，通常也需要查询表B中某个字段，而该字段的修改频率也不高，这是就可以按照反范式进行设计。如果需要查询B中好几个字段，而这些字段中有些又是经常更改的，这个时候就尽量遵守范式化设计，使用关联查询实现。

反范式设计的应用主要包含如下几个方面：

#### 2.1 缓存和汇总 ####

数据冗余常见的例子就是订单表和商品中间表，可以直接在中间表中就记录商品名称和单价这些信息，就不需要再去关联商品表进行查询了。

缓存衍生值也是很有用的，比如需要统计每个用户登陆了几次网站，可以每次查询的时候，就在登陆记录表里面进行count操作，但这种操作随着表记录增加，性能也就降低了；而通过缓存衍生，可以在用户表里面记录用户登录网站的次数，每次登录直接累加就可以了。

有时候缓存表或汇总表都是性能优化很好的手段，缓存表可以存放那些每次查询都比较慢的表数据，而汇总表，保存的是通过`GROUP BY`预聚合后的数据，都可以加快查询效率。

缓存表或汇总表的数据可以选择实时维护或定期重建，根据业务需要具体选择

#### 2.2 计数器表 ####

计数器表在Web应用中很常见。比如网站点击数、用户的朋友数、文件下载次数等。对于高并发下的处理，首先可以创建一张独立的表存储计数器，这样可使计数器表小且快，并且可以使用一些更高级的技巧。

如果计数器表，只有一条记录，那么在高并发的情况下，同一时刻只有一个事务可以修改，这样就变成了串行化的操作，性能很低。

可以借鉴JDK8中`LongAdder`的设计思路，在计数器表中存储多行，每一行对应一个槽位，每个事务随机选择一个槽位进行累加，这种方式称作`写热点分散`。

#### 2.3 分库分表中的查询 ####

在电商系统中，基本都采用分库分表设计，对于一笔交易，买家和卖家都需要查询，而买家和卖家的数据又是分库存储的，这样对于同一份订单信息，存放在哪一方都不合适，这样时候通常的做法就是，将订单信息分别在买家和卖家两边各存储一份。

**范式化设计的优缺点：**

1、范式化的更新操作通常比反范式化要快。

2、范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快。

3、很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句。在非范式化的结构中必须使用DISTINCT或者GROUPBY才能获得一份唯一的列表，但是如果是一张单独的表，很可能则只需要简单的查询这张表就行了。

**缺点：范式化设计的缺点是通常需要关联。稍微复杂一些的查询语句在符合范式的表上都可能需要至少一次关联，也许更多。这不但代价昂贵，也可能使一些索引策略无效。例如，范式化可能将列存放在不同的表中，而这些列如果在一个表中本可以属于同一个索引**

**反范式化设计的优缺点：**

1、反范式设计可以减少表的关联

2、可以更好的进行索引优化

**缺点：存在数据冗余及数据维护异常，对数据的修改需要更多的成本**

### 三、索引辨析 ###

索引辨析主要介绍一些MySQL内部的索引优化策略和索引实现方式。

#### 3.1 回表和MRR ####

回表通常用在辅助索引(二级索引)上，辅助索引的叶子只存放辅助索引字段数据和主键ID，如果查询的字段不再辅助索引的叶子节点上，则需要根据主键ID，再从主键索引(聚簇索引)中查询数据，这个过程就称为回表。  
如果每次从二级索引中读取到一条记录后，就会根据该记录的主键值执行回表操作。而在某个扫描区间中的二级索引记录的主键值是无序的，也就是说这些二级索引记录对应的聚簇索引记录所在的页面的页号是无序的，无序就意味着每次回表都是随机IO操作，效率很低。  
MySQL中提出了一个名为`Disk-Sweep Multi-Range Read (MRR，多范围读取)`的优化措施，即先读取一部分二级索引记录，将它们的主键值排好序之后再统一执行回表操作。

相对于每读取一条二级索引记录就立即执行回表操作，这样会节省一些IO开销。使用这个 MRR优化措施的条件比较苛刻，所以通常直接认为每读取一条二级索引记录就立即执行回表操作。MRR的详细信息，可以查询官方文档。

#### 3.2 自适应哈希索引 ####

自适应性哈希索引是`Innodb`存储引擎的三大特性之一，可以看作是对热点数据的缓存。  
我们知道B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度一般为34层,故需要34次的IO查询。  
所以在InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查询三四次节点的效率高了不少。  
InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式  
**注：自适应性哈希索引是数据库内部的机制，我们并不能进行干预**  
可以通过下面的命令查询自适应性哈希索引的使用状况：

show engine innodb status;

*注意：哈希索引只能用来搜索等值的查询,如 SELECT FROM table WHERE index co=xxx。而对于其他查找类型,如范围查找,是不能使用哈希索引的*\*  
可以通过`innodb_adaptive_hash_index`来考虑是禁用或启动此特性

#### 3.3 全文索引及倒排索引 ####

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引。从InnoDB 1.2.x版本开始，InnoDB存储引擎开始支持全文检索，对应的MySQL版本是5.6.x系列。  
注意，不管什么引擎，只有字段的数据类型为 `char、varchar、text`及其系列才可以建全文索引。  
全文索引和常用的模糊匹配使用 `like + %`不同，全文索引有自己的语法格式，使用`match` 和 `against` 关键字，比如：

select * from fulltext_test where match(content,tag) against('xxx xxx');

**倒排索引是全文索引实现的基石，将文档中包含的关键字全部提取处理，然后再将关键字和文档之间的对应关系保存起来，最后再对关键字本身做索引排序。用户在检索某一个关键字是，先对关键字的索引进行查找，再通过关键字与文档的对应关系找到所在文档。**

#### 3.4 MySQL索引类型 ####

1、从数据结构角度可分为B+树索引、哈希索引、以及FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）和R-Tree索引（用于对GIS数据类型创建SPATIAL索引）

2、从物理存储角度可分为聚集索引（clustered index）、非聚集索引（non-clustered index）

3、从逻辑角度可分为主键索引、普通索引或者单列索引、多列索引、唯一索引、非唯一索引等等

密集索引和稀疏索引，就是聚簇索引和非聚簇索引

覆盖索引：只通过辅助索引(二级索引)就可以获得查询的数据，不需要再回表(索引优化也是尽量使用覆盖索引)

#### 3.5 索引代价 ####

##### 3.5.1 索引在查询中的作用 #####

1、一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度。

2、一个select查询语句在执行过程中**一般**最多能使用一个二级索引来加快查询，即使在where条件中用了多个二级索引。

**注：这里说的是一般只会使用一个二级索引，有特殊的情况，后面有文章会介绍索引合并，就可以同时使用多个索引。**

##### 3.5.2 索引的代价 #####

**1、空间代价**

这个是显而易见的，每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成会占据很多的存储空间

**2、时间代价**

每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。而且B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是非叶子内节点中的记录都是按照索引列的值从小到大的顺序而形成了一个单向链表。

而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录的排序。如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，这必然会对性能造成影响。

**注：按照经验，一般来说，一张表6-7个索引以下都能够取得比较好的性能权衡。**

### 四、索引创建策略 ###

在前面的文章[《索引优化(二)》][Link 1]中已经介绍了一些索引设计的原则，但那也只是索引创建策略的一部分，这里将介绍一些其他的索引创建策略，以及前缀索引如何选择长度。

#### 4.1 索引的离散型 ####

索引的选择性/离散性是指，不重复的索引值（也称为基数，cardinality)和数据表的记录总数（N)的比值，范围从1/N到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。  
很差的索引选择性就是列中的数据重复度很高，比如性别字段，不考虑政治正确的情况下，只有两者可能，男或女。那么我们在查询时，即使使用这个索引，从概率的角度来说，依然可能查出一半的数据出来。  
**索引的离散型是一个非常重要重要的指标**  
索引离散性的计算方式:

select  COUNT(DISTINCT 字段名)/count(*) from table_name;

#### 4.2 前缀索引 ####

有时索引字段是一个很长的字符串，这会让索引树变得很大并且很慢，这个时候就可以通过截取字符串的部分前缀来作为索引，这就是前缀索引。  
根据《阿里巴巴编程规范》中索引规约的说法，字符串建立索引时，必须指定索引长度，推荐的长度是20，但20对于实际业务来说可能也是比较大的，我们自己根据索引的离散型(区分度)来确定，公式如下：

SELECT COUNT(DISTINCT LEFT(索引字段,前缀长度))/COUNT(*)

随着前缀长度的增加，离散型的差别变化不大时，就可以选择当前长度作为前缀索引的长度了。比如下面的取前15个字符和取前16个字符的离散型已经差别不大了，那么就可以选择15作为前缀索引的前缀长度。  
![在这里插入图片描述][9719aaca2aed44c7ab014a21230247a1.png_pic_center]

#### 4.3 三星索引 ####

三星索引概念是在\*\*《Rrelational Database Index Design and the optimizers》\*\* 一书中提出来的。  
对于一个查询而言，一个三星索引，可能是其最好的索引。  
三星索引包含三个维度：

1、索引将相关的记录放在一起则获得一星

简单来说就是通过索引，可以将查询的缩小到某个范围就算是一星索引

2、如果索引中的数据顺序和查找中的排列顺序一致则获得第二星

当查询需要排序，group by、order by，如果查询所需的排序与索引的排序是一致的，就不需要再进行额外排序了(排序走索引)

3、如果索引中的列包含了查询中需要的全部列则获得第三星。

如果索引中所包含了这个查询所需的所有列（包括 where 子句 和 select 子句中所需的列，也就是覆盖索引），这样一来，查询就不再需要回表了，减少了查询的步骤和IO请求次数，性能几乎可以提升一倍。

**注：这三颗星中第三星是最重要的，因为将一个列排除在索引之外可能会导致很多磁盘随机读（回表操作）。第一和第二颗星重要性差不多，可以理解为第三颗星比重是50%，第一颗星为27%，第二颗星为23%，所以在大部分的情况下，会先考虑第一颗星，但会根据业务情况调整这两颗星的优先度。**

#### 4.4 主键尽量少改 ####

主键是按照聚集索引物理排序的，如果主键频繁改变(update)，物理顺序会改变，MySQL要不断调整B+树，并且中间可能会产生页面的分裂和合并等等，会导致性能会急剧降低。

#### 4.5 去除冗余重复索引 ####

MySQL允许在相同列上创建多个索引，无论是有意的还是无意的。MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

对于不经常使用或不使用的索引，要及时删除

[Link 1]: https://blog.csdn.net/sermonlizhi/article/details/124509410
[9719aaca2aed44c7ab014a21230247a1.png_pic_center]: https://img-blog.csdnimg.cn/9719aaca2aed44c7ab014a21230247a1.png#pic_center