JAVA面试：mysql数据库
介绍一下MySQL架构
连接层（Connection Layer）
- 提供客户端与服务器的连接服务，负责建立、管理和终止客户端与数据库服务器之间的连接请求。
- 负责授权认证，确保只有合法的用户才能访问数据库资源。
- 实现安全协议，包括SSL加密连接等功能。
服务层（Service Layer）
- 包括SQL接口、解析器、优化器和执行器等多个组件。
- SQL接口接收客户端发送过来的SQL语句。
- 解析器对SQL语句进行词法分析和语法分析，理解SQL语句的意图。
- 优化器基于数据库表的统计信息，选择最优的执行计划，包括索引选择、表扫描顺序等。
- 执行器按照优化器产生的执行计划执行SQL语句，获取结果集，并返回给客户端。
存储引擎层（Storage Engine Layer）
- 存储引擎是MySQL的核心组件之一，负责数据的存储和读取操作。
- MySQL支持多种存储引擎，如InnoDB、MyISAM、MEMORY等，每种引擎有不同的特性，如事务处理、行级锁定、全文索引等。
- 各存储引擎独立管理其底层的数据存储结构，并通过统一的API与服务层进行交互。
系统文件层（File System Layer）
- 存储引擎将数据以物理形式持久化到磁盘上的文件系统中。
- 数据文件、索引文件以及其他类型的文件（如日志文件）都位于此层。
- 例如，InnoDB存储引擎有自己的表空间文件、重做日志文件和系统表空间等。
介绍一下InnoDB存储引擎的结构
- InnoDB 存储引擎是 MySQL 中一个非常重要的事务型存储引擎，其设计目标是提供高性能的同时保证事务处理的ACID特性。InnoDB 引擎的结构可以从内存结构和磁盘结构两个层面进行详细说明：
内存结构：
Buffer Pool（缓冲池）
- 缓冲池是InnoDB的核心组件之一，用于缓存数据页和索引页，减少磁盘I/O操作。当执行SQL查询时，首先尝试从缓冲池中获取所需页面，而不是直接从磁盘读取，显著提升了性能。
Change Buffer（变更缓冲区）
- 在非唯一二级索引修改操作中，对于尚未加载到缓冲池中的数据页，对应的更改会被记录在Change Buffer中，延迟对相应索引页的物理更新，等到这些页被访问时再合并更改。
Adaptive Hash Index（自适应哈希索引）
- InnoDB能够根据查询模式动态地为经常使用的索引构建哈希索引，从而加速某些查询操作。AHI有助于快速定位缓冲池中的数据页。
Log Buffer（重做日志缓冲区）
- InnoDB使用重做日志系统来保证事务的持久性。日志缓冲区存储着尚未写入磁盘的重做日志记录，包括对数据库所做的更改信息。
磁盘结构：
Tablespaces（表空间）
- 表空间是InnoDB逻辑上的最高层容器，可以包含多个数据文件（ibdata*或单独的xxx.ibd文件）。启用innodb_file_per_table选项后，每个表的数据和索引都会存储在一个独立的.ibd文件中。
Data Dictionary（数据字典）
- 数据字典在磁盘上保存有关InnoDB表及其索引的信息，是元数据存储的地方。
Doublewrite Buffer（双写缓冲区）
- 双写缓冲区用来防止突发故障导致的数据页损坏。新写入的数据首先被复制到这个区域，然后再写入对应的数据文件，确保即使在崩溃恢复期间也能找回未完全写入的数据。
Redo Log（重做日志）
- 重做日志文件用于记录事务对数据库所做的更改，以便在系统崩溃时能够通过回放日志恢复未提交的事务，实现事务的持久性。
Undo Logs（回滚日志）
- 回滚日志用于撤销事务操作，以支持事务的原子性和一致性。在事务回滚或并发事务间的多版本控制（MVCC）中发挥作用。
介绍一下MySQL索引结构
MySQL遇到过的性能问题，做了哪些调优
讲讲mysql mvcc
mysql 可重复读怎么怎么保证的不会幻读(mvcc + 间隙锁)
- 幻读(不可重复读)在事务隔离级别【读未提交和读已提交】隔离级别下发生
读未提交
- 允许脏读、不可重复读以及幻读发生。在这个级别下，事务可以读取到其他未提交事务修改的数据，而且随着其他事务的插入操作，查询结果可能会发生变化，表现为幻读现象。
读已提交
- 只能读取到已经提交的数据，解决了脏读问题
- 出现幻读，A事务可查询到B事务commit后记录，两次查询结果发生变化。
- A事务查询sql for update 显式加锁（行锁），B事务更新锁定记录，需等待A事务将锁释放，
- A事务查询sql for update 对一般索引范围查询（不是间隙锁），B事务可插入记录commit后，A事务可查询到新提交记录，出现幻读
可重复读(mvcc + 间隙锁)
- A事务不会读到B事务已提交的记录
- 当执行带有索引范围条件的INSERT、UPDATE或DELETE操作时，InnoDB也会自动根据条件施加间隙锁以保护事务的完整性。
手动加间隙锁，范围查询时SELECT ... FOR UPDATE （X锁，排他锁）或 SELECT ... LOCK IN SHARE MODE (S锁，共享锁)
- A事务查询sql for update 显式加锁，若为等值查询，加行锁，B事务等待锁释放
- A事务查询sql for update 显式加锁，一般索引（字段a）（非唯一）范围查询（a>=1），加间隙锁，锁定字段a值范围（1至无穷大），B事务针对字段a（大于等于1范围的值）的插入、更新的写操作都需要等待A事务间隙锁锁释放
SQL怎么优化
联合索引为什么需要最佳左前缀匹配
- 主要原因在于索引的底层实现机制以及数据检索的效率优化：
- 索引组织结构： 联合索引在MySQL的InnoDB存储引擎中是以B+树的形式存储的。在B+树中，一个节点包含多个键值对，其中键是由联合索引定义的多个字段组成的有序数组。这些键是按照联合索引定义的字段顺序依次排序的。节点存储联合索引的组合键值，叶子结点存储具体数据记录、主键等，
- 索引查找路径：
查询时，MySQL会首先使用联合索引的第一个字段来进行查找。由于B+树的有序性，基于第一个字段的值就能快速定位到对应的节点或叶子节点。
若查询条件包含联合索引的第二个字段，则可以根据第一个字段找到的范围继续沿着B+树向下查找第二个字段对应的值。
以此类推，对于每一个索引字段，都可以依据前面字段的值快速定位到下一个字段所在的子树，最终查询到记录、主键。 - 范围查询的影响：
当查询条件包含范围查询（如>、<、BETWEEN等）时，索引会停止在范围查询字段之后的索引字段上生效，即范围查询字段右边的所有字段都无法利用索引进行过滤。
这是因为范围查询打破了有序性，使得MySQL无法预知后面字段的具体分布情况，因而无法继续利用索引进行高效的检索。 - 最优化效果：
最左前缀匹配原则允许MySQL根据查询条件尽可能多地使用索引字段，从而最大化地利用索引加快查询速度，减少不必要的全表扫描或索引扫描。
- 综上所述，最左前缀匹配原则确保了MySQL能够在联合索引中高效地查找数据，并且考虑到索引结构的局限性和范围查询的特殊性，这种设计有效地平衡了索引大小与查询效率之间的关系。
数据库怎么实现乐观锁、怎么实现悲观锁
为什么不用hash B树 二叉树 而要用B+树
MySQL遇到过什么问题没？
查询时多表关联字段的字符集不一致（utf8、utf8mb4），导致查询变慢（索引无法使用），mysql实例CPU资源飙升100%。
字符集不一致时需要使用 convert 函数进行转换，从而导致索引失效。
数据库中插入100万条数据，怎么能快速插入
mysql change buffer
- InnoDB存储引擎提供了Change Buffer（变更缓冲区）功能，用于提高对非唯一二级索引的写入性能。在高并发的写入场景下，尤其是大量插入、更新操作发生在非聚簇索引页不在内存（Buffer Pool）中的情况下，Change Buffer能显著降低磁盘I/O的压力。
Change Buffer的工作原理如下：
- 非唯一二级索引的更新： 当对非唯一二级索引进行插入或删除操作时，如果目标页不在Buffer Pool中，InnoDB会选择将这个更改暂时缓存到Change Buffer中，而不是立即从磁盘读取数据页并更新。
- 合并操作： 后续当相应索引页被读取到Buffer Pool中时，InnoDB会将Change Buffer中的变更与索引页进行合并，这个过程称为Merge操作。在合并过程中，InnoDB会执行必要的插入、删除或者页分裂操作。
- 提升写性能： 通过这种方式，Change Buffer降低了大量的随机I/O，特别是在数据量大且索引页大部分不在内存中的情况下，极大地提升了写操作的性能。
需要注意的是，Change Buffer并不适用于唯一索引，因为对于唯一索引的插入操作，需要在插入前验证唯一性，而这一步骤必须立即访问索引页。此外，当数据库处于读密集型工作负载时，频繁的Merge操作可能会成为性能瓶颈，此时关闭Change Buffer可能会带来更好的性能表现。
mysql change buffer 为什么不支持唯一索引
- MySQL的InnoDB存储引擎的Change Buffer（变更缓冲区）主要是用来提高对于非唯一二级索引更新操作的性能。之所以不支持唯一索引，是因为唯一索引在插入或更新时必须立即检查索引值的唯一性，这一检查操作要求数据页已经在内存中的Buffer Pool中，以便可以直接读取和验证。
- 以下是Change Buffer不支持唯一索引的原因：
唯一性检查：
- 对于唯一索引，任何插入或更新操作都需要先确认即将插入的值在索引中是否唯一，这就意味着必须立刻访问到对应的索引页，不可能将这类更新推迟到后续阶段处理。
同步性要求：
- 唯一索引的插入和更新操作需要确保事务的一致性，尤其是并发环境下的唯一性约束，这意味着在写入数据之前必须获取适当的锁并对唯一性进行验证，这是一个即时的过程，无法通过异步的方式完成。
避免冲突：
- Change Buffer是为了减少随机I/O，通过将对尚未载入内存的数据页的更改暂存下来，等到以后需要访问这些页时再合并。而对于唯一索引，直接进行更改可以及时发现并解决潜在的唯一性冲突，避免后续合并时出现错误。
- 因此，由于上述原因，InnoDB的Change Buffer只适用于非唯一索引的更新，对于唯一索引，即使是二级索引，也不适用Change Buffer机制。
