MySQL数据库 【索引事务】

目录

 一、概念

二、索引的优缺点

1、索引的优点

2、索引的缺陷

三、索引的使用

1、查看索引

2、创建索引

3、删除索引

四、索引底层的数据结构 

1、B树

2、B+树

五、索引事务 

1、概念和回滚 

2、事务的使用

3、事务的基本特性

4、并发会遇到的问题

（1）脏读

（2）不可重复读

（3）幻读

5、隔离级别

---

 一、概念

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

索引，可以理解成一本书的目录。

一个表里面有很多的数据，在查询表的时候，最基本的方式就是遍历表，一条一条的筛选，因此，就可以 给这个表建立索引（以 列 为维度进行建立的）来提高查找的速度

---

二、索引的优缺点

1、索引的优点

现在假设有这么一个表：

 比如，按照 id 这一列建立索引

在数据库上，我们就额外搞了一个空间，维护一些和 id 相关的信息，这里就通过一些特定的数据结构，维护表示 id 相关的索引的情况

后续再按照 id 来查询，就不必再直接遍历了，而是从索引中进行查询，根据索引就能够初步的锁定出数据所在的位置

---

2、索引的缺陷

1、消耗额外的空间

 对于数据库来说，数据都是存储再硬盘上的，索引数据也是在硬盘上的

2、有可能会拖慢增删查改的速度

 对于新增来说，此时不光要往表里面写数据，同时还要修改索引

 如果删除、修改的条件如果刚好和索引匹配，速度还能快一点

 但是如果涉及到索引列的删除 / 修改，这个时候，也要同时维护索引

---

三、索引的使用

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

1、查看索引

 语法：

show index from 表名;

我们可以看到，目前 student 这个表中存在主键

当表中存在主键的时候，内部就会自动给这个  列 来创建索引

这是因为，主键不允许重复，因此进行插入 / 修改 操作的时候，就需要先查询，看看 插入 / 修改 后的结构是否已经存在 

 我们可以看到，使用 unique 约束的时候，也会自动生成索引

 再重新创建一个 student 表和 class 表相关联

我们可以看到，班级表，由于 classId 是主键，自动生成了一个索引

学生表里面包含了两个索引，其中一个索引是由主键生成的，另一个是由于我们设置了外键约束所自动生成的索引

 这是因为，外键这里也涉及到自动查询

1、给学生表中插入一条记录，就需要查询classId 是否在 class 表的 classId 中存在，这个查询就使用到了 class 表中的 classId （主键自动生成的索引）

2、给班级表中删除一条记录，就需要查询 classId 是否在学生表中存在

 在上述情况中，主键、unique 、外键 这三种情况都会使 表 自动创建出索引

---

2、创建索引

语法：

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

 此时，就能够看到我们新创建出来的索引

这个创建索引操作可能会非常危险！！！

如果这个表是空的，或者表里面包含的数据本身就不多，创建索引就没事

如果表非空，并且里面包含了非常多的数据，那么创建索引会引起非常大规模的硬盘 IO 操作，进一步就会导致数据库被卡死

---

3、删除索引

语法：

drop index 索引名 on 表名;

 删除索引，只能针对 手动创建的索引，自动生成的索引使不能被删除的

删除索引这个操作也是非常危险的，原因与前面创建索引类似

那么如果现在我们必须给一个已经创建了很多数据的表 创建 / 删除 索引，并且这个数据库还是生产环境的数据库，咋办？ 

数据库服务器往往也不是单台服务器，为了整个系统的可靠性，通常会搞多个MySQL服务器节点，这些节点的数据都是一样的，能够提高相同的服务（其中某个挂了也不影响大局

先准备好一个新的 MySQL 服务器，把表和索引都创建好，然后把数据都导入过来，再把要替换的MySQL服务器关闭，把新的MySQL 服务器替换上去就行了 

---

四、索引底层的数据结构 

 MySQL 的索引的数据结构取决于MySQL使用的是哪一个存储引擎

MySQL这个程序，里面包含了很多个模块，有的是负责解析SQLDE ,有的是负责网络通信的，由的是负责存储数据的 ......

存储引擎本质上就是代码里的一个模块（这里包含了若干个代码文件以及一大堆具体的代码） 

具体如何存储数据，MySQL支持多种存储方案，innodb 是当下最主流的一种方式

以往我们谈到的数据结构都是内存中的数据结构，但是我们数据库这块组织数据使用的数据结构则是在硬盘上的

