表结构及索引设计

Laughing_Xie

已于 2022-08-15 21:41:00 修改

阅读量479

点赞数 1

分类专栏： java小细节文章标签： java 数据库前端

于 2022-08-11 23:58:34 首次发布

本文链接：https://blog.csdn.net/qq_56754651/article/details/126294912

版权

java小细节专栏收录该内容

11 篇文章 0 订阅

订阅专栏

数据库表设计

设计准则：范式设计（Normal Form）MySQL是关系型数据库，但是要想设计—个好的关系，必须使关系满足一定的约束条件，此约束已经形成了规范，分成几个等级，一级比一级要求得严格。除了前面的三范式，后面的巴斯-科德范式，第四第五范式一般在实验室中存在。

第一范式[1NF]：属于第一范式关系的所有属性都不可再分，即数据项不可分。（强调数据表的原子性，是其他范式的基础）

第二范式[2NF]：要求数据库表中的每个实例或行必须可以被惟一地区分。通常在实现来说，需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。

第三范式[3NF]：指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主键对主键的传递依赖。

即数据不能存在传递关系，即每个属性都跟主键有直接关系而不是间接关系。

反范式设计

范式设计，在实际的业务查询中会大量存在表的关联查询，而大量关联很多时候会非常影响查询的性能。反范式化就是为了性能和读取效率的考虑而适当的对数据库设计范式要求进行违反，允许存在少量的冗余，反范式化就是使用空间来换取时间。

范式设计优缺点

反范式化设计优缺点

这是写热点的分散

InnoDB存储引擎的三大特性：

1.Buffer Pool

2.自适应哈希索引（MySQL内部自行维护）

3.双写缓冲区

同时，InnoDB也支持全文检索

字段数据类型优化

InnoDB使用了聚集索引，将表的主键用来构造一棵B+Tree,并且将整张表的行记录数据存放在该B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。叶子节点就是数据页，数据页上存放的是完整的每行记录，对于主键排序查找和范围查找速度非常快。如果我们没有定义主键，MySQL会使用唯一性索引，没有唯一性索引，MySQL也会创建一个银行列RowID来做主键，然后用这个主键来建立聚集索引。

辅助索引/二级索引

回表

可利用覆盖索引的方式来优化，避免回表，或者缩小查询范围或者尽量定位清晰，减少回表次数。回表操作基本上都是随机IO操作，相对于顺序IO会更慢。

MRR(多范围读取)

每次执行回表操作时都相当于要随机读取一个聚集索引页面，而这些随机IO带来的性能开销比较大。MySQL中提出了一个名为Disk-Sweep Multi-Range Read 的优化措施，即先读取一部分二级索引记录，将它们主键值排好序之后再统一执行回表操作。会相对节省一些IO开销，MRR优化措施的条件比较苛刻，所以我们直接认为每读取一条二级索引记录就立即执行回表操作。

自适应哈希索引

在InnoDB存储引擎内部自己去监控索引表，如果监控到某个索引经常用，那么就认为是热数据，然后内部自己创建一个hash索引，称之为自适应哈希索引( Adaptive Hash Index,AHI)，创建以后，如果下次又查询到这个索引，那么直接通过hash算法推导出记录的地址，直接一次就能查到数据，比重复去B+tree索引中查询三四次节点的效率高了不少。 InnoDB存储引擎使用的哈希函数采用除法散列方式，其冲突机制采用链表方式。注意，对于自适应哈希索引仅是数据库自身创建并使用的，我们并不能对其进行干预。通过命令show engine innodb status\G可以看到当前自适应哈希索引的使用状况【数据结构用的数组+链表的方式】哈希索引只能用来搜索等值的查询。

全文检索之倒排索引

什么是全文检索（Full-Text Search）？它是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术。它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析。我们比较熟知的Elasticsearch、Solr等就是全文检索引擎，底层都是基于Apache Lucene的。

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

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

记录上，加快查询的速度。

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

在where条件中用了多个二级索引。

3、索引合并(index merge)： 一般情况下是最多能使用一个，特殊情况下，也可能在一个查询中使用到多个二级索引，MySQL中这种使用到多个索引来完成一次查询的执行方法，称为：索引合并（index merge）。MySQL会分别分析单独使用这些索引执行查询的成本，最后还要分析是否可能使用到索引合并。

三种索引合并算法：

A.Intersection （交集合并）：是说某个查询可以使用多个二级索引，将从多个二级索引中查询到的结果取交集。因为二级索的记录都是由索引列+主键构成，叶子结点存的是主键id,将主键id取交集再统一进行回表操作，缩小了查询的范围。

