MySQL数据库【索引事务】

小鱼儿 2023-10-14 10:07 97阅读 0赞

![dcbf4a46186d4edd9d653532a3214378.jpeg][]

**目录**

一、概念

二、索引的优缺点

1、索引的优点

2、索引的缺陷

三、索引的使用

1、查看索引

2、创建索引

3、删除索引

四、索引底层的数据结构

1、B树

2、B+树

五、索引事务

1、概念和回滚

2、事务的使用

3、事务的基本特性

4、并发会遇到的问题

（1）脏读

（2）不可重复读

（3）幻读

5、隔离级别

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

## 一、概念 ##

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现。

索引，可以理解成一本书的目录。

一个表里面有很多的数据，在查询表的时候，最基本的方式就是遍历表，一条一条的筛选，因此，就可以 **给这个表建立索引（以 列 为维度进行建立的）**来提高查找的速度

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

## 二、索引的优缺点 ##

### 1、索引的优点 ###

现在假设有这么一个表：

![4398a62727a841daaecde2389b5e35b6.png][] 比如，按照 id 这一列建立索引

在数据库上，我们就额外搞了一个空间，维护一些和 id 相关的信息，这里就通过一些特定的数据结构，维护表示 id 相关的索引的情况

后续再按照 id 来查询，就不必再直接遍历了，而是从索引中进行查询，根据索引就能够初步的锁定出数据所在的位置

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

### **2、索引的缺陷** ###

**1、消耗额外的空间**

对于数据库来说，数据都是存储再硬盘上的，索引数据也是在硬盘上的

**2、有可能会拖慢增删查改的速度**

对于新增来说，此时不光要往表里面写数据，同时还要修改索引

如果删除、修改的条件如果刚好和索引匹配，速度还能快一点

但是如果涉及到索引列的删除 / 修改，这个时候，也要同时维护索引

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

## 三、索引的使用 ##

创建 **主键约束（PRIMARY KEY）、唯一约束（UNIQUE）、外键约束（FOREIGN KEY）** 时，会自动创建对应列的索引。

### 1、查看索引 ###

**语法：**

show index from 表名;

![f321259580624424936ec1950e030c09.png][]

我们可以看到，目前 student 这个表中存在主键

![c681e6d7e9894d26a53e2da316db1d72.png][]**当表中存在主键的时候，内部就会自动给这个 列 来创建索引**

这是因为，主键不允许重复，因此进行插入 / 修改 操作的时候，就需要先查询，看看 插入 / 修改 后的结构是否已经存在

我们可以看到，使用 unique 约束的时候，也会自动生成索引![77c104a1d38e4cb6961b509362840a20.png][]

再重新创建一个 student 表和 class 表相关联

![e01156254cba40f7bbf84cc72b7a29f5.png][]

我们可以看到，班级表，由于 classId 是主键，自动生成了一个索引

![597b115d62f044449eea69b6058677c0.png][]学生表里面包含了两个索引，其中一个索引是由主键生成的，另一个是由于我们设置了外键约束所自动生成的索引

![3010b908269546a2823c5e3fccb6b6cc.png][]

这是因为，外键这里也涉及到自动查询

> 1、给学生表中插入一条记录，就需要查询classId 是否在 class 表的 classId 中存在，这个查询就使用到了 class 表中的 classId （主键自动生成的索引）
> 
> 2、给班级表中删除一条记录，就需要查询 classId 是否在学生表中存在

**在上述情况中，主键、unique 、外键 这三种情况都会使 表 自动创建出索引**

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

### 2、创建索引 ###

**语法：**

create index 索引名 on 表名(字段名);

![e9d360ddadfc454c8ea5a8e73035a25a.png][]

![27b46b241c5a4effafbd3f2a8d77e0b0.png][] 此时，就能够看到我们新创建出来的索引

**这个创建索引操作可能会非常危险！！！**

如果这个表是空的，或者表里面包含的数据本身就不多，创建索引就没事

如果表非空，并且里面包含了非常多的数据，那么创建索引会引起非常大规模的硬盘 IO 操作，进一步就会导致数据库被卡死

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

### 3、删除索引 ###

**语法：**

drop index 索引名 on 表名;

![a264a406476e4b769883cfb420598d80.png][]

**删除索引，只能针对 手动创建的索引，自动生成的索引使不能被删除的**

删除索引这个操作也是非常危险的，原因与前面创建索引类似

**那么如果现在我们必须给一个已经创建了很多数据的表 创建 / 删除 索引，并且这个数据库还是生产环境的数据库，咋办？**

