MySQL的索引和事务

一、索引

（一）索引概念

索引是一种特殊的文件，包含数据库中所有记录的引用，可以对表中的一列或多列创建索引， 并指定索引的类型（存储引擎），每种索引在不同的存储引擎中的实现都有可能不同。

（索引类似数组的下标，通过下标拿到数组中的数据是很快的，同样通过索引去查询数据库中的数据也是很快的。）

（二）MySQL中经典的两种存储引擎

存储引擎：就是MySQL对数据进行增删改查的不同实现方案。

MyISAM	MySQL5.5之前的默认存储引擎，不支持事务，性能较高
InnoDB	MySQL5.5之后的默认存储引擎，支持事务，性能不如MyISAM

查看当前的存储引擎：

show variables like 'default_storage_engine';

 （三）索引适用情况

索引适用于数据量比较大，并且查多改少的情况。可以提高查询数据的效率，但是会拖慢增、删、改的效率。同时创建索引也需要时间和空间的开销。

  1. 演示：

step1:创建一张插入了8000000条记录的数据表

step2:在不创建搜索引的情况下，查询某条数据的具体信息。

在不创建索引的情况下，是进行全表扫描，在800万的记录中一行行扫描，知道查找到id_number为556677的信息为止，耗时3.58s。

step3:将id_number这一属性设置为索引。

可以看出，此时检索的速度是非常快的。

 2. 创建索引

create index 索引名称 on tb_name(属性名称)

 

3.查看索引

show index from tb_name;

4.可以通过explain可以查看MySQL的引擎分析SQL语句可能会用到的索引等信息，包括扫描的行数。

（1）没有创建索引的情况

（2）创建了索引的情况

5.删除索引

drop index  索引名称 on tb_name; 

（四）索引背后的数据结构

MySQL的索引实现基于B+树的设计（N叉搜索树）。

1.为什么数据库存储不用常见的高效查找数据的数据结构 （BST/哈希表等）

（1）BST：二叉搜索树 -- >RBTree :红黑树 

无论是二叉搜索树还是红黑树，它查找的时间复杂度为logN，当数据量非常大时，由于二叉树的每个节点最多只有两个子树，二叉树的高度会很高。而且数据库的数据都在磁盘上保存，每访问一个二叉树的节点都要读写一次磁盘，这样就会很慢。

（2）哈希表：

理论上利用哈希表可以在常数时间O（1）时间复杂度，找到指定数据，但哈希表无法处理区间查询操作。

2.B树 -- （N叉搜索树）

1. 每个树节点上都有多个值；

2.每个节点上的子树（分叉树）的个数，就是当前结点值的个数 + 1；

3.子树的结点值一定处在父节点的范围之内。

3.B+树

1.B+树中，子树节点中存在的最大值（最小值）是在父节点中出现过得值--->这样做的目的就是让最底层的叶子节点包含整个数据的全集！！！

2.B+树中，最底层的叶子节点使用链表连接 -- >更高效的区间查找

3.对于B+树来说，所有的数据都存储在叶子节点，非叶子结点只需要保存索引列的辅助信息（索引列的值），非叶子结点占用空间小，可以直接放在内存中，减少磁盘的IO。

4.聚簇索引 与 非聚簇索引

由于B树家族的每一个结点值都是一个复合值（都包含key(索引值) 和 value(对应数据)），根据索引类型和保存对应数据的的不同可以分为聚簇索引和非聚簇索引。

1.聚簇索引

聚簇索引（主键索引），一个表只有一个聚簇索引，构建索引树上的每个节点，除了要保存索引列的信息，还需要保存这条记录的完整内容。  ---- 查询速度快，一个表中只有一个聚簇索引，保存的信息多，占用空间大。

2.非聚簇索引

