Mysql高性能表结构及索引设计

一、范式化设计(NF-Normal Form)

1、范式种类

       第一范式、第二范式、第三范式、巴斯-科德范式、第四范式、第五范式(完美范式)

        逐级包含,一级比一级严格,正常开发中满足前三个范式即可

(1)第一范式(关系型数据库,一定是满足第一范式的)

a. 每一列属性都是不可再分的属性值.确保每一列的原子性.例如:

        (a)age 字段仅存储年龄.满足

        (b)name-age 字段,存储了姓名与年龄.不满足.将两者拆开(name,age)即满足

b.两列的属性相近或相似或一样.尽量合并属性一样的列,确保不产生冗余数据

c.单一属性的列为基本数据类型构成

d.设计出来的表都是简单的二维表

(2)第二范式

尽量保证一个表数据有一个字段作为唯一标识即可,尽量避免联合主键,尽量拆分

(3)第三范式

尽量保证一个表结构设计里面不要存在冗余字段.会增加数据维护成本

二、反范式化设计

1、为了提升性能和读取效率而适当的违反对数据库设计范式的要求

2、为了查询的性能,允许存在部分(少量)冗余数据.换句话说,反范式化设计就是使用空间换时间.

三、InnoDB中的索引

1、聚集索引/聚簇索引

将表的主键用来构造一棵B+树.并且将整张表的行记录数据存放在该B+树的叶子节点中.会存放所有的数据

生成聚簇索引的方式:

a.定义的唯一性主键

b.若无唯一性主键,则使用定义的唯一索引

c.若上述皆无,会采用rowId进行生成

2、辅助索引/二级索引

当使用辅助索引查询的时候,会访问两次B+树.因为辅助索引会独立存储在一棵B+树中,且仅存储有索引值与聚集索引的值.然后通过聚集索引回表查询聚集索引的B+树查询具体完整数据.因此会进行两次IO操作,访问两次B+树

回表

回表指的是查询操作需要根据索引查找到行的主键值，然后再通过主键值查找数据行。

回表是一种非常耗费性能的操作，特别是当查询的结果集包含较多的列时。因为回表需要进行额外的I/O操作，所以会增加查询的延迟时间和数据库的负载。

MRR

MRR（Multi Range Read）是一种针对范围查询优化的技术，它可以将多个范围区间的数据行，按照相邻的磁盘块一次性读取到缓存中，减少了磁盘I/O和锁竞争的次数，提高了查询效率。

MRR主要应用于InnoDB存储引擎的二级索引（非主键索引），对于覆盖索引和聚簇索引，MRR并没有什么优化效果。

MRR的实现原理是通过将查询涉及到的多个范围区间拆分成多个子区间（通常为16个），然后按照这些子区间的物理存储位置，将数据行按照相同的存储块读取到缓存中，避免了跨块读取和随机读取。这种优化技术可以大量减少磁盘I/O，从而缩短查询时间，提高数据库的性能。

要开启InnoDB MRR，需要满足以下两个条件：

1. 使用InnoDB存储引擎。

2. 在查询中使用范围查找（Range Scan），并针对一个二级索引进行查找。

当以上条件都满足时，MySQL优化器会自动使用MRR。在开启MRR之后，可以通过查询执行计划（EXPLAIN）来验证是否使用了MRR，也可以通过查询状态（SHOW STATUS）来查看MRR相关的统计信息和效果。