Java后端面试题：MySQL篇

MySQL基础部分

1. SELECT语句完整的执行顺序是什么？

2. 说一说内连接和外连接。

3. 请说说数据库三大范式。

4. 请你说说视图的作用，视图可以更改么？

架构

5. 请你说一说MySQL架构。

6. 请你说说一条SQL语句的执行过程，例如UPDATE user SET name="xiaoming" WHERE id=1。

7. 如果是InnoDB，UPDATE语句在执行器调用引擎时都发生了什么？

索引

8. 请你说说索引的优缺点？

9. 请你介绍MySQL数据库的索引原理，它是如何提高查询性能的？

10. 请你简单介绍你知道的数据库索引。

11. 请你说说Hash索引和B+树索引，说说他们的区别。

12. 介绍下主键索引，主键索引和聚集索引有什么区别？

13. 什么是聚簇索引和非聚簇索引？

14. 什么是回表？优化器如何决定回表还是全表查询？

15. 如何防止回表？

16. 什么是覆盖索引？

17. 什么是索引条件下推？

18. 请你介绍B树和B+树的区别。

19. MySQL为什么选择B+树，而不是B树作为索引数据结构。

20. 如何合理设计索引，如何选择合适的字段作索引？

22. 索引什么时候失效？

存储引擎

23. 有哪些常见的存储引擎？

24. 说说你对InnoDB引擎的理解。

25. 说说你对MyISAM引擎的理解。

26. 如果表中绝大多数都只是读查询，应该用哪个引擎？

27. InnoDB、MyISAM和Memory的区别是什么？

28. 什么是InnoDB的页、区、段？

29. InnoDB为什么推荐使用自增主键？

30. 在分布式架构中，为什么又不推荐使用自增ID作为主键？

31. UUID可以用来作为主键吗？存在哪些问题？

32. 雪花算法可以生成主键，请请你描述雪花算法的原理，能介绍一下它有哪些优势和不足？该如何解决这些不足呢？

事务

33. 什么是事务？事事务的ACID特性是什么？

34. 事务的数据并发问题有什么？

35. 解释MySQL中的事务隔离级别，以及它们如何影响并发。

事务日志

36. 请你说说Redo Log。

37. 请你说说Undo Log。

38. 请你说说Bin Log。

39. 为什么有了Bin Log还需要Redo Log？

40. 请你说说Redo Log的三种刷盘策略。

41. 为何不直接将修改的数据写入磁盘，而是要Write Ahead Log呢？

42. 说说Redo Log的Checkpoint机制。

锁

43. 什么是锁？MySQL中提供了几类锁？

44. 什么是死锁？产生死锁的原因是什么？死锁产生的必要条件是什么？

45. 如何查看死锁？如何处理死锁？

46. 查询结果长期不返回是什么原因？

47. InnoDB默认是如何对待死锁的？

48. 如何开启死锁检测？

49. 什么是全局锁？它的应用场景有哪些？

50. 请你解释一下共享锁和排它锁。

51. 请你说说MySQL有什么表锁？

52. 你了解自增锁吗？

53. InnoDB有哪几类行锁？

54. 请你解释一下悲观锁和乐观锁。

MVCC

55. ACID是什么保证的？

56. 解释一下什么是快照读和当前读。

57. 谈谈你对MVCC的了解？

58. 谈谈MVCC的隐藏字段？

59. 谈谈MVCC的Undo Log版本链？

60. 谈谈MVCC的Read View？Read View生成的时机是什么时候？

61. RR是如何解决幻读的？

调优

62. 为什么避免使用SELECT *，而选用SELECT 字段的语法？

63. MySQL中1000万条数据如何快速分页查询？

64. MySQL中的DISTINCT和GROUP BY哪个更高效？

65. 如果发现SQL查询慢，怎么排查和优化？

66. MySQL中的慢查询日志是什么，如何使用它来优化性能？

67. 如何优化MySQL中的关联查询以提高查询效率？

68. 为什么建议小表驱动大表？

MySQL重点：索引、事务、锁、MVCC

MySQL基础部分

1. SELECT语句完整的执行顺序是什么？

FROM -> WHERE -> GROUP BY -> HAVING -> SELECT 的字段 -> DISTINCT -> ORDER BY -> LIMIT

SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5

FROM player JOIN team ON player.team_id = team.team_id # 顺序 1

WHERE height > 1.80 # 顺序 2

GROUP BY player.team_id # 顺序 3

HAVING num > 2 # 顺序 4

ORDER BY num DESC # 顺序 6

LIMIT 2 # 顺序 7

2. 说一说内连接和外连接。

内连接（INNER JOIN）仅返回两个表中匹配的行，而外连接分为左外连接（LEFT JOIN）和右外连接（RIGHT JOIN），左外连接会返回左表的所有行以及右表中匹配的行，右外连接则返回右表的所有行以及左表中匹配的行。在优化查询时，内连接可以通过小表驱动大表来减少扫描行数，而外连接则通常以左表为驱动表，通过在右表的连接字段上建立索引来提高查询效率。

3. 请说说数据库三大范式。

第一范式（1NF）：要求表中的所有字段都是原子性的，即每个字段只包含不可分割的最小数据单位，表中没有重复的行。

第二范式（2NF）：在满足1NF的基础上，要求表中的非主键字段必须完全依赖于主键，不能仅依赖于主键的一部分，即表中不存在部分依赖。

第三范式（3NF）：在满足2NF的基础上，要求表中的非主键字段必须直接依赖于主键，不能存在传递依赖，即非主键字段之间不能相互依赖。

4. 请你说说视图的作用，视图可以更改么？

（1）视图在MySQL中主要有以下作用：

① 简化复杂的SQL操作

视图可以将复杂的查询语句封装起来，用户可以通过简单的查询来获取所需的数据。

② 数据安全

