MySQL的索引和事务

水深无声 2023-09-28 18:52 8阅读 0赞

**目录**

1.索引（index）

1.1 索引的理解

1.2 查看索引

1.3 创建索引

1.4 删除索引

1.5 索引的数据结构 （B+树）

2.事务

2.1 理解事务

2.2 使用

2.3 事务的特性

2.4 MySQL的隔离级别

3. 本篇需理解这几点

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

## 1.索引（index） ##

### 1.1 索引的理解 ###

> 索引存在的主要意义，就是为了**提高查询的效率**
> 
> **索引就相当于目录**
> 
> 大多数情况下，查询的频率是高于增删改的，所以索引的存在是很有必要的
> 
> 索引付出的代价：
> 
> 1. **消耗更多的空间**
> 
> 2. 虽然提高了查找效率，但是**降低了增 删 改的效率**
> 
> （比如插入新记录，需要既能够插入硬盘的数据，又要调整索引）

### 1.2 查看索引 ###

> **show index from 表名；**

![3c18b5e883d44e5b8680cc7b83910dcd.png][]

### 1.3 创建索引 ###

> **create index 索引名 on 表名 (列名);**

创建索引操作，也是一个低效操作，如果表中数据少不影响，

如果表中数据多，创建索引操作，就可能会非常耗时，并且带来大量的硬盘IO

![752d0c192e6640a19a4fcc48981478e9.png][]

### 1.4 删除索引 ###

> **drop index 索引名 on 表名;**

**删除操作和创建操作都是比较低效的操作**

### ![8e968864866f42619e0aee74456c7431.png][] ###

> SQL可以支持一些 定义变量，定义函数，条件，循环，库函数等 但是这些很少用到
> 
> SQL 一般都是单纯的去 增删查改

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

### 1.5 索引的数据结构 （B+树） ###

**索引能够提高查询效率，具体是怎么提高的**

**索引背后的数据结构是怎么样的**

> 数据结构中，**查询效率高的有 哈希表 和 二叉搜索树，但是这两种不适合用于数据库索引**
> 
> 因为
> 
> （1）**哈希表虽然增删查改都快 O(1)**
> 
> **但是只能查询 值 相等的情况**，但是如果是< > between and 这种**比较大小的范围查询就不适合**了
> 
> （2）**二叉搜索树，**查询速度 O(N)最坏情况
> 
> **AVL 树 / 红黑树** （比较平衡的二叉搜索树）O(logN)
> 
> 如果数据库**数据特别多时，树的高度就会比较高，高度高了就查询时比较的次数就会多**
> 
> 如果每次要读硬盘，这样就不太行了

所以上面两种都不适合，就有了这样一个**专门用于数据库的数据结构 B+ 树**

**下面先了解一下B树**

>  **B树，是一个N叉搜索树**
> 
> ![c45db17659384b3c91eb48e130c3dbcc.png][]
> 
> 这样**分成N个叉的作用，就是表示相同元素的数据集合时，比二叉树的高度小很多，IO次数就降低了很多**

**B+树**

> ![c7edb05c609047eb9a97513b5b121ed3.png][]

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

## 2.事务 ##

### 2.1 理解事务 ###

> 事务指逻辑上的一组操作，组成这组操作的各个单元，要 **不全部都执行完，要不就一个都不执行**
> 
> （ **不是说真的没执行，而是执行一半如果出现问题，能够恢复如初** ）
> 
> 事务就能保证，当执行过程中出现问题时，自动的把前面的sql执行的效果进行还原，恢复如初 这个操作叫 **回滚（rollback）**
> 
> 在事务执行过程中，MYSQL会记录每一步都执行了啥，一旦出现问题就可以根据记录回滚
> 
> 在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。

### 2.2 使用 ###

> **（1）开启事务：start transaction;**
> 
> **（2）执行多条SQL语句** 
> 
> **（3）回滚或提交：rollback/commit; 全部失败/全部成功**

### 2.3 事务的特性 ###

> **原子性：**事务的存在的意义，能够把多个SQL打包成一个整体，要么全部执行完，要么一个都不执行（如果执行过程中出错，则自动回滚）
> 
> **一致性：**事务执行前后，数据处在“一致”的状态（数据能对的上，合情合理）
> 
> **持久性：**事务进行的改动，都是写到硬盘里的，不会随着程序重启/主机重启，而丢失
> 
> **隔离性：**（最复杂）多个事务，并发执行时，事务之间能够保持“隔离”，不互相干扰
> 
> (隔离性存在的意义，就是让并发执行事务的过程中，尽量不出问题（问题在可控范围内）)

**在并发执行事务下会出现的问题**

**（1）脏读（读取到脏数据，解决方法：对写操作加锁）**

