MySQL索引和事务

索引

概念与作用:

索引就是相当于一本书的目录(index),他能够根据目录中每个章节对应的页码,就能够快速找到对应的文章章节,MySQL 的索引也是一样的,创建不同的类型,当从数据库中进行查找的时候,就可以快速定位,检索数据,对于提高数据库的性能有很大的帮助,就不用遍历查找.

缺点:

当然书的目录是要费纸的,数据库的索引相对就要消耗一定的存储空间,数据量越大,索引消耗的存储空间就越多,书的目录确定了,后续如果想调整书的内容,都可能会影响到目录的准确性,就需要重新调整目录,数据库的索引也是如此,当我们进行增删改的时候,往往也是需要同步的修改索引的结构

总的来说

,索引带来的好处就是提高了查找的速度,坏处就是占用了更多的空间,拖慢了增删改的速度,但是,往往在实际开发中,mysql里的数据量往往都是达到了千万级别的了,查找操作往往又是最高频的操作,如果我们去遍历表,就会非常低效,而增删改许久可能才会改动一次,所以,虽然看起来索引的坏处看起来更多,但是他雀氏是一个好东西…

使用

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

查看索引

show index from 表名;

创建索引

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

create index 索引名 on 表名(字段名);

删除索引

drop index 索引名 on 表名;

删除索引这个操作跟创建同理,都是非常低效的,也容易把数据库搞挂,谨慎操作!!!所以我们在创建表的时候就应该把索引规划好…

索引背后的数据结构

为啥索引不用顺序表/链表
此处的查找是 “按照值” 查找,不是 “按照下标” 查找(这个按照下标来访问元素,不叫查找),要想查找还是得遍历,不适合这里的索引…

为啥不用二叉搜索树
当他是单分支时,查询方式也就相当于遍历每一个值,不适合,如果当我们元素多了的时候,高度就高了,(高度对应着比较次数),而数据库数据存储到磁盘上的,每次比较都是意味着磁盘 IO,他也就更低效了

为啥不用哈希表
虽然哈希表查找速度非常快,是O(1),但是哈希表只能针对"相等"进行判定(就是只能查具体一个值),不能对于"大于小于",以及范围进行判定,也就不适合了

为啥不用堆
堆只能找到最大/最小值,也不适合这里的查找

根据上面的分析,我们得出结论,最适合做索引的,还得是树形结构,当然不是二叉树,而是要用到"N搜索叉树",这样高度就下降了…
在数据库中,使用这个 N搜索叉树 往往又是一个特殊的,被称为

B+ 树

(这是数据库中最常见的数据结构),要想理解这个 B+ 树还得先理解他的前身,

B 树

(也可以这样写

B-树

,

注意

,也叫B 树,不叫B减数),它的存储方式不在是一个节点一个数据，而是变成了一个节点中存储多个数据。分支则是各个数据的范围。（分支的个数 = 父节点内数据的个数 + 1）…

载荷 ：

因为我们是用树的方式来组织数据的，但是我们需要存储的数据，往往不是单纯的一个数字或字符，而是一些数据的集合，比如 上图中的 1 号节点，它不是只存了一个编号 1，它的里面还有编号1的相关信息。这些统称为载荷。

我们的改进方法就是使用 B+ 树:

事务

事物的诞生的目的就是为了把若干独立的操作给打包成一个整体,
在sql中,有的复杂的任务需要多个sql来执行,有的时候,也同样需要打包在一起…前一个sql是为了给后一个sql
提供支持,如果后一个sql不执行了或者执行出问题了,前一个sql也就失去意义了

概念:

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务…

这就要说到

原子性

,怎样保证原子性?
要么全部执行完,要么一个都不执行,任务不可以再被细分了
就像A 给 B转账500,

就是 A : -500 , B : +500

…如果中途出现意外(数据库崩了,机器断电,程序崩了…),那要是A扣了,B没加,那岂不是很尴尬,所以原子性就能避免出现这种情况,执行第一个成功,才执行第二个,第二个失败,就还原之前的操作(也就是把之前改过的数据改回去(数据库对执行的操作都有小本本记着的))

接下来就谈到常见的面试题了

事务的基本特性

四个基本特性:

1: 原子性:要么全部执行完,要么一个都不执行,任务不可以再被细分了
2:一致性:在事务执行之前,和执行之后,数据库中的数据都得合理合法,数据库的完整性没有被破坏…(就像转账完成之后,不能够出现账户余额为负这种情况…)
3:持久性:事务一旦提交之后,数据就持久化存储起来,数据就写入到硬盘了
4:隔离性:数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。这个是最不好办的,也是面试要求重点回答的