视图可以限制用户访问数据表中的一部分数据，只暴露必要的数据，从而提供一定程度的数据安全性。

③ 数据独立性

视图可以屏蔽底层表结构的变化，用户不必关心底层表的具体结构，只需操作视图即可。

（2）是否可以更改

视图本身是一个虚拟表，它不存储数据，只保存逻辑，而是基于底层表的结果集。关于视图是否可以更改，这取决于视图的定义。

架构

5. 请你说一说MySQL架构。

MySQL的架构可以分为两层：服务器层和存储引擎层。

（1）服务器层：包括连接器、分析器、优化器、执行器。连接器负责处理客户端与MySQL服务器之间的连接，包括管理连接、权限认证。分析器负责解析客户端发送的SQL语句，包括词法分析、语法分析。优化器负责选择索引、优化SQL、为INNER JOIN选择驱动小表。执行器负责调用执行引擎。

（2）存储引擎层：存储引擎用来具体执行SQL，常见的存储引擎有InnoDB、MyISAM、Memory等。

6. 请你说说一条SQL语句的执行过程，例如UPDATE user SET name="xiaoming" WHERE id=1。

首先客户端与MySQL连接器进行连接，然后分析器经过词法分析和语法分析，然后到达优化器，优化器生成执行计划，选择索引，这里选择主键索引id，然后就到达了执行器，执行器调用存储引擎，存储引擎返回结果。

7. 如果是InnoDB，UPDATE语句在执行器调用引擎时都发生了什么？

执行器调用存储引擎，找到id=1的数据，由于id是主键，直接在主键索引树上查找。如果这行数据所在的数据页在内存中，直接返回给执行器结果就可以了。如果不在内存中，会从磁盘中读取数据到内存，再返回给执行器结果。然后执行器拿到存储引擎返回的这行数据，把name设置成"xiaoming" ，得到新的数据，再调用执行引擎的接口写入这行数据。接着执行引擎将这行新的数据更新到内存中，同时将更新的记录写到Redo Log中，此时Redo Log处于prepare的状态，然后再告诉执行器随时可以提交事务，执行器会根据操作生成Bin Log，然后把Bin Log写入磁盘，写入成功执行器会调用存储引擎提交事务的接口，存储引擎把Redo Log的prepare状态改成commit状态，到此整个更新就完成了。

索引

8. 请你说说索引的优缺点？

（1）优点：保证数据唯一性、检索速度快、加快表的连接、优化分组和排序

① 提高查询速度：索引可以大幅提升数据检索的效率，类似于图书馆的索引目录，能够快速定位到所需的数据，从而减少数据库的I/O操作，提高查询性能。

② 保证数据唯一性：通过创建唯一索引，可以确保表中每一行数据的唯一性，避免数据重复。

③ 加速表连接：在执行涉及多个表的联合查询时，索引可以加速表之间的连接操作，特别是对于具有外键关系的表。

④ 优化分组和排序：在进行分组（GROUP BY）和排序（ORDER BY）操作时，索引可以显著减少查询时间，降低CPU的使用率。

（2）缺点：耗费时间和空间，增删改的效率降低

① 时间和空间成本：索引的创建和维护需要消耗时间，尤其是在数据量大的情况下，索引的构建和维护会更加耗时。同时，索引本身需要占用额外的存储空间。

② 降低数据维护效率：当对表中的数据进行插入、删除和更新操作时，索引也需要相应地进行更新，这可能会降低这些写操作的性能。

9. 请你介绍MySQL数据库的索引原理，它是如何提高查询性能的？

MySQL数据库的索引原理是基于数据结构中的B+树实现的。索引能够提高查询效率，其核心思想是通过快速定位来减少磁盘I/O次数。当执行查询操作时，MySQL会使用索引来快速找到数据行，而不是遍历整个数据表。B+树结构使得索引在查找、插入和删除操作中都能保持较高的效率，因为它的中间节点不存储数据，只有叶子节点才存储数据，并且叶子节点之间是通过指针连接的，便于范围查询。

10. 请你简单介绍你知道的数据库索引。

（1）B树索引是一种自平衡的树结构，它允许搜索、顺序访问和插入/删除操作，但相比于B+树，它的磁盘I/O操作较多。

（2）B+树索引是MySQL中最常用的索引类型，它的所有数据都在叶子节点，并且叶子节点之间有指针相连，适合范围查询。

（3）哈希索引基于哈希表实现，适用于等值查询，但不支持排序和范围查询。

（4）全文索引用于全文搜索，能够快速检索文本中的关键词。

（5）空间索引则是为空间数据类型设计的，适用于地理空间数据的查询。每种索引都有其特定的应用场景和优缺点。

11. 请你说说Hash索引和B+树索引，说说他们的区别。

（1）Hash和B+树索引的简要介绍

Hash索引底层就是Hash表，进行查询时调用Hash函数获取到相应的键值（对应地址），然后回表查询获得实际数据。B+树索引底层实现原理是多路平衡查找树，对于每一次的查询都是从根节点出发，查询到叶子节点方可以获得所查键值，最后查询判断是否需要回表查询。

（2）Hash索引的特点

① Hash进行等值查询更快，但无法进行范围查询。因为经过Hash函数建立索引之后，索引的顺序与原顺序无法保持一致，故不能支持范围查询。同理，也不支持使用索引进行排序。

② Hash不支持模糊查询以及多列索引的最左前缀匹配，因为Hash函数的值不可预测，如AA和AB的算出的值没有相关性。

③ Hash任何时候都避免不了回表查询数据。

④ 虽然在等值上查询效率高，但性能不稳定，因为当某个键值存在大量重复时，产生Hash碰撞，此时查询效率反而可能降低。

附：InnoDB不支持Hash索引，Memory支持Hash索引

（3）B+树索引的特点

① B+树本质是一棵查找树，自然支持范围查询和排序。

