【MySQL】索引和事务

系列文章目录

第一章 数据库基础
第二章 数据库基本操作
第三章数据库约束
第四章表的设计
第五章查询进阶

前言

索引和事务是mysql中十分核心的机制。索引就像目录一样，能够提高查询速度；

一、索引(index)

在MySQL中，对索引的使用包括创建索引、查看索引以及删除索引。

查看索引

基础的SQL语句为 show index from 表名;
在下面，student2表中是最基本的表，没有任何约束。而查看student2表中的index所得到的结果是为null的。


接下来我们看看student表，在该表中以id作为表的主键，而当我们对student表进查看索引之后我们可以看到，student表索引为id 因此我们可以知道，对于主键的列会自动生成索引。 同时，unique、foreign key也都是会自带索引的。

创建索引

最基本的SQL语句为 create index indexName on 表(列);
创建索引都是根据具体的列来创建的，后续查询的时候也需要依靠这一列的条件查询才能够提高查询速度。
对于刚才的student2，我们知道他是没有主键等约束的，因此我们可以尝试对student2中的id创建索引。

create index index_student2 on student2(id);

这里需要注意，创建索引对于cpu资源/硬盘IO的消耗都是比较大的，当遇到比较大型的表的时候，需要谨慎创建防止数据库崩溃。

删除索引

SQL语句的句式为 drop index 索引名 on 表名;
同理，删除索引也会也是会涉及到大量IO操作的。

索引的细节内容

索引的引入是通过其他的数据结构加快了查询速度，而索引所采用的数据结构是专门的B树。每个节点中存储一定数量的数据，加快了访问的速度。
如下图所示，每个方框代表一个节点。
假设此时查找一个数据key，那么就需要到节点中查找这个数据，
[1]每个节点中的数据都是有序的，进入节点中与这些数据进行比较（二分查找）。
[2]同时B树也会控制每个节点上的key的数量,当插入的元素变多了以后就会节点分裂出更多的子树。
[3]每个节点中的数据都放在一块连续的存储空间中，因此一次硬盘IO就可以读取整个节点。
[4]通过这种方式，大幅降低节点个数，也减少了硬盘IO的操作降低系统资源的消耗。

而B树的最终形态，我们称之为B+树，同样也是N叉搜索树
在B+树中，父节点中的数据会在子节点中重新出现，以N叉树的形式分裂出来，在每一层中的节点都从大到小有序排列。
假设查询id < 9 and id > 3时，就会从数据为8的入口进入，不断深入并通过二分查找的方式进入节点中。此时的范围查询就非常简单高效了。

与B树不同的是，B+树中查找过程都必须到达最底层，即使在第一层中就已经找到对应的数据，也不能直接返回。因此，B+树中查询操作消耗的IO次数都是一样的，查询消耗的时间是稳定的。
B+树的的优势有：

1. 非常方便进行遍历和范围查询；
2. 当前任何一次查询操作最终都是要落到叶子节点上的，查询操作消耗的时间是稳定的。
3. 由于叶子节点是数据的全集，对应的非叶子节点中，都是重复出现的数据。就可以把每行数据都关联到叶子节点这一层，非叶子节点只保存一个单纯的key即可。
4. 由于非叶子节点中只存储了单纯的key(如id)，因此占用的空间就比较小，于是非叶子节点就可以直接保存到内存中。大大提高了效率。

当存在索引/主键时，根据索引进行查找和不根据索引查找之间的差距是特别大的。
当根据索引进行查找时，可以通过B+树的方式快速找到所需要的数据；
当不根据索引查找时，只能按照遍历的方式进行查找。
每次创建出来新的索引，就会生成一个新的B+树，与其他索引是相互独立的。

二、事务

在日常开发中许多操作都是需要多个SQL配合完成的，当执行多个SQL操作的时候可能会引起系统崩溃等意外情况的出现。可能会出现前面的SQL执行成功，后面的SQL执行失败。这是不允许出现的问题的。
因此在SQL中就引入了事务。即将多个操作打包成一个整体，这样一来就能够保证这个整体要么都执行成功，要么就一个都不执行。有效避免了“中间状态”问题的发生。
这种打包操作就被称为“原子性”
虽然所有的SQL语句打包成了一个整体，但是这些语句并不是一个都不执行的。确保原子性的一个机制是回滚(rollback) 当执行到某一条SQL的时候出现了问题，数据库就能自动把前面的SQL造成的影响恢复如初。
为了实现回滚机制，数据库会在执行事务的时候记录日志，当整个事务执行完之后没有出现问题，就不需要这些日志了。
如果执行事务过程中出现了问题，MySQL就能够根据日志中记录的内容进行恢复操作。
当服务器出现问题需要重新启动的时候，就会根据记录的日志内容进行回滚。

事务的特性

1、原子性：有回滚机制，能够触发还原。
2、一致性：在执行事务之前和之后，数据是一致的。
3、持久性：在程序重启/主机重启，数据仍然能够存在。
4、隔离性：多个客户端同时向服务器发起事务，即出现需要并发处理的情况。在这种并发执行的情况下可能会出现几个问题。
[1] 脏读问题：事务A在执行过程中产生的“临时数据”被事务B进行读取。这就导致了B读取到的数据和A后续提交的数据是不一致的。为了解决这一问题，于是针对“写”操作进行加锁，即写的过程中不允许读。虽然降低了执行效率，但是提高了数据的准确性。
[2] 不可重复读问题：当事务A针对数据进行修改之后，事务B在这时候读取数据，在执行B的过程中又有一个事务C对数据进行了修改。这导致了B读取数据一半的时候发生了数据改变的情况。于是**对“读操作”进行了加锁，即读的过程中不允许写。**在对读写操作都加锁以后，就会使”并发程度“进一步降低，”隔离性“进一步提高。但是ABC三个事务都不能并发执行了。
[3] 幻读问题：假设事务A读取并修改了数据，事务B读取这一数据，此时事务C不能修改这一数据，但是对表进行了数据增删等操作，导致B读取到的数据集不同。这其实并不违反读写操作的加锁，因为已有的数据内容使一致的，但是出现了数据集的变化。可以认为使不可重复读的特殊情况。 如果要解决这个问题的话，就只能停止事务并发执行，也被成为 串行化。

隔离性的选择

对于隔离性中既要注意脏读问题，又要主要不可重复读问题。为事务执行效率带来了很大的限制。因此要在不同的情况下配置不同的隔离情况。

1. read uncommitted 允许读取其他事务未提交的数据 => 脏读+不可重复读+幻读 并发程度高，隔离性最低。
2. read committed 只能读取其他事务提交后的数据 => 解决了脏读+不可重复读+幻读 并发程度降低，隔离性提高。
3. repeatable read:针对读操作和写操作都进行了加锁 => 解决了脏读+不可重复读，存在幻读 并发程度降低，隔离性提高。
4. 串行化：所有事物串行执行 => 解决了脏读+不可重复读+幻读 并发基本不存在，隔离性最高。

最后 附上事务的编写案例

start transaction;
-- 阿里巴巴账户减少2000
update accout set money=money-2000 where name = '阿里巴巴';
-- 四十大盗账户增加2000
update accout set money=money+2000 where name = '四十大盗';
commit;

总结

索引和事务都是MySQL中非常核心的机制和知识点。在索引中我们除了索引的增删查，还需要掌握它的核心机制。
在事务中，我们了解了它的原子性、持久性、一致性、隔离性。其中隔离性是它核心的机制，根据不同的情况选择不同的隔离方式，以适应不同的使用环境是隔离性的核心问题。