【数据库必备知识】索引和事务

慧天城寻

已于 2023-06-29 22:50:53 修改

阅读量816

点赞数 12

分类专栏：小白的数据库学习之路文章标签： b树 b+树数据库索引数据库事务 mysql

于 2023-06-24 19:38:52 首次发布

本文链接：https://blog.csdn.net/qq_60366454/article/details/131169163

版权

小白的数据库学习之路专栏收录该内容

5 篇文章 7 订阅

订阅专栏

数据库系列文章

1. 零基础带你快速上手SQL语言
 2. 玩转表及其数据
 3. 上手表设计

4. 索引和事务

5. 最新版MySQL的JDBC编程

📖前言

本文讲解的是数据库中非常重要的知识, 也是面试中会常常问到的问题 ---- 索引和事务

1. 索引

1.1 索引的概念

索引是一种特殊的文件, 包含着对数据表里所有记录的引用指针. 可以对表中的一列或多列创建索引, 并指定索引的类型, 各类索引有各自的数据结构实现. 索引存在的意义, 就是为了加快查找的速度, 因为使用索引, 便减少了遍历的步骤.

我们可以将索引理解为一本书的目录, 当我们想查找书中某个内容时, 直接通过目录翻到指定页面即可, 而省去了逐页翻书查找的步骤, 这样便加快了我们查找的速度.

1.2 索引的作用

数据库中的表、数据、索引之间的关系，类似于书架上的图书、书籍内容和书籍目录的关系。
索引所起的作用类似书籍目录，可用于快速定位、检索数据。
索引对于提高数据库的性能有很大的帮助。

不过在使用索引中, 查找速度是变快了, 但是也付出了一定的代价.

需要付出额外的空间为代价, 来保存索引数据.
索引可能会拖慢新增, 删除, 修改的速度.

举例子来讲就是, 当书籍内容很多时, 目录的内容也会很多, 光目录就会占有几十页的空间, 当我们在写一些计划书或者论文时, 正文内容是会不断修改的, 目录随之也得不断修改.

当然, 索引总体来说, 还是利大于弊的, 因为在实际开发中, 查询场景往往比增删改查场景频繁很多, 所以我们仍然会选择牺牲一些空间, 来换取时间上的优势.

1.3 索引的使用场景

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

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

满足以上条件时, 考虑对表中的这些字段创建索引, 以提高查询效率. 反之, 如果非条件查询列, 或经常做插入, 修改操作, 或磁盘空间不足时, 不考虑创建索引.

1.4 索引的使用

在讲使用之前, 先告诉大家一个知识点, 创建主键约束(Primary Key), 唯一约束(Unique), 外键约束(Foreign Key)时，都会自动创建对应列的索引。

1.4.1 查看索引

语法:

show index from 表名;

示例: 查看student表的索引.

show index from student;

可以看到, 由于创建 student 表时, id列为主键, 所以自动创建了名为Primary的索引

1.4.2 创建索引

语法:

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

示例: 创建学生表中, name字段的索引, 叫做index_student_name.

create index index_student_name on student(name);

可以看到, 在student表中, 我们根据name列, 又创建了一个索引出来.

大家要注意, 使用创建索引操作, 可能会很危险, 如果表里的数据特别多, 这个建立索引的操作, 开销是会很大的. 那我们该如何避免该操作呢? 好的做法应该是, 在建表之处, 就提前规划好索引该创建哪些, 这样就不会在中途有很多数据的时候, 临时创建索引了.

1.4.3 删除索引

语法:

drop index 索引名 on 表名;

示例: 删除学生表中, 叫做index_student_name的索引.

drop index index_student_name on student;

可以看到, 我们之前创建的索引, 已经被删掉了.

删除索引和创建索引类似, 也是有可能存在危险的操作.

1.5 索引背后的数据结构(B+树)

索引使用的操作很容易, 但是我们要理解其背后的数据结构, 在我们之前学习过的数据结构当中, 有哪些数据结构是能加快我们的查找速度呢?