② 在符合某些条件（聚簇索引、覆盖索引等）时候可以只通过索引完成查询，不需要回表。

③ 查询效率比较稳定，因为每次查询都是从根节点到叶子节点，且为树的高度。

12. 介绍下主键索引，主键索引和聚集索引有什么区别？

（1）主键索引

主键索引是一种特殊的唯一索引，用于确保表中每一行数据的唯一性。在MySQL中，当定义一个主键时，系统会自动创建一个主键索引。主键索引可以加快查询速度，因为它们提供了快速的数据访问路径。

（2）区别

主键索引和聚集索引的区别在于：主键索引是一种约束，用于保证数据的唯一性；而聚集索引是一种索引存储方式，它决定了表中数据的物理存储顺序。在InnoDB存储引擎中，通常主键索引就是聚集索引，因为InnoDB会按照主键的顺序来存储数据。如果一个表没有主键，InnoDB会选择一个唯一的索引作为聚集索引，或者隐式地创建一个。

13. 什么是聚簇索引和非聚簇索引？

答案：

（1）聚簇索引是指数据的物理存储顺序与索引顺序相同，即索引项的顺序与表中记录的物理顺序一致。在聚簇索引中，叶子节点直接存储了数据行。

（2）非聚簇索引的物理存储顺序与索引顺序不同，叶子节点存储的是数据行的地址，需要根据地址再次查询才能获取到数据行。

扩充：请你进一步说说聚簇索引和非聚簇索引的数据结构和性能。

（1）数据结构

聚簇索引和非聚簇索引底层都是根据B+树的数据结构实现的。

（2）性能

由于数据行和索引顺序一致，对于范围查询和排序操作，聚簇索引通常有更好的性能，因为不需要额外的查找。对于非聚簇索引，查询需要先在索引中找到对应的数据行指针，然后根据指针去数据页中找到实际的数据，可能会增加额外的IO开销。

（3）索引更新

由于数据的物理顺序依赖于聚簇索引，因此当插入、删除或更新操作导致行移动时，可能会引起大量的数据移动，从而降低性能。非聚簇索引的更新不会引起数据行的物理移动，因此通常对性能的影响较小。

（4）主键索引

在MySQL的InnoDB存储引擎中，主键通常会自动创建为聚簇索引。

而在MyISAM存储引擎中，即使主键也存在，索引都是非聚簇的。

14. 什么是回表？优化器如何决定回表还是全表查询？

回表是指在非聚簇索引查询过程中，当我们通过索引找到对应的数据行地址后，需要回到数据表的聚簇索引中去查询完整的记录。

举一个例子：例如SELECT * FROM table WHERE key>1 AND key<1000000; key是一个二级索引。

如果扫描的二级索引行数特别多，不如全表扫描了，那优化器就会选择全表扫描，如果是有加上LIMIT 10这样的限制，那么回表次数就少惹，优化器可能就会选择回表。

15. 如何防止回表？

如果一个索引包含了查询中需要的所有列，那么这个索引就被称为“覆盖索引”。创建索引时，可以包含查询中需要的所有列，这样查询就可以直接通过索引返回结果，而不需要回表。

如果查询条件中已经包含了主键，那么直接使用主键进行查询，不需要回表。

16. 什么是覆盖索引？

覆盖索引是指一个索引包含了查询所需的所有字段的值。例如：SELECT id, name FROM user WHERE name='Jack'，为name建立了索引，同时id也是主键。优点有避免InnoDB表的二次查询（回表），把随机I/O变成顺序I/O。

17. 什么是索引条件下推？

索引条件下推（ICP）是一种数据库查询优化技术。它允许数据库在索引查找过程中，将部分WHERE子句的条件提前应用到索引扫描阶段，从而减少需要访问的索引项和回表的数据行数。这样做的目的是提高查询速度，减少数据库系统的I/O开销进而提升整体查询性能。

18. 请你介绍B树和B+树的区别。

B树和B+树都是平衡多路查找树。

（1）节点结构

B树：每个节点都包含键（Key）和数据（Value）。键用于排序，而数据是与键关联的实际信息。

B+树：非叶子节点只包含键，用来进行索引，不包含数据。所有的数据都存储在叶子节点中，并且叶子节点之间是通过指针相连的，形成了一个有序链表。

（2）查询效率

B树：由于每个节点都可能包含数据，查询可能在非叶子节点结束，这可能导致更多的磁盘I/O操作。

B+树：查询总是从根节点到叶子节点，因为所有数据都在叶子节点上，这样可以减少磁盘I/O操作，提高查询效率。

19. MySQL为什么选择B+树，而不是B树作为索引数据结构。

（1）B+树查询效率更稳定

由于数据只存在在叶子结点上，所以查找效率固定为O(log n)，所以B+树的查询效率相比B树更加稳定。

（2）减少磁盘I/O

B+树的非叶子节点不存储实际数据，只存储索引值，这使得每个节点可以存储更多的索引，从而降低了树的高度，减少了磁盘I/O操作的次数。

（3）适合范围查找

B+树的叶子节点是有序的，这使得范围查找变得非常高效，而B树的范围查找可能需要通过中序遍历，效率较低。数据库操作中范围查找非常常见，因此B+树在这方面更具优势。

20. 如何合理设计索引，如何选择合适的字段作索引？

（1）选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。

（2）为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

（3）为经常需要排序、分组和联合操作的字段建立索引；

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

（4）限制索引的数目

每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦。

（5）小表不建议索引（如数量级在百万以内）

由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

（6）尽量使用数据量少的索引；

如果索引的值很长，那么查询的速度会受到影响。此时尽量使用前缀索引。

（7）删除不再使用或者很少使用的索引。

21. 哪些情况不适合索引？

（1）对于不在查询条件（WHERE子句）中使用的字段，无需创建索引，因为索引不会被查询优化器利用。

