MySQL数据库学习（一）------MySQL索引+数据库设计三大范式+存储引擎

妖狐艹你老母 2023-07-06 11:34 4阅读 0赞

### 文章目录 ###

*  MySQL数据库之索引
 *   *  索引概述
     *  优化索引之底层结构
     *   *  二叉查找树结构
         *  B树结构
         *  B+树结构（优于B树）
         *  Hash结构
         *  位图索引Bit-Map
     *  索引之调优SQL
     *  密集索引与稀疏索引
     *  聚簇索引和非聚簇索引
     *   *  聚簇索引
         *  非聚簇索引
         *  覆盖索引
     *  索引之左匹配原则
 *  数据库三大范式
 *  MySQL数据库之存储引擎
 *   *  存储引擎分类及特点
     *  如何更改或设置存储引擎

这是最近准备春招复习数据库部分总结到的一些数据库方面比较重点的部分。  
这是第一部分，主要是对于数据库中索引、存储引擎、三大范式的总结整合一些网上的博客，和自己过去做的一些笔记。  
下一篇\*\*MySQL数据库学习（二）\*\*将是对于MySQL的事务与隔离级别，以及MySQL的锁机制和日志模块的一些总结

--------------------

MySQL的基本逻辑架构图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70]

*  连接器：验证客户端权限，建立和断开MySQL连接
 *  分析器：进行SQL语句的语法分析
 *  优化器：选择索引，生成具体的SQL语句执行计划
 *  执行器：操作存储引擎，执行SQL，返回执行结果
 *  存储引擎层：各个不同的存储引擎都提供了一些读写接口来操作数据库

关系型数据库通常的整体架构：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 1]

# MySQL数据库之索引 #

## 索引概述 ##

> 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息，就像一本书的目录一样，可以**加快查询速度**，避免**全表扫描**

什么样的信息可以成为索引？

> 主键，唯一键以及普通键等

经常需要作为条件查询的列上适合创建索引，并且该列上也必须有一定的区分度。创建索引需要维护，在插入数据的时候会重新维护各个索引树（数据页的分裂与合并），对性能造成影响。

## 优化索引之底层结构 ##

### 二叉查找树结构 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 2] 对半搜索，时间复杂度为**O(logn)**  
缺点是，频繁的插入删除操作可能使二叉树变成线性的，如下：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 3]此时时间复杂度为**O(n)**，检索深度每增加一个深度，就会增加一次I/O，成本开销会增大很多

### B树结构 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 4]

> B树的特征：
> 
> 1.  根节点至少包含两个孩子
> 2.  树中每个结点最多含有m个孩子(m >= 2)
> 3.  除了根节点和叶结点外，其他每个结点至少含有**ceil(m/2)个孩子**，ceil为向上取整
> 4.  所有叶子结点位于同一层（高度相同）
> 5.  假设每个非终端结点中包含有n个关键字信息，其中
> 
>  *  Ki(i = 1…n)为关键字，且按顺序升序排列
>  *  关键字的个数n必须满足：\[ceil(m / 2) - 1\] <= n <= m - 1
>  *  非叶子结点的指针P\[1\],P\[2\],…,P\[M\];其中K\[1\]指向关键字小于K\[1\]的子树，P\[M - 1\] 指向关键字大于K\[M - 1\]的子树，其它P\[i\]指向关键字属于(K\[i-1\], K\[i\])的子树，**比如看图中关键字值为8的这个结点，P1所对应的这个子树，其值均小于8**

查找效率为**O(logn)**，与二叉查找树一样，同时因为B-tree的这些上述特征，在增删改动数据时，根据一些策略，其结构可以保持，不会变为线性。

### B+树结构（优于B树） ###

> B+树是B树的变体，其基本的定义与B树相同,除了：
> 
>  *  非叶子结点的子树指针与关键字个数相同（所以相对于B树，B+树能够存储更多的关键字）
>  *  非叶子结点的子树指针P\[i\]，指向关键字值\*\*\[K\[i\], K\[i+1\])\*\*的子树，注意区间为左开右闭。
>  *  非叶子结点仅仅用来索引，数据都保存在叶子结点中（B+树所有的检索都是从根部开始，检索到叶子结点才能结束，而且非叶子结点不存储数据的话就能存储更多关键字）
>  *  B+树相对于B树更矮
>  *  所有叶子结点均有一个链指针指向下一个叶子节点

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 5]综合上面的，B+Tree更适合用来做存储索引：

1.  B+Tree的特点使得磁盘I/O代价更低，B+树的内部结点并没有指向关键字具体信息的指针，因此其内部结点相对B 树更小，同样空间可以读入更多的节点，所以B+树的磁盘读写代价更低
2.  B+Tree的查询效率更加稳定（查询路径均为从根结点到叶子结点，查询效率均为O(logn)）
3.  B+Tree更有利于对数据库的扫描（只需要遍历叶子结点就可以进行范围查询）

### Hash结构 ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 6]其查询效率高于B+Tree，只需经过一次定位，就可以查询到对应数据区  
但是其也存在一些弊端，因而不能成为数据库的主流索引的扛把子：