> **脏读：一个事务A在修改数据，提交之前，另一个事务B读取了数据，此时A极有可能在提交的时候提交的是不同数据（后续又修改了）**
> 
> 此时**事务B读到的就是“无效的数据”**，就称为 **脏读，读取脏数据**
> 
> **解决脏读问题的思路**
> 
> 在提交之前，不能读，提交之后才能读（写完才能读）
> 
> 相当于**对 写操作 加锁**
> 
> 在写加锁之前，我的写操作，和别的事务的读操作，就是完全并发的
> 
> 并发性是最高的，隔离性最低的
> 
> 当写加锁之后，我的写操作，别的事务就不能读了
> 
> 并发性降低了（效率降低）,隔离性提高了（准确性提高了）

**（2）不可重复读 （读的过程被修改，解决方法：使用读加锁）**

> 如果对写操作加锁后，当我的写操作执行完后，别的事务开始读取代码
> 
> 此时如果我再次对代码进行修改，再提交，此时别的事务就发现代码改变了
> 
> **不可重复读：在一个事务A中，多次读取同一个数据，发现不一样****（读的过程被修改）**
> 
> **不可重复读：需要使用 读加锁 来解决**
> 
> 引入读加锁后，并发程度又进一步降低了（效率降低了）
> 
> 隔离性又提高了（数据准确性也提高了）

**（3）幻读（结构集发生改变，解决方法：串行化）**

> 对写操作加锁，并且对读加锁，此时如果读文件A时，去改文件B（不影响另一个事务读）
> 
> 虽然这样操作，对另一个事务读取的数据不影响，但是，两次如果读完后，发现结构集变了
> 
> （另一个事务读取时第一次看到只有一个.java文件，下次读的时候突然发现变为两个.java文件了）
> 
> 这种情况称为**“幻读”**
> 
> **为了解决幻读问题，方法就是串行化**
> 
> **也就是另一个事务在读时，我不能进行代码操作**

### 2.4 MySQL的隔离级别 ###

>  **MySQL提供了“隔离级别”选项，四个选项**
> 
> **1.read uncommitted** 
> 
> 允许读未提交的数据，并发程度最高，隔离性最低。可能存在脏读/不可重复读/幻读问题
> 
> **2.read committed** 
> 
> 只能读提交之后的数据，相当于 写加锁，并发程度降低，隔离性提高
> 
> 解决了脏读，可能存在 不可重复读/幻读问题
> 
> **3.repeatable read(默认)**
> 
> 相当于读和写都加锁了，并发程度再降低，隔离性再提高，
> 
> 解决了脏读/不可重复读，可能存在幻读问题
> 
> **4.serializable**
> 
> 严格执行串行话，并发程度最低，隔离性最高
> 
> 解决了 脏读/不可重复读/幻读问题，但效率最低
> 
> **在MySQL的配置文件 my.ini 中进行设置**
> 
> **根据不同的需求场景，就可以分别设置不同的档位了**

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

## 3. 本篇需理解这几点 ##

**1. 如何理解索引**

> **从这三个方面思考**
> 
> **（1）索引是干啥的（解决了什么问题）**
> 
> **索引相当于目录。提高查询效率**
> 
> **（2）索引付出的代价**
> 
> **索引会占用额外的磁盘空间；**
> 
> **虽然提高了查找效率，但是降低了增 删 改的效率**
> 
> **（3）索引背后的数据结构**
> 
> **B+树（可以思考一下B树和B+树的区别）**

**2. 如何理解事务**

> **从这三个方便思考**
> 
> **（1）事务是干啥的（从原子性切入）**
> 
> **事务包含一个或多个业务操作，这些操作要么都执行，要么都不执行。事务常被用来确保数据的一致性。（可联系原子性展开说明，并解释“回滚”）**
> 
> **（2）事务的其他特征还要啥**
> 
> **原子性；持久性；一致性；隔离性；（可展开思考一下）**
> 
> **（3）重点隔离性，在并发执行事务下会有哪些问题，以如何解决**
> 
> **脏读（读取到脏数据，解决方法：对写操作加锁）**
> 
> **不可重复读 （读的过程被修改，解决方法：使用读加锁）**
> 
> **幻读（结构集发生改变，解决方法：串行化） （可详细展开思考）**

**3. MySQL 隔离级别都有什么**

> **1.read uncommitted** 
> 
> **2.read committed** 
> 
> **3.repeatable read(默认)**
> 
> **4.serializable**
> 
> **从上到下，并发程度逐渐降低，隔离性增高（可从脏读/不可重复读/幻读进行分析）**

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

[3c18b5e883d44e5b8680cc7b83910dcd.png]: https://img-blog.csdnimg.cn/3c18b5e883d44e5b8680cc7b83910dcd.png
[752d0c192e6640a19a4fcc48981478e9.png]: https://img-blog.csdnimg.cn/752d0c192e6640a19a4fcc48981478e9.png
[8e968864866f42619e0aee74456c7431.png]: https://img-blog.csdnimg.cn/8e968864866f42619e0aee74456c7431.png
[c45db17659384b3c91eb48e130c3dbcc.png]: https://img-blog.csdnimg.cn/c45db17659384b3c91eb48e130c3dbcc.png
[c7edb05c609047eb9a97513b5b121ed3.png]: https://img-blog.csdnimg.cn/c7edb05c609047eb9a97513b5b121ed3.png