【MYSQL】索引和事务

🥰🥰🥰来都来了，不妨点个关注叭！
👉博客主页：欢迎各位大佬!👈

在这里插入图片描述
本期内容讲解 MySQL 中的索引和事务，在学习的过程中，我们需要经常问自己为什么

文章目录

1. 索引
- 1.1 索引的概念
- 1.2 索引的目的
- - 1.2.1 为什么使用索引会加快查询速度？
- 1.3 索引的使用
- 1.4 索引的分类
- 1.5 索引的优缺点
- 1.6 MySQL 的默认存储引擎 InnoDB 背后的数据结构 —— B+ 树
- - 1.6.1 B树
  - 1.6.2 B+树
2. 事务
- 2.1 事务的概念
- 2.2 事务的使用
- 2.3 事务的四大特性 —— ACID
- - 2.3.1 原子性
  - 2.3.2 一致性
  - 2.3.3 持久性
  - 2.3.4 隔离性
  - - 1）脏读
    - 2）不可重复读
    - 3）幻读

1. 索引

1.1 索引的概念

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

1.2 索引的目的

【目的】就比如说，本期文章带有目录（笔者每篇文章都带有目录）~ 通过目录，我们就对这期内容有一个大致的了解，并且通过目录，我们可以快速的定位我们想要的内容，而索引的作用和目录的作用类似，都是加快查询速度~

1.2.1 为什么使用索引会加快查询速度？

首先我们知道，MySQL 数据库文件存储在磁盘上的，磁盘 I/O 是数据库操作比较耗时的一部分

无索引，数据库会进行全表扫描，它必须读取表中的每一行数据来查找匹配的行，时间效率为 O(n)，当表的数据量非常大时，就会导致大量的磁盘 I/O 操作，非常耗时间！
有索引，可以直接跳到索引指示的数据位置，不用扫描整张表，大大减少了磁盘 I/O 操作的次数，减少时间，如 MySQL 的默认存储引擎 InnoDB 默认使用 B+ 树来作为索引的数据结构，而 B+ 树的查询效率非常高，时间复杂度是O(logN)！（本期内容后面将会具体介绍 B+ 树）

我们知道索引是一种特殊的文件，MySQL 索引文件主要存储索引，数据库文件存储的数据不仅包括全部数据，还包括索引等信息，索引文件与数据库文件相比，体积小很多，通过查询索引，再映射到数据库记录，查询效率就会高很多~

【举例】
还是结合目录来看，通过目录可以快速定位到哪一个位置，比如查字典，可以通过目录，快速找到需要的内容在哪一页，没有目录的话，我需要从字典的第一页，每一页一页的翻，找到想要的内容，想想是不是很耗时呢？索引的作用也是如此！加快查询的作用！

1.3 索引的使用

【情况一】对于创建主键约束，唯一约束，外键约束时，会自动创建对应列的索引

首先创建一个 student 表，以 id 为主键（此时创建了主键约束，会自动创建对应列的索引）

 create table student (id int primary key,username varchar(50));

在这里插入图片描述

查看索引

 show index from 表名;

从下图中可以看到，主键 id，自动创建了索引
在这里插入图片描述
如果 id 没有加主键约束，是不会自动创建索引的~ 如下图：

在这里插入图片描述
【情况二】创建普通索引，对于非主键、非唯一约束、非外键的字段，可以创建普通索引

创建索引

create index 索引名 on 表名(列名);

在这里插入图片描述

删除索引

drop index 索引名 on 表名;

在这里插入图片描述

（可以动手多操作哦，才会记忆深刻呀~更加利于理解哦！）

1.4 索引的分类

在这里插入图片描述

1.5 索引的优缺点

【优点】

加快查询速度：通过索引可以快速定位到满足查询条件的数据行，减少数据的扫描范围，从而提高查询效率。例如，在一个包含大量用户信息的表中，如果经常根据用户姓名进行查询，为姓名列创建索引后，查询速度大大提升；
保证数据唯一性：可以通过创建唯一索引来确保表中某列或某些列组合的数据具有唯一性，防止出现重复数据，比如，在用户表中，为身份证号码列创建唯一索引，就可以保证每个用户的身份证号码是唯一的；
支持数据排序：索引可以按照指定的列进行排序，当查询需要对结果进行排序时，使用索引可以避免数据库在查询时进行额外的排序操作，提高查询性能。比如，在订单表中，为订单时间列创建索引，当按照订单时间查询并排序订单时，数据库可以直接使用索引来获取有序的数据。

【缺点】

