Java后端八股文之Mysql

0. 什么是数据库事务？

事务是一个不可分割的数据库操作序列，也是数据库并发控制的基本单位，其执行的结果必须使数据库从一种一致性状态变到另一种一致性状态。事务是逻辑上的一组操作，要么都执行，要么都不执行。

1. ACID是什么？

ACID 是数据库管理系统（DBMS）中用于确保事务可靠性和一致性的四个核心特性的缩写。

ACID 的四个特性如下：

• 原子性（Atomicity）：事务是一个不可分割的操作单元，要么全部执行成功，要么全部失败回滚。原子性确保事务中的所有操作要么全部执行，要么全部撤销，不会出现部分执行的情况。
• 一致性（Consistency）：事务在执行前后数据库必须保持一致的状态。这意味着事务的执行不会破坏数据库中的完整性约束，如主键、唯一性约束、外键等。如果事务违反了数据库的一致性规则，系统会将其回滚到事务开始之前的状态。
• 隔离性（Isolation）：事务的执行应该与其他事务隔离开，互不干扰。每个事务在执行期间应该感觉不到其他并发事务的存在，就好像它是唯一在运行的事务一样。隔离性可以通过并发控制机制（如锁）来实现，以防止并发事务之间出现数据不一致的问题。
• 持久性（Durability）：一旦事务成功提交，其对数据库的影响应该是永久的，即使在系统故障或重启之后也应该保持。持久性通过将事务的结果写入持久性存储介质（如磁盘）来实现，以确保即使在系统崩溃或断电的情况下，数据也能够被恢复。

这些 ACID 特性共同确保了数据库事务的可靠性、一致性和持久性。通过遵循 ACID 原则，DBMS 可以有效地管理事务，保证数据的完整性和可靠性，从而提供可靠的数据处理和保证数据一致性的能力。

2. ACID的实现原理是什么？

MySQL 是一种常用的关系型数据库管理系统（RDBMS），它采用了多种机制来实现 ACID 特性：

• 原子性（Atomicity）：MySQL 使用日志（log）来实现事务的原子性。在事务执行期间，所有的修改操作都会被写入事务日志（transaction log）中。如果事务执行失败或回滚，MySQL 可以使用日志来撤销已经执行的修改，将数据库恢复到事务开始之前的状态。
• 一致性（Consistency）：MySQL 通过实施完整性约束和触发器来确保数据库的一致性。完整性约束（如主键、唯一性约束、外键等）可以保证数据的有效性和一致性。触发器可以在数据库中的数据发生变化时自动执行相应的操作，以确保数据的一致性。
• 隔离性（Isolation）：MySQL 通过并发控制机制来实现隔离性，以防止并发事务之间的数据冲突。MySQL 支持多种事务隔离级别，如读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。不同的隔离级别提供了不同的并发控制机制，以满足不同的应用需求和性能要求。
• 持久性（Durability）：MySQL 使用写入日志和缓冲区管理来实现持久性。当事务提交时，MySQL 将修改操作写入事务日志，并使用缓冲区管理来将数据写入磁盘。通过将事务日志和数据写入持久性存储介质（如磁盘）并确保其可靠性，MySQL 可以在系统故障或重启后恢复事务的持久性。

此外，MySQL 还提供了其他机制来增强 ACID 特性的实现，例如锁机制、MVCC（多版本并发控制）、回滚段（Undo log）、重做日志（Redo log）等。

总之，MySQL 通过使用事务日志、完整性约束、触发器、并发控制机制、持久性存储和其他相关技术，实现了 ACID 特性，确保数据库事务的可靠性、一致性、隔离性和持久性。这使得 MySQL 成为一个可靠的数据库管理系统，适用于许多企业和应用场景。

3. MVCC是什么？

MVCC（Multi-Version Concurrency Control）是一种并发控制机制，常用于数据库管理系统中，用于处理并发事务访问共享数据的情况。

在传统的并发控制机制中，如锁机制，当一个事务修改一个数据项时，会对该数据项进行加锁，其他事务要访问该数据项时需要等待锁释放。这种机制在高并发环境下可能导致大量的锁竞争和事务等待，从而影响系统的性能。

