MySQL索引事务

1.索引

1.1 索引的概念

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

1.2 索引的作用

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

1.3 索引的使用场景

使用索引要考虑以下几点，在进行使用。

数据量较大，且经常对这些列进行条件查询。
该数据库表的插入操作，及对这些列的修改操作频率较低。

索引会占用额外的磁盘空间。

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

1.4 索引的使用

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

创建索引，一定要在建表之初就创建好，表在使用时创建索引，要上表数据是空着的或者数据很少，此时创建索引没关系，但是如果表本身存在大量数据，此时创建索引操作就会出发大量的硬盘IO，这就有可能会把数据库搞挂了。

但是实际开发中会遇到表已经在使用，但还是要给表添加索引，这时我们就需要在弄一个机器，搭建数据库，把生产环境数据库的数据，导入到新的数据库中（这个非常耗时）建议用新的数据库的这个机器，代替旧机器，把客户端访问数据库的ip地址修改一下就可以完成了。

-- 查看索引
show index from 表名;
 
-- 示例
show index from test;
 
-- 创建索引
-- 对于非主键、非唯一约束、非外键的字段，可以创建普通索引
create index 索引名 on 表名(字段名)
 
示例
create index idx_test_name on test(name)
 
-- 删除索引
drop index 索引名 on 表名;
 
-- 删除测试表中name字段的索引
drop index idx_test_name on test;

 

1.5 索引底层的实现

索引底层数据结构其实是通过B+树来实现的。

首先了解B+树先来了解B树

B树是一个N叉搜索树，B树就是在二叉树这里进行了扩展。一个节点上可能包含N个值，N个值划分出了N+1个区间。

 同样高度的树，能表示的元素相比于二叉树来说，就多了很多，使用B树来查询的时候，论比较次数，比二叉树搜索树要更多。但是，这里的关键在于，同一个节点的这些key都是一次硬盘IO就出来了，虽然总的比较次数增加，但是硬盘IO的次数太少，一次硬盘IO相当于内存中进行了1w次比较

B+树，是在B树上做出了改进，同样也是N叉搜索树，每个节点包含多个key，划分出N个区间 

 

 B+树的特点

1、N叉搜索树，每个节点上包含N个key，N个key划分出N个区间

2、每个节点的N个key中，会存在一个最大值（设定成最小值也是一样）

3、每个节点中的key，都会在子树中重复出现（重复出现的好处：所有的数据都包含在叶子节点这一层中）

针对B+树的查询的时间是稳定的，查询任何一个元素，都是需要从根节点查询到叶子节点的。过程中经过的硬盘IO次数是一样的（B树有的时候硬盘IO多，有的时候少），B树相对于B+树来睡哦就没有那么稳定。

4、把叶子节点之间使用链式结构相连

2.事务

2.1 为什么使用事务

比如在现实中的银行转账，比如爸妈给我们转5000元，正常情况应该是我的账户+5000，爸妈的-5000，如果在进行转账时，网络发生了错误或者数据库挂了，那爸妈的账户还是会少5000元，而我们的账户并没有加5000元，这个时候就需要使用事务来控制，事务就可以控制以上两个事件都成功才能成功，一个成功一个失败则也是执行失败

2.2 事务的概念

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

2.3 事务的使用

-- 开启事务
start transaction
 
--执行多条SQL语句
create table account(id int,name varchar(20),money int);
insert into account values(1,'ff',10000),(2,'xx',3000);
update account set money = money -2000 where name = 'ff';
update account set money = money + 2000 where name = 'xx';
 
--回滚或提交：rollback/commit; rollback 既是全部失败，commit既是全部成功
commit;

 

使用回滚可以将刚刚修改的数据还原回去。

事务虽然让数据更靠谱了，但是也付出了执行效率的代价，数据库对于事务这里有特殊的机制（undo logo + redo log）通过日志、printIn，写到文件里，记录之前的数据，进行操作。数据库中间虽然挂了，但是日志已经记录下来了，等数据库重启后，读取之前的日志，看是否有这种执行了一半的事务，如果有就会把这前面的操作进行回滚。

2.4 事务的核心特性

1、原子性：通过事务，把多个操作打包到一起（事务最重要的特性）

2、一致性：相当于原子性的延伸，当数据库中间出问题了，不会产生像上诉这种“钱凭空消失”不科学的情况。另一方面，还通过约束，来避免数据出现一些非法的情况。

3、持久性：事务任何的修改，都是持久化存在的（写入硬盘的），无论是重启程序，还是重启主机，修改都不会丢失，（数据库本身就是为了持久化存储）

4、隔离性：多个事务并发执行的时候，可能会带来一些问题，通过隔离性来对这里的问题进行权衡，看你是希望数据尽量准确，还是速度尽量快。

如果多个客户端吗，同时给数据库服务器发起事务请求，这个时候就叫做并发执行事务，如果多个事务时修改不同的表，那问题不大，如果是修改相同的表，就可能产生出一些bug。

举例说明：

典型bug1:脏读      给写操作加锁（写的时候不能读）

当有两个事务1，2。事务1修改了某个数据，但是事务还没有提交，事务2读取了同一个数据，此时事务2读取到的数据很可能是一个脏的数据，因为事务1后续可能还要再次修改这个数据。

解决脏读核心问题，核心思路就是降低事务的并发程度，给写操作加锁（加锁，就意味着释放锁之前，你是不能访问的）写的时候不能读，写完，并且提交事务之后（释放锁），才让别人读

典型bug2:不可重复读       给读操作也加锁（读的时候不能写）

不可重复读，像脏读，但是这是在写加锁的前提下导致的问题，虽然写加锁了，但是可以分多个事务，多次提交的方式来修改数据，虽然没有之前那么频繁，也是可能出现的。

有事务1，2其中事务，先修改数据（写加锁），此事务2想读数据，就需要等事务1提交完。等到事务1终于提交完之后，事务2开始读取数据（事务2中可能会多次读取数据）

又来了一个事务3，事务3有修改了上述数据，导致事务2在读的过程中，两次读到的结果不同。刚才加锁约定的是写的时候不能读，没约定读的时候不能写，所以出现了事务2正读者，事务3与又开始写了。

解决此问题就需要给读加锁，约定读的时候，就不能写

典型bug3:幻读

事务1、2，事务1修改数据提交，事务2开始读数据，此时事务3新增了一个其他的数据，此时事务2就可能出现两次读取的结果集不同

解决幻读问题，串行化，不在进行任何并发了，每个事务都是串行执行（执行完第一个，在执行第二个，在执行第三个）

2.5 事务的隔离级别

mysql在配置中，提供了隔离级别这样的选项，我们程序员就可以根据需要，调整隔离级别，适应不同的情况

1、read uncommitted 读未提交

读未提交并行程度是最高的，隔离程度是最低的，效率是最高的，数据是最不靠谱的，此时可能会出现脏读+不可重复读+幻读

2、read commited      读已提交

读已提交，相当于给写操作加锁了，并行程度降低了，隔离程度提高了，效率会低一些，数据会靠谱一些，此时可能出现不可重复读+幻读（事务之间，影响越小，隔离程度越高）

3、repeatable read     可重复读

可重复读，相当于给读操作和写操作都加锁，并行程度降低了，隔离程度提高了，效率又降低了，数据又更靠谱了，数据又更靠谱了

4、serializable            串行化

串行化让所有的事务串行执行，并行程度最低，隔离程度最高，效率最低，数据最靠谱