（2）对于数据量较小的表，索引可能不会带来显著的性能提升，反而可能因为索引的维护而降低写入效率。

（3）在那些具有高度重复值的列上建立索引通常是不划算的，因为索引的筛选效果不佳。

（4）对于频繁进行写操作（如插入、更新、删除）的表，应避免创建过多索引，以免降低写操作的性能。

（5）使用无序的值作为索引可能会导致索引效率低下，因为索引的目的是快速定位数据，而无序的值无法有效支持这一目的。

22. 索引什么时候失效？

（1）最左前缀原则：在使用复合索引时，查询未遵循最左前缀原则。

（2）函数和运算：避免在查询条件中对索引字段使用函数或运算。

（3）类型转换：确保查询条件中的数据类型与字段定义的类型匹配，避免隐式类型转换。

（4）模糊查询：避免使用前导通配符的模糊查询，如LIKE '%value'。

（5）OR条件：确保OR连接的所有条件都有索引，否则整个查询可能不会利用索引。

（6）不等号查询：使用!=或<>可能会使索引失效，应谨慎使用。

（7）IS NOT NULL：IS NOT NULL条件通常不会利用索引，IS NULL会利用索引。

（8）字符集不一致：保持数据库字符集的一致性，推荐使用utf8mb4。

存储引擎

23. 有哪些常见的存储引擎？

（1）MyISAM：MyISAM曾经是MySQL5.5版本及之前的默认存储引擎。MyISAM表不支持事务，行级锁和外键约束的功能。

（2）InnoDB：MySQL5.5版本开始，InnoDB成为了默认的存储引擎，它提供了事务、行级锁机制和外键约束的功能。

（3）Memory：这种类型的数据表只存在于内存中。它使用Hash索引，所以数据的存取速度非常快。因为是存在于内存中，所以这种类型常应用于临时表中。

24. 说说你对InnoDB引擎的理解。

InnoDB是MySQL默认的存储引擎。

InnoDB支持事务（最重要的特性，MyISAM是不支持事务的），来保证数据的ACID特性（原子性、一致性、隔离性、持久性）。

InnoDB支持行级锁（MyISAM支持表级锁）。行级锁对比表级锁粒度更细，提高了系统的并发性能。

InnoDB实现了外键约束。这使得数据库设计者能够通过外键关系建立表与表之间的关联，保证了数据的参照完整性。

InnoDB使用的是B+树索引，B+树检索速度较快，磁盘IO相对较少。

InnoDB的数据和索引等存储在.idb和.frm文件中，.frm在MySQL8.0之后数据合并到.idb里面（而MyISAM的表数据信息存放在.myd，表数据索引存放在.myi）。

25. 说说你对MyISAM引擎的理解。

MyISAM是基于B+树实现的，但是它B+树索引的叶子节点存储的不是数据本身，而是数据的指针或叫做数据的地址。所以索引和数据是分开存储的。因此相当于每次查询都要进行回表操作。MyISAM支持表锁和全文索引。但是不支持行锁和事务。由于锁的粒度是表级别的，因此适合用于读多写少的场景，例如日志分析等等。因此可以将读操作分配给MyISAM存储引擎，写操作分配给其他存储引擎。

26. 如果表中绝大多数都只是读查询，应该用哪个引擎？

如果业务主要是读查询，可以选择MyISAM引擎。MyISAM引擎在读取操作上进行了优化，因为它支持表级锁定，减少了锁定开销，并且在查询时不会因为写锁定而阻塞读操作，这使其在只读或读多写少的场景下性能表现优异。此外，MyISAM的存储格式使得它占用的空间更小，索引的大小也更紧凑，这有助于提高I/O效率。然而，需要注意的是MyISAM不支持事务和行级锁定，如果业务未来可能需要事务支持或高并发写操作，那么MyISAM可能不是最佳选择。

27. InnoDB、MyISAM和Memory的区别是什么？

从事务支持，外键支持和锁机制的角度回答。

28. 什么是InnoDB的页、区、段？

在InnoDB存储引擎中，页是磁盘管理的最小单位，通常大小为16KB，用于存储数据和索引；区是由连续的页组成，默认大小为1MB，是空间分配的基本单位；段则是由多个区组成，用于管理表和索引的数据。

29. InnoDB为什么推荐使用自增主键？

自增主键的顺序性保证了新记录总是被添加到索引的末尾，从而避免了频繁的页分裂，减小了插入数据操作的开销。如果插入到两个数据中间，可能会进行频繁的数据移动。有助于减少数据碎片，使得数据存储更加连续。对于范围查询，自增主键能够更好地利用索引的有序性，提高查询效率。

30. 在分布式架构中，为什么又不推荐使用自增ID作为主键？

（1）分区写入热点：使用自增ID时，所有的写入操作都会集中在最新的分区上，这会导致写入热点问题。一旦当前分区写满，写入操作才会移动到下一个分区，这种“尾部热点”现象会降低系统的写入性能。

（2）负载均衡困难：自增ID的连续性使得负载均衡变得困难。在分布式系统中，理想的状况是将请求均匀地分配到不同的服务器或分区上，而自增ID无法实现这一点。

31. UUID可以用来作为主键吗？存在哪些问题？

（1）优点：UUID几乎可以保证在全球范围内是唯一的，这对于分布式系统尤其重要。UUID不容易被猜解，因此可以提供一定程度的隐私保护。

（2）缺点：对于InnoDB这类使用B+树索引的存储引擎，无序的UUID会导致页分裂和碎片化，降低查询效率。

32. 雪花算法可以生成主键，请请你描述雪花算法的原理，能介绍一下它有哪些优势和不足？该如何解决这些不足呢？

原理是基于时间戳和机器标识生成一个64位的唯一ID。

（1）优势：

全局唯一性：由于时间戳和机器标识的组合，可以保证生成的ID在全局范围内是唯一的。