哈希表, 说到加快查询速度的数据结构, 我们无法避免的就是哈希表, 哈希表的插入和查询操作的时间复杂度可以到O(1), 虽然哈希表的查找很快, 但是索引背后的数据结构并不是哈希表. 哈希表存在一个问题, 它无法支持范围查询(between and), 也无法支持模糊匹配(like).
二叉搜索树, 改进之后的二叉搜索树(红黑树), 其时间复杂度能达到O(logN), 但是它也不适合做为支撑索引建立的数据结构, 为什么呢? 因为当树中元素变多时, 树的高度也会越来越高, 而树的高度就可以相当于元素比较次数, 由于数据库的数据都是存放在磁盘中的, 每向孩子结点访问一次, 便会发生一次磁盘IO, 所以当树的高度越高, 进行查询比较的时候, 访问此盘的次数就越多, 这样便会影响我们操作的整体性能.

那么, 什么数据结构能完美的解决我们这些问题呢? 答案就是B+树, B+树是为了数据库索引, 而量身定做的数据结构. 我们先了解B树, 再了解B+树.

1.5.1 B树

B树可以认为是一颗N叉搜索树, 它与二叉搜索树不同的点就是, 它每个结点不止有2个孩子结点, 而是根据结点中存储的数据来决定孩子结点数的.

我们可以规定每⼀个节点可以存多少个元素，当节点中达到了规定的元素个数时，才去调整树.

B树就是将数据都划分了范围, 该图中父亲结点存储了四个数据分别为30, 40, 50, 60, 其就有5个孩子结点分别用来存储小于30的数据, 大于30并且40的数据, 大于40并且小于50的数据, 大于50并且小于60的数据, 大于60的数据. 而其孩子结点也同理.

与二叉搜书树相比较来说, B树的每个结点的子树更多, 节点上保存的key值也多了, 意味着在同样key的个数的前提下, B树的高度就要比二叉搜索树的高度低很多.

不过我们索引使用的数据结构并不是B树, 而是它的改进版 ---- B+树

1.5.2 B+树

B+树做为B树的改进版, 他改进了哪里呢? 给大家看一颗B+树.

可以看到, B+树当中的数据有重复出现的, 那是因为, B+树中的每一个结点中存储的最大数据, 便是该结点范围中的最大值. 所以整个树的所有数据都会包含在叶子结点中的(所有非叶子结点中的数据, 也都会出现在叶子结点中).

😶B+树的特点:

一个结点, 可以存储N个数据, N个数据又划分出了N个区间(B树会划分N+1个区间).
每个结点中的数据, 都会在子节点中也存在(同时该数据是其子节点中的最大值).
B+树的叶子结点, 首位相连, 类似于一个链表.
由于叶子结点, 是完整的数据集合, 只需要在叶子结点存储数据表中每一行的详细数据即可, 而非叶子结点, 存储索引值本身即可, 并不用详细存储数据内容是什么.

😶B+树的优势:

当前一个结点可以保存更多的数据, 最终树的高度也是相对更矮的, 查询的时候减少了磁盘IO访问次数(原理和B树相同).
具有稳定性, 所有的查询最终都会落到叶子结点上, 查询任何一个数据, 经过的IO访问次数, 都是一样的.
B+树的所有叶子结点构成一个列表, 此时比较方便程序员进行范围查询. 当查询学号大于5并且小于11的同学, 只需要先找到5所在的位置, 再找到11所在的位置, 然后从5沿着链表遍历到11的位置, 中间所得结果集, 便为查询结果, 十分的高效.
由于数据都在叶子节点上, 而非叶子结点, 只需要存储索引值, 所以非叶子结点所占空间是比较小的, 这些非叶子结点就可能在内存中缓存一部分, 进一步减少了磁盘IO次数

1.5.3 补充: 回表

请大家思考一个问题, 当我们一个数据表中, 存在多个索引时, 比如 student 表中, 针对 id 有主键索引, 针对 name 又有一个索引时, 其数据是如何构建出来的呢?