MVCC 采用了一种不同的策略来解决并发访问共享数据的问题。它基于时间戳的概念，为每个事务分配一个唯一的时间戳，并为数据库中的每个数据项维护多个版本。每个版本都与事务的时间戳相关联，表示该版本是在该时间戳之前最新的可见版本。
当一个事务开始时，它使用自己的时间戳来确定可以看到哪些数据版本。在读取数据时，事务只能看到早于自己时间戳的数据版本，从而避免了读取到其他事务正在修改的数据。在写入数据时，事务会创建一个新的数据版本，并将其与自己的时间戳相关联。这样，其他事务可以继续读取旧版本的数据，而不受新版本的影响。

MVCC 的主要优点包括：

• 并发性能：MVCC 可以减少锁竞争和事务等待，提高并发性能。多个事务可以同时读取共享数据，而不会相互阻塞。
• 读一致性：MVCC 可以提供读一致性，即事务读取的数据是在事务开始时一致的快照。这意味着事务可以避免读取到其他事务正在修改的数据。
• 无锁读取：MVCC 的读操作不需要加锁，因此可以避免读锁的开销，提高读取操作的性能。

需要注意的是，MVCC 机制需要额外的存储空间来存储多个版本的数据项。此外，对于长时间运行的事务或大量并发写入的情况，MVCC 可能导致版本链的增长，占用大量的存储空间。

MVCC 是许多数据库管理系统的核心并发控制机制，如Oracle、PostgreSQL 和 MySQL（InnoDB 存储引擎）。它通过时间戳和多版本数据的概念，提供了高并发性和读一致性的能力，从而提升数据库系统的性能和并发控制效果。

4. undo log是什么？

Undo log（回滚日志）是数据库管理系统中的一种机制，用于支持事务的回滚和数据的恢复。它记录了事务执行过程中对数据所做的修改操作，以便在事务回滚或系统故障时进行数据恢复。

当一个事务执行修改操作时，数据库会将这些修改操作写入回滚日志中，而不是立即将其应用到磁盘上的数据。回滚日志保存了旧值和新值的信息，以及用于识别事务的标识（如事务 ID 或时间戳）。这样，在事务回滚或系统崩溃时，数据库可以使用回滚日志中的信息来撤销或恢复事务的修改操作，将数据库恢复到一致的状态。

回滚日志具有以下作用：

• 事务回滚：当一个事务需要回滚时，数据库可以使用回滚日志中的信息来撤销事务所做的修改操作，恢复到事务开始之前的状态。
• 并发控制：回滚日志在并发控制中起着重要的作用。当一个事务需要读取数据时，如果该数据正在被其他事务修改，数据库可以使用回滚日志中的旧值来提供一致的读取结果，而不会受到其他事务的影响。
• 持久性：回滚日志的写入通常是在内存中完成的，因此可以提供较高的写入性能。然后，数据库会将回滚日志写入持久性存储介质（如磁盘），以确保即使在系统故障或重启之后，数据的修改操作也可以被恢复。

回滚日志通常与重做日志（Redo log）一起使用，以提供数据的持久性和一致性。重做日志记录了事务对数据的修改操作，以便在系统崩溃或重启后重新应用这些操作，从而恢复到最新的一致状态。回滚日志和重做日志的组合可以提供事务的原子性、一致性和持久性。

5. redo log是什么

Redo log（重做日志）是数据库管理系统中的一种机制，用于支持事务的持久性和恢复。它记录了事务执行过程中对数据所做的修改操作，以便在系统崩溃或重启后重新应用这些操作，从而将数据库恢复到最新的一致状态。
当一个事务执行修改操作时，数据库会将这些修改操作写入重做日志中，而不是立即将其应用到磁盘上的数据。重做日志记录了修改操作的详细信息，如被修改的数据块、修改前后的值以及用于识别事务的标识（如事务 ID 或时间戳）等。

重做日志具有以下作用：

• 数据持久性：当事务提交时，数据库会将事务的修改操作写入重做日志。重做日志的持久性存储通常是在磁盘上完成的，这样即使在系统崩溃或重启之后，数据库可以通过重新应用重做日志中的操作来恢复数据的修改，确保数据的持久性。
• 数据恢复：在系统崩溃或重启后，数据库会使用重做日志中记录的修改操作来重新应用这些操作，将数据库恢复到最新的一致状态。通过重做日志的恢复，数据库可以保证已提交的事务的修改操作不会丢失。
• 并发控制：重做日志在并发控制中起着重要的作用。当一个事务需要写入数据时，数据库可以使用重做日志来确保事务的修改操作按照正确的顺序进行，从而保证数据的一致性。

