MySQL索引与事务

1. 索引

（1）概念

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

（2）利弊

利：

1. 数据库中的索引目的是为了加快查询速度，在执行select这样的操作时，默认是按照遍历的方式来进行查询的；比如在执行where判断语句时就是遍历来进行，满足条件则留下，不满足则跳过判断下一个（此处每取一行的时间复杂度为O(N)，每次遍历都是需要读硬盘的，之前像数组遍历的操作是读内存的操作）
2. 所谓的"索引"就相当于是在数据库中,构建一个特殊的"目录"(一系列特定的数据结构,硬盘) 通过这样的数据结构,加快查询的速度,尽可能避免针对表数据的遍历操作. 

弊： 引入索引后可能会影响增删改的效率         变慢：增加维护索引的开销，当在表中插入，修改或删除数据时，数据库不仅需要更新表中的数据，还需要更新相关的索引结构。这增加了额外的开销         变快：在进行where语句的删除操作时就会加快效率，因为会在查询的基础上来进行删除，查询的效率变高了

遍历读内存数据和遍历读硬盘数据有什么区别 ？（了解即可）

1. 访问速度 内存：因为内存与CPU直接相连，因此访问内存数据的速度非常快。 硬盘：与内存相比，硬盘的访问速度较慢，因为数据需要从硬盘读取到内存，然后才能被CPU处理。硬盘的存取速度往往是主存的几百分分之一。
2. 数据预读 内存：内存访问通常是直接和即时的，不需要预读。 硬盘：硬盘往往不是严格按需读取，而是每次都会预读
3. 存储结构： 内存：内存通常组织成线性的字节数组，可以直接通过地址访问。 硬盘：硬盘上的数据按照文件系统和目录结构组织，需要通过文件系统API进行访问。
4. 数据持久性 内存：内存中的数据在断电后会丢失，因此是易失性的 硬盘：硬盘上的数据在断电后仍然保留，因此是非易失性的。
5. 访问方式 内存：内存访问通常通过指针或引用进行，可以直接访问内存中的任何位置 硬盘：硬盘访问通常通过文件系统API（如open、read、write等）进行，这些API提供了对文件和目录的抽象操作。
6. 遍历方式 内存：遍历内存数据通常使用循环和指针或引用操作 硬盘：遍历硬盘数据通常涉及打开文件、读取文件内容、关闭文件等操作。

（3）使用场景

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

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

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

（4）使用

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

--查看索引
show index from 表名;

--查看affair的索引
show index from affair;

 说明：

• id为主键约束，自动创建了该列的索引，在进行形如： select * from affair where id .....的操作时就会大大提高查询的效率
• select * from affair where name .....则不会提高效率，注意一定是基于索引的语句

对于外键索引来说，在子表中插入/修改,需要查询父表.在父表进行修改/删除,也需要查询子表.

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

--给affair的name创建索引
create index name_index on affair(name);

 注意： 创建索引，也是一个"危险操作"，如果是针对空表,或者表中的数据比较少(几千，几万..)创建索引,就谈不上危险不危险. 一旦表的数据量比较大，千万级别....此时创建索引操作,就可能会触发大量的硬盘IO,直接把机器就搞的卡死住了. 在最初建表的时候,都要有哪些索引,提前规划好，创建好... 万一某个表，确实没有提前创建索引，现在又有了很多数据了，非加索引不可，此时咋办呢?? 使用新机器新创建一个数据库，旧库的数据导入到新库中，变为旧库访问新库操作

--删除索引
drop index 索引名 on 表名;

--删除affair中name的索引
drop index name_index on affair;

注意：只能删除自己创建的索引，不能删除自动生成的

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

所谓的"构建索引"其实就是引入一些数据结构,对数据进行存储,从而提高查找的速度.  那哪些数据结构可以提高查找的速度呢？

• 二叉搜索树：当要保存的元素多时，就会使整个树的高度变高，这样比较的次数就会越来越多，但由于是在硬盘上，所以不太合适。
• 哈希表：最大的问题在于，只能进行"相等"查询.无法进行><这样的"范围查询"，也无法进行like模糊查询.哈希表是要通过哈希函数,把查询的key映射成数组下标
• B+树就是为数据库索引量身定做的数据结构

