Mysql的一些面试问题

这篇文章上次修改于 375 天前，可能其部分内容已经发生变化，如有疑问可询问作者。

说一下mysql数据库的事务隔离级别

MySQL 数据库支持多个事务隔离级别，用于控制不同事务之间的可见性和并发性。以下是 MySQL 支持的事务隔离级别：

1、READ UNCOMMITTED（读未提交）：

在这个隔离级别下，事务可以读取其他事务未提交的修改。这可能导致脏读、不可重复读和幻读等问题。

2、READ COMMITTED（读提交）：

在这个隔离级别下，事务只能读取已经提交的其他事务的修改。它解决了脏读问题，但仍然可能存在不可重复读和幻读问题。

3、REPEATABLE READ（可重复读）：

在这个隔离级别下，事务开始时，看到的所有数据都是其他事务已提交的版本。这可以解决不可重复读的问题，但仍然可能存在幻读问题。

4、SERIALIZABLE（串行化）：

这是最高的隔离级别，它通过在整个事务期间使用锁来确保事务的原子性。在这个级别下，事务彼此之间是完全隔离的，可以解决所有的并发问题，但是会影响性能。

总结：隔离级别越高，事务之间的隔离越严格，但并发性越低。选择适当的隔离级别需要在数据一致性和性能之间进行权衡。

说一下什么是脏读，幻读，不可重复度读

1、脏读（Dirty Read）：

脏读是指一个事务中的操作读取了另一个事务未提交的数据。当一个事务修改了某个数据，但尚未提交，另一个事务读取到了这个未提交的数据，就发生了脏读。
脏读的问题在 READ UNCOMMITTED 隔离级别下可能发生，因为在这个级别下事务可以读取到其他未提交的事务的修改。

2、不可重复读（Non-Repeatable Read）：

不可重复读是指在同一个事务中，两次读取同一行数据，但得到的结果不同。这是因为在两次读取之间，另一个事务修改了该行数据并提交。
不可重复读问题在 READ COMMITTED 隔离级别下可能发生，因为在这个级别下事务只能读取到已提交的数据。

3、幻读（Phantom Read）：

幻读是指在同一个事务中，两次查询得到的结果集不一致。这是因为在两次查询之间，另一个事务插入、更新或删除了符合条件的数据，导致结果集发生了变化。
幻读问题在 READ COMMITTED 隔离级别下可能发生，因为在这个级别下只保证读取已提交的数据，但并未对读取的范围进行加锁。
间隙锁（Gap Locks）可以解决幻读的问题，例如：


-- 事务1
START TRANSACTION;
SELECT * FROM table_name WHERE column1 BETWEEN 10 AND 20 FOR UPDATE;

-- 事务2
START TRANSACTION;
INSERT INTO table_name (column1, column2) VALUES (15, 'new_value');
COMMIT;

在上述例子中，事务1读取范围内的数据时使用了 FOR UPDATE，这会引入间隙锁，防止其他事务2在这个范围内插入新的数据，从而避免了幻读问题。
需要注意的是，间隙锁可能会影响数据库的并发性能，因为它会在一定程度上阻塞其他事务插入满足范围条件的新数据。因此，在选择使用间隙锁时，需要根据实际需求和性能要求进行权衡。

什么是数据库的乐观锁，它是解决什么问题的，它存在什么问题?

乐观锁是一种并发控制机制，它与传统的悲观锁相反。悲观锁是在事务开始时就对数据进行锁定，假定会有并发冲突，而乐观锁则假定并发冲突较少，只在提交更新时检查冲突。
乐观锁的基本思想是，事务在进行更新之前不会主动加锁，而是在提交更新时检查数据是否被其他事务修改过。如果在提交时发现数据已经被其他事务修改，则拒绝本次更新操作，要求用户重新尝试。

常见的实现乐观锁的方式包括：

版本号（Versioning）：

在数据表中引入版本号字段，每次更新时增加版本号。事务在读取数据时获取版本号，更新时将版本号加一。如果更新时发现版本号已经变化，表示数据已被其他事务修改，需要回滚。

时间戳（Timestamp）：

使用时间戳记录数据的最后修改时间。事务在读取数据时获取时间戳，更新时将时间戳更新。如果更新时发现时间戳不匹配，表示数据已被其他事务修改，需要回滚。

乐观锁主要解决的问题是并发更新时可能出现的冲突，以及减少了事务加锁的开销。然而，乐观锁也存在一些问题和注意事项：

冲突检测和处理：

乐观锁依赖于冲突检测机制，需要在更新时检查数据是否被其他事务修改。如果检测到冲突，需要回滚事务并重新尝试，可能导致性能损失。

并发更新频繁时的性能问题：

当并发更新非常频繁时，乐观锁的冲突检测机制可能导致大量的回滚和重试，从而影响性能。

无法阻止所有并发问题：

乐观锁不能完全阻止并发问题，因为在检查冲突和实际更新之间，其他事务仍有可能修改数据。
需要额外的字段：

乐观锁通常需要在数据表中添加额外的字段（如版本号或时间戳），这会增加存储开销。
乐观锁适用于一些并发更新冲突较少的场景，但在高并发、频繁更新的环境中，可能需要谨慎使用，并考虑其性能和可靠性。

什么是数据库的悲观锁，它是解决什么问题的，它存在什么问题?

悲观锁是一种传统的并发控制机制，它假设在事务执行期间可能会发生并发冲突，因此在事务开始时就对数据进行锁定，以确保其他事务不能同时修改相同的数据。悲观锁的核心思想是，在事务进行读取或更新操作时，先获取锁，其他事务必须等待锁的释放。