内存上的数据结构，对于访问操作是不敏感的（找数据的过程，花时间多，但是真正访问的时候时间不多

硬盘上的数据结构，对于访问操作来说，比较敏感，读写一次硬盘开销远远大于内存

哈希表，O(1) 复杂度 查询 / 插入 / 删除 / 修改数据

但是并不适合数据库的查询场景，这是因为哈希表只能做这种精确查询，没法做模糊查询和范围查询

哈希表需要把指定的 kye 通过 hash 函数映射出一个具体的下标才能定位到具体的位置

红黑树，O(logN)复杂度 查询 / 插入 / 删除 / 修改数据

也不适合数据库的查询场景

元素有序，可以处理范围查询，最大的问题在于红黑树的高度，会在元素个数比较多的适合，变得比较高

---

B+ 树是为了数据库量身定做的数据结构

注意：当前目前的讨论都是围绕 MySQL 的 innodb 这个存储引擎进行的（其它的存储引擎中可能会用到 hash 作为索引，只能应对这种精准匹配的情况）

 要了解 B+ 树，就需要先了解一下 B树 

1、B树

 B树 有时候会写作 B- 树，- 是连接符，不是减号

B树的核心思路，和之前学过的 " 二叉搜索树 " 差不多，B树 本质上是一个 N叉搜索树

N叉搜索树： 一个节点上可以保存 多个 kye ,N 个 key 就能延伸出 N + 1 个分叉来， N  个 kye 就划分出了 N + 1 个区间

B树查询元素的流程：

1、拿着要查询的元素，从根节点出发， 判定要查找的元素是否在根节点上存在

2、如果不存在，看这个元素是落在哪个区间里，就沿着这个区间的路线往下一个节点上找

3、最终找到叶子节点，还不存在，就是真的存在了

此时，每个节点上，都可以保存多个元素了

当总的元素个数固定的适合，相比于二叉搜索树，涉及到的节点的总数就大大降低了 ，同时树的高度也大大降低了

由此可知，B树的高度是远远小于二叉搜索树的，于是，在进行查询的时候，硬盘IO的次数也就减少了

对于数据库来说，每个节点都需要把数据从硬盘上读出来，才能进行比较

一个节点上有多个 key 和 一个节点上有一个 key  ，硬盘IO的开销是差不多的

对于B树来说，在进行插入元素 和 删除元素 的时候，就涉及到 拆分 和 合并

一个节点 可以存多个 key ，但是也不能无限的存，当存储的 key 数量达到一定程度的时候，就需要把这个节点给拆分，把这个节点中的一部分 key 以树的子节点的方式，来进行重新组织，保持当前这个节点中的 key 数量始终不会太多，这样就会衍生出新的节点

---

2、B+树

B+ 树 才是数据库索引的主角，在B树的基础上，又针对数据库的查询场景做出了一些改进 

 

B+ 树的特点：

上面是B + 树的特点，那么这些特点产生的优势是什么呢？ 

 B+树的优势：

 数据库的数据在逻辑意义上是以 "表" 的形式存储的

但是在物理意义上，不需要 “表格” 这样的数据结构，而是直接用 B+ 树 来存储这个表的数据，表格只是用户看起来这像是个表格

此时，非叶子节点的存储空间，消耗是非常小的，可以在内存中缓存一份！！！

此时，进行数据查询的时候，就可以直接通过 内存 来直接进行比较，从而更快速的找到叶子节点上的记录（又一次减少了硬盘IO的次数）

但是B树如果也要把元素存储到每个节点上，非叶子节点就会占据空间比较大，从而无法在内存中缓存了

---

五、索引事务 

1、概念和回滚 

事务的本质，就是把多个操作打包成一个操作来完成（让这多个操作，要么全都能执行成功，要么一个都不执行）

注意：一个都不执行，不是真的没有执行，执行是否成功，得执行了才知道

真正执行之前，是不知道在执行具体哪一步的时候会失败

如果是执行到中间的操作出错了，就需要将前面已经成功执行的操作进行还原，还原到最初没有执行的样子，我们将还原到最初没有执行的样子的这个操作称为： 回滚（rollback）

回滚是怎么样实现的呢？

只要把事务中执行的每个操作，通过特定的日志来记录数据库事务操作的中间过程，如果需要回滚，就直接把之前操作的 "逆操作" 来执行就可以了

那如果数据库服务器重启了呢?