1.  仅仅能满足 “=” , “IN”，不能使用范围查询
2.  无法被用来避免数据的排序操作
3.  不能利用部分索引键查询，B+Tree可以利用组合索引中的部分索引查询
4.  不能避免表扫描
5.  因为Hash值是经过一定的算法得到的，所以存在相同的情况，当遇到大量Hash值相等的情况后性能不一定比B-Tree索引要高

### 位图索引Bit-Map ###

使用的数据库较少，比较主流的是Oracle数据库  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 7]感兴趣的小伙伴可以自己了解了解哈

## 索引之调优SQL ##

## 密集索引与稀疏索引 ##

*  密集索引文件中的每个搜索码值都对应一个索引值
 *  稀疏索引文件只为索引码的某些值建立索引项  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 8]众所周知，当前两种主流的数据库引为MyISAM和InnoDB(下一部分会有二者的对比介绍)  
    在MyISAM存储引擎中的索引均为稀疏索引，InnoDB中有且仅有一个密集索引  
    InnoDB中密集索引的具体规则：
 *  若一个主键被定义，该主键作为密集索引
 *  若没有主键被定义，该表的第一个唯一非空索引就作为密集索引
 *  若不满足以上条件，InnoDB内部会生成一个隐藏主键（密集索引）
 *  非主键索引存储相关的键位和其对应的主键值，包含两次查找

## 聚簇索引和非聚簇索引 ##

### 聚簇索引 ###

聚簇索引也称为**主键索引**，其索引树的叶子节点中存的是整行数据，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。因为索引（目录）只能按照一种方法进行排序。

### 非聚簇索引 ###

非聚簇索引（普通索引）的叶子节点内容是**主键的值**。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

*  如果语句是 select \* from User where id=3，即主键查询方式，则只需要搜索 主键索引树。
 *  如果语句是 select \* from User where uid=23，即普通索引查询方式，则需要先搜索 普通索引树，得到其对应的主键值为 3，再到主键索引树搜索一次。这个过程称为**回表**。

说道这里，在InnoDB中，**聚簇索引**是**密集索引**；**非聚簇索引**是**稀疏索引**。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 9]

### 覆盖索引 ###

如果在普通索引树上的查询已经直接提供了结果，不需要回表操作，这样的普通索引叫做覆盖索引。覆盖索引的使用可以显著提高查询效率，是常见的MySQL性能优化手段。

## 索引之左匹配原则 ##

在联合索引的情况下，不需要索引的全部定义，只要满足最左前缀，就可以利用索引来加快查询速度。这个最左前缀可以是联合索引的最左 N 个字段，也可以是字符串索引的最左 M 个字符。最左前缀原则的利用也可以显著提高查询效率，是常见的MySQL性能优化手段。  
![在这里插入图片描述][20200218190605457.png]

# 数据库三大范式 #

1.  第一范式：1NF 原子性，数据不可再分，且这个单一属性是由基本的数据类型所构成的
2.  第二范式：2NF 唯一性 使得每一行 数据具有唯一性，并消除数据之间的部分依赖

> 依赖：依赖，就是在一个表中，其中某个字段的值B可以由另一个字段值A来决定

> 完全依赖：主键可能由多个属性构成，完全依赖要求不允许存在非空主属性依赖于主键中的某一部分属性

> 若存在部分依赖，则将发生部分依赖的这一组属性单独新建一个实体，并且在旧实体中用外键于新实体关联，且新实体与旧实体为一对多的关系。

1.  第三范式：3NF 实体中的属性不能是其他实体中的非主属性。因为这样会出现冗余。

> 实体如果一个实体中出现了其它实体的非主属性，可以将这两个实体用外键关联，而不是将另一张表的非主属性直接写在当前表中。

# MySQL数据库之存储引擎 #

## 存储引擎分类及特点 ##

MySQL中最常见的存储引擎有**InnoDB**和**MyISAM**，它们的主要区别如下：

*  MyISAM不支持事务；InnoDB是事务类型的存储引擎。
 *  MyISAM只支持表级锁；InnoDB支持行级锁和表级锁，默认为行级锁。
 *  MyISAM引擎不支持外键；InnoDB支持外键。
 *  对于count(\*)查询来说MyISAM更有优势，因为其保存了行数。
 *  InnoDB是为处理巨大数据量时的最大性能设计的存储引擎。
 *  MyISAM支持全文索引（FULLTEXT）；InnoDB不支持。

> 最主要的区别就是MyISAM表不支持事务、不支持行级锁、不支持外键。 InnoDB表支持事务、支持行级锁、支持外键。

除了上述的那些存储引擎之外，在数据库中（不是MySQL）还有其他的存储引擎，如MRG\_MYISAM，Archive，Ndb cluster等。  
在面试中常见的考察存储引擎还有Memory，使用Memory作为存储引擎的表也可以叫做内存表，将数据存储在了内存中，所以适合做临时表来使用，在索引结构上支持B+树索引和Hash索引。

## 如何更改或设置存储引擎 ##

直接随意空降链接一枚，Google一下很多的，也感谢这位老哥…  
https://blog.csdn.net/qq\_36294875/article/details/81775274

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/2020021817164457.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200218095613894.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200218102423797.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200218102823724.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200218103031618.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200218110234778.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200218111349117.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200218112311411.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200218151943328.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4=,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200218154839726.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2JvYl9tYW4=,size_16,color_FFFFFF,t_70
[20200218190605457.png]: https://img-blog.csdnimg.cn/20200218190605457.png