有序性：生成的ID按照时间戳的顺序递增，便于排序和范围查询。

（2）不足：

时间依赖：如果系统时钟发生回拨，可能会导致ID重复

事务

33. 什么是事务？事事务的ACID特性是什么？

事务是指一系列操作，这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。

（1）原子性（Atomicity）：事务中的所有操作要么全部成功，要么全部失败回滚，不会处于中间状态。

（2）一致性（Consistency）：事务必须使数据库从一个一致性状态（合法的状态）转移到另一个一致性状态（合法的状态）。

破坏一致性的例子：

举例1：A账户有200元，转账300元出去，此时A账户余额为-100元（不合法）。你自然就发现此时数据是不一致的，为什么呢？因为你定义了一个状态，余额这列必须>=0。

举例2：A账户有200元，转账50元给B账户，A账户的钱扣了，但是B账户因为各种意外，余额并没有增加（不合法）。你也知道此时的数据是不一致的，为什么呢？因为你定义了一个状态，要求A+B的总余额必须不变。

举例3：在数据表中我们将`姓名`字段设置为`唯一性约束`，这时当事务进行提交或者事务发生回滚的时候，如果数据表的姓名不唯一，就破坏了事务的一致性要求。

（3）隔离性（Isolation）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的。

（4）持久性（Durability）：一个事务一旦提交，它对数据库中数据的改变就是永久性的。

34. 事务的数据并发问题有什么？

（1）脏写
一个事务修改了另一个未提交事务的数据。例如如果事务 A修改了另一个未提交事务B修改过的数据，然后B回滚操作，那就意味着发生了脏写（没有提交的数据就是脏数据）。

此时A和B事务结束后查询既不是张三，又不是李四。

（2）脏读

一个事务读取了另一个未提交事务的数据。例如事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据（没有提交的数据就是脏数据）。

如果解决了“脏读”，那么事务A读取的是之前的数据。

（3）不可重复读

一个事务在一段时间内，两次读取的数据不一致。例如，事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

如果解决了“不可重复读”，那么事务A读取的student信息仍然保持不变。

（4）幻读

一个事务按照相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足条件的新数据。例如，事务A从一个表中读取了一个字段，然后事务B在该表中插入了一些新的行。之后, 如果事务A再次读取同一个表，就会多出几行。那就意味着发生了幻读。

MySQL默认是解决了可重复读，如果事务B插入了“赵六”的信息，事实上事务A仍然查询不到“赵六”的信息了（这是MySQL的特性），但是无法插入“赵六”数据，仍然有“幻读”。

35. 解释MySQL中的事务隔离级别，以及它们如何影响并发。

按照严重性来排一下序：不论是哪种隔离级别，都不允许脏写的情况发生。

脏写 > 脏读 > 不可重复读 > 幻读

READ UNCOMMITTED（读未提交）

允许读取尚未提交的数据变更，可能会导致脏读、不可重复读和幻读。

READ COMMITTED（RC，读已提交）

只允许读取已经提交的数据变更，可以避免脏读，但不可重复读和幻读仍可能发生。

REPEATABLE READ（RR，可重复读）

确保在事务内可以多次读取同样的数据结果，避免脏读和不可重复读，但幻读仍可能发生。这是MySQL默认的隔离级别。

SERIALIZABLE（可串行化）

完全隔离，确保事务可以从数据库中检索到的数据是一致的，如同事务是顺序执行的一样，避免了脏读、不可重复读和幻读，但会大大降低系统的并发能力。

MySQL支持以上四种隔离级别，默认为REPEATABLE READ；而 Oracle 只支持SERIALIZABLE级别和READ COMMITTED两种，其中默认为读已提交。

事务日志

36. 请你说说Redo Log。

Redo Log保证的是事务的持久性，用于记录事务对数据库所做的修改操作的日志，用于在系统发生故障时，重新执行这些操作。。

Redo Log由Redo Log Buffer和Redo Log File组成。

Redo Log采取了WAL机制，也就是先写入Redo Log File，然后定时刷新到磁盘的数据库内，这样可以减少磁盘的随机写，减少了写操作的延迟。Redo Log在内存中是一个环形队列，写入的顺序是顺序写的，由Write Pos和CheckPoint维护，由于环形队列的空间是有限的，当环形队列快要满的时候就会阻塞，将Redo Log File的数据刷新到磁盘的数据库中。

37. 请你说说Undo Log。

Undo Log保证的是事务的原子性和一致性。

Undo Log是用于记录事务发生之前的数据状态的日志，用于在事务执行失败或系统发生故障时，回滚事务对数据库所做的修改，确保事务的原子性。当对一条记录改动的时候，如插入了一条数据，Undo Log就要根据主键删除一条数据。Undo Log的作用是回滚数据，其次MVCC也是由Undo Log保证的。

38. 请你说说Bin Log。

Bin log主要存储SQL语句，主要用于主从复制，例如读写分离和主从备份。

Bin Log有三种不同的日志格式：

STATEMENT格式记录的是SQL语句本身。优点是日志文件较小，便于传输和存储，缺点是可能导致主从数据不一致。

ROW格式记录的是数据行的实际变化，即每行数据的变化前后的完整记录。优点是可以确保主从数据的一致性，缺点是日志文件可能会非常大。

MIXED格式结合了STATEMENT和ROW格式的优点。对于大多数操作，它使用STATEMENT格式记录，但在必要时（如非确定性操作）会自动切换到ROW格式。这种格式旨在提供一种平衡，既减少了日志文件的大小，又保持了数据一致性。

39. 为什么有了Bin Log还需要Redo Log？

有了Bin Log还需要Redo Log，因为Redo Log确保了事务的持久性，即使在数据库崩溃时也能恢复未写入磁盘的数据更改，而Bin Log主要用于数据复制和恢复，两者的写入时机和作用不同，共同保障了数据库的完整性和高可用性。

