MySQL---索引+事务

索引

什么是索引？

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

注：

1. 数据表里的数据是以文件的形式保存在硬盘上，实现持久化存储的。
2. 保存索引的文件和保存数据表的文件是同一个文件。如果没有索引，则在数据表中查找；如果有索引，则在索引结构中查找。索引的每一块儿都会对应数据表的一条记录。
3. 创建主键约束（Primary key）、唯一约束（Unique）、外键约束（Foreign key）时，会自动创建对应列的索引。

索引的作用

索引是一个典型的以空间换时间的做法，它最大的作用就是提高查询效率。

注：

1. 索引会提高查询的效率，但是会降低增加、修改、删除的效率。因为在增加、修改、删除时不只需要更新表内的数据，还需要更新索引。
2. 当数据量较少时，不建议使用索引。因为所有会占据一定的磁盘空间。

索引的使用

• 查看索引

show index from 表名;
1

show index from student;
1

• 创建索引

//可以给非主键、非外键、非唯一约束的字段  创建普通索引
create index 索引名 on 表名（列名....）
1
2

create index idx_classes_name on classes(name);
1

• 删除索引

drop index 索引名 on 表名;
1

drop index idx_classes_name on classes;
1

索引背后的数据结构（B+树）

回想一下我们学过的数据结构，哪些能够提高查询效率呢？ 适不适合做索引呢？

哈希表：查询操作的时间复杂度是O（1）.但是不适合做索引，因为它只能查询值相同的情况，不能进行范围查询。

二叉树/二叉搜索树：查询的时间复杂度是log（N）。但是也不适合做索引，因为我们都是在数据量很大的情况下创建索引，数据量大时存储到二叉树中，树的高度就会比较高，在查询时需要节点之间进行比较，比较需要在内存中进行。如果比较的次数太多就会消耗大量的内存资源，所以不适合。

因此，程序员专门为索引创建了一个数据结构：B+树。它是B树的加强版。

• 认识B树

注：

1. B树是一个N叉搜索树，每一个节点上值的个数 <=N-1
2. 分为N叉树可以有效的降低树的高度

• B+树

注：

1. B树：有N-1个值分成N个区间；B+树：有N个值分成N个区间。
2. B树：值不会重复出现；B+树：值可能重复出现。
3. B+树：在叶子节点层，会以链表的形式把所有的叶子节点连接起来。前面非叶子节点的值都会在叶子节点里体现出来，得到全集数据，非常便于范围查找。
4. 因为叶子节点层会保存所有的数据，所以非叶子节点只需要保存索引列就行。占用空间非常小可以在内存中缓存，加快查询效率。并且减少了硬盘IO。

事务

什么是事务？

事务指：逻辑上的一组操作，组成这组操作的各个单元。要不全部执行成功，要不全部执行失败。

事务的使用

start transaction;
   事务的一组操作......
commit/rollback;
1
2
3

注：

1. 通过start transaction 来开启事务，把一组操作放进去
2. 这组操作全部执行成功，则commit（提交），真正改变数据库中的值。
3. 这组操作任意一个执行失败，则rollback（回滚），把数据库的值变为未执行这个事务之前的状态，不把执行到一半的数据写入数据库。

事务的ACID特性

原子性（Atomicity）

在以前原子被认为是不可分割的最小单元，在计算机中就沿用了这个观点。所以事务的原子性是指：组成事务的一组操作不可分割，这些操作要不全部执行，要不全部都不执行。

比如：
A给B转账100元钱的时候，当只执行了扣款语句时，此时突然断电导致转账语句没有被执行到。如果事务不是原子性的，A账号已经发生了扣款，B账号却没有收到加款，在生活中就会引起纠纷。这种情况就需要事务的原子性来保证事务要么执行，要么就不执行。

注： 当这些操作全部执行成功时，事务提交；如果有一个操作执行失败了，则事务回滚。

一致性（Consistency）

事务执行前后，数据处在一致的状态，就是一致性。即数据经过计算后数据的总量不变。

比如，转账事务，不管事务成功还是失败，应该保证事务结束后，表中A和B的存款总额跟事务执行前一致

注：

1. 事务执行前，数据库存储的数据处于一致的状态
2. 事务执行时，数据库存储的数据可能处于不一致的状态
3. 事务执行后，不管执行成功还是执行失败，数据库存储的数据都必须处于一致的状态

隔离性（Isolation）

当不同的事务并发执行的时候，如果有一个事务正在使用一个数据，而另一个事务则不能使用同一个事物，这就是隔离性。要保证事务的”隔离“，得让这些需要使用同一个数据的事务串行执行。

持久性（Durability）

在事务完成后，该事务对数据库的修改得持久的保存，不能随着程序的重启、电脑的重启而改变，这就是持久性。

事务之间的相互影响

脏读

脏读指：读脏数据，脏数据就是一个临时的数据，不代表最终的结果。

比如：事务A在修改数据，修改到一半了得到一个中间数据a，但是最终的数据是b。但是事务B在事务A执行到一半时读取到了中间数据a，然后进行后续操作，那么事务B的后续操作也都是错误的。

解决：
对写操作加锁（写操作就包含了修改操作）。即当事务A执行时就把数据锁到小黑屋里，不让其他人拿到，执行完再放出来。

不可重复读

不可重复读指：一个事务内两次相同的查询却得到了不同的数据。

比如：事务A先获取到数据a，进行循环判断，满足条件则可以执行后续操作，但是中途事务B获取到数据a修改为b，此时事务A可能还没执行完后续的操作就被迫退出循环。

解决：
对读操作加锁。即当事务A获取到数据就把数据锁到小黑屋，不让其他人拿到，知道事务A执行完再放出来。

幻读

幻读指：两次读取数据时对应的结果集不同。

比如：在一张表中有a、b、c三个数据。事务A获取了a数据，此时表中的数据是a、b、c；但是同时事务B把表中的b数据修改为了B，当事务A再次获取a数据时，此时表中的数据是a、B、c；

解决：
串行化。即不能让事务A和事务B同时执行。

丢失更新

丢失更新指：两次修改同一个数据时，第二次修改会忽略第一次已经修改过，把第二次修改后的值当作是最终的结果。

比如：a=1 事务A读取到a=1 并且a+1=2，同时事务B读取到a=1，并且a+1=2，最终将a的值修改为2，而不是3.

解决：
同时加读锁和写锁。规则：在一个事务加了写锁后，别的事务既不能进行写操作也不能进行读操作。

事务隔离级别

read uncommited: 允许读未提交的数据。并发程度最高，隔离性最低。可能存在脏读、不可重复读、幻读的问题。

read commited: 只能读 提交之后的数据，相当于写加锁。并发程度降低，隔离性提高。解决了脏读问题，可能存在不可重复读、幻读的问题。

repeatable read: 相当于读和写都加锁。并发程度再降低，隔离性再提高。解决了脏读、不可重复读的问题，可能存在幻读的问题。

serializable: 严格执行串行化。并发程度最低，隔离性最高。解决了脏读、不可重复读、幻读的问题

注：MySQL默认的隔离级别是 repeatable read