因为我们是通过日志来记录事务执行的中间过程的，日志中的数据是始终在硬盘上存在的，即使是数据库服务器重启，就会在重新启动之后，针对之前没有回滚完的情况继续进行处理

---

2、事务的使用

（1）开启事务

start transaction;

（2）执行多条SQL语句

（3）回滚或提交事务

rollback/commit;

commit 提交事务：把这些SQL 按照原子的方式来进行执行（带有回滚机制）

rollback 手动触发回滚

一个事务务必要以这两个操作（提交事务和回滚）结尾！！！

如果没有这两个操作，接下里的各种 SQL 操作都会被认为是事务的一部分

---

3、事务的基本特性

事务的基本特性是非常重要的一个知识点

那么事务的基本特性有哪些呢？

1、原子性 ：保证多个操作被打包成一个整体，要么能全部被正确执行，要么就一个都不执行

2、一致性 ： 保证事务执行之前和事务执行之后，数据能够对上，数据不能离谱

        （约束、回滚机制 是保证一致性的重要手段）

3、持久性 ：  事务这里执行的各种操作，都是持久生效的（最终写入到硬盘中的），一旦事务执行成功了，这里的所以操作产生的修改，都是写到硬盘里的

4、隔离性 ： 并发执行事务的时候，隔离性会在执行效率和数据可靠之间做出平衡

"隔离" 描述的是同时执行的事务之间，相互的影响

隔离性越高，并发性就越低，数据越可靠，性能就越低

什么是并发呢？

可以简单理解成：同时执行

数据库是一个客户端 服务器 结构的程序，既然是服务器，服务器就可以同一时刻给多个客户提供服务，这多个客户端，都能够给服务器提交事务

如果提交的这两个事务，是修改不同的数据库 / 不同的表，相互之间是没有什么影响的

如果这两个事务，修改的是同一个表，就可能存在麻烦

当顺序上存在差异的时候，顺序的先后就可能会影响到最终的执行结果，这就是并发执行事务带来的问题

---

4、并发会遇到的问题

（1）脏读

此外，并发执行事务的时候，还有可能遇到脏读的情况

脏数据：临时的，并非是最终准确的结果

那么如何解决脏读问题？

给 "写" 操作加锁 ，一个事务A 写的时候，其它事务B不能读了，直到事务A 写完数据，提交事务，其它的事务B才能来读取数据

引入了 “写” 加锁，降低了两个事务之间的并发性，提高了隔离性，降低了效率，使数据更准确了

---

（2）不可重复读

在同一个读取数据的事务中，可能会涉及到多次操作，多个 "读" 操作读到的数据可能会不一样

如何应对不可重复读？

给 "读" 操作也加锁

给 "写" 操作加锁的意思：我在写的时候，别人不能读（除非使我写完提交，别人才能读），此时，别人读的过程中，我还可以再开启一个事务来写，第二个事务提交之前，其它读的事务读到的就是新版本数据了

给 "读" 操作加锁的意思：别人读的时候，我不能写了。

这个时候，并发程度又进一步降低了（执行效率降低了），隔离性进一步提高了（数据更可靠了）

---

（3）幻读

 一个事务，在多次读的过程中，虽然多次读的数据的值是一样的但是结果集不同

比如：第一次读是10条记录，第二次读是11条记录，这11条记录中的10条和之前的10条是一模一样的，但是多出来一个，我们称为幻读

可以视为，是不可重复读的特殊情况

如何解决幻读？

办法只有一个： 串行化

彻底放弃并发执行事务，所有的事务都是一个挨着一个的串行执行（执行完一个事务，再执行下一个事务） 

---

5、隔离级别

MySQL 提供了四种事务的隔离级别，MySQL可以配置自己的隔离级别是哪一个

1、read uncommitted (RU) 允许读未提交的数据（脏读、不可重复度、幻读），隔离性最低，并发程度最高，数据可靠性最低，效率最高

2、read commmittded (RC) 允许读取已经提交了的数据（给写加锁了），解决脏读、存在不可重复读和幻读，隔离性提高了，并发性降低了，数据可靠性提高了，效率降低了

3、repeatable read （RR）默认的隔离级别，可以重复读取数据（给写操作和读操作都加锁），解决了脏读和不可重复读的问题，存在幻读问题，隔离性又提高了，并发性又降低了，数据可靠性又提高了，效率又降低了

4、serializable  事务彻底的串行执行，解决了脏读、不可重复读、幻读的问题，隔离性最高，并发性最低（没有），数据最可靠，效率也最低