MySQL索引事务

MySQL索引事务

1.索引

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

1.1 作用

1. 数据库中表,数据,索引之间的关系,类似于书架上的图书,书籍内容,书籍目录的关系.
2. 索引的作用类似于书籍目录,可以快速定位,检索数据.

1.2 底层结构(基础)

索引的本质是利用一些更加复杂的数据结构实现的,通过这种数据结构把待查询的记录给组织起来,从而能够加快查询的速度.

在查找效率高的数据结构中有二叉树(红黑树,B+树),哈希表.那是哪一个呢?

对于红黑树,基本结构为二叉树,如果元素的数量多了,树的高度就会变高,如果查找数据越深,就会导致在查询时的比较次数增多,由于数据库每次访问时需要访问磁盘,但是硬盘的读取时间远远大于数据处理时间，数据库读取硬盘的次数越少越好。比较次数多了,就会拖慢速度.

对于哈希表,他的查找时间复杂度时0(1),但是只能查相等的情况,对于范围查找(< , > )或者模糊搜索等,就无能为力.

最终答案是B+树,我们来分析B+树的结构特点及其优势.

B+树是由B树延申出的,而B树是一颗N叉树,它的设计思想是，将相关数据尽量集中在一起，以便一次读取多个数据，减少硬盘操作次数。

B树特点:

1. 一个结点可以容纳多个值,比如上图,最多的一个结点容纳4个值
2. 除非数据已经填满，否则不会增加新的层。也就是说，B树追求"层"越少越好.
3. 子节点中的值，与父节点中的值，有严格的大小对应关系。一般来说，如果父节点有a个值，那么就有a+1个子节点。比如上图中，父节点有两个值（7和16），就对应三个子节点，第一个子节点都是小于7的值，最后一个子节点都是大于16的值，中间的子节点就是7和16之间的值。

其查找的时间复杂度为log(N)N.

• B+树的结构

在B树的基础上增加几个条件

1. 每个结点上面存了几个值,最多就有几个子树.
2. 父节点的元素都存在于子节点中,是子节点的中的最大值(最小值).
3. 最下方的叶子节点使用链表的方式相连

优势:

1. 查询速度快.
2. 单个节点存放了更多的数据,树的高度比较低,所有比较次数就会大大降低.
3. 所有的叶子节点使用链表相连,非常便于范围查找(因为叶子节点包含了数据库的数据全局).
4. 每一个数据行,只需要保存在叶子节点上,非叶子节点不必存储真正的数据行,只需存储做索引的id即可,此时叶子节点占用空间小,就有了在内存中缓存非叶子节点的可能.

1.3 使用场景

索引的使用场景:

1. 数据量大,经常需要查询,但对经常需要插入,修改操作少.
2. 对空间不敏感,对于时间更敏感.

1.4 使用

创建主键约束（PRIMARY KEY),唯一约束（UNIQUE),外键约束(FOREIGN KEY)时,会自动创建对应列的索引

• 查看索引

show index from 表名;

• 创建索引

create index 索引名 on 表名(列名)

案例：创建学生表中，name字段的索引

• 删除索引

案例：删除学生表中name字段的索引

drop index 索引名 on 表名;

2. 事务

2.1 事务的场景

案例:

-- 测试表
 create table accout(id int, name varchar, balance int);
-- 插入数据
 insert into accout values(1, '张三', 1000), (2, '李四', 2000);

现在张三向李四转账500元

-- sql语句
update accout set balance = balance - 500 where name = '张三';
update accout set balance = balance + 500 where name = '李四';

如何在执行完第一句sql后,数据库出问题了,那么张三少了500,但是李四却没有增加500.

那么如何在遇到这种突发情况去处理呢?

解决方案:要么两个都执行完,要么一个都不执行

注:一个都不执行不是真没有执行,而是通过恢复的方式,把之前操作的影响给还原了,而这个过程称为’回滚’.

那么数据库的事务回滚是如何做到的呢?

数据库中的每个操作,在内部都有记录,如果事务中间出现问题,就可以根据之前的记录,进行处理.

2.2 事务的使用

1) 开启事务
start transaction;
2) 执行sql语句
3) 回滚或提交
rollback/commit;
注: rollback既是全部失败,commit即是全部成功

于是上面的转账sql就可以这样写

start transaction;
update accout set balance = balance - 500 where name = '张三';
update accout set balance = balance + 500 where name = '李四';
commit;

2.3 事务的基本特征

1. Atomic原子性：原子性要求所有语句要么全部成功，要么全部不成功，不能部分成功
2. Consistency一致性：规定了事物提交前后，永远只可能存在事物提交前的状态和事物提交后的状态，从一个一致性的状态到另一个一致性状态，而不可能出现中间的过程态。
3. Durability持久性：当一个事物提交之后，数据库状态永远的发生了改变。数据已经更新到硬盘。
4. Isolation隔离性：事物的隔离性，基于原子性和一致性，因为事物是原子化，量子化的，所以，事物可以有多个原子包的形式并发执行，但是，每个事物互不干扰。

为了解决并发执行事务带来的问题, MySQL等数据库引入了"隔离级别"，可以让用户自行选择一个适合自己当前业务场景的级别,那么并发执行事务时所产生的问题有哪些?

1. 脏读问题.(读脏数据)

一个事务A,在执行过程中，对数据进行了一系列修改,在提交到数据库之前(完成事务之前)，另一个事务B,读取了对应的数据,此时这个B读到的数据都是一些临时结果,后续可能马上就被A给改了.此时B的读取行为就是"脏读".

举个例子:

我在考试时,正在答题,后面的同学在我旁边,偷偷观察,然后把"答案"给拿走了,后面我发现这个题答案是不正确的,我又重新去修改了,此时这个同学看的"答案"就是’脏数据’,观察的过程就是’脏读’.那如何去解决,我在考试之前与同学约定在某个时间段,我检查好了,你可以’观察’.

这个解决过程是给读操作加锁,相当于降低并发程度,降低了效率,提高了隔离性

2. 不可重复读

事务A提交了之后,事务B才开始读(读是加锁了)然后在B的执行过程中,A又开启了一次,修改了数据.此时B执行中,两次读取操作可能结果就不一致.

举个例子:

把上面例子修改一下, 约定好时间后同学依旧观察完了,我又重新修改了答案,导致最终答案不一致.如何解决: 在约定之前不要看(之前加的锁),你们在观察时,我也不会改答案.

所以提高了隔离性,降低了并发性,数据更加准确,降低了效率.

3. 幻读

事务B读取过程中,事务A进行了修改，没有直接修改B读取的数据，但是影响到了B读取的结果集事务B两次读取到的结果集不一样.
这个就是“幻读”幻读相当于是不可重复读的特殊情况.

举个例子:

约定在同学观察时我不改,于是我去改选择题的答案,导致答案不同,如何解决:严格要求在同学’观察’时我不能做如何写操作,可以去后面看题,从而保证读和写的操作都是严格串行执行的(一个执行完,才能执行另一个)

此时隔离性最高,并发性最低,数据准确性最好,但是速度最慢.

MySQL里给我们提供了四个档位,供我们自由选择

限制级别	作用
read uncommitted	并发能力最强,隔离性最弱.
read committed	只能读取提交之后的数据,解决了脏读问题.并发能力下降一些,隔离性增加了一些.
repeatable read	针对读和写都进行限制了，解决了不可重复读问题.并发能力由进一步下降,隔离性进一步增加.
serializable	严格的串行执行,解决了幻读问题.并发能力最低,隔离性最高,但是速度慢