MySQL索引与事务

索引

什么是索引

索引，也可以理解为目录，在MySQL中，索引的本质就是引入额外的数据结构（主要为B+树），将待查询的数据进行组织，从而加快查询效率；

索引的使用

查看索引：show index from 表名;
创建索引：create index 索引名 on 表名（字段名）;
删除索引：drop index 索引名 on 表名;

可以看到表中的某些列已经有了索引：

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

创建一个索引并查看：

删除索引：

B+树

B+树的结构：

对于B+树而言，作为B树的改进版，主要有这样几个特点：

• 每个结点上存了几个数值，就有几个子树；
• 父结点的元素都存在于子树结点中，是子结点的最大值或最小值；
• 最下方放入叶子结点使用链表的方式相连；

B+树实现数据库索引，有下面几个优点：

• 查询速度快：
• 单个结点可以存储更多的数据，树的高度比较低，比较次数就更少；
• 叶子结点使用链表相连，便于范围查找；
• 每一个数据行，只需要保存在叶子结点上就够了，非叶子结点不必存储真实的数据行，只要存储用来做索引的id即可；

索引的使用场景

使用索引可以加快查询效率，但是这也意味着会消耗一些额外的空间，同时会影响增删改的效率；
因此，索引只有使用在一些合适的场景才可以有效地发挥它最大的作用，同时降低对数据库其他操作的影响。

索引的使用场景主要有：

1. 对于空间不紧张，而对时间更敏感时（空间换时间）；
2. 查询操作十分频繁，而增删改操作不频繁时；

最后，对于一个大表而言，创建索引和删除索引都是十分危险的操作，因为对大表创建索引会导致大规模磁盘IO，主机的磁盘被吃满，就会使主机变卡，从而无法对线上服务器及时进行响应；

事务

事务的概念

指逻辑上的一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败。
事务最核心的特性：原子性（代指不可分割的整体）；
对于事务而言，只有2种情况：所有操作都执行完毕且成功或者操作不执行，而操作不执行的情况实际上通过“回滚”恢复了之前操作的影响；
数据库的事务回滚，如何做到呢？
数据库里面的每个操作，在其内部都会有记录，对于事务更是如此，因此如果事务中间出现了问题，就可以根据之前的记录，来进行恢复；

事务的使用

开启事务：start transaction;
执行多条SQL语句;
回滚或提交：rollback/commit;
rollback即是全部失败，commit即是全部成功

事务的基本特性

• 原子性：最核心的特性，是事务诞生的初衷；
• 一致性：数据是一致的，正确的；
• 持久性：事务进行的操作都会写磁盘，因此事务一旦执行成功，所造成的修改就是持久化保存；
• 隔离性：描述多个事务并发执行的时候，所产生的情况

事务的并发执行

并发执行，可以有效提高执行效率，但是也会出现一些问题：

• 脏读问题：一个事务在执行过程中对数据进行了一系列修改，而在提交到数据库或完成事务之前，另一个事务B，读取了对应的数据，此时这个事务B读到的数据都是一些临时结果，后续可能马上就被事务A更改，因此事务B的读取行为就是“脏读”；

解决脏读的方法：给读操作加锁，相当于降低了并发程度，降低了效率，提高了隔离性；

• 不可重复读：事务A提交之后，事务B才开始读，然后再B的执行过程中，A又进行了修改，此时B执行中得到的数据可能不一致：

• 幻读：事务B读取过程中，事务A进行了修改，但没有直接修改B正在读取的数据，但却影响到了B读取的结果集，B两次读取到的结果就会不一致

解决幻读问题，其核心思路就是“串行化”，即在事务B执行时，事务A不做任何修改，从而保证读和写操作都是严格串行化执行的；
使用“串行化”的方式，隔离性最高，并发程度最低，数据的准确性最好，但同时速度最慢；

并发执行可以提高效率，而隔离是为了数据的准确性，但效率和准确往往是相互牵制的，因此在实际使用的时候，需要根据实际场景，来选择侧重于效率还是准确性；

数据库的隔离级别

在MySQL中，数据库的隔离级别有下面4种：

• read uncommitted ：并发能力最强，隔离性最弱；
• read committed ： 只能读取提交之后的数据，解决了脏读问题，同时并发能力下降，隔离性增加；
• repeatable read ： 针对读和写都进行了限制，解决了不可重复读问题，并发能力继续下降，隔离性进一步增强；
• serializable ： 严格的串行执行，解决了幻读问题，并发能力最低，但隔离性最高；

根据实际需要，我们也可以在配置文件my.ini中修改数据库的隔离级别，默认情况下的隔离级别是repeatable read