悲观锁主要解决的问题是并发操作可能导致的数据不一致性问题。悲观锁可以确保在某个事务修改数据时，其他事务不能同时对同一数据进行修改，从而避免了并发冲突。

悲观锁的常见实现方式包括：

共享锁（Shared Lock）：

共享锁允许多个事务同时对同一数据进行读取操作，但在有一个事务持有共享锁的情况下，其他事务不能获取到独占锁进行写入操作。

独占锁（Exclusive Lock）：

独占锁则是一种排他性的锁，它阻止其他事务同时对同一数据进行读取或写入操作，直到持有锁的事务释放锁为止。

悲观锁存在一些问题和局限性：

性能开销：

悲观锁的主要问题之一是性能开销，因为它在事务开始时就要获取锁，可能导致其他事务需要等待。这样的锁定机制可能降低数据库的并发性能。

死锁风险：

使用悲观锁时，存在死锁的风险。如果两个或多个事务相互等待对方释放锁，可能导致死锁的发生，需要谨慎处理。

并发度下降：

由于悲观锁会限制多个事务同时访问相同数据，因此可能导致并发度下降，特别是在高并发的环境下。

难以处理长事务：

在长事务中使用悲观锁可能会导致锁的持有时间较长，增加了其他事务等待的时间，可能影响系统的性能和吞吐量。

总的来说，悲观锁适用于对并发冲突较为敏感的场景，但在高并发、长事务和需要高度可伸缩性的系统中，可能需要考虑其他并发控制机制。

mysql 如何给事务加悲观锁

在 MySQL 中，可以使用 FOR UPDATE 和 LOCK IN SHARE MODE 语句来为事务添加悲观锁。这两个语句用于在事务中对查询的数据进行锁定，以防止其他事务同时对相同的数据进行修改。
for update 是一种独占锁
LOCK IN SHARE MODE 是共享锁

说一下innodb存储引擎下，可重复度隔离机制下的MVCC实现方式

在 InnoDB 存储引擎下，当设置事务隔离级别为可重复读（Repeatable Read）时，MVCC（Multi-Version Concurrency Control）机制会提供一致性的数据视图，确保事务在整个事务期间能够看到一个一致性的数据快照。

以下是 InnoDB 在可重复读隔离级别下的 MVCC 实现方式：

1、 Read-View 的生成

每个事务在开始时都会生成一个 Read-View，该视图包含了事务启动的时间戳。这个时间戳用于表示事务开始的时间点。

2、数据行版本控制

在每一行数据的记录中，InnoDB 维护了两个隐藏的列，分别是 DB_TRX_ID（最后修改该行的事务ID）和 DB_ROLL_PTR（用于回滚数据的指针）。这两个字段用于实现 MVCC。

3、Read-View 的判断

在执行读取操作（例如 SELECT）时，InnoDB 会判断数据行的 DB_TRX_ID 是否在事务的 Read-View 的有效范围内。如果是，则该行对该事务可见，如果不是，则表示该行的数据是由在 Read-View 创建之后提交的事务所修改的，因此不对该事务可见。

4、未提交事务的处理

当事务处于活跃状态（即尚未提交）时，其对数据库所做的修改对其他事务是不可见的。在可重复读隔离级别下，InnoDB 使用了一种称为 Next-Key Locking 的机制，它确保当前事务不会读取到未提交的事务所做的修改。

5、快照一致性

通过上述机制，可重复读隔离级别下的事务在整个事务期间都能看到一致性的数据快照，即使其他事务在此期间对同一数据行进行了修改。

这样，InnoDB 的 MVCC 机制能够提供在可重复读隔离级别下的高并发性和数据一致性。需要注意的是，MVCC 机制会导致一些额外的存储开销，因为每行数据都可能有多个版本，但这也是为了提供更高的并发性和隔离性。

innodb存储引擎下面，针对索引优化，有哪些方案？

1、合适的索引选择

确保表上存在合适的索引，以加速查询。考虑到查询的字段、联合索引的顺序以及选择性等因素，选择合适的索引。

2、覆盖索引

尽量使用覆盖索引，即索引包含了查询所需的所有字段，避免了回表操作，提高查询性能。

3、最左前缀原则

对于联合索引，最左前缀原则指的是在查询时，索引的最左边的列需要被用于查询条件，以确保索引的有效使用。

4、避免链表操作

处理业务尽量避免连表操作，对于复杂的查询尽量使用搜索引擎的方案替代

5、开通慢日志

查看慢日志sql，分析慢查询sql对应的业务，做响应的sql调优

**6、使用explan分析sql

使用explan来查看sql的执行计划
在观察 EXPLAIN 的输出时，主要关注以下几个方面：
- 查询类型 (type)：确认查询是否在所有可能的情况下都使用了最优的索引。
- 索引使用 (key 和 possible_keys)：确认查询是否使用了合适的索引。
- 行数 (rows)：估计的读取行数，该值较大可能需要优化。
- 额外信息 (Extra)：是否有使用临时表、文件排序等，这可能会影响性能。

当mysql的自增数量用完后，再增加数据会出现什么情况？

1、UNSIGNED 属性

如果自增列是无符号（UNSIGNED）的，当达到最大值时，插入下一个值会从最小值重新开始。例如，如果一个 UNSIGNED INT 列达到 4294967295（2^32 - 1），再插入新数据时将从 1 开始。

2、SIGNED 属性

如果自增列是有符号（SIGNED）的，当达到最大值时，插入下一个值会发生溢出错误，导致插入失败。例如，如果一个 INT 列达到 2147483647（2^31 - 1），再插入新数据时将导致溢出错误。

识海无涯