Bin Log是在事务提交后才记录的，如果数据库在事务执行过程中崩溃，那么尚未提交到Bin Log的事务更改将会丢失，因为没有Redo Log来保证这些更改在崩溃后能够被重新应用。

40. 请你说说Redo Log的三种刷盘策略。

通过设置innodb_flush_log_at_trx_commit参数，可以控制Redo Log的显式刷盘行为。

（1）当设置为1时（默认值），每个事务提交时，InnoDB都会执行一个显式的刷盘操作，确保Redo Log记录被立即写入磁盘。这是最安全的设置，可以保证事务的ACID属性。

（2）当设置为0时，InnoDB不会在每次事务提交时进行刷盘操作，而是依赖于操作系统的调度。这可能会在系统崩溃时丢失最后一个事务。

（3）当设置为2时，InnoDB会在事务提交时将Redo Log写入文件系统缓存，但不会立即执行fsync()操作，而是在每秒执行一次刷盘操作。

41. 为何不直接将修改的数据写入磁盘，而是要Write Ahead Log呢？

WAL是一种日志记录机制，它要求所有的数据修改操作（如插入、更新、删除）都必须先写入日志文件，然后才应用到数据库的实际数据页上。

WAL保证了数据恢复，确保了所有的修改操作都有记录，在系统崩溃或断电的情况下，未写入磁盘的数据可能会丢失，而可以根据Redo Log File日志来重放这些操作，确保数据的完整性。

42. 说说Redo Log的Checkpoint机制。

在整个日志文件组中还有两个重要的属性，分别是Write Pos和Checkpoint。Write Pos是当前记录的位置，记录了Redo Log Buffer到Redo Log File的刷盘写到了哪里，一边写一边后移；Checkpoint是当前要擦除的位置，记录了内存到磁盘的数据页写到哪了，也是往后推移。随着新日志的写入和应用，Write Pos和Checkpoint都会不断向后移动。在系统崩溃时，数据库会从Checkpoint开始，重放至Write Pos之间的日志记录，以确保所有数据变更得以持久化。

锁

43. 什么是锁？MySQL中提供了几类锁？

锁是实现数据库并发控制的重要手段，可以保证数据库在多人同时操作时能够正常运行。MySQL提供了全局锁、行级锁、表级锁。其中InnoDB支持表级锁和行级锁，MyISAM只支持表级锁。

44. 什么是死锁？产生死锁的原因是什么？死锁产生的必要条件是什么？

（1）死锁是指多个进程因为竞争资源而造成相互等待的情况，若无外力作用，这些进程都无法向前推进。

（2）产生死锁的原因：①资源竞争：多个事务同时访问同一资源，并且请求的资源总量超过了系统可用资源量。②资源持有等待：事务持有部分资源，等待获取其他事务持有的资源，而其他事务也在等待该事务释放其持有的资源。③事务不按顺序访问资源：多个事务访问同一批资源，但访问顺序不一致，可能导致循环等待。④锁定资源不释放：事务在等待其他资源时，没有释放自己已经持有的资源。

（3）死锁产生的必要条件：①互斥条件：进程对所获得的资源必须互斥访问，即同一时刻只能有一个进程访问；②请求和保持：进程在至少获得一个资源的情况下，又提出新的资源需求；③不可抢占：进程所获得的资源在其完成之前不可被其他进程抢占；④循环等待链：当发生死锁时，必然存在一个进程——资源循环等待链。

45. 如何查看死锁？如何处理死锁？

（1）使用命令 show engine innodb status 查看最近的一次死锁。InnoDB Lock Monitor打开锁监控，每15s输出一次日志。使用完毕后建议关闭，否则会影响数据库性能。

（2）对待死锁常见的两种策略：通过innodb_lock_wait_timeout来设置超时时间，一直等待直到超时；使用死锁检测处理死锁程序，通过wait-for graph算法来主动进行死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其它事务继续执行。

46. 查询结果长期不返回是什么原因？

执行结果长期不返回，可能是发生了死锁，可以通过执行show processlist命令来查看当前正在运行的所有数据库连接和线程的状态，从而发现是否有线程处于“Waiting for a lock”或“Locked”状态。

47. InnoDB默认是如何对待死锁的？

InnoDB 默认是使用设置死锁时间来让死锁超时的策略，默认innodb_lock_wait_timeout设置的时长是 50s。

48. 如何开启死锁检测？

设置innodb_deadlock_detect = on可以主动检测死锁，在 Innodb 中这个值默认就是 on 开启的状态。

49. 什么是全局锁？它的应用场景有哪些？

全局锁就是对整个数据库实例加锁，它的典型使用场景就是做全库逻辑备份和数据迁移。

50. 请你解释一下共享锁和排它锁。

共享锁允许多个事务同时读取同一数据项，但不允许进行写操作。

排它锁用于独占访问数据项，即加锁的数据项不能被其他事务读取或修改。

对于InnoDB引擎来说，共享锁和排它锁可以加在表上，也可以加在行上。

51. 请你说说MySQL有什么表锁？

（1）表级的共享锁和排它锁。当一个事务对表中的数据执行读取操作时，会施加共享锁，其他事务也可以施加共享锁，但无法进行更新操作，直到所有共享锁被释放。当一个事务需要对表中的数据进行写操作时，会施加排他锁，这个锁会阻塞其他事务对这个表进行读或写操作，直到排他锁被释放。

（2）意向锁。当事务准备在表中的某些行上施加共享锁时，它首先会在表级别施加一个意向共享锁。当事务准备在表中的某些行上施加排他锁时，它会在表级别施加一个意向排他锁。意向锁的主要目的是为了在一个事务中表明对表中的行记录进行加锁的意图，这可以使得其他事务更高效地判断是否可以安全地获取表中的行锁。例如对整个表添加表锁时，先检查是否有意向锁，无须检查每个行是否有行锁。

