MySQL事务和索引

约定不等于承诺〃 2023-09-27 17:52 22阅读 0赞

**目录**

事务的概念

事务的四大特性（ACID）

原子性

隔离性

持久性

一致性

什么是脏读、幻读和不可重复读？

脏读

幻读

不可重复读

事务的隔离级别

读未提交

读已提交

可重复读

串行化

索引概念

索引优点

索引缺点

索引分类

索引设计原则

1、选择唯一性索引

2、为常作为查询条件的字段建立索引

3、为经常需要排序、分组和联合操作的字段建立索引

4、限制索引的数目

5、小表不建议索引（如数量级在百万以内）

6、尽量使用数据量少的索引

7、删除不再使用或者很少使用的索引

索引失效场景

索引底层的数据结构

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

## 事务的概念 ##

事务是数据库一个不可分割的操作序列，也是数据库并发控制的基本单元。

事务是逻辑上的一组操作，在这操作上的单元要么全都成功，要么全都不成功。

> 事务的典型场景：银行转账
> 
> 小A到银行给小B转账100元，如果转账成功的话，第一个操作是让小A账户减少100元，第二个操作是让小B账户增加100元；如果因为某些原因导致第二个操作失败的话，此时就需要撤销第一个操作成功的指令。
> 
> 这就是事务上的操作单元要么都成功，要么都不成功

##  ##

## 事务的四大特性（ACID） ##

### 原子性 ###

事务是数据操作的最小单元，是不可分割的。事务的原子性保证了事务的操作要么都成功，要么都不成功

### 隔离性 ###

并发访问数据库，一个事务的操作不影响另一个事务的操作，事务的操作是相互独立的，互不影响

### 持久性 ###

一个事务提交之后，对数据库的操作是持久的，即使数据库发生故障也不会对事务产生影响

### 一致性 ###

事务执行前后，数据都是一致的，多个事务对同一个数据的读取都是一致的

## 什么是脏读、幻读和不可重复读？ ##

### 脏读 ###

一个事务A读到一个事务B还未提交的数据

### 幻读 ###

一个事务两次读取的数据量不一样

> 一个事务A多次读取同一个数据时，事务B在事务A读取期间对数据进行增加或者删除操作，导致事务A读取的数据量不一样

### 不可重复读 ###

一个事务两次读取的数据内容不一样

> 一个事务A在多次读取同一个数据时，事务B在事务A读取期间对数据进行更新提交，导致事务A多次读取同一数据时，读取的结果不一样

**注意点：**

> 1、幻读侧重于数据的**增加**或**删除**，读取的**数据行数不一样**
> 
> 2、不可重复读侧重于数据的**修改**，读取的**数据内容不一样**
> 
> 3、解决幻读的问题：**锁定整个表不被修改**
> 
> 解决不可重复读的问题：**锁定读取数据的这一行**

## 事务的隔离级别 ##

### 读未提交 ###

该隔离级别的事务可以看到其他事务中未提交的数据

因此该隔离级别下会产生脏读问题

### 读已提交 ###

该隔离级别的事务能读取到已经提交事务的数据，因此它不会有脏读问题。但由于在事务的执行中可以读取到其他事务提交的结果，所以在不同时间的相同 SQL 查询中，可能会得到不同的结果，因此该隔离级别下会产生不可重复读问题

### 可重复读 ###

该隔离级别可以解决“不可重复读”的问题，但还存在幻读的问题。

**可重复读隔离级别为MySQL默认的隔离级别**

### 串行化 ###

序列化，事务最高隔离级别，它会强制事务排序，使之不会发生冲突，从而解决了脏读、不可重复读和幻读问题，但因为**执行效率低，所以真正使用的场景并不多**。

**总结**

1、事务的隔离级别就是为了解决脏读、幻读和不可重复读的问题

2、读未提交的隔离级别最低，串行化的隔离级别最高

<table style="width:600px;"> 
 <tbody> 
  <tr> 
   <td style="width:265px;"><strong>事务隔离级别</strong></td> 
   <td style="width:110px;">脏读</td> 
   <td style="width:116px;">不可重复读</td> 
   <td style="width:108px;">幻读</td> 
  </tr> 
  <tr> 
   <td style="width:265px;">读未提交（READ UNCOMMITTED）</td> 
   <td style="width:110px;">√</td> 
   <td style="width:116px;">√</td> 
   <td style="width:108px;">√</td> 
  </tr> 
  <tr> 
   <td style="width:265px;">读已提交（READ COMMITTED）</td> 
   <td style="width:110px;">×</td> 
   <td style="width:116px;">√</td> 
   <td style="width:108px;">√</td> 
  </tr> 
  <tr> 
   <td style="width:265px;">可重复读（REPEATABLE READ）</td> 
   <td style="width:110px;">×</td> 
   <td style="width:116px;">×</td> 
   <td style="width:108px;">√</td> 
  </tr> 
  <tr> 
   <td style="width:265px;">串行化（SERIALIZABLE）</td> 
   <td style="width:110px;">×</td> 
   <td style="width:116px;">×</td> 
   <td style="width:108px;">×</td> 
  </tr> 
 </tbody> 
</table>

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

## 索引概念 ##

索引是对数据库表中一列或者多列数据进行排序的数据结构，就类似于一本书的目录，可以帮助我们更快的查询数据

索引本身也很大，不可能全部存储在内存中，因此**索引往往是存储在磁盘上的文件中的**

### 索引优点 ###

1、提高了数据查询的速度。

2、**索引列对数据进行自动排序**，降低数据排序的成本，降低了CPU的消耗。

### 索引缺点 ###

1、索引会占据磁盘空间。

2、**降低更新表的效率**。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

## 索引分类 ##

1）从**物理结构**上可以分为聚集索引和非聚集索引两类：

*  **聚簇索引**指索引键值的逻辑顺序与表中相应行的物理顺序一致，即每张表只能有一个聚簇索引（**一般情况下聚簇索引等于主键索引，但聚簇索引又不完全等于主键索引，因为如果一张表中没有主键索引，那么聚簇索引会使用第一个唯一索引（此列必须为 not null），如果以上情况都不满足，那么 InnoDB 会生成一个隐藏的聚簇索引。**）
 *  **非聚簇索引**的逻辑顺序则与数据行的物理顺序不一致。

2）从**应用**上可以划分为一下几类：

**主键索引**：索引列中的值必须是唯一的，不允许有空值

**普通索引**：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。

**唯一索引**：索引列中的值必须是唯一的，但是允许为空值。

**全文索引**：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。 MyISAM和InnoDB中都可以使用全文索引。

**前缀索引**：在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

3）按照索引列数量分类

1.  **单列索引**
2.  **组合索引**
    
    组合索引的使用，需要遵循**最左前缀匹配原则（最左匹配原则）**。一般情况下在条件允许的情况下使用组合索引替代多个单列索引使用。

## 索引设计原则 ##

#### 1、选择唯一性索引 ####

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

#### **2、为常作为查询条件的字段建立索引** ####