[MySQL数据库] 索引与事务

1. 索引

1.1 概念

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

1.2 作用

• 数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
• 索引所起的作用类似书籍目录，可用于快速定位、检索数据。
• 索引对于提高数据库的性能有很大的帮助。

注意:引入索引可以提高数据库的查询速度,但是也会有一定的缺点:

1. 目录本身也要占据一定的储存空间(一般是占据硬盘)
2. 索引会拖慢增删改的速度,因为后续在对数据库更新的时候,也要同步更新索引.

我们举个例子来说明:
有请助教:凯亚,丽莎
最近凯亚在跟着丽莎学药物炼制,凯亚要想在丽莎这里通过考核,必须写出出色的药物炼制论文,一篇优秀的长篇学术论文是不可能没有目录的.凯亚费尽九牛二虎之力,终于把内容和目录全部写完了,但是有一天,凯亚发现,根据丽莎的要求,他少写了一种药物的相关内容.凯亚此时崩溃了,让他崩溃的不是正文内容,而是目录,前面目录的序号和页码标的好好的,结果要在中间插入内容,序号和页码全乱套了.

1.3 使用场景

要考虑对数据库表的某列或者某几列创建索引,要考虑一下几点:

• 数据量较大,要经常对这些列进行条件查询.
• 该数据库对数据的修改频率较低.
• 索引会占用额外的空间.

1.4 使用

创建主键约束,唯一约束,外键约束时,会自动创建对应的索引.
原因如下:针对主键会有大量的查询行为,所以会自动创建.在往一个带有外键约束的表中添加元素的时候,外键约束会使得子表触发查询,要在父表中查询是否存在对应元素,查询操作也比较频繁.

• 查看索引:
  show index from 表名
  show index from student;
  
  我们看到,由于student的id一列为主键,id一列就默认有索引存在.
• 创建索引:
  对于非主键、非唯一约束、非外键的字段，可以创建普通索引
  create index 索引名 on 表名(列名)
  create index name_index on student(name);
  
  我们看到,name一列的索引被创建了.
• 删除索引:
  drop index 索引名 on 表名
  drop index name_index on student;
  
  我们看到,name一列的索引被删除了.

索引底层的搜索原理使用B+树,我们后续在数据库底层容器原理中介绍.

2.事务

2.1 为什么使用事务

现在我们想象一个场景
有请助教:迪卢克
今天迪卢克由于晨曦酒馆有事情要忙,来不及自己做饭了,他便在手机上点外卖,在手机上付款之后,却迟迟没有等到餐送来,于是,迪卢克姥爷便给蒙德餐馆打电话,但是它们说它们并没有收到订单,餐馆的账户上也没有相应金额的收入.调查清楚了事情的原委,原来是晨曦酒馆地位偏僻,在付款的一瞬间,导致网络中断,虽然在迪卢克姥爷的账户上扣除了相应的摩拉,但是蒙德餐馆那边一点反应都没有,这是我们所不想看到的情况.我们想要的情况是,要不付款不成功,餐馆的账户上也没有相应的收入,要不就是付款成功,迪卢克的账户上扣除金额,餐馆的账户上增加金额并收到订单.所以我们引入了事务来避免这种情况的发生.

2.2 事务的概念

事务指逻辑上的一组操作，就是把多个操作打包为一个操作,组成这组操作的各个单元，要么全部成功，要么全部失败。在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务,数据库事务可以有效避免部分执行,部分未执行的中间状态.

2.3 事务的特性

1. 原子性:事务的核心特性,把多个操作打包为一个整体称为"原子性".
   数据库事务的原子性,核心是通过"回滚"机制来保证.
   [那么何为回滚机制?]
   在我们执行数据库的事务的时候,数据库会在硬盘中记录日志,如果在执行事务的中间,出现了bug(网络错误,主机断电,程序崩溃,系统崩溃),MySQL可以根据日志来进行恢复,这便实现了原子性.

2. 一致性:是对数据库回滚内容与日志统一的保证,就是发明MySQL的这个公司,给了我们一个保证,假如数据丢失,或者出现了bug,一定可以通过日志回滚回来,并且不会有任何差错.(关系型数据库支持)

3. 持久性:执行事务对数据库的修改,就会在硬盘上永久保存(相反在内存中保存的不持久),重启之后仍然存在.

4. 隔离性: 描述的是数据在并发执行事务的时候产生的情况.
   比如:可能多个客户端正好就把事务赶到一块了,就需要服务器给出处理.
   如果一起处理,又会出现问题:(这里对应的是Java EE板块中的线程安全问题,可自行查看)

• 脏读(未加以任何限制)
  数据库中有事务A,B,在A提交之前B就读,杜处的就是未处理好的数据.
  
  那么如何解决上述问题呢?我们通过写加锁的操作,在A提交事务之前,B不可读取数据.
  

但是还是会有问题

• 不可重复读
  假如存在A,B,C三个事务,事务A对数据修改,提交之后B读取,在执行B的过程中,事务C对数据进行修改,导致B事务前后读到的数据不一致.

如何解决呢,我们进行读加锁,在B事务读取的时候,不可对A事务进行任何修改.

但还是会有一定的问题

• 幻读
  事务A先修改并提交数据,事务B对数据进行读取,此时C没有对A的数据直接进行修改,而是给对应的表进行新增,修改,使得数据改变.
  解决:串行化执行,使得所有的事物都按照一个接一个的方式执行,完全不并发,使得隔离性达到最高.也就是,从脏读,到串行化,事务的隔离性在不断增加,并发性在不断降低.

我们举个例子来说明:
有请助教:丽莎,班尼特
丽莎在蒙德是个成绩非常好的学徒,班尼特由于平时热爱冒险,完全没有把学习这件事放在心上,在一次考核的时候,它们坐在了一起…

1. 班尼特什么都不会,他就决定使用斜眼法偷看丽莎的卷子,此时丽莎正在写一道解答题,班尼特就记住了丽莎解答题上的其中两三行,就写在了自己的试卷上.但是丽莎写了一半的时候,觉得不对劲,算错了,就从头到尾修改了这道题的解答,此时班尼特就抄到的是丽莎错误的答案.在丽莎还正在写这道题,并且没有正式确定答案时去偷瞄,此时班尼特就在脏读.
2. 班尼特又偷瞄到了丽莎的一道填空题,答案是4,班尼特非常高兴,就抄了上去,但是,丽莎灵光一炸,好像前面这道填空题写错了,就修改了这道填空题的答案为5.丽莎在之前正式写完了一道填空题的答案,班尼特去偷瞄,但是最后丽莎又进行了修改,此时班尼特就在不可重复读.
3. 班尼特又偷瞄了丽莎的一道已经完成的证明题,班尼特就原封不动的把丽莎的这道证明题抄了上去,但是最后丽莎发现,证明的中间少了几步,于是就加上了几步证明,但是班尼特最后没有随着丽莎加上这几步,此时班尼特就在幻读.
   班尼特以为自己这次一定可以及格的,结果…
   

2.4 事务的使用

1. 开启事务
   start transaction;
2. 执行多条sql语句
3. 回滚或提交
   rollback/commit

说明：rollback即是全部失败，commit即是全部成功.

start transaction;
update account set money=money-20 where name='迪卢克';
update account set money=money+20 where name='蒙德餐馆';
commit;