占用存储空间：索引本身需要占用一定的磁盘空间来存储索引结构，随着数据量的增加和索引数量的增多，占用的空间也会相应增大，比如，一个大型数据表创建多个索引后，可能会使数据库文件的大小增加很多；
增加维护成本：在数据插入、更新和删除时，索引也需要进行相应的更新操作，这会增加数据库的维护成本和时间开销。例如，当向表中插入一条新记录时，如果该表有多个索引，那么每个索引都需要进行更新以保证索引的准确性和一致性；
降低写入性能：由于写入数据时需要同时更新索引，所以会降低写入操作的性能，比如，在批量插入数据时，没有索引的表插入速度会比有索引的表快很多

1.6 MySQL 的默认存储引擎 InnoDB 背后的数据结构 —— B+ 树

在本期内容开头就介绍了，数据库索引的作用是为了加快查询速度的~ 那么，我们会思考，其索引背后的数据结构是什么呢？并且能够让它加快查询速度！

我们可以回想一下之前学过的数据结构，其中，二叉搜索树和哈希表就是比较适合查询的，但是很遗憾的是，它们两个都不适合于数据库索引的底层数据结构：

二叉搜索树：二叉搜索树查找的时间复杂度是树的高度，我们知道数据库一般都是将数据存储在硬盘上，数据量很大的时候，如果使用二叉搜索树，则树的高度也会很大，导致查询效率会很慢，
哈希表：哈希表它是由数组和链表组成的，查找的时间复杂度为 O(1)，尽管时间复杂度低，但是，它的 key 不是有序的，并且对于数据库中的大于、小于的范围查找或者是 LIKE 类似的模糊查询，哈希表都是不能够做到的，比如，我们要查询学生的序号在 202110120510 到 202110120534 之间的学生信息，哈希表是做不到的~

噔噔噔!!! 这里，就要介绍一种特殊的数据结构，B+树 —— 专门为了数据库索引量身定做的数据结构！

1.6.1 B树

介绍 B+ 树前，先介绍一下 B 树，也叫做 B- 树，注意!!! 这里的 ‘-’ 不是减号，只是连接符哦！很多小伙伴看到有 B+ 树，可能会想当然的以为也有 B- 树

B树可以认为是一颗N叉搜索树，结构如下：

在这里插入图片描述

我们可以通过图看到，当节点的子树多了，节点上保存的 key 就多了，在相同个数 key 的情况下，B 树的高度就比二叉搜索树的高度要低很多，对 IO 操作的次数就越少，这样查找性能就会比较高！

1.6.2 B+树

我们先来了解一下 B+ 树的特点~

B+ 树的特点：

一个节点可以存储 N 个 key，N 个 key 可以划分出 N 个子区间（不是 N+1 个）
每一个 key 都会在子区间出现，且为子区间的最大值
B+ 树的叶子节点是首尾相连的，类似于一个链表
B+ 树的非叶子节点只用于索引并不保存数据，只有叶子节点存储着索引和数据

到底是一个什么样的数据结构呢？我们一起来看看！

在这里插入图片描述
既然有 B 树，那么为什么还要有 B+ 树呢？

我们一起来分析一下 B 树的优缺点：

B树优点：每个节点都存储了索引和对应的数据，在查找离根节点近的节点时，查找效率是很高的，不用每次都查找到叶子节点（与B+树相比，因为 B+ 树必须得查找到叶子节点）
B树缺点
不利于范围查找，即不利于区间查找，比如要找 5 - 14 的索引值，那么 B 树就需要多次从根节点逐个查找，而 B+ 树的叶子节点是链表连接起来的，且是从小到大依次有序的，在 B+ 树中，只用找到 5 索引和 14 索引的叶子节点，从 5 沿着链表遍历到 14 即可！

再来分析一下 B+ 树的优点：

查询任意一个节点最终都会落到叶子节点，每次 IO 访问次数是一样的
B+ 树的所有叶子节点构成了一个完整的链表，适合进行范围查找
只有叶子节点是存储着完整的数据，非叶子节点只记录索引，这样这些非叶子节点占用的内存就十分的小，又进一步减小 IO 操作次数

【补充知识】

1）回表

比如在有一些表中，有多个索引，如上述创建的 student 表里，我们在 username 这一列创建了一个索引，此时，表的数据还是会根据主键id 构建出 B+ 树，通过叶子节点将数据组织起来，其次，会根据 username 这个列创建一个B+树，但这个 B+ 树的叶子节点只存储主键id是什么，此时如果你是通过 username 这个索引来查找的，会通过叶子节点拿到的 id 索引再去由 id 创建的B+树里再查一次，即查两次B+树，上述过程，在数据库中就叫回表！