这时表的数据还是按照 id 为主键, 构建一个B+树出来, 通过叶子结点组织所有的数据行. 其次, 针对 name 这一列, 也会构建一个B+树, 但是这个B+树的叶子结点跟非叶子结点一样也不会再存储这一样的完整数据, 而是寸主键 id 的值. 此时, 如果根据 name 来查询, 查到叶子结点时, 得到的只是主键 id 的值, 还需要再通过主键 id 的值去, 根据主键够贱的B+树里再查一遍(相当于查了两次B+树). 上述过程便称为回表, 整个过程, 在MySQL中, 都是MySQL自动完成的, 用户感知不到.

2. 事务

2.1 事务的概念

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

数据库事务是指一系列的数据库操作，这些操作被视为单个逻辑单元，必须全部完成或者全部撤销。在事务的执行过程中，如果任意一个操作失败，整个事务都必须回滚，恢复到执行事务之前的状态，保证数据库的一致性和完整性。在数据库应用中，事务通常是为了确保数据的完整性、一致性和可靠性而使用的。一些常用的应用包括银行交易、航班预订系统、电子商务等。

2.2 数据库使用的经典场景

我们来讲一下数据库事务中的经典问题 ---- 银行的转账问题.

现在我们有两个账户, 小张和小王, 其数据都存放在 account 表中.

account
name	savings
小张	1000
小王	0

# 小王给小张转账500元
# 1. 小王先减500元
update account set savings = savings - 500 where name = '小王';
# 2. 小张再加500元
update account set savings = savings + 500 where name = '小张';

我们假设, 在执行转账的过程中, 执行完操作1后, 数据库崩溃了, 此时这个转账就出现问题了, 因为小王的钱减了, 但是小张却没加钱.

而事务就是为了解决上述问题而产生的概念, 本质上就是将多个SQL语句打包为一个整体, 要么全部执行成功, 要么就一个都不执行(并不是真的没有执行, 而是看起来像是没执行一样, 是因为在执行出错之后, 选择了恢复, 把数据还原到执行前的样子, 这种操作, 叫做回滚rollback), 这样就不会出现只 "执行一半" 的尴尬场景了.

如果将以上两部操作成为一个事务, 当第一个SQL语句执行结束后, 数据库崩溃, 当下次数据库重新启动后, 就会把上次修改一半的数据给自动还原(转账出现了错误, 将小王转出去的钱给加回去), 进行回滚的时候, 怎样就能知道数据库操作前的状态呢? 数据库当中有一个专门的日志用来记录事务, 正因为如此, 使用事务的时候, 执行SQL的开销会变大, 效率也会变低, 但是提高了数据准确性.

2.3 事务的使用

使用事务的步骤主要是以下三步:

开启事务: start transaction;
执行多条SQL语句
回滚或提交: commit/rollback;

注: commit是全部成功, rollback是全部失败.

# 小王给小张转账500元
# 1. 开启事务
start transaction;

# 2. 执行多条SQL语句
# 小王先减500元
update account set savings = savings - 500 where name = '小王';
# 小张再加500元
update account set savings = savings + 500 where name = '小张';

# 提交事务 
# 到这一步,相当于事务就执行完了
commit;

2.4 事务的特性

事物的四大特性是面试中的经典题目, 请同学们务必记住:

原子性 (Atomicity): 指整个事务中的所有操作要么全部成功, 要么全部失败回滚, 不允许出现部分成功或部分失败的情况.
一致性 (Consistency): 指事务开始之前和结束之后, 数据库的状态必须是一致的, 即满足所有的约束条件和完整性规则.
隔离性 (Isolation): 指事务的执行不受其他事务的干扰, 一个事务执行过程中的中间状态对其他事务是不可见的.
持久性 (Durability): 指事务一旦提交, 对数据库的修改将永久保存到数据库中, 即使出现宕机等故障, 也不会丢失数据.

其中事务的隔离性, 其实就是为了解决 "并发" 执行事务, 而引出的一些问题. 数据库也是服务器, 就有可能有多个客户端给服务器提交事务, 这时数据库就需要同时处理处理多个客户端的事务, 这就叫做并发.

接下来就说一说, 并发处理事务, 可能有哪些问题, 以及这些问题, 数据库的隔离性是怎样给解决的.