事务的使用

(1) 开启事务:

start transaction;

(2) 执行多条SQL语句
(3) 回滚或提交：

rollback/commit;

注:

rollback即是全部失败，commit即是全部成功。

先准备测试表:

droptableifexists accout;createtable accout(
id intprimarykeyauto_increment,
name varchar(20)comment'账户名称',
money decimal(11,2)comment'金额');insertinto accout(name, money)values('阿里巴巴',5000),('四十大盗',1000);

隔离性

之前谈到隔离性就是处理并发编程,并发执行多个事务,尤其是多个事务在尝试修改/读取同一份数据时,这个时候就会遇到一些问题,事务的隔离性就是解决这些问题

下面我们重点说一下并发执行事务可能带来的问题

脏读问题

假如有一天,我正在敲一个马上要交的代码,我啪啪一顿写,中途我后面以为同学瞄着我的屏幕,也跟着我的代码啪啪一顿敲,敲完之后就去上厕所了,在他上厕所时,我突然把代码改了…最后一起交了…在这里我们遇到的问题就是,我后面同学瞄到的不是最后的一个提交版本,而是一个中间的数据,最后可能会修改…

事务A 在对某个数据进行修改,修改的同时,事务B去读取了这个数据,此时B读到的很可能是一个"脏数据"(这个数据是一个临时的结果,不是最终结果)
出现这个问题的原因就是事务和事务之间没有进行任何隔离,如果我们加上一些约束条件就可以有效的避免脏读问题…

处理脏读问题:给写操作加锁
在修改的过程中,别人不能读了(加锁状态)
等修改完了别人才能读(接触加锁)
我跟后面的同学约定好了,你先别偷看,我写完了,会先提交到哪里,你去哪里上看就可以了,那个哪里就是我修改完后的代码,这样就可以做到读到的不是脏数据了,一旦有了这个加锁之后,意味着事务之间的隔离性就高了,并发性就降低了…

不可重复读问题

假如我后面的同学正在读我上传好的代码,这时,我又感觉有点不对,就又开始改代码了,这个时候后面的同学还在读我上传的代码(第一个版本的代码),等我改好之后,我又一提交,那位同学还在读,不过后面的就是在读我新上传的修改了的代码(第二个版本的),这俩都不一样,那岂不是又要重新读?

在一个事务中,包含了多次的读操作,这多次的读操作读出来的结果不一致,刚才约定的是写的过程不能读,又没说读的过程不能再写…,这就引入了不可重复读问题

解决不可重复读问题:给读操作加锁
再加一个约定,我读的时候你也不能修改,相当于给读也加锁了,意味着我必须要等同学读完后,才能进行修改,这样事务之间的并发性又降低了,隔离性又提高了…

幻读问题

还是刚刚的例子,虽然我不能修改之前的代码,但是,这个时候,我去写另一个代码了,这俩代码毫不相干,(

事务虽然在提交隔离性的时候进行了一系列的加锁,但是这个锁不是把整个数据库都给锁了,还可以该其他的表,或者改这个表的其他行

)这位同学此时还在读之前的代码,我的另一个代码写好了,啪的一提交,此时那位同学还在读,突然一刷新,诶,代码数量咋变多了…(

就相当于一个事务执行多次查询,多次查询的结果集都不一样(多了或者少了一条),相当于一种特殊的不可重复读

)

解决幻读问题:彻底串行化执行…
这时那位同学和我说,我读的时候,你啥都别干,玩去吧…(隔离性最高,并发程度最低,数据最可靠,速度最慢…)

并发(快) 和 隔离(可靠) 是不能兼得的…,就可以根据实际需求来调整数据库的隔离级别,通过不同的隔离级别,也就控制了事务之间的隔离性,也就控制了并发程度…

MySQL中事务的隔离级别

1、read uncommitted ：允许读取未提交的数据，并发程度最高，隔离最低，会带有脏读 + 不可重复读 + 幻读问题
2、read committed：只允许读取 提交之后的数据，相当于写加锁。并发性降低，隔离性提高。解决脏读，但带有 不可重复读 + 幻读问题
3、repeatable read：相当于给读和写操作都上锁了，并发性进一步降低，隔离性进一步提高。解决脏读、不可重复读，但带有 幻读问题。
4、serializable：串行化，并发性降到最低（串行执行），隔离程度最高，解决了脏读、不可重复读、幻读问题。但是运行的速度是最低的。