上述过程是 Mysql 自动完成的，用户是完全感知不到的，这是因为主键索引也是聚簇索引，叶子节点存储整个数据和索引，而其他索引就是非聚簇索引，叶子节点只存储主键和索引，因此，使用非聚簇索引进行查询的时候需要再拿着这个查询到的主键再在聚簇索引中进行一次查询，即回表

2）聚簇索引和非聚簇索引

在 MySQL 的默认 InnoDB 引擎中，每个索引都会对应一颗 B+ 树，而聚簇索引和非聚簇索引最大的区别在于叶子节点存储的数据不同，聚簇索引叶子节点存储的是行数据，因此通过聚簇索引可以直接找到真正的行数据；而非聚簇索引叶子节点存储的是主键信息，所以使用非聚簇索引还需要回表查询，因此，我们可以得出聚簇索引和非聚簇索引的区别主要有以下几个：

存储内容：聚簇索引叶子节点存储的是行数据；而非聚簇索引叶子节点存储的是聚簇索引，通常是主键 ID；
效率：聚簇索引查询效率更高，而非聚簇索引需要进行回表查询，因此性能不如聚簇索引；
数量上：聚簇索引一般为主键索引，而主键一个表中只能有一个，因此聚簇索引一个表中也只能有一个，而非聚簇索引则没有数量上的限制

2. 事务

2.1 事务的概念

我们先来想一下这个场景，七夕节到了，小丁准备给他的女朋友小万转账 1314，那么 sql 语句应该如下：

update account set  money = money - 1314 where name = ‘小丁’;
update account set  money = money + 1314 where name = ‘小万’;

假设在执行完第一条语句后，数据库突然崩溃了或者主机宕机了，此时就出现了问题，小丁的钱扣了，但是小万没有收到转账，小万就会很生气，觉得没转就没转，而不能这样骗她！小丁就百口莫辩了，明明自己转了呀！现实生活中，有很多这样的支付场景，那么如何解决呢？

事务则是解决上述问题的，事务指的是逻辑上一组操作，组成这组操作的各个单元，要么全部成功，要么全部失败，在不同环境中，都可以有事务，对应在数据库中，就是数据库事务，通俗的来讲，就是把 sql 语句打包在一起，要么全部都执行成功，要么全部都不执行，不会出现执行到一半的情况，注意，这里的全部都不执行，并不是真的没执行，而是执行到一半，出现问题后，选择恢复到原来的样子，将数据灰度到未执行的状态，这个恢复操作，就叫做 “回滚”（rollback）

2.2 事务的使用

开启事务：start transaction;
中间放要执行的 sql 语句
回滚或提交: rollback/commit;
（说明： rollback表示“全部失败”，commit表示“全部成功”）

具体代码如下：

start transaction; //开启事务
update account set money = money - 1314 where name = '小丁';
update account set money  =money + 1314 where name = '小万';
commit;   //提交事务

2.3 事务的四大特性 —— ACID

2.3.1 原子性

事务的初心就是原子性，事务是一个不可分割的单位，数据库事务里的 sql 语句要么全部执行，要么全部不执行，不会出现执行一半的情况，如果执行一半出现问题就会进行 “回滚” 操作（强调事务是一个整体）

2.3.2 一致性

事务执行前后，数据都是一个合法的状态，就像上面的转账情况，在转账前和转账后数据都应该是一个合法的状态，是一致的（强调数据的合法状态）

2.3.3 持久性

事务修改的内容是写入硬盘的，持久存在，重启也不会消失（强调事务修改内容都是写入硬盘的，具有持久性）

2.3.4 隔离性

多个事务并发执行的时候，每个事务应该感受不到其他事务的存在，各个事务是隔离的（强调多个事务并发执行，各事务是隔离的，相互不影响）

但是，不同的事务隔离级别会导致不同的并发问题，如脏读，不可重复读，幻读，下面将具体介绍这三个问题：

1）脏读

【概念】脏读就是一个事务读取到了另一个未提交事务修改的数据

【举例】事务A 对数据库中的小万余额进行读取，余额为 100，接着再执行更新操作，将余额更新为 500，此时，并没有提交事务，而另一边，事务B 对数据库中的小万余额进行读取，那么事务B就读取到了事务A更新的金额，余额为 500，但是此时事务A并没有提交事务！随时可能发生回滚操作，如果上述情况事务发生回滚，那么余额为 100，而事务B读取到了事务A的过期数据500，这个就是脏读

【解决方式】给事务A修改数据这个操作进行写加锁，当事务A修改数据的时候，其它事务不能对该数据进行读取，这样就意味着此时写操作和读操作不能并发执行了，降低了并发程度，即降低了小效率，但是提高了隔离性，即提高了数据的准确性