2.5 并发执行事务时可能会出现的问题

2.5.1 脏读

假设用户A向数据库中的账户表中插入了一条新记录，但是还未提交事务，此时用户B查询账户表发现了这条新记录。但是由于用户A还未提交事务，这条记录实际上还未被确认有效，如果用户A在事务提交之前回滚了该记录，那么用户B查询到的新记录实际上是“脏数据”，这就是脏读。

为了解决脏读问题, 在MySQL中, 引入了 "给操作加锁" 这样的机制, 在本例子中, 就是当用户A向数据库中插入数据时, 给写入操作上锁, 只有当用户A确认提交事务后, 用户B才能查询该数据, 这意味着, 修改操作跟插入操作没法并发了(不能同时执行), 这个给写入操作上锁, 就降低了并发程度(效率变低), 提高了隔离性(数据准确度提高).

2.5.2 不可重复读

假设用户A在一个事务中正在查询账户表中的某个记录，但是此时用户B也开始了一个事务并且对该记录进行了修改，然后用户A再次查询该记录，发现此时该记录已经与之前查询到的不同了，这就是不可重复读。

在本例子中, 我们就可以对读操作进行上锁, 就是当用户A在查询数据时, 用户B不可以修改改数据, 通过对读加锁, 又进一步降低了并发程度, 提高了隔离性.

2.5.3 幻读

假设用户A在一个事务中查询了账户表中某个范围的记录，但是此时用户B插入了一条新记录，这个新记录就像是幻觉一样出现在了该范围内，这就是幻读。例如，A事务查询到了某个部门的所有员工信息，但是在查询过程中，B事务插入了一些新的员工记录，导致A事务查询到的结果集中出现了未查询到的员工信息。

在读加锁和写加锁的前提下, 一个事务两次读取同一个数据, 发现读取的数据值是一样的, 但是结果集不一样, 我们可以使用 "串行化" 这样的方式来解决幻读. 串行化就是彻底放弃并发处理事务, 像糖葫芦串一样一个接一个的串行的处理事务. 这样做, 并发程度是最低的, 效率是最慢的, 隔离性是最高的, 数据准确性也是最高的.

2.5 数据库中的事务隔离级别

为了处理上述所说的并发处理事务可能发生的三个经典问题, MySQL提供了4种隔离级别, 用来对应上面的几个情况.

read uncommitted(读未提交): 没有进行任何锁限制. 并发程度最高, 隔离性最低.
read committed(读已提交): 给写加锁. 并发程度降低, 隔离性提高.(解决脏读)
repeatable read(可重复读): 给写和读都加锁. 并发程度进一步降低, 隔离性进一步提高.(解决不可重复读)
serializable(可序列化的): 串行化. 并发程度最低, 隔离性最高.(解决幻读)

这几个隔离级别该如何选择, 我们需要在准确性和效率之间做权衡, 看实际业务需求, 看业务使用的场景是怎样的来决定. 比如在涉及到钱的转账问题上, 一分钱都是不能错的, 那怕效率差点, 也得保证了钱的准确性. 再比如给一个热门视屏点赞, 来看一个视屏有多少赞, 只要求快, 成千上万的人都在同一时间点赞, 如果一个一个排队来的话, 效率得慢死, 但此场景我们追求的是效率, 赞的数据有十个八个的误差并不关键.

🎉小结ending

✨ 本文主要讲述了数据库当中的索引和事务, 并且花大量篇幅讲解了一下索引背后的数据结构 ---- B+树, 又讲了一下B树和B+树到底是什么, 以及有什么特点和优势.

✨ 想了解更多数据库知识, 可以打开博主的数据库专栏目录↓小白的数据库学习之路http://t.csdn.cn/Fh557✨ 感谢你们的耐心阅读, 博主本人也是一名学生, 也还有需要很多学习的东西. 写这篇文章是以本人所学内容为基础, 日后也会不断更新自己的学习记录, 我们一起努力进步, 变得优秀, 小小菜鸟, 也能有大大梦想, 关注我, 一起学习.

再次感谢你们的阅读, 你们的鼓励是我创作的最大动力!!!!!