【MySQL】面试常考索引与事务详解

最新推荐文章于 2024-01-17 14:15:38 发布

不知名L同学

最新推荐文章于 2024-01-17 14:15:38 发布

阅读量188

点赞数 1

CC 4.0 BY-SA版权

分类专栏：笔记文章标签：数据库

本文链接：https://blog.csdn.net/m0_55916917/article/details/124335178

笔记专栏收录该内容

10 篇文章

订阅专栏

本文详细介绍了MySQL面试中常见的索引与事务知识点。讲解了索引的概念、作用、使用场景，重点阐述了B+树作为索引数据结构的优势。同时，文章探讨了事务的理解、四大特性（ACID）以及不同隔离级别，帮助读者深入理解数据库事务处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

【MySQL面试常考】索引与事务

MySQL面试常考几大问题：

给定场景写SQL语句，一般考多表查询或者联合查询（多练习）
数据库的索引：什么是索引，索引的底层数据结构（B+树）
事务：什么是事务，事务的四大特征，事务的隔离级别，不同隔离级别中产生的问题
其他内容（较琐碎，可以多看看面经）

1. 什么是索引

1.1 概念

关系的某属性（组）上的索引是一种数据结构，它提供了在该属性（组）上快速查找具有某个特定值的元组的方法。

索引可以建立在元组的某一属性或者属性组上，这个属性或者属性组就被称为索引键（index key），有时也被称为查找键。索引键可以来自关系的任何一个属性或者属性组，而不必是建立索引关系的键属性。

索引就好比书的目录（index），本质上用于加快查找的效率，避免数据库进行顺序查找。如果数据库中没有索引，此时查找就需要把整个表遍历一遍，效率就会很低。

1.2 作用

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

1.3 使用场景

要考虑对数据库表的某列或某几列创建索引，需要考虑一下几点：

数据量较大，且经常对这些列进行条件查询。
该数据库表的插入、删除、修改操作的频率较低（因为索引会减慢这些操作的效率）。
索引会占用额外的磁盘空间（本质上是使用空间来换时间）。

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

1.4 索引的使用

创建主键约束（primary key）、唯一约束（unique）、外键约束（foreign key）时，会自动创建对应列的索引。

（1）查看索引

show index from 表名;-- 查看索引
-- 举例：
-- 查看学生表已有的索引
show index from student;

（2）创建索引
对于非主键、非唯一约束、非外键的字段，可以创建普通索引。

create index 索引名 on 表名（字段名）;
-- 举例：
-- 创建班级表中，name字段的索引
create index idx_classes_name on classes(name);

（3）删除索引

drop index 索引名 on 表名;
-- 举例：
-- 删除班级表中name字段的索引
drop index idx_classes_name on classes;

说明：创建索引和删除索引都是耗时操作。

1.5 索引的数据结构（难点）

索引可以考虑的数据结构有二叉树（二叉搜索树）和哈希表。
而在MySQL中，真实的索引结构是一种N叉搜索树，也就是B+树。

那么为什么不使用二叉树和哈希表？

原因：

二叉搜索树在比较平衡的情况下，查找效率是O(logN)，虽然从算法逻辑上来说，查找速度和比较次数都是最小的，但是我们不得不考虑磁盘IO。数据库索引是存储在磁盘上的，当数据量比较大的时候，索引的大小可能有几个G甚至更多。那么使用索引查询大规模数据时，树节点存储的元素数量是有限的（如果元素数量非常多的话，查找就退化成节点内部的线性查找了），这样就会导致二叉查找树结构由于树的深度过大而造成磁盘I/O读写过于频繁，进而导致查询效率低下。
哈希表查找效率是O(1)，效率更高。但是存在一些问题：哈希表只能处理相等的情况，不能处理使用其他逻辑运算符的情况，比如> >= < <= between and 等。

为了更好理解B+树，我们要先了解什么是B树。

1.5.1 什么是B树？

B树，又叫B-树（注意这不是减号，而是一个连接号，B-tree）。
B 树是为了磁盘或其它存储设备而设计的一种多叉平衡查找树（以下会解释，相对于二叉，B树每个内结点有多个分支，即多叉）。
我们可以采用多叉树结构（由于树节点元素数量是有限的，自然该节点的子树数量也就是有限的）来减少树的深度（当然是不能减少查询的数据量），避免磁盘过于频繁的查找，提高查找效率。

B树的各种操作能使B树保持较低的高度，从而达到有效避免磁盘过于频繁的查找存取操作，从而有效提高查找效率)。