B树，也就是N叉搜索树，每个节点可以有多个子树（树的度为N），这样每个节点存储的key就会是多个值了

某个节点上保存了N个key就可以延伸出N+1个子树；此时，进行查询的时候,针对每个节点,都需要比较多次,才能确定下一步走哪个区间，高度得到了降低，但比较次数变多了； B树优势：访问硬盘的次数变少了，和某个节点进行比较的时候,是先一次硬盘IO,把所有的这个节点上的内容都读取出来接下来的比较都是在内存中进行的了. 这里主要的目的，不是为了减少比较的次数,而是要减少硬盘IO的次数.

B+树 是针对B树做出的进一步的改进的数据结构，B+树也是N叉搜索树 特点：

• B+树是有N个key，划分出N个区间
• 父节点的key会在子节点中以最大值的形式再次出现
• B+树会把叶子节点像链表一样首尾相连

B+树的优势：

1. N叉搜索树,高度比较低, 此时硬盘IO次数就比较少.
2. 叶子结点是全集,并且用链表结构连接,非常便于范围查询
3. B＋树,所有的查询都是要落到叶子结点上完成的，任何一次查询,经历的IO次数和比较次数都是差不多的.查询的开销稳定的
4. 由于B+树,叶子结点是全集,非叶子节点上不必存储"数据行"，只需要存储索引列的 key即可.使得非叶子节点,消耗的空间比较少~~

2.事务

（1）概念

事务指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。 在不同的环境中，都可以有事务。对应在数据库中，就是数据库事务。 举例说明：A用户给B用户进行转账操作，会进行两步操作

1. A用户原本的余额为1000元，向B转账500元，余额变为500元 update 账户余额表set balance = balance - 500 where id = 1;
2. B用户原本的余额为500元，接收到A的转账，余额变为1000元 update 账户余额表set balance = balance + 500 where id = 2;

执行此事务时我们必须保证两步操作必须都要完成，不能出现类似"转账未到账的情况"，这时，我们引入事务就是为了解决此类问题 简单来说，所谓事务就是将多个要执行的SQL语句打包成一个整体，这个整体在执行的过程中就可以实现要么整个都执行完，要么都不执行，这样就可以避免转账一半的中间状况

 （2）注意事项

注意：此处的一个都不执行，并不是一个SQL语句都没执行，而是执行一半发现出错会自动进行"还原操作"，相当于将前面的SQL进行了"撤销操作"，最终像是没执行一样，这样的机制称为"回滚"，同时把事务支持的上述特性称为原子性 数据库是如何知道具体是怎样回滚的，如何知道前面的sql做出了什么修改？ 数据库内部存在一系列的"日志体系"，记录到文件中（可以应对程序崩溃和主机掉电的问题），当开启事务的时候,此时每一步执行的sql,都对数据进行了哪些修改,这些信息就会记录在案.后续如果需要回滚,就可以参考之前记录的内容，进行还原了.

（3）特性

事务最核心的特性,就是原子性,能够解决的问题,就是批量执行sql的问题. 例：有新生登记~~(可能需要往学生表/班级表同时插入数据)

        电商网站上下单(可能需要改商品表的库存数据/新增订单数据)

（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;

事物的四个核心特性：

1.  原子性（最重要的特性）
2. 一致性，描述的是,事务执行前和执行后,数据库中的数据,都是'合法状态'，不会出现非法的临时结果的状态.
3. 持久性，事务执行完毕之后,就会修改硬盘上的数据.事务都是会持久生效的.
4. 隔离性，描述了多个事务并发执行的时候,相互之间产生的影响是怎样的. MySQL是一个"客户端-服务器"结构的程序，一个服务器通常会给多个客户端同时提供服务.  因此,很可能,这多个客户端,就同时给这个服务器提交事务来执行.与之相对,服务器就需要同时执行这多个事务,此时就是"并发"执行

此时,如果这些同时执行的事务,恰好也是针对同一个表,进行一些增删改查,此时就可能会引入一些问题 1)脏读 2)不可重复读 3)幻读问题