数据库服务器往往也不是单台服务器，为了整个系统的可靠性，通常会搞多个MySQL服务器节点，这些节点的数据都是一样的，能够提高相同的服务（其中某个挂了也不影响大局

**先准备好一个新的 MySQL 服务器，把表和索引都创建好，然后把数据都导入过来，再把要替换的MySQL服务器关闭，把新的MySQL 服务器替换上去就行了**

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

## 四、索引底层的数据结构 ##

**MySQL 的索引的数据结构取决于MySQL使用的是哪一个存储引擎**

MySQL这个程序，里面包含了很多个模块，有的是负责解析SQLDE ,有的是负责网络通信的，由的是负责存储数据的 ......

**存储引擎本质上就是代码里的一个模块**（这里包含了若干个代码文件以及一大堆具体的代码）

具体如何存储数据，**MySQL支持多种存储方案，innodb 是当下最主流的一种方式**

以往我们谈到的数据结构都是内存中的数据结构，但是我们**数据库这块组织数据使用的数据结构则是在硬盘上的**

内存上的数据结构，对于访问操作是不敏感的（找数据的过程，花时间多，但是真正访问的时候时间不多

**硬盘上的数据结构，对于访问操作来说，比较敏感**，读写一次硬盘开销远远大于内存

**哈希表**，O(1) 复杂度 查询 / 插入 / 删除 / 修改数据

但是并**不适合数据库的查询场景**，这是因为哈希表只能做这种精确查询，没法做模糊查询和范围查询

哈希表需要把指定的 kye 通过 hash 函数映射出一个具体的下标才能定位到具体的位置

**红黑树**，O(logN)复杂度 查询 / 插入 / 删除 / 修改数据

**也不适合数据库的查询场景**

元素有序，可以处理范围查询，最大的问题在于红黑树的高度，会在元素个数比较多的适合，变得比较高

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

B+ 树是为了数据库量身定做的数据结构

**注意：当前目前的讨论都是围绕 MySQL 的 innodb 这个存储引擎进行的（其它的存储引擎中可能会用到 hash 作为索引，只能应对这种精准匹配的情况）**

要了解 B+ 树，就需要先了解一下 B树

### 1、B树 ###

B树 有时候会写作 B- 树，- 是连接符，不是减号

B树的核心思路，和之前学过的 " 二叉搜索树 " 差不多，**B树 本质上是一个 N叉搜索树**

**N叉搜索树： 一个节点上可以保存 多个 kye ,N 个 key 就能延伸出 N + 1 个分叉来， N 个 kye 就划分出了 N + 1 个区间**

![0ecb373b91234700a5bbcc4943d9164b.png][]

**B树查询元素的流程：**

> 1、拿着要查询的元素，从根节点出发， 判定要查找的元素是否在根节点上存在
> 
> 2、如果不存在，看这个元素是落在哪个区间里，就沿着这个区间的路线往下一个节点上找
> 
> 3、最终找到叶子节点，还不存在，就是真的存在了

此时，**每个节点上，都可以保存多个元素了**

**当总的元素个数固定的适合，相比于二叉搜索树，涉及到的节点的总数就大大降低了 ，同时树的高度也大大降低了**

由此可知，**B树的高度是远远小于二叉搜索树的**，于是，在进行查询的时候，硬盘IO的次数也就减少了

对于数据库来说，每个节点都需要把数据从硬盘上读出来，才能进行比较

**一个节点上有多个 key 和 一个节点上有一个 key ，硬盘IO的开销是差不多的**

对于B树来说，在进行插入元素 和 删除元素 的时候，就涉及到 拆分 和 合并

**一个节点 可以存多个 key ，但是也不能无限的存，当存储的 key 数量达到一定程度的时候，就需要把这个节点给拆分，把这个节点中的一部分 key 以树的子节点的方式，来进行重新组织，保持当前这个节点中的 key 数量始终不会太多，这样就会衍生出新的节点**

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

### 2、B+树 ###

B+ 树 才是数据库索引的主角，**在B树的基础上，又针对数据库的查询场景做出了一些改进**

![68671cbf5da8445daadcb84e40fc63e2.png][]

**B+ 树的特点：**

![ff5b137c2593477490f83444db5b2e3b.png][]

上面是B + 树的特点，那么这些特点产生的优势是什么呢？

**B+树的优势：**

![c72569f2e3a4423486bd82d90f20a4c1.png][]

数据库的数据在逻辑意义上是以 "表" 的形式存储的

![67271df1cfc344e18ffc7ca16ed7c678.png][]

