MySQL面试题

一、MySQL的事务隔离级别

SQL中定义了4种隔离级别，不同的隔离级别对应着事务中做不同的修改，同时在事务内和事务间的可见性也不同。越低的隔离级别越能支持更高的并发，对系统的开销也越低。

1. read uncommitted (未提交读)

最低级别，允许一个事务读取另一个事务尚未提交的数据。这可能导致脏读、不可重复读和幻读的问题。
在read uncommitted 级别中，当在事务A中对数据的修改即使是在事务未提交的时候，其他并发操作的事务依然可以读取到事务A修改的数据，这种情况我们叫做数据“脏读”。那如果事务A在后面出现回滚的操作时，而其他事务在已经读取到的是事务A之前的修改，就会导致出现数据的修改混乱。

2. read committed (已提交读)

允许一个事务只读取其他已提交事务的数据。这可以避免脏读，但仍可能出现不可重复读和幻读的问题。
在read committed级别中，当一个事务A从开始直到事务提交之前，其他的并发事务对该事务的修改都是不可见的。但是在其他同一事务内执行两次一样的查询时，查询的结果也会不同，所以这个级别同时也叫做不可重复读。

其他大多数的数据库默认的隔离级别都是read committed ，但是mysql 默认隔离级别不是。

3. repeatable read (可重复读)

mysql 默认隔离级别。在一个事务中，多次读取同一数据会得到相同的结果，即使其他事务对该数据进行了修改。这可以避免脏读和不可重复读，但可能出现幻读问题。

“幻读”是指当某个事务在读取某个范围内的记录时另一个事务又在该范围内插入了新的数据，那么再次读取该范围的数据时会出现幻行。

MySQL在存储引擎（InnoDB和XtraDB）下是通过锁机制加MVCC(多版本并发控制)来解决幻读的问题。

因此也是MySQL 中默认使用的事务隔离级别，同时需要使用事务时一般使用InnoDB存储引擎。

4. serializable (可串行化)

最高级别，要求事务串行执行，即事务之间没有并发。可以避免脏读、不可重复读和幻读，但会影响并发性能。
serializable隔离级别是将各个事务强制串行执行，在每个事务执行时都会对该事务影响的数据行进行加锁，因此其他并发的事务则无法进行读写操作，需等这条事务提交后并释放锁才能进行下一个事务的执行。

由于需要对每条数据都加锁，这样就会导致大量的操作超时和锁争用的问题。

在我们的实际项目开发中很少使用这个级别。

 

5. 查看数据库隔离级别：

 SELECT @@tx_isolation

        或

  show variables like 'tx_isolation

 

 6. MySQL设置隔离级别：

set [global/session] transaction_isolation = "隔离级别"

global代表全局设置，使用global设置时当前会话不生效需要重开会话才有效。

session代表只在当前会话中设置。

————————————————

脏读（Dirty Read）：
定义：脏读是指一个事务读取到了另一个事务尚未提交的数据，当那个事务最终回滚时，读取的数据就变得无效或“脏”了。

示例：假设有两个银行账户，Alice 和 Bob。Alice 想要转账给 Bob 1000美元，但在她的事务还没有提交之前，Bob 的查询可能会显示他的余额增加了1000美元。然而，如果 Alice 的事务最终回滚，Bob 实际上并没有得到这1000美元。

不可重复读（Non-Repeatable Read）：
定义：不可重复读是指在同一个事务中，多次读取相同数据时，得到了不同的结果。这是因为在事务执行期间，其他事务修改了数据。

示例：假设小明正在查询某本书的价格，结果是50美元。然后，他再次查询，但在这之间，另一个用户购买了这本书并将价格提高到100美元。因此，小明两次查询相同的数据得到了不同的结果，这就是不可重复读。

幻读（Phantom Read）：
定义：幻读是指在同一个事务中，多次执行相同的查询，但由于其他事务插入新数据或删除现有数据，每次查询返回的结果集不一样。

示例：考虑一个图书馆数据库，小红正在查询某个作者的所有书籍。第一次查询返回了5本书，但在事务执行期间，另一个用户添加了一本新书，所以在第二次查询时，返回了6本书。这就是幻读，因为查询结果集似乎发生了变化，尽管小红的事务并没有修改任何数据。

 二、MySQL的锁

 1.读锁（共享锁）：

它阻止其他用户更新数据，但允许他们读取数据。

2.写锁（排他锁）：

它阻止其他用户读取和更新数据。

3.全局锁：

全局锁锁的是整个数据库实例，加上全局锁后整个数据库实例下的所有数据库中的所有表都只能进行查询，包括当前这个加锁的会话也只能查询不能修改。

全局锁的典型使用场景是，进行一些需要确保整个数据库一致性的操作，例如全库备份，全库导出。

-- 加锁 FLUSH TABLES WITH READ LOCK;

-- 备份 mysqldump -uroot -p dbname > dbname.sql;

-- 解锁 UNLOCK TABLES;

 

4.表锁：

指对一整张表进行加锁。

主要的应用场景是，读多、写少、数据量不大、对整张表更新或删除。

以下命令会发生表锁：

-- 修改表结构 alert table;

-- 删除表 drop table；

-- 删除表中的数据 truncate table;

-- 加读锁 lock tables t_user read;

-- 加写锁 lock tables t_user write;

 

5.行锁：

行锁是 MySQL 中最小的锁粒度，指对一行记录进行加锁。当一个事务获得一个行锁后，其他事务就不能对该行进行任何修改操作，直到该事务释放了对该行的锁定。行锁可以精确地控制锁定的粒度，避免不必要的锁定操作。

MySQL 中的 InnoDB 存储引擎就是使用行锁的例子，它在执行修改操作时都会对涉及到的行进行锁定操作。

InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。

(1) 针对唯一索引进行检索时，对已存在的记录进行等值匹配会优化为加行锁。

(2)innodb的行锁是针对索引加的锁，不通过索引条件检索数据，将对表中所有记录都加锁此时就会升级为表锁。

共享锁：读锁。

排他锁：写锁。

select ... for update：排他锁

select ... lock in share mode：共享锁

insert ：排他锁

update：排他锁

delete：排他锁

6.乐观锁：

乐观锁机制认为对于同一个数据的并发访问情况是非常少的，因此不会出现数据冲突的情况，从而可以不加锁直接操作该数据。如果没有发现数据冲突，就会成功地进行操作，并且将操作结果更新到数据中；如果发现数据冲突，则会给出相应的提示，并让用户选择相应的处理方式（事物回滚）。乐观锁的实现方式通常包括版本号、时间戳、CAS（Compare And Swap）等技术。

-- 创建一个 test 表
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT '',
  `version` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 插入数据
INSERT INTO `test` (`name`, `version`) VALUES ('test', 0);

-- 模拟并发更新
-- session1
START TRANSACTION ;
SELECT @version:=`version` FROM `test` WHERE `id`=1;
-- 当前版本号为0
-- 延时等待 session2 修改提交
-- session2
START TRANSACTION ;
SELECT @version:=`version` FROM `test` WHERE `id`=1;
-- 当前版本号为0，`name` 改为 'test1'，`version` 加 1
UPDATE `test` SET `name`='test1',`version`=`version`+1 WHERE `id`=1;
COMMIT ;
-- `test` 表的数据变为(id=1,name='test1',version=1)
-- session1
-- 修改数据
UPDATE `test` SET `name`='test2',`version`=`version`+1 WHERE `id`=1 AND `version`=@version;
-- 若修改成功，则 `test` 表的数据变为(id=1,name='test2',version=1)；否则修改失败
COMMIT ;

7.悲观锁：

MySQL 的悲观锁机制比较常见，也是我们在日常开发中经常使用的锁机制。悲观锁机制认为当多个事务访问同一个数据时，一定会发生冲突从而导致数据不一致情况的发生，因此每次访问该数据时都会进行加锁操作，确保该数据的一致性。

悲观锁的实现方式常见有两种，分别为共享锁和排它锁。

select ... for update：排他锁

select ... lock in share mode：共享锁

-- 创建一个 test 表
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 插入数据
INSERT INTO `test` (`name`) VALUES ('test');

-- session1
START TRANSACTION ;
-- 获取悲观锁
SELECT `name` FROM `test` WHERE `id`=1 FOR UPDATE;
-- 执行一些操作
...
-- 释放悲观锁
COMMIT ;

-- session2
START TRANSACTION ;
-- 获取悲观锁
SELECT `name` FROM `test` WHERE `id`=1 FOR UPDATE;
-- 执行一些操作
...
-- 释放悲观锁
COMMIT ;

8.意向锁：

为了避免DML在执行时，加的行锁与表锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。

当事物A有行锁时，MySQL会自动为该表添加意向锁，当事物B如果想申请这个表的写锁，那么不需要遍历每一行判断是否存在写锁，而直接判断是否存在意向锁，增强性能。

意向共享锁(IS): 由语句select … lock in share mode添加 。 与 表锁共享锁(read)兼容，与表锁排他锁(write)互斥。

意向排他锁(IX): 由insert、update、delete、select…for update添加 。与表锁共享锁(read)及排他锁(write)都互斥，意向锁之间不会互斥。
9.间隙锁：

间隙锁是MySQL InnoDB存储引擎提供的一种锁定机制。它锁定的不是具体的行记录，而是两个索引之间的间隙（或者说区间），这样可以防止新的记录插入到该间隙，确保数据的一致性和事务的隔离性，防止幻读。

幻读是指在一个事务内读取某个范围的记录时，另外一个事务在该范围内插入或删除了记录，当第一个事务再次读取该范围的记录时，会发现有些原本不存在的记录。

注意：间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙锁不会阻止另一个事务在同一间隙上采用间隙锁。

-- session1
START TRANSACTION ;
SELECT * FROM `test` WHERE `id`>1 AND `id`< 5 FOR UPDATE;
COMMIT ;

-- session2
DELETE FROM `test` WHERE `id`=3;
--事务1未提交时，事务2会处于阻塞状态

10.临键锁：

Next-Key可以理解为一种特殊的间隙锁。通过临键锁可以解决幻读问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

InnoDB中行级锁时基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键）上不存在临键锁。

id	age	name
1	10	张三
2	24	李四
3	32	王五

 

该表中age列潜在的临键锁有：

(-∞,10]

(10,24]

(24,32]

(32,+∞]

--事务1
START TRANSACTION ;
SELECT * FROM user WHERE age=24 FOR UPDATE;
COMMIT ;

--事务2
INSERT INTO user VALUES(5,26,'刘六')；
--事务1未提交时，事务2会处于阻塞状态

 三、hash索引和b+tree索引的区别

1.hash索引底层是hash表，进行查找时，调用一次hash函数就可以获得相应的键值，之后进行回表查询获得实际数据。

B+树索引底层是一个多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶子节点方可获的所查询的键值，然后根据查询判断是否需要回表查询。

2.hash索引在等值查询上比B+树效率更高。Hash索引不能进行范围查询，而B+树可以。

3.hash索引不支持Order BY排序，而B+树支持。

4.hash索引在插入和删除上比较简单，只需要hash函数确定桶的位置，插入删除记录即可。而B+树的插入删除需要维护树平衡性，可能需要进行节点的拆分和节点的合并，相对来说更加复杂。