MySQL索引事务-CSDN博客

本文链接：https://blog.csdn.net/weixin_43497876/article/details/136065855

索引

1.1 概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引。

1.2 作用

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。

索引所起的作用类似书籍目录，可用于快速定位、检索数据。

索引对于提高数据库的性能有很大的帮助。

加快查的速度，降低增删改的速度。（对于插入、删除数据频率高的，不适用索引）

也会提高空间的开销

1.3 使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

1. 数据量较大，且经常对这些列进行条件查询。

2.该数据库表的插入操作，及对这些列的修改操作频率较低。

3. 索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。

反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

1.4 使用

查看索引

查看学生表已有的索引

针对学生表来说前面并没有创建索引，只是有主键因此自动创建的索引。

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

创建索引

最好是在建表之初就把索引建好！！

创建索引之后不需要手动使用，查询的时候会自动走索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

案例：创建班级表中，name字段的索引

有的时候我们会根据名字查询，因此可以根据这一列创建索引

删除索引

和上述的创建索引类似，可能也会吃大量的硬盘 IO

案例：删除班级表中name字段的索引

1.5 索引在MySQL中的数据结构

核心的数据结构是B+树（大概率，尤其是带主键的表）

1. B+ 树是一个N叉搜索树，每个节点包含N个key，划分出N个区间，最后一个key则是最大值

2. 父元素的key会在子元素中出现，并且是以最大值的方式

这样的重复出现，会让非叶子节点的所有值在叶子节点中都体现出来

3. 叶子节点之间会像链表一样首尾相连

上述B+树的好处

1. 跟二叉树相比，高度降低了，比较的时候硬盘IO次数减少（提高查询速度，本质就是减少硬盘IO次数）

2. 所有的查询都是落到叶子节点上的，因此无论查询哪个元素查询次数都比较均衡。对于B树而言，有的在根节点上就查的快，在叶子节点上就慢

3. 更适合范围查询

4. 由于所有的key都会在叶子节点中体现，因此非叶子节点不必存表的数据行，只需要存索引列的值即可（例如id）。这也就意味着非叶子节点占用的空间是大大降低的，更可能在内存中可以放进去缓存，进一步降低硬盘IO

对于带有主键的表，就是按照B+树来组织的。但是有些表，不只是主键索引，还有别的非主键列也有索引，这种情况会构造新的B+树，非叶子节点就会存这一列里面的key(比如学生姓名)，到了叶子节点这一层则不是存完整的数据行了，而是存主键id。

使用主键列来查询，只查一次B+树即可，如果是使用非主键的索引查询，则需要先查一遍索引列的B+树，再差一遍主键列的B+树（称为“回表”）。

当前B+树这个结构只是针对MySQL的InnoDB这个数据库引擎

事务

2.1 为什么使用事务

比如说，转账操作

account（id， balance）

1 100

2 100

让 1 给 2 转50，需要两个操作

1）1的余额 - 50

2）2的余额 + 50

假如在执行以上第一句 SQL 时，出现网络错误，或是数据库挂掉了，出现 1 的余额是50，2的余额是100，这显然是不科学的，因此就要使用到“事务”

2.2 事务的概念

事务就是把多个SQL打包成一个整体，要么全部执行完，要么一个都不执行。如果执行到中间出错了，就会进行“回滚”(rollback)操作，类似电脑的ctrl + z。（数据库会把执行的每个操作记录下来，如果某个操作出错，就会进行逆操作恢复如初）

数据库事务的四大特性

1.原子性，即打包成一个整体这个操作（最最最核心的特性，也是事务的初心）

2.一致性，事务执行前/执行后都得是合法的状态。即不能出现上述转账钱丢了的情况

3.持久性，事务产生的修改都是会写入硬盘的。即使程序重启/断电(有电的时候就会进行回滚，因为中途断电已经不正常状态了)，事务都能正常工作

4.隔离性，一个数据库服务器，同时执行多个事务的时候，事务之间的“相互影响程度”

隔离性越高，意味着事务之间的并发程度越低，执行效率越慢，但数据准确性越高

隔离性越低，意味着事务之间的并发程度越高，执行效率越快，但数据准确性越低

背景：现在我是一名正在备课的老师

这时候有个同学跑进我的办公室交作业，突然他发现我正在敲课堂代码，发现我写了个class_student，他就回去跟班上的同学说，可是我突然不想要这个案例了，就把class_student给删了，这时候这位同学读取的就是一个有问题的数据。也称作“ 脏读”，在这个场景中，我写数据和同学读数据这俩事务是并发的，没有限制。为了解决这一问题，我们可以给写这个操作“加锁”，这时候我写数据同学就不能读，只有当我写完了同学才能读到(上传到码云)。

同学在码云浏览代码的时候，我突然想到有个地方的代码有误，我连忙进行了修改，这时候同学看到的数据跟上一秒的不一样了。这种情况叫做“ 不可重复读”，在一个事务中，连续两次读到的结果不一样。为了解决这一问题，我们就可以给读这个操作“加锁”，这时候同学读数据的时候我就不能去修改数据。

(脏读和不可重复读都是针对同一个代码文件而言的)

这时候我觉得闲着也是闲着，就新建/删除了个文件，想着做别的事情，这时候虽然同学看的代码没有变化，但是惊讶的发现文件数量变了。这种情况称为“ 幻读”，在一个事务中，连续两次读到的结果集不同。为了解决这一问题，我们就可以串行化(像一条线)，彻底舍弃并发，即同学在读的时候我什么都不能干。

MySQL的四个隔离级别
读未提交	不做任何限制,事务之间是随意并发的.并发程度最高,隔离性最低.会产生脏读+不可重复读+幻读.执行速度最快.
读已提交	对写操作进行了加锁他,并发程度降低了,隔离性提高了.解决了脏读问题,仍然存在不可重复读+幻读.
可重复读	对写和读都加了锁.并发程度再次降低,隔离性再次提高.解决了脏读+不可重复读,仍然存在幻读问题.（MySQL默认挡位）
串行化	严格串行化.并发程度最低(因为是串行执行的),隔离性最高.解决了脏读+不可重复读+幻读问题.执行速度最慢.