但是在物理意义上，不需要 “表格” 这样的数据结构，**而是直接用 B+ 树 来存储这个表的数据**，表格只是用户看起来这像是个表格

此时，**非叶子节点的存储空间，消耗是非常小的，可以在内存中缓存一份！！！**

此时，**进行数据查询的时候，就可以直接通过 内存 来直接进行比较，从而更快速的找到叶子节点上的记录（又一次减少了硬盘IO的次数）**

**但是B树如果也要把元素存储到每个节点上，非叶子节点就会占据空间比较大，从而无法在内存中缓存了**

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

## 五、索引事务 ##

### 1、概念和回滚 ###

事务的本质，就是把多个操作打包成一个操作来完成（让这多个操作，要么全都能执行成功，要么一个都不执行）

**注意：一个都不执行，不是真的没有执行，执行是否成功，得执行了才知道**

真正执行之前，是不知道在执行具体哪一步的时候会失败

如果是执行到中间的操作出错了，就需要将前面已经成功执行的操作进行还原，还原到最初没有执行的样子，我们将还原到最初没有执行的样子的这个操作称为： **回滚（rollback）**

**回滚是怎么样实现的呢？**

> 只要把事务中执行的每个操作，**通过特定的日志**来记录数据库事务操作的中间过程，如果需要回滚，就直接把之前操作的 "逆操作" 来执行就可以了

**那如果数据库服务器重启了呢?**

> 因为我们是通过日志来记录事务执行的中间过程的，**日志中的数据是始终在硬盘上存在的**，即使是数据库服务器重启，就会在重新启动之后，针对之前没有回滚完的情况继续进行处理

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

### 2、事务的使用 ###

（1）开启事务

start transaction;

（2）执行多条SQL语句

（3）回滚或提交事务

rollback/commit;

commit 提交事务：把这些SQL 按照原子的方式来进行执行（带有回滚机制）

rollback 手动触发回滚

**一个事务务必要以这两个操作（提交事务和回滚）结尾！！！**

如果没有这两个操作，接下里的各种 SQL 操作都会被认为是事务的一部分

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

### 3、事务的基本特性 ###

事务的基本特性是非常重要的一个知识点

那么事务的基本特性有哪些呢？

> 1、原子性 ：保证多个操作被打包成一个整体，要么能全部被正确执行，要么就一个都不执行
> 
> 2、一致性 ： 保证事务执行之前和事务执行之后，数据能够对上，数据不能离谱
> 
> **（约束、回滚机制 是保证一致性的重要手段）**
> 
> 3、持久性 ： 事务这里执行的各种操作，都是持久生效的**（最终写入到硬盘中的）**，一旦事务执行成功了，这里的所以操作产生的修改，都是写到硬盘里的
> 
> 4、隔离性 ： 并发执行事务的时候，隔离性会在执行效率和数据可靠之间做出平衡

"隔离" 描述的是同时执行的事务之间，相互的影响

隔离性越高，并发性就越低，数据越可靠，性能就越低

**什么是并发呢？**

可以简单理解成：同时执行

数据库是一个客户端 服务器 结构的程序，既然是服务器，服务器就可以同一时刻给多个客户提供服务，这多个客户端，都能够给服务器提交事务

如果提交的这两个事务，是修改不同的数据库 / 不同的表，相互之间是没有什么影响的

如果这两个事务，修改的是同一个表，就可能存在麻烦

![78e4940b11e246d5bf0f4228aabaf16a.jpeg][]

当顺序上存在差异的时候，顺序的先后就可能会影响到最终的执行结果，这就是并发执行事务带来的问题

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

### 4、并发会遇到的问题 ###

#### （1）脏读 ####

此外，并发执行事务的时候，还有可能遇到**脏读**的情况

**脏数据：临时的，并非是最终准确的结果**

**那么如何解决脏读问题？**

> 给 "写" 操作加锁 ，一个事务A 写的时候，其它事务B不能读了，直到事务A 写完数据，提交事务，其它的事务B才能来读取数据

**引入了 “写” 加锁，降低了两个事务之间的并发性，提高了隔离性，降低了效率，使数据更准确了**

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

#### （2）不可重复读 ####

在同一个读取数据的事务中，可能会涉及到多次操作，多个 "读" 操作读到的数据可能会不一样

**如何应对不可重复读？**

给 "读" 操作也加锁

**给 "写" 操作加锁的意思**：我在写的时候，别人不能读（除非使我写完提交，别人才能读），此时，别人读的过程中，我还可以再开启一个事务来写，第二个事务提交之前，其它读的事务读到的就是新版本数据了

