SQL中的索引和事务

索引

概念

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

索引内部数据结构

聚簇索引和非聚簇索引
聚簇索引：把数据的每一行数据都给放到索引结构中

非聚簇索引：把数据的每一行还是按顺序存放在磁盘上存储，索引里面除了存储这个用来构建索引的列之外，再存一个行号/或者主键id
B+树

1. 父节点里面的值，会作为子节点的最大值出现（也可以是最小值），这样就可以让所有叶子结点都包含了整体数据的全集
2. 叶子节点按照顺序通过链表的方式连接起来，很方便实现范围查找
3. 每一行数据的其他列信息，只存在于叶子结点上，非叶子节点仅仅包含索引列的数据。非叶子结点占的内存就比较少，直接可以放在内存中，后续查找的时候，就可以直接读取内存，减少很多次的磁盘磁盘操作

作用

• 数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍的内容、书籍目录的关系
• 索引所起的作用类似于书籍目录，可以快速定位、检索数据。

使用场景

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

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

使用

     创建索引主键约束（primary key）、唯一约束（unique）、外键约束（foreing key）时、会自动创建对应列的索引。

查看索引

show index from 表名；

创建索引

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

create index  索引名 on 表名（字段名）；

删除索引

drop index 索引名 on 表名；

事务

概念

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

作用

     控制组成事务的SQL语句全部执行或全部不执行。

使用

   （1）开启事务：`start transaction；`
   （2）执行多条SQL语句
   （3）回滚或提交：rollback/commit

关于事务的问题

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

2. 事务有哪些特征?

1. 原子性：事务是不可再分割的部分
   2） 持久性:事务执行完毕之后，数据就存到磁盘上了，重启主机数据也依然存在。
   3） 隔离性：并发执行事务所产生的问题和解决方案

3. MySQL并发执行事务可能产生的问题
   1）脏读
   一个事务A在修改数据，另一个事务B直接读取A正在修改的数据，此时B读到的就是一个脏数据。因此B读到的数据很可能就在A的后续操作中又进行了修改，相当于读到的数据只是一个“中间状态”，读取脏数据的动作叫做脏读。

解决方法：引入写加锁，在事务A在写数据的时候其他事务不可读，对并发做出了一定的限制，并发程度降低，隔离性提高了。

2）不可重复读
事务A修改数据然后提交，提交完毕之后事务B开始读取数据，事务B包含了很多次的读操作，如果事务B两次的操作的到的结果不一样，就是 不可重复读。
不一样的原因就是事务A虽然把数据已经提交了，但又可能执行了一次事务A，又把数据修改了。

解决方法：引入读加锁，必须是事务A写完才能被事务B读。在事务B读完之后事务A才能写。

3）幻读
事务B正在读取数据，事务A插入了新的数据/删除了某个数据，事务B两次读取到的数据集的个数发生了变化。

解决方案：通过串行化解决

4. SQL中事务的隔离级别
   四种隔离级别
   1）read uncommitted 没有做出任何隔离的限制，因此三种问题都存在
   2）read committed 相当于进行了写加锁，并发程度降低了，隔离级别提高了。
   3）repeatable read 相当于进行了读写加锁，并发程度又降低了，隔离级别又提高了。
   4）Serializable 串行化，并发程度最低，隔离级别最高，三种问题都能得到解决。