1）脏读 有两个事务A和B并发执行. 其中事务A在针对某个表的数据进行修改.A执行过程中,B也去读取这个表的数据.当B读完之后,A把表里的数据又改成别的. 这就导致,B读到的数据,就不是最终的"正确数据"而是读到了临时性的,"脏数据" 通俗来说就是，小红学习很好，小刚学习不好，考试时小刚想要抄小红的答案，而小红因为粗心，最后才察觉到错误，需要重新修改，而此时小刚并未察觉小红已修改内容，此时小刚读到的内容就是"脏数据"（往往指的是"数据过期,过时了"错误的数据了~~） 如何解决呢？ 小刚和小红约定好最后五分钟修改完再抄，修改之前，小刚不能抄，也可以称为"给写操作加锁"（JavaEE多线程会学习）  小红修改的时候，小刚不能抄 一个事务在修改的时候，其他事物不能读取该事务

2)不可重复读 此时,有三个事务，ABC. 首先,事务A执行一个修改操作.A执行完毕的时候,提交数据.接下来事务B执行,事务B读取刚才A提交的数据.... 在B读取的过程中,又来了一个事务C,C又对刚才A修改的数据再次做出了修改. 此时对于B来说,后续再读取这个数据,读到的结果就和第一次读到的结果是不一样的. 如果是有多个事务,每个事务读到的数据不一样这种情况认为是正常 通俗来说就是，小刚和小红约定好在最后五分钟小红确定答案后小刚再进行抄写，在小刚进行抄写的过程，小红发现刚才的题好像有点问题，于是又进行了重新修改，此时小刚的视角看起来就是答案突然变样了 如何解决？ 再次进行约定，小红在修改的时候小刚不进行抄写 小刚在抄的时候，小红不能写 一个事务在读取数据的过程中,其他的事务不能修改它正在读的数据.给读操作加锁

3)幻读问题 相当于不可重复读的特殊情况 有一个事务A在读取数据.读的过程中,另外一个事务B, 新增了/删除了一些其他的数据....此时站在A的视角,多次读取的数据内容虽然一样,但是"结果集”不同 通俗来说就是，小刚和小红已经约定好了，小红写完再抄，小刚抄的时候小红不再去修改 小刚在抄的时候，小红没有修改错误的答案，在错题的旁边标注正解二字并写了正确答案，而站在小刚的角度来看，虽然小刚在读的过程中小红没有在修改答案，但是抄着抄着突然出现了正解，这样的问题就是幻读 如何解决? 小刚在抄的过程中出现了其它内容 还是跟小红进行约定，在抄的过程中不进行任何操作，这样的操作成为串行化

并发执行的事务设计的三个问题又和隔离性有什么关系呢？ 在mysql中提供了四个隔离级别.可以通过配置文件来设置当前服务器的隔离级别是哪个级别. 设置不同的隔离级别，就会使事务之间的并发执行的影响产生不同的差别,从而会影响到上述的三个问题的情况~~ 1）read uncommitted读未提交. 这种情况下，一个事务可以读取另一个事务未提交的数据.此时，就可能会产生脏读,不可重复读,幻读三种问题..... 但是此时，多个事务并发执行程度是最高的,执行速度也是最快的. 并发程度越高,速度就越快；并发程度越低，速度就越慢 2)read committed读已提交 这种情况下，一个事务只能读取另一个事务提交之后的数据(给写操作加锁了)\此时,可能会产生不可重复读,幻读问题(脏读问题解决了) 此时，并发程度会降低,执行速度会变慢,同时也称为,事务之间的隔离性（事务之间的相互影响变小了得到的数据更准了）提高了. 3)repeatable read可重复读     mysql默认的隔离级别 这个情况下,相当于是给写操作和读操作都加锁了.此时,可能产生幻读问题,解决了脏读和不可重复读问题. 并发程度进一步降低,执行速度进一步变慢,事务之间的隔离性,进一步提高了. 4) serializable串行化 此时,所有的事务都是在服务器上一个接一个的执行的.此时,解决了脏读,不可重复读,幻读问题. 并发程度最低,执行速度最慢,隔离性最高,数据最准确