**给 "读" 操作加锁的意思**：别人读的时候，我不能写了。

**这个时候，并发程度又进一步降低了（执行效率降低了），隔离性进一步提高了（数据更可靠了）**

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

#### （3）幻读 ####

一个事务，在多次读的过程中，虽然多次读的数据的值是一样的但是结果集不同

比如：第一次读是10条记录，第二次读是11条记录，这11条记录中的10条和之前的10条是一模一样的，但是多出来一个，我们称为**幻读**

**可以视为，是不可重复读的特殊情况**

**如何解决幻读？**

> 办法只有一个： **串行化**
> 
> 彻底放弃并发执行事务，所有的事务都是一个挨着一个的串行执行（执行完一个事务，再执行下一个事务）

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

### 5、隔离级别 ###

MySQL 提供了四种事务的隔离级别，MySQL可以配置自己的隔离级别是哪一个

1、**read uncommitted (RU)** 允许读未提交的数据（脏读、不可重复度、幻读），**隔离性最低**，并发程度最高，数据可靠性最低，效率最高

2、**read commmittded (RC)** 允许读取已经提交了的数据（给写加锁了），解决脏读、存在不可重复读和幻读，隔离性提高了，并发性降低了，数据可靠性提高了，效率降低了

3、**repeatable read （RR）****默认的隔离级别**，可以重复读取数据（给写操作和读操作都加锁），解决了脏读和不可重复读的问题，存在幻读问题，隔离性又提高了，并发性又降低了，数据可靠性又提高了，效率又降低了

4、**serializable** 事务彻底的串行执行，解决了脏读、不可重复读、幻读的问题，**隔离性最高，并发性最低（没有），数据最可靠，效率也最低**

[dcbf4a46186d4edd9d653532a3214378.jpeg]: https://img-blog.csdnimg.cn/dcbf4a46186d4edd9d653532a3214378.jpeg
[4398a62727a841daaecde2389b5e35b6.png]: https://img-blog.csdnimg.cn/4398a62727a841daaecde2389b5e35b6.png
[f321259580624424936ec1950e030c09.png]: https://img-blog.csdnimg.cn/f321259580624424936ec1950e030c09.png
[c681e6d7e9894d26a53e2da316db1d72.png]: https://img-blog.csdnimg.cn/c681e6d7e9894d26a53e2da316db1d72.png
[77c104a1d38e4cb6961b509362840a20.png]: https://img-blog.csdnimg.cn/77c104a1d38e4cb6961b509362840a20.png
[e01156254cba40f7bbf84cc72b7a29f5.png]: https://img-blog.csdnimg.cn/e01156254cba40f7bbf84cc72b7a29f5.png
[597b115d62f044449eea69b6058677c0.png]: https://img-blog.csdnimg.cn/597b115d62f044449eea69b6058677c0.png
[3010b908269546a2823c5e3fccb6b6cc.png]: https://img-blog.csdnimg.cn/3010b908269546a2823c5e3fccb6b6cc.png
[e9d360ddadfc454c8ea5a8e73035a25a.png]: https://img-blog.csdnimg.cn/e9d360ddadfc454c8ea5a8e73035a25a.png
[27b46b241c5a4effafbd3f2a8d77e0b0.png]: https://img-blog.csdnimg.cn/27b46b241c5a4effafbd3f2a8d77e0b0.png
[a264a406476e4b769883cfb420598d80.png]: https://img-blog.csdnimg.cn/a264a406476e4b769883cfb420598d80.png
[0ecb373b91234700a5bbcc4943d9164b.png]: https://img-blog.csdnimg.cn/0ecb373b91234700a5bbcc4943d9164b.png
[68671cbf5da8445daadcb84e40fc63e2.png]: https://img-blog.csdnimg.cn/68671cbf5da8445daadcb84e40fc63e2.png
[ff5b137c2593477490f83444db5b2e3b.png]: https://img-blog.csdnimg.cn/ff5b137c2593477490f83444db5b2e3b.png
[c72569f2e3a4423486bd82d90f20a4c1.png]: https://img-blog.csdnimg.cn/c72569f2e3a4423486bd82d90f20a4c1.png
[67271df1cfc344e18ffc7ca16ed7c678.png]: https://img-blog.csdnimg.cn/67271df1cfc344e18ffc7ca16ed7c678.png
[78e4940b11e246d5bf0f4228aabaf16a.jpeg]: https://img-blog.csdnimg.cn/78e4940b11e246d5bf0f4228aabaf16a.jpeg