mysql事务与索引

1.事务

（1）提出背景：

在日常开发环境中，有一些场景是需要“一气呵成”完成某一个操作。

eg：银行转账的场景：张三（手里有1000）现要给李四（手里有500）转500，本来最后期待的结果应该是张三（还有500），李四（有1500），但如果在这个过程中程序崩溃/停电/数据库崩溃就会导致张三已经扣了钱但是李四还没有收到钱，那在这个事就扯谈了！！！

所以引入事务就是为了避免上述类似的问题。事务会将多个sql语句打包成一个整体，这个整体要么执行成功，要么“一个都不执行”（这里的一个都不执行，不是指整体中的所有sql语句都不执行，而是可能执行到一半出问题了，就会回到最初的状态。这个过程也叫做回滚）。

（2）特点：

原子性：将多个sql语句打包成一个整体，要么执行成功，要么“一个都不执行”。

一致性：事务执行前后数据不能太过离谱。

持久性：事务做出的修改都是在硬盘上持久化存储的。

隔离性：并发执行多个事务所产生的问题。

（3）隔离性：

问题1：脏读

一个事务A正在写数据的时候，事务B此时来进行读数据，然后事务A又对刚才的数据进行了修改，此时导致事务B读到的是一个无效的数据。

eg：我正在写代码的时候，别人就来读我的代码，然后就不管了，之后我又修改了代码，那读我代码的那个人读到的代码就有可能是错误的。

如何解决：给写操作加锁，必须要写数据这个操作完成之后才能进行读数据。

问题2：不可重复读

在并发执行多个事务的过程中，一个事务内部在多次读取某同一数据的时候，读到的结果可能不同。

eg：别人在读我代码的同时，我也在写，就导致读我代码的人，读的是同一份代码但是结果可能在变化。

如何解决：给读操作加锁，必须要读操作完成之后才能写。

问题3：幻读

一个事务在执行过程中，内部进行两次读取的操作时，读取的数据集不同，那么读到的结果集不同。

eg：就好比是两份不同代码，那么读到的结果肯定不同，这个问题对实际场景有无影响，具体场景具体分析。

如何解决：引入串行化方式，保持绝对串行执行，避免并发执行。

（4）隔离级别：

针对数据正确和并发程度之间做出权衡，就是所谓的隔离级别。有四种：

a.read uncommitted（读未提交）

也就是脏读。此时隔离性最低，数据正确率最低，并发程度最高，并发执行效率最高。

b.read committed（读已提交）

解决脏读。此时隔离性提高，数据正确率提高，并发程度降低，并发执行效率降低。

c.repeatable read（可重复读）

解决不可重复读。此时隔离性进一步提高，数据正确率进一步提高，并发程度进一步降低，并发执行效率进一步降低。

d.serializable（串行化）

解决幻读。此时隔离性最高，数据正确率最高，并发程度最低，并发执行效率最低。

2.索引（index）

（1）提出背景：

数据库使用select查询的时候：

a.先遍历表

b.跟据条件去表中筛选，如果满足条件就保留该条数据，不成立则跳过。

如果表中数据过多，那么通过这种方式查询所消耗的时间是很多的，而且数据库中的数据都是存储在硬盘上的，对于计算机来说，读取硬盘（读取IO）的时间开销是很大的。

所以针对上述的问题，就提出了索引，它的存在就是为了优化数据库的查询操作。当然引入索引也需考虑几个问题：

索引会占用一定的内存空间。

进行插入或修改操作的频率较低，经常进行查询操作，就可以进行索引的创建。

数据量较大，并且经常对某一列进行查询，索引就是针对某一列来创建的，只会提高查询这一列的速度，对于没有创建索引的列，还是需要去遍历表然后进行条件匹配。

（2）相关操作：

a.创建：

create index 索引名字 on 列名;

ps：

主键（primary key），外键（foreign key），unique修饰的列会自动创建索引。

b.查看：

show index from 表名;

c.删除：

drop index 索引名 on 表名;

ps：只有手动创建的索引可以进行删除，自动创建的索引不可以通过此操作进行删除。

创建和删除索引都是一个危险的操作，创建索引的时候，会对当前这一列数据进行整理，如果数据量过大，那么一旦这个操作执行下去，可能服务器就会被卡住。

非得创建也不是没有办法，另外重新弄一台服务器，部署mysql，创建表，然后把想要新增的索引创建好，再把之前的数据全部导入进行，这样不论这个过程会进行多久都不会影响生产环境。

（3）背后相关知识：

索引是通过一定的数据结构来实现。

a.分析：

顺序表：适合于通过下标查找某个值，尾插，尾删

链表：适合于任意位置的删除，插入

栈和队列更加不适合。

哈希表：可以进行“精确匹配”，不能进行范围查询，更不能进行“模糊匹配”。

二叉搜索树：可以进行“精确匹配”，也可以进行范围查询和“模糊匹配”。

初步确定是应该采用二叉搜索树的形式，采用二叉搜索树进行搜索的时间复杂度为，如果每个树的度不为2，当节点的度逐渐增加的时候，那么对应的时间复杂度就会降低，所以这样分析的话索引背后采用的是N叉搜索树（B+树）。

b.B+树（N叉搜索树）

了解B+树之前，先要了解一下B树（B-树也是B树，“-”代表的是连接符）。

B树：

B树中的每个节点的度都不确定，一个节点中如果有N个key，就会划分出N+1个区间，然后每个区间都为衍生出一些子区间。

eg：

每个节点都不是无限衍生的，但插入达到一定规模的时候就会进行分裂，当删除数据达到一定规模是，节点之间会进行合并。由于每个节点都是存储在硬盘上的，每次只用去硬盘读取节点，然后对节点中的数据进行比较就好了。（读取一次硬盘相当读取很多次内存） 

B+树：

B+树是B树的改进，是为索引量身定做的数据结构。

特点：

1）树中每个节点的度都是不确定的，一个节点中有N个key只会划分出N个区间（子节点），然后子节点再进行衍生。

2）父亲节点中的最大key会存在在子节点中的最后位置，也就是每个节点上最后一个key是父节点中最大的值。

3）父亲节点中的每个key都会以最大值的身份存在于子节点中，这样就会导致整个树最后的结果集全部在叶子节点上。

4）B+树会使用链表将叶子节点给串起来。

eg：

 最后叶子节点使用一个双向链表给存储起来。

优点：

1）由于叶子节点是一个数据全集，所以所有的行数据（不只存了id，还有其他属性，是一条记录）都会存储在叶子节点上，而非叶子节点只是存储了一个用于比较的key，用来排序（比如存个id），所以叶子节点是比较占用内存的，而非叶子节点不怎么占用内存的。每次进行查询的时候，就把非叶子节点加载到内存中，就不用在去单独的读取非叶子节点了，这样整体查询比较的过程就可以在内存进行了，读取硬盘的次数就进一步降低。

2）非常擅长范围查询。如果使用B树的，那么进行范围查询的时候，会很麻烦，会进行回溯操作。

3）B+树由于所有的查询都是落在叶子节点上的，所以查询不同东西所产生的时间开销是稳定的，而B树可能是不稳定的。

4）是一个N叉搜索树，树的高度是有限的，可以降低读取硬盘的次数，进而加快查询的速率。