MySQL中索引与事务内容总结

文章目录👀

1️⃣一、索引介绍

1、索引的优缺点

二、索引分类

三、索引的创建与删除

1、在创建表时创建索引

2、在已经存在的表上创建索引

第一种：通过create语法创建索引

第二种：通过alter语法创建

3、删除索引

2️⃣四、事务定义

五、事务特征（ACID）

事务的原子性（Atomic）

事务的一致性（Consistency）

事务的隔离性（Isolation）

事务的持久性（Durability）

六、事务的使用

1、查看事务

2、修改事务

3、事务操作

(1)开启事务

(2)事务提交

(3)事务回滚

(4)保存点

七、并发事务问题

1、脏数据（Dirty READ）

2、不可重复读（UnrepeatableRead）

3、幻读（phantom Read）

---

1️⃣一、索引介绍

索引是一种数据结构，是对数据库表中的一列或多列的值进行排序的一种结构。【索引就类似于书中的目录】

1、索引的优缺点

在数据库中创建索引的优点：

① 加快查询速度，避免全表扫描

② 保证数据的唯一性；

③ 使用group by、order by子句进行查询时，利用索引可以减少排序和分组的时间。

缺点：

• 索引并非越多越好，过多的索引会导致CPU的使用率居高不小
• 索引会占据磁盘空间
• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

设计索引时，应考虑以下数据库准则：

（1）一个表如果建有大量索引，会影响insert、update和delete语句的性能。

（2）避免对经常更新的表创建过多的索引。

（3）使用多个索引可以提高更新少而数据量大的查询的性能。

（4）对小表进行索引可能不会产生优化效果；因为对于非常小的表而言，大部分情况下简单的全表扫描更高效。

二、索引分类

普通索引：没有任何限制条件，可以给任何类型的字段创建普通索引

唯一性索引：使用unique修饰的字段，索引列中的值必须是唯一的，且不能重复，但是允许为空值。

主键索引：使用primary key修饰的字段，索引列中的值必须是唯一的，不允许有空值。

单列索引：在一个字段上创建索引

多列索引：在表中多个字段上创建索引

全文索引：使用fulltext参数设置全文索引，只能在char、varchar和text类型的字段上创建全文索引，常用于数据量较大的字符串类型上，可以提高查询速度，MyISAM存储引擎支持全文索引，InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。

聚集性索引：在InNoDB引擎下建立的索引，将索引及数据存放在一起

非聚集性索引：在MyISAM引擎下建立的索引，将索引和数据分开存储在不同的文件中

三、索引的创建与删除

1、在创建表时创建索引

create table 表名(
 
属性名 属性类型 [完整性约束]
 
[unqiue|fulltext|spatial] index|key [别名] 属性名 （asc,desc）
);

说明：

unqiue|fulltext|spatial都是可选参数：

• unqiue表示索引为唯一性索引
• fulltext表示全文索引
• spatial表示空间索引
• 如果不写，就默认是普通索引

index|key：用来创建索引，index与key作用相同

别名：可以给自己所创建的索引起一个别名，可以是任意的字段

属性名：需要创建索引的属性的名称，该属性必须是已经存在的，如果不存在，需要先进行创建。

asc|desc是可选参数，asc:表示升序排列 ，desc：表示降序排列

示例如下：

创建普通索引

create table index1 (
id int ,
name varchar(20),
sex  varchar(2),
index (name) 
);

创建唯一性索引

create table index2(
id int ,
name varchar(20),
sex  varchar(2),
unique index idx_id (id) 
);

2、在已经存在的表上创建索引

第一种：通过create语法创建索引

格式：
create [unqiue|fulltext|spatial] index 索引名 on 表名 (属性[asc|desc])
 

第二种：通过alter语法创建

格式：
alter table 表名 add [unqiue|fulltext|spatial] index 索引名(属性名 [asc|desc])

3、删除索引

drop index 索引名 on 表名;

2️⃣四、事务定义

事务：由一条或多条对数据库操作的SQL语句组成的一个不可分割的单元，只有当事务中所有的操作都正常执行完了，整个事务才会被提交到数据库，如果部分事务处理失败，那么事务就要回滚到最初的状态，因此，事务要么全部执行成功，要么全部失败。

经典案例：

银行转账：当两个人发起转账业务时，如果A转账发起，而B因为一些原因不能成功接受，事务最终将不会提交，则A和B的请求最终不会成功。

五、事务特征（ACID）

事务的原子性（Atomic）

事务是一个不可分割的整体，事务必须具有原子特性，事务中的操作要么全都执行，要么全都不执行，不允许部分执行。

事务的一致性（Consistency）

事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库的数据都必须处于一致性状态。

数据库的一致性状态由用户来控制，并发控制机制实现。

事务的隔离性（Isolation）

当两个或者多个事务并发执行时，为了保证数据的安全性，将一个事务内部的操作与其他事务的操作隔离起来，不能被其他正在执行的事务所看到。

隔离性使每个事务的更新操作之前，对其他事务是不可见的。

事务的持久性（Durability）

事务执行完成之后，它对数据库中数据的修改是永久性的，即使数据因为故障出错，也应该能够恢复数据。

六、事务的使用

1、查看事务

select @@autocommit；

查看MySQL中事务是否打开自动提交：

默认为1表示开启自动提交， 0 表示手动提交。

2、修改事务

set autocommit=0；

set autocommit=X 操作，设置事务提交方式为手动提交还是自动提交

注意：设置事务手动还是自动只针对当前的窗口、当前的用户连接起作用，其他的连接是不受影响

3、事务操作

(1)开启事务

begin；

(2)事务提交

commit ;

当组成一个事务的多个SQL都执行成功，调用commit提交一个事务，意味着数据被写入到数据行中，可被其他的事务读取到。

(3)事务回滚

rollback ;

如果事务执行的过程中有操作执行失败就会回滚到初始的状态，begin之前的状态

(4)保存点

savepoint point1; //设置一个名称为point1的保存点

rollback to point1; //回滚到指定的保存点point1,而不是回滚到初始状态

七、并发事务问题

在多个事务并发运行时，会遇到操作相同的数据来完成各自任务，可能会存在以下问题：

1、脏数据（Dirty READ）

当一个事务正在访问数据并且对数据进行修改，而这个操作还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了该数据，因为数据还没有提交到数据，那么另一个事务读到的这个数据就是脏数据，依据脏数据所做的操作是不正确的。

如果A事务读取到B事务尚未提交的更改数据，并在这个脏数据的基础上进行的操作，如果B事务进行回滚，A事务读取到的事务是不被承认的。

2、不可重复读（UnrepeatableRead）

A事务内多次读取同一个数据，在这个事务还没结束时，B事务也访问该数据，A事务在两次读数据之间，B事务修改了该数据，A事务再次读取数据可能和第一次读取的不太一样。一个事务内两次读取的数据是不一样的，因此称为不可重复读。

一个事务内两次读取同一个数据的结果不一样。不可重复读指A事务读取到了B事务已经提交的更改数据

3、幻读（phantom Read）

A事务读取几行数据，接着B事务插入了一些数据，在随后的查询中，A事务会发现一些原来不存在的记录，好像发生了幻觉一样，所以称为幻读。

不可重读读和幻读区别：

不可重读一般针对行级别的数据的更改，重点是修改数据；

幻读一般针对表级别的新增/删除数据，在统计事务中，两次读取的数据统计不一样，幻读的重点在于新增或者删除。