需要注意的是，重做日志通常与回滚日志（Undo log）一起使用，以提供数据的持久性和一致性。回滚日志记录了事务对数据的修改操作，以支持事务的回滚和数据的恢复。重做日志和回滚日志的组合可以提供事务的原子性、一致性和持久性。

6. MySQL 中的 varchar 和 char 有什么区别？

char 是一个定长字段,假如申请了char(10)的空间,那么无论实际存储多少内容.该字段都占用 10 个字符,而 varchar 是变长的,也就是说申请的只是最大长度,占用的空间为实际字符长度+1,最后一个字符存储使用了多长的空间.

7. MySQL中 in 和 exists 区别

MySQL中的in语句是把外表和内表作hash 连接，而exists语句是对外表作loop循环，每次loop循环再对内表进行查询。一直大家都认为exists比in语句的效率要高，这种说法其实是不准确的。这个是要区分环境的。

如果查询的两个表大小相当，那么用in和exists差别不大。 如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in。 not in 和not exists：如果查询语句使用了not in，那么内外表都进行全表扫描，没有用到索引；而not extsts的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

8. MySQL 的四种隔离级别

• Read Uncommitted（读取未提交内容）
  在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。

• Read Committed（读取提交内容）
  这是大多数数据库系统的默认隔离级别（但不是 MySQL 默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓 的 不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的 commit，所以同一 select 可能返回不同结果。

• Repeatable Read（可重读）
  这是 MySQL 的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。

• Serializable（可串行化）
  通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

9. 什么是脏读？幻读？不可重复读？

• 1、脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据是脏数据

• 2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果 不一致。

• 3、幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

不可重复读侧重于修改，幻读侧重于新增或删除（多了或少量行），脏读是一个事务回滚影响另外一个事务。

10. 什么是MySQL的 binlog？

MySQL的 binlog 是记录所有数据库表结构变更（例如 CREATE、ALTER TABLE）以及表数据修改（INSERT、UPDATE、DELETE）的二进制日志。binlog 不会记录 SELECT 和 SHOW 这类操作，因为这类操作对数据本身并没有修改，但你可以通过查询通用日志来查看 MySQL 执行过的所有语句。

MySQL binlog 以事件形式记录，还包含语句所执行的消耗的时间，MySQL 的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。

binlog 有三种格式，各有优缺点：

• statement： 基于 SQL 语句的模式，某些语句和函数如 UUID, LOAD DATA INFILE 等在复制过程可能导致数据不一致甚至出错。
• row： 基于行的模式，记录的是行的变化，很安全。但是 binlog 会比其他两种模式大很多，在一些大表中清除大量数据时在 binlog 中会生成很多条语句，可能导致从库延迟变大。
• mixed： 混合模式，根据语句来选用是 statement 还是 row 模式。

11. InnoDB引擎的行锁是怎么实现的？

InnoDB是基于索引来完成行锁

例: select * from tab_with_index where id = 1 for update;

for update 可以根据条件来完成行锁锁定，并且 id 是有索引键的列，如果 id 不是索引键那么InnoDB将完成表锁，并发将无从谈起。

12. MySQL主从同步延时问题如何解决？

MySQL 实际上在有两个同步机制，一个是半同步复制，用来 解决主库数据丢失问题；一个是并行复制，用来 解决主从同步延时问题。

半同步复制，也叫 semi-sync 复制，指的就是主库写入 binlog 日志之后，就会将强制此时立即将数据同步到从库，从库将日志写入自己本地的 relay log 之后，接着会返回一个 ack 给主库，主库接收到至少一个从库的 ack 之后才会认为写操作完成了。
并行复制，指的是从库开启多个线程，并行读取 relay log 中不同库的日志，然后并行重放不同库的日志，这是库级别的并行。

13. 统计过慢查询吗？对慢查询都怎么优化过？

在业务系统中，除了使用主键进行的查询，其他的我都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么？ 是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？

所以优化也是针对这三个方向来的，

• 首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。
• 分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。
• 如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。