（3）元数据锁。元数据锁确保了在事务执行期间，表的结构不会发生改变，从而保证数据的一致性。

（4）自增锁。自增锁的作用是在执行插入操作时，确保为AUTO_INCREMENT列分配的唯一序列号不会因为并发插入操作而产生冲突。

52. 你了解自增锁吗？

自增锁（AUTO-INC锁）是MySQL中InnoDB存储引擎为了维护带有AUTO_INCREMENT属性列的一致性和顺序性而采用的一种表级锁。当多个事务同时插入数据时，自增锁能够保证每个事务获取到的自增值是连续且不重复的，从而确保了数据的一致性。

自增锁有三种模式：传统锁定模式会为所有插入操作加AUTO-INC锁；连续锁定模式只为批量插入加AUTO-INC锁；交错锁定模式是MySQL8.0之后的默认模式，它不使用AUTO-INC锁，允许多个语句交错生成自增值，可能会导致自增值不连续。

53. InnoDB有哪几类行锁？

（1）记录锁（Record Lock）：也称为行锁，它锁定的是索引记录。如果表没有设置索引，那么InnoDB会使用隐式的主键索引来锁定记录。记录锁只会锁定涉及的特定行，不会影响到其他行。

（2）间隙锁（Gap Lock）：当使用范围条件而不是相等条件检索数据时，并请求共享或排他锁时，InnoDB会使用间隙锁。间隙锁锁定的是索引记录之间的间隙，目的是防止其他事务在这个间隙内插入新的记录，从而避免幻读问题。

（3）临键锁（Next-Key Lock）：临键锁是记录锁和间隙锁的组合，它不仅锁定一个记录，还锁定这个记录前面的间隙。在InnoDB的可重复读隔离级别下，默认使用临键锁来锁定记录，这样可以有效防止幻读现象。

54. 请你解释一下悲观锁和乐观锁。

（1）悲观锁

① 基本概念

悲观锁假设在数据被访问时，极有可能发生冲突，因此在数据处理之前就加锁，直到事务完成后再释放锁。在写多读少的情况下，悲观锁可能是更合适的选择。

② 实现方式

通常通过数据库提供的锁定机制实现，如行锁、表锁、读写锁等。

③ 特点

阻塞：如果一个事务持有了某数据的锁，其他尝试访问该数据的事务将会被阻塞，直到锁被释放。

锁竞争：可能导致较高的锁竞争和较少的并发。

安全性：相对更安全，因为锁机制保证了数据在事务中的隔离性。

（2）乐观锁

① 基本概念

乐观锁假设在数据被访问时，发生冲突的可能性较低，因此不会在操作开始时就加锁，而是在数据提交时检查是否有冲突。在读多写少的情况下，乐观锁通常是更好的选择。

② 实现方式

通常通过版本号或时间戳机制实现。每次读取数据时，会记录数据的版本号或时间戳，更新数据时检查版本号或时间戳是否发生变化。如果变化了，表示数据在读取后被其他事务修改，更新操作将失败。

③ 特点

非阻塞：不会阻塞其他事务的执行，只有在提交时才会检查冲突。

锁竞争：减少了锁竞争，提高了并发性。

安全性：相对于悲观锁来说，乐观锁在高度并发环境下可能会遇到更多的一致性问题。

MVCC

55. ACID是什么保证的？

（1）原子性

Undo Log：如果事务中的某条SQL语句执行失败，那么MySQL会使用undo日志来撤销该事务已经执行的所有操作，确保数据库状态回到事务开始之前。

（2）一致性

一致性是事务的最终目标，它确保事务执行的结果是数据库状态的合法状态。MySQL通过原子性、隔离性和持久性来间接保证一致性。

（3）隔离性

① MVCC：MVCC允许数据在多个事务中可见，而不会互相干扰，这样不同的读写操作可以并发执行，而不会互相阻塞。

② 隔离级别：MySQL通过设置不同的隔离级别来控制事务之间的可见性和交互，从而提供不同程度的隔离性。

（4）持久性

Redo Log：当事务提交时，InnoDB存储引擎会将事务对数据页的所有修改记录到Redo Log中。如果系统发生故障，MySQL可以使用Redo Log来恢复数据，确保已经提交的事务所做的更改不会丢失。

56. 解释一下什么是快照读和当前读。

（1）快照读就是读取的是快照数据。快照读通常用于SELECT语句，它不会对读取的数据加锁，从而避免了脏读、不可重复读和幻读的问题。

（2）当前读就是读的是最新数据，而不是历史的数据。在执行当前读时，InnoDB存储引擎会对读取的数据加锁，确保在事务提交前，其他事务不能对这些数据进行修改。适用于UPDATE、DELETE和SELECT FOR UPDATE语句。

57. 谈谈你对MVCC的了解？

MVCC（多版本并发控制）允许多个事务同时对同一数据进行读取和修改，而不会相互干扰。MySQL默认的隔离级别是REPEATABLE READ，可以通过MVCC解决脏读、不可重复读和幻读问题。的MVCC的实现原理是依靠：隐含字段、Undo Log版本链和Read View实现的。MVCC适用于读已提交和可重复读的隔离级别。

58. 谈谈MVCC的隐藏字段？

（1）事务ID（Transaction ID）：指的是修改该行记录的事务ID。每次一个事务修改一行数据时，系统会将这个事务的ID写入该字段。

（2）回滚指针（Rollback Pointer）：这个字段指向该行数据的前一个版本。当数据被修改时，并不是直接覆盖原有数据，而是插入一条新记录，并通过回滚指针链接到旧记录。这样，即使有新的事务在操作数据，旧的事务依然可以通过回滚指针访问到旧版本的数据。

59. 谈谈MVCC的Undo Log版本链？