原因（成本因素）：只读取一个二级索引的成本为根据从该二级索引得到的主键值进行回表，然后将结果根据其他搜索条件进行过滤的成本；读取多个二级索引之后取交集的成本为，按照不同的搜索条件分别读取不同的二级索引，并将主键取交集，然后回表的成本。虽然读取多个二级索引比读取一个二级索引消耗性能，但是大部分情况下读取二级索引的操作时顺序I/O，而回表操作时随机I/O，如果只读取一个二级索引回表记录特别多，但是读取多个二级索引之后取交集的记录数明显更少，节省的因回表而造成的性能损耗比访问多个二级索引带来的性能损耗更高时，读取多个二级索引后取交集比只读取一个二级索引的成本更低。

满足交集合并的情况：

1.等值匹配，条件里面都是等值匹配的二级索引，如果是联合索引，则每个列都必须等值匹配才可以。

2.主键列可以是范围匹配，例：

SELECT * FROM order_exp WHERE id > 100 AND insert_time = 'a';

对于InnoDB的二级索引来说，记录先是按照索引列进行排序，如果该二级索引是个联合索引，那么会按照联合索引中的各个列依次排序。而单个二级索引是由索引列+主键构成，二级索引列的值相同的记录可能会有好多条，这些索引列的值相同的记录又是按照主键的值进行排序的。但如果从各个二级索引中查询出的结果集并不是按照主键排序的话，就要先把结果集中的主键值排序完再来进行比较，比较耗时。所以内部会自行先将分别获取到的主键id进行排序，再进行操作，最后按照有序的主键值去回表叫做：Rowid Ordered Retrieval ROR;

按照一般的想法，会先从聚簇索引中获取一次记录，再从二级索引中获取一次记录，求交集。但其实二级索引中的记录是都带有主键值的！所以可以从二级索引中获取到的主键id,直接运用条件 id>100过滤就行了。 此时不是等值匹配，但是依然进行了索引合并！

B.Union（并集合并）：

我们在写查询语句时经常想把既符合某个搜索条件的记录取出来，也把符合另外的某个搜索条件的记录取出来，我们说这些不同的搜索条件之间是OR关系。有时候OR关系的不同搜索条件会使用到不同的索引

SELECT * FROM order_exp WHERE order_no = 'a' OR expire_time = 'b'

满足并集合并的情况同交集合并的情况一致，同时，可以使用交集合并作为并集合并的搜索条件。

SELECT * FROM order_exp WHERE insert_time = 'a' AND order_status = 'b' AND expire_time = 'c' OR (order_no = 'a' AND expire_time = 'b');

会先用交集合并得到一个主键集合，再采用Union索引合并的方式把两个主键集合合并取并集，然后进行回表操作。

C.Sort-Union(排序索引合并)：

Union索引合并的使用条件太苛刻，必须保证各个二级索引列在进行等值匹配的条件下才

可能被用到，比方说下边这个查询就无法使用到Union索引合并：

SELECT * FROM order_exp WHERE order_no< 'a' OR expire_time> 'z'

先根据order_no< 'a'条件从idx_order_no二级索引中获取记录，并按照记录的主键值进行排序再根据expire_time> 'z'条件从idx_expire_time二级索引中获取记录，并按照记录的主键值进行排序因为上述的两个二级索引主键值都是排好序的，剩下的操作和Union索引合并方式就一样了。上述这种先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程。

总结：可以用联合索引替代Intersection索引合并。

空间上的代价

这个是显而易见的，每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点

都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成会占据很多的存储空间。

时间上的代价

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

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

高性能索引创建策略

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

2.创建索引应该选择选择性（离散性）高的列：不重复的索引值（基数）和数据表的记录总数（N）的比值。索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性是1，也是最好的索引选择性，性能也是最好。

3.前缀索引：索引很长的字符列，会让索引变的大且慢，可考虑用Hash索引（但是不支持范围查询），可选择足够长的前缀索引以保证较高的离散性。

前缀索引是一种能使索引更小，更快的办法，但是无法用前缀索引做order by和 group by，也无法使用前缀索引做覆盖扫描。有时候后缀索引（suffix index）也有用途（例如，找到某个域名的所有电子邮件地址)。MySQL原生并不支持反向索引，但是可以把字符串反转后存储，并基于此建立前缀索引。可以通过触发器或者应用程序自行处理来维护索引。