【 MySQL索引和事务 】

一、索引

1.1 概念

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

1.2 作用

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

注意：

索引的意义就是要提高查找的效率，但同时也存在缺点，即占用了更多空间，也拖慢了增删改的速度(需要及时同步调整索引)。即使如此，索引还是广泛应用，因为查找操作往往是最高频的！

1.3 使用场景

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

数据量较大，且经常对这些列进行条件查询。
该数据库表的插入操作，及对这些列的修改操作频率较低。
索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

1.4 使用

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

查看索引：

show index from 表名;

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

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

删除索引：

drop index 索引名 on 表名;

注意： 创建索引 和 删除索引都是非常低效的操作！尤其当你的表中已经存在很多数据时。如果你是针对线上的数据库时，一定不要贸然去创建索引，容易让数据库垮掉！！所以在创建表的时候就要提前把索引规划好。

1.5 底层原理

我们今天要介绍的是工作开发中最常接触到innodb存储引擎中的的B+树索引。

要介绍B+树索引，就不得不提二叉查找树，平衡二叉树和B树这三种数据结构。B+树就是从他们仨演化来的。

一、二叉查找树：

首先，让我们先看一张图：

从图中可以看到，我们为user表（用户信息表）建立了一个二叉查找树的索引。图中的圆为二叉查找树的节点，节点中存储了键(key)和数据(data)。

键对应user表中的id，数据对应user表中的行数据。二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。 顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点。

如果我们需要查找id=12的用户信息，利用我们创建的二叉查找树索引，查找流程如下：

1. 将根节点作为当前节点，把12与当前节点的键值10比较，12大于10，接下来我们把当前节点的右子节点作为当前节点。

2. 继续把12和当前节点的键值13比较，发现12小于13，把当前节点的左子节点作为当前节点。

3. 把12和当前节点的键值12对比，12等于12，满足条件，我们从当前节点中取出data，即id=12,name=xm。

利用二叉查找树我们只需要3次即可找到匹配的数据。如果在表中一条条的查找的话，我们需要6次才能找到。

二、平衡二叉树：

上面我们讲解了利用二叉查找树可以快速的找到数据。但是，如果上面的二叉查找树是这样的构造：

这个时候可以看到我们的二叉查找树变成了一个链表。如果我们需要查找id=17的用户信息，我们需要查找7次，也就相当于全表扫描了。 导致这个现象的原因其实是二叉查找树变得不平衡了，也就是高度太高了，从而导致查找效率的不稳定。为了解决这个问题，我们需要保证二叉查找树一直保持平衡，就需要用到平衡二叉树了。 平衡二叉树又称AVL树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过1。

三、B树：
因为内存的易失性。一般情况下，我们都会选择将user表中的数据和索引存储在磁盘这种外围设备中。

但是和内存相比，从磁盘中读取数据的速度会慢上百倍千倍甚至万倍，所以，我们应当尽量减少从磁盘中读取数据的次数。 另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。

如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。

如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块，我们都知道平衡二叉树是每个节点只存储一个键值和数据的。

那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？可以想象到二叉树的节点将会非常多，高度也会及其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！

为了解决平衡二叉树的这个弊端，我们应该寻找一种单个节点可以存储多个键值和数据的平衡树。也就是我们接下来要说的B树。

B树（Balance Tree）即为平衡树的意思，下图即是一颗B树：

注意： B树的每个节点上都会存储N个key值。N个key值就会划分出N+1个区间，而每个区间就对应到一棵子树

从上图可以看出，B树相对于平衡二叉树，每个节点存储了更多的键值(key)和数据(data)，并且每个节点拥有更多的子节点。基于这个特性，B树查找数据读取磁盘的次数将会很少(相对于每个节点的比较次数增加了，IO次数减少才是关键)，数据的查找效率也会比平衡二叉树高很多

四、B+树：

B+树是对B树的进一步优化。让我们先来看下B+树的结构图：

自己的理解：

对于B+树，它的每个节点上有N个key值，且划分成了N个区间！父节点中的key值会作为子节点中的最大值或最小值。父节点的key值都会在子节点中体现，非叶子节点的(k,v)值最终都会在叶子结点上体现。最后，使用双向链表将叶子结点顺序连接起来。

B+书总结：

想了解更加详细的内容，可以参考大佬笔记！！！

---

二、事务

2.1 引入

准备测试表：

drop table if exists accout;
create table accout(
id int primary key auto_increment,
name varchar(20) comment ‘账户名称’,
money decimal(11,2) comment ‘金额’
);
insert into accout(name, money) values
(‘阿里巴巴’, 5000),
(‘四十大盗’, 1000);

比如说，四十大盗把从阿里巴巴的账户上偷盗了2000元：