非聚簇索引（二级索引、普通索引、唯一索引、index创建的都是非聚簇索引，一张表中可以有多个非聚簇索引）。索引树上的每个节点，除了保存索引列的信息之外，还需要保存该记录的行号（对应的主键id）。--查询速度慢（只能找到主键信息，需要回表查询 -- 非聚簇索引相较于聚簇索引有更多的查表操作，也就是读写磁盘操作），一个表中可以有多个非聚簇索引，保存的信息少，占用空间小。

二、事务

（一）事务的概念

        所谓事务，就是把若干sql操作打包为一个整体，实际执行的时候，这个整体要么全部执行，要么全都不执行。

（如 银行转账业务：A要给B转钱，银行先要把A要转的钱扣除，然后再将钱转给B，这个过程在我们看来是一个整体，不可能说把A的钱扣了，但是B却没有收到。）

若执行的过程中，出现了突发事故，某些操作执行不下去了，MySQL可以保证在突发情况恢复之后，（数据没有遭到破坏为前提），通过事物的回滚（rollback）操作进行数据的还原。

事务的三个操作：

        1.开启事务：start transaction;

        2.执行多条sql语句（一个整体）

        3.提交或回滚：commit / rollback

        提交事务就是把开启事务之后所有sql语句统一在数据库上持久化。

        回滚操作就是取消上次对数据库做得修改。

（二）事务的特性（ACID）

        1.原子性 ：

         一个事务中的所有操作，要么全都执行，要么全都执行失败。（执行失败之后，通过rollback操作进行数据的恢复）。

        2.持久性 ：

         一个事务执行完成之后，这个事务对数据库的所有修改都是永久的（持久化，保存到磁盘上），不会丢失。

        3.一致性 ：

        一个事务执行前后的数据都一种合法性的状态，事务永远都是从一个一致性的状态到另一个一致性的状态。

（仍以银行转账为例，在执行转账操作之前，A账户有100万，B账户有50万，这些确确实实是他们拥有的（合法状态），此时A要给B转10万，A账户应有90万，B账户应有60万（合法状态）。但如果A转账结束后是90万，B还是50万（不合法状态））

        4.隔离性 ：

        多个并发事务访问数据库时，事务之间是相互隔离的，一个事务不应该被其他事务干扰，不同事务之间相互隔离。

（一个sql中的执行操作，MySQL自身通过读写锁是可以保证数据并发时的正确性 ，但如果是事务，并发执行就会存在相应问题。）

（三）脏读  VS  幻读  VS  不可重复读

        1.脏读：

        事务A在修改数据，此时A还没将修改后的数据提交到数据库，事务B读取到了事务A修改后的数据，但事务A又进行了“回滚”,前面的修改都不作数了，事务B读到的就是“脏数据”，这种情况就称为脏读。

        2.幻读 ：

        幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。事务不是独立执行时发生的一种现象，且其他事物的修改对于本事务不可见。（明明没有修改，但是读的同一组数据却发生了改变）

        3.不可重复读 ：

        是指在同一个事务内，在多次读同一数据得到的结果不同。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。其他事务的修改对于本事务来说是可见的。

        事务A此时在读一组数据为“XXXX”,事务A 读取了数据，但还未提交事务，这时事务B也读取了这组相同的数据，并将其修改为“****”，事务A再次读取这组数据时，结果就与上次读取时不一致，这种情况称之为不可重复读。

    

（四）事务的四种隔离级别

1.读未提交

在该隔离级别下事务可以看到其他还没有提交事务对数据库的修改。

2.读已提交

处在该隔离级别下的事务可以看到其他已经提交事务对数据库的修改。（Oracle数据库默认的隔离级别）

3.可重复读

一个事务一旦开启，在该隔离级别下，事务提交之前，多次查询看到的结果是相同的。无论其他事务如何修改数据库，在当前这个事务提交之前都是不可见得。

4.串行化

事务最高的隔离级别，所有事务都串行访问数据库，不会发生冲突，不会产生任何事物问题（没有并发执行）。

 

 1.读未提交：v1,v2,v3的值：2  2  2

 2.读已提交：v1,v2,v3的值：1  2  2

 3.读未提交：v1,v2,v3的值：1  1  2