为了描述B-Tree，首先定义一条数据记录为一个二元组[key, data]，key为记录的键值，对于不同数据记录，key是互不相同的；data为数据记录除key外的数据。
一个简单的B树示意图：
在这里插入图片描述
和二叉树的差异：
（1）每个节点不是二叉了，而是N叉；
（2）每个节点不是存一个数据了，而是可能存多个数据。

1.5.2 什么是B+树？

B-Tree有许多变种，其中最常见的是B+Tree，例如MySQL就普遍使用B+Tree实现其索引结构。

与B-Tree相比，B+Tree有以下不同点：
（1）每一层的元素之间都链接到一起了；
（2）数据（表中的一行记录）只在叶子节点上保存，非叶子节点上只保存一些辅助查找的边界信息。也就是说内节点不存储data，只存储key；叶子节点不存储指针。

一个简单的B+树示意图：
在这里插入图片描述

说明：给具体的表的某列加索引的时候，加在主键上的索引和加在其他列上的索引是截然不同的（和auto_increment无关,只要指定了primary key 就是主键索引，主键索引不可删除）。
主键的叶子节点存的是数据的完整记录，其他索引的叶子节点存的是主键的id。

2. 什么是事务

2.1 理解

举个例子来理解：
数据表中保存了一些人的银行账户余额，接下来需要进行：账户A向账户B转账3000块钱。
那么会涉及到两个步骤：

第1步： A的账户余额 -3000
第2步： B的账户余额 +3000

如果步骤1执行成功，步骤2执行过程中出现问题，此时A账户的钱减少了但是B账户的钱没有增加，那么3000块钱是否凭空消失了？
要想规避这个问题，就需要使用事务。保证以上操作使用的SQL语句要么全部执行成功，要么全部执行失败。

2.2 概念

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

简单来理解，就是把一组操作封装到了一起，成为了一个共同的执行单元，此时执行整个事务就能保证这一组操作能被正确执行。

2.3 使用

（1）开启事务：

start transaction;-- 开启事务

（2）执行多条SQL语句
（3）回滚或提交：

rollback/commit;

说明：rollback即是全部失败，commit即是全部成功。

-- 例如：
-- 开启事务
start transaction;
-- 用户A账户减少3000
update accout set money=money-3000 where name = '用户A';
-- 用户B账户增加3000
update accout set money=money+3000 where name = '用户B';
commit;

2.4 事务的基本特征（ACID）

2.4.1 原子性

是事务当中最重要的特性。
事务中的若干操作，要么全部执行成功，要么全部失败回滚（rollback）。

借助逆向操作，把原来操作造成的影响进行还原。

2.4.2 一致性

执行事务前后，数据始终处于一种合法合理的状态。也就是说事务执行之前和执行之后都必须处于一致性状态。

例如：转账操作，减去账户余额的时候，不能把账户减为负数。

2.4.3 持久性

事务一旦执行完毕，此时对于数据的修改就是持久生效的（写入磁盘中了）。

数据存到磁盘中就是持久的，存到内存中就是不持久的，重启就没了。

2.4.4 隔离性（重要）

隔离性是事务当中最复杂的一个特性，是事务的核心。涉及到了“并发执行事务”。
隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。

即要达到这样的效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

要想了解隔离性，就需要先了解事务并发时存在的问题：

1. 脏读（Dirty Read）
脏数据指的是未提交的数据，而脏读是指在一个事务处理过程中读取了另一个未提交的事务中的数据。
比如一个事务正在对一条记录作修改，在这个事务完成并提交之前，这条数据是处于待定状态的（可能提交也可能回滚），这时，第二个事务来读取这条没有提交的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被称为脏读。

2.不可重复读（Non-repeatable Read）
一个事务先后读取同一条记录，而事务在两次读取之间该数据被其他事务所修改，则两次读取的数据不同，我们称之为不可重复读。
例如事务T1在读取某一数据，而事务T2立马修改了这个数据并且提交事务给数据库，事务T1再次读取该数据就得到了不同的结果，发送了不可重复读。

不可重复读和脏读的区别：
脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是在同一事务内读取了前一事务提交的数据，即前一次读到的数据是另一个事务提及前，后一个读到的数据是提交后的。

3.幻读（Phantom Read）
一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读。
幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作第一个事务的用户发现表中还存在没有修改的数据行，就好像发生了幻觉一样。

三个的区别：
幻读和不可重复读都是读取了另一条已经提交的事务（这点就脏读不同），所不同的是不可重复读查询的都是同一个数据项，而幻读针对的是一批数据整体（比如数据的个数）。

在实际开发和使用中，其实幻读问题是可以接受也符合用户的心理预期，比如在淘宝双十一抢东西，点击“购买”，成功进入**“下单页面”，结果到“付款页面”**却提示已经被抢完，这是可以接受的。