– 阿里巴巴账户减少2000
update accout set money=money-2000 where name = ‘阿里巴巴’;
– 四十大盗账户增加2000
update accout set money=money+2000 where name = ‘四十大盗’;

问题：假如在执行以上第一句SQL时，出现网络错误，或是数据库挂掉了，阿里巴巴的账户会减少2000，但是四十大盗的账户上就没有了增加的金额。

解决方案：使用事务来控制，保证以上两句SQL要么全部执行成功，要么全部执行失败。

2.2 概念

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

2.3 使用

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

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

start transaction;
– 阿里巴巴账户减少2000
update accout set money=money-2000 where name = ‘阿里巴巴’;
– 四十大盗账户增加2000
update accout set money=money+2000 where name = ‘四十大盗’;
commit;

2.4 事务特性

一、一致性：

事务的一致性是指事务在执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处以一致性状态。比如：张三给李四转钱，不可能张三被扣了钱，李四没有加钱。

二、持久性：

事务的持久性是指事务一旦提交后，数据库中的数据必须被永久的保存下来，因为已经写入到硬盘。即使服务器系统崩溃或服务器宕机等故障，只要数据库重新启动，那么一定能够将其恢复到事务成功结束后的状态。

三、原子性(重要)：

事务诞生的目的就是将若干个独立的操作打包成一个整体。 在SQL中有的复杂任务需要多个SQL语句来执行，有的时候前一个SQL语句就是为了给下一个SQL提供支持，所以同样也需要打包成一个整体，如果后一个SQL失去了作用，那前一个SQL也就没有了意义！！

从引用部分我们可以知道，我们可以使用事务的原子性来保证以上两句SQL要么全部执行成功，要么全部执行失败。但是由于我们无法预知操作是否能够执行成功，该执行的操作还是得执行，并不是都不执行即所谓的全部执行失败。

那原子性是如何保证以上机制呢？当出现执行失败后，由数据库自动执行一些’ 还原性’ 的工作，来消除前面SQL带来的影响，即所谓的 ‘回滚(rollback)’ 。 针对引用部分，当A转账后，账户自动扣除，但是B却没有收到钱，就会执行还原操作，将转账金额加回到A账户。看起来就像一个操作都没执行。

扩展：

数据库是如何知道该还原成哪个值呢？数据库会将执行过的每一个操作都记录下来(日志 + 数据库内置的一些表)

注意：

虽然能够执行还原操作，但是在删除数据库时还是要仔细斟酌！！

四、隔离性(重要)：

事务的隔离性是指在并发环境中，并发的事务是互相隔离的，一个事务的执行不能被其它事务干扰。也就是说，不同事物并非操作相同数据时，每个事务都有完整的数据空间。一个事务内部的操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务是不能互相干扰的。

当并发执行多个事务，尤其是这多个事务在尝试修改或读取同一份数据时，这时候就非常容易出现问题，如下分析：

1、脏读：

比如，我在写代码的过程中，有一个同学看到了我屏幕上的内容后就走了。然后我把代码改了，后面他发现他看到的和我最终的版本不一样。所以脏读问题可以理解为事务A在对某个数据进行修改，而修改的同时，事务B去读取了这个数据。此时B读到的很可能是一个“脏数据”，即一个临时的结果。

出现脏读是因为事务和事务之间没有进行任何的隔离！！我们可以加上一些约束限制，如给写操作加锁。 在修改的过程中别人不能读取(加锁状态)，修改完之后才解除加锁，可以读取。

2、不可重复度

针对脏读问题我们对写操作进行了加锁！！但是如果当我们在写操作加锁的基础上进行了多次读操作后又会出现怎样的情况呢？如下分析：

前面我们约定在写的过程中不能对内容进行读取，但是没有约定在读的过程中不能写数据！才导致我们多次的读操作读出的结果不一致。针对以上问题，我们也可以对读操作进行加锁。即我读的时候你也不能写，这样就可以解决不可重复读。如下：

3、幻读

针对以上问题，我们对读和写操作同时加上了锁！但是这里的加锁并不是把整个数据库都上锁了，所以我们还可以对数据库的其他表进行读写操作。那此时就会出现问题：一个事务执行过程中进行了多次查询，而多次查询的结果集不一样(可能多一条，也可能少一条)， 我们可以将此视为一种特殊的不可重复度。如下：

当读代码的时候，发现代码的数量多了。原本只有A.java，现在多了个B.java。这就是所谓的幻读问题，而解决幻读问题的方法就是 彻底串行化执行

扩展：

事务的隔离性是有划分等级的，如下：

根据实际需求，来调整数据库的隔离等级，也就控制了事务间的隔离性，也就控制了并发程度！

总结：

以上就是解决事务并发执行的方法！我们会发现，每一种方法的并发性越来越低，而隔离性越来越高！所以并发(快) 和 隔离(准) 是不能兼得的

---

MVCC！！！！！！！！！！！！！