版本链是指在MVCC中，每个数据行可能存在多个版本，这些版本按照时间顺序连成一条链。每个版本代表了一个事务对数据行的修改结果。当事务读取数据时，会根据事务的隔离级别和事务开始的时间点来决定可以读取哪个版本的数据。

60. 谈谈MVCC的Read View？Read View生成的时机是什么时候？

它是事务在某一时刻的“一致性读”视图。简而言之，Read View是事务执行SQL语句时，用来确定哪些版本的数据是可见的快照。

对于RC隔离级别：每当执行一个普通的SELECT操作时，InnoDB会为这个SELECT语句创建一个Read View，用于确定语句可以看到哪些数据版本。

对于RR隔离级别：Read View是在事务的第一个一致性读（SELECT）操作时创建的，并且在事务的整个过程中都使用这个Read View，这就是可重复读的隔离级别能够确保事务中的一致性读的原因。

61. RR是如何解决幻读的？

在Repeatable Read（RR）隔离级别下，解决幻读问题主要依赖于以下三个机制：快照读、行锁和间隙锁。

（1）快照读。大多数实现RR隔离级别的数据库系统使用MVCC机制的快照读。它通过保存数据行的多个版本来支持并发读取，使得每个事务看到的是数据库的一个快照，从而避免了读取到其他事务插入或删除的行。

（2）行锁。当事务更新某行数据时，数据库会锁定这一行，直到事务完成。这样，其他事务就不能修改这行数据，从而保证了事务读取的一致性。

（3）间隙锁。在RR隔离级别下，除了对索引记录加锁外，还会对索引记录之间的间隙加锁。如果一个事务在某个范围内读取数据，其他事务不能在这个范围内插入新的记录，从而避免了幻读。

调优

【MySQL调优】如何进行MySQL调优？一篇文章就够了！-CSDN博客

62. 为什么避免使用SELECT *，而选用SELECT 字段的语法？

（1）通过EXPLAIN查看执行计划：

SELECT *的访问方法（type）：ALL级别

SELECT 字段1, 字段2的访问方法（type）：range级别

（2）执行索引：

如果查询中只涉及某些字段，而这些字段恰好建立了索引，那么SELECT 字段1, 字段2的访问方法查询将能够更有效地利用索引。如果使用SELECT *，可能会涉及到不在索引中的字段，导致索引的覆盖扫描无法使用，从而转变为更耗费资源的全表扫描。

腾讯二面：你对禁止使用 SELECT *有什么看法？我被恶心到了。。_哔哩哔哩_bilibili

63. MySQL中1000万条数据如何快速分页查询？

（1）原子性

Undo Log：如果事务中的某条SQL语句执行失败，那么MySQL会使用undo日志来撤销该事务已经执行的所有操作，确保数据库状态回到事务开始之前。

（2）一致性

一致性是事务的最终目标，它确保事务执行的结果是数据库状态的合法状态。MySQL通过原子性、隔离性和持久性来间接保证一致性。

（3）隔离性

① MVCC：MVCC允许数据在多个事务中可见，而不会互相干扰，这样不同的读写操作可以并发执行，而不会互相阻塞。

② 隔离级别：MySQL通过设置不同的隔离级别来控制事务之间的可见性和交互，从而提供不同程度的隔离性。

（4）持久性

64. MySQL中的DISTINCT和GROUP BY哪个更高效？

在语义相同且有索引的情况下，DISTINCT和GROUP BY确实都可能使用索引来提高查询效率，如果查询优化的结果是相同的，那么它们的效率可能会相近。

但是，在没有索引的情况下，DISTINCT通常会比GROUP BY更高效，因为DISTINCT只需要找出不同的值，而不需要进行排序或其他额外的分组操作。相比之下，GROUP BY可能会在内存中进行排序这会增加额外的计算开销。因此，在没有索引支持时，选择DISTINCT通常可以避免不必要的排序操作，提高查询性能。

65. 如果发现SQL查询慢，怎么排查和优化？

（1）使用EXPLAIN分析查询计划：通过执行EXPLAIN命令，可以查看MySQL是如何执行SQL查询的，包括是否使用了索引、扫描的行数、连接类型等，这有助于发现查询性能瓶颈。

（2）分析慢查询日志：开启MySQL的慢查询日志功能，可以记录执行时间超过设定阈值的查询语句，通过分析这些日志可以找到慢查询的具体原因。

（3）利用开源工具，如Arthas、Prometheus、Skywalking跟踪哪条SQL查询慢。

（4）检查索引使用情况：确认查询中涉及的字段是否都有适当的索引。如果没有索引或者索引使用不当，可能会导致全表扫描，从而降低查询速度。

（5）优化SQL语句：检查SQL语句是否可以优化，比如减少子查询、避免使用SELECT *、优化JOIN操作、使用LIMIT限制返回结果等。

（6）检查数据库表结构：确保表结构设计合理，比如避免过多的冗余数据、合理使用数据类型、适当的分区等。

（7）服务器性能监控：监控服务器的CPU、内存、磁盘I/O等性能指标，确定是否是服务器资源不足导致查询变慢。

（8）查询缓存：检查MySQL的查询缓存是否开启，以及查询是否能够被缓存命中。

（9）数据库配置优化：检查MySQL的配置文件，如my.cnf或my.ini，确保配置参数（如innodb_buffer_pool_size、query_cache_size等）适合当前的工作负载。

66. MySQL中的慢查询日志是什么，如何使用它来优化性能？

慢查询日志是MySQL数据库中记录执行时间超过指定阈值的查询语句的日志。通过分析慢查询日志，可以发现数据库性能瓶颈，从而优化SQL语句或数据库结构。要使用慢查询日志，首先需要开启慢查询日志功能，并设置合适的阈值（例如，将long_query_time参数设置为大于0的值）。然后，通过查看慢查询日志文件，分析日志中的慢SQL语句，对其进行优化，比如增加索引、优化查询条件等。